Analyze your Spotify data with Metriql

[buremba]

We will develop an app that lets Spotify users see their playlist/song data in a dashboard and run queries on Metriql. The aim of the project is to let data people interact with their actual data in Metriql and see how they can use it in a simple way.

User flow

1. The user will click a Login with Spotify button that redirects the user to Spotify and authenticates using OAuth.
2. Once the user is redirected back to the app, the app will start loading users' Spotify data into our Postgresql database.
   a. The data that we need to load includes the saved tracks and the songs in the user playlists. We should enrich the data and add info such as tempo, energy, etc. similar to cumul.io's Spotify project.
   b. Ideally we can perform these requests on users' browsers to push the work to the user's device if Spotify API supports CORS. This approach can also help us put a progress bar and be more responsive in loading the data. Once we load all the songs into an array, we can upload it to our server which creates a Postgresql table using the username of the user as a table name and load all the data into that table. Once we have the data in a table, we can create a Postgresql user and give the user access to that table. You can generate a random password and then show it to the user on the next screen.
   c. Once we have the data, we should define the metrics with Metriql deploy a Metriql server that will run the queries.
   d. The last step is a creating sample dashboard in an open-source BI tool such as Metabase or Superset. We can just manually create a dashboard from our data and make the table name to be a dashboard parameter. When embedding the dashboard, use locked parameters and use the table name as the value of the parameter. The dashboard will be embedded in the page and we will also put a link to the Metriql server so that the users can analyze the data in their preferred data tool.

Resources

Postgresql database:

We can use local Postgresql, I will provide the credentials of the production database later on)

The Javascript / HTML project that will access Spotify and load the data using its API into our server.

The HTML part is rather easy, there will be a Login with Spotify button, preferably a progress bar and an embedded dashboard. For the Javascript part that interacts with Spotify, we can use cumulio-spotify's codebase.

Metriql server

You can deploy Metriql locally and publish the data models in the Github repository. We will run the dbt using Github Actions and publish the dbt Docs on Github similar to https://github.com/metriql/metriql-public-demo and use the manifest.json URL to start Metriql. The users will use their Postgresql credentials to connect Metriql so that we can only give them the access to see their data.

Metabase server

We're not tied to Metabase. We can use Rakam as well. Once the other steps are done, we can discuss the solution accordingly. Ideally, we want to create a dashboard and parametrize the table name. As the table schema will be the same, we can model the data only once and embed the dashboard into our page that will not let the user change the parameter thus only see his/her data.

[vintageplayer]

@buremba

• I'm using the same scope as cumulio while requesting user access token from spotify: user-read-private user-read-email playlist-read-collaborative playlist-read-private playlist-modify-public playlist-modify-private.
• Regarding Spotify CORS request, once the login is successful, the access token can be returned to the client device. With access token, the Spotify Web Api can be made without any IP based restriction. So we push the work to client and show the progress bar.
• Also as confirmed on slack, will use Postgres Version 14 in dev.

[TuanaCelik]

@buremba as we did in our open source project feel free to use Cumul.io as your visualization layer. And If you do I would be happy to help. We are hosting the project cumulio-spotify as a working web app here: cumusicalio.cumul.io

[buremba]

@TuanaCelik Sure, initially we will use an open-source dashboarding solution but it will be open-source as well so we can also use cumul.io's dashboards as an alternative. Looking at the integrations, I don't see Trino / Prestodb though, do you have any plans to integrate them?