Standardizing User Activation: Building a Shared Data Layer at Karrot

1. Background and Goals

• Limitation of Active User Metrics: Only shows 'what happened', not 'why it happened'.
• Need for User Activation: Analyzing user activity through States and State Transitions for deeper insights.
• Goal of Shared Activation Layer: Ensure data reliability, operational stability, and increase reusability.

2. Activation Layer Structure and Design

• Layered Architecture: Composed of Base → Fact → Activation layers.
• Core Models: Operates a set of three models – FirstLast, Activation, and Activation Status – based on a Fact model.
• Key Challenges Addressed:
  • Reliability: Clearly defining reference actions in Fact models to eliminate ambiguity.
  • Cost: Implementing a daily snapshot approach in the FirstLast model to minimize backfill and daily calculation costs.
  • Productivity: Enabling the creation of new Activation models without writing SQL by leveraging DBT Macros.

3. Detailed Model Functions

• FirstLast Model: Stores daily snapshots of each user's first/last action timestamps (cost optimization).
• Activation Model: Calculates New/Retained/Reactivated states and return intervals based on Fact data (decomposes Active User components).
• Activation Status Model: Provides inactivity duration, consecutive active days, and transition segments, including inactive periods (refines user flow).

4. Use Cases

• AU Dashboard: Identifies causes through contribution by activity state and Transition Segment analysis.
• Marketing Targeting: Analyzes CRM actions and performance using criteria like inactivity duration.
• Core Action Tracking: Defines core actions per service and supports strategic decisions from the perspective of user flow changes.