Customer Loyalty and Repeat Purchase Tracker

Most small businesses earn the bulk of their revenue from customers who come back, yet few can say who those customers are, who has quietly stopped buying, or what their points programme owes. This workbook answers all three from a simple purchase log.

List customers (up to 1,000) and log orders (up to 5,000) with date, customer ID and amount. Each customer gets first and last purchase, orders, lifetime and last-12-month spend, average order, days since last purchase and days to second purchase, then an RFM score (recency, frequency and monetary, each 1–5 against thresholds you set), one of nine segments (Champion, Loyal, New, Promising, Needs attention, At risk, Hibernating, Lost, No purchases yet) with your own suggested action, a spend tier with its perk, and a points balance with its cash value.

Reports: Segments and Tiers, Repeat and Retention (repeat-purchase rate, time to second purchase, orders per customer), Monthly first-order versus repeat revenue with points issued and redeemed, a printable Win-Back List with opt-in status and the action for each customer, and a Dashboard with 15 headline numbers, top 10 customers and two charts.

Included: worked-example and blank workbooks (10 sheets, 49,061 formulas; the example is a fictional tea shop with 236 customers and 2,147 orders), plus a 5-page PDF guide in US Letter and A4 with a quick start, how RFM scoring works, what to do in each segment, win-back message wording, a worked example and a monthly review. Google Sheets copy: pending.

General information, not marketing, legal, tax or accounting advice.