こんにちは、エクスチュアの權泳東(権泳東/コン・ヨンドン)です。
Google Analytics 4 (以下GA4)がリリースされましたね。
というわけでGA4のBigQueryエクスポート機能を使って早速データを抽出してみましょう！
以前、GA360+BigQueryで私がよく使う基本的なSQL例を紹介しましたが、今回はそれのGA4バージョンです。
1. ページビュー数
GA4の日別ページビューを出してみます。
タイムゾーンを任意で変更したいので、event_dateカラムではなくevent_timestampを「Asia/Tokyo」にして使ってます。
それがメンドクサイならevent_dateでもOK。
event_name = ‘page_view’ に絞るとページビューになる。
※アプリのスクリーンビューは’screen_view’を使う
SELECT DATE(timestamp_micros(event_timestamp), 'Asia/Tokyo') AS hitdate, COUNT(1) AS pageviews FROM `analytics_12345678.events_*` WHERE _TABLE_SUFFIX BETWEEN '20201001' AND '20201031' AND event_name = 'page_view' GROUP BY 1 ORDER BY 1
結果はこうなります。
日別ページビュー数
なお、ページURL別にページビューを出すならこうです。
SELECT (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location, COUNT(1) AS pageviews FROM `analytics_12345678.events_*` WHERE _TABLE_SUFFIX BETWEEN '20201001' and '20201031' AND event_name = 'page_view' GROUP BY 1 ORDER BY 2 DESC
ページURLは、event_params.key が「page_location」になっている行のstring_value列に入ってます。
event_paramsはネストされたRECORD型のカラムになってるので、UNNEST関数でフラットにしてあげる必要があります。
以前書いた、「GoogleAnalytics Apps+Web プロパティをBigQueryと連携したついでにフラット化する」の記事を参考。
結果ははこうなります。
ページ別PV数
2. セッション数
user_pseudo_id と event_params.key=ga_session_idのint_valueを文字列にしたものをCONCATして、ユニーク数をカウントしたのがセッション数。
これも日別で出します。
WITH
t1 AS (
SELECT
DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo') AS hitdate,
CONCAT(user_pseudo_id, CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') as STRING)) AS sid,
FROM
`analytics_12345678.events_*`
WHERE
_TABLE_SUFFIX BETWEEN '20201001'
AND '20201031'
AND event_name = 'session_start' )
SELECT
hitdate,
COUNT(DISTINCT sid) AS sessions
FROM t1
GROUP BY 1
ORDER BY 1
結果はこうなります。
日別セッション数
なお、Webとアプリ両方でUserIDを使ってる場合は user_pseudo_id の代わりに user_id を使えばクロスデバイスで同じユーザーとして扱えます。
3. ユーザー数
user_pseudo_id のユニーク数をカウントするとユーザー数。
下記はhitdateでGROUP BYしてるので、日別のユーザー数を出してます。
SELECT DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo') AS hitdate, COUNT(DISTINCT user_pseudo_id) AS users FROM `analytics_12345678.events_*` WHERE _TABLE_SUFFIX BETWEEN '20201001' AND '20201031' AND event_name = 'session_start' GROUP BY 1 ORDER BY 1
結果はこうなります。
日別ユーザー数
4. ページ別ランディング数・離脱数・直帰数
先程出したセッションIDを使います。
これをevent_timestampを昇順ソートして、一番目のページがランディングページ。
逆にevent_timestampを降順ソートしたら、一番目のページが離脱ページ。
そしてセッション中に見たページの数が「1」なら直帰。
event_name = page_viewで絞ってます。
WITH
t1 AS (
SELECT
event_timestamp,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
CONCAT(user_pseudo_id, CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') as STRING)) AS sid
FROM
`analytics_12345678.events_*`
WHERE
_TABLE_SUFFIX BETWEEN '20201001' and '20201031'
AND event_name = 'page_view'
)
,t2 AS (
SELECT
page_location,
CASE
WHEN ROW_NUMBER() OVER(PARTITION BY sid ORDER BY event_timestamp) = 1 THEN 1
ELSE 0
END AS is_entry,
CASE
WHEN ROW_NUMBER() OVER(PARTITION BY sid ORDER BY event_timestamp DESC) = 1 THEN 1
ELSE 0
END AS is_exit,
CASE
WHEN COUNT(1) OVER(PARTITION BY sid) = 1 THEN 1
ELSE 0
END AS is_single
FROM
t1
)
SELECT
t2.page_location AS page_location,
SUM(t2.is_entry) AS entry,
SUM(t2.is_exit) AS exit,
SUM(t2.is_single) AS singlevisit
FROM t2
GROUP BY 1
ORDER BY 2 DESC
結果はこうなります。
ランディング数・離脱数・直帰数
ただし、この書き方だと集計期間を跨いでる訪問の場合には集計期間中の最初に見たページがランディングページになるけどキニシナイ
5. ページ別合計滞在時間・平均滞在時間
これもセッションIDを使う。
ページ別に次ページとのタイムスタンプの差分を合算した秒数をページ別トータル滞在時間として抽出。
次ページとのタイムスタンプ差が1800秒以上あるものはセッションが切れてるものとして除外する。
滞在時間の合計を、ページのセッション数で割れば平均滞在時間になる。
event_name = page_viewに絞ってないので、スクロールイベントや離脱クリックなどのエンゲージイベントも滞在時間に含まれます。
WITH t1 AS (
SELECT
timestamp_micros(event_timestamp) AS event_timestamp,
CONCAT(user_pseudo_id, CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') as STRING)) AS sid,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location
FROM
`analytics_12345678.events_*`
WHERE
_TABLE_SUFFIX BETWEEN '20201001' and '20201031'
)
,t2 AS (
SELECT
event_timestamp,
sid,
page_location,
CASE
WHEN timestamp_diff(LEAD(event_timestamp) OVER(PARTITION BY sid ORDER BY event_timestamp), event_timestamp, second) > 1800 THEN NULL
ELSE LEAD(event_timestamp) OVER(PARTITION BY sid ORDER BY event_timestamp) END AS next_hit
FROM t1
)
SELECT
t2.page_location,
COUNT(DISTINCT t2.sid) AS sessions,
SUM(IFNULL(TIMESTAMP_DIFF(t2.next_hit, t2.event_timestamp, second), 0)) AS total_time,
ROUND(SAFE_DIVIDE(SUM(IFNULL(TIMESTAMP_DIFF(t2.next_hit, t2.event_timestamp, second), 0)), COUNT(DISTINCT t2.sid)), 2) AS avg_time
FROM t2
WHERE t2.page_location IS NOT NULL
GROUP BY 1
ORDER BY 2 DESC
結果はこうなります。
滞在時間の単位は「秒」です。
合計滞在時間・平均滞在時間
6. イベントパラメータの集計
サイト内検索ワードをイベントパラメータで計測してるという前提です。
view_search_resultsイベントが発生した時に、検索ワードがevent_params.key = search_term の string_value列に入るので、それを集計します。
SELECT (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'search_term') AS int_search_term, COUNT(1) AS int_search FROM `analytics_12345678.events_*` WHERE _TABLE_SUFFIX BETWEEN '20201001' and '20201031' AND event_name = 'view_search_results' GROUP BY 1 ORDER BY 2 DESC
結果はこうなります。
サイト内検索ワード
event_name と event_paramsのキー名はイベント内容に合わせて書き換えてください。
7. ユーザープロパティの集計
ユーザープロパティは event_params と同じく縦持ちのレコードなのでUNNEST関数でフラットにする必要があります。
ユーザープロパティの値別にユーザー数を出してみます。
SELECT (SELECT value.string_value FROM UNNEST(user_properties) WHERE key = 'weather') AS weather, COUNT(distinct user_pseudo_id) AS users FROM `analytics_12345678.events_*` WHERE _TABLE_SUFFIX BETWEEN '20201001' and '20201031' AND event_name = 'user_engagement' GROUP BY 1 ORDER BY 2 DESC
結果はこうなります。
ユーザープロパティ
event_name と user_propertiesのキー名はユーザープロパティをセットするイベント内容に合わせて書き換えてください。
まとめ
SQLでWeb解析ログを分析すると、GA4で集めたデータを自由にドリルダウン出来るのがメリットです。
GA4には標準でBigQueryエクスポート機能が備わっています。
みなさんもこの機会にぜひBigQueryを使った自由で柔軟なWeb解析を始めましょう。
弊社ではGoogleAnalytics/AdobeAnalyticsなどの各Martechツールの導入実装コンサルティングサービスや、GCP/AWSなどのパブリッククラウドを使ったデータ分析基盤構築コンサルティングサービスを提供しております。
お問い合わせはこちらからどうぞ。
