Quick Take: A cohort retention report converts a vague sense that customers are churning into a precise, specific number. Group buyers by acquisition month, track what share repurchases in each subsequent month, and you’ll pinpoint exactly when and where retention breaks down. That data drives every decision from win-back timing to paid acquisition budgets.
The Three Data Points You Need First
You need three fields from your order history to build a cohort retention report: a unique customer identifier, an order date, and an order amount. Pull those three columns from a Shopify CSV export, a database query, or any order management system and you have everything the analysis needs. A cohort retention report groups customers by the month they first purchased and tracks what percentage of that group returns to buy again in each subsequent month.
The customer identifier is the most important field. Email address works as a proxy if you’ve deduplicated it across guest and registered checkouts; otherwise a numeric customer ID from your platform is cleaner. Order date defines cohort membership permanently: a customer’s first order date places them in that cohort, forever. Order amount isn’t required for the retention percentage itself, but you’ll want it right after to extend the analysis into average revenue per cohort member and lifetime value by acquisition month.
Before you export, choose your time grain. Monthly cohorts are the standard starting point for most stores. Weekly cohorts are only warranted if your order volume is high enough to populate each cell without statistical noise. For most stores running under a few thousand orders per month, weekly splits produce tiny cell sizes that look alarming but carry no real meaning. Start monthly, validate the numbers, then consider finer granularity if the business genuinely needs it.
The Core Method: How to Build a Cohort Retention Report for Your Store
A cohort retention report is a grid: rows represent acquisition months, columns represent months since first purchase, and each cell holds the percentage of that cohort who bought again at that point. Month 0 is the acquisition month itself. Month 1 is the following month. Month 2 is two months after acquisition. The grid is triangle-shaped because newer cohorts have fewer data points than older ones.
The retention rate formula has two components. The numerator is the count of unique customers from a given cohort who placed at least one order in a specific month. The denominator is the original size of that cohort on acquisition. Multiply by 100 for a percentage.
Retention rate formula:
(Unique returning customers in period / Original cohort size) × 100
So if 200 customers first bought in January and 34 of them placed another order in March (Month 2 for that cohort), the Month-2 retention rate for the January cohort is 17%. Never compare a 3-month-old cohort with a 12-month-old cohort at Month 6 as if the data points are equivalent. Always compare cohorts at equal age: every cohort’s Month-3 rate against every other cohort’s Month-3 rate.
Field Note: Set your action threshold before you build the report. Decide in advance what number triggers a response: for example, “If any cohort’s Month-3 retention drops below 15%, we activate a win-back email sequence within that week.” Without a pre-set threshold, the report becomes a dashboard people glance at rather than a decision engine they act on. Define the trigger first, then run the numbers.
Why Counting Orders Instead of Customers Overstates Retention
Counting order events overstates retention because a buyer who places three orders in one month gets counted three times in that period’s returning buyer tally, not once. The result is a retention rate that looks meaningfully higher than reality, and the distortion compounds over time as repeat buyers accumulate more orders.
The fix is a single deduplication step: before counting returning customers in any period, reduce the dataset so each customer ID appears at most once per month. You want to know whether a customer placed at least one order in a given period, not how many. Purchase frequency data has real value elsewhere in RFM modeling and subscription cadence analysis, but it doesn’t belong inside a retention percentage calculation.
The practical impact is especially visible in high-frequency categories such as supplements, pet food, or consumables. Buyers in those niches often purchase several times a month. A naive order count can overstate retention by a large margin in those segments. If your numbers look suspiciously strong relative to your intuition about the business, start by auditing the deduplication logic.
Building Your Cohort Retention Heatmap in Google Sheets
Building a cohort retention heatmap in Google Sheets takes three steps: export your order data, build a pivot table with cohort month as the row and months-since-first-purchase as the column, then apply a color scale. No paid tools required. Apply conditional formatting by going to Format, Conditional Formatting, Color Scale, and choosing a gradient from green (high retention) to red (low retention). Excel supports the same layout under Home, Conditional Formatting, Color Scales.
To build the underlying pivot table, export your order data as a CSV with the three required columns. Add two calculated columns in the spreadsheet: first purchase month per customer (using a MINIFS formula keyed to customer ID) and months since first purchase (the difference in months between the order date and that customer’s first purchase date). Build a pivot table with cohort month as the row dimension, months-since-first-purchase as the column dimension, and a count-distinct of customer ID as the value. Divide each cell by its row’s cohort size and multiply by 100. Apply the color scale last.
Start with one metric and monthly cohorts. Once your numbers reconcile and you trust the output, add a second dimension such as acquisition channel or first product purchased. Segmenting before the base report is validated is how teams end up with four conflicting versions of the retention rate and no consensus on which one is correct.
Before sharing the file, lock the cohort definition in a comment at the top of the spreadsheet. The most common source of “our numbers don’t match” is two analysts using different definitions of acquisition month: one using first order date, another using account signup date. Document it once and never change it retroactively.
Tool Options: How to Build a Cohort Retention Report for Your Store Faster
Shopify’s built-in Customer Cohort Analysis report, available on all paid plans, is the fastest starting point with zero setup required. Navigate to Analytics, then Reports, then Customer Cohort Analysis. It shows Month-0 through Month-12 retention by acquisition month and works well for a quick executive read. Its main limitation is segmentation: it doesn’t filter by acquisition channel, first product category, or geography inside the native view.
GA4 includes a Cohort Exploration template under Explore, but it tracks sessions by default, not purchase events. Session-based cohorts inflate retention because any page visit counts as a return. For purchase-event precision, enable the BigQuery export from GA4 settings and query the raw events table, filtering specifically for rows where the event name equals purchase. This method is the most accurate option for high-volume stores with complex multi-session purchase journeys.
BigQuery base query for purchase-event cohorts:
SELECT
user_pseudo_id,
MIN(DATE(TIMESTAMP_MICROS(event_timestamp))) AS first_purchase_date,
DATE(TIMESTAMP_MICROS(event_timestamp)) AS purchase_date
FROM `your_project.your_dataset.events_*`
WHERE event_name = 'purchase'
GROUP BY user_pseudo_id, purchase_date
Paid apps offer the fastest path to segmented cohort tables if SQL isn’t in your workflow. Use Shopify Analytics when you need a quick validation check with no setup. Use a paid app when you need channel-level or SKU-level cohort breakdowns to inform acquisition spend decisions.
| Tool | Free | Setup Effort | Segmentation Depth | Best Used When |
|---|---|---|---|---|
| Shopify Analytics | Yes | None | Basic | You need a quick read with no setup |
| Google Sheets / CSV | Yes | Medium | Medium | You want full control over logic and formatting |
| GA4 + BigQuery | Yes | High | High | You need purchase-event cohorts from raw GA4 data |
| Polar Analytics | No | Low | High | You need channel-level cohort views without SQL |
| Lifetimely | No | Low | High | LTV-by-cohort is your primary reporting goal |
| Triple Whale | No | Low | High | You want cohort LTV tied directly to ad attribution |
| Peel Insights | No | Low | High | You need cohort retention segmentation by product, tag, or customer attribute, not channel-level attribution |
| RetentionX | No | Low | High | You want automated retention actions tied to cohort data |
| Count | No | Medium | High | You need cohorts that cross Shopify, email, and CS data |
Quick Takeaways
- Three fields power any cohort retention table: unique customer ID, order date, and order amount. Customer ID is non-negotiable for accurate deduplication.
- Always count unique customers per period, not orders. Counting orders overstates retention in many datasets, and the distortion is sharpest in high-frequency categories.
- Compare cohorts at equal age only. A January cohort’s Month-3 rate versus an October cohort’s Month-3 rate is a valid comparison. Cross-age comparisons are not.
- Shopify Analytics includes a free native cohort report on all paid plans. It’s the fastest starting point but lacks channel and SKU-level segmentation.
- Set your action threshold before reviewing the data. Pre-defining what number triggers a win-back sequence removes ambiguity from every future review cycle.
Frequently Asked Questions
- What is the minimum data I need to build a cohort retention report from scratch?
- Three fields are sufficient: a unique customer identifier, an order date, and an order amount. Customer ID is the most critical because it anchors the deduplication step that prevents order counts from inflating your retention rate. Order date determines cohort membership by first purchase month, and order amount becomes useful when you extend the analysis to lifetime value calculations by cohort.
- Why does counting orders instead of unique customers overstate retention?
- An order count treats every transaction as a separate return signal. When one buyer submits three orders in a month, the order-based denominator credits three returns instead of one. Run a quick diagnostic: compare your total monthly orders to your unique monthly buyers. If the ratio runs meaningfully above 1.0, your retention rate is inflated and the deduplication step is non-negotiable before you can trust the output.
- Does Shopify include a built-in cohort analysis report and how do I access it?
- Shopify ships a built-in Customer Cohort Analysis report on every paid plan. Find it inside the Reports section under Analytics with no additional configuration required. It covers Month-0 through Month-12 retention for each acquisition cohort. Its main constraint: traffic source, initial product category, and customer geography filters are unavailable in the native view, so a manual export or third-party app is needed for segmented reads.
- How do I run a purchase-event cohort analysis in GA4 instead of a session-based one?
- GA4’s built-in Cohort Exploration tracks sessions by default, so a user who only browsed is counted as retained. To base cohort analysis on purchase events instead, activate the BigQuery export from GA4 Admin, pull the raw events table, and keep only rows where the event name is ‘purchase’. That grounds both cohort enrollment and re-engagement measurement in real transactions rather than pageviews.
- Do these tools require SQL knowledge to build a cohort retention report?
- Most options covered here need no SQL. Shopify Analytics, Lifetimely, Triple Whale, Peel Insights, and RetentionX are all point-and-click interfaces. Google Sheets with a CSV export requires spreadsheet skills but no query language. The only path that requires SQL is the GA4 plus BigQuery route, which suits stores that have a data analyst or a technically comfortable founder available.


