Spotify Business Model & Metrics Questions

view: user_metrics {
  sql_table_name: analytics.user_metrics ;; 

  # Primary key
  dimension: user_id {
    primary_key: yes
    sql: ${TABLE}.user_id ;;
    type: string
  }

  # date dimension for monthly grouping
  dimension_group: event_date {
    sql: ${TABLE}.event_date ;;
    type: time
    timeframes: [raw, month]
  }

  measure: monthly_active_users {
    type: count_distinct
    sql: ${user_id} ;;
    drill_fields: [user_id]
  }

  # subscribers_count from subscriptions join (could also be derived here)
  measure: subscribers_count {
    type: count_distinct
    sql: ${subscriptions.user_id} ;;
  }

  # ARPU computed using normalized revenue (see model currency conversion)
  measure: arpu {
    type: number
    sql: CASE WHEN ${monthly_active_users} = 0 THEN 0
              ELSE ROUND(${ad_revenue.normalized_revenue} / NULLIF(${monthly_active_users},0),2) END ;;
    value_format_name: "usd"
  }
}

explore: user_metrics {
  join: subscriptions {
    from: subscriptions
    type: left_outer
    sql_on: ${user_metrics.user_id} = ${subscriptions.user_id} ;;
    relationship: many_to_one
  }

  join: ad_revenue {
    from: ad_revenue
    type: left_outer
    sql_on: ${user_metrics.user_id} = ${ad_revenue.user_id}
      AND DATE_TRUNC('month', ${user_metrics.event_date}) = DATE_TRUNC('month', ${ad_revenue.revenue_date}) ;;
    relationship: many_to_one
  }
}

WITH months AS (
  -- generate last 12 months (month_start, month_end)
  SELECT DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '11' MONTH AS month_start
  UNION ALL
  SELECT month_start + INTERVAL '1' MONTH
  FROM months
  WHERE month_start + INTERVAL '1' MONTH <= DATE_TRUNC('month', CURRENT_DATE)
),
active_counts AS (
  SELECT
    m.month_start,
    (m.month_start + INTERVAL '1' MONTH - INTERVAL '1' DAY) AS month_end,
    COUNT(DISTINCT s.user_id) FILTER (
      WHERE s.start_date <= m.month_start
        AND (s.end_date IS NULL OR s.end_date >= m.month_start)
    ) AS active_at_start,
    COUNT(DISTINCT s2.user_id) FILTER (
      WHERE s2.start_date <= (m.month_start + INTERVAL '1' MONTH - INTERVAL '1' DAY)
        AND (s2.end_date IS NULL OR s2.end_date >= (m.month_start + INTERVAL '1' MONTH - INTERVAL '1' DAY))
    ) AS active_at_end
  FROM months m
  LEFT JOIN subscriptions s ON TRUE
  LEFT JOIN subscriptions s2 ON TRUE
  GROUP BY m.month_start
)
SELECT
  month_start,
  month_end,
  active_at_start,
  active_at_end,
  CASE
    WHEN active_at_start = 0 THEN NULL
    ELSE ROUND(100.0 * (active_at_start - active_at_end) / active_at_start, 2)
  END AS churn_pct
FROM active_counts
ORDER BY month_start;

WITH params AS (
  SELECT DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '11' MONTH AS start_month,
         DATE_TRUNC('month', CURRENT_DATE) AS end_month
),
months AS (
  -- recursive ANSI CTE to generate month starts for last 12 months
  SELECT start_month AS month_start FROM params
  UNION ALL
  SELECT month_start + INTERVAL '1' MONTH
  FROM months
  JOIN params ON month_start + INTERVAL '1' MONTH <= params.end_month
),
events AS (
  -- normalize to date/time and restrict window
  SELECT
    user_id,
    CAST(event_time AS DATE) AS event_date,
    DATE_TRUNC('month', event_time) AS month_start,
    subscription_type,
    event_type
  FROM user_activity
  JOIN params ON event_time >= params.start_month AND event_time < params.end_month + INTERVAL '1' MONTH
),
mau AS (
  -- one row per user-month
  SELECT month_start, COUNT(DISTINCT user_id) AS mau
  FROM events
  GROUP BY month_start
),
dau AS (
  -- distinct users per day
  SELECT month_start, event_date, COUNT(DISTINCT user_id) AS dau
  FROM events
  GROUP BY month_start, event_date
),
avg_dau AS (
  -- average daily active users per month
  SELECT month_start, AVG(dau) AS avg_dau
  FROM dau
  GROUP BY month_start
)
SELECT
  to_char(m.month_start,'YYYY-MM') AS month,
  COALESCE(mau.mau, 0) AS mau,
  ROUND(COALESCE(avg_dau.avg_dau,0) / NULLIF(mau.mau,0), 3) AS dau_mau_ratio
FROM months m
LEFT JOIN mau     ON mau.month_start = m.month_start
LEFT JOIN avg_dau ON avg_dau.month_start = m.month_start
ORDER BY m.month_start;