RavenStack SaaS — Revenue & Churn Analysis (SQL + Power BI)

An end-to-end SQL Server and Power BI project analyzing SaaS revenue, customer churn, and subscription behavior.

The project focuses on metric definition and data quality before analysis. In particular, I checked how churn should be calculated, validated inconsistencies between account status and churn events, and avoided assigning churn drivers when the available data did not support them.

• Tools: Microsoft SQL Server (T-SQL) for the data pipeline and analysis; Power BI for the report.
• Data: RavenStack Synthetic SaaS Dataset by River @ Rivalytics (fully synthetic, MIT-like licence; credit retained).
• Scale: 500 accounts · 5,000 subscriptions · 24,979 usage events · 2,000 support tickets · 600 churn events · Jan 2023 – Dec 2024.

---

Headline findings

#	Finding	Number
1	Raw churn logs inflate customer loss by nearly 3×. The 600 churn entries cover just 352 distinct accounts and feature 61 reactivations. Based on current account status, logo churn is 22% (110/500), compared with the misleading ~70% obtained by counting accounts appearing in the churn-event log.	22.0%
2	Revenue is heavily concentrated at the top. While Enterprise accounts represent only ~34% of active subscriptions, they drive 74% of the $10.16M MRR total. Enterprise accounts represent ~34% of active subscriptions but contribute 74% of the $10.16M MRR, so account count alone does not reflect revenue exposure.	74%
3	Churn is not concentrated among low-value accounts. Lost accounts represent ~$291K of last-known MRR (~21%), with an average contract size that is nearly on par with retained accounts.	~21%
4	There were 529 upgrades and 218 downgrades, giving an upgrade-to-downgrade ratio of 2.43:1. — demonstrating a strong land-and-expand engine worth safeguarding.	2.43:1
5	Available operational metrics show little difference between churned and retained accounts.. Active and lost accounts share nearly identical profiles across support ticket volume, customer satisfaction ratings, and usage levels.	null result

Because the available variables do not clearly separate churned from retained accounts, additional behavioral and billing data would be needed to investigate churn drivers further. See Recommendations.

---

Business context

RavenStack is a synthetic B2B SaaS dataset containing account, subscription, product usage, support, and churn data from January 2023 to December 2024. The analysis focuses on revenue concentration, subscription changes, and customer churn.

Approach

1. Load and transform — Loaded CSV data into stg_ staging tables as text, then validated, cast, cleaned, and deduplicated the data before loading it into typed dbo tables. (SQL/01_schema.sql, SQL/02_load.sql)
2. Data quality checks — Checked nulls, duplicate keys, redundant fields, and inconsistencies between churn_flag and churn_events. Removed 21 duplicate keys and identified arr as redundant. (SQL/03_data_quality.sql)
3. Define metrics — Defined churn at the account level using current account status rather than churn-event counts. Revenue calculations exclude trials, and arr is treated as 12 × mrr. (SQL/04_analysis.sql)
4. Analyze churn factors, Compared churned and retained accounts across usage, support, satisfaction, industry, and acquisition channel.
5. Visualise in Power BI across three pages (executive summary, revenue & retention, churn drivers).

Data model

A normalized dbo schema — accounts is the parent; subscriptions, support_tickets, and churn_events reference it, and feature_usage references subscriptions. Built with a staging → transform pattern. Full diagram in docs/ERD.md.

                accounts
              /    |     \
  subscriptions  support_  churn_events
        |        tickets
   feature_usage

Each account has approximately 10 subscription records because plan changes create new subscription rows. Therefore, subscription records cannot be counted directly when calculating account-level churn.

---

Findings in detail

1. Clarifying the churn definition. Counting distinct accounts in the 600-row churn log produces a churn rate of about 70%. However, 277 of those accounts are currently active and 61 reactivation events are present. Using current account status gives 110 churned accounts out of 500, or 22%.

2. Revenue concentration. Active paid MRR totals $10.16M/month ($121.9M ARR). Enterprise (~$5,787 ARPA) holds ~74% of it, Pro ~19%, Basic ~7%. Losing one Enterprise account ≈ losing ten Basic accounts. Churn is not concentrated in small accounts either — lost accounts hold ~$291K of last-known MRR (~21%), closely matching the 22% logo-churn rate.

3. Expansion vs contraction. There were 529 upgrades and 218 downgrades, a 2.43:1 ratio and net difference of +311 plan changes.

4. Recorded churn reasons Churn reasons are strikingly flat — features 19%, support 17%, budget 17%, unknown 16%, competitor 15%, pricing 15% — no single dominant cause. Refund leakage totals ~$8,652 across 142 events.

5. Churned vs. retained comparison Churned and retained accounts show similar values for support tickets (3.9 vs. 4.0), satisfaction (~4.0 for both), and usage (52 vs. 49). Industry and acquisition channel also show no clear separation. Based on the available variables, no clear churn factor was identified.

Recommendations

1. Base reporting on account churn (22%) rather than event frequency. Standardize the definition of churn and distinctly track account reactivations.
2. Include revenue exposure in retention reporting. Enterprise accounts contribute 74% of MRR, so churn should be monitored by both account count and revenue at risk.
3. Track upgrade and downgrade behavior over time — he dataset contains 529 upgrades versus 218 downgrades, but additional analysis is needed to understand what precedes these plan changes.
4. Review the ~$8.7K in churn-related refunds to understand whether they follow expected refund policies and whether any patterns require further investigation.
5. Collect additional leading indicators for churn analysis, such as activation milestones, payment failures, and changes in product usage over time. The current variables do not clearly distinguish churned from retained customers.

Dashboard

Three report pages (Power BI/dashboard.pbix):

1. Executive summary — KPI cards (MRR, ARR, logo churn, accounts, Enterprise share), the "600 events vs 22% churn" callout, MRR by plan tier, and a point-in-time MRR trend.

2. Revenue & retention — expansion vs contraction, churn reason mix, refund leakage, and churn rate by signup cohort (with a right-censoring caveat).

3. Churn drivers — the churned-vs-retained comparison and churn rate by industry/channel, with a comparison of churned and retained accounts and the conclusion that no clear churn factor was identified.

How to run

Prerequisites: SQL Server 2019+ (or Express) with SSMS or Azure Data Studio; Power BI Desktop.

1. Run SQL/01_schema.sql — creates the database and typed dbo tables.
2. Edit the BULK INSERT paths in SQL/02_load.sql to your local /Data folder, then run it — stages, transforms, and verifies row counts.
3. Run SQL/03_data_quality.sql — confirms the data-quality checks.
4. Run SQL/04_analysis.sql — reproduces every number in this README.
5. Open Power BI/dashboard.pbix, point the source at your RavenStack database, and refresh.

Repository structure

SaaS Subscription & Churn Analytics/
├── Data/                     # source CSVs + dataset README (Rivalytics)
├── SQL/
│   ├── 01_schema.sql         # database + typed dbo tables (PK/FK)
│   ├── 02_load.sql           # staging -> transform -> verify
│   ├── 03_data_quality.sql   # profiling & DQ checks
│   └── 04_analysis.sql       # the business questions, answered
├── Power BI/
│   ├── dashboard.pbix        # 3-page report
│   ├── Executive Summary.png
│   ├── Revenue & Retention.png
│   └── Churn Drivers.png
├── docs/
│   └── ERD.md                # entity-relationship diagram
└── README.md

Method notes & caveats

• Synthetic data. This dataset is artificially generated, so relationships between churn and operational variables may not reflect real customer behavior. Results should therefore be treated as a demonstration of the analysis process rather than evidence about real SaaS customers.
• Cohort censoring. Later signup cohorts show lower churn partly because they have had less time to churn; the cohort chart is captioned accordingly.
• Count-based expansion. A full dollar-based Net Revenue Retention bridge is listed as future work; the log cleanly supports a count-based expansion signal.

Credit

Dataset: River @ Rivalytics — RavenStack Synthetic SaaS Dataset (credit required by the dataset licence).