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]); }
I've tried this and got the following error:
ReplyDeleteTypeError: Cannot call method "appendRow" of null. (line 38)
Any insight in how to fix this?
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.
ReplyDeleteThanks,
Russ
Hi Russell,
ReplyDeletethanks 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
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?
ReplyDeleteHi Russell,
ReplyDeletejust shared it with you; I've changed the spreadsheet name to be "Account1" and tried again but still same result.
Regards
Euan
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:
Deletevar SPREADSHEET_URL = "https://docs.google.com/spreadsheet/ccc?key=0Ajj1WA1LFvf1dFhBbE9hNWg2ZWg4MDdWcE1jVXQ1dFE"; //your spreadsheet url
var ACCOUNT_NAME = 'Account1';
Hi Russel,
ReplyDeletemanaged 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
I'm glad it finally worked! If you have any suggestions for new scripts, feel free to let me know.
DeleteThanks,
Russ
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)
ReplyDeleteAny ideas on what I can do to fix this.
Brendan
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.
DeleteLet me know if you still have issues after that and thanks for reading.
~Russ
how can we store high level impression kws instead of account level in another tab with the same sheet?
ReplyDeleteHi 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:
Deletehttps://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++;
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.
DeleteOn 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
i am a layman when it comes to js code..
Deletecan 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
how can we have data sent to separate google doc account outside of the adwords login email?
ReplyDeleteI've tried this and got the following error:
ReplyDeleteTypeError: Exceeded maximum execution time
Any insight in how to fix this?
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.
DeleteThanks,
Russ
It worked.
DeleteThanks!