The Business Problem

A growing e-commerce brand was facing a classic Siloed Attribution Crisis (meaning their marketing platforms and their actual bank account were telling two completely different stories).

While their Meta and Google Ads dashboards reported exceptionally high Returns on Ad Spend (ROAS), the company’s internal sales data showed shrinking profit margins and rising acquisition costs.

The issue was that their data was scattered across different places: ad platforms tracked spend, while the backend system tracked actual sales.

Relying on manual, error-prone spreadsheet merges to plan their quarterly budget, the executive team was flying blind.

They handed over their raw, fragmented data and asked me to build an automated analysis layer to answer core commercial questions:

• which channels drive revenue;
• what is the true ROAS;
• where is budget being wasted;
• and what is our repeat purchase rate?

My Approach: How I Engineered the Analysis Workflow

To permanently eliminate the manual spreadsheet crashing, I bypassed ad-hoc exports and architected a relational SQL data model to serve as a single source of truth.

Here is the step-by-step build process:

Step 1: Define the Schema (Building the Foundation)

Before writing any queries, I organized the raw tables into a normalized relational schema (orders, customers, and ad_performance).

This matters because weak structure leads to weak analysis; this schema ensured business relationships were visible and logical.

[MySQL Workbench Schema Diagram or Table Overview]

Step 2: Start with Channel-Level Revenue

I wrote an aggregation query to answer a basic but critical question: which channels drive the most revenue?

This establishes baseline channel contribution, identifying exactly where revenue is concentrated and which channels deserve a closer look.

[SQL Query & Result Grid for Channel Revenue]

Step 3: Comparing Campaigns by ROAS (Cross-Platform Joins)

Next, I engineered a LEFT JOIN to map ad performance directly to order data.

To calculate true ROAS without crashing the database on zero-spend days, I deployed a division-by-zero protection logic: SUM(o.revenue) / NULLIF(SUM(a.spend), 0).

A campaign can look active without being efficient; this step finally connected commercial return to marketing spend.

[SQL Query & Result Grid for Campaign ROAS]

Step 4: Analyzing Monthly Revenue Trends

I utilized time-series grouping via the DATE_FORMAT function to aggregate revenue month-over-month.

Trend analysis is vital because it supports forecasting, reporting cadence, and performance comparison over time.

[SQL Query & Result Grid for Monthly Trends]

Step 5: Reviewing Product-Category Performance

I queried the database to identify which specific product categories generated the most revenue.

This insight shows the C-suite exactly where product demand is strongest and which categories need more inventory or marketing attention.

[SQL Query & Result Grid for Product Categories]

Step 6: Measuring Repeat Purchase Behavior

To measure customer loyalty, I deployed HAVING COUNT(order_id) > 1 logic to isolate the specific cohort of customers with more than one order.

Repeat purchase behavior is one of the strongest signals of customer quality, retention, and long-term business value.

[SQL Query & Result Grid for Repeat Purchases]

Step 7: Comparing Average Order Value by Country

I joined the orders and customers tables to calculate the AVG() revenue by country.

Geographic comparisons directly influence ad targeting, pricing, fulfillment strategies, and performance expectations.

[SQL Query & Result Grid for AOV by Country]

Step 8: Identifying High-Spend, Low-Return Channels

I reversed the sorting logic (ORDER BY roas ASC, total_spend DESC) to isolate spending vulnerability.

This query specifically hunted for campaigns that were bleeding budget—the exact data needed to support immediate budget review and reallocation decisions.

[SQL Query & Result Grid for High-Spend/Low-Return]

Step 9: Exporting Outputs for Business Presentation

Finally, I extracted the query outputs into summarized Excel charts and tables.

Strong technical analysis is useless if stakeholders can’t read it; this step translated the code into visual findings that non-technical executives could understand in seconds.

[Exporting data from MySQL to Excel]

Data Visualization & Output

With the SQL data model successfully validated, the insights were packaged into an executive-ready presentation layer.

✅ ROAS by Campaign Matrix: A comparative view highlighting the exact financial return of every active campaign.

✅ Monthly Revenue Trendline: A visual proof of the trajectory of verified sales.

✅ Budget Reallocation Highlight: A specific view isolating high-spend, negative-ROI outliers.

[Clean, minimalist Excel Dashboard or Chart showing Revenue vs Spend]

Business Impact

The true value of this SQL-driven workflow wasn’t just to have clean data—it was the immediate commercial leverage it gave the executive team.

By moving out of spreadsheets and into a unified database, the business gained four distinct advantages: