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」を翻訳・加筆修正したものです。