Analytics and capstone
Cohorts, funnels and RFM
Define business metrics before writing SQL.
Understand the concept
Cohorts group users by the period of a meaningful first event. A first-paid-order cohort differs from a registration cohort. Retention might mean repeat paying users in a period; define the denominator and observation window. Funnels need event data: order statuses alone do not provide a complete visit-to-cart-to-payment funnel.
RFM means recency since last purchase, frequency of purchases and monetary value. A fixed as-of date makes results reproducible. Specify missing-customer, cancelled and pending rules. NTILE on a tiny group does not guarantee meaningful industry segments. This lab teaches the model; real segmentation needs larger validated data.
Connect analytics to defensible business definitions.
Calculating frequency from post-join line counts.
One topic. Three levels of depth.
SELECT CustomerId, MIN(OrderDate) AS FirstPurchase FROM lab.SalesOrder WHERE Status = 'Paid' GROUP BY CustomerId;The first paid order defines the cohort event.
This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.
Try it yourself first.
Calculate days since last payment as of May 1, 2026.
Show the solution and explanation
SELECT CustomerId, DATEDIFF(day, MAX(OrderDate), CAST('20260501' AS date)) AS RecencyDays FROM lab.SalesOrder WHERE Status = 'Paid' GROUP BY CustomerId;Selin is absent from this paying-customer report because she has no purchases.