Ecommerce Retention Audit: The Eight Checks, the Export to Ask For, and the One-Page Deliverable

You have a client whose acquisition numbers look fine, but first-time buyers are not placing a second order. The retention audit exists to find out why, from the client's own order history, and to turn the answer into three tests you can measure inside 90 days.

Key takeaways

• A retention audit is eight numbers computed from one order export, each compared with what the client's flows, offers and product mix are actually doing.

• Ask for 18 to 24 months of line-item order history with customer id, dates, products, discounts, channel tags and subscription flags. Most checks run in a spreadsheet.

• The deliverable is one page: the eight numbers, what each one says, and three ranked tests with a success criterion and a review date.

How do I run a retention audit for an e-commerce client?

Export the order history, compute eight retention checks, compare them with the client's current messaging and timing, then propose three tests ranked by revenue impact and effort.

The sequence:

1. Gather data. A clean line-item export covering at least 18 months.

2. Compute the eight checks. Cohort repeat rate, second-order window, product-level repeat rate, reorder cadence, lapsed customers, flow timing versus cadence, discount dependence, subscription fit.

3. Diagnose. Put observed behaviour next to what the flows and offers assume. The gaps are the findings.

4. Propose tests. Three, ranked, each with the number that will prove it worked.

For an agency this needs to be a repeatable checklist that takes a few hours per account. For a merchant auditing their own store, the same method works with the emphasis on quick wins in the next 30 to 90 days.

What data do I ask the client for?

A full order export for the last 18 to 24 months at line-item level, with every column needed to rebuild each customer's journey from the first order forward.

Why 18 months minimum: replenishable products need time to cycle through two or more reorders, and slower categories need time to show whether customers return at all.

Clean before computing. Remove test orders, zero-value internal orders and staff purchases. Merge duplicate customer ids or emails that belong to one person. Drop fully refunded orders. If category or brand metadata exists, request it: it lets you group results instead of reading hundreds of SKUs.

Which tools do agencies use, and what can a spreadsheet do?

Most of the audit runs in a spreadsheet. Tools add speed and depth once the basics exist.

• Spreadsheets for cohort repeat rate, second-order window, lapsed counts and discount dependence. Pivot tables and the median function cover it.

• A BI tool for visualisation when the dataset is large.

• Order-history analysis tools for the parts that are tedious by hand: which products retain and which only open, the reorder cadence per product, which product follows which across orders, and ranked lists of customers who are late. Affinsy does this from the same export, including RFM segmentation and exportable audiences.

• The email platform to map the current post-purchase flow timing against the observed cadence.

A practical workflow: run the core checks in a spreadsheet, then validate segments and build audiences in whichever tool you use.

How do retention agencies decide what to test next?

By picking the check with the widest gap between what customers actually do and what the client's setup assumes, and testing the change that closes it.

Priority order:

1. The second order first. If cohort repeat rate is weak, fix that before anything else. It shapes lifetime value more than any later event.

2. High-volume replenishment products next. Reminder timing on the items customers use up is the cheapest lift available.

3. Win-back for lapsed high-value customers. Best targeted at customers who once spent well and have gone quiet past their product's window.

Rank each idea by estimated revenue impact, effort and risk. Heavy discounting scores badly on risk because it trains customers to wait. Support tickets, reviews and survey answers explain what the numbers cannot, so read them next to the tables. Each test outcome feeds the next audit.

The eight checks

Each check produces one number and a read on what good and bad look like.

1. Repeat purchase rate by cohort. Group customers by first-order month. For each cohort, the share that placed at least one more order within 90 days, and within 12 months. Good: stable or rising across recent cohorts. Bad: a drop in newer cohorts after a change to product, price or site. The repeat purchase rate benchmark article covers how to compare cohorts honestly.

2. Second-order window. Days between first and second order for customers who reordered: the median and the 90th percentile. Good: second orders cluster in a narrow band. Bad: no peak, so nothing can be timed.

3. Product-level repeat rate. For each product common in first orders, the share of those buyers who came back. Openers bring people in and may not retain. Retainers appear again and again in later orders. Many products are neither. The products that drive repeat purchases article has the full table.

4. Reorder cadence per top product. For replenishable items, days between purchases of the same product by the same customer: median and spread. Good: a tight, repeatable cadence. Bad: inconsistent gaps, which means the product is not truly replenishable or customers are stocking up.

5. Lapsed customers by product window. Customers whose time since last order exceeds their product's 90th percentile. Good: a defined pool you can size a campaign for. Bad: a growing pool among previously high-value customers.

6. Post-purchase flow timing versus observed cadence. List every flow email with its day offset. Put those offsets next to the second-order window and product cadences. Good: nudges land shortly before the natural reorder moment. Bad: the reorder reminder fires at day 21 when the median second order is day 35, or the flow ends before most customers reach the window.

7. Discount dependence of the second order. Among second orders, the share that used a discount code. Good: a meaningful share of second orders arrive at full price. Bad: almost none do, which means the second order is bought, not earned.

8. Subscription fit. Products with a stable cadence and customers with three or more purchases of the same item at regular intervals. Good: subscription candidates with few skips. Bad: sporadic reorders where a subscription push would frustrate.

Running the core checks in a spreadsheet

Clean the export, sort by customer id and order date, and add a column numbering each customer's orders in sequence. Add a cohort column from each customer's first order month.

• Cohort repeat rate: pivot with cohort as rows, count of customers with sequence number above one divided by cohort size.

• Second-order window: filter to sequence number two, subtract the first order date, take the median and the 90th percentile.

• Lapsed customers: compare days since last order with the product's 90th percentile and flag those above it.

• Discount dependence: filter to sequence number two, count rows with a discount against rows without.

Count, sum, median and pivot tables cover all eight checks.

Worked example

Round numbers, as an illustration, not a benchmark.

A store acquires 1,000 new customers in January. By the end of March, 300 have placed a second order: a 30% cohort repeat rate at 90 days. Among those 300, the median gap is 35 days, and most fall between 25 and 45.

Two products tell different stories. A coffee blend appeared in 200 first orders and 120 of those customers came back, a 60% product-level repeat rate: opener and retainer. A ceramic mug appeared in 150 first orders and 20 came back, 13%: an opener that does not retain.

Of the 300 second orders, 180 used a discount code. That is 60% discount dependence, and it says the second order is being bought.

The current flow sends the reorder reminder at day 21. The data says most second orders happen around day 35. The reminder is early.

Three tests from this audit:

1. Move the reorder reminder from day 21 to day 30 with a follow-up at day 40. Success: higher 90-day repeat rate for the next cohort.

2. Feature the coffee blend in second-order offers, especially to mug buyers. Success: higher second-order rate among mug first-timers.

3. Test a no-discount second touch, usage content instead of a code, against the current coupon. Success: same second-order rate at lower discount share.

The one-page deliverable

• Two sentences on current retention health in business language.

• The five numbers that matter for this client, usually cohort repeat rate, second-order window, top product repeat rate, discount dependence and lapsed pool size.

• One chart: the second-order window distribution or the cohort table.

• One or two lines per check: the finding and what it implies.

• Three tests, each with the metric that proves it, and review dates at 30 and 90 days.

Where a tool helps: after the spreadsheet work, Affinsy runs the same order history to find which products earn the second order, which product follows which, the reorder cadence per product and the customers late for it, with each finding as an exportable audience. The audit itself needs none of that. The method is the point.

Next steps

Run the three tests. Recompute the eight checks at 30 and 90 days and compare with the audit baseline. Keep one retention sheet per client with the same definitions every period so improvement is visible. Document what worked so the next audit starts higher.

If you want the eight checks run for a client store in 48 hours, with the product roles and the lapsed lists included, the 48-hour analysis does exactly that.

FAQ

How often should I repeat the audit?

A light version quarterly and a full version yearly. Fast-changing catalogues may want the key checks monthly. Same definitions every time, or progress is invisible.

Can I audit a low-volume store?

Yes, with a longer date range and products grouped into categories rather than SKUs. Lean more on support tickets and reviews. Even at low volume, who came back and how long it took is worth knowing.

How do I handle wholesale or B2B orders?

Tag them from existing fields and run the checks separately if wholesale revenue is material. State in the deliverable whether they are included.

What if columns are missing?

Run the checks you can, note the gaps, and make better data collection one of the recommendations. Discount dependence and subscription fit are the checks most often blocked by missing fields.

How do I link retention to growth targets?

Work backwards from the revenue goal to the number of second orders needed at the current order value, then show how a small change in cohort repeat rate moves that figure. That is the case for retention budget, stated in the client's numbers.