1. Analyzing ecommerce data
Hello, and welcome to this case study. My name is Iason, and I'll be your instructor.
2. What is a case study?
So, what is a case study? A case study allows you to apply your skills. You won't learn any new concepts, but you'll have to combine the skills you've learned to solve a real-world problem.
We recommend you taking some of these prerequisite courses before completing this case study.
3. Case study goals
In this case study, you'll work with a fictitious dataset from an online pet supply company called Whiskique. You have been hired as a new analyst to uncover insights into the company's overall performance and possible improvement opportunities.
By the end of this course, you will successfully analyze an ecommerce dataset and leverage your Power BI skills to create engaging dashboard-style pages full of insights.
Firstly, we will examine some important terms and operations involved in ecommerce businesses.
4. Ecommerce operations and terminologies
Let's look at an illustrative example of the total costs for canned pet food through various stages in the supply chain.
In this example, for a $20 purchase of canned pet food, $4 goes to the manufacturer. This is also called the cost of goods sold or COGS.
one dollar and 50 cents is spent on freight costs and fulfillment fees. This includes the cost to ship a product from the manufacturer to the warehouse and the warehouse storage fees. Last-mile delivery to customers costs $5. This leaves a profit markup of $8 before administrative costs.
5. Whiskique's business goals
Whiskique pet supply has two primary business goals. The first is to serve as many customers as possible and increase sales. The second is to reduce its operating expenses.
Through the analysis work you will carry out for Whiskique, you will advise on increasing sales and reducing expenses. Let us begin with the opportunities for sales growth.
6. Increase sales
For sales growth, you will focus on upsell and cross-sell opportunities. In a cross-sell, you promote a relevant product at the point of purchase. For example, when a customer is ready to buy a beach umbrella, present an offer on beach chairs. In an upsell, you promote a higher-priced alternative or a higher quantity of a product. For example, when a customer buys pet food regularly, present an offer on organic food.
7. Shipping cost reduction strategies
For shipping cost reductions, we can explore some of the following alternatives.
Consolidating multiple shipments into a single one. Reducing package size dimensions and weight. And, finally, shipping a higher quantity of a product.
8. Market basket analysis
A market basket analysis aims to find products frequently bought together in a single checkout. One way this is can be achieved is by determining the correlation coefficient between purchased quantities. This course will not cover this for Power BI, as it requires more advanced DAX formulas. However, you can identify which products are more regularly purchased with specific items.
9. The data source
We will be using a fictitious data source for product sales. The data is located in four CSV files. The sales table is the primary transaction table. The product and customer tables contain additional attributes. The state mapping has consistent values for locations. You will be creating relationships between the tables during your data preparation steps.
10. Let's practice!
With the basic information covered, you are now ready to analyze the data and help optimize Whiskique's business operations. Good luck!