GA4で動画エンゲージメント関連の数値を集計するSQLクエリ

本記事ではBigQueryにエクスポートしたGA4（Googleアナリティクス4）のデータを用いて、動画エンゲージメント関連の数値を集計するSQLクエリについて解説をおこなう。UAでは動画関連のインタラクションをトラッキングするためには、Googleタグマネージャでの追加設定が必要だったが、GA4では拡張計測機能による自動収集イベントに含まれているため、データ取得が簡単になっている。ぜひSQLを書いて活用していきたい。

SQLクエリの概要

まず、GA4における動画エンゲージメント関連のイベントの定義は以下のようになっている。

JavaScript API サポートが有効になっている埋め込み動画では、次のイベントが記録されます。

・video_start: 動画の再生が開始されたとき

・video_progress: 動画が再生時間の 10%、25%、50%、75% 以降まで進んだとき

・video_complete: 動画が終了したとき

パラメータによって次のディメンションが入力されます。

・動画プロバイダ（video_provider から）

・動画のタイトル（video_title から）

・動画の URL（video_url から）

・表示（visible から）

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

下記のサンプルクエリでは、以下の数値を動画のプロバイダ、タイトル、URLごとに集計している。

動画の再生開始数
動画の50％視聴完了数
動画の視聴完了数

SQLクエリサンプル

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

SELECT
  (SELECT value.string_value FROM UNNEST(event_params) WHERE event_name LIKE 'video%' AND key = 'video_provider') AS provider,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE event_name LIKE 'video%' AND key = 'video_title') AS title,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE event_name LIKE 'video%' AND key = 'video_url') AS url,
  COUNTIF(event_name = 'video_start') AS start_count,
  COUNTIF(event_name = 'video_progress' AND (SELECT value.int_value FROM UNNEST(event_params) WHERE event_name = 'video_progress' AND key = 'percent') = 50) AS progress_50_percent_count,
  COUNTIF(event_name = 'video_complete') AS complete_count,
FROM
    `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*` 
GROUP BY 
  provider,
  title,
  url
HAVING
  provider IS NOT NULL
ORDER BY 
  progress_50_percent_count DESC
;

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

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

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

このSQLクエリは、特定のビデオ関連のイベントの発生回数を集計し、それらの結果をビデオの提供者、タイトル、URL別に表示します。このクエリはGoogle Analytics 4（GA4）のイベントデータセットに対して実行されます。

具体的には、以下のような操作を行います。

1.サブクエリを使用して、イベント名が ‘video%’（’video’で始まる任意のイベント名）であるイベントパラメータから、ビデオの提供者（video_provider）、タイトル（video_title）、URL（video_url）を抽出します。

2.各ビデオについて、特定のビデオイベント（’video_start’, ‘video_progress’, ‘video_complete’）の発生回数を集計します。そのうち、’video_progress’イベントについては、パーセンテージが50％であるもののみをカウントします。

3.結果は、ビデオの提供者（provider）、タイトル（title）、URL（url）ごとにグループ化されます。

4.HAVING句を使用して、提供者（provider）がNULLでない行のみを絞り込みます。

5.最後に、50%の進行が報告されたビデオイベントの回数（progress_50_percent_count）に基づいて結果を降順にソートします。

このクエリは、特定のビデオがどのくらいの頻度で開始、進行（特に50％のマイルストーン）、完了されているかを調査するために使用することができます。また、ビデオの提供者、タイトル、URLごとに結果を分けることで、どのビデオがユーザーにとって魅力的であるか、またはどのビデオが視聴を途中でやめる傾向があるかを調査することも可能です。

今回の記事では、動画エンゲージメント関連の数値を集計するSQLクエリについて解説を行った。データの取得がUAよりもGA4の方が簡単になったので、ぜひBigQueryで動画エンゲージメント関連の数値を集計するときの参考にしてほしい。

GA4で動画エンゲージメント関連の数値を集計するSQLクエリ | GA4QUERY

SQLクエリの概要

SQLクエリサンプル

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