from pathlib import Path MODEL_VERSION = Path(__file__).parent.name assert MODEL_VERSION == "t2605_amazon_youtube" SNOWFLAKE_REGISTRY_MODEL_NAME = "TADAS_30_SHADOW" SNOWFLAKE_REGISTRY_MODEL_VERSION = "V1" REQUIRED_DSPS = ["spotify", "apple", "amazon", "youtube", "tiktok"] # features from # https://app.snowflake.com/sme/delphi/#/data/databases/FACTS/schemas/QA/model/TADAS_30_SHADOW/version/V1 ML_MODEL_FEATURES = """ CONSECUTIVE_TREND WOW_TOTAL_STREAMS WOW_TOTAL_SKIPS WOW_TOTAL_SAVES WOW_TOTAL_STREAMS_PASSIVE WOW_TOTAL_STREAMS_ACTIVE WOW_TOTAL_COLLECTION_STREAMS WOW_TOTAL_PAID_STREAMS WOW_TOTAL_FREE_STREAMS WOW_SPOTIFY_COLLECTION_STREAMS WOW_SPOTIFY_PLAYQUEUE_STREAMS WOW_SPOTIFY_ALBUM_PAGE_STREAMS WOW_SPOTIFY_ARTIST_PAGE_STREAMS WOW_SPOTIFY_SEARCH_STREAMS WOW_SPOTIFY_PLAYLIST_STREAMS WOW_SPOTIFY_RELEASE_RADAR_STREAMS WOW_SPOTIFY_DISCOVER_WEEKLY_STREAMS WOW_SPOTIFY_RADIO_STREAMS WOW_SPOTIFY_DAILYMIX_STREAMS WOW_SPOTIFY_CHART_STREAMS WOW_SPOTIFY_OTHER_STREAMS WOW_APPLE_LIBRARY_STREAMS WOW_APPLE_EXTERNAL_STREAMS WOW_APPLE_VOICE_STREAMS WOW_APPLE_SEARCH_STREAMS WOW_APPLE_MUSICKIT_STREAMS WOW_APPLE_NOWPLAYING_STREAMS WOW_APPLE_DISCOVERY_STREAMS WOW_APPLE_OTHER_STREAMS WOW_TT_CREATIONS WOW_TT_FAVORITES WOW_TT_LIKES WOW_TT_SHARES WOW_TT_VIEWS WOW_AMAZON_USERPLAYLIST_STREAMS WOW_AMAZON_SONG_STREAMS WOW_AMAZON_ALBUM_STREAMS WOW_AMAZON_ARTIST_STREAMS WOW_AMAZON_SEARCH_STREAMS WOW_AMAZON_PLAYLIST_STREAMS WOW_AMAZON_STATION_STREAMS WOW_YOUTUBE_DIRECT_OR_UNKNOWN_STREAMS WOW_YOUTUBE_SEARCH_STREAMS WOW_YOUTUBE_MISC_SOURCES_STREAMS WOW_YOUTUBE_CHANNELS_STREAMS WOW_YOUTUBE_PLAYLIST_STREAMS WOW_YOUTUBE_BROWSE_FEATURES_STREAMS WOW_YOUTUBE_SUGGESTED_VIDEOS_STREAMS WOW_YOUTUBE_OTHER_YOUTUBE_FEATURES_STREAMS WOW_YOUTUBE_PLAYLIST_PAGES_STREAMS WOW_YOUTUBE_ADVERTISING_STREAMS WOW_YOUTUBE_SHORTS_STREAMS PCT_CHANGE_VS_PREV ADJ_DOD_TOTAL_STREAMS ADJ_DOD_TOTAL_SKIPS ADJ_DOD_TOTAL_SAVES ADJ_DOD_TOTAL_STREAMS_PASSIVE ADJ_DOD_TOTAL_STREAMS_ACTIVE ADJ_DOD_TOTAL_COLLECTION_STREAMS ADJ_DOD_TOTAL_PAID_STREAMS ADJ_DOD_TOTAL_FREE_STREAMS ADJ_DOD_SPOTIFY_COLLECTION_STREAMS ADJ_DOD_SPOTIFY_PLAYQUEUE_STREAMS ADJ_DOD_SPOTIFY_ALBUM_PAGE_STREAMS ADJ_DOD_SPOTIFY_ARTIST_PAGE_STREAMS ADJ_DOD_SPOTIFY_SEARCH_STREAMS ADJ_DOD_SPOTIFY_PLAYLIST_STREAMS ADJ_DOD_SPOTIFY_RELEASE_RADAR_STREAMS ADJ_DOD_SPOTIFY_DISCOVER_WEEKLY_STREAMS ADJ_DOD_SPOTIFY_RADIO_STREAMS ADJ_DOD_SPOTIFY_DAILYMIX_STREAMS ADJ_DOD_SPOTIFY_CHART_STREAMS ADJ_DOD_SPOTIFY_OTHER_STREAMS ADJ_DOD_APPLE_LIBRARY_STREAMS ADJ_DOD_APPLE_EXTERNAL_STREAMS ADJ_DOD_APPLE_VOICE_STREAMS ADJ_DOD_APPLE_SEARCH_STREAMS ADJ_DOD_APPLE_MUSICKIT_STREAMS ADJ_DOD_APPLE_NOWPLAYING_STREAMS ADJ_DOD_APPLE_DISCOVERY_STREAMS ADJ_DOD_APPLE_OTHER_STREAMS ADJ_DOD_TT_CREATIONS ADJ_DOD_TT_FAVORITES ADJ_DOD_TT_LIKES ADJ_DOD_TT_SHARES ADJ_DOD_TT_VIEWS ADJ_DOD_AMAZON_USERPLAYLIST_STREAMS ADJ_DOD_AMAZON_SONG_STREAMS ADJ_DOD_AMAZON_ALBUM_STREAMS ADJ_DOD_AMAZON_ARTIST_STREAMS ADJ_DOD_AMAZON_SEARCH_STREAMS ADJ_DOD_AMAZON_PLAYLIST_STREAMS ADJ_DOD_AMAZON_STATION_STREAMS ADJ_DOD_YOUTUBE_DIRECT_OR_UNKNOWN_STREAMS ADJ_DOD_YOUTUBE_SEARCH_STREAMS ADJ_DOD_YOUTUBE_MISC_SOURCES_STREAMS ADJ_DOD_YOUTUBE_CHANNELS_STREAMS ADJ_DOD_YOUTUBE_PLAYLIST_STREAMS ADJ_DOD_YOUTUBE_BROWSE_FEATURES_STREAMS ADJ_DOD_YOUTUBE_SUGGESTED_VIDEOS_STREAMS ADJ_DOD_YOUTUBE_OTHER_YOUTUBE_FEATURES_STREAMS ADJ_DOD_YOUTUBE_PLAYLIST_PAGES_STREAMS ADJ_DOD_YOUTUBE_ADVERTISING_STREAMS ADJ_DOD_YOUTUBE_SHORTS_STREAMS """.split() spotify_streams_feed_id = 1 apple_store_id = 1 apple_streams_feed_id = 4 spotify_store_id = 286 PREFIX_YEST = "yest_" PREFIX_SEVEN = "seven_" PERIODS = { "today": { "date_range": "%(current_date)s::date", "column_prefix": "", }, "yesterday": { "date_range": "%(current_date)s::date - 1", "column_prefix": PREFIX_YEST, }, "week_ago": { "date_range": "%(current_date)s::date - 7", "column_prefix": PREFIX_SEVEN, }, } # mapping from feature name to dict with column name and optional condition DBT_MODELS_COLUMNS = { "v_streams_by_track_country_feed_distributor_daily": { # common features "total_streams": dict(column="STREAMS"), "total_skips": dict(column="SKIPS"), "total_saves": dict(column="SAVES"), "total_streams_active": dict( column=""" STREAMS_SOS_SPOTIFY_COLLECTION + STREAMS_SOS_SPOTIFY_PLAYQUEUE + STREAMS_SOS_SPOTIFY_ALBUMPAGE + STREAMS_SOS_SPOTIFY_ARTISTPAGE + STREAMS_SOS_SPOTIFY_SEARCH + STREAMS_SOS_APPLE_LIBRARY + STREAMS_SOS_APPLE_EXTERNAL + STREAMS_SOS_APPLE_VOICE + STREAMS_SOS_APPLE_SEARCH + STREAMS_SOS_YOUTUBE_DIRECT_OR_UNKNOWN + STREAMS_SOS_YOUTUBE_YOUTUBE_SEARCH + STREAMS_SOS_YOUTUBE_MISC_SOURCES + STREAMS_SOS_YOUTUBE_YOUTUBE_CHANNELS + STREAMS_SOS_YOUTUBE_YOUTUBE_SEARCH """, ), "total_streams_passive": dict( column=""" STREAMS_SOS_SPOTIFY_PLAYLISTS + STREAMS_SOS_SPOTIFY_RELEASERADAR + STREAMS_SOS_SPOTIFY_DISCOVERWEEKLY + STREAMS_SOS_SPOTIFY_RADIO + STREAMS_SOS_SPOTIFY_DAILYMIX + STREAMS_SOS_SPOTIFY_CHART + STREAMS_SOS_SPOTIFY_OTHER + STREAMS_SOS_APPLE_MUSICKIT + STREAMS_SOS_APPLE_NOWPLAYING + STREAMS_SOS_APPLE_DISCOVERY + STREAMS_SOS_APPLE_OTHER + STREAMS_SOS_YOUTUBE_PLAYLISTS + STREAMS_SOS_YOUTUBE_BROWSE_FEATURES + STREAMS_SOS_YOUTUBE_SUGGESTED_VIDEOS + STREAMS_SOS_YOUTUBE_OTHER_YOUTUBE_FEATURES + STREAMS_SOS_YOUTUBE_PLAYLIST_PAGES + STREAMS_SOS_YOUTUBE_YOUTUBE_ADVERTISING """, ), "total_collection_streams": dict(column="STREAMS_COLLECTION"), "total_paid_streams": dict(column="SUB_TYPE_SUBSCRIPTION"), "total_free_streams": dict(column="SUB_TYPE_ADSUPPORTED"), # spotify features "spotify_collection_streams": dict(column="STREAMS_SOS_SPOTIFY_COLLECTION"), "spotify_playqueue_streams": dict(column="STREAMS_SOS_SPOTIFY_PLAYQUEUE"), "spotify_album_page_streams": dict(column="STREAMS_SOS_SPOTIFY_ALBUMPAGE"), "spotify_artist_page_streams": dict(column="STREAMS_SOS_SPOTIFY_ARTISTPAGE"), "spotify_search_streams": dict(column="STREAMS_SOS_SPOTIFY_SEARCH"), "spotify_playlist_streams": dict(column="STREAMS_SOS_SPOTIFY_PLAYLISTS"), "spotify_release_radar_streams": dict( column="STREAMS_SOS_SPOTIFY_RELEASERADAR" ), "spotify_discover_weekly_streams": dict( column="STREAMS_SOS_SPOTIFY_DISCOVERWEEKLY" ), "spotify_radio_streams": dict(column="STREAMS_SOS_SPOTIFY_RADIO"), "spotify_dailymix_streams": dict(column="STREAMS_SOS_SPOTIFY_DAILYMIX"), "spotify_chart_streams": dict(column="STREAMS_SOS_SPOTIFY_CHART"), "spotify_other_streams": dict(column="STREAMS_SOS_SPOTIFY_OTHER"), # apple features "apple_library_streams": dict(column="STREAMS_SOS_APPLE_LIBRARY"), "apple_external_streams": dict(column="STREAMS_SOS_APPLE_EXTERNAL"), "apple_voice_streams": dict(column="STREAMS_SOS_APPLE_VOICE"), "apple_search_streams": dict(column="STREAMS_SOS_APPLE_SEARCH"), "apple_musickit_streams": dict(column="STREAMS_SOS_APPLE_MUSICKIT"), "apple_nowplaying_streams": dict(column="STREAMS_SOS_APPLE_NOWPLAYING"), "apple_discovery_streams": dict(column="STREAMS_SOS_APPLE_DISCOVERY"), "apple_other_streams": dict(column="STREAMS_SOS_APPLE_OTHER"), # amazon features "amazon_userplaylist_streams": dict(column="STREAMS_SOS_AMAZON_USERPLAYLIST"), "amazon_song_streams": dict(column="STREAMS_SOS_AMAZON_SONGS"), "amazon_album_streams": dict(column="STREAMS_SOS_AMAZON_ALBUM"), "amazon_artist_streams": dict(column="STREAMS_SOS_AMAZON_ARTIST"), "amazon_search_streams": dict(column="STREAMS_SOS_AMAZON_SEARCH"), "amazon_playlist_streams": dict(column="STREAMS_SOS_AMAZON_PLAYLIST"), "amazon_station_streams": dict(column="STREAMS_SOS_AMAZON_STATION"), # youtube features "youtube_direct_or_unknown_streams": dict( column="STREAMS_SOS_YOUTUBE_DIRECT_OR_UNKNOWN" ), "youtube_search_streams": dict(column="STREAMS_SOS_YOUTUBE_YOUTUBE_SEARCH"), "youtube_misc_sources_streams": dict(column="STREAMS_SOS_YOUTUBE_MISC_SOURCES"), "youtube_channels_streams": dict( column="STREAMS_SOS_YOUTUBE_YOUTUBE_CHANNELS" ), "youtube_playlist_streams": dict(column="STREAMS_SOS_YOUTUBE_PLAYLISTS"), "youtube_browse_features_streams": dict( column="STREAMS_SOS_YOUTUBE_BROWSE_FEATURES" ), "youtube_suggested_videos_streams": dict( column="STREAMS_SOS_YOUTUBE_SUGGESTED_VIDEOS" ), "youtube_other_youtube_features_streams": dict( column="STREAMS_SOS_YOUTUBE_OTHER_YOUTUBE_FEATURES" ), "youtube_playlist_pages_streams": dict( column="STREAMS_SOS_YOUTUBE_PLAYLIST_PAGES" ), "youtube_advertising_streams": dict( column="STREAMS_SOS_YOUTUBE_YOUTUBE_ADVERTISING" ), "youtube_shorts_streams": dict(column="STREAMS_SOS_YOUTUBE_SHORTS"), # below features are used only in trending flags "spotify_streams": dict( condition=f"store_id={spotify_store_id} and feed_id={spotify_streams_feed_id}", column="STREAMS", ), "spotify_active_streams": dict( condition=f"store_id={spotify_store_id} and feed_id={spotify_streams_feed_id}", column="STREAMS_ACTIVE", ), "apple_streams": dict( condition=f"store_id={apple_store_id} and feed_id={apple_streams_feed_id}", column="STREAMS", ), "apple_active_streams": dict( condition=f"store_id={apple_store_id} and feed_id={apple_streams_feed_id}", column="STREAMS_ACTIVE", ), }, "v_tiktok_by_isrc_country_daily": { # tiktok features "tt_creations": dict(column="creations"), "tt_favorites": dict(column="favorites"), "tt_likes": dict(column="likes"), "tt_shares": dict(column="shares"), "tt_views": dict(column="views"), }, } LIFT_THRESHOLD = 0.1 TRENDING_FLAGS = { # spotify trending flags "spotify_collection": { "lift_feature": "wow_spotify_collection_streams", "is_trending_lambda": lambda df: ( (df["spotify_collection_streams"] >= 250) & (df["wow_spotify_collection_streams"] >= LIFT_THRESHOLD) ), }, "spotify_lean_forward": { "lift_feature": "wow_spotify_active_streams", "is_trending_lambda": lambda df: ( df["wow_spotify_active_streams"] >= LIFT_THRESHOLD ), }, "spotify_search": { "lift_feature": "wow_spotify_search_streams", "is_trending_lambda": lambda df: ( (df["spotify_search_streams"] >= 250) & (df["wow_spotify_search_streams"] >= LIFT_THRESHOLD) ), }, "spotify_all": { "lift_feature": "wow_spotify_streams", "is_trending_lambda": lambda df: df["wow_spotify_streams"] >= LIFT_THRESHOLD, }, # apple trending flags "apple_lean_forward": { "lift_feature": "wow_apple_active_streams", "is_trending_lambda": lambda df: ( df["wow_apple_active_streams"] >= LIFT_THRESHOLD ), }, "apple_search": { "lift_feature": "wow_apple_search_streams", "is_trending_lambda": lambda df: ( (df["seven_apple_search_streams"] >= 250) & (df["wow_apple_search_streams"] >= LIFT_THRESHOLD) ), }, "apple_all": { "lift_feature": "wow_apple_streams", "is_trending_lambda": lambda df: df["wow_apple_streams"] >= LIFT_THRESHOLD, }, "shazam": { "lift_feature": "wow_apple_streams", "is_trending_lambda": lambda df: False, }, # tiktok trending flag "tiktok_creations_country": { "lift_feature": "wow_tt_creations", "is_trending_lambda": lambda df: ( (df["tt_creations"] >= 100) & (df["wow_tt_creations"] >= LIFT_THRESHOLD) ), }, "tiktok_creations_global": { "lift_feature": "wow_tt_creations", "is_trending_lambda": lambda df: False, }, "tiktok_views_country": { "lift_feature": "wow_tt_views", "is_trending_lambda": lambda df: ( (df["tt_views"] > 100000) & (df["wow_tt_views"] >= LIFT_THRESHOLD) ), }, "tiktok_views_global": { "lift_feature": "wow_tt_views", "is_trending_lambda": lambda df: False, }, "tiktok_likes_country": { "lift_feature": "wow_tt_likes", "is_trending_lambda": lambda df: ( (df["tt_likes"] > 10000) & (df["wow_tt_likes"] >= LIFT_THRESHOLD) ), }, "tiktok_likes_global": { "lift_feature": "wow_tt_likes", "is_trending_lambda": lambda df: False, }, # instagram - only stubs "insta_reels_views_country": { "lift_feature": None, "is_trending_lambda": lambda df: False, }, "insta_reels_views_global": { "lift_feature": None, "is_trending_lambda": lambda df: False, }, "insta_reels_creations_country": { "lift_feature": None, "is_trending_lambda": lambda df: False, }, "insta_reels_creations_global": { "lift_feature": None, "is_trending_lambda": lambda df: False, }, }