MARKETING CAMPAIGN ANALYSIS WITH SQL
With strategies like email blasting and social media marketing, businesses aim to engage their target audience effectively. This project seeks to analyse the effectiveness of different marketing campaigns in terms of both return on investment (ROI) and lead generation. The difficulty of pinpointing the most effective marketing strategies with the highest return on investment (ROI).
The problem statement underscores the uncertainty surrounding the identification of marketing initiatives that deliver optimal sales conversions. This uncertainty hampers businesses’ ability to allocate resources effectively and maximize their marketing impact.
In response, the project aims to achieve several objectives:
a) Assess the effectiveness of various marketing campaigns on Facebook by evaluating key performance indicators (KPIs) such as conversion rate (CR), click-through rate (CTR), and cost per click (CPC).
b) Determine which campaign offers the most favourable returns on investment.
c) Provide actionable recommendations for optimizing future marketing campaigns based on the findings.
PROJECT STRATEGY
The data used for this analysis is available for download from Facebook Ad Campaign (kaggle.com). The dataset includes information on three campaigns identified as 916, 936, and 1178. It provides details about the targeted audience, including their age, gender, and interests. Additionally, the dataset contains information on the advertising expenditure, the number of clicks received, impressions (the number of times the ad was displayed), total conversions (people who inquired about the product), and approved conversions (people who purchased the product after seeing the ad).
Data cleaning procedures were conducted in R, resulting in a cleaned CSV file. Subsequently, the cleaned data was imported into a SQL database for analysis. All analyses for this project will be performed using SQL Server Management Studio.
The main steps to this project are:
1. Univariate Analysis
2. Multivariate Analysis
3. Analysis of Key Performance Indicators.
4. Recommendations
1. UNIVARIATE ANALYSIS
I am interested in finding some important information about some individual attributes in the data.
1.1 Top 20 Frequency per Click
The data shows that zero clicks (no clicks) had the highest frequency, indicating that most people do not click on the ad after it is shown to them. Additionally, there’s a notable trend whereas the number of clicks increases, the frequency decreases. This implies that, overall, the ads did not receive a significant number of clicks.
--Top 20 Frequency per Clicks --
USE facebookdata;
SELECT TOP 20 clicks, COUNT(*) AS frequency
FROM [dbo].[fddata]
GROUP BY clicks
ORDER BY frequency DESC;
- 2 Frequency of Individual Campaigns
campaign 936 had the most occurrence in the dataset.
-- Frequency of Individual campaigns
SELECT campaign_id, COUNT(*) AS frequency
FROM [dbo].[fddata]
GROUP BY campaign_id
ORDER BY frequency DESC;
2. MULTIVARIATE ANALYSIS
In this analysis, I will delve deeper into each individual campaign, comparing their performance by gender, and ultimately, their conversions.
2.1 Gender impressions per campaign
This analysis reveals which gender the ads were primarily shown to or had the most impressions for in each of the campaigns.
The ads in Campaigns 916 and 1178 were predominantly shown to males rather than females. However, in Campaign 936, the ads were shown more to females. Based on this, I anticipate that males will make more purchases of the products in Campaigns 916 and 1178 compared to females.
It is also important to note that:
a) The ads in Campaign 916 were displayed 88 thousand more times to males than to females (difference between the impressions for males and females in Campaign 916).
b) The ads in campaign 936 were also shown 4 million times more to females than males.
c) The “NULL” value indicates that no female details were recorded in Campaign 1178, suggesting that all individuals to whom the ad was shown were males.
-- -- Gender Impressions Per Campaign --
SELECT
Gender,
[916] AS Campaign_916,
[936] AS Campaign_936,
[1178] AS Campaign_1178
FROM (
SELECT
gender,
campaign_id,
SUM(impressions) AS impressions_count
FROM [dbo].[fddata]
GROUP BY gender, campaign_id
) AS PivotData
PIVOT (
SUM(impressions_count)
FOR campaign_id IN ([916], [936], [1178])
) AS PivotTable;
2.2 The number of clicks by gender for each campaign
Despite the variation in impressions between the two genders in Campaign 916, males had only 9 clicks more than females. This suggests that if the ads in Campaign 916 were shown to more females, it could potentially have resulted in more clicks.
In Campaign 936, there were more female clicks than males, with a difference of over one thousand. Considering that the ad was shown 4 million times more to females than males, is the difference in clicks a significant? (further examination of the KPIs will provide additional insights on this matter)
-- Sum of clicks by gender for each campaign
SELECT
Gender,
[916] AS Campaign_916,
[936] AS Campaign_936,
[1178] AS Campaign_1178
FROM (
SELECT
Gender,
Campaign_id,
SUM(clicks) AS clicks_count
FROM [dbo].[fddata]
GROUP BY Gender, Campaign_id
) AS PivotData
PIVOT (
SUM(clicks_count)
FOR Campaign_id IN ([916], [936], [1178])
) AS PivotTable
2.3 Total Conversion by gender for each campaign
Total conversion is the total number of people who enquired about the product after they saw the ad for each campaign.
There is something noteworthy in the data regarding Campaign 936. Despite females having over a thousand clicks on the ads, only 302 of them enquired about the product. Conversely, out of the 351 males who clicked on the ads in the same campaign, 235 showed interest and enquired about the product.
This observation suggests that the difference in clicks between the two genders in Campaign 936 is not significant. It is likely primarily due to the fact that the ad was shown more frequently to females, as indicated by the impression table.
-- Sum of total conversion by Campaign Id and Gender --
SELECT
Gender,
[916] AS Campaign_916,
[936] AS Campaign_936,
[1178] AS Campaign_1178
FROM (
SELECT
gender,
campaign_id,
SUM(total_conversion) AS total_conversion_count
FROM [dbo].[fddata]
GROUP BY gender, campaign_id
) AS PivotData
PIVOT (
SUM(total_conversion_count)
FOR campaign_id IN ([916], [936], [1178])
) AS PivotTable;
2.4 Approved Conversion by Gender for each Campaign
Out of the 235 males who enquired about the product in Campaign 936, only 87 ended up purchasing it. Interestingly, a significant portion of males did not maintain the same level of interest after showing initial interest in the product. Similarly, out of the 302 females who enquired about the product, only 96 made a purchase.
In summary, both campaigns experienced low conversions from both genders, particularly when compared to the number of people to whom the ads were shown.
SELECT
Gender,
[916] AS Campaign_916,
[936] AS Campaign_936,
[1178] AS Campaign_1178
FROM (
SELECT
Gender,
Campaign_id,
SUM(approved_conversion) AS Approved_conversion_count
FROM [dbo].[fddata]
GROUP BY Gender, Campaign_id
) AS PivotData
PIVOT (
SUM(Approved_conversion_count)
FOR Campaign_id IN ([916], [936], [1178])
) AS PivotTable;
NOTE
So far, our discussions have focused on the numbers associated with each campaign attribute like impressions, clicks, total conversion, and approved conversion. However, comparing campaigns solely based on these numbers is not fair since they had different levels of exposure. To draw more accurate conclusions about the campaign performance, it is important to delve into the key performance indicators (KPIs), which I have outlined below.
I did not include the multivariate analysis of campaign performance by age groups for brevity, but I did include its analysis for the KPIs, which offers a more comprehensive foundation for assessing campaign performance across different age groups.
3. ANALYSIS OF THE KEY PERFORMANCE INDICATORS (KPIs)
Cost Per Click (CPC) measures the cost efficiency for each click the ad receives. CPC was computed by dividing the spent (amount spent) by clicks(spent/clicks). Click through rate (CTR) shows the ability of the ad to attract or engage with customers. I computed CTR by dividing the number of clicks by the total impressions (CTR=clicks/impressions). Conversion Rate (CR) reflects the conversion ability for each campaign. I computed CR by total conversions by impressions (total conversion/ impressions). These KPIs will help us to properly compare the campaign performances without necessarily counting and comparing figures but rather based on the rates, which is a better format to make comparison without a bias.
3.1 Mean CPC, CTR and CR by Campaign
Campaign 1178 had the highest cost per click (CPC), indicating that the company allocated more funds to it. However, despite the higher investment, its click-through rate (CTR) and conversion rate (CR) were the lowest among the campaigns. This suggests that Campaign 1178 performed poorly in attracting people and converting them into customers. The previously observed high figures for Campaign 1178 were mainly due to its higher impression count resulting from the larger budget. Campaigns 916 and 936 on the other hand, did not show a significant difference in performance based on the KPIs. It is essential to conduct A/B testing to confirm if there is a notable difference between the campaigns. However, it is evident that all three campaigns had a very low mean conversion rate, indicating a lack of substantial interest from the audience in the advertised product.
-- Mean CPC, CTR and CPC by Campaign
SELECT
campaign_id,
ROUND(AVG(CPC),5) AS Mean_CPC,
ROUND(AVG(CTR),5) AS Mean_CTR,
ROUND(AVG(CR),5) AS Mean_CR
FROM
[dbo].[fddata]
GROUP BY
campaign_id;
3.2 Quick Insights on CTR, and CR by Campaign, gender, and age group
Examining Click-Through Rate (CTR) and Conversion Rate (CR) by Campaign, gender, and age group provides valuable insights into engagement and purchasing behaviour across different segments
3.2.1 Mean Click Through Rate by Age and Campaign
Comparing the click-through rates (CTR) of the three campaigns across different age groups reveals interesting insight.
Reading the data in row wise, reveals that, age group:
a) 30–34 and 45–49 engaged more with campaign 916 than any other campaign, with CTR of 0.0015 and 0.00027 respectively.
b) Similarly, 35–39 and 40–44 engaged more with the ads of campaign 936 than any other campaign with CTR of 0.00017 and 0.00022 respectively.
However, one key point to note from the output below is that, comparing all the CTR across the age groups (read the table by columns), age group 45–49 had the highest mean CTR across all the campaigns, implying a higher engagement rate on the ads of the campaign by this age group.
This can tell the company on how to strategize next campaign to ensure more engagement.
-- Mean Click Through Rate(CTR) by Age and Campaign
SELECT
age,
[916] AS Campaign_916,
[936] AS Campaign_936,
[1178] AS Campaign_1178
FROM (
SELECT
age,
Campaign_id,
ROUND(AVG(CTR),5) AS AVG_CTR
FROM [dbo].[fddata]
GROUP BY age, Campaign_id
) AS PivotData
PIVOT (
SUM(AVG_CTR)
FOR Campaign_id IN ([916], [936], [1178])
) AS PivotTable;
3.2.2 Mean Conversion Rate by Age and Campaign
a) Age group 30–34 which engaged more on the ads of campaign 916, had a higher conversion rate in campaign 936 instead.
b) Age groups 35–39 and 40–44 again had the best mean conversion in campaign 936, after they were rated as the ones with the most engagement in this campaign.
c) Age group 45–49 as well had the most mean conversion rate in campaign 916 after they were rated as the age group with most engagement in campaign 916.
-- Mean Conversion Rate(CR) by Age and Campaign
SELECT
age,
[916] AS Campaign_916,
[936] AS Campaign_936,
[1178] AS Campaign_1178
FROM (
SELECT
age,
Campaign_id,
ROUND(AVG(CR),5) AS AVG_CR
FROM [dbo].[fddata]
GROUP BY age, Campaign_id
) AS PivotData
PIVOT (
SUM(AVG_CR)
FOR Campaign_id IN ([916], [936], [1178])
) AS PivotTable;
Insights on the CTR and CR for each campaign by age group:
a) Remarkably, nearly all age groups, with the exception of the 45–49 age group, exhibited the highest purchase rate (mean CR) for Campaign 936 compared to any other campaign. To optimize higher purchase rates in similar campaigns, it would be prudent to concentrate more on targeting this particular age group.
b) Despite displaying higher engagement with the ads in Campaign 916, the mean conversion rate (CR) suggests that the age group 30–34 had a higher purchase rate in Campaign 936
Key information for the company: The disparity between the high click-through rate (CTR) and lower conversion rate (CR) for the age group 30–43 in Campaign 916 suggests a potential loss of interest in the products being advertised. The company should investigate possible reasons behind this discrepancy to understand why despite showing interest by clicking on the ads, this age group did not follow through with purchases. Potential factors to explore could include:
1. Mismatched Expectations: The ads may have promised something that the product ultimately didn’t deliver, leading to disappointment and hesitancy to make a purchase.
2. Product Fit: The product being advertised may not align well with the preferences, needs, or purchasing behaviours of this age group, resulting in a lower conversion rate despite initial interest.
c) The higher mean engagement rate (CTR) and purchase rate (CR) for Campaign 916 among the age group 45–49 indicate a strong interest in the products advertised in this campaign. This suggests that this particular age group is highly receptive to the offerings presented in Campaign 916. The company should take note of this valuable insight and consider tailoring future marketing strategies and product offerings to better cater to the preferences and interests of this age group.
3.2.3 Mean Click Through Rate (CTR) by Gender and Campaign
Reading the table row by row, it is evident that:
a) Female engagement rate with the ads of Campaign 916 surpassed that of any other campaign.
b) Similarly, males also showed higher engagement rate with the ads of Campaign 916 compared to the other two campaigns, with a higher CTR of 0.00017.
c) The “NULL” value for females in Campaign 1178 suggests that no female records were collected for this campaign. This implies that only males engaged with the ads of Campaign 1178 and subsequently made purchases, hence the absence of data for females.
When examining individual campaigns (reading the table column by column):
a) Females exhibited a higher engagement rate (higher CTR) than males in both Campaign 916 and Campaign 936.
b) Conversely, only males engaged with the ads of Campaign 1178, as indicated by the absence of female engagement data (NULL).
-- Mean Click Through Rate by Gender and Campaign
SELECT
gender,
[916] AS Campaign_916,
[936] AS Campaign_936,
[1178] AS Campaign_1178
FROM (
SELECT
gender,
Campaign_id,
ROUND(AVG(CTR),5) AS AVG_CTR
FROM [dbo].[fddata]
GROUP BY gender, Campaign_id
) AS PivotData
PIVOT (
SUM(AVG_CTR)
FOR Campaign_id IN ([916], [936], [1178])
) AS PivotTable;
3.2.4 Mean Conversion Rate by Gender and Campaign
Analysing the conversion rate for each campaign (reading the table row by row):
a) Females once again demonstrated a higher conversion rate in Campaign 916 compared to the other two campaigns, following their superior engagement rate. This suggests that females exhibited a greater interest in the ads of Campaign 916.
b) Despite females having the highest engagement rate (higher CTR) in Campaign 936 compared to males, males exhibited a higher purchase rate (higher mean CR) than females.
-- Mean Conversion Rate by Gender and Campaign
SELECT
gender,
[916] AS Campaign_916,
[936] AS Campaign_936,
[1178] AS Campaign_1178
FROM (
SELECT
gender,
Campaign_id,
ROUND(AVG(CR),5) AS AVG_CR
FROM [dbo].[fddata]
GROUP BY gender, Campaign_id
) AS PivotData
PIVOT (
SUM(AVG_CR)
FOR Campaign_id IN ([916], [936], [1178])
) AS PivotTable;
4.0 RECOMMENDATIONS
Based on the findings from the analysis, several actionable approaches can be proposed:
1. Given the superior performance in conversion rate and click-through rate observed in ad campaigns 916 and 936, the company should consider prioritizing their scaling up. However, this should be done cautiously, considering the potential changes in performance when reaching a larger audience.
2. Despite having a larger budget, campaign 1178’s efficiency in generating conversions is lower compared to the other two campaigns. Therefore, the company should investigate the reasons behind the lack of engagement and conversions in campaign 1178, especially considering its exposure to a larger audience reach.
3. The analysis highlights that the most engaged audience belongs to the age group 30–34. Hence, the company should target this age group for maximum user engagement and interactions. Additionally, it is essential to explore other age groups to understand the reasons for lower interactions with the ads. The company should consider why age group 45–49 patronized their products more in campaign 916 and how they can optimize sales from that age group.
The End: Thank you for reading.
Marketing Campaign Analysis with A/B Testing: https://medium.com/@siriboekofiduodu/facebook-marketing-campaign-analysis-using-a-b-testing-26b4a15c83fa
