使用路径表的归因建模

通过 *_paths 表，您可以深入了解用户与您使用 Display & Video 360 和 Campaign Manager 360 管理的媒体的互动情况。这些互动（也称为“接触点”）会拼接成事件路径，可记录一组用户在整个转化漏斗中所经历的历程。*_paths 表中的数据与您通过数据传输功能获取的数据相同，不过会在预先组合的路径中提供给您。事件路径适用于已转化用户和未转化用户，并且可以包含多次转化。

正如广告数据中心内的所有用户数据一样，*_paths 表中的数据也需要遵守汇总要求。

技术详情

每一行 paths 都包含一位客户在 30 天的时间范围内的转化历程。这些数据每天更新一次，以涵盖过去 30 天的数据。事件路径是使用 Cookie 组合而成的，因此仅限于单一设备。在某些情况下，例如用户选择停用广告个性化功能时，系统会将用户 ID 设置为 0。通常，这些用户仍按行进行分隔，因此用户 ID 为 0 时，其对应的 2 条不同的转化路径会有不同的行。不过，对于某些类型的分析（例如按 user_id = 0 进行分组），系统会将多条转化路径合并为一条，这可能会导致数据出现差异。详细了解用户 ID 为零的情况

*_paths.events 列对应的每一行都包含一个结构体数组，其中每个结构体都是用户路径中的一个事件。构成路径的各个结构体按时间戳进行排序，其中数组中的第一个事件最早发生。

示例查询

以下查询衡量的是展示位置对特定细分用户群完成转化的影响。这些查询使用以下 3 种不同的归因模型来分配功劳：

  • 首次接触（所有功劳都归于第一个接触点）。
  • 最终接触（所有功劳都归于最后一个接触点）。
  • 线性（功劳会在各个接触点之间平分）。

示例数据

行 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

示例查询

首次接触

/* 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

最终接触

/* 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

线性

/* 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

结果

如果您对示例数据运行查询，便会得出以下结果：

首次接触

行 placement_id 功劳
1 11 2
2 22 1

最终接触

行 placement_id 功劳
1 11 3

线性

行 placement_id 功劳
1 11 2.5
2 22 0.5