تقرير تحليل الأتراب - نشاط المستخدمين الشهري

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

هذا هو إصدار SQL لتقرير تحليل الفوج (Cohort Analysis Report) لنشاط المستخدم، للاستخدام داخل المكون الإضافي لاستكشاف البيانات (Data Explorer plugin).
يهدف تقرير تحليل الفوج إلى تزويد المسؤولين برؤى حول تفاعل المستخدمين بمرور الوقت. من خلال تحليل نشاط المستخدمين المجمعة حسب شهر تسجيلهم (الفوج)، يتتبع هذا التقرير عدد المستخدمين النشطين كل شهر بعد التسجيل الذين يستوفون معايير الحد الأدنى لنشاط النشر.
يمكن أن يكون هذا التقرير موردًا قيمًا لفهم الاحتفاظ بالمستخدمين، واتجاهات التفاعل، وتقييم صحة المجتمع، وتحديد فعالية استراتيجيات نمو المجتمع.

تقرير تحليل الفوج - المستخدمون النشطون شهريًا

--[params]
-- date :start_date = 2023-01-01
-- int :min_posts_per_month = 1

WITH user_cohorts AS (
    SELECT
        id AS user_id,
        DATE_TRUNC('month', created_at) AS cohort,
        COUNT(id) OVER (PARTITION BY DATE_TRUNC('month', created_at)) AS users_signed_up
    FROM users
    WHERE created_at >= :start_date -- استخدم معلمة start_date لتصفية المستخدمين
),
posts_activity AS (
    SELECT
        p.user_id,
        EXTRACT(YEAR FROM AGE(p.created_at, u.created_at)) * 12 + EXTRACT(MONTH FROM AGE(p.created_at, u.created_at)) AS months_after_registration,
        DATE_TRUNC('month', u.created_at) AS cohort
    FROM posts p
    JOIN users u ON p.user_id = u.id
    WHERE p.created_at >= u.created_at
),
activity_counts AS (
    SELECT
        cohort,
        months_after_registration,
        COUNT(user_id) AS posts_count,
        user_id
    FROM posts_activity
    GROUP BY cohort, months_after_registration, user_id
    HAVING COUNT(user_id) >= :min_posts_per_month -- تصفية المستخدمين حسب الحد الأدنى للمشاركات شهريًا
),
active_users AS (
    SELECT
        cohort,
        months_after_registration,
        COUNT(DISTINCT user_id) AS active_users
    FROM activity_counts
    GROUP BY cohort, months_after_registration
),
cohorts_series AS (
    SELECT generate_series AS months_after_registration
    FROM generate_series(0, 11)
),
cohorts AS (
    SELECT
        cohort,
        MAX(users_signed_up) AS users_signed_up -- تجميع للحصول على إجمالي المستخدمين المسجلين لكل فوج
    FROM user_cohorts
    GROUP BY cohort
),
cross_join AS (
    SELECT
        c.cohort,
        c.users_signed_up,
        cs.months_after_registration
    FROM cohorts c
    CROSS JOIN cohorts_series cs
),
final_counts AS (
    SELECT
        cj.cohort,
        cj.users_signed_up,
        cj.months_after_registration,
        COALESCE(au.active_users, 0) AS active_users
    FROM cross_join cj
    LEFT JOIN active_users au ON au.cohort = cj.cohort AND au.months_after_registration = cj.months_after_registration
)
SELECT
    TO_CHAR(cohort, 'Mon YYYY') AS "تاريخ الانضمام", -- تضمين السنة في عمود تاريخ الانضمام
    users_signed_up AS "المستخدمون المسجلون",
    MAX(CASE WHEN months_after_registration = 0 THEN active_users END) AS "الشهر 1",
    MAX(CASE WHEN months_after_registration = 1 THEN active_users END) AS "الشهر 2",
    MAX(CASE WHEN months_after_registration = 2 THEN active_users END) AS "الشهر 3",
    MAX(CASE WHEN months_after_registration = 3 THEN active_users END) AS "الشهر 4",
    MAX(CASE WHEN months_after_registration = 4 THEN active_users END) AS "الشهر 5",
    MAX(CASE WHEN months_after_registration = 5 THEN active_users END) AS "الشهر 6",
    MAX(CASE WHEN months_after_registration = 6 THEN active_users END) AS "الشهر 7",
    MAX(CASE WHEN months_after_registration = 7 THEN active_users END) AS "الشهر 8",
    MAX(CASE WHEN months_after_registration = 8 THEN active_users END) AS "الشهر 9",
    MAX(CASE WHEN months_after_registration = 9 THEN active_users END) AS "الشهر 10",
    MAX(CASE WHEN months_after_registration = 10 THEN active_users END) AS "الشهر 11",
    MAX(CASE WHEN months_after_registration = 11 THEN active_users END) AS "الشهر 12"
FROM final_counts
GROUP BY cohort, users_signed_up
ORDER BY cohort

شرح استعلام SQL

يعمل التقرير عن طريق تقسيم المستخدمين إلى أفواج بناءً على الشهر الذي انضموا فيه. ثم يتتبع هذه الأفواج لمعرفة عدد المستخدمين الذين يظلون نشطين في الأشهر اللاحقة، بناءً على عدد أدنى محدد من المشاركات شهريًا.

المعلمات

يحتوي هذا التقرير على معلمتين:

  • start_date: تاريخ البدء الذي يتم من خلاله اعتبار المستخدمين لتحليل الفوج. يتم تضمين المستخدمين الذين انضموا بعد هذا التاريخ في التقرير.
  • min_posts_per_month: الحد الأدنى لعدد المشاركات التي يجب على المستخدم القيام بها في شهر واحد ليتم اعتباره نشطًا لذلك الشهر.

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

يستخدم تقرير تحليل الفوج العديد من تعبيرات الجدول المشتركة (CTEs) لتنظيم ومعالجة البيانات للتحليل. يخدم كل CTE غرضًا محددًا في الاستعلام العام، ويبني على الاستعلامات السابقة لإنتاج التقرير النهائي في النهاية. إليك تفصيل لكيفية عمل كل CTE:

1. user_cohorts
يحدد هذا CTE الأفواج بناءً على الشهر الذي انضم فيه المستخدمون. لكل مستخدم، يحسب الفوج الذي ينتمي إليه عن طريق اقتطاع طابع created_at الزمني إلى الشهر. كما يحسب عدد المستخدمين الذين سجلوا في كل فوج.

  • العمليات الرئيسية:
    • DATE_TRUNC('month', created_at) AS cohort: يقتطع الطابع الزمني created_at إلى دقة الشهر، مما يجمع المستخدمين فعليًا حسب شهر تسجيلهم.
    • COUNT(id) OVER (PARTITION BY DATE_TRUNC('month', created_at)): يحسب عدد المستخدمين في كل فوج.

2. posts_activity
يتتبع هذا CTE نشاط النشر للمستخدمين بالنسبة لتاريخ تسجيلهم. يربط جداول posts و users لربط كل منشور بالمستخدم الذي قام به ويحسب عدد الأشهر التي مرت منذ تسجيل المستخدم وقت كل منشور.

  • العمليات الرئيسية:
    • EXTRACT(YEAR FROM AGE(p.created_at, u.created_at)) * 12 + EXTRACT(MONTH FROM AGE(p.created_at, u.created_at)): يحسب عدد الأشهر التي مرت منذ تسجيل المستخدم لكل منشور.
    • DATE_TRUNC('month', u.created_at) AS cohort: يحدد فوج المستخدم بناءً على شهر تسجيله.

3. activity_counts
يقوم هذا CTE بتجميع نشاط النشر من posts_activity لحساب عدد المشاركات التي قام بها كل مستخدم في كل شهر بعد التسجيل. يقوم بتصفية هذه الأعداد لتشمل فقط المستخدمين الذين يستوفون الحد الأدنى لنشاط النشر المحدد بواسطة معلمة min_posts_per_month.

  • العمليات الرئيسية:
    • GROUP BY cohort, months_after_registration, user_id: يجمع البيانات حسب الفوج، والأشهر بعد التسجيل، ومعرف المستخدم للتحضير لعد المشاركات.
    • HAVING COUNT(user_id) >= :min_posts_per_month: يقوم بتصفية البيانات المجمعة لتشمل فقط المستخدمين الذين قاموا بما لا يقل عن الحد الأدنى لعدد المشاركات في شهر واحد.

4. active_users
يقوم هذا CTE بتجميع البيانات من activity_counts بشكل أكبر لحساب عدد المستخدمين النشطين المميزين في كل فوج لكل شهر بعد التسجيل.

  • العمليات الرئيسية:
    • COUNT(DISTINCT user_id) AS active_users: يحسب عدد المستخدمين النشطين الفريدين في كل فوج لكل شهر بعد التسجيل.

5. cohorts_series
ينشئ هذا CTE سلسلة من الأعداد الصحيحة من 0 إلى 11، تمثل الأشهر بعد التسجيل. تُستخدم هذه السلسلة لضمان تضمين التقرير النهائي لجميع الأشهر حتى 12 لكل فوج، حتى لو لم تكن هناك بيانات نشاط لبعض الأشهر.

  • العمليات الرئيسية:
    • generate_series(0, 11): ينشئ سلسلة من الأعداد الصحيحة من 0 إلى 11.

6. cohorts
يقوم هذا CTE بتجميع البيانات من user_cohorts للحصول على العدد الإجمالي للمستخدمين المسجلين لكل فوج.

  • العمليات الرئيسية:
    • MAX(users_signed_up) AS users_signed_up: يجمع العدد الإجمالي للمستخدمين المسجلين لكل فوج.

7. cross_join
يقوم هذا CTE بإجراء ربط تقاطعي بين cohorts و cohorts_series لإنشاء شبكة لجميع التركيبات الممكنة للأفواج والأشهر بعد التسجيل. يضمن هذا أن التقرير النهائي يتضمن صفوفًا لكل شهر لكل فوج، مما يسهل حساب المستخدمين النشطين شهريًا.

8. final_counts
يجمع هذا CTE البيانات من cross_join و active_users لحساب العدد النهائي للمستخدمين النشطين لكل فوج لكل شهر بعد التسجيل. يستخدم ربطًا أيسرًا لضمان تضمين جميع تركيبات الأفواج والأشهر، حتى لو لم يكن هناك مستخدمون نشطون لبعضها.

  • العمليات الرئيسية:
    • COALESCE(au.active_users, 0) AS active_users: يضمن أن التقرير يعرض 0 مستخدم نشط للتركيبات التي لا تحتوي على أي نشاط، بدلاً من تركها فارغة.

ثم يقوم بيان SELECT النهائي خارج CTEs بتنسيق هذه البيانات وتقديمها، مع عرض عدد المستخدمين المسجلين وعدد المستخدمين النشطين لكل شهر بعد التسجيل لكل فوج.

النتائج

ينشئ التقرير جدولًا يحتوي على الأعمدة التالية:

  • تاريخ الانضمام: الشهر والسنة التي تم فيها إنشاء الفوج، مما يشير إلى وقت تسجيل هؤلاء المستخدمين.
  • المستخدمون المسجلون: العدد الإجمالي للمستخدمين الذين سجلوا في هذا الفوج.
  • الشهر 1 إلى الشهر 12: يمثل كل من هذه الأعمدة عدد المستخدمين النشطين للفوج في كل شهر لاحق بعد الانضمام، حتى 12 شهرًا. يُعرَّف المستخدم النشط بأنه شخص قام بما لا يقل عن الحد الأدنى لعدد المشاركات المحدد بواسطة معلمة min_posts_per_month.

نتائج مثال

تاريخ الانضمام المستخدمون المسجلون الشهر 1 الشهر 2 الشهر 3 الشهر 4 الشهر 5 الشهر 6 الشهر 7 الشهر 8 الشهر 9 الشهر 10 الشهر 11 الشهر 12
يناير 2023 120 40 8 4 3 3 3 4 3 2 1 1 4
فبراير 2023 119 40 7 5 3 2 2 7 2 2 2 1 1
… … … … … … … … … … … … … …

ستخرج النتائج الكاملة من التقرير عامًا واحدًا من البيانات بعد start_date.

3 إعجابات