Trail Trekker Subscription Analytics Pipeline

Problem

The Growth team at Trail Trekker needs reliable, timely insight into how customers change their subscriptions over time—upgrades, downgrades, pauses, and churn. They lack a warehouse and scheduled transformations to answer questions like which plans customers upgrade to, when churn events occur, and the revenue impact of plan changes.

Stakeholder

Primary: Growth/Revenue Operations (ETAs on upgrades, churn detection, MRR impact).
Secondary: Product & Finance (feature adoption, pricing tiers, forecasting).

Approach

1) Ingestion (Python + DuckDB)

• CSV sources ingested into a persistent DuckDB file (trail_trekker.db).
• Idempotent table creation via CREATE OR REPLACE TABLE; minimal CLI for repeatability.

2) Transformation (SQLMesh)

• Staging views to standardize types/names and fix data quality (e.g., literal "NULL" timestamps).
• Dimensional layer with SCD Type 2 for customers and plans to preserve history.
• Transaction fact table for subscription change events.

3) Orchestration (cron)

• Hourly schedule on the fact model; local cron invokes sqlmesh run prod with logs.

Solutions

Dimensional Model

• Dimensions: dim.customers, dim.plans (SCD2 with valid from/to, is_current, updated_at); dim.dates calendar spine.
• Fact: fct.subscription_changes at grain one row per customer per subscription per change.

Key Metrics & Logic

• Change detection via window functions (e.g., previous plan, days on prior plan).
• Flags for upgrade/downgrade/cancellation; plan change amount and revenue impact.

Results

• Detected 13 plan transitions in sample data.
• Common transitions: Basic → Pro Annual; Basic → Premium Monthly; Premium → Basic.

Impact for Growth Team

• Near real-time visibility into upgrades/downgrades and churn windows.
• Historical truth of customer and plan attributes for pricing and segmentation decisions.
• Clear runway to plug into BI and lifecycle automation.

← Back to Home