Friday, May 3, 2013

Put AdWords Data to Amazon S3 Using Scripts

I'm not sure about you, but writing all these scripts is putting me on Google Spreadsheet overload. With the release of the ability to access any of the AdWords reports, the data quickly starts to get unwieldy. Once things get over a few thousand rows, I much prefer to use Excel or store and manipulate data in a database.

So I was looking for other ways to get data from an AdWords report out of the scripts world and I thought of Amazon S3. Amazon offers a RESTful API through their Amazon Web Services that allows you to store and retrieve large amounts of data from the cloud. I figured I could use them to store some CSV files of account/campaign/keyword performance for downloading or processing later, maybe by another script or software.

The script below tries to encapsulate all the logic required to build and authorize a proper S3 put request. I followed the documentation found here.

To get started for free, sign up for Amazon S3 here and find your access and secret keys here:


Next, create a new S3 bucket to hold all your data through the AWS console. Fill in the details below and try a test file. Ideally, you would combine this with one of the other AdWords reporting scripts found here to start storing your data in the cloud.

NOTE: This is a very specific set of functions which represent the bare minimum required information to PUT a file to Amazon S3. For more information, please check out the full Amazon S3 Docs.

Thanks,
Russ

//-----------------------------------
// Put Data To Amazon S3
// Created By: Russ Savage
// FreeAdWordsScripts.com
//-----------------------------------
var ACCESS_KEY = 'YOUR_ACCESS_KEY_HERE';
var SECRET_KEY = 'YOUR_SECRET_KEY_HERE';
var S3_BUCKET = 'YOUR_S3_BUCKET_NAME_HERE';
  

function main() {
  var date_str = Utilities.formatDate(new Date(),AdWordsApp.currentAccount().getTimeZone(),'yyyy-MM-dd');
  var file_name = 'adwords_keyword_perf_'+date_str+'.csv';
  putDataToBucket(S3_BUCKET,'/'+file_name,'this is where the data from an AdWords report would go.');
}


function putDataToBucket(bucket,file_path,data) {
  var auth_options = {  
    method : 'PUT',
    base_url : "http://" + bucket + ".s3.amazonaws.com",
    s3_bucket : bucket,
    path : file_path,
    headers : { 
       "Date" : getDate(),
       "Content-Type" : "application/x-www-form-urlencoded"
    },
  };
  
  var auth_string = generateAuthString(auth_options);
  auth_options.headers["Authorization"] = auth_string;
  
  var options = {
    method : auth_options.method,
    headers : auth_options.headers,
    payload : data
  };
  
  return (UrlFetchApp.fetch(auth_options.base_url+auth_options.path, options).getResponseCode() == 200);
}

//Generates an AWS Auth String
//For more info, see the AWS docs - http://goo.gl/m5nCe
function generateAuthString(url) {
  var string_to_sign =  getStringToSign(url);
  Logger.log(string_to_sign);
  var signature = getSignature(SECRET_KEY,string_to_sign);
  return "AWS" + " " + ACCESS_KEY + ":" + signature;
}

//Generates an AWS Signature
//For more info, see the AWS docs - http://goo.gl/m5nCe
function getSignature(SECRET_KEY,string_to_sign) {
  return  Utilities.base64Encode(
    Utilities.computeHmacSignature(
      Utilities.MacAlgorithm.HMAC_SHA_1,
      string_to_sign,
      SECRET_KEY,
      Utilities.Charset.UTF_8
    )
  );
}

//Generates an AWS string to sign
//For more info, see the AWS docs - http://goo.gl/m5nCe
function getStringToSign(url,params) {
  var method = url.method;
  var date = url.headers.Date;
  
  return method + "\n" + "\n" + 
    url.headers['Content-Type'] + "\n" +
    date + "\n" + 
    getCanonicalizedAmzHeaders(url);
}

//Generates the Canonicalized Amazon Headers (not really)
//For more info, see the AWS docs - http://goo.gl/m5nCe
function getCanonicalizedAmzHeaders(url) {
  var ret_val = "/" + url.s3_bucket;
  ret_val += url.path;
  return ret_val;
}

function getDate() {
  return Utilities.formatDate(new Date(),"GMT", "EEE, dd MMM yyyy HH:mm:ss +0000");
}

37 comments:

  1. Thank you for the informative post about Security challenges in AWS , Found it useful . cloud migration services have now become secured and with no-risk

    Cloud Migration services

    Aws Cloud Migration services

    Azure Cloud Migration services

    ReplyDelete
  2. I have gone through your post and I found it very helpfull. Looking forward to see more post from you.
    Vmware Cloud Migration services

    Database Migration services

    ReplyDelete
  3. I am really impressed with the way of writing of this blog. The author has shared the info in a crisp and short way.
    Lia Infraservices

    ReplyDelete
  4. We are a part of the success story for many of our customer's successful cloud Migrations.
    Cloud Migration services


    Best Cloud Migration Tool

    ReplyDelete
  5. The straightforward truth is that not many organizations will ever have the option to contend with Amazon. So why trouble?Buy amazon traffic

    ReplyDelete
  6. The writer has truly done equity with his perusers and his work.
    Download Alexa app

    ReplyDelete
  7. When combined with the experience of entering the supplier's first international market, a company can transform the plan into a policy or procedure for entering new markets in the future. click

    ReplyDelete
  8. I just got to this amazing site not long ago. I was actually captured with the piece of resources you have got here. Big thumbs up for making such wonderful blog page! mp3 songs

    ReplyDelete
  9. Nice information, valuable and excellent design, as share good stuff with good ideas and concepts, lots of great information and inspiration, both of which I need, thanks to offer such a helpful information here. pagalworld music

    ReplyDelete
  10. Instruments like this empower them to screen sections of land of land and furthermore help them in tending to things that impact yield before they become a matter of issues because of the nonattendance of excrement, low clamminess, and so forth. Data Analytics Course in Bangalore

    ReplyDelete
  11. A very awesome blog post. We are really grateful for your blog post. You will find a lot of approaches after visiting your post. Admond Lee

    ReplyDelete
  12. Will there be an upfront cost for the appliance or specialist shop service call as well as an itemized estimate given before maintenance of the appliance or will all charges be totaled when the appliance is fully repaired on the final bill? Property Management in Los Angeles

    ReplyDelete
  13. Positive site, where did u come up with the information on this posting?I have read a few of the articles on your website now, and I really like your style. Thanks a million and please keep up the effective work. anaconda install tensorflow

    ReplyDelete
  14. You make so many great points here that I read your article a couple of times. Your views are in accordance with my own for the most part. This is great content for your readers. negative feedback

    ReplyDelete
  15. These are just a few examples of how technology products can save you money. Technology

    ReplyDelete
  16. Keep up the good work , I read few posts on this web site and I conceive that your blog is very interesting and has sets of fantastic information. buzzoid instagram likes

    ReplyDelete
  17. Glad to chat your blog, I seem to be forward to more reliable articles and I think we all wish to thank so many good articles, blog to share with us. netsuite data entry

    ReplyDelete
  18. The first phase the preparation should, theoretically, be uninfluenced by the intended intensity and duration of the sound which is subsequently produced. In fact, however, so quickly are the three phases accomplished that the pianist rarely has capacity to think, in performance, of each phase separately. data entry bookkeeping

    ReplyDelete
  19. By outsourcing the services you receive to India, get ready to enjoy the advantage of saving up to 70% cost incurred on business operations. Data entry could be the hub from a business even though it might look like simple to manage and handle, this involves many processes that need to be dealt systematically bookkeeping data entry

    ReplyDelete
  20. By outsourcing the services you receive to India, get ready to enjoy the advantage of saving up to 70% cost incurred on business operations. Data entry could be the hub from a business even though it might look like simple to manage and handle, this involves many processes that need to be dealt systematically. bookkeeping data entry

    ReplyDelete
  21. Very good points you wrote here..Great stuff...I think you've made some truly interesting points.Keep up the good work.
    360digitmg python course training in Guwahati

    ReplyDelete
  22. All things considered, I will reveal to you this; a significant number of the organizations I investigated shared a great deal for all intents and purpose.data entry usa

    ReplyDelete
  23. Finally, there are some organizations that need a lot of data entry done and outsource data entry to India you can visit their web sites and sign up for their programs to have a constant stream of work and revenue.

    ReplyDelete
  24. Thanks for the blog post buddy! Keep them coming... for more info

    ReplyDelete
  25. A good blog always comes-up with new and exciting information and while reading I have feel that this blog is really have all those quality that qualify a blog to be a one. inventory data entry

    ReplyDelete
  26. Add to this their perpetually expanding content network, advanced reporting and analytics and their position as the worlds primary search engine and it's not surprising that more and more companies are looking for specialist AdWords services. adwords specialist

    ReplyDelete
  27. What are some of the benefits of using URL shorteners? Perhaps the main benefit is that a short URL is easier for copying into an email, a forum post, or a post on Twitter, where the poster is limited to 140 characters for the entire post. best paying url shortener

    ReplyDelete