🧴スキンケアアプリのリアルなDAU集計物語📊〜リセマラユーザーを見抜け！〜

こんにちは！Yuinaです。

今日はスキンケアアプリのDAU（デイリーアクティブユーザー）集計を通じて、「リセマラユーザー」を除外する方法をお話しします✨

リセマラとは…ゲームやアプリで「何度もアカウントを作り直して、いいアイテムや状態になるまでやり直す」行為のこと。
スキンケアアプリでも、実はリセマラユーザーがいるんです…！😲

登場人物紹介👩‍🦰👨‍🦱👩‍🦳

• 🌸 さくらさん（device_id: device_01）
  • 2025-05-01に初めてアカウント作成（user_id: 1001）
  • 2025-05-05に「リセマラ」してアカウント作り直し（user_id: 1020）
• 🌟 たけしさん（device_id: device_02）
  • 2025-05-02にアカウント作成（user_id: 1002）
• 💧 みずきさん（device_id: device_03）
  • 2025-05-03にアカウント作成（user_id: 1003）
  • 2025-05-04にリセマラして作り直し（user_id: 1015）
  • 2025-05-06にさらにリセマラ（user_id: 1030）

テーブルイメージ：users

テーブルイメージ：login_logs

リセマラ除外の考え方💡

device_03 → 1030が正規、1003・1015はリセマラユーザー

同じ device_id の中で最新の created_at の user_id が正規ユーザー

それ以前に作ったアカウントはリセマラユーザーとみなす

なので上記で言うと：

device_01 → user_id 1020 が正規、1001はリセマラユーザー

device_02 → 1002のみ

どうやって集計するの？(プログラム作成)

-- ✨まずは「最新の正規ユーザー」を端末ごとに探すサブクエリ✨
WITH 最新の正規ユーザー AS (
  SELECT 
    device_id,                 -- 📱 ユーザーの端末ID（同じ人が複数アカウント作ったかのカギ🔑）
    MAX(created_at) AS 最新作成日時  -- ⏰ その端末で一番新しいアカウントの作成日をゲット！
  FROM users
  WHERE device_id IS NOT NULL AND device_id <> ''  -- 🚫 空っぽのdevice_idは除外するよ
  GROUP BY device_id           -- 🧩 端末ごとにグループ分け
),

-- ✨最新ユーザーだけを元テーブルから引っ張ってくるよ✨
正規ユーザーだけ AS (
  SELECT u.user_id, u.created_at
  FROM users u
  JOIN 最新の正規ユーザー l
    ON u.device_id = l.device_id 
   AND u.created_at = l.最新作成日時   -- 🌈 最新の作成日時とピッタリ合うユーザーだけ抽出！
)

-- 🌟ここからは実際にDAUを集計しよう🌟
SELECT
  ll.login_date AS 日付,                          -- 📅 日付ごとにまとめて
  COUNT(DISTINCT ll.user_id) AS DAU,             -- 👥 ログインしたユニークユーザー数（リセマラ含む）
  COUNT(DISTINCT CASE WHEN vu.user_id IS NOT NULL THEN ll.user_id END) AS DAU_リセマラ除外
  -- 💎 最新ユーザーだけをカウント！リセマラユーザーは数えないよ！
FROM login_logs ll
LEFT JOIN 正規ユーザーだけ vu ON ll.user_id = vu.user_id
-- 🔍 ログインしたユーザーに対して「正規ユーザーかどうか」をチェックしてるの
GROUP BY ll.login_date
ORDER BY ll.login_date;

結果イメージ🎉

まとめ

リセマラユーザーを除外するには、端末IDごとに最新のアカウントのみを正規ユーザーと見なします。

こうすることで、本当にアプリを使っている実ユーザー数を正確に把握できます。

そして、DAUの数値が「実はリセマラで水増しされていた…」という悲しい誤解を防げます！💪

ありがとうございました✨🌈