The Uphill Advantage: Adventure Works’ Strategic Sales Climb
Tool Used: Power BI | Data Preparation, Cleaning and Transformation, Analysis, and Visualization
Problem
The Sales Manager disseminates your key tasks to develop an end-to-end data analytics solution Sales Dashboard. Adventure Works Bike Shop aims to analyze its historical sales data to extract valuable insights and inform strategic marketing decisions.
Business Objectives
Utilize Power Bl Desktop to:
- Prepare the data using Power Query, and create new dimensions/measures as required.
- Model the data effectively.
- Analyze data to discover patterns and trends.
- And create data-driven insights through a dashboard.
These are the CSV files that need to be imported to Power BI.
Methodology
Revealing Marketing Strategies: Unveiling Insights from Data
In pursuing data-driven insights, I have undergone a thorough data analysis process: data preparation, modeling, analysis, visualization, and giving insights and recommendations.
Step 1: Data Preparation and Examination
- Adjusted regional settings to English (United States) to standardize the date formats following the client’s location.
- Imported CSV files into Power BI for analysis.
- Distinguished between dimensional tables and fact tables to facilitate data modeling.
- Modified data types to ensure data integrity and accuracy.
Step 2: Data Modeling
- Evaluated table relationships to enhance performance and query efficiency.
- Identified ‘Sales_Data’ as the fact table, with other tables serving as dimensions.
Step 3: Data Analysis
- Employed Power BI for exploratory data analysis (EDA) to uncover underlying patterns and trends.
- Introduced measures to compute key financial metrics: Total Revenue, Total Cost, Profit, and Gross Profit Margin Percentage.
Step 4: Data Visualization
- Utilized Power BI to craft compelling visualizations that convey data-driven insights.
- Created a suite of visualizations, including tables, slicers, bar charts, column charts, pie charts, and card graphs, to facilitate a comprehensive understanding of the data.
Step 5: Insight Generation and Recommendations
- Translated analytical findings into coherent insights that resonate with the client’s strategic objectives.
- Formulated insights and recommendations to guide the client towards their targeted outcomes.
Data Modeling
Data Visualization
Insights and Recommendations
Overall Dashboard
Insights
Sales Trends
An analysis of the Monthly Sales data indicates a pattern with a notable upsurge in sales every three months starting from February, with revenues ranging between $3.4M and $7.2M.
Growth Analysis
There is a significant upward trend in sales from 2018 to 2020, with an overall increase exceeding 117%. This suggests a robust growth trajectory for the business.
Revenue by Category
The Bike category stands out as the largest revenue generator, contributing $95M and comprising 86.17% of the total revenue. This underscores the importance of the Bike segment to the company’s financial health.
Geographic Performance
The United States leads in revenue with a total of $62,997,591, likely due to its large population of customers and its love for bikes.
Sales Channels
Resellers are a key revenue channel, accounting for 73.26% of the total revenue. This is particularly pronounced in the United States and Canada, where reseller revenue constitutes 85.10% and 87.91% of each country’s total, respectively. Conversely, Australia and Germany heavily rely on Internet Sales, contributing 85.04% and 59.33% to their total revenues.
Recommendations
Inventory Management
To capitalize on the trimonthly sales spikes, it is recommended to adjust inventory levels accordingly to ensure product availability and maximize revenue potential.
Market Expansion
Considering the significant revenue generated by bikes, expanding the market reach through targeted campaigns in regions with high growth potential could be beneficial such as Australia, Germany, and the United Kingdom having high Gross Profit Margin percentage.
Channel Optimization
Strengthening relationships with resellers and optimizing online sales platforms could enhance sales performance, given their substantial contribution to total revenue.
Development of the Internet Channels
This strategic focus on digital optimization is likely to result in increased profit, as it leverages the cost-efficiency of internet-based transactions, which is expected to increase profit margins.
Data-Driven Marketing
Implementing data-driven marketing strategies to further penetrate the U.S. market, while also exploring opportunities in other high-revenue regions, could lead to increased market share and profitability.
Bike Sales
Insights
Revenue Distribution by Category
The sales analysis reveals that Road Bicycles are the predominant category in total sales, contributing 46.37% to the shop’s revenue. This is significantly higher than the sales of Mountain Bikes, which account for 38.52%, and Touring Bikes, which make up 15.11% of the total revenue.
Sales Performance by Model
In terms of sales performance by model, the Mountain-200 model stands out as the top performer, generating a remarkable $22.3 million in revenue. Following this, the Road-250 model trails with an $8.5 million difference in sales. Notably, the Touring-1000 model, despite being part of the smallest sales category, ranks fourth in revenue, contributing an impressive 67.96% to the Touring Bikes category’s total revenue.
Annual Sales Trends
The fiscal year 2020 marked a significant milestone for Touring Bike sales, which captured 99.06% of the category’s annual revenue. This was a stark contrast to the mere 0.94% recorded in the fiscal year 2019, indicating a substantial increase in consumer interest and market demand for Touring Bikes during that period.
Consumer Preferences
When it comes to consumer preferences, black emerges as the most popular color among all bike sales, representing 34.4% of the total. However, preferences for bike colors vary by category. For Mountain Bikes, black and silver are the favored choices, while Road Bikes see a preference for black, red, and yellow. Touring Bikes, on the other hand, are most commonly chosen in blue and yellow.
Geographical Sales Distribution
Geographically, the United States leads in units sold by a significant margin, with sales nearly quadruple those of the second-highest country, Canada. This disparity is largely attributed to the fact that 39.07% of the shop’s total unique customer base, equating to 3570 unique customers, resides in the United States. In comparison, other countries contribute between 7.19% to 23.59% of unique customers, resulting in fewer units sold internationally.
Recommendations
Capitalize on the Dominance of Road Bikes
Given their substantial share in total sales, strategies should be developed to sustain and enhance the market position of Road Bikes. Special attention should be given to the Road-250 model to close the gap with the leading Mountain-200 model.
Leverage the Surge in Touring Bikes
The remarkable growth in Touring Bike sales in FY2020 warrants an in-depth analysis to understand the drivers behind this trend. Insights gained should inform strategies to maintain and build upon this momentum.
Customize Marketing Based on Color Preferences
The distinct color preferences across bike categories suggest the potential for targeted marketing campaigns. These campaigns should align with the favored colors of each bike type to appeal to customer tastes.
Expand Global Reach
While the United States remains the primary market, there is an opportunity to increase sales in other countries. Tailoring products and marketing efforts to match the preferences and demands of these markets could lead to a more diversified and resilient customer base.
Additional Recommendations for Higher Marketability
Product Diversification and Customization
- Develop a limited-edition series for Road and Mountain Bikes, incorporating the most popular colors and features based on consumer preferences. This could create a buzz and attract enthusiasts looking for exclusive products.
Marketing and Promotional Strategies
- Launch a “Bike of the Month” campaign, highlighting different models each month, especially the Road-250 and Touring-1000, to drive sales and maintain customer engagement.
- Implement seasonal promotions aligned with the trimonthly sales spikes, offering discounts or bundled accessories to incentivize purchases during these peak times.
Global Market Penetration
- Establish partnerships with cycling events and local communities in underrepresented countries to increase brand visibility and market penetration.
- Pursue market expansion opportunities in regions with high Gross Profit Margins like Australia and Germany.
Customer Relationship Management
- Create a loyalty program that rewards frequent purchases and referrals, encouraging repeat business and word-of-mouth marketing.
- Utilize customer data analytics to offer personalized recommendations and promotions based on past purchases and browsing behavior.
Sustainability Initiatives
- Highlight sustainability efforts in marketing campaigns, such as eco-friendly materials or support for green initiatives, which can appeal to environmentally conscious consumers and enhance brand image.
