A guided Market Data Analysis case study in Power BI
Today’s world is the Era of Digital Marketing. Nowadays, every retail chain, eCommerce companies, small and middle-sized stores are in online business and everyone is doing their campaigns in different ways.
In this blog, we will do data analysis step by step with marketing data using Power BI
Table of Contents
1. Data Set
3. Learning Objective
4. Import Data
5. Data Processing
6. Data Visualization
As before, this time also, I have chosen Marketing Data from Kaggle.Marketing AnalyticsPractice Exploratory and Statistical Analysis with Marketing Datawww.kaggle.com
The data set
marketing_data.csv consists of 2,240 customers with 28 variables.
The variables provide insights about:
- Customer profiles
- Product preferences
- Campaign successes/failures
- Channel performance
Using our online shopping knowledge, we can very well categorize these variables from this table.
The primary objectives of this case study are following
Functional: To analyze the data and understand why marketing campaigns are not effective as expected and provide data-driven solutions.
Technical: Extensive Data Processing
- The null values imputation method
- Outlier treatment,
- Locale Change type for a date field
- Calculated variables using Unpivot Column
- Let’s start with the Get Data option under the Home tab. As this is a CSV file, select the Text/CSV option from the drop-down list
- Select the file named marketing_data.csv
- After selecting the file, data will be displayed, and click on Load Data.
Go to Power Query Editor and perform the following tasks
By default, most of the columns are not in the right data type format. At first, change the data type for the above columns.
Change Type for Date Field
By default, date values are in MM/DD/YY format. To change DD/MM/YYYY format, we need to use locale and follow these steps in the image.
Click on Close&Apply under the Home tab and come out from Power Query Editor.
In the main Power BI editor, change the Summarization property to “Don’t summarize” for the fields ID and Year_Birth.
Looking for Null Values
Go to Power Query Editor and check the box for Column Quality and Column Distribution under the View tab.
We can there are some null values for the Income column and the count of records is 24. Now we will use the median of Income values and replace null values with this. This imputation method will help to avoid any error due to outlier values.
After observing the data for the Year_Birth column, we can notice there two rows 1893 and 1899. Other records are for above 1900. So to achieve successful outlier treatment, we will filter out these two records.
Using Unpivot column feature in Power Query Editor, we will make 4 columns instead of 11 columns
- Product Type and Amount Spent by Product Type
Using Replace Values features, we can rename Product Type values with meaningful names.
2. Purchasing Type and No. of Purchases
Using Replace Values features, we can rename Purchasing Type values with meaningful names.
3. Campaign and Campaign Response
Now the data is ready for visualization.
In this section, we will find the answers to some business questions.
- Most Successful Marketing Campaign (Visual — Campaign Success Rate)
- Product in Demand (Visual — Average Product Performance)
- Underperformance Purchasing Channel (Visual — Purchasing Channel Demand)
- Country-wise Analysis ( Visual — Total Campaign Response Rate by Country, Amount Spent by Count, Total Purchases by Country)
Please find the code in the following location
In this guided project we learned about the following things.
- Analyzing Marketing data
- Extensive Data Processing Techniques in Power Bi
- A prototype of Marketing Analytics reports
If you have any questions related to this project, please feel free to post your comments.