A Udacity Power BI project for an online clothing store, View Power Bi Report Here
An online national clothing chain needs your help creating a targeted marketing campaign. Sales have been flat and they want to lure lost customers back. They want to advertise specific products to specific customers in specific locations, but they don’t know who to target. They have three products in mind:
Shirt: $25 Sweater: $100 Leather Bag: $1,000 They need you to conduct an analysis to determine the best product to advertise to each customer.
- What is the correlation (R2 value) between sales and income?
- What is the correlation (R2 value) between customer ratings and product return rate?
- What are the linear regression formulas to predict customer income from customer sales?
- Which customer do you predict has the highest income?
- Which product will be advertised the most?
Average income | location | population | industry
Product inventory | Product prices | Customer rating | Product return rate
Customer ID | Names | Location | Date of birth | Purchase history
- Used Power Query to Split cloumns, Replace Values, Unpivot.
- Created a date table in Power Query using {Number.From(List.Min(TableName[ColumnName]))..Number.From(List.Max(TableName[ColumnName]))}
Purchase List | Product Inventory |
---|---|
A star schema was relationship between the necessary tables
- Linear Regression Formula: X=(-722.14-Y)/0.01
- Created Regretion table
- Created Income Buckets "Image Below"
Income Range | Regression table |
---|---|
The report can be accessed Here
There is a positive correlation value between Sales 0.78 as per the “Average income VS average sales scatter plot”.
There is a strong Negative Correlation with a value of 0.69 between customer ratings and product returns as per “the relationship between return rate and rating” scatter plot.
Linear Regression Formula: X=(-722.14-Y)/0.01
The predicted Customer with the highest income is “Jon Little” As per the Card visual representing the below formula: Highest Cx income = LOOKUPVALUE('Customer List'[Full name],'Customer List'[Predict Cx income],MAX('Customer List'[Predict Cx income]))
- As per the analysis an average of 81% of the client’s income range is low and average as per the “Predicted income range column chart”
- I recommend advertising the most: a $25 spring t-shirt & $100 Cotton sweater according to the season of the year.
- For the $1,000 leather bag it can be advertised in the highest income state as it falls in the luxury category.
- There are low sales in some of the high-income states ex: the District of Columbia, New Jersey, Maryland, Massachusetts, and Hawaii in comparison with California, as per the “state, last 6 months purchase, income” table.
- There is an opportunity to market these products in those states.
- Majority of clients based on the last 6-month purchase are born between 1960-2000 as per the “Last 6-month purchase by age group” donut chart