The sale countdown script demonstrates how to update your ad parameters hourly to count down to a single sale event. The sales countdown calendar extends this concept to multiple events by integrating a calendar to the script, and using ad customizers.
Overview
Suppose you own a kitchen supply store that offers many brands of stand mixers, two of which you've set up Google Ads campaigns for.
| Product Name | Campaign Name |
|---|---|
| ProWhip Stand Mixer | prowhip |
| WhipMaster Stand Mixer | whipmaster |
Assume you have two upcoming promotions for these mixers:
| Product Name | Start date | End Date |
|---|---|---|
| ProWhip Stand Mixer | 23 Dec 2014 | 26 Dec 2014 |
| WhipMaster Stand Mixer | 23 Dec 2014 | 29 Dec 2014 |
When someone searches for your products on Google.com during the sales period (say, on 24 Dec 2014), you’d like them to see the following ads:
| Product searched | Ad shown |
|---|---|
| ProWhip Stand Mixer |  |
| WhipMaster Stand Mixer |  |
This script reads a list of events from a calendar, sets up an ad customizer source using that data, finds the specific campaign for each event, and applies the correct ad customizer item to each of these campaigns.
Setting up your campaigns
You need to set up your ads to use the COUNTDOWN function of
ad customizers. For
this example, the ads have the following setup:
| Campaign Name | Example Ad |
|---|---|
| prowhip |
ProWhip Stand Mixer www.example.com 5 quart stand mixer. $199 - sale ends in {=COUNTDOWN(SalesCountdown.EndDate)}.
|
| WhipMaster Stand Mixer |
WhipMaster Pro Stand Mixer www.example.com 6 quart tilt-head stand mixer. $219 - sale ends in {=COUNTDOWN(SalesCountdown.EndDate)}.
|
Setting up a countdown calendar
Setting up your countdown calendar involves 3 steps:
1. Create a countdown calendar
You need to create a new Google Calendar by opening the dropdown menu next to My Calendars and selecting the Create new calendar option.
Next, enter the calendar name and click the Create Calendar button to create the new calendar.
2. Get your calendar ID
Open the calendar settings page by opening the dropdown menu next to the Sales countdown calendar and select Calendar Settings.
Scroll all the way to the bottom and copy the calendar ID. You will need this value later, when setting up your script.
3. Create calendar entries
You need to create a calendar entry for each countdown event. You can do this by clicking anywhere on the calendar grid. This brings up the page to create a new calendar entry.
Keep the following things in mind when creating your event:
- Uncheck the All day option and pick appropriate start and end times when creating your event.
- Make sure the event is created on the correct calendar.
- Make sure you give a meaningful name to the calendar event title. The script uses this value to uniquely identify a sales event.
- In the description field, enter
Campaign=CAMPAIGN_NAME, whereCAMPAIGN_NAMEis the promotional campaign associated with your sales event.
Click the Save button to create your event.
How does the script work?
The script starts by reading all events in the calendar using the Calendar API.
function listAllEvents() {
var calendarEvents = Calendar.Events.list(calendarId, {
singleEvents: true,
orderBy: 'startTime'
});
var retval = [];
for (var i = 0; i < calendarEvents.items.length; i++) {
var event = calendarEvents.items[i];
if (event.start.date || event.end.date) {
throw ('All day events are not supported. Set a start and end time.');
}
var startDate = parseDate(event.start.dateTime);
var endDate = parseDate(event.end.dateTime);
retval.push({
'Name': event.summary,
'Campaign': getCampaignName(event.description),
'StartDate': startDate,
'EndDate': endDate
});
}
return retval;
}
Next, it sets up an ad customizer source if required.
function createAdCustomizerSource() {
var operation = AdsApp.newAdCustomizerSourceBuilder()
.addAttribute('StartDate', 'date')
.addAttribute('EndDate', 'date')
.addAttribute('EventName', 'text')
.withName(CUSTOMIZER_NAME)
.build();
return operation.getResult();
}
Then an ad customizer entry is created for each calendar entry:
function addCustomizerItem(adCustomizerSource, eventName, startDate, endDate,
campaignName) {
var operation = adCustomizerSource.adCustomizerItemBuilder()
.withAttributeValue('StartDate', formatDate(startDate))
.withAttributeValue('EndDate', formatDate(endDate))
.withAttributeValue('EventName', eventName)
.withTargetCampaign(campaignName)
.build();
return operation.getResult();
}
If you have set up your campaigns correctly to use ad customizers, your campaign will start serving with the appropriate sales countdown.
Set up your script
- Make sure you enable Calendar API from the Advanced APIs dialog. See instructions
- At the top of the script replace the value of the
CALENDAR_IDvariable with the ID of the calendar you created earlier. - At the top of the script replace the value of the
EMAILandEMAIL_CCvariables with the email addresses of the people who should be notified when the sales countdown calendar script syncs an event from the event calendar. Leave them empty to skip sending emails.
- In Google Ads, navigate to Scripts
- Create a new script and give it a name; authorize the script
- Copy-paste source code into the script (see below)
- Save the script, click "Close".
- If necessary, schedule the script
Scheduling
Schedule the script to run hourly. Whenever the script runs, it will detect any changes made to events on your calendar and update your account accordingly.
Source code
// Copyright 2015, Google Inc. All Rights Reserved.
//
// 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.
/**
* @name Sales Countdown Calendar
*
* @overview The Sales Countdown Calendar script allows you to update ads in
* your account to count down to multiple sales events by integrating a
* calendar to the script, and using ad customizers. See
* https://developers.google.com/google-ads/scripts/docs/solutions/sales-countdown-calendar
* for more details.
*
* @author Google Ads Scripts Team [adwords-scripts@googlegroups.com]
*
* @version 1.0
*
* @changelog
* - version 1.0
* - Released initial version.
*/
// Set this value to your calendar's ID.
var CALENDAR_ID = 'INSERT_CALENDAR_ID';
// Name of the ad customizer source created by this script. If you change this
// value, you should also change your ads to refer to the new customizer name.
var CUSTOMIZER_NAME = 'SalesCountdown';
// Set this value to receive an email whenever the script updates ad
// customizers in your account.
var EMAIL = 'email@example.com';
// Set this value to the list of users who should get updates whenever the
// script updates ad customizers in your account.
var EMAIL_CC = ['email@example.com', 'email@example.com'];
function main() {
var events = listAllEvents();
var now = new Date();
var customizer = getAdCustomizerSource();
if (!customizer) {
customizer = createAdCustomizerSource();
}
var logs = [];
for (var i = 0; i < events.length; i++) {
var event = events[i];
if (event.Campaign) {
var customizerItem = getCustomizerItem(customizer, event.Name);
if (customizerItem) {
if (event.EndDate > now) {
if (customizerItem.getAttributeValue('EndDate') !=
formatDate(event.EndDate) ||
customizerItem.getTargetCampaignName() != event.Campaign) {
setCustomizerItem(customizerItem, event.StartDate,
event.EndDate, event.Campaign);
logs.push(Utilities.formatString('Updated countdown entry ' +
'for %s. New end date: %s, associated campaign: %s.',
event.Name, event.EndDate, event.Campaign));
}
} else {
removeCustomizerItem(customizerItem);
logs.push(Utilities.formatString('Removed countdown entry for ' +
'expired event: %s.', event.Name));
}
} else {
if (event.EndDate > now) {
addCustomizerItem(customizer, event.Name, event.StartDate,
event.EndDate, event.Campaign);
logs.push(Utilities.formatString('Added countdown entry for %s. ' +
'End date: %s, associated campaign: %s.', event.Name,
event.EndDate, event.Campaign));
}
}
}
}
if (logs.length > 0) {
var htmlBody = [];
htmlBody.push('The Sales countdown calendar script made the following ' +
'changes to Customer ID: ' +
AdsApp.currentAccount().getCustomerId() +
'<br>');
htmlBody.push('<ul>');
for (var i = 0; i < logs.length; i++) {
htmlBody.push('<li>' + logs[i] + '</li>' + '<br>');
}
htmlBody.push('</ul>');
MailApp.sendEmail({
to: EMAIL,
cc: EMAIL_CC.join(','),
subject: 'Sales countdown calendar',
htmlBody: htmlBody.join('\n')
});
}
}
/**
* Gets the campaign name associated with an event.
*
* @param {string} eventDescription The event description.
*
* @return {?(string )} Name of the campaign associated with this event,
* or null if the event description doesn't specify one.
*/
function getCampaignName(eventDescription) {
var parts = eventDescription.split('\n');
for (var i = 0; i < parts.length; i++) {
var subparts = parts[i].split('=');
if (subparts.length == 2 && subparts[0].toLowerCase() == 'campaign') {
return subparts[1];
}
}
return null;
}
/**
* Removes an ad customizer item.
*
* @param {AdCustomizerItem} customizerItem The item to be removed.
*/
function removeCustomizerItem(customizerItem) {
customizerItem.remove();
}
/**
* Retrieves an ad customizer item by its event name.
*
* @param {AdCustomizerSource} adCustomizerSource The ad customizer source to
* search for the item.
* @param {string} eventName The event name.
*
* @return {?(AdCustomizerItem )} The ad customizer item if it exists,
* null otherwise.
*/
function getCustomizerItem(adCustomizerSource, eventName) {
var items = adCustomizerSource.items().get();
while (items.hasNext()) {
var item = items.next();
if (item.getAttributeValue('EventName') == eventName) {
return item;
}
}
return null;
}
/**
* Updates an ad customizer item with new event details.
*
* @param {AdCustomizerItem} customizerItem The ad customizer item to
* be updated.
* @param {Date} startDate The event start date.
* @param {Date} endDate The event end date.
* @param {string} campaignName The name of the campaign this event is
* associated with.
*/
function setCustomizerItem(customizerItem, startDate, endDate, campaignName) {
customizerItem.setAttributeValue('StartDate', formatDate(startDate));
customizerItem.setAttributeValue('EndDate', formatDate(endDate));
customizerItem.setTargetCampaign(campaignName);
}
/**
* Adds an ad customizer item.
*
* @param {AdCustomizerSource} adCustomizerSource The ad customizer source to
* which the new item is added.
* @param {string} eventName The event name.
* @param {Date} startDate The event start date.
* @param {Date} endDate The event end date.
* @param {string} campaignName The name of the campaign this event is
* associated with.
*
* @return {AdCustomizerItem} The ad customizer item.
*/
function addCustomizerItem(adCustomizerSource, eventName, startDate, endDate,
campaignName) {
var operation = adCustomizerSource.adCustomizerItemBuilder()
.withAttributeValue('StartDate', formatDate(startDate))
.withAttributeValue('EndDate', formatDate(endDate))
.withAttributeValue('EventName', eventName)
.withTargetCampaign(campaignName)
.build();
return operation.getResult();
}
/**
* Formats a date for creating a ad customizer item.
*
* @param {string} date The date to be formatted.
*
* @return {string} The formatted date.
*/
function formatDate(date) {
return Utilities.formatDate(date, AdsApp.currentAccount().getTimeZone(),
'yyyyMMdd HHmmss');
}
/**
* Create a new ad customizer source for this script.
*
* @return {(AdCustomizerSource)} The new ad customizer source.
*/
function createAdCustomizerSource() {
var operation = AdsApp.newAdCustomizerSourceBuilder()
.addAttribute('StartDate', 'date')
.addAttribute('EndDate', 'date')
.addAttribute('EventName', 'text')
.withName(CUSTOMIZER_NAME)
.build();
return operation.getResult();
}
/**
* Gets the ad customizer source for this script.
*
* @return {?(AdCustomizerSource )} An ad customizer source,
* if it exists, null otherwise.
*/
function getAdCustomizerSource() {
var customizers = AdsApp.adCustomizerSources().get();
while (customizers.hasNext()) {
var customizer = customizers.next();
if (customizer.getName() == CUSTOMIZER_NAME) {
return customizer;
}
}
return null;
}
/**
* Gets the calendar's timezone.
*
* @return {string} The calendar's timezone.
*/
function getCalendarTimezone() {
var calendar = Calendar.Calendars.get(CALENDAR_ID);
return calendar.timeZone;
}
/**
* Lists all events on the calendar.
*
* @return {Array.<Object>} An array of event details.
*/
function listAllEvents() {
var calendarEvents = Calendar.Events.list(CALENDAR_ID, {
singleEvents: true,
orderBy: 'startTime'
});
var retval = [];
for (var i = 0; i < calendarEvents.items.length; i++) {
var event = calendarEvents.items[i];
if (event.start.date || event.end.date) {
throw ('All day events are not supported. Set a start and end time.');
}
var startDate = parseDate(event.start.dateTime);
var endDate = parseDate(event.end.dateTime);
retval.push({
'Name': event.summary,
'Campaign': getCampaignName(event.description),
'StartDate': startDate,
'EndDate': endDate
});
}
return retval;
}
/**
* Parses a date from Google Calendar.
*
* @param {string} dateText The date as a string.
*
* @return {Date} the parsed date.
*/
function parseDate(dateText) {
// Date format is yyyy-mm-ddTHH:mm:ss±HH:mm.
var dateFormat = /^(\d{4})-(\d{2})-(\d{2})T(\d{2}):(\d{2}):(\d{2})([+-]\d{2}):(\d{2})$/;
var parts = dateText.match(dateFormat);
var year = parts[1];
var month = parts[2];
var day = parts[3];
var hour = parts[4];
var minute = parts[5];
var second = parts[6];
var tzHour = parseInt(parts[7]);
var tzMin = parseInt(parts[8]);
// Change the sign of tzMin if tzHour is negative. This way, -05:30
// is interpreted as -05:00 -00:30 instead of -05:00 -00:30
if (tzHour < 0) {
tzMin = -tzMin;
}
var tzOffset = new Date().getTimezoneOffset() + tzHour * 60 + tzMin;
return new Date(year, month - 1, day, hour, minute - tzOffset, second, 0);
}