Surfing the Data for Product Success: Sales Insights for Adventure Works

About this project

As a Business Intelligence Analyst, I spearheaded the transformation of data into actionable insights that the management team can use to enhance their Regional performance, track KPIs (Sales, Revenue, Profit, and Returns), analyze product level trends and identify high value customers. My analysis for a Global manufacturing company that produces cycling equipment and accessories focused on Dashboard, Sales, Customer, Products, and Demographics Analysis. Utilizing advanced AI tools such as decomposition trees and dynamic search bars, along with performance optimization techniques, I ensured that our reports provided smooth and scalable performance.****

1. Dashboard:

• Key Performing Indicators: The KPIs consist of Total revenue reached $1.83 million, slightly below last month's $1.76 million but falling short of the $1.945 million target (5.3% difference). We processed 2.15k orders, a minor dip from last month's 2.156k, with 581 orders placed over the weekend. Returns totaled $1.81k, a slight increase from the previous month, equating to a 22.13% return rate. Our overall financial balance stands at $10.45772 million.
• Customer Segmentation: A clear wealth gap exists among our customers, with high earners accounting for 11.23% of purchases, middle earners at 46.10%, and low earners at 40.80%.
• Revenue Tracker: The company aims for a total revenue of $25.4 million. North America leads with $9 million, followed by Europe at $7.2 million and the Pacific region at $7 million.

2. Product Analysis:

• Key Performing Indicators: The KPIs consist of Total Product Cost reached $121.20k. We offered a Product Price of $209.3k, which is slightly on a higher end with a Discount Price totaled $18.8k.
• Top 10 Products: Our data reveals some standout performers. The Water Bottle, a hydration essential, boasts 3,983 orders generating $39,755.33 in revenue, but carries a concerning 8.24% return rate. In contrast, the Sport-100 Helmet, Black, a safety staple, has 1,940 orders and impressive revenue of $65,269.748, with a more manageable 2.87% return rate. Finally, the AWC Logo Cap, a classic accessory, has 2,062 orders contributing $35,882.0742 to the bottom line, and impresses with the lowest return rate at 2.49%.
• Monthly Financial Performance: Our financial performance followed an upward trajectory from January to June, culminating in a peak profit of $1,269,393. However, a sharp decline occurred in July, plummeting to $539,307. While subsequent months witnessed a gradual recovery, profits failed to regain the June peak, settling at $926,515 in December.
• Product Category Performance: Components emerged as the top-performing product category, generating a substantial $10,750,428 in revenue. Bikes followed closely behind with $7,880,698 in sales. Clothing, unfortunately, lagged behind as the lowest-performing category, contributing $3,312,786 to overall revenue.

3. Sales Analysis:

• Key Performing Indicators: The KPIs consist of 10-day rolling revenue of $616.3K and a strong 90-day rolling profit of $2.14M. With a low return rate of 2.2% and 11.3K high-ticket orders, the data reflects robust financial performance and customer satisfaction.
• Product SKUs: The AI Decomposition Tree offers a comprehensive analysis centered around a total of 25,164 orders, with a specific focus on the product SKU BB-7421. This analysis breaks down the data by examining the SKU category 'BB' and delves deeper into the nuances of the SKU type 'BB-7421', providing valuable insights into the product's performance and trends within its category.
• Performance Metrics: The performance metrics reveal a nuanced picture of the company's performance across various dimensions.**** The actual sales o**f $771.58K **fall short of the target of $1.83 million. This indicates a significant gap in achieving sales goals, suggesting potential issues with market penetration, pricing strategies, or sales execution that need to be addressed to meet future targets. The total profit achieved is $10.46M, which is slightly below the target of $10.65M. While this shortfall is relatively minor, it still highlights the need for strategies to boost profitability, possibly through cost management, increased revenue, or efficiency improvements. The total number of orders stands at 25.16K, slightly below the target of 25.32K. This marginal shortfall indicates that while the company is close to meeting its order targets, there may be room for improvement in customer acquisition or retention strategies to close the gap.
• The Power of One or Many: The analysis reveals that 37.33% of purchases are for single items, while 62.67% involve multiple items. Last week, out of a total of 55 orders, this distribution was evident, underscoring a predominant preference for multi-item purchases. This trend suggests that customers are more inclined to buy in bulk or select several products in a single transaction, which could inform strategies for inventory management and promotional offers.

4. Customer Analysis:

• Key Performing Indicators: The company has 18.15K unique customers, with a revenue per customer of $1,431. The top customer, Mr. Maurice Shan, placed 6 orders, generating a total revenue of $12.41K at the same revenue per-customer rate of $1,431.
• Market Penetration by Population: The market penetration shows a strong presence in the professional sector, with 7.93K individuals. This suggests that the product or service is well-suited to or highly valued by those in professional roles, possibly due to higher disposable incomes or a greater need for specialized products. The penetration among skilled manual workers (5.95K) and management (4.38K) is also significant, indicating a broader appeal that spans both higher and mid-level job categories. This diversity in market segments could suggest a versatile offering that meets different needs across various industries.
• Customer Growth Analysis: Customer Growth Analysis indicates a period of stagnation from February to November, where the customer base saw a slight decline from 1.58K to 1.57K. The highest growth occurred in March, peaking at 1.61K, while July experienced a noticeable dip, reaching the lowest point of 1.49K. This trend recommends fluctuating engagement, with a brief peak followed by a period of decline and minimal recovery.

Recommendations for Management Team:

1. Enhance Sales Strategies : With actual sales falling short of the $1.83 million target, a review of current sales strategies is essential. Consider implementing targeted marketing campaigns, optimizing pricing strategies, and enhancing sales training to boost performance. Expand Market Penetration: Focus on increasing market share by exploring new customer segments or expanding into underrepresented regions, especially considering the lower sales figures in comparison to regional targets.

2. Improve Profitability : The profit achieved ($10.46M) is slightly below the target ($10.65M). Implement cost-control measures to reduce expenses and improve margins. Analyze cost drivers and seek opportunities for cost reduction without compromising quality. Revenue Enhancement: Explore opportunities to increase revenue through upselling, cross-selling, and improving product offerings. Evaluate pricing strategies to ensure they align with market expectations and customer willingness to pay.

3. Optimize Product Offerings : Invest more in marketing and inventory for top-performing products like the Sport-100 Helmet and Water Bottle, while addressing issues related to high return rates. Revise Pricing Strategies: Review the pricing and discount strategies, particularly for products with high return rates, to enhance profitability and reduce returns.

4. Boost Customer Acquisition and Retention : With a slight decline in customer growth and a notable dip in July, focus on improving customer engagement through personalized marketing, loyalty programs, and better customer service. Target High-Value Customers: Leverage insights from high-value customers like Mr. Maurice Shan to develop targeted offers and personalized experiences that can encourage repeat purchases and increase overall customer lifetime value.

5. Refine Inventory and Order Management : Given the preference for multi-item purchases (62.67%), optimize inventory management to ensure adequate stock levels for multi-item orders and reduce stock outs or overstock situations. Analyze Order Trends: Utilize insights from order performance metrics to align inventory with demand patterns, and adjust order fulfillment strategies to improve efficiency and customer satisfaction.

6. Enhance Data Analysis and Reporting : Ensure that performance metrics and KPIs are accurately tracked and reported. Implement more granular analysis tools if necessary to better understand trends and performance gaps. Utilize Advanced Analytics: Continue leveraging advanced AI tools like decomposition trees to gain deeper insights into product performance and customer behavior, which can guide strategic decision-making.