Splunkでコホート分析

はじめに

Splunkで色々行動分析を試しています。
その一つでコホート分析ができたのでメモです。

コーホート（英: cohort）とは、共通した因子を持ち、観察対象となる集団のこと。人口学においては同年（または同期間）に出生した集団を意味する[1]。疫学においてはコホート研究において用いられる母集団を指し、コホートと呼ばれることが多い。

例えばビジネス観点で活用すると、とあるオンライン販売サービスを提供しており、ある日サービス改善を行ったことで、それ以降の新規ユーザがそれ以前のユーザよりもコンバージョンレートが高いかとか継続利用してくれるかなどの改善効果の追跡調査を行ったりできます。
ユーザ登録時期での比較以外にも、何かしらのキャンペーンで登録してくれたユーザとそれ以外など色々な属性が考えられます。

それじゃやっていきましょう。

環境

• Splunk Enterprise or Cloud

特にAppなど不要でSPLだけで完結します。

データ

UCI Machine Learning RepositoryのOnline Retail II Data Setをお借りしました。

2009/1/12から2011/9/12までのオンライン小売サービスのトランザクションログです。
いつ（InvoiceDate）、どのユーザIDが（Customer ID）、単価がいくらの（Price）、何の商品を（StockCode）、いくつ（Quantity）を買ったかの記録です。

ログ例

Country,"Customer ID",Description,Invoice,InvoiceDate,Price,Quantity,StockCode
"United Kingdom","17530.0","3 TIER SWEETHEART GARDEN SHELF",538171,"2010-12-09 20:01:00","35.95",1,22824
"United Kingdom","17530.0","FIRST AID TIN",538171,"2010-12-09 20:01:00","3.25",3,21260
"United Kingdom","17530.0","PLASTERS IN TIN VINTAGE PAISLEY",538171,"2010-12-09 20:01:00","1.65",1,22557

こちらを使って、登録した時期ごとのユーザ別に売上やリテンション率（継続的に利用してくれるか）を分析してみたいと思います。

分析してみる

データアップロード

元データはxlsxで、Splunkでは読み込めないのでCSVに変換します。
ルックアップファイルとしてアップロードします。

1.設定 > ルックアップ
2.ルックアップテーブルファイル」の [+ 新規追加] をクリック
3.ファイルを指定し、宛先ファイル名に同じファイル名を記入して保存

4.設定 > ルックアップに戻り、「ルックアップ定義」の [+ 新規追加] をクリック
5.Nameにファイル名を記入、ルックアップファイルでアップロードしたファイルを選択して保存

6.Searchで念のため確認しておきましょう

SPL

| inputlookup online_retail_II.csv

ユーザの新規登録時期の取得

データにはユーザ登録時期の情報はありませんでしたので「最初に購入した日」を登録時期とみなします。
もしユーザデータベースで登録時期が管理されていればそちらを使えばOKです。

こんなSPLで年月ごとにユーザを分類したいと思います。

SPL

| inputlookup online_retail_II.csv
| where isnotnull("Customer ID") ``` 空のデータがあるのでデータがある行だけ処理 ```
| stats min(InvoiceDate) as firstInvoiceDate by "Customer ID" ``` 各ユーザに対して最初の購入日を取得 ```
| rex field=firstInvoiceDate "(?<joinDate>[0-9]{4}\-[0-9]{2}\-[0-9]{2})" ``` InvoiceDateには秒まで含まれているので年月日を抽出 ```
| rex field=joinDate "(?<joinMonth>[0-9]{4}\-[0-9]{2})" ``` 分類のため年月を抽出 ```
| eval cohortGroup = "C_".joinMonth ``` 区別しやすくするため頭にC_をつける ```

結果は以下のようになります。例えば2009-12-14に初回購入したユーザ「12346.0」のcohortGroupは「C_2009-12」です。

SPL実行結果

"Customer ID",firstInvoiceDate,cohortGroup,joinDate,joinMonth
"12346.0","2009-12-14 08:34:00","C_2009-12","2009-12-14","2009-12"
"12347.0","2010-10-31 14:20:00","C_2010-10","2010-10-31","2010-10"
"12348.0","2010-09-27 14:59:00","C_2010-09","2010-09-27","2010-09"

良さそうなのでこれをルックアップファイルに出力します。
上記のSPLの最後でoutlookupすればルックアップファイルにしてくれます。

ルックアップファイル出力SPL

SPL（略）
| outputlookup online_retail_II_joinMonth.csv

念のため確認しておきましょう。

ルックアップファイル確認SPL

| inputlookup online_retail_II_joinMonth.csv

コホート分析

コホートグループのためのルックアップファイルが手に入りましたので、これを元データでルックアップすれば各ユーザがいつ登録したかという属性でグループ分けすることができます。
あとは基本的なSPLで色々と分析できます。

グループごとの売り上げ

例えば、以下SPLでグループごとの各月の平均売上を算出できます。

SPL

| inputlookup online_retail_II.csv
| where isnotnull("Customer ID")
| lookup online_retail_II_joinMonth.csv "Customer ID" OUTPUTNEW cohortGroup ``` コホートグループのルックアップ。ユーザのグループ（cohortGroup）を出力する。 ```
| eval _time = strptime(InvoiceDate, "%Y-%m-%d %H:%M:%S") ``` 時間処理をしやすくするため、InvoiceDateをUNIX Timeに変化して_timeにセット ```
| eval year_month = strftime(_time, "%Y-%m") ``` 各月の分析をするためyyyy-mmを抽出 ```
| chart limit=0 usenull=f avg(totalSell) over year_month by cohortGroup ``` コホートグループごとの各月の平均売上を算出 ```

結果は以下のようになります。

縦軸が購入年月、横軸がグループで、平均売上を出力しています。
例えばC_2009_12の列は、2009年12月に登録したユーザの2009年12月の平均売上は約20、2010年1月は約29などです。

視覚的に分かりやすくします。
視覚エフェクトで「Area Chart」を選び、フォーマットで「100%スタック」を選択します。

きれいですねー
最初期ユーザは1年後でも平均売上が他ユーザグループより高いままということが分かりますね。

これで例えば、ある時期にサービスのテコ入れをしたとき、それ以降のユーザの売り上げがそれ以前のユーザより良くなっているかどうか（最初からサービスに惹きつけられているか）、みたいな分析が一目で分かるようになります。

他にもデータがあれば簡単に色々な分析ができます。

SPL（平均販売数）

| chart limit=0 usenull=f count over year_month by cohortGroup

SPL（合計売上）

| eval totalSell = Price * Quantity
| chart limit=0 usenull=f sum(totalSell) over year_month by cohortGroup

グループごとのリテンション率

他に重要な「ユーザーはファンになってくれてサービスを使い続けてくれているか」を分析してみましょう。

まず手始めに、ほぼ同じSPLですが最後の行でdc（distinct countの略）で、グループごとのユニークユーザー数を分析します。

SPL（ユニークユーザー数）

| inputlookup online_retail_II.csv
| where isnotnull("Customer ID")
| lookup online_retail_II_joinMonth.csv "Customer ID" OUTPUTNEW cohortGroup
| eval _time = strptime(InvoiceDate, "%Y-%m-%d %H:%M:%S")
| eval year_month = strftime(_time, "%Y-%m")
| chart limit=0 usenull=f dc("Customer ID") over year_month by cohortGroup ``` グループごとのユニークユーザー数 ```

同じくArea Chartで、フォーマットは「スタック」にします。これにより、グループごとにどれだけのユーザが継続的に利用してくれているか分かります。

率でも見たいと思います。ちょっと難しいSPLを書きますね。

SPL（リテンション率）

| inputlookup online_retail_II.csv
| where isnotnull("Customer ID")
| lookup online_retail_II_joinMonth.csv "Customer ID" OUTPUTNEW cohortGroup
| eval _time = strptime(InvoiceDate, "%Y-%m-%d %H:%M:%S")
| eval year_month = strftime(_time, "%Y-%m")
| stats dc("Customer ID") as dc_customer by cohortGroup year_month ``` グループx購入年月ごとのユニークユーザー数 ```
| streamstats max(dc_customer) as customer_max_num by cohortGroup ``` グループごとの最大ユーザ数を計算 ```
| eval retentionRate = round(dc_customer / customer_max_num, 2)  ``` グループごとに各月のユニークユーザー数 / 最大ユーザ数でリテンション率を計算 ```
| xyseries year_month cohortGroup retentionRate ``` 縦軸を購入年月、横軸をグループ、値をリテンション率のテーブルに変換 ```

これまでと同じくリテンション率の計算ができました。

グラフ化します。Line Chartがいい感じ。
各グループの初月は当然100%（1）で、翌月からがくんと落ちていることが分かります。
その中でも最初期ユーザーはやはり強い。
これでサービスのテコ入れしたとき、離脱率を下げたり復帰率を向上できているか分析できます。

最後にダッシュボードでまとめてあげましょう。

まとめ

Splunkでコホート分析できました。
今回は登録時期で分析しましたが、ユーザ属性（登録契機や年齢層など）なら何でもOKなので、それでどんなユーザが特にヘビーユーザーになってくれるかを調査できたりしますね。