リセマラを除外した正確なMAU分析～スキンケアアプリの事例～

こんばんは、Yuinaです🧚

今日はスキンケアアプリのデータを使って、
「リセマラ（複数アカウント作成）の影響を除外した正確なMAU分析」について解説します✨💪

MAU（Monthly Active Users）は、1ヶ月の間にサービスを利用したユニークユーザー数のことです!
MAUは、サービスの健康状態や成長を測る重要な指標です。📝💕

リセマラ問題とは？
どうやってリセマラを除外するの？
プログラムを作ってみよう
解説
まとめ💅🌈

リセマラ問題とは？

スマホアプリでは、ユーザーがゲームなどで「リセット＆再登録」を繰り返すことがあります。
この「リセマラ」により、同じ人が複数のアカウントを作ってしまい、
MAUが実際よりも多くカウントされてしまう問題があります👀

どうやってリセマラを除外するの？

私たちはユーザーを一意に識別するために「guid（端末ID）」を利用しました。
複数のアカウントが同じguidを持つ場合、最新の1アカウントだけを「本当のユーザー」として扱います。

プログラムを作ってみよう

MAU分析の流れ：

最新のユーザーを抽出💡
guidごとに最新のユーザーIDを取得し、リセマラで作成された重複アカウントを除外。
リセマラ除外済みログイン履歴の取得📅
ログイン履歴から、リセマラを除外したユーザーのログインデータだけを抽出。
月別に集計📊
インストール月とログイン月でグループ化し、ユニークユーザー数をカウント。

💾 今回使ったデータベースの中身💖

🧚‍♀️ beauty.users テーブル（ユーザー情報）

user_id	guid	install_time
101	A	2025-02-10
102	A	2025-01-01
201	B	2025-03-05
301	C	2025-01-15

📝 補足：

install_time はアプリをインストールした日時。ここで「いつ入れたか」がわかります💡

guid は端末IDみたいなイメージです。同じ端末で複数アカウント（＝リセマラ）を作っても識別できます！

🪩 beauty.login_log テーブル（ログイン履歴）

user_id	login_time
101	2025-02-15 10:00
102	2025-01-03 08:00
201	2025-03-10 12:30
301	2025-01-20 19:45

📝 補足：

これはユーザーが実際にログインした記録です✨

時間まで含まれていますが、MAU分析では月ごとに YYYY-MM 形式にして使います！

SQL：

WITH latest_users AS (
  	SELECT 
	  	DISTINCT ON (guid)
	  	user_id,
	  	guid,
	  	install_time
  	FROM 
	  	beauty.users
  	ORDER BY 
	  	guid, 
	  	install_time DESC
),

login_with_users AS(
	SELECT
   		lu.user_id,
   		TO_CHAR(lu.install_time, 'YYYY-MM') AS install_month,
   		TO_CHAR(ll.login_time, 'YYYY-MM') AS login_month
	FROM 
		beauty.login_log ll
	JOIN 
		latest_users lu 
	ON 
		ll.user_id = lu.user_id
)

SELECT
	install_month,
	login_month,
	COUNT(DISTINCT user_id) AS unique_users
FROM 
	login_with_users
GROUP BY 
	install_month, 
	login_month
ORDER BY 
	install_month, 
	login_month;

解説

ステップ1：最新ユーザーの抽出（リセマラ対策）

ポイント：同じ端末（guid）で複数アカウントがあったら、一番新しいユーザーだけを残す。

SQL:

WITH latest_users AS (
  	SELECT 
	  DISTINCT ON (guid)
	  user_id,
	  guid,
	  install_time
  	FROM 
	  beauty.users
  	ORDER BY 
	  guid, 
	  install_time DESC
),

guidでグループ化し、install_timeが最新の1件だけを残します。

これにより、古いリセマラアカウントは除外されます。

イメージ：

詳説データベースストレージエンジンと分散データシステムの仕組み [ Alex Petrov ]

価格:4180円
(2025/5/21 22:45時点)
感想(0件)

ステップ２：リセマラ除外済みログイン履歴の取得

ここでは、最新ユーザーだけでログイン履歴を取得します。

その上で、ログイン日時とインストール日時から「月」の情報を抽出し、集計軸にします。

SQL:

login_with_users AS(
	SELECT
   		lu.user_id,
   		TO_CHAR(lu.install_time, 'YYYY-MM') AS install_month,
   		TO_CHAR(ll.login_time, 'YYYY-MM') AS login_month
	FROM 
		beauty.login_log ll
	JOIN 
		latest_users lu 
	ON 
		ll.user_id = lu.user_id
)

最新ユーザーだけでログイン履歴を取得します。

ログイン日時とインストール日時から「月」の情報を抽出し、集計軸にします。

イメージ：

ステップ3：月別でユニークユーザー数を集計

インストール月 × ログイン月でグループ化します。

そして、COUNT(DISTINCT user_id)で重複を排除したユニークユーザー数をカウントします。

SQL:

SELECT
	install_month,
	login_month,
	COUNT(DISTINCT user_id) AS unique_users
FROM 
	login_with_users
GROUP BY 
	install_month, 
	login_month
ORDER BY 
	install_month, 
	login_month;