version: 2 models: - name: int_agg_user_hook_platform_daily_spine_base description: | Base daily aggregation of hook play metrics by user and platform. This intermediate model aggregates fact_hook_play data into daily user-platform metrics with placeholders for future hook creation and engagement features. Historical activity flags are set to NULL and calculated in the final spine model. config: warehouse: FACT_HOOK_PLAY_LARGE group: hooks on_schema_change: append_new_columns meta: dagster: partitions_def: type: daily start_date: 2025-07-31 end_offset: 0 partition_mappings: - asset_key: ['prod_int', 'int_user_platform_date_spine'] type: identity - asset_key: fact_hook_play type: time_window - asset_key: dim_hook type: time_window 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, calculated from join date - name: play_count description: Total number of 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 (deduplicated if same parts replayed) - name: hook_create_count description: Number of hooks created on this platform - 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 ownership: {"own": {"count": N, "duration_seconds": N, "unique_duration_seconds": N}, "others": {...}} - name: is_high_intent_listening description: JSON object with high intent listening flags by version (v1, v2) on this day. - name: high_intent_listening_breakdown description: JSON breakdown of high intent listening by version, with v2 including own/others breakdown - name: p_date description: Date partition for the metrics - name: int_user_hook_interactions_hourly description: Hourly user hook interactions metrics, downstream of hook_session table. config: warehouse: HOOK_SESSION_X_SMALL group: hooks on_schema_change: append_new_columns meta: dagster: partitions_def: type: hourly start_date: 2025-07-31 end_offset: -1 automation_condition: hourly_cron_with_eager_historical_backfill_condition backfill_policy: max_partitions_per_run: 336 columns: - name: user_id description: User ID - name: hook_id description: Hook ID - name: p_date description: Date partition for the metrics - name: p_hour description: Hour partition for the metrics - name: hook_session_count description: Number of hook sessions - name: hook_session_duration_seconds description: Hook session duration in seconds - name: play_duration_seconds description: Play duration in seconds - name: unique_play_duration_seconds description: Unique play duration in seconds - name: long_watch_session_count description: Number of long watch sessions - name: repeat_count description: Repeat count - name: like_count description: Like count - name: undo_like_count description: Undo like count - name: net_like_count description: Net like count - name: dislike_count description: Dislike count - name: undo_dislike_count description: Undo dislike count - name: net_dislike_count description: Net dislike count - name: comment_open_tap_count description: Comment open tap count - name: comment_begin_write_count description: Comment begin write count - name: comment_count description: Comment count - name: share_tap_count description: Share tap count - name: share_count description: Share count - name: profile_tap_count description: Profile tap count - name: follow_count description: Follow count - name: unfollow_count description: Unfollow count - name: net_follow_count description: Net follow count - name: playlist_add_tap_count description: Playlist add tap count - name: playlist_add_count description: Playlist add count - name: create_hook_tap_count description: Create hook tap count - name: create_hook_submit_count description: Create hook submit count - name: create_hook_success_count description: Create hook success count - name: remix_tap_count description: Remix tap count - name: song_pill_tap_count description: Song pill tap count - name: go_to_full_song_tap_count description: Go to full song tap count - name: song_title_tap_count description: Song title tap count - name: report_count description: Report count - name: hide_creator_count description: 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: int_user_hook_interactions_lifetime description: Lifetime user hook interactions metrics, downstream of int_user_hook_interactions_hourly table. config: warehouse: HOOK_SESSION_X_SMALL group: hooks tags: ['automation_condition=hourly_cron'] on_schema_change: append_new_columns columns: - name: user_id description: User ID - name: hook_id description: Hook ID - name: first_interaction_ts description: First interaction timestamp - name: last_interaction_ts description: Last interaction timestamp - name: hook_session_count description: Hook session count - name: hook_session_duration_seconds description: Hook session duration in seconds - name: play_duration_seconds description: Play duration in seconds - name: unique_play_duration_seconds description: Unique play duration in seconds - name: long_watch_session_count description: Long watch session count - name: repeat_count description: Repeat count - name: like_count description: Like count - name: undo_like_count description: Undo like count - name: net_like_count description: Net like count - name: dislike_count description: Dislike count - name: undo_dislike_count description: Undo dislike count - name: net_dislike_count description: Net dislike count - name: comment_open_tap_count description: Comment open tap count - name: comment_begin_write_count description: Comment begin write count - name: comment_count description: Comment count - name: share_tap_count description: Share tap count - name: share_count description: Share count - name: profile_tap_count description: Profile tap count - name: follow_count description: Follow count - name: unfollow_count description: Unfollow count - name: net_follow_count description: Net follow count - name: playlist_add_tap_count description: Playlist add tap count - name: playlist_add_count description: Playlist add count - name: create_hook_tap_count description: Create hook tap count - name: create_hook_submit_count description: Create hook submit count - name: create_hook_success_count description: Create hook success count - name: remix_tap_count description: Remix tap count - name: song_pill_tap_count description: Song pill tap count - name: go_to_full_song_tap_count description: Go to full song tap count - name: song_title_tap_count description: Song title tap count - name: report_count description: Report count - name: hide_creator_count description: Hide creator count - name: first_context_type description: Context type from first interaction - name: first_platform description: Platform from first interaction - name: first_country description: Country from first interaction - name: first_city description: City from first interaction - name: recommended_interactions description: Recommended interactions metrics, in a JSON object - name: organic_interactions description: Organic interactions metrics, in a JSON object