SaaS & Subscription Analytics

Learn MRR tracking, cohort retention analysis, and churn prediction using sample SaaS data.

Time: 15-20 minutes Difficulty: Beginner-friendly

---

About This Tutorial

In this tutorial, you'll analyze a fictional SaaS business using industry-standard SaaS metrics. You'll learn to:

• Track Monthly Recurring Revenue (MRR) and growth
• Analyze customer retention with cohort analysis
• Score churn risk using AI Tables

No SQL knowledge required—just follow the prompts and see what Shadowfax creates.

---

Get the Sample Data

Download the Dataset

We've prepared realistic SaaS sample data for you:

Download SaaS Sample Data (placeholder link)

What's included:

• users.csv - 5,000 users with signup dates and account details
• subscriptions.csv - 8,000 subscription records (upgrades, downgrades, cancellations)
• usage_events.csv - 500,000 product usage events

Import the Data

1. Download and unzip the files
2. In your Shadowfax Workbook, click "Import Data"
3. Upload all three CSV files
4. Confirm they appear under "Sources" in your sidebar

Check the Schema

Click the Graph tab to see relationships:

---

Choose Your Path

Pick an analysis path based on what you want to learn. Each path is independent—do one, two, or all three!

Path A: MRR & Revenue Tracking

Goal: Calculate and track Monthly Recurring Revenue.

Path B: Cohort Retention Analysis

Goal: Understand how well you retain customers over time.

Path C: Churn Risk Scoring

Goal: Identify customers at risk of churning using AI.

---

Path A: MRR & Revenue Tracking

Goal: Calculate Monthly Recurring Revenue (MRR) and understand revenue growth.

Step 1: Calculate Current MRR

Prompt:

@[subscriptions]
Calculate current MRR (Monthly Recurring Revenue).
Only include active subscriptions.
For annual plans, divide by 12 to get monthly value.

What the AI creates:

• View name: current_mrr (Classification: Select Filter + Calculate Columns)
• Filters to status = 'active'
• Normalizes all plans to monthly values
• Sums total MRR

You might see:

Total MRR
$245,800

Step 2: MRR Over Time

Now let's see how MRR has grown month by month.

Prompt:

@[subscriptions]
Calculate MRR for each month historically.
Show new MRR, expansion MRR, contraction MRR, and churned MRR.

What the AI creates:

• View name: mrr_by_month (Classification: Aggregate + Calculate Columns)
• Groups subscriptions by month
• Categorizes MRR movements:
  • New MRR: From new customers
  • Expansion MRR: Upgrades
  • Contraction MRR: Downgrades
  • Churned MRR: Cancellations

Sample data:

month	new_mrr	expansion_mrr	contraction_mrr	churned_mrr	net_mrr
2024-01	$18,500	$3,200	-$1,400	-$5,100	$15,200
2024-02	$22,100	$4,800	-$900	-$4,200	$21,800
...	...	...	...	...	...

Step 3: Calculate MRR Growth Rate

Prompt:

@[mrr_by_month]
Add month-over-month MRR growth rate (%)

What the AI creates:

• View name: mrr_growth (Classification: Calculate Groupwise)
• Uses LAG window function to get previous month MRR
• Calculates growth percentage

Step 4: Visualize MRR Movement

Prompt:

/visualize @[mrr_by_month]
Create a waterfall chart showing MRR components:
- Start with previous month's MRR
- Show new, expansion, contraction, churned as steps
- End with current month's MRR
Use green for positive, red for negative movements

Step 5: MRR Trend Line

Prompt:

/visualize @[mrr_growth]
Create a line chart showing total MRR over time
with growth rate as a secondary Y-axis line

What You Learned

Filtering active records (status-based queries) Revenue normalization (annual → monthly) MRR categorization (new, expansion, contraction, churn) Period-over-period growth (LAG window function) Waterfall visualizations (component breakdowns)

Next: Try Path B: Cohort Retention!

---

Path B: Cohort Retention Analysis

Goal: Understand how well you retain customers by analyzing cohorts (groups who signed up in the same month).

What is Cohort Retention?

Cohort retention tracks what % of users from each signup month remain active over time. It answers:

• "Of users who signed up in January, how many are still active 3 months later?"
• "Which signup cohorts have the best retention?"

Step 1: Define User Cohorts

Prompt:

@[users]
Assign each user to a signup cohort (the month they signed up)

What the AI creates:

• View name: user_cohorts (Classification: Calculate Columns)
• Extracts month from signup_date
• Adds cohort_month column

Step 2: Determine Active Status by Month

Prompt:

@[user_cohorts] @[subscriptions]
For each user and each month, determine if they had an active subscription

What the AI creates:

• View name: user_activity_by_month (Classification: Combine)
• Joins users with subscriptions
• Creates a row for each user-month combination
• Flags whether subscription was active

Step 3: Calculate Cohort Retention Rates

Prompt:

@[user_activity_by_month]
Calculate retention rate for each cohort by month:
- Month 0: 100% (everyone is "retained" at signup)
- Month 1: % still active 1 month after signup
- Month 2: % still active 2 months after signup
- etc.

What the AI creates:

• View name: cohort_retention (Classification: Aggregate + Calculate Groupwise)
• Groups by cohort and months since signup
• Calculates retention percentage per cohort per period

Sample data:

cohort_month	month_0	month_1	month_2	month_3	month_6
2023-01	100%	87%	78%	72%	65%
2023-02	100%	91%	85%	80%	74%
2023-03	100%	89%	82%	76%	71%
...	...	...	...	...	...

Step 4: Visualize Retention Curves

Prompt:

/visualize @[cohort_retention]
Create a line chart with:
- X-axis: Months since signup
- Y-axis: Retention rate (%)
- One line per cohort (different colors)
- Highlight the most recent cohort

Insights you might discover:

• Month 1 retention improved from 87% → 91% (recent cohorts are stickier)
• Major drop-off happens between months 0-2
• After month 6, retention plateaus (loyal customer base)

Step 5: Retention Heatmap

Prompt:

/visualize @[cohort_retention]
Create a retention heatmap:
- Rows: cohort months
- Columns: months since signup
- Color intensity: retention rate (darker = better retention)

Bonus: Calculate Average Revenue Per User by Cohort

Prompt:

@[user_cohorts] @[subscriptions]
For each cohort, calculate average MRR per user over their lifetime

What the AI creates:

• View name: cohort_arpu (Classification: Combine + Aggregate)
• Joins cohorts with subscription revenue
• Calculates average revenue per user per cohort

Combined insight:

• 2023-02 cohort has both the highest retention (91% at month 1) AND highest ARPU ($47/user)
• This cohort came from a successful marketing campaign → replicate the strategy!

What You Learned

Cohort definition (grouping by time period) Time-based analysis (months since event) Retention calculations (percentage of original cohort) Multi-line visualizations (cohort curves) Heatmap visualizations (2D retention view) ARPU analysis (average revenue per user)

Next: Try Path C: Churn Risk Scoring!

---

Path C: Churn Risk Scoring

Goal: Identify customers at risk of churning using behavioral signals and AI-powered risk scoring.

Step 1: Gather Churn Signals

Prompt:

@[users] @[subscriptions] @[usage_events]
For each active user, calculate churn risk signals:
- Days since last login
- Usage trend (increasing/decreasing)
- Number of support tickets in last 30 days
- Payment failures in last 90 days
- Feature adoption score (% of key features used)

What the AI creates:

• View name: churn_signals (Classification: Combine + Aggregate + Calculate Columns)
• Joins all three Sources
• Calculates behavioral metrics per user

Sample data:

user_id	user_name	days_since_login	usage_trend	support_tickets	payment_failures	feature_adoption_pct
1001	Acme Corp	45	decreasing	3	1	35%
1002	TechStart Inc	2	increasing	0	0	82%
...	...	...	...	...	...	...

Step 2: Create Manual Risk Score

Prompt:

@[churn_signals]
Create a simple risk score (0-100) where:
- days_since_login > 30: +20 points
- usage_trend = 'decreasing': +25 points
- support_tickets > 2: +15 points
- payment_failures > 0: +30 points
- feature_adoption_pct < 40%: +20 points

Classify as: High Risk (70+), Medium Risk (40-69), Low Risk (<40)

What the AI creates:

• View name: churn_risk_manual (Classification: Calculate Columns)
• Uses CASE statements to calculate score
• Adds risk category

Step 3: Use AI Table for Intelligent Risk Assessment

Now let's use an AI Table to generate more nuanced risk assessments.

Prompt:

@[churn_signals]
Create an AI Table that analyzes each customer's churn risk.
For each user, generate:
- risk_level: 'High', 'Medium', or 'Low'
- risk_reasoning: explanation of why they're at this risk level
- recommended_action: specific retention strategy for this customer

What the AI creates:

• AI Table name: churn_risk_ai
• Input columns: all signals from churn_signals
• AI output columns: risk_level, risk_reasoning, recommended_action

Remember: AI columns will be NULL until you manually refresh the table!

Step 4: Refresh the AI Table

Click the Refresh button on the AI Table to process all rows.

After refresh, you'll see:

user_name	risk_level	risk_reasoning	recommended_action
Acme Corp	High	"Hasn't logged in for 45 days, decreasing usage trend, recent payment failure, and low feature adoption (35%). Multiple concerning signals."	"Immediate outreach: Schedule success manager call. Offer onboarding refresh session to improve feature adoption. Resolve payment issue."
TechStart Inc	Low	"Active user with increasing engagement, high feature adoption (82%), no support or payment issues. Strong health signals."	"Nurture: Share advanced features and use cases. Consider upsell opportunities."
...	...	...	...

Step 5: Analyze Risk Distribution

Prompt:

@[churn_risk_ai]
Summarize churn risk distribution:
- Count of users by risk level
- Total MRR at risk by level
- Percentage of customer base in each category

What the AI creates:

• View name: churn_risk_summary (Classification: Aggregate)
• Groups by risk level
• Calculates counts and MRR totals

Sample insights:

risk_level	user_count	mrr_at_risk	pct_of_base
High	87	$42,150	8.2%
Medium	234	$58,900	22.1%
Low	738	$144,750	69.7%

Insight: $42K in MRR is at high risk—prioritize outreach to these 87 customers!

Step 6: Visualize Risk Distribution

Prompt:

/visualize @[churn_risk_summary]
Create a bar chart showing MRR at risk by risk level.
Use red for High, orange for Medium, green for Low.

Bonus: Create High-Risk Customer Export

Prompt:

@[churn_risk_ai]
Create a view of high-risk customers with their recommended actions,
sorted by MRR descending.
This will be exported to the customer success team.

What the AI creates:

• View name: high_risk_customers_export (Classification: Select Filter + Present)
• Filters to risk_level = 'High'
• Sorts by MRR
• Includes contact info and recommended actions

You can now export this as CSV for your CS team!

What You Learned

Behavioral signals (combining multiple data sources) Rule-based scoring (weighted point systems) AI Tables for semantic analysis (risk reasoning, recommendations) AI Table refresh workflow Risk segmentation and analysis Actionable exports (presentation layer views)

---

Putting It All Together

You've now mastered three critical SaaS analytics workflows:

• MRR tracking → Understand your revenue health
• Cohort retention → Know how well you keep customers
• Churn prediction → Proactively save at-risk accounts

Create a SaaS Metrics Dashboard

Prompt:

Create a comprehensive SaaS metrics dashboard combining:
- Current MRR and growth rate
- Cohort retention chart
- Churn risk distribution
- Top high-risk customers to contact

The AI will help you build a multi-chart executive dashboard!

---

What's Next?

Explore More

Try these follow-up analyses:

Revenue Analysis:

• What's the Customer Acquisition Cost (CAC) payback period?
• How does LTV vary by pricing tier or customer segment?
• What's the revenue impact of different contract lengths (monthly vs annual)?

Retention Analysis:

• Which onboarding actions correlate with better retention?
• How does time-to-value affect month 1 retention?
• What features do retained vs churned users adopt differently?

Churn Prevention:

• Can we predict churn 60 days in advance?
• What interventions have successfully saved at-risk customers?
• How do win-back campaigns perform on churned users?

Apply to Your Data

Import your own SaaS data and:

1. Adapt the prompts to your schema and metrics
2. Add custom signals relevant to your product (e.g., API usage, collaboration invites)
3. Refine risk scoring based on your churn patterns

Learn More

• Prompt Engineering Guide - Advanced techniques
• Use Cases - More examples
• FAQ - Common questions

---

Quick Reference: Prompts Used

Goal	Prompt Pattern
Calculate MRR	@[subscriptions] Calculate MRR, only active, normalize to monthly
MRR components	@[subscriptions] Show new/expansion/contraction/churned MRR by month
Cohort definition	@[users] Assign to cohort (month they signed up)
Retention calculation	@[activity] Calculate % retained by cohort by month since signup
Behavioral signals	@[multiple tables] Calculate [list of metrics] per user
Risk scoring	@[signals] Create risk score where [rules], classify as High/Med/Low
AI risk assessment	@[signals] Create AI Table that generates risk_level, reasoning, actions

---

Congratulations! You've completed the SaaS analytics tutorial. Ready for another domain?

• E-commerce & Sales Analytics →
• Marketing Campaign Analytics →
• Finance & Operations Analytics →