『できる逆引き Googleアナリティクス4』サポートページ - 計測・分析に役立つSQL文を公開

『できる逆引き Googleアナリティクス4 成果を生み出す分析・改善ワザ 192』の読者のみなさまに、紙面で解説したSQLを公開しています。お使いの環境で試してみたいときにご利用ください。

『できる逆引き Googleアナリティクス4 成果を生み出す分析・改善ワザ 192』の紙面で紹介している、SQL文を公開しています。各データはワザの番号順に掲載しています。

公開データ一覧

ワザ028：追加したタグの動作をBigQueryで確認する
ワザ183：BigQueryによる高度な分析の前提を理解する
ワザ185：ユニバーサルアナリティクスと同じ定義の直帰率を求める
ワザ186：ランディングページと2ページ目のコンバージョン率を確認する
ワザ187：指標「ページの価値」でページの評価を行う
ワザ188：平均以外の指標でセグメントの本当の評価を行う
ワザ189：アトリビューションの考えをコンテンツ分析に適用する
ワザ190：閲覧するとCVRが高くなるページを網羅的に探索する
ワザ191：初回訪問からN日以内のLTVで初回訪問獲得を最適化する
ワザ192：初回訪問時のメディアとランディングページごとのLTVを確認する

ワザ028
追加したタグの動作をBigQueryで確認する

特定の時刻より後に発生したscrollイベントだけに限定し、時系列順に並べたうえで「scroll」イベントが発生した「page_title」と「percent_scrolled」を一覧で確認できます。

    SELECT event_timestamp, user_pseudo_id, event_name
    , (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_title') AS page_title
    , (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'percent_scrolled') AS percent_scrolled
    FROM 'bigquerytableauoct.analytics_254394192.events_20220911'
    WHERE event_name = "scroll"
    ORDER BY event_timestamp

ワザ183
BigQueryによる高度な分析の前提を理解する

FROM句に次のような記述がある場合は、Googleアナリティクス4のデモアカウントのデータを参照していることを指しています。

    `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_XXXXXXXX`

自社のデータを使って分析を行う場合、FROM句のAAAのところに自社のBigQueryのプロジェクトIDを、BBBのところにGA4のプロパティIDを、YYYYMMDDのところに分析したい日付を入れ、SQL文を実行してください。

    FROM `AAA.analytics_BBB.events_YYYYMMDD`

ワザ185
ユニバーサルアナリティクスと同じ定義の直帰率を求める

ユニバーサルアナリティクスの「直帰率」（1ページビューしか発生しなかったセッションの割合）を求めるSQL文です。

    WITH master AS (
    SELECT event_date, ga_session_id, COUNT(*) AS pv_by_session
    FROM (
    SELECT
    event_date
    , (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id
    FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_202011*`
    WHERE event_name = 'page_view'
    ) GROUP BY event_date, ga_session_id
    )

    SELECT event_date, COUNT(DISTINCT ga_session_id) AS sessions, SUM(bounce) AS bounces
    , ROUND(SUM(bounce) / COUNT(DISTINCT ga_session_id) * 100, 2) AS bounce_rate_percent
    FROM (
    SELECT
    event_date, ga_session_id, IF(pv_by_session = 1, 1, 0) AS bounce
    FROM master
    )
    GROUP BY event_date
    ORDER BY 1

ワザ186
ランディングページと2ページ目のコンバージョン率を確認する

ユニバーサルアナリティクスと同様の「ランディングページ」「2ページ目」を求めるSQL文です。

    WITH cv_sessions AS
    ( SELECT DISTINCT ga_session_id, 1 AS cv
    FROM ( SELECT
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id
    FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_202101*`
    WHERE event_name = 'purchase'
    ))
    , pages AS (SELECT ga_session_id, MAX(landing_page) AS landing_page
    , MAX(IF(second_page = landing_page, third_page, second_page)) AS second_page
    FROM (
      SELECT ga_session_id
      , FIRST_VALUE(page_title) OVER (PARTITION BY ga_session_id ORDER BY event_timestamp) AS landing_page
      , NTH_VALUE(page_title,2) OVER (PARTITION BY ga_session_id ORDER BY event_timestamp) AS second_page
      , NTH_VALUE(page_title,3) OVER (PARTITION BY ga_session_id ORDER BY event_timestamp) AS third_page
      FROM (
      SELECT
      event_timestamp
    , (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 = 'page_title') AS page_title
    FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_202101*`
    WHERE event_name = 'page_view'
    ))
    GROUP BY ga_session_id)

    SELECT landing_page, second_page, COUNT(DISTINCT ga_session_id) AS sessions, SUM(cv) AS cvs
    , ROUND(SUM(cv) / COUNT(DISTINCT ga_session_id) * 100 , 2) AS cvr_percent
    FROM (
    SELECT p.ga_session_id , p.landing_page , p.second_page , COALESCE(cvs.cv,0) AS cv FROM pages AS p
    LEFT JOIN cv_sessions AS cvs
    ON p.ga_session_id = cvs.ga_session_id
    )
    --WHERE second_page is not null
    GROUP BY landing_page, second_page
    ORDER BY 3 DESC

ワザ187
指標「ページの価値」でページの評価を行う

ユニバーサルアナリティクスに存在した、ページのコンバージョン貢献度を可視化する指標「ページの価値」を求めるSQL文です。

    WITH page as (
      SELECT ga_session_id, page_title
      FROM (
      SELECT
    (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 = 'page_title') AS page_title
      FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_202101*`
      ORDER BY 1)
      WHERE page_title NOT IN ('Shopping Cart', 'Checkout Your Information', 'Payment Method', 'Checkout Confirmation', 'Checkout Review')
      GROUP BY ga_session_id, page_title)
    , revenue AS (
      SELECT ga_session_id, SUM(revenue) AS revenue
      FROM (
      SELECT
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id
    , ecommerce.purchase_revenue AS revenue
      FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_202101*`
    ) GROUP BY ga_session_id)

    SELECT *, revenue / unique_sessions_per_page AS page_value
    FROM (
    SELECT page_title, COUNT(DISTINCT ga_session_id) AS unique_sessions_per_page, SUM(revenue) AS revenue
    FROM (
      SELECT p.ga_session_id, p.page_title, COALESCE(r.revenue, 0) AS revenue
      FROM page AS p
      LEFT JOIN revenue AS r
      ON p.ga_session_id = r.ga_session_id
    )
    GROUP BY page_title
    ) ORDER BY 2 DESC

ワザ188
平均以外の指標でセグメントの本当の評価を行う

国別のユーザーエンゲージメントを平均値、中央値、標準偏差で求めるSQL文です。

    WITH master AS (
    SELECT user_pseudo_id
    , MAX(geo.country) AS country
    , SUM((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec')) / 1000 AS engagement_time
    FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`
    GROUP BY user_pseudo_id)
    , a AS (
    SELECT country, COUNT(DISTINCT user_pseudo_id) AS users
    , ROUND(AVG(engagement_time), 1) AS average_engagement_time
    FROM master
    GROUP BY country)
    , m AS (
    SELECT DISTINCT country
    , ROUND(PERCENTILE_CONT(engagement_time, 0.5) OVER(PARTITION BY country), 1) AS median_engagement_time
    , ROUND(STDDEV_POP(engagement_time) OVER(PARTITION BY country), 1) AS std_dev_engagement_time
    FROM master)

    SELECT a.country, a.users, a.average_engagement_time, m.median_engagement_time, m.std_dev_engagement_time
    FROM a
    INNER JOIN m
    ON a.country = m.country
    ORDER BY 2 DESC

ワザ189
アトリビューションの考えをコンテンツ分析に適用する

Googleアナリティクス4のデモアカウントで「purchase」イベントをコンバージョンとして、ページ別のコンバージョン貢献度をアトリビューションモデルごとに取得するSQL文です。

    WITH cv_users AS (
    SELECT
    user_pseudo_id
    , MAX(IF(event_name = 'purchase', 1, 0)) AS cv_flag
    , MIN(IF(event_name = 'purchase', event_timestamp, NULL)) AS min_cv_timestamp
    FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_202101*`
    GROUP BY user_pseudo_id
    HAVING cv_flag = 1
    )
    , pageviews AS (
      SELECT * FROM (
      SELECT user_pseudo_id, event_name
    , event_timestamp
    , (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_title') AS page_title
    FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_202101*`
    WHERE event_name IN ('page_view', 'purchase')
      ) WHERE page_title NOT IN ('Shopping Cart', 'Checkout Your Information', 'Payment Method', 'Checkout Confirmation', 'Checkout Review')
    )
    , master AS (
      SELECT * FROM (
    SELECT pv.user_pseudo_id
    , pv.event_name
    , pv.page_title
    , pv.event_timestamp
    , cv.min_cv_timestamp
    , ROW_NUMBER() OVER(PARTITION BY pv.user_pseudo_id ORDER BY pv.event_timestamp) AS row_num
    FROM pageviews AS pv
    INNER JOIN cv_users AS cv
    ON pv.user_pseudo_id = cv.user_pseudo_id
      ) WHERE event_timestamp < min_cv_timestamp
    )

    SELECT page_title
    , SUM(first_touch) AS first_touch
    , SUM(last_touch) AS last_touch
    , SUM(linear) AS linear
    , SUM(touch_point) AS touch_point
    FROM (
    SELECT *
    , IF(row_num = MIN(row_num) OVER(PARTITION BY user_pseudo_id), 1, 0) AS first_touch
    , IF(row_num = MAX(row_num) OVER(PARTITION BY user_pseudo_id), 1, 0) AS last_touch
    , 1 / MAX(row_num) OVER(PARTITION BY user_pseudo_id) AS linear
    , CASE
    WHEN MAX(row_num) OVER(PARTITION BY user_pseudo_id) = 1 THEN 1
    WHEN MAX(row_num) OVER(PARTITION BY user_pseudo_id) = 2 THEN 0.5
    ELSE
      CASE
    WHEN row_num = MIN(row_num) OVER(PARTITION BY user_pseudo_id) THEN 0.4
    WHEN row_num = MAX(row_num) OVER(PARTITION BY user_pseudo_id) THEN 0.4
    ELSE 0.2 / (MAX(row_num) OVER(PARTITION BY user_pseudo_id) - 2)
    END
    END AS touch_point
      FROM master
    ) GROUP BY page_title
    ORDER BY 2 DESC

ワザ190
閲覧するとCVRが高くなるページを網羅的に探索する

複数のコンテンツについて「表示したユーザー」「表示し、かつコンバージョンしたユーザー」「表示していないユーザー」「表示していない、かつコンバージョンしたユーザー」を可視化し、どのコンテンツを表示した場合にユーザー単位コンバージョン率が変化するのかを、網羅的に可視化するSQL文です。

    WITH master AS (
    SELECT DISTINCT user_pseudo_id AS cid
    FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_202101*`
    ), pvu AS (
    SELECT * FROM (
    SELECT user_pseudo_id AS cid
    , (SELECT value.string_value FROM UNNEST(event_params) WHERE key = "page_title") AS page
    FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_202101*`)
    WHERE REGEXP_CONTAINS(page, r"^(Home|Men's\s/\sUnisex\s\|\sApparel|Sale\s\|\sG|New\s\|\sG|Small\sGoods\s\|\sLifestyle|YouTube\s\|\sS|Bags\s\|)") IS true
    group by cid, page
    ), cvu AS (
    SELECT DISTINCT user_pseudo_id AS cid, 1 AS cv
    FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_202101*`
    WHERE event_name = "purchase")

    SELECT *, ROUND(view_user_cvr / non_view_user_cvr, 1) AS difference_ratio
    FROM (
    SELECT *
    , ROUND(non_view_cv_users / non_view_users, 4) AS non_view_user_cvr
    FROM (
    SELECT *, ROUND(view_cv_users / view_users, 4) AS view_user_cvr
    , (SELECT COUNT(DISTINCT cid) FROM master) - view_users AS non_view_users
    , (SELECT SUM(cv) FROM cvu) - view_cv_users AS non_view_cv_users
    FROM (
    SELECT page, COUNT(DISTINCT view_users) AS view_users, SUM(cv) AS view_cv_users
    FROM (
    SELECT master.cid AS all_users, pvu.cid AS view_users, pvu.page, cvu.cv
    FROM master
    LEFT JOIN pvu
    USING (cid)
    LEFT JOIN cvu
    USING (cid)
    WHERE page IS NOT NULL
    ) GROUP BY page)))
    ORDER BY 2 DESC

ワザ191
初回訪問からN日以内のLTVで初回訪問獲得を最適化する

「初回訪問から30日以内のLTV」ユーザーの最初のメディアを評価するSQL文です。

    WITH master AS (
      SELECT user_pseudo_id
      , MAX(user_first_touch_date) AS user_first_touch_date
      , MAX(user_first_medium) AS user_first_medium
      , SUM(revenue) AS revenue
    FROM (
    SELECT user_pseudo_id
      , CAST(DATETIME_TRUNC(DATETIME(timestamp_micros(user_first_touch_timestamp),'America/Los_Angeles'), day) AS date) AS user_first_touch_date
      , PARSE_DATE(""%Y%m%d"",event_date) AS event_date
      , traffic_source.medium AS user_first_medium
      , ecommerce.purchase_revenue_in_usd AS revenue
    FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_2020*`
    WHERE event_name IN ('first_visit','purchase')
    )
    WHERE DATE_DIFF(event_date, user_first_touch_date, day) <=30 AND user_first_touch_date >= '2020-11-01'
    GROUP BY user_pseudo_id
    )

    SELECT user_first_medium, COUNT(DISTINCT user_pseudo_id) AS users
      , SUM(revenue) AS ltv_within_30days_from_first_visit
      , ROUND(SUM(revenue)/COUNT(DISTINCT user_pseudo_id),2) AS avg_ltv_within_30days_from_first_visit
    FROM master
    GROUP BY user_first_medium
    ORDER BY 2 DESC

ワザ192
初回訪問時のメディアとランディングページごとのLTVを確認する

初回訪問のメディアと、ランディングページ別のユーザー数とLTVを取得するSQL文です。

    WITH ltv AS (
    SELECT user_pseudo_id, MAX(user_ltv.revenue) AS ltv, MAX(traffic_source.medium) AS first_media
    FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_202101*`
    GROUP BY user_pseudo_id)
    , flp AS(
    SELECT user_pseudo_id
    , (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location
    FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
    WHERE event_name = "first_visit" )
    
    SELECT first_media, first_landing_page, COUNT(DISTINCT user_pseudo_id) AS users, SUM(ltv) AS ltv
    , SUM(ltv)/COUNT(DISTINCT user_pseudo_id) AS avg_ltv
    FROM (
    SELECT ltv.first_media, flp.page_location AS first_landing_page, ltv.user_pseudo_id, ltv.ltv
    FROM ltv JOIN flp USING(user_pseudo_id)
    WHERE ltv.first_media <> "(data deleted)"
    )
    GROUP BY first_media, first_landing_page
    ORDER BY 3 DESC

公開データは以上です。お詫びと訂正（正誤表）については、インプレスブックスの書籍詳細ページでご確認ください。

新たな一歩を応援するメディア

『できる逆引き Googleアナリティクス4』サポートページ - 計測・分析に役立つSQL文を公開

公開データ一覧

ワザ028
追加したタグの動作をBigQueryで確認する

ワザ183
BigQueryによる高度な分析の前提を理解する

ワザ185
ユニバーサルアナリティクスと同じ定義の直帰率を求める

ワザ186
ランディングページと2ページ目のコンバージョン率を確認する

ワザ187
指標「ページの価値」でページの評価を行う

ワザ188
平均以外の指標でセグメントの本当の評価を行う

ワザ189
アトリビューションの考えをコンテンツ分析に適用する

ワザ190
閲覧するとCVRが高くなるページを網羅的に探索する

ワザ191
初回訪問からN日以内のLTVで初回訪問獲得を最適化する

ワザ192
初回訪問時のメディアとランディングページごとのLTVを確認する

ピックアップ

ピックアップ

『できる逆引き Googleアナリティクス4』サポートページ - 計測・分析に役立つSQL文を公開

公開データ一覧

ワザ028追加したタグの動作をBigQueryで確認する

ワザ183BigQueryによる高度な分析の前提を理解する

ワザ185ユニバーサルアナリティクスと同じ定義の直帰率を求める

ワザ186ランディングページと2ページ目のコンバージョン率を確認する

ワザ187指標「ページの価値」でページの評価を行う

ワザ188平均以外の指標でセグメントの本当の評価を行う

ワザ189アトリビューションの考えをコンテンツ分析に適用する

ワザ190閲覧するとCVRが高くなるページを網羅的に探索する

ワザ191初回訪問からN日以内のLTVで初回訪問獲得を最適化する

ワザ192初回訪問時のメディアとランディングページごとのLTVを確認する

ピックアップ

ピックアップ

ワザ028
追加したタグの動作をBigQueryで確認する

ワザ183
BigQueryによる高度な分析の前提を理解する

ワザ185
ユニバーサルアナリティクスと同じ定義の直帰率を求める

ワザ186
ランディングページと2ページ目のコンバージョン率を確認する

ワザ187
指標「ページの価値」でページの評価を行う

ワザ188
平均以外の指標でセグメントの本当の評価を行う

ワザ189
アトリビューションの考えをコンテンツ分析に適用する

ワザ190
閲覧するとCVRが高くなるページを網羅的に探索する

ワザ191
初回訪問からN日以内のLTVで初回訪問獲得を最適化する

ワザ192
初回訪問時のメディアとランディングページごとのLTVを確認する