Learning Objectives
- Understand how RevOps data is organized across leads, accounts, opportunities, subscriptions, and campaigns — and how each table connects across the revenue lifecycle.
- Distinguish between lead-level, account-level, opportunity-level, and subscription-level data and explain how each shapes SQL query design.
- Write SQL queries to analyze pipeline activity, conversion performance, marketing attribution, and sales execution using real CRM-structured data.
- Apply advanced SQL techniques to perform funnel tracking, cohort analysis, customer lifetime value, churn, sales velocity, and executive KPI reporting.
SQL for Revenue Operations Analytics follows a natural progression — starting with how revenue data is structured across marketing, sales, and customer success, moving to pipeline and conversion analytics, and finishing with the strategic intelligence that supports executive decision-making. Every part builds directly on the one before it.
Understanding how revenue data is structured — leads, contacts, accounts, and campaigns. Learning to retrieve, filter, and summarize CRM data to answer basic questions about prospect volume, campaign activity, and account status.
Joining RevOps tables to analyze the conversion of leads into opportunities, pipeline structure and health, sales rep performance, marketing attribution, activity engagement, and revenue forecasting.
Advanced analysis covering end-to-end funnel tracking, cohort analysis, customer lifetime value, churn and retention, sales velocity and bottlenecks, and building executive dashboards with revenue KPIs.
By the end of all three parts, learners will be able to answer the questions that drive revenue strategy — "Where is pipeline stalling?", "Which campaigns generate the highest-value opportunities?", "What is our net revenue retention?", "Which accounts are at churn risk?" — directly from CRM data using SQL.
Every table in this course reflects the real data infrastructure used by CRM platforms such as Salesforce, HubSpot, and Microsoft Dynamics. The 13 tables are organized across three groups that align with the course progression — foundational tables for beginners, pipeline tables for intermediate analysis, and revenue tables for advanced reporting.
Lead volume analysis, source attribution, status-based segmentation, lead scoring distribution, and tracking which campaigns generate the most inbound interest.
Revenue segmentation by industry or company size, account-level lifetime value calculations, customer status reporting, and linking all opportunities and subscriptions to their customer organisation.
Tracking the progression from lead to account contact, role-based segmentation (decision-maker vs. influencer), and linking individuals to their account and campaign activity.
Campaign ROI analysis, channel effectiveness comparisons, budget tracking, and joining to leads and campaign_members to measure how campaigns translate into pipeline and revenue.
Marketing attribution — linking each lead to the campaign that generated them, calculating cost-per-lead by campaign, and measuring engagement rates by campaign type.
Pipeline value analysis, stage-by-stage funnel reporting, win/loss analysis, forecasted revenue calculations, average deal size, and sales cycle length.
Sales velocity analysis — measuring how long deals spend in each stage, identifying bottlenecks in the pipeline, and comparing stage progression across reps and segments.
Individual and team performance scorecards, revenue attribution by rep, win rate analysis, quota attainment reporting, and tenure-based performance comparisons.
Sales effort analysis, activity-to-outcome conversion rates, identifying the activity patterns associated with won deals, and measuring engagement volume by rep and stage.
MRR and ARR calculations, subscription status reporting, revenue cohort analysis, customer lifetime value, and tracking the contracted revenue base at any point in time.
Churn analysis, renewal rate reporting, at-risk account identification, net revenue retention calculations, and supporting customer success intervention planning.
Stores region_name, country, and market_type. Used for segmenting revenue performance by geography and comparing win rates, pipeline, and churn across markets.
Stores function_name and region_id. Links sales reps to their marketing, sales, or customer success team — enabling cross-functional performance analysis.
How the Tables Connect
All 13 tables connect through shared keys that mirror the real-world structure of CRM platforms. lead_id connects leads to contacts and campaign members. account_id links accounts to opportunities, contacts, and subscriptions. opportunity_id connects deals to their history, activities, and resulting subscriptions.
Before writing any query, always ask: Am I analyzing leads, accounts, opportunities, or subscriptions? The answer determines which table to start from and how every join should be structured.
Stores each inbound or outbound prospect before qualification. One lead can eventually become one contact and one account. Use for top-of-funnel volume, source attribution, and lead scoring analysis.
Stores the customer organisation. Multiple contacts, opportunities, and subscriptions all link back to one account. Use for account-level revenue reporting and customer lifetime value.
Stores each individual sales deal. One account can have many opportunities over time. Use for pipeline value, win rates, deal size, and sales cycle analysis.
Stores each recurring revenue contract that results from a closed-won opportunity. Use for MRR, ARR, churn, and net revenue retention analysis.
The Full Revenue Data Hierarchy
| Level | Table(s) | Relationship | Example Revenue Question |
|---|---|---|---|
| Lead | leads | 1 row per prospect | "How many leads did each campaign generate last quarter?" |
| Contact | contacts | 1 row per qualified individual | "Which contacts are decision-makers at enterprise accounts?" |
| Account | accounts | 1 row per company | "What is total pipeline value by industry?" |
| Opportunity | opportunities | Many rows per account | "What is the average deal size by rep this quarter?" |
| Subscription | subscriptions | Many rows per account | "What is total MRR across active subscriptions?" |
| Renewal | renewals | Many rows per subscription | "Which accounts at high churn risk are due for renewal this month?" |
Always verify row counts after joining. An account joined to its opportunities will produce one row per opportunity — not one row per account. Use COUNT(DISTINCT account_id) when counting accounts, not COUNT(*), to prevent inflated totals in revenue reporting.
RevOps analysis is built around a set of core fields that appear across nearly every query in the course. Understanding what each field represents — and how it behaves across different revenue stages — is essential before writing any pipeline or retention SQL.
Pipeline & Opportunity Fields
| Field | Table | Why It Matters in RevOps |
|---|---|---|
| amount | opportunities | The monetary value of each deal. Used for total pipeline value, average deal size, and forecasted revenue calculations. |
| stage_name | opportunities | The current stage of a deal in the sales process. Used for funnel distribution, stage-by-stage conversion rates, and identifying pipeline bottlenecks. |
| probability_percent | opportunities | Estimated likelihood of closing. Multiplied by amount to produce weighted pipeline value — the foundation of revenue forecasting queries. |
| opportunity_status | opportunities | Open, Closed-Won, or Closed-Lost. Used to separate active pipeline from historical results and to calculate win rates. |
| expected_close_date | opportunities | Forecasted close date used for time-based pipeline reporting and identifying slipping deals that have passed their expected close without resolution. |
Subscription & Retention Fields
| Field | Table | Why It Matters in RevOps |
|---|---|---|
| monthly_recurring_revenue | subscriptions | The contracted monthly revenue from each active subscription. Summed across all active subscriptions to produce total MRR — the most important metric in subscription business models. |
| subscription_status | subscriptions | Active, Cancelled, or Paused. Used to separate contracted revenue from churned accounts and to calculate net revenue retention. |
| churn_risk_level | renewals | Risk classification (Low, Medium, High) assigned to each renewal. Used to prioritize customer success interventions and forecast potential churn impact on revenue. |
| renewal_status | renewals | Renewed, Churned, Pending, or At Risk. Used to calculate renewal rate, measure retention performance, and identify accounts that have already churned or are likely to. |
Key Questions These Fields Answer
| RevOps Question | Field(s) | Table(s) |
|---|---|---|
| "What is our total weighted pipeline this quarter?" | amount · probability_percent | opportunities |
| "What is our current total MRR?" | monthly_recurring_revenue · subscription_status | subscriptions |
| "Which reps have the highest win rate this quarter?" | opportunity_status · owner_rep_id | opportunities + sales_reps |
| "Which campaigns generated the most closed-won revenue?" | campaign_id · amount · opportunity_status | leads + opportunities + marketing_campaigns |
| "Which accounts are at high churn risk this month?" | churn_risk_level · renewal_due_date | renewals + subscriptions + accounts |
What Revenue Operations is, how marketing, sales, and customer success data connect in a CRM system, and why SQL is the tool that unifies analysis across all three functions.
Exploring the leads and marketing_campaigns tables — retrieving prospect records, filtering by lead source and status, and understanding how campaigns connect to lead generation.
Understanding the central role of the accounts table and how contacts link qualified individuals to their customer organisation. Filtering by industry, company size, and account status.
Using WHERE to isolate specific records — active leads, open opportunities, high-risk renewals, accounts in target industries — the core filtering skill for all RevOps reporting.
Using COUNT, SUM, and AVG to calculate lead volume, total pipeline value, average deal size, and campaign spend — the building blocks of every RevOps KPI.
Building first RevOps reports — lead counts by source, accounts by industry, campaign budget summaries — structured for sharing with marketing and sales leadership.
SELECT, FROM, WHERE, ORDER BY, LIMIT, COUNT(), SUM(), AVG(), GROUP BY basics, column aliases, status filtering, date filtering.
Joining leads to opportunities through accounts and contacts to measure conversion rates — the percentage of leads that progress into qualified pipeline — by source, campaign, and time period.
Querying the opportunities table to calculate total pipeline value by stage, identify slipping deals, measure weighted pipeline using probability, and track stage distribution across the team.
Joining opportunities to sales_reps and teams to compare win rates, average deal sizes, and total closed revenue across individual reps, roles, and regions.
Connecting campaign_members to leads and opportunities to measure which campaigns drive the most pipeline and closed-won revenue — the core of multi-touch attribution analysis.
Querying sales_activities to measure rep effort, activity-to-outcome conversion rates, and the correlation between activity volume and deal velocity.
Building weighted pipeline forecasts by multiplying opportunity amount by probability, filtering by expected close date, and aggregating by rep, region, and time period.
INNER JOIN, LEFT JOIN, multi-table queries, GROUP BY with aggregation, HAVING, calculated columns, date range filtering, weighted pipeline calculations.
Tracking the full revenue journey — from first campaign touch through lead, opportunity, and closed-won deal to active subscription — measuring conversion rates at every stage.
Grouping accounts by their acquisition month or quarter and comparing revenue retention, expansion, and churn behavior across cohorts — identifying which customer vintages perform best.
Calculating CLV by summing subscription MRR over each account's active period — and segmenting lifetime value by acquisition channel, industry, company size, and rep.
Measuring subscription churn rate, net revenue retention, and identifying the account characteristics and behavioral signals most associated with renewal failure.
Using opportunity_history to calculate average time spent in each pipeline stage, identify the stages where deals stall most frequently, and compare velocity across reps and segments.
Building complete, board-ready RevOps reports — MRR growth, pipeline coverage, win rate trends, churn rate, and net revenue retention — structured for monthly and quarterly business reviews.
CASE WHEN for segmentation, subqueries, window functions, date arithmetic for sales cycle calculations, MRR aggregations, retention rate logic, cohort GROUP BY patterns, advanced multi-table joins.
Overview — Key Takeaways
Five foundational principles from this introductory section.
Leads, contacts, accounts, campaigns, opportunities, history, activities, sales reps, subscriptions, renewals, teams, and regions — covering the full revenue lifecycle from first touch to renewal.
Lead, account, opportunity, and subscription-level data. Joining correctly across these levels — and using COUNT(DISTINCT) when counting entities — prevents inflated revenue totals and misleading pipeline numbers.
amount, stage_name, probability_percent, opportunity_status, monthly_recurring_revenue, subscription_status, churn_risk_level, and renewal_status are the backbone of RevOps SQL — the fields that power every pipeline, retention, and forecast query.
Marketing, sales, and customer success each map to a distinct set of tables. Identifying which function the analytical question belongs to before writing a query points directly to the right starting table.
Conceptual clarity prevents analytical errors. Understanding how RevOps data flows — from campaign to lead to opportunity to subscription — produces accurate, trustworthy revenue insights from the very first query.