Data Analysis in Power BI: How to Build Marketing Campaign Analytics Report

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

Image for post
Image by Unsplash

Table of Contents

1. Data Set

2. Abstraction

3. Learning Objective

4. Import Data

5. Data Processing

6. Data Visualization

7. Conclusion

Data Set

As before, this time also, I have chosen Marketing Data from Kaggle.Marketing AnalyticsPractice Exploratory and Statistical Analysis with Marketing Datawww.kaggle.com

Abstraction

The data set marketing_data.csv consists of 2,240 customers with 28 variables.

Image for post
Image by Author

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.

Learning Objective

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

  1. The null values imputation method
  2. Outlier treatment,
  3. Locale Change type for a date field
  4. Calculated variables using Unpivot Column

Import Data

  • 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.
Image for post
Image by Author

Data Processing

Go to Power Query Editor and perform the following tasks

Change Type

Image for post
Image by Author

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

Image for post
Image by Author

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.

Image for post
Image by Author

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.

Outlier Treatment

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.

Unpivot Columns

Using Unpivot column feature in Power Query Editor, we will make 4 columns instead of 11 columns

  1. Product Type and Amount Spent by Product Type
Image for post
Image By Author

Using Replace Values features, we can rename Product Type values with meaningful names.

2. Purchasing Type and No. of Purchases

Image for post
Image bu Author

Using Replace Values features, we can rename Purchasing Type values with meaningful names.

3. Campaign and Campaign Response

Image for post
Image by Author

Now the data is ready for visualization.

Data Visualization

In this section, we will find the answers to some business questions.

  1. Most Successful Marketing Campaign (Visual — Campaign Success Rate)
  2. Product in Demand (Visual — Average Product Performance)
  3. Underperformance Purchasing Channel (Visual — Purchasing Channel Demand)
  4. Country-wise Analysis ( Visual — Total Campaign Response Rate by Country, Amount Spent by Count, Total Purchases by Country)
Image for post
Image by Author

Download Code

Please find the code in the following location

https://github.com/arpitag1/Power-BI

Conclusion

In this guided project we learned about the following things.

  1. Analyzing Marketing data
  2. Extensive Data Processing Techniques in Power Bi
  3. A prototype of Marketing Analytics reports

If you have any questions related to this project, please feel free to post your comments.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: