The *_paths tables give you insight into the interactions users have with the media you manage using Display & Video 360 and Campaign Manager 360. These interactions (also known as touchpoints) are stitched together into event paths, chronicling the journey that a group of users takes through your conversion funnel. The data in the *_paths tables uses the same data that's available to you via Data Transfer, but comes to you in preassembled paths. Event paths are available for both converting and non-converting users and can include multiple conversions.
Just as with all user data in Ads Data Hub, data from the *_paths tables is subject to aggregation requirements.
Technical details
Each row of paths contains a single customer journey over a 30-day window. This data is updated daily to cover the last 30 days. Event paths are assembled using cookies, and therefore are limited to a single device. In certain situations, such as when users have opted out of ads personalization, user IDs will be set to 0. Normally, these users will still be separated by row, so 2 distinct conversion paths keyed to a user ID of 0 will have distinct rows. However, for certain types of analysis — such as grouping by user_id = 0 — multiple conversion paths will be merged into one, which could create discrepancies in your data. Learn more about zeroed user IDs
Each row of the *_paths.events column contains an array of structs where each struct is a single event in the user's path. The structs that make up the path are ordered by timestamp, with the first event in the array being the oldest.
Sample queries
The following queries measure the impact of placements towards a particular user segment converting. The queries attribute credit using 3 different attribution models:
- First-touch. (All credit goes to the first touchpoint.)
- Last-touch. (all credit goes to the last touchpoint.)
- Linear. (Credit is divided evenly among the touchpoints.)
Sample data
| Row | user_id |
*_paths.events.event_time |
*_paths.events.event_type |
*_paths.events.placement_id |
|---|---|---|---|---|
| 1 | 1 | 1563384139 | FLOODLIGHT |
null |
| 1563384129 | CLICK |
11 | ||
| 1563384119 | VIEW |
22 | ||
| 2 | 2 | 1563384139 | FLOODLIGHT |
null |
| 1563384129 | VIEW |
11 | ||
| 1563384119 | FLOODLIGHT |
null | ||
| 1563384109 | VIEW |
11 |
Sample queries
First-touch
/* Substitute *_paths for the specific paths table that you want to query. */
SELECT
(
SELECT
attributed_event_metadata.placement_id
FROM (
SELECT
AS STRUCT attributed_event.placement_id,
ROW_NUMBER() OVER(ORDER BY attributed_event.event_time ASC) AS rank
FROM
UNNEST(t.*_paths.events) AS attributed_event
WHERE
attributed_event.event_type != "FLOODLIGHT"
AND attributed_event.event_time < conversion_event.event_time
AND attributed_event.event_time > (
SELECT
IFNULL( (
SELECT
MAX(prev_conversion_event.event_time) AS event_time
FROM
UNNEST(t.*_paths.events) AS prev_conversion_event
WHERE
prev_conversion_event.event_type = "FLOODLIGHT"
AND prev_conversion_event.event_time < conversion_event.event_time),
0)) ) AS attributed_event_metadata
WHERE
attributed_event_metadata.rank = 1) AS placement_id,
COUNT(*) AS credit
FROM
adh.*_paths AS t,
UNNEST(*_paths.events) AS conversion_event
WHERE
conversion_event.event_type = "FLOODLIGHT"
GROUP BY
placement_id
HAVING
placement_id IS NOT NULL
ORDER BY
credit DESC
Last-touch
/* Substitute *_paths for the specific paths table that you want to query. */
SELECT
(
SELECT
attributed_event_metadata.placement_id
FROM (
SELECT
AS STRUCT attributed_event.placement_id,
ROW_NUMBER() OVER(ORDER BY attributed_event.event_time DESC) AS rank
FROM
UNNEST(t.*_paths.events) AS attributed_event
WHERE
attributed_event.event_type != "FLOODLIGHT"
AND attributed_event.event_time < conversion_event.event_time
AND attributed_event.event_time > (
SELECT
IFNULL( (
SELECT
MAX(prev_conversion_event.event_time) AS event_time
FROM
UNNEST(t.*_paths.events) AS prev_conversion_event
WHERE
prev_conversion_event.event_type = "FLOODLIGHT"
AND prev_conversion_event.event_time < conversion_event.event_time),
0)) ) AS attributed_event_metadata
WHERE
attributed_event_metadata.rank = 1) AS placement_id,
COUNT(*) AS credit
FROM
adh.*_paths AS t,
UNNEST(*_paths.events) AS conversion_event
WHERE
conversion_event.event_type = "FLOODLIGHT"
GROUP BY
placement_id
HAVING
placement_id IS NOT NULL
ORDER BY
credit DESC
Linear
/* Substitute *_paths for the specific paths table that you want to query. */
SELECT
attributed_event_metadata.placement_id AS placement_id,
/* Give equal credit to all attributed events */
SUM(SAFE_DIVIDE(1, ARRAY_LENGTH(attributed_events_metadata)))
FROM (
SELECT
ARRAY(
SELECT
AS STRUCT attributed_event.placement_id,
ROW_NUMBER() OVER(ORDER BY attributed_event.event_time DESC) AS rank
FROM
UNNEST(t.*_paths.events) AS attributed_event
WHERE
attributed_event.event_type!="FLOODLIGHT"
AND attributed_event.event_time < conversion_event.event_time
AND attributed_event.event_time > (
SELECT
MAX(prev_conversion_event.event_time) AS event_time
FROM
UNNEST(t.*_paths.events) AS prev_conversion_event
WHERE
prev_conversion_event.event_type="FLOODLIGHT"
AND prev_conversion_event.event_time < conversion_event.event_time)) AS attributed_events_metadata
FROM
adh.*_paths AS t,
UNNEST(*_paths.events) AS conversion_event
WHERE
conversion_event.event_type="FLOODLIGHT" ),
UNNEST(attributed_events_metadata) AS attributed_event_metadata
GROUP BY
1
HAVING
placement_id IS NOT NULL
ORDER BY
2 DESC
Results
If you were to run the query on the sample data, you would get these results out:
First-touch
| row | placement_id |
credit |
|---|---|---|
| 1 | 11 | 2 |
| 2 | 22 | 1 |
Last-touch
| row | placement_id |
credit |
|---|---|---|
| 1 | 11 | 3 |
Linear
| row | placement_id |
credit |
|---|---|---|
| 1 | 11 | 2.5 |
| 2 | 22 | 0.5 |