会員ログの活用法 第3回
会員が大きく減るのは、登録した月のうち。そのあと2か月の減り方はゆるやか。
会員数の報告では、新規登録の人数と累計が並びます。毎月の登録が順調なら、会員サイトは育っているように見えます。
ただ、登録した会員がそのあとも利用しているかどうかは、登録の人数からは分かりません。登録した月のうちに一度も利用しない会員もいれば、最初の数回だけ利用して離れる会員もいます。定着しているかどうかは、登録した月ごとに追いかけないと見えてきません。
会員IDで名簿とアクセスログをつなぐと、登録した月ごとに、当月・1か月後・2か月後に利用した会員を数えられます。こうした表は、コホート表と呼ばれます。
登録月,登録した会員(人),登録した月に利用,1か月後に利用,2か月後に利用 2026-04,66,50,42,42 2026-05,69,42,36,32 2026-06,74,55,52,44 2026-07,68,50,42,38
このグラフの数え方
架空のサンプルデータで集計した例です。
ここに挙げた使い方は一例です。サンプルの数字は架空のもので、実際の結果や施策の効果を約束するものではありません。
ここから先は、システム担当者に渡してください。集計SQLで出た結果を、AIプロンプトと一緒にAIへ貼ると、上のようなグラフと読み取りが返ってきます。
with m as (
select member_id, date_trunc('month', registered_at) as reg_month
from member
where registered_at >= '2026-04-01' and registered_at < '2026-08-01'
and withdrawn_at is null
),
v as (
select distinct member_id, date_trunc('month', accessed_at) as month
from access_log
where member_id is not null
)
select to_char(m.reg_month, 'YYYY-MM') as reg_month,
count(distinct m.member_id) as members,
count(distinct case when v.month = m.reg_month then m.member_id end) as month_0,
count(distinct case when v.month = m.reg_month + interval '1 month' then m.member_id end) as month_1,
count(distinct case when v.month = m.reg_month + interval '2 month' then m.member_id end) as month_2
from m
left join v on v.member_id = m.member_id
group by m.reg_month
order by m.reg_month;次の表は、会員サイトの登録した月ごとの集計です。
reg_month=登録した月、members=登録した会員数(退会者を除く)、month_0=登録した月にサイトを利用した(ログインしてページを見た)会員数、month_1=翌月に利用した会員数、month_2=翌々月に利用した会員数です。
個人を特定できる列は含めていません。
1. 登録した月を行、month_0〜month_2 を列にしたコホート表を、画像で描いてください。各欄に members に対する割合を添え、割合の高い欄ほど濃く塗ってください。
2. 次の点を読み取って、3行程度で教えてください。
・一番大きく減っているのは、どの時点か
・登録した月によって、減り方に違いはあるか
・定着させるために、どの時点に手を打つべきか
(ここに集計結果を貼る)SQLとプロンプトは、準備編の汎用テーブルを前提にした例です。実際のテーブル名・列名に合わせて書き換えてください。動作や結果を保証するものではありません。
使う表: member(会員)/access_log(アクセスログ) (表の形は準備編)
会員の名簿とアクセスログが、会員IDでつながっていないと、この問いには答えられません。
アクセス解析で分かるのは「毎月何人来たか」までで、「何月に登録した人が、何か月後まで利用しているか」は出せないからです。
writeWiredでは、会員IDの付いたアクセスログとメール・フォームの記録が最初から同じ場所に揃っています。会員サイトCMSとしてのwriteWired