Tableauで累積ユーザー数を出してみる

こんにちは、黄です。グロービスのアドベントカレンダーの20日目です。
他にも色々ありますので、ぜひ[ここ←] (https://qiita.com/advent-calendar/2017/globis)御覧ください。

背景

ビジネスや経営などといえば、「ユーザー数」はものすごく大事なことです。
我々が注目されていますか、サーバーの負荷は結構かかっていますか、キャンペーンやプロモーションは本当に効果があるのか、いずれにしてもユーザー数が大事な視点です。

ユーザー数だけではなく、新規ユーザー、継続ユーザー、離脱のユーザー、累積ユーザー数なども重要な指標です。
そのうち、累積ユーザー数は割りと出しにくい数字です。
なぜならば、UU数の累積は、線型ではないので、気をつけないといけません。

「累積」について、まず下記のサンプルデータで説明します。

date	user_id	商品	shop	取引金額
2017/8/3	A	CD	NITORI	210
2017/8/9	A	water	IKEA	250
2017/8/9	A	CD	IKEA	125
2017/8/9	B	CD	IKEA	350
2017/8/9	C	watch	7-11	1,000
2017/9/3	A	watch	NITORI	5,000
2017/9/27	C	desk	NITORI	15,500
2017/10/7	D	watch	NITORI	6,000
2017/10/8	B	bicycle	NITORI	20,000
2017/10/9	C	desk	MUJI	2,300
2017/10/10	D	water	7-11	150

【線型の累積：(例) 取引金額】
2017/8/3 取引金額は、210円
2017/8/9 取引金額は、250 + 125 + 350 + 1000 = 1725円
→ → → → → 2017/8/3 ~ 2017/8/9 の累積金額は、210 + 1725 = 1935円。

【非線形の累積：(例) UU数】
2017/8/3 ユーザー数は、１人
2017/8/9 ユーザー数は、３人
→ → → → → 2017/8/3 ~ 2017/8/9 の累積ユーザー数は、1 + 3 = 4人。 →　3人です。
非線形の世界では、「1 + 1 != 2」 ですね。

SQLの場合、Window関数とCOUNT DISTINCTの組み合わせだと出せるのです。
下記の例をご参考ください。

sample.sql

COUNT(DISTINCT user_id) OVER (ORDER BY date) AS cul_uu

ただし、サポートしていないデータベースもあるので(AWS Athena , AWS Redshiftなど)、
それの回避方法は別途で紹介いたします。

BIツールのタブロー(Tableau)では、いろんな関数や、表計算やLODなど整備していますけれども、
こういうような非線形の累積では、ちょっと工夫しないといけません。
タブローの公式サイトにはすこし書いてありますが、
実際にどういう仕組なのか詳しく説明されていません。

この記事はその参考情報を元に、
更にタブローの表計算など説明し、累積UUを出すまで紹介いたします。

[参考：Tableau 公式サイト：Calculating a Running Count Distinct] (http://kb.tableau.com/articles/howto/calculating-a-running-count-distinct)

累積ユーザー数といえば

累積ユーザー数では、累積unique Userとも言えるでしょう。
略語は、累積UU数とも呼ばれたりします。

累積UU数を出すため、まずタブローの２つの関数を紹介します。[参考：タブロー関数レファレンス] (http://onlinehelp.tableau.com/current/pro/desktop/ja-jp/functions_functions_aggregate.html)

COUNT(expression) 　
→　カウント、数

COUNTD(expression)　
→　個別カウント、重複値は無視されます。SQLというと、COUNT(DISTINCT [expression])に近いでしょう。

先程の例を使うと、
COUNT([user_id]) = 11
COUNTD([user_id]) = 4 となります。
11個のuser_idレコードがあり(NULLでもない)、そのうち、「A」「B」「C」「D」4人がいるわけです。

タブローの表計算で、累積UUを出す

それでは、先程の「[Tableau 公式サイト：Calculating a Running Count Distinct] (http://kb.tableau.com/articles/howto/calculating-a-running-count-distinct)」を参考しながら、累積UUを出してみましょう。

手順の通り、下記の計算フィールドを作ります。

1. Moving Active Users ：　WINDOW_MAX(MIN([レコード数]), FIRST(), 0)

2. !CountD Users ：　WINDOW_SUM([Moving Active Users])

3. First ：　FIRST()

それで、下記で各フィールドを詳細や行に入れる。

1. [date] → 行に入れる

2. [user_id] → マークの詳細

3. !CountD Users → マークのテキスト。表計算設定：特定のディメンションは「user_id」；ネストされた計算のMoving Active Usersは「dateの日」。

4. First → フィルターにいれる。表計算設定：表計算設定：特定のディメンションは「user_id」。値の「0」を絞る。

アウトプットは、こんな感じになります。

累積UUはちゃんと出していますね。例えば、
2017-08-03： Aさん
2017-08-09： Aさん、Bさん、Cさん　→ 2017-08-03 〜 2017-08-09 は、「3」になっています。
Dさんの初登場は、2017-10-07なので、2017-10-07から「4」になっていて、その日から、データの最終日まで、全部の累積UUも、「4」　になっています。「11」ではありません。

説明

では、作成した計算フィールドは、一体どういう仕組みになっているでしょうか。
一つずつ見てみましょう。

【Moving Active Users】

「WINDOW_MAX(MIN([レコード数]), FIRST(), 0)」を理解するため、下記のように、「FIRST()」, 「MIN([レコード数])」, 「WINDOW_MAX(MIN([レコード数]), FIRST(), 0)」それぞれを出してみました。

Moving Active Users：「WINDOW_MAX(MIN([レコード数]), FIRST(), 0)」を簡単に言うと、一行目から、現在行までの「MIN([レコード数])」の最大値を出すということですね。
しかも、どの行でも、MIN([レコード数])は１になっているので、この計算式の意図がまだ見えていません。

ただし、user_idを更に列を入れると、
下記のような面白いことが出ました。

赤い部分は、ユーザーの生きている期間で、緑のは、各ユーザーの初登場の日でしょう。
まず、「MIN([レコード数])」を考えてみましょう。
ここのVizにある、「MIN([レコード数])」は、日別で集計され、ユーザーがいた日に、数値が出て、ユーザーがいない日は、NULLになっています。

次、Moving Active Usersのほう、即ち「WINDOW_MAX(MIN([レコード数]), FIRST(), 0)」の方はどうなるでしょう。
後ろに書いてある　FIRST(), 0というのは、一行目から、現在行までを表しています。
WINDOW_MAXを合わせると、一行目から、現在行までの「MIN([レコード数])」の最大値です。
MIN([レコード数])は、ユーザーがいた日しか値がないので、
WINDOW_MAXを使うと、「一行目から、現在行までの「MIN([レコード数])」の最大値」、
つまり、「初日から、当日まで、ユーザーがいたことがあれば、当日に１を付与する」ということです。
赤い部分になります。あるユーザーがある日登場し、その日とその後全部１を付与します。

【!CountD Users】

では、累積UUは、どのように出せばいいのでしょうか。
まず、先程出した数字をヒントとして見てみましょう。
下記の図、青く囲まれている部分を合計すると、１，３，３，３，４，４，４，４，になっています。
つまり、目標の累積UU数ですね。
それは、「ユーザー別で、一行目から、現在行までの「MIN([レコード数])」の最大値」を、日別に沿って合計することです。

「ユーザー別で、一行目から、現在行までの「MIN([レコード数])」の最大値」を、日別に沿って合計するというのは、
この計算式の通りです：!CountD Users：「WINDOW_SUM([Moving Active Users])」

ただし、それを正しく出すため、表計算の方向をちゃんとしないといけません。下記を参考してください。
!CountD Users：特定のディメンションは「user_id」
ネストされた計算のMoving Active Users：特定のディメンションは「dateの日」。
下記をご参考ください。

そうすると、
!CountD Usersは、user_idに沿って合計します。
「user_idに沿う」＝「先程のキャプチャーの青い枠に沿う」＝「一つずつのウインドウ(window)に沿う」。
user_idに沿って、合計するものは、dateの日に沿ったMoving Active Users(青い枠の中にある数字)です。
結局、累積UU数はこれになります↓

追記：ここでは、user_idを詳細に入れないと、特定のディメンションに出ないので、気をつけてください。

【First】

四人があるので、user_idを詳細に入れると、累積UUは繰り返し4回出ています。
解決方法：First：「FIRST()」 のフィールドを作って、フィルターにいれますと、綺麗になります。(表計算設定：特定のディメンションは「user_id」)
それは、可視化Vizの方で、１人目以外のデータを非表示のやり方です。
何故かと言うと、FIRST()の関数を使うと、Aさん、Bさん、Cさん、Dさんそれぞれは、０，−１，−２，−３の順序番号を付与されます。フィルターにいれて、「0」を絞ると、１人目以外のデータは非表示になります。

追記：**FIRST()**の代わりに、**INDEX()**でも同じように使えます。ただし、「0」ではなく、「1」を絞ります。(INDEXは1から数えるわけです)。

終わりに

タブローの表計算を使い、色々と組み合わせすることで、複雑な数字でも出すことができます。
タブローの公式サイトにある記事や、他人の記事を参考しながら上達していきましょう。

追記：この記事は、グロービスのアドベントカレンダーの20日目(2017/12/20)です。
他にも色々ありますので、ぜひ[ここ←] (https://qiita.com/advent-calendar/2017/globis)御覧ください。