Chinook SQL Analysis

This project analyzes the Chinook music store database using SQL to extract business insights, explore product performance, and make data-driven recommendations.

Business Context

Chinook is a digital music store modeled after iTunes. It sells individual tracks and full albums from various artists across different genres. The company operates globally, managing a catalog of music and a sales/support team.

🗺️ Schema Overview

This schema diagram shows how the tables in the Chinook database are related:

Key Tables Summary

Table	Description
customers	Info about customers, including their country, contact info, and assigned support rep.
employees	Company employees, including sales/support reps and their managers.
invoices	Orders placed by customers, including billing info and invoice totals.
invoice_line	Line items for each invoice (tracks sold, quantities, and prices).
tracks	Detailed metadata about each track (name, album, genre, composer, duration, price).
albums	Metadata about albums (title, associated artist).
artists	Names of artists associated with albums.
genres	Genre types assigned to tracks (Rock, Jazz, etc.).

The goals of this analysis are to:

• Understand which genres and products generate the most revenue
• Evaluate sales team performance by employee
• Analyze revenue by customer country
• Compare album vs. individual track purchases
• Propose actionable business improvements based on the data

This project uses PostgreSQL (via DBeaver) and follows a realistic data analyst workflow. Optional enhancements will include visualizations and advanced SQL features (views, window functions, etc.).

Step 1: Global Business Overview

We start by analyzing overall revenue and customer volume.

Metric	Value
Total revenue	$2328.60
Total customers	59
Total sales (invoices)	412
Average revenue per customer	$39.47
Total tracks in catalog	3503

These metrics give us a general sense of the scale of the business and customer value.

Step 2.1: Product Performance — Best-Selling Genres

This analysis identifies which genres generate the most sales.

Genre	Tracks Sold	% of Total
Rock	835	37.28
Latin	386	17.23
Metal	264	11.79
Alternative & Punk	244	10.89
Jazz	80	3.57
Blues	61	2.72
TV Shows	47	2.10
Classical	41	1.83
R&B/Soul	41	1.83
Reggae	30	1.34

Insight: Rock is the top-selling genre, accounting for over a third of all track purchases. Chinook may benefit from promoting albums or artists in this genre more aggressively.

Step 2.2: Top Revenue-Generating Artists

This analysis highlights the top 10 artists based on total revenue generated from track sales.

Artist	Revenue ($)
Iron Maiden	138.60
U2	105.93
Metallica	90.09
Led Zeppelin	86.13
Lost	81.59
The Office	49.75
Os Paralamas Do Sucesso	44.55
Deep Purple	43.56
Faith No More	41.58
Eric Clapton	39.60

Insight: Chinook’s revenue is concentrated in a few top artists. These artists should be prioritized in promotional campaigns or contract renewals.

Step 2.3: Employee Sales Performance

This query analyzes how much revenue each sales/support employee generated through their assigned customers.

Employee	Title	Hire Date	Total Revenue
Jane Peacock	Sales Support Agent	2002-04-01	$833.04
Margaret Park	Sales Support Agent	2003-05-03	$775.40
Steve Johnson	Sales Support Agent	2003-10-17	$720.16

Insight: Jane Peacock leads in total revenue, but all reps are performing within a ~20% range. Tenure may partially explain the differences.

Step 2.4: Sales by Country & Market Opportunities

This query identifies countries that generate the most revenue, along with customer counts and average order metrics.

Country	Customers	Total Revenue	Lifetime Value	Avg Order
Other	15	604.30	40.29	5.76
USA	13	523.06	40.24	5.75
Canada	8	303.96	38.00	5.43
France	5	195.10	39.02	5.57
Brazil	5	190.10	38.02	5.43
Germany	4	156.48	39.12	5.59
United Kingdom	3	112.86	37.62	5.37
Czech Republic	2	90.24	45.12	6.45
Portugal	2	77.24	38.62	5.52
India	2	75.26	37.63	5.79

Insight: USA and "Other" are top revenue contributors, but countries like Czech Republic show the highest customer lifetime value. These insights suggest a dual strategy:

• Focus on volume in the USA
• Test micro-targeted campaigns in high-value but low-volume markets

Step 2.5: Albums vs Individual Tracks

This query analyzes whether customers tend to purchase entire albums or just individual tracks.

Purchase Type	Invoices	% of Total
Single Tracks	1,254	96.3%
Album Purchase	49	3.7%

Insight:
The vast majority of purchases are individual tracks.
This suggests that users prefer flexibility and only want the specific songs they like, not entire albums.

🎯 Business Recommendation:
Focus on single-track promotions (e.g. bundles, discounts), while keeping album options available for niche preferences.

Step 2.6: Genre Popularity by Country

We analyzed which music genres generated the most revenue in each country. This helps identify market preferences and can guide localized marketing, playlist curation, and content acquisition strategies.

The full dataset (200+ rows) includes revenue per genre per country. Below is a summary showing the top-selling genre for each country:

Country	Top Genre	Revenue ($)
USA	Rock	155.43
Canada	Rock	105.93
Brazil	Rock	80.19
France	Rock	64.35
Germany	Rock	61.38
United Kingdom	Rock	36.63
Portugal	Rock	30.69
India	Rock	24.75
Czech Republic	Rock	24.75
Spain	Rock	21.78
Australia	Rock	21.78
Poland	Rock	21.78
Belgium	Rock	20.79
Denmark	Rock	20.79
Netherlands	Rock	17.82
Finland	Rock	17.82
Italy	Rock	17.82
Norway	Rock	16.83
Austria	Rock	14.85
Ireland	TV Shows	13.93
Sweden	Latin	11.88
Hungary	Rock	10.89
Argentina	Alternative & Punk	8.91
Chile	Rock	8.91

---

🔍 Insight:

• Rock dominates nearly every country, making it the clear global preference among Chinook's customers. This genre should remain the centerpiece of the catalog and promotions.
• However, some regional nuances exist:
  • Ireland’s top genre is “TV Shows,” which may reflect a cultural interest in popular soundtracks (e.g., Game of Thrones, Breaking Bad, Charmed). This indicates potential for a TV Soundtrack playlist or a category for fans of cinematic music.
  • Sweden prefers Latin, and Argentina leans toward Alternative & Punk — both are clear cases where local tastes deviate from the global norm.

---

🎯 Recommendation:

• Focus international marketing efforts around Rock content, including featured artists and cross-sell opportunities.
• Develop curated regional playlists (e.g. "Latin Hits in Sweden", "TV Soundtrack Essentials for Ireland") to deepen local engagement.
• Test genre-specific promotions in emerging markets like Argentina to validate genre responsiveness and growth potential.