Decoding Success: Analyzing Marketing Campaign Results

Context:

For this project, I analyzed customer data for five different marketing campaigns for a company that sells products to customers in eight different countries.

My goals were:

• Understand what the average customer looks like
• What are the top products
• Which channels are underperforming?
• Is there a correlation between web purchases and other factors?

If you feel so inclined, you can view the original data set here: data playground

Tools:

• I conducted the analysis and built a dashboard in Google Sheets

Exploring the data:

Before the analysis, I looked through the data dictionary and explored the dataset to understand what each column represented. For example, recency referred to the number of days since the customer's last purchase.

Each customer also has a unique ID and there were records of if they accepted various marketing campaign offers, as well as products purchased. There was also basic demographic information such as marital status, country, number of children, and year of birth.

Cleaning the data:

Right away, I noticed a problem. In the year of birth, some folks had submitted that they were born in the 1893, 1899, and one in 1900. This was just not possible. And while we might naturally assume a person who entered '1893' meant to put '1993,' it's still unclear what someone who entered '1900' meant.

I knew these outliers could skew the data. Since it was only three records (0.1% of the data), I felt comfortable removing them.

Unfortunately, there was little insight to gain from "Education." For responses, people included "graduation", "2n cycle", "PHD," "Basic," and "Master." However, there's no standardization or explanation of what these mean.

For example, what are we defining as a basic education? Does "graduation" mean they completed high school or graduated from university? It's anyone's guess.

Analyzing the data:

I used Google Sheets pivot tables to slice and dice the data.

Additionally, I also used the CORREL function to determine if there was a relationship between various factors and web purchases (products, marital status, campaigns, recency, etc).

To determine the average income, I opted to use median versus average. Salaries can have a wide range. In this example, the dataset had a salary as high as $666,666 and the lowest salary was around $1,700. This could heavily skew an average. Using median, I determined the average customer's income.

I decided to group together "together" and "married" into one demographic. These are customers in a relationship which could include married, a civil union, or simply a long-term romantic partner with no official legal status. I grouped "YOLO" and "alone" (which could be someone being silly or something else undetermined) into "other" in my pivot table.

Creating the dashboard

Finally, I used pivot tables to create charts and design the dashboard. I designed the dashboard with busy marketing managers and CMOs in mind. You can view the dashboards here:

Customer Dashboard

Campaigns and Products Dashboard

Insights and recommendations:

• Most customers come from Spain, with Saudi Arabia in second. It might be helpful to ensure some of our campaign ads are in Spanish and Arabic, if they aren't already.
• Most customers make in-store purchases.
• Additionally, since website purchases are lower than store purchases (which is strange for 2021), is our website translated correctly for Spanish and Arabic-speaking customers? What about keyword optimization in those languages?
• The median customer income is $51,373.
• Wine dominates overall sales, with fruit only making up a small fraction. Is it worth continuing to stock fruit? Perhaps it's going bad by the time it ships across the globe.
• In a victory for this company, very few customers have complaints, less than one percent!
• To get better insights into customers' education status, a drop down menu might be advisable.
• I'd also advise the same for marital status, since some people put "YOLO".
• Most customers are child and teen-free.
• 64% are married or partnered in a relationship.
• Most of the customers were born in the '70s.
• Campaign 4 was the most successful, and campaign 2 underperformed by quite a lot. If I were a marketing analyst or a member of the marketing team, I'd want to review the campaigns to identify key differences that could contribute to strong/low performance.