In this blog post, I want to share how I analyzed the data that is shared by Maven Analytics for mavenmarketingchallenge.
As always, it is so fun to see and learn data that are shared by Maven Analytics Team.
The given situation and task are briefly described below.
Situation: The latest marketing campaigns have underperformed.
Task: Pitch #1 recommendation to improve the impact of future marketing campaigns.
Before I start to explain how I see the data, I want to share how I imported the data set from the link (https://maven-datasets.s3.amazonaws.com/Marketing+Campaigns/Marketing+Data.zip).
The download-link is shared in the blog post (https://www.mavenanalytics.io/blog/maven-marketing-challenge), and the file is a zip file.
In the zip file, there are two CSV files. One is the data file, and the other is the column-header explanation file (dictionary file).
Instead of downloading the zip file, unzip, and import to Power BI, I did the below process. One of the reasons I did get data from WEB is to easily share my pbix file with people. In this case, changing Data Source Settings is not necessary, and as a result, source tables are shown in Power Query Editor without having any additional steps.
Step 1.
Copy the below code (red color).
I learned it from the link below.
In Power Query Editor, open Advanced Editor in Blank Query, and paste the code.
Name the fx query as “unzip”
(ZIPFile) =>
let
Header = BinaryFormat.Record([
MiscHeader = BinaryFormat.Binary(14),
BinarySize = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
FileSize = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
FileNameLen= BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian),
ExtrasLen = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian)
]),
HeaderChoice = BinaryFormat.Choice(
BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
each if _ <> 67324752 // not the IsValid number? then return a dummy formatter
then BinaryFormat.Record([IsValid = false, Filename=null, Content=null])
else BinaryFormat.Choice(
BinaryFormat.Binary(26), // Header payload - 14+4+4+2+2
each BinaryFormat.Record([
IsValid = true,
Filename = BinaryFormat.Text(Header(_)[FileNameLen]),
Extras = BinaryFormat.Text(Header(_)[ExtrasLen]),
Content = BinaryFormat.Transform(
BinaryFormat.Binary(Header(_)[BinarySize]),
(x) => try Binary.Buffer(Binary.Decompress(x, Compression.Deflate)) otherwise null
)
]),
type binary // enable streaming
)
),
ZipFormat = BinaryFormat.List(HeaderChoice, each _[IsValid] = true),
Entries = List.Transform(
List.RemoveLastN( ZipFormat(ZIPFile), 1),
(e) => [FileName = e[Filename], Content = e[Content] ]
)
in
Table.FromRecords(Entries)
Step 2.
Copy the download link, that contains a zip file.
In Power Query Editor, open New Source -> Web. And paste the link.
Open Advanced Editor, and type in the below code or amend it as below (red color + blue color).
let
Source = Web.Contents("https://maven-datasets.s3.amazonaws.com/Marketing+Campaigns/Marketing+Data.zip"),
Unzip = unzip (Source),
In Unzip
Step 3.
You will easily know what to do next.
Please be sure that you checked the structure of each file in the zip file, if the ZIP contains more than one file. For instance, there are two differently-structured CSV files in the zip file when you open the above-mentioned file (Maven Marketing Challenge).
Now, I will share what is my #1 recommendation to improve the impact of future marketing campaigns for the Maven Marketing Team.
The situation is that the latest marketing campaigns have underperformed, and I analyze the data and propose a solution.
There are 2,240 customers.
Campaign 1 ~5, and the last campaign were held.
In campaign 2, customers who selected “No” were 98.7% among all customers, and this campaign showed the worst result from a customer-count perspective. These customers’ spent amount in the last 2 years was 97.11% among all-spent-amount. Those who were born in the 1970s, whose annual household income is 70K~80K, whose education level is graduation, and who visited the website 3~5 times during last month spent a higher amount than customers in other categories. Most products that these customers bought were Wines and Meat. These customers’ number of purchases was 98.05% among all-number-of-purchased. Those who were born in the 1970s, whose annual household income is 60K~70K, whose education level is graduation, and who visited the website 6~10 times during last month purchased a higher number than customers in other categories. The most used buying channel was Store-Purchase.
In the contents of campaign 2, I assume that some generated dislike-feelings to customers who are in the above categories. I suggest finding & fixing those, then applying them to the next campaign. Keep in mind that the data shows these customers like to buy Wines and Meats at the store.
In the "last campaign", customers who selected “Yes” were 14.9% among all customers, and this campaign shows the best result from a customer-count perspective. These customers’ spent amount in the last 2 years was 24.30% among all-spent-amount. Those who were born in the 1970s, whose annual household income is 80K~90K, whose education level is graduation or Ph.D., and who visited the website less than 10 times during last month spent a higher amount than customers in other categories. Most bought products by these customers were Wines and Meat. These customers’ number of purchases was 18.28% among all-number-of-purchases. Those who were born in the 1970s, whose annual household income is 80K~90K, whose education level is graduation or Ph.D., and who visited the website 6~10 times during last month purchased a higher number than customers in other categories. The most used buying channel was Store-Purchase. One of the interesting parts is that the majority of these customers' recency of purchase is within 39 days, which means they are more frequent buyers.
In the contents of the last campaign, I assume that some generated liking-feelings to customers who are in the above categories. I suggest finding & keeping or developing those, then applying them to the next campaign. Again, keep in mind that the data shows these customers like to buy Wines and Meats at the store.
As a result, in my opinion, the groups of customers that the marketing team has to focus on for the next campaign are,
Born in the 1970s
Annual household income is 60K~90K
Education level is graduation and Ph.D.
Visited the website less than 10 times during last month
Wines, Meat, Store-Purchase, and located in Spain (Spain shows the highest number in any category)
I hope this helps Maven Marketing Team to have an enhanced result in the next campaign.
Comments