This analysis is based on the data provided and reflects the state of the dataset as of the time of the analysis. The insights and recommendations are derived solely from my point of view and the dataset in question and do not necessarily represent the broader operations or circumstances of the company. The analysis assumes the accuracy of the data as received and has not been independently verified. Future analyses may yield different insights as new data becomes available or as business conditions change.
Due to the large volume of results generated by this analysis, only a subset has been presented here to illustrate the key trends and patterns. Specifically, the top 10 to 15 results have been included as screenshots to provide a snapshot of the most relevant findings. For a complete view of the data and to explore additional insights, please refer to the full dataset.
SELECT DISTINCT * FROM retail_store.inventory;- All the duplicates removed!
SELECT
`Month_Year`,
`Product_Category`,
`Units_Sold`,
CAST(REPLACE(CAST(`Discount` AS STRING), '%', '') AS FLOAT64) AS `Discount`,
`Targeted_Campaign`,
`Customer_Ratings`,
`Defects_Reported`
FROM `retail_store.inventory`;
- This query replaces the % sign in the Discount column with an empty string and casts the result as a floating-point number.
SELECT *
FROM `retail_store.inventory`
WHERE
`Month_Year` IS NOT NULL
AND `Product_Category` IS NOT NULL
AND `Units_Sold` IS NOT NULL
AND `Discount` IS NOT NULL
AND `Targeted_Campaign` IS NOT NULL
AND `Customer_Ratings` IS NOT NULL
AND `Defects_Reported` IS NOT NULL;SELECT
MIN(`Units_Sold`) AS Min_Units_Sold,
MAX(`Units_Sold`) AS Max_Units_Sold,
AVG(`Units_Sold`) AS Avg_Units_Sold,
STDDEV(`Units_Sold`) AS StdDev_Units_Sold,
MIN(`Customer_Ratings`) AS Min_Customer_Ratings,
MAX(`Customer_Ratings`) AS Max_Customer_Ratings,
AVG(`Customer_Ratings`) AS Avg_Customer_Ratings,
STDDEV(`Customer_Ratings`) AS StdDev_Customer_Ratings
FROM `retail_store.inventory`;
From this above image we can understand that the number of order placed over past years is increased.
- Sales Volume Variability: There is a wide range in the number of units sold, with the minimum at 1,215 and the maximum at 7,125. The standard deviation of approximately 1,989 units suggests significant variability in sales volume across different periods or product categories.
- Average Sales: The average units sold across all categories and periods is approximately 3,379 units. This figure can serve as a benchmark for evaluating the performance of sales promotions or changes in strategy.
- Customer Ratings Consistency: Customer ratings show less variability than sales volume, with a standard deviation of about 0.60. This suggests that customers' perceptions of products are relatively consistent over time.
- Rating Extremes: The range of customer ratings is from 7.1 to 9.6, indicating that even the lowest-rated products are viewed fairly positively. However, there is still room for improvement, given that the ratings do not consistently hit the maximum (which would be 10 in most rating scales).
- Investigate the Variability in Sales: Explore the factors contributing to the high variability in units sold. Determine if certain product categories or time periods (such as holidays or sales events) are driving the majority of sales, and consider focusing marketing efforts on these areas.
- Target the Lower End of Sales Volume: Identify the products or categories that are contributing to the lower end of the sales volume range and assess why they are underperforming. Consider product improvements, repackaging, bundling with more popular items, or discontinuing low-performers.
- Leverage High Customer Ratings: Use the strong customer ratings in marketing materials and product highlights. Since ratings are generally high, showcasing customer satisfaction could help attract new customers and maintain the loyalty of existing ones.
- Boost the Lower-Rated Products: For products at the lower end of the ratings scale, consider investigating the causes of customer dissatisfaction and implementing product improvements. Additionally, solicit more feedback to understand customer needs and preferences.
- Standardize Excellence: With the highest customer ratings reaching 9.6, strive to make this level of satisfaction the standard. Implement quality control measures or customer service training programs to maintain and elevate the customer experience.
SELECT
`Product_Category`,
SUM(`Units_Sold`) AS Total_Units_Sold,
AVG(`Units_Sold`) AS Avg_Units_Sold
FROM `retail_store.inventory`
GROUP BY `Product_Category`;
-
Top Seller: Fitness Gadgets emerge as the best-selling category with a total of 35,585 units sold, averaging 3,954 units per period. This indicates a strong market demand or a successful sales strategy for this category.
-
Consistent Performer: Travel Accessories also show strong sales with the highest average units sold per period at 3,106, slightly above Designer Clothes. This suggests consistent performance across the periods analyzed.
-
Room for Growth: Designer Clothes, while not far behind, have the lowest average units sold per period at 3,005. There may be untapped potential in this category.
-
Capitalize on the Popularity of Fitness Gadgets: Given the high sales volume, consider expanding the range of Fitness Gadgets offered, allocate more marketing budget to this category, or explore cross-selling opportunities with other categories.
-
Seasonal or Promotional Strategies for Travel Accessories: Investigate the periods when Travel Accessories perform best and align marketing strategies to capitalize on these peaks. Consider seasonal promotions or bundling with related categories.
-
Boost Designer Clothes Sales: To increase sales of Designer Clothes, consider strategies such as introducing a wider variety of styles, offering personalized items, or implementing targeted marketing campaigns, especially if there are specific periods where sales dip.
-
Cross-Category Analysis: Analyze the data further to understand if the sales of one category benefit from the performance of another. For example, do increased sales in Fitness Gadgets correlate with a rise in sales of Designer Clothes or Travel Accessories?
-
Customer Feedback: Collect customer feedback specifically related to these categories to understand the driving factors behind the sales figures. Use this information to tailor products and marketing strategies accordingly.
SELECT
`Month_Year`,
SUM(`Units_Sold`) AS Total_Units_Sold
FROM `retail_store.inventory`
GROUP BY `Month_Year`
ORDER BY Total_Units_Sold desc;
-
Peak Season Sales: The Oct-Dec quarters of both 2018 and 2019 show the highest sales volumes, with 23,045 and 20,060 units sold respectively. This suggests a significant seasonal impact, likely due to the holiday season.
-
Year-Over-Year Decline: There is a noticeable decrease in sales during the Oct-Dec quarter from 2018 to 2019, which could indicate a trend that needs addressing or could be due to external factors impacting sales.
-
Mid-Year Performance: The mid-year sales in Jul-Sep and Apr-Jun quarters are lower than the end-of-year sales, which is consistent across both years. However, there's an increase in sales from Jul-Sep 2018 to Jul-Sep 2019, which is a positive trend.
-
Start of the Year Sales: The first quarters (Jan-Mar) of both 2018 and 2019 have the lowest sales numbers, but there's a concerning drop from 2018 to 2019.
-
Optimize Inventory for Peak Seasons: Given the high sales in the Oct-Dec quarters, ensure adequate stock levels during this period to avoid missed sales opportunities.
-
Investigate the Decline: Examine why there might be a decrease in sales in the Oct-Dec quarter from 2018 to 2019. Assess whether it's due to internal factors like stock issues or external factors such as market trends.
-
Promotional Strategies for Mid-Year Sales: Develop targeted marketing and sales strategies for the mid-year quarters to boost sales during these lower-performing periods.
- Analyze Early Year Sales Drop: Research the factors contributing to the lower sales at the start of the year, especially the drop from Jan-Mar 2018 to Jan-Mar 2019. This could inform adjustments in sales strategies or promotions to counteract this trend.
-
Plan for Year-Round Engagement: Implement loyalty programs or marketing campaigns that encourage year-round customer engagement, reducing the reliance on seasonal peaks.
-
Conduct Market Analysis: Perform a deeper market analysis to understand the competitive landscape, especially during the high-performing months, to ensure the retailer remains competitive during these critical periods.
SELECT
`Month_Year`,
`Product_Category`,
SUM(`Units_Sold`) AS Total_Units_Sold
FROM `retail_store.inventory`
GROUP BY `Month_Year`, `Product_Category`
ORDER BY `Month_Year`, `Product_Category`;
-
Overall Growth for Designer Clothes: There is a clear upward trend in sales for Designer Clothes, with significant growth from Apr-Jun 2018 to Oct-Dec 2019, showing the strongest performance in the last quarter.
-
Fluctuations in Fitness Gadgets Sales: Sales for Fitness Gadgets are more erratic. There was a substantial peak in Oct-Dec 2018, which could be attributed to the holiday season or a successful marketing campaign, followed by a notable decrease in Apr-Jun 2019.
-
Stability in Travel Accessories: Travel Accessories show some seasonal fluctuation but maintain relatively stable sales, with Jul-Sep being strong periods, possibly reflecting vacation travel seasons.
-
Yearly Growth: Comparing Oct-Dec 2018 to Oct-Dec 2019, there's significant growth across all categories, especially notable in Designer Clothes and Travel Accessories, indicating an overall positive trend in sales year-over-year.
-
Maximize Holiday Sales: Since all categories show increased sales in the Oct-Dec quarter, a focused strategy on the holiday season can be very effective. Consider bundling products, offering holiday discounts, or increasing marketing efforts during this period.
-
Investigate Fitness Gadgets Fluctuation: Delve into the reasons behind the high sales of Fitness Gadgets in Oct-Dec 2018 and why there was a drop in subsequent quarters. It's important to understand the factors driving such variability to sustain sales growth.
-
Leverage Travel Accessories Stability: With relatively stable sales, especially in the Jul-Sep quarters, marketing efforts for Travel Accessories could be amplified during these months to capitalize on the travel season.
-
Increase Year-Round Visibility for Designer Clothes: Given the overall increase in sales for Designer Clothes, explore opportunities for off-peak promotions to maintain sales momentum throughout the year.
-
Stock Optimization: Align inventory levels with sales trends to ensure supply meets demand, particularly for peak sales periods identified in the trend analysis.
-
Customer Feedback Collection: For quarters with lower sales, particularly for Fitness Gadgets, gather customer feedback to better understand purchasing decisions and refine product offerings.
SELECT
CASE
WHEN `Month_Year` LIKE 'Jan-Mar%' THEN 1
WHEN `Month_Year` LIKE 'Apr-Jun%' THEN 2
WHEN `Month_Year` LIKE 'Jul-Sep%' THEN 3
WHEN `Month_Year` LIKE 'Oct-Dec%' THEN 4
END AS Quarter,
`Product_Category`,
AVG(`Units_Sold`) AS Average_Quarterly_Sales
FROM `retail_store.inventory`
GROUP BY Quarter, `Product_Category`
ORDER BY Quarter;
-
Quarter 1 (Jan-Mar) shows lower average sales for all categories. Fitness Gadgets, despite being the highest among them, still have relatively low sales, indicating a post-holiday dip.
-
Quarter 2 (Apr-Jun) sees a moderate increase in sales, suggesting a gradual rise in consumer activity, particularly for Designer Clothes and Fitness Gadgets.
-
Quarter 3 (Jul-Sep) marks a significant boost for Designer Clothes and Travel Accessories, potentially driven by summer travel.
-
Quarter 4 (Oct-Dec) experiences the highest sales, especially for Fitness Gadgets, indicating a strong holiday purchasing trend across all product categories.
-
Prepare for the holiday surge by stocking up on Fitness Gadgets and Designer Clothes in advance of the fourth quarter.
-
Take advantage of the third quarter travel season by ensuring Travel Accessories are well-stocked.
-
Counteract the first quarter sales slump with special promotions and discounts for all categories.
-
Use the quiet first quarter to engage in customer feedback collection to inform product improvements and inventory decisions for the upcoming quarters.
-
Leverage the seasonal sales data to refine inventory forecasting and demand planning models.
-
Consider cross-promotion opportunities, especially between Designer Clothes and Fitness Gadgets during the high sales period in the fourth quarter, to maximize revenue.
SELECT
`Targeted_Campaign`,
AVG(`Units_Sold`) AS Average_Units_Sold
FROM `retail_store.inventory`
GROUP BY `Targeted_Campaign`;
- Campaigns seem to have a substantial impact on sales, with average units sold jumping from 2,310 when there's no campaign to 4,983 during campaign periods.
- The data indicates that targeted campaigns nearly double the average sales, highlighting their effectiveness in driving customer purchases.
- Continue to invest in targeted campaigns as they significantly increase sales. Consider increasing the frequency of these campaigns or extending their duration.
- Analyze the return on investment (ROI) for these campaigns to ensure that the additional revenue covers the cost of the campaigns and increases overall profitability.
- Explore which aspects of the campaigns are most effective—such as the marketing channels used, the timing, the messaging, or the offers included—and optimize future campaigns accordingly.
- Consider implementing targeted campaigns during periods of traditionally lower sales to boost performance, such as in the first quarter.
- Conduct a deeper analysis to determine if certain product categories are more responsive to campaigns than others and tailor the approach to each category based on these insights.
SELECT
`Discount`,
AVG(`Units_Sold`) AS Average_Units_Sold
FROM `retail_store.inventory`
GROUP BY `Discount`
ORDER BY `Discount`;
- There is a clear positive correlation between the level of discount offered and the average units sold. As the discount increases from 0% to 30%, average units sold consistently rise.
- No discount (0%) has the lowest average sales at approximately 2,058 units, whereas a 30% discount yields the highest average sales at 6,510 units.
- The data suggests a strong incentive effect of discounts on consumer purchasing behavior, with higher discounts leading to significantly increased sales.
- Leverage discounts strategically to boost sales, especially when trying to move excess inventory or during traditionally slow sales periods.
- Gradually increase discount levels to find the optimal balance between increased sales volume and profit margins, as deeper discounts might not always lead to proportionally higher profits due to reduced margins.
- Conduct a break-even analysis to understand the discount level at which increased sales compensate for the reduced price, ensuring profitability is not adversely affected.
- Consider testing different discount strategies for various product categories to determine if some are more price-sensitive than others.
- Monitor competitor discounting strategies to ensure the retailer remains competitive without engaging in a price war that could erode profitability.
SELECT
`Defects_Reported`,
`Customer_Ratings`,
LAG(`Units_Sold`, 1) OVER (ORDER BY `Month_Year`) AS Previous_Month_Sales,
`Units_Sold` AS Current_Month_Sales
FROM `retail_store.inventory`
ORDER BY `Month_Year`;
-
There doesn't appear to be a consistent trend between the number of defects reported and customer ratings or sales in the current month. For instance, a high number of defects (32) corresponds to a lower customer rating (7.7) but doesn't result in the lowest sales in the following month.
-
Interestingly, a high customer rating (9.6) is followed by a significant decrease in sales, from 2,175 to 1,275, suggesting that ratings alone may not directly influence sales.
-
In some cases, a higher number of defects doesn't always correlate with a significant drop in sales for the following month; this might indicate that factors other than product quality are influencing purchasing decisions.
-
Analyze customer reviews and feedback to pinpoint the nature of the defects and address specific issues that could be affecting product quality and customer satisfaction.
-
Evaluate the customer service and return policy to ensure they effectively mitigate customer dissatisfaction from defects, as this might explain why sales don't always drop in the face of higher defect reports.
-
Dive deeper into the sales data to understand external factors that might be influencing sales beyond customer ratings and defect reports, such as seasonal trends or competitive actions.
-
Consider implementing additional quality control measures to reduce the incidence of defects, especially in cases where they correlate with lower customer ratings.
-
Enhance customer engagement strategies post-purchase to encourage repeat sales and loyalty, which may help mitigate the impact of defects on future sales.
SELECT
`Product_Category`,
SUM(`Units_Sold`) AS Total_Units_Sold,
AVG(`Customer_Ratings`) AS Average_Customer_Ratings
FROM `retail_store.inventory`
GROUP BY `Product_Category`
ORDER BY Total_Units_Sold DESC;
-
Fitness Gadgets Lead in Sales: Fitness Gadgets have the highest total sales with 35,585 units sold, which may reflect a strong market demand or successful sales strategies for this category.
-
Close Customer Rating Across Categories: All three product categories have very close average customer ratings, with Travel Accessories slightly leading at 8.85, followed by Designer Clothes and Fitness Gadgets. This suggests that customer satisfaction levels are relatively consistent across categories.
-
Sales vs. Satisfaction: Despite the slightly lower customer rating for Fitness Gadgets, their sales are significantly higher than the other categories, indicating that other factors like utility, marketing, or trends may be influencing purchase decisions more than customer ratings.
-
Capitalize on Fitness Gadgets' Popularity: Considering their strong sales, focus on expanding the range of Fitness Gadgets, allocating more marketing budget, and exploring cross-selling opportunities with other categories.
-
Enhance Travel Accessories Features: Given that Travel Accessories have the highest customer ratings but lower total sales than Fitness Gadgets, investigate additional features or services that could increase their appeal and sales, such as bundling with travel-related offers.
-
Review Designer Clothes Positioning: For Designer Clothes, which have the lowest sales but high customer ratings, consider strategies to enhance their market presence. This might include exclusive deals, fashion shows, influencer partnerships, or loyalty programs.
-
Balance Quality and Marketing: As customer ratings are relatively high across the board, ensure that marketing efforts do not overshadow the importance of maintaining product quality, which contributes to customer satisfaction and repeat purchases.
-
Product Differentiation: Seek ways to differentiate products in each category to cater to various customer segments. Tailored marketing strategies that highlight the unique selling points of each category can help increase sales.
SELECT
`Month_Year`,
LAG(`Customer_Ratings`, 1) OVER (ORDER BY `Month_Year`) AS Previous_Ratings,
`Units_Sold` AS Current_Sales
FROM `retail_store.inventory`
ORDER BY `Month_Year`;
-
Variability in Sales: Sales figures show considerable variation across different periods, which do not consistently align with the ratings from the previous period. For example, even with a high previous rating of 9.6, sales can drop, as seen in one period where sales went from 2175 to 1275.
-
High Ratings Not Always Leading to High Sales: High customer ratings don't necessarily predict an increase in sales in the subsequent period. This could suggest that while satisfaction is high, other factors may play more significant roles in driving sales.
-
Some Positive Indicators: Certain periods show a correlation between high customer ratings and increased sales, such as a rise from 1275 to 4250 units with an increase in rating to 9.3, which could indicate positive word of mouth or repeat purchases.
-
Explore Beyond Ratings: While maintaining high customer satisfaction is important, explore other sales drivers such as marketing efforts, seasonal demand, pricing strategies, and product availability that may have a more direct impact on sales.
-
Leverage Positive Ratings in Marketing: Use periods of high customer satisfaction in marketing campaigns to build brand reputation and trust, which could positively influence sales over time.
-
Deep Dive into Customer Feedback: Analyze customer feedback for more nuanced insights that can inform product improvements, customer service enhancements, and tailored marketing messages.
-
Monitor Competitive Landscape: Keep an eye on competitors and market trends to ensure that the offerings remain compelling, regardless of the high customer ratings, to convert satisfaction into sales.
-
Strengthen Customer Engagement: Engage customers with high satisfaction ratings through loyalty programs and targeted promotions to encourage repeat purchases and referrals, which may not be immediately reflected in sales but can contribute to long-term growth.
SELECT
A.`Month_Year`,
A.`Product_Category` AS Category_A,
B.`Product_Category` AS Category_B,
A.`Units_Sold` AS Sales_A,
B.`Units_Sold` AS Sales_B
FROM `retail_store.inventory` A
INNER JOIN `retail_store.inventory` B
ON A.`Month_Year` = B.`Month_Year` AND A.`Product_Category` < B.`Product_Category`
ORDER BY A.`Month_Year`, A.`Product_Category`;
-
The data shows pairings of different product categories and their respective sales figures, giving insight into potential cross-selling opportunities.
-
Strong Sales in Pairings: Some product pairings show strong sales for both categories in the same period, like Fitness Gadgets and Designer Clothes in Jan-Mar 2018, suggesting customers might be interested in purchasing items from both categories together.
-
Varied Performance: Other pairings show contrasting sales performance, indicating that success in one category doesn't necessarily lift sales in another. For instance, while Designer Clothes sales are relatively lower, Fitness Gadgets show strong sales in the same periods, highlighting differing consumer demand patterns.
-
Seasonal Influence: There is a noticeable trend where Travel Accessories sales increase significantly in the Jul-Sep quarter, potentially aligning with vacation periods where consumers might also be interested in new clothing, like Designer Clothes.
-
Bundle Products: For categories that perform well together, create bundled offers that incentivize customers to purchase from both categories at the same time.
-
Marketing Campaigns: Run targeted marketing campaigns that highlight the complementary nature of certain products, like fitness gadgets with fitness apparel or travel accessories with travel clothing.
-
Customer Segmentation: Use sales data to segment customers based on their purchasing patterns and tailor special cross-promotion deals to those segments.
-
Incentives for Multiple Purchases: Offer discounts or loyalty points to customers who purchase from multiple categories within a single transaction to encourage cross-category sales.
-
Seasonal Promotions: Capitalize on the increased sales of Travel Accessories in Jul-Sep by pairing them with other categories in promotions during that period.
SELECT
`Month_Year`,
`Product_Category`,
SUM(`Units_Sold`) / COUNT(DISTINCT `Month_Year`) AS Inventory_Turnover_Ratio
FROM `retail_store.inventory`
GROUP BY `Month_Year`, `Product_Category`;
-
Varied Turnover Rates: The turnover rates vary significantly between product categories and across different quarters. For example, Fitness Gadgets show a high turnover in the Jul-Sep 2019 quarter, which is indicative of either strong sales or lower inventory levels.
-
Seasonal Effects on Turnover: There appears to be a seasonal effect on inventory turnover, with certain periods like Jan-Mar for Fitness Gadgets and Jul-Sep for Designer Clothes showing higher turnover rates. This suggests that sales may be influenced by seasonal demand.
-
Comparative Performance: When comparing inventory turnover ratios year-over-year for the same quarters, there is an indication of performance changes. For instance, Designer Clothes see an increase in turnover from 1275 in Jan-Mar 2018 to 3100 in Jul-Sep 2019.
-
Inventory Management: Use turnover ratios to refine inventory management strategies. Higher turnover rates may indicate a need to stock more inventory to avoid stockouts, especially during peak seasons.
-
Supply Chain Adjustments: For product categories with fluctuating turnover ratios, consider more flexible supply chain solutions that can quickly respond to changing demand levels.
-
Strategic Purchasing: Time purchasing to optimize inventory levels based on historical turnover rates, preparing for periods of high demand to maximize sales and minimize holding costs.
-
Performance Review: Investigate periods of low turnover to identify potential issues such as overstocking or waning demand, and consider promotions or discounts to move excess inventory.
-
Forecasting Improvements: Incorporate turnover ratio trends into sales forecasting models to improve the accuracy of future inventory needs.
This analysis is conducted based on data provided to us up to the point of this analysis. The insights, conclusions, and recommendations herein reflect our interpretation of this dataset and do not necessarily represent the views, strategies, or operational status of the company or entity involved. We have relied on the data's accuracy as provided, without independent verification. Future analyses may lead to different insights as new data becomes available or if business conditions evolve.
- Data Privacy and Confidentiality: This analysis respects privacy and confidentiality norms and complies with applicable data protection laws, including, but not limited to, the handling of personally identifiable information (PII). No PII has been disclosed in this report.
- Limitations and Assumptions: This report makes several assumptions for analytical purposes, and its findings are subject to these assumptions' validity. The analysis may exclude certain data segments due to availability, relevance, or other factors deemed appropriate by the analyst. We acknowledge potential biases in data collection and analysis methods and have strived to minimize their impact on our conclusions.
- Intended Audience: This report is intended for internal stakeholders and strategic decision-making purposes. It should not be considered as financial or legal advice, nor should it be used as the sole basis for any investment decisions.
- Use of External Data or Sources: Where external data sources or analytical tools have been employed, they are cited accordingly. The inclusion of such sources does not imply endorsement or their accuracy by us. Users are encouraged to consult the original sources for further information.
- Revision Policy: Given the dynamic nature of data and business environments, this analysis is subject to revisions. Updates or corrections may be issued as new information becomes available or in response to feedback from stakeholders.
- Non-Endorsement: The mention of any software, platforms, or tools used in the analysis does not constitute an endorsement. These are selected based on their utility for the purposes of this analysis only.
- Use at Own Risk: The insights and recommendations provided are based on the data and assumptions noted. Stakeholders are encouraged to use this information responsibly and in conjunction with other sources of information, acknowledging the inherent risks and uncertainties in data-driven decision-making.