Pfad zur Conversion (Path to Conversion)

1 · Business-Frage

Welche Touchpoint-Reihenfolge fuehrt vor einer Kauf-Conversion? Wo greift Sponsored Display als Assists?

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

Pfad-Muster mit Display-Early / Sponsored-Brands-Late zeigen, dass Display fuer Awareness oben in der Funnel-Spitze arbeitet. Budget fuer untere Funnel-Stufen darauf abstimmen.