{"id":289,"date":"2023-04-14T18:24:00","date_gmt":"2023-04-14T08:24:00","guid":{"rendered":"https:\/\/andyhowes.local\/?p=289"},"modified":"2024-04-15T03:21:27","modified_gmt":"2024-04-14T17:21:27","slug":"diy-spotify-wrapped-with-power-bi","status":"publish","type":"post","link":"https:\/\/andyhowes.local\/diy-spotify-wrapped-with-power-bi\/","title":{"rendered":"DIY Spotify Wrapped with Power BI"},"content":{"rendered":"\n<p>Spotify collects a lot of data about what we listen to. I used Power BI to find out what I could learn about my music preferences by analysing my Spotify data.<\/p>\n\n\n\n<p>Most streaming services provide a way for you to download the data they keep for you. <a href=\"https:\/\/support.spotify.com\/us\/article\/data-rights-and-privacy-settings\/\" target=\"_blank\" rel=\"noreferrer noopener\">Spotify<\/a> provides your data to you on request, although it can take a few weeks for them to process it.<\/p>\n\n\n\n<p>So I put in a request for my data to see what I could find out about my music habits over the last three years.<\/p>\n\n\n\n<p>Using Power BI, Microsoft\u2019s business intelligence tool, and taking advantage of the free Spotify API, I put together a detailed report into my last three years of streaming history. That\u2019s data from over 3,000 hours of listening to over 7,000 songs from 2600 artists.<\/p>\n\n\n\n<div class=\"wp-block-rank-math-toc-block\" id=\"rank-math-toc\"><h2>Contents<\/h2><nav><ul><li class=\"\"><a href=\"#spotify-power-bi\">Spotify Power BI<\/a><\/li><li class=\"\"><a href=\"#what-i-found\">What I found<\/a><\/li><li class=\"\"><a href=\"#how-it-works\">How it works<\/a><\/li><li class=\"\"><a href=\"#data-analytics\">Data analytics<\/a><\/li><li class=\"\"><a href=\"#whats-next\">What&#8217;s next?<\/a><\/li><li class=\"\"><a href=\"#fa-qs\">FAQs<\/a><ul><\/ul><\/li><\/ul><\/nav><\/div>\n\n\n\n<h2 class=\"wp-block-heading has-link-color wp-elements-30881baef1a51ea8859bd52b60f48cc1\" id=\"spotify-power-bi\"><a href=\"https:\/\/app.powerbi.com\/view?r=eyJrIjoiZTM1NjllM2YtMGEzOC00ZjIyLWJmMDYtNmI5MTk1ZjZmNjc3IiwidCI6ImJkZTA3MzY0LTczZDYtNGNkZC1hNTljLTY5NmNkNjlmNGNlNSJ9&amp;pageName=ReportSection\" target=\"_blank\" data-type=\"URL\" data-id=\"https:\/\/app.powerbi.com\/view?r=eyJrIjoiZTM1NjllM2YtMGEzOC00ZjIyLWJmMDYtNmI5MTk1ZjZmNjc3IiwidCI6ImJkZTA3MzY0LTczZDYtNGNkZC1hNTljLTY5NmNkNjlmNGNlNSJ9&amp;pageName=ReportSection\" rel=\"noreferrer noopener\">Spotify Power BI<\/a><\/h2>\n\n\n\n<iframe loading=\"lazy\" title=\"Spotify\" width=\"100%\" height=\"486\" src=\"https:\/\/app.powerbi.com\/view?r=eyJrIjoiZTM1NjllM2YtMGEzOC00ZjIyLWJmMDYtNmI5MTk1ZjZmNjc3IiwidCI6ImJkZTA3MzY0LTczZDYtNGNkZC1hNTljLTY5NmNkNjlmNGNlNSJ9&#038;pageName=ReportSection\" frameborder=\"0\" allowFullScreen=\"true\"><\/iframe>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"what-i-found\">What I found<\/h2>\n\n\n\n<p>I didn&#8217;t realise I was such a Harry Styles fan! I&#8217;ve spent the equivalent of five days listening to Harry. I&#8217;m putting this down to getting into Fine Line late and then getting way into Harry&#8217;s House as soon as it dropped in 2022.<\/p>\n\n\n\n<div class=\"wp-block-columns is-layout-flex wp-container-core-columns-is-layout-1 wp-block-columns-is-layout-flex\">\n<div class=\"wp-block-column is-layout-flow wp-block-column-is-layout-flow\">\n<p>I also discovered that I can&#8217;t seem to stick with any artist, album or song for too long. None of my top songs, artists or album stay in the top spot for more than a year.<\/p>\n\n\n\n<p>But while the songs may change, the type of song doesn&#8217;t vary much at all. Thanks to Spotify&#8217;s API, I processed audio feature for every song that I streamed. This provides information on the  danciness, energy levels, tempo and much more data for each song.<\/p>\n<\/div>\n\n\n\n<div class=\"wp-block-column is-layout-flow wp-block-column-is-layout-flow\">\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"514\" src=\"https:\/\/andyhowes.local\/wp-content\/uploads\/2023\/05\/image-1024x514.png\" alt=\"\" class=\"wp-image-293\" style=\"width:660px;height:331px\" srcset=\"https:\/\/andyhowes.local\/wp-content\/uploads\/2023\/05\/image-1024x514.png 1024w, https:\/\/andyhowes.local\/wp-content\/uploads\/2023\/05\/image-300x151.png 300w, https:\/\/andyhowes.local\/wp-content\/uploads\/2023\/05\/image-768x386.png 768w, https:\/\/andyhowes.local\/wp-content\/uploads\/2023\/05\/image-1536x772.png 1536w, https:\/\/andyhowes.local\/wp-content\/uploads\/2023\/05\/image-2048x1029.png 2048w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n<\/div>\n<\/div>\n\n\n\n<p>Analysing that data showed that I like songs that are danceable, high energy and positive. My preferences also don&#8217;t change much over time.<\/p>\n\n\n\n<figure class=\"wp-block-image aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"940\" height=\"705\" src=\"https:\/\/andyhowes.local\/wp-content\/uploads\/2023\/05\/Spotify-BI-scatter-chart.png\" alt=\"A Power BI scatter chart showing Spotify songs listened to by the energy and danceability of the song. It shows that most songs listened to have high danceability and high energy.\" class=\"wp-image-346\" style=\"width:470px;height:353px\" srcset=\"https:\/\/andyhowes.local\/wp-content\/uploads\/2023\/05\/Spotify-BI-scatter-chart.png 940w, https:\/\/andyhowes.local\/wp-content\/uploads\/2023\/05\/Spotify-BI-scatter-chart-300x225.png 300w, https:\/\/andyhowes.local\/wp-content\/uploads\/2023\/05\/Spotify-BI-scatter-chart-768x576.png 768w\" sizes=\"(max-width: 940px) 100vw, 940px\" \/><figcaption class=\"wp-element-caption\">This scatter chart shows my song preferences by danceability and energy. The bigger the dot, the more I listened to that song. The colour of the dot shows the time signature of the song.<\/figcaption><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"how-it-works\">How it works<\/h2>\n\n\n\n<p>Spotify provides the data in several JSON files. Those files contain a lot of different types of data, but I was only interested in my streaming history.<\/p>\n\n\n\n<p>I used Power Query M in Power BI to transform the streaming history JSON files into a tabular format. Each track played includes a name, artist, album and a unique Spotify identifier to identify it.<\/p>\n\n\n\n<p>As always, the data is never as clean as you expect it to be. Some plays were missing their unique identifiers and the most recent data came in a different format to the extended data.<\/p>\n\n\n\n<p>Luckily, Spotify provide their <a href=\"https:\/\/developer.spotify.com\/documentation\/web-api\" target=\"_blank\" rel=\"noopener\">REST API<\/a> for free, which I could take advantage of to overcome some of these limitations. For the plays without unique IDs, I removed all duplicates and queried the database to retrieve their unique ID.<\/p>\n\n\n\n<p> I wrote Power Query M functions to use the API to:<\/p>\n\n\n\n<ul>\n<li>Retrieve the unique ID for songs without them, by searching by title, artist and album<\/li>\n\n\n\n<li>Retrieve song information from the unique ID<\/li>\n\n\n\n<li>Retrieve audio features from the unique ID<\/li>\n<\/ul>\n\n\n\n<p>You can find all these functions in my <a href=\"https:\/\/github.com\/YesWeCandrew\/PBI-Spotify\" target=\"_blank\" rel=\"noopener\">Github repository<\/a>. I used <a href=\"https:\/\/excelguru.ca\/connect-to-spotify-using-power-query\/\" data-type=\"URL\" data-id=\"https:\/\/excelguru.ca\/connect-to-spotify-using-power-query\/\" target=\"_blank\" rel=\"noreferrer noopener\">Ken Puls&#8217;<\/a> blog to help create a function to retrieve an OAuth token from Spotify. This method isn&#8217;t perfect and you need to be careful not to expose your API secret in the file when you share it.<\/p>\n\n\n\n<p>While Spotify&#8217;s API is free, it does have a rate limit. Sending it <a href=\"https:\/\/developer.spotify.com\/documentation\/web-api\/reference\/get-track\" data-type=\"URL\" data-id=\"https:\/\/developer.spotify.com\/documentation\/web-api\/reference\/get-track\" target=\"_blank\" rel=\"noreferrer noopener\">one query<\/a> for every single song in the entire database would hit this limit and be inefficient. Instead, I grouped the songs into blocks of less than 50 songs. This enabled me to send a <a href=\"https:\/\/developer.spotify.com\/documentation\/web-api\/reference\/get-several-tracks\" data-type=\"URL\" data-id=\"https:\/\/developer.spotify.com\/documentation\/web-api\/reference\/get-several-tracks\" target=\"_blank\" rel=\"noreferrer noopener\">request for several tracks<\/a> information at once, reducing the queries to the API from 5000+ to around 100. I used the same trick to then retrieve each song&#8217;s <a href=\"https:\/\/developer.spotify.com\/documentation\/web-api\/reference\/get-several-audio-features\" data-type=\"URL\" data-id=\"https:\/\/developer.spotify.com\/documentation\/web-api\/reference\/get-several-audio-features\" target=\"_blank\" rel=\"noopener\">audio features<\/a>. <\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"data-analytics\">Data analytics<\/h2>\n\n\n\n<p>Once the data modelling was complete, it was time to get to work analysing and visualising the data. I began with basic analytics, such as time spent listening in minutes, hours and days. <\/p>\n\n\n\n<p>Calculating a value for number of plays for each song is slightly harder and requires a bit of a judgement call. Does listening to a song for 30 seconds count as a play? What if that song is only 30 seconds long? I decided to count any listen that is above one minute as a song played, although this does exclude sub one minute long songs.<\/p>\n\n\n\n<p>I also enabled What-If parameters to allow end-users to customise the report. On the second page, viewers can select the number of favourite artists to display from one to twenty. Measures in the report then dynamically change the number of favourites they display. <\/p>\n\n\n\n<p>To do this, I used the RANKX function and SWITCH to ensure that even if no number was selected, a value would be returned. You can see the DAX code below.<\/p>\n\n\n\n<pre class=\"wp-block-code has-base-color has-contrast-background-color has-text-color has-background has-small-font-size\" style=\"border-style:none;border-width:0rem;padding-top:var(--wp--preset--spacing--30);padding-right:var(--wp--preset--spacing--30);padding-bottom:var(--wp--preset--spacing--30);padding-left:var(--wp--preset--spacing--30)\"><code>Plays by top x artist = \nVAR NumberOfArtists = SELECTEDVALUE('Top N to Display'&#91;Top number to display], 6)\nRETURN\nSWITCH(\n    TRUE(),\n    CALCULATE(\n        \/\/ Calculates a ranking of all artists by plays\n        RANKX(\n            ALLSELECTED('Artists'), \n            &#91;Plays]\n            ) &lt;= NumberOfArtists,\n        \/\/ Ensures that the context for the ranking is filtered by year only, to stop function from calculating top X artists per month, day etc\n       ALLEXCEPT('Date','Date'&#91;Year])),\n    &#91;Plays]\n)<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"whats-next\">What&#8217;s next?<\/h2>\n\n\n\n<p>I&#8217;ve only scrapped the surface of the insights that you can gather from this data. I&#8217;m hoping to do more analysis on it in the future.<\/p>\n\n\n\n<p>In the meantime, enjoy having a sticky-beak at my listening habits. If you find something interesting, <a href=\"https:\/\/andyhowes.local\/contact\/\" data-type=\"page\" data-id=\"163\">let me know<\/a>!<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"fa-qs\">FAQs<\/h2>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"im-getting-a-formula-firewall-error-when-querying-the-spotify-api-how-can-i-resolve-this\">I&#8217;m getting a Formula.Firewall error when querying the Spotify API. How can I resolve this?<\/h4>\n\n\n\n<p>I&#8217;ve had some reports of people coming up against the Formula.Firewall error when working with this dataset. The error looks like this:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Formula.Firewall: Query 'query' (step 'step') references other queries or steps and so may not directly access a data source. Please rebuild this data combination.<\/code><\/pre>\n\n\n\n<p>I&#8217;ve written a blog post that outlines what this error is and <a href=\"https:\/\/andyhowes.local\/handling-the-power-query-formula-firewall-error\/\" data-type=\"post\" data-id=\"595\">how to resolve it here<\/a>!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Spotify collects a lot of data about what we listen to. I used Power BI to find out what I could learn about my music preferences by analysing my Spotify data.<\/p>\n","protected":false},"author":1,"featured_media":346,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8,7],"tags":[23,22,18,21,27,28,25,16,24,26,29,17,20],"_links":{"self":[{"href":"https:\/\/andyhowes.local\/wp-json\/wp\/v2\/posts\/289"}],"collection":[{"href":"https:\/\/andyhowes.local\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/andyhowes.local\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/andyhowes.local\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/andyhowes.local\/wp-json\/wp\/v2\/comments?post=289"}],"version-history":[{"count":23,"href":"https:\/\/andyhowes.local\/wp-json\/wp\/v2\/posts\/289\/revisions"}],"predecessor-version":[{"id":603,"href":"https:\/\/andyhowes.local\/wp-json\/wp\/v2\/posts\/289\/revisions\/603"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/andyhowes.local\/wp-json\/wp\/v2\/media\/346"}],"wp:attachment":[{"href":"https:\/\/andyhowes.local\/wp-json\/wp\/v2\/media?parent=289"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/andyhowes.local\/wp-json\/wp\/v2\/categories?post=289"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/andyhowes.local\/wp-json\/wp\/v2\/tags?post=289"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}