Upgrade to Pro — share decks privately, control downloads, hide ads and more …

GA4+BigQuery ハンドブック / ga4_bigquery

29e4aa4e265e478995df09ca52d62103?s=47 ShinU
October 13, 2021

GA4+BigQuery ハンドブック / ga4_bigquery

作成者 :しんゆう
ブログ :データ分析とインテリジェンス
     https://analytics-and-intelligence.net/
Twitter:https://twitter.com/data_analyst_

29e4aa4e265e478995df09ca52d62103?s=128

ShinU

October 13, 2021
Tweet

Transcript

  1. GA4+BigQuery ハンドブック Ver 1.0.0α しんゆう @data_analyst_

  2. 本資料について • GA4+BigQueryはまだオフィシャルサイトにも情報が少 なく手探り状態 • そこでいろいろな情報を1つにまとめておくことは有用だ と考えた • まだまだ取り組み始めたばかりなので間違いや効率の悪い 方法を見つけたら教えていただけると幸いです

    本資料について About
  3. 本資料について • 2021/10/13 α版公開 更新履歴 About

  4. 名前:しんゆう @data_analyst_ ブログ:データ分析とインテリジェンス https://analytics-and-intelligence.net 最近の活動:データを使いやすくする人 (データアーキテクトまたはデータ整備人) スライドが表示されているページの下段にある説明欄からも リンクがあります 本資料について 自己紹介

    About
  5. GA4+BigQueryハンドブック 目次 •基本事項 •イベント •指標 •クエリ解説 •議題 •参考リンク

  6. GA+BigQuery 基本事項

  7. • 毎日連携される時間が違う • 朝9時に終わっていることもあれば12時を過ぎてもお終わ っていないことがある • BigQueryのデータは前々日の鮮度で使うようにしておい たほうが無難か • どうしても速報値が必要ならGA側で見る

    本資料について データの連携 GA+BigQuery 基本事項
  8. • _table_suffixで期間をしていないと全期間が対象 • _table_suffixは8桁の文字列(日付型ではない) • 例:2021年1月1日を取得 _table_suffix = '20210101' •

    例:クエリを実行した前日以前の全期間を取得 _table_suffix <= format_date('%Y%m%d',date_add(current_date("Asia/Tokyo"),in terval -1 day)) *日本時間で実行日の日付を取ってから1日減らし、さらにyyyymmddに変換 本資料について 集計期間の絞り込み方法 GA+BigQuery 基本事項
  9. イベント

  10. • セッション開始時点で記録される • 開始時点のイベントなのでそのセッションでのページや滞 在時間の情報はない 本資料について session_start GA+BigQuery イベント

  11. • オフィシャルのリファレンスには以下の説明 ◦ ユーザーが各ページの最下部まで初めてスクロールし たとき ◦ 垂直方向に 90% の深さまで表示されたときなど •

    ページの一番下まで表示されなくてもイベントが起きるの で90%が正しそう • 割合は変更できないが、任意の割合までスクロールした場 合のカスタムイベントを作ることが出来る 本資料について scroll GA+BigQuery イベント
  12. カラム

  13. • event_date(日付型) • オフィシャルによると「アプリの登録タイムゾーンにおけ る日付を YYYYMMDD 形式で示したもの」とあるが詳細不 明 • 次のevent_timestampから日付を作った方が安全かも

    本資料について イベント発生日 GA+BigQuery イベント
  14. • event_timestamp • そのままだと使いづらいので日付などに変換する • ミリ秒なのでtimestamp_microsを経由して使う timestamp_micros(event_timestamp) • 例:日付型(日本時間)に変換する DATE(timestamp_micros(event_timestamp),

    'Asia/Tokyo') 本資料について イベント発生時刻 GA+BigQuery イベント
  15. • user_pseudo_id 本資料について ユーザー GA+BigQuery イベント

  16. • event_params.value.int_value • 条件:event_params.key = ’ga_session_id’ • ユニークではないらしいのでユーザーと紐づける 本資料について セッションID

    GA+BigQuery イベント
  17. • event_params.value.string_value • 条件:event_params.key = ’page_title’ • イベントが発生した時点でタイトルタグに表示されている 文字列が入るため集計には使わない方がよさそう •

    閲覧中に翻訳された場合同じセッション内でページタイト ルの値が変わる 本資料について ページタイトル GA+BigQuery イベント
  18. • event_params.value.string_value • 条件:event_params.key = ’page_location’ • page_urlとかではない • ページ内リンク(#)は無視される

    • 例:パラメータを除外 SPLIT((select value.string_value from unnest(event_params) p where p.key = 'page_location'),'?')[OFFSET(0)] 本資料について URL GA+BigQuery イベント
  19. 指標

  20. • セッション数はsession_startのレコード数で取れる • セッション内での行動は他のイベントを使って集計する必 要がある • 例えばセッションにおける閲覧ページや滞在時間は page_viewをセッションごとに纏める 本資料について セッション数/セッションごとの行動

    GA+BigQuery 指標
  21. • 直帰はセッションごとに集計を行ってpage_view数=1の セッションを取る • session_startのイベントはセッション開始時点の情報しか ないため使えない 本資料について 直帰/直帰数/直帰率 GA+BigQuery 指標

  22. • page_titleで集計するとうまくいかない • タイトルを自分で変更したり、閲覧中に翻訳があると違う 値になる • page_locationごとに集計し、最新(または最初)の page_titleに入れ替えるなどの方法が必要 本資料について page_titleごとに集計

    GA+BigQuery 指標
  23. クエリ

  24. • page_locationを”?”で分割して前半を取得 SPLIT((select value.string_value from unnest(event_params) p where p.key =

    'page_location'),'?')[OFFSET(0)] 本資料について URLからパラメータを削除 GA+BigQuery クエリ
  25. • アクセスが無いと欠損するので埋めるには日付マスタを 作ってそこにJOINする必要がある GENERATE_DATE_ARRAY('yyyy-mm-dd','yyyy-mm-dd') • GENERATE_DATE_ARRAYは配列 • 例:ページ×日付の組み合わせのテーブルを作る unnest(GENERATE_DATE_ARRAY('yyyy-mm-dd','yyyy-mm- dd'))

    ,(select distinct page_title from tbl) • 日付でなく月、毎日ではなくn日おきでも作れる 本資料について 日付マスタ GA+BigQuery クエリ
  26. 議題

  27. Taku Ogawa (小川 卓)さん @ryuka01 https://twitter.com/ryuka01/status/1431181216825233409 • イベントは5秒に1回まとめて送られてくる • event_timestampはイベント発生時刻ではなくイベント

    が送信された時刻 • 5秒以内に起きたイベントは同時刻と見なされるためペー ジ遷移や複数種類のイベントが発生順ではとれないかも • 仕様なら回避できないためどう扱うのがいいのか 本資料について イベント発生時間の取り扱い GA+BigQuery 議題
  28. • 連携された状態から使うとちょっとした集計をするのにも クエリが複雑怪奇になる • イベントごとにテーブルを作ってネストを解除しておけれ ば使いやすいがデータ量が膨大になることが予想される • 使いやすさ、管理しやすさ、コストのバランスをうまくと るにはどのような方法が考えられるか 本資料について

    アーキテクチャ GA+BigQuery 議題
  29. 参考リンク

  30. • [GA4] BigQuery Export スキーマ https://support.google.com/firebase/answer/7029846?hl=ja&ref_topic=7 029512 • [GA4] BigQuery

    のデータからオーディエンスを抽出するクエリのサン プル https://support.google.com/firebase/answer/9037342?hl=ja&ref_topic=7 029512#zippy=%2C%E3%81%93%E3%81%AE%E8%A8%98%E4%B A%8B%E3%81%AE%E5%86%85%E5%AE%B9 • BigQuery ドキュメント https://cloud.google.com/bigquery/docs?hl=ja 本資料について オフィシャル GA+BigQuery 参考リンク
  31. 協力者募集

  32. • 協力者募集中 ◦ データを提供してくれる企業 ◦ ネタの提供やレビューしてくれる人 本資料について 誰か手伝ってください 協力者募集 ご質問などあればお気軽にご連絡ください

    しんゆう @data_analyst_