GA4×BigQueryで実現する顧客行動分析 – SQLクエリの実践例

アクセス解析
著者について

デジタルマーケティングの世界で、Google Analytics 4（GA4）とBigQueryの連携が注目を集めています。この組み合わせにより、従来のGA4の分析機能では実現できなかった、より深い顧客行動分析が可能になりました。本記事では、GA4とBigQueryを活用した顧客行動分析の実践例を、具体的なSQLクエリとともに解説します。デジタルマーケティング担当者の皆様が、より高度なデータ分析スキルを身につけ、効果的なマーケティング戦略を立案するための一助となれば幸いです。

GA4とBigQueryの連携の基本

まず、GA4とBigQueryの連携の基本について押さえておきましょう。GA4は、ウェブサイトやアプリのユーザー行動データを収集するツールです。一方、BigQueryは、Googleが提供する大規模データ分析プラットフォームで、膨大なデータを高速に処理できる特徴があります。GA4とBigQueryを連携させることで、GA4で収集した詳細なイベントデータをBigQuery上で自由に分析できるようになります。連携の設定は比較的簡単で、GA4の管理画面からBigQueryとのリンクを有効にするだけです。設定後、GA4のデータは自動的にBigQueryにエクスポートされ、SQLクエリを使って分析できるようになります。

顧客セグメンテーション分析

顧客セグメンテーションは、効果的なマーケティング戦略を立てる上で重要な分析の一つです。BigQueryを使用することで、より複雑で詳細なセグメンテーションが可能になります。以下は、購入金額と購入頻度に基づいて顧客をセグメント分けするSQLクエリの例です：

sql
WITH user_metrics AS (
SELECT
user_pseudo_id,
SUM(ecommerce.purchase_revenue) AS total_revenue,
COUNT(DISTINCT event_timestamp) AS purchase_count
FROM
`your_project.your_dataset.events_*`
WHERE
event_name = 'purchase'
AND _TABLE_SUFFIX BETWEEN '20230101' AND '20231231'
GROUP BY
user_pseudo_id
)
SELECT
CASE
WHEN total_revenue > 100000 AND purchase_count > 10 THEN 'VIP'
WHEN total_revenue > 50000 OR purchase_count > 5 THEN 'Loyal'
ELSE 'Regular'
END AS customer_segment,
COUNT(*) AS user_count,
AVG(total_revenue) AS avg_revenue
FROM
user_metrics
GROUP BY
customer_segment
ORDER BY
avg_revenue DESC

このクエリでは、まず各ユーザーの総購入金額と購入回数を計算し、その結果に基づいて顧客を「VIP」「Loyal」「Regular」の3つのセグメントに分類しています。さらに、各セグメントのユーザー数と平均購入金額を算出しています。

ユーザージャーニー分析

ユーザージャーニー分析は、顧客がコンバージョンに至るまでの道筋を理解するために重要です。BigQueryを使用することで、複雑なユーザージャーニーを詳細に分析できます。以下は、購入に至るまでの主要なタッチポイントを分析するSQLクエリの例です：

sql
WITH user_journey AS (
SELECT
user_pseudo_id,
event_name,
event_timestamp,
ROW_NUMBER() OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS event_order
FROM
`your_project.your_dataset.events_*`
WHERE
_TABLE_SUFFIX BETWEEN '20230101' AND '20231231'
AND event_name IN ('page_view', 'view_item', 'add_to_cart', 'purchase')
)
SELECT
event_order,
event_name,
COUNT(*) AS event_count,
COUNT(*) / SUM(COUNT(*)) OVER () AS event_percentage
FROM
user_journey
WHERE
user_pseudo_id IN (
SELECT DISTINCT user_pseudo_id
FROM user_journey
WHERE event_name = 'purchase'
)
GROUP BY
event_order, event_name
ORDER BY
event_order, event_count DESC

このクエリでは、購入に至ったユーザーの行動シーケンスを分析し、各ステップでのイベント数とその割合を算出しています。これにより、どのタッチポイントが購入に重要な役割を果たしているかを把握できます。

コホート分析

コホート分析は、特定の期間にアクションを起こしたユーザーグループの長期的な行動を追跡する手法です。BigQueryを使用することで、より柔軟で詳細なコホート分析が可能になります。以下は、ユーザーの初回購入月ごとのリテンション率を分析するSQLクエリの例です：

sql
WITH first_purchase AS (
SELECT
user_pseudo_id,
DATE_TRUNC(PARSE_DATE('%Y%m%d', event_date), MONTH) AS cohort_month,
MIN(event_timestamp) AS first_purchase_time
FROM
`your_project.your_dataset.events_*`
WHERE
event_name = 'purchase'
GROUP BY
user_pseudo_id, cohort_month
),
user_activity AS (
SELECT
fp.user_pseudo_id,
fp.cohort_month,
DATE_DIFF(DATE_TRUNC(PARSE_DATE('%Y%m%d', e.event_date), MONTH), fp.cohort_month, MONTH) AS months_since_first_purchase
FROM
first_purchase fp
JOIN
`your_project.your_dataset.events_*` e
ON
fp.user_pseudo_id = e.user_pseudo_id
AND e.event_timestamp > fp.first_purchase_time
WHERE
e.event_name = 'purchase'
)
SELECT
cohort_month,
months_since_first_purchase,
COUNT(DISTINCT user_pseudo_id) AS retained_users,
COUNT(DISTINCT user_pseudo_id) / FIRST_VALUE(COUNT(DISTINCT user_pseudo_id)) OVER (PARTITION BY cohort_month ORDER BY months_since_first_purchase) AS retention_rate
FROM
user_activity
GROUP BY
cohort_month, months_since_first_purchase
ORDER BY
cohort_month, months_since_first_purchase

このクエリでは、ユーザーの初回購入月を特定し、その後の各月における購入活動を追跡しています。これにより、時間の経過とともにユーザーがどの程度リピート購入しているかを分析できます。

商品レコメンデーション分析

効果的な商品レコメンデーションは、クロスセルやアップセルの機会を増やすために重要です。BigQueryを使用することで、購入履歴に基づいた高度な商品レコメンデーション分析が可能になります。以下は、よく一緒に購入される商品ペアを分析するSQLクエリの例です：

sql
WITH product_pairs AS (
SELECT
e1.ecommerce.items.item_id AS product1,
e2.ecommerce.items.item_id AS product2,
COUNT(*) AS pair_count
FROM
`your_project.your_dataset.events_*` e1
JOIN
`your_project.your_dataset.events_*` e2
ON
e1.user_pseudo_id = e2.user_pseudo_id
AND e1.event_timestamp < e2.event_timestamp
AND DATE_DIFF(DATE(e2.event_timestamp), DATE(e1.event_timestamp), DAY) <= 30
WHERE
e1.event_name = 'purchase'
AND e2.event_name = 'purchase'
AND e1.ecommerce.items.item_id != e2.ecommerce.items.item_id
GROUP BY
product1, product2
)
SELECT
product1,
product2,
pair_count,
pair_count / SUM(pair_count) OVER (PARTITION BY product1) AS confidence
FROM
product_pairs
WHERE
pair_count > 10
ORDER BY
pair_count DESC, confidence DESC
LIMIT 100

このクエリでは、30日以内に同じユーザーによって購入された商品ペアを分析し、その購入頻度と信頼度（一方の商品が購入された際に、もう一方の商品も購入される確率）を算出しています。これにより、効果的な商品レコメンデーションの基礎データを得ることができます。

アトリビューション分析

マルチチャネルマーケティングの効果を正確に評価するためには、高度なアトリビューション分析が必要です。BigQueryを使用することで、カスタムアトリビューションモデルの構築が可能になります。以下は、ラストクリックアトリビューションモデルを実装するSQLクエリの例です：

sql
WITH user_touchpoints AS (
SELECT
user_pseudo_id,
event_timestamp,
event_name,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'source') AS source,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'medium') AS medium,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'campaign') AS campaign,
ecommerce.purchase_revenue AS revenue
FROM
`your_project.your_dataset.events_*`
WHERE
_TABLE_SUFFIX BETWEEN '20240101' AND '20240831'
AND (event_name = 'purchase' OR event_name = 'session_start')
),
last_touch AS (
SELECT
user_pseudo_id,
LAST_VALUE(source IGNORE NULLS) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS last_source,
LAST_VALUE(medium IGNORE NULLS) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS last_medium,
LAST_VALUE(campaign IGNORE NULLS) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS last_campaign,
revenue
FROM
user_touchpoints
WHERE
event_name = 'purchase'
)
SELECT
last_source,
last_medium,
last_campaign,
COUNT(*) AS conversion_count,
SUM(revenue) AS total_revenue
FROM
last_touch
GROUP BY
last_source, last_medium, last_campaign
ORDER BY
total_revenue DESC

このクエリでは、各ユーザーの最後のタッチポイント（ソース、メディウム、キャンペーン）を特定し、それに基づいてコンバージョン数と売上を集計しています。これにより、どのマーケティングチャネルが最終的な購入決定に影響を与えているかを分析できます。

まとめと今後の展望

GA4とBigQueryの連携により、デジタルマーケティング担当者は従来よりも深い顧客行動分析を行うことが可能になりました。本記事で紹介したSQLクエリ例は、その可能性のほんの一部に過ぎません。これらの分析手法を組み合わせ、自社のビジネスモデルに合わせてカスタマイズすることで、より効果的なマーケティング戦略の立案が可能になるでしょう。今後は、機械学習やAIとの連携がさらに進み、予測分析や自動最適化などの高度な機能が実現されると予想されます。デジタルマーケティング担当者には、これらの新しい技術やツールに常にアンテナを張り、スキルアップを続けることが求められます。GA4とBigQueryを活用した高度なデータ分析は、専門的な知識とスキルが必要です。

株式会社インティメート・マージャーのサービスへの

お問い合わせはこちら