AdWords
5.3K members online now
5.3K members online now
Dive into advanced features like Remarketing, Flexible Bid Strategies, AdWords Editor, and AdWords Scripts
Guide Me
star_border
Reply

Adwords Scripts - Accessing & Using Reports Data

[ Edited ]
Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

Hey, I'm hoping that someone out there might be able to point me in the right direction for a script I'm trying to put together. Basically, I'm trying to pull several stats for all keywords within a set of campaigns having a given label. Since one of the stats I need is conversion value, I understand that the only way for me to get that is by creating a report.  Unfortunately, I haven't been able to figure out how to create a report just for those keywords in the labeled campaigns, nor how to then use the report data to make bid changes to the actual keywords. 

 

My initial attempt to pull the stats looked like the following which seems to be working but then quickly slows down and seems to stop doing anything. Am I correct in assuming it's because the way in which I'm pulling individual reports for each keyword is inefficient? Just couldn't think of another way to do it.

 

Please let me know of any better ways to accomplish this. Thanks in advance!

 

function main() {
  // Get keywords for labeled campaigns
  var labelIter = AdWordsApp.labels().withCondition('Name = "Bid Adj"').get();
  if (labelIter.hasNext()) {
    var label = labelIter.next();
    var campaignIter = label.campaigns().get();
  }
  
  while (campaignIter.hasNext()){
    var campaign = campaignIter.next();
    var keywordIter = campaign.keywords().get();
    while (keywordIter.hasNext()){
      var keyword = keywordIter.next();
      processKeywordBids(keyword);
    }
  }  
}


function processKeywordBids(keyword) {
  var keywordName = keyword.getText();
  var report = AdWordsApp.report(
      'SELECT Clicks, Impressions, Cost, Conversions, ConversionValue, AveragePosition, CpcBid ' +
      'FROM KEYWORDS_PERFORMANCE_REPORT ' +
      "WHERE Criteria = '" + keywordName + "' " +
      'DURING LAST_30_DAYS');

  var rows = report.rows();

  if (rows.hasNext()) {
    var row = rows.next();
    
    var impressions = row['Impressions'];
    var clicks = row['Clicks'];
    var cost = row['Cost'];
    var conversions = row['Conversions'];
    var conversionVal = row['ConversionValue'];
    var avePos = row['AveragePosition'];
    var cpcBid = row['CpcBid'];

    Logger.log(keywordName + ': ' + clicks + ', ' + impressions + ', ' + cost);
  }

// do something with the gathered keyword stats here
}

 

2 Expert replyverified_user
1 ACCEPTED SOLUTION

Accepted Solutions
Marked as Best Answer.
Solution
Accepted by topic author Jared P
October 2016

Re: Adwords Scripts - Accessing & Using Reports Data

Top Contributor
# 4
Top Contributor

Hi @Jared P (thanks for the heads up and nice comment @James_Clemens!).

 

Processing an Account can sometimes be time consuming and the best way to cut that time down is to exclude unnecessary elements from your processes.  In this case a lot will depend upon whether you want to report/work upon Keywords that have no conversions.  If you only want those with conversions, you can reduce the data returned by the report.  However, the first thing I'd do is simplify and tighten up your Keyword selection process, something like this:

 

 

var DURATION = "LAST_30_DAYS";

function main() {
var campIter = AdWordsApp.campaigns()
.withCondition("Status = ENABLED")
.forDateRange(DURATION)
.orderBy("Cost DESC")
.get();
while(campIter.hasNext()) {
var thisCamp = campIter.next();
Logger.log("Campaign: " + thisCamp.getName());
var keyIter = thisCamp.keywords()
.withCondition("AdGroupStatus = ENABLED")
.withCondition("Status = ENABLED")
.forDateRange(DURATION)
.orderBy("Clicks DESC")
.get();
while(keyIter.hasNext()) {
var thisKeyword = keyIter.next();
processKeywordBids(thisKeyword);
}
}
}

Here I've defined a "global" variable for the duration (so we can just use it where we need it and change it easily), simplified the Campaign selection (one step, not two) and ensured we're only getting enabled Keywords from enabled Campaigns, etc.  I've also sorted the Campaigns by cost, and the Keywords by clicks (both descending); this makes sure we're doing the most expensive Keywords first (so if the script does time out, at least it's done the most important parts first).

 

In these selectors you could also add other conditions, depending upon what you want to process.  If you only want to work on Keywords with conversions for example, it'd be nice at this stage to only select those with conversions.  Unfortunately, the selector doesn't allow this but we can exclude Keywords that definitely won't have conversions by excluding those with no clicks, so you could add a condition of:

 

.withCondition("Clicks > 0")

either just at the Keyword iter level or at both.  

For the process, I'd again separate out Keywords with no conversions before asking for their report but you could skip this:

function processKeyword(thisKeyword) {
  var kStats = thisKeyword.getStatsFor(DURATION);
  var kConv = kStats.getConversions();
  if(kConv > 0) {
    var kReport = getKeyReport(DURATION,thisKeyword.getId(),thisKeyword.getAdGroup().getId());
    Logger.log(kReport.Clicks + ", " + kReport.Impressions + ", " + kReport.Cost);
  
  }
} 

For the report itself, I'd make a separate function; this not only keeps it clear in the code, but makes it easy to copy/paste into other scripts!  So:

 

function getKeyReport(during,thisKeyId,thisAdGroupId) {
var report = AdWordsApp.report(
"SELECT Clicks, Impressions, Cost, Conversions, ConversionValue, AveragePosition, CpcBid " +
"FROM KEYWORDS_PERFORMANCE_REPORT " +
"WHERE Id = '" + thisKeyId + "' " +
"AND AdGroupId = '" + thisAdGroupId + "' " +
"DURING " + during);
var repRows = report.rows();
if(repRows.hasNext()) {
return repRows.next();
}
else {
return false;
}
}

Here I'm using the Keyword and Ad Group ID (must be paired) rather than the text, as this avoids problems if you have the same Keyword more than once in the Account (for example, if you have multiple regional Campaigns).

 

So, now you have your data, what to do with it.  That really depends upon your objectives.  I'd probably start simple.  I'd work out an ROAS and then raise or lower CPC values depending upon whether the ROAS is above or below my target.  To get an ROAS figure, you'll need to use this line:

var ROAS = (kReport.ConversionValue.replace(",","") / kReport.Cost.replace(",","")).toFixed(2);

For reasons best known to Google, conversion and cost figures are returned as strings rather than numbers, so we have to remove the commas in larger numbers.  Performing the calculation also handily casts the result to a number.  I'd include some catches to make sure we don't reduce CPCs too far (or let them get too high) and this is the full script:

 

var DURATION = "LAST_30_DAYS";
var MINCPC = 0.20;
var MAXCPC = 5.00;
var CPCDOWN = 0.02;
var CPCUP = 0.03;
var ROASTAR = 10.00;

function main() {
  var campIter = AdWordsApp.campaigns()
    .withCondition("Status = ENABLED")
    .forDateRange(DURATION)
    .orderBy("Cost DESC")
    .get();
  while(campIter.hasNext()) {
    var thisCamp = campIter.next();
    Logger.log("Campaign: " + thisCamp.getName());
    var keyIter = thisCamp.keywords()
      .withCondition("AdGroupStatus = ENABLED")
      .withCondition("Status = ENABLED")
      .forDateRange(DURATION)
      .orderBy("Clicks DESC")
      .get();
    while(keyIter.hasNext()) {
      var thisKeyword = keyIter.next();
      processKeyword(thisKeyword);
    }
  }
}

function processKeyword(thisKeyword) {
  var kStats = thisKeyword.getStatsFor(DURATION);
  var kConv = kStats.getConversions();
  var kClicks = kStats.getClicks();
  var kCost = kStats.getCost();
  var kCpc = thisKeyword.bidding().getCpc();
  var newCpc = kCpc;
  var ROAS = 0;
  if(kConv > 0) {
    var kReport = getKeyReport(DURATION,thisKeyword.getId(),thisKeyword.getAdGroup().getId());
    ROAS = (kReport.ConversionValue.replace(",","") / kReport.Cost.replace(",","")).toFixed(2);
    if(ROAS < ROASTar) {
      newCpc = newCpc - CPCDOWN;
    }
    if(ROAS > ROASTar) {
      newCpc = newCpc + CPCUP;
    }
  }
  else {
    newCpc = newCpc - CPCDOWN;
  }
  // check limits on CPC
  if(newCpc > MAXCPC) newCpc = MAXCPC;
  if(newCpc < MINCPC) newCpc = MINCPC;
  // set the CPC
  thisKeyword.bidding().setCpc(newCpc);
  Logger.log(thisKeyword.getText() + ": "
             + "Clicks: " + kClicks
             + ", Conversions: " + kConv
             + ", Cost: " + kCost 
             + ", ROAS:" + ROAS
             + ", Old CPC: " + kCpc
             + ", New CPC: " + newCpc.toFixed(2));
}
  
function getKeyReport(during,thisKeyId,thisAdGroupId) {
  var report = AdWordsApp.report(
      "SELECT Clicks, Impressions, Cost, Conversions, ConversionValue, AveragePosition, CpcBid " +
      "FROM KEYWORDS_PERFORMANCE_REPORT " +
      "WHERE Id = '" + thisKeyId + "' " +
      "AND AdGroupId = '" + thisAdGroupId + "' " +
      "DURING " + during);
  var repRows = report.rows();
  if(repRows.hasNext()) {
    return repRows.next();
  }
  else {
    return false;
  }
}

I've left the code in a simple format (there are ways to shorten some of what I've done) and you'll see you don't really need to get cost/clicks from the report as I'm using them for stats when the Keywords don't have any conversions.

 

Have a play with this.  Some things I've done with my clients is create a "score" based upon the number of conversions as well as their ROAS, then using that score to adjust the CPC up or down.  I have other scripts that, rather than adjusting by an amount, adjust by calculation to hit a desired ROAS (so, if the desired ROAS is 10 and it's currently 5, the CPC would be halved).  You could also adjust CPCs by a percentage.

 

Let me know how you get on.


Jon

AdWords Top Contributor Google+ Profile | Partner Profile | AdWords Audits

View solution in original post

Adwords Scripts - Question About Reports

Top Contributor
# 2
Top Contributor

Hi @Jared P,

 

One of the smartest people I know with scripts would be @Jon_Gritton. He should reply once he has a moment.

 

Kind Regards,

 

James

 

_________________________________________________________
Google AdWords Top Contributor | Google Partner | GYBO | Local Guide | My Profile


 


 


 

Adwords Scripts - Question About Reports

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭

Thanks James. I'd seen many of Jon's responses to scripting questions here in the forum. Looking forward to any thoughts from him or anyone who can point me in the right direction. Have a great day!

 

Marked as Best Answer.
Solution
Accepted by topic author Jared P
October 2016

Re: Adwords Scripts - Accessing & Using Reports Data

Top Contributor
# 4
Top Contributor

Hi @Jared P (thanks for the heads up and nice comment @James_Clemens!).

 

Processing an Account can sometimes be time consuming and the best way to cut that time down is to exclude unnecessary elements from your processes.  In this case a lot will depend upon whether you want to report/work upon Keywords that have no conversions.  If you only want those with conversions, you can reduce the data returned by the report.  However, the first thing I'd do is simplify and tighten up your Keyword selection process, something like this:

 

 

var DURATION = "LAST_30_DAYS";

function main() {
var campIter = AdWordsApp.campaigns()
.withCondition("Status = ENABLED")
.forDateRange(DURATION)
.orderBy("Cost DESC")
.get();
while(campIter.hasNext()) {
var thisCamp = campIter.next();
Logger.log("Campaign: " + thisCamp.getName());
var keyIter = thisCamp.keywords()
.withCondition("AdGroupStatus = ENABLED")
.withCondition("Status = ENABLED")
.forDateRange(DURATION)
.orderBy("Clicks DESC")
.get();
while(keyIter.hasNext()) {
var thisKeyword = keyIter.next();
processKeywordBids(thisKeyword);
}
}
}

Here I've defined a "global" variable for the duration (so we can just use it where we need it and change it easily), simplified the Campaign selection (one step, not two) and ensured we're only getting enabled Keywords from enabled Campaigns, etc.  I've also sorted the Campaigns by cost, and the Keywords by clicks (both descending); this makes sure we're doing the most expensive Keywords first (so if the script does time out, at least it's done the most important parts first).

 

In these selectors you could also add other conditions, depending upon what you want to process.  If you only want to work on Keywords with conversions for example, it'd be nice at this stage to only select those with conversions.  Unfortunately, the selector doesn't allow this but we can exclude Keywords that definitely won't have conversions by excluding those with no clicks, so you could add a condition of:

 

.withCondition("Clicks > 0")

either just at the Keyword iter level or at both.  

For the process, I'd again separate out Keywords with no conversions before asking for their report but you could skip this:

function processKeyword(thisKeyword) {
  var kStats = thisKeyword.getStatsFor(DURATION);
  var kConv = kStats.getConversions();
  if(kConv > 0) {
    var kReport = getKeyReport(DURATION,thisKeyword.getId(),thisKeyword.getAdGroup().getId());
    Logger.log(kReport.Clicks + ", " + kReport.Impressions + ", " + kReport.Cost);
  
  }
} 

For the report itself, I'd make a separate function; this not only keeps it clear in the code, but makes it easy to copy/paste into other scripts!  So:

 

function getKeyReport(during,thisKeyId,thisAdGroupId) {
var report = AdWordsApp.report(
"SELECT Clicks, Impressions, Cost, Conversions, ConversionValue, AveragePosition, CpcBid " +
"FROM KEYWORDS_PERFORMANCE_REPORT " +
"WHERE Id = '" + thisKeyId + "' " +
"AND AdGroupId = '" + thisAdGroupId + "' " +
"DURING " + during);
var repRows = report.rows();
if(repRows.hasNext()) {
return repRows.next();
}
else {
return false;
}
}

Here I'm using the Keyword and Ad Group ID (must be paired) rather than the text, as this avoids problems if you have the same Keyword more than once in the Account (for example, if you have multiple regional Campaigns).

 

So, now you have your data, what to do with it.  That really depends upon your objectives.  I'd probably start simple.  I'd work out an ROAS and then raise or lower CPC values depending upon whether the ROAS is above or below my target.  To get an ROAS figure, you'll need to use this line:

var ROAS = (kReport.ConversionValue.replace(",","") / kReport.Cost.replace(",","")).toFixed(2);

For reasons best known to Google, conversion and cost figures are returned as strings rather than numbers, so we have to remove the commas in larger numbers.  Performing the calculation also handily casts the result to a number.  I'd include some catches to make sure we don't reduce CPCs too far (or let them get too high) and this is the full script:

 

var DURATION = "LAST_30_DAYS";
var MINCPC = 0.20;
var MAXCPC = 5.00;
var CPCDOWN = 0.02;
var CPCUP = 0.03;
var ROASTAR = 10.00;

function main() {
  var campIter = AdWordsApp.campaigns()
    .withCondition("Status = ENABLED")
    .forDateRange(DURATION)
    .orderBy("Cost DESC")
    .get();
  while(campIter.hasNext()) {
    var thisCamp = campIter.next();
    Logger.log("Campaign: " + thisCamp.getName());
    var keyIter = thisCamp.keywords()
      .withCondition("AdGroupStatus = ENABLED")
      .withCondition("Status = ENABLED")
      .forDateRange(DURATION)
      .orderBy("Clicks DESC")
      .get();
    while(keyIter.hasNext()) {
      var thisKeyword = keyIter.next();
      processKeyword(thisKeyword);
    }
  }
}

function processKeyword(thisKeyword) {
  var kStats = thisKeyword.getStatsFor(DURATION);
  var kConv = kStats.getConversions();
  var kClicks = kStats.getClicks();
  var kCost = kStats.getCost();
  var kCpc = thisKeyword.bidding().getCpc();
  var newCpc = kCpc;
  var ROAS = 0;
  if(kConv > 0) {
    var kReport = getKeyReport(DURATION,thisKeyword.getId(),thisKeyword.getAdGroup().getId());
    ROAS = (kReport.ConversionValue.replace(",","") / kReport.Cost.replace(",","")).toFixed(2);
    if(ROAS < ROASTar) {
      newCpc = newCpc - CPCDOWN;
    }
    if(ROAS > ROASTar) {
      newCpc = newCpc + CPCUP;
    }
  }
  else {
    newCpc = newCpc - CPCDOWN;
  }
  // check limits on CPC
  if(newCpc > MAXCPC) newCpc = MAXCPC;
  if(newCpc < MINCPC) newCpc = MINCPC;
  // set the CPC
  thisKeyword.bidding().setCpc(newCpc);
  Logger.log(thisKeyword.getText() + ": "
             + "Clicks: " + kClicks
             + ", Conversions: " + kConv
             + ", Cost: " + kCost 
             + ", ROAS:" + ROAS
             + ", Old CPC: " + kCpc
             + ", New CPC: " + newCpc.toFixed(2));
}
  
function getKeyReport(during,thisKeyId,thisAdGroupId) {
  var report = AdWordsApp.report(
      "SELECT Clicks, Impressions, Cost, Conversions, ConversionValue, AveragePosition, CpcBid " +
      "FROM KEYWORDS_PERFORMANCE_REPORT " +
      "WHERE Id = '" + thisKeyId + "' " +
      "AND AdGroupId = '" + thisAdGroupId + "' " +
      "DURING " + during);
  var repRows = report.rows();
  if(repRows.hasNext()) {
    return repRows.next();
  }
  else {
    return false;
  }
}

I've left the code in a simple format (there are ways to shorten some of what I've done) and you'll see you don't really need to get cost/clicks from the report as I'm using them for stats when the Keywords don't have any conversions.

 

Have a play with this.  Some things I've done with my clients is create a "score" based upon the number of conversions as well as their ROAS, then using that score to adjust the CPC up or down.  I have other scripts that, rather than adjusting by an amount, adjust by calculation to hit a desired ROAS (so, if the desired ROAS is 10 and it's currently 5, the CPC would be halved).  You could also adjust CPCs by a percentage.

 

Let me know how you get on.


Jon

AdWords Top Contributor Google+ Profile | Partner Profile | AdWords Audits

Adwords Scripts - Accessing &amp; Using Reports Data

Visitor ✭ ✭ ✭
# 5
Visitor ✭ ✭ ✭

Jon, thanks so much for the VERY thorough reply! I should be able to work on this in the next couple of days, and will let you know how it turns out. From what I can see here, this is exactly the guidance I needed.

 

Jared

 

Adwords Scripts - Accessing &amp; Using Reports Data

Visitor ✭ ✭ ✭
# 6
Visitor ✭ ✭ ✭

@Jon_Gritton, just wanted to confirm that your note gave me exactly what I needed in order to get this script working as I'd hoped. Thanks again!