[Python] BigQueryに蓄積されたGA4データを取得してエクセルに出力

今回は、Big Query_(※1)にエクスポートされたGA4のデータを取得して、エクセルファイルに出力するPythonスクリプトをご紹介します。

※1 : Google Cloud Platform (GCP) が提供するデータベース

前回と同様、今回もいろんな方のスクリプトを切り貼りして、試行錯誤しながら作ったので、元ネタが何だったかわからなくなってしまいました。
重ね重ね大変申し訳ないですm(_ _)m

やっている処理は前回のAPI版とだいたい同じですが、今回は

スクリプト内にBigQuery操作用のSQL文が入っている
今回は取得データをスプレッドシートではなくエクセルファイルに出力している

の2点が異なります。下記の仕様についても前回と同じです。

スクリプトを実行すると、シート名「YYYY-MM-DD〜YYYY-MM-DD」の新規シートを作成し、前月のレポートを出力します。
もし同名のシートが作成済みの場合は、そのまま上書きしてしまうのでご注意ください。
（同一シートの末尾にデータ追加していくスクリプトも作成可能）
なお、定期実行される場合は、毎月3日0時ぐらいに設定するのをお勧めします。データ反映に24時間以上かかっている事例を複数見ていますので、くれぐれもご注意ください。

それでは冒頭から。

まずは、各ライブラリのインポートと、Big Queryにアクセスするための鍵ファイル (json) の読み込みなど。前回のAPI版に比べると、使用するライブラリがかなり少ないですね。

import os
import pandas as pd
import datetime
from google.cloud import bigquery

creds= '/xxxxx/xxxxx/xxxxxxxxxx.json'

os.environ['GOOGLE_APPLICATION_CREDENTIALS']=creds

client= bigquery.Client()

そして次が、BigQueryからデータ抽出するためのSQL文。
「q」という変数の中にコメントの形でSQL文を入れています。

q= ('''
select
    (select value.string_value from unnest(event_params) where key = 'page_title') as page_title, --ページタイトルをイベントパラメータから取得
    (select value.string_value from unnest(event_params) where key = 'page_location') as page_location, --ページURLをイベントパラメータから取得
    event_date,
    count(event_name) as pageviews, --イベント数をカウントする。対象イベントはwhere内で指定
    count(distinct (select value.int_value from unnest(event_params) where key = 'engagement_time_msec')) as engagement_count, --エンゲージメント数をパラメータからカウントする。
from
    `xxxxxxxxxxxxxx.analytics_xxxxxxxxx.events_*`   -- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
where
  _TABLE_SUFFIX BETWEEN
    FORMAT_DATE("%Y%m%d",DATE_TRUNC(DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 1 MONTH), MONTH)) AND
    FORMAT_DATE("%Y%m%d",DATE_SUB(DATE_TRUNC(CURRENT_DATE('Asia/Tokyo'), MONTH), INTERVAL 1 DAY))
    and  event_name = 'page_view' -- イベント名を指定
 
group by 
    event_date, -- 日付でグルーピング
    page_title, -- ページタイトルでグルーピング
    page_location -- ページURLでグルーピング
 
order by 
    pageviews desc -- ページビュー数降順で並び替え
'''
)

ここでポイントなのが、2行目から3行目の「unnest」というところです。

下図1のように、ページタイトルやページURLなどのイベントパラメータは「page_view」の子階層におり、そのままではデータを抽出できないため、必要なデータを「unnest」で引っ張り出してやってから、抽出しているようです。

図1 : Big Queryのデータは、イベント＞イベントパラメータの親子関係でテーブルがネストされているため、データ抽出時は「unnest」でネストされているデータを引っ張り出す必要がある

select
(select value.string_value from unnest(event_params) where key = ‘page_title’) as page_title, –ページタイトルをイベントパラメータから取得
(select value.string_value from unnest(event_params) where key = ‘page_location’) as page_location, –ページURLをイベントパラメータから取得
↑上記スクリプトの「unnest」によってイベントパラメータの値を引き出している

SQLからのデータ取得が終わったら、変数 query でデータを受け取り、
「.result().to_dataframe() 」で DataFrame に変換しています。

query= client.query(q)

df= query.result().to_dataframe()
lastmonth = df.at[0, 'event_date']
lastmonth = datetime.datetime.strptime(lastmonth, '%Y%m%d')
lastmonth = lastmonth.strftime('%Y年%m月の計測結果')
df = df.drop('event_date', axis=1)
df.columns = ['ページタイトル', 'URL', 'PV数', 'エンゲージメント数']
df['エンゲージメント率'] = df['エンゲージメント数'] / df['PV数']
df.index = df.index + 1

今回はフィルタ設定は行いませんでしたが、代わりにエンゲージメント率の列を
df[‘エンゲージメント率’] = df[‘エンゲージメント数’] / df[‘PV数’]
の行で追加してみました。旧GAの計算指標のようなものですね。

最後に、今回はスプレッドシートではなく、エクセルデータにダウンロードさせてみました。
と言っても、
df.to_excel(‘ファイル名’, sheet_name=’シート名’)
の1行で済むので、スプレッドシートに比べて、かなり楽でした。

df.to_excel('BigQUery-GA4_'+lastmonth+'.xlsx', sheet_name=lastmonth)

print(lastmonth + 'を出力しました') #実行完了の確認用

全体では、下記のようになります。

▼今回作成したスクリプト（全体）

import os
import pandas as pd
import datetime
from google.cloud import bigquery

creds= '/xxxxx/xxxxx/xxxxxxxxxx.json'

os.environ['GOOGLE_APPLICATION_CREDENTIALS']=creds

client= bigquery.Client()

q= ('''
select
    (select value.string_value from unnest(event_params) where key = 'page_title') as page_title, --ページタイトルをイベントパラメータから取得
    (select value.string_value from unnest(event_params) where key = 'page_location') as page_location, --ページURLをイベントパラメータから取得
    event_date,
    count(event_name) as pageviews, --イベント数をカウントする。対象イベントはwhere内で指定
    count(distinct (select value.int_value from unnest(event_params) where key = 'engagement_time_msec')) as engagement_count, --エンゲージメント数をパラメータからカウントする。
from
    `xxxxxxxxxxxxxx.analytics_xxxxxxxxx.events_*`   -- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
where
  _TABLE_SUFFIX BETWEEN
    FORMAT_DATE("%Y%m%d",DATE_TRUNC(DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 1 MONTH), MONTH)) AND
    FORMAT_DATE("%Y%m%d",DATE_SUB(DATE_TRUNC(CURRENT_DATE('Asia/Tokyo'), MONTH), INTERVAL 1 DAY))
    and  event_name = 'page_view' -- イベント名を指定
 
group by 
    event_date, -- 日付でグルーピング
    page_title, -- ページタイトルでグルーピング
    page_location -- ページURLでグルーピング
 
order by 
    pageviews desc -- ページビュー数降順で並び替え
'''
)

query= client.query(q)

df= query.result().to_dataframe()
lastmonth = df.at[0, 'event_date']
lastmonth = datetime.datetime.strptime(lastmonth, '%Y%m%d')
lastmonth = lastmonth.strftime('%Y年%m月の計測結果')
df = df.drop('event_date', axis=1)
df.columns = ['ページタイトル', 'URL', 'PV数', 'エンゲージメント数']
df['エンゲージメント率'] = df['エンゲージメント数'] / df['PV数']
df.index = df.index + 1

df.to_excel('BigQUery-GA4_'+lastmonth+'.xlsx', sheet_name=lastmonth)

print(lastmonth + 'を出力しました') #実行完了の確認用

以上、ご参考になれば幸いです。