包含路徑資料表的歸因模式

您可以運用「*_paths」資料表，深入瞭解使用者與您透過 Display & Video 360 和 Campaign Manager 360 管理的媒體有哪些互動。這些互動 (又稱為接觸點) 會拼接成事件路徑，可記錄一群使用者通過轉換漏斗的歷程。*_paths 資料表中的資料與您透過資料移轉取得的資料相同，但是會在預先組合的路徑中提供給您。事件路徑適用於已轉換和未轉換使用者，且可包含多次轉換。

就像廣告資料中心的所有使用者資料一樣，*_paths 資料表中的資料必須符合匯總需求條件。

技術詳細資料

paths 的每一列都涵蓋 30 天回溯期內的單一顧客歷程。這項資料每天都會更新，讓時間範圍能保持在過去 30 天。事件路徑是使用 Cookie 組合而成，因此僅限於單一裝置。在某些情況下 (例如使用者選擇停用廣告個人化功能)，User-ID 會設為 0。一般而言，這些使用者仍會按列區分，因此 User-ID 為 0 時，對應的 2 條不同轉換路徑會有不同的資料列。不過，在某些類型的分析中 (例如按 user_id = 0 分組)，系統會將多條轉換路徑合而為一，這可能導致資料出現差異。進一步瞭解 User-ID 為 0 的情況

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