Looker Studioの痒いところに手を届かせる

Looker Studioを活用したとあるプロジェクトにおいて、複数の結合キーの組み合わせをOR条件で指定してテーブル結合して、データを可視化したい場面に遭遇しました。

現状、Looker Studioの統合機能（複数のデータソースをLooker StudioのUIでテーブル結合する機能）では、複数の結合キーの組み合わせをOR条件で指定したり、等号以外の結合条件を指定する方法はありません。

さらに今回のケースでは、片方のデータソースがLooker Studioのコネクタを介したSaaSへの直接接続であったため、データベース側での事前結合が不可能という制約もありました。

そのため、どうにかしてLooker StudioのUIを使って実現するためのワークアラウンドを検討することになりました。その内容をご紹介します。

今回やりたいこと

まずは、検証用にスプレッドシートでサンプルデータを準備しました。

顧客テーブル（customers）

顧客ID（customer_id）
名前（name）
メールアドレス１（primary_email）
メールアドレス２（secondary_email）

注文テーブル（orders）

オーダーID（order_id）
オーダー日（order_date）
注文者のメールアドレス（email）
購入金額（sales）

Looker Studioでこれら2つのテーブルを結合して、各顧客の総購入金額を抽出したいという場面をモデルケースとして考えます。 Untitled

この際に問題になるのが、例えば顧客テーブルで customer_id が 1 の Alice は、2024/01/01 には primary_email に登録された alice@example.com で商品を購入しているのに、 2024/01/04 には secondary_email に登録している別のメールアドレス alice@example.net で購入した記録が残っています。

この場合、顧客テーブルの primary_email または secondary_email が注文テーブルの email と一致するかを調べた上で、購入金額を足し上げなければ、各顧客の正確な総購入金額を抽出することができません。

今回やりたいことをSQLで書くとこうなります。

（SQLを扱える方には文章で説明するよりこちらの方がわかりやすいかもしれません。）

SELECT
	c.customer_id,
	c.name,
	COALESCE(SUM(o.sales), 0) AS total_sales
FROM
  customers c
  　　LEFT OUTER JOIN
   　　orders o
    　　ON c.primary_email = o.email OR c.secondary_email = o.email
GROUP BY
	c.customer_id, c.name
ORDER BY
	c.customer_id

このクエリの出力と同様の結果をLooker Studioの統合機能を使って抽出するのが目標となります。

Looker Studioの統合では結合キーを複数指定するとAND条件が適用される

Looker Studioの統合機能の説明はこちらのヘルプページにまとめられています。

結合条件を複数設定した際にどのような扱いになるかについての直接の言及はありませんでしたが、冒頭にも書いたとおり、Looker Studioの統合機能で結合条件を複数指定した場合はANDで評価されることが事前の検証で確認できています。

実際に、今回は顧客テーブルの primary_email または secondary_email を注文テーブルの email と結合したいので、下の画像のように結合条件を設定してみましたが、やはり購入金額はひも付きませんでした。 Untitled

ワークアラウンドの検討と実装

Looker Studioではテーブル結合にOR条件は使えませんが、OR条件で結合したのと同じ結果を抽出するためのワークアラウンドを考えます。

SQLで結合条件にORを使用せずに同様の結果を得ようとした場合、複数のJOINを使用する方法が思いつきました。今回はそのアプローチをLooker Studioの統合機能で実現できないかと考えました。

統合テーブルを準備する

まず、顧客テーブルに注文テーブルを左外部結合で2回繰り返し結合し、結合条件にはそれぞれprimary_email と email 、 secondary_email と email を指定して統合テーブルを作成します。 Untitled

顧客テーブルは全てのディメンションを選択、各注文テーブルのディメンションは結合に必要な mail を追加し、指標にはそれぞれ sales を選択し集計方法は合計（SUM）を指定、 primary_email で結合した注文テーブル1には primary_email_sales 、 secondary_email で結合した注文テーブル2には secondary_email_sales と名前を付けておきます。

繰り返し結合フィールドを非表示にするチェックボックスを選択して保存します。

2つのメールアドレスで紐づいたそれぞれの総購入金額を足し上げる

次に、作成した統合テーブルを使用してグラフを作成して統合テーブルのデータを確認します。 Untitled

すると、 primary_email と secondary_email それぞれに紐づいた足し上げられた購入金額が別々の列に抽出されているのが確認できました。

あとはこれらの列をさらに足し上げた新たな行を作成できれば良さそうです。

先程のグラフを選択し、ディメンションを絞り、指標を追加をクリック、 Untitled

フィールドを追加をクリック、 Untitled

以下のように入力し適用ボタンをクリックします。

名前：
total_sales

計算式：
primary_email_sales + secondary_email_sales

Untitled

結果を見てみると・・・ Untitled

Aliceは意図したとおり総購入金額が足し上げられていますが、残りの3人はデータなし（NULL）の表示になってしまっています。

これは、「四則演算にNULLが含まれる場合、結果は必ずNULLになる」というSQLの仕様によるものです。

そこで計算式に修正を加えます。

計算式：
COALESCE(primary_email_sales, 0) + COALESCE(secondary_email_sales, 0)

Untitled

COALESCE関数でprimary_email_sales がNULLでない場合はその値を、そうでない場合は0を使用して、 secondary_email_sales も同様に扱うように変更しました。 Untitled

無事、全員の正確な総購入金額を抽出することができました。

生成されたクエリ

今回のワークアラウンドで実装した設定の裏側では、以下のようなSQLのクエリが生成されていると推察されます。（あくまで予想です）

WITH orders1 AS(
	SELECT
		email,
		SUM(sales) AS primary_email_sales
	FROM
		orders
	GROUP BY
		email
),
orders2 AS(
	SELECT
		email,
		SUM(sales) AS secondary_email_sales
	FROM
		orders
	GROUP BY
		email
)
SELECT
	c.customer_id,
	c.name,
	COALESCE(o1.primary_email_sales, 0) + COALESCE(o2.secondary_email_sales, 0) AS total_sales
FROM
	customers c
		LEFT OUTER JOIN orders1 o1 ON c.primary_email = o1.email
		LEFT OUTER JOIN orders2 o2 ON c.secondary_email = o2.email
ORDER BY
	c.customer_id

結果として得られるデータは同じですが、最初に紹介したJOINの条件にORを使用したクエリとは異なるアプローチを取っていることがわかると思います。

ノーコードで利用できるSaaSや、クエリを書かなくてもAIがデータを抽出してくれることを謳うBIツールがあふれる今日このごろですが、SQLとデータベースの仕組みや構造への理解があったからこそ今回のような特殊な状況にも臨機応変に対応することができたと感じています。

普段の業務でがっつりクエリを書くことはなくても、マーケターやデータアナリストでもSQLを学ぶべきだと考えている理由が、こういうところにあったりします。

今回ご紹介したケースはかなりニッチな内容でしたが、このワークアラウンド自体は、例えば、ECサイトの運営メンバーがCMSからダウンロードした複数のCSVファイルをLooker Studioで統合して可視化する際などにも活用できる機会がありそうだなと思い、ブログ記事化させていただきました。

いざというときの参考にしてください！