会員サイトの休眠会員を、最終利用日から見つける

一度は利用していた会員の4人に1人が、直近30日は利用していない。

よくある状況

会員サイトの報告では、その月に利用した会員の数が並びます。先月と同じくらいの人数が利用していれば、変わりはないように見えます。

ただ、利用した人数が同じでも、中身が入れ替わっていることがあります。新しく利用するようになった会員がいる一方で、以前は利用していた会員が来なくなっている。いわゆる休眠会員は、こうして少しずつ増えていきます。

会員IDでアクセスログを会員ごとにまとめると、会員それぞれが最後に利用した日が分かります。基準日からの日数で分けると、来なくなりかけている会員がどれだけいるかを数えられます。

最後に利用した日からの日数ごとに、会員の人数が分かるグラフ

最後に利用してからの日数,会員（人）
30日以内,488
31〜60日,57
61〜90日,27
91〜120日,22
121日以上,71

利用したことのある会員の27%（665人中177人）が、31日以上利用していない。そのうち半分以上は、91日以上利用していない。

このグラフの数え方

対象：2026年4〜9月に1回以上利用した、退会していない会員（665人）
「利用した」：ログインした状態でページを1回以上閲覧したこと
日数の基準日：2026年9月30日（実際には集計した日）
言えないこと：31日以上の空きが、離れたのか、たまたま間が空いただけなのかは、この表だけでは決められない

架空のサンプルデータで集計した例です。

これが分かると

休眠に入りかけた会員（31〜90日）と、すでに離れた会員（91日以上）で、声のかけ方を分けられます。
「最後に利用した日」を会員ごとに持っておけば、毎月同じ基準で休眠の人数を追えます。
来なくなった時期がそろっていれば、その頃に何があったかをたどれます。

ここに挙げた使い方は一例です。サンプルの数字は架空のもので、実際の結果や施策の効果を約束するものではありません。

試してみる

ここから先は、システム担当者に渡してください。集計SQLで出た結果を、AIプロンプトと一緒にAIへ貼ると、上のようなグラフと読み取りが返ってきます。

集計SQL件数と割合だけを出します

select case
         when days <= 30 then '30日以内'
         when days <= 60 then '31〜60日'
         when days <= 90 then '61〜90日'
         when days <= 120 then '91〜120日'
         else '121日以上'
       end as since_last_visit,
       count(*) as members
from (
  select a.member_id,
         date '2026-09-30' - max(a.accessed_at)::date as days  -- 基準日。実際には current_date にする
  from access_log a
  join member m on m.member_id = a.member_id
  where m.withdrawn_at is null
    and a.accessed_at >= '2026-04-01' and a.accessed_at < '2026-10-01'  -- 期間。実際には集計する期間に差し替える
  group by a.member_id
) t
group by 1
order by min(days);

AIプロンプト集計結果を貼るだけです

次の表は、会員サイトに一度でもログインして利用したことがある会員を、最後に利用した日からの日数で分けた集計です。
since_last_visit＝最後に利用してからの日数、members＝会員数（退会者を除く）です。
個人を特定できる列は含めていません。

1. since_last_visit ごとの members を棒グラフにして、画像で描いてください。各区分に、全体に占める割合を添えてください。
2. 次の点を読み取って、3行程度で教えてください。
   ・31日以上利用していない会員は、全体の何割か
   ・91日以上利用していない会員は、31日以上利用していない会員の何割か
   ・休眠に入りかけた会員と、すでに離れた会員で、声のかけ方を分けるべきか

（ここに集計結果を貼る）

社内用SQL会員IDの一覧が出ます。社外のサービスやAIには貼らないでください

-- 社内用：会員IDの一覧が出ます。社外のサービスやAIには貼らないでください
select a.member_id, m.occupation, m.specialty, max(a.accessed_at) as last_visit
from access_log a
join member m on m.member_id = a.member_id
where m.withdrawn_at is null
  and a.accessed_at >= '2026-04-01' and a.accessed_at < '2026-10-01'  -- 期間。実際には集計する期間に差し替える
group by a.member_id, m.occupation, m.specialty
having max(a.accessed_at) < date '2026-09-30' - 90  -- 基準日。実際には current_date にする
order by last_visit;

SQLとプロンプトは、準備編の汎用テーブルを前提にした例です。実際のテーブル名・列名に合わせて書き換えてください。動作や結果を保証するものではありません。

使う表： member（会員）／access_log（アクセスログ）（表の形は準備編）

試せなかったら

アクセスログに会員IDが残っていないと、この問いには答えられません。
アクセス解析で分かるのは「その月に何人来たか」までで、「名簿の中の誰が、いつを最後に来なくなったか」は出せないからです。

writeWiredでは、会員IDの付いたアクセスログとメール・フォームの記録が最初から同じ場所に揃っています。会員サイトCMSとしてのwriteWired

次に読む

会員ログの活用法一覧へ