Digital Marketing Academy · レッスン

コホートとファネルのクエリ

Q: 「コホートとファネルのクエリ」で何を学びますか？

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

リテンションに関する疑問に答えます。

レッスン 4/413 ステップ

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

コホートとは

コホートとは、通常は登録月のように、同じ開始イベントを共有するユーザーのグループです。各コホートを時間の経過とともに追跡すると、実際のリテンションが明らかになります。

集計した指標だけでは解約の実態が隠れますが、コホートを使えば、新規ユーザーが本当に定着しているかを明らかにできます。

SELECT user_id,
       DATE_TRUNC('month', signup_date) AS cohort_month
FROM users;

コホートの定義

最初のステップは、各ユーザーにコホートを付けることです。DATE_TRUNCで登録日を月単位に切り下げると、ユーザーをきれいにグループ化できます。

これを再利用可能な構成要素として保存し、その後の処理すべてで使います。

SELECT DATE_TRUNC('month', signup_date) AS cohort_month,
       COUNT(*) AS cohort_size
FROM users
GROUP BY 1
ORDER BY 1;

アクティビティ期間

リテンションでは、登録後何か月目に活動しているかを測定します。注文月とコホート月の差を計算して、その期間を求めます。

期間0は登録月、期間1は翌月というように続きます。

SELECT o.user_id,
       (DATE_PART('year', o.order_date) - DATE_PART('year', u.signup_date)) * 12
       + (DATE_PART('month', o.order_date) - DATE_PART('month', u.signup_date)) AS period
FROM orders o
JOIN users u ON u.user_id = o.user_id;

リテンションのグリッドを作る

コホート月と期間を組み合わせ、各セルでアクティブユーザーの重複なし件数を数えます。その結果が、典型的なリテンション・トライアングルです。

各行が1つのコホート、各列が登録からの経過月を表します。

SELECT DATE_TRUNC('month', u.signup_date) AS cohort,
       DATE_PART('month', AGE(o.order_date, u.signup_date)) AS period,
       COUNT(DISTINCT o.user_id) AS active
FROM users u
JOIN orders o ON o.user_id = u.user_id
GROUP BY 1, 2;

リテンション率

規模が異なるコホート間では、実数の比較は困難です。各期間のアクティブユーザー数を、コホートの元の規模で割ります。

ウィンドウ関数を使うと、期間0の規模を比率計算のためにすべての行へ持ってこられます。

SELECT cohort, period,
       active * 1.0 / FIRST_VALUE(active) OVER (
         PARTITION BY cohort ORDER BY period
       ) AS retention
FROM cohort_activity;

ファネルとは

ファネルでは、訪問、登録、カート追加、購入のように、順序付けられたステップをユーザーが進む様子を追跡します。ステップ間の減少を見ると、どこでユーザーを失っているかが分かります。

ファネル分析により、「コンバージョン率が低い」という曖昧な不満を、具体的に離脱が起きているステージへ変えられます。

SELECT step, COUNT(DISTINCT user_id) AS users
FROM events
WHERE step IN ('visit', 'signup', 'cart', 'purchase')
GROUP BY step;

各ステップを数える

条件付き集計を使うと、1回の処理で各ステージのユーザー数を数えられます。FILTERで各ステップに到達したユーザーの重複なし件数を集計します。

1つのクエリ、1行で、ファネル全体をひと目で確認できます。

SELECT
  COUNT(DISTINCT user_id) FILTER (WHERE step = 'visit') AS visits,
  COUNT(DISTINCT user_id) FILTER (WHERE step = 'signup') AS signups,
  COUNT(DISTINCT user_id) FILTER (WHERE step = 'purchase') AS buyers
FROM events;

ステップのコンバージョン率

重要なのは、連続するステップ間の比率です。各ステップを直前のステップで割ると、最大の離脱箇所が明らかになります。

整数除算で率が0にならないよう、decimal型にキャストします。

SELECT signups * 1.0 / NULLIF(visits, 0) AS visit_to_signup,
       buyers * 1.0 / NULLIF(signups, 0) AS signup_to_buy
FROM funnel_counts;

タイムスタンプで順序付けたファネル

厳密なファネルでは、ステップが順番どおりに発生する必要があります。各ステップを次のステップと結合するのは、次のステップのタイムスタンプが後である場合だけにします。

これにより、対応する登録より前に発生した購入を数えることを防げます。

SELECT COUNT(DISTINCT v.user_id) AS visited,
       COUNT(DISTINCT p.user_id) AS purchased
FROM events v
LEFT JOIN events p
  ON p.user_id = v.user_id
  AND p.step = 'purchase'
  AND p.event_time > v.event_time
WHERE v.step = 'visit';

チャネル別ファネル

ファネルを獲得チャネル別に分けると、単にクリックするだけでなく、実際にコンバージョンするユーザーをどの流入元が送っているかが分かります。

条件付きの件数をチャネルでグループ化して、流入元ごとの質を比較します。

SELECT channel,
       COUNT(DISTINCT user_id) FILTER (WHERE step = 'visit') AS visits,
       COUNT(DISTINCT user_id) FILTER (WHERE step = 'purchase') AS buyers
FROM events
GROUP BY channel;

インサイトから行動へ

コホートを使えば、リリースを重ねるごとにリテンションが改善しているかが分かり、ファネルを使えば、最初に修正すべきステップが分かります。

両者を組み合わせると、マーケティングを「何が起きたか」の報告から、「なぜ起きたか、次にどこへ投資するか」の診断へと変えられます。

クイックチェック

訪問、登録、購入でユーザー数を数えるファネルがあります。連続するステップの比率から何が分かりますか？

対話的なレッスンで答える

まとめ

コホートではユーザーを開始月でグループ化し、DATE_TRUNCとAGEを使って期間ごとのリテンションを追跡します。ファネルでは順序付けられた各ステップでユーザーの重複なし件数を数え、連続する比率を比較します。

これで、リテンションとコンバージョンをSQLだけで診断するための高度なツールキットが整いました。

無料で開始

AI チューターと学ぶ Digital Marketing Academy — 無料

ブラウザでリアルコードを書いて実行し、24/7 の AI チューターから瞬時にサポートを受け、ウェブまたはアプリで続きから学習できます。

コース: 63
レッスン: 239

無料で学習を始めるコース全体を表示

よくある質問

「コホートとファネルのクエリ」レッスンは無料ですか？

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

「コホートとファネルのクエリ」で何を学びますか？

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

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

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

「コホートとファネルのクエリ」レッスンにはどのくらい時間がかかりますか？

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

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

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

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

← Digital Marketing Academyに戻る