Google Ads Script P.max insight

federica vitzizzai

unread,

Mar 12, 2024, 9:49:37 AM3/12/24

to Google Ads Scripts Forum

Hello, I've just inserted this script but upon previewing, I immediately encountered an error. What should I modify?

Script:

/**
* PMax Brand Traffic Analyzer . * ･｡ﾟ☆━੧[ ✪ ᗜ ✪ ]⊃
*
* Overview: This Google Ads script fetches search term categories from your Consumer Spotlight.
* The script is configured with an output Google Sheets url and term(s) that identify your
* branded search queries (usually a brand/shop name). The script will compare your data against your
* branded term(s) and determine how much of your conversions, conv. value, clicks & impressions
* came from search categories that are branded vs non-branded. The analysis groups data in specified
* periods (for example, 7 days) and looks several periods back in time (for example, 6 periods).
* In the end you will see the output charted in the Google Sheets url you provided, showing the branded
* search category metrics over time compared to the numbers from the non-branded search categories.
* This is especially valuable for the PERFORMANCE_MAX advertising channel type, which doesn’t have
* many alternatives for reporting on that. In the Preferences section of the script you can adjust and check
* other channels too (like SHOPPING or SEARCH). You can also adjust the grouping periods and the lookback window,
* or specify the campaigns analyzed.

*
* NOTE 1: The Consumer Spotlight search terms report is a representation of impressions, clicks, and conversions
* that happened from a known search category. Search terms that don't have an identifiable search category are
* grouped together as "Uncategorized search terms" and this report does not consider them.
*
* NOTE 2: The Consumer Spotlight search terms report does not report cost-based metrics like cost, CPC, or ROAS.
* “By Time” metrics such as Conv. value (by conv. time) are also not available. Generally the report is limited
* in available metrics and dimensions.
*
* Author: smec [smarter-ecommerce.com]
* (c) Smarter Ecommerce GmbH. All rights reserved.
* License: MIT
*/

/** ============================== Attention ===================================
* This script creates and writes data into a new spreadsheet even when run in preview mode.
*/

/** ============================ Configuration ============================== */

const SHEET_URL = 'https://docs.google.com/spreadsheets/u/3/d/1yHj9CilZbgGshPFyVZkvjAr9nNtQPQvYfvC8IYJ9nGc/copy';
const BRANDED_TERM_VARIANTS = ['smec','smarter ecommerce']; //Your branded term(s) - usually brand/shop name and variants

/* ============================== Preferences (Calculation) =============================== */

const ADVERTISING_CHANNEL_TYPE = "PERFORMANCE_MAX"; //DISCOVERY;DISPLAY;HOTEL;LOCAL;LOCAL_SERVICES;MULTI_CHANNEL;PERFORMANCE_MAX;SEARCH;SHOPPING;SMART;TRAVEL;UNKNOWN;UNSPECIFIED;VIDEO
const CAMPAIGN_COUNT_LIMIT = 0; //0 = No limit; If your account is big, start with limit like 3 to include only the top 3 campaigns by number of conversions
const CHECK_SPECIFIC_CAMPAIGN_IDS = []; //[] = Check all campaigns; ["19191919191","1818181818","171717171717"] = a comma separated campaign IDs (one or more) to include in the check. The specified campaign(s) must match the ADVERTISING_CHANNEL_TYPE setting.
const LOOKUP_INTERVALS_IN_DAYS = 30; //7 for WEEKLY | 14 for BI-WEEKLY | 30 for MONTHLY aggregations
const COUNT_OF_INTERVALS = 6; //The more intervals, the far you look back in time, but also computation becomes heavier

/* ============================== Preferences (Presentation) =============================== */

const CHART_TYPE = "AREA"; // AREA | STEPPED_AREA | COLUMN // Pick "COLUMN" for less data points in order to get more accurate scientific representation of the aggregated periods.
const SHOW_DATA_LABELS = false; // true or false

/* ============================== Debug modes =============================== */

const DEBUG_MODE = 0; //0 = Off; 1= On; Limit the number of campaigns analyzed by CAMPAIGN_COUNT_LIMIT before you enable the Debug Mode. This mode will create debug sheets - 2 for each interval. One will contain which campaigns were taken into consideration. The other - the aggregated search term categories from the consumer spotlight.
const DEBUG_CREATE_SHEET_PER_CAMPAIGN_FOR_LAST_INTERVAL = 0; //CAUTION! Enabling this will create many many sheets. 0 = Off; 1 = On; It will create one sheet PER CAMPAIGN for the last interval only. Each of the sheets will contain the search categories from consumer spotlight.

/* =============================== Execution ================================ */

let spreadsheetDocument = SpreadsheetApp.openByUrl(https://docs.google.com/spreadsheets/d/1blImayij9dpULUrjpf0ugDahJRi5S2nYQd_BQ2T3V7s/edit#gid=1727345785);
spreadsheetDocument.rename('Search Term Insights by smec v1.0');

function main() {
Logger.log(`Welcome to smec! Let's analyse your Consumer Spotlight search terms...`);

let resultData = [];

const timeFrames = prepareDateFilter();
for (let k = 0; k < timeFrames.length; k++) {

let dateFilter = `segments.date BETWEEN '${formatDateForGAQL(timeFrames[k].start)}' AND '${formatDateForGAQL(timeFrames[k].end)}'`;

let specificCampaignIdCheck = ``;
if (CHECK_SPECIFIC_CAMPAIGN_IDS.length > 0) {
let campaignIdList = `"${CHECK_SPECIFIC_CAMPAIGN_IDS[0]}"`;
CHECK_SPECIFIC_CAMPAIGN_IDS.forEach(function(campaignId, index){
if (index>0) {
campaignIdList += `,"${campaignId}"`;
}
});
specificCampaignIdCheck += ` AND campaign.id IN (${campaignIdList}) `;
}

let campaignQuery = `
SELECT
campaign.id,
campaign.name,
metrics.clicks,
metrics.impressions,
metrics.conversions,
metrics.conversions_value
FROM campaign
WHERE
campaign.status != 'REMOVED'
AND campaign.advertising_channel_type = "${ADVERTISING_CHANNEL_TYPE}"
AND metrics.impressions > 0
AND metrics.conversions > 0
${specificCampaignIdCheck}
AND ${dateFilter}
ORDER BY metrics.conversions DESC
`;

if (CAMPAIGN_COUNT_LIMIT > 0) {
campaignQuery += ` LIMIT ${CAMPAIGN_COUNT_LIMIT}`;
}

let campaignsData = [];
let searchTermCategoriesData = {};

let campaignIdsQuery = AdsApp.report(campaignQuery);
let rows = campaignIdsQuery.rows();

let campaignsSheet;
if (DEBUG_MODE) {
campaignsSheet = getOrInsertSheetByName('campaignsAnalyzed_'+formatDateForGAQL(timeFrames[k].start), spreadsheetDocument);
campaignsSheet.appendRow(['Campaign Name','Campaign Id','Clicks','Impressions','Conversions','Conversion Value']);
}
while (rows.hasNext()) {
let row = rows.next();
campaignsData.push({id: row['campaign.id'], name: row['campaign.name'], conversions: row['metrics.conversions']});
if (DEBUG_MODE) {
campaignsSheet.appendRow([row['campaign.name'], row['campaign.id'], row['metrics.clicks'], row['metrics.impressions'], row['metrics.conversions'], row['metrics.conversions_value']]);
}
}

let resultDataRow = {};
resultDataRow["start"] = formatDateForGAQL(timeFrames[k].start);
resultDataRow["end"] = formatDateForGAQL(timeFrames[k].end);
resultDataRow["conversions_branded_sum"] = 0;
resultDataRow["conversions_nonbranded_sum"] = 0;
resultDataRow["conv_value_branded_sum"] = 0;
resultDataRow["conv_value_nonbranded_sum"] = 0;
resultDataRow["clicks_branded_sum"] = 0;
resultDataRow["clicks_nonbranded_sum"] = 0;
resultDataRow["impressions_branded_sum"] = 0;
resultDataRow["impressions_nonbranded_sum"] = 0;

for (let i = 0; i < campaignsData.length; i++) {
let campaignId = campaignsData[i].id;

let STInsightsQuery = AdsApp.report(
`
SELECT
campaign_search_term_insight.category_label,
metrics.clicks,
metrics.impressions,
metrics.conversions,
metrics.conversions_value
FROM
campaign_search_term_insight
WHERE
campaign_search_term_insight.campaign_id = '${campaignId}'
AND ${dateFilter}
ORDER BY
metrics.conversions DESC
`
);

let STInsightsRows = STInsightsQuery.rows();

while (STInsightsRows.hasNext()) {
let STIrow = STInsightsRows.next();

if (STIrow['campaign_search_term_insight.category_label']) { //Take into consideration only rows that have a search term/category; excludes unknowns
let hasMatch = false;
BRANDED_TERM_VARIANTS.forEach(function(brandedTerm){
if (STIrow['campaign_search_term_insight.category_label'].toLowerCase().indexOf(brandedTerm.toLowerCase()) >= 0) {
hasMatch = true;
}
});
if (hasMatch) {
resultDataRow["conversions_branded_sum"] += +Number(STIrow['metrics.conversions']).toFixed(1);
resultDataRow["conv_value_branded_sum"] += +Number(STIrow['metrics.conversions_value']).toFixed(1);
resultDataRow["clicks_branded_sum"] += +Number(STIrow['metrics.clicks']).toFixed(1);
resultDataRow["impressions_branded_sum"] += +Number(STIrow['metrics.impressions']).toFixed(1);
} else {
resultDataRow["conversions_nonbranded_sum"] += +Number(STIrow['metrics.conversions']).toFixed(1);
resultDataRow["conv_value_nonbranded_sum"] += +Number(STIrow['metrics.conversions_value']).toFixed(1);
resultDataRow["clicks_nonbranded_sum"] += +Number(STIrow['metrics.clicks']).toFixed(1);
resultDataRow["impressions_nonbranded_sum"] += +Number(STIrow['metrics.impressions']).toFixed(1);
}
}

if (DEBUG_MODE) {
if (searchTermCategoriesData[STIrow['campaign_search_term_insight.category_label']]) {
searchTermCategoriesData[STIrow['campaign_search_term_insight.category_label']]['conversions'] += Number(STIrow['metrics.conversions']);
searchTermCategoriesData[STIrow['campaign_search_term_insight.category_label']]['impressions'] += Number(STIrow['metrics.impressions']);
searchTermCategoriesData[STIrow['campaign_search_term_insight.category_label']]['clicks'] += Number(STIrow['metrics.clicks']);
searchTermCategoriesData[STIrow['campaign_search_term_insight.category_label']]['conversions_value'] += Number(STIrow['metrics.conversions_value']);
} else {
if (STIrow['campaign_search_term_insight.category_label']) {
searchTermCategoriesData[STIrow['campaign_search_term_insight.category_label']] = {};
searchTermCategoriesData[STIrow['campaign_search_term_insight.category_label']]['conversions'] = Number(STIrow['metrics.conversions']);
searchTermCategoriesData[STIrow['campaign_search_term_insight.category_label']]['impressions'] = Number(STIrow['metrics.impressions']);
searchTermCategoriesData[STIrow['campaign_search_term_insight.category_label']]['clicks'] = Number(STIrow['metrics.clicks']);
searchTermCategoriesData[STIrow['campaign_search_term_insight.category_label']]['conversions_value'] = Number(STIrow['metrics.conversions_value']);
}
}
}
}

if (DEBUG_CREATE_SHEET_PER_CAMPAIGN_FOR_LAST_INTERVAL) {
let sheet = getOrInsertSheetByName(campaignId, spreadsheetDocument);
STInsightsQuery.exportToSheet(sheet);
}
}

resultData.push(resultDataRow);

let termsAgregatedPerformanceSheet;
if (DEBUG_MODE) {
termsAgregatedPerformanceSheet = getOrInsertSheetByName('termsAggregatedTotal_'+formatDateForGAQL(timeFrames[k].start), spreadsheetDocument);
termsAgregatedPerformanceSheet.appendRow(['Search Term Insights Category','Clicks','Conversions']);

for (const objKey in searchTermCategoriesData) {
if (searchTermCategoriesData.hasOwnProperty(objKey)) {
if (DEBUG_MODE) {
termsAgregatedPerformanceSheet.appendRow([objKey, searchTermCategoriesData[objKey]['clicks'], searchTermCategoriesData[objKey]['conversions']]);
}
}
}
}
}

//Output end result
let outputSheet = getOrInsertSheetByName("End Result", spreadsheetDocument);

outputSheet.appendRow(["Timeframe start date","Timeframe end date","branded conversions","non-branded conversions","branded conv. value","non-branded conv. value", "branded clicks","non-branded clicks", "branded impressions","non-branded impressions", "ratioBrandedConversions", "ratioBrandedConvValue", "ratioBrandedClicks", "ratioBrandedImpressions" ]);

for (let n = 0; n < resultData.length; n++) {
let ratioBrandConv = resultData[n]['conversions_branded_sum'] / (resultData[n]['conversions_branded_sum'] + resultData[n]['conversions_nonbranded_sum']);
let ratioBrandConvValue = resultData[n]['conv_value_branded_sum'] / (resultData[n]['conv_value_branded_sum'] + resultData[n]['conv_value_nonbranded_sum']);
let ratioBrandClicks = resultData[n]['clicks_branded_sum'] / (resultData[n]['clicks_branded_sum'] + resultData[n]['clicks_nonbranded_sum']);
let ratioBrandImpr = resultData[n]['impressions_branded_sum'] / (resultData[n]['impressions_branded_sum'] + resultData[n]['impressions_nonbranded_sum']);

outputSheet.appendRow([resultData[n]['start'], resultData[n]['end'], resultData[n]['conversions_branded_sum'], resultData[n]['conversions_nonbranded_sum'], resultData[n]['conv_value_branded_sum'], resultData[n]['conv_value_nonbranded_sum'], resultData[n]['clicks_branded_sum'], resultData[n]['clicks_nonbranded_sum'], resultData[n]['impressions_branded_sum'], resultData[n]['impressions_nonbranded_sum'], (ratioBrandConv ? ratioBrandConv:0), (ratioBrandConvValue ? ratioBrandConvValue:0), (ratioBrandClicks ? ratioBrandClicks:0), (ratioBrandImpr ? ratioBrandImpr:0) ]);
}

buildSmecMainChart(outputSheet, "Branded vs non-branded conversions over time", "A1:A"+(1+COUNT_OF_INTERVALS), "C1:D"+(1+COUNT_OF_INTERVALS), 10, 1, 'branded conversions', 'non-branded conversions');
buildSmecMainChart(outputSheet, "Branded vs non-branded conv. value over time", "A1:A"+(1+COUNT_OF_INTERVALS), "E1:F"+(1+COUNT_OF_INTERVALS), 30, 1, 'branded conv. value', 'non-branded conv. value');
buildSmecMainChart(outputSheet, "Branded vs non-branded clicks over time", "A1:A"+(1+COUNT_OF_INTERVALS), "G1:H"+(1+COUNT_OF_INTERVALS), 50, 1, 'branded clicks', 'non-branded clicks');
buildSmecMainChart(outputSheet, "Branded vs non-branded impressions over time", "A1:A"+(1+COUNT_OF_INTERVALS), "I1:J"+(1+COUNT_OF_INTERVALS), 70, 1, 'branded impressions', 'non-branded impressions');

buildSmecMoreInfoChart(outputSheet, "Share of branded conversions over time", "A1:A"+(1+COUNT_OF_INTERVALS), "K1:K"+(1+COUNT_OF_INTERVALS), 10, 8, 'branded conversions', true);
buildSmecMoreInfoChart(outputSheet, "Share of branded conv. value over time", "A1:A"+(1+COUNT_OF_INTERVALS), "L1:L"+(1+COUNT_OF_INTERVALS), 30, 8, 'branded conv. value', true);
buildSmecMoreInfoChart(outputSheet, "Share of branded clicks over time", "A1:A"+(1+COUNT_OF_INTERVALS), "M1:M"+(1+COUNT_OF_INTERVALS), 50, 8, 'branded clicks', true);
buildSmecMoreInfoChart(outputSheet, "Share of branded impressions over time", "A1:A"+(1+COUNT_OF_INTERVALS), "N1:N"+(1+COUNT_OF_INTERVALS), 70, 8, 'branded impressions', true);

deleteSheet1(spreadsheetDocument);
}

function getOrInsertSheetByName(sheetName, spreadsheetDocument) {
let resultSheet = spreadsheetDocument.getSheetByName(sheetName);
if (!resultSheet) {
resultSheet = spreadsheetDocument.insertSheet(sheetName);
} else {
resultSheet.clear();
}
return resultSheet;
}

function formatDateForGAQL(date) {
let month = '' + (date.getMonth() + 1);
let day = '' + date.getDate();
let year = date.getFullYear();

if (month.length < 2)
month = '0' + month;
if (day.length < 2)
day = '0' + day;

return [year, month, day].join('-');
}

function prepareDateFilter() {
const date_filter = [];
const today = new Date();

const oneDayInMilliseconds = 24 * 60 * 60 * 1000;
const nDaysInMilliseconds = LOOKUP_INTERVALS_IN_DAYS * oneDayInMilliseconds;

for (let i = 1; i <= COUNT_OF_INTERVALS; i++) {
const end = new Date();
end.setTime(today.getTime() - nDaysInMilliseconds * (i - 1));
const start = new Date();
start.setTime(end.getTime() - nDaysInMilliseconds);
end.setTime(end.getTime() - oneDayInMilliseconds);

date_filter.push({"start": start, "end": end});
}

const reversedArray = [...date_filter].reverse();
return reversedArray;
}
function buildSmecMainChart(sheet, title, range1, range2, xPos, yPos, label1, label2) {
let seriesConfig = {
0: {labelInLegend: label1},
1: {labelInLegend: label2}
};
if (SHOW_DATA_LABELS) {
seriesConfig[0]['dataLabel'] = 'value';
seriesConfig[1]['dataLabel'] = 'value';
}

let chartBuilder = sheet.newChart().setChartType(Charts.ChartType[CHART_TYPE]);

chartBuilder.addRange(sheet.getRange(range1))
if (range2) {
chartBuilder.addRange(sheet.getRange(range2))
}

chart = chartBuilder.setOption("title", title)
.setOption("subtitle", 'Data: terms from identifiable search categories in "Consumer Spotlight"')
.setOption("hAxis.title", "Start dates of the aggregated time intervals")
.setOption("isStacked", true)
.setOption("pointSize", 5)
.setOption("series", seriesConfig)
.setOption("colors", ['#ebbf52','#4d40d2'])
.setOption("width", 700)
.setPosition(xPos, yPos, 0, 0)
.build();

sheet.insertChart(chart);
}
function buildSmecMoreInfoChart(sheet, title, range1, range2, xPos, yPos, label1, range2_in_percent) {
let seriesConfig = {
0: {labelInLegend: label1}
};
if (SHOW_DATA_LABELS) {
seriesConfig[0]['dataLabel'] = 'value';
}

let chartBuilder = sheet.newChart().setChartType(Charts.ChartType[CHART_TYPE]);

chartBuilder.addRange(sheet.getRange(range1));
if (range2) {
if (range2_in_percent) {
chartBuilder.addRange(sheet.getRange(range2).setNumberFormat("0.0%"));
} else {
chartBuilder.addRange(sheet.getRange(range2));
}
}

chart = chartBuilder.setOption("title", title)
.setOption("subtitle", 'Data: terms from identifiable search categories in "Consumer Spotlight"')
.setOption("hAxis.title", "Start dates of the aggregated time intervals")
.setOption("pointSize", 5)
.setOption("series", seriesConfig)
.setOption("colors", ['#ebbf52','#4d40d2'])
.setOption("width", 700)
.setPosition(xPos, yPos, 0, 0)
.build();

sheet.insertChart(chart);
}
function deleteSheet1(spreadsheetDocument) {
let sheet1 = spreadsheetDocument.getSheetByName("Sheet1");

if (sheet1 != null) {
spreadsheetDocument.deleteSheet(sheet1);
}
}

https://searchengineland.com/how-to-track-performance-max-campaigns-with-google-apps-script-429278
https://docs.google.com/spreadsheets/d/1aGOBOLNUjEIwwlYuS0D3fcIimRnzGdLSJxVd9rHOQ0E/copy

Error:
SyntaxError: missing ) after argument list (line 61)

Google Ads Scripts Forum

unread,

Mar 12, 2024, 3:15:37 PM3/12/24

to Google Ads Scripts Forum

Hi,

Thank you for reaching out to the Google Ads Scripts support team.

By reviewing your concern, I understand that you are getting an error "SyntaxError: missing ) after argument list (line 61)" while previewing the script.

In order to fix this error, I would suggest you to place the spreadsheet url in between the single inverted commas in line no.61. You can use the below line for reference.

let spreadsheetDocument = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/1blImayij9dpULUrjpf0ugDahJRi5S2nYQd_BQ2T3V7s/edit#gid=1727345785');

If the issue still persists, kindly provide us with the following details to further investigate your issue:

Google Ads account ID / CID
Name of the script
Provide a shareable link to the spreadsheet. You can follow this guide to share a file publicly.

You can send the details via Reply privately to the author option or a direct private reply to this email.

Regards

Google Ads Scripts support team.

federica vitzizzai

unread,

Mar 14, 2024, 6:25:22 PM3/14/24

to Google Ads Scripts Forum

I executed the script without errors.
But where can I see the data? In the linked spreadsheet? In the latter, I still see the original data in most sheets even though I have refreshed.

Thank you

Reply all

Reply to author

Forward