Case study link: https://app.datacamp.com/learn/courses/case-study-ecommerce-analysis-in-power-bi

Date created: Oct 31, 2023

Project overview:

"In this Power BI case study, you’ll explore a real-world ecommerce business dataset to uncover insights for an online pet supply company called Whiskique. Using what you’ve learned from previous courses; you’ll use Power Query to investigate and clean the data to find out how the company’s sales are performing and where their customers are located. You’ll then use DAX to build insightful visualizations of your findings. Finally, you’ll bring it all together using everything Power BI has to offer to create dashboard-style pages so that you can answer questions for the Whiskique executive team."

I have created 3 main dashboards in this course I will go in depth for each dashboard, but first I am going to show the preparation stage:

Data extraction:

Data was loaded from 4 CSV files:

• Customers.csv
• Sales.csv
• Products.csv
• State Mapping.csv

Date cleaning:

• Null values were removed from Invoice No.
• Some values' names were changed to be shorter.

Tables, measures, and KPIs created:

1. New Measures table was created to hold some measures we will be creating.

2. Number of Customers measure: counts unique customers from the Sales table:

Number of Customers = DISTINCTCOUNTNOBLANK(Sales[Customer ID])

3. Lifetime Value (LTV): the total value of all sales by a customer:

Customer LTV (avg) = SUM(Sales[Sales]) / 'New Measures'[Number of Customers]

4. Invoice Total table, which is a Sales table duplicate created to group by Invoice No and create 2 new columns of it: Total Sales and Total Quantity grouped by Invoice No.

5. Market Basket table, which is a Sales table duplicate created to be able to view the products bought together.

6. Shipping Cost column: taken from another table which holds shipping cost data:

Shipping Cost = RELATED(Products[Shipping_Cost_1000_mile])

7. Shipping (Baseline) measure: calculates the shipping for more than one product, for example if 1 product is shipped by itself the shipping cost will be the same as Shipping Cost, but if 4 products shipped together the first product will be 100% of the shipping and the other 3 will cost 70% each of the shipping cost:

Shipping (Baseline) = SUMX(Sales, IF(Sales[Quantity]=1, Sales[Shipping Cost], Sales[Shipping Cost] + ((Sales[Quantity]-1)*(Sales[Shipping Cost]*0.7))))

8. What-if Quantity parameter: to calculate the shipping costs and savings when 1-20 products shipped together:

9. Blended Shipping Cost Factor measure: created under the What-if Quantity parameter which calculates the discounted shipping cost based on the following table:

Blended Shipping Cost Factor = IF('What-if Quantity'[What-if Quantity Value]<=1,1, IF('What-if Quantity'[What-if Quantity Value]<=2, 0.8, IF('What-if Quantity'[What-if Quantity Value]<=4, 0.6, IF('What-if Quantity'[What-if Quantity Value]<=7, 0.5, IF('What-if Quantity'[What-if Quantity Value]<=9, 0.4, 0.3)))))

10. Shipping (What-if) measure: same as Shipping (Baseline) but uses the Blended Shipping Cost Factor instead of a fixed 0.7 value:

Shipping (What-if) = SUMX(Sales, IF(Sales[Quantity]=1, Sales[Shipping Cost], Sales[Shipping Cost]+ ((Sales[Quantity]-1)*(Sales[Shipping Cost]*[Blended Shipping Cost Factor]))))

11. Shipping Difference measure: calculates shipping difference (savings):

Shipping Difference = [Shipping (Baseline)] - [Shipping (What-if)]

12. Baseline Running Total measure:

Baseline Running Total = SUMX(FILTER(ALLSELECTED(Sales), Sales[Transaction Date]<=MAX('Market Basket'[Transaction Date])), [Shipping (Baseline)])

13. What-if Running Total measure:

What-if Running Total = SUMX(FILTER(ALLSELECTED(Sales), Sales[Transaction Date]<=MAX('Market Basket'[Transaction Date])), [Shipping (What-if)])

14. Difference Running Total measure:

Difference Running Total = SUMX(FILTER(ALLSELECTED(Sales), Sales[Transaction Date]<=MAX('Market Basket'[Transaction Date])), [Shipping Difference])

15. COGS (cost of goods sold) column: multiplies Landed Cost with Quantity:

COGS = Sales[Quantity]*RELATED(Products[Landed Cost])

16. Profit (Baseline) column: subtracts sale price from COGS and shipping cost:

Profit (Baseline) = Sales[Sales] - Sales[COGS] - [Shipping (Baseline)]

17. Profit % measure: divides profit by sales to get the profit margin:

Profit % = SUM(Sales[Profit (Baseline)]) / SUM(Sales[Sales])

Data modeling:

Now the data preparation is finished, let's dive into dashboards, as mentioned before I created 3 dashboards:

Dashboards:

1. Executive Summary:

Goal: visualize important KPIs and visuals to be quickly understood by executives, like the profit margin, top selling products, profit by category.. etc:

2. Shipping Metrics:

Goal: view the shipping costs and what-if analysis to reduce the shipping costs and increase profits.

3. Market Basket Analysis:

Goal: analyze which products should be recommended to customers for cross-selling, the user can highlight a product to view which other products are bought with the selected product.

Other visuals created which were not include in the main dashboards:

Results:

Cross-selling and upselling can be easier now, with 1 click you can view the frequently bought together products.

Strategies to reduce shipping costs and increase profits can be viewed clearly in the Shipping Metrics dashboard, just choose the number of products to ship together and you will get a breakdown on costs, savings, and running costs and savings.

Important KPIs are viewed in 1 interactive dashboard which gives a breakdown on costs, sales, and profits.

Done.

Thanks for reading!