TableauでF2転換率を計算する(テーブルに購買した回数の番号を付け、自己リレーションシップで集計)
概要
TableauでF2転換率を集計する方法を書いた記事です。
今回は、顧客ごとに何回目に買ったか分かる番号を付与し、自己リレーションシップで集計する方法を書きます。
何かの参考になればうれしいです！
F2転換率とは？
F2転換率とは、初回購入者が2回目の購入に至る割合を指します。
「F」はFrecency(頻度)の頭文字で、何回目の購入なのかを表しています。
この指標は、顧客がリピーターになるかどうかを示す重要な指標であり、ビジネスの持続的な成長に大きな影響を与えます。F2転換率を追跡することで、顧客のロイヤルティやマーケティング施策の効果を測定し、改善策を講じることが可能です。
F2転換率の定義
F2転換率は以下で計算します。
[2回目に購入した人数] / [1回目に購入した人数]2回目に購入した人数は「1回目に購入した日から3か月以内に2回目に買った人数」というように制限をかける場合があります。
例えば、初めて購入した人に対し2か月以内で使えるクーポンを発行したとします。この場合は60日以内に購入があるかどうかで判定することで、クーポンの効果を調べることができます。
業種・業態に応じて2回目に購入する頻度が異なるため、実際の購入頻度に合わせた設計が重要です。
実践
データはTableauでよく使う[サンプル スーパーストア]を使います。
4ステップで説明していきます。
顧客ごとに何回目の購入かが分かる番号の列を追加
テーブルを顧客番号と購入回数で自己リレーションシップ
(セカンダリのテーブルは 購入回数 - 1 でリレーションシップ)F2転換率で使用する計算フィールドを作成
可視化
顧客ごとに何回目の購入かが分かる番号の列を追加
F2転換を判定するには、そのデータがF1なのか、F2なのか分かるようにしないといけません。
今回はSQLのViewを使って何回目の購入なのかを判定します。
環境はSQL Serverを使います。
元のデータは以下のようになっています。
ここに、顧客IDごと、オーダー日ごとに番号を振っていきます。
DENCE_RANK() を使います。
SELECT
[顧客 ID],
オーダー日,
製品名,
売上,
DENSE_RANK() OVER(PARTITION BY [顧客 ID] ORDER BY [オーダー日] ASC) as Frecency
FROM
dbo.[order]これでviewを作ると
番号が付与されました！この番号が作れればほぼ完成です。
注意点は、返品データが混ざる場合です。事前にクレンジングしないと、本当は1回しか買っていないのに3回購入したことになります。(購入→返品→購入で3回)
あらかじめデータの特性を把握したうえで前処理しましょう。
テーブルを顧客番号と購入回数で自己リレーションシップ
(セカンダリのテーブルは 購入回数 - 1 でリレーションシップ)
それではTableauを使っていきます。
データの接続で、先ほど作成したviewを自己リレーションシップします。
セカンダリ(右側にあるテーブル)は、関係の計算で [Frecency] - 1 で繋ぎます。以下のようなテーブルができます。
縦持ちのデータを横持ちにするイメージです。
この形であれば1回に買った人の数、2回目に買った人の数が計算しやすいです。
F2転換率で使用する計算フィールドを作成
計算フィールドを作ります。
1回目に買った人(F1会員数)
//F1会員数
countd([顧客 Id])2回目に買った人は、3か月以内に購入したかどうかを判定したいので、日付のパラメータ[F2の日数]を作り、datediffで判定します。
//F2会員数
countd(
if
datediff('day',[オーダー日],[オーダー日 (F2分析1)]) <= [F2の日数]
then
[顧客 Id (F2分析1)]
end
)F2の日数は90日にしています。
F2転換率も作ります。
//F2転換率
[F2会員数]/[F1会員数]必要なフィールドが揃ったので、可視化します。
可視化
リレーションシップで繋いでいるので、全体のF2転換率とカテゴリごとのF2転換率を同時に出すことができます。
まず表を作ってみます。プライマリのテーブルのFrecencyを1にフィルターすることで、F2転換率が出せます。(抽出フィルターを使ってもOK)
カテゴリごとのF1会員数(新規の人数)と、F2会員数(1回目から90日間で2回目に購入した人数)、F2転換率が出ることを確認します。
差が出ているので、散布図を作ってみます。
行にF1会員数、列にF2転換率、テキストにサブカテゴリ、フィルターにFrecency = 1 を入れます。
せっかくなのでクラスターを作ります。
アナリティクスペインから「クラスター」をドラッグ&ドロップします。
クラスターの数は自動にします。
4つのグループができました。
クラスター1 : 平均的
クラスター2 : F1が多い(新規が取りやすいカテゴリ)
クラスター3 : F1が少なくF2転換率が高い(買う人は少ないが、リピーターになりやすいカテゴリ)
クラスラー4 : 新規が少なく、リピーターも少ないカテゴリ
ここから色々な仮説が作れます。
クラスター2は新規を多く取っている商品なので、さらにF2になるような施策をつくる。
クラスター3はリピートする人が多いが、新規が買いにくいので、新規の方でも買いやすくなるような施策を考える。
クラスター4は新規・リピートともに低いので、F2転換率以外の数字も調べる。(例:単価や利益に貢献しているか？など)
他にも、時系列のグラフを作ると、キャンペーンごとに新規がどれだけ獲得できたか、それがリピータにどのくらい繋がったのか、が分かります。
実際は1の買い物で1つのカテゴリだけでなく、複数のカテゴリを購入しています。今回は分かりやすいように1つのカテゴリで計算していますが、複数カテゴリを購入した場合にF2転換しやすいか？という集計もデータの下処理次第で可能です。
ワークブックのDL
ワークブックはTableauPublicにパブリッシュしています。ご覧ください。
https://public.tableau.com/views/F2_17284521203150/sheet1?:language=ja-JP&publish=yes&:sid=&:redirect=auth&:display_count=n&:origin=viz_share_link
おわりに
このように購買順序を考慮した分析は「シーケンシャルパターンマイニング」と呼ばれます。(同時購買の分析は「アソシエーション分析」「バスケット分析」などと呼ばれます)
Tableauをやっている人から何度か「併売分析の記事読みました！」と言われました。併売分析をやっている人は、おそらくF2転換分析もやるだろうと思いこの記事を書きました。
要望があればもう少し記事に肉付けしようと思います。
いいね！と思った方はXをフォローしてくれると喜びます！
https://x.com/Seiji_Suna