Exploratory セミナー #52-2 - GA4のBigQueryエクスポートで始まるマーケターのSQL学習

株式会社プリンシプルの木田和廣さんをお招きし、GA4がBigQueryにエクスポートするテーブルを例にSQLの重要性と、その最速学習法をお話しして頂きます。

SQLはデータベースへのクエリ言語であり、GA4がBigQueryにエクスポートする生データを自在に操作することで、GA4のブラウザUIからは行うことができない分析を可能にします。

木田さんはGoogle アナリティクスについての著作もあり、また、最近発売された「SQL入門 - Google Big Queryではじめるビジネスデータ分析」の著者でもあります。

木田さん：https://twitter.com/kazkida

SQL入門 - Google Big Queryではじめるビジネスデータ分析：https://www.amazon.co.jp/dp/B08XBTVZ3R

Transcript

1. Date Prepared by Exploratory Online Seminar #52 GA4のBigQueryエクスポートで始まる マーケターのSQL学習 2021/04/08 株式会社プリンシプル 取締役 副社長 カスタマーサクセス室 室長 木田和廣 Twitter : @kazkida
2. Copyright (C) Principle Co, Ltd. All Rights Reserved 1 プリンシプルについて 専門性の高いコンサルタント、エンジニア、プロジェクトマネージャーがデジタルマ ーケティング支援領域（SEO、広告、CROなど）とDX支援領域（CDM構築、ダッシ ュボード作成、分析と施策立案など）でお客様企業の成長を支援します。 Google Analytics 認定パートナー Tableau 認定パートナー Google Tag Manager 認定パートナー
3. Copyright (C) Principle Co, Ltd. All Rights Reserved 自己紹介(木田 和廣) : Googleアナリティクス関連 2 講演数：年20回くらい GA本：ベストセラー GAIQ：11年半保有 コミュニティリーダー Udemy講座 有償講座講師
4. Copyright (C) Principle Co, Ltd. All Rights Reserved 自己紹介(木田 和廣) : Tableau関連 3 セミナー(a2i初） Tableau入門書(日本初) コミュニティリーダー 有資格者(CP:日本初) Certified Professional Udemy講座 有償講座講師
5. Copyright (C) Principle Co, Ltd. All Rights Reserved 自己紹介(木田 和廣) : その他 4 Tableau Prep SQL 統計学 2021年2月19日発売
6. Copyright (C) Principle Co, Ltd. All Rights Reserved 自己紹介(木田 和廣) : Exploratory 5 データサイエンス・ブートキャンプ 2019年11月平日版の卒業生 2019年9月からのExploratoryユーザー 昨年始まったデータリテラシーサロンに 呼んでいただきました
7. Copyright (C) Principle Co, Ltd. All Rights Reserved 自己紹介(木田 和廣) : まとめ 6 SQL / Tableau Prep データプレパレーション Google Analytics Web解析 Tableau データ ビジュアライズ ビジョン：データサイエンスで人生を拓きたいあなたの最高の教師 +統計学 +機械学習
8. Copyright (C) Principle Co, Ltd. All Rights Reserved セミナー本編
9. Copyright (C) Principle Co, Ltd. All Rights Reserved 本日お話すること 8 マーケターはSQLを学ぼう 1. これまでも学ぶべきだったが、人によっては、「学習ROI」はプ ラスではなかったかもしれない。 2. ところが、Google アナリティクスの最新版GA4のローンチで誰に とっても「学習ROI」はプラスに転換している。 3. 今身につければ「できるマーケーター」に！
10. Copyright (C) Principle Co, Ltd. All Rights Reserved 本日お話すること 9 サンプルサイズは比較的小さい(n=50)ですし、ランダムサンプリン グになってませんが、Twitter投票機能で調査した結果、マーケター にとって「自分でSQLを書けるスキル」を必須と思う人は、およそ 1/4でした。 95%信頼区間:[13.8%,38.1%]
11. Copyright (C) Principle Co, Ltd. All Rights Reserved 最新版のGoogle アナリティクス GA4の特徴
12. Copyright (C) Principle Co, Ltd. All Rights Reserved 2020年10月Google アナリティクスの最新版がローンチ 11 • 2020年10月それまでベータ版であった「アプリ+ウェブ プロパティ」が 突如製品版に • 名前も「Google アナリティクス4 プロパティ」（通称：GA4）に変更 https://analytics-ja.googleblog.com/2020/10/google.html
13. Copyright (C) Principle Co, Ltd. All Rights Reserved Google アナリティクスの最新版GA4の特徴 12 以下が主要な特徴です。 1. Webとアプリの統合分析基盤（新機能） 2. データモデル・取得範囲の変更 a. セッション→イベント b. イベント+パラメータの2層構造 c. セッションの定義の変更 d. 自動収集イベントの強化 3. ユーザー識別機能の強化（Cookieのみ→UserID+Googleシグナル +Cookie） 4. レポート数の大幅削減（100個以上→15個） 5. アドホック分析機能の強化（表形式のみ→6種類のテンプレート） 6. 機械学習に基づく予測指標の提供（新機能） 7. BigQueryへのデータエクスポートをサポート（新機能）
14. Copyright (C) Principle Co, Ltd. All Rights Reserved Google アナリティクスの最新版GA4の特徴 13 以下が主要な特徴です。 1. Webとアプリの統合分析基盤（新機能） 2. データモデル・取得範囲の変更 a. セッション→イベント b. イベント+パラメータの2層構造 c. セッションの定義の変更 d. 自動収集イベントの強化 3. ユーザー識別機能の強化（Cookieのみ→UserID+Googleシグナル +Cookie） 4. レポート数の大幅削減（100個以上→15個） 5. アドホック分析機能の強化（表形式のみ→6種類のテンプレート） 6. 機械学習に基づく予測指標の提供（新機能） 7. BigQueryへのデータエクスポートをサポート（新機能）
15. Copyright (C) Principle Co, Ltd. All Rights Reserved Google アナリティクスの最新版GA4の特徴 14 結論： 1. これまでのGoogle アナリティクス（ユニバーサルアナリティ クス）とは別製品と考えたほうが良い 2. Googleは、今後の新機能はGA4に（≠ユニバーサルアナリテ ィクスに）追加すると明言している 3. いつかはユニバーサルアナリティクスがサンセットを迎える のであろう 4. ユーザーは(Google アナリティクスを使い続けるなら）GA4 に適応しなくてはいけない
16. Copyright (C) Principle Co, Ltd. All Rights Reserved BigQueryデータはどのような時に役立つか？ 15 BigQueryにエクスポートされたデータは、以下のようなシーンで役立ちます。 上記を「やりたい！」、「できるようになりたい！」なら、SQLを書けるよ うになる必要があります 1. GA4のブラウザUIから作成したレポートの数値に疑義があり、「生データ」 にあたって検算したいとき 2. 「UAには存在したがGA4でなくなってしまったレポート」を再現したいとき 3. ExploratoryやTableauなどの分析ツール・BIツールでGA4のデータを可視化 したいとき 4. GA4のブラウザUIから作成するレポートでは回答を得られないビジネス上の 問いへの答えを得たいとき 5. オフライン購入履歴などの別のデータと結合して分析したいとき 6. ExploratoryやBigQuery MLなど機械学習を使った予測・分類を行いたいとき
17. Copyright (C) Principle Co, Ltd. All Rights Reserved GA4がエクスポートした BigQuery上のデータの確認
18. Copyright (C) Principle Co, Ltd. All Rights Reserved GA4からエクポートされたデータのウォークスルー 17 1. 日別にテーブルが作成されます。
19. Copyright (C) Principle Co, Ltd. All Rights Reserved GA4からエクポートされたデータのウォークスルー 18 2. 以下がスキーマ（一部）です。 3. 数えたらカラム数は107あり ました。 4. 見慣れない？RECORD型カ ラムがあります。
20. Copyright (C) Principle Co, Ltd. All Rights Reserved GA4からエクポートされたデータのウォークスルー 19 5. 以下の図が1レコード(1ヒットの情報を格納した行）です。 6. 1レコードに「小さなテーブル」が入れ子になっています。 7. 「イベントにパラメータが紐づく」というデータ構造です。
21. Copyright (C) Principle Co, Ltd. All Rights Reserved GA4からエクポートされたデータのウォークスルー 20 8. ちなみに、イベントの種類(ウェブサイトから自動的に収集さ れるもののみ）には以下があります。 イベントの名前 イベントのトリガー page_view ページが読み込まれた時 scroll ページが垂直方向に90%の深さまで表示された時 click 離脱クリックが発生した時 view_search_results サイト内検索結果が表示された時 video_start video_progress video_complete 動画コンテンツにインタラクションが発生した時 file_download ファイルがダウンロードされた時 user_engagement ユーザーのエンゲージメント(10秒以上の滞在 or 2ページ以 上の閲覧）が発生した時 session_start セッションが開始された時 first_visit ユーザーの初回訪問が発生した時
22. Copyright (C) Principle Co, Ltd. All Rights Reserved BigQuery上のGA4データ をSQLで分析する上でのTips
23. Copyright (C) Principle Co, Ltd. All Rights Reserved 複数テーブルをまたいだ分析 22 • 1日1テーブルが生成されますので、複数の日にまたがった分析は、複 数のテーブルを分析対象にする必要があります。 • Union???
24. Copyright (C) Principle Co, Ltd. All Rights Reserved 複数テーブルをまたいだ分析 23 _table_suffix関数を使うと便利です。
25. Copyright (C) Principle Co, Ltd. All Rights Reserved 入れ子になった小さなテーブルからのデータの取り出し方 24 unnest関数を利用します。
26. Copyright (C) Principle Co, Ltd. All Rights Reserved ExploratoryへのカスタムSQLの投入と データの可視化
27. Copyright (C) Principle Co, Ltd. All Rights Reserved BigQueryをクエリした結果は・・・ 26 • BigQueryにクエリを投げた結果は「テーブル」として返ってきます • 「何が起きているか？」を直感的に利用するには不向きです • そこで、Exploratoryを利用します
28. Copyright (C) Principle Co, Ltd. All Rights Reserved ExploratoryでBQ上のデータを可視化するステップ 27 1. BigQueryにクエリを投入し、SQL文を確定させます。 2. BigQueryのUIには、キーワードの補完機能や、エラーメッセージ出力機 能があるので便利です。 キーワードの補完機能 エラーメッセージ出力機能
29. Copyright (C) Principle Co, Ltd. All Rights Reserved ExploratoryでBQ上のデータを可視化するステップ 28 3. 完成したSQL文を、ExploratoryのカスタムSQL投入画面にコピペします。 4. 以下のSQL文では、2021年1月1日～3月31日の期間における「日付」、 「デバイス」、「ユーザーが初回訪問時に利用したランディングページ」 「セッション」を取得しています。
30. Copyright (C) Principle Co, Ltd. All Rights Reserved ExploratoryでBQ上のデータを可視化するステップ 29 数クリックで、以下のような可視化が完了します。
31. Copyright (C) Principle Co, Ltd. All Rights Reserved 少々複雑なお題 30 このような問いが生まれたとします。 「ユーザーが初回訪問時に利用したランディングページによって、その後の 2ヶ月間でサイトに再訪問する回数が異なるのではないか？」 初回訪問時の LPとしてトッ プページを見 たユーザー 初回訪問時の LPとしてブロ グページを見 たユーザー 1月1日に初回訪問した このユーザーの、2月 28日までの訪問数 1月18日に初回訪問し たこのユーザーの、3 月17日までの訪問数 合計s回の訪問÷ n人のユーザー =ユーザー別平均 再訪問数 合計t回の訪問÷ m人のユーザー =ユーザー別平均 再訪問数
32. Copyright (C) Principle Co, Ltd. All Rights Reserved SQLが解決します 31 このSQLで答えを得ることが出来ます。
33. Copyright (C) Principle Co, Ltd. All Rights Reserved SQLが解決します 32 Exploratoryでの可視化結果。
34. Copyright (C) Principle Co, Ltd. All Rights Reserved SQL学習基盤としてのBigQuery
35. Copyright (C) Principle Co, Ltd. All Rights Reserved 新しいスキルを学ぶ意味 34 様々なスキルがマーケターを「そこまで の人」か「そこから先にも行ける人」か のふるいを掛けに来ます。 正規表現書ける？ GA4分かる？ 統計学使える？ SQL書ける？ 機械学習使える？ 低位の知見 高位の知見
36. Copyright (C) Principle Co, Ltd. All Rights Reserved SQL学習基盤としてのBigQuery 35 • SQLを学ぶ上で、BigQueryは優れた性質を持っています。 • 是非、今日のセミナーを期に、SQLを学ぶ気持ちが生まれたの であれば嬉しいです。 • 以下が、BigQueryがSQLの学習基盤として優れていると考える 点です。 1. 無料枠がある（学習するくらいであれば、実質無料） 2. Standard SQLが使える 3. インストールが不要 4. 公式ヘルプ（＝特にリファレンス）がまあまあ良い 5. 親切な書籍がある 2021年2月19日発売
37. ご清聴ありがとうございました！ Twitter : @kazkida