Advanced Analytics
Consultant

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, and converted expense columns to a numeric format. Used LOWER() and TRIM() to ensure naming consistency.
• Analyse Descriptive: I created a flat table by joining the cleaned data. Explored data to find:
• Location of the anomaly: Toronto in Canada was particularly problematic in terms of profitability.

ROAS by City (Toronto Anomaly)

I analyzed performance by country and city. I observed that the city of Toronto in Canada was particularly problematic in terms of profitability.
• The month affected: Drastic drop in performance in December, with ROAS turning negative.
• Campaign affected: "Valentines_Promo" and "Post-Holiday-Sale" were showing very low profitability.

Campaign Profitability Comparison (ROAS)

I reviewed the campaign performance. I noticed that two campaigns, "Valentines_Promo" and "Post-Holiday-Sale," were showing very low profitability.
• Attribution: Facebook's attribution model overestimated its conversions, attributing 51% more to itself than GA4 did.

4. Diagnostic Analysis

I formulated several hypotheses to understand the cause:

• Hypothesis 1 (Strategy): Ad strategy not adapted to target audience.
• Hypothesis 2 (Execution): Incorrect setup/optimization (targeting, high bids).
• Hypothesis 3 (Customer): Saturated or un-receptive audience in Toronto.
• Hypothesis 4 (Technical): Pixel/GA4 tracking malfunction.
• Hypothesis 5 (Content): Creatives/Products not attractive enough.
• Hypothesis 6 & 7: Competition / Seasonality & Economic factors.

I focused on Hypothesis 1 because the analysis revealed a glaring anomaly.

5. Validation of the problem

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. This simple observation helped explain the entire underperformance.

6. Recommendations & Quantified Impact

• Immediate Action: Pause or adjust poorly timed campaigns to avoid budget waste.
• Optimization: Review the annual marketing calendar with clear launch dates.
• Preventive Action: Implement a double validation system for campaign launches.

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

7. Demonstrated Technical Skills

• Databases & SQL Logic: Joined Facebook Ads and GA4 to create a flat table, ensuring data standardization.
• Statistical & Predictive Analysis: Scenario modeling to provide a quantifiable forecast ($3,240 additional revenue).
• Data visualization: Used Google Sheets to explore anomalies and support conclusions.

1. Objective

Identify high-potential audience segments to optimize advertising campaigns and predict sales. The central question that guided my work was: “Which audience segments have the highest conversion potential?”

2. Data Transformation Methodology

Working with raw, heterogeneous data on BigQuery and R:

• Cleaning and Standardization: Standardized formats for dates and texts ("google/cpc" and "Google/CPC" unified). Used SAFE.PARSE_DATE and REGEXP_REPLACE to remove symbols like '$' to make strings computable.
• Creating a Flat Table: Joined acquisition and transaction tables into a single flat table, retaining all sessions (even without sales) to calculate accurate conversion rates.

3. Exploration (Where and Who?)

Conducted descriptive analysis with `ggplot2` in R:

• The "Electronics" category overwhelmingly dominates revenue.
• The Google/CPC channel is one of the main revenue generators, confirming effectiveness of paid campaigns.

Revenue by Acquisition Channel

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

4. The “Aha Moment”

I formulated 7 hypotheses. The analysis revealed a synergy of elements for the Electronics success:

• Exceptionally high conversion rate on Google/CPC for Electronics.
• Massively superior average basket for Electronics.
• Year-end seasonality creates strong purchase intent.

Aha Moment: Success is not chance, but a well-orchestrated customer journey where upstream efforts educate an audience, which is then converted efficiently by Google/CPC when intent is highest.

5. Recommendations & Predictions

Using linear regression `lm()` in R to model the relationship between investment (ad_spend) and return (revenue):

6. Demonstrated Technical Skills

Programming (R): Automation, predictive modeling `lm()`, `ggplot2`.
Databases (SQL/BigQuery): Complex JOINs, REGEXP_REPLACE.
Statistical Analysis: Descriptive (what), diagnostic (why), and predictive (what will happen) workflows.

1. Executive Summary

The analysis of Apple's cash position as of September 2025 reveals exceptionally robust financial health and very comfortable short-term liquidity. Key indicators demonstrate a proven ability to cover short-term liabilities.

• Current Ratio stands at 1.8.
• Working Capital Requirement (WCR) is negative at -$119,739, acting as a source of financing.
• Conclusion: Management possesses full strategic flexibility for growth initiatives or deleveraging operations.

2. KPIs and Short-Term Liquidity

Total Net Sales: $1,004,592 | Net Income: $247,948 (Increased from 2024).

Current Ratio: A ratio of 1.8 is considered very healthy. The company can easily cover its entire current liabilities ($165,631M) with its current assets ($281,518M). We observe a positive "scissors" dynamic where assets increase (+3.0%) while liabilities decrease (-6.1%).

3. Operating Cycle Analysis

Working Capital Requirement (WCR): A negative WCR (-$119,739) is characteristic of a dominant business model. Apple collects cash from its sales before it must pay its suppliers. The NWC is largely positive and more than covers the WCR, resulting in a structurally positive and growing Net Cash position.

4. Strategic Recommendation

The company's cash position is optimal and under no financial pressure. Decisions can be based on ROI criteria.

• Option 1: Massive Internal Investment in AI. Apple can afford to invest massively in internal R&D to catch up on its perceived AI deficit.
• Option 2: Strategic AI Partnership. Partnering (e.g., with Gemini) provides immediate access to capabilities without significant upfront capital.
• Option 3: Focus on Core Business. Continue investing in product improvement and advertising.

Decision: The choice depends on Apple's risk appetite. This analysis confirms the company has the financial means to pursue any of these initiatives without compromising stability.

1. Context & Business Problem

How to design a hyper-effective advertising campaign for a back pain solution? Rather than intuition, I wanted to rigorously analyze spontaneous conversations of hundreds of people on public forums (Reddit) to build a surgically precise message.

2. Methodology (R Programming)

Deployed a complete processing pipeline within R:

• Data Source: Scraped Reddit using Apify.
• Cleaning: Used tolower(), gsub for punctuation/digits, and removeWords(stopwords) to isolate meaningful terms.
• Analysis Tool: sentimentr library for sentiment scoring and emotion_by() to detect 8 fundamental emotions.

3. Analysis & Insights: The "Aha Moment"

The emotional palette is dominated by negativity, fear, and sadness. Key Observation: Back pain is not just a physical issue; it is an emotional suffering that impacts daily life. I tested 3 hypotheses:

• H1 (Emotional > Physical?): Validated. Pain is linked to sadness and fear. Our ad must speak to these emotions (Empathy).
• H2 (Attack Competitors?): Invalidated. Sentiment towards medications/doctors is neutral. An aggressive strategy would be ineffective.

Competitor Sentiment Distribution

Neutrality dominates, indicating that an aggressive advertising strategy against competitors would be ill-advised.
• H3 (Ideal words?): Validated via word cloud. Words like "relief", "walk", "sleep", "finally" are what customers desire.

Top Customer Word Associations (Bigram Frequency)

This word cloud provides the key arguments for our future advertising promise.

4. Action Plan & Predictive Recommendations

1. Background: Why did SEO growth stop?

As an SEO consultant for an artisanal coffee site, I was commissioned to analyze a sudden drop in organic traffic observed from May 2025. The objective: diagnose the root cause and develop an action plan to regain growth momentum.

2. Methodology in Google Sheets

Used Pivot Tables, SUMIFS/AVERAGEIFS, and Linear Regression to quantify the decline and predict impact. The descriptive analysis revealed:

• Where: Loss concentrated on informational blog posts.

Organic Traffic Loss by Page Type

The loss of traffic is mainly concentrated on the blog posts informational in nature. These are the pages that attracted a large volume of users and are now in difficulty.
• When: Brutal drop starting May 2025.
• Who: Performance collapsed ONLY on mobile devices. Desktop remained stable.

3. Diagnosing the Cause

Tested hypotheses: internal technical changes, competitor offensives, algorithm updates.

Conclusion (Confirmed): Perfect temporal correlation between the drop and Google's official update on mobile UX (Core Web Vitals). Google penalized our blog pages on mobile for insufficient usability and load times.

4. Quantified Action Plan

1. Context and Business Problem

As part of a personal project simulating a move to Japan, I set a goal for financial independence. The business problem: build a reliable, automated ETL and BI reporting system capable of consolidating heterogeneous data sources to track my monthly expenses against passive income.

2. Data-Driven Methodology

Utilized Google Sheets as an ETL and BI platform:

• Data Sources: Monthly manual expense inputs, asset performance tracking sheets.
• Web Scraping: Used =IMPORTHTML to scrape real-time cost-of-living data for Osaka from Numbeo.com.
• Consolidation: Used =IMPORTRANGE to centralize data. Solved Master Data Management issues (evolving expense categories) by strictly adding new rows instead of renaming, ensuring historical data integrity.

3. Analysis: Complex Formulas

Created real-time currency conversion formulas pulling data from GOOGLEFINANCE APIs to convert USD revenue into simulated JPY expenses. Engineered a Financial Independence KPI using =SPARKLINE to visually track the percentage of expenses covered by income inside the cells.

4. Dashboard & Results

The master data sheet acts as the single source of truth. With advanced filters, I can explore expenses by category, compare asset performance, and visualize the impact of inflation on my budget over time.

Technical Skills Demonstrated: Data Engineering (IMPORTRANGE, IMPORTHTML), Data Cleaning, Complex BI formulas (QUERY, SPARKLINE), and interactive Data Visualization.

Introduction

Welcome to the portfolio website of John Bellanger ("we," "our," or "us"). We are committed to protecting your privacy. This Privacy Policy explains how we collect, use, and safeguard your information when you visit our website, in compliance with applicable regulations, including the California Consumer Privacy Act (CCPA) and California Privacy Rights Act (CPRA).

Data Collection & Processing

We process data with a privacy-first mindset. The only data processed is as follows:

• Anonymized Analytics: We use Google Tag Manager (GTM) and Google Analytics 4 (GA4) to collect aggregate statistical data about site usage (such as page views, time spent, and referral sources). IP address anonymization is strictly enabled, Google Signals are disabled, and data sharing features for advertising are turned off. We do not use advertising pixels (such as Meta, LinkedIn, etc.) or collect personally identifiable information (PII) through tracking.
• Contact Form Information: If you voluntarily contact us using the contact form or email, we collect your name, email address, and the content of your message. This data is only used to respond to your inquiry and is never shared, sold, or rented to third parties.

Cookies and Tracking Technology

Our website utilizes cookies for analytics purposes. Cookies are small files stored on your device that help us analyze web traffic. You can configure your browser to reject cookies or alert you when a cookie is sent. However, doing so will not affect your user experience on this site.

Your Rights (CCPA / CPRA & International Visitors)

If you are a resident of California or access the site from jurisdictions with privacy regulations, you have specific rights regarding your personal information under the CCPA/CPRA, including:

• Right to Know: The right to request disclosure of the personal data collected, used, or shared.
• Right to Delete: The right to request the deletion of personal data collected from you.
• Right to Correct: The right to request correction of inaccurate personal data.
• Right to Opt-Out: Since we do not sell or share your personal information with third parties for cross-context behavioral advertising, no opt-out is necessary.

To exercise any of these rights, please contact us at contact@johnbellanger.com. We will respond to verified requests in accordance with applicable law.

Data Retention & Security

Contact inquiries are kept only as long as necessary to address your request. We implement standard, industry-recognized security measures to protect the personal information submitted to us.

Contact Information

If you have any questions or concerns about this Privacy Policy, please contact us at:

Email: contact@johnbellanger.com