How to run a cohort retention analysis for a Shopify store

A cohort retention analysis answers a simple question that store-wide averages hide: do the customers you acquire keep coming back, and is that getting better or worse? Instead of one blended repeat rate, you follow each group of new customers through time and compare groups side by side. It is the most useful single table a Shopify brand can build, and it takes an afternoon with an orders export.

What is a cohort retention analysis?

It is a table where each row is an acquisition cohort and each column is the cohort’s age. Each cell holds a metric for that cohort at that age. Three definitions do most of the work:

• Acquisition cohort: every customer whose first order fell in the same period, usually a calendar month. A customer belongs to exactly one cohort, forever.
• Repeat purchase rate: the share of a cohort that placed a second order within a fixed window after their first, for example 90 days. The fixed window matters: an all-time repeat rate always flatters older cohorts because they have had longer to come back.
• Cumulative revenue per customer: total net revenue the cohort has generated up to a given age, divided by the number of customers originally acquired. Divide by the original count, not by the customers still buying, or you get a survivor average that only ever goes up.

Shopify’s own Customer cohort analysis report groups customers by the date of their first order and can show retention rate, gross and net sales and average order value. It is a good first look. Build your own when you need to control what counts as an order, exclude wholesale or subscriptions, or join cohorts to acquisition cost.

What does a cohort table look like?

Here is the shape, with illustrative numbers rather than real store data:

Cohort	Customers	Month 0	Month 1	Month 2	Month 3
2026-01	1,000	100%	8%	6%	5%
2026-02	850	100%	9%	7%	—
2026-03	1,200	100%	6%	—	—

Read it in three directions:

1. Along a row: how one cohort decays. A steep drop after month 1 followed by a flat tail is the typical shape for non-subscription stores; the tail is your loyal base.
2. Down a column: whether newer cohorts behave better or worse than older ones at the same age. This is where you see the effect of a new acquisition channel, a product launch or a change in post-purchase emails.
3. Along a diagonal: cells on the same diagonal happened in the same calendar month. A whole diagonal that lights up usually means a sitewide sale, not better retention.

The dashes are cells the cohort has not reached yet. They must stay blank. A zero there reads as “everyone churned”, which is wrong.

How do I build a cohort table from a Shopify orders export?

Export orders from the Shopify admin (Orders → Export) as CSV. Two details in that file matter. First, orders with several line items take several rows, and according to Shopify’s documentation many fields are left blank on the extra rows, so the order total appears once per order. Second, the export has an Email column but no customer ID column, so email is your customer key. If you pull data through the Admin API instead, the Order object links to a customer and has fields such as cancelledAt, test and totalRefundedSet, which make a cleaner key and cleaner filters.

In pandas

import pandas as pd

raw = pd.read_csv("orders_export.csv")

# One row per order: extra line-item rows leave order fields blank,
# so take the first non-null value of each column per order.
orders = raw.groupby("Name", as_index=False).first()
orders = orders[orders["Canceled at"].isna() & orders["Email"].notna()].copy()

orders["customer"] = orders["Email"].str.strip().str.lower()
# Keep the store's local time; drop the UTC offset.
orders["created"] = pd.to_datetime(orders["Created at"].str[:19])
orders["net"] = orders["Total"] - orders["Refunded Amount"].fillna(0)

first = orders.groupby("customer")["created"].transform("min")
orders["cohort"] = first.dt.to_period("M")
orders["age"] = ((orders["created"].dt.year - first.dt.year) * 12
                 + orders["created"].dt.month - first.dt.month)

size = orders.groupby("cohort")["customer"].nunique()
active = (orders.groupby(["cohort", "age"])["customer"].nunique()
                .unstack(fill_value=0))
revenue = (orders.groupby(["cohort", "age"])["net"].sum()
                 .unstack(fill_value=0))

retention = active.div(size, axis=0)
cum_rev_per_customer = revenue.cumsum(axis=1).div(size, axis=0)

# Blank out cells a cohort hasn't reached yet, so they don't read as zero.
last = orders["created"].max().to_period("M")
max_age = pd.Series([(last - c).n for c in size.index], index=size.index)
mask = pd.DataFrame({a: max_age >= a for a in retention.columns})
retention = retention.where(mask)
cum_rev_per_customer = cum_rev_per_customer.where(mask)

Name is the order number (for example #1001), so grouping on it collapses line items into orders. Drop the current, incomplete month before you present anything.

For the 90-day repeat purchase rate, measure the gap between each customer’s first and second order:

orders = orders.sort_values("created")
nth = orders.groupby("customer").cumcount()
first_order = orders[nth == 0].set_index("customer")
second_order = orders[nth == 1].set_index("customer")["created"]

first_order["days_to_2nd"] = (second_order - first_order["created"]).dt.days
eligible = first_order[first_order["created"] <= orders["created"].max() - pd.Timedelta(days=90)]
repeat_90 = (eligible["days_to_2nd"] <= 90).groupby(eligible["cohort"]).mean()

Only customers who have had a full 90 days are included, so the rate is comparable across cohorts.

In SQL

If your orders are already in a warehouse with one row per order, the same table is a few CTEs. This runs on PostgreSQL and DuckDB; BigQuery needs minor date-function changes.

WITH o AS (
  SELECT lower(trim(email))                    AS customer,
         created_at,
         total - coalesce(refunded_amount, 0)  AS net
  FROM shopify_orders
  WHERE canceled_at IS NULL
    AND email IS NOT NULL
),
f AS (
  SELECT customer, date_trunc('month', min(created_at)) AS cohort
  FROM o
  GROUP BY customer
),
g AS (
  SELECT f.cohort,
         (extract(year FROM o.created_at) - extract(year FROM f.cohort)) * 12
           + extract(month FROM o.created_at) - extract(month FROM f.cohort) AS month_index,
         count(DISTINCT o.customer) AS active_customers,
         sum(o.net)                 AS revenue
  FROM o
  JOIN f USING (customer)
  GROUP BY 1, 2
)
SELECT cohort,
       month_index,
       active_customers,
       active_customers * 1.0 / first_value(active_customers) OVER w AS retention,
       sum(revenue) OVER w / first_value(active_customers) OVER w     AS cum_revenue_per_customer
FROM g
WINDOW w AS (PARTITION BY cohort ORDER BY month_index)
ORDER BY cohort, month_index;

Month 0 always contains every customer in the cohort, so first_value at month 0 is the cohort size. Months in which nobody from a cohort bought produce no row at all; fill them with zero when you pivot.

How do I read the results?

Start with cumulative revenue per customer, because it combines retention and order value into the number finance cares about. Put it next to what you paid to acquire each cohort and you have a payback view: the month in which the curve crosses acquisition cost is your payback period.

Then look at retention by month to understand why the revenue curve has its shape. Two cohorts can reach the same 12-month revenue in different ways: one with a few high-value repeaters, the other with many small reorders. They call for different retention programs.

Finally, check the 90-day repeat purchase rate by cohort. It is the earliest reliable signal. You do not have to wait a year to see whether customers acquired through a new channel come back; a 90-day comparison with earlier cohorts tells you most of what you need.

If you want to project these curves forward rather than just observe them, see our piece on LTV forecasting with under a year of data.

What are the most common cohort analysis mistakes on Shopify?

Ignoring refunds. Gross sales overstate value, and the gap is not uniform. A cohort acquired with a product that gets returned often looks great on gross revenue and poor on net. Subtract refunded amounts, and attribute the refund to the original order’s cohort and month.

Counting cancelled and test orders. Filter out cancelled orders. If you use the API, also drop orders where test is true.

Mixing subscriptions with one-off purchases. Subscription renewals are repeat orders by construction, so a store with a subscription app will show inflated retention. Identify subscription orders however your app marks them (often a tag or the order source) and build separate tables, or treat subscription status as a cohort dimension.

Including wholesale, B2B or staff orders. A handful of wholesale accounts that reorder every month can lift the retention curve of an entire cohort. Exclude them using tags, a customer segment or the sales channel, and analyze them separately.

Not separating discount-acquired cohorts. Customers acquired during a big promotion often repeat at a lower rate and lower order value. Split cohorts by whether the first order used a discount code, or by first-order discount depth, before you conclude that retention is falling overall.

Treating identity as solved. Email is a reasonable key but not a perfect one: guest checkouts with different addresses, typos and changed emails split one person into two “new” customers, which deflates retention. Normalize case and whitespace at a minimum.

Hiding same-month repeats. Month 0 is 100% by definition, so a customer who buys twice in their first month is invisible in the retention view. The fixed-window repeat purchase rate catches this.

Reading immature cells. The most recent cohorts have only a few columns, and the newest column is often a partial month. Blank out what has not happened yet and drop the current month.

When is a cohort table not enough?

A cohort table describes groups. It does not tell you which individual customers are about to lapse, and it does not forecast beyond the history you have. When you need customer-level predictions for retention campaigns, or a forward-looking LTV for budget setting, the cohort table becomes the baseline you validate those models against, not the end product. Our guide on predicting which Shopify customers will churn picks up from there.

If you would rather have the cohort tables built on clean, deduplicated order data and refreshed automatically, that is the kind of diagnostic Asabisoft runs for Shopify brands as the first step of an LTV and retention analytics engagement.