SQL Example: Top First Purchased Products (Count, AOV, LTV…)

Overview

This query shows which products are most commonly included in a customer's first purchase and how those products correlate with customer value over time. It ranks the top 10 first-purchased products based on customer count, showing each product’s Average Order Value (AOV) and customer Lifetime Value (LTV) over 60, 90, 180, and 365 days.

SQL Query

WITH src_tbl AS
(
  SELECT
    customer_id,
    arrayDistinct(arrayMap(x -> (x.product_id as product_id, x.product_name as product_name), groupArrayArray(if(is_new_customer AND event_date BETWEEN '2024-01-01' AND '2024-03-31',products_info,array())))) AS products_info_first_orders,
    groupUniqArray( (event_date, order_id, order_revenue) ) AS orders,
    any(if(is_new_customer AND event_date BETWEEN '2024-01-01' AND '2024-03-31', event_date, null)) AS first_order_date,
    any(if(is_new_customer AND event_date BETWEEN '2024-01-01' AND '2024-03-31', order_revenue, null)) AS first_order_order_revenue
  FROM
    orders_table
  WHERE
    event_date BETWEEN '2024-01-01' AND '2025-03-31'
  GROUP BY 
    customer_id
  HAVING 
    first_order_date IS NOT NULL
)
SELECT
  pi.product_id AS first_product_id,
  pi.product_name AS first_product_name,
  count(1) AS count,
  avg(first_order_order_revenue) AS aov,
  avg(arraySum(arrayMap((d, o, r) -> if(dateDiff('day', first_order_date, d) <= 60, r, 0), orders))) AS avg_ltv_60,
  avg(arraySum(arrayMap((d, o, r) -> if(dateDiff('day', first_order_date, d) <= 90, r, 0), orders))) AS avg_ltv_90,
  avg(arraySum(arrayMap((d, o, r) -> if(dateDiff('day', first_order_date, d) <= 180, r, 0), orders))) AS avg_ltv_180,
  avg(arraySum(arrayMap((d, o, r) -> if(dateDiff('day', first_order_date, d) <= 365, r, 0), orders))) AS avg_ltv_365
FROM 
  src_tbl ARRAY
  JOIN products_info_first_orders AS pi
GROUP BY
  first_product_id, first_product_name
ORDER BY
  count DESC
LIMIT 10

Tables and Metrics Used

Table

orders_table (Orders Table)

Dimensions

customer_id
event_date
order_id
products_info.product_id
products_info.product_name
is_new_customer

Measures

order_revenue

Customization Options

This query tags campaigns based on whether their name contains specific keywords, using arrayFirst to assign the first matching keyword. You can customize it in the following ways:

Change matching logic

Replace LIKE with ILIKE for case-insensitive keyword matching.

Allow multiple matches

Use arrayFilter(...) instead of arrayFirst(...) to tag campaigns with all matching keywords instead of just the first one.

Switch ad dimension

Replace campaign_name with ad_name, adset_name, or account_name to group by a different level of the ad hierarchy.

Adjust fallback label

Change 'Other Campaigns' to a different default label (e.g., 'Unmatched', NULL) for campaigns that don’t match any keyword.

Updated 4 days ago