Tata Analysis Report
In the preceding article, I provided a thorough explanation of the data wrangling process undertaken for this report. Click here to read. The CEO and CMO want to see trends and how money is coming in from different countries and products. This will help them plan how to grow the business. They want to look at charts and graphs to get a clear picture of what's happening and what's driving sales on the online store.
Outline
Design
When preparing and loading the data, I utilized SSIS and SSMS. Initially, I attempted to use bulk insert in SSMS, but encountered issues with data type conversion. To address this, I moved cleaning to SSIS. Within SSIS, I established a data pipeline with three conditional splits to remove null and incorrect rows. The cleaned data was then directed to the SSMS-created database using the OLE DB Destination.
image 1: Data pipeline with three conditional splits
Findings
Question 1 The CEO of the retail store is interested to view the time series of the revenue data for the year 2011 only. He would like to view granular data by looking into revenue for each month. The CEO is interested in viewing the seasonal trends and wants to dig deeper into why these trends occur. This analysis will be helpful for the CEO to forecast for the next year.
A sudden surge in sales was noted from July to November, but November witnessed a significant revenue decline. Further analysis revealed that this sales increase during these months was driven by the anticipation of school reopenings and the approaching festive season. These specific products, appearing in the top 20 rows of our data, were purchased more than 800 times each, underscoring their popularity.
Question 2 The CMO is interested in viewing the top 10 countries which are generating the highest revenue. Additionally, the CMO is also interested in viewing the quantity sold along with the revenue generated. The CMO does not want to have the United Kingdom in this visual.
In the chart above, outlining revenue from top-performing countries excluding the United Kingdom, the Netherlands ranks highest, generating revenue exceeding 285,446.34 from 200,937 purchases. Notably, a trend is observed where countries with greater purchase quantities generally yield higher revenue, except in the case of Sweden. Despite Sweden's higher demand compared to Spain, Switzerland, and Belgium, its revenue remains relatively lower.
Question 3 The CMO of the online retail store wants to view the information on the top 10 customers by revenue. He is interested in a visual that shows the greatest revenue generating customer at the start and gradually declines to the lower revenue generating customers. The CMO wants to target the higher revenue generating customers and ensure that they remain satisfied with their products.
The chart showcases the top ten customers and their individual revenue contributions. Although the usual expectation is that revenue aligns with the quantity of products bought, we observed exceptions. Some customers, despite purchasing fewer items, significantly impact revenue, hinting at the acquisition of higher-priced products.
Question 4 The CEO is looking to gain insights on the demand for their products. He wants to look at all countries and see which regions have the greatest demand for their products. Once the CEO gets an idea of the regions that have high demand, he will initiate an expansion strategy which will allow the company to target these areas and generate more business from these regions. He wants to view the entire data on a single view without the need to scroll or hover over the data points to identify the demand. There is no need to show data for the United Kingdom as the CEO is more interested in viewing the countries that have expansion opportunities.
Among the 35 countries, Six regions, namely the Netherlands, EIRE, Germany, France, Australia, and Sweden, emerged as significant contributors to the sales surge, showcasing substantial demand within these areas.
Conclusion
CMO
The Netherlands leads in revenue, aligning with purchase quantity trends. However, Sweden, with higher demand than Spain, Switzerland, and Belgium, generates comparatively less revenue, hinting at expensive purchases. While purchase quantity matters, certain customers opting for expensive products significantly impact revenue. This shows that purchase quantity isn't the sole factor determining revenue. This emphasizes the role of product pricing and customer choices in revenue generation, challenging a straightforward correlation between purchase quantity and revenue. Understanding this relationship is crucial for strategic pricing and market targeting.
CEO
Between July and November, there was a substantial surge in product demand, notably seen in the top 20 products, each purchased over 800 times. These purchases were primarily in preparation for school reopening and the upcoming festive season. Additionally, out of the 35 countries, only six regions—Netherlands, EIRE, Germany, France, Australia, and Sweden—exhibited noteworthy demand.
Recommendation
CMO
Now that we have pinpointed the high-revenue-generating customers, we can create customized marketing strategies and personalized campaigns catered to this segment. Active engagement and communication, coupled with a robust feedback loop, will gather valuable insights for refining products/services, fostering long-term satisfaction, and promoting consistent purchasing behavior.
CEO
The surge in product demand from July to November is attributed to school reopening and the approaching festive season, presenting a clear seasonal pattern to leverage. Focusing on the six regions with notable demand is a prudent move for expansion, aiming to enhance the company's business prospects. Tailoring product offerings and marketing campaigns to align with the specific needs of these regions will be a key aspect of this strategy, ultimately boosting revenue and market presence.
Comments