How to Back-Fill Historical Spotify Data

This guide will walk you through the process of importing your extended Spotify listening history into the Smart Playlist Builder. By following these steps, you can back-fill your database with older play data, ensuring your analytics and playlists reflect your complete listening journey.

1. Request Extended History from Spotify

1. Log in to your Spotify account on the Spotify website.
2. Navigate to the Privacy Settings or Data Download section.
3. Request a download of your extended listening history data in JSON format.
4. Wait for Spotify to prepare and send you the data files via email or download link.

2. Import JSON Files into Google Sheets

1. Create a new Google Sheet.
2. Install and open the JSON import add-on.
3. Use the add-on to import all Spotify JSON history files.
   • Important: Do NOT select “merge data from all files together.” Import each file so that all rows append into a single sheet.
4. Add a filter to the header row and filter the column named spotify_episode_uri (if present) to exclude blanks, then delete all visible rows.
   • This column looks like: spotify:episode:6MQeLMh23l9D0piX6CUmaVm. These are podcasts. After deleting them, only music tracks should remain.
5. Delete all columns except the following (renaming is not required):
   1. ts
   2. master_metadata_track_name
   3. master_metadata_album_artist_name
   4. master_metadata_album_album_name
   5. spotify_track_uri
6. Find & Replace to extract the Spotify Track ID:
   • Find: spotify:track:
   • Replace with: (leave blank)
   • This converts values like spotify:track:4j0dhzx1BiPKCkMPxyj9eO → 4j0dhzx1BiPKCkMPxyj9eO.
7. Remove duplicates so you only import clean rows:
   1. Select the entire table (all remaining columns).
   2. Go to Data → Data cleanup → Remove duplicates.
   3. Tick My data has header row.
   4. Click Deselect all, then select only ts and spotify_track_uri.
   5. Run Remove duplicates.
8. Download the sheet as a CSV file (File → Download → Comma-separated values).

3. Import the Cleaned CSV into the Database

You will import into the existing spotify_play_history table. Map your CSV columns to the DB columns as follows:

Tip (TablePlus on macOS): Right‑click the table → Import → From CSV, map columns as above, and run. Other tools have similar CSV import flows.

4. Run the Backlog Action to Enrich Missing Fields

1. Open GitHub and go to the repository’s Actions tab.
2. Find the workflow named zzzzzz_999999_Backfill Spotify Play History (manual dispatch).
3. Run the workflow. This job fills in missing fields in spotify_play_history (e.g., artist_id, album_id, album_type, and duration_ms) in small batches.
4. Note: It may take a while depending on volume and API rate‑limits. You can re‑run as needed; it is idempotent.

Heads‑up: If you run playlist builds before backfill completes, any rows with blank fields will be skipped by the build logic.