Créez votre propre Spotify Wrapped avec SQL (parce que pourquoi pas ?)

Tout le monde montre son Spotify Wrapped sur les réseaux sociaux, mais si vous pouviez faire le vôtre et voir tout ce que Spotify ne montre pas ? Avec un peu de SQL, vous pouvez creuser dans vos habitudes d'écoute, voir vos vrais titres préférés, et même découvrir combien de temps vous avez passé à faire tourner en boucle cette chanson coup de cœur. C'est facile, amusant et c'est un excellent moyen d'utiliser vos compétences en SQL. Dans cet article, je vais vous montrer comment faire.

Vous savez que Spotify Wrapped est publié chaque année, et que tout le monde publie ses meilleurs titres, artistes et statistiques d'écoute ? C'est amusant, mais soyons honnêtes, parfois vous voulez plus que ce que Spotify décide de vous montrer. Par exemple, combien de temps de votre vie avez-vous gaspillé en écoutant cette chanson qui vous a fait culpabiliser ? Ou qui était vraiment votre artiste préféré si Spotify n'avait pas fait passer des podcasts en douce ?

Si vous avez toujours voulu vous amuser avec vos propres données musicales, j'ai quelque chose de cool pour vous : construisez votre propre Spotify Wrapped en utilisant SQL. Ce n'est pas aussi compliqué que ça en a l'air. Vous aurez un contrôle total sur vos données, vous pourrez vous vanter et vous aurez une bonne raison de pratiquer le SQL tout en créant des graphiques pour le plaisir.

Etape 1 : Télécharger vos données Spotify

La première chose à faire est de télécharger vos données d'écoute depuis Spotify. C'est étonnamment facile - Spotify est légalement tenu de vous donner accès à toutes les données personnelles qu'ils ont collectées, et ils ont rendu le processus assez simple.

Voici ce que vous devez faire :

• Allez sur la page Confidentialité de Spotify. Vous devrez vous connecter si ce n'est pas déjà fait. C'est là que Spotify conserve tous les outils de gestion et d'accès à vos données.
• Une fois que vous êtes connecté, faites défiler jusqu'à la section "Télécharger vos données". Cliquez dessus et Spotify vous demandera de confirmer la demande. Ce n'est pas instantané - Spotify prend un peu de temps pour tout mettre en place (1 à 2 jours, d'après mon expérience). Ils vous enverront un email lorsque les données seront prêtes, donc ne rafraîchissez pas votre boîte de réception comme je l'ai fait.
• Lorsque vous recevez l'e-mail, téléchargez le fichier ZIP qu'il vous fournit. À l'intérieur de ce fichier ZIP, vous trouverez plusieurs fichiers, mais celui qui nous intéresse est StreamingHistory.json. Ce fichier est une véritable mine d'or : il contient un journal détaillé de toutes les pistes que vous avez écoutées, de la date à laquelle vous les avez écoutées et de la durée de l'écoute. C'est en fait votre vie Spotify sous forme de données.

Attention : la première fois que j'ai consulté le fichier StreamingHistory.json, j'ai eu l'impression de pirater mes propres goûts musicaux. Ce n'est pas très joli à première vue - juste des lignes de texte avec des timestamps, des noms d'artistes et des titres de morceaux - mais croyez-moi, c'est super puissant une fois que vous le mettez au travail avec SQL.

Alors, attrapez ce fichier et commençons à transformer vos habitudes d'écoute en quelque chose de bien plus cool que Spotify Wrapped.

Etape 2 : Préparer vos données pour SQL

Voici le truc : Spotify vous donne vos données sous la forme d'un fichier JSON. Si vous ne connaissez pas, JSON (JavaScript Object Notation) est un moyen de stocker des données dans un format texte que les machines adorent, mais qui n'est pas très convivial pour les humains. Il convient parfaitement aux développeurs, mais SQL a besoin de vos données dans un format de tableau, comme une feuille de calcul. La première étape consiste donc à convertir StreamingHistory.json en fichier CSV.

Ne vous inquiétez pas, c'est plus facile qu'il n'y paraît. Vous n'avez pas besoin d'outils sophistiqués ou de connaissances en programmation : Excel, Google Sheets ou même un convertisseur en ligne de base feront l'affaire. Voici comment procéder :

Si vous utilisez Excel ou Google Sheets :

• Ouvrez Excel (ou Sheets) et recherchez l'option permettant d'importer un fichier. Dans Excel, vous allez dans Données -> Obtenir des données -> À partir d'un fichier -> JSON. Dans Google Sheets, c'est un peu plus manuel, vous aurez donc peut-être besoin d'un convertisseur en ligne.
• Téléchargez votre fichier StreamingHistory.json. Excel analysera le JSON et le transformera en lignes et en colonnes.

Si vous souhaitez une solution rapide en ligne :

Utilisez un outil comme json-csv.com ou similaire. Ces sites web vous permettent de télécharger votre fichier JSON et de le convertir en CSV en quelques secondes. Téléchargez le fichier résultant et vous êtes prêt à partir.

Que contient le fichier CSV ?

Une fois converti, votre fichier CSV ressemblera à une petite feuille de calcul bien ordonnée comportant quatre colonnes principales :

Column Name	Description
endTime	When you listened to the track (timestamp).
artistName	The name of the artist.
trackName	The title of the song you listened to.
msPlayed	How long you played the song (in milliseconds).

Par exemple :

endTime	artistName	trackName	msPlayed
2024-06-05 12:00	Metallica	Enter Sandman	325000
2024-06-05 12:05	Korn	Freak on a Leash	244000

Il s'agit de vos données brutes, l'épine dorsale de votre analyse. endTime vous permet de savoir exactement quand vous avez écouté une chanson, tandis que msPlayed vous indique combien de temps vous avez écouté. Une fois le fichier CSV prêt, vous pouvez le charger directement dans SQL et commencer à interroger vos habitudes d'écoute comme un pro.

Etape 3 : Charger les données dans PostgreSQL (pgAdmin)

Maintenant que vos données Spotify sont prêtes sous forme de fichier CSV, il est temps de les charger dans PostgreSQL en utilisant pgAdmin. C'est ici que nous transformons votre historique d'écoute brut en une table compatible avec SQL. Ne vous inquiétez pas, je vais vous guider tout au long du processus.

1. Créer la table dans pgAdmin

Tout d'abord, vous avez besoin d'une table pour contenir les données. Ouvrez pgAdmin et connectez-vous à votre base de données PostgreSQL. Une fois connecté :

1. Ouvrez une nouvelle fenêtre de requête(cliquez avec le bouton droit de la souris sur votre base de données -> Outil de requête).
2. Exécutez le script SQL suivant pour créer une table :

CREATE TABLE streaming_history (
    endTime TIMESTAMP,
    artistName VARCHAR(255),
    trackName VARCHAR(255),
    msPlayed INT
);
    

Cliquez sur le bouton Exécuter (l'icône de l'éclair), et votre table sera créée.

2. Importer le fichier CSV dans votre table

Chargez maintenant votre fichier CSV dans la table que vous venez de créer :

• Dans le menu de gauche de pgAdmin, développez votre base de données -> Schémas -> Tables.
• Trouvez votre table (streaming_history) et cliquez dessus avec le bouton droit de la souris.
• Sélectionnez Import/Export dans le menu.

Une nouvelle fenêtre s'ouvre. Voici ce qu'il faut faire :

• Nom de fichier : Naviguez jusqu'à l'emplacement de votre fichier CSV.
• Format : Sélectionnez CSV.
• Délimiteur : Définissez une virgule (,).
• En-tête : Assurez-vous que cette case est cochée si votre CSV comporte des noms de colonnes dans la première ligne.

Cliquez sur OK pour lancer le processus d'importation.

3. Vérifier les données

Pour vous assurer que tout a été chargé correctement, vous pouvez exécuter une simple requête SQL pour vérifier votre tableau :

SELECT * FROM streaming_history LIMIT 10;
    

Cette requête affichera les 10 premières lignes de votre table. Si vous voyez vos titres, noms d'artistes et durées de lecture bien rangés dans les colonnes, vous êtes prêt !

Étape 4 : Analyser votre liste de lecture à l'aide de requêtes SQL

Vous avez vos données dans PostgreSQL, et il est maintenant temps de libérer la puissance de SQL. Avec seulement quelques requêtes, vous pouvez obtenir des informations que même Spotify Wrapped ne montre pas. Prenez un café (ou mettez votre playlist préférée), et c'est parti.

Top 5 des titres les plus écoutés

Vous voulez savoir quelles sont les chansons que vous avez le plus écoutées ? Cette requête résume le temps que vous avez passé sur chaque piste et les classe par ordre décroissant.

SELECT trackName, artistName, SUM(msPlayed) / 60000 AS totalMinutes
FROM streaming_history
GROUP BY trackName, artistName
ORDER BY totalMinutes DESC
LIMIT 5;

Ce qu'elle fait:

• Regroupe vos données par nom de morceau et par artiste.
• Additionne le temps d'écoute total (en millisecondes) et le convertit en minutes.
• Trie les pistes par nombre total de minutes, en affichant les 5 premières.

Vous verrez enfin si la chanson que vous jurez n'avoir écoutée qu'une seule fois domine en fait votre année.

Durée d'écoute totale

Vous êtes-vous déjà demandé combien d'heures de votre vie vous avez passé à écouter de la musique cette année ? Voici comment le savoir :

SELECT SUM(msPlayed) / 3600000 AS totalHours
FROM streaming_history;

Ce que ça fait:

• Il additionne le temps total (en millisecondes) que vous avez passé à écouter des morceaux.
• Il le convertit en heures.

Vous risquez de rire, de pleurer ou de remettre en question vos choix de vie lorsque vous verrez ce chiffre. Quoi qu'il en soit, c'est une donnée amusante à connaître.

Tendances d'écoute mensuelles

Vous voulez savoir comment vos habitudes musicales ont évolué au fil des mois ? Cette requête le montre :

SELECT DATE_TRUNC('month', endTime) AS month, SUM(msPlayed) / 3600000 AS hours
FROM streaming_history
GROUP BY month
ORDER BY month;

Ce qu'elle fait:

• Regroupe vos données d'écoute par mois à l'aide de DATE_TRUNC.
• Additionne le temps d'écoute total et le convertit en heures.
• Elle trie les données par ordre chronologique pour que vous puissiez voir les tendances.

Vous remarquerez que vous écoutez plus pendant les voyages d'été, l'hibernation d'hiver ou les échéances stressantes.

Étape 5 : Visualisez vos données dans Power BI

Maintenant que vous avez obtenu vos informations grâce à SQL, il est temps de transformer ces résultats en visuels attrayants grâce à Power BI. Voici comment procéder :

Connecter Power BI à PostgreSQL

• Ouvrez Power BI.
• Allez dans Accueil -> Obtenir des données -> Base de données PostgreSQL.
• Saisissez le nom de votre serveur et les informations d'identification de la base de données.
• Chargez la table streaming_history dans Power BI.

Créez vos visuels Utilisez ces suggestions pour donner vie à vos données :

• Top Tracks: Diagramme à barres
• Axe X : Nom de la piste
• Axe Y : Nombre total de minutes d'écoute
• Principaux artistes: Diagramme circulaire
• Montre la proportion du temps passé sur chaque artiste.
• Tendances d'écoute mensuelles: Graphique linéaire
• Axe des X : Mois
• Axe des ordonnées : Nombre total d'heures d'écoute
• Durée totale d'écoute: Visualisation de la carte
• Affichez la durée totale d'écoute en heures sous la forme d'un gros chiffre en gras.
• Style comme Spotify
• Utilisez des thèmes sombres avec des couleurs vives (verts, violets, jaunes) pour donner l'impression d'écouter Spotify.
• Ajoutez des titres comme "Top 5 NuMetal Tracks" ou "Monthly Listening Habits" pour donner de la personnalité à votre tableau de bord.

Votre tableau de bord sera élégant, professionnel et prêt à être partagé. Voici mes 10 meilleurs morceaux de 2024 !

Étape 6 : Partager et flexibiliser

Une fois que votre tableau de bord Power BI est prêt, vous avez gagné le droit de le montrer. Voici comment procéder :

• Exportez-le: Enregistrez le tableau de bord au format PDF ou sous forme d'image.
• Partagez-le: Publiez-le sur les réseaux sociaux, envoyez-le à vos amis ou imprimez-le si vous voulez être à l'ancienne.
• Vantez-vous un peu: Faites savoir à tout le monde que vous ne vous contentez pas d'écouter du rock, mais que vous l'analysez comme un pro.

Imaginez que vous lâchiez des statistiques comme "J'ai passé 120 heures à écouter Metallica cette année" ou "Duality de Slipknot a été ma chanson numéro 1 avec 15 écoutes". Le respect est immédiat.

Réflexions finales

Construire votre propre Spotify Wrapped n'est pas seulement amusant - c'est un mélange de votre amour pour la musique et de la manipulation de données. Avec SQL, vous pouvez découvrir des informations que Spotify n'affiche pas, comme les titres que vous avez écoutés en boucle ou comment vos habitudes musicales ont évolué au cours de l'année.

Dans cet article, vous avez appris à :

• Utiliser SQL pour extraire vos Top Tracks, Top Artists, et les tendances d'écoute.
• Exécuter des requêtes simples mais puissantes telles que GROUP BY, SUM(), et DATE_TRUNC.
• Transformer toutes ces données en visuels clairs, de type Spotify, avec Power BI.

Le plus intéressant, c'est que les techniques SQL que vous avez utilisées (tri des données, regroupement et calcul des totaux) ne sont qu'un début. Si vous voulez en savoir plus et vous sentir vraiment à l'aise avec les données, consultez la piste SQL de A à Z sur LearnSQL.fr. Vous y apprendrez pas à pas tout ce que vous devez savoir, et vous pourrez travailler avec des données réelles comme celles-ci.

Avec SQL, vous pouvez analyser n'importe quoi : votre musique, vos données professionnelles ou même vos projets secondaires. C'est un outil qui ne s'arrête jamais.

Alors, prenez vos données, écrivez quelques requêtes supplémentaires et voyez à quel point le rock et le métal ont marqué votre année. Et si vous découvrez quelque chose de fou, comme le fait d'avoir joué "Espresso" 200 fois ou qu'Ariana Grande représente 80 % de votre liste de lecture, nous sommes tous passés par là, pas de jugement. Maintenant, voyons qui a vraiment dominé votre playlist !