📅💄【WITH句、 リセマラ除外】週次アクティブユーザー分析✨

こんばんは、Yuinaです🙏

今日は、「スキンケアアプリ、今どれくらいの人がちゃんと使ってるんだろう〜？」

みたいな感じで、ユーザーさんの動きをもっとリアルに知りたくて、

週ごとのアクティブユーザー分析（Weekly MAU）をやってみました〜💅💻

リセマラ（＝複数アカ作成）を除いて、ほんとに大事なユーザーだけをカウントしてます!

💡 リセマラってなに？MAUってどういう意味？

「言葉の意味から知りたい〜！」という方は、先にこちらの記事をチェックしてみてください👇

▶ 前提知識として、リセマラやMAUについてはこちらの記事をご覧ください📝💖

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

こんばんは、Yuinaです🧚今日はスキンケアアプリのデータを使って、「リセマラ（複数アカウント作成）の影響を除外した正確なMAU分析」について解説します✨💪MAU（Monthly Active Users）は、1ヶ月の間にサービスを利用した...

jagajagablog.com

2025.05.21

それでは、よろしくお願いいたします！

---

💾 今回使ったデータベースの中身はこちらです↓

🧚‍♀️ 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

📝 補足：

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

今日のプログラム👩‍💻❤️‍🔥

今回作成したプログラムは以下のとおりです。

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,
    	DATE_TRUNC('week', lu.install_time)::date AS install_week,
    	DATE_TRUNC('week', ll.login_time)::date AS login_week
  	FROM 
	  	beauty.login_log ll
	JOIN 
		latest_users lu 
	ON 
		ll.user_id = lu.user_id
)

SELECT
  	install_week,
  	login_week,
  	COUNT(DISTINCT user_id) AS unique_users
FROM 
	login_with_users
GROUP BY 
	install_week, 
	login_week
ORDER BY 
	install_week, 
	login_week;

解説✏️☁️

💄 週次MAU分析の手順はこんな感じです。

1. 最新ユーザーを抽出（リセマラ除外✨）

• guid ごとにいちばん新しい user_id を抽出
• リセマラの影響をカットして、分析の精度UP！

2.リセマラ除外済みログイン履歴を作成📕

• login_log に最新ユーザー情報をJOIN
• 本当に使ってる人だけのログイン履歴ができあがる💫

3.インストール週 × ログイン週で集計📊

• TO_CHAR(..., 'IYYY-IW') を使って週単位の「期間ラベル」に変換
• COUNT(DISTINCT user_id) でユニークなアクティブユーザー数をカウント！

イメージ：

---

ここからは、分析の手順について３ステップで見ていきましょう。

guidごとに最新user_idだけ残しましょう😌

SQL:

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

DATE_TRUNC('week', 日付) で、その日が属する週の月曜日が取れます。

install_week / login_week は DATE_TRUNC('week', 日付) で週の月曜日に切り捨てます🗓
（例：2025-01-10 → 2025-01-06週）

SQL:

, login_with_users AS (
	SELECT
    	lu.user_id,
    	DATE_TRUNC('week', lu.install_time)::date AS install_week,
    	DATE_TRUNC('week', ll.login_time)::date AS login_week
  	FROM 
		beauty.login_log ll
  	JOIN 
  		latest_users lu
	ON 
		ll.user_id = lu.user_id
)

インストール週 × ログイン週でグループ化して、いつインストールした人が、どの週にログインしたか？」をクロスで見れるようにします。

さらに、COUNT(DISTINCT user_id) で、アクティブな人だけしっかり数えます✨

SQL:

SELECT
  install_week,
  login_week,
  COUNT(DISTINCT user_id) AS unique_users
FROM login_with_users
GROUP BY install_week, login_week
ORDER BY install_week, login_week;

結果、同じ週にインストール＆ログインした人もいれば、

インストールしてから1〜2週間後にログインしてる人もいることがわかりました💫

まとめ☁️✨

今日は、スキンケアアプリを使ってくれてるユーザーさんの動きを、もっとリアルに知りたくて
「週ごとのアクティブユーザー分析（MAU）」をしてみました！

MAUを行うことで、「どの週にインストールした人が、どの週まで使い続けてる？」って見えるようになります✨

つまり、「1回使って終わり」なのか、「ちゃんと続けて使ってくれてるのか？」ってところを週ごとに見ていくと…

✨ どの週にインストールされたユーザーが、どの週まで使ってくれているのか？
✨ アプリの“推し続け力（Retention）”が週単位で見えてきます！

この視点があると、リリース後の反応とか、キャンペーンの効果もグラフで見えるようになるから超便利ですね〜！

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