Revenue Operations Analytics in SQL

Build a RevOps analytics pipeline with churn, CAC/LTV, and attribution models.

Introduction

30 Second Summary

Every subscription business lives and dies by three questions: why are customers leaving, how much does it cost to acquire new ones, and which marketing channels actually bring in profitable customers? Revenue Operations teams answer these questions with data every single day.

In this project, you will build a complete RevOps analytics pipeline for NovaCRM, a fictional B2B SaaS company. You will write T-SQL queries to analyze churn patterns, calculate unit economics, and model marketing attribution, then visualize everything in an interactive Power BI dashboard.

What You'll Build

A Revenue Operations analytics stack that loads synthetic customer data into SQL Server LocalDB, runs progressively complex analytical queries, and connects to Power BI Desktop for executive-ready visualization.

By the end of this project, you'll have:

  • A NovaCRM database with 4 normalized tables containing 500 customers and 2000+ events, powered by a Python data generator script.
  • T-SQL analytical queries covering cohort retention, churn rates by plan, CAC by channel, LTV calculations, and multi-touch attribution models using CTEs and window functions.
  • An interactive Power BI dashboard with retention heatmaps, CAC vs LTV charts, MRR trends, and attribution model comparisons connected live to your database.
  • Secret Mission: a Python-powered churn risk scoring model that writes predictions back to SQL Server and surfaces risk scores in your dashboard.

Are there any prerequisites?

You should be comfortable with basic SQL (SELECT, JOIN, GROUP BY) and Python fundamentals. You also need VS Code, SQL Server LocalDB (already running), Power BI Desktop, and Python installed on your Windows machine.

This project will teach you the advanced T-SQL patterns (CTEs, window functions, conditional aggregation) from scratch. No prior experience with those is required.

Set Up Your Environment

Before you can analyze churn patterns or calculate unit economics, you need data to work with. NovaCRM is a fictional B2B SaaS company with 500 customers, multiple subscription plans, and marketing touchpoints across six channels.

In this step, you will create the NovaCRM database schema and load 18 months of synthetic customer lifecycle data. This gives you a realistic foundation for the analytical queries you will write in later steps.

In this step, get ready to:
  • Confirm your LocalDB connection is active in VS Code.
  • Create the NovaCRM database with four normalized tables.
  • Run a Python data generator to populate the database with realistic SaaS metrics.
Confirm your LocalDB connection

Your MSSQL extension is already connected to SQL Server LocalDB. Before creating anything, confirm the connection is live.

  • In VS Code, click the SQL Server icon in the left sidebar (it looks like a server with a plug).
  • Expand (localdb)\MSSQLLocalDB in the connections panel.
  • You should see a green dot next to the server name, indicating the connection is active.

Don't see a green dot?

Right-click the connection and select Connect. If the connection fails, open a terminal and run sqllocaldb start MSSQLLocalDB to ensure the instance is running.

help me connect to LocalDB in VS Code

Create the NovaCRM database and schema

The analytics database needs four tables that model a SaaS customer lifecycle. Customers sign up, subscribe to plans, interact with marketing channels, and generate monthly revenue.

  • Create a new file called 01_create_database.sql in your project folder by pressing Ctrl+N in VS Code, then Ctrl+S to save it with that name.
  • Paste the following SQL into 01_create_database.sql:
-- Create NovaCRM database
CREATE DATABASE NovaCRM;
GO

USE NovaCRM;
GO

-- Customers table
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    company_name NVARCHAR(100) NOT NULL,
    industry NVARCHAR(50) NOT NULL,
    signup_date DATE NOT NULL,
    plan_tier NVARCHAR(20) NOT NULL,
    is_churned BIT NOT NULL DEFAULT 0,
    churn_date DATE NULL
);

-- Subscriptions table
CREATE TABLE subscriptions (
    subscription_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    event_type NVARCHAR(20) NOT NULL,
    event_date DATE NOT NULL,
    mrr_amount DECIMAL(10,2) NOT NULL,
    plan_tier NVARCHAR(20) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Marketing touchpoints table
CREATE TABLE marketing_touchpoints (
    touchpoint_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    channel NVARCHAR(50) NOT NULL,
    touchpoint_date DATE NOT NULL,
    cost DECIMAL(10,2) NOT NULL,
    is_conversion BIT NOT NULL DEFAULT 0,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Revenue events table
CREATE TABLE revenue_events (
    event_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    event_date DATE NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    event_type NVARCHAR(20) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
GO

What does this schema do?

  • customers stores each company's signup date, plan tier, and churn status.
  • subscriptions tracks lifecycle events (new signup, upgrade, downgrade, cancellation) with MRR at each change.
  • marketing_touchpoints records every marketing interaction before a customer converted, along with the channel and cost.
  • revenue_events logs each monthly payment as an individual event, enabling cumulative revenue calculations.
  • Foreign keys on customer_id link all tables back to the customers table, enforcing referential integrity.
  • Execute the SQL by pressing Ctrl+Shift+E.
  • When prompted to choose a connection, select (localdb)\MSSQLLocalDB.

You should see a Commands completed successfully message in the results panel at the bottom of VS Code.

  • In the SQL Server connections panel on the left, right-click (localdb)\MSSQLLocalDB and select Refresh.
  • Expand Databases and confirm you see NovaCRM listed.
  • Expand NovaCRM > Tables and confirm all four tables appear: customers, subscriptions, marketing_touchpoints, and revenue_events.

Seeing an error about the database already existing?

If you ran this script before, the database already exists. Run DROP DATABASE NovaCRM; first (press Ctrl+Shift+E to execute), then re-run the full creation script.

help me fix a CREATE DATABASE error in SQL Server

Populate the database with synthetic data

Empty tables are not useful for analytics. You will run a Python script that generates 500 customers with realistic SaaS behavior patterns spanning January 2023 through June 2024.

  • Create a new file called generate_data.py in the same project folder.
  • Paste the following Python script into generate_data.py:
import pyodbc
import pandas as pd
import numpy as np
from datetime import datetime, timedelta
import random

# Connect to LocalDB
conn_str = (
    r'DRIVER={ODBC Driver 17 for SQL Server};'
    r'SERVER=(localdb)\MSSQLLocalDB;'
    r'DATABASE=NovaCRM;'
    r'Trusted_Connection=yes;'
)

# --- Generate Customers ---
np.random.seed(42)
random.seed(42)

n_customers = 500
start_date = datetime(2023, 1, 1)
end_date = datetime(2024, 6, 30)

industries = ['Technology', 'Healthcare', 'Finance', 'Retail', 'Manufacturing', 'Education']
plans = ['Starter', 'Growth', 'Enterprise']
plan_weights = [0.4, 0.35, 0.25]
channels = ['Google Ads', 'LinkedIn', 'Content Marketing', 'Referral', 'Cold Outreach', 'Webinar']

customers = []
for i in range(1, n_customers + 1):
    signup_date = start_date + timedelta(days=random.randint(0, (end_date - start_date).days))
    plan = np.random.choice(plans, p=plan_weights)
    industry = random.choice(industries)
    
    # Churn probability varies by plan
    churn_probs = {'Starter': 0.35, 'Growth': 0.20, 'Enterprise': 0.10}
    is_churned = random.random() < churn_probs[plan]
    
    if is_churned:
        min_tenure = 30
        max_tenure = (end_date - signup_date).days
        if max_tenure > min_tenure:
            churn_date = signup_date + timedelta(days=random.randint(min_tenure, max_tenure))
        else:
            churn_date = None
            is_churned = False
    else:
        churn_date = None
    
    customers.append({
        'customer_id': i,
        'company_name': f'Company_{i:04d}',
        'industry': industry,
        'signup_date': signup_date,
        'plan_tier': plan,
        'is_churned': 1 if is_churned else 0,
        'churn_date': churn_date
    })

df_customers = pd.DataFrame(customers)

# --- Generate Subscriptions ---
subscriptions = []
sub_id = 1
for _, cust in df_customers.iterrows():
    mrr_base = {'Starter': 49, 'Growth': 149, 'Enterprise': 499}
    current_mrr = mrr_base[cust['plan_tier']] + random.randint(-10, 30)
    current_date = cust['signup_date']
    end = cust['churn_date'] if cust['is_churned'] else end_date
    
    subscriptions.append({
        'subscription_id': sub_id,
        'customer_id': cust['customer_id'],
        'event_type': 'new',
        'event_date': current_date,
        'mrr_amount': current_mrr,
        'plan_tier': cust['plan_tier']
    })
    sub_id += 1
    
    # Random plan changes
    months_active = max(1, (end - current_date).days // 30)
    for m in range(1, months_active):
        if random.random() < 0.05:  # 5% chance of change per month
            event_date = current_date + timedelta(days=m * 30 + random.randint(-5, 5))
            if event_date < end:
                change = random.choice(['upgrade', 'downgrade'])
                if change == 'upgrade':
                    current_mrr = int(current_mrr * 1.3)
                else:
                    current_mrr = int(current_mrr * 0.7)
                subscriptions.append({
                    'subscription_id': sub_id,
                    'customer_id': cust['customer_id'],
                    'event_type': change,
                    'event_date': event_date,
                    'mrr_amount': current_mrr,
                    'plan_tier': cust['plan_tier']
                })
                sub_id += 1
    
    if cust['is_churned'] and cust['churn_date']:
        subscriptions.append({
            'subscription_id': sub_id,
            'customer_id': cust['customer_id'],
            'event_type': 'cancellation',
            'event_date': cust['churn_date'],
            'mrr_amount': 0,
            'plan_tier': cust['plan_tier']
        })
        sub_id += 1

df_subscriptions = pd.DataFrame(subscriptions)

# --- Generate Marketing Touchpoints ---
touchpoints = []
tp_id = 1
channel_costs = {
    'Google Ads': (50, 200),
    'LinkedIn': (80, 300),
    'Content Marketing': (10, 50),
    'Referral': (0, 20),
    'Cold Outreach': (30, 100),
    'Webinar': (20, 80)
}

for _, cust in df_customers.iterrows():
    n_touches = random.randint(2, 7)
    days_before = sorted(random.sample(range(1, 90), min(n_touches, 89)), reverse=True)
    
    for days in days_before:
        tp_date = cust['signup_date'] - timedelta(days=days)
        channel = random.choice(channels)
        cost_range = channel_costs[channel]
        cost = round(random.uniform(cost_range[0], cost_range[1]), 2)
        
        touchpoints.append({
            'touchpoint_id': tp_id,
            'customer_id': cust['customer_id'],
            'channel': channel,
            'touchpoint_date': tp_date,
            'cost': cost,
            'is_conversion': 1 if days == min(days_before) else 0
        })
        tp_id += 1

df_touchpoints = pd.DataFrame(touchpoints)

# --- Generate Revenue Events ---
revenue_events = []
rev_id = 1
for _, cust in df_customers.iterrows():
    current_date = cust['signup_date']
    end = cust['churn_date'] if cust['is_churned'] else end_date
    mrr = mrr_base = {'Starter': 49, 'Growth': 149, 'Enterprise': 499}[cust['plan_tier']]
    
    while current_date < end:
        revenue_events.append({
            'event_id': rev_id,
            'customer_id': cust['customer_id'],
            'event_date': current_date,
            'amount': mrr + random.randint(-5, 15),
            'event_type': 'payment'
        })
        rev_id += 1
        current_date += timedelta(days=30)

df_revenue = pd.DataFrame(revenue_events)

# --- Load into SQL Server ---
conn = pyodbc.connect(conn_str)
cursor = conn.cursor()

# Insert customers
for _, row in df_customers.iterrows():
    cursor.execute("""
        INSERT INTO customers (customer_id, company_name, industry, signup_date, plan_tier, is_churned, churn_date)
        VALUES (?, ?, ?, ?, ?, ?, ?)
    """, row['customer_id'], row['company_name'], row['industry'],
       row['signup_date'], row['plan_tier'], row['is_churned'],
       row['churn_date'] if pd.notna(row['churn_date']) else None)

# Insert subscriptions
for _, row in df_subscriptions.iterrows():
    cursor.execute("""
        INSERT INTO subscriptions (subscription_id, customer_id, event_type, event_date, mrr_amount, plan_tier)
        VALUES (?, ?, ?, ?, ?, ?)
    """, row['subscription_id'], row['customer_id'], row['event_type'],
       row['event_date'], row['mrr_amount'], row['plan_tier'])

# Insert touchpoints
for _, row in df_touchpoints.iterrows():
    cursor.execute("""
        INSERT INTO marketing_touchpoints (touchpoint_id, customer_id, channel, touchpoint_date, cost, is_conversion)
        VALUES (?, ?, ?, ?, ?, ?)
    """, row['touchpoint_id'], row['customer_id'], row['channel'],
       row['touchpoint_date'], row['cost'], row['is_conversion'])

# Insert revenue events
for _, row in df_revenue.iterrows():
    cursor.execute("""
        INSERT INTO revenue_events (event_id, customer_id, event_date, amount, event_type)
        VALUES (?, ?, ?, ?, ?)
    """, row['event_id'], row['customer_id'], row['event_date'],
       row['amount'], row['event_type'])

conn.commit()
conn.close()
print(f"Data loaded: {len(df_customers)} customers, {len(df_subscriptions)} subscriptions, {len(df_touchpoints)} touchpoints, {len(df_revenue)} revenue events")

What does this script do?

This script generates and loads four sets of synthetic data into your NovaCRM database:

  • 500 customers spread across six industries and three plan tiers (Starter, Growth, Enterprise), with realistic churn rates that vary by plan.
  • Subscription events tracking signups, upgrades, downgrades, and cancellations over each customer's lifetime.
  • Marketing touchpoints recording 2-7 interactions per customer across channels like Google Ads, LinkedIn, and Referrals before they converted.
  • Revenue events logging monthly payments for every active customer from signup through churn or the end of the observation window.

The random seeds (np.random.seed(42)) ensure everyone generates the same data, making results comparable across learners.

  • Save generate_data.py.
  • Open a terminal in VS Code by pressing Ctrl+` (backtick key, usually above Tab).
  • Run the script by entering this command:
python generate_data.py

You should see output similar to:

Data loaded: 500 customers, 637 subscriptions, 2207 touchpoints, 4736 revenue events

What should I see?

Your exact subscription, touchpoint, and revenue event counts will match because of the fixed random seeds. The key confirmation is 500 customers and several thousand events across the other three tables.

  • Verify the data loaded by running a quick count query in VS Code.
  • Create a new SQL file or open 01_create_database.sql, clear it, and paste this query:
-- Quick row count check
USE NovaCRM;
SELECT
    (SELECT COUNT(*) FROM customers) AS customer_count,
    (SELECT COUNT(*) FROM subscriptions) AS subscription_count,
    (SELECT COUNT(*) FROM marketing_touchpoints) AS touchpoint_count,
    (SELECT COUNT(*) FROM revenue_events) AS revenue_count;
  • Execute the query by pressing Ctrl+Shift+E.
  • Confirm you see 500 in the customer_count column and non-zero values in all other columns.

Seeing zero rows or a connection error?

If the Python script failed, check that your ODBC Driver name matches what is installed. Open ODBC Data Source Administrator from your Windows search bar and look under the Drivers tab for the exact driver name. If you see ODBC Driver 18 for SQL Server instead of 17, update the connection string in generate_data.py to match.

help me troubleshoot the pyodbc connection to LocalDB

✔️ Awesome, I've got everything!

Great. Double check you have saved both 01_create_database.sql and generate_data.py.

ⓧ I'd like to double check the full code

Here is the complete SQL schema file:

-- Create NovaCRM database
CREATE DATABASE NovaCRM;
GO

USE NovaCRM;
GO

-- Customers table
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    company_name NVARCHAR(100) NOT NULL,
    industry NVARCHAR(50) NOT NULL,
    signup_date DATE NOT NULL,
    plan_tier NVARCHAR(20) NOT NULL,
    is_churned BIT NOT NULL DEFAULT 0,
    churn_date DATE NULL
);

-- Subscriptions table
CREATE TABLE subscriptions (
    subscription_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    event_type NVARCHAR(20) NOT NULL,
    event_date DATE NOT NULL,
    mrr_amount DECIMAL(10,2) NOT NULL,
    plan_tier NVARCHAR(20) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Marketing touchpoints table
CREATE TABLE marketing_touchpoints (
    touchpoint_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    channel NVARCHAR(50) NOT NULL,
    touchpoint_date DATE NOT NULL,
    cost DECIMAL(10,2) NOT NULL,
    is_conversion BIT NOT NULL DEFAULT 0,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Revenue events table
CREATE TABLE revenue_events (
    event_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    event_date DATE NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    event_type NVARCHAR(20) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
GO

And here is the complete data generator script:

import pyodbc
import pandas as pd
import numpy as np
from datetime import datetime, timedelta
import random

# Connect to LocalDB
conn_str = (
    r'DRIVER={ODBC Driver 17 for SQL Server};'
    r'SERVER=(localdb)\MSSQLLocalDB;'
    r'DATABASE=NovaCRM;'
    r'Trusted_Connection=yes;'
)

# --- Generate Customers ---
np.random.seed(42)
random.seed(42)

n_customers = 500
start_date = datetime(2023, 1, 1)
end_date = datetime(2024, 6, 30)

industries = ['Technology', 'Healthcare', 'Finance', 'Retail', 'Manufacturing', 'Education']
plans = ['Starter', 'Growth', 'Enterprise']
plan_weights = [0.4, 0.35, 0.25]
channels = ['Google Ads', 'LinkedIn', 'Content Marketing', 'Referral', 'Cold Outreach', 'Webinar']

customers = []
for i in range(1, n_customers + 1):
    signup_date = start_date + timedelta(days=random.randint(0, (end_date - start_date).days))
    plan = np.random.choice(plans, p=plan_weights)
    industry = random.choice(industries)
    
    # Churn probability varies by plan
    churn_probs = {'Starter': 0.35, 'Growth': 0.20, 'Enterprise': 0.10}
    is_churned = random.random() < churn_probs[plan]
    
    if is_churned:
        min_tenure = 30
        max_tenure = (end_date - signup_date).days
        if max_tenure > min_tenure:
            churn_date = signup_date + timedelta(days=random.randint(min_tenure, max_tenure))
        else:
            churn_date = None
            is_churned = False
    else:
        churn_date = None
    
    customers.append({
        'customer_id': i,
        'company_name': f'Company_{i:04d}',
        'industry': industry,
        'signup_date': signup_date,
        'plan_tier': plan,
        'is_churned': 1 if is_churned else 0,
        'churn_date': churn_date
    })

df_customers = pd.DataFrame(customers)

# --- Generate Subscriptions ---
subscriptions = []
sub_id = 1
for _, cust in df_customers.iterrows():
    mrr_base = {'Starter': 49, 'Growth': 149, 'Enterprise': 499}
    current_mrr = mrr_base[cust['plan_tier']] + random.randint(-10, 30)
    current_date = cust['signup_date']
    end = cust['churn_date'] if cust['is_churned'] else end_date
    
    subscriptions.append({
        'subscription_id': sub_id,
        'customer_id': cust['customer_id'],
        'event_type': 'new',
        'event_date': current_date,
        'mrr_amount': current_mrr,
        'plan_tier': cust['plan_tier']
    })
    sub_id += 1
    
    # Random plan changes
    months_active = max(1, (end - current_date).days // 30)
    for m in range(1, months_active):
        if random.random() < 0.05:  # 5% chance of change per month
            event_date = current_date + timedelta(days=m * 30 + random.randint(-5, 5))
            if event_date < end:
                change = random.choice(['upgrade', 'downgrade'])
                if change == 'upgrade':
                    current_mrr = int(current_mrr * 1.3)
                else:
                    current_mrr = int(current_mrr * 0.7)
                subscriptions.append({
                    'subscription_id': sub_id,
                    'customer_id': cust['customer_id'],
                    'event_type': change,
                    'event_date': event_date,
                    'mrr_amount': current_mrr,
                    'plan_tier': cust['plan_tier']
                })
                sub_id += 1
    
    if cust['is_churned'] and cust['churn_date']:
        subscriptions.append({
            'subscription_id': sub_id,
            'customer_id': cust['customer_id'],
            'event_type': 'cancellation',
            'event_date': cust['churn_date'],
            'mrr_amount': 0,
            'plan_tier': cust['plan_tier']
        })
        sub_id += 1

df_subscriptions = pd.DataFrame(subscriptions)

# --- Generate Marketing Touchpoints ---
touchpoints = []
tp_id = 1
channel_costs = {
    'Google Ads': (50, 200),
    'LinkedIn': (80, 300),
    'Content Marketing': (10, 50),
    'Referral': (0, 20),
    'Cold Outreach': (30, 100),
    'Webinar': (20, 80)
}

for _, cust in df_customers.iterrows():
    n_touches = random.randint(2, 7)
    days_before = sorted(random.sample(range(1, 90), min(n_touches, 89)), reverse=True)
    
    for days in days_before:
        tp_date = cust['signup_date'] - timedelta(days=days)
        channel = random.choice(channels)
        cost_range = channel_costs[channel]
        cost = round(random.uniform(cost_range[0], cost_range[1]), 2)
        
        touchpoints.append({
            'touchpoint_id': tp_id,
            'customer_id': cust['customer_id'],
            'channel': channel,
            'touchpoint_date': tp_date,
            'cost': cost,
            'is_conversion': 1 if days == min(days_before) else 0
        })
        tp_id += 1

df_touchpoints = pd.DataFrame(touchpoints)

# --- Generate Revenue Events ---
revenue_events = []
rev_id = 1
for _, cust in df_customers.iterrows():
    current_date = cust['signup_date']
    end = cust['churn_date'] if cust['is_churned'] else end_date
    mrr = mrr_base = {'Starter': 49, 'Growth': 149, 'Enterprise': 499}[cust['plan_tier']]
    
    while current_date < end:
        revenue_events.append({
            'event_id': rev_id,
            'customer_id': cust['customer_id'],
            'event_date': current_date,
            'amount': mrr + random.randint(-5, 15),
            'event_type': 'payment'
        })
        rev_id += 1
        current_date += timedelta(days=30)

df_revenue = pd.DataFrame(revenue_events)

# --- Load into SQL Server ---
conn = pyodbc.connect(conn_str)
cursor = conn.cursor()

# Insert customers
for _, row in df_customers.iterrows():
    cursor.execute("""
        INSERT INTO customers (customer_id, company_name, industry, signup_date, plan_tier, is_churned, churn_date)
        VALUES (?, ?, ?, ?, ?, ?, ?)
    """, row['customer_id'], row['company_name'], row['industry'],
       row['signup_date'], row['plan_tier'], row['is_churned'],
       row['churn_date'] if pd.notna(row['churn_date']) else None)

# Insert subscriptions
for _, row in df_subscriptions.iterrows():
    cursor.execute("""
        INSERT INTO subscriptions (subscription_id, customer_id, event_type, event_date, mrr_amount, plan_tier)
        VALUES (?, ?, ?, ?, ?, ?)
    """, row['subscription_id'], row['customer_id'], row['event_type'],
       row['event_date'], row['mrr_amount'], row['plan_tier'])

# Insert touchpoints
for _, row in df_touchpoints.iterrows():
    cursor.execute("""
        INSERT INTO marketing_touchpoints (touchpoint_id, customer_id, channel, touchpoint_date, cost, is_conversion)
        VALUES (?, ?, ?, ?, ?, ?)
    """, row['touchpoint_id'], row['customer_id'], row['channel'],
       row['touchpoint_date'], row['cost'], row['is_conversion'])

# Insert revenue events
for _, row in df_revenue.iterrows():
    cursor.execute("""
        INSERT INTO revenue_events (event_id, customer_id, event_date, amount, event_type)
        VALUES (?, ?, ?, ?, ?)
    """, row['event_id'], row['customer_id'], row['event_date'],
       row['amount'], row['event_type'])

conn.commit()
conn.close()
print(f"Data loaded: {len(df_customers)} customers, {len(df_subscriptions)} subscriptions, {len(df_touchpoints)} touchpoints, {len(df_revenue)} revenue events")

Your NovaCRM database is loaded and ready for analysis. Next up, you will write your first analytical query to uncover churn patterns across customer cohorts.

Analyze Churn with Cohort Queries

Your NovaCRM database is loaded with 500 customers and 18 months of subscription data. Now you can start asking the questions that RevOps teams care about most: which customer cohorts are retaining, and where is churn accelerating?

In this step, you will write T-SQL queries that group customers into monthly signup cohorts and measure how many survive over time. You will use CTEs (Common Table Expressions) to layer logic cleanly, window functions to compare months, and conditional aggregation to count retained customers at specific intervals.

In this step, get ready to:
  • Write a CTE-based cohort retention query that calculates survival rates at month 1, 3, 6, and 12.
  • Add a LAG window function to detect churn acceleration by plan tier.
  • Create a reusable SQL view that Power BI can query directly for dashboard visuals.
Write the cohort retention query

Cohort analysis groups customers by the month they signed up, then tracks what percentage are still active at fixed intervals. This is the foundation of every SaaS retention report.

  • In VS Code, create a new file called 02_churn_cohort_analysis.sql in the same folder as your other project files.
  • Paste the following cohort retention query into the file:
USE NovaCRM;
GO

-- Monthly cohort retention analysis
WITH cohorts AS (
    SELECT
        customer_id,
        DATEFROMPARTS(YEAR(signup_date), MONTH(signup_date), 1) AS cohort_month,
        plan_tier,
        signup_date,
        churn_date,
        is_churned
    FROM customers
),
retention AS (
    SELECT
        c.cohort_month,
        c.plan_tier,
        COUNT(*) AS cohort_size,
        COUNT(CASE WHEN DATEDIFF(MONTH, c.signup_date, ISNULL(c.churn_date, '2024-06-30')) >= 1 THEN 1 END) AS retained_m1,
        COUNT(CASE WHEN DATEDIFF(MONTH, c.signup_date, ISNULL(c.churn_date, '2024-06-30')) >= 3 THEN 1 END) AS retained_m3,
        COUNT(CASE WHEN DATEDIFF(MONTH, c.signup_date, ISNULL(c.churn_date, '2024-06-30')) >= 6 THEN 1 END) AS retained_m6,
        COUNT(CASE WHEN DATEDIFF(MONTH, c.signup_date, ISNULL(c.churn_date, '2024-06-30')) >= 12 THEN 1 END) AS retained_m12
    FROM cohorts c
    GROUP BY c.cohort_month, c.plan_tier
)
SELECT
    cohort_month,
    plan_tier,
    cohort_size,
    CAST(retained_m1 AS FLOAT) / cohort_size * 100 AS retention_pct_m1,
    CAST(retained_m3 AS FLOAT) / cohort_size * 100 AS retention_pct_m3,
    CAST(retained_m6 AS FLOAT) / cohort_size * 100 AS retention_pct_m6,
    CAST(retained_m12 AS FLOAT) / cohort_size * 100 AS retention_pct_m12
FROM retention
ORDER BY cohort_month, plan_tier;
GO

What does this query do?

This query uses two chained CTEs to build up logic in layers:

  • The cohorts CTE assigns each customer to a monthly signup cohort using DATEFROMPARTS to normalize signup dates to the first of the month.
  • The retention CTE uses COUNT(CASE WHEN...) (conditional aggregation) to check if each customer survived at least 1, 3, 6, or 12 months. ISNULL(churn_date, '2024-06-30') treats active customers as if they survived through the end of the data window.
  • The final SELECT divides retained counts by cohort size to produce retention percentages.
  • Save 02_churn_cohort_analysis.sql.
  • Highlight the entire query (from USE NovaCRM down through the GO after the final SELECT).
  • Press Ctrl+Shift+E to execute the selected query against your LocalDB connection.
  • You should see a results grid in the bottom panel with columns cohort_month, plan_tier, cohort_size, and four retention percentage columns. Enterprise plans should show noticeably higher retention than Starter plans.

Seeing an error when you execute?

  • Make sure you are connected to the NovaCRM database in VS Code. Check the connection status bar at the bottom of the editor. It should show (localdb)\MSSQLLocalDB and NovaCRM.
  • If you see "Invalid object name 'customers'", your connection may be pointing at the master database. Click the connection in the status bar and switch to NovaCRM.

Still stuck? Ask for help.

Detect churn acceleration with LAG

Knowing overall retention is useful, but spotting when churn is speeding up is what triggers action. A sudden spike in month-over-month churn for a specific plan tier tells RevOps teams to investigate immediately.

  • Add the following churn acceleration query below the first GO statement in 02_churn_cohort_analysis.sql:
-- Churn acceleration using LAG
WITH monthly_churn AS (
    SELECT
        DATEFROMPARTS(YEAR(churn_date), MONTH(churn_date), 1) AS churn_month,
        plan_tier,
        COUNT(*) AS churned_count
    FROM customers
    WHERE is_churned = 1 AND churn_date IS NOT NULL
    GROUP BY DATEFROMPARTS(YEAR(churn_date), MONTH(churn_date), 1), plan_tier
)
SELECT
    churn_month,
    plan_tier,
    churned_count,
    LAG(churned_count) OVER (PARTITION BY plan_tier ORDER BY churn_month) AS prev_month_churn,
    churned_count - LAG(churned_count) OVER (PARTITION BY plan_tier ORDER BY churn_month) AS churn_acceleration
FROM monthly_churn
ORDER BY plan_tier, churn_month;
GO

What does LAG do here?

The LAG window function looks at the previous row within each plan tier's time series. By subtracting last month's churn count from this month's, you get the churn_acceleration value.

A positive number means churn is increasing. A negative number means churn is slowing down. This is the signal that triggers intervention from RevOps.

  • Save 02_churn_cohort_analysis.sql.
  • Highlight just the churn acceleration query (from the -- Churn acceleration comment through its GO) and press Ctrl+Shift+E.
  • You should see results with columns churn_month, plan_tier, churned_count, prev_month_churn, and churn_acceleration. The first row for each plan tier will show NULL for prev_month_churn because there is no prior month to compare against.

All values showing NULL?

If every row shows NULL for prev_month_churn, check that your PARTITION BY clause says plan_tier and your ORDER BY says churn_month. Without both, LAG cannot find the correct previous row.

Help me debug my LAG window function.

Create the Power BI view

Your ad-hoc queries are great for exploration, but Power BI needs a stable, predictable data source. A SQL view acts like a saved query that Power BI can read from directly without you pasting SQL into the BI tool.

  • Add the following view creation query at the bottom of 02_churn_cohort_analysis.sql, after the last GO:
-- Create view for Power BI
CREATE OR ALTER VIEW vw_cohort_retention AS
WITH cohorts AS (
    SELECT
        customer_id,
        DATEFROMPARTS(YEAR(signup_date), MONTH(signup_date), 1) AS cohort_month,
        plan_tier,
        signup_date,
        churn_date,
        is_churned
    FROM customers
),
months AS (
    SELECT DISTINCT DATEFROMPARTS(YEAR(signup_date), MONTH(signup_date), 1) AS cohort_month
    FROM customers
),
periods AS (
    SELECT value AS months_since_signup
    FROM GENERATE_SERIES(0, 18)
)
SELECT
    c.cohort_month,
    p.months_since_signup,
    c.plan_tier,
    COUNT(*) AS cohort_size,
    COUNT(CASE
        WHEN DATEDIFF(MONTH, c.signup_date, ISNULL(c.churn_date, '2024-06-30')) >= p.months_since_signup
        THEN 1
    END) AS retained_count,
    CAST(COUNT(CASE
        WHEN DATEDIFF(MONTH, c.signup_date, ISNULL(c.churn_date, '2024-06-30')) >= p.months_since_signup
        THEN 1
    END) AS FLOAT) / COUNT(*) * 100 AS retention_pct
FROM cohorts c
CROSS JOIN periods p
GROUP BY c.cohort_month, p.months_since_signup, c.plan_tier;
GO

What does this view do differently?

Unlike the first query that checks specific fixed intervals (month 1, 3, 6, 12), this view calculates retention for every month from 0 to 18. The GENERATE_SERIES(0, 18) function creates rows numbered 0 through 18, and the CROSS JOIN pairs every cohort with every period.

This gives Power BI a continuous retention curve to plot as a heatmap, instead of four disconnected data points.

GENERATE_SERIES not recognized?

The GENERATE_SERIES function requires SQL Server 2022 or newer. If your LocalDB version is older, replace the periods CTE with a VALUES list instead:

periods AS (
    SELECT value AS months_since_signup
    FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18)) AS t(value)
)

This produces the same result and works on all SQL Server versions.

  • Save 02_churn_cohort_analysis.sql.
  • Highlight the CREATE OR ALTER VIEW block (from the -- Create view for Power BI comment through the final GO) and press Ctrl+Shift+E.
  • You should see "Commands completed successfully" in the messages panel. This means the view was created.
  • Verify the view works by highlighting and executing this quick test query:
SELECT TOP 10 * FROM vw_cohort_retention ORDER BY cohort_month, months_since_signup;
  • You should see 10 rows with columns cohort_month, months_since_signup, plan_tier, cohort_size, retained_count, and retention_pct. The retention_pct value at months_since_signup = 0 should be 100 for every cohort.

View created but SELECT returns an error?

  • If you see "Invalid object name 'vw_cohort_retention'", the view creation may have failed silently. Re-run the CREATE OR ALTER VIEW block and check for errors in the messages panel.
  • If you see an error about GENERATE_SERIES, use the VALUES-based replacement shown in the callout above.

Help me fix my vw_cohort_retention view.

✔️ Awesome, I've got everything!

Great. Double check you have saved 02_churn_cohort_analysis.sql and all three query sections executed successfully.

ⓧ I'd like to double check the full code

Here is the complete 02_churn_cohort_analysis.sql file for comparison:

USE NovaCRM;
GO

-- Monthly cohort retention analysis
WITH cohorts AS (
    SELECT
        customer_id,
        DATEFROMPARTS(YEAR(signup_date), MONTH(signup_date), 1) AS cohort_month,
        plan_tier,
        signup_date,
        churn_date,
        is_churned
    FROM customers
),
retention AS (
    SELECT
        c.cohort_month,
        c.plan_tier,
        COUNT(*) AS cohort_size,
        COUNT(CASE WHEN DATEDIFF(MONTH, c.signup_date, ISNULL(c.churn_date, '2024-06-30')) >= 1 THEN 1 END) AS retained_m1,
        COUNT(CASE WHEN DATEDIFF(MONTH, c.signup_date, ISNULL(c.churn_date, '2024-06-30')) >= 3 THEN 1 END) AS retained_m3,
        COUNT(CASE WHEN DATEDIFF(MONTH, c.signup_date, ISNULL(c.churn_date, '2024-06-30')) >= 6 THEN 1 END) AS retained_m6,
        COUNT(CASE WHEN DATEDIFF(MONTH, c.signup_date, ISNULL(c.churn_date, '2024-06-30')) >= 12 THEN 1 END) AS retained_m12
    FROM cohorts c
    GROUP BY c.cohort_month, c.plan_tier
)
SELECT
    cohort_month,
    plan_tier,
    cohort_size,
    CAST(retained_m1 AS FLOAT) / cohort_size * 100 AS retention_pct_m1,
    CAST(retained_m3 AS FLOAT) / cohort_size * 100 AS retention_pct_m3,
    CAST(retained_m6 AS FLOAT) / cohort_size * 100 AS retention_pct_m6,
    CAST(retained_m12 AS FLOAT) / cohort_size * 100 AS retention_pct_m12
FROM retention
ORDER BY cohort_month, plan_tier;
GO

-- Churn acceleration using LAG
WITH monthly_churn AS (
    SELECT
        DATEFROMPARTS(YEAR(churn_date), MONTH(churn_date), 1) AS churn_month,
        plan_tier,
        COUNT(*) AS churned_count
    FROM customers
    WHERE is_churned = 1 AND churn_date IS NOT NULL
    GROUP BY DATEFROMPARTS(YEAR(churn_date), MONTH(churn_date), 1), plan_tier
)
SELECT
    churn_month,
    plan_tier,
    churned_count,
    LAG(churned_count) OVER (PARTITION BY plan_tier ORDER BY churn_month) AS prev_month_churn,
    churned_count - LAG(churned_count) OVER (PARTITION BY plan_tier ORDER BY churn_month) AS churn_acceleration
FROM monthly_churn
ORDER BY plan_tier, churn_month;
GO

-- Create view for Power BI
CREATE OR ALTER VIEW vw_cohort_retention AS
WITH cohorts AS (
    SELECT
        customer_id,
        DATEFROMPARTS(YEAR(signup_date), MONTH(signup_date), 1) AS cohort_month,
        plan_tier,
        signup_date,
        churn_date,
        is_churned
    FROM customers
),
months AS (
    SELECT DISTINCT DATEFROMPARTS(YEAR(signup_date), MONTH(signup_date), 1) AS cohort_month
    FROM customers
),
periods AS (
    SELECT value AS months_since_signup
    FROM GENERATE_SERIES(0, 18)
)
SELECT
    c.cohort_month,
    p.months_since_signup,
    c.plan_tier,
    COUNT(*) AS cohort_size,
    COUNT(CASE
        WHEN DATEDIFF(MONTH, c.signup_date, ISNULL(c.churn_date, '2024-06-30')) >= p.months_since_signup
        THEN 1
    END) AS retained_count,
    CAST(COUNT(CASE
        WHEN DATEDIFF(MONTH, c.signup_date, ISNULL(c.churn_date, '2024-06-30')) >= p.months_since_signup
        THEN 1
    END) AS FLOAT) / COUNT(*) * 100 AS retention_pct
FROM cohorts c
CROSS JOIN periods p
GROUP BY c.cohort_month, p.months_since_signup, c.plan_tier;
GO

Why CTEs instead of subqueries?

CTEs let you name each logical layer of your query and reference it by name in the next layer. Subqueries would produce the same results, but the SQL becomes much harder to read and debug as complexity grows.

In RevOps, queries get reviewed by teammates who need to understand the logic quickly. CTEs make that possible.

You now have a working cohort retention analysis and a Power BI-ready view. Next, you will calculate the unit economics that tell you whether each marketing channel is worth the money: Customer Acquisition Cost and Lifetime Value.

Calculate CAC and LTV

Your churn cohort analysis revealed which customer groups leave fastest. Now you need to answer the next critical question: is the money you spend acquiring customers actually worth it?

In this step, you will calculate Customer Acquisition Cost (CAC) and Lifetime Value (LTV) for each marketing channel. You will then combine them into a ratio that flags which channels are healthy investments and which are burning cash.

In this step, get ready to:
  • Calculate CAC by channel using a CTE that joins marketing spend to customer signups.
  • Compute customer LTV by plan tier using aggregated revenue and active months.
  • Create a view that calculates the CAC:LTV ratio and flags unhealthy channels.
Calculate CAC by channel

CAC answers a simple question: how much does it cost to win one customer through a given channel? You calculate it by dividing total marketing spend by the number of customers acquired.

  • Create a new file called 03_cac_ltv_modeling.sql in your project folder.
  • Add the following query that joins marketing_touchpoints to customers and calculates cost per acquisition by channel and month:
USE NovaCRM;
GO

-- CAC by channel
WITH channel_spend AS (
    SELECT
        mt.channel,
        DATEFROMPARTS(YEAR(c.signup_date), MONTH(c.signup_date), 1) AS acquisition_month,
        SUM(mt.cost) AS total_spend,
        COUNT(DISTINCT c.customer_id) AS customers_acquired
    FROM marketing_touchpoints mt
    INNER JOIN customers c ON mt.customer_id = c.customer_id
    GROUP BY mt.channel, DATEFROMPARTS(YEAR(c.signup_date), MONTH(c.signup_date), 1)
)
SELECT
    channel,
    acquisition_month,
    total_spend,
    customers_acquired,
    CASE
        WHEN customers_acquired > 0 THEN total_spend / customers_acquired
        ELSE 0
    END AS cac
FROM channel_spend
ORDER BY channel, acquisition_month;
GO

What does this query do?

The channel_spend CTE groups all marketing touchpoints by channel and the month the associated customer signed up. It sums the cost and counts unique customers acquired.

The outer SELECT divides total spend by customers acquired to produce the CAC. The CASE expression prevents division by zero for months where a channel had spend but no conversions.

  • Save 03_cac_ltv_modeling.sql.
  • Execute the query by pressing Ctrl+Shift+E. You should see a results grid with rows for each channel-month combination, showing the CAC value in the rightmost column.

Seeing zero rows or an error?

Make sure your first line is USE NovaCRM; so the query runs against the correct database. If you see a syntax error, check that each GO batch separator sits on its own line.

Help me debug my CAC query

What is CAC?

Customer Acquisition Cost tells you how much you spent in marketing to gain each new paying customer. A channel with high CAC might still be worthwhile if those customers stay and spend a lot. That is exactly what LTV tells you next.

Compute customer Lifetime Value

LTV measures the total revenue a customer generates over their entire relationship with your company. Comparing LTV against CAC reveals whether each channel produces customers that pay back their acquisition cost.

  • Add the following query below the GO at the end of your CAC query in 03_cac_ltv_modeling.sql:
-- Customer LTV with cumulative revenue
WITH customer_ltv AS (
    SELECT
        c.customer_id,
        c.plan_tier,
        c.signup_date,
        DATEFROMPARTS(YEAR(c.signup_date), MONTH(c.signup_date), 1) AS cohort_month,
        SUM(re.amount) AS total_revenue,
        COUNT(re.event_id) AS payment_count,
        DATEDIFF(MONTH, c.signup_date, ISNULL(c.churn_date, '2024-06-30')) AS months_active
    FROM customers c
    LEFT JOIN revenue_events re ON c.customer_id = re.customer_id
    GROUP BY c.customer_id, c.plan_tier, c.signup_date, c.churn_date
)
SELECT
    plan_tier,
    COUNT(*) AS customer_count,
    AVG(total_revenue) AS avg_ltv,
    AVG(months_active) AS avg_lifetime_months,
    AVG(CASE WHEN months_active > 0 THEN total_revenue / months_active ELSE 0 END) AS avg_monthly_value
FROM customer_ltv
GROUP BY plan_tier
ORDER BY avg_ltv DESC;
GO

What does this query do?

The customer_ltv CTE calculates each customer's total revenue, payment count, and active months. It uses ISNULL(c.churn_date, '2024-06-30') to treat customers who have not churned as still active through the end of the data period.

The outer query averages LTV by plan tier. Enterprise customers should show significantly higher LTV than Starter customers, reflecting both higher MRR and longer retention.

  • Save 03_cac_ltv_modeling.sql.
  • Execute the LTV query by highlighting it and pressing Ctrl+Shift+E. You should see three rows (one per plan tier) with Enterprise at the top showing the highest avg_ltv value.

All rows showing NULL for avg_ltv?

Check that your revenue_events table has data by running SELECT COUNT(*) FROM revenue_events. If it returns zero, re-run your generate_data.py script from Step 1.

Help me troubleshoot my LTV query

Create the unit economics view with health flags

The standard SaaS benchmark is a 3:1 LTV-to-CAC ratio. Anything below that means you are spending too much to acquire customers relative to the value they generate.

  • Add the following view creation below the GO at the end of your LTV query in 03_cac_ltv_modeling.sql:
-- CAC:LTV Ratio by channel with health flag
CREATE OR ALTER VIEW vw_unit_economics AS
WITH cac_by_channel AS (
    SELECT
        mt.channel,
        SUM(mt.cost) AS total_spend,
        COUNT(DISTINCT mt.customer_id) AS customers_acquired,
        CASE
            WHEN COUNT(DISTINCT mt.customer_id) > 0
            THEN SUM(mt.cost) / COUNT(DISTINCT mt.customer_id)
            ELSE 0
        END AS cac
    FROM marketing_touchpoints mt
    GROUP BY mt.channel
),
ltv_by_channel AS (
    SELECT
        mt.channel,
        AVG(cltv.total_revenue) AS avg_ltv
    FROM (
        SELECT
            c.customer_id,
            SUM(re.amount) AS total_revenue
        FROM customers c
        INNER JOIN revenue_events re ON c.customer_id = re.customer_id
        GROUP BY c.customer_id
    ) cltv
    INNER JOIN marketing_touchpoints mt ON cltv.customer_id = mt.customer_id
    GROUP BY mt.channel
)
SELECT
    cac.channel,
    cac.total_spend,
    cac.customers_acquired,
    cac.cac,
    ltv.avg_ltv,
    CASE WHEN cac.cac > 0 THEN ltv.avg_ltv / cac.cac ELSE 0 END AS ltv_cac_ratio,
    CASE
        WHEN cac.cac > 0 AND ltv.avg_ltv / cac.cac >= 3 THEN 'Healthy'
        WHEN cac.cac > 0 AND ltv.avg_ltv / cac.cac >= 1 THEN 'Warning'
        ELSE 'Unhealthy'
    END AS health_status
FROM cac_by_channel cac
LEFT JOIN ltv_by_channel ltv ON cac.channel = ltv.channel;
GO

What does this view do?

The view combines two CTEs. The cac_by_channel CTE calculates how much each channel costs per customer acquired. The ltv_by_channel CTE uses a subquery to sum each customer's total revenue, then averages it per channel.

The final SELECT divides LTV by CAC to produce the ratio. The health_status column flags channels as Healthy (ratio 3 or above), Warning (ratio between 1 and 3), or Unhealthy (below 1).

  • Save 03_cac_ltv_modeling.sql.
  • Highlight the CREATE OR ALTER VIEW block and execute it with Ctrl+Shift+E. You should see a "Commands completed successfully" message.
  • Verify the view works by running:
SELECT * FROM vw_unit_economics;

You should see one row per channel with columns for total spend, customers acquired, CAC, average LTV, the LTV:CAC ratio, and a health status label.

View creation failed?

If you see an error about "only one statement allowed", make sure you highlighted only the view creation block (from CREATE OR ALTER VIEW to the final GO) and not the entire file. View definitions in T-SQL must be the only statement in their batch.

Help me fix my vw_unit_economics view

Why 3:1 as the benchmark?

The 3:1 LTV:CAC ratio is the widely accepted SaaS health benchmark. It means a customer generates three times more revenue than it cost to acquire them. Below 3:1, the business is spending too aggressively on growth. Above 5:1 may indicate underinvestment in marketing.

✔️ Awesome, I've got everything!

Great. Double check you have saved 03_cac_ltv_modeling.sql and confirmed the vw_unit_economics view returns results.

ⓧ I'd like to double check the full code

USE NovaCRM;
GO

-- CAC by channel
WITH channel_spend AS (
    SELECT
        mt.channel,
        DATEFROMPARTS(YEAR(c.signup_date), MONTH(c.signup_date), 1) AS acquisition_month,
        SUM(mt.cost) AS total_spend,
        COUNT(DISTINCT c.customer_id) AS customers_acquired
    FROM marketing_touchpoints mt
    INNER JOIN customers c ON mt.customer_id = c.customer_id
    GROUP BY mt.channel, DATEFROMPARTS(YEAR(c.signup_date), MONTH(c.signup_date), 1)
)
SELECT
    channel,
    acquisition_month,
    total_spend,
    customers_acquired,
    CASE
        WHEN customers_acquired > 0 THEN total_spend / customers_acquired
        ELSE 0
    END AS cac
FROM channel_spend
ORDER BY channel, acquisition_month;
GO

-- Customer LTV with cumulative revenue
WITH customer_ltv AS (
    SELECT
        c.customer_id,
        c.plan_tier,
        c.signup_date,
        DATEFROMPARTS(YEAR(c.signup_date), MONTH(c.signup_date), 1) AS cohort_month,
        SUM(re.amount) AS total_revenue,
        COUNT(re.event_id) AS payment_count,
        DATEDIFF(MONTH, c.signup_date, ISNULL(c.churn_date, '2024-06-30')) AS months_active
    FROM customers c
    LEFT JOIN revenue_events re ON c.customer_id = re.customer_id
    GROUP BY c.customer_id, c.plan_tier, c.signup_date, c.churn_date
)
SELECT
    plan_tier,
    COUNT(*) AS customer_count,
    AVG(total_revenue) AS avg_ltv,
    AVG(months_active) AS avg_lifetime_months,
    AVG(CASE WHEN months_active > 0 THEN total_revenue / months_active ELSE 0 END) AS avg_monthly_value
FROM customer_ltv
GROUP BY plan_tier
ORDER BY avg_ltv DESC;
GO

-- CAC:LTV Ratio by channel with health flag
CREATE OR ALTER VIEW vw_unit_economics AS
WITH cac_by_channel AS (
    SELECT
        mt.channel,
        SUM(mt.cost) AS total_spend,
        COUNT(DISTINCT mt.customer_id) AS customers_acquired,
        CASE
            WHEN COUNT(DISTINCT mt.customer_id) > 0
            THEN SUM(mt.cost) / COUNT(DISTINCT mt.customer_id)
            ELSE 0
        END AS cac
    FROM marketing_touchpoints mt
    GROUP BY mt.channel
),
ltv_by_channel AS (
    SELECT
        mt.channel,
        AVG(cltv.total_revenue) AS avg_ltv
    FROM (
        SELECT
            c.customer_id,
            SUM(re.amount) AS total_revenue
        FROM customers c
        INNER JOIN revenue_events re ON c.customer_id = re.customer_id
        GROUP BY c.customer_id
    ) cltv
    INNER JOIN marketing_touchpoints mt ON cltv.customer_id = mt.customer_id
    GROUP BY mt.channel
)
SELECT
    cac.channel,
    cac.total_spend,
    cac.customers_acquired,
    cac.cac,
    ltv.avg_ltv,
    CASE WHEN cac.cac > 0 THEN ltv.avg_ltv / cac.cac ELSE 0 END AS ltv_cac_ratio,
    CASE
        WHEN cac.cac > 0 AND ltv.avg_ltv / cac.cac >= 3 THEN 'Healthy'
        WHEN cac.cac > 0 AND ltv.avg_ltv / cac.cac >= 1 THEN 'Warning'
        ELSE 'Unhealthy'
    END AS health_status
FROM cac_by_channel cac
LEFT JOIN ltv_by_channel ltv ON cac.channel = ltv.channel;
GO

You now have a complete picture of NovaCRM's unit economics. Next up, you will build multi-touch attribution models to understand which marketing channels deserve credit for driving conversions.

Build Multi-Touch Attribution Models

Your CAC and LTV calculations reveal which channels are efficient at acquiring customers. But they treat each channel as if it works in isolation.

In reality, a customer might discover NovaCRM through a webinar, click a Google Ad two weeks later, and finally convert after reading a blog post. Multi-touch attribution solves the problem of deciding which touchpoints deserve credit for that conversion. In this step, you will build three different attribution models in pure T-SQL and compare how each one redistributes revenue credit across channels.

In this step, get ready to:
  • Build first-touch and last-touch attribution using ROW_NUMBER() window functions.
  • Implement a linear (equal-weight) attribution model that splits credit evenly.
  • Create a time-decay model and combine all four models into a single view for Power BI.
Build first-touch and last-touch attribution

The simplest attribution models give 100% of the credit to a single touchpoint. First-touch credits the channel that introduced the customer. Last-touch credits the channel immediately before conversion.

  • Create a new file called 04_attribution_analysis.sql in the same folder as your previous SQL files.
  • Add the first-touch and last-touch attribution query by pasting this code:
USE NovaCRM;
GO

-- First-touch and last-touch attribution
WITH ordered_touches AS (
    SELECT
        mt.customer_id,
        mt.channel,
        mt.touchpoint_date,
        mt.cost,
        c.signup_date,
        ROW_NUMBER() OVER (PARTITION BY mt.customer_id ORDER BY mt.touchpoint_date ASC) AS touch_rank_asc,
        ROW_NUMBER() OVER (PARTITION BY mt.customer_id ORDER BY mt.touchpoint_date DESC) AS touch_rank_desc
    FROM marketing_touchpoints mt
    INNER JOIN customers c ON mt.customer_id = c.customer_id
),
customer_revenue AS (
    SELECT
        customer_id,
        SUM(amount) AS total_revenue
    FROM revenue_events
    GROUP BY customer_id
)
SELECT
    'First Touch' AS model,
    ot.channel,
    COUNT(DISTINCT ot.customer_id) AS attributed_customers,
    SUM(cr.total_revenue) AS attributed_revenue
FROM ordered_touches ot
INNER JOIN customer_revenue cr ON ot.customer_id = cr.customer_id
WHERE ot.touch_rank_asc = 1
GROUP BY ot.channel

UNION ALL

SELECT
    'Last Touch' AS model,
    ot.channel,
    COUNT(DISTINCT ot.customer_id) AS attributed_customers,
    SUM(cr.total_revenue) AS attributed_revenue
FROM ordered_touches ot
INNER JOIN customer_revenue cr ON ot.customer_id = cr.customer_id
WHERE ot.touch_rank_desc = 1
GROUP BY ot.channel
ORDER BY model, attributed_revenue DESC;
GO

What does this code do?

The ordered_touches CTE uses two ROW_NUMBER() window functions to rank each customer's touchpoints. touch_rank_asc = 1 is the first touchpoint. touch_rank_desc = 1 is the last.

The UNION ALL combines both model results into a single result set so you can compare them side by side. Each model gives 100% of a customer's total revenue to whichever channel touched them first (or last).

  • Highlight the entire query and press Ctrl+Shift+E to execute it against your LocalDB connection.
  • You should see a results grid with rows for each channel, split between First Touch and Last Touch models, showing attributed customers and revenue per channel.

No results or connection error?

Make sure your MSSQL connection in VS Code is pointing to the NovaCRM database. Click the connection indicator in the bottom status bar and verify the database name.

If you see a syntax error, check that GO appears on its own line after the query. The MSSQL extension uses GO as a batch separator.

Help me debug my attribution query

Why multiple attribution models?

First-touch attribution favors awareness channels (like Content Marketing) that introduce new leads. Last-touch favors conversion channels (like Cold Outreach) that close deals. Neither tells the full story.

Building multiple models lets you see how revenue credit shifts depending on assumptions. This is the core of RevOps attribution analysis.

Implement linear attribution

Linear attribution takes a more democratic approach. Every touchpoint in a customer's journey receives equal credit for the conversion.

  • Add the linear attribution query below the first GO statement in 04_attribution_analysis.sql:
-- Linear attribution (equal weight)
WITH touch_counts AS (
    SELECT
        customer_id,
        COUNT(*) AS total_touches
    FROM marketing_touchpoints
    GROUP BY customer_id
),
linear_attribution AS (
    SELECT
        mt.customer_id,
        mt.channel,
        1.0 / tc.total_touches AS attribution_weight
    FROM marketing_touchpoints mt
    INNER JOIN touch_counts tc ON mt.customer_id = tc.customer_id
),
customer_revenue AS (
    SELECT
        customer_id,
        SUM(amount) AS total_revenue
    FROM revenue_events
    GROUP BY customer_id
)
SELECT
    la.channel,
    SUM(la.attribution_weight) AS weighted_conversions,
    SUM(la.attribution_weight * cr.total_revenue) AS attributed_revenue
FROM linear_attribution la
INNER JOIN customer_revenue cr ON la.customer_id = cr.customer_id
GROUP BY la.channel
ORDER BY attributed_revenue DESC;
GO

What does this code do?

The touch_counts CTE counts how many touchpoints each customer has. The linear_attribution CTE then assigns each touchpoint a weight of 1.0 / total_touches.

If a customer had 4 touchpoints, each one gets 25% of the credit. The final SELECT multiplies each weight by the customer's total revenue so you get fractional revenue attributed per channel.

  • Highlight just the linear attribution query and press Ctrl+Shift+E to execute it.
  • You should see one row per channel with weighted_conversions (fractional customer counts) and attributed_revenue columns.

Seeing NULL values in the results?

If attributed_revenue is NULL for some channels, it means those customers have touchpoints but no matching revenue events. The INNER JOIN on customer_revenue filters those out. Verify your data loaded correctly by running SELECT COUNT(*) FROM revenue_events.

Help me debug NULL values in my linear attribution query

Create the time-decay model and combined view

Time-decay attribution gives more credit to touchpoints closer to the conversion date. A touchpoint one week before signup matters more than one two months before.

This final query also wraps all four models (First Touch, Last Touch, Linear, and Time Decay) into a single view that Power BI can consume directly.

  • Add the time-decay attribution view below the linear attribution query in 04_attribution_analysis.sql:
-- Time-decay attribution
CREATE OR ALTER VIEW vw_attribution_comparison AS
WITH customer_revenue AS (
    SELECT customer_id, SUM(amount) AS total_revenue
    FROM revenue_events
    GROUP BY customer_id
),
touch_with_decay AS (
    SELECT
        mt.customer_id,
        mt.channel,
        mt.touchpoint_date,
        c.signup_date,
        DATEDIFF(DAY, mt.touchpoint_date, c.signup_date) AS days_before_conversion,
        -- Time decay: more recent = higher weight
        POWER(0.5, CAST(DATEDIFF(DAY, mt.touchpoint_date, c.signup_date) AS FLOAT) / 7.0) AS decay_weight
    FROM marketing_touchpoints mt
    INNER JOIN customers c ON mt.customer_id = c.customer_id
),
normalized_decay AS (
    SELECT
        customer_id,
        channel,
        decay_weight / SUM(decay_weight) OVER (PARTITION BY customer_id) AS normalized_weight
    FROM touch_with_decay
),
-- First touch
first_touch AS (
    SELECT customer_id, channel,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY touchpoint_date ASC) AS rn
    FROM marketing_touchpoints
),
-- Last touch
last_touch AS (
    SELECT customer_id, channel,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY touchpoint_date DESC) AS rn
    FROM marketing_touchpoints
),
-- Linear
touch_counts AS (
    SELECT customer_id, COUNT(*) AS total_touches
    FROM marketing_touchpoints
    GROUP BY customer_id
)
SELECT 'First Touch' AS attribution_model, ft.channel,
    SUM(cr.total_revenue) AS attributed_revenue,
    COUNT(DISTINCT ft.customer_id) AS attributed_customers
FROM first_touch ft
INNER JOIN customer_revenue cr ON ft.customer_id = cr.customer_id
WHERE ft.rn = 1
GROUP BY ft.channel

UNION ALL

SELECT 'Last Touch', lt.channel,
    SUM(cr.total_revenue),
    COUNT(DISTINCT lt.customer_id)
FROM last_touch lt
INNER JOIN customer_revenue cr ON lt.customer_id = cr.customer_id
WHERE lt.rn = 1
GROUP BY lt.channel

UNION ALL

SELECT 'Linear', mt.channel,
    SUM(cr.total_revenue * (1.0 / tc.total_touches)),
    CAST(SUM(1.0 / tc.total_touches) AS INT)
FROM marketing_touchpoints mt
INNER JOIN customer_revenue cr ON mt.customer_id = cr.customer_id
INNER JOIN touch_counts tc ON mt.customer_id = tc.customer_id
GROUP BY mt.channel

UNION ALL

SELECT 'Time Decay', nd.channel,
    SUM(cr.total_revenue * nd.normalized_weight),
    CAST(SUM(nd.normalized_weight) AS INT)
FROM normalized_decay nd
INNER JOIN customer_revenue cr ON nd.customer_id = cr.customer_id
GROUP BY nd.channel;
GO

What does this code do?

The time-decay formula POWER(0.5, days_before_conversion / 7.0) halves the weight for every 7 days between a touchpoint and the signup date. A touch 1 day before conversion gets nearly full weight. A touch 60 days before gets almost nothing.

The normalized_decay CTE divides each weight by the sum of all weights for that customer. This ensures each customer's total attribution adds up to 100%.

The view combines all four models with UNION ALL so Power BI can filter by attribution_model to show any model or compare them all.

  • Highlight the CREATE OR ALTER VIEW statement and press Ctrl+Shift+E to create the view.
  • Verify the view works by running this query:
SELECT * FROM vw_attribution_comparison ORDER BY attribution_model, attributed_revenue DESC;
  • You should see rows for all four models (First Touch, Last Touch, Linear, Time Decay) with revenue and customer counts broken down by channel.

View creation failed?

If you see an error about ORDER BY not being allowed in views, make sure there is no ORDER BY inside the view definition. The ORDER BY belongs only in your SELECT query against the view, not inside the view itself.

If you see a permissions error, confirm you are connected with Windows Authentication and that the active database is NovaCRM.

Help me fix my vw_attribution_comparison view

  • Save 04_attribution_analysis.sql.

✔️ Awesome, I've got everything!

Great. Double check you have saved 04_attribution_analysis.sql and that the vw_attribution_comparison view returns results.

ⓧ I'd like to double check the full code

Here is the complete 04_attribution_analysis.sql file:

USE NovaCRM;
GO

-- First-touch and last-touch attribution
WITH ordered_touches AS (
    SELECT
        mt.customer_id,
        mt.channel,
        mt.touchpoint_date,
        mt.cost,
        c.signup_date,
        ROW_NUMBER() OVER (PARTITION BY mt.customer_id ORDER BY mt.touchpoint_date ASC) AS touch_rank_asc,
        ROW_NUMBER() OVER (PARTITION BY mt.customer_id ORDER BY mt.touchpoint_date DESC) AS touch_rank_desc
    FROM marketing_touchpoints mt
    INNER JOIN customers c ON mt.customer_id = c.customer_id
),
customer_revenue AS (
    SELECT
        customer_id,
        SUM(amount) AS total_revenue
    FROM revenue_events
    GROUP BY customer_id
)
SELECT
    'First Touch' AS model,
    ot.channel,
    COUNT(DISTINCT ot.customer_id) AS attributed_customers,
    SUM(cr.total_revenue) AS attributed_revenue
FROM ordered_touches ot
INNER JOIN customer_revenue cr ON ot.customer_id = cr.customer_id
WHERE ot.touch_rank_asc = 1
GROUP BY ot.channel

UNION ALL

SELECT
    'Last Touch' AS model,
    ot.channel,
    COUNT(DISTINCT ot.customer_id) AS attributed_customers,
    SUM(cr.total_revenue) AS attributed_revenue
FROM ordered_touches ot
INNER JOIN customer_revenue cr ON ot.customer_id = cr.customer_id
WHERE ot.touch_rank_desc = 1
GROUP BY ot.channel
ORDER BY model, attributed_revenue DESC;
GO

-- Linear attribution (equal weight)
WITH touch_counts AS (
    SELECT
        customer_id,
        COUNT(*) AS total_touches
    FROM marketing_touchpoints
    GROUP BY customer_id
),
linear_attribution AS (
    SELECT
        mt.customer_id,
        mt.channel,
        1.0 / tc.total_touches AS attribution_weight
    FROM marketing_touchpoints mt
    INNER JOIN touch_counts tc ON mt.customer_id = tc.customer_id
),
customer_revenue AS (
    SELECT
        customer_id,
        SUM(amount) AS total_revenue
    FROM revenue_events
    GROUP BY customer_id
)
SELECT
    la.channel,
    SUM(la.attribution_weight) AS weighted_conversions,
    SUM(la.attribution_weight * cr.total_revenue) AS attributed_revenue
FROM linear_attribution la
INNER JOIN customer_revenue cr ON la.customer_id = cr.customer_id
GROUP BY la.channel
ORDER BY attributed_revenue DESC;
GO

-- Time-decay attribution
CREATE OR ALTER VIEW vw_attribution_comparison AS
WITH customer_revenue AS (
    SELECT customer_id, SUM(amount) AS total_revenue
    FROM revenue_events
    GROUP BY customer_id
),
touch_with_decay AS (
    SELECT
        mt.customer_id,
        mt.channel,
        mt.touchpoint_date,
        c.signup_date,
        DATEDIFF(DAY, mt.touchpoint_date, c.signup_date) AS days_before_conversion,
        -- Time decay: more recent = higher weight
        POWER(0.5, CAST(DATEDIFF(DAY, mt.touchpoint_date, c.signup_date) AS FLOAT) / 7.0) AS decay_weight
    FROM marketing_touchpoints mt
    INNER JOIN customers c ON mt.customer_id = c.customer_id
),
normalized_decay AS (
    SELECT
        customer_id,
        channel,
        decay_weight / SUM(decay_weight) OVER (PARTITION BY customer_id) AS normalized_weight
    FROM touch_with_decay
),
-- First touch
first_touch AS (
    SELECT customer_id, channel,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY touchpoint_date ASC) AS rn
    FROM marketing_touchpoints
),
-- Last touch
last_touch AS (
    SELECT customer_id, channel,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY touchpoint_date DESC) AS rn
    FROM marketing_touchpoints
),
-- Linear
touch_counts AS (
    SELECT customer_id, COUNT(*) AS total_touches
    FROM marketing_touchpoints
    GROUP BY customer_id
)
SELECT 'First Touch' AS attribution_model, ft.channel,
    SUM(cr.total_revenue) AS attributed_revenue,
    COUNT(DISTINCT ft.customer_id) AS attributed_customers
FROM first_touch ft
INNER JOIN customer_revenue cr ON ft.customer_id = cr.customer_id
WHERE ft.rn = 1
GROUP BY ft.channel

UNION ALL

SELECT 'Last Touch', lt.channel,
    SUM(cr.total_revenue),
    COUNT(DISTINCT lt.customer_id)
FROM last_touch lt
INNER JOIN customer_revenue cr ON lt.customer_id = cr.customer_id
WHERE lt.rn = 1
GROUP BY lt.channel

UNION ALL

SELECT 'Linear', mt.channel,
    SUM(cr.total_revenue * (1.0 / tc.total_touches)),
    CAST(SUM(1.0 / tc.total_touches) AS INT)
FROM marketing_touchpoints mt
INNER JOIN customer_revenue cr ON mt.customer_id = cr.customer_id
INNER JOIN touch_counts tc ON mt.customer_id = tc.customer_id
GROUP BY mt.channel

UNION ALL

SELECT 'Time Decay', nd.channel,
    SUM(cr.total_revenue * nd.normalized_weight),
    CAST(SUM(nd.normalized_weight) AS INT)
FROM normalized_decay nd
INNER JOIN customer_revenue cr ON nd.customer_id = cr.customer_id
GROUP BY nd.channel;
GO

You now have three analytical views ready for visualization: cohort retention, unit economics, and attribution comparison. Next up, you will connect Power BI Desktop to your LocalDB and build an interactive dashboard that brings all of these queries to life.

Build Your Power BI Dashboard

Your SQL queries are powerful, but executives don't read CTEs. They read dashboards.

In this step, you will connect Power BI Desktop directly to your LocalDB instance and build an interactive executive dashboard. The views you created in previous steps become the data sources for retention heatmaps, unit economics charts, and attribution comparisons.

In this step, get ready to:
  • Connect Power BI Desktop to your NovaCRM database on LocalDB.
  • Build 4 dashboard visuals: cohort retention matrix, CAC vs LTV bar chart, MRR trend line, and attribution comparison.
  • Add interactivity with slicers, conditional formatting, and cross-filtering.
Connect Power BI to your NovaCRM database

Power BI Desktop has a native SQL Server connector that works directly with LocalDB. No extra drivers or custom connectors needed.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Power BI Desktop and press Enter to open it.
  • Close any splash screen or sign-in prompts that appear on startup.
  • Click Get Data in the Home ribbon at the top.
  • Select Database in the left panel, then choose SQL Server database.
  • Click Connect.

A SQL Server database dialog appears asking for connection details.

  • In the Server field, enter (localdb)\MSSQLLocalDB.
  • In the Database (optional) field, enter NovaCRM.
  • Leave the Data Connectivity mode as Import.
  • Click OK.

Why Import mode?

Import mode copies data into Power BI's in-memory engine, making your visuals snappy and responsive. Since your dataset is only 500 customers, the import takes seconds and gives you the best interactive experience while building.

Power BI uses Windows Authentication automatically since that is how your LocalDB is configured. A Navigator window appears showing available tables and views.

  • Check the boxes next to these 7 items:
  • customers.
  • subscriptions.
  • marketing_touchpoints.
  • revenue_events.
  • vw_cohort_retention.
  • vw_unit_economics.
  • vw_attribution_comparison.
  • Click Load.

Power BI imports the data. You should see a loading progress bar, then the Fields pane on the right populates with all 7 data sources.

Connection failed or no tables showing?

Make sure your LocalDB instance is running. Open a terminal and run sqllocaldb info MSSQLLocalDB to check. If the state says "Stopped", run sqllocaldb start MSSQLLocalDB to restart it.

Double check the server name uses a backslash, not a forward slash. The correct value is (localdb)\MSSQLLocalDB.

Why can't Power BI connect to my LocalDB?

Build the cohort retention heatmap

Your first visual transforms the cohort retention view into a matrix that shows how each monthly cohort retains over time. RevOps teams use this to spot which signup months produced the stickiest customers.

  • Click the blank canvas area to deselect any existing visuals.
  • In the Visualizations pane on the right, click the Matrix icon (a grid of cells).
  • From the Fields pane, expand vw_cohort_retention.
  • Drag cohort_month to the Rows well.
  • Drag months_since_signup to the Columns well.
  • Drag retention_pct to the Values well.

Power BI may default to summing retention_pct. You need to change the aggregation to average.

  • Click the dropdown arrow next to retention_pct in the Values well.
  • Select Average.
  • Resize the matrix visual to fill roughly the top-left quarter of the canvas.

You should see a grid with cohort months as rows and months since signup (0 through 18) as columns. The values show average retention percentages.

What makes this a heatmap?

Right now it is just a number grid. You will add conditional formatting (color scales) in the interactivity substep to turn it into a proper heatmap where darker colors mean higher retention.

Build the CAC vs LTV bar chart
  • Click a blank area of the canvas to deselect the matrix.
  • In the Visualizations pane, click the Clustered bar chart icon.
  • Expand vw_unit_economics in the Fields pane.
  • Drag channel to the Y-axis well.
  • Drag cac to the X-axis well.
  • Drag avg_ltv to the X-axis well as a second measure.
  • Resize the visual to fill the top-right quarter of the canvas.

You should see a clustered bar chart with each marketing channel on the Y-axis and two bars per channel showing CAC and average LTV side by side.

Build the MRR trend line chart
  • Click a blank area of the canvas.
  • In the Visualizations pane, click the Line chart icon.
  • Expand subscriptions in the Fields pane.
  • Drag event_date to the X-axis well.
  • Drag mrr_amount to the Y-axis well.

Power BI may create a date hierarchy (Year, Quarter, Month, Day). You want to see the monthly trend instead.

  • Click the dropdown arrow next to event_date in the X-axis well.
  • Select event_date (the plain date, not the hierarchy) to show a continuous time axis.
  • Click the dropdown arrow next to mrr_amount in the Y-axis well and select Sum.
  • Resize the visual to fill the bottom-left quarter of the canvas.

You should see a line chart showing MRR over time from January 2023 through June 2024.

Build the attribution model comparison chart
  • Click a blank area of the canvas.
  • In the Visualizations pane, click the Grouped bar chart icon (also called Clustered column chart).
  • Expand vw_attribution_comparison in the Fields pane.
  • Drag channel to the X-axis well.
  • Drag attributed_revenue to the Y-axis well.
  • Drag attribution_model to the Legend well.
  • Resize the visual to fill the bottom-right quarter of the canvas.

You should see grouped columns for each channel, with 4 colored bars per channel representing First Touch, Last Touch, Linear, and Time Decay attribution models.

Why compare attribution models side-by-side?

Different attribution models tell different stories about which channels drive revenue. First-touch credits the channel that introduced a customer. Last-touch credits the final interaction before conversion. Linear and time-decay spread credit more evenly.

Showing all four together lets stakeholders see where models agree (strong signal) and where they diverge (needs further investigation).

  • Save your report by pressing Ctrl+S.
  • Name the file NovaCRM_RevOps_Dashboard and save it to your project folder.

Visuals showing blank or unexpected values?

Check that the aggregation is correct for each measure. Click the dropdown on each field in the Values/Y-axis well and confirm: retention_pct should be Average, mrr_amount should be Sum, and attributed_revenue should be Sum.

If a view shows zero rows, go back to VS Code and run a quick SELECT TOP 5 * FROM vw_cohort_retention to confirm the view returns data.

Why is my Power BI visual showing blank or zero values?

Add interactivity with slicers, conditional formatting, and cross-filtering

Static charts are fine for a PDF report. An interactive dashboard lets stakeholders filter, drill, and explore data on their own. You will add three layers of interactivity to your report.

  • Click a blank area at the top of the canvas (above your visuals) to make room for a slicer.
  • In the Visualizations pane, click the Slicer icon.
  • Expand customers in the Fields pane.
  • Drag plan_tier to the Field well of the slicer.
  • Resize the slicer so it sits horizontally across the top of your dashboard.

You should see buttons or a list showing Starter, Growth, and Enterprise. Clicking any plan tier filters all visuals on the page to show only that tier's data.

What is a slicer?

A slicer is Power BI's interactive filter control. When a user clicks a slicer value, every visual on the page that shares a relationship with that field updates automatically. It is the simplest way to make a dashboard explorable without building separate pages for each segment.

Next, add conditional formatting to make unhealthy CAC:LTV ratios visually obvious.

  • Click on your CAC vs LTV clustered bar chart to select it.
  • In the Visualizations pane, click the Format visual tab (the paint roller icon).
  • Expand Data colors (or Bars depending on your Power BI version).
  • Click the fx (conditional formatting) button next to the color for the cac series.
  • Set Format style to Rules.
  • Add a rule: if the value is greater than the average LTV, set the color to red.
  • Click OK.

Use the health_status field as an alternative

Your vw_unit_economics view already has a health_status column with values Healthy, Warning, and Unhealthy. You can also use this field in the Legend well and manually set Unhealthy to red, Warning to yellow, and Healthy to green for a cleaner approach.

Finally, configure cross-filtering so clicking a channel in one chart filters all the others.

  • Click on your CAC vs LTV bar chart to select it.
  • In the ribbon, click Format (the top menu tab, not the paint roller).
  • Click Edit interactions.
  • On each of the other three visuals, you will see filter icons appear. Make sure the Filter icon (funnel) is selected on each, not the "None" icon.
  • Click Edit interactions again to exit the mode.

What is cross-filtering?

Cross-filtering means clicking a data point in one visual automatically filters the other visuals on the page. For example, clicking "Google Ads" in your attribution chart will filter the MRR trend to show only revenue from Google Ads customers.

Power BI enables cross-filtering by default for most visuals. The Edit interactions mode lets you override this on a per-visual basis if needed.

  • Test your interactivity by clicking Starter in the plan tier slicer.
  • Verify that all four visuals update to show Starter-only data.
  • Click Starter again to deselect and restore all data.
  • Save your report with Ctrl+S.

Your dashboard is now fully interactive. Stakeholders can slice by plan tier, spot unhealthy channels in red, and click any data point to explore relationships across all four visuals.

Slicer not filtering all visuals?

The slicer uses the plan_tier field from the customers table. For it to filter views like vw_cohort_retention, Power BI needs a relationship between the tables. Go to Model view (the icon in the left sidebar) and verify that relationships exist between customers and your views/tables.

If no relationship exists, you can alternatively drag the plan_tier field from the view itself (e.g., from vw_cohort_retention) into the slicer instead.

Why isn't my slicer filtering all visuals in Power BI?

Your RevOps analytics stack is complete. You have SQL queries powering real-time views, and a Power BI dashboard that makes the data explorable for anyone in the organization. Next up, the Secret Mission challenges you to add predictive churn scoring with Python and surface it directly in this dashboard.

Secret mission

Build a Python-Powered Churn Risk Scoring Model

Your SQL queries revealed which cohorts churn fastest and which channels acquire the stickiest customers. But what if you could predict which specific customers are about to churn before they leave? In this secret mission, you'll build a machine learning model in Python that scores every customer's churn risk and writes predictions back to your database for Power BI to visualize.

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project runs entirely locally on SQL Server LocalDB and Power BI Desktop, so there are no ongoing costs.

Resources you used:

  • NovaCRM database on SQL Server LocalDB.
  • Python data generator script (generate_data.py).
  • SQL query files (01_create_database.sql, 02_churn_cohort_analysis.sql, 03_cac_ltv_modeling.sql, 04_attribution_analysis.sql).
  • Power BI report file (.pbix).
  • MSSQL VS Code extension.
  • Python packages: pyodbc, pandas, numpy.

Keep everything running

No action needed. Choose this if you are still actively building or plan to extend the project with additional queries or visuals.

  • Your NovaCRM database stays available through LocalDB whenever you need it.
  • Your SQL files and Power BI report remain on disk, ready to open.
  • LocalDB runs on demand, so it does not consume resources when idle.

Pause - I'll come back to this later

Shut down running processes to free up memory. Your files and database stay intact for when you return.

  • Close Power BI Desktop to free up RAM.
  • LocalDB stops automatically after 10 minutes of inactivity. No manual action needed.
  • Keep your project folder on your Desktop so you can find it easily next time.

Delete - I don't want to use this again

Remove all project resources and start fresh. This is permanent.

  • Drop the NovaCRM database by opening a new query in VS Code and running:
USE master;
GO
DROP DATABASE NovaCRM;
GO
  • Delete your project folder from your Desktop.
  • Uninstall the Python packages by running:
pip uninstall pyodbc pandas numpy -y
  • Optionally uninstall the MSSQL extension from VS Code by opening the Extensions sidebar and clicking **Uninstall** on the SQL Server (mssql) extension.
  • Delete your Power BI report file if you saved it outside the project folder.

That's a Wrap!

That's a Wrap!

You just built a complete Revenue Operations analytics stack from scratch. This is the exact combination of SQL, data modeling, and BI visualization that RevOps and data analytics teams use every day.

You've learned how to:

  • Build a normalized SaaS data model and write T-SQL analytical queries using CTEs, window functions, and conditional aggregation to analyze churn cohorts, calculate CAC/LTV ratios, and model multi-touch attribution.
  • Design three distinct marketing attribution models (first-touch, linear, and time-decay) in pure SQL and compare how each distributes revenue credit across channels.
  • Connect Power BI Desktop directly to SQL Server LocalDB and build an interactive executive dashboard with retention heatmaps, unit economics charts, and cross-filtered attribution breakdowns.
  • Build a Python churn risk scoring model and surface predictions in your Power BI dashboard to identify high-value at-risk customers.

Ready to quiz yourself?