Unraveling the Data: Exploring Chinook Digital Music Store's Customer Behavior & Sales Analysis with PowerBI.
- Oluwakemi Oyefeso
- Jan 28
- 15 min read
Updated: Feb 1
Problem Statement:
The Chinook Digital Store seeks to analyze its sales and customer behavior from 2009 to 2013 to identify growth opportunities, enhance its marketing strategies, and improve customer retention.
Goals
Understand Current Trends:
Gain insights into sales performance and customer retention patterns.
Identify Growth Opportunities:
Highlight underperforming areas and potential revenue streams.
Develop Data-Driven Strategies:
Create targeted marketing campaigns and personalized recommendations.
Key Stakeholders
Executive Management: To guide strategic decisions on market expansion and resource allocation.
Marketing Team: To craft data-driven campaigns and optimize ROI.
Sales Team: To improve strategies for high-value customer retention and sales growth.
Customer Service Team: To enhance customer experience through data insights.
Data Collection
Source: SQLite database provided by AltSchool Africa.
Tables Extracted:
artist_album_sales
Customer Lifetime Value (CLV)
Customer Segmentation
Market Basket Analysis (Tracks & Albums)
TopArtist table
Limitations:
No gender-specific or user feedback data.
Scope
Part 2: Visualization and analysis using Power BI.
Key Steps:
Step 1: Data Cleaning:
After uploading the tables, I ensured all columns were stored in the correct data types. I verified that the billing address, state, city, and postal codes were recognized as geo-zone data and that the date was correctly identified as such.
I performed the following steps to clean and standardize the data:
Handling Missing Billing States:
I filtered the BillingState column and noticed that some entries were null.
To address this, I conducted a Google search to identify and fill in the missing state values.
Standardizing Billing Country:
While filtering the BillingCountry column, I observed that while most countries were written in full, the USA was abbreviated as "USA."
To ensure consistency, I used the "Replace Values" feature under the TRANSFORM menu in Power BI to replace all instances of "USA" with "United States of America."
Addressing Missing Billing Postal Codes:
Filtering the BillingPostalCode column revealed that some rows had missing values. These missing postal codes would exclude certain locations from the geographic distribution map, which is crucial for identifying customer locations for targeted ads and promotions.
To resolve this without altering existing correct values, I followed these steps:
Using the BillingAddress, State, and Country columns, I performed a quick Google search to retrieve the missing postal codes.
In Power BI's Power Query Editor, I navigated to the Customer_LifeTime_Value table and added a new Custom Column.
I used a formula to populate the missing postal codes based on the retrieved information.
if [BillingPostalCode] = null or [BillingPostalCode] = "" then
if [BillingCity] = "Lisbon" then "1100-042"
else if [BillingCity] = "Porto" then "4350-305"
else if [BillingCity] = "Santiago" then "8320000"
else if [BillingCity] = "Dublin" then "D02 A529"
else [BillingPostalCode]
else [BillingPostalCode]d. After creating the new custom column and populating it with the missing postal codes, I took the following steps to finalize the data:
Renaming the Custom Column:
I changed the name of the new custom column to PostalCode for clarity and consistency.
Removing the Old Column:
I deleted the original BillingPostalCode column that contained missing values to avoid redundancy and ensure data integrity.
Renaming Other Columns:
To simplify the dataset and improve readability, I renamed other relevant columns to more straightforward and descriptive names.
Step 2: Data Transformation
Phases in Step 2:
To calculate my KPIs and the percentage changes, I needed four new measures for each KPI.
Calculating the Total measures:
DAX Formulas:
a.
Total Customers = DISTINCTCOUNT(artist_album_sales[CustomerId])The Previous Year Values
Total Sales = SUM(artist_album_sales[Amount])c.
Total Tracks Sold = COUNT(artist_album_sales[TrackId])d.
Total Transactions = DISTINCTCOUNT(artist_album_sales[InvoiceId])Calculating Previous Year Measures:
Previous Year Values Dax Formulas:
a.
Previous Year Customers =
CALCULATE(
[Total Customers],
SAMEPERIODLASTYEAR(artist_album_sales[InvoiceDate].[Date])
)b.
Previous Year Sales =
CALCULATE(
[Total Sales],
SAMEPERIODLASTYEAR(artist_album_sales[InvoiceDate].[Date])
)
c.
Previous Year TracksSold =
CALCULATE(
[Total Tracks Sold],
SAMEPERIODLASTYEAR(artist_album_sales[InvoiceDate].[Date])
)
d.
Previous Year Transactions =
CALCULATE(
[Total Transactions],
SAMEPERIODLASTYEAR(artist_album_sales[InvoiceDate].[Date])
)
Calculating the difference between the previous and current year Measures:
Dax Formulas:
a.
CustomersDifference = [Total Customers] - [Previous Year Customers]b.
DifferenceInTracksSold = [Total Tracks Sold] - [Previous Year TracksSold]c.
SalesDifference = [Total Sales] - [Previous Year Sales]d.
TransactionsDifference = [Total Transactions] - [Previous Year Transactions]Calculating Percentage Difference Measures:
Dax Formulas:
a.
% Change In TracksSold =
DIVIDE([DifferenceInTracksSold], [Previous Year TracksSold], 0)
b.
Customers % Change =
DIVIDE([CustomersDifference], [Previous Year Customers], 0)c.
Sales % Change =
DIVIDE([SalesDifference], [Previous Year Sales], 0)
d.
Transactions % Change =
DIVIDE([TransactionsDifference], [Previous Year Transactions], 0)
Step 3: Data Visualization, Analysis & Insights.
Phases in Step 3:
KPIs Overview (2009–2013)
Total Sales: $2,330
Total Customers: 59
Total Tracks Sold: 1,983
Total Transactions: 412
Average Revenue Per Customer:
$2,330 ÷ 59 = $39.49 per customer
This indicates that each customer, on average, spent approximately $39.49 over the five years.
Average Revenue Per Transaction:
$2,330 ÷ 412 = $5.66 per transaction
The revenue generated per transaction suggests that customers typically make smaller purchases.
Average Tracks Sold Per Transaction:
1,983 ÷ 412 = 4.81 tracks per transaction
On average, customers purchased around 4-5 tracks per transaction.
Customer Engagement:
59 customers over five years indicate a limited customer base.
However, the average tracks sold and revenue per customer suggest relatively high engagement from the existing base.
Annual Sales Overview:
i. 2009:
Key Metrics:
Total Sales: $580.14
Total Customers: 46
Tracks Sold: 454
Transactions: 83
Quarterly Trend:
Q2 to Q4 shows consistent performance after a slight Q1 increase.
Top Artists:
Iron Maiden, Led Zeppelin, Metallica.
Top Genres:
Rock, Latin, Alternative & Punk.
Insight: Rock dominates, showing strong consumer preference.
ii. 2010:
Key Metrics:
Total Sales: $558.73 (-4% YoY)
Total Customers: 42 (-9% YoY)
Tracks Sold: 447 (-2% YoY)
Quarterly Trend:
Sales declined in Q2, remaining steady afterward.
Top Artists:
U2, Iron Maiden, Metallica.
Top Genres:
Rock, Latin, Metal.
Insight: A slight decline in sales and customers suggests potential market saturation or unmet needs.
iii. 2011:
Key Metrics:
Total Sales: $463.64 (-17% YoY)
Total Customers: 39 (-7% YoY)
Tracks Sold: 401 (-10% YoY)
Quarterly Trend:
A sharp increase in Q2, followed by consistent declines.
Top Artists:
U2, Lost, R.E.M.
Top Genres:
Rock, Latin, Alternative & Punk.
Insight: The overall metrics drop indicates customer acquisition and retention challenges.
iv. 2012:
Key Metrics:
Total Sales: $405.23 (-13% YoY)
Total Customers: 39 (No change YoY)
Tracks Sold: 368 (-8% YoY)
Quarterly Trend:
Growth in Q3 but declines in other quarters.
Top Artists:
Iron Maiden, Metallica, U2.
Top Genres:
Rock, Latin, Metal.
Insight: Stable customer base, but declining sales highlight a need for better engagement and upselling strategies.
v. 2013:
Key Metrics:
Total Sales: $318.87 (-21% YoY)
Total Customers: 38 (-3% YoY)
Tracks Sold: 313 (-15% YoY)
Quarterly Trend:
Consistent growth from Q2 to Q4.
Top Artists:
Iron Maiden, U2, Lost.
Top Genres:
Rock, Latin, Alternative & Punk.
Insight: The decline in the customer base, and sales highlights a need for better upselling strategies and customer acquisition and retention.
Insights:
Genre Preference: Rock dominates all years, followed by Latin and Metal. Marketing strategies should focus on promoting these genres.
Top Artists: Iron Maiden and U2 are consistently top performers. Consider exclusive promotions or bundles to boost sales further.
Customer Behavior: The drop in customers and sales year-over-year highlights challenges in retention. Strategies like loyalty programs and targeted campaigns could help.
Seasonal Trends: Peaks in specific quarters (e.g., Q2 2011, Q3 2012, Q4 2013) indicate opportunities for seasonal promotions.
Suggestions:
Improve Retention:
Introduce loyalty programs and personalized recommendations.
Seasonal Campaigns:
Leverage historical trends to design high-impact seasonal promotions.
Expand Genre Offerings:
Consider adding more popular sub-genres to attract niche audiences.
Upselling Strategies:
Offer bundled discounts on albums and tracks frequently purchased together.
Geographic Insights:
Use cleaned geographic data to target high-performing locations with specific ads.
Customer Feedback:
Incorporate surveys to gain qualitative insights into customer preferences.
Sales Pattern For The Top 15 Selling Artists:
Insight:
Between 2009 and 2013, sales for the top 15 selling artists saw a steady decline:
2009 to 2010: a 3.62% decrease in sales
2010 to 2011: a 16.99% decrease in sales
2011 to 2012: a 12.71% decrease in sales
2012 to 2013: a 21.24% decrease in sales
Overall, from 2009 to 2013, there was a total sales drop of 45%.
Steady Decline in Sales: The data shows a consistent decline in sales from 2009 to 2013 for the top 15 selling artists. The sharpest drops occurred between 2012 and 2013, when sales decreased by 21.24% suggesting that the overall demand for digital music from top-selling artists is weakening.
Impact of Market Trends: The drop could be linked to broader market trends, such as the rise of music streaming services like Spotify, Apple Music, and YouTube. These platforms may have drawn customers away from purchasing individual songs or albums, leading to a decline in sales in the store.
Changing Consumer Behavior: A potential shift in consumer behavior from ownership to access (via streaming) could be influencing purchasing patterns. Consumers may prefer subscribing to music services rather than purchasing individual tracks, resulting in fewer downloads at Chinook.
Recommendations:
Enhance Digital Music Offerings: Chinook should consider diversifying its offerings to appeal to evolving consumer preferences. This could include providing exclusive content, such as live recordings, interviews, or early access to new releases, to create added value for customers who prefer to buy rather than stream.
Subscription or Streaming Model: Exploring the introduction of a subscription model, where customers can access a large catalog of music for a monthly fee, could help combat the shift to streaming services. Offering a service that allows users to download a certain number of tracks per month for a fixed fee might increase customer loyalty.
Promote Emerging Artists: Focus marketing efforts on promoting up-and-coming artists in addition to established ones. Creating specialized playlists or promotional content around emerging talent can draw in new customers who want to discover fresh music and help drive additional sales.
Cross-Platform Integration: To compete with streaming services, Chinook could explore partnerships or integrations with popular platforms such as Spotify or Apple Music, allowing users to purchase and stream content seamlessly across both platforms. This approach could encourage current music downloaders to explore streaming without abandoning the download model entirely.
Seasonal Sales & Discounts: Introducing limited-time offers, seasonal discounts, or bundle deals could help increase sales, especially during key shopping seasons. Bundling multiple songs or albums into a discount package can incentivize customers to make larger purchases.
Genre Popularity and Media Type Sales Analysis:
Insight:
Top-Selling Genre: Rock
Rock is the most popular genre, accounting for 42.11% of the total tracks sold.
Rock performed best in Q3 (Summer), with total sales of 214.83, likely due to increased leisure activities during this period.
The lowest sales for Rock occurred in Q4 (Festive Season), with 198.99 in total sales, suggesting reduced interest in this genre during the holidays.
Lowest-Selling Genres:
By Quantity Sold: Science Fiction was the least popular, with only 6 units sold.
By Total Sales Amount: Rock & Roll generated the lowest revenue, at $5.94. This indicates a lack of customer demand or a limited catalog in these genres.
Monthly Sales Trends:
Highest Sales Month: January, with a total sales amount of $215.01, accounting for 9.24% of annual sales. This was 25.47% higher than the lowest month, November, which recorded $171.36 in sales.
Monthly Range: Sales fluctuated between $171.36 (November) and $215.01 (January), highlighting significant seasonal variations.
Recommendations:
Capitalize on Rock’s Popularity:
Promotional Campaigns: Focus on promoting Rock music heavily during Q3 to maximize sales during the peak season. This can include exclusive bundles, discounts, or featured playlists.
Boost Q4 Sales: Develop strategies to increase Rock sales during the festive season. This could involve curating holiday-themed Rock playlists or offering holiday discounts.
Address Underperforming Genres:
For Science Fiction and Rock & Roll, consider:
Improving Visibility: Use marketing efforts to showcase these genres, like highlighting them in recommendations or promotional banners.
Expanding Catalog: Assess whether the limited selection in these genres impacts sales. If so, work on acquiring more titles.
Niche Targeting: Target niche audiences who may appreciate these genres through personalized marketing.
Seasonal Promotions:
Leverage the peak sales month of January by launching New Year promotions to encourage early-year purchases.
For low-performing months like November, introduce sales events (e.g., Black Friday deals) to boost customer spending.
Monthly Trends Optimization:
Use historical data to anticipate sales fluctuations and plan inventory and marketing campaigns accordingly.
Create exclusive offers or events during traditionally low-performing months (e.g., November) to smooth out revenue dips.
Customer Insights:
Conduct customer surveys or analyze purchasing patterns to better understand why certain genres underperform. Use these insights to refine catalog offerings and marketing strategies.
By focusing on Rock's strengths, addressing the weaknesses of underperforming genres, and leveraging seasonal trends, Chinook can optimize its sales strategy and improve revenue across all categories.
Seasonality:
Maximize Q3 (Summer) Opportunities:
Launch summer-themed music collections and promotions to capitalize on peak sales during this period.
Boost Q4 Sales (Festive Season):
Offer holiday discounts, themed playlists, or bundle deals to attract customers during the festive months when sales are typically low.
January Promotions:
Encourage high engagement in January with "New Year, New Music" campaigns, gift card redemption offers, or exclusive new releases to sustain the strong sales momentum.
Focus on November:
Create pre-holiday campaigns or Black Friday music deals to counteract the seasonal dip and encourage purchases.
Customer Segmentation Analysis:
Insights:
Loyal Customers (29 customers):
The largest group, contributing 49.99% of total sales.
Indicates a strong, consistent customer base that drives nearly half of the store’s revenue.
At Risk Customers (12 customers):
Contributing a significant 36.64% of total sales, despite their lower engagement.
This group represents an opportunity to recover and re-engage valuable customers.
New Customers (15 customers):
Comprising 1.86% of total sales, showing low immediate revenue impact but potential for growth.
Indicates that efforts to convert new customers into loyal ones may not be very effective yet.
Hibernating Customers (3 customers):
The smallest group contributed 11.52% of total sales.
Represents customers who were once active but have significantly reduced engagement.
Monthly Sales Trends of Customers:
November Peak: This represents 11.21% of yearly sales, with a peak sales value of $35.73. Probably affected by holiday promotions or buying patterns.
Consistent Sales Months: January, March, and June–October all record steady sales of $26.73, suggesting minimal fluctuations during these periods.
Low Point in February: Sales decreased to $18.81, possibly reflecting a decline in activity following the holiday season.
Top Customer:
Helena Holy is the top customer with a total purchase value of $21.87. This highlights the importance of focusing on high-value customers for retention and personalized marketing.
Recency Vs Monetary Score:
1. High-Value & Recent Customers (Loyal Customers)
Customers with Recency Score 1-2 and Monetary Score 5 are the most valuable.
These customers have purchased recently and spent the most, making them ideal candidates for retention and VIP programs.
Example: The highest count (7) appears at Recency 2 & Monetary 5, indicating a strong group of high spenders who have made recent purchases.
2. At-Risk High Spenders
Customers with Monetary Score 5 but Recency Score 3-4 previously spent a lot but have not purchased recently.
If they continue to disengage, they may shift into the hibernating category.
Chinook should consider personalized retention efforts (e.g., exclusive offers, loyalty perks) to bring them back.
3. Hibernating Low-Spenders
Customers with Recency Score 4-5 and Monetary Score 1-2 are disengaged and contribute the least revenue.
They have not purchased recently and spend minimally when they do.
Chinook should decide whether to reactivate them with discounts or shift marketing focus away from this low-return group.
Recommendations:
1. Focus on Loyal Customers:
Rewards Program: Introduce a loyalty rewards program to strengthen engagement with this group and incentivize repeat purchases.
Exclusive Benefits: Offer early access to new releases, discounts, or VIP perks to make them feel valued.
2. Re-engage At Risk Customers:
Targeted Campaigns: Create personalized offers to encourage this group to return, such as discounts on previously purchased genres or tracks.
Email Reminders: Use automated emails or notifications highlighting new releases, promotions, or limited-time offers.
3. Convert New Customers:
Onboarding Experience: Offer first-time buyers a welcome discount or free track download to encourage additional purchases.
Follow-Up: Send personalized recommendations and encourage subscription to newsletters for consistent communication.
Upsell and Cross-Sell: Suggest complementary tracks or albums based on their first purchase to increase their value.
4. Engage Hibernating Customers:
Win-Back Campaigns: Offer deep discounts or bundle deals to re-engage hibernating customers. Highlight how their previous purchases align with current popular tracks.
Feedback Collection: Reach out to understand why they stopped purchasing and use the insights to improve.
5. Optimize for Seasonality:
November Campaigns: Build on the strong November performance by introducing Black Friday and holiday deals.
February Boost: Introduce Valentine’s Day promotions or offers to counteract the dip in February sales.
6. Leverage Top Customers:
Helena Holy as a Case Study: Identify why Helena Holy spends more than others and replicate this pattern across customers.
Personalization: Provide tailored offers to high-value customers and acknowledge them through VIP recognition programs.
By implementing these suggestions, Chinook can strengthen customer retention, recover lost customers, and convert new customers into loyal ones, driving sustained growth.
Customer Lifetime Value:
Loyal Customers
Key Insights:
Consistently maintain the highest Customer Lifetime Value (CLV).
CLV has remained stable over the years, with only minor fluctuations, reflecting strong retention and consistent spending.
This group represents the backbone of the business’s revenue stream.
New Customers
Key Insights:
Start with a low CLV but show a significant upward trend from 2012 to 2013.
Indicates successful acquisition campaigns and improved customer engagement in later years.
At-Risk Customers
Key Insights:
CLV showed a gradual decline between 2010 and 2012 but recovered slightly in 2013.
This group may be losing interest or experiencing unmet needs but shows potential for re-engagement.
Hibernating Customers
Key Insights:
Have the lowest and most stagnant CLV across the years.
Reflect minimal engagement and little to no spending behavior.
Recommendations:
Segment-Specific Strategies: Tailor engagement strategies based on the unique behaviors and needs of each segment.
Data-Driven Personalization: Leverage purchase history and behavioral data to send highly relevant offers and recommendations.
Monitor Trends: Regularly track CLV trends to adjust strategies as customer behaviors evolve.
Focus on Retention: Retaining loyal and at-risk customers is often more cost-effective than acquiring new ones.
Cross-Sell and Upsell Opportunities: Promote bundles or complementary products to increase average spend per customer.
Market Basket Analysis - Tracks:
Displays the association rules and frequency of tracks frequently purchased together. For example, selecting the track [Just Like] Starting Over from the dropdown menu reveals the association rules and frequency of tracks commonly bought together, as shown in the charts.
The analysis provides insights into association rules and the frequency of tracks often purchased together.
By selecting a specific track (e.g., [Just Like] Starting Over) from the slicer, the Marketing and Sales departments, can view detailed information on which other tracks are commonly bought alongside it. This information is visualized in charts, making it easy to interpret patterns and purchasing behavior.
Association Rules:
The analysis identifies strong relationships between tracks that are frequently purchased together.
For example, if a customer buys [Just Like] Starting Over, the charts reveal other tracks most likely to be added to their purchase.
Frequency:
The frequency data reveals which tracks are most frequently paired, offering valuable insights into customer preferences.
This can guide cross-selling and upselling strategies, ensuring customers are shown relevant recommendations during their shopping experience.
Recommendations:
1. Personalized Recommendations:
Use the association rules to power a recommendation engine on the platform.
When customers place a track in their cart, show a "Frequently Bought Together" section highlighting tracks identified in the analysis, similar to what is seen on sites like Amazon.
2. Curated Playlists:
Create and promote pre-curated playlists based on the most common track associations.
Highlight these playlists in marketing emails, homepage banners, or special offers.
3. Bundle Deals:
Introduce discounted bundles for tracks frequently purchased together.
For example, offer a small discount when customers buy [Just Like] Starting Over and another associated track.
4. Targeted Marketing Campaigns:
Segment customers based on their purchase history and send personalized emails recommending tracks that complement their previous purchases.
Use data from the frequency analysis to tailor these recommendations.
5. Incentivize Larger Purchases:
Encourage customers to purchase more by offering free shipping or discounts when they add multiple associated tracks to their cart.
6. Improve Product Discovery:
Enhance the search and browse experience by displaying related tracks on product pages using association rules.
Include messaging like "Customers who bought this track also purchased..." to make the experience more engaging.
7. Seasonal Promotions Using Insights:
Leverage the findings to create seasonal playlists or sales campaigns (e.g., summer hits, holiday classics) based on frequently purchased track associations during specific times of the year.
8. Track Pairing Analysis Over Time:
Regularly review and update the association rule analysis to identify new trends in customer preferences.
This ensures that the recommendations and promotions stay relevant as buying behavior evolves.
Market Basket Analysis - Albums:
Displays the association rules and frequency of tracks frequently purchased together. For example, selecting the Album [1997] Black Light Syndrome from the dropdown menu reveals the association rules and frequency of Albums commonly bought together, as shown in the charts.
The analysis highlights association rules and the frequency of albums often purchased together. For instance, selecting the album [1997] Black Light Syndrome from the dropdown menu allows users to view detailed insights into which other albums are frequently purchased alongside it.
The charts visually display this information, providing a clear and practical insight into customer purchasing behavior.
Recommendations:
1. Enhance Recommendation Systems:
Use the insights to develop an AI-powered recommendation engine that suggests albums commonly purchased together.
Display these suggestions on the album's product page with a message like, "Customers who bought this album also purchased...".
2. Promote Bundle Deals:
Create bundle offers for albums that are frequently purchased together.
For example, offer a small discount for purchasing [1997] Black Light Syndrome alongside its most commonly associated albums.
3. Curated Collections:
Use the insights to design themed playlists or collections, such as albums that pair well by genre, artist, or release year.
Promote these collections in newsletters, social media campaigns, and the homepage.
4. Personalized Marketing Campaigns:
Target customers who bought specific albums (e.g., [1997] Black Light Syndrome) with emails or ads recommending the top associated albums.
Highlight these recommendations in the form of limited-time promotions or exclusive bundles.
5. Optimize Store Layout (Physical or Digital):
For physical stores: Place albums frequently purchased together close.
For online stores: Use prominent "Frequently Bought Together" sections to guide customer navigation.
6. Seasonal Campaigns:
Analyze whether associations vary by season and design season-specific bundles or discounts.
For example, offer summer-themed album packages if data shows a trend in certain purchases during that time.
7. Upsell with Additional Merchandise:
Upsell related merchandise such as posters, apparel, or concert tickets that align with frequently purchased albums.
8. Analyze Trends Over Time:
Monitor how these associations evolve to identify emerging patterns or shifts in customer preferences.
Update recommendations and promotions regularly to reflect the latest trends.
Comments