from pathlib import Path MODEL_VERSION = Path(__file__).parent.name assert MODEL_VERSION == 't2604_noamazon_noyoutube' SNOWFLAKE_REGISTRY_MODEL_NAME = 'TADAS30' SNOWFLAKE_REGISTRY_MODEL_VERSION = 'NOAMAZON_NOYOUTUBE' REQUIRED_DSPS = ['spotify', 'apple', 'tiktok'] # features from # https://app.snowflake.com/sme/orchard/#/data/databases/FACTS/schemas/QA/model/TADAS30/version/NOAMAZON_NOYOUTUBE/version-details 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 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 """.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': { 'total_streams': dict(column='STREAMS'), 'total_skips': dict(column='SKIPS'), 'total_saves': dict(column='SAVES'), 'total_streams_passive': dict(column='STREAMS_PASSIVE'), 'total_streams_active': dict(column='STREAMS_ACTIVE'), 'total_collection_streams': dict(column='STREAMS_COLLECTION'), 'total_paid_streams': dict(column='SUB_TYPE_SUBSCRIPTION'), 'total_free_streams': dict(column='SUB_TYPE_ADSUPPORTED'), '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_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'), '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': { '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, }, }