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

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

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

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

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

Contents

1 広告施策のアシスト効果をベン図で可視化
2 Looker Studioの「Venn Diagram」によりベン図をビジュアライズ
3 SQLで実現するベン図（セグメントの重複）BigQuery x Looker Studio

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

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

思うに広告のアシスト効果の可視化にはベン図が最も適しているのではないか？という考えが以前からあった。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
)