0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@ponfreelance

Supabaseで登録週ごとのDay-N継続率ダッシュボードを作る

0
Posted at

Supabaseで登録週ごとのDay-N継続率ダッシュボードを作る

組み込みや制御系の仕事の傍らで SaaS を作り始めると、「ユーザーは何人登録したか」の次に「その人たちは続いているのか」が気になってくる。この記事は、Supabase（PostgreSQL）だけで登録週ごとの Day-N 継続率を出す人向けに、SQL の組み方から公開時の注意点までをまとめたものです。BI ツールは使わず、SQL とビュー1本で動くところまでを扱います。

結論

  • 登録日を JST の暦日に揃え、「登録日から N 日目に1回でも行動したか」を数える
  • 登録週（月曜始まり）でコホートを切り、Day-1 / 3 / 7 / 14 / 30 を横に並べる
  • まだ N 日経っていないユーザーは分母から外す。ここを外すと直近の週がずっと低く見える
  • 集計はビュー1本にして、公開範囲は service role 経由に絞る

問題の深掘り

総ユーザー数に対するアクティブ率だけを見ていると、古い登録者が分母に積み上がるので、数字はじわじわ下がっていく。見たいのは「先週登録した人は、1週間後にまだ来ているか」であって、全体の平均ではない。登録週ごとにコホートを切ると、機能を変えた週の前後で比較できるようになる。

もう1つの理由は、Day-N の定義が人によってぶれやすいことです。大きく2通りあります。

  • N日目ちょうどに行動した人の割合（クラシック・リテンション）
  • N日目以降のどこかで行動した人の割合（アンバウンデッド）

この記事は前者で書きます。後者は「戻ってきたか」を見たいときに向きますが、日数が経つほど値が上がる性質があり、グラフの読み方が変わります。

このダッシュボードは、日次で行動ログが溜まるサービスなら形を変えずに使えます。自分は副業の commit とダイエットの歩数を1つのキャラで育てる ALTER という SaaS を作って運用していて、指標入力が日々のログになる構造です。ただし、以下のテーブル名・カラム名は説明用の例で、ALTER の実スキーマそのものではありません。

解決手順

1. 前提のテーブル

次の2つがあれば足ります。

create table public.profiles (
  id uuid primary key,
  created_at timestamptz not null default now()
);

create table public.activity_logs (
  id bigint generated always as identity primary key,
  user_id uuid not null references public.profiles(id),
  logged_at timestamptz not null default now()
);

create index on public.activity_logs (user_id, logged_at);

行動ログは「何を入力したか」に関係なく、1回でも何かした日を拾えれば足ります。種類別に見たくなったら、あとで列を足せば拡張できます。

2. 日付を JST の暦日に揃える

timestamptz のまま日数を引くと、UTC の日付境界で数がずれます。日本のユーザーが対象なら、先に JST の date に落としておきます。

(created_at at time zone 'Asia/Tokyo')::date

at time zone を通すと timestamp（タイムゾーンなし）になるので、::date で暦日にできます。登録週は date_trunc('week', ...) で出せて、PostgreSQL の週は月曜始まりです。

3. 集計ビュー

登録者ごとに Day-N の行を作り、その日に行動があったかを左結合で見る構成にします。

create or replace view analytics.retention_by_signup_week as
with u as (
  select
    id,
    (created_at at time zone 'Asia/Tokyo')::date as signup_date,
    date_trunc('week', created_at at time zone 'Asia/Tokyo')::date as signup_week
  from public.profiles
),
a as (
  select distinct
    user_id,
    (logged_at at time zone 'Asia/Tokyo')::date as active_date
  from public.activity_logs
)
select
  u.signup_week,
  n.day_n,
  count(*)         as eligible_users,
  count(a.user_id) as retained_users,
  round(100.0 * count(a.user_id) / count(*), 1) as retention_pct
from u
cross join (values (1), (3), (7), (14), (30)) as n(day_n)
left join a
  on a.user_id = u.id
 and a.active_date = u.signup_date + n.day_n
where u.signup_date + n.day_n <= (now() at time zone 'Asia/Tokyo')::date
group by u.signup_week, n.day_n;

要点は3つです。

  • a を distinct で日単位に潰しておくと、同じ日に何回入力しても1回として数えられ、count(*) が人数と一致する
  • where で「登録日 + N 日が今日以前」のユーザーだけを分母に残す。これが未到達の週を除く処理になる
  • analytics スキーマは別途 create schema analytics; で作っておく。public に置くとAPIから見えやすくなるので分けている

4. 横持ちにして読みやすくする

ダッシュボードでは、行が登録週、列が Day-N のほうが読みやすいです。

select
  signup_week,
  max(eligible_users) filter (where day_n = 1) as users,
  max(retention_pct) filter (where day_n = 1)  as d1,
  max(retention_pct) filter (where day_n = 3)  as d3,
  max(retention_pct) filter (where day_n = 7)  as d7,
  max(retention_pct) filter (where day_n = 14) as d14,
  max(retention_pct) filter (where day_n = 30) as d30
from analytics.retention_by_signup_week
group by signup_week
order by signup_week desc;

未到達のセルは行自体が出ないので null になります。「まだ測れない」と「0%」が区別できるので、あえて 0 で埋めません。

5. 画面に出す

最小構成は、Supabase Studio の SQL Editor に上のクエリを保存して見る方法です。画面を作るなら、サーバー側（Edge Function や Next.js の Route Handler）で service role キーを使って読み、ブラウザには整形済みの JSON だけを返します。

import { createClient } from "@supabase/supabase-js";

const supabase = createClient(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!
);

export async function GET() {
  const { data, error } = await supabase
    .schema("analytics")
    .from("retention_by_signup_week")
    .select("*")
    .order("signup_week", { ascending: false });

  if (error) return Response.json({ error: error.message }, { status: 500 });
  return Response.json(data);
}

schema("analytics") を使うには、Supabase の設定で API に公開するスキーマとして analytics を追加する必要があります。公開したうえでロールの権限を絞る手順は、次の節に書きます。

落とし穴と対処

以下は実際に起きやすいものを技術的な理由つきでまとめています。

ビューが RLS を素通しにする
PostgreSQL のビューは、既定では作成者の権限で実行されます。そのため、元テーブルに RLS をかけていても、ビュー経由だと全行が見えてしまう構成になりえます。集計ビューを public に置いて anon キーで叩けるままにすると、全ユーザーの登録日と行動日の集計が外から読めることになります。対処は次のとおりです。

  • ビューは analytics のような別スキーマに置く
  • revoke all on analytics.retention_by_signup_week from anon, authenticated; で権限を外す
  • 読むのは service role（サーバー側）だけにする

タイムゾーンのずれ
UTC のまま date にキャストすると、JST の朝9時前の行動が前日扱いになります。朝活のように早い時間帯に動くサービスだと、Day-1 の数字がまとまって動く原因になります。最初から JST に揃えておくほうが、あとから直すより手戻りが小さいです。

母数が小さい週の数字
登録が1週間に数人なら、1人の有無で Day-7 が 20 ポイント動きます。パーセントだけでなく eligible_users を必ず並べておくと、数字を読み違えにくくなります。

行動の定義が緩すぎる
ログイン1回を「行動」に含めると、通知から開いただけの人が継続扱いになります。何をもって1行動とするかは、集計の前にプロダクト側で決めておく必要があります。

関連ツール

日々の行動ログを焦らせずに溜めていく設計に興味があれば、自分が作っている ALTER を見てみてください。副業の commit とダイエットの歩数を1つのキャラで育てる、焦らせない人生 OS（SaaS）で、全職業を使える無料プランがあります。

→ ALTER

まとめ

  • Day-N 継続率は、登録週のコホートごとに「N 日目に行動したか」を見る
  • 日付は JST の暦日に揃え、未到達の登録者は分母から外す
  • 集計は analytics スキーマのビュー1本にして、service role 経由でだけ読む
  • 母数を横に並べると、小さい週の数字に振り回されにくい

著者：ぽん（@pon_freelance）
C言語実務23年、組み込み／制御系。
副業で技術記事販売と自作ツール販売をやっている。

書いているもの：

  • ALTER - 副業の commit とダイエットの歩数を1つのキャラで育てる、焦らせない人生 OS（SaaS）
    （その他：（なし））
0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?