📅💄 週次アクティブユーザー分析✨

こんばんは、Yuinaです🙏

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

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

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

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

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

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

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

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

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

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

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

🧚‍♀️ 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) でユニークなアクティブユーザー数をカウント！

イメージ：

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

💄STEP1：まずはリセマラ除去！

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
)

💄 STEP2：ログインデータに週の情報を付けて、JOIN✨

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
)

💄STEP3：週×週でクロス集計して、ユニークユーザー数カウント✨

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

さらに、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;