Tuning In to Profits: Analyzing Music Royalties with EDA in SSMS
We're here for data, so let's look at some numbers.
In 2023, the total revenue of the recorded music industry amounted to 28.6 billion U.S. dollars. Global streaming revenues reached 19.3 billion U.S. dollars in the same year
It means that over 67% of all music revenue comes from streaming services. Have you ever wondered what lies beyond Spotify, Pandora, and iTunes? A quick search will reveal that there are currently over 200 streaming services globally.
For artists, managing this landscape can be quite a headache. Enter a digital music distribution company specializing in revenue management, potentially offering a solution.
Data analysis can determine the most beneficial outcomes for both the company and the artist. When objectives aren't clearly defined, creativity becomes crucial. Data analysis itself is an art.
One question leads to another. With this database, it's easy to become lost in endless analysis. However, the crucial task is evaluating which data section provides the most revenue insights.
I've prepared this exploratory data analysis to address the most critical business question for the music distribution company: Which music generates the most revenue?
All the below screenshots of analysis, can be found on my GitHub Page
EXPLORATORY DATA ANALYSIS
Step 1. Examining if data types in each column are appropriate and consistent with the data they store.
In order to make it look this neat, I needed to
Upload csv files to SSMS.
Uniform all field names and check their data types.
Define which fields are my keys and are allow to be nulls.
As far as exploratory data analysis goes, the first task can be checked out. I examined the types and made sure they are consistent. I cannot emphasize enough how important this step is. Numerous error messages can drive you nuts. I was there. Just learn from my mistakes.
Right after that, we MUST examine each table for null values, especially in crucial columns. Decide whether nulls are allowed and handle them appropriately. Understand the uniqueness of values in key columns like song_id, album_id, and others. Ensure the uniqueness constraints are met.
Before you hit the query button, and start pretending you are a hardcore heavy metal movie hacker with a black screen, just make yourself familiar with the table.
All steps above make you somewhat memorize what each table consists of. It should be logical, of course. But you are the parent here. You make changes so it fits you. But keep it to the convention, in case someone will want to refer to your work later on! Keep those notes. They will come in handy.
Little love, little patience, and magic. All my data is nice and cozy and stored inside my music database. I'm sure we are forming a pretty solid friendship at this point, and I am confident we can make this work. This, and no issues from SSMS. It's a nice confirmation, I'm not gonna lie.
EXEC sp_help 'royalty_sales'
SELECT column_name, data_type
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'royalty_sales' ;
SELECT column_name, data_type
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'songs' ;
SELECT column_name, data_type
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'albums' ;
SELECT column_name, data_type
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'artists'
Step 2. Statistics!
To have an idea of how our numbers work, we need to know where they fall. Distribution of Quantitative Data is the main focus this evening. Exploring each table separately and then joining them will paint a picture quite soon. Finding the average values of crucial columns, checking the maximum and minimum values, and sums is satisfying enough to see if our data actually makes sense. Good, old judgment is important. If the average song makes 34 cents, please check if the maximum value for a song doesn't go to $100. Let's be serious here.
Exploring: royalty_sales, albums, artists, songs
-- quering dbo.songs for reference
EXEC sp_help 'songs'
-- checking for duplicates in dbo.songs
SELECT song_id, COUNT (*) AS count
FROM songs
GROUP BY song_id
HAVING COUNT(*) > 1;
-- no record with this combination shows that each row is unique
SELECT song_id, song_title, album_id, COUNT(*) AS count
FROM songs
GROUP BY song_id, song_title, album_id
HAVING COUNT(*) > 1;
--example of duplicate
SELECT *
FROM dbo.songs
where song_id = 'SOALSZJ1370F1A7C75'
-- let's check for duplicates for albums
SELECT album_id, COUNT (*) AS count
FROM albums
GROUP BY album_id
HAVING COUNT(*) > 1;
-- being empty - no results - means that I can establish primary key here
-- for dbo.artists
SELECT artist_id, COUNT (*) AS count
FROM artists
GROUP BY artist_id
HAVING COUNT(*) > 1;
--empty again so I can make it a primary key
-- making PK in songs table
ALTER TABLE songs
ADD CONSTRAINT PK_songs_album_id_song_id PRIMARY KEY (album_id, song_id);
-- droping album_id from royalties because it can be queried with song_id and it is empty anyways
ALTER TABLE royalty_sales
DROP COLUMN album_id;
--changing in royalties_sale date_sale for just date
ALTER TABLE royalty_sales
ALTER COLUMN sale_date DATE;
-- creating FK
ALTER TABLE albums
ADD CONSTRAINT FK_albums_artist_id
FOREIGN KEY (artist_id)
REFERENCES artists (artist_id);
--populate album_id in royalties_sales
UPDATE rs
SET rs.album_id = s.album_id
FROM royalty_sales rs
INNER JOIN songs s ON rs.song_id = s.song_id
WHERE rs.album_id IS NULL;
--is it unique?
SELECT sale_id, COUNT (*) AS count
FROM royalty_sales
GROUP BY sale_id
HAVING COUNT(*) > 1;
-- Alter the column to be NOT NULL
ALTER TABLE royalty_sales
ALTER COLUMN sale_id [int] NOT NULL;
-- Add the primary key constraint
ALTER TABLE royalty_sales
ADD CONSTRAINT PK_royalty_sales PRIMARY KEY (sale_id);
--final check
SELECT *
FROM royalty_sales
WHERE sale_id IS NULL;
Investigating the distribution of quantitative data like quantity, payable_amt_due, and royalty_share. Let me show you example, I'm sure you just itch to get to findings.
-- exploring royalty_sales table
SELECT *
FROM royalty_sales ; -- 114,358
SELECT TOP 10 *
FROM royalty_sales
-- findings: song_id have some null spaces, check for payable_curency consistency, album_id is also null
-- next: how many nulls in song_id
SELECT sale_id, count(*) as count
FROM royalty_sales
WHERE song_id IS NULL
GROUP BY sale_id;
-- the finding of that is 114,358 rows
-- I want to know how many song_id we have and I want to sort them alphabetically
SELECT song_id, count(*) as count
FROM royalty_sales
GROUP BY song_id
ORDER BY count desc;
-- with ASC we have 53,766 song_id with null values (tho it still shows no nulls there).
-- the highest amout of song_id is 16
SELECT *
FROM royalty_sales
WHERE song_id = '' ;
-- so those have empty spaces and we have 53 766 of them.
-- exploring date
SELECT sale_id, sale_date
FROM royalty_sales
ORDER BY sale_date ;
--this way I see that the latest sale is 2022-05-31 and the oldest is from 2000-01-01
-- store: do we have null values there, thich is the most/least popular, how many of them is there
SELECT store, count(*) as count
FROM royalty_sales
GROUP BY store
ORDER BY count desc;
--96 rows, it would mean we have 96 of them, let's check:
SELECT count( DISTINCT store) as store
FROM royalty_sales ;
-- it is 96 of them
--now let's check for the null values there
SELECT store
FROM royalty_sales
WHERE store IS NULL ;
-- NO NULL VALUES
--exploring currency
SELECT payable_currency, count(*) AS count
FROM royalty_sales
GROUP BY payable_currency
ORDER BY count desc; -- 685 desc Nepalese Rupee, 684 asc Falkland Island Pounds
-- NOTE payable_amt_due is a amount payed in the currency, usd_payable is the amt that is payed in USD dollars
--after conversion
-- move on to payable_amt_due to see the statistics
SELECT
COUNT(*) AS num_rows,
ROUND(AVG(payable_amt_due),2) AS avg_payable_amt_due,
ROUND(MIN(payable_amt_due),2) AS min_payable_amt_due,
ROUND(MAX(payable_amt_due),2) AS max_payable_amt_due,
ROUND(SUM(payable_amt_due), 2) AS sum_payable_amt_due,
ROUND(STDEV(payable_amt_due),2) AS stdev_payable_amt_due
FROM royalty_sales ;
-- more info from here
SELECT
COUNT(*) AS num_rows,
ROUND(AVG(usd_payable),2) AS avg_usd_payable,
ROUND(MIN(usd_payable),2) AS min_usd_payable,
ROUND(MAX(usd_payable),2) AS max_usd_payable,
ROUND(SUM(usd_payable), 2) AS sum_usd_payable,
ROUND(STDEV(usd_payable),2) AS stdev_usd_payable
FROM royalty_sales ;
-- move on to quantity to see the statistics
SELECT
count(*) AS num_rows,
ROUND(AVG(quantity),2) AS avg_quantity,
ROUND(MIN(quantity),2) AS min_quantity,
ROUND(MAX(quantity),2) AS max_quantity,
ROUND(SUM(quantity), 2) AS sum_quantity,
ROUND(STDEV(quantity),2) AS stdev_quantity
FROM royalty_sales ;
Step 3. Findings.
Documenting your findings in some type of documentation will help you organize your information. After a while, you might get confused about what is what. Good doc document or Excel table would suffice for now.
What I've learned from this project was to be vigilant of the numbers we work on. They can get tricky, also simple statistics hides bunch of important information about data. Because of that, I can get creative. I can ask more questions.
Well... in this case...
Funnily enough, this exemplary database wasn't diverse enough to be plotted anywhere, but it was a great SQL exercise.
Power BI is a strong tool to graph and present your findings to a non-technical audience. This is another form of artistry because that math tells you everything you need to know.
Now, do you know how to show it to someone who is not interested in this whole funny code?
Comments