Data Analysis of Spotify's Top 2000 Songs Case Study

pdf

School

Griffith University *

*We aren’t endorsed by this school

Course

131

Subject

Business

Date

Jun 13, 2024

Type

pdf

Pages

Uploaded by EarlLapwingMaster1006

BSB131 APPLIED BUSINESS ANALYTICS | Assessment 2 1 | P a g e Assessment 2 - Case Study - Spotify Top 2000 Length: 2000 Words Due: Friday 10 th May, 11:59PM Submission: Online (Canvas) Weight: 35% Instructions: • Data for this assessment can be found in the Excel file Dutch Top 2000 Spotify Songs.xlsx • This assessment covers the topics up to and including Hypothesis Testing. • You will be expected to manipulate the data using pivot tables, to create appropriate visuals (tables and graphs) to demonstrate outcomes and to conduct a range of statistical analyses (particularly hypothesis testing) to answer questions. • Should you decide to reference additional material there is no referencing standard specifically required so long as you are consistent. • Complete all calculations in the Excel file and save it as <Your_Name> Assessment 2.xlsx. This will be the file you upload to Canvas. • Separate worksheets have been identified for each question in the Excel File. Please complete each question on the nominated worksheet. It is recommended that you keep an unadjusted, original data set on the first worksheet Dutch Top 2000 which you can refer back to. Some questions may require you to adjust the data set, which will not be needed in later questions, so keep a clean copy on the first worksheet • In addition to the Excel file, you will be required to create a solution document where you will copy your final excel output for each question (Table, graph, formula or excel calculation solution) as well as the written explanation and conclusion for each question. You may use the original Assessment 2 document incorporating your solutions under each question, or you may create a new document, however ensure that each question is clearly identified. This document should be created in Word, but once you have completed the assessment please save it as a PDF using the title <Your_Name> Assessment 2.pdf • For those answers which require written responses these should be short and succinct, with reference to relevant data and provided in non technical language • When you copy your tables, graphs etc across to the solution document please include them in the section relevant to answering a particular question. Do not put them in appendices. Only copy over relevant output. Note that we need to see this in your solution so that we do not have to go searching for it in the Excel output. Any good report always includes the relevant information up front so it can be referred to immediately without the reading having to look elsewhere. • Please ensure you format and label all graphs, tables and output. • This assessment is worth 35% but you will be given a mark out of 70. Marks for the relevant parts are shown below. • Remember to upload both the Excel and PDF file as solutions to Canvas. • Have fun. Learn something about Dutch music preferences. Agree or disagree with the choices made. Leave the assessment with something you could say to your friends along the lines of “Did you know……..?” .

BSB131 APPLIED BUSINESS ANALYTICS | Assessment 2 2 | P a g e Background Spotify is the largest music streaming service globally used by over 30% of streamers. Their ability to offer a wide range of services, including curating personal lists and making recommendations, relies on their massive music data base and the ability to analyse a vast range of data on both the consumer and the music itself. You have been provided with a data set containing information on 2000 (actually 1994) of the top Spotify Songs according to Dutch subscribers released from 1956 to 2019. 1 Being a Dutch data set there are a large number of tracks by Dutch artists. Information provided in the spreadsheet includes data on the following variables: 1. Index – a simple identifier 2. Title – name of the track 3. Artist – name of the artist 4. Genre – the first listed genre of the track 5. Year – Year of most recent release 6. Tempo – The Beats Per Minute (BPM) of the song 7. Energy – Spotify rating from 0 to 100 with values closer to 100 indicating a more energetic song 8. Danceability – Spotify rating from 0 to 100 with higher values indicating it is more danceable. 9. Loudness – The overall loudness in decibels (db) with higher values indicating louder song 10. Valence – score from 0 to 100 with the higher the score the more positive the mood of the song 11. Length – Duration of the song in seconds 12. Acousticness – score from 0 to 100 with the higher the score the more acoustic it is 13. Speechiness – score from 0 to 100 with the higher the score the more spoken words there are 14. Popularity – Score from 0 to 100 based on an algorithm that includes the number of times it is played. Songs with a higher score are more popular. Answer the following questions: General Popularity 1. What are the 5 most popular songs in this list? (2 Marks) Steps: - Copy the relevant columns (Title, Popularity) to Q1 work sheet - Use Sort option -> Sort by Popularity from Largest to Smallest - Use Filter option -> Top 5 Results: Title Popularity Dance Monkey 100 Memories 98 bad guy 95 All I Want for Christmas Is You 94 Believer 88 1 Thank you to Sumat Singh who scraped the Spotify Database to put together this set.

BSB131 APPLIED BUSINESS ANALYTICS | Assessment 2 3 | P a g e 2. Who are the 5 most popular artists – note the artist must have at least 5 songs in the top 2000 to qualify? Show the top 20 most popular artists using a graph with the most popular on the left and with decreasing popularity. (4 Marks) Steps: - Copy Artist and Popularity columns to Q2 worksheet - Use Pivot Table to get Count and Average Popularity - Use Filter option to filter Count of Artist greater or equal to 5 to get only artists that have at least 5 songs - Use Sort option to sort by average of Popularity in descending order - Select the first 20 values and tick “Keep the Selected Items” box to make a graph Result: Row Labels Average of Popularity Ed Sheeran 78.83333333 Imagine Dragons 77.88888889 Red Hot Chili Peppers 76 Eminem 76 AC/DC 75.33333333 3. What are the 5 most popular genres among the Dutch top 2000? (3 Marks) Steps: - Copy Genre and Popularity columns to Q3 worksheet - Use Pivot Table to get Average Popularity - Use Top 10 filter and sort by descending order to get the 5 most popular genres Result: 64 66 68 70 72 74 76 78 80 Top 20 most popular Artists in descending order

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

BSB131 APPLIED BUSINESS ANALYTICS | Assessment 2 4 | P a g e Row Labels Average of Popularity hip hop 73.76470588 Indie 70.75 Latin 70.66666667 Rap 68.16666667 Soul 67.2826087 Genre Preference 4. Is there any evidence at the 5% level of significance that there is a preference for Metal over Classic Rock? You should conduct this test in two ways: a. Is there a difference in the average popularity of Metal and Classic Rock Songs? (8 Marks) b. Is there a difference in the proportion of Metal and Classic Rock songs which have made it to the top 2000? (7 Marks) a. Firstly, the data were split into the popularity of Classic Rock and Metal. The table below shows the first 5 as examples. Classic Rock Metal 59 76 40 72 39 65 53 52 72 77 To be able to do our test of the difference between two means we need to know if the two population variances are equal or not Classic Rock Metal Mean 61.264151 Mean 65.51389 Standard Error 1.7137604 Standard Error 1.179174 Median 63 Median 67 Mode 58 Mode 72 Standard Deviation 12.476364 Standard Deviation 10.00562 Sample Variance 155.65965 Sample Variance 100.1125 Kurtosis -0.597112 Kurtosis 0.305633 Skewness -0.646568 Skewness -0.7269 Range 47 Range 46 Minimum 32 Minimum 37 Maximum 79 Maximum 83 Sum 3247 Sum 4717 Count 53 Count 72 Confidence Level(95.0%) 3.4389117 Confidence Level(95.0%) 2.351206 Using Excel Descriptive Statistics, we know that both n CR (53) and n M (72) ≥ 30, we can assume individual sampling distributions are normal, and hence sampling distribution of difference in sample means is normal We have:

BSB131 APPLIED BUSINESS ANALYTICS | Assessment 2 5 | P a g e H 0 : σ 1 2 = σ 2 2 - Variances are equal H 1 : σ 1 2 ≠ σ 2 2 - Variances are not equal Decision Rule: Reject Ho if p – value < 0.05 F-Test Two-Sample for Variances Classic Rock Metal Mean 61.264151 65.51388889 Variance 155.65965 100.1124804 Observations 53 72 df 52 71 F 1.5548476 P(F<=f) one-tail 0.0419 F Critical one-tail 1.5217266 Using F.Test, we have one-tail p-value of 0.0419. Therefore, p-value for this test = 0.0419 x 2 = 0.0838 Since 0.0838 > 0.05, we cannot reject H 0 . Hence, conclude the variances are equal. Test to see if there is a difference in the average popularity of Metal and Classic Rock We have: 𝐻 0 : 𝜇 ? − 𝜇 ?𝑅 = 0 𝐻 1 : 𝜇 ? − 𝜇 ?𝑅 ≠ 0 Decision Rule: Reject H 0 if p – value < 0.05 Because we know the variances are equal, we can use T – Test: Two-Sample Assuming Equal Variances t-Test: Two-Sample Assuming Equal Variances Classic Rock Metal Mean 61.264151 65.51388889 Variance 155.65965 100.1124804 Observations 53 72 Pooled Variance 123.59584 Hypothesized Mean Difference 0 df 123 t Stat -2.112075 P(T<=t) one-tail 0.0183514 t Critical one-tail 1.6573364 P(T<=t) two-tail 0.0367028 t Critical two-tail 1.9794387 p-value for two tail returns 0.0367, which implies there is a 3.67% the null hypothesis is true p-value < 0.05, hence we can reject H 0 and conclude that there is a difference in the average popularity of Metal and Classic Rock.

BSB131 APPLIED BUSINESS ANALYTICS | Assessment 2 6 | P a g e b. We can divide the sample into 2 groups: • Group 1 – Metal songs that made it into top 2000 • Group 2 – Classic Rock songs that made it into top 2000 We are asked to test to see if there is a difference – two tail test We have: H 0 : p 1 − p 2 = 0 H 1 : p 1 − p 2 ≠ 0 Decision Rule: Reject H 0 if p – value < 0.05 We can use the z distribution provided x 1 , (n 1 – x 1 ), x 2 , (n 2 -x 2 ) all have to be ≥ 10 From our question n 1 = 1994 x 1 = 72 (n 1 – x 1 = 1922) n 2 = 1994 x 2 = 53 (n 2 – x 2 = 1941) In this case all of x 1 , (n 1 – x 1 ), x 2 , (n 2 -x 2 ) are well over 10 and hence the sampling distribution of the difference in sample proportions will be normally distributed We need to calculate (p ̂ 1 − p ̂ 2 ) , (p 1 − p 2 ) σ (p ̂ 1 −p ̂ 2 ) p ̂ 1 = x 1 n 1 = 72 1994 = 0.0361 p ̂ 2 = x 2 n 2 = 53 1994 = 0.0266 p ̂ 1 − p ̂ 2 = 0.0361 − 0.0266 = 0.0095 From H 0 , we have p 1 − p 2 = 0 σ (p ̂ 1 −p ̂ 2 ) = √p ̂q ̂ ( 1 n 1 + 1 n 2 ) Where p ̂ = x 1 + x 2 n 1 + n 2 p ̂ = 72 + 53 1994 + 1994 = 0.0313 σ (p ̂ 1 −p ̂ 2 ) = √ (0.0313)(0.9687) ( 1 1994 + 1 1994 ) = 0.00552

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

BSB131 APPLIED BUSINESS ANALYTICS | Assessment 2 7 | P a g e For a two-tail test, if p ̂ 1 − p ̂ 2 > 0 then p – value = 2 x (1 - norm.dist( (p ̂ 1 − p ̂ 2 ) , (p 1 − p 2 ) , σ (p ̂ 1 −p ̂ 2 ) , true)) Using Excel formula =NORM.DIST(0.0095, 0, 0.00552, TRUE), p-value = 0.0852 Here p – value implies there is only a 8.52% chance the null hypothesis is true P – value is not less than 0.05 hence we cannot reject H 0 and conclude that there is not sufficient evidence that there is a difference in the proportion of Metal and Classic Rock songs which have made it to the top 2000. Song Duration 5. Have you seen the movie Bohemian Rhapsody (if you haven’t you should) ? In the movie, famed music producer Ray Foster tells Queen the song Bohemian Rhapsody was too long to be a single release in 1975. Conduct a test at the 5% level of significance to see if the average length of songs in 1975 was less than the length of Bohemian Rhapsody at 354 seconds. Was Ray Foster right, or was 1975 the year that changed the way popular songs were viewed? (5 Marks) First the data is filtered to be only songs in 1975 and because Bohemian Rhapsody is being used for comparison, we excluded it from the sample. The question asks if the average length of songs was less than 354 seconds, therefore we have: H 0 : μ ≥ 354 H 1 : μ < 354 Decision Rule: Reject H 0 if p-value < 0.05 We do not know the population standard deviation. We can use an Excel trick to do a single population test of means using t-Test: Two-Sample Assuming Unequal Variances. The Table below shows the examples with Dummy columns Length (Duration) Dummy 217 0 811 0 270 508 428 292 From that table, we have t-Test: Two-Sample Assuming Unequal Variances Length (Duration) Dummy Mean 313.7142857 0 Variance 21719.91597 0 Observations 35 2 Hypothesized Mean Difference 354 df 34 t Stat -1.617171281 P(T<=t) one-tail 0.057541552 t Critical one-tail 1.690924255 P(T<=t) two-tail 0.115083104 t Critical two-tail 2.032244509

BSB131 APPLIED BUSINESS ANALYTICS | Assessment 2 8 | P a g e p-value for one-tail test is 0.0575 p – value is greater than  = 0.05 so we reject the null hypothesis and we conclude that the average length of songs in 1975 was less than the length of Bohemian Rhapsody at 354 seconds and Ray Foster was right. Musical Features impacting Popularity 6. Construct a correlation matrix including the variables: Popularity; Year; Beats per minute; Energy; Danceability; Loudness; Valence; Length; Acousticness; Speechiness. (2 Mark) a. What does the correlation between Popularity and Year imply? (2 Mark) b. Other than Year, what are the three factors which seem to have the biggest impact on popularity? Create appropriate graphs to demonstrate the relationship and briefly explain how the graph supports the correlation measurement. (5 Marks) a. After selecting the relevant columns, using Excel Correlation option in Data Analysis, we have the correlation matrix (see page below) The correlation measurement between Year and Popularity is -0.15904284, which indicates a weak negative correlation. This means that as the year increases, the popularity slightly decreases, but the relationship is very weak. In other words, there is a small tendency for newer years to have lower popularity, but the trend is not strong or definitive.

BSB131 APPLIED BUSINESS ANALYTICS | Assessment 2 1 | P a g e Year Tempo (BPM) Energy Danceability Loudness (dB) Valence Length (Duration) Acousticness Speechiness Popularity Year 1 Tempo (BPM) 0.01256997 1 Energy 0.14723485 0.156644435 1 Danceability 0.07749327 -0.14060233 0.1396163 1 Loudness (dB) 0.34376421 0.092926501 0.7357109 0.04423531 1 Valence -0.16616312 0.059653223 0.4051748 0.514563763 0.147041116 1 Length (Duration) -0.05034575 0.006251671 0.0228004 -0.1354316 -0.056126527 -0.20369 1 Acousticness -0.13294603 -0.122471813 -0.6651564 -0.135768879 -0.451634993 -0.23973 -0.102318918 1 Speechiness 0.05409671 0.085598211 0.205865 0.125228998 0.125089747 0.107102 -0.027825837 -0.0982561 1 Popularity -0.15904284 -0.00318879 0.1034363 0.144401672 0.165504177 0.09597 -0.065387411 -0.08761202 0.111729669 1

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

BSB131 APPLIED BUSINESS ANALYTICS | Assessment 2 1 | P a g e b. Based on the matrix above, Danceability, Loudness, Speechiness have the highest correlation value, which means they have the most impact on Popularity. The graph above shows the correlation between Popularity and Danceability. It has a correlation value of 0.1444 which shows a weak positive correlation with popularity. More danceable songs tend to be somewhat more popular. The graph above shows the correlation between Popularity and Loudness. It has a correlation value of 0.1655 which shows a weak positive correlation with popularity. Louder songs tend to be somewhat more popular. 0 20 40 60 80 100 120 0 20 40 60 80 100 120 Popularity Danceability Popularity v Danceability 0 20 40 60 80 100 120 -30 -25 -20 -15 -10 -5 0 Popularity Loudness Popularity v Loudness

BSB131 APPLIED BUSINESS ANALYTICS | Assessment 2 2 | P a g e The graph above shows the correlation between Popularity and Speechiness. It has a correlation value of 0.1117 which shows a weak positive correlation with popularity. More spoken-worded songs tend to be somewhat more popular. Dutch v Global Artists It is unsurprising that since this is the Dutch top 2000 that there are a number of Dutch Artists and Genres in the data set. 7. Split the data set in to Dutch and Non Dutch songs (you can assume the Dutch Genres represent Dutch Artists). Using a full range of statistics and appropriate graphical methods, what do you conclude about the relative popularity of Dutch vs Non Dutch artists? (10 Marks) Using Filter option in Excel - > Contains “dutch” and Does not contain “dutch” -> Data can be split and copy into Q7 & 8 worksheet. Here is an example of how the table would look like: Popularity of Dutch songs Popularity of non-Dutch songs 34 71 48 39 55 69 16 76 50 59 To be able to do our test of the difference between two means we need to know if the two population variances are equal or not Popularity of Dutch songs Popularity of non- Dutch songs Mean 42.87628866 Mean 62.37052261 Standard Error 0.682379816 Standard Error 0.308929064 Median 43 Median 64 Mode 49 Mode 69 Standard Deviation 11.64052766 Standard Deviation 12.74870561 Sample Variance 135.5018841 Sample Variance 162.5294949 Kurtosis 0.050503404 Kurtosis 0.130484227 0 20 40 60 80 100 120 0 10 20 30 40 50 60 Popularity Speechiness Popularity v Speechiness

BSB131 APPLIED BUSINESS ANALYTICS | Assessment 2 3 | P a g e Skewness -0.306107291 Skewness -0.596223771 Range 62 Range 86 Minimum 11 Minimum 14 Maximum 73 Maximum 100 Sum 12477 Sum 106217 Count 291 Count 1703 Confidence Level(95.0%) 1.343044871 Confidence Level(95.0%) 0.60592073 Using Excel Descriptive Statistics, we know that both n D (291) and n ND (1703) ≥ 30, we can assume individual sampling distributions are normal, and hence sampling distribution of difference in sample means is normal We have: H 0 : σ 1 2 = σ 2 2 - Variances are equal H 1 : σ 1 2 ≠ σ 2 2 - Variances are not equal Decision Rule: Reject Ho if p – value < 0.05 F-Test Two-Sample for Variances F-Test Two-Sample for Variances Popularity of Dutch songs Popularity of non-Dutch songs Mean 42.87628866 62.37052261 Variance 135.5018841 162.5294949 Observations 291 1703 df 290 1702 F 0.833706425 P(F<=f) one-tail 0.025044469 F Critical one-tail 0.858583682 Using F.Test, we have one-tail p-value of 0.025044469. Therefore, p-value for this test = 0.025044469 x 2 = 0.050088939 Since 0.050088939 > 0.05, we cannot reject H 0 . Hence, conclude the variances are equal. Assume the question is asking if non-Dutch songs are more popular than Dutch songs. We have: 𝐻 0 : 𝜇 ?? − 𝜇 ? ≤ 0 𝐻 1 : 𝜇 ?? − 𝜇 ? > 0

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

BSB131 APPLIED BUSINESS ANALYTICS | Assessment 2 4 | P a g e Decision Rule: Reject H 0 if p – value < 0.05 Because we know the variances are equal, we can use T – Test: Two-Sample Assuming Equal Variances t-Test: Two-Sample Assuming Equal Variances Popularity of Dutch songs Popularity of non-Dutch songs Mean 42.87628866 62.37052261 Variance 135.5018841 162.5294949 Observations 291 1703 Pooled Variance 158.5947523 Hypothesized Mean Difference 0 df 1992 t Stat -24.40355003 P(T<=t) one-tail 1.3405E-115 t Critical one-tail 1.645618929 P(T<=t) two-tail 2.6809E-115 t Critical two-tail 1.961155595 p-value for one tail returns 1.3405E-115, implies no chance the null hypothesis is true which is less than 0.5, hence we can reject H 0 and conclude that non-Dutch songs are more popular than Dutch songs. 8. To see if your conclusion is supported, construct a 95% confidence interval for the average popularity of Dutch songs and another of non Dutch songs. Do these results support your conclusion in the last question? Why? (4 Marks) 95% confidence interval is given by: x ̅ ± E Based on the Descriptive Statistics table in Q7, we have x ̅ ND = 62.3705 E ND = 0.6059 95% CI for mean popularity of non Dutch songs is: 61.7646 ≤ µ ND ≤ 62.97644 x ̅ D = 42.8763 E D = 1.3430 95% CI for mean popularity of Dutch songs is: 41.53324 ≤ µ D ≤ 44.21933 These confidence intervals support last question’s conclusion of non -Dutch songs being more popular than Dutch songs because no matter where the true mean popularity value of Dutch songs lie between the range, it would still be significantly less than the range of non-Dutch songs.

BSB131 APPLIED BUSINESS ANALYTICS | Assessment 2 5 | P a g e Your Own Question 9. From all of the data provided, propose one question of your own that you are interested in. Construct a table, create a graph or conduct a test which answers that question. (4 Marks) Question: What is the top 10 artist with the most songs? Using Pivot Table with Sort and Filter option, we can find that: Row Labels Count of Artist Queen 37 The Beatles 36 Coldplay 27 U2 26 The Rolling Stones 24 Michael Jackson 23 Bruce Springsteen 23 ABBA 22 David Bowie 21 Fleetwood Mac 18 The top 10 artists with the most songs in top 2000 do not contain any Dutch artist. Dashboard 10. Build a dashboard to show the Dutch Top 200. In the dashboard include the following: • Top 5 most popular songs (Hint: Consider using the =Rank function) (3 Marks) • Graph showing average popularity by genre (3 Marks) • Table showing the top 10 artists by the number of songs (3 Marks) • A Table showing the most popular song and artist by year. NOTE – this last table is difficult and is for those who love playing around with, and learning about, Excel. As such it does not count towards your marks on this assessment, HOWEVER, if you can do it you can get a bonus mark on the assessment. (2 bonus marks) 0 5 10 15 20 25 30 35 40 Top 10 Artists with the most songs Total

BSB131 APPLIED BUSINESS ANALYTICS | Assessment 2 6 | P a g e Do not include this information in your spreadsheet solution, however you may use it to test your Dashboard. The markers will use it to make sure the dashboard is “live” – that is the whole dashboard updates when you add the single piece of information and hit refresh, all tables and graphs update automatically. Index Title Artist Genre Year Length Popularity 1995 Flame Trees Cold Chisel Oz Rock 1984 260 99 Putting it all together – summarising your results 11. In no more than 250 words, synthesise the information provided in all questions above. When you do this don’t just repeat the results – instead paint a picture of what you’ve learned about Dutch song tastes. (5 Marks) Dutch musical tastes reveal a strong preference for international artists, with non-Dutch songs enjoying greater popularity and a higher presence in the top 2000 charts. Hip hop stands out as the dominant genre. The relationship between song attributes and popularity offers further insights. The weak negative correlation between a song's release year and its popularity implies a slight preference for newer tracks, yet classics retain significant appeal. Danceability, loudness, and speechiness all show weak positive correlations with popularity, indicating that energetic, vibrant, and lyrically engaging tracks tend to fare better. This aligns with the popularity of hip hop, a genre known for its strong beats, dynamic sound, and articulate lyrics. Overall, Dutch song preferences reflect a blend of global influences and modern trends, with an appreciation for high-energy and engaging music. The nuanced correlations between various musical elements and popularity underscore the complexity of listener choices, revealing a sophisticated audience that values both contemporary styles and classic tracks. This understanding of Dutch musical tastes not only paints a vivid picture of current trends but also provides valuable insights for artists and producers aiming to capture the Dutch market.

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Related Documents