Marketing metrics explained

What is cohort analysis

Cohort analysis groups customers by when they first bought from you, then tracks each group separately over the months that follow.

About the Shopify connector

A cohort is everyone whose first order fell in the same month. The table answers whether the customers you won in March are coming back at the same rate as the ones you won in January, which a single repeat-rate figure cannot.

How it's calculated

For each cohort, count the customers who placed another order one month after their first, two months after, and so on, and divide by the cohort's size.

Retention at month n = customers from the cohort who ordered in month n ÷ customers in the cohort

Month 0 is the first order, always 100%, and usually left out.

A worked example

Three cohorts from an online shop, repeat buyers counted one and two months after the first order:

March's month 2 is still arriving, so the table is a triangle.

What it tells you, and what it doesn't

Read down a column and you see whether retention is improving for newer customers. Here it dipped in February and recovered in March.

A single figure hides this. Across all three cohorts, 193 of 630 customers bought again within a month, a repeat rate of 30.6%. That one number is true, and it says nothing. It cannot show that February's customers came back less often than January's, or that March's came back more often than either. And a large cohort drags the blend towards its own rate, so a report built on it reads a change in acquisition volume as a change in loyalty.

Read across a row and you see how fast a cohort decays, and whether it flattens at a floor of loyal buyers. That curve is what an LTV estimate should be built on.

What it does not tell you is why. A better March could be a better welcome email, or a discount that pulled a second order forward.

Where it goes wrong

Cohorts too small to read. A cohort of 20 moves 5 points when one person buys. Group by quarter until a monthly cohort is large enough that one customer does not move it.

Mixing new and returning customers. A cohort is defined by a customer's first ever order. Someone who bought two years ago and again in March belongs to their original cohort, so build the first-order date from the whole order history, however far back it goes.

Calendar months against elapsed months. A first order on 30 January and a second on 2 February is a month 1 repeat by calendar month and a one-week repeat by elapsed time. Pick one and say which.

Building it in Google Sheets

Start from an orders export with a customer (Shopify's export identifies them by email) and an order date. On a separate tab:

1. First order date per customer: MINIFS over the order date column, matched on customer, filled down a list of distinct customers.
2. Cohort month: =DATE(YEAR(C2), MONTH(C2), 1).
3. Months since first order: =(YEAR(order date) - YEAR(first date)) * 12 + MONTH(order date) - MONTH(first date).
4. Cohort size: COUNTUNIQUEIFS of customers where cohort month matches.
5. Each cell: COUNTUNIQUEIFS of customers where cohort month matches the row and months-since matches the column, divided by the cohort size.

Related metrics

• Retention rate formula: one period's retention, the figure each cell holds.
• Churn rate formula: the same cell from the other side.
• Customer lifetime value formula: what the row feeds into.
• Net revenue retention: the revenue version, for subscriptions.

Keeping it current

The table needs a customer against every order, and that comes from Shopify's own orders export (Orders, then Export, in the Shopify admin), whose Email column identifies the customer on each row. Source: Shopify Help Centre, Exporting orders. Paste it into a tab of its own and point the cohort formulas at it by heading, so they survive a column being added:

=INDEX('Orders'!A:Z, 0, MATCH("Email", 'Orders'!1:1, 0))

A scheduled Fresh Sheets report can keep the order counts and totals by day in a separate Shopify tab, for the cohort sizes and revenue; it carries no customer field, so it does not replace the export for the table itself. It replaces that tab's contents on every refresh, so keep every formula on a tab of its own.