BigQueryでGA4データのユーザーエクスプローラ
GA4では探索でユーザーエクスプローラの機能がありますが、イベント名が並んでいるだけで、どのページかを確認するためには、いちいち当該のイベントをクリックしてパラメータを見なければいけません。
いずれ改善されるかもしれませんが、今の段階では非常に面倒なので、BigQueryでユーザーがサイトをどのように使っているか軌跡を可視化してみます。
SELECT
user_pseudo_id,
event_name,
datetime(TIMESTAMP_MICROS(event_timestamp),'Asia/Tokyo') AS event_time,
lead(datetime(TIMESTAMP_MICROS(event_timestamp),'Asia/Tokyo'))
over(partition by (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') order by datetime
(TIMESTAMP_MICROS(event_timestamp),'Asia/Tokyo')) as next_event_time,
datetime_diff(lead(datetime(TIMESTAMP_MICROS(event_timestamp),'Asia/Tokyo'))
over(partition by (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') order by datetime
(TIMESTAMP_MICROS(event_timestamp),'Asia/Tokyo')),
datetime(TIMESTAMP_MICROS(event_timestamp),'Asia/Tokyo'), second) as margin
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`
where event_name = 'page_view' or event_name = 'scroll'
クエリがやや長くなっていますが、ユーザーid、セッションid、イベント名、イベントが発生したページ、イベントが発生した時間、次のイベントが発生した時間、その差を集計しています。
イベントをpage_viewとscrollにしていますが、必要に応じてほかのイベントを表示させてもいいと思います。
クエリを実行すると下図のようになります。
お試しください。
BigqueryでGA4データの訪問回数分布を集計
GA4データの訪問回数の分布を集計したいと思います。session_startイベントのパラメータga_session_numberを使って、ユーザーidをユニークにカウントします。
すると、初回訪問のユーザー数、2回目訪問のユーザー数、というように分布を把握できるようになります。
SELECT
session_number,
count(distinct user_pseudo_id) as user
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`
where event_name = 'session_start'
group by 1
order by 1
お試しください。
BigQueryでGA4データのスクロール率集計
ページ単位のスクロール率集計
GA4では「scroll」が自動収集イベントで計測されています。ユーザーがウェブページの最下部までスクロールしたときにイベントが記録されます。
そこで、ページ単位のスクロール率（最下部までスクロールした割合）を集計してみたいと思います。
クエリですが、ページ単位でpage_viewイベントの数とscrollイベントの数を集計し、scrollイベント数/page_viewイベント数を計算します。
SELECT
countif(event_name = 'page_view') as page_view,
countif(event_name = 'scroll') as scroll,
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`
Group by 1
お試しください。
*1:countif(event_name = 'scroll') / countif(event_name = 'page_view'
BigQueryでGA4データのCVR集計
GA4データでCVR集計
BigQueryにあるGoogleアナリティクス4のデータを使ってCVRを集計します。GA4のサンプルデータが用意されていますので、それを使っています。
[GA4] BigQuery 用の Google アナリティクス 4 プロパティのサンプル データセット
イベントのsession_startとpurchseを使って簡易的に集計しています。
session_startについては、user_pseudo_idとga_session_idをCONCATして集計する方法もあります。また、purchaseもトランザクションidをパラメータに送っていれば、それをユニークにカウントすることもできると思います。
クエリです-------------------------------------------------------------------------
SELECT
event_date,
countif(event_name = 'session_start') as sessions,
countif(event_name = 'purchase') as purchase,
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`
group by 1
*1:countif(event_name = 'purchase'
BigQueryでGA4データの流入経路単位集計
■メディア単位のセッション数集計
BigQueryにあるGoogleアナリティクス4のデータを使って流入経路単位のsession数を集計します。GA4のサンプルデータが用意されていますので、それを使っています。
[GA4] BigQuery 用の Google アナリティクス 4 プロパティのサンプル データセット
メディア単位で集計するには、メディアとセッションIDを取り出す必要があります。
クエリです----------------------------------------------------------------------
SELECT
traffic_source.medium,
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`
------------------------------------------------------------------------------------
ただ、ga_session_idはユーザー間で重複するかもしれませんので、user_pseudo_idとconcatし、メディア単位でユニークにカウントします。また、session_startイベントに絞ります。
クエリです----------------------------------------------------------------------
SELECT
traffic_source.medium,
WHERE key = 'ga_session_id') as STRING))) AS sessions,
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`
where event_name = 'session_start'
group by 1
------------------------------------------------------------------------------------
■セッション数集計にsession_startイベントを使う
セッション数の集計は、user_pseudo_idとga_session_idをCONCATして集計しましたが、session_startイベントをそのままカウントする方法もあります。ただ、この場合前者よりも数字が若干多くなっています。organicでは955が956になっています。
尚、原因の深堀まではできていません。。
■参照元/メディアでの集計
参照元/メディアで集計したい場合は、traffic_source.sourceとtraffic_source.mediumをCONCATすればできます。
concat(traffic_source.source,' / ',traffic_source.medium) as source_medium
BigQueryでユーザーの購入日から遡って行動を集計する
Googleアナリティクスデータでも、どんなデータでもそうですがデータを集計する期間を例えば2020年1月1日から1月31日までとすると、当たり前ですがその期間のすべての数値を集計することになります。
BQMLなりAutoMLで購入予測をしたいときに、前述のような期間指定の方法で購入者の行動を集計すると場合によってはノイズ的な数値を含む可能性があります。例えばWebトラフィックを使って購入者の教師データを作るケースを考えてみたいのですが、あるユーザーが下記のようにサイトに訪問したとします。
1/ 4 検討段階
1/10 検討段階
1/12 検討段階
1/13 トランザクション
1/15 予約・購入の確認
1/20 メルマガにあったキャンペーンを見たくてたまたま訪問
私たちが普段オンラインでモノを買うときに、よくある行動だと思います。Googleアナリティクスのユーザーエクスプローラでトランザクションしたユーザーを見ると、前述のように行動しているケースはとても多いのではないでしょうか？
先程の話に戻りますが、購入を予測したい教師データを作成するときはトランザクションに至る検討段階の行動をベースにしたテーブルが必要です。購入していないユーザーは上のパターンでは1/15のサイト訪問はあり得ないですし、検討が終わった1/20の訪問もノイズになりかねません。
よって集計期間を1月1日から1月31日とするのではなく、トランザクションから遡って検討期間だけ集計する必要がありますので、そのクエリを書いていきたいと思います。
WITH
yoyaku AS (
SELECT
fullvisitorid,paese_DATE('%Y%m%d', date) AS txn_date,
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_table_suffix BETWEEN '20170701' AND '20170731'
AND totals.transactions IS NOT NULL),
kentou AS (
SELECT
fullvisitorid,paese_DATE('%Y%m%d', date) AS date,visitid,
COUNT(totals.pageviews) AS PV_count,
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_table_suffix BETWEEN '20170701' AND '20170731'
AND totals.visits IS NOT NULL
GROUP BY 1,2,3 )
SELECT
fullvisitorid, txn_date, date, DATE_DIFF(txn_date,date,day) AS date_diff,
visitid, PV_count
FROM
kentou
LEFT JOIN
yoyaku
USING (fullvisitorid)
WHERE txn_date IS NOT NULL
ORDER BY 1,3
;
yoyaku AS (
SELECT
fullvisitorid,paese_DATE('%Y%m%d', date) AS txn_date,
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_table_suffix BETWEEN '20170701' AND '20170731'
AND totals.transactions IS NOT NULL),
kentou AS (
SELECT
fullvisitorid,paese_DATE('%Y%m%d', date) AS date,visitid,
COUNT(totals.pageviews) AS PV_count,
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_table_suffix BETWEEN '20170701' AND '20170731'
AND totals.visits IS NOT NULL
GROUP BY 1,2,3 )
SELECT
fullvisitorid, txn_date, date, DATE_DIFF(txn_date,date,day) AS date_diff,
visitid, PV_count
FROM
kentou
LEFT JOIN
yoyaku
USING (fullvisitorid)
WHERE txn_date IS NOT NULL
ORDER BY 1,3
;
（一般公開のGoogleアナリティクスのサンプルデータを使っています。）
WITH句で2つのテーブルを作成しています。yoyakuにはユーザー単位で予約日を格納しており、WHERE句で予約したレコードのみに絞っています。kentouはユーザーのサイト訪問全ての日とvisitid、PV数を集計しています。
その2つをfullvisitoridをキーにJOINして実行すると以下のように、予約日(txn_date)とすべてのサイト訪問日(date)のカラムができています。また、DATE_DIFF関数で予約日と訪問日の差分を取っています。
上のユーザーでは7月18日にトランザクションが発生していますが、7月24日にも訪問していますのでそれを除外して予約日までの訪問回数も計算したいと思います。尚、GoogleアナリティクスのサンプルデータはPVは1しか入っていません。
WITH
yoyaku AS (
SELECT
fullvisitorid,parse_DATE('%Y%m%d', date) AS txn_date,
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_table_suffix BETWEEN '20170701' AND '20170731'
AND totals.transactions IS NOT NULL),
kentou AS (
SELECT
fullvisitorid,parse_DATE('%Y%m%d', date) AS date,visitid,
COUNT(totals.pageviews) AS PV_count,
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_table_suffix BETWEEN '20170701' AND '20170731'
AND totals.visits IS NOT NULL
GROUP BY 1,2,3 )
SELECT
fullvisitorid,
COUNT(visitid) AS total_sessions,
COUNT(PV_count) AS total_PV
FROM (
SELECT
fullvisitorid, txn_date, date, DATE_DIFF(txn_date,date,day) AS date_diff,
visitid, PV_count
FROM
kentou
LEFT JOIN
yoyaku
USING (fullvisitorid)
WHERE txn_date IS NOT NULL
ORDER BY 1,3)
WHERE
date <= txn_date
AND date_diff <= 10
GROUP BY 1
;
yoyaku AS (
SELECT
fullvisitorid,parse_DATE('%Y%m%d', date) AS txn_date,
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_table_suffix BETWEEN '20170701' AND '20170731'
AND totals.transactions IS NOT NULL),
kentou AS (
SELECT
fullvisitorid,parse_DATE('%Y%m%d', date) AS date,visitid,
COUNT(totals.pageviews) AS PV_count,
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_table_suffix BETWEEN '20170701' AND '20170731'
AND totals.visits IS NOT NULL
GROUP BY 1,2,3 )
SELECT
fullvisitorid,
COUNT(visitid) AS total_sessions,
COUNT(PV_count) AS total_PV
FROM (
SELECT
fullvisitorid, txn_date, date, DATE_DIFF(txn_date,date,day) AS date_diff,
visitid, PV_count
FROM
kentou
LEFT JOIN
yoyaku
USING (fullvisitorid)
WHERE txn_date IS NOT NULL
ORDER BY 1,3)
WHERE
date <= txn_date
AND date_diff <= 10
GROUP BY 1
;
最初に示した結果をサブクエリにして、セッション数とPV数をカウントしていますが、最後のWHRER句が肝心のところです。まず、「date <= txn_date」で予約日より前のレコードに絞った上で、何日遡るのかを「date_diff <= 10」で指定しています。トランザクション日から20日遡りたければ20にすればいいわけです。
このようにすれば予約前だけに絞ってユーザーの行動を集計することができるようになります。
QwiklabsでGCPトレーニング
いつもは自身のアウトプットの意味も含めてクエリをポストしていますが、今回は番外編ということでQwiklabsを紹介したいと思います。
私自身は、SQLはUdemy 
にあったwebアナリティクス界の巨人、木田さんのSQL講座で学び、GoogleCloudPlatformについてはQwiklabsでトレーニングしています。
QwiklabsはGoogleが運営するオンライン学習環境で、GCPに関するものが多いですが、AWSに関する講座もあります。一つの講座をラボと読んでいます。
通常は有料ですが、新型コロナウイルスによる自学環境の拡大を受けて2020年5月2日現在では無料で利用することができます。
主な内容は以下のようなものです。
〇インフラストラクチャとDevOps
クラウド内でアプリケーションの実装、デプロイ、移行、保守を行います。
〇ビッグデータ
ビッグデータソリューションの設計、構築、分析、最適化を行います。
〇セキュリティ、バックアップ、リカバリ
コンプライアンスを維持し、情報、データアプリケーション、インフラストラクチャを保護します。
〇ウェブサイトとアプリ開発
クラウドでアプリケーションを開発するソフトウエアエンジニア向けです。
〇機械学習
スケーラブルな分散型機械学習モデルを作成します。
学習方法はまずここからアカウントを開設（自分のGoogleアカウントでよい）し、自分が受けたいラボの「ラボを開始」という緑色のボタンを押すと下のようにそのラボを受講するためのGoogleアカウントが発行されます。「Open Google Console」をクリックしてその専用のアカウントでログインすると開始できます。
問題を出されるケースもありますが、私が受けたBigQueryに関する講座はSQLがすでに書かれておりそれをコピーして実行結果を確認しながらすすめるような感じでした。「BQML で分類モデルを使用して訪問者の購入を予測する」のラボは、モデル作成、モデル評価、モデルによる予測といった流れになっており、ビジネスでもすぐに活用できそうな内容になっています。ラボで知り得たことを個人のBigQueryアカウントの一般公開GAデータを使って自分なりにクエリを書いて実践してみると効果的な訓練になると思います。
日本語に翻訳されていない講座も多くありますが、GCPの理解を進めるには効果的だと思います。
