Data Dictionary
Start typing to search…

SQL Example: Multi-Touch Landing Page Paths (Conversion & Bounce Metrics)

Overview

This query analyzes multi-touch session sequences leading up to a purchase. It reconstructs each user’s session history in order, showing up to the first five landing pages visited before the conversion event. Alongside touchpoints, it calculates engagement (page views), conversion rate, bounce rate, and revenue — helping you identify common paths to purchase, drop-off points, and high-value journeys.

SQL Query

WITH session_data AS
(
SELECT
  triple_id,
  session_id,
  landing_page,
  session_page_views,
  if(session_page_views = 1, true, false) AS session_bounce
FROM
  sessions_table s
WHERE
  event_date BETWEEN @start_date AND @end_date
),
orders_data AS
(
SELECT distinct
  session_id,
  order_revenue,
  orders_quantity
FROM
  pixel_orders_table po
WHERE
  event_date BETWEEN @start_date AND @end_date
),
combined_data_pixel AS
(
SELECT 
  s.triple_id AS triple_id,
  arrayReverseSplit(o -> o.orders_quantity > 0,
    arraySort(x -> x.session_id,
      groupArray((s.session_id AS session_id, s.landing_page AS landing_page, COALESCE(s.session_page_views, 0) AS session_page_views, COALESCE(s.session_bounce, 0) AS session_bounce, COALESCE(po.order_revenue, 0) AS order_revenue, COALESCE(po.orders_quantity, 0) AS orders_quantity))
    )
  ) AS sessions_array
 FROM session_data s LEFT JOIN
  orders_data po ON s.session_id = po.session_id
 GROUP BY 1
)
SELECT
  s.landing_page[1] AS landing_page_1,
  s.landing_page[2] AS landing_page_2,
  s.landing_page[3] AS landing_page_3,
  s.landing_page[4] AS landing_page_4,
  s.landing_page[5] AS landing_page_5,
  least(length(s), 5) AS touchpoints,
  SUM(length(s)) AS sessions,
  SUM(arraySum(s.session_page_views)) AS session_page_views,
  countDistinct(triple_id) AS users,
  SUM(arraySum(s.orders_quantity)) AS orders_quantity,
  SUM(arraySum(s.orders_quantity)) / SUM(length(s)) AS conversion_rate,
  SUM(arraySum(s.order_revenue)) AS order_revenue,
  SUM(arraySum(s.session_bounce)) AS bounces,
  SUM(arraySum(s.session_bounce)) / SUM(length(s)) AS bounce_rate
FROM combined_data_pixel ARRAY JOIN
  sessions_array AS s 
GROUP BY ALL
ORDER BY users DESC
limit 1000

Tables and Metrics Used

Tables

Dimensions

  • triple_id
  • session_id
  • landing_page
  • session_page_views

Measures

  • order_revenue
  • orders_quantity

Customization Options

This query builds per-user session paths within the selected date range and aggregates them by the first five landing pages. You can customize it in the following ways:

Extend the path length

  • Increase the number of landing page fields (landing_page_1, landing_page_2, etc.) to capture more touchpoints in the journey.

Focus on specific entry points

  • Filter in the WHERE clause of the session_data CTE, e.g.,
    WHERE landing_page LIKE '%sale%' to analyze only sessions starting on sale pages.

Limit to sessions with conversions

  • Add a filter in combined_data_pixel to only keep sessions where orders_quantity > 0.

Segment by geography or device

  • Join the sessions table with device or country fields and include them in SELECT and GROUP BY clauses for additional breakdowns.

Adjust bounce definition

  • Change the bounce condition in session_data to a different threshold (e.g., fewer than 2 page views) to match your business rules.

Updated 3 days ago