John Bellanger - Facebook Ads: Negative ROAS

Facebook Ads Campaign Performance

1. Context and Objective of the Stakeholder

The objective of this mission, at the request of my stakeholder, was to understand why increasing advertising spending on Facebook Ads was not translating into equivalent growth in the company's overall revenue. The stakeholder noted that profitability appeared to be at risk, particularly in certain quarters.

The hypothesis raised was that traffic from Facebook Ads was of low quality, leading to overestimation of conversions by the platform itself, a low conversion rate on the site, and a low value per customer.

2. The Problem

The analysis confirmed the stakeholder's concerns: an overall low ROAS (Return On Ad Spend) for Facebook traffic, and a conversion rate for this same channel that is the lowest of all acquisition channels. A particularly negative ROAS was observed in December, signaling a major underperformance.

3. The Method of Analysis

I adopted a rigorous methodology in several phases to respond to this problem:

Data cleaning:I duplicated the raw tables from Facebook Ads and Google Analytics 4 to work on copies on BigQuery. I then standardized date formats, handled missing values (NULL and NA), and converted the expense columns to a numeric format. I chose to leave most of the text columns for granular analysis, ensuring naming consistency via functions like LOWER() and TRIM().
Analyse Descriptive :I created a flat table by joining the cleaned data from Facebook Ads and GA4 for Facebook traffic (facebook/cpc). I then proceeded to analyze on Google Sheets by exploring the data from the following angles:

Location of the anomaly:I analyzed performance by country and city. I observed that the city of Toronto in Canada was particularly problematic in terms of profitability.

Cleaning data with SQL

The month that was affected by this anomaly:I analyzed monthly and quarterly trends in expenses and revenue. I identified a drastic drop in performance in December, with ROAS turning negative.

The advertising campaign was affected:I reviewed the campaign performance. I noticed that two campaigns, "Valentines_Promo" and "Post-Holiday-Sale," were showing very low profitability.

The conversion percentage attributed to Facebook:I quantified Facebook's contribution to the total acquisition and measured the attribution gap. I found that Facebook's attribution model overestimated its conversions, attributing 51% to itself more conversions than GA4.

4. Diagnostic Analysis

To understand the cause of the underperformance, I formulated several hypotheses, including the following:

Hypothesis 1: Team-related problem (Strategy):The advertising strategy of the campaigns was not adapted to the target audience.
Hypothesis 2: Team-related problem (Execution):Campaigns may have been incorrectly set up or optimized, resulting in inappropriate targeting, poor quality audiences, or excessively high bids.
Hypothesis 3: Customer-related problem:Target audiences in Toronto are either saturated or have already engaged with the brand. Traffic is high but not receptive to these campaigns.
Hypothesis 4: Technical problem (Pixel/GA4):There was a malfunction with the Facebook pixel or GA4 tracking.
Hypothesis 5: Problem related to the content of the offer (Creatives/Products): The content of the advertisements or the products featured in the campaigns were not attractive enough to the Toronto audience.
Hypothesis 6: Competition problem:Increased competition in Toronto has saturated the advertising market.
Hypothesis 7: Circumstances related problem (Seasonality/Economics): Public purchasing power in Toronto was weaker than expected.

I chose to focus on Hypothesis 1 because the analysis revealed a glaring anomaly that turned out to be the root cause.

5. Validation of the problem related to the strategy teams

Looking at the data for the "Post-Holiday-Sale" campaign, which launched in December 2024, I had an "Aha Moment." The campaign's name clearly indicates that it should have been launched. After the holiday season. The data confirmed this error: the campaign was executed at the wrong time, wasting significant budget at a time when the offer was not relevant to the audience. This simple observation helped explain the entire underperformance.

6. Recommendations

To address this problem, I propose the following three recommendations, based on the analysis:

Immediate Action:Immediately pause or adjust poorly timed campaigns to avoid continued budget waste.
Action d'Optimisation :Review and refine the annual marketing calendar, with clear and unambiguous launch dates for all campaigns.
Preventive Action:Implement a double validation system for campaign launches to prevent similar errors in the future.

7. The Quantified Impact of these Recommendations

The implementation of these recommendations would have a direct and significant financial impact:

Prediction:Based on the performance of a properly planned winter sales campaign (with a ROAS of 7.19), we believe that launching the "Post-Holiday-Sale" campaign at the right time could have generated $3,240 additional incomeand about 56 conversions for the same budget. This correction would have transformed a loss into a substantial profit.

Dashboard

Presentation

7. Demonstrated technical skills

This project has enabled me to apply a wide range of data analysis skills:

Databases & SQL Logic: My ability to logically join two fictitious data sources generated by Gemini (Facebook Ads and GA4) to create a relevant flat table, as well as my rigor in cleansing, standardizing and aggregating data, stem directly from structured thinking driven by SQL.
Statistical & Predictive Analysis: I used a simple modeling approach, not as a gimmick, but to translate the observation of underperformance into a "what if..." scenario. I was thus able to provide a quantifiable forecast of $3,240 in additional revenue, offering a clear ROI for the first recommendation.
Data visualization (Google Sheets): I demonstrated my ability to choose the right tool for analysis: clear tables and powerful graphs in Google Sheets to explore the data, identify anomalies and support my conclusions.

This project illustrates my ability to translate a complex business issue into a rigorous data analysis, communicate clear conclusions and, above all, propose a concrete, costed action plan to generate value.

If I can help you with any of your assignments, we can talk about it during a call.

Let's become partner

Contact

Mail

Github