BigQueryによるLTV算出とABC分析

Googleアナリティクスでライフタイムバリューのメニューができて久しいですが、BigQueryで顧客ごとのLifeTimeValueの算出とABC分析を行いたいと思います。

LTVの算出

ユーザー単位での一定期間の売り上げ総額を出すのは比較的簡単です。

　select
　fullvisitorid,
　sum(cast(product.productrevenue as numeric))/1000000 as sales
　FROM `bigquery-public-data.google_analytics_sample.ga_sessions_201707*`
　,unnest(hits) as hits
　,unnest(product) as product
　group by 1
　having sum(product.productrevenue)/1000000 <> 0.0
　order by 2 desc
　;

 実行すると売上の多い順にfullvisitoridが並びます。収益（productrevenue）はGAデータの値に10の6乗を掛けた数値がBigQueryに格納されますので、1000000で割っています。上のクエリでは一般公開のGoogleアナリティクスのサンプルデータを使っており、売上はドルです。よって少数を扱えるようにデータをNUMERICに変換しています。

having句は売上のないユーザーを除外するために書いています。ABC分析の際に必要です。

（クエリ消費を少なくするために、2017年の7月だけのデータを使っています）

ABC分析

どのユーザーからいくら売り上げているか分かったので、マーケティング予算の配分等に使えそうですが、上位10%の顧客で全売上の何パーセントを占めているかも確認したいと思います。

　select
　case when row_number < 3788 then '1-10'
　　　when row_number < 7576 then '11-20'
　　　when row_number < 11364 then '21-30'
　　　when row_number < 15152 then '31-40'
　　　when row_number < 18940 then '41-50'
　　　when row_number < 22728 then '51-60'
　　　when row_number < 26516 then '61-70'
　　　when row_number < 30304 then '71-80'
　　　when row_number < 34092 then '81-90'
　　　else '91-100' end as rank,
　sum(sales) as total_sales,
　round(sum(sales) / 28455109.858,3) as sales_ratio
　from
　(
　select
　fullvisitorid,
　sum(cast(product.productrevenue as numeric))/1000000 as sales,
　row_number()
　over(order by sum(product.productrevenue)/1000000 desc ) as row_number
　FROM `bigquery-public-data.google_analytics_sample.ga_sessions_201707*`
　,unnest(hits) as hits
　,unnest(product) as product
　group by 1
　having sum(product.productrevenue)/1000000 <> 0.0
　order by 2 desc)
　group by rank
　order by 1
　;

 LTVの算出用クエリにrow_number関数で売上ランキングを1位から順にふって、row_numberというカラムに格納しています。それを使ってユーザーを10グループに分かるのですが、売上のあった顧客の数の37,886を予め計算しておき、それを10分割します。37,886÷10=3,788.6 なので、上位3788が上位10%のグループ、次の7676までが11-20％のグループというようにcase文を書きます。このクエリでは91-100のグループだけ6ユーザー多くなっています。

そして、sum(sales) as total_salesでランクごとの合計売上を算出し、
round(sum(sales) / 28455109.858,3) as sales_ratioでランク毎の売上構成を計算しています。分母の全ユーザーの売上（28455109.858）も予め計算したものです。

これを実行すると下のように結果をかえしてくれます。

パレードの法則（80:20）のようにはなっていませんが、上位20%の顧客で売上の65%を占めていることがわかりますので、顧客構造が可視化されマーケ予算の再配分等に使えそうです。