Shopify customer cohort analysis: repeat customers and revenue

Customer cohort analysis in Shopify compares buyers grouped by their first order, with every buyer observed for the same elapsed time. Calculate repeat purchasing and revenue after refunds separately: a cohort can produce more orders while retaining less money.

Define the comparison before opening the export

Use this question: which first-order month produced stronger purchasing during each customer's first 30 days? Use a rolling timestamp interval: first order inclusive, first-order timestamp plus 30 elapsed days exclusive. It is a customer-age comparison, not sales booked during the acquisition month.

Shopify describes customer retention as bringing first-time buyers back for repeat purchases in its customer retention guide. Here, the operational definition is a later order with positive merchandise revenue after attributed refunds. Keep customers whose first order was fully refunded in the original acquisition base.

Keep three measures distinct:

• Original orders: completed, noncanceled purchase orders in the window.
• Retained orders: those orders with positive net merchandise revenue after refunds. This is this workbook's definition, not a claim about a Shopify report label.
• Repeat customers: original cohort members with a later retained order in the window.

Shopify's analytics overview describes its dashboards and reporting. The workbook below establishes explicit comparison rules for your exported rows. Covirage is not affiliated with Shopify or Microsoft.

Prepare one row per order

Start with your order export and a refund reconciliation. Preserve customer ID, order ID, order timestamp, refund posting timestamps, status, merchandise sales after discounts, and merchandise refunds. Exclude shipping and sales tax from both sales and refunds in this example.

If an order occupies multiple product rows, consolidate merchandise amounts by order ID. Do not sum an order-header total repeated on every line. Preserve full purchase history when finding first orders; then restrict the comparison window.

A US retailer has these orders, all at midnight UTC on the 2026 dates shown. Sales and refunds are positive. Purchases are complete through March 21; refund history extends through April 5. Separately, count each order's refunds only from its purchase timestamp inclusive to 30 elapsed days later exclusive. All listed refunds meet that rule. The latest order, March 5, has full refund maturity by April 5. Later refunds are excluded consistently across cohorts.

Put the first five columns in Orders!A1:E10, with actual spreadsheet timestamps in C. E contains refunds restricted to each order's refund window. Net revenue in F2 is:

=D2-E2

Fill down. For larger extracts, extend every range below through the last order row.

Build the customer worksheet

On Customers, put the six distinct IDs in A2:A7. B is first purchase timestamp; C is acquisition month; D is retained orders within the purchasing window; E is net revenue; F is a repeat-customer flag. Microsoft's COUNTIFS documentation explains how multiple conditions restrict the count; here they combine customer identity, purchase timestamps, and positive net revenue.

B2 =MINIFS(Orders!$C$2:$C$10,Orders!$A$2:$A$10,A2)
C2 =DATE(YEAR(B2),MONTH(B2),1)
D2 =COUNTIFS(Orders!$A$2:$A$10,A2,Orders!$C$2:$C$10,">="&B2,Orders!$C$2:$C$10,"<"&B2+30,Orders!$F$2:$F$10,">0")
E2 =SUMIFS(Orders!$F$2:$F$10,Orders!$A$2:$A$10,A2,Orders!$C$2:$C$10,">="&B2,Orders!$C$2:$C$10,"<"&B2+30)
F2 =--(COUNTIFS(Orders!$A$2:$A$10,A2,Orders!$C$2:$C$10,">"&B2,Orders!$C$2:$C$10,"<"&B2+30,Orders!$F$2:$F$10,">0")>0)

Fill down. The later-order test uses the purchase timestamp; preserve times when two orders occur on the same date. These formulas assume customer IDs and amounts are complete and refunds have already been matched to orders.

J01's later order occurs at age 15 days; F01's at 24 days; F02's at 23 days. Each qualifies. J03 remains an acquired customer despite retaining no revenue. For a full retention grid, use the dedicated Excel cohort retention tutorial.

Summarize and prove the cohort results

With a cohort month in Summary!A2, customer count and repeat rate are:

=COUNTIF(Customers!$C$2:$C$7,A2)
=SUMIF(Customers!$C$2:$C$7,A2,Customers!$F$2:$F$7)/COUNTIF(Customers!$C$2:$C$7,A2)

Sum D and E by cohort for retained orders and net revenue. Divide cohort net revenue by its original customer count for revenue per acquired customer.

Rates round to one decimal place; dollars per customer round to cents. January's net is 380 minus 100; February's is 460 minus 10. Their combined 730 equals the order sheet's 840 minus 110. The combined repeat rate is 3 divided by 6, with every acquired customer counted once.

February looks stronger within this window. That does not establish why: inspect acquisition mix, merchandising, and returns before changing spending. Marketing cohort analysis addresses acquisition comparisons; customer value analysis extends the commercial assessment. For interpreting later periods, see how to read a cohort retention table.

Where it goes wrong

• Incomplete history. An established buyer's first exported order is mistaken for acquisition.
• Unequal maturity. Recent customers have less opportunity to repeat or return orders. Require complete purchasing and per-order refund windows.
• Refunds subtracted twice. A net export amount is reduced again using a separate refund file.
• Changing the base. Removing fully refunded first orders makes the cohort look stronger without another purchase.
• Identity changes. Duplicate customer records split purchasing histories. Resolve the identifier before grouping.

Compare your own export in Covirage

Bring an order file with customer IDs, purchase dates, and matched refunds. Covirage maps the columns; its tools compute and check the cohort figures, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own export.

See customer profitability for the supporting business context.