Product Analytics with SQL: Funnels, Cohorts and Retention
Turn raw product events into metrics a team can trust: active users, sessions, funnels, retention cohorts, attribution and experiment readouts, all in SQL.
Intermediate · Data Science · about 14 hours · 5 modules · certificate on completion
Taught by Arjùn Sharmà, Valentinà Lopèz, AI Learning Companions.
About this course
Product teams live on a handful of numbers: how many people use the product, where they give up, whether they come back, which channels brought them and whether the last change helped. This course teaches you to compute all of them yourself, in SQL, from the raw event log, and to know when a number can be trusted. You'll work with 13 weeks of events from Basketful, a fictional grocery-delivery app, generated on your own laptop and queried with DuckDB, complete with the duplicates, test accounts and anonymous visitors that real tracking data contains.
You'll build a clean events view, define active users and stickiness, sessionise event streams with window functions, build ordered funnels and use them to find a broken checkout, build retention cohorts and growth accounting that cope with cohorts still in progress, share sign-up credit between channels under four attribution rules, and read out a real A/B test from its assignment log, including a sample ratio check, a triggered analysis, intervals for conversion, revenue and a ratio metric, and a decision. It builds on SQL for Data Analysis with DataGrip and goes much further, into the queries product analysts write every week. Every lab prints real results that your instructor checks, and the capstone is a quarterly product review built on a reusable metrics layer.
What you'll learn
- Model product events and build a clean, deduplicated, identity-stitched events view
- Measure DAU, WAU, MAU and stickiness with a definition of active that holds up
- Sessionise event streams and build ordered funnels with window functions, then locate where they break
- Build retention cohort tables, curves and growth accounting that handle cohorts still in progress
- Compare first-touch, last-touch, linear and position-based attribution, and explain what none of them measures
- Read out an A/B test from assignment and event data: sample ratio check, triggered analysis, intervals and a decision
Skills you'll gain
Product analytics, Event data modelling, SQL window functions, Sessionisation, Funnel analysis, Cohort and retention analysis, Growth accounting, Marketing attribution, Experiment readouts, DuckDB
Tools
DuckDB, Python 3
Before you start
SQL for Data Analysis with DataGrip, or the same SQL: GROUP BY, joins, CTEs and basic window functions (ROW_NUMBER, LAG, running totals). Statistics for Data Science and AI helps with module 5; Practical Statistics for Data Scientists covers test design and significance in depth. Install Python 3.10 or later and pip install duckdb; any editor or Jupyter works.
Modules
Event data and active users
How product events are tracked and stored, how to turn a raw event log into a clean events view, and how to count active users so the number means something.
- Events, users and the tracking plan (12 min)
- Querying event logs in DuckDB (12 min)
- Active users: DAU, WAU, MAU and stickiness (12 min)
- Hands-on lab: Clean the event log and measure active users
Sessions and funnels
Use window functions to turn an event stream into sessions and ordered funnels, then read a funnel by platform and week to find where, and when, users drop off.
- Window functions for event streams (12 min)
- Sessionisation (12 min)
- Ordered funnels and conversion windows (12 min)
- Reading a funnel: segments, trends and time to convert (10 min)
- Hands-on lab: Sessionise the events and find the broken checkout
Cohorts and retention
Group users by when they started, build retention tables and curves that handle cohorts still in progress, and account for growth: new, retained, resurrected and churned users.
- Cohorts: comparing users who started together (10 min)
- Retention curves and cohorts still in progress (12 min)
- Churn, resurrection and growth accounting (12 min)
- Hands-on lab: Build retention cohorts and growth accounting
Attribution: giving conversions their credit
Match conversions to the marketing touches before them, share the credit with single-touch and multi-touch rules in SQL, and see why attribution isn't the same as incrementality.
- Touchpoints, lookback windows and single-touch rules (12 min)
- Multi-touch rules, and what attribution can't tell you (12 min)
- Hands-on lab: Attribute sign-ups to channels four ways
Experiment readouts from event data
Build an A/B test readout in SQL: join assignments to events, choose the population and windows, check the split, compute differences and intervals for conversion, means and ratios, and decide.
- Assignment, exposure and the analysis population (12 min)
- Computing the readout in SQL (12 min)
- Writing the readout and making the decision (10 min)
- Hands-on lab: Read out the one-page checkout experiment
Project: A quarterly product review for Basketful
Basketful's product lead wants this quarter's review built from the event log rather than from screenshots of dashboards. Using the course data (or your own product's events, in the same shape), build a small, reusable metrics layer in DuckDB and answer five questions with it. Is engagement growing (DAU, WAU, MAU and stickiness)? Where does the purchase funnel lose the most people, by platform and week? Did the onboarding flow launched on 23 February improve retention, and how sure can anyone be without an experiment? Which channels bring sign-ups who stay, looking at week-4 retention by first-touch channel, and how much do the attribution rules disagree? And should one_page_checkout ship? Write it up as a memo of at most two pages for the product team, with tables or charts, and say plainly what the data can't tell you.
Part of
The products this course names are trademarks of their owners, who haven't made or endorsed it.