هذا هو إصدار SQL لتقرير تحليل الفوج لنشاط المستخدم، للاستخدام ضمن المكون الإضافي Data Explorer.
يتطلب هذا التقرير تمكين المكون الإضافي Discourse Solved .
يتطلب هذا التقرير تمكين المكون الإضافي يقدم تقرير تحليل الفوج هذا رؤى حول تفاعل المستخدم من خلال تتبع نشاط المستخدمين الذين سجلوا منذ تاريخ بدء محدد. يركز بشكل خاص على المستخدمين الذين يستوفون أو يتجاوزون عتبات معينة للمشاركات التي تم إنشاؤها والحلول المقدمة كل شهر بعد تسجيلهم.
هذا التقرير قيم للمسؤولين الذين يتطلعون إلى فهم فعالية مجتمعهم في الاحتفاظ بالمستخدمين الجدد وإشراكهم بمرور الوقت، خاصة فيما يتعلق بالنشر وتقديم الحلول للمواضيع. يمكن أن يكون هذا التقرير مفيدًا أيضًا لتقييم صحة المجتمع وتحديد فعالية استراتيجيات نمو المجتمع.
المستخدمون النشطون شهريًا بعد التسجيل مع الحد الأدنى من معلمات المشاركات + الحلول
--[params]
-- date :start_date = 2023-01-01
-- int :min_posts_per_month = 1
-- int :min_solutions_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
),
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
),
solutions_counts AS (
SELECT
p.user_id,
COUNT(p.user_id) as solutions_count,
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 discourse_solved_solved_topics dsst
INNER JOIN posts p ON p.id = dsst.answer_post_id
JOIN topics t ON t.id = p.topic_id
JOIN users u ON p.user_id = u.id
WHERE p.created_at >= u.created_at
GROUP BY months_after_registration, cohort, p.user_id
HAVING COUNT(p.user_id) >= :min_solutions_per_month
),
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
sc.cohort,
sc.months_after_registration,
COUNT(DISTINCT ac.user_id) AS active_users
FROM solutions_counts sc
FULL JOIN activity_counts ac ON sc.user_id = ac.user_id
AND sc.months_after_registration = ac.months_after_registration
AND sc.cohort = ac.cohort
GROUP BY sc.cohort, sc.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 "Joined In", -- Include the year in the Joined In column
users_signed_up AS "Users Signed Up",
MAX(CASE WHEN months_after_registration = 0 THEN active_users END) AS "Month 1",
MAX(CASE WHEN months_after_registration = 1 THEN active_users END) AS "Month 2",
MAX(CASE WHEN months_after_registration = 2 THEN active_users END) AS "Month 3",
MAX(CASE WHEN months_after_registration = 3 THEN active_users END) AS "Month 4",
MAX(CASE WHEN months_after_registration = 4 THEN active_users END) AS "Month 5",
MAX(CASE WHEN months_after_registration = 5 THEN active_users END) AS "Month 6",
MAX(CASE WHEN months_after_registration = 6 THEN active_users END) AS "Month 7",
MAX(CASE WHEN months_after_registration = 7 THEN active_users END) AS "Month 8",
MAX(CASE WHEN months_after_registration = 8 THEN active_users END) AS "Month 9",
MAX(CASE WHEN months_after_registration = 9 THEN active_users END) AS "Month 10",
MAX(CASE WHEN months_after_registration = 10 THEN active_users END) AS "Month 11",
MAX(CASE WHEN months_after_registration = 11 THEN active_users END) AS "Month 12"
FROM final_counts
GROUP BY cohort, users_signed_up
ORDER BY cohort
شرح استعلام SQL
المعلمات
يستخدم هذا التقرير ثلاث معلمات:
start_date: التاريخ الذي سيتم البدء منه في تتبع تسجيلات المستخدمين الجدد.min_posts_per_month: الحد الأدنى لعدد المشاركات التي يجب على المستخدم إنشاؤها في شهر واحد ليتم اعتباره نشطًا.min_solutions_per_month: الحد الأدنى لعدد الحلول (الإجابات المقبولة) التي يجب على المستخدم تقديمها في شهر واحد ليتم اعتباره نشطًا.
يجب على المستخدمين استيفاء كل من متطلبات min_posts_per_month و min_solutions_per_month ليتم اعتبارهم نشطين لهذا الشهر.
تعبيرات الجدول المشتركة (CTEs)
يستخدم استعلام SQL المقدم العديد من تعبيرات الجدول المشتركة (CTEs) لتقسيم عملية حساب المستخدمين النشطين شهريًا بعد التسجيل، بناءً على معايير الحد الأدنى للمشاركات والحلول. إليك شرح لكل CTE:
user_cohorts
يحدد هذا CTE مجموعات المستخدمين بناءً على شهر تسجيلهم. لكل مستخدم، يقوم بحساب شهر تسجيله (cohort) ويحسب العدد الإجمالي للمستخدمين الذين سجلوا في نفس الشهر. يساعد هذا في فهم الحجم الأولي لكل مجموعة.
posts_activity
يتتبع هذا CTE نشاط المستخدمين من حيث المشاركات التي تم إجراؤها بعد تسجيلهم. لكل مشاركة، يقوم بحساب عدد الأشهر التي مرت منذ تاريخ تسجيل المستخدم (months_after_registration) ويقوم بتجميعها حسب مجموعة تسجيل المستخدم. يُستخدم هذا لتتبع مدى نشاط المستخدمين من حيث نشر المحتوى بمرور الوقت.
solutions_counts
يركز هذا CTE على حساب الحلول (الإجابات المقبولة) التي قدمها المستخدمون. يقوم بتصفية المشاركات التي تم تمييزها كحلول ويحسبها لكل مستخدم، مع التأكد من أن المشاركات التي تم إجراؤها بعد تسجيل المستخدم فقط هي التي تم أخذها في الاعتبار. كما يقوم بحساب عدد الأشهر التي مرت منذ تسجيل المستخدم لكل حل. يتم تضمين المستخدمين في هذا العدد فقط إذا استوفوا أو تجاوزوا الحد الأدنى المحدد لعدد الحلول شهريًا.
activity_counts
يقوم هذا CTE بتجميع عدد المشاركات التي أجراها كل مستخدم شهريًا بعد التسجيل. يقوم بتجميع المستخدمين حسب مجموعة تسجيلهم وعدد الأشهر منذ التسجيل، ثم يحسب عدد المشاركات التي تم إجراؤها. يتم تضمين المستخدمين الذين يستوفون أو يتجاوزون الحد الأدنى المحدد لعدد المشاركات شهريًا فقط في هذا العدد.
active_users
يجمع هذا CTE البيانات من solutions_counts و activity_counts لتحديد المستخدمين النشطين - أولئك الذين يستوفون معايير المشاركات والحلول. يقوم بحساب المستخدمين المميزين النشطين بناءً على المعايير المحددة للمشاركات والحلول، مجمعة حسب مجموعة تسجيلهم وعدد الأشهر منذ التسجيل.
cohorts_series
ينشئ هذا CTE سلسلة من الأرقام من 0 إلى 11، تمثل عدد الأشهر بعد التسجيل. يُستخدم هذا لضمان أن التقرير النهائي يتضمن بيانات لكل شهر حتى 12 شهرًا، حتى لو لم يكن هناك مستخدمون نشطون في بعض الأشهر.
cohorts
يقوم هذا CTE بتجميع البيانات من user_cohorts للحصول على العدد الإجمالي للمستخدمين الذين سجلوا في كل مجموعة. يضمن أن التقرير النهائي يتضمن العدد الإجمالي للمستخدمين المسجلين لكل مجموعة.
cross_join
يقوم هذا CTE بإجراء ربط متقاطع بين CTE cohorts و CTE cohorts_series. يضمن هذا تمثيل كل مجموعة لكل شهر بعد التسجيل، مما يسهل حساب المستخدمين النشطين لكل شهر في الخطوة النهائية.
final_counts
يجمع هذا CTE جميع CTEs السابقة لحساب الأعداد النهائية للمستخدمين النشطين لكل شهر بعد التسجيل، لكل مجموعة. يستخدم ربطًا أيسر لمطابقة المستخدمين النشطين من CTE active_users مع المجموعات والأشهر التي تم إنشاؤها في CTE cross_join. يضمن أن كل زوج من مجموعة-شهر لديه عدد من المستخدمين النشطين، مع افتراض القيمة 0 إذا لم يكن هناك مستخدمون نشطون لهذا الزوج.
SELECT النهائي
تقوم عبارة SELECT النهائية في الاستعلام بتجميع المستخدمين في مجموعات بناءً على شهر وسنة تسجيلهم، ثم تحسب عدد المستخدمين النشطين لكل شهر حتى عام واحد بعد التسجيل. يتم ذلك من خلال مزيج من التحويلات والتجميعات الشرطية، التي تقوم بتحويل البيانات إلى تنسيق حيث يمثل كل صف مجموعة، ويمثل كل عمود عدد المستخدمين النشطين لكل شهر بعد التسجيل، من “الشهر 1” إلى “الشهر 12”.
النتائج
يقدم التقرير جدولًا بالأعمدة التالية:
- Joined In: شهر وسنة المجموعة (وقت تسجيل المستخدمين).
- Users Signed Up: العدد الإجمالي للمستخدمين الذين سجلوا في تلك المجموعة.
- Month 1 to Month 12: عدد المستخدمين النشطين لكل شهر بعد التسجيل، حتى 12 شهرًا.
نتائج مثال
| Joined In | Users Signed Up | Month 1 | Month 2 | Month 3 | Month 4 | Month 5 | Month 6 | Month 7 | Month 8 | Month 9 | Month 10 | Month 11 | Month 12 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Jan 2023 | 50 | 40 | 8 | 4 | 3 | 3 | 3 | 4 | 3 | 2 | 1 | 1 | 4 |
| Feb 2023 | 63 | 40 | 7 | 5 | 3 | 2 | 2 | 7 | 2 | 2 | 2 | 1 | 1 |
| … | … | … | … | … | … | … | … | … | … | … | … | … | … |
ستخرج النتائج الكاملة من التقرير بيانات لمدة عام واحد بعد start_date.