SQLで実現するベン図（セグメントの重複）BigQuery x Looker Studio

まず初めに求めている情報とは違った場合、読み進めても意味がないかもしれないので、ここでトライする環境について記載する。今回、ベン図の可視化にいたっては、GA4からBigQueryに蓄積されたサイト訪問者のデータをSQLで抽出し、Looker Studioでベン図の可視化を行うものである。このようにGA4からBigQueryに蓄積されたデータを取り扱うため、汎用的なSQLにはなっていない。またベン図の可視化にいたってもLooker Studioに備わっているベン図を作成する機能を使うため、SQLで取り出したデータの持ち方に関しても、ベン図を作成するためのツールが指定しているもので汎用的に活用できるものではないかもしれない。

GA4の探索の中にも「セグメントの重複」があり円と円の重なりのビジュアライズを可能にするベン図が作成できるが、レポートとしての共有に不向きであったり、設定できるセグメントの上限が3つであったりする。そのためこれらのデメリットを克服したカスタマイズに優れたベン図を作成したく、SQLでデータを抽出してLookerStudioで可視化する方法について解説する。

もちろんGA4のベン図（セグメントの重複）で済むのであれば、わざわざSQLで可視化する必要もない。でもGA4のセグメントの重複が正常に機能しているのかという疑問もあるが

≫ [GA4] セグメントの重複 -アナリティクスヘルプ

広告施策のアシスト効果をベン図で可視化

ベン図の作成用途としては諸々あると思うけど、ここでは円の重なり具合により広告施策のアシスト効果を可視化することで、どの広告施策に注力すればよいかを見極める目的としてベン図を作成する。つまりは円と円の重なりが大きい程、広告施策の相乗効果が高いという意味になる。

思うに広告のアシスト効果の可視化にはベン図が最も適しているのではないか？という考えが以前からあった。GA4になり生ログをBigQueryに出力できるようになり、ベン図によるアシスト効果を可視化できる環境が整ったことで、実現可能になったテクニックといえる。

ここではシンプルなSQLを示すだけに留めるが、このSQLをベースにして重ねる円グラフを増やすこともできるし（Looker Studioのベン図作成ツールの重ねる円グラフの上限はあるが）、セグメントの粒度も変更が可能だ。例えば、選択するセグメントを「キャンペーン」にすることで、1商品の注文に至るまでに、Google広告のどのキャンペーン間で相乗効果が高いかを視覚的に判別することが可能になる。

Looker Studioの「Venn Diagram」によりベン図をビジュアライズ

ベン図の可視化にはLooker Studioのコミュニティビジュアリゼーションのコンポーネントである「Venn Diagram」を使う。

Community visualizations > Venn Diagram
Venn Diagram – Usage Guide

ようはこのコンポーネントが指定するデータソースの形式に則るようにSQLでデータを抽出し、Looker Studioでそのまま可視化を行うのであればカスタムクエリにSQLを記載すればいい。

SQLで実現するベン図（セグメントの重複）BigQuery x Looker Studio

ここで記載するSQLは単純に2つの円グラフが重なったベン図を可視化するための最小限のSQLとなる。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
/* 注文したユーザーセグメント */
WITH cv_users AS(
  SELECT
    DISTINCT user_pseudo_id
  FROM
    `イベント名.events_*`
  WHERE
    _TABLE_SUFFIX BETWEEN @DS_START_DATE AND @DS_END_DATE
  AND
    event_name = "purchase"
)

/* 検索経由の注文ユーザー数 */
SELECT
  '検索' AS dimension,
  COUNT(DISTINCT metric) AS metric
FROM(
  SELECT
    CASE
      WHEN
        REGEXP_CONTAINS((SELECT VALUE.string_value FROM UNNEST(event_params) WHERE KEY = 'medium'),'(cpc|xxx|yyy)')
          THEN user_pseudo_id
      ELSE NULL
    END AS metric
  FROM
    `イベント名.events_*`  
  WHERE
    _TABLE_SUFFIX BETWEEN @DS_START_DATE AND @DS_END_DATE
  AND
    user_pseudo_id IN (SELECT user_pseudo_id FROM cv_users)
)
WHERE
  metric IS NOT NULL

UNION ALL

/* ディスプレイ経由の注文ユーザー数 */
SELECT
  'ディスプレイ' AS dimension,
  COUNT(DISTINCT metric) AS metric
FROM(
  SELECT
    CASE
      WHEN
        REGEXP_CONTAINS((SELECT VALUE.string_value FROM UNNEST(event_params) WHERE KEY = 'medium'),'(display|xxx|yyy)')
          THEN user_pseudo_id
      ELSE NULL
    END AS metric
  FROM
    `イベント名.events_*`  
  WHERE
    _TABLE_SUFFIX BETWEEN @DS_START_DATE AND @DS_END_DATE
  AND
    user_pseudo_id IN (SELECT user_pseudo_id FROM cv_users)
)
WHERE
  metric IS NOT NULL

UNION ALL

/* 検索&ディスプレイ経由の注文ユーザー数 */
SELECT
  '検索,ディスプレイ' AS dimension,
  COUNT(DISTINCT metric) AS metric
FROM(
  SELECT
    metric AS metric
  FROM(
    SELECT
      CASE
        WHEN
          REGEXP_CONTAINS((SELECT VALUE.string_value FROM UNNEST(event_params) WHERE KEY = 'medium'),'(cpc|xxx|yyy)')
            THEN user_pseudo_id
        ELSE NULL
      END AS metric
    FROM
      `イベント名.events_*`  
    WHERE
      _TABLE_SUFFIX BETWEEN @DS_START_DATE AND @DS_END_DATE
    AND
      user_pseudo_id IN (SELECT user_pseudo_id FROM cv_users)
  )
  WHERE
    metric IS NOT NULL

INTERSECT DISTINCT

  SELECT
    metric AS metric
  FROM(
    SELECT
      CASE
        WHEN
          REGEXP_CONTAINS((SELECT VALUE.string_value FROM UNNEST(event_params) WHERE KEY = 'medium'),'(display|xxx|yyy)')
            THEN user_pseudo_id
        ELSE NULL
      END AS metric
    FROM
      `イベント名.events_*`  
    WHERE
      _TABLE_SUFFIX BETWEEN @DS_START_DATE AND @DS_END_DATE
    AND
      user_pseudo_id IN (SELECT user_pseudo_id FROM cv_users)
  )
  WHERE
    metric IS NOT NULL
)

WITH句でCVしたユーザーに絞り込む

まず先頭でフィルターをかけたいイベントで絞り込んだユーザーのみ抽出する。ここで該当したユーザーに絞り込むには、続く処理の中でWHERE条件で以下SQLで絞り込むをかける。

user_pseudo_id in (SELECT user_pseudo_id FROM cv_users)

セグメントに該当する箇所

REGEXP_CONTAINS((SELECT value.string_value FROM UNNEST(event_params) WHERE key = ‘medium’),'(cpc|xxx|yyy)’)

セグメントは「key」の値を変更する。またREGEXP_CONTAINSを使うことで条件指定にも対応している。↑の例でいえば、「medium」が「cpc」である場合という意味。

カスタムクエリで期間を動的にする

「@DS_START_DATE」と「@DS_END_DATE」を記述することで自動で、Looker Studioの期間のコントロールの開始日・終了日に置き換わる。

≫ カスタムクエリでパラメータを使用する -Looker Studioのヘルプ

分析SQLに特化した書籍はこちらがおすすめ

集中演習 SQL入門 Google BigQueryではじめるビジネスデータ分析 できるDigital Camp

「Visual SEM Report」
最高のデータビジュアライズ体験を
統計検定 データサイエンス基礎のテキストと勉強方法 前の記事 インハウスプラス x LookerStudioで広告レポートを自動化 【ダッシュボード構築】 次の記事
渋谷TAIZI

渋谷TAIZIGA4SQLスペシャリスト

関連記事

渋谷TAIZI

渋谷TAIZIGA4SQLスペシャリスト