John Bellanger - Google: Prediction on Ads

Growth Strategies for Targeted Advertising

Objective: Identify high-potential audience segments to optimize advertising campaigns and predict sales.

1. What was our starting point?

The initial goal of this analysis was to mine our Google Ads and Google Analytics data to build a more targeted advertising strategy based on reliable sales predictions. The central question that guided my work was:“Which audience segments have the highest conversion potential?”

2. How did I transform raw data into a reliable basis for analysis?

My first challenge was working with raw, heterogeneous, and "dirty" data. To ensure the reliability of the analysis, I followed a rigorous multi-step methodology on BigQuery and R.

Cleaning and Standardization:I first duplicated the tables to preserve the originals. Then, I removed duplicates, standardized the formats for dates, texts (e.g., "google/cpc" and "Google/CPC" were unified), and numbers (removing currency symbols like '$' to make them computable). I used functions like SAFE.PARSE_DATE for dates and REGEXP_REPLACE for numbers, chosen for their robustness in handling multiple formats without generating errors.
Creating a Flat Table:To simplify the analysis, I joined the acquisition and transaction tables into a single flat table. I used a JOIN to ensure I retained all sessions, including those that didn't result in a sale, which is crucial for calculating accurate conversion rates.

3. What did we discover during the exploration?

The descriptive analysis, conducted with the ggplot2 library on R for its advanced visualization capabilities, quickly revealed very clear trends by answering three simple questions.

Where does performance lie?

The "Electronics" category overwhelmingly dominates revenue, far ahead of all others. I used the reorder() function in ggplot2 to sort the bars and make this hierarchy immediately visible.

Cleaning data with SQL

Who are the drivers of this performance?

The Google/CPC channel is one of the main revenue generators, confirming the effectiveness of our paid campaigns on this lever.

Who are the drivers of this performance?

Finally, to link performance (Where and When) to marketing actions, I completed my exploration with the "Who/How." I analyzed Total_Revenue by Traffic Source. This view was essential to understand which acquisition channels were most effective at generating revenue, especially for the previously identified category and period.

Descriptive Analysis with R

4. What is the “Aha Moment” that explains everything?

After exploring the data, I formulated 7 hypotheses to understand the"For what"behind the success of Electronics. The analysis revealed that the performance is not due to a single factor, but to a synergy of several elements.

I chose to focus on the Hypothesis 2: Advertising targeting is more effective, because it is enriched and validated by several other discoveries.

The conversion rate on Google/CPC for Electronics is exceptionally high, proving that we are reaching a qualified audience.
The average basket of Electronics is massively superior, making each conversion very profitable.
The seasonality year-end creates a strong purchase intent that our targeted campaigns successfully capture.

The "Aha Moment" is therefore this: our success is not the result of chance, but of a well-orchestrated customer journey, where upstream marketing efforts (like newsletters) "educate" an audience, which is then converted with formidable efficiency by our Google/CPC campaigns at the time when the purchase intent is the strongest.

Diagnostic Analysis with R

5. How to capitalize on these discoveries? (Recommendations)

My analysis leads to a three-step action plan based on predictive models. To estimate the impact, I used the lm() function in R to create linear regression models, chosen for their simplicity and their ability to clearly quantify the relationship between an investment (ad_spend) and a return (revenue).

Recommendation 1: Capitalize on our strengths (Immediate Action)

Action :Double the Google advertising budget/CPC for the Electronics category, from 10,472 to 20,944.

Predicted Impact:The model shows a ROAS of 2.11. This action would increase revenue from $24,708 has $46,976 , or an additional gain of more than $22,000.

Recommendation 2: Correct our weak point (Optimization Action)

Action: Do NOT invest further in the "Home" category at this time. Launch an advertising strategy audit.

Numerical Justification: The predictive model showed that every dollar invested in this segment only returns $0.85 . It is therefore necessary to first correct the strategy before reinvesting.

Recommendation 3: Securing the future (Preventive Action)

Action :Pause TikTok campaigns targeting direct revenue.

Numerical Justification:The analysis showed no evidence of a link between clicks and revenue on this channel, so it is wiser to reallocate this budget.

Predictions with R

Presentation

Dashboard

6. Demonstrated Technical Skills

Programming (R): Wrote R scripts for analysis automation, data manipulation, and predictive modeling (lm()). Created complex and customized visualizations with ggplot2.
Databases (SQL/BigQuery): Authored complex queries including joins (JOIN), performed data cleaning and transformation (REGEXP_REPLACE), and validated data quality.
Visualization (Looker Studio, R): I used R for static and exploratory visualizations during the analysis phase. For reporting, I recommend Looker Studio for its ability to create interactive and automated dashboards that are easily shareable within the Google ecosystem.
Statistical Analysis: Mastered the complete analytical workflow: descriptive (what), diagnostic (why), and predictive (what will happen). Formulated and validated hypotheses and applied linear regression models for forecasting.
Communication: I used Google Slides (via Gamma) to present these results as a compelling narrative, translating complex analyses into clear insights and actionable recommendations for a business audience.

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