Customer Analytics: Lifetime Value, Cohorts & Churn

What you'll build

What you will be able to do

Each lab ends with a concrete result that you build yourself and the checker confirms, cell by cell.

• Calculate Emberly Coffee Roasters's average order value and purchase frequency the right way and combine them into a first, simple customer lifetime value.
• Build a year-by-year CLV table with survival and discount factors, then check it against the one-line geometric formula.
• Tag every order with the customer's first-order month and month number, then build the cohort table with one COUNTIFS formula.
• Turn six cohorts into retention rates, combine them into one weighted retention curve, measure month-to-month retention and chart the curve.
• Turn a cohort revenue table into cumulative margin per customer, find the month the January cohort paid back its acquisition cost and its LTV:CAC so far.
• Measure the monthly churn of Emberly Coffee Roasters's subscription, project how many subscribers survive 1–24 months and estimate the average lifetime.
• Compare the CLV of four customer segments and work out how much you can afford to pay to acquire each one.
• Score 12 customers on recency and frequency, look up their repeat-purchase probability in a calibrated grid and forecast repeat customers and revenue for the next 90 days.