Which Date Should Recency Use? A Worked RFM Example

A concrete starting point: one shop, three orders, one question

Imagine a small Bangkok-based skincare shop that started exporting its order history to a spreadsheet for RFM analysis. The analyst opens the file and immediately runs into a puzzle: each row in the order table has at least three dates — the date the customer placed the order, the date the parcel was delivered, and the date a staff member last edited the record to add a return note. For the purposes of calculating Recency, which one do you use?

This is not an academic edge case. Pick the wrong date and two customers who behaved identically can end up with different Recency values. Pick an inconsistent date — order date for some customers, delivery date for others — and the entire ranking is corrupted before any scoring begins. This guide works through a single fictional customer, Customer A, with three explicitly defined orders to show exactly how to make and document the choice.

What Recency actually measures

Recency is the number of calendar days between a customer's most recent eligible purchase and a fixed reference date. It does not measure enthusiasm, loyalty, or intent. It measures elapsed time based on whichever date field you designate as the purchase date. Shopify's RFM guide describes the calculation as 'days since their last purchase' counted from 'the date you'll measure recency from' — a reference date that must be pinned to a single point in time, not a moving target like today's date.

Two properties are essential. First, every customer's Recency must be computed against the same reference date so that the values are comparable. A customer who last bought 10 days before the reference date has the same Recency regardless of when you happen to open the spreadsheet. Second, the input date field must be the same type of date for every row. Mixing order dates from some rows with delivery dates from others produces a meaningless number.

Sources: Shopify: RFM Analysis — Definition and Segmentation Guide (2026)

Three candidate dates and the different questions they answer

For this worksheet, order date means the date our fictional shop records the confirmed purchase. We choose it to measure elapsed time since buying, independently of delivery or administration. Before using a real export, check what its date field means and which orders qualify as purchases.

Delivery date is the timestamp when the parcel reached the customer. Using it shifts Recency to measure elapsed time since the customer received the product rather than since they chose to buy. This can be meaningful for businesses where the replenishment cycle starts at receipt — for example, a product consumed over a predictable number of days. However, delivery dates are often missing, delayed by courier API feeds, or unavailable for digital goods.

Record-edit date is the timestamp of the most recent change to the order record in an admin system. It reflects data-entry activity, not customer behavior. A staff member adding a return note weeks after delivery would push the record-edit date forward, making the customer appear more recent than they actually are. This date should never be used as the Recency input unless the specific question being answered is 'when was this record last touched?'

The decision among these three is a business question, not a math question. Document it in writing before building any formula, and apply it consistently to every row in the dataset.

For Customer A's latest order T05-03, suppose the order date is 9 October, delivery is 11 October, and the last record edit is 12 October 2026. Against 13 October, these produce 4, 2, and 1 days respectively. Only 4 answers our chosen purchase question. These are three fictional events on the same order, not two additional purchases.

Why the reference date must be pinned, not dynamic

If the reference cell uses =TODAY(), recalculation on a later day increases the raw day count even when the saved purchase history has not changed. This makes snapshots harder to compare unless their reference dates are recorded. A rolling reference can suit a daily report, but record the date used in each run. A larger raw day count does not automatically mean a worse quartile score.

The correct approach is to pin the reference date to a specific calendar date that represents the end of your analysis window. For this guide, the reference date is 13 October 2026 in the Asia/Bangkok time zone (UTC+7). That date is entered once into a named cell and every Recency formula in the sheet points to it. Nothing changes until you intentionally update the reference and recalculate. This is a teaching convention chosen to make the worked example reproducible; any tool you use for RFM analysis will have its own documented cutoff logic that you should verify separately.

Customer A: three orders, one Recency value

Customer A is a fictional teaching example. The three fields needed to reproduce this calculation are the customer identifier, the order identifier (to count distinct orders correctly), and the order date as a date-only value in the local time zone. No other customer's dates may enter the MAX calculation for Customer A.

Customer A placed three orders. Order T05-01 was placed on 13 July 2026. Order T05-02 was placed on 23 September 2026. Order T05-03 was placed on 9 October 2026. The most recent order date is therefore 9 October 2026. Against the reference date of 13 October 2026, the elapsed time is 4 calendar days. That single number — 4 — is Customer A's raw Recency input for this analysis window.

Spreadsheet layout and the DAYS formula

Use this cell layout to reproduce Customer A's Recency in Excel. Enter =DATE(2026,10,13) in B1 to store a numeric date value rather than text. Cell formatting determines whether you see a calendar date or its serial number. C2, C3, and C4 hold Customer A's order dates as numeric date-only values with no time component. In D1, enter =DAYS($B$1,MAX(C2:C4)).

The DAYS function takes two arguments: end_date first, then start_date. Microsoft's documentation confirms that when both arguments are numeric date serial numbers, DAYS computes end_date minus start_date. Our normalized date-only integers therefore give a whole-day difference. With B1 = 13 October 2026 and MAX(C2:C4) = 9 October 2026, the result is 4. The dollar signs on $B$1 lock the reference cell when adapting the formula. For another customer, use a separate MAX range containing only that customer's dates; do not copy a range that mixes customers.

Two error behaviors are documented by Microsoft and worth knowing. If either argument is a text string that Excel cannot parse as a date, DAYS returns #VALUE!. If either argument falls outside the range of valid date serial numbers, DAYS returns #NUM!. Both are detectable with IFERROR and can be used as a data-quality signal, which is discussed in the checks section below.

Sources: Microsoft Support: DAYS function — Excel

The timestamp normalization problem

Order management systems often store timestamps in UTC rather than local time. A timestamp of 2026-10-12T18:30:00Z is 18:30 on 12 October in UTC, but when converted to Asia/Bangkok (UTC+7) it becomes 01:30 on 13 October 2026. If you treat the UTC date 12 October as the order date without converting, you compute a Recency of 1 day. The correct local date is 13 October, which gives a Recency of 0 days against the same reference date. That is a difference of 1 day — enough to move a customer across a scoring boundary.

Convert the time zone before extracting the date, and record the chosen zone. If the source cell is genuinely a numeric date-time serial in UTC, a helper =INT(timestamp_cell+7/24) converts it to a Bangkok date-only value. This formula accepts a numeric serial, not the ISO 8601 string 2026-10-12T18:30:00Z; parse such strings correctly first. DAYS does not convert time zones. Do not add seven hours again if the source is already local time.

A closely related issue arises with fractional-day serial numbers. If a raw timestamp is stored as a decimal in Excel — for example, 46,307.77 representing a date plus a time fraction — DAYS will subtract the fractional portions along with the date portions and may not return a whole number. Normalize all inputs to date-only integer serial numbers before the formula runs. The safest approach is INT(timestamp_cell) after adding the UTC offset, then verify that the result matches the expected local date for a few known orders before running the full analysis.

Data-quality checks before MAX runs

The MAX function silently ignores blank cells, which can be either helpful or dangerous depending on your data. If Customer A had a fourth order whose date field is blank because it was never entered, MAX(C2:C5) will still return the largest of the three populated dates and produce no error. You would never know the row was missing. Add a COUNTBLANK(C2:C5) check and flag any customer whose order count in the date column differs from the expected row count.

Future dates are a separate hazard. A data-entry error or a pre-order record can produce a date later than the reference date, which would yield a negative Recency value. Negative Recency is nonsensical in most analysis contexts. A simple check is =IF(MAX(C2:C4)>$B$1, "FUTURE DATE — CHECK", DAYS($B$1,MAX(C2:C4))). This traps the problem before it propagates into scoring.

Do not rely on DAYS($B$1,MAX(C2:C4)) to expose dates stored as text. MAX ignores text in a referenced range, including date-looking strings and N/A, so DAYS may receive an apparently valid numeric input. Before MAX, compare =COUNT(C2:C4) with =ROWS(C2:C4). Both must be 3 for this three-row worksheet, which requires every cell to contain a numeric date. If they differ, inspect blank or text cells before calculating. IFERROR catches formula errors; it cannot catch text that MAX has already ignored.

Finally, confirm that the dates in C2:C4 belong only to Customer A. In a raw export file where orders for multiple customers appear in sequence, it is easy to accidentally include a range that spans two customers. The MAX of a mixed range would be wrong for both. Use a helper table or MAXIFS to restrict the date range to a single customer identifier before computing Recency.

Sources: Microsoft: MAX function · Microsoft: COUNT function

What the day count does and does not tell you

Customer A's Recency input is 4 calendar days. This raw number cannot, by itself, produce an RFM score or assign Customer A to a segment. Scoring requires a separate step: comparing the raw day count against the distribution of Recency values across all customers in the analysis window, then dividing that distribution into quartiles or other bands. CasperBrain uses four quartiles labeled 1 through 4, where quartile 1 represents the most recent customers and quartile 4 the least recent. Whether 4 days maps to quartile 1 depends entirely on what the rest of the customer base looks like. Do not assign a quartile from this single example; the raw day count alone is insufficient.

This distinction matters because a Recency score can change between analysis runs without any change in Customer A's behavior, simply because other customers bought more recently and shifted the quartile boundaries. Shopify's RFM documentation makes a parallel observation: a drop in a customer's RFM ranking can occur because other customers bought more, not because the individual's own behavior changed. Always inspect the raw day count before acting on a score shift.

Different platforms use different scoring conventions. Dynamics 365 Commerce, for example, uses a 1-to-5 scale in which 5 represents the highest recency rating — the opposite direction from CasperBrain's quartile 1. Microsoft's documentation explicitly describes this as the 20 customers who bought most recently receiving a recency score of 5. If you ever import scoring rules or segment thresholds from another tool, verify which direction is 'best' before applying them, because mixing conventions will invert your segments.

Sources: Shopify: RFM Analysis — Definition and Segmentation Guide (2026) · Microsoft Learn: Set up RFM analysis — Dynamics 365 Commerce

Decision flow: choosing and documenting your date convention

The following steps give you a repeatable process for making and recording the date-field decision before any formula is written.

Step 1: Identify every date field available in your order export. Common candidates are created_at, shipped_at, delivered_at, and updated_at. Write down what each one represents in plain language.

Step 2: Ask which event you want Recency to reflect. If the answer is 'when did the customer decide to buy?', use the order creation date. If the answer is 'when did the customer receive the product?', use the delivery date, but only if that field is populated for all rows and is not derived from a carrier scan that updates retroactively.

Step 3: Check the time zone of each candidate field. If timestamps are stored in UTC, document the offset required to convert to your local calendar date and apply it consistently before extracting the date portion.

Step 4: Write the chosen field name, the time zone conversion applied, and the reference date into a comment or a dedicated documentation row at the top of the spreadsheet. This record is what allows another analyst — or yourself three months later — to reproduce the exact same values.

Step 5: Run the data-quality checks described in the previous section before computing any Recency values. Fix or flag errors first; do not let them propagate silently into MAX or DAYS.

Troubleshooting common errors

When text is passed directly to DAYS, Excel attempts DATEVALUE conversion and returns #VALUE! if it cannot parse a valid date. Parseable text is not automatically an error. Check the locale and day/month order. For this worksheet, convert all date cells to numeric values first and check with ISNUMBER(). Display formatting or alignment does not prove the stored value type, and MAX may ignore text without producing an error.

DAYS returns a negative number. Either the end_date and start_date arguments are reversed, or a future date has crept into the data. Recall that DAYS(end_date, start_date) subtracts start from end. If your reference date is end_date and a customer's last order is later than the reference date, the result is negative. Fix the data or add an IF check as described in the quality-checks section.

If MAX finds no numbers in its referenced range, it returns 0, including when every cell is blank or stored as text. Stop and inspect the date cells; do not interpret 0 as an actual purchase date. If you built the range by filtering customer IDs, verify it contains that customer's orders and that identifier types and spaces match your join rules.

Recency values differ slightly between runs on the same data. The most common cause is that the reference date was entered as =TODAY() rather than as a pinned DATE() formula. Replace it with the explicit date and the values will stabilize.

Two customers with identical order histories show different Recency values. Check whether the date field is consistently the same type for all rows. If some rows use the order date and others use the delivery date because the export joined two tables with different logic, the inputs are not comparable. Trace each row back to its source field and normalize before rerunning.

Sources: Microsoft Support: DAYS function — Excel · Microsoft: MAX function

Limitations and what this guide does not cover

This guide focuses exclusively on the Recency component of RFM and specifically on the date-selection and day-count steps. It does not cover how to assign quartile scores from the resulting day counts, how to handle refunded or cancelled orders, or how to define the analysis window for Frequency and Monetary calculations. Those topics each carry their own set of decisions and should be documented separately.

The worked example uses a single fictional customer with three orders. Real datasets introduce additional complications: customers with hundreds of orders where the MAX is obvious but the date-field quality varies row by row; customers whose most recent order was placed just before midnight UTC so the local date is the following day; and customers who appear under multiple IDs because they checked out as guests on some orders. None of these are resolved by the DAYS formula alone.

The reference date of 13 October 2026 is a teaching choice used to make the arithmetic in this guide checkable. It is not a claim about any application's current cutoff logic. Any tool you use for RFM analysis will manage its own reference date according to its own documented behavior, which you should verify before relying on the results.

Finally, raw Recency in days is an observed measure of elapsed time. It does not predict whether a customer will buy again, explain why they stopped, or guarantee that contacting them will produce a different outcome. Using Recency to prioritize outreach is a reasonable heuristic; treating it as a causal explanation for churn or retention is an inference the data cannot support on its own.

Sources and editorial method

Examples are fictional and contain no customer records. Prepared by the CasperBrain team with AI drafting assistance; facts and language are checked before publication.

• Shopify: RFM Analysis — Definition and Segmentation Guide (2026)
• Microsoft Support: DAYS function — Excel
• Microsoft Learn: Set up RFM analysis — Dynamics 365 Commerce
• Microsoft: MAX function
• Microsoft: COUNT function