from pathlib import Path MODEL_VERSION = Path(__file__).parent.name assert MODEL_VERSION == 't2509_snowflake' SNOWFLAKE_REGISTRY_MODEL_NAME = 'TADAS30' SNOWFLAKE_REGISTRY_MODEL_VERSION = 'DEV_V1' REQUIRED_DSPS = ['spotify', 'apple', 'tiktok', 'amazon'] # feature from # https://app.snowflake.com/sme/orchard/#/data/databases/FACTS/schemas/DEV/model/TADAS_30_FYQ225/version/DEV_V1 ML_MODEL_FEATURES = [ 'TOTAL_STREAMS', 'TOTAL_SKIPS', 'TOTAL_SAVES', 'TOTAL_STREAMS_PASSIVE', 'TOTAL_STREAMS_ACTIVE', 'TOTAL_COLLECTION_STREAMS', 'TOTAL_PAID_STREAMS', 'TOTAL_FREE_STREAMS', 'SPOTIFY_COLLECTION_STREAMS', 'SPOTIFY_PLAYQUEUE_STREAMS', 'SPOTIFY_ALBUM_PAGE_STREAMS', 'SPOTIFY_ARTIST_PAGE_STREAMS', 'SPOTIFY_SEARCH_STREAMS', 'SPOTIFY_PLAYLIST_STREAMS', 'SPOTIFY_RELEASE_RADAR_STREAMS', 'SPOTIFY_DISCOVER_WEEKLY_STREAMS', 'SPOTIFY_RADIO_STREAMS', 'SPOTIFY_DAILYMIX_STREAMS', 'SPOTIFY_CHART_STREAMS', 'SPOTIFY_OTHER_STREAMS', 'APPLE_LIBRARY_STREAMS', 'APPLE_EXTERNAL_STREAMS', 'APPLE_VOICE_STREAMS', 'APPLE_SEARCH_STREAMS', 'APPLE_MUSICKIT_STREAMS', 'APPLE_NOWPLAYING_STREAMS', 'APPLE_DISCOVERY_STREAMS', 'APPLE_OTHER_STREAMS', 'AMAZON_USERPLAYLIST_STREAMS', 'AMAZON_SONG_STREAMS', 'AMAZON_ALBUM_STREAMS', 'AMAZON_ARTIST_STREAMS', 'AMAZON_SEARCH_STREAMS', 'AMAZON_PLAYLIST_STREAMS', 'AMAZON_STATION_STREAMS', 'YEST_TOTAL_STREAMS', 'YEST_TOTAL_SKIPS', 'YEST_TOTAL_SAVES', 'YEST_TOTAL_STREAMS_PASSIVE', 'YEST_TOTAL_STREAMS_ACTIVE', 'YEST_TOTAL_COLLECTION_STREAMS', 'YEST_TOTAL_PAID_STREAMS', 'YEST_TOTAL_FREE_STREAMS', 'YEST_SPOTIFY_COLLECTION_STREAMS', 'YEST_SPOTIFY_PLAYQUEUE_STREAMS', 'YEST_SPOTIFY_ALBUM_PAGE_STREAMS', 'YEST_SPOTIFY_ARTIST_PAGE_STREAMS', 'YEST_SPOTIFY_SEARCH_STREAMS', 'YEST_SPOTIFY_PLAYLIST_STREAMS', 'YEST_SPOTIFY_RELEASE_RADAR_STREAMS', 'YEST_SPOTIFY_DISCOVER_WEEKLY_STREAMS', 'YEST_SPOTIFY_RADIO_STREAMS', 'YEST_SPOTIFY_DAILYMIX_STREAMS', 'YEST_SPOTIFY_CHART_STREAMS', 'YEST_SPOTIFY_OTHER_STREAMS', 'YEST_APPLE_LIBRARY_STREAMS', 'YEST_APPLE_EXTERNAL_STREAMS', 'YEST_APPLE_VOICE_STREAMS', 'YEST_APPLE_SEARCH_STREAMS', 'YEST_APPLE_MUSICKIT_STREAMS', 'YEST_APPLE_NOWPLAYING_STREAMS', 'YEST_APPLE_DISCOVERY_STREAMS', 'YEST_APPLE_OTHER_STREAMS', 'YEST_AMAZON_USERPLAYLIST_STREAMS', 'YEST_AMAZON_SONG_STREAMS', 'YEST_AMAZON_ALBUM_STREAMS', 'YEST_AMAZON_ARTIST_STREAMS', 'YEST_AMAZON_SEARCH_STREAMS', 'YEST_AMAZON_PLAYLIST_STREAMS', 'YEST_AMAZON_STATION_STREAMS', 'SEVEN_TOTAL_STREAMS', 'SEVEN_TOTAL_SKIPS', 'SEVEN_TOTAL_SAVES', 'SEVEN_TOTAL_STREAMS_PASSIVE', 'SEVEN_TOTAL_STREAMS_ACTIVE', 'SEVEN_TOTAL_COLLECTION_STREAMS', 'SEVEN_TOTAL_PAID_STREAMS', 'SEVEN_TOTAL_FREE_STREAMS', 'SEVEN_SPOTIFY_COLLECTION_STREAMS', 'SEVEN_SPOTIFY_PLAYQUEUE_STREAMS', 'SEVEN_SPOTIFY_ALBUM_PAGE_STREAMS', 'SEVEN_SPOTIFY_ARTIST_PAGE_STREAMS', 'SEVEN_SPOTIFY_SEARCH_STREAMS', 'SEVEN_SPOTIFY_PLAYLIST_STREAMS', 'SEVEN_SPOTIFY_RELEASE_RADAR_STREAMS', 'SEVEN_SPOTIFY_DISCOVER_WEEKLY_STREAMS', 'SEVEN_SPOTIFY_RADIO_STREAMS', 'SEVEN_SPOTIFY_DAILYMIX_STREAMS', 'SEVEN_SPOTIFY_CHART_STREAMS', 'SEVEN_SPOTIFY_OTHER_STREAMS', 'SEVEN_APPLE_LIBRARY_STREAMS', 'SEVEN_APPLE_EXTERNAL_STREAMS', 'SEVEN_APPLE_VOICE_STREAMS', 'SEVEN_APPLE_SEARCH_STREAMS', 'SEVEN_APPLE_MUSICKIT_STREAMS', 'SEVEN_APPLE_NOWPLAYING_STREAMS', 'SEVEN_APPLE_DISCOVERY_STREAMS', 'SEVEN_APPLE_OTHER_STREAMS', 'SEVEN_AMAZON_USERPLAYLIST_STREAMS', 'SEVEN_AMAZON_SONG_STREAMS', 'SEVEN_AMAZON_ALBUM_STREAMS', 'SEVEN_AMAZON_ARTIST_STREAMS', 'SEVEN_AMAZON_SEARCH_STREAMS', 'SEVEN_AMAZON_PLAYLIST_STREAMS', 'SEVEN_AMAZON_STATION_STREAMS', 'TT_CREATIONS', 'TT_FAVORITES', 'TT_LIKES', 'TT_SHARES', 'TT_VIEWS', 'YEST_TT_CREATIONS', 'YEST_TT_FAVORITES', 'YEST_TT_LIKES', 'YEST_TT_SHARES', 'YEST_TT_VIEWS', 'SEVEN_TT_CREATIONS', 'SEVEN_TT_FAVORITES', 'SEVEN_TT_LIKES', 'SEVEN_TT_SHARES', 'SEVEN_TT_VIEWS', 'CONSECUTIVE_TREND', 'WOW_TOTAL_STREAMS', 'DOD_TOTAL_STREAMS', 'WOW_TOTAL_SKIPS', 'DOD_TOTAL_SKIPS', 'WOW_TOTAL_SAVES', 'DOD_TOTAL_SAVES', 'WOW_TOTAL_STREAMS_PASSIVE', 'DOD_TOTAL_STREAMS_PASSIVE', 'WOW_TOTAL_STREAMS_ACTIVE', 'DOD_TOTAL_STREAMS_ACTIVE', 'WOW_TOTAL_COLLECTION_STREAMS', 'DOD_TOTAL_COLLECTION_STREAMS', 'WOW_TOTAL_PAID_STREAMS', 'DOD_TOTAL_PAID_STREAMS', 'WOW_TOTAL_FREE_STREAMS', 'DOD_TOTAL_FREE_STREAMS', 'WOW_SPOTIFY_COLLECTION_STREAMS', 'DOD_SPOTIFY_COLLECTION_STREAMS', 'WOW_SPOTIFY_PLAYQUEUE_STREAMS', 'DOD_SPOTIFY_PLAYQUEUE_STREAMS', 'WOW_SPOTIFY_ALBUM_PAGE_STREAMS', 'DOD_SPOTIFY_ALBUM_PAGE_STREAMS', 'WOW_SPOTIFY_ARTIST_PAGE_STREAMS', 'DOD_SPOTIFY_ARTIST_PAGE_STREAMS', 'WOW_SPOTIFY_SEARCH_STREAMS', 'DOD_SPOTIFY_SEARCH_STREAMS', 'WOW_SPOTIFY_PLAYLIST_STREAMS', 'DOD_SPOTIFY_PLAYLIST_STREAMS', 'WOW_SPOTIFY_RELEASE_RADAR_STREAMS', 'DOD_SPOTIFY_RELEASE_RADAR_STREAMS', 'WOW_SPOTIFY_DISCOVER_WEEKLY_STREAMS', 'DOD_SPOTIFY_DISCOVER_WEEKLY_STREAMS', 'WOW_SPOTIFY_RADIO_STREAMS', 'DOD_SPOTIFY_RADIO_STREAMS', 'WOW_SPOTIFY_DAILYMIX_STREAMS', 'DOD_SPOTIFY_DAILYMIX_STREAMS', 'WOW_SPOTIFY_CHART_STREAMS', 'DOD_SPOTIFY_CHART_STREAMS', 'WOW_SPOTIFY_OTHER_STREAMS', 'DOD_SPOTIFY_OTHER_STREAMS', 'WOW_APPLE_LIBRARY_STREAMS', 'DOD_APPLE_LIBRARY_STREAMS', 'WOW_APPLE_EXTERNAL_STREAMS', 'DOD_APPLE_EXTERNAL_STREAMS', 'WOW_APPLE_VOICE_STREAMS', 'DOD_APPLE_VOICE_STREAMS', 'WOW_APPLE_SEARCH_STREAMS', 'DOD_APPLE_SEARCH_STREAMS', 'WOW_APPLE_MUSICKIT_STREAMS', 'DOD_APPLE_MUSICKIT_STREAMS', 'WOW_APPLE_NOWPLAYING_STREAMS', 'DOD_APPLE_NOWPLAYING_STREAMS', 'WOW_APPLE_DISCOVERY_STREAMS', 'DOD_APPLE_DISCOVERY_STREAMS', 'WOW_APPLE_OTHER_STREAMS', 'DOD_APPLE_OTHER_STREAMS', 'WOW_AMAZON_USERPLAYLIST_STREAMS', 'DOD_AMAZON_USERPLAYLIST_STREAMS', 'WOW_AMAZON_SONG_STREAMS', 'DOD_AMAZON_SONG_STREAMS', 'WOW_AMAZON_ALBUM_STREAMS', 'DOD_AMAZON_ALBUM_STREAMS', 'WOW_AMAZON_ARTIST_STREAMS', 'DOD_AMAZON_ARTIST_STREAMS', 'WOW_AMAZON_SEARCH_STREAMS', 'DOD_AMAZON_SEARCH_STREAMS', 'WOW_AMAZON_PLAYLIST_STREAMS', 'DOD_AMAZON_PLAYLIST_STREAMS', 'WOW_AMAZON_STATION_STREAMS', 'DOD_AMAZON_STATION_STREAMS', 'WOW_TT_CREATIONS', 'DOD_TT_CREATIONS', 'WOW_TT_FAVORITES', 'DOD_TT_FAVORITES', 'WOW_TT_LIKES', 'DOD_TT_LIKES', 'WOW_TT_SHARES', 'DOD_TT_SHARES', 'WOW_TT_VIEWS', 'DOD_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_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_TT_CREATIONS', 'ADJ_DOD_TT_FAVORITES', 'ADJ_DOD_TT_LIKES', 'ADJ_DOD_TT_SHARES', 'ADJ_DOD_TT_VIEWS', ] 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'), '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'), '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, }, }