Unwrapping the Wrapper
It is the “wrap” season, and I am not referring to Christmas presents. I’m talking about “Spotify Wrapped”. On Wednesday, 3rd December, Spotify users received their wrap, and as usual, Nigerians flooded social media with their wrappers showing their personalised stats. Not to sound like a hater, but abeg, can you keep it to yourself? I mean, there’s a reason why you are the only one seeing it nau.
I am not judging sha, we see, we don’t judge. I want to discuss something else with you. I am here to break down the analysis behind the Spotify wrap, and you will eventually realise it’s not that deep (make your body calm down small).
Database Description
All things being equal, I will assume I am a Data Analyst at Spotify (manifesting into existence), and there is a database containing the following tables with their respective fields
- user_info: storing data about the users, i.e.,
user_id: the unique number that differntiates a user from another user
user_name: the name of the user
user_email: the email the user registered with
phone_number: the phone number the user registered with
location: where the user is located
account_tier: describes if the user is on the free, indiviudal_premium, duo_premium, family_premium
- track_info: stores information about songs on the app, i.e.,
song_id: the unique key identifying the song
song_title: the title of the song
song_length: the length of the song
artiste_id: a unique number key for each artiste
artiste_name: the name of the artiste who made the song
image_cover: the cover image of the song
audio: the song itself
- track_listened: it keeps data about the songs the user listened to, i.e.
user_id: this is to establish a relationship between this table and the user_info table
song_id: this is to establish a relationship between this table and the track_info table
artiste_id: this is to establish a relationship between this table and the track_info table
minutes_played: how long the user listened to the song out of the total length
play_count: the number of times a song was played,
date_played: the date a song was played
Disclaimer: I don’t know what tables Spotify uses to store their customers’ data; these are all assumptions based on my knowledge of relational databases. Also, this write-up doesn’t cover the basics of SQL. For more information or clarification on the code, kindly check YouTube or leave a comment!
Stats Analysis
I will “speak” Structured Query Language (SQL) to communicate with the database. Using my wrap as a case study, I will show some of the commands (queries) I can use to get my stats displayed.
Let’s assume the following details were what I registered on the app with
user_id: tiwatope_123
user_name: Tiwatope Olorunshola
user_email: olorunshola22@xyz.com
phone_number: +1–340–762–1957
location: Lagos
account_tier: Individual_premium
Typically, Spotify greets you as you check out your wrap. Dem no go greet ke? Me wey dey use premium. Ok, that’s enough fooling around; the query to get “Tiwatope’s Olorunshola” name will be from the database is
SELECT user_name FROM user_info
WHERE user_id = "tiwatope123";
The image above says I spent 2773 minutes listening to music. As of 3rd December, I’m guessing that that’s about 485,280 minutes out of 525,600 minutes a year (do the math!), which is approximately 0.6% of my time listening to music in 2024. Hopefully, the number will rise in 2025. The information in the above image can be retrieved using the query below.
SELECT SUM(minutes_listened) FROM track_listened
INNER JOIN on user_info.artiste_id = track_listened.artiste_id
WHERE user_info.user_id = "tiwatope123";
On 28th October, I spent 199 minutes (approximately 3.3 hours) listening to music (omo wetin I dey use my life do o). The command to display this information is
SELECT SUM(track_listened.minutes_listened) AS total_minutes
FROM track_listened
INNER JOIN on user_info.artiste_id = track_listened.artiste_id
WHERE user_info.user_id = "tiwatope123"
GROUP BY date_played;
ORDER BY minutes_listened DESC
LIMIT 1;
According to the analysis, I listened to 258 unique songs this year. I’m amazed because I know I didn’t finish listening to all the songs! I’m pretty sure I’m not the only lazy person who prefers to listen to the same songs again rather than find new ones. The SQL command to obtain this information from the database is
SELECT COUNT (DISTINCT track_info.song_title) AS total_song_played
FROM track_info
INNER JOIN on track_info.artiste_id = track_listened.artiste_id
INNER JOIN on user_info.artiste_id = track_listened.artiste_id
WHERE user_info.user_id = "tiwatope123";
This section is the most anticipated part of the wrap: seeing which songs you listened to the most. This part is your cue to listen to this track! Forrest Frank and Huvley are great artists! This song kept my spirit high during low moments! Enough of digressing; as a Data Analyst, if I wanted to get this information, I would use the following commands
SELECT MAX(track_listened.play_count) AS highest_play, track_info.song_title,
track_info.artiste_name FROM track_info
INNER JOIN on track_info.artiste_id = track_listened.artiste_id
INNER JOIN on user_info.artiste_id = track_listened.artiste_id
WHERE user_info.user_id = "tiwatope123";
Drumrolls, please; these are the top five songs I listened to. I only recently discovered Alex Jean’s discography, and I can tell you his songs are good! The query to get this information from the database is
SELECT SUM(track_info.play_count) AS total_play, track_listened.song_title,
track_listened.artiste_name FROM track_info
INNER JOIN track_listened ON track_info.artiste_id = track_listened.artiste_id
INNER JOIN track_listened ON user_info.artiste_id = track_listened.artiste_id
WHERE user_info.user_id = "tiwatope123"
GROUP BY track_listened.song_title
ORDER BY play_count DESC
LIMIT 5;
Ladies and gentlemen, give it up for Alex Jean! I expected someone else to take the top spot. Nevertheless, it is a tremendous honour to be among the top 2% of about 650,000 monthly listeners (Alex Jean’s monthly listeners as of this writing). The query to extract this information is
SELECT SUM(track_listened.minutes_played) AS total_minutes,
track_info.artiste_name FROM track_info
INNER JOIN track_listened ON track_info.artiste_id = track_listened.artiste_id
INNER JOIN track_listened ON user_info.artiste_id = track_listened.artiste_id
WHERE user_info.user_id = "tiwatope123"
GROUP BY track_info.artiste_name
ORDER BY SUM(minutes_played) DESC
LIMIT 1;
The previous sections already displayed my top 5 songs and my top artist. In addition to Alex Jean, other artists made it to the top 5. I’m somewhat sure that the difference between Forrest Frank and Huvley is minimal, but the stats don’t lie, hmm…maybe. You know what, forget I said, the query to show this information is similar to the one that gave Alex Jean as the top artiste; the only difference is I changed the LIMIT to five instead of one.
SELECT SUM(track_listened.minutes_played) AS total_minutes,
track_info.artiste_name FROM track_info
INNER JOIN track_listened ON track_info.artiste_id = track_listened.artiste_id
INNER JOIN track_listened ON user_info.artiste_id = track_listened.artiste_id
WHERE user_info.user_id = "tiwatope123"
GROUP BY track_info.artiste_name
ORDER BY SUM(minutes_played) DESC
LIMIT 5;Recommendations
I recommend you check out songs by Forrest Frank and Huvley. Also, keep your wrap to yourself (I no dey judge o).
Conclusion
Spotify Wrap is a marketing genius strategy (two cold bottles of malt for the Head of Marketing). Spotify has harnessed the power of analytics to help their customers derive value from the app by providing them with personalised stats delivered in style! As you can see, there’s nothing there, na simple queries you dey wait 365 days for.
On this note, I wish you a merry Christmas in advance and an excellent 2025!
