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;
}

50 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
    Replies
    1. There are many of the young guys that are like to spend some special moments with the high profile independent call girls in Bangalore that give some special companion that makes clients more excited to take them for the enjoyment for the whole night in Bangalore. Visit More: bangalore escorts service

      Delete
    2. There is a huge number of independent Chennai escort girls are available ready to give the best and friendly escort service that makes clients feel free to get complete enjoyment without any of the issues. Visit More: Chennai escorts service

      Delete
  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
  9. WAJA sauna is specialist manufacturer of top quality sauna products. Products include sauna rooms, steam rooms, barrel saunas, wooden hot tubs, and all kinds of sauna accessories.


    steam room

    ReplyDelete
  10. Many people try to find a mail-order bride. To be honest, I also decided to try my luck. And, let me tell you, I have not lost a bit, much less regretted it, everything in my life is fine now, I have become a different person, so the service single ukrainian ladies will help in finding girls from different countries. My friends have known about this site for a long time and don't intend to leave it, because there you can find girls for absolutely any taste and easily find a common language with them.Site tested, I often sit there myself and I'm not going to leave!

    ReplyDelete
  11. My son loved to write me poems, and poetry. While surfing the internet I came across a website of camping for teens, I immediately got interested and showed my son, he was very interested in it and we decided to book a trip. After 7 days in this camp my son came back with a big smile on his face and positive emotions. He loved it very much, and even received a diploma as one of the 50 talented children of the camp! As a mom of a talented little kid, I can recommend an essay writer.

    ReplyDelete
  12. Vadodara escorts service call us +91-9000000000 is one of the most top able escorts female are available to make available her most excellent curve to her customers.

    Click here : vadodara escorts girls

    ReplyDelete
  13. Munrika Escorts Services | Book Now | Munrika Escorts Girls
    Munrika Call Girls Service Book high profile Escorts. Independent escorts services in Munirka at cheap rates, Gentlemen can get their dream sexy call girls strand Another significant benefit of searching for Munrika Escort services online is the wide variety to which the internet gives you access.
    Munrika Escorts Girls
    Munrika Escorts Services

    ReplyDelete
  14. Model Escorts in Mumbai are those girls who are providing the best quality sex to customers through which you can make sure the premium quality sex
    pleasure in your life. Thus, play nude games with models and enjoy more and more in a boring relationship.
    Mumbai Escorts
    Escorts in Mumbai
    Mumbai Call Girls

    ReplyDelete
  15. Thanks for the sharing such a great post!! I will share it with my friend circle..
    aunty escorts in bangalore
    bangalore escort review
    bangalore model escorts

    ReplyDelete
  16. The kinds of programs might be a social event, corporate party, official meeting or some other occasions, but these babes are best in resolving all your issues.
    Dehradun Escorts
    Dehradun Escorts Agency
    Mussoorie Escorts
    Haridwar Escorts
    Rishikesh Escorts

    ReplyDelete
  17. If you desire to book the kinds of services from our ladies at your locations, then they are much prepared not to give you negative results. With their passion and dedication inside, these associates are going to unleash all types of boredoms and anxieties which is being confirmed.
    Chandigarh Escorts Agency
    Chandigarh Escort
    Chandigarh Escort Service
    Chandigarh Call Girls
    Vip Chandigarh Escorts

    ReplyDelete
  18. There is any occasion which you might enjoy being related to the ladies out here in agency. City Escorts are termed as the best of professionals whom you can easily hire using the Internet.
    Mumbai Escort
    Escort In Karnal
    Call Girls In Amritsar
    Jodhpur Escorts Service
    Jaipur Escorts Agency

    ReplyDelete
  19. CustomBoxestogo.co.uk provides customized double wall tuck front boxes, featuring a developing structure for holding of heavy-weight products. Such a variety of packaging boxes are consumed for the purpose of shipping as per the stipulation provided.Custom CBD pills boxes The functions of custom CBD pills boxes are surprising the dealers, by helping them achieve the desired packaging in either full-color printing or no printing at all. Looking for an effective packaging solution that not only packages your product, but adds value to your brand? You can grab no other better option than a customized packaging solution, printed with quality ink.

    ReplyDelete
  20. I love this. It is soo informative. Are you also searching for Swedish assignment writing help we are the best solution for you. We are best known for delivering the best services to students without having to break the bank

    ReplyDelete
  21. I love your creativity. Are you also searching for academic nursing admission essay? we are the best solution for you. We are best known for delivering quality essay writing services to students without having to break the bank

    ReplyDelete
  22. Enjoy The Beauty And Choose A Preferable Delhi Escorts Girl
    Enjoy your time in India with the best Delhi Escorts.The Models of our site has a great variety of girls and Escorts Models. Escorts of Delhi do everything to satisfy the wishes of our male and female clients.All you need to do – is to review profiles you like and make a call.
    Delhi Escorts
    Delhi Escorts Services
    Delhi Escort

    ReplyDelete
  23. Flavoured milk is a dairy product made by mixing milk, sugar or candy, colouring, and natural or artificial flavors. Inclusion of flavored milk as part of school meal plans in several countries and awareness among the population has been directly impacting the growth of flavoured milk market globally.

    ReplyDelete
  24. Thanks for Sharing communicative blog for sexual pleasure Book today Jodhpur Escorts for knowing about our desirable Jodhpur Call Girls.

    ReplyDelete
  25. The way you write is awesome you know the techniques that how to attract people for your writing its appreciated. If anybody needs excellent quality packaging and packaging services so Packaging Printing is making the good quality any type of Boxes, in any style material, size shape and color. Also Offering you free shipping and design support. Packaging Printing also Providing you the quality material of Candle Boxes and Votive Candle Boxes Wholesale with unique designs and fabulous Printing.

    ReplyDelete
  26. The team of our gurgaon escort service provides a 24-hour service and interview and personally trains all the women who collaborate with us. We regularly visit escort locations to make sure the ladies' apartment is luxurious enough and tailored to the needs of our clients. We only deal with serious and educated girls who are not only stunning women with a great body, but also kind, friendly and with a big heart.
    gurgaon escorts
    Udaipur escorts
    jodhpur escorts
    ajmer escorts
    jaipur escorts

    ReplyDelete
  27. We have different girls coming from all over the world, unique looks and individual approaches to life, there is something for everyone, whether you are looking for a laid back or laid back experience or a wild party with one of the best escorts in udaipur.
    Udaipur escorts
    jaipur escorts
    ajmer escorts
    udaipur escorts
    jodhpur escorts
    pushkar escorts

    ReplyDelete
  28. you have found our Gallery of Angels! This is the place to be when looking for a sexy escort in udaipur. As you can see, it is possible to search for your ideal udaipur escorts. From body type to hair colour and price to location, there is no reason why you can't find the perfect udaipur escort for you.
    udaipur escorts
    Jodhpur escorts
    nainital escorts
    ajmer escorts

    ReplyDelete
  29. To brighten your mood, it is possible to hire busty couples and feel hot and sensational. All men want to enjoy their mood with awesome people and make love with their body. To surprise yourself and excite your innermost sexual desires, consider hiring our beautiful escorts in gurgaon.
    Gurgaon escorts
    manesar escorts
    surajkund escorts
    iffco chowk escorts
    alwar escorts
    bhiwadi escorts
    rewari escorts

    ReplyDelete
  30. Custom Printing Services co.uk. it is the best printing services and the follow my website new technology printing in the best works try the link spoken. thanks lip gloss box sets | lip gloss box sets

    ReplyDelete
  31. Do you want to get a full exotic service? Mumbai Escorts Hub here at your service. You can meet with our call girls to make your time memorable full of enjoyment. We offer all types of sex with great satisfaction. Our Independent call Girl gives real massage along with full service of deep throat, cim, cum, anal and much more basically you think our ladies and would love to serve you without rushing. It's our passion and choice to be an escort that's why our call girls love to satisfy our clients for recommendation and referral.

    Mumbai Escorts Services:
    Mumbai Escorts | Mumbai Escorts Service |

    Mumbai Escorts Categories:
    Mumbai Sex Aunty Number | Phone Sex Mumbai | Mumbai Call Girl Whatsapp Number | Mumbai Call Girls Near Me |

    ReplyDelete
  32. Sex in JAIPUR Callgirls
    Call/Whatsapp - 8239556555
    I am happy to find to you we are the fundamental sex in jaipur service suppliers. We work in most rajasthan. Most our escort/call girls in jaipur are awarded to her workand give complete satisfaction to most of our customers. A confirmation to the 100% satisfaction with advantage and the most completed strategy of picking and also find the young woman Our point could be the gratification with a wide scope of associations. We are the best class and especially authentic office at jaipur.
    https://www.nishasharma.in/
    https://sexycallgirljaipur.com/
    escorts in mumbai
    escorts in dadar
    escorts in santacruz
    Escorts in Goa
    Escorts in bhiwadi
    Escorts in Jaipur

    ReplyDelete
  33. MEET GORGEOUS YOUNG CALL GIRLS FROM Delhi NOW
    Are you looking to meet the super hot and sexy Delhi model escorts? You don't have to worry about meeting our Delhi call escorts. Our Delhi escorts agency has the best escorts.
    Call us at our Delhi escort agency number and let us know that you would like to have intimate conversations with one of our sexy female escorts. We will immediately book the Delhi hot girls for you.These hot Delhi girls will make you want to meet them. These sexy escorts arrive in beach and bikini dresses. You can then take a moment to admire their beauty and get in touch with them. Respect their presence and behave professionally with them once they arrive at your home. Our Delhi escorts will not tolerate rude language. Treat them as a Royal Lady.
    Call us 9873071583
    https://sapnaoberoi.com/
    Escorts in Mahipalpur

    ReplyDelete
  34. Guys just sharing, I've found this interesting! Check it out!
    Erotic Massage in Berlin

    ReplyDelete
  35. If You are Looking For Escorts Service in Your City? Ascorts Club Escorts Agency - Who Provide Independent Call Girls, Model Escort, Celebrity Escort and More Kind Escorts Girls in India. Just Call us and Book Your Dream Girl.

    Ascorts Club Bhopal Escorts
    Ascorts Club Indore Escorts
    Ascorts Club Jaisalmer Escorts
    Ascorts Club Andheri Escorts

    ReplyDelete
  36. Welcome to Best Jaipur Escorts Service and Call Girls Agency. Call anytime To Book Female Escorts in Jaipur, Escort Girls Available 24/7.
    Jaipur Escorts Service

    ReplyDelete
  37. The team of our gurgaon escort service provides a 24-hour service and interview and personally trains all the women who collaborate with us. We regularly visit escort locations to make sure the ladies' apartment is luxurious enough and tailored to the needs of our clients. We only deal with serious and educated girls who are not only stunning women with a great body, but also kind, friendly and with a big heart.
    gurgaon escort service
    Udaipur escort service
    jodhpur escort service
    ajmer escort service
    jaipur escort service

    ReplyDelete
  38. Hire cheap and vivacious escorts in nainital to receive endless entertainment of the end times and lie back in their faithful arms. We are glad that you are showing enthusiasm for hot college girls in nainital. As you will see, escorts in nainital are beautiful and to satisfy them, you just need to call them.
    Nainital escort service
    haldwani escort service
    rudrapur escort service
    ramnagar escort service

    ReplyDelete
  39. Babes of jaipur is hiring hot women to join our fun loving team. If you have that special touch of beauty and enjoy connecting with new people, we would love to meet you. Please take a minute to fill out our jaipur escorts agency employment form, with as much detail as possible, and we will get in touch.
    jaipur escort service
    ajmer escort service
    udaipur escort service
    jodhpur escort service
    pushkar escort service

    ReplyDelete
  40. We offer a great escort service in Dehradun, which is famous for its nightlife. We offer Indian and Russian Escorts service in Dehradun, High profile, Indian call girls.
    Dehradun Escorts | Dehradun Call Girls | Escorts in Dehradun

    ReplyDelete
  41. Welcome Delhi Escort
    Welcome to Escorts Delhi. We offer high-profile, VIP female escorts in Delhi. These women give you more pleasure and complete enjoyment, because your body requires satisfaction. Delhi's Escort's priority is to make you happy and satisfied so you are impressed by our call girls.
    Call us 9873071583
    https://sapnaoberoi.com/
    Escorts in Mahipalpur

    ReplyDelete