Overview

This query analyzes customers who placed more than one order but have not placed another since. It captures the time between each customer's final two purchases, helping you identify customers who may have slowed down or stopped purchasing. This can reveal patterns in post-repeat behavior and support churn risk modeling or winback strategies.

SQL Query

SELECT
  o.customer_id AS customer_id,
  o.order_id AS order_id,
  COALESCE(o.created_at,date_add(o.event_date, INTERVAL o.event_hour HOUR)) AS created_at,
  any(order_id) OVER (PARTITION BY customer_id ORDER BY created_at ASC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prev_order_id,
  any(created_at) OVER (PARTITION BY customer_id ORDER BY created_at ASC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prev_created_at,
  round(dateDiff('hour', prev_created_at, created_at)/24,2) AS day_diff,
FROM
  orders_table o
WHERE
  event_date BETWEEN @start_date AND @end_date
QUALIFY
  prev_order_id IS NOT NULL
  AND any(order_id) OVER (PARTITION BY customer_id ORDER BY created_at ASC ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) IS NULL
ORDER BY created_at DESC

Tables and Metrics Used

Table

• orders_table (Orders Table)

Dimensions

• customer_id
• order_id
• created_at
• event_date
• event_hour

Customization Options

This query helps analyze time between final and prior purchases, offering insights into customer repeat behavior or drop-off risk. You can customize it in the following ways:

Analyze deeper order history

• Expand the window frame to ROWS BETWEEN 2 PRECEDING AND 2 PRECEDING to examine time between the last and third-to-last orders instead of just the final two.

Focus on specific customer types

• Add a filter like AND is_new_customer = 0 to limit analysis to returning customers only
• Add AND created_at >= '2024-01-01' to the WHERE clause to narrow analysis to customers active in a specific period.

Compare order value

• Include fields such as order_revenue or order_quantity for both current and previous orders to measure changes in value or basket size.

Change analysis period

• Adjust @start_date and @end_date to evaluate behavior over different time windows (e.g. peak seasons vs. off-season).