見出し画像

formの途中で消したutmパラメータをBigQueryでページパスに設定する

木庭有基

この記事はなにか

もともと付与しない設定にしているなどにより、お申込みformの途中で消したutmパラメータをBigQueryを使って各page_pathに再度設定します。年齢セグメント等を変えたキャンペーン毎のform遷移率などを算出するのに役立ちます。

用語の定義

  • utmパラメータ - utm_campaign、utm_sourceとか

前提

justInCaseTechnologiesのマーケティング事業部のformプロダクトでは、formを一定進むとutmパラメータをURLに付与しない設定にしています。

例

  • https://xx.jp/care/?utm_source=xx&utm_medium=xx&utm_campaign=xx

  • https://xx.jp/form/?utm_source=xx&utm_medium=xx&utm_campaign=xx

--utmパラメータがつかなくなる境界--

  • https://xx.jp/form/quote/target

  • 略

  • https://xx.jp/form/entry/member

  • …

↑のような場合に、年齢ごとセグメントによるキャンペーンを行った際に、年齢によってどれくらいformを進むのかということを検証したいわけです。

処理1：ga4をUNNESTする

SELECT
  event_timestamp,
  event_date,
  SPLIT((
    SELECT
      params.value.string_value
    FROM
      UNNEST(event_params) AS params
    WHERE
      params.key = "page_location" ),'?')[ SAFE_OFFSET (0)] AS page_path,
  (
  SELECT
    value.int_value
  FROM
    UNNEST(event_params)
  WHERE
    key = 'ga_session_id') AS ga_session_id,
  (
  SELECT
    value.string_value
  FROM
    UNNEST(event_params)
  WHERE
    key = 'medium') AS utm_medium,
  (
  SELECT
    value.string_value
  FROM
    UNNEST(event_params)
  WHERE
    key = 'term') AS utm_term,
  (
  SELECT
    value.string_value
  FROM
    UNNEST(event_params)
  WHERE
    key = 'campaign') AS utm_campaign,
  (
  SELECT
    value.string_value
  FROM
    UNNEST(event_params)
  WHERE
    key = 'source') AS utm_source,
  (
  SELECT
    value.string_value
  FROM
    UNNEST(event_params)
  WHERE
    key = 'campaign_id') AS utm_id,
  user_pseudo_id
FROM
  `xxxxx.analytics_xxxxx.*`
WHERE
  event_name IN ('page_view')
ORDER BY
  user_pseudo_id DESC,
  ga_session_id ASC,
  utm_campaign DESC,
  event_timestamp ASC

抽出結果スクショ

ga4_tableとして抽出


塗りつぶしだらけですまそ

ga_session_idとuser_pseudo_idが同じ同一人物が/caner/、/form/まで遷移しutmパラメータが付いてる一方、/form/の次ページである form/quote/target 等でutmパラメータがnullになっていますが、処理1では意図した挙動です。

やってること

  • ga4のデータをunnestし、必要な項目だけ出力した

  • 本処理はこれだけ

処理2：session_id & user_pseudo_id ごとにutmパラメータを抽出

SELECT
  event_timestamp,
  event_date,
  SPLIT((
    SELECT
      params.value.string_value
    FROM
      UNNEST(event_params) AS params
    WHERE
      params.key = "page_location" ),'?')[ SAFE_OFFSET (0)] AS no_utm_params,
  (
  SELECT
    value.int_value
  FROM
    UNNEST(event_params)
  WHERE
    key = 'ga_session_id') AS ga_session_id,
  user_pseudo_id,
  (
  SELECT
    value.string_value
  FROM
    UNNEST(event_params)
  WHERE
    key = 'medium') AS utm_medium,
  (
  SELECT
    value.string_value
  FROM
    UNNEST(event_params)
  WHERE
    key = 'term') AS utm_term,
  (
  SELECT
    value.string_value
  FROM
    UNNEST(event_params)
  WHERE
    key = 'campaign') AS utm_campaign,
  (
  SELECT
    value.string_value
  FROM
    UNNEST(event_params)
  WHERE
    key = 'source') AS utm_source,
  (
  SELECT
    value.string_value
  FROM
    UNNEST(event_params)
  WHERE
    key = 'campaign_id') AS utm_id,
FROM
  `xxxxxx.analytics_xxxxxx.*`
WHERE
  event_name IN ('page_view')
  AND ((
    SELECT
      value.string_value
    FROM
      UNNEST(event_params)
    WHERE
      key = 'source') IS NOT NULL
    OR (
    SELECT
      value.string_value
    FROM
      UNNEST(event_params)
    WHERE
      key = 'campaign_id') IS NOT NULL) QUALIFY ROW_NUMBER() OVER (PARTITION BY user_pseudo_id, ga_session_id ORDER BY event_timestamp) = 1
ORDER BY
  event_timestamp DESC,
  user_pseudo_id DESC,
  ga_session_id DESC

抽出結果スクショ

utm_view_only_pagepathとして抽出

塗りつぶしだらけですま…

やってること

  1. SPLIT関数でutmパラメータが付いたURLを ? 以降を消し、page_pathのみにする

  2. DISTINCTで一意のga_session_id、user_pseudo_idを抽出

  3. window関数のROW_NUMBER()を使う *1

    1. PARTITION BYでuser_pseudo_id, ga_session_idの順でグルーピングする

    2. ORDER BYでevent_timestampの開始順にする ※指定しない場合は開始順になる

    3. ※Qualify句で簡潔にしてる *2

  4.  3でできた一意のga_session_id、user_pseudo_idごとにキャンペーン毎に最初に到達したLPを抽出

参考

*1

*2

処理3：処理1と処理2をLEFT OUTER JOIN


WITH
  t1 AS (
  SELECT
    DISTINCT t.user_pseudo_id,
    t.ga_session_id,
    t.page_path,
  FROM
    `xxxxx.warehouse.ga4_table` AS t )
SELECT
  u.utm_campaign,
  u.utm_source,
  t1.page_path,
  COUNT(*) AS user
FROM
  `xxxxx.warehouse.utm_view_only_pagepath` AS u
LEFT OUTER JOIN
  t1
ON
  t1.user_pseudo_id = u.user_pseudo_id
  AND t1.ga_session_id = u.ga_session_id
WHERE
  t1.page_path NOT LIKE "%care%"
  AND t1.page_path NOT LIKE "%cancer%"
  AND t1.page_path NOT LIKE "%healthcare%"
  AND t1.page_path NOT LIKE "%faq%"
GROUP BY
  u.utm_campaign,
  u.utm_source,
  t1.page_path
ORDER BY
  u.utm_campaign DESC,
  u.utm_source DESC,
  user DESC,
  page_path ASC

抽出結果スクショ

抽出名はよしなに…

すまそ…

form/系にutmパラメータ付与できました！（塗りつぶしだらけで全然わからん）

締めのコメント

formのpage_pathに途中からutmパラメータが付いてなくてどないしよ、というケースでお困りの人はこれで幸せになれるのではないかと思います。

いいなと思ったら応援しよう！