تقرير لوحة القيادة - DAU/MAU

البيانات والتقارير
, ,
1

هذا إصدار SQL من تقرير لوحة المعلومات لـ DAU/MAU.

يقدم هذا التقرير تحليلاً مفصلاً لنسبة المستخدمين النشطين يوميًا (DAU) إلى المستخدمين النشطين شهريًا (MAU) خلال نطاق تاريخي محدد. نسبة DAU/MAU هي مؤشر أداء رئيسي (KPI) يقيس تفاعل المستخدم من خلال مقارنة عدد المستخدمين النشطين الفريدين يوميًا بعدد المستخدمين النشطين الفريدين على مدى فترة 30 يومًا.

-- [params]
-- date :start_date = 2023-01-01
-- date :end_date = 2024-01-01

WITH date_series AS (
  SELECT generate_series(:start_date::date, :end_date::date, '1 day'::interval) AS date
),

DAU AS (
  SELECT date(date) AS day, COUNT(DISTINCT user_id) AS dau_count
  FROM user_visits
  JOIN date_series ON date(user_visits.visited_at) = date_series.date
  GROUP BY date
),
MAU AS (
  SELECT date_series.date, COUNT(DISTINCT user_visits.user_id) AS mau_count
  FROM user_visits
  JOIN date_series ON date(user_visits.visited_at) >= date_series.date - INTERVAL '30 days'
  AND date(user_visits.visited_at) <= date_series.date
  GROUP BY date_series.date
)
SELECT
  DAU.day,
  (DAU.dau_count::FLOAT / NULLIF(MAU.mau_count, 0)) * 100 AS "DAU/MAU",
  DAU.dau_count,
  MAU.mau_count
FROM DAU
JOIN MAU ON DAU.day = MAU.date
ORDER BY DAU.day

شرح استعلام SQL

يستخدم استعلام SQL تعبيرات الجدول المشتركة (CTEs) لإنشاء مجموعتي بيانات: واحدة لـ DAU والأخرى لـ MAU. ثم يجمع هاتين المجموعتين من البيانات لحساب نسبة DAU/MAU لكل يوم ضمن النطاق الزمني المحدد.

المعلمات

يقبل الاستعلام معلمتين، :start_date و :end_date، مما يسمح للمستخدم بتحديد النطاق الزمني للتقرير. تقبل كلتا معلمتي التاريخ تنسيق التاريخ YYYY-MM-DD.

تعبيرات الجدول المشتركة (CTEs)

  • date_series: يقوم تعبير date_series بإنشاء سلسلة من التواريخ من :start_date إلى :end_date، بفاصل يوم واحد بين كل تاريخ. تُستخدم هذه السلسلة لضمان تضمين التقرير لكل يوم ضمن النطاق المحدد، حتى لو لم يكن هناك نشاط للمستخدم في بعض الأيام.
  • DAU: يحسب تعبير DAU عدد المستخدمين الفريدين الذين زاروا الموقع في كل يوم (dau_count). يتم ذلك عن طريق ربط جدول user_visits بتعبير date_series بشرط أن يتطابق تاريخ الزيارة مع التاريخ الموجود في السلسلة. يتم تجميع النتيجة حسب التاريخ للحصول على عدد المستخدمين الفريدين لكل يوم.
  • MAU: يحسب تعبير MAU عدد المستخدمين الفريدين الذين زاروا الموقع خلال الـ 30 يومًا السابقة لكل تاريخ في date_series. يتم ذلك عن طريق ربط جدول user_visits بتعبير date_series، حيث يكون تاريخ الزيارة بين 30 يومًا قبل التاريخ الموجود في السلسلة وحتى ذلك التاريخ، بما في ذلك. يتم تجميع النتيجة حسب التاريخ للحصول على عدد المستخدمين الفريدين لكل فترة 30 يومًا تسبق كل تاريخ.

التحديد النهائي

يحسب الجزء الأخير من الاستعلام نسبة DAU/MAU لكل يوم عن طريق قسمة عدد DAU على عدد MAU وضرب الناتج في 100 للحصول على نسبة مئوية. كما يحدد عدد DAU و MAU كمرجع. تُستخدم الدالة NULLIF لتجنب القسمة على صفر، والتي قد تحدث إذا لم يكن هناك MAU ليوم معين. يتم ترتيب النتيجة حسب اليوم لتوفير عرض زمني لتفاعل المستخدم.

نتائج مثال

day DAU/MAU dau_count mau_count
2023-11-19 15.31914893617021 360 2350
2023-11-20 23.92223161453931 566 2366
2023-11-21 23.55648535564853 563 2390
2023-11-22 23.09929372663066 556 2407
2023-11-23 19.394944053046 468 2413
… … … …
إعجابَين (2)
2

إضافة نسخة معدلة من هذا التقرير هنا تُظهر إحصائيات MAU (المستخدمون النشطون شهريًا) فقط في حال كان ذلك مفيدًا لأي شخص:

MAU (المستخدمون النشطون شهريًا)

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01

WITH date_series AS (
  SELECT generate_series(:start_date::date, :end_date::date, '1 month'::interval) AS month_start
),

MAU AS (
  SELECT
    date_trunc('month', date_series.month_start) AS month,
    COUNT(DISTINCT user_visits.user_id) AS mau_count
  FROM user_visits
  JOIN date_series
    ON date(user_visits.visited_at) >= date_series.month_start - INTERVAL '1 month'
   AND date(user_visits.visited_at) < date_series.month_start + INTERVAL '1 month'
  GROUP BY date_trunc('month', date_series.month_start)
)
SELECT
  month::date AS "الشهر",
  mau_count AS "المستخدمون النشطون شهريًا"
FROM MAU
WHERE month <= CURRENT_DATE
ORDER BY month

نتائج مثال

الشهر المستخدمون النشطون شهريًا
2024-07-01 2
2024-08-01 3
… …
3 إعجابات
3

شكراً، @SaraDev هذا مفيد لنا. بما أننا مجتمع صغير والموظفون أكثر نشاطًا بكثير من المستخدمين العاديين، فقد قمت أنا (أو بالأحرى أنا و ChatGPT) بتعديل الاستعلام ليكون قادرًا على تشغيل المستخدمين العاديين والموظفين بشكل منفصل أو مدمج. وأحب أن أرى ذلك مرسومًا بيانيًا، لذلك قمت بتعديله لتمكين ذلك.
لقد رأيت مؤخرًا كيفية تنزيل مخطط قاعدة البيانات في هذه النصيحة من @tyler.lamparter، وأقوم بتحميل ذلك إلى ChatGPT في بداية هذه الجلسات؛ مما يسهل على شات جي بي تي الفصل بين staff، على سبيل المثال.

-- [params]
-- int :months_ago = 3
-- int :include_staff = 1


WITH staff_users AS (
  SELECT user_id FROM group_users WHERE group_id = 3
),
daily_users AS (
  SELECT
    date_trunc('day', visited_at)::DATE AS day,
    COUNT(DISTINCT user_id) AS dau
  FROM user_visits
  WHERE visited_at >= CURRENT_DATE - INTERVAL ':months_ago months'
  AND (
    (:include_staff = 1) -- All users
    OR (:include_staff = 0 AND user_id NOT IN (SELECT user_id FROM staff_users)) -- Non-staff only
    OR (:include_staff = 2 AND user_id IN (SELECT user_id FROM staff_users)) -- Staff only
  )
  GROUP BY day
),
monthly_users AS (
  SELECT
    date_trunc('month', visited_at)::DATE AS month,
    COUNT(DISTINCT user_id) AS mau
  FROM user_visits
  WHERE visited_at >= CURRENT_DATE - INTERVAL ':months_ago months'
  AND (
    (:include_staff = 1) -- All users
    OR (:include_staff = 0 AND user_id NOT IN (SELECT user_id FROM staff_users)) -- Non-staff only
    OR (:include_staff = 2 AND user_id IN (SELECT user_id FROM staff_users)) -- Staff only
  )
  GROUP BY month
)
SELECT
  d.day::DATE AS date,  -- ✅ X-axis for graphing
  ROUND((d.dau::numeric / NULLIF(m.mau, 0)::numeric) * 100, 1)::FLOAT AS dau_mau_ratio  -- ✅ Y-axis for graphing
FROM daily_users d
JOIN monthly_users m ON date_trunc('month', d.day) = m.month
ORDER BY date
-- 0 = Non-Staff Only, 1 = All Users, 2 = Staff Only
إعجاب واحد (1)