Tuesday, February 26, 2013

Store Account Level Quality Score in a Google Spreadsheet

EDIT 2013-04-22: Check out an updated version of this script that stores Account, Campaign, and AdGroup level quality score.

EDIT 2013-03-18: I added DESC to the .orderBy() clause so that it orders the impressions from highest to least.

I was reading a post on SearchEngineLand.com the other day about storing and analyzing your account level quality score.

The Author of the article provided a script, but I figured I could come up with my own version pretty easily.  In the following script, I am using the same logic to calculate the account level quality score but I am looking at all campaigns and all keywords with an impression in the last 30 days.

Depending on your account size, this might be too much data to analyze in the 30 minute limit imposed by AdWords, but ordering by impressions should get you the top 50000 (again, another AdWords limit) keywords with impressions.

You can find the spreadsheet I am storing data in here: https://docs.google.com/spreadsheet/ccc?key=0Aotb6eheEOpodGNKMW1UWkZKekc5NWxkR3Zra3lzWVE

Your best bet is to copy that sheet, erase my data (except the headers) and copy the new spreadsheet url into the script. If you have multiple accounts that this will run in, you can store the data in the same spreadsheet but add additional tabs for the account name. Then update the ACCOUNT_NAME variable and you should be all set.

Thanks,
Russ

/***************************************
* Store Account Level Quality Score in Google Spreadsheet.
* Version 1.1
* ChangeLog v1.1
*  - Changed ACCOUNT_NAME to SHEET_NAME and updated the default value.
*  - Removed getSpreadsheet function
*
* Created By: Russ Savage
* Based on script originally found at: http://goo.gl/rTHbF
* FreeAdWordsScripts.com
*********************************/
function main() {
  var SPREADSHEET_URL = "Your Spreadsheet Url Goes Here";
  var SHEET_NAME = 'Sheet1';
  var today = new Date();
  var date_str = [today.getFullYear(),(today.getMonth() + 1),today.getDate()].join("-");
  
  var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var qs_sheet = spreadsheet.getSheetByName(SHEET_NAME);
  
  var kw_iter = AdWordsApp.keywords()
    .withCondition("Status = ENABLED")
    .forDateRange("LAST_30_DAYS")
    .withCondition("Impressions > 0")
    .orderBy("Impressions DESC")
    .withLimit(50000)
    .get();

  var tot_imps_weighted_qs = 0;
  var tot_imps = 0;
  
  while(kw_iter.hasNext()) {
    var kw = kw_iter.next();
    var kw_stats = kw.getStatsFor("LAST_30_DAYS");
    var imps = kw_stats.getImpressions();
    var qs = kw.getQualityScore();
    tot_imps_weighted_qs += (qs * imps);
    tot_imps += imps;
  }
    
  var acct_qs = tot_imps_weighted_qs / tot_imps;
  
  qs_sheet.appendRow([date_str,acct_qs]);
}

18 comments:

  1. I've tried this and got the following error:

    TypeError: Cannot call method "appendRow" of null. (line 38)

    Any insight in how to fix this?

    ReplyDelete
  2. That means for some reason your spreadsheet isn't put in there correctly. Make sure you start with my spreadsheet and then create a copy. Then paste that url into the script. Let me know if that solves your issue.

    Thanks,
    Russ

    ReplyDelete
  3. Hi Russell,

    thanks for the quick response.

    I've double checked and as far as I can see, I am using the URL for the copied Google Doc that sits in the Google Drive of the account I'm working on.

    I've tried removing the "#gid=0" from the end of the URL and that doesn't work either.

    I've tried a fresh G Doc only removing your data (not the headers), then in putting that URL into the script along with the account name.

    Same error results.

    Any further thoughts? Thanks for your help so far.

    Regards

    Euan

    ReplyDelete
  4. Could you share your spreadsheet with russellsavage at gmail dot com so that I could take a look? Is the name of the sheet in your spreadsheet Account1?

    ReplyDelete
  5. Hi Russell,

    just shared it with you; I've changed the spreadsheet name to be "Account1" and tried again but still same result.

    Regards

    Euan

    ReplyDelete
    Replies
    1. Hummm, I'm not sure what the issue is. Since you shared that doc with me, I was able to copy the exact script from this page, paste in the URL for your spreadsheet, and put data in their with no issues. This is what my variables look like at the top:

      var SPREADSHEET_URL = "https://docs.google.com/spreadsheet/ccc?key=0Ajj1WA1LFvf1dFhBbE9hNWg2ZWg4MDdWcE1jVXQ1dFE"; //your spreadsheet url
      var ACCOUNT_NAME = 'Account1';

      Delete
  6. Hi Russel,

    managed to get it working. I cleared out all the old scripts + spreadsheets from the GDrive account and started from scratch. This was the third time I'd tried again and it really was the charm!

    Thanks for your help and guidance. This has been my first use of an adwords script but your site has started getting the brain cogs working ;-)

    Regards

    Euan

    ReplyDelete
    Replies
    1. I'm glad it finally worked! If you have any suggestions for new scripts, feel free to let me know.

      Thanks,
      Russ

      Delete
  7. Hi Russell, I had quick go at using your script but I keep running into the same error - TypeError: Cannot call method "appendRow" of null. (line 38)

    Any ideas on what I can do to fix this.

    Brendan

    ReplyDelete
    Replies
    1. Hi Brendan, Euan was having that issue as well. The way he finally solved it was by starting with a fresh copy of my original spreadsheet and a fresh copy of the script. When you open the link to my spreadsheet above, make sure you go to "File > Make a copy..." and then copy the url of the copy into the script.

      Let me know if you still have issues after that and thanks for reading.

      ~Russ

      Delete
  8. how can we store high level impression kws instead of account level in another tab with the same sheet?

    ReplyDelete
    Replies
    1. Hi Marco, It is pretty simple to adjust the script above to fit your use case. Here is a sample spreadsheet I made that you can copy:

      https://docs.google.com/spreadsheet/ccc?key=0Aotb6eheEOpodFZOU2F4V3cxVDl2bTNKVDZtOFhmQnc

      Next, you need to make a few changes to the script above:
      1. Right after you create your qs_sheet, you need to create a variable to another sheet for storing your keywords with impressions. Add this code just after var qs_score which will clear the sheet and add the column headers.

      var kw_sheet = spreadsheet.getSheetByName("Account1 Keywords");
      kw_sheet.clear();
      kw_sheet.appendRow(['Campaign','AdGroup','Keyword','MatchType','Imps']);

      2. Next, update your keyword iteration loop to append the keywords to the sheet:
      kw_sheet.appendRow([kw.getCampaign().getName(),kw.getAdGroup().getName(),kw.getText(),kw.getMatchType(),imps]);

      3. If you want to be fancy and only store the top 50 keywords, you can add a keyword counter and wrap the statement from number 2 in an if statement (don't forget to declare tot_kws outside of the loop and set it to 0 ):

      if(tot_kws <= 50) {
      kw_sheet.appendRow([kw.getCampaign().getName(),kw.getAdGroup().getName(),kw.getText(),kw.getMatchType(),imps]);
      }
      tot_kws++;

      Delete
    2. One more thing Marco, if you want to store additional data at an account level, all you need to do is collect the data in the keyword while loop above.

      On line 25, I declare var tot_imps = 0; If you want additional metrics at the account level, you can add lines underneath that such as var tot_clicks = 0; or var tot_cost = 0;

      Next, after line 30, where I am pulling the impressions from the kw_stats object, you can say something like:

      tot_clicks += kw_stats.getClicks();
      tot_cost += kw_stats.getCost();

      And finally, you want to store those values in the spreadsheet. So update line 38 to be:

      qs_sheet.appendRow([date_str,acct_qs,tot_clicks,tot_cost]);

      Hopefully that helps. Let me know if you have any other questions.

      Thanks,
      Russ

      Delete
    3. i am a layman when it comes to js code..

      can you make a post on storing account level stats like the following...

      Max. CPC
      Clicks Impr.
      CTR
      Avg. CPC
      Qual. score
      Est. top page bid
      Avg. Pos.
      Conv. (1-per-click)
      Cost / conv. (1-per-click)
      Conv. rate (1-per-click)
      Cost
      Total conv. value
      Conv. value / cost

      Delete
  9. how can we have data sent to separate google doc account outside of the adwords login email?

    ReplyDelete
  10. I've tried this and got the following error:

    TypeError: Exceeded maximum execution time

    Any insight in how to fix this?

    ReplyDelete
    Replies
    1. Hi cafeemarketing, If you got this error when you tried to preview the script, it is not an issue. The preview function will stop the script after 30 seconds no matter what. Just try running the script and it will run for up to 30 minutes instead.

      Thanks,
      Russ

      Delete
    2. It worked.

      Thanks!

      Delete