🧙♂️ Chapter 1: The Monthly Revenue Enchantment
Our first spell transforms raw payment data into monthly revenue treasures, grouping our heroes by their payment months, user identities, and game realms.
הכישוף הראשון שלנו הופך נתוני תשלום גולמיים לאוצרות הכנסה חודשיים, מקבץ את הגיבורים שלנו לפי חודשי התשלום, זהויות המשתמשים וממלכות המשחק שלהם.
WITH monthly_revenue AS (
SELECT
DATE(DATE_TRUNC('month', payment_date)) AS payment_month,
user_id,
game_name,
SUM(revenue_amount_usd) AS total_revenue
FROM project.games_payments gp
GROUP BY 1, 2, 3
),
🔮 Chapter 2: The Time-Travel Analytics Spell
With mystical LAG and LEAD functions, we peer into the past and future, tracking each player's journey through time to understand their payment patterns.
עם פונקציות LAG ו-LEAD מיסטיות, אנחנו מציצים לעבר ולעתיד, עוקבים אחר המסע של כל שחקן בזמן כדי להבין את דפוסי התשלום שלהם.
revenue_lag_lead_months AS (
SELECT
*,
DATE(payment_month - INTERVAL '1 month') AS previous_calendar_month,
DATE(payment_month + INTERVAL '1 month') AS next_calendar_month,
LAG(total_revenue) OVER (PARTITION BY user_id ORDER BY payment_month) AS previous_paid_month_revenue,
LAG(payment_month) OVER (PARTITION BY user_id ORDER BY payment_month) AS previous_paid_month,
LEAD(payment_month) OVER (PARTITION BY user_id ORDER BY payment_month) AS next_paid_month
FROM monthly_revenue
),
⚡️ Chapter 3: The Revenue Metrics Mastery
The final transformation spell categorizes each payment into the sacred revenue types: New MRR (new heroes joining), Churned Revenue (heroes departing), Expansion (heroes growing stronger), and Contraction (heroes reducing their power).
כישוף הטרנספורמציה הסופי מקטלג כל תשלום לסוגי ההכנסה הקדושים: MRR חדש (גיבורים חדשים מצטרפים), הכנסה נטושה (גיבורים עוזבים), הרחבה (גיבורים מתחזקים), וכיווץ (גיבורים מפחיתים את כוחם).
revenue_metrics AS (
SELECT
payment_month, user_id, game_name, total_revenue,
CASE
WHEN previous_paid_month IS NULL
THEN total_revenue
END AS new_mrr,
CASE
WHEN next_paid_month IS NULL
OR next_paid_month != next_calendar_month
THEN total_revenue
END AS churned_revenue,
CASE
WHEN previous_paid_month = previous_calendar_month
AND total_revenue > previous_paid_month_revenue
THEN total_revenue - previous_paid_month_revenue
END AS expansion_revenue,
CASE
WHEN previous_paid_month = previous_calendar_month
AND total_revenue < previous_paid_month_revenue
THEN total_revenue - previous_paid_month_revenue
END AS contraction_revenue
FROM revenue_lag_lead_months
),
🏆 Chapter 4: The Final Enchantment - Hero Demographics Integration
🎯 The Ultimate Spell - SELECT rm.*: This magical wildcard (*) summons ALL calculated fields from our revenue_metrics CTE including payment_month, user_id, game_name, total_revenue, new_mrr, churned_revenue, expansion_revenue, and contraction_revenue. It's like opening the ultimate treasure chest containing all our hard-earned revenue insights!
🌍 The Language Oracle - gpu.language: This enchanted field reveals the sacred tongue of each hero in our kingdom! By understanding which languages our warriors speak, we can craft personalized experiences, localized content, and region-specific marketing campaigns.
🎯 הכישוף האולטימטיבי - SELECT rm.*: כרטיס הפלא הקסום הזה (*) מזמן את כל השדות המחושבים מה-CTE של revenue_metrics כולל payment_month, user_id, game_name, total_revenue, new_mrr, churned_revenue, expansion_revenue, ו-contraction_revenue. זה כמו לפתוח את תיבת האוצר האולטימטיבית המכילה את כל תובנות ההכנסה שעמלנו עליהן!
🌍 אורקל השפה - gpu.language: השדה הקסום הזה חושף את הלשון הקדושה של כל גיבור בממלכה שלנו! על ידי הבנה אילו שפות הלוחמים שלנו מדברים, אנחנו יכולים ליצור חוויות מותאמות אישית, תוכן מקומי וקמפיינים שיווקיים ספציפיים לאזור.
SELECT
rm.*,
gpu.language,
gpu.has_older_device_model,
gpu.age
FROM revenue_metrics rm
LEFT JOIN project.games_players_users gpu
USING (user_id);