Overview

This query identifies which products customers buy most often in their second order, regardless of what they bought first. It focuses on retention behavior by isolating the second purchase in each customer journey, helping you find key products that drive early repeat purchases and long-term value.

SQL Query

WITH orders AS
(
  SELECT
    o.customer_id AS customer_id,
    COALESCE(o.created_at,date_add(o.event_date, INTERVAL o.event_hour HOUR)) AS created_at,
    arrayDistinct(arrayMap(x -> (x.product_id AS product_id, x.product_name AS product_name), o.products_info)) AS products,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at ASC) AS order_seq_number
  FROM
    orders_table o
  WHERE
    event_date BETWEEN @start_date AND @end_date
  QUALIFY
    MAX(is_new_customer) OVER (PARTITION BY customer_id)
)
SELECT
  p.product_id AS product_id,
  p.product_name AS product_name,
  count(1) AS orders
FROM orders
  ARRAY JOIN products AS p
WHERE order_seq_number = 2
GROUP BY ALL
ORDER BY orders DESC
LIMIT 100

Tables and Metrics Used

Table

• orders_table (Orders Table)

Dimensions

• customer_id
• event_date
• event_hour
• products_info.product_id
• products_info.product_name

Measures

• is_new_customer

Derived

• orders

Customization Options

This query helps identify which products drive early repeat purchases across your customer base. You can customize it in the following ways:

Focus on a different order number

• Change order_seq_number = 2 to another value (e.g. 3 or 4) to explore third or fourth purchases instead of second.

Restrict to a specific acquisition cohort

• To include only customers who were acquired during a specific time period (e.g. Q1 2024), add the following to the QUALIFY clause in the CTE:

MAX(is_new_customer) OVER (PARTITION BY customer_id) = 1 AND
MAX(CASE WHEN is_new_customer = 1 THEN event_date END) OVER (PARTITION BY customer_id) BETWEEN '2024-01-01' AND '2024-03-31'