Why the first product decides so much of the second order
The repeat purchase guide calls the second order the hardest and describes the output built here: a short list of first-purchase products, each paired with the purchase that most often follows it and the median time before it, read within a window matched to the category's cycle. The e-commerce email and SMS flows guide times replenishment from that gap and the guide to email for a retention objective asks for the time between orders split by first product, so much of the retention section assumes the table exists. Its neighbours answer other questions: the cohort analysis tutorial groups customers by when they first bought, and the product and merchandising analysis guide finds products bought together in one basket. The first-product table groups customers by what they bought first and reads their next order.
| Question the table answers | What it reads | Where the answer is used |
|---|---|---|
| Which first products bring customers back? | The second-order rate within the window, by first product | Entry products and acquisition targets |
| How long do returning customers take? | The median gap to the second order and its spread | Replenishment and post-purchase timing |
| What do they buy next? | The contents of the second order, named by path | Cross-sells, inserts and post-purchase content |
| Is a difference the product's or the customer's? | The same groups split by channel and first-order promotion | Which findings to act on |
The easiest way to get it wrong is to start from customers who came back. A list of what returning customers bought second is easy to pull and describes only the survivors; the customers who never returned are the other half of every rate, and the survivorship bias framework is the reason the steps below keep them in.
Before you start
- The order history, from the order system, long enough for the oldest first-time customers in it to have completed the window. Analytics data is a cross-check, not the record.
- A written definition of a customer and a first purchase. The guide to new and returning customers sets out the decisions, from refunded first orders to matching guest checkouts.
- A sense of the category's buying cycle, from the store's own re-order data. It decides the window.
- A spreadsheet or SQL access. Sorting, counting and comparing each order with the one before it are enough; the SQL for marketers guide shows the patterns.
- The people who will act on it: the owners of the flows, merchandising, the parcel and acquisition.
The steps
Decide the question, the window and what counts as an order
Write the question down before exporting anything: for customers whose first order contained a given product, what share ordered again within the window, after how long, and what did they buy? Choose a window long enough to cover the category's natural buying cycle, apply it the same way to every customer, and measure each customer from their own first order rather than by calendar period; the repeat purchase rate entry explains why a calendar period makes the repeat purchase rateShare of customers who placed more than one order in the period. follow the timing of first orders instead of loyalty. A store selling both consumables and durables may need a window per category. Decide also what counts as an order (a free replacement, an exchange, an order cancelled before dispatch) and whether marketplace orders that cannot be tied to a customer are in, and write the decisions down for the next run.
Export the orders with a customer identifier and nothing more
Take one row per order line from the order system: a customer identifier, the order number and date, the product and its line or category, quantity, line value, status, any discount code, and the order's source where the system records one. Leave out names, email addresses, phone numbers and postal addresses. The GDPR sets data minimisation among its principles, under which personal data must be adequate and relevant to its purpose and no more than that purpose requires, as the guide to keeping personal data out of analytics explains, and a table bound for merchandising and acquisition needs none of them. An internal customer number is still personal dataAny information relating to an identified or identifiable living person, such as a name, email address, location data or an online identifier., so handle the file like other customer records and delete it when the analysis is finished.
Join guest and account orders and remove orders that did not stand
Guest checkout breaks the link between a customer's orders, as the guest checkout entry notes, so one person can appear as several first-time buyers and repeat figures read low. Match guest orders to accounts in the order system, on the business's own records such as the email address, never in an analytics tool. Then apply the rule for orders that did not stand: the new and returning customers guide keeps a customer new until a first order is kept, so a cancelled or fully refunded first order does not start a window. Keep partially refunded orders, minus the refunded lines. Google's developer guide documents refund events that carry the original transaction identifier, as the e-commerce tracking tutorial describes, so analytics can check the order system's refund count.
Number each customer's orders and mark the first
Sort the rows by customer and date and give each customer's orders a sequence: first, second, third. In a spreadsheet this is a sort and a running count per customer; in SQL it is the SQL guide's ranking pattern for numbering each customer's orders, with a comparison against the previous order giving the gap between them. Two orders placed by the same customer on the same day are often one purchase split at the checkout, such as a forgotten item; decide whether to merge them, and apply the rule to everyone. Keep only customers whose first order falls inside the export, so that someone who first bought before it began is not mistaken for a newcomer.
Group first orders by product, line or basket type
A first order often holds several products, so decide what the first product means before counting. Three groupings work: the lead product (the highest-value line, or the one the order's source shows the customer came for), the product line or category, or the basket type (a single item, a starter set or bundle, several full-size items). Use one grouping per table and record which. A wide range with few customers per product reads better by line or category, with single products broken out only where a group is large. Keep trial sizes, starter sets and gift sets as groups of their own, because their buyers are in a different position from someone buying the full product for themselves.
Count second orders within the window for every group, keeping customers who never returned
For each group, count the first-time buyers whose window has closed, then those among them who ordered again inside the window; the second count divided by the first is the group's second-order rate. Every first-time buyer belongs in the denominator, including those who never came back, or the table shows what returners bought while hiding how many returned at all. Customers whose window is still open wait for the next run. Record the count beside each rate, because a rate from a small group moves a long way with each customer.
Measure the gap to the second order as a median and a spread
For the customers who returned, measure the time from the first order to the second. Report the median rather than the average, because a few very late returners pull an average away from the typical customer, and add the spread: the range within which the middle half of returners fall. The median is what the flows guide times a replenishment flowAutomated messages that remind a customer to re-order a consumable, timed to the product's re-order cycle from the store's own data, not a fixed delay. from, with the prompt landing slightly before it. The spread decides what kind of prompt fits. Gaps that cluster tightly mark a line as a candidate for a subscription, as the subscribe-and-save guide and the comparison of a loyalty programme with subscribe-and-save set out; where gaps scatter, both send the line to a reminder instead, and a very wide spread warns that a reminder timed to the median will reach many customers too early or too late. Where the history allows, measure later gaps too: the rhythm after the second order is the purchase frequencyAverage number of orders each customer placed in the period. the flows will meet.
Record what the second order contained and name the path
Cross-tabulate each first-product group against the line or category of the second order, then name the path each common pairing represents, using the types the repeat purchase guide sets out: replenishment (the same product again), a consumable for a durable, completion of a set, a move to another category, an occasion that recurs, or an upgrade or replacement. Naming the path matters because each calls for a different action: a timed reminder, the consumable shown at the first purchase, the next piece of the set, or a "goes with" recommendation for cross-sellingOffering a customer a related product alongside the one they are buying; upselling offers a higher-value version instead.. Products bought together in one basket are a different question, answered by the export query in the merchandising analysis guide; this step reads the next order, not the same one.
Check each group against channel and first-order promotion
A first product's rate can belong to the customers it attracted rather than to the product. Split the largest groups by acquisition channel and by whether the first order carried a discount code or a sale price: the repeat purchase rate entry notes that customers first won with deep discounts or broad prospecting tend to come back less often, and the guide to measuring a sale or promotion reads who a promotion brought in separately. If a product's low rate comes almost entirely from customers who first bought it in a sale, the finding is about the sale; if one channel's customers return whatever they bought first, it is about the channel. Stock-up offers distort the gap too, because the repeat purchase guide warns that they push the next order further away.
Turn each finding into a brief and repeat on a schedule
Write each finding for the person who will act on it. The retention owner gets the timing and the next product for the post-purchase flow and the replenishment flow. Merchandising gets the pairs to show together, and the parcel the one ask that fits the path, as the packaging and order inserts guide describes. Acquisition gets the first products that bring customers back: the guide to choosing which products to put marketing behind calls them entry products and judges them on the second order, and the shopping and catalogue ads guide accepts a first-order target below break-even only where repeat margin is measured rather than assumed. This table is that measurement, and the second order's margin is the first instalment of lifetime valueTotal value a customer brings over time.. Re-run the table with the store's retention review and whenever the range, the flows or the acquisition mix change, keep every run for comparison, and use the customer retention audit to check that the actions are live.
Reading the first-product table
Read each row against the store's own earlier runs, not against another store's figures, and read small groups as the guide to reading small numbers advises: a difference between two small groups is a question for the next run, not a finding. The patterns below are the common ones and the first thing each suggests.
| What the row shows | What it usually means | First thing to do |
|---|---|---|
| A high rate, a tight gap and the same product again | Replenishment | Time a reminder just before the median gap; weigh a subscription |
| A moderate rate, a longer gap and a consumable for the first product | A consumable for a durable | Show the consumable with the first purchase and again at the gap |
| The next order from the same range | Completion of a set | Show the set on the page and make the next piece the obvious add |
| The next order from another category | A move across categories | Recommend the pair in post-purchase content and on inserts |
| A low rate and widely scattered gaps | No natural next purchase, or a first experience that disappoints | Read the product's reviews and return reasons before spending on retention |
| A high rate only among one channel's customers | The channel, not the product | Judge the channel on the customers it brings |
| A low rate only for first orders taken on a discount | Promotion buyers, not the product | Judge the promotion on its repeat buyers |
Two cautions apply to every row. A cohortA group of customers who share a starting event and period, tracked together over time. of first-time buyers is only fair to compare with another once both have had the full window, so a recent launch cannot yet be judged against an established product. And the table describes what happened under the current flows, offers and range; when one changes, the next run tests whether the change worked. The same export, read by recency, frequency and value, also supports the scoring the RFM segmentation framework describes.
A pen and ink shop maps its customers' second orders (illustrative)
Pilvenzo Pens reads two years of first orders
Pilvenzo Pens, an invented online shop selling fountain pens, bottled inks, ink cartridges and notebooks, exports 24 months of order lines with a customer number in place of names and addresses. It matches guest orders to accounts by email inside the order system, removes 61 first orders that were cancelled or fully refunded, chooses a 180-day window and keeps the 4,820 first-time customers whose window has closed.
| First product group | First-time buyers | Second order within 180 days | Median gap | Middle half of gaps | Most common second purchase |
|---|---|---|---|---|---|
| Fountain pen | 1,540 | 34% | 71 days | 48 to 104 days | Bottled ink |
| Bottled ink | 1,210 | 41% | 52 days | 35 to 80 days | Bottled ink in another colour |
| Ink cartridges | 690 | 48% | 38 days | 32 to 45 days | The same cartridges |
| Notebook | 1,050 | 12% | 96 days | 60 to 150 days | Notebook |
| Gift set | 330 | 9% | 140 days | 90 to 170 days | Fountain pen |
Split by first-order discount, pen buyers who used the 20% welcome code come back at 19% against 39% for pen buyers who paid full price. The shop moves its ink reminder for pen buyers to day 60, offers a cartridge subscription from day 30, adds an ink sample card to every pen parcel, moves prospecting spend from notebooks to cartridges and ink, and limits the welcome code to the second order. Once the pen buyers who first ordered after the changes have completed their 180-day window, the next run shows full-price pen buyers returning at 42%; meanwhile cartridge subscribers already make up 15% of cartridge customers.
Common mistakes
- Starting from the customers who came back. Second purchases without the buyers who never returned show what survivors bought and nothing about how many returned.
- Calendar windows. Late first orders get less time to return, so the rate follows their timing, not loyalty.
- Analytics users instead of customers. Devices, unmatched guest orders and missing refunds make analytics a check, not the record.
- Averages for gaps. A few very late returners stretch the average; the median and the spread describe the typical customer.
- Counting a multi-item first order several times. Pick one first product or a basket type per customer.
- Reading a promotion or a channel as a product effect. Split the large groups before acting on a product's rate.
- Acting on a handful of customers. Combine small groups or wait for the next run.
- Running the table once. Its value is in the comparison between runs, after the flows, range or acquisition mix have changed.
