Analyzing Music Shop Performance Using SQL

In this project, we'll be answering questions about a fictional digital music shop - kind of like a mini-iTunes store.

Approach

As this is mostly SQL practice, we'll attempt to find answers to the following:

Best-selling genres in the USA.
Analysing employee sales performance.
Analysing sales by country.
Sales performance of albums vs individual tracks.

Results

Best selling USA genres are Rock, Alternative & Punk, and Metal.
The employee with the best sales performance is also the one who's been at the company longest.
While USA has the highest number of customers, sales and average sales, Czech Republic has the highest average order value.
81.4% of sales come from individual tracks, while just 18.6% come from album purchases.

Data Overview

We'll be using a modified version of the Chinook database. The Chinook database contains information about the artists, songs, and albums from the music shop, as well as information on the shop's employees, customers, and the customers purchases.

Let's connect to the database and view the tables:

%%capture %load_ext sql %sql sqlite:///chinook.db

%%sql SELECT name, type FROM sqlite_master WHERE type in ("table", "view");

While we have 11 distinct tables, they're all interconnected in some way. For example, an invoice can have many invoice lines, while an invoice line can only refer to one invoice.

Data Analysis

Best-Selling Genres in the USA

Let's start our analysis with finding the best selling genres in the USA.

%%sql WITH total_track_sold AS ( SELECT il.* FROM invoice AS i JOIN invoice_line AS il ON il.invoice_id = i.invoice_id JOIN track AS t ON t.track_id = il.track_id WHERE billing_country = "USA" ) SELECT g.name AS genre_name, COUNT(*) AS tracks_sold, ROUND( (CAST(COUNT(*) AS float) / (SELECT Count(*) FROM total_track_sold) ), 2) * 100 AS percentage FROM invoice AS i JOIN invoice_line AS il ON il.invoice_id = i.invoice_id JOIN track AS t ON t.track_id = il.track_id JOIN genre AS g on g.genre_id = t.genre_id WHERE billing_country = "USA" GROUP BY g.name ORDER BY percentage DESC LIMIT 5;

This highlights that Rock, Alternative & Punk, and Metal are the top selling USA genres from our dataset.

Analysing Employee Sales Performance

Each customer for the Chinook store gets assigned a sales support agent when they first make a purchase. We'll analyse this connection to identify the sales support agents who are performing the best.

%%sql SELECT e.first_name || " " || e.last_name AS employee_name, hire_date, "$" || Round(SUM(total), 2) AS total_sales FROM employee AS e JOIN customer AS c ON c.support_rep_id = e.employee_id JOIN invoice AS i ON i.customer_id = c.customer_id GROUP BY e.employee_id

We can see that Jane Peacock is the best performing Sales Support Agent. However, this may be due to the fact that she has worked at the company for the longest period of time.

Analysing Sales by Country

Here, we'll calculate the following for each country:

Total number of customers
Total value of sales
Average value of sales per customer
Average order value

Where a country has only one customer, we'll ignore them.

%%sql WITH num_customers AS ( SELECT country, COUNT(*) as num_customers FROM customer GROUP BY country ), total_sales AS ( SELECT country, ROUND(SUM(total), 2) as total_sales FROM customer AS c JOIN invoice AS i ON c.customer_id = i.customer_id GROUP BY country ), avg_sales AS ( SELECT ts.country, ts.total_sales / nc.num_customers as avg_sales FROM total_sales AS ts JOIN num_customers AS nc ON ts.country = nc.country ), total_orders AS ( SELECT country, COUNT(i.invoice_id) as total_orders FROM customer AS c JOIN invoice AS i ON c.customer_id = i.customer_id GROUP BY country ), avg_order_value AS ( SELECT ts.country, ROUND(ts.total_sales / t.total_orders, 2) as avg_order_value FROM total_sales AS ts JOIN total_orders AS t ON ts.country = t.country ) SELECT nc.country, nc.num_customers, ts.total_sales, a.avg_sales, aov.avg_order_value FROM num_customers AS nc JOIN total_sales AS ts ON nc.country = ts.country JOIN avg_sales AS a ON a.country = nc.country JOIN avg_order_value AS aov ON aov.country = a.country WHERE nc.num_customers > 1 ORDER BY total_sales DESC

While USA has the highest number of customers, sales and average sales, Czech Republic has the highest average order value.

Album vs Individual Track Performance

Let's find out what percentages of purchases comes from albums versus individual tracks

%%sql WITH invoice_first_track AS ( SELECT il.invoice_id AS invoice_id, MIN(il.track_id) AS first_track_id FROM invoice_line AS il GROUP BY 1 ) SELECT album_purchase, COUNT(invoice_id) AS number_of_invoices, CAST(count(invoice_id) AS FLOAT) / (SELECT COUNT(*) FROM invoice) AS percent FROM ( SELECT ifs.*, CASE WHEN ( SELECT t.track_id FROM track AS t WHERE t.album_id = ( SELECT t2.album_id FROM track AS t2 WHERE t2.track_id = ifs.first_track_id ) EXCEPT SELECT il2.track_id FROM invoice_line AS il2 WHERE il2.invoice_id = ifs.invoice_id ) IS NULL AND ( SELECT il2.track_id FROM invoice_line AS il2 WHERE il2.invoice_id = ifs.invoice_id EXCEPT SELECT t.track_id FROM track AS t WHERE t.album_id = ( SELECT t2.album_id FROM track AS t2 WHERE t2.track_id = ifs.first_track_id ) ) IS NULL THEN "yes" ELSE "no" END AS "album_purchase" FROM invoice_first_track AS ifs ) GROUP BY album_purchase;

We can note that album purchases account for just 18.6% of purchases, meaning that listeners prefer to buy tracks in isolation.

Results

From our analysis, we've learned the following:

Best selling USA genres are Rock, Alternative & Punk, and Metal.
The employee with the best sales performance is also the one who's been at the company longest.
While USA has the highest number of customers, sales and average sales, Czech Republic has the highest average order value.
81.4% of sales come from individual tracks, while just 18.6% come from album purchases.