Adwords script for better A/B testing of Ads

This script is really useful for long term ad A/B testing for bigger accounts.

Key functions:

Track changes or additions to the Ad Group and calculate statistical significance for A/B testing from a specific date. You can see the date range for each test in the Dates list.
Once the test has reached at least 95% significance, and the Ad Group has 2 or more ads, more than 50 clicks, and 500 impressions for the test period, you will find a list of the Ad Groups which are finished testing in the Results sheet.
If/when you make any changes to the ads in the Ad Group (pausing an ad or creating a new one), the script will automatically detect the change and start gathering new data on the day the change is made.
If the Results sheet is empty, then the A/B tests are still running and there are no results currently available.

What you should know:

The script isn’t limited to A/B testing only 2 ads in an Ad Group, you can also use it for Dynamic remarketing or banners. But you must have the same dimensions for each banner in one Ad Group, because the script is not designed to compare banners of different size.
You must make sure to have the “Rotate evenly/indefinitely“ setting selected so that every ad in the Ad Group will get the same amount of impressions.

How to set up the script:

Create new spreadsheet in your Google Drive.
Insert the URL of your spreadsheet in line 1 of the script inside the brackets.
Authorize the script and then preview it.
Wait for the script to finish. Then go to the Dates list spreadsheet and change the date according to your needs, we recommend 7 to 10 days in the past. The script will begin tracking your changes the first time you use it, so initially, you won’t have access to the account history.
When you change the dates for each Ad Group you should rerun the script.
Look at the Results list, where you A/B test results will appear when they have finished.
Then go to the Ad Group that has finished testing and create a new test. The best way to do this is to pause the ad which lost and create a new competitor for the winning ad.
The next day, the Ad Group where you started a new test will disappear from the Results list because the new test is running.
If the Results list is clear there is no Ad Group with at least a 95 % accuracy for test results.
You can run A/B tests like this on thousands of Ad Groups.

var SPREADSHEET_URL = '';
var DATA_FOLDER = '_adwords_data';
var DATA_FILE = 'ad-abtest-snapshot';
 
 
function main() {
  var account = AdWordsApp.currentAccount();
  var tz = account.getTimeZone();
  
  DATA_FILE = DATA_FILE + "-" + account.getCustomerId();
 
  var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var source_sheet = lynt_create_or_get_sheet(spreadsheet, "Dates", 1);
  var export_sheet = lynt_create_or_get_sheet(spreadsheet, "Results", 2);
  var adGroupWithOneAd = lynt_create_or_get_sheet(spreadsheet, "Only one Ad", 3)
  
  var lastModifications = getLastModifications(source_sheet, tz);
  
  var currentSnapshot = getCurrentSnapshot();
  
  var today = resetTimezone(new Date(), tz);
  today.setHours(0);
  today.setMinutes(0);
  today.setSeconds(0);
  today.setMilliseconds(0);
  
  for (var campaignId in currentSnapshot) {
    if (!currentSnapshot.hasOwnProperty(campaignId)) {
      continue;
    }
    
    var currCampaignSnapshot = currentSnapshot[campaignId];
    var prevCampaignSnapshot = getPreviousSnapshot(campaignId);
    
    for (var groupId in currCampaignSnapshot.groups) {
      if (!currCampaignSnapshot.groups.hasOwnProperty(groupId)) {
        continue;
      }
      
      var currGroupSnapshot = currCampaignSnapshot.groups[groupId];
      currGroupSnapshot.ads = JSON.stringify(currGroupSnapshot.ads);
      var prevGroupSnapshot = prevCampaignSnapshot ? prevCampaignSnapshot.groups[groupId] : null;
      
      var change;
      if (prevGroupSnapshot) {
        change = prevGroupSnapshot.ads != currGroupSnapshot.ads;
      } else {
        change = true;
      }
      
      if (!lastModifications[groupId] || change) {
        lastModifications[groupId] = {
          campaignName: currCampaignSnapshot.name,
          groupName: currGroupSnapshot.name,
          lastChange: lynt_getDateNDaysBack(7)
        };
      }
      
      if (prevGroupSnapshot) {
        delete prevCampaignSnapshot.groups[groupId];
      }
    }
    
    if (prevCampaignSnapshot) {
      for (var groupId in prevCampaignSnapshot.groups) {
        if (prevCampaignSnapshot.groups.hasOwnProperty(groupId)) {
          currCampaignSnapshot.groups[groupId] = prevCampaignSnapshot.groups[groupId];
        }
      }
    }
  }
  
  saveLastModifications(source_sheet, lastModifications);
  for (var campaignId in currentSnapshot) {
    if (currentSnapshot.hasOwnProperty(campaignId)) {
      saveCurrentSnapshot(campaignId, currentSnapshot[campaignId]);
    }
  }
  
  var labelSel = AdWordsApp.labels()
  	.withCondition("Name = 'Winner'");
  var labelIt = labelSel.get();
  if (labelIt.totalNumEntities() == 0) {
    AdWordsApp.createLabel("Winner", "Winner Ad", "#ADFF2F");
  }
  
  var deleteLabel = AdWordsApp.ads()
    .withCondition("LabelNames = 'Winner'")
      var labeledAds = deleteLabel.get();
      while(labeledAds.hasNext()) {
        var labeledAdIterator = labeledAds.next();
        labeledAdIterator.removeLabel("Winner");
      }
   var delAdGroupLabel = AdWordsApp.adGroups()
    .withCondition("LabelNames = 'Winner'")
      var labelAdGroups = delAdGroupLabel.get();
      while(labelAdGroups.hasNext()) {
        var labeledAdGroupIterator = labelAdGroups.next();
        labeledAdGroupIterator.removeLabel("Winner");
      }
  
  export_sheet.clear();
  export_sheet.getRange(1, 1, 1, 3).setValues([
    ["Campaign", "Ad Group", "Impressions of Ad Group"]
  ]);
  adGroupWithOneAd.clear();
  adGroupWithOneAd.getRange(1, 1, 1, 3).setValues([
    ["Campaign", "Ad Group", "Impressions of Ad Group"]
  ]);
  
  var yesterday = lynt_get_date(1);
  var lastChangeAWQL;
 
  var all_ads = [];
  for(var groupId in lastModifications) {
    var group = lastModifications[groupId];
    
    lastChangeAWQL = lynt_date_format(group.lastChange);
    if (lastChangeAWQL >= yesterday) {
      continue;
    }
    
    var adSelector = AdWordsApp
     .ads()
     .withCondition("CampaignName ="+"'"+group.campaignName+"'")
     .withCondition("AdGroupName ="+"'"+group.groupName+"'")
     .withCondition("Status = ENABLED")
     .forDateRange(lastChangeAWQL, yesterday)
     .orderBy("Impressions DESC")
     .withLimit(2);
    
    var groupAds = [];
 
    var adIterator = adSelector.get();
    if (adIterator.totalNumEntities() < 2) {     
      while(adIterator.hasNext()) {
       var ad = adIterator.next();
       var stats = ad.getStatsFor(lynt_date_format(group.lastChange),lynt_get_date(1));
       adGroupWithOneAd.appendRow([ad.getCampaign().getName(),ad.getAdGroup().getName(),stats.getImpressions()]);
      }
      continue;
    }
      
    while (adIterator.hasNext()) {
     var ad = adIterator.next();
         var stats = ad.getStatsFor(lynt_date_format(group.lastChange),lynt_get_date(1));
         var ad_for_adgroup = [ad.getId(),ad.getAdGroup().getName(),ad.getAdGroup().getId(),stats.getImpressions(), stats.getClicks()];
         groupAds.push(ad_for_adgroup);
     }
     all_ads.push(groupAds);
    }
  for(a = 0; a< all_ads.length; a ++) { if(winner(all_ads[a][0][3],all_ads[a][0][4],all_ads[a][1][3],all_ads[a][1][4],95)==1 && all_ads[a][0][3]+all_ads[a][1][3]>500 && all_ads[a][0][4]+all_ads[a][1][4]>50) {
      var adSelector = AdWordsApp
      .ads()
      .withIds([[all_ads[a][0][2],all_ads[a][0][0]]])
 
      var adIterator = adSelector.get();
      while (adIterator.hasNext()) {
      var ad = adIterator.next();
        export_sheet.appendRow([ad.getCampaign().getName(),ad.getAdGroup().getName(),all_ads[a][0][3]+all_ads[a][1][3]]);
        ad.applyLabel("Winner");
        ad.getAdGroup().applyLabel("Winner");
      }
 
   } else if(winner(all_ads[a][0][3],all_ads[a][0][4],all_ads[a][1][3],all_ads[a][1][4],95)==2 && all_ads[a][0][3]+all_ads[a][1][3]>500 && all_ads[a][0][4]+all_ads[a][1][4]>50) {
      var adSelector = AdWordsApp
       .ads()
       .withIds([[all_ads[a][1][2],all_ads[a][1][0]]])
 
      var adIterator = adSelector.get();
      while (adIterator.hasNext()) {
       var ad = adIterator.next();
       export_sheet.appendRow([ad.getCampaign().getName(),ad.getAdGroup().getName(),all_ads[a][0][3]+all_ads[a][1][3]]);
       ad.applyLabel("Winner");
       ad.getAdGroup().applyLabel("Winner");
      }
   } else {
      continue;
 
   }
 }
 export_sheet.sort(3, ascending=false);
 adGroupWithOneAd.sort(3, ascending=false);
  
}
 
function getCurrentSnapshot() {
  var snapshot = {};
  
  var adsSel = AdWordsApp.ads()
  	.withCondition("Status = ENABLED AND AdGroupStatus = ENABLED AND CampaignStatus = ENABLED")
  	.orderBy("Headline ASC");
  var adsIt = adsSel.get();
  while (adsIt.hasNext()) {
    var ad = adsIt.next();
    var campaign = ad.getCampaign();
    var campaignId = ad.getCampaign().getId();
    var group = ad.getAdGroup();
    var groupId = group.getId();
    
    if (!snapshot[campaignId]) {
      snapshot[campaignId] = {
        'name': campaign.getName(),
        'groups': {}
      };
    }
    var campSnapshot = snapshot[campaignId];
    if (!campSnapshot.groups[groupId]) {
      campSnapshot.groups[groupId] = {
        'name': group.getName(),
        'ads': []
      };
    }
    var adSnapshot = {
      'headline': ad.getHeadline(),
      'line1': ad.getDescription1(),
      'line2': ad.getDescription2()
    };
    campSnapshot.groups[groupId].ads.push(adSnapshot);
  }
  
  return snapshot;
}
  
function getPreviousSnapshot(campaignId) {
  return lynt_store_load(DATA_FOLDER, DATA_FILE + '-' + campaignId + '.json');
}
 
function saveCurrentSnapshot(campaignId, currentSnapshot) {
  lynt_store_save(DATA_FOLDER, DATA_FILE + "-" + campaignId + ".json", currentSnapshot);
}
 
/**
 * Get the date of last modification.
 */
function getLastModifications(overviewSheet, tz) {
  var lastRow = overviewSheet.getLastRow();
  var lastCol = overviewSheet.getLastColumn();
  if (lastRow > 1 && lastCol >= 4) {
    var data = overviewSheet.getRange(2, 1, lastRow, 4).getValues();
    var changes = {};
    for (var r = 0; r < data.length; r++) { var row = data[r]; if (row.length > 3 && row[0] && row[3]) {
        changes[row[0]] = {
          campaignName: row[1],
          groupName: row[2],
          lastChange: resetTimezone(row[3], tz)
        };
      }
    }
    return changes;
  }
  else {
    return {};
  }
}
 
 
/**
 * Saves last modifications to spreadsheets.
 */
function saveLastModifications(overviewSheet, changes) {
  overviewSheet.clear();
  var data = [
        ['ID', 'Campaign name', 'Adgroup name', 'Last change']
    ];
  for (var groupId in changes) {
    if (changes.hasOwnProperty(groupId)) {
      var groupChangeInfo = changes[groupId];
      data.push([
        groupId,
        groupChangeInfo.campaignName,
        groupChangeInfo.groupName,
        groupChangeInfo.lastChange.toISOString().substr(0, 10)
      ]);
    }
  }
  var dataRange = overviewSheet.getRange(1, 1, data.length, data[0].length);
  dataRange.setValues(data);
  for (var i = 1; i <= 4; i++) { overviewSheet.autoResizeColumn(i); } } function lynt_get_date(pocet_dni) { var dnes = new Date(); var minule = new Date(); minule.setTime(dnes.getTime() - (1000 * 60 * 60 * 24 * pocet_dni)); return minule.getFullYear()+("0"+(minule.getMonth()+1)).slice(-2)+("0"+minule.getDate()).slice(-2); } function lynt_date_format(date) { var datum = new Date(); datum.setTime(date.getTime()); return datum.getFullYear()+("0"+(datum.getMonth()+1)).slice(-2)+("0"+datum.getDate()).slice(-2); } function cnormdist(x) { b1 = 0.319381530; b2 = -0.356563782; b3 = 1.781477937; b4 = -1.821255978; b5 = 1.330274429; p = 0.2316419; c = 0.39894228; if(x >= 0.0) {
      t = 1.0 / ( 1.0 + p * x );
      return (1.0 - c *  Math.exp( -x * x / 2.0 ) * t *
      ( t *( t * ( t * ( t * b5 + b4 ) + b3 ) + b2 ) + b1 ));
  }
  else {
      t = 1.0 / ( 1.0 - p * x );
      return ( c *  Math.exp( -x * x / 2.0 ) * t *
      ( t *( t * ( t * ( t * b5 + b4 ) + b3 ) + b2 ) + b1 ));
  }
}
 
function winner(impressionA,clicksA,impressionB,clicksB,probability){
  var ctrA = clicksA/impressionA;
  var ctrB = clicksB/impressionB;
  var errA = Math.sqrt(ctrA*(1-ctrA)/impressionA);
  var errB = Math.sqrt(ctrB*(1-ctrB)/impressionB);
 
  var p = cnormdist((ctrA-ctrB)/Math.sqrt(errA*errA+errB*errB));    
 
  if(p>probability/100) {
    //Logger.log("A is the winner "+Math.round(p*1000)/10+"%")
    return 1;
   
  } else if(p<1-probability/100) {
    //Logger.log("B is the winner "+Math.round((1-p)*1000)/10+"%");
    return 2;
  } else {
    return 3;    
  }
}
 
 
 
/*** Utility functions: common library. ***/
 
function lynt_store_save(dataFolderName, fileName, object) {
  var objectJson = JSON.stringify(object);
  var dataFolder = lynt_get_data_folder(dataFolderName);
  var files = dataFolder.getFilesByName(fileName);
  var dataFile;
  if (files.hasNext()) {
    dataFile = files.next();
    dataFile.setContent(objectJson);
  }
  else {
    dataFolder.createFile(fileName, objectJson, "application/json");
  }
  return true;
}
 
function lynt_store_load(dataFolderName, fileName) {
  var dataFolder = lynt_get_data_folder(dataFolderName);
  var files = dataFolder.getFilesByName(fileName);
  var dataFile;
  if (files.hasNext()) {
    dataFile = files.next();
  }
  if (dataFile) {
    var objectJson = dataFile.getBlob().getDataAsString("UTF-8");
    return JSON.parse(objectJson);
  }
  else {
    return null;
  }
}
 
function lynt_get_data_folder(dataFolderName) {
  var root = DriveApp.getRootFolder();
  var folders = root.getFoldersByName(dataFolderName);
  var dataFolder;
  if (folders.hasNext()) {
    dataFolder = folders.next();
  }
  else {
    dataFolder = root.createFolder(dataFolderName);
  }
  return dataFolder;
}
 
function lynt_getDateNDaysBack(nDays) {
  var date = resetTimezone(new Date());
  date.setHours(0);
  date.setMinutes(0);
  date.setSeconds(0);
  date.setMilliseconds(0);
  var time = date.getTime();
  time = time - 86400000 * nDays;
  date.setTime(time);
  return date;
}
 
function lynt_formatDateAWQL(date) {
  return date.toISOString().substr(0, 10).replace(/-/g, '');
}
 
function resetTimezone(date, timezone) {
  var tzOffset = parseInt(Utilities.formatDate(date, timezone, "Z").substr(0, 3), 10) * 60 * 60 * 1000;
  var srcOffset = date.getTimezoneOffset() * 60 * 1000;
  var time = date.getTime();
  time += (tzOffset + srcOffset);
  date.setTime(time);
  return date;
}
 
function lynt_create_or_get_sheet(spreadsheet, name, position) {
  var sheet = spreadsheet.getSheetByName(name);
  if (sheet == null) {
    sheet = spreadsheet.insertSheet(name, position);
  }
  return sheet;
}
 
function lynt_remove_empty_sheets(spreadsheet) {
  var sheets = spreadsheet.getSheets();
  for (var i = 0; i < sheets.length; i++) {
    if (sheets[i].getLastRow() == 0) {
      spreadsheet.deleteSheet(sheets[i]);
    }
  }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

var SPREADSHEET_URL = '';

var DATA_FOLDER = '_adwords_data';

var DATA_FILE = 'ad-abtest-snapshot';

function main() {

var account = AdWordsApp.currentAccount();

var tz = account.getTimeZone();

DATA_FILE = DATA_FILE + "-" + account.getCustomerId();

var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);

var source_sheet = lynt_create_or_get_sheet(spreadsheet, "Dates", 1);

var export_sheet = lynt_create_or_get_sheet(spreadsheet, "Results", 2);

var adGroupWithOneAd = lynt_create_or_get_sheet(spreadsheet, "Only one Ad", 3)

var lastModifications = getLastModifications(source_sheet, tz);

var currentSnapshot = getCurrentSnapshot();

var today = resetTimezone(new Date(), tz);

today.setHours(0);

today.setMinutes(0);

today.setSeconds(0);

today.setMilliseconds(0);

for (var campaignId in currentSnapshot) {

if (!currentSnapshot.hasOwnProperty(campaignId)) {

continue;

}

var currCampaignSnapshot = currentSnapshot[campaignId];

var prevCampaignSnapshot = getPreviousSnapshot(campaignId);

for (var groupId in currCampaignSnapshot.groups) {

if (!currCampaignSnapshot.groups.hasOwnProperty(groupId)) {

continue;

}

var currGroupSnapshot = currCampaignSnapshot.groups[groupId];

currGroupSnapshot.ads = JSON.stringify(currGroupSnapshot.ads);

var prevGroupSnapshot = prevCampaignSnapshot ? prevCampaignSnapshot.groups[groupId] : null;

var change;

if (prevGroupSnapshot) {

change = prevGroupSnapshot.ads != currGroupSnapshot.ads;

} else {

change = true;

}

if (!lastModifications[groupId] || change) {

lastModifications[groupId] = {

campaignName: currCampaignSnapshot.name,

groupName: currGroupSnapshot.name,

lastChange: lynt_getDateNDaysBack(7)

};

}

if (prevGroupSnapshot) {

delete prevCampaignSnapshot.groups[groupId];

}

if (prevCampaignSnapshot) {

for (var groupId in prevCampaignSnapshot.groups) {

if (prevCampaignSnapshot.groups.hasOwnProperty(groupId)) {

currCampaignSnapshot.groups[groupId] = prevCampaignSnapshot.groups[groupId];

}

saveLastModifications(source_sheet, lastModifications);

for (var campaignId in currentSnapshot) {

if (currentSnapshot.hasOwnProperty(campaignId)) {

saveCurrentSnapshot(campaignId, currentSnapshot[campaignId]);

}

var labelSel = AdWordsApp.labels()

.withCondition("Name = 'Winner'");

var labelIt = labelSel.get();

if (labelIt.totalNumEntities() == 0) {

AdWordsApp.createLabel("Winner", "Winner Ad", "#ADFF2F");

}

var deleteLabel = AdWordsApp.ads()

.withCondition("LabelNames = 'Winner'")

var labeledAds = deleteLabel.get();

while(labeledAds.hasNext()) {

var labeledAdIterator = labeledAds.next();

labeledAdIterator.removeLabel("Winner");

}

var delAdGroupLabel = AdWordsApp.adGroups()

.withCondition("LabelNames = 'Winner'")

var labelAdGroups = delAdGroupLabel.get();

while(labelAdGroups.hasNext()) {

var labeledAdGroupIterator = labelAdGroups.next();

labeledAdGroupIterator.removeLabel("Winner");

}

export_sheet.clear();

export_sheet.getRange(1, 1, 1, 3).setValues([

["Campaign", "Ad Group", "Impressions of Ad Group"]

]);

adGroupWithOneAd.clear();

adGroupWithOneAd.getRange(1, 1, 1, 3).setValues([

["Campaign", "Ad Group", "Impressions of Ad Group"]

]);

var yesterday = lynt_get_date(1);

var lastChangeAWQL;

var all_ads = [];

for(var groupId in lastModifications) {

var group = lastModifications[groupId];

lastChangeAWQL = lynt_date_format(group.lastChange);

if (lastChangeAWQL >= yesterday) {

continue;

}

var adSelector = AdWordsApp

.ads()

.withCondition("CampaignName ="+"'"+group.campaignName+"'")

.withCondition("AdGroupName ="+"'"+group.groupName+"'")

.withCondition("Status = ENABLED")

.forDateRange(lastChangeAWQL, yesterday)

.orderBy("Impressions DESC")

.withLimit(2);

var groupAds = [];

var adIterator = adSelector.get();

if (adIterator.totalNumEntities() < 2) {

while(adIterator.hasNext()) {

var ad = adIterator.next();

var stats = ad.getStatsFor(lynt_date_format(group.lastChange),lynt_get_date(1));

adGroupWithOneAd.appendRow([ad.getCampaign().getName(),ad.getAdGroup().getName(),stats.getImpressions()]);

}

continue;

}

while (adIterator.hasNext()) {

var ad = adIterator.next();

var stats = ad.getStatsFor(lynt_date_format(group.lastChange),lynt_get_date(1));

var ad_for_adgroup = [ad.getId(),ad.getAdGroup().getName(),ad.getAdGroup().getId(),stats.getImpressions(), stats.getClicks()];

groupAds.push(ad_for_adgroup);

}

all_ads.push(groupAds);

}

for(a = 0; a< all_ads.length; a ++) { if(winner(all_ads[a][0][3],all_ads[a][0][4],all_ads[a][1][3],all_ads[a][1][4],95)==1 && all_ads[a][0][3]+all_ads[a][1][3]>500 && all_ads[a][0][4]+all_ads[a][1][4]>50) {

var adSelector = AdWordsApp

.ads()

.withIds([[all_ads[a][0][2],all_ads[a][0][0]]])

var adIterator = adSelector.get();

while (adIterator.hasNext()) {

var ad = adIterator.next();

export_sheet.appendRow([ad.getCampaign().getName(),ad.getAdGroup().getName(),all_ads[a][0][3]+all_ads[a][1][3]]);

ad.applyLabel("Winner");

ad.getAdGroup().applyLabel("Winner");

}

} else if(winner(all_ads[a][0][3],all_ads[a][0][4],all_ads[a][1][3],all_ads[a][1][4],95)==2 && all_ads[a][0][3]+all_ads[a][1][3]>500 && all_ads[a][0][4]+all_ads[a][1][4]>50) {

var adSelector = AdWordsApp

.ads()

.withIds([[all_ads[a][1][2],all_ads[a][1][0]]])

var adIterator = adSelector.get();

while (adIterator.hasNext()) {

var ad = adIterator.next();

export_sheet.appendRow([ad.getCampaign().getName(),ad.getAdGroup().getName(),all_ads[a][0][3]+all_ads[a][1][3]]);

ad.applyLabel("Winner");

ad.getAdGroup().applyLabel("Winner");

}

} else {

continue;

}

export_sheet.sort(3, ascending=false);

adGroupWithOneAd.sort(3, ascending=false);

}

function getCurrentSnapshot() {

var snapshot = {};

var adsSel = AdWordsApp.ads()

.withCondition("Status = ENABLED AND AdGroupStatus = ENABLED AND CampaignStatus = ENABLED")

.orderBy("Headline ASC");

var adsIt = adsSel.get();

while (adsIt.hasNext()) {

var ad = adsIt.next();

var campaign = ad.getCampaign();

var campaignId = ad.getCampaign().getId();

var group = ad.getAdGroup();

var groupId = group.getId();

if (!snapshot[campaignId]) {

snapshot[campaignId] = {

'name': campaign.getName(),

'groups': {}

};

}

var campSnapshot = snapshot[campaignId];

if (!campSnapshot.groups[groupId]) {

campSnapshot.groups[groupId] = {

'name': group.getName(),

'ads': []

};

}

var adSnapshot = {

'headline': ad.getHeadline(),

'line1': ad.getDescription1(),

'line2': ad.getDescription2()

};

campSnapshot.groups[groupId].ads.push(adSnapshot);

}

return snapshot;

}

function getPreviousSnapshot(campaignId) {

return lynt_store_load(DATA_FOLDER, DATA_FILE + '-' + campaignId + '.json');

}

function saveCurrentSnapshot(campaignId, currentSnapshot) {

lynt_store_save(DATA_FOLDER, DATA_FILE + "-" + campaignId + ".json", currentSnapshot);

}

/**

* Get the date of last modification.

function getLastModifications(overviewSheet, tz) {

var lastRow = overviewSheet.getLastRow();

var lastCol = overviewSheet.getLastColumn();

if (lastRow > 1 && lastCol >= 4) {

var data = overviewSheet.getRange(2, 1, lastRow, 4).getValues();

var changes = {};

for (var r = 0; r < data.length; r++) { var row = data[r]; if (row.length > 3 && row[0] && row[3]) {

changes[row[0]] = {

campaignName: row[1],

groupName: row[2],

lastChange: resetTimezone(row[3], tz)

};

}

return changes;

}

else {

return {};

}

/**

* Saves last modifications to spreadsheets.

function saveLastModifications(overviewSheet, changes) {

overviewSheet.clear();

var data = [

['ID', 'Campaign name', 'Adgroup name', 'Last change']

];

for (var groupId in changes) {

if (changes.hasOwnProperty(groupId)) {

var groupChangeInfo = changes[groupId];

data.push([

groupId,

groupChangeInfo.campaignName,

groupChangeInfo.groupName,

groupChangeInfo.lastChange.toISOString().substr(0, 10)

]);

}

var dataRange = overviewSheet.getRange(1, 1, data.length, data[0].length);

dataRange.setValues(data);

for (var i = 1; i <= 4; i++) { overviewSheet.autoResizeColumn(i); } } function lynt_get_date(pocet_dni) { var dnes = new Date(); var minule = new Date(); minule.setTime(dnes.getTime() - (1000 * 60 * 60 * 24 * pocet_dni)); return minule.getFullYear()+("0"+(minule.getMonth()+1)).slice(-2)+("0"+minule.getDate()).slice(-2); } function lynt_date_format(date) { var datum = new Date(); datum.setTime(date.getTime()); return datum.getFullYear()+("0"+(datum.getMonth()+1)).slice(-2)+("0"+datum.getDate()).slice(-2); } function cnormdist(x) { b1 = 0.319381530; b2 = -0.356563782; b3 = 1.781477937; b4 = -1.821255978; b5 = 1.330274429; p = 0.2316419; c = 0.39894228; if(x >= 0.0) {

t = 1.0 / ( 1.0 + p * x );

return (1.0 - c * Math.exp( -x * x / 2.0 ) * t *

( t *( t * ( t * ( t * b5 + b4 ) + b3 ) + b2 ) + b1 ));

}

else {

t = 1.0 / ( 1.0 - p * x );

return ( c * Math.exp( -x * x / 2.0 ) * t *

( t *( t * ( t * ( t * b5 + b4 ) + b3 ) + b2 ) + b1 ));

}

function winner(impressionA,clicksA,impressionB,clicksB,probability){

var ctrA = clicksA/impressionA;

var ctrB = clicksB/impressionB;

var errA = Math.sqrt(ctrA*(1-ctrA)/impressionA);

var errB = Math.sqrt(ctrB*(1-ctrB)/impressionB);

var p = cnormdist((ctrA-ctrB)/Math.sqrt(errA*errA+errB*errB));

if(p>probability/100) {

//Logger.log("A is the winner "+Math.round(p*1000)/10+"%")

return 1;

} else if(p<1-probability/100) {

//Logger.log("B is the winner "+Math.round((1-p)*1000)/10+"%");

return 2;

} else {

return 3;

}

/*** Utility functions: common library. ***/

function lynt_store_save(dataFolderName, fileName, object) {

var objectJson = JSON.stringify(object);

var dataFolder = lynt_get_data_folder(dataFolderName);

var files = dataFolder.getFilesByName(fileName);

var dataFile;

if (files.hasNext()) {

dataFile = files.next();

dataFile.setContent(objectJson);

}

else {

dataFolder.createFile(fileName, objectJson, "application/json");

}

return true;

}

function lynt_store_load(dataFolderName, fileName) {

var dataFolder = lynt_get_data_folder(dataFolderName);

var files = dataFolder.getFilesByName(fileName);

var dataFile;

if (files.hasNext()) {

dataFile = files.next();

}

if (dataFile) {

var objectJson = dataFile.getBlob().getDataAsString("UTF-8");

return JSON.parse(objectJson);

}

else {

return null;

}

function lynt_get_data_folder(dataFolderName) {

var root = DriveApp.getRootFolder();

var folders = root.getFoldersByName(dataFolderName);

var dataFolder;

if (folders.hasNext()) {

dataFolder = folders.next();

}

else {

dataFolder = root.createFolder(dataFolderName);

}

return dataFolder;

}

function lynt_getDateNDaysBack(nDays) {

var date = resetTimezone(new Date());

date.setHours(0);

date.setMinutes(0);

date.setSeconds(0);

date.setMilliseconds(0);

var time = date.getTime();

time = time - 86400000 * nDays;

date.setTime(time);

return date;

}

function lynt_formatDateAWQL(date) {

return date.toISOString().substr(0, 10).replace(/-/g, '');

}

function resetTimezone(date, timezone) {

var tzOffset = parseInt(Utilities.formatDate(date, timezone, "Z").substr(0, 3), 10) * 60 * 60 * 1000;

var srcOffset = date.getTimezoneOffset() * 60 * 1000;

var time = date.getTime();

time += (tzOffset + srcOffset);

date.setTime(time);

return date;

}

function lynt_create_or_get_sheet(spreadsheet, name, position) {

var sheet = spreadsheet.getSheetByName(name);

if (sheet == null) {

sheet = spreadsheet.insertSheet(name, position);

}

return sheet;

}

function lynt_remove_empty_sheets(spreadsheet) {

var sheets = spreadsheet.getSheets();

for (var i = 0; i < sheets.length; i++) {

if (sheets[i].getLastRow() == 0) {

spreadsheet.deleteSheet(sheets[i]);

}

Comments

Alexandre Langzam says

February 22, 2017 at 11:23 am

Hi Jakub,
I am afraid this script as such is not working,
Parsing error. Please check your selector. (line 134)
I already posted some punctuation problems but I there there are several as its still not working

Best

- Alexandre Langzam says
  
  February 22, 2017 at 11:27 am
  
  spotted*
  
  - Yang Jieyu says
    
    February 27, 2017 at 9:27 am
    
    Hi,
    
    Am experiencing the same error as Alexandre – is there a workaround?
    
    Cheers,
    
Eli says

February 21, 2018 at 10:37 am

Hi there, just became alert to your blog through Google, and found that it is really informative. I’m going to watch out for brussels. I’ll be grateful if you continue this in future. Many people will be benefited from your writing. Cheers!

latest movie download says

February 21, 2018 at 8:00 pm

There are terrific developments on the style of the blog, I really like this. Mine is dealing with free movies and right now there are lots of stuff to be done, I am currently a newbie in web development. Thanks!

Reader Interactions

Comments

Leave a Reply Cancel reply