LinkedIn
Twitter
Lookerではテーブル計算でのrunning_total関数や、Measureでrunning_totalパラメータが準備されていますので、Explore上で簡単に累計値を取ることができます。ですが、例えばユーザーの新規純増分だけを抽出してその累計を出したい、と言うようなケースでは、少し工夫が必要になります。
こちらの記事にあるように、日付でソートし「一つ前の日付」を参照することで、そのユーザーが新規なのかどうかを判断することができます。これを使って、純増分のユーザー数をカウントすることでDISTINCTした累計を算出することができます。
考え方については前述の記事の通りなのですが、これをもう少し具体的に見ていきたいと思います。例えば、次のようなデータがあるとします。
田中 一郎は10-01に初回ログインをしており、10-03に2回目のログインをしています。同様に佐藤 次郎は10-01と10-02にログインをしています。このログインユーザーのUUを日別にとると、次のようなUUおよび累計になります。
ここで、10-01のUUは初日なので2で問題ありません。しかし、10-02の累計値は5になっています。10-02に着目すると佐藤 次郎は確かにログインをしているのですが、この人はすでに10-01にログイン済みなので、当該期間の延べ数としてカウントしたくありません。
そこで、それぞれのログインユーザーの「前回のログイン日」を参照することで、それぞれのユーザーが初めてログインしたのか、それとも以前にログインしたことがあるのかを区別するロジックを作って、これを実現してみます。ここではサブクエリーを書く必要がありますが、Lookerでは、このようなケースではネイティブ派生テーブルを利用すると、すっきりと記述することができます。
まず、先程のログイン日とログインユーザーIDを表示させている画面で、ギアマークから Get LookML をクリックします。
続いて、Derived Tableタブをクリックして、生成されたコードをクリップボードにコピーします。
得られたLookMLのコード（ネイティブ派生テーブルのコード）を、LookMLプロジェクトの中に記述します。このとき、View名をわかりやすいものに変更してください。
得られたネイティブ派生テーブルに二つの変更を追加します。一つは、derived_columnを定義して、LAG関数を用いて「前回ログイン日」を取得することです。もう一つは、bind_all_filters: yes を定義して、元のExploreで指定されたフィルターを全て引き継げるようにしておきます。
derived_table: {
explore_source: moving_distinct_running_total {
column: login_date {}
column: user_id {}
column: count_distinct_user_id {}
derived_column: last_login_date {
sql: LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date ASC) ;;
}
bind_all_filters: yes
}
}
追加した派生テーブルの定義に合わせて、ディメンションおよびメジャーを定義します。このときに、前回ログインの有無を判断することで、新規ユーザーなのかどうかを判断することができますので、これを使って純新規UUという新たなメジャーを作成します。
dimension: is_new_user {
label: "新規ユーザーか？"
type: yesno
sql: ${last_login_date} IS NULL ;;
}
measure: count_of_purely_new_user {
label: "純新規UU"
type: count_distinct
sql: ${user_id} ;;
filters: [is_new_user: "yes"]
}
元のExploreに作成した派生テーブルのViewをJOINすると、純新規UUが追加されますので、これを使ってユーザーの新規純増分を取得することができるようになります。
ここでの純新規UUでは、
10-01→ 田中 一郎, 佐藤 次郎
10-02→ 鈴木 三郎, 山本 四郎
10-03→ 山下 吾郎
となっていますので、この累計をとれば、新規の純増分だけを累計で表現することができます。
参考にしていただければ幸いです。
今回使用した全てのコードは以下を参考にしてください。
LookMLコード
view: moving_distinct_running_total {
label: "ログイン"
derived_table: {
sql:
SELECT '2020-10-01' AS login, '田中 一郎' AS user_id
UNION ALL
SELECT '2020-10-02' AS login, '佐藤 次郎' AS user_id
UNION ALL
SELECT '2020-10-03' AS login, '鈴木 三郎' AS user_id
UNION ALL
SELECT '2020-10-01' AS login, '佐藤 次郎' AS user_id
UNION ALL
SELECT '2020-10-02' AS login, '鈴木 三郎' AS user_id
UNION ALL
SELECT '2020-10-02' AS login, '山本 四郎' AS user_id
UNION ALL
SELECT '2020-10-03' AS login, '山本 四郎' AS user_id
UNION ALL
SELECT '2020-10-03' AS login, '田中 一郎' AS user_id
UNION ALL
SELECT '2020-10-03' AS login, '山下 吾郎' AS user_id
;;
}
dimension: primary_key {
sql: ${login_date} || '-' || ${user_id} ;;
primary_key: yes
hidden: yes
}
dimension_group: login {
label: "ログイン日"
type: time
datatype: date
timeframes: [date]
sql: ${TABLE}.login ;;
}
dimension: user_id {
label: "ユーザーID"
type: string
sql: ${TABLE}.user_id ;;
}
measure: count_distinct_user_id {
label: "UU"
type: count_distinct
sql: ${user_id} ;;
}
measure: rt_count_distinct_user_id {
label: "UU(累計)"
type: running_total
sql: ${count_distinct_user_id} ;;
}
}
view: moving_distinct_running_total_ndt {
label: "ログイン"
derived_table: {
explore_source: moving_distinct_running_total {
column: login_date {}
column: user_id {}
column: count_distinct_user_id {}
derived_column: last_login_date {
sql: LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date ASC) ;;
}
bind_all_filters: yes
}
}
dimension: primary_key {
sql: ${login_date} || '-' || ${user_id} ;;
primary_key: yes
hidden: yes
}
dimension: login_date {
type: date
hidden: yes
}
dimension: user_id {
hidden: yes
}
dimension: count_distinct_user_id {
hidden: yes
type: number
}
dimension: last_login_date {
hidden: yes
type: date
}
dimension: is_new_user {
label: "新規ユーザーか？"
type: yesno
sql: ${last_login_date} IS NULL ;;
}
measure: count_of_purely_new_user {
label: "純新規UU"
type: count_distinct
sql: ${user_id} ;;
filters: [is_new_user: "yes"]
}
measure: rt_count_of_purely_new_user {
label: "純新規UU(累計)"
type: running_total
sql: ${count_of_purely_new_user} ;;
}
}
explore: moving_distinct_running_total {
join: moving_distinct_running_total_ndt {
sql_on: ${moving_distinct_running_total.user_id} = ${moving_distinct_running_total_ndt.user_id}
AND ${moving_distinct_running_total.login_date} = ${moving_distinct_running_total_ndt.login_date} ;;
type: left_outer
relationship: one_to_one
}
}
