SaaS Analytics Engineering

Executive summary

A dbt and BigQuery build that turns synthetic subscription events into reporting-ready SaaS metrics. It models 420 customers across 24 months and produces monthly recurring revenue, churn, and lifetime value with automated checks and a scheduled production run.

Business context

Subscription reporting is difficult when customer, plan, and billing events live at different levels of detail. The question was how to make revenue movement and customer value trustworthy enough for recurring commercial reporting.

Data and preparation

Python generated customers, subscriptions, and billing events with early churn, a short honeymoon period, contract fatigue, upgrades, downgrades, failed payments, and refunds. Raw fields were renamed and lightly cleaned in staging before any metric logic was applied.

Method

The models follow staging, intermediate, and marts layers. A window function in the subscription-period model derives each customer’s time on plan. That choice keeps the history logic inside the transformation flow rather than adding snapshot infrastructure that was outside the project scope.

Results, limits, and next step

The final marts separate new, expansion, contraction, and churned revenue by month, alongside a customer-level lifetime-value table. Not-null, uniqueness, accepted-value, and non-negative MRR tests protect the outputs. The next improvement would be snapshots for a fuller audit trail when source history changes.