One Order, Several Line Items: How to Count Purchase Frequency for RFM

A familiar problem at a fictional Thai online shop

Imagine a small Bangkok skincare shop — call it Glow Lab — that has been running for about two years and has accumulated a few hundred repeat customers. The shop manager exports order data into a spreadsheet to calculate RFM scores for the first time. She opens the file and immediately notices something puzzling: one customer, whose account shows three completed orders, appears on six separate rows. Another customer who placed only one order appears on a single row. Before she can score anyone for Frequency, she needs to understand why the row count and the order count are not the same number, and which one belongs in the F column.

In this example, counting product rows gives 6 while counting distinct completed orders gives 3. Those are different Frequency inputs, not two scores that can be assigned from this customer alone. Whether the mistake changes a quartile depends on the other customers and the scoring rules; it can then change which follow-up group the team considers.

This guide works through the definition, the arithmetic, and the practical steps needed to count Frequency correctly. All customer names, order IDs, and figures used as examples throughout are entirely fictional and created only to illustrate the concepts.

What Frequency actually measures in RFM

RFM analysis groups customers by three behavioral signals: how recently they bought, how often they buy, and how much they have spent. Frequency — the F in the middle — answers the question of how many purchases a customer made during the chosen analysis window. Standard RFM practice defines a purchase as an order event, not as an individual product chosen within that order. The Shopify RFM guide describes Frequency as the count of orders within the analysis period and lists the total number of orders as one of the three data inputs needed to run the model.

Why does the definition matter? Because the word 'purchase' can mean different things depending on the data structure in front of you. In everyday language, buying a moisturiser, a face wash, and a sunscreen at the same time feels like one shopping trip — one decision to open the website, fill the cart, and pay. That trip produces one order. Yet in an export file structured as one row per product, that single shopping trip appears as three rows. If you count rows instead of orders, you are measuring something closer to 'how many different products has this customer ever chosen' rather than 'how many times has this customer returned to buy'.

The distinction matters for segmentation because these two counts measure different customer behaviors. A customer who bought three products in one order on one occasion is behaviorally different from a customer who placed three separate orders on three separate occasions. Both might produce the same row count in a line-item export, but only the second customer has demonstrated repeated return visits to the shop — and return visits are what Frequency is designed to capture.

Sources: Shopify: RFM Analysis — Definition and Segmentation Guide · Shopify IN: RFM Analysis — Definition and Segmentation Guide

What a line item is and why exports are structured around them

A line item is a single product or product variant within an order, together with its quantity and pricing details. If a customer adds three different products to a cart and checks out once, the resulting order contains three line items — each with its own product name, SKU, quantity, unit price, and any applicable discounts. The Shopify API documentation describes the LineItem object precisely in this way: it represents one product or service purchased within an order, carrying fields such as quantity, unit price, variant title, and fulfilment status.

Platforms structure exports at the line-item level because line items are where the actionable detail lives. A merchant who wants to know which specific products were returned, which SKUs are running low, or which variants attract the most discount usage needs one row per line item — not one row per order. This is a sensible design for inventory and logistics purposes. It becomes a trap only when an analyst treats those rows as equivalent to order events without first checking which level the data represents.

The practical implication is straightforward: before you count anything for RFM, look at your export and determine whether each row represents one order or one line item. If rows represent line items, you must aggregate to the order level before calculating Frequency. The mechanism for doing this is grouping or deduplicating on the order identifier column, which is usually a field like order_id or order_number.

Sources: Shopify Admin GraphQL API: LineItem object reference

A six-row fictional export: seeing both counts at once

The table below is a completely invented teaching example for Glow Lab. It contains six rows drawn from three distinct orders placed by a single fictional customer named Malee (customer ID C-001). No real customer data appears anywhere in this example. Every order ID, product name, and amount is fabricated solely to illustrate the counting question.

Reading across the table, the six rows belong to three different orders. Order ORD-101, placed on 3 February, contained two line items: a toner (฿390) and a serum (฿890). Order ORD-118, placed on 15 April, contained three line items: a cleanser (฿290), a moisturiser (฿650), and a sunscreen (฿480). Order ORD-135, placed on 9 August, contained one line item: a face mask set (฿750). The export therefore has six rows, but Malee made three distinct purchase decisions on three distinct dates.

Now the two possible Frequency counts become visible. If you count spreadsheet rows, F = 6. If you count distinct order IDs, F = 3. Both numbers can be calculated from the same file, but only one of them counts what RFM Frequency is supposed to measure: the number of times Malee came back to buy. The value that belongs in her Frequency input is 3.

The business case for counting orders rather than line items

Order-count Frequency answers a specific business question: how many times did this customer choose to return? Each order represents one decision — the customer visited the shop, decided the value was there, committed to payment, and went through checkout. That is the behavioral signal that retention analysis cares about. A customer who returns three times is more engaged than a customer who visited once and happened to add many products to their cart, even if both produce the same total spend.

IBM's analytics documentation draws the same conceptual line: orders and items ordered are distinct metrics — one measures purchase events, the other counts products within those events. Conflating the two would mean that a customer who buys a gift bundle of ten items in a single visit would appear more 'frequent' than a customer who returns every month to buy one refill. That interpretation misrepresents the loyalty signal that Frequency is intended to capture.

There is also a practical consistency argument. If every analyst at Glow Lab agrees that Frequency means distinct order count, then every customer is measured on the same basis regardless of how many products they tend to put in a single cart. Customers who prefer variety bundles and customers who buy single items are all ranked by return visits. The moment different team members use different counting rules — one using rows, another using order IDs — the scores become incomparable and the segments unreliable.

Sources: IBM Support: Why is the Orders metric greater than Items Ordered?

How the two counts can produce different quartile outcomes

To see why the counting unit affects scoring, consider a simplified illustration using Glow Lab's fictional customer list. Suppose the shop uses four quartiles for each RFM dimension, following the convention where quartile 1 identifies the best performers and quartile 4 the least active — so a lower quartile number on Frequency means more frequent buying.

Malee's raw Frequency value is 3 orders if counted by distinct order IDs, or 6 if counted by line-item rows. Whether those two numbers land in the same quartile or different quartiles depends entirely on the distribution of all other customers' values. For a customer who places many small single-item orders, the order count could be high while the line-item count is nearly identical — placing her firmly in quartile 1 by either method. For a customer who places few large bundle orders, the line-item count could be many times the order count, potentially pushing her into a better-performing quartile under the line-item method than under the order-count method. The point is not that the two methods always disagree, but that they measure different things and will systematically diverge for customers whose order sizes vary widely across the customer base.

The practical risk is a misidentified segment. A customer who placed seven orders of one item each looks like a highly engaged repeat buyer on the order-count measure. That same customer produces only seven line-item rows — identical to the customer who placed one order with seven products — and the two land in the same quartile despite completely different purchase patterns. Order count preserves the behavioral distinction; line-item count erases it.

Apply the correct counting unit consistently across all customers

Once you have established that Frequency means distinct orders — not line-item rows — it is equally important to apply that definition uniformly across every customer in the same scoring run. The RFM model ranks customers relative to one another, so the scores are meaningful only because every customer is measured on the same basis. Introducing inconsistency in the counting unit corrupts the entire distribution, not just the affected records.

Shopify’s RFM guide discusses score bands and time periods. Before comparing scores between systems, check their scale, thresholds and analysis period. For this worksheet, also document which records count as eligible orders and apply that same counting unit to every customer.

If you calculate Frequency as distinct orders for customer A but accidentally use row count for customer B because her export happened to be structured differently, every comparative rank involving those two customers is corrupted. In practical terms, consistency means documenting your definition in writing before you start the calculation: the analysis window dates, the counting unit (distinct orders), which order statuses are included, how canceled orders are treated, and how refunded orders are treated. Whatever decisions you make must be applied uniformly across all customers in the same scoring run.

Sources: Shopify SG: RFM Analysis — Definition and Segmentation Guide

Step-by-step: calculating correct Frequency from a line-item export in a spreadsheet

The following steps assume you have exported your order data as a CSV with at least four columns: customer_id, order_id, order_date, and order_total (or equivalent names). Each row in the file represents one line item. These steps are written for a general spreadsheet application; adapt column references to match your actual field names.

Step 1: Open the export and read the field definitions. Inspect sample rows, including an order you can verify in the source. Repeated order_id values are a reason to investigate product lines, duplicate export rows, status history or adjustments; the repeated key alone does not identify the file’s grain. Confirm customer_id, order_id, order_date and the fields required by your eligibility rules before filtering and reconciling the records.

Step 2: Build a distinct-order table. Copy the customer_id, order_id, and order_date columns to a new sheet. Remove duplicate rows so that each combination of customer_id and order_id appears exactly once. In most spreadsheet tools you can use a Remove Duplicates function on the customer_id and order_id columns together, or use a pivot table grouped by both customer_id and order_id.

Step 3: Count distinct orders per customer. In the deduplicated sheet — where each row represents one order — use COUNTIF on the customer_id column to count how many rows belong to each customer. For example: =COUNTIF(customer_id_column, current_customer_id). The result is that customer's Frequency value. For Malee with three orders, this count returns 3.

Step 4: Set your analysis window before counting. If your window is the past 12 months, filter order_date to include only rows within that range before performing the count in Step 3. Orders outside the window should not contribute to the Frequency calculation even if the customer placed them.

Step 5: Record both the raw count and the order IDs for audit purposes. If a score later looks unexpected, having the list of order IDs that contributed to each customer's count lets you verify the arithmetic quickly without re-running the full export.

• Confirm whether your export is one row per order or one row per line item before starting any count.
• Deduplicate on the customer_id and order_id pair together to produce the distinct-order list; do not deduplicate on order_id alone unless you have confirmed that order_id values are globally unique across all customers in the export.
• Apply your analysis window date filter before counting — orders outside the window must be excluded.
• On the deduplicated sheet, use COUNTIF on the customer_id column (for example, =COUNTIF(customer_id_column, current_customer_id)) or a pivot table to count orders per customer; avoid summing a quantity column for this step.
• Document your window dates, included order statuses, and refund handling in writing before scoring.
• Store the list of order IDs per customer for post-analysis auditing.

Working with CasperBrain CSV exports

CasperBrain supports authorized customer CSV exports, with visible fields controlled by the requesting user’s permissions. Do not assume a customer export is a complete order or product-line history. Inspect the actual file’s counting unit, identifiers, date coverage and exclusions. To reproduce the raw-order example in this guide, use a verified source containing the necessary customer and order identifiers; a summary customer row alone is insufficient.

Repeated order_id values for one customer show repeated order keys; they do not by themselves prove product-line granularity. Check the source definition and sample rows to distinguish legitimate product lines from duplicate exports, status-history rows or adjustments. Apply the declared eligibility rules and reconcile the distinct customer/order pairs before counting. Count rows directly only after verifying that each remaining row represents one eligible order and each customer/order pair is unique.

Note that CasperBrain's own RFM scoring uses quartiles numbered 1 through 4, where 1 represents the best performers on each dimension. This scale is the opposite of some other tools that number their best segment as 5. If you are building a custom calculation outside CasperBrain and plan to compare results with CasperBrain scores later, verify which direction each scale runs before interpreting any composite code such as '114'. A '1' on Frequency in CasperBrain's convention means the most frequent buyers; a '1' on some five-level tools means the least frequent. These two conventions are not interchangeable.

Common mistakes and how to catch them early

One mistake to watch for is counting spreadsheet rows without checking what each row represents. Find a source order whose customer and product lines you can verify, then inspect all matching rows in the export. Multiple rows may reflect product lines, status changes, adjustments or duplicates. Resolve their meaning using source definitions and the declared eligibility rules before building the distinct customer/order pairs.

A second mistake is including canceled or fully refunded orders in the Frequency count without a deliberate policy decision. Your team should decide in advance which orders to include — for example, only paid and fulfilled orders, or all placed orders regardless of status — and document that decision clearly. Sources on RFM consistently call for using consistent rules for refunds and canceled orders rather than prescribing a single universal exclusion rule. Whatever rule you choose, apply it uniformly across all customers.

A third mistake arises when the analysis window is not applied consistently. If one analyst filters by order_date and another filters by payment_date or shipped_date, two runs on the same raw data will produce different Frequency counts for customers whose orders straddle a month boundary. Agree on a single date field and use it in every run.

A fourth mistake — less obvious but consequential — is importing RFM scores from one platform into another without checking whether the two platforms use the same scale direction. A customer scored '1' on Frequency by a platform that uses 1 = least frequent will appear to be a top performer if imported into CasperBrain, where 1 = most frequent. Always recompute scores natively on the target platform rather than carrying over numbers from a system with a different convention.

• Spot-check: find a customer with one known multi-product order and confirm their row count in the export equals 1 after deduplication.
• Decide in advance which order statuses count as purchase events; document and apply that rule uniformly across all customers.
• Use one date field consistently — choose order_date, payment_date, or shipped_date and apply it across every scoring run.
• Never import raw RFM scores across platforms with different scale directions; recompute on the target platform.
• After scoring, sanity-check the top-quartile customers: their names should be recognizable as genuinely frequent buyers from your own experience with the shop.

Limitations of order-count Frequency and when to reconsider

Counting distinct orders is the standard definition of RFM Frequency and it handles most retail scenarios well. However, it has known limitations worth acknowledging so you can decide whether the standard approach fits your specific shop.

Order count does not capture purchase quantity within an order. A customer who consistently buys 10 units of a product per order looks identical to one who buys 1 unit per order, as long as both place the same number of orders. If unit volume per order is strategically important to your shop — for example, if you supply small businesses that restock in bulk — you may want to track average units per order as a supplementary metric alongside the standard Frequency count, rather than replacing it.

Order count also does not distinguish between orders of very different monetary sizes. That distinction is handled by the Monetary dimension of RFM, not by Frequency. Keep the dimensions separate and let each measure do its own job. Trying to encode monetary information into the Frequency count by weighting orders by their value contradicts the original model design and makes the scores harder to interpret.

Finally, Frequency is inherently sensitive to the analysis window. A 12-month window captures repeat buyers who return within a year; a 6-month window will produce lower Frequency scores for the same customers. There is no universally correct window length. Choose a window that reflects your shop's typical repurchase cycle — a monthly consumable shop and an electronics shop require different windows — and use the same window every time you recalculate so that scores across periods are comparable.

Putting it all together: a decision checklist before you score

Before you assign a single Frequency value to any customer, work through the following decisions in order. Each decision affects the number you will calculate, so resolving them before touching the data is far more efficient than discovering inconsistencies after the scores are already in use.

First, confirm your analysis window: a specific start date and a specific end date, with a documented date field to filter on. Second, confirm your export structure: is each row one order or one line item? If line items, plan the deduplication step on the customer_id and order_id pair. Third, decide which order statuses count as a purchase event and which are excluded — this is a store-specific policy choice, not a universal rule. Fourth, decide how to handle partial refunds: does an order with one refunded line item still count as one purchase event? Fifth, verify the Frequency scale direction of any tool you will use to score the deduplicated counts, particularly if you are comparing results across platforms.

Once those five decisions are documented, the calculation itself is mechanical. For Glow Lab's fictional customer Malee, the output is: 3 distinct orders in the analysis window, which is the number that belongs in the Frequency column — not the 6 that appears as the raw row count in the line-item export. That single digit difference, multiplied across hundreds of customers, is the difference between a segmentation that reflects real shopping behavior and one built on a data artifact.

• Set and document the analysis window start date, end date, and filter field before opening the export.
• Identify whether the export is one row per order or one row per line item.
• Deduplicate on the customer_id and order_id pair if the export is line-item level; verify that the distinct count matches known order history for at least one test customer.
• Define which order statuses are included as a deliberate store-specific policy and apply that rule uniformly.
• Decide how partial refunds affect order inclusion and document the decision.
• Confirm the Frequency scale direction (1 = best or 1 = worst) of any scoring tool before entering calculated values.

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
• Shopify Admin GraphQL API: LineItem object reference
• IBM Support: Why is the Orders metric greater than Items Ordered?
• Shopify IN: RFM Analysis — Definition and Segmentation Guide
• Shopify SG: RFM Analysis — Definition and Segmentation Guide