Sunday, October 20, 2013

Manage Your AdWords Creatives Using Excel

Since my last post, Google has been busy releasing a ton of new features for AdWords scripts.  Among them was the ability to interact directly with your Google Drive files.  This is awesome for a few reasons and one of them is that it allows you to manage your account through spreadsheets that are automatically synced back to your Google AdWords account every hour.

There is the opportunity to create keywords, manage Ads, AdGroups, or Ad Extensions all using the power of Excel spreadsheets. Still not convinced? You can also have all your account metrics automatically delivered to your Google Drive and automatically downloaded to your computer each day.

So I thought I would demonstrate how you can manage your Creatives using this new functionality.

The first thing you need to do is install Google Drive onto your computer. It's pretty easy to do but does require Admin rights on your machine.  If you can't install it right now, no need to worry.  You can manage your SiteLinks by updating the files in Google Drive via your browser.

Now take a look at the script below. There are a few config options that you should set.  FOLDER_PATH is the path to the folder where you want to store these files.  For example, 'AdWords/Creatives'. If the folder path doesn't exist, this script will create it for you.

The next parameter is FILE_NAME, which would be the file that the Creative data will be stored in. In this example, the file must be written as a .CSV.  Google makes it pretty easy to parse CSV files using the Utilities.parseCsv function.

This script allows you to make changes to headlines, descriptions, urls, status, and device preferences all from the csv file using Excel.  Then, simply save the file back to Google drive and the next time the script runs (daily or hourly), your changes will be reflected.

What other AdWords features might be easier to manage through Excel rather than through some of the UIs out there?

Thanks,
Russ

/******************************************
 * Manage AdWords Ads Using Excel
 * Version 1.0
 * Author: Russ Savage
 * FreeAdWordsScripts.com
 ****************************************/
var FOLDER_PATH = 'AdWordsData'; //The path where the file will be stored on GDrive
var FILE_NAME = 'creatives.csv'; //The name of the file on GDrive
 
var INCLUDE_STATS = true; // Set to false to remove stats from the file
var DATE_RANGE = 'LAST_30_DAYS'; //The date range for the stats
 
var INCLUDE_PAUSED_ADS = true; //Set to false to only report on active ads
var DELETE_ORIGINAL_ADS = true; //When set to false, the original ads will be paused instead of deleted
 
function main() {
  var file = getFile(FILE_NAME,FOLDER_PATH);
  if(!file) {
    file = createNewFile(FILE_NAME,FOLDER_PATH,buildCreativesFile());
    return;
  }
  
  var fileContent = file.getBlob().getDataAsString();
  var creatives = {};
  if(fileContent) {
    creatives = parseFileContent(fileContent);
  }
  
  var creativeIter = getAdIterator();
  while(creativeIter.hasNext()) {
    var creative = creativeIter.next();
    var adId = creative.getId();
    if(creatives[adId]) {
      //ok we found the ad.
      //Checking status
      var isEnabledFile = (creatives[adId]['Status'] === 'Enabled');
      if(creative.isEnabled() != isEnabledFile) {
        if(isEnabledFile) { 
          creative.enable(); 
        } else { 
          creative.pause(); 
        }
      }
      
      if(hadAdChanged(creative,creatives[adId])) {
        if(DELETE_ORIGINAL_ADS) {
          creative.remove();
        } else {
          creative.pause();
        }
        createNewAd(creative.getAdGroup(),creatives[creative.getId()]);
      }
    }
  }
  file.setContent(buildCreativesFile());
}
 
//Helper function to create a new ad
function createNewAd(ag,newAd) {
  var optArgs = {
    isMobilePreferred: (newAd['Device'] === 'Mobile') ? true : false
  };
  ag.createTextAd(newAd['Headline'],newAd['Desc1'],newAd['Desc2'],newAd['DisplayUrl'],newAd['DestinationUrl'],optArgs);
}
 
//This checks to see if the ad has been changed
function hadAdChanged(ad,oldAd) {
  var newAdText = [ad.getHeadline(),
                   ad.getDescription1(),
                   ad.getDescription2(),
                   ad.getDisplayUrl(),
                   ad.getDestinationUrl(),
                   (ad.isMobilePreferred()) ? 'Mobile' : 'Desktop'].join('~~!~~');
  var oldAdText = [oldAd['Headline'],
                   oldAd['Desc1'],
                   oldAd['Desc2'],
                   oldAd['DisplayUrl'],
                   oldAd['DestinationUrl'],
                   oldAd['Device']].join('~~!~~');
  Logger.log(newAdText);
  Logger.log(oldAdText);
  return (newAdText !== oldAdText);
}
 
//This builds the creatives file from all the ads in the account.
function buildCreativesFile() {
  var report = getReportColumns();
  var creativeIter = getAdIterator();
  while(creativeIter.hasNext()) {
    var creative = creativeIter.next();
    report += getReportRow(creative);
  }
  return report;
}
 
//This returns the ad iterator based on options.
function getAdIterator() {
  var adSelector = AdWordsApp.ads().withCondition("Type = 'TEXT_AD'"); 
  if(!INCLUDE_PAUSED_ADS) {
    adSelector = adSelector.withCondition('CampaignStatus = ENABLED')
                           .withCondition('AdGroupStatus = ENABLED')
                           .withCondition('Status = ENABLED');
  }
  return adSelector.get();
}
 
//This returns a CSV fow for the report.
function getReportRow(ad) {
  var retVal = [
    ad.getId(),
    ad.getCampaign().getName(),(ad.getCampaign().isPaused()) ? 'Paused' : 'Enabled',
    ad.getAdGroup().getName(),(ad.getAdGroup().isPaused()) ? 'Paused' : 'Enabled',
    ad.getHeadline(),
    ad.getDescription1(),
    ad.getDescription2(),
    ad.getDisplayUrl(),
    ad.getDestinationUrl(),
    (ad.isPaused()) ? 'Paused' : 'Enabled',
    (ad.isMobilePreferred()) ? 'Mobile' : 'Desktop',
  ];
  if(INCLUDE_STATS) {
    var stats = ad.getStatsFor(DATE_RANGE);
    retVal = retVal.concat([
      stats.getImpressions(),
      stats.getClicks(),
      stats.getCtr(),
      stats.getCost(),
      stats.getAverageCpc(),
      stats.getConversions(),
      stats.getConversionRate(),
      stats.getAveragePageviews(),
      stats.getAveragePosition(),
      stats.getAverageTimeOnSite(),
      stats.getBounceRate()
      ]);
  }
  return '"' + retVal.join('","') + '"\n';
}
 
//This returns the column headings used for the report.
function getReportColumns() {
  var columnHeadings = [
    'AdId',
    'CampaignName','CampaignStatus',
    'AdGroupName','AdGroupStatus',
    'Headline',
    'Desc1',
    'Desc2',
    'DisplayUrl',
    'DestinationUrl',
    'Status',
    'Device'];
  if(INCLUDE_STATS) {
    columnHeadings = columnHeadings.concat([
      'Impressions',
      'Clicks',
      'Ctr',
      'Cost',
      'Cpc',
      'Conversions',
      'ConversionRate',
      'AveragePageviews',
      'AvgPosition',
      'AvgTimeOnSite',
      'BounceRate'
      ]);
  }
  return '"' + columnHeadings.join('","') + '"\n';
}   
 
//This function parses the creatives file into an object for processing
function parseFileContent(fileContent) {
  var headers = [];
  var idHash = {};
  var data = Utilities.parseCsv(fileContent);
  for(var i in data) {
    var cells = data[i]
    if(cells.length == 1) { continue; } //skip any empty rows
    if(i == 0) { 
      headers = cells; 
    } else {
      var rowMap = {};
      for(var x in headers) {
        headers[x] = headers[x];
        cells[x] = cells[x];
        rowMap[headers[x]] = cells[x];
      }
      idHash[rowMap['AdId']] = rowMap;
    }
  }
  return idHash;
}
 
//This function gets the file from GDrive
function getFile(fileName,folderPath) {
  var folder = getFolder(folderPath);
  if(folder.getFilesByName(fileName).hasNext()) {
    return folder.getFilesByName(fileName).next();
  } else {
    return null;
  }
}
 
//This function creates a new file on GDrive
function createNewFile(fileName,folderPath,content) {
  if(!fileName) { throw 'createNewFile: Missing filename.'; }
  var folder = getFolder(folderPath);
  
  return folder.createFile(fileName, content);
}
 
//This function finds the folder for the file and creates folders if needed
function getFolder(folderPath) {
  var folder = DriveApp.getRootFolder();
  if(folderPath) {
    var pathArray = folderPath.split('/');
    for(var i in pathArray) {
      var folderName = pathArray[i];
      if(folder.getFoldersByName(folderName).hasNext()) {
        folder = folder.getFoldersByName(folderName).next();
      } else {
        folder = folder.createFolder(folderName);
      }
    }
  }
  return folder;
}

10 comments:

  1. Certsout.com provides authentic IT Certification exams preparation material guaranteed to make you pass in the first attempt. Download instant free demo & begin preparation. 700-651 Exam Test Questions Answers

    ReplyDelete
  2. You may find out more at this article. I hope to see you soon!

    ReplyDelete
  3. if you really want to earn money online from your home with your daily words then don't miss this article. get paid to listen to music
    online in 2020.

    ReplyDelete
  4. For booking independent call girls or escort female girls in Nehru Place, you can contact us on our Whatsapp number.college girls in nehru place

    ReplyDelete
  5. Ludhiana escorts are educated along with experienced and they Have the propensity to catch the attention of consumers with their sex appeal and glorious bodily structure. That's to say they might be called intriguing, sexy, and alluring.
    Ludhiana Call Girls
    Dehradun Call Girls
    Jodhpur Call Girls
    Gurgaon Call Girls
    Noida Call Girls
    Chandigarh Call Girls

    ReplyDelete
  6. The entirety of our young Dehradun call girls photographs are 100% certifiable so when you will meet her, she will be not let down you at all. On the off chance that you have to go out with her, our escorts amazingly familiar with Dehradun escorts are where anyone needs to join, a few people came here for their work, some for entertainment only and some came getting erotic with escorts in Dehradun.
    Dehradun escort service
    Haridwar escort service
    Mussoorie escort service
    Rishikesh escort service
    haldwani escort service
    Nainital escort service
    Ramnagar escort service
    Rudrapur escort service

    ReplyDelete
  7. I have been married for ten years to a girl from Brazil. By the way, she is very beautiful and attractive. Recently I found the site brazilian brides and I can tell you that all the information here is real. Brazilian girls are really very hot and would not mind to start a family with a foreigner.

    ReplyDelete
  8. The information you are providing that is really good. Thank for making and spending your precious time for this useful information. Thanks again and keep it up. Top CA firm in India

    ReplyDelete