Digital Marketing Academy · レッスン

マーケティングテーブルを結合する

Q: 「マーケティングテーブルを結合する」で何を学びますか？

セッション、ユーザー、注文を扱います。 ブラウザで直接実行するハンズオンコードでDigital Marketing Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

セッション、ユーザー、注文を扱います。

「マーケティングテーブルを結合する」はCoddyKit上の無料Digital Marketing Academyレッスンです。これはレッスン3/4です。下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。これはDigital Marketing Academy学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 Digital Marketing Academyコースには全4レッスンが含まれています。

なぜJOINするのか

現実の問いは複数のテーブルにまたがります。費用はキャンペーンに、売上高は注文に、属性はユーザーに格納されています。セグメント別のROASやLTVを計算するには、これらを組み合わせる必要があります。

JOINは、user_idやcampaign_idのような共通キーを使って、2つのテーブルの行を対応付けます。

SELECT o.order_id, u.country
FROM orders o
JOIN users u ON o.user_id = u.user_id;

INNER JOIN

INNER JOINは、両方のテーブルで一致する行だけを返します。一致するユーザーがいない注文や、注文のないユーザーは除外されます。

購入者のように、両側に存在するレコードだけが必要な場合に使います。

SELECT u.user_id, u.country, SUM(o.revenue) AS revenue
FROM users u
JOIN orders o ON o.user_id = u.user_id
GROUP BY u.user_id, u.country;

LEFT JOIN

LEFT JOINは、右側に一致する行がない場合でも、左側のテーブルのすべての行を保持します。一致しない値はNULLとして返されます。

注文したことのないユーザーや、コンバージョンに至らなかったセッションを見つけるときに使います。

SELECT u.user_id, COALESCE(SUM(o.revenue), 0) AS revenue
FROM users u
LEFT JOIN orders o ON o.user_id = u.user_id
GROUP BY u.user_id;

抜け漏れを見つける

LEFT JOINとNULLチェックを組み合わせると、一致しなかった行だけを取り出せます。「登録したユーザーのうち、購入していないのは誰か？」は、リテンション分析でよくある問いです。

右側がNULLであることは、一致する注文が存在しなかったことを意味します。

SELECT u.user_id, u.signup_date
FROM users u
LEFT JOIN orders o ON o.user_id = u.user_id
WHERE o.order_id IS NULL;

セッションと注文の結合

セッションと注文を結び付けると、行動と成果を関連付けられます。user_idで一致させると、どの流入が最終的に購入につながったかを確認できます。

これはチャネルのアトリビューション分析の基盤です。

SELECT s.channel, SUM(o.revenue) AS revenue
FROM sessions s
JOIN orders o ON o.user_id = s.user_id
GROUP BY s.channel;

ROASの計算

正確なROASを計算するには、費用と売上高を横に並べる必要があります。campaign_idでキャンペーンと注文を結合し、その後に合計値を割ります。

NULLIFは、費用がゼロの場合のゼロ除算を防ぎます。

SELECT c.campaign_id,
       SUM(o.revenue) / NULLIF(SUM(c.spend), 0) AS roas
FROM campaigns c
LEFT JOIN orders o ON o.campaign_id = c.campaign_id
GROUP BY c.campaign_id;

テーブルエイリアス

エイリアス（ordersにはo、usersにはu）を使うと、複数テーブルのクエリを短く、かつ曖昧さなく書けます。両方のテーブルに同じ名前の列がある場合は、必ずテーブル名などで列を修飾します。

明確なエイリアスを使うと、複雑なJOINもはるかに読みやすく、デバッグしやすくなります。

SELECT c.name AS campaign, SUM(o.revenue) AS revenue
FROM campaigns c
JOIN orders o ON o.campaign_id = c.campaign_id
GROUP BY c.name;

3つのテーブルを結合する

JOINを連ねて、2つを超えるテーブルをまとめます。ここではcampaigns、orders、usersを結び、国別に売上高をセグメント化します。

各JOINでは、新しいテーブルを既存の集合に結び付ける別のON条件を追加します。

SELECT c.name, u.country, SUM(o.revenue) AS revenue
FROM campaigns c
JOIN orders o ON o.campaign_id = c.campaign_id
JOIN users u ON u.user_id = o.user_id
GROUP BY c.name, u.country;

粒度に注意する

1対多のリレーションシップを結合すると、行が増殖して合計が膨らむことがあります。1つのキャンペーンに多数の注文がある場合、注文ごとに費用を合計すると二重計上になります。

各テーブル側を別々に集計してから合計値を結合すると、数値を正しく保てます。

SELECT c.campaign_id, c.total_spend, r.revenue
FROM campaigns c
JOIN (
  SELECT campaign_id, SUM(revenue) AS revenue
  FROM orders GROUP BY campaign_id
) r ON r.campaign_id = c.campaign_id;

ファーストタッチアトリビューション

ユーザーが最初に訪れたチャネルに貢献度を割り当てるには、各ユーザーの最初のセッションを見つけてから、そのセッションを注文に結合します。

サブクエリでファーストタッチを切り出してから、売上高と結合します。

SELECT f.channel, SUM(o.revenue) AS revenue
FROM (
  SELECT DISTINCT ON (user_id) user_id, channel
  FROM sessions ORDER BY user_id, session_date
) f
JOIN orders o ON o.user_id = f.user_id
GROUP BY f.channel;

実践でのJOIN

JOINは、マーケティングSQLの力が発揮される部分です。費用と売上高を組み合わせればROAS、セッションと注文を組み合わせればアトリビューション、ユーザーと注文を組み合わせればセグメント別LTVを求められます。

両側の存在が必須ならINNER、左側を保持して一致しない部分を調べたいならLEFTを選びます。

クイックチェック

登録したすべてのユーザーを、注文していないユーザーも含めて一覧にしたい場合、どのJOINを使いますか？

まとめ

INNER JOINは両側で一致する行を保持し、LEFT JOINは左側のすべての行を保持して、一致しない部分を明らかにします。エイリアスと修飾した列名を使うと、クエリを整理できます。

1対多のJOINで起こるファンアウトの罠に注意し、合計を正確に保つため事前に集計します。次は、コホートとファネルのクエリです。

よくある質問

「マーケティングテーブルを結合する」レッスンは無料ですか？

はい。「マーケティングテーブルを結合する」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、Digital Marketing Academyコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 Digital Marketing Academyコースには全4レッスンが含まれています。

「マーケティングテーブルを結合する」で何を学びますか？

セッション、ユーザー、注文を扱います。ブラウザで直接実行するハンズオンコードでDigital Marketing Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

Digital Marketing Academyを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのDigital Marketing Academyは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。これはレッスン3/4です。

「マーケティングテーブルを結合する」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

このDigital Marketing Academyレッスンでコードを書いて実行できますか？

はい。すべてのDigital Marketing Academyレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

このコースのすべてのレッスン

← Digital Marketing Academyに戻る