Customer Retention Analysis: Four Tables You Can Build From One Order Export

You can open your Shopify or WooCommerce admin right now and see total orders, revenue and average order value. What you cannot see is why May's new customers placed fewer second orders than April's, or which product brought in the buyers who never came back. A complete retention analysis for a store is four tables, all built from the same order export, and each one answers one decision.

Key takeaways

• One export with order id, customer id, order date, product and revenue is enough. You do not need a data team.

• Table one, cohort repeat rate, tells you whether retention is getting better or worse. Table two, the second-order window, tells you when to follow up. Table three, product-level repeat rate, tells you which first product retains. Table four, lapsed customers by product, tells you who to contact this week.

• A simple lifetime value estimate falls out of the same numbers.

• A spreadsheet handles all four once. Tools take over when you need them refreshed monthly across many products or several stores.

How do I calculate repeat purchase rate from Shopify or WooCommerce orders?

Group orders by customer over a period, count customers with at least one order and customers with at least two, and divide the second by the first.

In a spreadsheet: a pivot with customer id as rows and a count of order ids as values, plus a helper column flagging counts of two or more. Illustration: 1,000 customers ordered between January and June. 260 placed two or more orders. Repeat purchase rate is 26%. Filter to customers whose first order fell in the window so the figure reflects a clean acquisition cohort rather than long-standing customers. The repeat purchase rate benchmark article covers why the window and the denominator matter.

How do I find the second-order window from order data?

Sort each customer's orders by date, compute the days between the first and second order only, and take the median and the 90th percentile of those gaps.

The median is when most repeaters come back. The 90th percentile is the point past which a customer is almost certainly gone. Both drive timing: the reminder goes shortly before the median, the win-back starts after the 90th percentile.

How do I run a churn analysis for a non-subscription store?

Define churn per product from reorder gaps. A customer is at risk past the product's median gap with no reorder and churned past its 90th percentile. Monthly churn is the share of active customers who crossed that line in the month. The churn rate article walks through it step by step. Table four below is the output.

How do I estimate customer lifetime value from an export?

Take one acquisition cohort, sum its revenue over twelve months, and divide by the number of customers in the cohort. That is a twelve-month value per acquired customer. Illustration: a March cohort of 1,000 customers produced 80,000 in revenue by the following March: 80 per customer. Compare it with what you paid to acquire them, and compare cohorts with each other to see whether retention work is moving it.

What can a spreadsheet do, and where does it stop?

A spreadsheet builds all four tables, the churn flags and the lifetime value view from one export. It stops being practical when the tables need refreshing every month across dozens of products or several stores, when you want the cross-order product patterns behind table three, or when you need a ranked customer list for a campaign rather than a count. Affinsy runs the same four tables from an uploaded export, adds RFM segmentation and which product follows which, and turns any row into an exportable audience. Do the spreadsheet version once anyway: it tells you what any tool has to answer.

The export

Columns: order id, customer id or billing email, order date (paid date, not fulfilment), product id or SKU, product name, quantity, line or order revenue. Optional: acquisition channel. Use 12 to 24 months so repeat patterns are visible. Remove test and fully refunded orders and merge duplicate customers before you start.

Table one: cohort repeat rate at 90 days and 12 months

1. Find each customer's first order date and assign a cohort month.

2. For each customer, check whether a second order landed within 90 days and within 365 days.

3. Roll up per cohort.

Illustration:

Read it for direction. A newer cohort that drops after a change to product, price or the post-purchase flow is the signal. Cohorts still inside their window are marked in progress, not compared.

Table two: second-order window

If the median is 35 days and your reorder email fires on day 45, you are late for most of the cohort. The replenishment email guide turns this table into flow timing per product.

Table three: product-level repeat rate by first product

Tag each customer's first order with its main product (the highest-value line item). Group by that product and count who repeated within 90 days and within 12 months.

Sort by the 90-day rate. The top is your gateway product for acquisition, hero placements and bundles. The products that drive repeat purchases article covers what to do with each role.

Table four: lapsed customers by product window

For each replenishable product, compute the median reorder gap and the 90th percentile from customers who reordered it. Then flag every customer whose last purchase of that product is older than the median (at risk) or the 90th percentile (churned).

This is the table that becomes a campaign. Start with the product that has the largest pool of recently late buyers. The win-back timing article covers who to contact first and how to measure it against a hold-out.

Reading the four tables together

Start with table one for direction. Use table two to set timing. Use table three to choose the products to lead with. Use table four to choose who to contact this week. Write one decision under each table so the analysis becomes a short to-do list rather than a dashboard.

Illustration: March and April cohorts show a higher 90-day repeat rate after the post-purchase flow was changed. The median second-order gap shortened from 35 to 28 days. Product A still leads on first-order repeat. The serum has 300 customers past their median. The first action is a serum win-back to those 300, and the second is moving acquisition spend toward Product A.

For agencies

Each table is one slide with one headline finding and one proposed experiment. Standardise the four tables as the baseline for every new store and repeat them quarterly with fresh data, so progress is visible in the client's own numbers. Affinsy keeps them refreshed across several client stores from each store's export.

Next steps

Export orders, build the four tables, write one decision under each, and repeat quarterly. If you would rather have all four built for your store in 48 hours, with the product roles and the lapsed lists included, the 48-hour analysis does that.

FAQ

How often should I recalculate?

Monthly or quarterly. Run a separate pass on November and December cohorts, which behave differently from the rest of the year.

Should I split by channel or campaign?

Yes, once the store-level tables exist. Start with paid versus organic before finer splits, and watch table one and table three by channel: it shows which sources bring customers who come back.

What if products have very different cycles?

Compute tables two and four per product category, never at store level. Tag products as replenishable, occasional or one-off and read each group on its own terms.

What if many orders lack a customer id?

Use the most consistently filled identifier, usually email, and clean duplicates first. Improving identifier capture at checkout is itself a retention project.

What is the minimum volume?

A few hundred customers per cohort month gives usable tables. Below about 50, combine months into quarterly cohorts and read relative differences rather than exact figures.