Skip to content

Explore your Spotify streaming history, playlists & tracks library using Metabase.

Notifications You must be signed in to change notification settings

Wallerand/spotify-unwrapped

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

3 Commits
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

Spotify Unwrapped 👀

Explore your Spotify streaming history, playlists & tracks library using Metabase.

Works locally without third-party or API, from GDPR request data.

Sample question example

Getting started

  1. Request your Spotify "Account data" and "Extended streaming history" from account privacy settings

  2. Place following JSON files to "dataset" folder :

    • Streaming_History_Audio_*.json
    • Playlist*.json
    • YourLibrary.json
  3. Launch services via docker-compose up -d

  4. Open http://localhost:3000 and set up Metabase by creating an account and adding the following MongoDB database :

    mongodb://admin:password@spotify-database/spotify?retryWrites=true&w=majority&authSource=admin&readPreference=nearest

Fields details

Streaming history

Field Type Description
ts Datetime This field is a timestamp indicating when the track stopped playing in UTC (Coordinated Universal Time). The order is year, month and day followed by a timestamp in military time
username String This field is your Spotify username.
platform String This field is the platform used when streaming the track (e.g. Android OS, Google Chromecast).
ms_played Integer This field is the number of milliseconds the stream was played.
conn_country String This field is the country code of the country where the stream was played (e.g. SE - Sweden).
ip_addr_decrypted String This field contains the IP address logged when streaming the track.
user_agent_decrypted String This field contains the user agent used when streaming the track (e.g. a browser, like Mozilla Firefox, or Safari).
master_metadata_track_name String This field is the name of the track.
master_metadata_album_artist_name String This field is the name of the artist, band or podcast.
master_metadata_album_album_name String This field is the name of the album of the track.
spotify_track_uri String A Spotify URI, uniquely identifying the track in the form of “spotify:track:”.
episode_name String This field contains the name of the episode of the podcast.
episode_show_name String This field contains the name of the show of the podcast.
spotify_episode_uri String A Spotify Episode URI, uniquely identifying the podcast episode in the form of “spotify:episode:”.
reason_start String This field is a value telling why the track started (e.g. “trackdone”, see below).
reason_end String This field is a value telling why the track ended (e.g. “endplay”, see below).
shuffle Boolean This field has the value True or False depending on if shuffle mode was used when playing the track.
skipped Boolean This field indicates if the user skipped to the next song.
offline Boolean This field indicates whether the track was played in offline mode (“True”) or not (“False”).
offline_timestamp Integer This field is a timestamp of when offline mode was used, if used.
incognito_mode Boolean This field indicates whether the track was played in incognito mode (“True”) or not (“False”).

reason_start identified values :

  • appload
  • backbtn
  • clickrow
  • clickside
  • fwdbtn
  • persisted
  • playbtn
  • popup
  • remote
  • trackdone
  • trackerror
  • unknown
  • uriopen

reason_end identified values :

  • backbtn
  • clickrow
  • clickside
  • endplay
  • fwdbtn
  • logout
  • playbtn
  • popup
  • remote
  • trackdone
  • trackerror
  • unexpected-exit
  • unexpected-exit-while-paused
  • unknown
  • uriopen

Playlists

Field Type Description
name String Playlist name.
lastModifiedDate Datetime Last modified date (note: no time given).
description String Playlist description.
tracksCount Integer Number of tracks for given playlist.
numberOfFollowers Integer Number of followers for given playlist.
items Array Playlist tracks.

Miscellaneous

Querying MongoDB

Mongo database is queryable by running :

$ docker exec -it spotify-database mongosh spotify \ 
    --username admin --password password --authenticationDatabase admin

Re-importing data

Data can be re-imported by running :

$ docker stop spotify-database
$ docker rm spotify-database
$ docker volume rm spotify-db
$ docker-compose up -d database

Duration conversion cheatsheet

To convert milliseconds to human-readable unit :

Wanted unit Operation
ms to seconds divide by 1 000
ms to minutes divide by 60 000
ms to hours divide by 3 600 000
ms to days divide by 86 400 000