Subscription Business Analytics Suite

This project is a self-contained subscription analytics suite designed to turn customer, subscription, and product usage data into a decision-ready KPI layer and an executive dashboard.

The model includes an MRR engine (new vs. churned revenue), logo churn tracking, ARPU, tenure analysis, and usage breakdowns to connect revenue outcomes to customer behavior.

It is built to be Excel/Google Sheets compatible and structured to scale from ad-hoc analysis into a repeatable monthly reporting workflow.

Subscription businesses need consistent, comparable KPI definitions over time. Most reporting breaks down because revenue and customer events are scattered across tables with inconsistent logic.

• MRR movements (new vs. churn) are not clearly separated
• Customer churn is measured inconsistently (logo vs. revenue churn)
• Tenure and retention insights require additional transformations
• Usage signals are not connected to commercial outcomes

This suite solves those issues by:

• Standardizing the monthly KPI layer (MRR, New MRR, Churn MRR, Net MRR)
• Building reusable customer and subscription “clean” tables
• Adding tenure bucketing to support cohort and retention narratives
• Including product usage logs to support behavioral segmentation

The workbook is organized as a simple analytics “stack”:

• Raw: Customers, Subscriptions, UsageLogs
• Clean: Customers_clean, Subscriptions_clean, UsageLogs_clean
• Logic: TenureMonths, TenureBucket, MRR_Engine
• Output: metrics, Dashboard

Core entities

Table

Purpose

Customers_clean

Customer master data: join month, plan, country, segment, cohort label

Subscriptions_clean

Subscription lifecycle: start/end months, monthly fee, status, active flag, data quality flag

UsageLogs_clean

Product usage events: usage month, feature used, actions performed

Scale (dataset)

• ~20,000 customers and ~20,000 subscriptions
• ~40,000 usage log rows (~997k actions recorded)
• Average customer tenure ~15.9 months (median 12)

The KPI layer is built around a monthly MRR engine that decomposes revenue into movement components instead of reporting a single line.

• MRR: recurring revenue for the month
• New MRR: revenue from new/starting subscriptions
• Churn MRR: revenue lost from ending subscriptions
• Net MRR: New MRR − Churn MRR
• ARPU: MRR / active customers
• Logo churn rate: churned customers / customers at risk

Lifecycle signal (from the dashboard)

• Peak month: 2022-09
• Avg monthly MRR decline (post-peak): ~−10,068
• Months from peak to ~zero: 42

Customer longevity is modeled explicitly by calculating month-level tenure and assigning each customer to a bucket.

• Tenure computed from start month → end month
• Bucketed into interpretable retention bands (e.g., 0–3, 4–6, 7–12, 12+ months)
• Designed to support cohort-style narratives and churn diagnostics

The suite includes product usage logs to support segmentation beyond billing events. Usage is aggregated by month and feature area to connect engagement to commercial outcomes.

• Feature usage tracking (API / Dashboard / Reports / Exports)
• Action-volume intensity via ActionsPerformed
• Enables “high-usage vs low-usage” segmentation for churn-risk hypotheses

This model supports typical subscription analytics questions such as:

• Is growth driven by acquisition, or is churn offsetting it?
• When did the business peak, and what was the decline profile after that?
• How does ARPU evolve over time alongside logo churn?
• What does tenure distribution look like, and where does churn concentrate?
• Do usage signals correlate with longer tenure or lower churn?

1. Expansion / contraction MRR not separated
   The KPI engine focuses on new and churned revenue; add-on and downgrade flows can be modeled as additional movement categories.
2. Usage is event-based, not account-scored
   A dedicated engagement score (rolling 30/90-day usage index) would improve risk segmentation.
3. Spreadsheet-native scaling limits
   For larger volumes, the same model structure ports cleanly to SQL/dbt + BI semantic layers.

Potential next steps

• Add expansion/contraction MRR and net revenue retention (NRR)
• Add cohort retention curves by join month and plan
• Introduce churn-risk scoring using usage intensity + tenure + plan
• Add segmentation cuts (country, segment, plan) across all KPI charts