Spotify Penetration Analysis
Roles: Data Engineer | Data Scientist | BI Analyst | Data Analyst | ML Engineer | Software Engineer
Introduction
Market penetration is an important metric for understanding Spotify's performance and growth potential in different regions. You are part of the analytics team at Spotify and are tasked with calculating the active user penetration rate in specific countries.
Active Users Criteria
For this task, 'active_users' are defined based on the following criteria: last_active_date: The user must have interacted with Spotify within the last 30 days. sessions: The user must have engaged with Spotify for at least 5 sessions. listening_hours: The user must have spent at least 10 hours listening on Spotify.
Calculation
Based on the conditions above, calculate the active 'user_penetration_rate' by using the following formula: Active User Penetration Rate = (Number of Active Spotify Users in the Country / Total users in the Country) Total Population of the country is based on both active and non-active users.
Overview
This guide describes the step-by-step process for creating a Tableau dashboard that includes:
A Rank of Listening Hours (Bar Chart) by countries and years (2023, 2024).
A Year of Activity Date Table summarizing data by year.
Rank of Listening Hours by Region displayed with 5 donut charts (Brazil, France, Germany, Japan, USA).
A Map with a Year Toggle Button to switch between 2023 and 2024.
A Year of Activity Date Table summarizing data by year.
Rank of Listening Hours by Region displayed with 5 donut charts (Brazil, France, Germany, Japan, USA).
A Map with a Year Toggle Button to switch between 2023 and 2024.
1. Build Rank of Listening Hours (Bar Chart)
Drag Country to Rows.
Drag Listening Hours to Columns.
Right-click on Listening Hours in the Columns shelf, select Quick Table Calculation → Rank.
Drag last_active_date to the Filters shelf and set it to show only 2023 and 2024.
Drag last_active_date to the Color shelf to differentiate the years.
Adjust the chart to display ranks clearly:
Drag Listening Hours to Columns.
Right-click on Listening Hours in the Columns shelf, select Quick Table Calculation → Rank.
Drag last_active_date to the Filters shelf and set it to show only 2023 and 2024.
Drag last_active_date to the Color shelf to differentiate the years.
Adjust the chart to display ranks clearly:
Sort the bars by rank.
Add labels for Listening Hours.
Customize the title as "Rank of Listening Hours by Country and Year (2023, 2024)".
Add labels for Listening Hours.
Customize the title as "Rank of Listening Hours by Country and Year (2023, 2024)".
2. Create Year of Activity Date Table
Drag last_active_date to Rows.
Right-click on it and select Convert to Continuous Month.
Drag Listening Hours to Columns.
Adjust the table view:
Use the Analytics Pane to add totals/subtotals if needed.
Format the table to display yearly trends clearly.
Title the sheet "Year of Activity Date".
Right-click on it and select Convert to Continuous Month.
Drag Listening Hours to Columns.
Adjust the table view:
Use the Analytics Pane to add totals/subtotals if needed.
Format the table to display yearly trends clearly.
Title the sheet "Year of Activity Date".
3. Create Rank of Listening Hours by Region (5 Donut Charts)
Filter the data by regions: Brazil, France, Germany, Japan, and the USA.
Create calculated fields for each region. For example: plaintext IF [Region] = "Brazil" THEN [Listening Hours] ELSE 0
Create individual donut charts for each region:
Drag Listening Hours to Columns.
Drag Region to Rows.
Add a Pie Chart visualization.
Break the chart into slices by year (last_active_date as Color).
Adjust the size and formatting to create a "donut" effect (e.g., dual axis with one empty circle).
Repeat for all regions (Brazil, France, Germany, Japan, USA).
Title the sheets appropriately, such as "Listening Hours - Brazil".
Create calculated fields for each region. For example: plaintext IF [Region] = "Brazil" THEN [Listening Hours] ELSE 0
Create individual donut charts for each region:
Drag Listening Hours to Columns.
Drag Region to Rows.
Add a Pie Chart visualization.
Break the chart into slices by year (last_active_date as Color).
Adjust the size and formatting to create a "donut" effect (e.g., dual axis with one empty circle).
Repeat for all regions (Brazil, France, Germany, Japan, USA).
Title the sheets appropriately, such as "Listening Hours - Brazil".
4. Prepare Your Data for the Map
Ensure your dataset includes latitude, longitude, and listening hours for 2023 and 2024.
Sample Data Structure
Sample Data Structure
Sample Data Structure
5. Create a Parameter for Year Selection
Go to the Data Pane.
Right-click and select Create Parameter.
Set up the parameter:
Name: Year Toggle
Data Type: Integer
Allowable Values: List
Right-click and select Create Parameter.
Set up the parameter:
Name: Year Toggle
Data Type: Integer
Allowable Values: List
Values:
2023
2024
2023
2024
Click OK.
6. Show the Parameter Control
Right-click on the Year Toggle parameter in the Data Pane.Select Show Parameter to make it visible on the worksheet/dashboard.
Right-click on the Year Toggle parameter in the Data Pane.Select Show Parameter to make it visible on the worksheet/dashboard.
7. Create a Calculated Field for Year Filtering
Create a new calculated field:
Name: Filter by Year
Formula:
plaintextYEAR([last_active_date]) = [Year Toggle]
Drag Filter by Year to the Filters shelf and select True.
Name: Filter by Year
Formula:
plaintextYEAR([last_active_date]) = [Year Toggle]
Drag Filter by Year to the Filters shelf and select True.
8. Build the Map
Drag Latitude to Rows and Longitude to Columns.
Drag Listening Hours to Size or Color.
Add Country or Region to the Detail shelf to enhance the map.
Customize the map (e.g., tooltips, colors, and zoom).
Drag Listening Hours to Size or Color.
Add Country or Region to the Detail shelf to enhance the map.
Customize the map (e.g., tooltips, colors, and zoom).
9. Customize the Toggle Button
Save custom shapes for 2023 and 2024 (e.g., icons) in Tableau's Shapes folder.
Create a new sheet for the toggle button:
Drag the Year Toggle parameter to Columns.
Assign the custom shapes for each year.
Adjust formatting to clearly display the toggle.
Create a new sheet for the toggle button:
Drag the Year Toggle parameter to Columns.
Assign the custom shapes for each year.
Adjust formatting to clearly display the toggle.
10. Combine Sheets in a Dashboard
Create a new dashboard.
Add the following sheets:
Rank of Listening Hours (Bar Chart).
Year of Activity Date Table.
Rank of Listening Hours by Region (5 donut charts).
Map with a Year Toggle Button.
Position the toggle button prominently on the dashboard.
Use filters and parameter actions to connect the toggle to the map and other visualizations.
Add the following sheets:
Rank of Listening Hours (Bar Chart).
Year of Activity Date Table.
Rank of Listening Hours by Region (5 donut charts).
Map with a Year Toggle Button.
Position the toggle button prominently on the dashboard.
Use filters and parameter actions to connect the toggle to the map and other visualizations.
11. Test and Publish
Test the toggle button to ensure the dashboard updates dynamically for 2023 and 2024.
Publish the dashboard to Tableau Server, Tableau Online, or save it as a packaged workbook.
Publish the dashboard to Tableau Server, Tableau Online, or save it as a packaged workbook.
Final Outcome
The dashboard provides an interactive visualization with:
Bar charts ranking listening hours by country and year.
A summary table of activity dates.
Donut charts showing regional listening hours.
A toggle button to switch between 2023 and 2024 data on the map.
Bar charts ranking listening hours by country and year.
A summary table of activity dates.
Donut charts showing regional listening hours.
A toggle button to switch between 2023 and 2024 data on the map.