Business Analysis Project

Here is Chinbook Database Schema:

Chinbook Schema

Connecting the database

In [1]:
%%capture
%load_ext sql
%sql sqlite:///chinook.db
Out[1]:
'Connected: [email protected]'

Listing all the tables

In [3]:
%%sql
SELECT
    name,
    type
FROM sqlite_master
WHERE type IN ("table","view");
Done.
Out[3]:
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

Selecting Albums to Purchase

  • We'll need to write a query to find out which genres sell the most tracks in the USA, write up a summary of your findings, and make a recommendation for the three artists whose albums we should purchase for the store.
In [4]:
%%sql
WITH usa_tracks_sold AS
   (
    SELECT il.* 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(uts.invoice_line_id) tracks_sold,
    cast(count(uts.invoice_line_id) AS FLOAT) / (
        SELECT COUNT(*) from usa_tracks_sold
    ) percentage_sold
FROM usa_tracks_sold uts
INNER JOIN track t on t.track_id = uts.track_id
INNER JOIN genre g on g.genre_id = t.genre_id
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10;
Done.
Out[4]:
genre tracks_sold percentage_sold
Rock 561 0.5337773549000951
Alternative & Punk 130 0.12369172216936251
Metal 124 0.11798287345385347
R&B/Soul 53 0.05042816365366318
Blues 36 0.03425309229305423
Alternative 35 0.03330161750713606
Latin 22 0.02093244529019981
Pop 22 0.02093244529019981
Hip Hop/Rap 20 0.019029495718363463
Jazz 14 0.013320647002854425

According to our analysis I can clarify that, Highest revenue geneated from:

  • Rock Genre (53%)
  • followed by 'Alternative & Punk' and 'Metal' with 12.36%, 11.8% Respecvily.

So, we should be on the lookout for artists and albums from the 'rock' genre, which accounts for 53% of sales.

Analyzing Employee Sales Performance

In [5]:
%%sql
WITH customer_support_rep_sales AS
    (
     SELECT
         i.customer_id,
         c.support_rep_id,
         SUM(i.total) total
     FROM invoice i
     INNER JOIN customer c ON i.customer_id = c.customer_id
     GROUP BY 1,2
    )

SELECT
    e.first_name || " " || e.last_name employee,
    e.hire_date,
    SUM(csrs.total) total_sales
FROM customer_support_rep_sales csrs
INNER JOIN employee e ON e.employee_id = csrs.support_rep_id
GROUP BY 1;
Done.
Out[5]:
employee hire_date total_sales
Jane Peacock 2017-04-01 00:00:00 1731.5099999999998
Margaret Park 2017-05-03 00:00:00 1584.0000000000002
Steve Johnson 2017-10-17 00:00:00 1393.92

Jane is at Peak with 36% of Total sales.

  • While there is a 20% difference in sales between Jane (the top employee) and Steve (the bottom employee), the difference roughly corresponds with the differences in their hiring dates.

Analyzing Sales by Country

In [6]:
%%sql

WITH country_or_other AS
    (
     SELECT
       CASE
           WHEN (
                 SELECT count(*)
                 FROM customer
                 where country = c.country
                ) = 1 THEN "Other"
           ELSE c.country
       END AS country,
       c.customer_id,
       il.*
     FROM invoice_line il
     INNER JOIN invoice i ON i.invoice_id = il.invoice_id
     INNER JOIN customer c ON c.customer_id = i.customer_id
    )

SELECT
    country,
    customers,
    total_sales,
    average_order,
    customer_lifetime_value
FROM
    (
    SELECT
        country,
        count(distinct customer_id) customers,
        SUM(unit_price) total_sales,
        SUM(unit_price) / count(distinct customer_id) customer_lifetime_value,
        SUM(unit_price) / count(distinct invoice_id) average_order,
        CASE
            WHEN country = "Other" THEN 1
            ELSE 0
        END AS sort
    FROM country_or_other
    GROUP BY country
    ORDER BY sort ASC, total_sales DESC
    );
Done.
Out[6]:
country customers total_sales average_order customer_lifetime_value
USA 13 1040.490000000008 7.942671755725252 80.03769230769292
Canada 8 535.5900000000034 7.047236842105309 66.94875000000043
Brazil 5 427.68000000000245 7.011147540983647 85.53600000000048
France 5 389.0700000000021 7.781400000000042 77.81400000000042
Germany 4 334.6200000000016 8.161463414634186 83.6550000000004
Czech Republic 2 273.24000000000103 9.108000000000034 136.62000000000052
United Kingdom 3 245.52000000000078 8.768571428571457 81.84000000000026
Portugal 2 185.13000000000022 6.3837931034482835 92.56500000000011
India 2 183.1500000000002 8.72142857142858 91.5750000000001
Other 15 1094.9400000000085 7.448571428571486 72.99600000000056

Based on the data, there may be opportunity in the following countries:

  • Czech Republic
  • United Kingdom
  • India

It's worth keeping in mind that because the amount of data from each of these countries is relatively low. Because of this, we should be cautious spending too much money on new marketing campaigns, as the sample size is not large enough to give us high confidence. A better approach would be to run small campaigns in these countries, collecting and analyzing the new customers to make sure that these trends hold with new customers.