Cohort Analysis on Databricks Using Fivetran, dbt and Tableau - The Databricks Blogの翻訳です。

本書は抄訳であり内容の正確性を保証するものではありません。正確な内容に関しては原文を参照ください。

モダンデータスタックにおける応用マーケティング分析

概要

コホート分析は、顧客(「コホート」として知られれています)の挙動、成果、貢献の時間変化を研究するプロセスを意味します。セールスの収益や全体的な企業成長のような全体的なトップレベルのメトリクスに顧客グループがどのようにインパクトを与えるのかにより多くの光を照らす助けになるので、マーケティングの領域においては重要なユースケースとなります。

コホートは、共通の特性を共有する顧客のグループとして定義されます。これは、小売点で初めて購買したタイミング、ウェブサイトにサインアップした日付、誕生日、特定の個人をグルーピングするために用いられる他の属性を用いて決定されます。ここで検討するのは、時間と共に特定の挙動をドライブするコホートに関してです。

単一のプラットフォームでデータウェアハウスやAIユースケースを統合するDatabricksレイクハウスは、コホート分析ソリューションを構築するには理想的な場所です。信頼できる唯一の情報源を維持し、データエンジニアリングとモデリングワークロードをサポートし、膨大な分析、AI/MLユースケースを解放します。

このハンズオンのブログ記事では、3つのステップでDatabricksにおけるコホート分析ユースケースを実装し、データ取り込み、ETL、データビジュアライゼーションに渡る全てのデータツールを接続するためにモダンデータスタックにDatabricksレイクハウスプラットフォームにどれだけ容易にインテグレーションできるのかをご紹介します。

ユースケース：顧客の再購入の分析

マーケティング分析の領域における格言は、新規顧客を獲得するには大変な労力を要するので、企業は一度顧客を獲得したら、繰り返し購入してもらいたいと考えるということです。この記事では、この質問に答えることに集中しています。

我々のソリューションを開発するステップを以下に示します。

Fivetranを用いたデータ取り込み
dbtを用いたデータ変換
Tableauを用いたデータビジュアライゼーション

ステップ1：Fivetranを用いたデータ取り込み

Azure MySQLとFivetran間のコネクションのセットアップ

1.1：コネクター設定

この準備ステップでは、Azure MySQLデータベースからDelta LakeにEコマースセールスデータを取り込み始めるために、Fivetranにおいて新たなAzure MySQLコネクションを作成します。上のスクリーンショットに示しているように、接続パラメーターを入力するだけで良いので、設定は非常にシンプルです。データ取り込みにFivetranを使いことのメリットは、お使いのデータベースソースからDelta Lakeディスティネーションに対して、自動で正確にスキーマとテーブルを複製し、管理するということです。Deltaでテーブルが作成されたら、後ほどデータを変換し、データをモデリングするためにdbtを活用します。

1.2：ソースからディスティネーションへの同期

この設定が行われたら、それぞれのオブジェクトが個々のテーブルとして保存されるDelta Lakeに対してどのデータオブジェクトを同期するのかを選択します。Fivetranには、どのテーブルとカラムを同期するのかをクリックできる直感的なユーザーインタフェースがあります。

Delta Lakeに同期するデータオブジェクトを選択するFivetranスキーマUI

1.3：Databricks SQLにおけるデータオブジェクト生成の検証

初回の履歴動機を起動した後は、Databricks SQLワークスペースに移動し、eコマースセールスデータがDelta Lakeに存在していることを検証します。

同期されたテーブルを表示するデータエクスプローラ

ステップ2：dbtを用いたデータ変換

ecom_ordersテーブルがDelta Lakeにあるので、分析のためにデータを変換、整形するためにdbtを使います。このチュートリアルでは、dbtモデルスクリプトを作成するためにVisual Studio Codeを使用していますが、お好きなテキストエディタを使うことができます。

2.1：プロジェクトインスタンスの作成

新規dbtプロジェクトを作成し、プロンプトが表示された際にはDatabricks SQLウェアハウス設定パラメータを入力します。

Databricksを選択するために数字1を入力します。
お使いのDatabricks SQLウェアハウスのサーバーホスト名
HTTPパス
パーソナルアクセストークン
デフォルトスキーマ名(ここにテーブルやビューが格納されます)
スレッド数のプロンプトが表示されたら4を入力します。

dbtプロジェクトを初期化する際の接続パラメーター

プロファイルを設定したら、以下を使用して接続をテストすることができます。

dbt debug

dbtが無事Databricksに接続できたことを示す結果

2.2：データ変換とモデリング

これで我々はこのチュートリアルで最も重要なステップの一つにたどり着いたことになります。ここでは、コホートの購入の時系列変化をビジュアライズするためにトランザクションオーダーテーブルを変換し、整形します。プロジェクトのモデルフィルターにおいては、以下のSQL文を用いてvw_cohort_analysis.sqlというファイルを作成します。

IDE内でのdbtモデルスクリプトの開発

以下のコードブロックでは、特定顧客にとっての最初と二回目の購入を特定するためにCommon Table Expressions (CTEs)を用いたステップバイステップの変換処理を組み上げるために、モジュール性のデータエンジニアリングベストプラクティスを活用しています。また、Databricksレイクハウスがサポートしているサブクエリーのような高度なSQLテクニックも活用されています。

SQL

{{
 config(
   materialized = 'view',
   file_format = 'delta'
 )
}}

with t1 as (
       select
           customer_id,
           min(order_date) AS first_purchase_date
       from azure_mysql_mchan_cohort_analysis_db.ecom_orders
       group by 1
),
       t3 as (
       select
           distinct t2.customer_id,
           t2.order_date,
       t1.first_purchase_date
       from azure_mysql_mchan_cohort_analysis_db.ecom_orders t2
       inner join t1 using (customer_id)
),
     t4 as (
       select
           customer_id,
           order_date,
           first_purchase_date,
           case when order_date > first_purchase_date then order_date
                else null end as repeat_purchase
       from t3
),
      t5 as (
      select
        customer_id,
        order_date,
        first_purchase_date,
        (select min(repeat_purchase)
         from t4
         where t4.customer_id = t4_a.customer_id
         ) as second_purchase_date
      from t4 t4_a
)
select *
from t5;

これでモデルの準備ができたので、以下のコマンドを用いてDatabricksにデプロイすることができます。

dbt run

上で実行したスクリプトの結果を検証するためにDatabricks SQLエディターに移動します。

dbtテーブル変換による結果セット

ステップ3：Tableauを用いたデータのビジュアライゼーション

最後のステップとして、データをビジュアライズし、命を吹き込むことになりました！Databricksはネイティブコネクターを通じて容易にTableauや他のBIツールとインテグレーションすることができます。コホート分析チャートを構築し始めるために対応するSQLウェアハウスコネクションパラメーターを入力します。

TableauデスクトップにおけるDatabricksコネクターウィンドウ

3.1：ヒートマップビジュアライゼーションの作成

ビジュアライゼーションを構築するために以下のステップを実施します。

行に[first_purchase_date]をドラッグし、四半期の粒度に設定します。
[quarters_to_repeat_purchase]を列にドラッグします。
[customer_id]のcount distinctをカラーシェルフに割り当てます。
カラーパレットをシーケンシャルに設定します。

複数の四半期におけるコホートの購入を表示するヒートマップ

3.2：結果の解析

ここで構築したビジュアライゼーションから得られたキーとなる洞察とテイクアウェイがあります。

2016 Q2において最初に購入を行なった顧客の間で、168人の顧客は2回目に購入するためには2クォーターを要しています。
NULL値は去ってしまった顧客を示しているのでしょう。初回の購入の後に2回目の購入をしませんでした。これは、これらの顧客をさらにドリルダウンし、彼らの購買行動を理解する機会と言えます。
積極的なマーケティングプログラムを通じて、初回の購買と二回目の購買のギャップを短縮する機会が存在しています。

まとめ

おめでとうございます！上述のステップを終えることで、シームレスにインテグレーションされたパワフルかつ実践的なマーケティング分析ソリューションを構築するために、Fivetran、dbt、TableauとDatabricksレイクハウスを活用しました。このハンズオンチュートリアルが興味を引いて有用であると感じていただけたら幸いです。ご質問があれば是非メッセージをいただければと思います。そして、将来的に公開されるDatabricksのチュートリアルを楽しみにしていてください。

参考情報

DatabricksとFivetran: https://docs.databricks.com/integrations/ingestion/fivetran.html
Databricksとdbt: https://docs.databricks.com/integrations/prep/dbt.html
DatabricksとTableau: https://docs.databricks.com/integrations/bi/tableau.html

Fivetran、dbt、Tableauを用いたDatabricksにおけるコホート分析