Sisense日本公式ブログ

SQLは、アナリストにとって最も強力なツールの1つです。SQL Superstarでは、この汎用性の高い言語を最大限に活用し、美しく効果的なクエリを作成するための実用的なアドバイスをお届けします。

ビジネスにおいて、最も避けるべきことは顧客を失うことです。スタートアップ企業であれば、顧客維持が重要であることをご存知でしょう。ユーザー維持率を常に測定し、改善することで、より多くのユーザーを長期的に維持できるようにする必要があります。この記事では、SQLを使って独自のデータからユーザー維持率を計算する方法を紹介します。

リテンションの定義

グロリアが月曜日に製品を使用し、火曜日にも製品を使用した場合、彼女は継続的なユーザーと言えます。ビルが月曜日に製品を使用し、火曜日には使用しなかった場合、彼は失効したユーザーです。月曜日の保持率は、保持されたユーザー数を総ユーザー数で割ったものです。月曜日のユーザーがGloriaとBillの2人だけだった場合、月曜日の保持率は50%です。

基本的なユーザー維持率の算出

リテンションを計算するには、#1の時点でアクティブだったユーザーを数え、次に#2の時点でアクティブだったユーザーの数を数えることが重要です。SQLでこれを行う簡単な方法は、次のようにユーザーアクティビティテーブルを自身に左結合することです：

select *

from activity

left join activity as future_activity on

  activity.user_id = future_activity.user_id

  and activity.date = future_activity.date - interval '1 day'

現在、ユーザーのアクティビティの各行には、同じ行に1日後のユーザーのアクティビティが表示されています。これは、いくつかの単純なカウントでリテンションを計算するための理想的なテーブルとなります：

select

  activity.date, 

  count(distinct activity.user_id) as active_users, 

  count(distinct future_activity.user_id) as retained_users,

  count(distinct future_activity.user_id) / 

    count(distinct activity.user_id)::float as retention

from activity

left join activity as future_activity on

  activity.user_id = future_activity.user_id

  and activity.date = future_activity.date - interval '1 day'

group by 1

このような図式になります：

さらに、1日の保存期間を7日や30日に変更すると、より長期的なユーザーのエンゲージメントを把握することができます。

新規ユーザーと既存ユーザーのリテンションを計算する

多くの場合、登録したばかりのユーザーと、ロイヤルティの高い長期的なユーザーとでは、リテンションが大きく異なります。新規ユーザーのリテンションを計算するには、単純にユーザーテーブルに参加し、そのユーザーの参加日に発生したアクティビティ行のみを調べます：

select

  users.date as date,

  count(distinct activity.user_id) as new_users, 

  count(distinct future_activity.user_id) as retained_users,

  count(distinct future_activity.user_id) / 

    count(distinct activity.user_id)::float as retention

from activity

-- Limits activity to activity from new users

join users on

  activity.user_id = users.id 

  and users.date = activity.date

left join activity as future_activity on

  activity.user_id = future_activity.user_id

  and activity.date = future_activity.date - interval '1 day'

group by 1

全体のリテンションが46％であるのに対し、新規ユーザーのリテンションはわずか5.8％であることがわかります。これで、新規ユーザーを分けることが非常に有効であることがわかりました。新規ユーザーの保持率を向上させることは、明らかに優先すべきことです。

戻ってきたユーザーの保持率を見るには、単純に次のように変更します：

users.date = activity.date 

to:

users.date != activity.date

これにより、その日に参加したユーザーのアクティビティは効果的に除外されます。クエリは次のようになります：

select

  activity.date as date,

  count(distinct activity.user_id) as new_users, 

  count(distinct future_activity.user_id) as retained_users,

  count(distinct future_activity.user_id) / 

    count(distinct activity.user_id)::float as retention

from activity

-- Limits activity to activity from existing users

join users on 

  activity.user_id = users.id 

  and users.date != activity.date

left join activity as future_activity on

  activity.user_id = future_activity.user_id

  and activity.date = future_activity.date - interval '1 day'

group by 1

予想通り、既存ユーザーの維持率は全体の平均よりも高い。66%対46%です。

コホートにおけるリテンションの算出

A週に加わったユーザーとB週に加わったユーザーの定着率を比較することは、非常に有益です。これにより、製品の変更によって定着率が向上しているかどうかを確認することができます。

理想的には、次のようなグラフになります：

まず、問題を簡単にするために、便利なサブクエリをいくつか定義します。new_user_activityは、ユーザーの活動を新規ユーザーに制限します：

with new_user_activity as (

  select activity.* from activity

  join users on

    users.id = activity.user_id

    and users.date = activity.date

)

Cohort_active_user_countは、各デイリーコホートにおけるアクティブユーザーの総数（リテンション計算の分母）を算出します：

, cohort_active_user_count as (

  select 

    date, count(distinct user_id) as count 

  from new_user_activity

  group by 1

)

それに加えて、メインのクエリにいくつかの小さな変更を加えます。

保持期間（日数）をfuture_activity.date - new_user_activity.dateとして計算し、それによってグループ化します。このグループ化では、コホート内のアクティブユーザーの単純なカウントが失われます。幸いなことに、この点を考慮してCohort_active_user_countサブクエリを作成し、これに結合して分母として使用することができます。

最後に、見栄えのする出力を作成し、偽の行を除外し、ソートを行う外部クエリでクエリをラップします。

select date, 'Day '|| to_char(period, 'DD') as period,

  new_users, retained_users, retention 

from (

  select 

    new_user_activity.date as date,

    (future_activity.date 

      - new_user_activity.date) as period,

    max(cohort_size.count) as new_users, -- all equal in group

    count(distinct future_activity.user_id) as retained_users,

    count(distinct future_activity.user_id) / 

      max(cohort_size.count)::float as retention

  from new_user_activity

  left join activity as future_activity on

    new_user_activity.user_id = future_activity.user_id

    and new_user_activity.date < future_activity.date

    and (new_user_activity.date + interval '10 days')

      >= future_activity.date

  left join cohort_active_user_count as cohort_size on 

    new_user_activity.date = cohort_size.date 

  group by 1, 2) t

where period is not null

order by date, period

また、私たちのお気に入りのSQLトリックの1つであるレンジジョインを使って、複数の日の保持量を1つのチャートに表示していることにも注目してください。

この結果、テーブルには：

Sisense for Cloud Data Teamsでは、結果を自動的にピボットし、パーセンタイルで色分けすることができます：

リテンションをより具体的に

新規ユーザーと既存ユーザーのリテンションがどれほど違うか覚えていますか？多くのユーザーセグメントで同じような変化が見られます。人口統計、ユーザー獲得チャネル、有料ユーザーと非有料ユーザー、または閲覧、作成、購入などのアクティビティの種類によってリテンションを分けることは効果的です。

※本記事は、「How To Calculate Cohort Retention in SQL」を翻訳・加筆修正したものです。