The objective for this project is to analyse data from the chinook database, which has information about a digital music shop.
The chinook database contains details about the artists, songs, and albums from the music shop, as well as details on the shop's employees, customers, and the customers purchases. This information is contained in eleven tables, click here to visit the github repository.
The data analysis will explore below business questions:
artist used in the most playlists?
most popular track?
how many tracks have been purchased vs not purchased?
music genre which has the least purchased tracks?
performance of sales support agent
breakdown of customer and sales data by country
most popular music genre in the US
recommendation for new albums to be added to the store for US customers
For more details, please refer to the the full analysis below.
%%capture
%load_ext sql
%sql sqlite:///chinook.db
Preview the data stored in the chinook database.
%%sql
SELECT
name,
type
FROM sqlite_master
WHERE type IN ("table","view");
* sqlite:///chinook.db Done.
name | type |
---|---|
album | table |
artist | table |
customer | table |
employee | table |
genre | table |
invoice | table |
invoice_line | table |
media_type | table |
playlist | table |
playlist_track | table |
track | table |
The most popular artist with tracks used in the most playlists is Iron Maiden.
%%sql
SELECT
a.artist_id,
a.name artist_name,
count(a.name) tracks_in_playlist
FROM playlist_track pt
INNER JOIN track t on t.track_id = pt.track_id
INNER JOIN album ab on ab.album_id = t.album_id
INNER JOIN artist a on a.artist_id = ab.artist_id
GROUP BY a.name
ORDER BY tracks_in_playlist DESC
LIMIT 10;
* sqlite:///chinook.db Done.
artist_id | artist_name | tracks_in_playlist |
---|---|---|
90 | Iron Maiden | 516 |
150 | U2 | 333 |
50 | Metallica | 296 |
22 | Led Zeppelin | 252 |
58 | Deep Purple | 226 |
149 | Lost | 184 |
118 | Pearl Jam | 177 |
82 | Faith No More | 145 |
81 | Eric Clapton | 145 |
100 | Lenny Kravitz | 143 |
Below we try to find out if there is a particular track which is more popular amongst Iron Maiden music.
%%sql
SELECT
a.name artist_name,
pt.track_id,
t.name track_name,
ab.title album,
count(t.name) tracks_in_playlist
FROM playlist_track pt
INNER JOIN track t on t.track_id = pt.track_id
INNER JOIN album ab on ab.album_id = t.album_id
INNER JOIN artist a on a.artist_id = ab.artist_id
WHERE a.artist_id = 90
GROUP BY track_name
ORDER BY tracks_in_playlist DESC
LIMIT 10;
* sqlite:///chinook.db Done.
artist_name | track_id | track_name | album | tracks_in_playlist |
---|---|---|---|---|
Iron Maiden | 1221 | 2 Minutes To Midnight | A Real Dead One | 13 |
Iron Maiden | 1213 | The Trooper | A Real Dead One | 12 |
Iron Maiden | 1212 | The Number Of The Beast | A Real Dead One | 12 |
Iron Maiden | 1223 | Hallowed Be Thy Name | A Real Dead One | 12 |
Iron Maiden | 1234 | Fear Of The Dark | A Real Live One | 11 |
Iron Maiden | 1278 | Wrathchild | Killers | 10 |
Iron Maiden | 1229 | The Evil That Men Do | A Real Live One | 10 |
Iron Maiden | 1222 | Iron Maiden | A Real Dead One | 10 |
Iron Maiden | 1227 | Wasting Love | A Real Live One | 9 |
Iron Maiden | 1228 | Tailgunner | A Real Live One | 9 |
The album 'A Real Dead One' has the Top 4 tracks for Iron Maiden and the most popular Iron Maiden digital track is '2 Minutes to Midnight'.
There are 3503 digital tracks in the chinook digital music store, of this, customers have purchased 1806, there are still 1697 tracks which have not yet been purchased by any customer.
%%sql
WITH total_tracks AS
(
SELECT
count(distinct track_id) total_tracks,
(
SELECT
count(distinct track_id)
FROM invoice_line) purchased
FROM track
)
SELECT tt.*,
(tt.total_tracks - tt.purchased) non_purchased
FROM total_tracks tt;
* sqlite:///chinook.db Done.
total_tracks | purchased | non_purchased |
---|---|---|
3503 | 1806 | 1697 |
This list has some of the tracks which have not yet been purchased from the store by a customer.
%%sql
SELECT
t.*
FROM track t
WHERE t.track_id not in (
SELECT
il.track_id
FROM invoice_line il
)
LIMIT 5;
* sqlite:///chinook.db Done.
track_id | name | album_id | media_type_id | genre_id | composer | milliseconds | bytes | unit_price |
---|---|---|---|---|---|---|---|---|
99 | Your Time Has Come | 11 | 1 | 4 | Cornell, Commerford, Morello, Wilk | 255529 | 8273592 | 0.99 |
101 | Be Yourself | 11 | 1 | 4 | Cornell, Commerford, Morello, Wilk | 279484 | 9106160 | 0.99 |
104 | Heaven's Dead | 11 | 1 | 4 | Cornell, Commerford, Morello, Wilk | 276688 | 9006158 | 0.99 |
106 | Man Or Animal | 11 | 1 | 4 | Cornell, Commerford, Morello, Wilk | 233195 | 7542942 | 0.99 |
107 | Yesterday To Tomorrow | 11 | 1 | 4 | Cornell, Commerford, Morello, Wilk | 273763 | 8944205 | 0.99 |
From the tracks which have not been purchased, 460 belong to the 'Latin' genre, hmmm... how many tracks are available for purchase for this genre?
%%sql
SELECT
g.name genre,
g.genre_id,
count(t.track_id) total_tracks
FROM track t
INNER JOIN genre g on g.genre_id = t.genre_id
WHERE t.track_id not in (
SELECT
il.track_id
FROM invoice_line il
)
GROUP BY genre
ORDER BY total_tracks DESC
LIMIT 10;
* sqlite:///chinook.db Done.
genre | genre_id | total_tracks |
---|---|---|
Latin | 7 | 460 |
Rock | 1 | 382 |
Alternative & Punk | 4 | 156 |
Metal | 3 | 136 |
TV Shows | 19 | 91 |
Jazz | 2 | 69 |
Drama | 21 | 63 |
Classical | 24 | 58 |
Soundtrack | 10 | 38 |
Reggae | 8 | 36 |
The data suggests that the 'Latin' genre has only had 20% of all available tracks (579) purchased by customers. This insight will be helpful when recommending new albums to be added to the digital music shop.
%%sql
SELECT
count(t.track_id) total_tracks,
g.name genre
FROM track t
INNER JOIN genre g on g.genre_id = t.genre_id
WHERE t.genre_id = 7
GROUP BY genre;
* sqlite:///chinook.db Done.
total_tracks | genre |
---|---|
579 | Latin |
The below data displays total sales amount (dollars) for each sales support agent within the organisation.
%%sql
WITH rep_sales AS
(
SELECT
c.customer_id,
round(sum(i.total), 2) rep_sales_total,
c.support_rep_id
FROM customer c
INNER JOIN invoice i on i.customer_id = c.customer_id
GROUP BY i.customer_id
)
SELECT
e.first_name || ' ' || e.last_name employee_name,
SUM(rs.rep_sales_total) rep_sales_total,
round(CAST(SUM(rs.rep_sales_total) AS FLOAT) /
(
SELECT SUM(rep_sales_total)
FROM rep_sales
) * 100, 2) percentage_sales,
e.hire_date
FROM rep_sales rs
INNER JOIN employee e on e.employee_id = rs.support_rep_id
WHERE e.title in ('Sales Support Agent')
GROUP BY employee_name;
* sqlite:///chinook.db Done.
employee_name | rep_sales_total | percentage_sales | hire_date |
---|---|---|---|
Jane Peacock | 1731.51 | 36.77 | 2017-04-01 00:00:00 |
Margaret Park | 1584.0000000000002 | 33.63 | 2017-05-03 00:00:00 |
Steve Johnson | 1393.92 | 29.6 | 2017-10-17 00:00:00 |
The highest performing sales agent is Jane Peacock with 36.77% of all sales while Margaret Park has 33.63%. Steve Johnson has the least percent of sales recorded with 29.6%, approximately 7% from the top performing employee.
Based on the hire date for these employees, the data suggests some correlation with sales performance. Jane was the first sales support agent to be hired by the organisation amongst the three. This suggests that Steve's sales amount could be as a result of joining the organisation much later (at least five months) when compared with the other two employees in same role.
The below shows sales data, for each country by calculating:
Because there are a number of countries with only one customer, these customers will be grouped in a new category which will be labelled 'other'.
%%sql
WITH customer_sales AS
(
SELECT il.*,
CASE
WHEN (
SELECT count(*)
FROM customer
where country = c.country
) = 1 THEN "Other"
ELSE c.country
END AS country,
i.total,
c.customer_id
FROM invoice_line il
INNER JOIN invoice i on il.invoice_id = i.invoice_id
INNER JOIN customer c on i.customer_id = c.customer_id
)
SELECT
country,
count(distinct(customer_id)) total_customers,
round(SUM(unit_price), 2) total_sales,
round(SUM(unit_price) / count(distinct customer_id), 2) avg_sales_per_customer,
round(SUM(unit_price) / count(distinct invoice_id), 2) avg_order_value
FROM customer_sales cs
GROUP BY country
ORDER BY total_sales DESC;
* sqlite:///chinook.db Done.
country | total_customers | total_sales | avg_sales_per_customer | avg_order_value |
---|---|---|---|---|
Other | 15 | 1094.94 | 73.0 | 7.45 |
USA | 13 | 1040.49 | 80.04 | 7.94 |
Canada | 8 | 535.59 | 66.95 | 7.05 |
Brazil | 5 | 427.68 | 85.54 | 7.01 |
France | 5 | 389.07 | 77.81 | 7.78 |
Germany | 4 | 334.62 | 83.66 | 8.16 |
Czech Republic | 2 | 273.24 | 136.62 | 9.11 |
United Kingdom | 3 | 245.52 | 81.84 | 8.77 |
Portugal | 2 | 185.13 | 92.57 | 6.38 |
India | 2 | 183.15 | 91.58 | 8.72 |
The US might have the most customer sales overall, but the average order value per customer is only $80
(the second lowest total amount across the countries excluding those countries with only one customer which have been grouped in 'other'). The data suggests that there might be opportunity for higher sales in Czech Republic and Portugal which have $136.62
and $92.57
average order values per customer, though this data should be used with caution due to the limited sample size being used for analysis.
The chinook record store has just signed a deal with a new record label, and would like to select three albums that will be added to the store, from a list of four. The four albums are by artists that don't have any tracks in the store right now - below we have the artist names, and the genre of music they produce:
Artist | Genre |
---|---|
Regal | Hip-Hop |
Red Tone | Punk |
Meteor and the Girls | Pop |
Slim Jim Bites | Blues |
The record label specializes in artists from USA, and they have given chinook some money to advertise the new albums in the US, this means we're interested in finding out which genres sell best in USA.
%%sql
WITH popular_usa_genres AS
(
SELECT il.*,
c.country
FROM invoice_line il
INNER JOIN invoice i on il.invoice_id = i.invoice_id
INNER JOIN customer c on i.customer_id = c.customer_id
WHERE c.country = "USA"
)
SELECT g.name genre,
COUNT(pug.invoice_line_id) tracks_purchased,
round(CAST(COUNT(pug.invoice_line_id) AS FLOAT) / (
SELECT COUNT(*)
FROM popular_usa_genres
) * 100, 2) percentage_purchased
FROM popular_usa_genres pug
INNER JOIN track t on t.track_id = pug.track_id
INNER JOIN genre g on g.genre_id = t.genre_id
GROUP BY g.genre_id
ORDER BY tracks_purchased desc;
* sqlite:///chinook.db Done.
genre | tracks_purchased | percentage_purchased |
---|---|---|
Rock | 561 | 53.38 |
Alternative & Punk | 130 | 12.37 |
Metal | 124 | 11.8 |
R&B/Soul | 53 | 5.04 |
Blues | 36 | 3.43 |
Alternative | 35 | 3.33 |
Pop | 22 | 2.09 |
Latin | 22 | 2.09 |
Hip Hop/Rap | 20 | 1.9 |
Jazz | 14 | 1.33 |
Easy Listening | 13 | 1.24 |
Reggae | 6 | 0.57 |
Electronica/Dance | 5 | 0.48 |
Classical | 4 | 0.38 |
Heavy Metal | 3 | 0.29 |
Soundtrack | 2 | 0.19 |
TV Shows | 1 | 0.1 |
The most popular music genre for customers purchasing music from the US is 'Rock' which has 53% of sales while 'Alternative & Punk' genre has 12%. The 'Metal' genre has 11.7% sales, and much further from the Top 3 genres is 'R&B/Soul' which has 5% of total sales.
Based on global sales of tracks across the different genres in the US, chinook digital record store should purchase albums by the following 3 artists:
Artist | Genre |
---|---|
Red Tone | Punk |
Meteor and the Girls | Pop |
Slim Jim Bites | Blues |
Punk genre is ranked 2nd overall in sales, hence we expect 'Red Tone' to have better sales.
The below artist narrowly missed the recommended shortlist, despite hip-hop/rap being ranked 9th overall in the US, this music genre was only 0.19 per-cent off the 7th position in popularity presently held by 'Pop'.