Full technical breakdown: the GAQL query, the ShoppingContent API call, the five-dimension grading logic, the supplemental feed architecture, and the campaign routing. If you want the business case first, start with the positioning overview.
shopping_performance_view via GAQL — pull 30 days of per-SKU impressions, clicks, cost, conversions, and revenue for all products with trafficShoppingContent.Products.list() — retrieve every active product from Merchant Center and cross-reference by product IDcustom_label_0), price tier, margin proxy, conversion efficiency, and visibility score"We presented this system to our Google Ads rep, and they said they hadn't seen anyone do this before — where the supplemental feed updates daily and the campaigns automatically refresh with fresh top-performing SKUs."
— Annie Lee, Founder, Akorn Media
of Google Shopping advertisers cannot attribute revenue to individual SKUs in their PMax campaigns — because aggregate ROAS reporting masks product-level performance. The result is budget allocated by Google's algorithm, not by the advertiser's profitability strategy.
Source: Based on Akorn Media account audit data, 2024–2026 (n=12 DTC accounts, $20K–$100K/month spend).
Five steps, two APIs, one supplemental feed. The script connects Google Ads performance data to Merchant Center product attributes — closing the loop between what the algorithm spends and what your products actually earn.
Step 1
Pull Ads data
GAQL
Step 2
Pull GMC catalog
ShoppingContent API
Step 3
Grade & label
5 dimensions
Step 4
Push to sheet
Supplemental Feed
Step 5
Campaigns update
Auto-routing
The script opens with a query against shopping_performance_view — the Google Ads resource that exposes per-product performance data across Shopping and PMax campaigns.
// Pull 30 days of per-product Shopping + PMax performance
var query = "SELECT segments.product_item_id, " +
"metrics.impressions, metrics.clicks, metrics.cost_micros, " +
"metrics.conversions, metrics.conversions_value " +
"FROM shopping_performance_view " +
"WHERE segments.date DURING LAST_30_DAYS";
var result = AdsApp.search(query);
var performanceMap = {};
while (result.hasNext()) {
var row = result.next();
var productId = row.segments.productItemId.toLowerCase();
performanceMap[productId] = {
impressions: row.metrics.impressions,
clicks: row.metrics.clicks,
cost: row.metrics.costMicros / 1000000,
conversions: row.metrics.conversions,
revenue: row.metrics.conversionsValue
};
}
The ShoppingContent API returns every active product in your Merchant Center account. The script cross-references this against the performance map using case-insensitive ID matching — catching any formatting inconsistencies between the two data sources.
var MERCHANT_ID = 'YOUR_MERCHANT_ID';
var catalogMap = {};
var pageToken = null;
// Handle pagination for large catalogs
do {
var response = ShoppingContent.Products.list(MERCHANT_ID, {
maxResults: 250,
pageToken: pageToken
});
(response.resources || []).forEach(function(product) {
var id = product.offerId.toLowerCase();
catalogMap[id] = {
title: product.title,
price: parseFloat(product.price.value),
availability: product.availability
};
});
pageToken = response.nextPageToken;
} while (pageToken);
Every product gets evaluated across five dimensions. Label 0 drives campaign routing. Labels 1–4 provide secondary intelligence for bid decisions, asset group targeting, and identifying products one optimization away from becoming winners.
| Tier | Condition | Label value | Campaign action |
|---|---|---|---|
| Hero | roas >= 3.0 | 'High ROAS' | PMax Hero — aggressive tROAS |
| Growth | roas >= 1.5 | 'Medium ROAS' | PMax Growth — lower tROAS |
| Review | roas > 0 | 'Low ROAS' | Monitor — consider pausing |
| Bleeder | cost > 0, conv = 0 | 'Bleeder' | Exclude from all campaigns |
| Untested | no traffic | 'Untested' | Standard Shopping testing lab |
| Label | Dimension | What it tells you | How it's calculated |
|---|---|---|---|
| custom_label_1 | Price tier | Enables differentiated bidding by price segment | Product price vs. catalog price quartiles |
| custom_label_2 | Margin proxy | Discount dependency — full price vs. promoted | Sale price vs. regular price ratio |
| custom_label_3 | Conversion efficiency | Click-to-sale rate independent of spend | conversions / clicks (min click threshold) |
| custom_label_4 | Visibility score | Is Google surfacing or suppressing this product | impressions vs. expected by price tier |
var outputRows = [['id', 'custom_label_0', 'custom_label_1',
'custom_label_2', 'custom_label_3', 'custom_label_4']];
Object.keys(catalogMap).forEach(function(id) {
var perf = performanceMap[id] || {};
var cost = perf.cost || 0;
var revenue = perf.revenue || 0;
var conversions = perf.conversions || 0;
var roas = cost > 0 ? revenue / cost : 0;
// Label 0: ROAS tier — primary campaign routing signal
var label_0;
if (roas >= 3.0) label_0 = 'High ROAS';
else if (roas >= 1.5) label_0 = 'Medium ROAS';
else if (roas > 0) label_0 = 'Low ROAS';
else if (cost > 0) label_0 = 'Bleeder';
else label_0 = 'Untested';
// Labels 1-4: price, margin, conv rate, visibility
// Production version calculates all four — see full script
outputRows.push([id, label_0, label_1, label_2, label_3, label_4]);
});
// Write to Supplemental Feed sheet in chunks to avoid timeout
var sheet = SpreadsheetApp.openById(SHEET_ID).getActiveSheet();
sheet.clearContents();
var chunkSize = 500;
for (var i = 0; i < outputRows.length; i += chunkSize) {
var chunk = outputRows.slice(i, i + chunkSize);
sheet.getRange(i + 1, 1, chunk.length, chunk[0].length).setValues(chunk);
}
The graded output goes to a Google Sheet registered as a Supplemental Feed in Merchant Center. GMC fetches the sheet on its daily crawl, overlays the label values onto your live product data, and your campaign product group filters update automatically.
id, custom_label_0 through custom_label_4offerId — use case-insensitive normalization in the script to prevent mismatchesCampaign 01
PMax — Hero Winners
custom_label_0 = 'High ROAS'Aggressive tROAS. PMax scales proven winners across Search, Shopping, Display, YouTube, Discovery.
60–70% of budget
Campaign 02
PMax — Growth
custom_label_0 = 'Medium ROAS'Lower tROAS target. Algorithm finds incremental conversions and graduates products to Hero.
20–25% of budget
Campaign 03
Standard Shopping — Testing Lab
custom_label_0 = 'Untested'Maximize Clicks. Full search term visibility. Products graduate automatically when they prove ROAS.
10–15% of budget
Bleeders auto-excluded daily
Any product with spend and zero conversions is labeled Bleeder and excluded from all campaigns — no manual audit cycle required.
Heroes get isolated budget
High-ROAS products stop competing with thousands of untested SKUs. Dedicated campaign, aggressive bidding, no dilution.
Untested products get a fair window
Standard Shopping testing lab gives new products controlled exposure with full search term visibility before any judgment is made.
PMax ROAS becomes trustworthy
Branded traffic no longer inflates aggregate ROAS. Each product's performance is graded independently, giving you a real signal on non-brand growth.
Common implementation questions from developers and PPC practitioners building this system.
What Google Ads API query pulls per-product Shopping performance data?
Query the shopping_performance_view resource in GAQL, selecting segments.product_item_id, metrics.impressions, metrics.clicks, metrics.cost_micros, metrics.conversions, and metrics.conversions_value, filtered by segments.date DURING LAST_30_DAYS. Execute via AdsApp.search(query). This returns individual SKU-level data across both Shopping and PMax campaigns in a single query.
How do you download a full product catalog from Google Merchant Center via script?
Use ShoppingContent.Products.list(MERCHANT_ID) with a maxResults parameter and handle pagination via nextPageToken. Do not assume all products return in a single call — large catalogs require multiple paginated requests. Normalize all product IDs to lowercase before building your catalog map to ensure consistent matching with GAQL output.
How do you assign custom labels in Google Shopping via a script?
Write label values to a Google Sheet with column headers matching GMC attribute names exactly: id, custom_label_0 through custom_label_4. Register the sheet as a Supplemental Feed in Merchant Center (Feeds → Add Supplemental Feed → Google Sheets). GMC fetches the sheet on its daily crawl and overlays label values onto matching products. Use chunked writes of 500 rows maximum to avoid Google Apps Script timeout errors.
What ROAS thresholds should I use to grade Google Shopping products?
Starting thresholds: High ROAS at 3x or above, Medium ROAS between 1.5x and 3x, Low ROAS above 0 but below 1.5x, Bleeder where cost is greater than 0 and conversions equal 0, Untested where no traffic exists. These are starting points — adjust based on your target margin. A brand with 80% gross margin can sustain a lower ROAS target than a brand at 40%. Review thresholds after 60 days of data and adjust per category if your catalog spans significantly different margin profiles.
How do I set up a Supplemental Feed in Google Merchant Center?
In Merchant Center go to Feeds, click Add Supplemental Feed, select Google Sheets as the input type, paste your sheet URL, and map column headers to product attributes. Set fetch frequency to daily. GMC matches products by the id field (your offerId) and overlays any attribute values present in the supplemental sheet. No modification to your primary feed is required — supplemental feeds overlay, they do not replace.
How do I exclude bleeder products from all Google Shopping campaigns automatically?
In the GAQL query, bleeders are identified where metrics.cost_micros > 0 and metrics.conversions = 0 over the last 30 days. They receive custom_label_0 = 'Bleeder' in the supplemental feed. In each campaign — PMax Hero, PMax Growth, and Standard Shopping — create a product group that excludes custom_label_0 = 'Bleeder'. Because the script re-grades daily, products that start converting will graduate out of Bleeder status automatically on the next run.
Why does PMax aggregate ROAS hide product-level underperformance?
PMax reports a single ROAS figure blending branded and non-branded traffic. Branded queries convert at 3–10x the rate of non-brand queries depending on the category. A campaign with strong brand recall can show a 6x aggregate ROAS while its non-brand performance runs below 1x. This script grades by individual product ID — separating signal by SKU, not campaign average — so you can see which products are actually profitable on non-brand traffic versus which are riding brand intent.
The production version of this script handles error recovery, multi-account execution, and a full performance summary dashboard. It's built for catalogs with 500+ SKUs and accounts spending $10K+/month on Shopping or PMax.
We install and configure the full production system — script, supplemental feed, campaign architecture, and daily monitoring dashboard — as part of our engagement.
Book a strategy call →For brands with 500+ SKUs spending $10K–$100K/month on Google Shopping or PMax