Data Visualization: Charting my Spotify Wrapped Songs and More
Introduction
I’ve been a Spotify user since 2017 and really enjoy using the streaming platform. One of my favorite features of Spotify is the annual “Wrapped” marketing campaign. It’s a brightly colored and easily shareable compilation of your listening activity, including your top songs, artists, genres, and podcasts of the year. Every early December, my social media feeds get flooded with my mutuals showcasing their favorite songs and how they’re in the top 0.05% of listeners of artist X.
After receiving the latest Wrapped in 2022, I wondered how many songs in my Top 100 this year were in last year’s. What if a song went even further back? Are there songs that have consistently stayed in my Top 100s over the years?
Ultimately, I wanted a view that charted my personal hits; my own Billboard 100. This is the motivation behind this data visualization project.
Producing the dataset
The shareable graphic components (AKA story mode) of Wrapped only last for a few weeks. They disappear and you can’t view or share them again. However, the Spotify Wrapped playlist (your top 100 songs) for each year will remain in your Library.
I found a tool called spotlistr that exports Spotify playlists into a CSV file. I used this to export each of my Spotify Wrapped playlists, starting from 2017. Since I wanted to chart the position of songs from year to year, I added a “rank” column to each year’s 100 songs, which was easy as the playlists are already in ascending order. Then I consolidated every year’s list into one Excel sheet.
I imported the Excel file into Tableau and got to work creating my visualizations.
Creating the visualizations
As mentioned earlier, I was most eager to learn which songs have been in my annual top 100 more than once, and how they’ve charted over the years. Initially, I plotted every song’s rank across 6 years of listening.
Seeing how songs have fallen and risen on my personal rankings was cool. However, this still doesn’t satisfy my curiosity about how many songs stayed stuck in my head for multiple years. To get this answer, I recreated my dataset as a table inside Microsoft SQL Server and ran some queries.
Judging from the initial visualization, there are a lot of songs that have been in the Top 100 for at least 2 years. I thought that view would still be too noisy, so I decided to identify songs that have appeared on a minimum of 3 Wrapped playlists.
Now that I have the list, I can filter for them only. It’s more straightforward to analyze how these 18 songs have trended in my 6 years of Spotify listening.
Most of these songs started off hot and trended down the charts over the new few years, but some others like Door Closed by Kid Quill were discovered by me late in the year and rose up the charts in the following one.
Lost in a Room by Rome Hero Foxes had an interesting story- the song was my #11 in 2020, dropped to #50 in 2021, and bounced back into the top 20 in 2022.
One more thing about songs: I wondered how many tracks “carried over” from year to year, or, how many Top 100 songs from a given year were in the Top 100 in the next one. I wrote a query* to find out.
declare @yr INT = 2017
while @yr <2022
begin
with carryoverpercent as
(select song_name,
case when COUNT(song_name) = 2 then 1
else 0 end as carryover
from TopSpotifySongs.dbo.song_t
where year in (@yr,@yr+1)
group by song_name
)
select @yr+1 as year,
sum(carryover) as carryovers_from_prev_year
from carryoverpercent
set @yr = @yr+1
end;
For the past 3 years, about a quarter of my Top 100 songs from the year “carried over” to the following year. Let’s see if that trend continues for 2023’s Wrapped!
This dataset also allowed me to create some visualizations about the artists who appeared in my Wrapped playlists. Naturally, I had to know who my top artists were. So I ordered them by the number of total song appearances on my Wrapped playlists. I made use of calculated fields to help me note the number of distinct songs.
Lastly, I created another view with year as a dimension to create a “heat map” of which artists were hot and when.
The peak of the Kid Quill era looks behind me. On the other hand, Laufey, an artist I discovered in the past year, burst onto the scene with 8 songs in my 2022 Wrapped playlist.
Conclusion
I had a blast working on this data project. It was fun and nostalgia-inducing to visualize 6 years of listening history in this dynamic way. Thanks for reading!
Link to Tableau Dashboard
*note: the query relies on the fact that there have been no songs with identical titles released from different artists in my data set. Because I listen to international artists, some artists and song names got wonky during the CSV export and I was unable to count artist-song pairings accurately. This is an issue I hope to work out in a future iteration of this project.
