GA4でファイルダウンロード数を集計するSQLクエリ

本記事ではBigQueryにエクスポートしたGA4（Googleアナリティクス4）のデータを用いて、ファイルダウンロード数を集計するSQLクエリについて解説をおこなう。UAではファイルダウンロードをトラッキングするためには、仮想ページビューやカスタムイベントを設定する等の工数が発生していたが、GA4では拡張計測機能による自動収集イベントに含まれているため、データ取得が簡単になっている。ぜひSQLを書いて活用していきたい。

SQLクエリの概要

まず、GA4における file_download イベントの仕様は以下のようになっている。

ユーザーが次のタイプの（一般的なファイル拡張子の）ファイルに移動するリンクをクリックすると記録されます。
　・ドキュメント
　・テキスト
　・実行可能ファイル
　・プレゼンテーション
　・圧縮ファイル
　・動画
　・音声

次の正規表現に一致するファイル拡張子で、イベントがトリガーされます。

pdf|xlsx?|docx?|txt|rtf|csv|exe|key|pp(s|t|tx)|7z|pkg|
rar|gz|zip|avi|mov|mp4|mpe?g|wmv|midi?|mp3|wav|wma

パラメータによって次のディメンションが入力されます。
　・ファイル拡張子（file_extension から）
　・ファイル名（file_name から）
　・リンクのクラス（link_classes から）
　・リンクドメイン（link_domain から）
　・リンク ID（link_id から）
　・リンクテキスト（link_text から）
　・リンク URL（link_url から）

出典 : [GA4] 拡張イベント計測機能

下記のサンプルクエリでは、 file_extension , file_name , link_text, link_url の4つのパラメータを使用して集計をおこなっている。

また、特定のファイル形式（PDFなど）のみダウンロード数を集計したい場合は、さらにWHERE句でfile_extension を絞り込めば集計できる。

SQLクエリサンプル

今回のSQLクエリは、以下のようになる。

SELECT
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'file_extension') AS file_type,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'file_name') AS file_name,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'link_text') AS link_text,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'link_url') AS link_url,
  COUNT(*) AS download_count,
FROM
  `technogram-354804.analytics_291973511.events_intraday_202303*`
WHERE
  event_name = 'filedownload'
GROUP BY
  file_type,
  file_name,
  link_text,
  link_url
HAVING
  file_type IS NOT NULL
ORDER BY
  download_count DESC
;

※今回は、弊社が保有しているデータセット及びGA4のサンプルデータセットにおいて、ファイルダウンロードイベントが発生しているデータがないため、BigQueryでの集計結果は省略させていただく。

ChatGPT（GPT-4モデル）によるSQL解説

今回もChatGPTによる解説を付すので、学習の参考にされたい。

このSQLクエリは、ウェブサイトやアプリでのファイルダウンロードイベントを分析し、各種類のファイルがダウンロードされた回数をカウントするものです。

具体的には、次の情報を選択しています：

1.ファイルタイプ：(SELECT value.string_value FROM UNNEST(event_params) WHERE key = ‘file_extension’) AS file_type。これはevent_paramsという配列型のカラムから、キーがfile_extensionのものを探し出し、その値を取得しています。

2.ファイル名：(SELECT value.string_value FROM UNNEST(event_params) WHERE key = ‘file_name’) AS file_name。これはevent_paramsから、キーがfile_nameのものを探し出し、その値を取得しています。

3.リンクテキスト：(SELECT value.string_value FROM UNNEST(event_params) WHERE key = ‘link_text’) AS link_text。これはevent_paramsから、キーがlink_textのものを探し出し、その値を取得しています。

4.リンクのURL：(SELECT value.string_value FROM UNNEST(event_params) WHERE key = ‘link_url’) AS link_url。これはevent_paramsから、キーがlink_urlのものを探し出し、その値を取得しています。

5.ダウンロードカウント：COUNT(*) AS download_count。これはfiledownloadというイベントの数をカウントしています。

FROM句では、データセットtechnogram-354804.analytics_291973511.events_intraday_202303*からデータを取得します。ここで*を使って、events_intraday_202303で始まるすべてのテーブルを対象にしています。

またWHERE句では、イベント名がfiledownloadであるレコードのみを選択しています。

さらにGROUP BY句では、結果をファイルタイプ、ファイル名、リンクテキスト、リンクURLによりグループ化します。これにより、同じタイプの同じファイルのダウンロードは一つにまとまります。

そしてHAVING句では、ファイルタイプがNULLでないレコードのみを対象にしています。

最後に、ORDER BY句で結果をダウンロードカウントの降順に並べます。これにより、最もダウンロードされたファイルが先頭に表示されます。

このクエリを実行することで、各種類のファイルがダウンロードされた回数を取得でき、どのコンテンツが最も価値があるかを理解するための重要な情報を得ることができます。

今回の記事では、ファイルダウンロード数を集計するSQLクエリについて解説を行った。GA4でのファイルダウンロード数を、BigQueryでも集計する際の参考にしてほしい。

GA4でファイルダウンロード数を集計するSQLクエリ | GA4QUERY

SQLクエリの概要

SQLクエリサンプル

ChatGPT（GPT-4モデル）によるSQL解説