ניתוח קבוצות (Cohort Analysis)

דוגמאות לניתוח קבוצות ב-SQL

דוגמה 1: יצירת קבוצות לפי תאריך הרשמה

SELECT
    user_id,
    signup_date,
    DATE_TRUNC('month', signup_date) AS cohort_month
FROM
    users;
    

בשאילתה זו, אנו יוצרים עמודה חדשה cohort_month המייצגת את החודש שבו המשתמש נרשם. פעולה זו מאפשרת לנו לחלק את המשתמשים לקבוצות לפי חודש ההרשמה שלהם.

דוגמה 2: מעקב אחרי פעילות משתמשים

SELECT
    cohort_month,
    DATE_TRUNC('month', last_active_date) AS active_month,
    COUNT(user_id) AS active_users
FROM
    (SELECT
        user_id,
        DATE_TRUNC('month', signup_date) AS cohort_month,
        last_active_date
    FROM
        users) AS cohorts
GROUP BY
    cohort_month,
    active_month
ORDER BY
    cohort_month,
    active_month;
    

בשאילתה זו, אנו עוקבים אחרי כל משתמש ובודקים באיזה חודש הוא היה פעיל מאז ההרשמה שלו. הספירה בקבוצות אלה מאפשרת לנו לראות את הירידה בפעילות לאורך הזמן עבור כל קבוצת משתמשים.

דוגמה 3: חישוב אחוז שימור משתמשים

WITH user_activity AS (
    SELECT
        user_id,
        DATE_TRUNC('month', signup_date) AS cohort_month,
        DATE_TRUNC('month', last_active_date) AS active_month
    FROM
        users
),
cohort_counts AS (
    SELECT
        cohort_month,
        COUNT(DISTINCT user_id) AS cohort_size
    FROM
        user_activity
    GROUP BY
        cohort_month
),
retention_counts AS (
    SELECT
        cohort_month,
        active_month,
        COUNT(DISTINCT user_id) AS active_users
    FROM
        user_activity
    GROUP BY
        cohort_month,
        active_month
)
SELECT
    retention_counts.cohort_month,
    retention_counts.active_month,
    retention_counts.active_users,
    cohort_counts.cohort_size,
    (retention_counts.active_users::float / cohort_counts.cohort_size) AS retention_rate
FROM
    retention_counts
JOIN
    cohort_counts ON retention_counts.cohort_month = cohort_counts.cohort_month
ORDER BY
    retention_counts.cohort_month,
    retention_counts.active_month;
    

שאילתה זו מחלקת את המשתמשים לקבוצות לפי חודש ההרשמה שלהם, ולאחר מכן מחשבת את אחוז השימור עבור כל חודש פעילות.

סיכום

ניתוח קבוצות הוא כלי חזק עבור דאטה אנליסטים, המאפשר להבין בצורה עמוקה את ההתנהגות של משתמשים לאורך זמן. באמצעות SQL, ניתן ליישם ניתוח זה בצורה יעילה ומדויקת, ולהפיק תובנות שיסייעו לשיפור החלטות עסקיות ולשיפור חוויית המשתמש.

קורס הדאטה אנליסט של מכללת SmartBrainData מציע הכשרה מקיפה ומעשית בכלים ובשיטות אלו ובשיטות נוספות, ומבטיח להעניק לך את הכלים הנחוצים להצלחה בתחום. הצטרף עוד היום לקורס המובחר שלנו ותחל את המסע שלך בעולם הנתונים. לפרטים נוספים והרשמה, בקרו באתר smartbraindata.org.il.