Declining Ad Groups script
Yudhi Pratama
var
SPREADSHEET_URL = 'ALREADY ADDED MY URL HERE';
function main() {
Logger.log('Using spreadsheet - %s.',
SPREADSHEET_URL);
var spreadsheet =
validateAndGetSpreadsheet(SPREADSHEET_URL);
spreadsheet.setSpreadsheetTimeZone(AdsApp.currentAccount().getTimeZone());
var sheet = spreadsheet.getSheets()[0];
spreadsheet.getRangeByName('account_id').setValue(
AdsApp.currentAccount().getCustomerId());
sheet.getRange(1, 2, 1, 1).setValue('Date');
sheet.getRange(1, 3, 1, 1).setValue(new Date());
sheet.getRange(7, 1, sheet.getMaxRows() - 7,
sheet.getMaxColumns()).clear();
var adGroupsIterator = AdsApp.adGroups()
.withCondition("Status =
'ENABLED'")
.withCondition("CampaignStatus =
'ENABLED'")
.forDateRange('LAST_7_DAYS')
.orderBy('Ctr ASC')
.withLimit(100)
.get();
var today = getDateStringInPast(0);
var oneWeekAgo = getDateStringInPast(7);
var twoWeeksAgo = getDateStringInPast(14);
var threeWeeksAgo = getDateStringInPast(21);
var reportRows = [];
while (adGroupsIterator.hasNext()) {
var adGroup = adGroupsIterator.next();
// Let's look at the trend of the ad group's
CTR.
var statsThreeWeeksAgo =
adGroup.getStatsFor(threeWeeksAgo, twoWeeksAgo);
var statsTwoWeeksAgo =
adGroup.getStatsFor(twoWeeksAgo, oneWeekAgo);
var statsLastWeek =
adGroup.getStatsFor(oneWeekAgo, today);
// Week over week, the ad group is declining -
record that!
if (statsLastWeek.getCtr() <
statsTwoWeeksAgo.getCtr() &&
statsTwoWeeksAgo.getCtr() <
statsThreeWeeksAgo.getCtr()) {
reportRows.push([adGroup.getCampaign().getName(), adGroup.getName(),
statsLastWeek.getCtr(),
statsLastWeek.getCost(),
statsTwoWeeksAgo.getCtr(), statsTwoWeeksAgo.getCost(),
statsThreeWeeksAgo.getCtr(), statsThreeWeeksAgo.getCost()]);
}
}
if (reportRows.length > 0) {
sheet.getRange(7, 2, reportRows.length,
8).setValues(reportRows);
sheet.getRange(7, 4, reportRows.length,
1).setNumberFormat('#0.00%');
sheet.getRange(7, 6, reportRows.length,
1).setNumberFormat('#0.00%');
sheet.getRange(7, 8, reportRows.length,
1).setNumberFormat('#0.00%');
sheet.getRange(7, 5, reportRows.length,
1).setNumberFormat('#,##0.00');
sheet.getRange(7, 7, reportRows.length,
1).setNumberFormat('#,##0.00');
sheet.getRange(7, 9, reportRows.length,
1).setNumberFormat('#,##0.00');
}
var email =
spreadsheet.getRangeByName('email').getValue();
if (email) {
var body = [];
body.push('The Ctr of the following ad groups
is declining over the ' +
'last three weeks.\n');
body.push('Full report at ' + SPREADSHEET_URL
+ '\n\n');
for (var i = 0; i < reportRows.length; i++)
{
body.push(reportRows[i][0] + ' > ' +
reportRows[i][1]);
body.push(' ' +
ctr(reportRows[i][6]) + ' > ' + ctr(reportRows[i][4]) +
' > ' +
ctr(reportRows[i][2]) + '\n');
}
MailApp.sendEmail(email, '' +
reportRows.length + ' ad groups
are declining in Google Ads account ' +
AdsApp.currentAccount().getCustomerId(), body.join('\n'));
}
}
function ctr(number) {
return parseInt(number * 10000) / 10000 + '%';
}
// Returns YYYYMMDD-formatted date.
function getDateStringInPast(numDays, date) {
date = date || new Date();
var MILLIS_PER_DAY = 1000 * 60 * 60 * 24;
var past = new Date(date.getTime() - numDays *
MILLIS_PER_DAY);
return getDateStringInTimeZone('yyyyMMdd', past);
}
function getDateStringInTimeZone(format, date, timeZone) {
date = date || new Date();
timeZone = timeZone ||
AdsApp.currentAccount().getTimeZone();
return Utilities.formatDate(date, timeZone, format);
}
/**
* Validates the provided spreadsheet URL
* to make sure that it's set up properly. Throws a
descriptive error message
* if validation fails.
*
* @param {string} spreadsheeturl The URL of the
spreadsheet to open.
* @return {Spreadsheet} The spreadsheet object itself,
fetched from the URL.
* @throws {Error} If the spreadsheet URL hasn't been
set
*/
function validateAndGetSpreadsheet(spreadsheeturl) {
if (spreadsheeturl == 'YOUR_SPREADSHEET_URL') {
throw new Error('Please specify a valid
Spreadsheet URL. You can find' +
' a link to a template in the
associated guide for this script.');
}
return SpreadsheetApp.openByUrl(spreadsheeturl);
}
The error logs are:
6/10/2024 2:54:52 PM
Exception:
Invalid argument:url
at validateAndGetSpreadsheet (Code:106:25)
at main (Code:5:21)
at Object.<anonymous> (adsapp_compiled:20505:54)
Privileged/Confidential Information may be contained in this
message. If you are
not the addressee indicated in this message (or responsible for delivery of the
message to such person), you may not copy or deliver this message to anyone. In
such case, you should destroy this message and kindly notify the sender by
reply
email. Please advise immediately if you or your employer does not consent to
email
for messages of this kind. Opinions, conclusions and other information in this
message that do not relate to the official business of Group M Worldwide LLC
and/or
other members of the GroupM group of companies shall be understood as neither
given
nor endorsed by it. GroupM is the global media investment management arm of
WPP.
For more information on our business ethical standards and Corporate
Responsibility
policies please refer to WPP's website at http://www.wpp.com/WPP/About/
Google Ads Scripts Forum
Hi,
Thank you for reaching out to the Google Ads Scripts support team.
I would like to inform you that the error “Invalid argument:url” indicates that the URL you have entered might not be a correct one. Kindly cross check if there are any typos in the URL. Also, make sure you are having an “Editor” access to the spreadsheet.
I hope this helps. If you still face any issues, get back to us with the below details
- Google Ads account ID/CID
- Name of the affected script
- Shareable spreadsheet link that you are using in the script
You can share the requested details via Reply privately to the author option or a direct private reply to this email.
Thanks,
Google Ads Scripts Team
Nils Rooijmans
Hope this helps,
Nils Rooijmans
https://nilsrooijmans.com
See my Google Ads Scripts FAQ to avoid the same mistakes I made: https://nilsrooijmans.com/google-ads-scripts-faq/
Google Ads Scripts Forum Advisor
Hi,
Thank you Nils for your suggestion.
You can refer to the solution provided by Nils Rooijmans and get back to us if you still face any issues.
Thanks,
 |
Google Ads Scripts Team |
