Online Retail Business

*Github repository with SQL queries available here.

*Dashboard here.

The dataset contains the orders placed online at a retail business. It represents the transactions of every order being made. A retail business involves the sales of goods and services to consumers. They purchase these goods and services directly from manufacturers or through wholesalers and then sell them to consumers in small quantities to gain profit.

This project aimed to dive deep into the dataset to bring insights to make strategic business decisions.

Outline

• Design

• Findings

• Conclusion

• Recommendations

Design

SSIS Package was used to create two conditional splits to transform the dataset as seen below:

Why SSIS Package? Usually, Excel is my go-to tool to clean any dataset. But for this dataset, I had to move cleaning to SSIS because it was too large for Excel to handle.

SQL Server Management Studio was then used to access the database, retrieve, manipulate and derive insights from the data. Power BI was then used to visualize.

Findings

The top five countries by revenue?

The United Kingdom, Netherland, Ireland, Germany, and France are the top five countries by revenue, respectively. From the size of the bubbles, we can tell that out of the top five, the United Kingdom tops. With a total of above $9M. This is because most orders were made in the United Kingdom. And also because individuals in the United Kingdom purchased most of the higher-priced products.

What is the relationship between quantity and revenue?

Revenue and quantity have a positive correlation, i.e., as quantity increases, revenue increases, and vice versa.

In 2010, transactions were given for only December, whose revenue amounted to above $826,000. The above chart shows the quantity demanded with the respective revenue for each month in 2011, amounting to $10M. And $11M as the total revenue for both years.

In the 3rd quarter of 2011(November), there was a spike in the number of goods demanded, consequently increasing revenue. From September through November, an increase in revenue was experienced. This increase is due to the rise in orders in the three months, especially November, as can be seen in the table below:

Conclusion

The higher the quantity, the higher the revenue and vice versa. Most purchases were made in the United Kingdom, having the highest orders and income. In 2011, the highest income was recorded in November, with a fall in December. The increased revenue recorded was due to the increase in goods purchased(Number of orders), especially in the United Kingdom.

Could this be a result of the festive season? Yes, you hit the nail on the head. After analyzing the dataset to figure out the sudden spike in November, most products were found to be purchased in preparation for Christmas.

Therefore, this project concludes that the United Kingdom contributed positively to the business revenue. And after each festive period, demand for products will decrease. Perhaps because a lot of money would have been spent in the previous months and there will be little or no need to get any product hence the decrease in revenue.

Recommendations

With the outcome in November, we can conclude that revenue will increase with a conscious effort toward festive seasons. Hence, all festive seasons should be duly noted. Appropriate products for each season should be made available, and efforts should be put to create awareness that showcases the products available for various occasions.

What would people buy for Halloween, Thanksgiving, Valentine's Day, New Year, and other festive seasons? Do the research and have them ready. By all means, have a long list and showcase these products by creating awareness.

Prepare early and always have a good business plan for these seasons, having your target audience in mind. Preparing early ensures that people looking for presents for these seasons will be able to see what you have to offer.