# The prod_eu_analytics schema
>This section is still (and most likely always will be) somewhat of a work in progress
## About the schema
???+ note "Permissions"
If you do not have access to a table, please reach out to Matti or Julien.
If you add a table into the schema, we recommend granting `select` for the CEA group:
```grant select on prod_eu_analytics.shook_ones to group cea_group```
???+ note "Naming Conventions"
The only "rule" for naming tables is to prefix the tables with the name of the associated project. (i.e. the project that contains the script that generates the table) Otherwise, just try to keep the table names informative.
## Schema contents
### Discovery Dashboard
- All tables prefixed by `discoverydash`
- Created/Modified/Used by scripts in the [discoverydash-cea](https://github.com/SME-BUS/cea-infamous/tree/main/projects-cea/discoverydash-cea) project
- Used by the [discovery dashboard](https://initiatives.scuba.tools/#/site/int_mktng/workbooks/5555/views) on the CEA Tableau Server
!!! tip "discoverydash"
??? abstract "prod_eu_analytics.discoverydash_apollo_mkts"
For constructing contry-specifc apollo links.
| Column | Description |
|----------|-------------|
|country_code| |
|apollo_mkt| The apollo market number associated with a country. Stored as a varchar since this is always treated as a string. Injected into an url to form an apollo link to a specific country view.|
??? abstract "prod_eu_analytics.discoverydash_playlist_hyper"
The table where the tableau extract is taken from for the playlist views. A filtered version of `prod_eu_analytics.discoverydash_playlist_main` with some renamed variables and added metadata. Columns are renamed in order to align the structure between the search, playlist and radio data. This enables easy data source switching in case we ever want to add new views to the dashboard!
| Column | Description |
|----------|-------------|
|report_date | |
|isrc_cd | |
|cc_or_pl | Name of playlist that the consumers discovered the track from |
|primary_artist_name | |
|product_name | |
|apollo_link | The country-specific url for the track in apollo|
|n_consumers_collection | Number of consumers who had at least one collection stream after the playlist discovery |
|n_consumers_other | Number of consumers who had a at least one repeat stream after the playlist discovery from a source other than the playlist or collection |
|n_consumers_source | Number of consumers who had a discovery stream from the playlist |
|sum_streams_collection | Total number of collection streams after the playlist discovery |
|sum_streams_other | Total number of repeat streams after the playlist discovery from a source other than the playlist or collection |
|sum_streams_source | Total number of streams from the playlist after the discovery (including the discovery) |
??? abstract "prod_eu_analytics.discoverydash_playlist_main"
Stores the playlist discovery data. Gets appended incrementally with new data daily. Does not contain metadata.
| Column | Description |
|----------|-------------|
|report_date | |
|isrc_cd | |
|source_uri | Source uri of playlist that the consumers discovered the track from |
|n_consumers_collection | Number of consumers who had at least one collection stream after the playlist discovery |
|n_consumers_other | Number of consumers who had a at least one repeat stream after the playlist discovery from a source other than the playlist or collection |
|n_consumers_playlist | Number of consumers who had a discovery stream from the playlist |
|sum_streams_collection | Total number of collection streams after the playlist discovery |
|sum_streams_other | Total number of repeat streams after the playlist discovery from a source other than the playlist or collection |
|sum_streams_playlist | Total number of streams from the playlist after the discovery (including the discovery) |
??? abstract "prod_eu_analytics.discoverydash_playlist_temp"
Not for use. Only used for updating `prod_eu_analytics.discoverydash_playlist_main`.
??? abstract "prod_eu_analytics.discoverydash_playlist_uri"
Contains the playlist ids/uris that we want to include in the dashboard.
| Column | Description |
|----------|-------------|
|playlist_name | |
|source_uri_5 | Spotify playlist id (naming consistent with `spotify.dim_playlists`)|
|uri | Spotify playlist uri|
|market | The main market of the playlist|
|type | The type of playlist (Hits/Pop/Dance etc.)|
??? abstract "prod_eu_analytics.discoverydash_radio_hyper"
The table where the tableau extract is taken from for the radio views. A filtered version of `prod_eu_analytics.discoverydash_radio_main` with some renamed variables and added metadata. Columns are renamed in order to align the structure between the search, playlist and radio data. This enables easy data source switching in case we ever want to add new views to the dashboard!
| Column | Description |
|----------|-------------|
|report_date | |
|isrc_cd | |
|cc_or_pl | country_code |
|primary_artist_name | |
|product_name | |
|apollo_link | The country-specific url for the track in apollo|
|n_consumers_collection | Number of consumers who had at least one collection stream after the radio discovery |
|n_consumers_other | Number of consumers who had a at least one repeat stream after the radio discovery from a source other than radio or collection |
|n_consumers_source | Number of consumers who had a discovery stream from radio |
|sum_streams_collection | Total number of collection streams after the radio discovery |
|sum_streams_other | Total number of repeat streams after the radio discovery from a source other than radio or collection |
|sum_streams_source | Total number of streams from radio after the discovery (including the discovery) |
??? abstract "prod_eu_analytics.discoverydash_radio_main"
Stores the radio discovery data. Gets appended incrementally with new data daily. Does not contain metadata.
| Column | Description |
|----------|-------------|
|report_date | |
|isrc_cd | |
|country_code | |
|n_consumers_collection | Number of consumers who had at least one collection stream after the radio discovery |
|n_consumers_other | Number of consumers who had a at least one repeat stream after the radio discovery from a source other than radio or collection |
|n_consumers_radio | Number of consumers who had a discovery stream from radio |
|sum_streams_collection | Total number of collection streams after the radio discovery |
|sum_streams_other | Total number of repeat streams after the radio discovery from a source other than radio or collection |
|sum_streams_radio | Total number of streams from radio after the discovery (including the discovery) |
??? abstract "prod_eu_analytics.discoverydash_radio_temp"
Not for use. Only used for updating `prod_eu_analytics.discoverydash_radio_main`.
??? abstract "prod_eu_analytics.discoverydash_search_hyper"
The table where the tableau extract is taken from for the search views. A filtered version of `prod_eu_analytics.discoverydash_search_main` with some renamed variables and added metadata. Columns are renamed in order to align the structure between the search, playlist and radio data. This enables easy data source switching in case we ever want to add new views to the dashboard!
| Column | Description |
|----------|-------------|
|report_date | |
|isrc_cd | |
|cc_or_pl | country_code |
|primary_artist_name | |
|product_name | |
|apollo_link | The country-specific url for the track in apollo|
|n_consumers_collection | Number of consumers who had at least one collection stream after the search discovery |
|n_consumers_other | Number of consumers who had a at least one repeat stream after the search discovery from a source other than search or collection |
|n_consumers_source | Number of consumers who had a discovery stream from search |
|sum_streams_collection | Total number of collection streams after the search discovery |
|sum_streams_other | Total number of repeat streams after the search discovery from a source other than search or collection |
|sum_streams_source | Total number of streams from search after the discovery (including the discovery) |
??? abstract "prod_eu_analytics.discoverydash_search_main"
Stores the search discovery data. Gets appended incrementally with new data daily. Does not contain metadata.
| Column | Description |
|----------|-------------|
|report_date | |
|isrc_cd | |
|country_code | |
|n_consumers_collection | Number of consumers who had at least one collection stream after the search discovery |
|n_consumers_other | Number of consumers who had a at least one repeat stream after the search discovery from a source other than search or collection |
|n_consumers_search | Number of consumers who had a discovery stream from search |
|sum_streams_collection | Total number of collection streams after the search discovery |
|sum_streams_other | Total number of repeat streams after the search discovery from a source other than search or collection |
|sum_streams_search | Total number of streams from search after the discovery (including the discovery) |
??? abstract "prod_eu_analytics.discoverydash_search_temp"
Not for use. Only used for updating `prod_eu_analytics.discoverydash_search_main`.
### Live Playlisting
- All tables prefixed by `liveplaylisting`
- The playlist contents are fetched from the APIs every day, except on fridays when the process runs every hour. The contents are "live" in a sense that we do not need to wait for the streaming data and the 2-day lag.
!!! tip "liveplaylisting"
??? abstract "prod_eu_analytics.liveplaylisting_apple"
Apple playlist contents for the most recent run.
| Column | Description |
|----------|-------------|
| storefront | |
| playlist_id | |
| playlist_name | |
| curator | |
| playlist_url | |
| nb_tracks | |
| cover | |
| position | |
| artists | |
| track | |
| isrc | |
| track_id | |
??? abstract "prod_eu_analytics.liveplaylisting_apple_histo"
Apple playlist content history
| Column | Description |
|----------|-------------|
| storefront | |
| report_date | |
| playlist_id | |
| position | |
| isrc | |
| track_id | |
??? abstract "prod_eu_analytics.liveplaylisting_deezer"
Deezer playlist contents for the most recent run.
| Column | Description |
|----------|-------------|
| playlist_id | |
| playlist_name | |
| curator | |
| followers | |
| playlist_url | |
| nb_tracks | |
| cover | |
| position | |
| artists | |
| track | |
| track_id | |
??? abstract "prod_eu_analytics.liveplaylisting_deezer_histo"
Deezer playlist content history
| Column | Description |
|----------|-------------|
| report_date | |
| playlist_id | |
| position | |
| track_id | |
??? abstract "prod_eu_analytics.liveplaylisting_spotify"
Spotify playlist contents for the most recent run.
| Column | Description |
|----------|-------------|
| source_uri_5 | |
| playlist_name | |
| curator | |
| followers | |
| playlist_url | |
| nb_tracks | |
| cover | |
| position | |
| artists | |
| track | |
| isrc | |
??? abstract "prod_eu_analytics.liveplaylisting_spotify_histo"
Spotify playlist content history
| Column | Description |
|----------|-------------|
| report_date | |
| source_uri_5 | |
| position | |
| isrc | |
??? abstract "prod_eu_analytics.liveplaylisting_title"
No idea tbh, probably used by BI
| Column | Description |
|----------|-------------|
| title | |
| last_thursday | |
### MARS
- All tables prefixed by `mars`
!!! tip "mars"
??? abstract "prod_eu_analytics.mars_ads_metadata"
Table description here.
| Column | Description |
|----------|-------------|
| supplier | |
| account_name | |
| account_id | |
| manager_account_name | |
| manager_account_id | |
| objective | |
| campaign_start_date | |
| campaign_end_date | |
| campaign_id | |
| campaign_name | |
| campaign_status | |
| campaign_budget | |
| adset_id | |
| adset_name | |
| adset_status | |
| adset_budget | |
| adset_start_date | |
| adset_end_date | |
| ad_id | |
| ad_name | |
| ad_status | |
| updated_at | |
| destination | |
| destination_type | |
| territory | |
| project_no | |
| label | |
| artist | |
| product | |
| manual_objective | |
| c_information | |
| ads_information | |
| ad_information | |
| created_at | |
??? abstract "prod_eu_analytics.mars_benchmarks"
Table description here.
| Column | Description |
|----------|-------------|
| supplier | |
| territory | |
| objective | |
| end_date | |
| benchmark_attribution_window | |
| campaigns_for_benchmark | |
| kpi_name | |
| average_kpi_value | |
| platform | |
| placement | |
??? abstract "prod_eu_analytics.mars_fact_ads"
Table description here.
| Column | Description |
|----------|-------------|
| supplier | |
| report_date | |
| account_id | |
| ad_id | |
| campaign_id | |
| objective | |
| territory | |
| placement_category | |
| platform_category | |
| platform | |
| placement | |
| impressions | |
| reach | |
| spend | |
| video_views | |
| comments | |
| interactions | |
| likes | |
| link_clicks | |
| reactions | |
| shares | |
| follows | |
| saves | |
| conversions | |
| all_conversions | |
| cpm | |
| cpc | |
| ctr | |
| cost_per_conversion | |
| cost_per_comment | |
| cost_per_share | |
| cost_per_reaction | |
| cost_per_view | |
| cost_per_profile_visit | |
| cost_per_follow | |
| benchm__cost_per_comment | |
| benchm__cost_per_conversion | |
| benchm__cost_per_reaction | |
| benchm__cost_per_share | |
| benchm__cost_per_view | |
| benchm__cpc | |
| benchm__cpm | |
| benchm__ctr | |
| _fivetran_id | |
| _fivetran_synced | |
??? abstract "prod_eu_analytics.mars_settings_adaccounts"
Table description here.
| Column | Description |
|----------|-------------|
| country_code | |
| country_name | |
| supplier | |
| account_name | |
| account_id | |
| manager_account_name | |
| manager_account_id | |
| region_name | |
| notes | |
??? abstract "prod_eu_analytics.mars_settings_conversion_settings"
Table description here.
| Column | Description |
|----------|-------------|
| id | |
| include_in_calc | |
??? abstract "prod_eu_analytics.mars_settings_destinations"
Table description here.
| Column | Description |
|----------|-------------|
| domain | |
| destination_type | |
??? abstract "prod_eu_analytics.mars_settings_kpi_names"
Table description here.
| Column | Description |
|----------|-------------|
| supplier | |
| kpi_name | |
| rename | |
??? abstract "prod_eu_analytics.mars_settings_objective"
Table description here.
| Column | Description |
|----------|-------------|
| objective | |
| objective_group | |
??? abstract "prod_eu_analytics.mars_settings_placement_category"
Table description here.
| Column | Description |
|----------|-------------|
| supplier | |
| placement | |
| placement_category | |
??? abstract "prod_eu_analytics.mars_settings_platform_category"
Table description here.
| Column | Description |
|----------|-------------|
| supplier | |
| platform | |
| platform_category | |
??? abstract "prod_eu_analytics.mars_settings_status"
Table description here.
| Column | Description |
|----------|-------------|
| supplier | |
| level | |
| search | |
| replace | |
### Spotify Vendor Key Map
- As crazy as it might sound, we do not have a common mapping of the vendor keys found in the Spotify Prorata Report (`spotify_reports.prorata_sony_weekly_v2`). This table allows you to map vendor keys to free/paid tiers and subscription types.
Example usage:
```
select
vk.free_paid,
pr.country_code,
pr.report_date,
sum(pr.rightsholder_streams)::float as sony_streams,
sum(pr.total_streams)::float as total_streams,
sum(pr.active_users)::float as n_active_users
from spotify_reports.prorata_sony_weekly_v2 pr
inner join common.dim_calendar ca on pr.report_date = ca.calendar_date
left outer join prod_eu_analytics.spotify_vendor_key_map vk on pr.vendor_key = vk.vendor_key
where ca.month_key >= 202301
and pr.country_code in ('FI', 'FR')
group by 1, 2, 3
```
??? abstract "prod_eu_analytics.spotify_vendor_key_map"
Based on the Spotify statements distributed by GDB. The vendor keys are not 100% exhaustive, as there are a few keys in the prorata report that are missing from the statements, although they are of rather small magnitude. Updated monthly after the arrival of the new statement.
| Column | Description |
|----------|-------------|
|vendor_key | Joins into the prorata report on the `vendor_key` column|
|free_paid | Free / Paid / Trial|
|subscription_type | Subscription / Ad-Supported / B2B / 3rd Party|
|subscription_name | Name/description of the subscription |
### Summer Playlists
- All tables prefixed by `summer_playlists`
- Created/Modified/Used by scripts in the [summer-playlists-cea](https://github.com/SME-BUS/cea-infamous/tree/main/projects-cea/summer-playlists-cea) project
- Powers the CEA summer playlist dashboard [summer playlist dashboard](https://initiatives.scuba.tools/#/site/int_mktng/workbooks/6495/views) on the CEA Tableau Server
!!! tip "summer_playlists"
??? abstract "prod_eu_analytics.summer_playlists_discovery_hyper"
The table where the tableau extract is taken from for the discovery section of the [summer playlist dashboard](https://initiatives.scuba.tools/#/site/int_mktng/views/SUMMERPLAYLISTDASH/PlaylistDiscovery?:iid=3). The same structure as the regular [discovery-dashboard](#discovery-dashboard) playlist hyper table, with an added column for playlist type.
| Column | Description |
|----------|-------------|
|report_date | |
|isrc_cd | |
|cc_or_pl | Name of playlist that the consumers discovered the track from |
|playlist_type | Editorial or Filtr playlist |
|primary_artist_name | |
|product_name | |
|apollo_link | The country-specific url for the track in apollo|
|n_consumers_collection | Number of consumers who had at least one collection stream after the playlist discovery |
|n_consumers_other | Number of consumers who had a at least one repeat stream after the playlist discovery from a source other than the playlist or collection |
|n_consumers_source | Number of consumers who had a discovery stream from the playlist |
|sum_streams_collection | Total number of collection streams after the playlist discovery |
|sum_streams_other | Total number of repeat streams after the playlist discovery from a source other than the playlist or collection |
|sum_streams_source | Total number of streams from the playlist after the discovery (including the discovery) |
??? abstract "prod_eu_analytics.summer_playlists_ids"
The spotify playlist ids of the playlists included in the dashboard.
| Column | Description |
|----------|-------------|
|country_code | The main market of the playlist|
|playlist_id | |
??? abstract "prod_eu_analytics.summer_playlists_live_hyper"
The table where the tableau extract is taken from for the "live" section of the [summer playlist dashboard](https://initiatives.scuba.tools/#/site/int_mktng/views/SUMMERPLAYLISTDASH/LiveContents?:iid=1). Created by [this](https://github.com/SME-BUS/cea-infamous/blob/main/projects-cea/summer-playlists-cea/sql/make-live-hyper-table.sql). Note that each track/playlist-combination exists in the table, even if the track is **not** included on the playlist.
| Column | Description |
|----------|-------------|
|isrc | |
|primary_artist_name | |
|product_name | |
|apollo_link | Apollo url for the track|
|playlist_name | |
|country_code | Affiliate / Main market country code|
|playlist_type | Filter / Editorial|
|position_on_playlist | `NULL` if track not included|
??? abstract "prod_eu_analytics.summer_playlists_session"
The consumer sessions used for assigning skips to playlists and consequently approximating track skip rates on playlists. A temporary table that gets dropped and re-created upon every release. Created by [this](https://github.com/SME-BUS/cea-infamous/blob/main/projects-cea/summer-playlists-cea/sql/make-session-table.sql)
??? abstract "prod_eu_analytics.summer_playlists_skiprate_hyper"
The table where the tableau extract is taken from for the skip rate section of the [summer playlist dashboard](https://initiatives.scuba.tools/#/site/int_mktng/views/SUMMERPLAYLISTDASH/SkipRates?:iid=1). Created by [this](https://github.com/SME-BUS/cea-infamous/blob/main/projects-cea/summer-playlists-cea/sql/make-skiprate-hyper-table.sql).
| Column | Description |
|----------|-------------|
|report_date | |
|isrc_cd | |
|cc_or_pl | Playlist name. Named this way for to comply with the discoverydash conventions.|
|playlist_type | Filter / Editorial|
|country_code | Affiliate / Main market country code|
|primary_artist_name | |
|product_name | |
|apollo_link | Apollo url for the track|
|sum_streams | Streams from the identified sessions|
|num_consumers_stream | Number of consumers with streams from the identified sessions|
|sum_skips | Skips assigned to the sessions|
|num_consumers_skip | Number of consumers with skips from the identified sessions|
|position_on_playlist | |
??? abstract "prod_eu_analytics.summer_playlists_skiprate_main"
The main data table for the skip rate section of the [summer playlist dashboard](https://initiatives.scuba.tools/#/site/int_mktng/views/SUMMERPLAYLISTDASH/SkipRates?:iid=1). Does not include metadata. Appended daily by [this](https://github.com/SME-BUS/cea-infamous/blob/main/projects-cea/summer-playlists-cea/sql/append-skiprate-table.sql).
| Column | Description |
|----------|-------------|
|report_date | |
|track_isrc | |
|source_uri | |
|sum_streams | Streams from the identified sessions|
|num_consumers_stream | Number of consumers with streams from the identified sessions|
|sum_skips | Skips assigned to the sessions|
|num_consumers_skip | Number of consumers with skips from the identified sessions|
|position_on_playlist | |
??? abstract "prod_eu_analytics.summer_playlists_skiprate_temp"
A temporary table only used for appending `prod_eu_analytics.summer_playlists_skiprate_main` [by this](https://github.com/SME-BUS/cea-infamous/blob/main/projects-cea/summer-playlists-cea/sql/append-skiprate-table.sql).
??? abstract "prod_eu_analytics.summer_playlists_stats_agg_hyper"
The table where the tableau extract is taken from for the aggregated overview section of the [summer playlist dashboard](https://initiatives.scuba.tools/#/site/int_mktng/views/SUMMERPLAYLISTDASH/AggregatedOverview?:iid=1). Daily country/playlist-level stream counts. Created by [this](https://github.com/SME-BUS/cea-infamous/blob/main/projects-cea/summer-playlists-cea/sql/make-agg-hyper-table.sql).
| Column | Description |
|----------|-------------|
|report_date | |
|country_code | Country code of the streams|
|cc_or_pl | Playlist Name|
|playlist_type | Editorial / Filtr|
|country_code_playlist | Affiliate / Main market of the playlist|
|sum_streams | |
??? abstract "prod_eu_analytics.summer_playlists_stats_agg_main"
The main data table for the aggregated overview section of the [summer playlist dashboard](https://initiatives.scuba.tools/#/site/int_mktng/views/SUMMERPLAYLISTDASH/AggregatedOverview?:iid=1). Does not include metadata. Appended daily by [this](https://github.com/SME-BUS/cea-infamous/blob/main/projects-cea/summer-playlists-cea/sql/append-agg-table.sql).
| Column | Description |
|----------|-------------|
|report_date | |
|country_code | Country code of the streams|
|source_uri | |
|sum_streams | |
??? abstract "prod_eu_analytics.summer_playlists_stats_agg_temp"
A temporary table only used for appending `prod_eu_analytics.summer_playlists_stats_agg_main` [by this](https://github.com/SME-BUS/cea-infamous/blob/main/projects-cea/summer-playlists-cea/sql/append-agg-table.sql).
??? abstract "prod_eu_analytics.summer_playlists_stats_hyper"
The table where the tableau extract is taken from for the streaming overview section of the [summer playlist dashboard](https://initiatives.scuba.tools/#/site/int_mktng/views/SUMMERPLAYLISTDASH/StreamingOverview?:iid=1). Created by [this](https://github.com/SME-BUS/cea-infamous/blob/main/projects-cea/summer-playlists-cea/sql/make-stats-hyper-table.sql).
| Column | Description |
|----------|-------------|
|report_date | |
|isrc_cd | |
|position_on_playlist | |
|cc_or_pl | Playlist Name|
|playlist_type | Editorial / Filtr|
|country_code | Affiliate / Main market of the playlist|
|primary_artist_name | |
|apollo_link | Apollo uri for the track|
|gender_name | |
|spotify_age_group_name | |
|sum_streams | |
|sum_stream_duration_thousands | Total duration of streaming in units of a thousand seconds|
|sum_media_duration_thousands | Total media duration units of a thousand seconds. Divide the streaming duration by this to get the avg completion rate.|
|num_consumers | |
??? abstract "prod_eu_analytics.summer_playlists_stats_main"
The main data table for the streaming overview section of the [summer playlist dashboard](https://initiatives.scuba.tools/#/site/int_mktng/views/SUMMERPLAYLISTDASH/StreamingOverview?:iid=1). Does not include metadata. Appended daily by [this](https://github.com/SME-BUS/cea-infamous/blob/main/projects-cea/summer-playlists-cea/sql/append-stats-table.sql).
| Column | Description |
|----------|-------------|
|report_date | |
|track_isrc | |
|source_uri | |
|position_on_playlist | |
|gender_name | |
|spotify_age_group_name | |
|sum_streams | |
|sum_stream_duration_thousands | Total duration of streaming in units of a thousand seconds|
|sum_media_duration_thousands | Total media duration units of a thousand seconds. Divide the streaming duration by this to get the avg completion rate.|
|num_consumers | |
??? abstract "prod_eu_analytics.summer_playlists_stats_temp"
A temporary table only used for appending `prod_eu_analytics.summer_playlists_stats_main` [by this](https://github.com/SME-BUS/cea-infamous/blob/main/projects-cea/summer-playlists-cea/sql/append-stats-table.sql).
### WHATS COOKIN
- All tables prefixed by `whatscookin`
- Created/Modified/Used by scripts in the [whats-cookin-cea](https://github.com/SME-BUS/cea-infamous/tree/main/projects-cea/whats-cookin-cea) project
- Used for sending out the **WHATS COOKIN reports**, and associated dashboards such as [this](https://initiatives.scuba.tools/#/site/int_mktng/workbooks/6137/views) on the CEA Tableau Server
!!! tip "whatscookin"
??? abstract "prod_eu_analytics.whatscookin_dashboard_main"
Spotify streaming data for the [WHATS COOKIN monitoring tool](https://initiatives.scuba.tools/#/site/int_mktng/workbooks/6137/views)
| Column | Description |
|----------|-------------|
|country_code | |
|report_date | |
|isrc_cd | |
|primary_artist_name | |
|product_name | |
|lean_type | |
|playlist_key | |
|playlist_name | |
|playlist_uri | |
|is_cookin | 1 if the track was included in the report on this day, 0 otherwise |
|streams | Total Spotify streams for this date/track/country/lean_type combo |
??? abstract "prod_eu_analytics.whatscookin_dashboard_tiktok"
TikTok data for the [WHATS COOKIN monitoring tool](https://initiatives.scuba.tools/#/site/int_mktng/workbooks/6137/views)
| Column | Description |
|----------|-------------|
|country_code | |
|report_date | |
|isrc_cd | |
|primary_artist_name | |
|product_name | |
|creations | Creations for **any products in the same product family as the Spotify ISRC** |
|views | Views for any products in the same product family as the Spotify ISRC |
|likes | Likes for any products in the same product family as the Spotify ISRC |
|shares | Shares for any products in the same product family as the Spotify ISRC |
|favorites | Favorites for any products in the same product family as the Spotify ISRC |
|searches | Searches for any products in the same product family as the Spotify ISRC |
??? abstract "prod_eu_analytics.whatscookin_distro"
The distribution list for the WHATS COOKIN reports.
| Column | Description |
|----------|-------------|
|country_code | The report country. Regular country codes correspond to the standard report. For example, adding a row with `FI`, would send the user a report called `WHATS COOKIN in 🇫🇮`. Country codes prefixed with `x` imply the international version of the reports. For example, adding a row with `xFI` would send the user a report called `WHATS COOKIN around the world, SME Finland edition`. You can also use `CEA` for the CEA aggregated edition, or `ALL` for subscribing to all reports.|
|email | |
|mode | The mode in which the email will be sent out: `prod` or `dev`. `dev` should only be used if you are involved in the development of WHATS COOKIN. |
|category | The type of recipient: `optional` or `required`. All new recipients should be added as `optional`.|
??? abstract "prod_eu_analytics.whatscookin_history"
Historical data for the report. This data is generated daily even if the emails only go out 3 times per week.
| Column | Description |
|----------|-------------|
|isrc_cd | |
|country_code | |
|report_date | |
|n_consumers_collection | Number of consumers who had at least one collection stream after the search discovery |
|n_consumers_other | Number of consumers who had a at least one repeat stream after the search discovery from a source other than search or collection |
|n_consumers_search | Number of consumers who had a discovery stream from search |
|sum_streams_collection | Total number of collection streams after the search discovery |
|sum_streams_other | Total number of repeat streams after the search discovery from a source other than search or collection |
|sum_streams_search | Total number of streams from search after the discovery (including the discovery) |
|is_searched_now | Boolean flag for indicating high search discovery volume. If True, at least one of the 3 highest days in the past 180 days happened in the most recent week|
|sum_streams_spotify | The total (uncapped) Spotify daily stream count in the country (UTC day)|
|spotify_chart_threshold | The daily (capped) stream count of the lowest track on the Spotify chart for the country|
|spotify_chart_position | |
|primary_artist_name | |
|product_name | |
|apollo_link | The country-specifc apollo url |
|track_release_date | |
|rep_owner | |
|parent_rep_owner | |
|entry_date | The first date of appearance for the track in the report (country-specific)|
|stream_threshold | A country-specifc total stream volume threshold for a track to be included in the report|
|kpi | The WHATS COOKIN KPI|
|kpi_percentile | The precentile of the daily KPI among all tracks in the 180-day trailing window |
|days_since_entry | How many days passed since the first time the track was included in the report (country-specific)|
|tiktok_rank | The tiktok rank of the track among Sony tracks, measured by creations, for the most recent date available. Only the top 50 get a rank, otherwise NULL. Includes creations for any ISRCs in the product family, and not just the Spotify version. |
|shazam_chart_position | Track placement on the daily Shazam hart for the country (if applicable)|
|owner_abbreviation | The mapped abbreviation of the parent_rep_owner. Equals country_code for most affiliates.|
??? abstract "prod_eu_analytics.whatscookin_last_run"
Used for triggering the process. Contains the date for which the the report was most recently created.
??? abstract "prod_eu_analytics.whatscookin_ro_map"
Mapping table for parent_rep_owners. Used to populate the "owner_abberivation" column in the data.
| Column | Description |
|----------|-------------|
|parent_rep_owner | |
|owner_abbreviation | If this equals a country_code, then tracks with the associated parent_rep_owner will be included in the reperoire-owner version of the report (i.e. WHATS COOKIN around the world, SME XXX Edition) |