A monthly revenue chart can show whether a business is growing, but it cannot explain whether customers are staying. Cohort analysis answers that question by grouping people who share a starting event and tracking their behaviour over time. A common cohort is the month of a customer’s first purchase, registration or subscription.
The result is usually a retention matrix: cohort rows, elapsed-period columns and a percentage in every cell. It helps analysts distinguish durable engagement from growth caused only by new acquisition.
Start with a precise business definition
Before writing SQL, define three elements:
- Cohort event: the action that places a customer in a group.
- Return event: the later behaviour that counts as retained.
- Time grain: day, week or month.
For an online store, the cohort event may be the first completed order and the return event may be any later completed order. Cancelled transactions should not count. For an application, registration might form the cohort while an active session defines return behaviour.
Prepare the required data
At minimum, the source needs a customer identifier, event timestamp and valid event status. Standardise time zones, remove test accounts, resolve duplicate events and decide how to treat refunds. Data cleaning decisions can materially alter retention percentages, so keep them in the analysis notes.
Then calculate the first valid event for each customer.
WITH first_activity AS (
SELECT customer_id,
DATE_TRUNC('month', MIN(order_date)) AS cohort_month
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
)
SELECT * FROM first_activity;SQL syntax differs by database, but the logic is consistent.
Calculate the cohort period
Join each valid event to the customer’s first-event month. Calculate how many months have passed between the cohort month and activity month. Month zero represents the starting period, month one the following period, and so on.
Aggregate distinct customers by cohort and period. The denominator for a retention rate is normally the number of customers in month zero.
Retention rate = active customers in period N / customers in period 0Use distinct customers rather than transaction count. One highly active buyer should still count as one retained customer unless the metric has deliberately been defined as repeat-order activity.
Build the matrix in Power BI
Load the cohort summary into Power BI. Place cohort month on rows, cohort period on columns and retention rate in values. Conditional formatting creates a heat map that highlights strong and weak areas.
Add supporting cards for cohort size, month-one retention and the latest fully matured cohort. Avoid comparing an incomplete recent cohort with an older cohort that has had more time to develop. A filter or note should identify partial periods.
Read the pattern correctly
A steep decline after month zero may show that customers try once but do not find recurring value. A gradual decline is normal in many products. An improving month-one rate across newer cohorts may reflect better onboarding, targeting or product quality.
Do not claim causation from the matrix alone. A campaign, pricing change or product release may coincide with better retention, but follow-up analysis is required. Segment by acquisition channel, product, geography or plan only when cohort sizes remain large enough to interpret.
Useful extensions
Analysts can expand the same framework to measure revenue retention, repeat-purchase frequency, subscription renewal or feature adoption. Revenue retention may rise even when customer retention falls if remaining customers spend more. Showing both metrics gives stakeholders a more complete picture.
Portfolio project structure
Present the business question, cohort rule, cleaning logic, SQL transformations, Power BI matrix and three recommendations. Include a data-quality section and explain partial-period handling. This demonstrates that you understand the decision behind the dashboard, not just its colours.
Build the database and reporting foundations in the Data Analytics Course in Vizag. Use SQL window functions for analyst workflows when selecting first or latest events, and define the retention measure through a clear KPI tree.
Final takeaway
Cohort analysis becomes useful when the cohort event, return event and time grain are unambiguous. Clean the activity data, protect the denominator and mark incomplete periods. The goal is not merely to create a heat map; it is to reveal where customer value strengthens or disappears.