How to Analyze Google Shopping Campaign Effectiveness

Your Google Shopping campaigns are generating traffic, but you don't know which products are actually making money and which ones are just burning through the budget? Campaign-level ROAS looks acceptable, but the margin at the end of the month doesn't add up. This is a classic e-commerce problem that can be solved once you go one level deeper and start analyzing data at the product level.

Why Campaign-Level ROAS Lies

Most e-commerce teams look at ROAS in Google Ads and stop there. The campaign returns 4x? Great. The problem is that behind this aggregate hides reality: 20% of products generate 80% of conversions, while the rest of the assortment consumes budget with no return.

To see the real picture, you need to connect three data sources:

• Google Ads — cost, clicks, conversions at the product level (Shopping report → Products)
• GA4 — sessions, conversion rate, e-commerce revenue
• ERP/backend system — product margin, inventory levels, purchase cost

Google Ads alone will tell you how much you spent. Only after joining with margin data will you know whether the campaign is actually profitable.

Analysis Structure in Snowflake — From Cost to Margin

If you have Google Ads data pulled into Snowflake (e.g., via Keboola Google Ads Extractor), you can build a product-level analysis without leaving SQL. Here's a simplified example:

WITH ads_data AS (
    SELECT
        product_id,
        product_title,
        SUM(cost) AS total_cost,
        SUM(clicks) AS total_clicks,
        SUM(conversions) AS total_conversions,
        SUM(conversion_value) AS total_revenue
    FROM google_ads.shopping_product_stats
    WHERE date BETWEEN '2024-01-01' AND '2024-03-31'
    GROUP BY 1, 2
),
margin_data AS (
    SELECT
        product_id,
        AVG(margin_pct) AS avg_margin,
        AVG(unit_price) AS avg_price
    FROM erp.products
    GROUP BY 1
)
SELECT
    a.product_id,
    a.product_title,
    a.total_cost,
    a.total_revenue,
    ROUND(a.total_revenue / NULLIF(a.total_cost, 0), 2) AS roas,
    ROUND((a.total_revenue * m.avg_margin) / NULLIF(a.total_cost, 0), 2) AS margin_roas,
    ROUND(a.total_cost / NULLIF(a.total_conversions, 0), 2) AS cpa,
    a.total_conversions
FROM ads_data a
LEFT JOIN margin_data m ON a.product_id = m.product_id
ORDER BY a.total_cost DESC;

The key column is margin_roas — not revenue divided by ad cost, but margin divided by cost. A product with ROAS = 6x and 15% margin is less profitable than a product with ROAS = 3x and 50% margin.

Segmenting Products by Profitability

After building the cost-margin view, the next step is segmentation. I use a simple 2×2 matrix:

| | High ROAS | Low ROAS |

|---|---|---|

| High margin | ⭐ Scale budget | 🔍 Optimize bids |

| Low margin | ⚠️ Monitor | ❌ Limit/pause |

In SQL you can encode this as:

SELECT
    product_id,
    product_title,
    margin_roas,
    avg_margin,
    CASE
        WHEN margin_roas >= 3 AND avg_margin >= 0.30 THEN 'SCALE'
        WHEN margin_roas >= 3 AND avg_margin < 0.30  THEN 'MONITOR'
        WHEN margin_roas < 3  AND avg_margin >= 0.30 THEN 'OPTIMIZE'
        ELSE 'PAUSE'
    END AS recommended_action
FROM product_performance_view
ORDER BY total_cost DESC;

The thresholds margin_roas >= 3 and avg_margin >= 0.30 need to be calibrated for your business — in electronics, margins of 10-15% are the norm, while in cosmetics they're often 50%+.

GA4 as a Complement to Google Ads Data

Google Ads shows conversions, but GA4 provides behavioral context that's missing from advertising reports. Particularly useful metrics:

• Add-to-cart rate from Shopping traffic — does the product that gets clicks actually end up in the cart?
• Time to conversion — products with a long purchase cycle require a different bidding strategy than impulse buys
• Conversion paths — is Shopping the first touchpoint or the closing one?

In GA4 (BigQuery Export) you can extract session paths with the session_campaign parameter and compare the behavior of Shopping users vs. other channels. It often turns out that Shopping initiates the sale, but the conversion "gets credited" to remarketing — and then you mistakenly reduce the Shopping budget.

Automating Reporting in Keboola

Manually joining data from Google Ads, GA4, and ERP every month is a waste of time. A repeatable pipeline in Keboola looks like this:

1. Extractors: Google Ads Extractor + BigQuery (GA4 export) + ERP database extractor
2. Transformations: SQL transformations in Snowflake — data cleaning, joining on product_id, metric calculations
3. Orchestration: daily trigger running the pipeline at 6:00 AM
4. Output: result table feeding a Tableau dashboard with an automatic alert when margin_roas of any product with a budget > $150/month drops below 1.5

This setup takes 2-3 days of one-time work, but after that the Shopping campaign report generates itself — without involving an analyst at every reporting cycle.

TL;DR

• ROAS without margin is half the truth — always combine ad data with product margin to see real profitability
• Segment products using the ROAS × margin matrix and assign actions: scale, optimize, pause
• GA4 + Google Ads together show the full user journey — don't evaluate Shopping only through last-click attribution
• Snowflake SQL lets you build a product-level analysis by joining data sources without no-code tools
• Automation in Keboola eliminates manual work and delivers a daily profitability view of Shopping campaigns