アプリのリテンションをSQLで出して可視化する

f:id:j-hiraiwa:20180711152430p:plain

アプリの３つのリテンションについて、実際にSpin Appチームで行なっている分析をSQLの解説を中心にご紹介します。

あいさつ
リテンションとは
SQL
可視化
まとめ

あいさつ

こんにちは！Opt Technologiesの岩井と申します。主にSpin Appというサービスのサーバサイド開発を行なっております。最近は開発以外にはBIツールのR&Dやデータ分析なんかもしています。今回は「アプリのリテンションをSQLで出して可視化する」というテーマで記事を書いていきます。

リテンションとは

リテンションとはざっくり言うとユーザーがアプリを継続的に使用しているかを測る指標です。リテンションにもいくつか種類がありまして

クラシックリテンション
ローリングリテンション
フルリテンション

の３つのリテンションが代表的です。　　 f:id:j-hiraiwa:20180711152435p:plain

SQL

では、SQLのサンプルを紹介して行きます。SpinAppではBigqueryを使用していますので、構文や関数等はBigqueryのStandardSQLに準拠しています。Bigqueryはとにかく速いので膨大なデータを高速で分析するには最適なDBです。今回は流入元(インストールに貢献した広告)ごとでのリテンションを見ることにします。以下、今回登場するテーブルとカラムです。

テーブル名:logs

カラム名	型	説明
user_id	STRING	ユーザー一意のID
event	STRING	「セッション」「インストール」等のイベント名が入る
ad	STRING	流入元
install_at	INTEGER(unixtime)	インストールされた時間
event_at	INTEGER(unixtime)	イベントが発火した時間
_PARTITIONTIME	TIMESTAMP	Bigqueryが持つ擬似列

※ _PARTITIONTIMEについての詳細な説明は割愛しますが、これを指定することによりBigqueryのクエリ課金を抑えています。
以下、各リテンションをインストールから7日目まで算出し流入元毎の割合を算出するSQLです。

1. クラシックリテンション

WITH classic AS
(SELECT 
    user_id,
    MAX(CASE day WHEN 1 THEN 1 ELSE 0 END) AS day_1,
    MAX(CASE day WHEN 2 THEN 1 ELSE 0 END) AS day_2,
    MAX(CASE day WHEN 3 THEN 1 ELSE 0 END) AS day_3,
    MAX(CASE day WHEN 4 THEN 1 ELSE 0 END) AS day_4,
    MAX(CASE day WHEN 5 THEN 1 ELSE 0 END) AS day_5,
    MAX(CASE day WHEN 6 THEN 1 ELSE 0 END) AS day_6,
    MAX(CASE day WHEN 7 THEN 1 ELSE 0 END) AS day_7
  FROM
  (SELECT DISTINCT
      user_id,
      DATETIME_DIFF(DATETIME(TIMESTAMP_SECONDS(event_at),'Asia/Tokyo'),DATETIME(TIMESTAMP_SECONDS(install_at),'Asia/Tokyo') ,DAY ) AS day
    FROM
      logs
    WHERE 
      _PARTITIONTIME BETWEEN TIMESTAMP('2017-08-01') AND TIMESTAMP('2017-08-31')
    AND event ='session'
    AND user_id IS NOT NULL AND user_id != '')
  GROUP BY user_id
)   

SELECT
  ad,
  100 AS day_0,  
   round(SUM(day_1) / COUNT(ad)*100,2) AS day_1,
   round(SUM(day_2) / COUNT(ad)*100,2) AS day_2,
   round(SUM(day_3) / COUNT(ad)*100,2) AS day_3,
   round(SUM(day_4) / COUNT(ad)*100,2) AS day_4,
   round(SUM(day_5) / COUNT(ad)*100,2) AS day_5,
   round(SUM(day_6) / COUNT(ad)*100,2) AS day_6,
   round(SUM(day_7) / COUNT(ad)*100,2) AS day_7
FROM
  (
  SELECT
    _PARTITIONTIME AS pt,
    ad,
    COALESCE(day_1,0)　AS day_1,
    COALESCE(day_2,0)　AS day_2,
    COALESCE(day_3,0)　AS day_3,
    COALESCE(day_4,0)　AS day_4,
    COALESCE(day_5,0)　AS day_5,
    COALESCE(day_6,0)　AS day_6,
    COALESCE(day_7,0)　AS day_7
  FROM
    logs l
  LEFT OUTER JOIN
    classic c
  ON l.user_id= c.user_id   
  WHERE
     _PARTITIONTIME BETWEEN TIMESTAMP('2018-01-01') AND TIMESTAMP('2018-01-31')
  AND event='install'
  AND l.user_id IS NOT NULL AND l.user_id!=''
AND l.ad IS NOT NULL AND l.ad!=''
)
GROUP BY ad

解説

セッションが上がった時間とインストールされた時間の差分を取り、それが「n日」と一致するか判定する。一致すれば「1」なければ「0」を「day_n」に格納する。
user_idで集計する。「day_n」には0か1しか入っていないので、その最大値を取ることでuser_idに「インストールからn日目にセッションが上がったかどうか」が紐づく。(ここまでview)
installイベントが発火した際の「user_id」「ad」を取得する。これにview[classic]をuser_idをキーに左外部結合する。これによりインストール（day_0に相当）から7日間のクラシックリテンションが取得できる。
adで集計し、各カラムの合計値を出す。それを全体のレコード数で割り、百分率をだす。

2. ローリングリテンション

WITH rolling AS
(SELECT 
    user_id,
    MAX(CASE WHEN day BETWEEN 1 AND 30 THEN 1 ELSE 0 END) AS day_1,
    MAX(CASE WHEN day BETWEEN 2 AND 30 THEN 1 ELSE 0 END) AS day_2,
    MAX(CASE WHEN day BETWEEN 3 AND 30 THEN 1 ELSE 0 END) AS day_3,
    MAX(CASE WHEN day BETWEEN 4 AND 30 THEN 1 ELSE 0 END) AS day_4,
    MAX(CASE WHEN day BETWEEN 5 AND 30 THEN 1 ELSE 0 END) AS day_5,
    MAX(CASE WHEN day BETWEEN 6 AND 30 THEN 1 ELSE 0 END) AS day_6,
    MAX(CASE WHEN day BETWEEN 7 AND 30 THEN 1 ELSE 0 END) AS day_7
  FROM
  (SELECT DISTINCT 
      user_id,
      DATETIME_DIFF(DATETIME(TIMESTAMP_SECONDS(event_at),'Asia/Tokyo'),DATETIME(TIMESTAMP_SECONDS(install_at),'Asia/Tokyo') ,DAY ) AS day
    FROM
      logs
    WHERE 
      _PARTITIONTIME BETWEEN TIMESTAMP('2018-01-01') AND TIMESTAMP('2018-01-31')
    AND event ='session'
    AND user_id IS NOT NULL AND user_id != '')
GROUP BY user_id
)   

SELECT
  ad,
  100 AS day_0,  
   round(SUM(day_1) / COUNT(ad)*100,2) AS day_1,
   round(SUM(day_2) / COUNT(ad)*100,2) AS day_2,
   round(SUM(day_3) / COUNT(ad)*100,2) AS day_3,
   round(SUM(day_4) / COUNT(ad)*100,2) AS day_4,
   round(SUM(day_5) / COUNT(ad)*100,2) AS day_5,
   round(SUM(day_6) / COUNT(ad)*100,2) AS day_6,
   round(SUM(day_7) / COUNT(ad)*100,2) AS day_7
FROM
  (
  SELECT
    _PARTITIONTIME AS pt,
    ad,
    COALESCE(day_1,0)　AS day_1,
    COALESCE(day_2,0)　AS day_2,
    COALESCE(day_3,0)　AS day_3,
    COALESCE(day_4,0)　AS day_4,
    COALESCE(day_5,0)　AS day_5,
    COALESCE(day_6,0)　AS day_6,
    COALESCE(day_7,0)　AS day_7
  FROM
    logs l
  LEFT OUTER JOIN
    rolling r
  ON l.user_id= r.user_id   
  WHERE
     _PARTITIONTIME BETWEEN TIMESTAMP('2018-01-01') AND TIMESTAMP('2018-01-31')
  AND event='install'
  AND l.user_id IS NOT NULL AND l.user_id!=''
  AND l.ad IS NOT NULL AND l.ad!=''
)
GROUP BY ad

解説

セッションが上がった時間とインストールされた時間の差分を取り、それが「n日以上、30日未満」にあるか判定する。あれば「1」なければ「0」を「day_n」に格納する。(弊社ではローリングリテンションを30日の期間で見ることが多いので30日としてますが、状況によってここの数字は変更しています。)
user_idで集計する。「day_n」には0か1しか入っていないので、その最大値を取ることでuser_idに「インストールからn日目に見なしセッションが上がったかどうか」が紐づく。(ここまでview)
以下クラシックリテンションと同じ作業なので割愛。

3. フルリテンション

WITH term_1
AS (
  SELECT DISTINCT
    user_id,
    1 AS day_1
  FROM
    logs
  WHERE
    _PARTITIONTIME BETWEEN TIMESTAMP('2018-01-01') AND TIMESTAMP('2018-01-31')
   AND event='session'
   AND DATETIME_DIFF(DATETIME(TIMESTAMP_SECONDS(event_at),'Asia/Tokyo'),DATETIME(TIMESTAMP_SECONDS(installed_at ),'Asia/Tokyo') ,DAY) = 1)
,term_2
  AS (
  SELECT DISTINCT
    user_id,
    1 AS day_2
  FROM
    logs
  WHERE
    _PARTITIONTIME BETWEEN TIMESTAMP('2018-01-01') AND TIMESTAMP('2018-01-31')
   AND event='session'
   AND DATETIME_DIFF(DATETIME(TIMESTAMP_SECONDS(event_at),'Asia/Tokyo'),DATETIME(TIMESTAMP_SECONDS(installed_at ),'Asia/Tokyo') ,DAY) = 2)
,term_3
  AS (
  SELECT DISTINCT
    user_id,
    1 AS day_3
  FROM
    logs
  WHERE
    _PARTITIONTIME BETWEEN TIMESTAMP('2018-01-01') AND TIMESTAMP('2018-01-31')
   AND event='session'
   AND DATETIME_DIFF(DATETIME(TIMESTAMP_SECONDS(event_at),'Asia/Tokyo'),DATETIME(TIMESTAMP_SECONDS(installed_at ),'Asia/Tokyo') ,DAY )= 3)
,term_4
  AS (
  SELECT DISTINCT
    user_id,
    1 AS day_4
  FROM
    logs
  WHERE
    _PARTITIONTIME BETWEEN TIMESTAMP('2018-01-01') AND TIMESTAMP('2018-01-31')
   AND event='session'
   AND DATETIME_DIFF(DATETIME(TIMESTAMP_SECONDS(event_at),'Asia/Tokyo'),DATETIME(TIMESTAMP_SECONDS(installed_at ),'Asia/Tokyo') ,DAY) = 4)
,term_5
  AS (
  SELECT DISTINCT
    user_id,
    1 AS day_5
  FROM
    logs
  WHERE
    _PARTITIONTIME BETWEEN TIMESTAMP('2018-01-01') AND TIMESTAMP('2018-01-31')
   AND event='session'
   AND DATETIME_DIFF(DATETIME(TIMESTAMP_SECONDS(event_at),'Asia/Tokyo'),DATETIME(TIMESTAMP_SECONDS(installed_at ),'Asia/Tokyo') ,DAY) = 5)
,term_6
  AS (
  SELECT DISTINCT
    user_id,
    1 AS day_6
  FROM
    logs
  WHERE
    _PARTITIONTIME BETWEEN TIMESTAMP('2018-01-01') AND TIMESTAMP('2018-01-31')
   AND event='session'
   AND DATETIME_DIFF(DATETIME(TIMESTAMP_SECONDS(event_at),'Asia/Tokyo'),DATETIME(TIMESTAMP_SECONDS(installed_at ),'Asia/Tokyo') ,DAY) = 6)
,term_7
  AS (
  SELECT DISTINCT
    user_id,
    1 AS day_7
  FROM
    logs
  WHERE
    _PARTITIONTIME BETWEEN TIMESTAMP('2018-01-01') AND TIMESTAMP('2018-01-31')
   AND event='session'
   AND DATETIME_DIFF(DATETIME(TIMESTAMP_SECONDS(event_at),'Asia/Tokyo'),DATETIME(TIMESTAMP_SECONDS(installed_at ),'Asia/Tokyo') ,DAY )= 7)

SELECT
 ad,
 100 AS day_0,  
  round(SUM(day_1) / COUNT(ad)*100,2) AS day_1,
  round(SUM(day_2) / COUNT(ad)*100,2) AS day_2,
  round(SUM(day_3) / COUNT(ad)*100,2) AS day_3,
  round(SUM(day_4) / COUNT(ad)*100,2) AS day_4,
  round(SUM(day_5) / COUNT(ad)*100,2) AS day_5,
  round(SUM(day_6) / COUNT(ad)*100,2) AS day_6,
  round(SUM(day_7) / COUNT(ad)*100,2) AS day_7
FROM   
  (
    SELECT
      a.ad,
      _PARTITIONTIME AS pt,
      COALESCE(day_1,0) AS day_1,
      COALESCE(day_2,0) AS day_2,
      COALESCE(day_3,0) AS day_3,
      COALESCE(day_4,0) AS day_4,
      COALESCE(day_5,0) AS day_5,
      COALESCE(day_6,0) AS day_6,
      COALESCE(day_7,0) AS day_7
    FROM 
      logs l
    LEFT OUTER JOIN term_1 
      ON l.user_id=term_1.user_id
    LEFT OUTER JOIN  term_2
      ON term_1.user_id= term_2.user_id
    LEFT OUTER JOIN  term_3
      ON term_2.user_id= term_3.user_id
    LEFT OUTER JOIN  term_4
      ON term_3.user_id= term_4.user_id 
    LEFT OUTER JOIN  term_5
      ON term_4.user_id= term_5.user_id 
    LEFT OUTER JOIN  term_6
      ON term_5.user_id= term_6.user_id
    LEFT OUTER JOIN  term_7
      ON term_6.user_id= term_7.user_id   
     WHERE
       _PARTITIONTIME BETWEEN TIMESTAMP('2018-01-01') AND TIMESTAMP('2018-01-31')
     AND event = 'install' 
     AND l.user_id IS NOT NULL AND l.user_id!=''
 )
GROUP BY ad

解説

インストールからn日後にセッションが上がったユーザーを取得するviewを7個作成する。その際、カラム「day_n」も作成し、1を格納する。各viewはuser_id,day_nでユニークとなるようにしておく。
installイベントが発火した際の「user_id」「ad」を取得する。これを起点に各viewをuser_idをキーに外部結合していく。その際、「term_n」に「term_n+1」を左外部結合していく。
adで集計し、百分率を出す。

可視化

SQLで各リテンションの流入元ごとの割合を算出しました。この結果を可視化してみましょう。今、様々なBIツールが出ていますが、今回は無料でお手軽な「Google Data Studio」を使ってグラフ化してみます。先のテーブルは実際のテーブルではありませんので、ここで紹介するグラフはとある非ゲーム系アプリのデータを可視化したものです。（クライアント名や流入元は隠しています）
使い方の詳細は割愛しますが、基本的にはSQLの結果を描画できるものと考えていただいて構いません。今回は各流入元ごとのリテンションを棒グラフで表示してみます。

f:id:j-hiraiwa:20180711152709p:plain

f:id:j-hiraiwa:20180711152712p:plain

f:id:j-hiraiwa:20180711152714p:plain

流入元ごとの差はそこまで大きくないですが、各リテンションの特徴はグラフから見て取れると思います。非ゲーム系のアプリは連続起動（フルリテンション）をKPIに置くケースは少なく、結果としても5日以上連続で起動しているユーザーはいませんでした。アプリによって追うべきリテンションは違うので自社のアプリの特性を考えた上で適切なKPIを立てることが求められます。データを可視化できれば、その判断も容易です。

まとめ

アプリのリテンションのSQL、可視化についてご紹介しました。リテンションはユーザーの継続利用を示す非常に重要な指標の1つですので、しっかり分析・可視化をすることでアプリをより発展させていくことができます。もちろんアプリにはリテンション以外に分析すべきことが山のようにあります。分析するにはデータが必要であり、まずはデータを貯めることがアプリマーケティングを成功させる第一歩であると言えます。そして貯めたデータを分析し、効果的な打ち手に繋げどんどんPDCAを回していくことが必要です。

オプトテクノロジーズではエンジニア採用も積極的に行なっています！
様々なプロダクトがありますので、少しでもご興味を持って頂けた方はぜひこちらからご連絡ください！
※カジュアル面談ご希望の方は、補足欄に「カジュアル面談希望」とご記載ください。

Opt Technologies Magazine

オプトテクノロジーズ公式Webマガジン