version: 2 models: - name: agg_user_hook_platform_daily_spine description: | Complete daily hook activity spine by user and platform with historical activity windows. Includes all user-platform-date combinations (spine) with hook play metrics and historical activity flags calculated using window functions. This is the foundation table for all other hook aggregations. config: on_schema_change: append_new_columns group: hooks meta: dagster: partitions_def: type: daily start_date: 2025-07-31 end_offset: 0 partition_mappings: - asset_key: ['prod_int', 'int_agg_user_hook_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 hook activity occurred (web, ios, android) - name: days_since_user_joined description: Number of days since the user joined - name: play_count description: Total number of hook play sessions on this platform this day - name: play_duration_seconds description: Total hook play duration in seconds on this platform this day - name: unique_play_duration_seconds description: Total unique hook play duration in seconds (deduplicated) - name: hook_create_count description: Number of hooks created on this platform this day - name: is_active description: Whether user had any hook activity on this platform this day - name: is_active_create description: Whether user created hooks on this platform this day - name: is_active_listen description: Whether user listened to hooks on this platform this day - name: is_active_listen_own description: Whether user listened to their own hooks on this platform this day - name: is_active_listen_others description: Whether user listened to others' hooks on this platform this day - name: play_breakdown description: JSON breakdown of 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, with v2 including own/others breakdown - name: historical_activity description: JSON object with historical activity flags across different time windows - name: p_date description: Date partition - name: agg_user_hook_platform_daily description: | Daily hook 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: hooks meta: dagster: partitions_def: type: daily start_date: 2025-07-31 end_offset: 0 partition_mappings: - asset_key: ['prod_marts', 'agg_user_hook_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 hook activity occurred - name: days_since_user_joined description: Number of days since the user joined - name: play_count description: Total hook play sessions on this platform - name: play_duration_seconds description: Total hook play duration in seconds on this platform - name: unique_play_duration_seconds description: Total unique hook play duration in seconds on this platform - name: hook_create_count description: Total hooks created on this platform - name: is_active description: Whether user had hook activity (always TRUE in this table) - name: is_active_create description: Whether user created hooks on this platform - name: is_active_listen description: Whether user listened to hooks on this platform - name: is_active_listen_own description: Whether user listened to their own hooks on this platform - name: is_active_listen_others description: Whether user listened to others' hooks on this platform - name: play_breakdown description: JSON breakdown of 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, with v2 including own/others breakdown - name: historical_activity description: JSON object with historical activity flags across different time windows - name: p_date description: Date partition - name: agg_user_hook_daily_spine description: | Cross-platform daily hook activity spine by user with historical activity windows. Aggregates platform-level metrics into user-level daily totals. Includes all user-date combinations with hook activity metrics summed across platforms. config: on_schema_change: append_new_columns group: hooks meta: dagster: partitions_def: type: daily start_date: 2025-07-31 end_offset: 0 partition_mappings: - asset_key: ['prod_marts', 'agg_user_hook_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: days_since_user_joined description: Number of days since the user joined - name: play_count description: Total hook play sessions across all platforms this day - name: play_duration_seconds description: Total hook play duration across all platforms this day - name: unique_play_duration_seconds description: Total unique hook play duration across all platforms this day - name: hook_create_count description: Total hooks created across all platforms this day - name: is_active description: Whether user had hook activity on ANY platform this day - name: is_active_create description: Whether user created hooks on ANY platform this day - name: is_active_listen description: Whether user listened to hooks on ANY platform this day - name: is_active_listen_own description: Whether user listened to their own hooks on ANY platform this day - name: is_active_listen_others description: Whether user listened to others' hooks on ANY platform this day - name: play_breakdown description: Aggregated JSON breakdown of 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, with v2 including own/others breakdown - name: historical_activity description: Historical activity flags aggregated across platforms (TRUE if active on ANY platform) - name: p_date description: Date partition - name: agg_user_hook_daily description: | Cross-platform daily hook activity by user, 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: hooks meta: dagster: partitions_def: type: daily start_date: 2025-07-31 end_offset: 0 partition_mappings: - asset_key: ['prod_marts', 'agg_user_hook_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: days_since_user_joined description: Number of days since the user joined - name: play_count description: Total hook play sessions across all platforms - name: play_duration_seconds description: Total hook play duration across all platforms - name: unique_play_duration_seconds description: Total unique hook play duration across all platforms - name: hook_create_count description: Total hooks created across all platforms - name: is_active description: Whether user had hook activity on any platform (always TRUE in this table) - name: is_active_create description: Whether user created hooks on any platform - name: is_active_listen description: Whether user listened to hooks on any platform - name: is_active_listen_own description: Whether user listened to their own hooks on any platform - name: is_active_listen_others description: Whether user listened to others' hooks on any platform - name: play_breakdown description: Aggregated JSON breakdown of 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, with v2 including own/others breakdown - name: historical_activity description: Historical activity flags aggregated across platforms (TRUE if active on ANY platform) - name: p_date description: Date partition - name: user_hook_engagement description: User hook engagement metrics, downstream of int_user_hook_interactions_lifetime table. config: group: hooks tags: ["automation_condition=hourly_cron"] on_schema_change: append_new_columns columns: - name: user_id description: User ID - name: total_hooks_interacted description: Total number of hooks interacted with - name: distinct_hooks_interacted description: Total number of distinct hooks interacted with - name: first_interaction_ts description: First interaction timestamp - name: last_interaction_ts description: Last interaction timestamp - name: hook_session_count description: Total number of hook sessions - name: hook_session_duration_seconds description: Total hook session duration in seconds - name: play_duration_seconds description: Total play duration in seconds - name: unique_play_duration_seconds description: Total unique play duration in seconds - name: long_watch_session_count description: Total long watch session count - name: repeat_count description: Total repeat count - name: like_count description: Total like count - name: undo_like_count description: Total undo like count - name: net_like_count description: Total net like count - name: dislike_count description: Total dislike count - name: undo_dislike_count description: Total undo dislike count - name: net_dislike_count description: Total net dislike count - name: comment_open_tap_count description: Total comment open tap count - name: comment_begin_write_count description: Total comment begin write count - name: comment_count description: Total comment count - name: share_tap_count description: Total share tap count - name: share_count description: Total share count - name: profile_tap_count description: Total profile tap count - name: follow_count description: Total follow count - name: unfollow_count description: Total unfollow count - name: net_follow_count description: Total net follow count - name: playlist_add_tap_count description: Total playlist add tap count - name: playlist_add_count description: Total playlist add count - name: create_hook_tap_count description: Total create hook tap count - name: create_hook_submit_count description: Total create hook submit count - name: create_hook_success_count description: Total create hook success count - name: remix_tap_count description: Total remix tap count - name: song_pill_tap_count description: Total song pill tap count - name: go_to_full_song_tap_count description: Total go to full song tap count - name: song_title_tap_count description: Total song title tap count - name: report_count description: Total report count - name: hide_creator_count description: Total hide creator count - name: recommended_interactions description: Recommended interactions metrics, in a JSON object - name: organic_interactions description: Organic interactions metrics, in a JSON object - name: hook_stats description: Hook stats metrics, downstream of int_user_hook_interactions_lifetime table. config: group: hooks tags: ["automation_condition=hourly_cron"] on_schema_change: append_new_columns columns: - name: hook_id description: Hook ID - name: total_users_interacted description: Total number of users interacted with - name: distinct_users_interacted description: Total number of distinct users interacted with - name: first_interaction_ts description: First interaction timestamp - name: last_interaction_ts description: Last interaction timestamp - name: hook_session_count description: Total hook session count - name: hook_session_duration_seconds description: Total hook session duration in seconds - name: play_duration_seconds description: Total play duration in seconds - name: unique_play_duration_seconds description: Total unique play duration in seconds - name: long_watch_session_count description: Total long watch session count - name: repeat_count description: Total repeat count - name: like_count description: Total like count - name: undo_like_count description: Total undo like count - name: net_like_count description: Total net like count - name: dislike_count description: Total dislike count - name: undo_dislike_count description: Total undo dislike count - name: net_dislike_count description: Total net dislike count - name: comment_open_tap_count description: Total comment open tap count - name: comment_begin_write_count description: Total comment begin write count - name: comment_count description: Total comment count - name: share_tap_count description: Total share tap count - name: share_count description: Total share count - name: profile_tap_count description: Total profile tap count - name: follow_count description: Total follow count - name: unfollow_count description: Total unfollow count - name: net_follow_count description: Total net follow count - name: playlist_add_tap_count description: Total playlist add tap count - name: playlist_add_count description: Total playlist add count - name: create_hook_tap_count description: Total create hook tap count - name: create_hook_submit_count description: Total create hook submit count - name: create_hook_success_count description: Total create hook success count - name: remix_tap_count description: Total remix tap count - name: song_pill_tap_count description: Total song pill tap count - name: go_to_full_song_tap_count description: Total go to full song tap count - name: song_title_tap_count description: Total song title tap count - name: report_count description: Total report count - name: hide_creator_count description: Total hide creator count - name: recommended_interactions description: Recommended interactions metrics, in a JSON object - name: organic_interactions description: Organic interactions metrics, in a JSON object