version: 2 models: - name: agg_user_metrics description: Aggregated current user metrics as of today regarding creation and listening activity. config: group: user_metrics tags: ["cadence=daily", "dbt"] columns: - name: user_id description: User ID - name: user_uid description: User UID - name: n_days_active description: Number of days the user has been active - name: n_days_active_create description: Number of days the user has been active and created clips - name: n_days_active_listen description: Number of days the user has been active and listened to clips - name: n_days_active_last_7_days description: Number of days the user has been active in the last 7 days - name: n_days_active_create_last_7_days description: Number of days the user has been active and created clips in the last 7 days - name: n_days_active_listen_last_7_days description: Number of days the user has been active and listened to clips in the last 7 days - name: n_days_active_listen_own_last_7_days description: Number of days the user has been active and listened to their own clips in the last 7 days - name: n_days_active_listen_others_last_7_days description: Number of days the user has been active and listened to other users' clips in the last 7 days - name: n_days_active_last_28_days description: Number of days the user has been active in the last 28 days - name: n_days_active_create_last_28_days description: Number of days the user has been active and created clips in the last 28 days - name: n_days_active_listen_last_28_days description: Number of days the user has been active and listened to clips in the last 28 days - name: n_days_active_listen_own_last_28_days description: Number of days the user has been active and listened to their own clips in the last 28 days - name: n_days_active_listen_others_last_28_days description: Number of days the user has been active and listened to other users' clips in the last 28 days - name: n_clips_created description: Number of clips the user has created - name: n_gen_clips_created description: Number of clips the user has created using the generative AI - name: pct_clips_created_gen description: Percentage of clips the user has created using the generative AI - name: avg_clips_created_per_active_create_day description: Average number of clips the user has created per day they have been active and created clips - name: n_plays description: Number of clips the user has listened to - name: n_plays_own description: Number of clips the user has listened to their own clips - name: n_plays_others description: Number of clips the user has listened to other users' clips - name: pct_plays_own description: Percentage of total play count the user has listened to their own clips - name: ratio_plays_others_to_own description: Ratio of total play count the user has listened to other users' clips to their own clips - name: avg_plays_per_active_listen_day description: Average number of clips the user has listened to per day they have been active and listened to clips - name: avg_plays_own_per_active_listen_day description: Average number of clips the user has listened to their own clips per day they have been active and listened to clips - name: avg_plays_others_per_active_listen_day description: Average number of clips the user has listened to other users' clips per day they have been active and listened to clips - name: avg_plays_per_active_create_day description: Average number of clips the user has created per day they have been active and created clips - name: total_play_duration_seconds description: Total duration of clips the user has listened to - name: total_play_duration_seconds_own description: Total duration of clips the user has listened to their own clips - name: total_play_duration_seconds_others description: Total duration of clips the user has listened to other users' clips - name: pct_play_duration_own description: Percentage of total play duration the user has listened to their own clips - name: ratio_play_duration_others_to_own description: Ratio of total play duration the user has listened to other users' clips to their own clips - name: avg_play_duration_seconds_own description: Average duration of clips the user has listened to their own clips - name: avg_play_duration_seconds_others description: Average duration of clips the user has listened to other users' clips - name: days_since_user_joined description: Number of days since the user joined the platform - name: active_user_metrics description: | Unified active user metrics table with Daily Active Users (DAU), Weekly Active Users (WAU), and Monthly Active Users (MAU) with comprehensive bot/nonbot breakdowns. Each metric has separate columns for bot and non-bot users, providing full visibility into bot activity across all dimensions including platforms and subscription tiers. TIME WINDOWS: - DAU: Users active on that specific day - WAU: Users active in the past 7 days (including that day) - 7-day rolling window - MAU: Users active in the past 30 days (including that day) - 30-day rolling window Uses complete date spine to ensure one row per day (even days with 0 users). Built from active_users_daily dbt model and user_subscription_tier_daily (Dagster-managed). config: group: user_metrics meta: dagster: partitions_def: type: daily start_date: 2024-06-01 end_offset: 0 partition_mappings: - asset_key: ['prod_marts', 'dim_date'] type: identity - asset_key: ['prod_marts', 'active_users_daily'] type: identity - asset_key: ['prod_marts', 'user_subscription_tier_daily'] type: identity automation_condition: daily_cron_with_eager_historical_backfill_condition backfill_policy: max_partitions_per_run: 7 columns: - name: p_date description: Date partition # DAU Overall - name: dau_bot description: Daily Active Users - total, including bots and non-bots - name: dau_nonbot description: Daily Active Users - non-bots only # DAU by Platform - name: dau_web description: Daily Active Users on Web - total, including bots and non-bots - name: dau_web_nonbot description: Daily Active Users on Web - non-bots only - name: dau_ios description: Daily Active Users on iOS - total, including bots and non-bots - name: dau_ios_nonbot description: Daily Active Users on iOS - non-bots only - name: dau_android description: Daily Active Users on Android - total, including bots and non-bots - name: dau_android_nonbot description: Daily Active Users on Android - non-bots only # DAU Free Tier - name: dau_free description: Daily Active Users with Free Plan - total, including bots and non-bots - name: dau_free_nonbot description: Daily Active Users with Free Plan - non-bots only - name: dau_free_web description: Daily Active Users with Free Plan on Web - total, including bots and non-bots - name: dau_free_web_nonbot description: Daily Active Users with Free Plan on Web - non-bots only - name: dau_free_ios description: Daily Active Users with Free Plan on iOS - total, including bots and non-bots - name: dau_free_ios_nonbot description: Daily Active Users with Free Plan on iOS - non-bots only - name: dau_free_android description: Daily Active Users with Free Plan on Android - total, including bots and non-bots - name: dau_free_android_nonbot description: Daily Active Users with Free Plan on Android - non-bots only # DAU Pro Tier - name: dau_pro description: Daily Active Users with Pro Plan - total, including bots and non-bots - name: dau_pro_nonbot description: Daily Active Users with Pro Plan - non-bots only - name: dau_pro_web description: Daily Active Users with Pro Plan on Web - total, including bots and non-bots - name: dau_pro_web_nonbot description: Daily Active Users with Pro Plan on Web - non-bots only - name: dau_pro_ios description: Daily Active Users with Pro Plan on iOS - total, including bots and non-bots - name: dau_pro_ios_nonbot description: Daily Active Users with Pro Plan on iOS - non-bots only - name: dau_pro_android description: Daily Active Users with Pro Plan on Android - total, including bots and non-bots - name: dau_pro_android_nonbot description: Daily Active Users with Pro Plan on Android - non-bots only # DAU Premier Tier - name: dau_premier description: Daily Active Users with Premier Plan - total, including bots and non-bots - name: dau_premier_nonbot description: Daily Active Users with Premier Plan - non-bots only - name: dau_premier_web description: Daily Active Users with Premier Plan on Web - total, including bots and non-bots - name: dau_premier_web_nonbot description: Daily Active Users with Premier Plan on Web - non-bots only - name: dau_premier_ios description: Daily Active Users with Premier Plan on iOS - total, including bots and non-bots - name: dau_premier_ios_nonbot description: Daily Active Users with Premier Plan on iOS - non-bots only - name: dau_premier_android description: Daily Active Users with Premier Plan on Android - total, including bots and non-bots - name: dau_premier_android_nonbot description: Daily Active Users with Premier Plan on Android - non-bots only # WAU Overall - name: wau description: Weekly Active Users (7-day rolling window) - total, including bots and non-bots - name: wau_nonbot description: Weekly Active Users (7-day rolling window) - non-bots only # WAU by Platform - name: wau_web description: Weekly Active Users on Web - total, including bots and non-bots - name: wau_web_nonbot description: Weekly Active Users on Web - non-bots only - name: wau_ios description: Weekly Active Users on iOS - total, including bots and non-bots - name: wau_ios_nonbot description: Weekly Active Users on iOS - non-bots only - name: wau_android description: Weekly Active Users on Android - total, including bots and non-bots - name: wau_android_nonbot description: Weekly Active Users on Android - non-bots only # WAU Free Tier - name: wau_free description: Weekly Active Users with Free Plan - total, including bots and non-bots - name: wau_free_nonbot description: Weekly Active Users with Free Plan - non-bots only - name: wau_free_web description: Weekly Active Users with Free Plan on Web - total, including bots and non-bots - name: wau_free_web_nonbot description: Weekly Active Users with Free Plan on Web - non-bots only - name: wau_free_ios description: Weekly Active Users with Free Plan on iOS - total, including bots and non-bots - name: wau_free_ios_nonbot description: Weekly Active Users with Free Plan on iOS - non-bots only - name: wau_free_android description: Weekly Active Users with Free Plan on Android - total, including bots and non-bots - name: wau_free_android_nonbot description: Weekly Active Users with Free Plan on Android - non-bots only # WAU Pro Tier - name: wau_pro description: Weekly Active Users with Pro Plan - total, including bots and non-bots - name: wau_pro_nonbot description: Weekly Active Users with Pro Plan - non-bots only - name: wau_pro_web description: Weekly Active Users with Pro Plan on Web - total, including bots and non-bots - name: wau_pro_web_nonbot description: Weekly Active Users with Pro Plan on Web - non-bots only - name: wau_pro_ios description: Weekly Active Users with Pro Plan on iOS - total, including bots and non-bots - name: wau_pro_ios_nonbot description: Weekly Active Users with Pro Plan on iOS - non-bots only - name: wau_pro_android description: Weekly Active Users with Pro Plan on Android - total, including bots and non-bots - name: wau_pro_android_nonbot description: Weekly Active Users with Pro Plan on Android - non-bots only # WAU Premier Tier - name: wau_premier description: Weekly Active Users with Premier Plan - total, including bots and non-bots - name: wau_premier_nonbot description: Weekly Active Users with Premier Plan - non-bots only - name: wau_premier_web description: Weekly Active Users with Premier Plan on Web - total, including bots and non-bots - name: wau_premier_web_nonbot description: Weekly Active Users with Premier Plan on Web - non-bots only - name: wau_premier_ios description: Weekly Active Users with Premier Plan on iOS - total, including bots and non-bots - name: wau_premier_ios_nonbot description: Weekly Active Users with Premier Plan on iOS - non-bots only - name: wau_premier_android description: Weekly Active Users with Premier Plan on Android - total, including bots and non-bots - name: wau_premier_android_nonbot description: Weekly Active Users with Premier Plan on Android - non-bots only # MAU Overall - name: mau description: Monthly Active Users (30-day rolling window) - total, including bots and non-bots - name: mau_nonbot description: Monthly Active Users (30-day rolling window) - non-bots only # MAU by Platform - name: mau_web description: Monthly Active Users on Web - total, including bots and non-bots - name: mau_web_nonbot description: Monthly Active Users on Web - non-bots only - name: mau_ios description: Monthly Active Users on iOS - total, including bots and non-bots - name: mau_ios_nonbot description: Monthly Active Users on iOS - non-bots only - name: mau_android description: Monthly Active Users on Android - total, including bots and non-bots - name: mau_android_nonbot description: Monthly Active Users on Android - non-bots only # MAU Free Tier - name: mau_free description: Monthly Active Users with Free Plan - total, including bots and non-bots - name: mau_free_nonbot description: Monthly Active Users with Free Plan - non-bots only - name: mau_free_web description: Monthly Active Users with Free Plan on Web - total, including bots and non-bots - name: mau_free_web_nonbot description: Monthly Active Users with Free Plan on Web - non-bots only - name: mau_free_ios description: Monthly Active Users with Free Plan on iOS - total, including bots and non-bots - name: mau_free_ios_nonbot description: Monthly Active Users with Free Plan on iOS - non-bots only - name: mau_free_android description: Monthly Active Users with Free Plan on Android - total, including bots and non-bots - name: mau_free_android_nonbot description: Monthly Active Users with Free Plan on Android - non-bots only # MAU Pro Tier - name: mau_pro description: Monthly Active Users with Pro Plan - total, including bots and non-bots - name: mau_pro_nonbot description: Monthly Active Users with Pro Plan - non-bots only - name: mau_pro_web description: Monthly Active Users with Pro Plan on Web - total, including bots and non-bots - name: mau_pro_web_nonbot description: Monthly Active Users with Pro Plan on Web - non-bots only - name: mau_pro_ios description: Monthly Active Users with Pro Plan on iOS - total, including bots and non-bots - name: mau_pro_ios_nonbot description: Monthly Active Users with Pro Plan on iOS - non-bots only - name: mau_pro_android description: Monthly Active Users with Pro Plan on Android - total, including bots and non-bots - name: mau_pro_android_nonbot description: Monthly Active Users with Pro Plan on Android - non-bots only # MAU Premier Tier - name: mau_premier description: Monthly Active Users with Premier Plan - total, including bots and non-bots - name: mau_premier_nonbot description: Monthly Active Users with Premier Plan - non-bots only - name: mau_premier_web description: Monthly Active Users with Premier Plan on Web - total, including bots and non-bots - name: mau_premier_web_nonbot description: Monthly Active Users with Premier Plan on Web - non-bots only - name: mau_premier_ios description: Monthly Active Users with Premier Plan on iOS - total, including bots and non-bots - name: mau_premier_ios_nonbot description: Monthly Active Users with Premier Plan on iOS - non-bots only - name: mau_premier_android description: Monthly Active Users with Premier Plan on Android - total, including bots and non-bots - name: mau_premier_android_nonbot description: Monthly Active Users with Premier Plan on Android - non-bots only # Metadata - name: dbt_updated_at description: Timestamp when this row was last updated by dbt - name: user_subscription_tier_daily description: | Daily table showing subscription tier for each user on each day. This table provides a historical view of user subscription tiers from rds_discord_info. IMPORTANT: rds_discord_info gets updated hourly (2 minutes past each hour) with current date data. This model runs at 5am UTC (daily_5am_cron) and processes TODAY's partition (end_offset: 1). Historical data (before 2024-06-01) must be backfilled from agg_user_daily_spine in Snowflake. One row per user per day showing their subscription_tier for that day. config: group: user_metrics meta: dagster: partitions_def: type: daily start_date: 2024-06-01 backfill_policy: max_partitions_per_run: 7 automation_condition: daily_cron_with_eager_historical_backfill_condition partition_mappings: - asset_key: ['rds_discord_info'] type: time_window - asset_key: ['rds_usage_plan'] type: all columns: - name: user_id description: User ID (number) - name: user_uid description: User UID (string) - name: subscription_tier description: User's subscription tier for this day (e.g., "Pro Plan", "Free Plan", etc.) - name: p_date description: Partition date (DATE)