- Analyse Spotify Extended Streaming History using MySQL and PHP
If you spend a lot of time listening to Spotify, you are probably familiar with Spotify Unwrapped, which provides a summary for the year of things like top artists, tracks, time spent listening to music etc.
If you want a more detailed view of your listening activity on Spotify, you can ask to get your extended streaming history emailed to you.
There are various websites which will analyse your stats for you.
There are two versions which you have to pay for:
There are also some free options, such as:
I wanted to make a website which allowed me to search through my listening history, export the data to Excel, do charts etc.
This is my attempt at that.
It's not as slick as Curio and Explorify since those store the data in the browser and nothing is stored in a database or server.
If you are comfortable with installing things on your computer I have written instructions showing how you can set things up so you can import your listening data into a database and run a website on a local server on your computer.
I have loaded in data for me and some people in my family and put it on a public website and it seems to work well enough, but you can also run the site on your local computer, so you don't have to pay for web hosting or buy a domain name.
Instructions are covered in the docs - here are the pages:
- 01 - Screenshots
- 02 - Request Spotify Data
- 03 - Install Xampp
- 04 - Set up Spotify History Database
- 05 - Set up Spotify History Website
- 06 - Load data from JSON files into database
- 07 - Useful SQL
Homepage provides a summary at the top of the page across all years, showing:
- Year
- Total:
- Minutes
- Hours
- Distinct Artist Count
- Distinct Track Count
- Sum of plays
Slightly further down the page, listening activity is displayed in a bar chart:
See a summary of tracks - for all time, or using the links at the top of the page, narrow down by year.
Example of narrowing down by year:
See a summary of artists - for all time:
You can use year the links at the top of the page to focus on a specific year:
Use the Year links at the top of the page to focus on a specific year.
At the bottom of the page a summary shows listening minutes per hour of the day and per day of the week. Use the Year links at the top of the page to focus on a specific year.
Search for / see data for artists.
Click on the link in the Albums column to see the album summary listing for that artist.
See charts showing listening history across all years and months for an artist, the further down the page, see a summary per year.
Search for / see data for albums.
Click on the link in the Albums column to see the album summary listing for that artist.
Search for / see data for tracks.
Listening activity per day for all days.
There are two sections on this page.
Section listing artists where only one track has been listened to, ordered by most time listened to, in descending order.
Lists artists which have only been listened to that year, and not during any of the other years of your listening history.
Data can be copied to clipboard, or exported to CSV or Excel:
Use the Column visiblity button to show / hide columns:
The search form on any data grid can be used to quickly filter results:
Column headings can be clicked to sort data:
Where relevant, there are next / previous links at the bottom of the data grids:
You can click and drag column headings and change the order of the columns.
The icons / links in the Artist # column are:
- Chart - see bar graph charting listening activity for artist over the years
- Calendar - track summary for artist - showing which tracks you have listened to for that artist (showing first listened to, last listened to, listening time and play count)
- Clock - detailed track listening history for artist
- Search icon - album summary for artist - showing which albums you have listened to for that artist (showing first listened to, last listened to, number of tracks listened to, listening time and play count).
Click on the link in the Track column to view a detailed listening history for that track.
This page provides a summary of shows listened to. Click the show link to see a detail list of episodes that were listened to for that show.
This shows an example of data provided when drilling down to view episodes for a show:
- The way I have written the PHP code in this project is very basic and clunky.
- If a real programmer were to look at the code they'd find countless bugs, issues, shout-out-loud errors and silly ways of doing things.
- Lots of things are repeated, not a lot is re-used, so I haven't followed the Don't Repeat Yourself rule.
- I think I have taken a Procedural approach to writing the PHP as well, which is not ideal.
- There is also lots of spaghetti code, so all in all while this site work, there are 1,001 reasons why it shouldn't be used as an example of the correct way to do things.
- For my basic hobbyist needs it works for me so I have put the code on Github in case anyone else finds it useful.
- If you have any problems with this repository, please raise a new issue.