Digital Music Store Analysis Using SQL!

In this project, I carried out an analysis of a digital music store. The database contained 11 interconnected tables (the Entity Relationship Diagram is provided below).

To ensure the relevance of my findings, I subdivided the analysis into four sections, each tailored to address the specific needs of different teams within the store.

1. Artists and Tracks Overview:

Here, I explored insights on top artists, playlists, and tracks that define the digital store.

Target team: Marketing and Content Teams – to inform marketing strategies and content creation.

Key Questions Explored:

- Which artists lead the pack with the most extensive collections of albums?

- What are the number of tracks in each playlist?

- What is the average song track length per genre?

2. Employee & Customer Dynamics

In this segment, I explored data on customer interactions, employee performance, and the employee hierarchy.

Target team: Customer Service or HR Teams – to inform customer relation techniques.

Key Questions Explored:

- How many customers does each customer support agent attend to?

- What is the total amount generated by all the customers supported by each agent?

- Who does each employee report to?

3. Sales & Performance Metrics

Here, I explored sales performance, revenue, and key sales metrics from invoice records.

Target team: Sales Team – to identify growth opportunities and optimize sales strategies.

Key Questions Explored:

- What are the top performing countries, highlighting their revenue?

- How does the trend of earnings evolve across years?

- What are the genres with the highest purchases in each country?

4. Marketing Initiative Proposal

In this segment, I explored the potential of a promotional campaign event in the USA, targeting the most popular genre - Rock.

Target team: Management Team - to maximize the impact of marketing initiatives.

Key areas explored:

- Who are the customers most likely to be interested in this event based on their purchase history?

- What are their contact details?

- What is the total amount spent (to influence pricing)?