version: 2 models: - name: agg_user_platform_daily_spine description: | Complete daily user activity spine by user and platform with historical activity windows. Includes all user-platform-date combinations (spine) with clip, play, and hook metrics, and historical activity flags calculated using window functions. This is the foundation table for all other user aggregations. config: on_schema_change: append_new_columns group: agg meta: dagster: partitions_def: type: daily start_date: 2024-06-01 end_offset: 0 partition_mappings: - asset_key: ['prod_int', 'int_agg_user_platform_daily_spine_base'] type: identity automation_condition: agg_user_daily_cron backfill_policy: max_partitions_per_run: 28 columns: - name: user_id description: User ID - name: user_uid description: User UID - name: subscription_tier description: User's subscription tier (Pro Plan, Pro v2 Plan, etc.) - name: platform description: Platform where activity occurred (web, ios, android) - name: clip_total_count description: Total clips created on this platform this day - name: gen_clip_total_count description: Total gen clips created on this platform this day - name: play_count description: Total number of play sessions on this platform this day - name: play_duration_seconds description: Total play duration in seconds on this platform this day - name: unique_play_duration_seconds description: Total unique play duration in seconds (deduplicated) - name: play_breakdown description: JSON breakdown of play metrics by song ownership (own/others) - name: hook_create_count description: Number of hooks created on this platform this day - name: hook_play_count description: Total number of hook play sessions on this platform this day - name: hook_play_duration_seconds description: Total hook play duration in seconds on this platform this day - name: hook_unique_play_duration_seconds description: Total unique hook play duration in seconds - name: hook_play_breakdown description: JSON breakdown of hook play metrics by song ownership - name: is_high_intent_listening description: JSON object with high intent listening flags by version (v1, v2) - name: high_intent_listening_breakdown description: JSON breakdown of high intent listening by version, format (clips/hooks), and ownership - name: is_active description: Whether user had any activity on this platform this day (from active_users_daily) - name: engagement_flags description: JSON object with all engagement flags (clip_create, clip_listen, hook_create, hook_listen, etc.) - name: total_following_cnt description: Total count of users this user is following (platform-agnostic) - name: total_follower_cnt description: Total count of users following this user (platform-agnostic) - name: net_likes_received_count description: Net likes received on user's content on this platform this day - name: historical_activity description: JSON object with historical activity flags across different time windows (prev_day, 7d, prev_7d, 28d) - name: p_date description: Date partition - name: days_since_user_joined description: Number of days since the user joined - name: agg_user_platform_daily description: | Daily user activity by user and platform, filtered to only active user-platform-days. Same schema as spine but excludes rows where is_active = FALSE. config: on_schema_change: append_new_columns group: agg meta: dagster: partitions_def: type: daily start_date: 2024-06-01 end_offset: 0 partition_mappings: - asset_key: ['prod_marts', 'agg_user_platform_daily_spine'] type: identity automation_condition: agg_user_daily_cron backfill_policy: max_partitions_per_run: 28 columns: - name: user_id description: User ID - name: user_uid description: User UID - name: subscription_tier description: User's subscription tier - name: platform description: Platform where activity occurred - name: clip_total_count description: Total clips created on this platform - name: gen_clip_total_count description: Total gen clips created on this platform - name: play_count description: Total play sessions on this platform - name: play_duration_seconds description: Total play duration in seconds on this platform - name: unique_play_duration_seconds description: Total unique play duration in seconds on this platform - name: play_breakdown description: JSON breakdown of play metrics by song ownership - name: hook_create_count description: Total hooks created on this platform - name: hook_play_count description: Total hook play sessions on this platform - name: hook_play_duration_seconds description: Total hook play duration in seconds on this platform - name: hook_unique_play_duration_seconds description: Total unique hook play duration in seconds on this platform - name: hook_play_breakdown description: JSON breakdown of hook play metrics by song ownership - name: is_high_intent_listening description: JSON object with high intent listening flags by version - name: high_intent_listening_breakdown description: JSON breakdown of high intent listening by version, format, and ownership - name: is_active description: Whether user had activity (always TRUE in this table) - name: engagement_flags description: JSON object with all engagement flags - name: total_following_cnt description: Total count of users this user is following - name: total_follower_cnt description: Total count of users following this user - name: net_likes_received_count description: Net likes received on user's content on this platform - name: historical_activity description: JSON object with historical activity flags across different time windows - name: p_date description: Date partition - name: days_since_user_joined description: Number of days since the user joined - name: agg_user_daily_spine description: | Cross-platform daily user activity spine with historical activity windows. Aggregates platform-level metrics into user-level daily totals. Includes all user-date combinations with metrics summed across platforms. config: on_schema_change: append_new_columns group: agg meta: dagster: partitions_def: type: daily start_date: 2024-06-01 end_offset: 0 partition_mappings: - asset_key: ['prod_marts', 'agg_user_platform_daily_spine'] type: identity automation_condition: agg_user_daily_cron backfill_policy: max_partitions_per_run: 28 columns: - name: user_id description: User ID - name: user_uid description: User UID - name: subscription_tier description: User's subscription tier - name: clip_total_count description: Total clips created across all platforms this day - name: gen_clip_total_count description: Total gen clips created across all platforms this day - name: play_count description: Total play sessions across all platforms this day - name: play_duration_seconds description: Total play duration across all platforms this day - name: unique_play_duration_seconds description: Total unique play duration across all platforms this day - name: play_breakdown description: Aggregated JSON breakdown of play metrics across all platforms - name: hook_create_count description: Total hooks created across all platforms this day - name: hook_play_count description: Total hook play sessions across all platforms this day - name: hook_play_duration_seconds description: Total hook play duration across all platforms this day - name: hook_unique_play_duration_seconds description: Total unique hook play duration across all platforms this day - name: hook_play_breakdown description: Aggregated JSON breakdown of hook play metrics across all platforms - name: is_high_intent_listening description: JSON object with high intent listening flags (TRUE if active on ANY platform) - name: high_intent_listening_breakdown description: Aggregated JSON breakdown of high intent listening across all platforms - name: is_active description: Whether user had activity on ANY platform this day - name: engagement_flags description: JSON object with all engagement flags (clip_create, clip_listen, hook_create, hook_listen, etc.) - name: total_following_cnt description: Total count of users this user is following (platform-agnostic) - name: total_follower_cnt description: Total count of users following this user (platform-agnostic) - name: net_likes_received_count description: Net likes received on user's content across all platforms - name: historical_activity description: JSON object with historical activity flags across different time windows (prev_day, 7d, prev_7d, 28d) - name: p_date description: Date partition - name: days_since_user_joined description: Number of days since the user joined - name: agg_user_daily description: | Cross-platform daily user activity, filtered to only active user-days. Same schema as daily spine but excludes rows where is_active = FALSE. config: on_schema_change: append_new_columns group: agg meta: dagster: partitions_def: type: daily start_date: 2024-06-01 end_offset: 0 partition_mappings: - asset_key: ['prod_marts', 'agg_user_platform_daily_spine'] type: identity automation_condition: agg_user_daily_cron backfill_policy: max_partitions_per_run: 28 columns: - name: user_id description: User ID - name: user_uid description: User UID - name: subscription_tier description: User's subscription tier - name: clip_total_count description: Total clips created across all platforms - name: gen_clip_total_count description: Total gen clips created across all platforms - name: play_count description: Total play sessions across all platforms - name: play_duration_seconds description: Total play duration across all platforms - name: unique_play_duration_seconds description: Total unique play duration across all platforms - name: play_breakdown description: Aggregated JSON breakdown of play metrics across all platforms - name: hook_create_count description: Total hooks created across all platforms - name: hook_play_count description: Total hook play sessions across all platforms - name: hook_play_duration_seconds description: Total hook play duration across all platforms - name: hook_unique_play_duration_seconds description: Total unique hook play duration across all platforms - name: hook_play_breakdown description: Aggregated JSON breakdown of hook play metrics across all platforms - name: is_high_intent_listening description: JSON object with high intent listening flags - name: high_intent_listening_breakdown description: Aggregated JSON breakdown of high intent listening across all platforms - name: is_active description: Whether user had activity on any platform (always TRUE in this table) - name: engagement_flags description: JSON object with all engagement flags (clip_create, clip_listen, hook_create, hook_listen, etc.) - name: total_following_cnt description: Total count of users this user is following - name: total_follower_cnt description: Total count of users following this user - name: net_likes_received_count description: Net likes received on user's content across all platforms - name: historical_activity description: JSON object with historical activity flags across different time windows - name: p_date description: Date partition - name: days_since_user_joined description: Number of days since the user joined