The solution presents the data in an accessible way, dividing it into Shopping vs other channels.
Additionally, it highlights key metrics for both of these segments:
cost, conversions, ROAS, and CPA.
This allows for an informed decision to be made:
-Whether to keep the current campaigns with a full set of assets (Full Assets)
-Whether to test Feed Only or No Feed as separate campaigns
-Whether to switch to Standard Shopping.
Config:
- Set the number of days you want to analyse.
I have taken 45 as a standard, but it’s worth checking longer periods as well. - Provide the link to the spreadsheet where the data should be saved.
- Specify what percentage of spend on other channels should be considered.
I recommend the standard 10%. If a PMax campaign allocates less than 10% of its total budget to other channels, it will be difficult to draw meaningful conclusions.
Unless the overall campaign budget is very high.
In that case, you can reduce this threshold, e.g. to 5%. - Specify the minimum spend for the PMax campaign for it to be taken into account.
By default, it is 100.
Happy analysing!
Copy the following code:
/*
Copyright 2025 Krzysztof Bycina, www.LiveAds.pl
Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
*/
// Script configuration - you can adjust the parameters here
var config = {
// Whether to show detailed logs in the console
LOG: true,
// Analysis period - number of days back to be included in the report
DAYS_TO_ANALYSE: 45,
// URL to the Google Spreadsheet where the results will be saved
// IMPORTANT: You must have edit permissions for this spreadsheet!
SPREADSHEET_URL: "https://docs.google.com/spreadsheets/d/aaaaaaaaaaaaaaaaa/edit",
// Minimum percentage of spending on "other channels" (non-Shopping),
// above which the campaign will be included in the report
THRESHOLD_PERCENTAGE: 10,
// Minimum total spend on a campaign (in account currency)
// to be included in the analysis
MIN_TOTAL_SPEND: 100
};
//Script code - don't modify anything below this line
function main(){try{var a=SpreadsheetApp.openByUrl(config.SPREADSHEET_URL);_cs(a);_sb(a);
_pa();config.LOG&&Logger.log("Finished processing account")}catch(b)
{Logger.log("Error in main function: "+b)}}
function _cs(a){var b=a.getSheets();if(1<b.length)for(var c=0;c<b.length-1;c++)
a.deleteSheet(b[c]);a=a.getSheets()[0];a.clear();a.setName("Description")}
function _pa(){try{var a=_gdr(),b=AdsApp.currentAccount().getName(),c=_gcd(a);
if(0<Object.keys(c).length){_usd(c,a);var d=_pfr(c);if(d&&0<d.length){
var e=SpreadsheetApp.openByUrl(config.SPREADSHEET_URL);_sts(e,b,d);config.LOG&&
(Logger.log("Report saved for account: "+b),Logger.log("Number of campaigns exceeding threshold: "
+(d.length-1)))}else config.LOG&&Logger.log("No campaigns exceeding threshold for account: "+b)}
else config.LOG&&Logger.log("No campaigns with spending for account: "+b)}catch(f)
{Logger.log("Error in processAccount for "+b+": "+f)}}
function _gcd(a){var b={},c=AdsApp.report("SELECT campaign.id, campaign.name, "+
"metrics.cost_micros, metrics.conversions, metrics.conversions_value FROM campaign "+
"WHERE campaign.advertising_channel_type = 'PERFORMANCE_MAX' AND metrics.cost_micros > 0 "+
"AND segments.date BETWEEN '"+a.startDate+"' AND '"+a.endDate+"'"),d=c.rows();
for(;d.hasNext();){var e=d.next(),f=parseFloat(e["metrics.cost_micros"]||0)/1E6,
g=parseFloat(e["metrics.conversions"]||0),h=parseFloat(e["metrics.conversions_value"]||0),
k=0<f?h/f:0,l=0<g?f/g:0;b[e["campaign.id"]]={name:e["campaign.name"],totalSpend:f,
totalConversions:g,totalConversionValue:h,totalRoas:k,totalCpa:l,shoppingSpend:0,
shoppingConversions:0,shoppingConversionValue:0,shoppingRoas:0,shoppingCpa:0}}return b}
function _usd(a,b){if(0!==Object.keys(a).length){var c=AdsApp.report("SELECT campaign.id, "+
"metrics.cost_micros, metrics.conversions, metrics.conversions_value FROM shopping_performance_view "+
"WHERE campaign.id IN ('"+Object.keys(a).join("','")+"') AND segments.date BETWEEN '"+
b.startDate+"' AND '"+b.endDate+"'"),d=c.rows();for(;d.hasNext();){var e=d.next(),
f=e["campaign.id"],g=Math.abs(parseFloat(e["metrics.cost_micros"]||0)/1E6),
h=parseFloat(e["metrics.conversions"]||0),k=parseFloat(e["metrics.conversions_value"]||0);
a[f]&&(a[f].shoppingSpend+=g,a[f].shoppingConversions+=h,a[f].shoppingConversionValue+=k,
0<a[f].shoppingSpend&&(a[f].shoppingRoas=a[f].shoppingConversionValue/a[f].shoppingSpend),
0<a[f].shoppingConversions&&(a[f].shoppingCpa=a[f].shoppingSpend/a[f].shoppingConversions))}}}
function _pfr(a){var b=[],c={_t:0,_ct:0,_cv:0,_ss:0,_sc:0,_scv:0,_os:0,_oc:0,_ocv:0};
for(var d in a)if(a.hasOwnProperty(d)){var e=a[d];if(e.totalSpend>=config.MIN_TOTAL_SPEND){
var f=Math.min(e.shoppingSpend,e.totalSpend),g=e.totalSpend-f,h=g/e.totalSpend*100,
k=Math.max(0,e.totalConversions-e.shoppingConversions),
l=Math.max(0,e.totalConversionValue-e.shoppingConversionValue),m=0<g?l/g:0,n=0<k?g/k:0;
h>config.THRESHOLD_PERCENTAGE&&(c._t+=e.totalSpend,c._ct+=e.totalConversions,
c._cv+=e.totalConversionValue,c._ss+=f,c._sc+=e.shoppingConversions,c._scv+=e.shoppingConversionValue,
c._os+=g,c._oc+=k,c._ocv+=l,b.push([e.name,e.totalSpend,e.totalConversions,e.totalRoas,e.totalCpa,
f,f/e.totalSpend,e.shoppingConversions,e.shoppingRoas,e.shoppingCpa,g,g/e.totalSpend,k,m,n]))}}
if(0<b.length){var p=0<c._t?c._cv/c._t:0,q=0<c._ss?c._scv/c._ss:0,r=0<c._os?c._ocv/c._os:0,
t=0<c._ct?c._t/c._ct:0,u=0<c._sc?c._ss/c._sc:0,v=0<c._oc?c._os/c._oc:0;
b.push(["TOTAL",c._t,c._ct,p,t,c._ss,c._ss/c._t,c._sc,q,u,c._os,c._os/c._t,c._oc,r,v])}return b}
function _gdr(){var a=new Date;a.setDate(a.getDate()-1);var b=new Date;
b.setDate(b.getDate()-config.DAYS_TO_ANALYSE);return{startDate:_fd(b),endDate:_fd(a)}}
function _fd(a){return a.getFullYear()+(a.getMonth()+1).toString().padStart(2,"0")+
a.getDate().toString().padStart(2,"0")}
function _sb(a){var b=a.getSheetByName("Description");b.clear();b.setColumnWidth(1,50);b.setColumnWidth(2,600);b.setColumnWidth(3,50);var c=[["","Hello!",""],["","",""],["","My name is Krzysztof Bycina and I specialise in e-commerce and international campaigns.",""],["","",""],["","I hope this script will help you better understand Performance Max campaign spending and effectiveness.",""],["","",""],["","If you have any doubts about the overall effectiveness of your Google Ads account, I think we should talk. For new clients, I offer a free, no-obligation audit.",""],["","",""],["","I believe it's especially worth having a conversation if you spend tens of thousands or more per month on Google advertising.",""],["","",""],["","",""],["","",""],["","Contact:",""],["","",""],["","krzysztof@liveads.pl",""],["","",""],["","",""],["","",""],["","SCRIPT USAGE INSTRUCTIONS:",""],["","",""],["","1. CONFIGURATION - main script parameters can be adjusted in the \"config\" section at the beginning of the code",""],["","",""],["","2. ANALYSIS - the script analyses Performance Max campaigns and their spending distribution between Shopping and other formats",""],["","",""],["","3. ANALYSIS THRESHOLD - only campaigns where spending on \"other formats\" exceeds the % specified in THRESHOLD_PERCENTAGE are included",""],["","",""],["","4. COLOURING - the script automatically highlights cells with low efficiency of non-Shopping channels",""],["","",""],["","5. REPORT - results will be saved in the spreadsheet specified in SPREADSHEET_URL",""]],d=[[1,35],[3,42],[5,42],[7,63],[9,42],[20,30],[21,25],[22,40],[24,40],[26,40],[28,40],[30,40]],e=b.getRange(1,1,c.length,3);e.setValues(c);e.setFontFamily("Arial").setFontSize(12).setVerticalAlignment("middle");b.getRange(1,2).setFontWeight("bold").setFontSize(20);b.getRange(5,2).setFontWeight("bold");b.getRange(13,2).setFontWeight("bold").setFontSize(16);b.getRange(20,2).setFontWeight("bold").setFontSize(16);b.getRange(22,2,10,1).setFontSize(11);b.getRange(1,2,c.length,1).setWrap(!0);for(var f=0;f<d.length;f++)b.setRowHeight(d[f][0],d[f][1]);b.getRange(15,2,1,1).setFontColor("#1155cc");b.getRange(1,2,c.length,1).setHorizontalAlignment("left")}
function _sts(a,b,c){var d=a.insertSheet(b),e={main:"#4A86E8",shopping:"#93C47D",other:"#E69138"},
f=[["CAMPAIGN","","","","","PRODUCTS (SHOPPING)","","","","","OTHER CHANNELS","","","",""],
["Campaign name","Spend","Conversions","ROAS","CPA","Spend","Share %","Conversions","ROAS","CPA",
"Spend","Share %","Conversions","ROAS","CPA"]],g=d.getRange(1,1,2,f[0].length);g.setValues(f);
var h=d.getRange(1,1,1,f[0].length);h.setValues([f[0]]);d.getRange(1,1,1,5).merge().
setBackground(e.main);d.getRange(1,6,1,5).merge().setBackground(e.shopping);
d.getRange(1,11,1,5).merge().setBackground(e.other);var k=d.getRange(2,1,1,f[1].length);
k.setValues([f[1]]);d.getRange(2,1,1,5).setBackground(e.main);
d.getRange(2,6,1,5).setBackground(e.shopping);d.getRange(2,11,1,5).setBackground(e.other);
g.setFontWeight("bold").setHorizontalAlignment("center").setVerticalAlignment("middle").setFontColor("white");
if(0<c.length){d.setColumnWidth(1,300);for(var l=2;l<=f[1].length;l++)d.setColumnWidth(l,120);
for(l=3;l<=c.length+2;l++){var m=c[l-3];d.getRange(l,1).setValue(m[0]);
d.getRange(l,2).setValue(m[1]).setNumberFormat('#,##0.00');
d.getRange(l,3).setValue(m[2]).setNumberFormat("#,##0");
d.getRange(l,4).setValue(m[3]).setNumberFormat("#,##0.00");
d.getRange(l,5).setValue(m[4]).setNumberFormat("#,##0.00");
d.getRange(l,6).setValue(m[5]).setNumberFormat('#,##0.00');
d.getRange(l,7).setValue(m[6]).setNumberFormat("0.0%");
d.getRange(l,8).setValue(m[7]).setNumberFormat("#,##0");
d.getRange(l,9).setValue(m[8]).setNumberFormat("#,##0.00");
d.getRange(l,10).setValue(m[9]).setNumberFormat("#,##0.00");
d.getRange(l,11).setValue(m[10]).setNumberFormat('#,##0.00');
d.getRange(l,12).setValue(m[11]).setNumberFormat("0.0%");
d.getRange(l,13).setValue(m[12]).setNumberFormat("#,##0");
d.getRange(l,14).setValue(m[13]).setNumberFormat("#,##0.00");
d.getRange(l,15).setValue(m[14]).setNumberFormat("#,##0.00");
var n=m[5],p=m[8],q=m[9],r=m[10],t=m[12],u=m[13],v=m[14],w=Math.max(.1*n,100);
if(r>w&&0<n){var x=d.getRange(l,11,1,5);if(0===t)0<q&&r>q?x.setBackground("#FF0000").
setFontColor("white"):200<r&&x.setBackground("#FF0000").setFontColor("white");
else{var y=0<p?u/p:1,z=0<q?v/q:1;.5>y&&1.4<z?x.setBackground("#FF9999"):
.7>y&&1.3<z&&x.setBackground("#FFE5CC")}}}var A=c.length+2;
d.getRange(A,1,1,f[1].length).setBackground("#E6E6E6").setFontWeight("bold");
d.getRange(3,1,c.length,1).setHorizontalAlignment("left");
d.getRange(3,2,c.length,f[1].length-1).setHorizontalAlignment("center");
var B=d.getRange(1,1,c.length+2,f[1].length);
B.setBorder(!0,!0,!0,!0,!0,!0,"black",SpreadsheetApp.BorderStyle.SOLID);
var C=c.length+4;d.getRange(C,1,1,3).merge();
d.getRange(C,1).setValue("LEGEND:").setFontWeight("bold").setHorizontalAlignment("left");
d.getRange(C+1,1,1,f[1].length).merge();
d.getRange(C+1,1).setValue("⛔ Red background with white text - no conversions with spend >200 or >CPA in Shopping (if available)").
setHorizontalAlignment("left");d.getRange(C+2,1,1,f[1].length).merge();
d.getRange(C+2,1).setValue("🔴 Red background - significantly worse results (ROAS <50% and CPA >140% compared to Shopping)").
setHorizontalAlignment("left");d.getRange(C+3,1,1,f[1].length).merge();
d.getRange(C+3,1).setValue("🟡 Light orange background - worse results (ROAS <70% and CPA >130% compared to Shopping)").
setHorizontalAlignment("left")}d.setFrozenRows(2);d.getRange(1,1,c.length+2,1).setWrap(!0);
d.setRowHeight(1,30);d.setRowHeight(2,40)}