import json from datetime import datetime from typing import Any, Dict, List, Mapping from analytics.constants import cache from analytics.constants.account_summary_field_mappings import ( ACCOUNT_SUMMARY_FIELD_MAPPINGS, ) from analytics.logic import data_availability from analytics.logic.permissions import get_cross_attribution_pairs from analytics.queries.account import ( AccountDownloadsTimeSeries, AccountStreamsTimeSeries, AccountSummary, AccountSummaryDefaultParams, AccountVideoSummary, AccountVideoTimeseries, ) from analytics.queries.format import format_row from analytics.utils.cache import cache_in_redis from analytics.utils.streams import calc_date_skip_rate TOTAL_SUMMARY_TYPE = "TOTAL" TOTAL_TIMESERIES_TYPE = "ACCOUNT_STREAMS" AGGREGATION_FIELDS_TIMESERIES = { "ACCOUNT_STREAMS_BY_COUNTRY": "country_code", "ACCOUNT_STREAMS_BY_IMPRINT": "dr.imprint", "ACCOUNT_STREAMS_BY_PARTICIPANT": "m.global_participant_id", "ACCOUNT_STREAMS_BY_PRODUCT": "dr.product_id", "ACCOUNT_STREAMS_BY_PROJECT": "dr.projectid", "ACCOUNT_STREAMS_BY_STORE": "store_id", "ACCOUNT_STREAMS_BY_SUBACCOUNT": "subaccount_id", "ACCOUNT_STREAMS_BY_TRACK": "tracks.isrc", "ACCOUNT_STREAMS_BY_SOUND_RECORDING_FAMILY": "prod_fam_no", # ACCOUNT_STREAMS_BY_SUBSCRIPTION is implemented in Python (_breakdown_by) # ACCOUNT_STREAMS_BY_SOS is implemented in Python (_breakdown_by) "ACCOUNT_ALBUM_DOWNLOADS_BY_COUNTRY": "country_code", "ACCOUNT_ALBUM_DOWNLOADS_BY_IMPRINT": "dr.imprint", "ACCOUNT_ALBUM_DOWNLOADS_BY_PARTICIPANT": "m.global_participant_id", "ACCOUNT_ALBUM_DOWNLOADS_BY_PRODUCT": "dr.product_id", "ACCOUNT_ALBUM_DOWNLOADS_BY_PROJECT": "dr.projectid", "ACCOUNT_ALBUM_DOWNLOADS_BY_STORE": "store_id", "ACCOUNT_ALBUM_DOWNLOADS_BY_SUBACCOUNT": "dr.subaccountid", "ACCOUNT_TRACK_DOWNLOADS_BY_COUNTRY": "country_code", "ACCOUNT_TRACK_DOWNLOADS_BY_IMPRINT": "dr.imprint", "ACCOUNT_TRACK_DOWNLOADS_BY_PARTICIPANT": "m.global_participant_id", "ACCOUNT_TRACK_DOWNLOADS_BY_PRODUCT": "dr.product_id", "ACCOUNT_TRACK_DOWNLOADS_BY_PROJECT": "dr.projectid", "ACCOUNT_TRACK_DOWNLOADS_BY_STORE": "store_id", "ACCOUNT_TRACK_DOWNLOADS_BY_SUBACCOUNT": "dr.subaccountid", "ACCOUNT_TRACK_DOWNLOADS_BY_TRACK": "tracks.isrc", "ACCOUNT_TRACK_DOWNLOADS_BY_SOUND_RECORDING_FAMILY": "prod_fam_no", } AGGREGATION_FIELDS_SUMMARY = { "vendor": { "TOTAL": "label_id", "STORE": "store_id", "TRACK": "isrc", "SOUND_RECORDING_FAMILY": "prod_fam_no", "COUNTRY": "country_code", "IMPRINT": "imprint", "PARTICIPANT": "m.global_participant_id", "PRODUCT": "product_id", "PROJECT": "dr.projectid", "SUBACCOUNT": "subaccount_id", "SOS": "label_id", "SUBSCRIPTION": "label_id", }, "subaccount": { "TOTAL": "subaccount_id", "STORE": "store_id", "TRACK": "isrc", "SOUND_RECORDING_FAMILY": "prod_fam_no", "COUNTRY": "country_code", "IMPRINT": "imprint", "PARTICIPANT": "m.global_participant_id", "PRODUCT": "product_id", "PROJECT": "dr.projectid", "SUBACCOUNT": "subaccount_id", "SOS": "subaccount_id", "SUBSCRIPTION": "subaccount_id", }, } # map of type -> aggregation type -> table ACCOUNT_TABLES_TIMESERIES = { "ACCOUNT_STREAMS": { "total": "V_STREAMS_BY_ACCOUNT_TRACK_FEED_DISTRIBUTOR_DAILY", "country": "V_STREAMS_BY_ACCOUNT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY", }, "ACCOUNT_STREAMS_BY_COUNTRY": { "total": "V_STREAMS_BY_ACCOUNT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY", "country": "V_STREAMS_BY_ACCOUNT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY", }, "ACCOUNT_STREAMS_BY_IMPRINT": { "total": "V_STREAMS_BY_ACCOUNT_TRACK_FEED_DISTRIBUTOR_DAILY", "country": "V_STREAMS_BY_ACCOUNT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY", }, "ACCOUNT_STREAMS_BY_PARTICIPANT": { "total": "V_STREAMS_BY_ACCOUNT_TRACK_FEED_DISTRIBUTOR_DAILY", "country": "V_STREAMS_BY_ACCOUNT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY", }, "ACCOUNT_STREAMS_BY_PRODUCT": { "total": "V_STREAMS_BY_ACCOUNT_TRACK_FEED_DISTRIBUTOR_DAILY", "country": "V_STREAMS_BY_ACCOUNT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY", }, "ACCOUNT_STREAMS_BY_PROJECT": { "total": "V_STREAMS_BY_ACCOUNT_TRACK_FEED_DISTRIBUTOR_DAILY", "country": "V_STREAMS_BY_ACCOUNT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY", }, "ACCOUNT_STREAMS_BY_STORE": { "total": "V_STREAMS_BY_ACCOUNT_TRACK_FEED_DISTRIBUTOR_DAILY", "country": "V_STREAMS_BY_ACCOUNT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY", }, "ACCOUNT_STREAMS_BY_SUBACCOUNT": { "total": "V_STREAMS_BY_ACCOUNT_TRACK_FEED_DISTRIBUTOR_DAILY", "country": "V_STREAMS_BY_ACCOUNT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY", }, "ACCOUNT_STREAMS_BY_TRACK": { "total": "V_STREAMS_BY_ACCOUNT_TRACK_FEED_DISTRIBUTOR_DAILY", "country": "V_STREAMS_BY_ACCOUNT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY", }, "ACCOUNT_STREAMS_BY_SOUND_RECORDING_FAMILY": { "total": "V_STREAMS_BY_ACCOUNT_TRACK_FEED_DISTRIBUTOR_DAILY", "country": "V_STREAMS_BY_ACCOUNT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY", }, "ACCOUNT_STREAMS_BY_SUBSCRIPTION": { "total": "V_STREAMS_BY_ACCOUNT_TRACK_FEED_DISTRIBUTOR_DAILY", "country": "V_STREAMS_BY_ACCOUNT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY", }, "ACCOUNT_STREAMS_BY_SOS": { "total": "V_STREAMS_BY_ACCOUNT_TRACK_FEED_DISTRIBUTOR_DAILY", "country": "V_STREAMS_BY_ACCOUNT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY", }, "ACCOUNT_ALBUM_DOWNLOADS": { "total": "DOWNLOADS_BY_PRODUCT_FEED_DISTRIBUTOR_DAILY", "country": "DOWNLOADS_BY_PRODUCT_COUNTRY_FEED_DISTRIBUTOR_DAILY", }, "ACCOUNT_ALBUM_DOWNLOADS_BY_COUNTRY": { "total": "DOWNLOADS_BY_PRODUCT_COUNTRY_FEED_DISTRIBUTOR_DAILY", "country": "DOWNLOADS_BY_PRODUCT_COUNTRY_FEED_DISTRIBUTOR_DAILY", }, "ACCOUNT_ALBUM_DOWNLOADS_BY_IMPRINT": { "total": "DOWNLOADS_BY_PRODUCT_FEED_DISTRIBUTOR_DAILY", "country": "DOWNLOADS_BY_PRODUCT_COUNTRY_FEED_DISTRIBUTOR_DAILY", }, "ACCOUNT_ALBUM_DOWNLOADS_BY_PARTICIPANT": { "total": "DOWNLOADS_BY_PRODUCT_FEED_DISTRIBUTOR_DAILY", "country": "DOWNLOADS_BY_PRODUCT_COUNTRY_FEED_DISTRIBUTOR_DAILY", }, "ACCOUNT_ALBUM_DOWNLOADS_BY_PRODUCT": { "total": "DOWNLOADS_BY_PRODUCT_FEED_DISTRIBUTOR_DAILY", "country": "DOWNLOADS_BY_PRODUCT_COUNTRY_FEED_DISTRIBUTOR_DAILY", }, "ACCOUNT_ALBUM_DOWNLOADS_BY_PROJECT": { "total": "DOWNLOADS_BY_PRODUCT_FEED_DISTRIBUTOR_DAILY", "country": "DOWNLOADS_BY_PRODUCT_COUNTRY_FEED_DISTRIBUTOR_DAILY", }, "ACCOUNT_ALBUM_DOWNLOADS_BY_STORE": { "total": "DOWNLOADS_BY_PRODUCT_FEED_DISTRIBUTOR_DAILY", "country": "DOWNLOADS_BY_PRODUCT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY", }, "ACCOUNT_ALBUM_DOWNLOADS_BY_SUBACCOUNT": { "total": "DOWNLOADS_BY_PRODUCT_FEED_DISTRIBUTOR_DAILY", "country": "DOWNLOADS_BY_PRODUCT_COUNTRY_FEED_DISTRIBUTOR_DAILY", }, "ACCOUNT_TRACK_DOWNLOADS": { "total": "DOWNLOADS_BY_TRACK_FEED_DISTRIBUTOR_DAILY", "country": "DOWNLOADS_BY_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY", }, "ACCOUNT_TRACK_DOWNLOADS_BY_COUNTRY": { "total": "DOWNLOADS_BY_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY", "country": "DOWNLOADS_BY_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY", }, "ACCOUNT_TRACK_DOWNLOADS_BY_IMPRINT": { "total": "DOWNLOADS_BY_TRACK_FEED_DISTRIBUTOR_DAILY", "country": "DOWNLOADS_BY_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY", }, "ACCOUNT_TRACK_DOWNLOADS_BY_PARTICIPANT": { "total": "DOWNLOADS_BY_TRACK_FEED_DISTRIBUTOR_DAILY", "country": "DOWNLOADS_BY_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY", }, "ACCOUNT_TRACK_DOWNLOADS_BY_PRODUCT": { "total": "DOWNLOADS_BY_TRACK_FEED_DISTRIBUTOR_DAILY", "country": "DOWNLOADS_BY_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY", }, "ACCOUNT_TRACK_DOWNLOADS_BY_PROJECT": { "total": "DOWNLOADS_BY_TRACK_FEED_DISTRIBUTOR_DAILY", "country": "DOWNLOADS_BY_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY", }, "ACCOUNT_TRACK_DOWNLOADS_BY_STORE": { "total": "DOWNLOADS_BY_PRODUCT_TRACK_FEED_DISTRIBUTOR_DAILY", "country": "DOWNLOADS_BY_PRODUCT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY", }, "ACCOUNT_TRACK_DOWNLOADS_BY_SUBACCOUNT": { "total": "DOWNLOADS_BY_TRACK_FEED_DISTRIBUTOR_DAILY", "country": "DOWNLOADS_BY_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY", }, "ACCOUNT_TRACK_DOWNLOADS_BY_TRACK": { "total": "DOWNLOADS_BY_PRODUCT_TRACK_FEED_DISTRIBUTOR_DAILY", "country": "DOWNLOADS_BY_PRODUCT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY", }, "ACCOUNT_TRACK_DOWNLOADS_BY_SOUND_RECORDING_FAMILY": { "total": "DOWNLOADS_BY_PRODUCT_TRACK_FEED_DISTRIBUTOR_DAILY", "country": "DOWNLOADS_BY_PRODUCT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY", }, } # map of type -> aggregation type -> dict of tables required ACCOUNT_TABLES_SUMMARY = { # Account streams "TOTAL": { "total": { "streams_table": "V_STREAMS_BY_ACCOUNT_TRACK_FEED_DISTRIBUTOR_DAILY", # noqa "account_album_downloads_table": "DOWNLOADS_BY_PRODUCT_FEED_DISTRIBUTOR_DAILY", # noqa "account_track_downloads_table": "DOWNLOADS_BY_PRODUCT_TRACK_FEED_DISTRIBUTOR_DAILY", # noqa }, "country": { "streams_table": "V_STREAMS_BY_ACCOUNT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY", # noqa "account_album_downloads_table": "DOWNLOADS_BY_PRODUCT_COUNTRY_FEED_DISTRIBUTOR_DAILY", # noqa "account_track_downloads_table": "DOWNLOADS_BY_PRODUCT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY", # noqa }, }, "STORE": { "total": { "streams_table": "V_STREAMS_BY_ACCOUNT_TRACK_FEED_DISTRIBUTOR_DAILY", # noqa "account_album_downloads_table": "DOWNLOADS_BY_PRODUCT_FEED_DISTRIBUTOR_DAILY", # noqa "account_track_downloads_table": "DOWNLOADS_BY_PRODUCT_TRACK_FEED_DISTRIBUTOR_DAILY", # noqa }, "country": { "streams_table": "V_STREAMS_BY_ACCOUNT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY", # noqa "account_album_downloads_table": "DOWNLOADS_BY_PRODUCT_COUNTRY_FEED_DISTRIBUTOR_DAILY", # noqa "account_track_downloads_table": "DOWNLOADS_BY_PRODUCT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY", # noqa }, }, "IMPRINT": { "total": { "streams_table": "V_STREAMS_BY_ACCOUNT_TRACK_FEED_DISTRIBUTOR_DAILY", # noqa "account_album_downloads_table": "DOWNLOADS_BY_PRODUCT_FEED_DISTRIBUTOR_DAILY", # noqa "account_track_downloads_table": "DOWNLOADS_BY_PRODUCT_TRACK_FEED_DISTRIBUTOR_DAILY", # noqa }, "country": { "streams_table": "V_STREAMS_BY_ACCOUNT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY", # noqa "account_album_downloads_table": "DOWNLOADS_BY_PRODUCT_COUNTRY_FEED_DISTRIBUTOR_DAILY", # noqa "account_track_downloads_table": "DOWNLOADS_BY_PRODUCT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY", # noqa }, }, "TRACK": { "total": { "streams_table": "V_STREAMS_BY_ACCOUNT_TRACK_FEED_DISTRIBUTOR_DAILY", # noqa "account_album_downloads_table": "DOWNLOADS_BY_PRODUCT_FEED_DISTRIBUTOR_DAILY", # noqa "account_track_downloads_table": "DOWNLOADS_BY_PRODUCT_TRACK_FEED_DISTRIBUTOR_DAILY", # noqa }, "country": { "streams_table": "V_STREAMS_BY_ACCOUNT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY", # noqa "account_album_downloads_table": "DOWNLOADS_BY_PRODUCT_COUNTRY_FEED_DISTRIBUTOR_DAILY", # noqa "account_track_downloads_table": "DOWNLOADS_BY_PRODUCT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY", # noqa }, }, "SOUND_RECORDING_FAMILY": { "total": { "streams_table": "V_STREAMS_BY_ACCOUNT_TRACK_FEED_DISTRIBUTOR_DAILY", # noqa "account_album_downloads_table": "DOWNLOADS_BY_PRODUCT_FEED_DISTRIBUTOR_DAILY", # noqa "account_track_downloads_table": "DOWNLOADS_BY_PRODUCT_TRACK_FEED_DISTRIBUTOR_DAILY", # noqa }, "country": { "streams_table": "V_STREAMS_BY_ACCOUNT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY", # noqa "account_album_downloads_table": "DOWNLOADS_BY_PRODUCT_COUNTRY_FEED_DISTRIBUTOR_DAILY", # noqa "account_track_downloads_table": "DOWNLOADS_BY_PRODUCT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY", # noqa }, }, "COUNTRY": { "total": { "streams_table": "V_STREAMS_BY_ACCOUNT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY", # noqa "account_album_downloads_table": "DOWNLOADS_BY_PRODUCT_COUNTRY_FEED_DISTRIBUTOR_DAILY", # noqa "account_track_downloads_table": "DOWNLOADS_BY_PRODUCT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY", # noqa }, "country": { "streams_table": "V_STREAMS_BY_ACCOUNT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY", # noqa "account_album_downloads_table": "DOWNLOADS_BY_PRODUCT_COUNTRY_FEED_DISTRIBUTOR_DAILY", # noqa "account_track_downloads_table": "DOWNLOADS_BY_PRODUCT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY", # noqa }, }, "PARTICIPANT": { "total": { "streams_table": "V_STREAMS_BY_ACCOUNT_TRACK_FEED_DISTRIBUTOR_DAILY", "account_album_downloads_table": "DOWNLOADS_BY_PRODUCT_FEED_DISTRIBUTOR_DAILY", # noqa "account_track_downloads_table": "DOWNLOADS_BY_TRACK_FEED_DISTRIBUTOR_DAILY", # noqa }, "country": { "streams_table": "V_STREAMS_BY_ACCOUNT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY", # noqa "account_album_downloads_table": "DOWNLOADS_BY_PRODUCT_COUNTRY_FEED_DISTRIBUTOR_DAILY", # noqa "account_track_downloads_table": "DOWNLOADS_BY_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY", # noqa }, }, "PRODUCT": { "total": { "streams_table": "V_STREAMS_BY_ACCOUNT_TRACK_FEED_DISTRIBUTOR_DAILY", # noqa "account_album_downloads_table": "DOWNLOADS_BY_PRODUCT_FEED_DISTRIBUTOR_DAILY", # noqa "account_track_downloads_table": "DOWNLOADS_BY_PRODUCT_TRACK_FEED_DISTRIBUTOR_DAILY", # noqa }, "country": { "streams_table": "V_STREAMS_BY_ACCOUNT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY", # noqa "account_album_downloads_table": "DOWNLOADS_BY_PRODUCT_COUNTRY_FEED_DISTRIBUTOR_DAILY", # noqa "account_track_downloads_table": "DOWNLOADS_BY_PRODUCT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY", # noqa }, }, "PROJECT": { "total": { "streams_table": "V_STREAMS_BY_ACCOUNT_TRACK_FEED_DISTRIBUTOR_DAILY", # noqa "account_album_downloads_table": "DOWNLOADS_BY_PRODUCT_FEED_DISTRIBUTOR_DAILY", # noqa "account_track_downloads_table": "DOWNLOADS_BY_PRODUCT_TRACK_FEED_DISTRIBUTOR_DAILY", # noqa }, "country": { "streams_table": "V_STREAMS_BY_ACCOUNT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY", # noqa "account_album_downloads_table": "DOWNLOADS_BY_PRODUCT_COUNTRY_FEED_DISTRIBUTOR_DAILY", # noqa "account_track_downloads_table": "DOWNLOADS_BY_PRODUCT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY", # noqa }, }, "SUBACCOUNT": { "total": { "streams_table": "V_STREAMS_BY_ACCOUNT_TRACK_FEED_DISTRIBUTOR_DAILY", # noqa "account_album_downloads_table": "DOWNLOADS_BY_PRODUCT_FEED_DISTRIBUTOR_DAILY", # noqa "account_track_downloads_table": "DOWNLOADS_BY_PRODUCT_TRACK_FEED_DISTRIBUTOR_DAILY", # noqa }, "country": { "streams_table": "V_STREAMS_BY_ACCOUNT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY", # noqa "account_album_downloads_table": "DOWNLOADS_BY_PRODUCT_COUNTRY_FEED_DISTRIBUTOR_DAILY", # noqa "account_track_downloads_table": "DOWNLOADS_BY_PRODUCT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY", # noqa }, }, "SOS": { "total": { "streams_table": "V_STREAMS_BY_ACCOUNT_TRACK_FEED_DISTRIBUTOR_DAILY", # noqa "account_album_downloads_table": "DOWNLOADS_BY_PRODUCT_FEED_DISTRIBUTOR_DAILY", # noqa "account_track_downloads_table": "DOWNLOADS_BY_PRODUCT_TRACK_FEED_DISTRIBUTOR_DAILY", # noqa }, "country": { "streams_table": "V_STREAMS_BY_ACCOUNT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY", # noqa "account_album_downloads_table": "DOWNLOADS_BY_PRODUCT_COUNTRY_FEED_DISTRIBUTOR_DAILY", # noqa "account_track_downloads_table": "DOWNLOADS_BY_PRODUCT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY", # noqa }, }, "SUBSCRIPTION": { "total": { "streams_table": "V_STREAMS_BY_ACCOUNT_TRACK_FEED_DISTRIBUTOR_DAILY", # noqa "account_album_downloads_table": "DOWNLOADS_BY_PRODUCT_FEED_DISTRIBUTOR_DAILY", # noqa "account_track_downloads_table": "DOWNLOADS_BY_PRODUCT_TRACK_FEED_DISTRIBUTOR_DAILY", # noqa }, "country": { "streams_table": "V_STREAMS_BY_ACCOUNT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY", # noqa "account_album_downloads_table": "DOWNLOADS_BY_PRODUCT_COUNTRY_FEED_DISTRIBUTOR_DAILY", # noqa "account_track_downloads_table": "DOWNLOADS_BY_PRODUCT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY", # noqa }, }, } ACCOUNT_VIDEO_AGGREGATION_FIELDS = { "vendor": { "TOTAL": "label_id", "TRACK": "isrc", "COUNTRY": "country_code", "PARTICIPANT": "global_participant_id", "SUBACCOUNT": "subaccount_id", "VIDEO": "video_id", }, "subaccount": { "TOTAL": "subaccount_id", "TRACK": "isrc", "COUNTRY": "country_code", "PARTICIPANT": "global_participant_id", "VIDEO": "video_id", }, } ACCOUNT_VIDEO_AGGREGATION_FIELDS_PREFIXES = { "vendor": { "TOTAL": "v.", "TRACK": "video_track.", "COUNTRY": "v.", "PARTICIPANT": "video_participant.", "SUBACCOUNT": "", "VIDEO": "v.", }, "subaccount": { "TOTAL": "", "TRACK": "video_track.", "COUNTRY": "v.", "PARTICIPANT": "video_participant.", "VIDEO": "v.", }, } ACCOUNT_VIDEO_TABLES = { "TOTAL": "V_VIEWS_BY_ACCOUNT_VIDEO_DISTRIBUTOR_DAILY", "COUNTRIES": "V_VIEWS_BY_ACCOUNT_VIDEO_COUNTRY_DISTRIBUTOR_DAILY", } ACCOUNT_VIDEO_VIEWS_TYPE_KEY = { "ALL": "VIEWS", "SUBSCRIPTION": "PREMIUM_VIEWS", } ACCOUNT_VIDEO_SUMMARY_MAPPING = { "SUBACCOUNT": { "label_id": "label_id", "subaccount_id": "subaccount_id", "views": "views", }, "TOTAL": {"views": "views"}, "TRACK_FAMILY": { "isrc": "isrc", "prod_fam_no": "prod_fam_no", "views": "views", }, } ACCOUNT_VIDEO_TIMESERIES_MAPPING = { "SUBACCOUNT": { "label_id": "label_id", "date": "download_activity_date", "subaccount_id": "id", "views": "views", }, "TOTAL": { "views": "views", "date": "download_activity_date", }, "TRACK_FAMILY": { "isrc": "isrc", "prod_fam_no": "prod_fam_no", "views": "views", "date": "download_activity_date", }, } def _format_response(data, mapping): result = [] for data_point in data: data_point = format_row(data_point) result.append( { result_key: data_point.get(data_key) for result_key, data_key in mapping.items() } ) return result def _breakdown_by(item: Dict, key="value", by=None, timeseries=True, sources=None): if by == "sos": sources = sources or ["active", "passive", "collection", "unknown"] field_template = "streams_{id}" elif by == "subscription": sources = sources or ["subscription", "adsupported", "midtier"] field_template = "sub_type_{id}" def _breakdown(item, id): breakdown = { "id": id, key: item.get(field_template.format(id=id)), } if timeseries: breakdown["date"] = item["date"] return breakdown return [_breakdown(item, source) for source in sources] def get_account_table_for_timeseries(query_type: str, countries: List) -> str: key = "total" if len(countries) != 0: key = "country" table_options = ACCOUNT_TABLES_TIMESERIES.get(query_type) if not table_options: raise NotImplementedError(f"Query {query_type} not supported") table = table_options.get(key) if not table: raise NotImplementedError(f"Query {query_type} not supported with type {key}") return table def get_account_table_for_summary( query_type: str, countries: List, ) -> str: key = "total" if len(countries) != 0: key = "country" table_options = ACCOUNT_TABLES_SUMMARY.get(query_type) if not table_options: raise NotImplementedError(f"Query {query_type} not supported") table = table_options.get(key) if not table: raise NotImplementedError(f"Query {query_type} not supported with type {key}") return table @cache_in_redis(ttl=cache.ONE_DAY) def get_account_timeseries( query_params: Mapping[str, Any], permissions: Mapping[str, Any], ) -> List[Dict]: query_type = query_params.get("type", TOTAL_TIMESERIES_TYPE) aggregation_field = AGGREGATION_FIELDS_TIMESERIES.get(query_type) if aggregation_field: query_params["aggregation_field"] = aggregation_field countries = query_params.get("countries", []) query_params["account_table"] = get_account_table_for_timeseries( query_type, countries ) if query_params.get("transfer_product_ownership_enabled"): query_params["cross_attribution_pairs"] = get_cross_attribution_pairs( query_params["account_id"], query_params["account_type"] ) if query_type.startswith("ACCOUNT_STREAMS"): query = AccountStreamsTimeSeries({**query_params, **permissions}) else: query = AccountDownloadsTimeSeries({**query_params, **permissions}) time_series = query.execute() ts = [] for data_point in time_series: data_point = format_row(data_point) if ( data_point.get("streams_with_skips") or data_point.get("streams_with_skips") == 0 ): skip_rate = calc_date_skip_rate(data_point) del data_point["streams_with_skips"] data_point["skip_rate"] = skip_rate ts.append(data_point) if query_type in ("ACCOUNT_STREAMS_BY_SUBSCRIPTION", "ACCOUNT_STREAMS_BY_SOS"): ids = query_params.get("ids") timeseries = [] if query_type == "ACCOUNT_STREAMS_BY_SUBSCRIPTION": by = "subscription" elif query_type == "ACCOUNT_STREAMS_BY_SOS": by = "sos" for item in ts: timeseries.extend(_breakdown_by(item, by=by, sources=ids)) ts = timeseries # sum of streams by subscription types is always equal to total streams, # so if the filter values are set, we can calculate the sum of streams if query_params.get("subscription_types") and query_type.startswith( "ACCOUNT_STREAMS" ): for data_point in ts: data_point["value"] = sum( [ data_point.get(f"sub_type_{sub_type.lower()}") or 0 for sub_type in query_params["subscription_types"] ] ) return ts def _is_default_params(query_params, query_type): """This short circuit only support TOTAL and SUBSCRIPTION, since we fire these dimensions to display a default account summary page. """ max_available_date = data_availability.get_max_available_date().strftime("%Y-%m-%d") if ( query_type in ("TOTAL", "SUBSCRIPTION") and query_params.get("end_date") == max_available_date and ( datetime.strptime(query_params.get("end_date", "2023-01-01"), "%Y-%m-%d") - datetime.strptime( query_params.get("start_date", "1999-01-01"), "%Y-%m-%d" ) ).days == 27 and not query_params.get("countries") and not query_params.get("store_ids") and not query_params.get("fin_label_ids") and not query_params.get("upper_profit_center_ids") and not query_params.get("distributors") ): return True else: return False def _map_row_key_to_field_from_query_type( row, row_key_to_be_mapped, query_type, field_mappings ): mapped_field_name = field_mappings.get(query_type) if mapped_field_name is not None: row[mapped_field_name] = row[row_key_to_be_mapped] return row @cache_in_redis(ttl=cache.ONE_DAY) def get_account_summary( query_params: Mapping[str, Any], permissions: Mapping[str, Any], ) -> List[Dict]: query_type = query_params.get("type", TOTAL_SUMMARY_TYPE) aggregation_field = AGGREGATION_FIELDS_SUMMARY[query_params["account_type"]].get( query_type ) if aggregation_field: query_params["aggregation_field"] = aggregation_field countries = query_params.get("countries", []) tables = get_account_table_for_summary(query_type, countries) query_params.update(tables) if query_params.get("transfer_product_ownership_enabled"): query_params["cross_attribution_pairs"] = get_cross_attribution_pairs( query_params["account_id"], query_params["account_type"] ) if _is_default_params(query_params, query_type): query = AccountSummaryDefaultParams({**query_params, **permissions}) else: query = AccountSummary({**query_params, **permissions}) summary = query.execute() result = [] for data_point in summary: data_point = format_row(data_point) if ( data_point.get("streams_with_skips") or data_point.get("streams_with_skips") == 0 ): skip_rate = calc_date_skip_rate(data_point) del data_point["streams_with_skips"] data_point["skip_rate"] = skip_rate if query_params.get("subscription_types"): data_point["streams"] = sum( [ data_point.get(f"sub_type_{sub_type.lower()}") or 0 for sub_type in query_params["subscription_types"] ] ) result.append(data_point) if result and query_type in ["SOS", "SUBSCRIPTION"]: result = _breakdown_by( result[0], key="streams", by=query_type.lower(), timeseries=False ) # ex. if query_type is PARTICIPANT map id field to new field global_participant_id result = [ _map_row_key_to_field_from_query_type( row, "id", query_type, ACCOUNT_SUMMARY_FIELD_MAPPINGS ) for row in result ] return result def _parse_video_parameters( query_params: Mapping[str, Any], default_type: str or None = None ) -> tuple[Mapping[str, Any], str, str]: # get the date range or set "all_time" if no start date is sent start_date = query_params.get("start_date", None) end_date = query_params.get("end_date", None) if not (start_date and end_date): query_params["all_time"] = True else: query_params["all_time"] = False countries = query_params.get("countries", []) account_type = query_params.get("account_type", "vendor") type = query_params.get("type", default_type) if type == "SUBACCOUNT": query_params["is_subaccount_level"] = True if type == "PARTICIPANT": query_params["is_participant_join"] = True aggregation_field = ACCOUNT_VIDEO_AGGREGATION_FIELDS[account_type].get(type) aggregation_field_prefix = ACCOUNT_VIDEO_AGGREGATION_FIELDS_PREFIXES[ account_type ].get(type) if aggregation_field: query_params["aggregation_field"] = aggregation_field query_params["aggregation_field_prefix"] = aggregation_field_prefix if countries or aggregation_field == "country_code": query_params["table_name"] = ACCOUNT_VIDEO_TABLES.get("COUNTRIES") else: query_params["table_name"] = ACCOUNT_VIDEO_TABLES.get("TOTAL") views_type = query_params.get("views_type") if views_type == "AD_SUPPORTED": query_params["is_views_type_ad_supported"] = True elif views_type: query_params["views_type"] = ACCOUNT_VIDEO_VIEWS_TYPE_KEY[views_type] query_params["labelid"] = query_params.get("account_id") return query_params, type, aggregation_field, aggregation_field_prefix @cache_in_redis(ttl=cache.ONE_DAY) def get_account_video_summary( query_params: Mapping[str, Any], permissions: Mapping[str, Any], ) -> List[Dict]: ( query_params, query_type, aggregation_field, aggregation_field_prefix, ) = _parse_video_parameters(query_params) query = AccountVideoSummary({**query_params, **permissions}) summary = query.execute() return _format_response( summary, ACCOUNT_VIDEO_SUMMARY_MAPPING.get(query_type) or {aggregation_field: "id", "views": "views"}, ) @cache_in_redis(ttl=cache.ONE_DAY) def get_account_video_timeseries( query_params: Mapping[str, Any], permissions: Mapping[str, Any], ) -> List[Dict]: ( query_params, query_type, aggregation_field, aggregation_field_prefix, ) = _parse_video_parameters(query_params, TOTAL_SUMMARY_TYPE) query = AccountVideoTimeseries({**query_params, **permissions}) timeseries = query.execute() return _format_response( timeseries, ( ACCOUNT_VIDEO_TIMESERIES_MAPPING.get(query_type) or { aggregation_field: "id", "views": "views", "date": "download_activity_date", } ), )