RFM analysis for products: what the scores can tell you

RFM analysis for products compares each SKU's recency, order frequency, and sales value over a stated period. It turns order history into a compact commercial review list, with separate scores for recent activity, repeated orders, and revenue. The scores help decide what to investigate; inventory and profitability decisions need additional evidence.

Define the product version of RFM

The three measures answer different questions. Recency asks when the product last sold. Frequency asks how often distinct orders included it. Monetary value asks how much net sales those lines generated during the review window.

Product recency = Review date − Latest eligible sale date

Product frequency = Distinct eligible order IDs containing the SKU

Product monetary value = Sum of eligible net sales for the SKU

Lower recency is more recent; higher frequency and monetary value indicate more recorded activity. The direction matters when assigning scores. A product sold yesterday should receive a stronger recency score than one last sold months ago under the same rules.

The established customer RFM method uses customers as its grouping unit. Here the grouping unit is the SKU. Confirm that identity first: product taxonomy for analytics explains why product families, lines, and individual SKUs should not be mixed in one comparison.

Start with the order lines

A US distributor reviews eligible sales from July 1 through September 30, 2026, using September 30 as the review date. The table contains posted sales after line discounts, with no tax or freight included. This example has no returns or cancellations; those require an explicit treatment in a real export.

SKU A appears on four lines but only three distinct orders. Both lines on A03 belong in sales value; that order contributes once to frequency. Deduplicating the sales table by order and SKU would discard valid revenue, so keep a separate distinct-order calculation.

Calculate the measures before scoring

In Google Sheets, place SKU in column A, order ID in B, sale date in C, and net sales in D, with the detail rows in 2:11. Create a summary with SKU codes A through D in H2:H5. Dates must be actual date values.

Find the latest sale date in I2 and fill down:

=MAXIFS($C$2:$C$11,$A$2:$A$11,H2)

Google's MAXIFS documentation describes taking a maximum after applying criteria. With the source already restricted to the review window, the criterion selects the SKU's sale dates.

Calculate recency in J2:

=DATE(2026,9,30)-I2

Calculate frequency in K2 and net sales in L2:

=COUNTUNIQUE(FILTER($B$2:$B$11,$A$2:$A$11=H2))

=SUMIFS($D$2:$D$11,$A$2:$A$11,H2)

The SUMIFS documentation explains summing a range according to criteria. Fill these formulas down for the summary SKUs. Covirage is not affiliated with Google.

The sales proof is 1,500 + 2,000 + 350 + 500 = 4,350, matching the source lines. Frequency totals nine distinct SKU/order combinations. In this small example every order contains one SKU; with mixed-SKU orders, that total would exceed the company-wide distinct order count. Do not sum recency days across products.

Assign explicit score bands

The distributor chooses these illustrative bands for this review. They are business rules for the example, not industry benchmarks or a recommendation for every catalog.

Apply the bands to the measured table:

Keep the code as a label rather than adding or multiplying its digits. A is active across all three measures. B generated the most sales but has not sold recently. C has recent orders at a smaller value, while D has older sales and a middle monetary score.

Now add one eligible C order dated September 30 for $1,200. C's recency becomes zero days, frequency becomes three orders, and sales become 350 + 1,200 = $1,550. Its code changes from 321 to 333, and total sales become 4,350 + 1,200 = $5,550. A and C now share a code despite different histories; the underlying measures must remain visible.

Decide who acts on the result

Sales operations owns the order definitions, product keys, and reconciliation. The product manager approves score thresholds and review questions. Finance confirms net sales treatment, while purchasing brings stock availability, supplier lead times, minimum orders, and service obligations to any stocking discussion.

B's score warrants a conversation about timing, customer concentration, and availability. It does not prove fading demand: the product may have been unavailable or bought on a planned cycle. Compare the results with product-tail revenue concentration, and use RFM alternatives when scoring hides the business question.

Where it goes wrong

• Line counts used as frequency. Split lines inflate repeated-order activity.
• Short windows applied to all products. Seasonal items, new launches, and long-cycle products have different selling opportunities.
• Stockouts read as disinterest. An unavailable SKU cannot demonstrate its unconstrained demand through recorded sales.
• Returns treated as new sales. A credit can reduce monetary value without making the last genuine sale more recent.
• Unscored products forced into a band. Catalog SKUs with no eligible sales need a separate status. Do not invent a last-sale date or a positive frequency.

Review your own SKU history in Covirage

Upload your order-line file or Google Sheet; Covirage maps the columns, and its tools compute and check last-sale dates, distinct-order counts, and sales totals. The external AI model explains the results with every figure traced to its rows. Create a free Covirage account, then set up your business and bring a sample of your own data.

See Covirage for distributors for the supporting approach to product and sales analysis from business files.