Path to Conversion

1 · Business question

Which touchpoint sequences occur before a purchase conversion? Where does Sponsored Display act as an assist?

2 · Query (SQL)

SELECT
  path,
  COUNT(*) AS conversions,
  ROUND(AVG(days_to_convert), 2) AS avg_days_to_convert
FROM (
  SELECT
    user_id,
    CONCAT_WS(' > ', COLLECT_LIST(touchpoint_channel ORDER BY event_time)) AS path,
    DATEDIFF(MAX(event_time), MIN(event_time)) AS days_to_convert
  FROM amazon_attributed_events
  WHERE event_type = 'purchase'
  GROUP BY user_id
) sub
GROUP BY path
ORDER BY conversions DESC
LIMIT 50;

3 · Interpretation

Paths with Display-early / Sponsored-Brands-late show Display working at the top of funnel. Align lower-funnel budget accordingly.