Google Data Studio cohort analysis: retention by age

Google Data Studio cohort analysis starts with a fixed customer group and measures how many return at each completed age after their first purchase. Prepare and validate the counts before putting retention into a report. Google returned the Looker Studio product name to Data Studio in April 2026, so both names may appear in your team's existing reports.

Define the cohort before the chart

For a US distributor, define a cohort as customers whose first qualifying purchase occurred in the same calendar month. Active means making another qualifying purchase during the month being measured. Exclude voided transactions and credit-only activity consistently.

Age zero is the first-purchase calendar month; age one is the following month. This measures monthly purchasing activity, not continuous subscription retention. A customer who skips a month and returns counts as active in the return month.

Retention at age = Distinct cohort customers active at that age / Original cohort customers

Calendar months give different amounts of exposure within the acquisition month. Use this convention consistently and compare later completed ages; use elapsed-day windows instead if the business question requires equal days since purchase.

The existing Excel cohort retention guide covers the broader invoice-to-cohort workflow. Here the task is validating the prepared table and presenting a comparable result.

Prepare the source rows in Google Sheets

Keep a customer register named Customers. Its first-purchase dates must come from complete purchase history, not just the months visible in the report. These are all the customers in this worked example:

Store dates as actual date values. In Customers!C2, fill down through row 6:

=DATE(YEAR(B2),MONTH(B2),1)

Next, create Activity with one row per customer and active calendar month. Deduplicate customer-month pairs from the purchase export first: several purchases by the same customer in a month still count as one active customer.

In Activity!C2, retrieve the permanent cohort. In D2, calculate calendar-month age. Fill both through row 11:

=VLOOKUP(A2,Customers!$A$2:$C$6,3,FALSE)
=12*(YEAR(B2)-YEAR(C2))+MONTH(B2)-MONTH(C2)

A missing customer match or negative age is an exception to fix before counting. For related spreadsheet cleanup questions, see Google Sheets data analysis.

Build the table with a completion check

Create Cohorts with the layout below. Enter cohort dates and ages in columns A and B. Put =DATE(2026,3,31) in H1, the last fully completed month-end for which the export is complete. Use this fixed cutoff for the report rather than today's date.

In C2, count original members. In D2, return a blank for unfinished ages; otherwise count deduplicated activity. In E2, divide only observed counts by membership. Fill down through row 7:

=COUNTIF(Customers!$C$2:$C$6,A2)
=IF(EOMONTH(EDATE(A2,B2),0)>$H$1,"",COUNTIFS(Activity!$C$2:$C$11,A2,Activity!$D$2:$D$11,B2))
=IF(D2="","",D2/C2)

Percentages are rounded to one decimal place; calculations retain full precision. February's age two ends in April, so its blank cannot be compared with January's completed age two.

Prove the result before presenting it

January's age-one numerator is customers A and B. February's is D and E. At the same completed age, retention is therefore 2 / 3 = 66.7% versus 2 / 2 = 100.0%. That difference is an observation to investigate, not evidence that a particular sales change caused it.

Run these reconciliations:

• Original membership is 3 + 2 = 5, matching the five customer-register rows. Count membership once per cohort, not once per age.
• Observed active cells total 3 + 2 + 1 + 2 + 2 = 10, matching the ten deduplicated activity rows shown above.
• Age-zero active equals original membership for each cohort. Every later observed active count is between zero and its original membership.
• Overall age-one retention is (2 + 2) / (3 + 2) = 80.0%. The simple average of 66.7% and 100.0%, using unrounded inputs, is 83.3% and gives the smaller cohort extra weight.

Use how to read a cohort retention table when interpreting rows and diagonals. Keep this validation tied to customer IDs so a changed result can be traced to a changed source row.

Present the validated table in Data Studio

Connect the Cohorts worksheet using Create > Data Source > Google Sheets, select the spreadsheet and worksheet, and connect. Google's Sheets connector instructions require tabular data, a single header row, and consistent types within each column. Select the range A1:E7 so the cutoff cell is outside the connected table.

Add a pivot table with cohort month as its row dimension, age as its column dimension, and retention as its metric. Keep one source row per cohort-age pair, format retention as a percentage, and sort numeric age ascending. Turn off percentage totals; summing rates across ages does not measure retention.

In the chart's missing-data settings, show a blank rather than zero. Google's pivot table reference documents row and column dimensions, missing-data display choices, and the heatmap metric style. Check the numeric values before using color, and label the report with the activity definition and completed-through date.

Covirage is not affiliated with Google.

Where it goes wrong

• Membership moves with a filter. Filtering away earlier purchases can make established customers appear new. Preserve the full-history cohort assignment and its original denominator.
• Purchase rows replace customers. Multiple purchases inflate activity unless customer-month pairs are deduplicated before COUNTIFS.
• An incomplete month becomes zero. The completion test must reflect both the calendar and export availability. An unfinished or unavailable age stays blank.
• A report filter breaks eligibility. Restricting displayed dates can remove the rows needed for a fair comparison. Recheck the customer counts and completed ages after changing controls.

Validate your own cohorts in Covirage

Upload your customer register and purchase export, or bring a Google Sheet with a sample of your own data. Covirage maps the columns; its tools compute and check cohort membership, activity counts, and comparable retention figures, while the external AI model explains the result with every figure traced to its rows. Create a free Covirage account; account creation takes about a minute, then you set up your business and bring your file.

See self-service analytics for the approach to answering business questions from your own files.