Home/Methods

E-commerce · Customer analytics

Customer segments, RFM and lifetime value for cash-on-delivery stores: why you must use delivered orders

RFM segmentation, cohort retention and customer lifetime value only mean something on a cash-on-delivery store if they are computed on delivered orders, because a parcel refused at the door is not revenue. We score every customer by recency, frequency and monetary value from delivered orders only, per store and per currency, and we find a single customer across systems by the last nine digits of the phone number. The hard part was not the formulas but making sure the search and the data use the same normalisation.

Updated 2026-10-11

Why customer analytics on a cash-on-delivery store must use delivered orders

On a cash-on-delivery store, customer segments, RFM scores and lifetime value must be computed on delivered orders only. A cash-on-delivery order is paid in cash to the courier at the door, so an order the customer refuses brings no money in. If you build segments from the raw order list of the store platform, every refused parcel counts as a purchase, and a customer who refused several parcels looks like a loyal, valuable buyer.

RFM stands for recency (how long since the last order), frequency (how many orders) and monetary value (how much the customer paid). Lifetime value (LTV) is the average revenue a customer brings over the whole relationship. Cohort retention is the share of customers, grouped by the month of their first order, who buy again in later months. On a cash-on-delivery store, all three must read from the courier's delivery status, not from the order being placed.

We run this analysis on our delivery database, where each order carries an aggregated delivery status, and we filter on status = 'delivered'. The store platform's own order list is the wrong source here: it includes refusals, and in our case it also includes cancellations.

Step 1: pick one customer identity and one currency

1. Identity. RFM needs one row per customer. In our delivery data the identity key is the customer email, which covers about 355,000 unique customers across about 443,000 orders. Orders with an empty email are excluded, so a customer who never gave an email stays anonymous and their orders are not linked together. Email coverage is good, but it is not complete.

2. Currency. Monetary value is stored in the currency of each store. A store that sells in Romanian lei and one that sells in Czech koruna cannot be added together, so customer analytics runs per store. Running across all stores is useful only for counting customers, never for value.

3. Refunds. Our customer analytics does not subtract partial refunds. The order total is the value of the delivered order. That is acceptable for ranking customers; for profit you need the full profitability pipeline, which is a different method.

Step 2: score RFM with quintiles and map scores to segments

Each customer gets a score from 1 to 5 on recency, frequency and monetary value, using quintiles (NTILE(5) in SQL): the most recent fifth of customers get 5 for recency, the most frequent fifth get 5 for frequency, and so on. Quintiles adapt to each store, so you do not have to guess what "recent" means for a store that sells perfume versus one that sells furniture.

We then average frequency and monetary into one score (FM) and map the pair to six segments. Champions: recency at least 4 and FM at least 4. Loyal: recency at least 3 and FM at least 3. New or promising: recency at least 4 but FM below 3. At risk: recency 2 or lower but FM at least 4, meaning valuable customers who have stopped buying. Lost: recency and FM both 2 or lower. Everyone else is needs attention.

The segment that pays for the analysis is at risk: customers who spent a lot and have gone quiet. They are the first target for a win-back email or SMS flow, and reactivating them costs far less than finding new customers.

Step 3: cohort retention, LTV per cohort and a simple forecast

Cohort retention. Group customers by the month of their first delivered order, then count what share of each cohort bought again in month 1, month 2, up to month 6. Month 0 is 100% by definition. A flat retention curve is a product or post-purchase problem, not an acquisition problem.

LTV per cohort. For each first-order month we report the number of customers, orders per customer, average delivered revenue per customer, and the repeat rate (share of customers with 2 or more delivered orders). On one of our stores the repeat rate ranged from 4.9% in the youngest cohorts to 28.5% in the oldest. Older cohorts always look better because they have had more time to buy again, so read them as the ceiling a young cohort may reach, not as its current value. Cohort LTV is the realistic upper bound for what you can pay to acquire a customer.

Forecast. We take delivered orders and revenue per week over the last 52 weeks, drop the current partial week, average the last 8 weeks, and compare the last 4 weeks with the 4 before them to get a trend. The trend is applied at half strength per week ahead. This is a deliberately simple model with no fine seasonality, good enough for stock and budget planning, not for a peak season.

Step 4: find one customer by phone across formats

To find a single customer across systems by phone number, strip every non-digit and compare only the last 9 digits. A Romanian mobile number arrives as 0700000000, 40700000000, +40 700 000 000 or with dashes, depending on the checkout form, the courier and the agent who typed it. The last 9 digits are the same in all of them. Our customer-service lookup searches both the billing phone and the shipping phone with that suffix, and returns every order from every store.

From that list the lookup computes lifetime value from delivered orders only, the refusal rate, and a flag for serial refusers: a customer with 2 or more refusals, or a refusal rate of 50% or more over at least 2 completed parcels, is offered card payment only, never cash on delivery again.

Phone is the better key for customer service on cash-on-delivery stores, because the phone is the one field the courier needs and the customer cannot skip. Email is the better key for the RFM batch, because it is stored consistently in the delivery data. We accept the gap rather than pretend one key covers both jobs.

Linking a support ticket to a customer is harder. In our helpdesk, email tickets link directly (27% of tickets). In private social messages (about 12%) the customer usually types an email or phone number when asked, which we extract with a pattern match. Comments on ads (60% of tickets) carry only an anonymised, page-scoped ID, so the person behind them cannot be identified, only the store.

Where we were wrong: the search normalisation was not the data normalisation

Our worst identity bug was not in customer data but in an address lookup, and it applies to any search. The search function folded text one way, by decomposing characters and dropping accents, while the stored column had been normalised another way. In Bulgarian, a Cyrillic letter that decomposes lost its mark, so 303 of 6,195 localities could never be found. In Polish, a letter with no decomposed form kept its stroke while the stored name had a plain letter, so 6,382 of 35,714 names were unfindable, including well-known cities.

A search that asks the wrong question always answers "nothing found", and that looks exactly like "nothing exists". The fix is a test, not a better fold: when you compare input with a precomputed column, prove the two normalisations are the same function by counting rows where norm(name) <> name_norm. If the count is not 0, you are searching in a different language from the one the data was written in. Our last-9-digits rule for phones is safe precisely because the stored phones are matched with the same rule, as a suffix.

The second mistake was trusting a source because it looked fresh. Our analytics warehouse had orders up to the current day, yet for one store it was missing about 15% of orders compared with the live store. A defect-rate report built on a truncated source overestimated the rate by about 38% before we moved it to the delivery database.

Limits and a short checklist

This method ranks customers; it does not measure profit. Partial refunds are not subtracted, customers without email are invisible to the RFM batch, and the forecast ignores seasonality. Quintile scores are relative, so a store with very few repeat buyers will still have a top fifth called "Champions".

Checklist. Filter on delivered orders, not placed orders. Run per store and per currency. Choose one identity key per job and know what it misses. Score R, F and M with quintiles and act first on the at-risk segment. Read old cohorts as a ceiling. Match phones on the last 9 digits of both sides. Before trusting a "not found", prove the search and the stored data are normalised by the same function.

Questions and answers

Why should RFM use delivered orders instead of placed orders?

On a cash-on-delivery store the customer pays the courier at the door, so a refused parcel brings no money. Placed-order data counts every refusal as a purchase, which inflates frequency and monetary scores and can rank a customer who refused several parcels as a top buyer. Filtering on delivered orders makes RFM, lifetime value and retention describe customers who actually paid.

How do you calculate RFM segments in SQL?

Aggregate delivered orders per customer into last order date, order count and total paid. Score each with NTILE(5) to get 1 to 5. Average frequency and monetary into one score, then map: recency 4+ and FM 4+ is Champions, recency 3+ and FM 3+ is Loyal, recency 4+ with FM below 3 is New, recency 2 or lower with FM 4+ is At risk, both 2 or lower is Lost, the rest need attention.

How do I find a customer by phone number when it is stored in different formats?

Remove every character that is not a digit and compare only the last 9 digits, as a suffix match on the stored phone. That makes a local number with a leading zero, the same number with the country code and the same number with spaces or a plus sign match each other. Search both the billing and the shipping phone, because customers often fill in only one.

What is a good repeat purchase rate for a cash-on-delivery store?

We do not have a universal benchmark. On one of our stores the share of customers with 2 or more delivered orders ranged from 4.9% in young cohorts to 28.5% in old ones. The spread is mostly time: old cohorts have had longer to buy again. Compare cohorts of the same age, and measure on delivered orders only.

Should I run customer analytics across all my stores at once?

Only for counting customers. Monetary value is stored in each store's own currency, so adding lei and koruna produces a meaningless number. Run RFM, lifetime value and forecasts per store, and convert currencies explicitly if you must compare stores.

All methods