Building a SaaS LTV Model with SQL: From BigQuery to Visualization скачать в хорошем качестве

Building a SaaS LTV Model with SQL: From BigQuery to Visualization

Technical Guide to build a robust LTV with SQL Most tutorials use the ARPU/Churn Rate shortcut to calculate Lifetime Value. In a real-world business with refunds, reactivations, and non-linear churn, that formula is a liability. In this video, we build a robust, cohort-based LTV model from scratch using raw SQL logic in BigQuery. No simplifications, no Excel hacks—just the data modeling required for professional-grade LTV curves. 🔥 Get the SQL Template & Data Generator on my Substack: https://mackansql.substack.com/p/buil... What this video is covering: Handling the Mess: Building a UNION ALL logic to net payments against refunds based on actual cash-flow timing. The Cohort Map: Identifying "Birth Months" without letting refunds skew your cohort size. Payment Age: Using DATE_DIFF and window functions to calculate cumulative revenue per month. Visualization: Bumping the results into a sheet to generate the LTV decay curve. Timestamps: 0:00 - The LTV Problem (Why ARPU/Churn Fails) 1:05 - Table structure 1:50 - CTE 1: Cleaning and merge Payments & Refunds 04:40 - CTE 2: Insert helper columns 06:20 - CTE 3: Aggregate monthly 06:55 - CTE 4: Cohort size 08:44 - CTE 5: Joining and final aggregation 10:40 - Export to Google Sheet 111.20 - Visualizing the Result in Google Sheets 14:30 - Next Steps: WACC & NPV (Part 2) Resources Mentioned: BigQuery SQL Documentation Looker Studio Dashboarding Discounted Cash Flow (DCF) Basics Connect with me: Substack: https://mackansql.substack.com/ #SaaS #SQL #BigQuery #DataAnalytics #LTV #CohortAnalysis #datascience #marketinganalytics #churn