John Bellanger - SEO: Drop Trafic

SEO – cafearabicafictif.fr

Subject: Diagnosis and action plan following the drop in performance in May-June 2025.

Background: Why did our SEO growth suddenly stop?

As an SEO consultant forcafearabicafictif.fr, I was commissioned to analyze a drop in SEO performance observed from May 2025. After a very promising start to the year, the site, specialized in the sale of artisanal coffee and with an influential blog, suffered a drop in organic traffic. The objective of this analysis is therefore clear: diagnose the nature of the problem, identify the cause of racine and develop a concrete action plan to reverse the trend and regain our growth momentum.

Methodology: The Tools Chosen for Accurate Analysis

To conduct this analysis, I deliberately chose to use Google Sheet. This tool, while seemingly simple, is extremely powerful for the type of analysis required here. It allows me to manipulate raw data, create custom visualizations, and apply statistical models without the complexity of a heavier programming environment.

Throughout the analysis, I used specific functions for their effectiveness:

Pivot tables:I used this tool to segment the data. It's the fastest and most reliable way to synthesize thousands of rows of data and compare performance across different dimensions like device type or site pages.
SUMIFS / AVERAGEIFS :To quantify the decline, I needed to target specific periods. These functions are ideal because they aggregate data (sum, average) based on one or more criteria, guaranteeing me highly accurate calculations.
Linear Regression:To predict the impact of our actions, simple correlation is not enough. I modeled the relationship between position and clicks using linear regression to obtain a predictive equation. This transforms an observation into a quantified management tool.

What does the data tell us?

The descriptive analysis allowed me to draw up a very precise portrait of the problem. The drop in traffic is not general, but surgically localized.

Where is the problem most evident?

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 exactly did the decline take place?

The drop is clear and brutal. It began in May 2025, breaking with the good momentum of previous months. Our click traffic fell by 341 in April at an average of 28 in May-June.

Who is affected by this drop?

This is the most telling insight: performance has collapsed only on mobile devices Desktop traffic, on the other hand, remained stable. The problem is therefore intrinsically linked to the experience or perception of our site on mobile.

Why did we lose these rankings?

With a clear diagnosis (traffic drop on blog posts, on mobile, starting in May), I formulated and tested several hypotheses to find the root cause.

Hypothesis 2 (Technical Issue) & 3 (On-site Change):~~ I first checked for internal factors. An analysis of change logs and technical reports (Core Web Vitals) revealed no major bugs or site updates coinciding with the drop.
Hypothesis 4 (Competition):~~ A review of the SERPs showed that no competitor had launched a significant offensive during this period.
Hypothesis 1 (Google Algorithm Update): CONFIRMED. The perfect temporal correlation between the drop and Google's official communications about a mobile user experience-focused algorithm update was too strong a signal to be ignored.

My conclusion was that Google had re-evaluated our blog pages on mobile and, deeming their usability and load times (Core Web Vitals) insufficient, had penalized them.

Results & Performance: My Quantified Action Plan

Based on this diagnosis, I developed a three-phase action plan, quantifying the expected impact of each recommendation to support decision-making. To do this, I first modeled the relationship between our Google ranking and the number of clicks received.

Recommendation 1: Stop the Bleed (Impact: 1-2 Weeks)

Action: Immediately audit and fix the Core Web Vitals issues (speed, usability) on the 5 to 10 most affected blog pages.

Quantified Impact: Based on my regression model, restoring our initial positions could help us regain over 10,000 monthly clicks.

Recommendation 2: Aim for Excellence (Impact: 1-2 Months)

Action: Go beyond the technical fix. I recommended enriching the content of these strategic pages and improving the overall design to provide a flawless mobile experience.

Quantified Impact: The goal is to increase the engagement rate on these pages from 45% to 60%, securing an audience of ~30,000 monthly users against future updates.

Dasboard

Recommendation 3: Implement Quality Assurance (Impact: Ongoing)

Action: To prevent this situation from reoccurring, I advocated for creating monitoring processes via dedicated Looker Studio dashboards for mobile performance and quality checklists for all new publications.

Quantified Impact: This crisis cost us approximately 312 clicks per day. This preventive system acts as an "insurance policy" aiming for a

future goal of zero traffic loss related to technical quality issues.

Presentation

Demonstrated Technical Skills

This project allowed me to leverage a wide range of data analysis skills:

Databases & SQL Logic: My ability to logically join 5 data sources (pages, traffic, queries, devices, channels) to identify precise insights stems directly from SQL-driven structured thinking (joins, aggregations, segmentations).
Statistical Analysis: I used a linear regression model not as a gimmick, but to translate a statistical correlation (R² of 0.897) into a quantifiable business forecast, providing a clear ROI for my recommendations.
Data Visualization (Google Sheets, Looker Studio): I demonstrated my ability to choose the right tool for the right audience: clear and direct charts in Google Sheets for analysis, and an interactive, automated dashboard in Looker Studio for stakeholder reporting.

This project illustrates my ability to translate a complex business question into a rigorous data analysis, communicate clear insights, and, most importantly, provide a concrete and quantified 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