Which products bring customers back? First-purchase analysis for WooCommerce

A first-purchase analysis groups customers by the product in their first order, then checks how many of each group ordered again. WooCommerce doesn't have this report, so you build it from order data: an SQL query on the Analytics tables wc_order_stats and wc_order_product_lookup is the most direct way, or a spreadsheet from an order export.

Below: why this matters more than a best-seller list, how to run it, how to read the results, and what to change.

Why first products matter

Your best-selling products tell you what sells. A first-purchase analysis tells you what creates customers who come back, which is often a different list.

Say 300 customers started with your sampler pack and 120 of them ordered again (40%). Another 500 started with a discounted gift set, and 60 ordered again (12%). The gift set sells more, but the sampler pack builds your customer base. That's worth knowing before you decide which one to put in front of new visitors.

This is a simple form of cohort analysis: customers grouped by something they share (here, the first product) and followed over time.

What WooCommerce gives you out of the box

Core WooCommerce gets you partway:

• Analytics › Orders has a Customer type advanced filter. Combine it with a Products filter set to one product and Customer type set to new to see how many new-customer orders included that product.
• Analytics › Products shows units, net sales and orders per product, but not whether buyers were new or returning.
• WooCommerce › Customers shows each customer's order count, but not what they bought.

What's missing is the link between the two: of the customers whose first order contained product X, how many placed a second order? That needs order-level data.

The spreadsheet way

1. Export your orders for a long period (a year or more) with the customer, the order date and the products in each order. Check that your export has a column that identifies the customer, ideally the billing email.
2. Sort by customer, then by order date.
3. For each customer, mark their first order, and count their total orders.
4. Make a pivot table of first orders: rows are products, values are the number of customers, and the number of those customers with 2 or more orders.
5. Divide to get the share who came back, per product.

It works, but it's slow for a store with thousands of orders, and orders with several products need one row per product.

For developers: first-purchase analysis in SQL

The Analytics tables work the same with HPOS or posts storage. wc_order_stats has one row per order, and wc_order_product_lookup has one row per product line. This query needs MySQL 8.0+ or MariaDB 10.2+ for the window functions:

WITH ranked AS (
  SELECT order_id,
         customer_id,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY date_created, order_id) AS n,
         COUNT(*) OVER (PARTITION BY customer_id) AS total_orders
  FROM wp_wc_order_stats
  WHERE parent_id = 0
    AND status IN ('wc-completed', 'wc-processing')
)
SELECT p.post_title AS first_product,
       COUNT(DISTINCT r.customer_id) AS first_time_buyers,
       COUNT(DISTINCT CASE WHEN r.total_orders >= 2 THEN r.customer_id END) AS came_back,
       ROUND(100 * COUNT(DISTINCT CASE WHEN r.total_orders >= 2 THEN r.customer_id END)
             / COUNT(DISTINCT r.customer_id), 1) AS came_back_pct
FROM ranked r
JOIN wp_wc_order_product_lookup l ON l.order_id = r.order_id
JOIN wp_posts p ON p.ID = l.product_id
WHERE r.n = 1
GROUP BY l.product_id, p.post_title
HAVING first_time_buyers >= 20
ORDER BY came_back_pct DESC;

parent_id = 0 leaves out refund rows. HAVING first_time_buyers >= 20 hides products with too few first-time buyers to judge; adjust it to your volume. A customer whose first order had three products counts toward all three. Match the status list to the statuses your Analytics counts and change wp_ if your prefix differs.

A worked example: reading the output

Say the query returns these rows for a small coffee store:

The gift box is the most common first purchase, but most of those buyers were shopping for someone else and never returned. The sampler pack wins far fewer first orders but turns two in five buyers into regulars. A sensible response: feature the sampler pack to new visitors year-round, and treat gift box buyers as a separate group with a different follow-up (a reminder before next year's holidays, not a weekly coffee email).

How to read the results fairly

• Give customers time. Products launched recently will look worse because their buyers haven't had time to return. Limit the analysis to first orders more than a few months old, or compare products over the same period.
• Price and product type matter. Consumables naturally bring people back sooner than durable goods. Compare similar products with each other.
• Discounts change who buys. A product often sold with a coupon may draw bargain hunters. Check with the guide on whether a coupon paid off.
• Look at the gap too. A product whose buyers return in 30 days is different from one whose buyers return in a year. See how long customers take to order again.

What Askshelf can and can't tell you

Askshelf doesn't do first-purchase analysis. It doesn't follow individual customers from one order to the next or read customer details, which is a deliberate privacy choice, so it can't link a first product to later orders. More on that on the customer analytics use case page.

The nearest thing it can answer is which products are selling, and how that's changing:

Pair that with your first-purchase results: a product that both sells well and brings people back is the one to feature to new visitors.

What to do with the answer

• Lead with your "returner" products on the home page, in ads and in welcome offers.
• Fix or drop "one-and-done" products that sell but rarely create a second order, especially if they're often a first purchase.
• Use the gap. Send a follow-up to buyers of returner products just before their typical reorder time.
• Track the result in your repeat purchase rate.

For the rest of what WooCommerce can tell you about customers, see the WooCommerce customer analytics guide.