AdWords
3.4K members online now
3.4K members online now
For questions related to Google Shopping and Merchant Center. Learn to optimize your Shopping ads
Guide Me
star_border
Reply

Help to get a Shopping Automation Script

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

Hello, I'm looking for an automation script for my shopping campaign. The script should be able to help me stop a product that is getting more clicks than X amount of clicks or money. Something that can stop the particular product. I saw a product that for some unknown reason got almost a 100 clicks and produced no good results. I need to be able to automate this process, can you please help me? Thank you

1 Expert replyverified_user
1 ACCEPTED SOLUTION

Accepted Solutions
Marked as Best Answer.
Solution
Accepted by topic author Jerry B
September 2016

Re: Help to get a Shopping Automation Script

Top Contributor
# 4
Top Contributor

Hi @Jerry B (Jenny?), stopping the products that don't perform may not be ideal as the only way to do that completely at the AdWords level is to exclude or filter them and in my experience, doing this can make management difficult.  I'd be inclined simply to reduce their CPC bids to 0.01 which, in most cases is probably going to stop their Ads appearing (or at the very least will substantially reduce their spend).


I've included a script I use at the end of this message, most of the variables should be self-explanatory.  The script works best against individual products (item ids) so the ACTIVE_LEVEL is tricky, this is the level in the hierarchy of the product group, which starts at 0 and increases with each segregation.  So, if you had All Products->Brand->Items, "All Products" is level 0, Brand is level 1 and items is level 2.  Testing (previewing) the script will help find the right level.  You also have a choice whether to change bids gradually using percentages or try to hit the "perfect" CPC based upon your ROAS target.  

 

In your case, I'd consider setting a low value for MINCPC to allow these underperformers to reach low CPCs quickly, but have a play.  DO make sure you preview this script before running it to make sure it's doing what you expect.

 

In this config the Script will affect ALL enabled Shopping Ad Groups, if you want it to work on only a subset, you could use Labels and a condition.

 

Jon

 

 

// Shopping bids script
// (c) Jon Gritton 2016

var MAXCPC = 2.50;  // the most you're willing to bid
var MINCPC = 0.35;  // the lowest CPC that will be set - usually you'll want to allow some impressions
var PERINC = 1.03;  // percentage increase in CPC for high performers; 1.03 = 3%
var MONDEC = 0.01;  // actual decrease amount for low performance, adjust this to suit typical CPCs
var ROASTAR = 10.5; // target ROAS (as shown in ConversionValue/Cost column)
var DURING = 70;    // lookback period for conversion checking, usually more than 30 days
var ROASABS = true; // use ROAS target to calculate CPCs (ignores percentage increase/decrease)
var ACTIVE_LEVEL = 1; // the "level" of the individual products where 0 is the root of the Group.  Most products will be level 1 or 2
var MIN_CHANGE = 0.02; // the minimum change in CPC when using ROASABS = true (ignored if ROASABS = false)

function main() {
  var groupIter = AdWordsApp.shoppingAdGroups()
    .withCondition("CampaignStatus = ENABLED")
    .withCondition("AdGroupStatus = 'ENABLED'")
    .forDateRange("LAST_7_DAYS")
    .orderBy("Clicks DESC")
    .get();
  var d = new Date();
  var now = Utilities.formatDate(new Date(),AdWordsApp.currentAccount().getTimeZone(), 'yyyyMMdd');
  d.setDate(d.getDate()-DURING);
  var then = Utilities.formatDate(d,AdWordsApp.currentAccount().getTimeZone(), 'yyyyMMdd');
  while (groupIter.hasNext() ) {
    var thisGroup = groupIter.next();
    Logger.log("Campaign: " + thisGroup.getCampaign().getName() + ", Product Group: " + thisGroup.getName());
    var root = thisGroup.rootProductGroup();
    walkHierarchy(root, 0, now, then);
  }
}

function walkHierarchy(productGroup, level, now, then) {
  var description = '';

  if (productGroup.isOtherCase()) {
    description = 'Other';
  } else if (productGroup.getDimension() == 'CATEGORY') {
    // Shows how to process a product group differently based on its type.
    description = productGroup.asCategory().getName();
  } else {
    description = productGroup.getValue();
  }

  var thisDimension = productGroup.getDimension();
  if(thisDimension = "ITEM_ID"){
    var thisStats = productGroup.getStatsFor(then, now);
    var thisConversions = thisStats.getConversions();
    var currentCPC = productGroup.getMaxCpc();
    var newCPC = currentCPC;
    if(thisConversions > 0  && level == ACTIVE_LEVEL && description != "Other") {
      var perfReport = getReportRowForDuring(now,then,description);
      var rows = perfReport.rows();
      while (rows.hasNext()) {
        var thisRow = rows.next();
        var thisCost = thisRow['Cost'];
        var thisValue = thisRow['ConversionValue'];
        var thisAvCPC = thisRow['AverageCpc'];
        var ROAS = (thisValue.replace(",","") / thisCost.replace(",",""));
        
        if(!ROASABS) {
          // alter CPCs gradually
          if(ROAS <= ROASTAR) {
            if (ROAS >= (ROASTAR*0.8)) {
              // close to ROAS target, so keep bids higher than MINCPC, or reduce if too high still
              newCPC = ((currentCPC - MONDEC) > (MINCPC*1.5))? currentCPC - MONDEC : MINCPC*1.5;
            }
            else {
              // way off ROAS target, reduce bid by MONDEC
              newCPC = currentCPC - MONDEC;
            }
          }
          if(ROAS > (ROASTAR*1.3)) {
            // well above ROAS target, increase bids
            //without any other conditions, increase bid
            newCPC = currentCPC * PERINC;
            // make sure this will result in an actual increase
            if((newCPC - currentCPC) < MIN_CHANGE) {
              newCPC = currentCPC + MIN_CHANGE;
            }
          }
        }
        else {
          // calculate CPC to hit ROAS exactly
          newCPC = (ROAS / ROASTAR) * thisAvCPC;
        }
      }
    }
    else if(level > 0 && thisConversions == 0 && description !="Other") {
	    newCPC = currentCPC - MONDEC;
	}
    // check for absolute limits and log
    if(newCPC > MAXCPC) newCPC = MAXCPC;
    if(newCPC < MINCPC) newCPC = MINCPC;
    newCPC = newCPC.toFixed(2);
    if(thisConversions > 0 && level > 0) {
      Logger.log('Desc: %s, Conversions: %s, Cost: %s, Value: %s, ROAS: %s, Curr.CPC: %s, Av.CPC: %s, New CPC: %s',
                   description,
                   thisConversions,
                   thisCost,
                   thisValue,
                   ROAS,
                   currentCPC,
                   thisAvCPC,
                   newCPC
                  );
    }
    productGroup.setMaxCpc(newCPC);
  }
  
  // Note: Child product groups may not have a max cpc if it is excluded.
  var childProductGroups = productGroup.children().get();
  while (childProductGroups.hasNext()) {
    var childProductGroup = childProductGroups.next();
    walkHierarchy(childProductGroup, level + 1, now, then);
  }
}

function getReportRowForDuring(now,then,thisID) {
  var report = AdWordsApp.report(
      "SELECT Cost, ConversionValue, AverageCpc " +
      "FROM SHOPPING_PERFORMANCE_REPORT " +
      "WHERE OfferId = '" + thisID + "' " + 
      "DURING " + then + "," + now);
  return report;
}
AdWords Top Contributor Google+ Profile | Partner Profile | AdWords Audits

View solution in original post

Re: Help to get a Shopping Automation Script

Top Contributor
# 2
Top Contributor

Hi Jerry, it's possible to do what you want with a script, but scripts for Shopping are a little more complex than those used for other Campaign types, so I'd need some more details before I could show any code.

 

Can you describe the structure of your Product Group?  Have you split the products by brand, then item id, or in some other way?

 

Jon

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

Re: Help to get a Shopping Automation Script

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭
Hi Jon, thank you for your reply. Yes, I'm new at this but based on some research and advice, I split the products into 11 campaigns, 10 being brands and then the main one. I do exclude All Other in every brand campaign and right now all have same Campaign Priority: Medium. I also have 3 Product Groups: #0 for inexpensive items, #1 for New Items and #2 for Bundles.

I'm just trying to figure it out what works and what does not in the products I sell but I'm very concerned about some products that seem to get a lot of clicks for a day, eating more than half of my budget. You are correct, the only scripts I found are for text campaigns and what I found about Shopping Campaigns is for very basic tasks such as reporting. By any chance do you have any script code that help me stop products that get and x number or clicks or amount of money? Thank you very much for your help Jon

Kind Regards,

Jenny
Marked as Best Answer.
Solution
Accepted by topic author Jerry B
September 2016

Re: Help to get a Shopping Automation Script

Top Contributor
# 4
Top Contributor

Hi @Jerry B (Jenny?), stopping the products that don't perform may not be ideal as the only way to do that completely at the AdWords level is to exclude or filter them and in my experience, doing this can make management difficult.  I'd be inclined simply to reduce their CPC bids to 0.01 which, in most cases is probably going to stop their Ads appearing (or at the very least will substantially reduce their spend).


I've included a script I use at the end of this message, most of the variables should be self-explanatory.  The script works best against individual products (item ids) so the ACTIVE_LEVEL is tricky, this is the level in the hierarchy of the product group, which starts at 0 and increases with each segregation.  So, if you had All Products->Brand->Items, "All Products" is level 0, Brand is level 1 and items is level 2.  Testing (previewing) the script will help find the right level.  You also have a choice whether to change bids gradually using percentages or try to hit the "perfect" CPC based upon your ROAS target.  

 

In your case, I'd consider setting a low value for MINCPC to allow these underperformers to reach low CPCs quickly, but have a play.  DO make sure you preview this script before running it to make sure it's doing what you expect.

 

In this config the Script will affect ALL enabled Shopping Ad Groups, if you want it to work on only a subset, you could use Labels and a condition.

 

Jon

 

 

// Shopping bids script
// (c) Jon Gritton 2016

var MAXCPC = 2.50;  // the most you're willing to bid
var MINCPC = 0.35;  // the lowest CPC that will be set - usually you'll want to allow some impressions
var PERINC = 1.03;  // percentage increase in CPC for high performers; 1.03 = 3%
var MONDEC = 0.01;  // actual decrease amount for low performance, adjust this to suit typical CPCs
var ROASTAR = 10.5; // target ROAS (as shown in ConversionValue/Cost column)
var DURING = 70;    // lookback period for conversion checking, usually more than 30 days
var ROASABS = true; // use ROAS target to calculate CPCs (ignores percentage increase/decrease)
var ACTIVE_LEVEL = 1; // the "level" of the individual products where 0 is the root of the Group.  Most products will be level 1 or 2
var MIN_CHANGE = 0.02; // the minimum change in CPC when using ROASABS = true (ignored if ROASABS = false)

function main() {
  var groupIter = AdWordsApp.shoppingAdGroups()
    .withCondition("CampaignStatus = ENABLED")
    .withCondition("AdGroupStatus = 'ENABLED'")
    .forDateRange("LAST_7_DAYS")
    .orderBy("Clicks DESC")
    .get();
  var d = new Date();
  var now = Utilities.formatDate(new Date(),AdWordsApp.currentAccount().getTimeZone(), 'yyyyMMdd');
  d.setDate(d.getDate()-DURING);
  var then = Utilities.formatDate(d,AdWordsApp.currentAccount().getTimeZone(), 'yyyyMMdd');
  while (groupIter.hasNext() ) {
    var thisGroup = groupIter.next();
    Logger.log("Campaign: " + thisGroup.getCampaign().getName() + ", Product Group: " + thisGroup.getName());
    var root = thisGroup.rootProductGroup();
    walkHierarchy(root, 0, now, then);
  }
}

function walkHierarchy(productGroup, level, now, then) {
  var description = '';

  if (productGroup.isOtherCase()) {
    description = 'Other';
  } else if (productGroup.getDimension() == 'CATEGORY') {
    // Shows how to process a product group differently based on its type.
    description = productGroup.asCategory().getName();
  } else {
    description = productGroup.getValue();
  }

  var thisDimension = productGroup.getDimension();
  if(thisDimension = "ITEM_ID"){
    var thisStats = productGroup.getStatsFor(then, now);
    var thisConversions = thisStats.getConversions();
    var currentCPC = productGroup.getMaxCpc();
    var newCPC = currentCPC;
    if(thisConversions > 0  && level == ACTIVE_LEVEL && description != "Other") {
      var perfReport = getReportRowForDuring(now,then,description);
      var rows = perfReport.rows();
      while (rows.hasNext()) {
        var thisRow = rows.next();
        var thisCost = thisRow['Cost'];
        var thisValue = thisRow['ConversionValue'];
        var thisAvCPC = thisRow['AverageCpc'];
        var ROAS = (thisValue.replace(",","") / thisCost.replace(",",""));
        
        if(!ROASABS) {
          // alter CPCs gradually
          if(ROAS <= ROASTAR) {
            if (ROAS >= (ROASTAR*0.8)) {
              // close to ROAS target, so keep bids higher than MINCPC, or reduce if too high still
              newCPC = ((currentCPC - MONDEC) > (MINCPC*1.5))? currentCPC - MONDEC : MINCPC*1.5;
            }
            else {
              // way off ROAS target, reduce bid by MONDEC
              newCPC = currentCPC - MONDEC;
            }
          }
          if(ROAS > (ROASTAR*1.3)) {
            // well above ROAS target, increase bids
            //without any other conditions, increase bid
            newCPC = currentCPC * PERINC;
            // make sure this will result in an actual increase
            if((newCPC - currentCPC) < MIN_CHANGE) {
              newCPC = currentCPC + MIN_CHANGE;
            }
          }
        }
        else {
          // calculate CPC to hit ROAS exactly
          newCPC = (ROAS / ROASTAR) * thisAvCPC;
        }
      }
    }
    else if(level > 0 && thisConversions == 0 && description !="Other") {
	    newCPC = currentCPC - MONDEC;
	}
    // check for absolute limits and log
    if(newCPC > MAXCPC) newCPC = MAXCPC;
    if(newCPC < MINCPC) newCPC = MINCPC;
    newCPC = newCPC.toFixed(2);
    if(thisConversions > 0 && level > 0) {
      Logger.log('Desc: %s, Conversions: %s, Cost: %s, Value: %s, ROAS: %s, Curr.CPC: %s, Av.CPC: %s, New CPC: %s',
                   description,
                   thisConversions,
                   thisCost,
                   thisValue,
                   ROAS,
                   currentCPC,
                   thisAvCPC,
                   newCPC
                  );
    }
    productGroup.setMaxCpc(newCPC);
  }
  
  // Note: Child product groups may not have a max cpc if it is excluded.
  var childProductGroups = productGroup.children().get();
  while (childProductGroups.hasNext()) {
    var childProductGroup = childProductGroups.next();
    walkHierarchy(childProductGroup, level + 1, now, then);
  }
}

function getReportRowForDuring(now,then,thisID) {
  var report = AdWordsApp.report(
      "SELECT Cost, ConversionValue, AverageCpc " +
      "FROM SHOPPING_PERFORMANCE_REPORT " +
      "WHERE OfferId = '" + thisID + "' " + 
      "DURING " + then + "," + now);
  return report;
}
AdWords Top Contributor Google+ Profile | Partner Profile | AdWords Audits

Re: Help to get a Shopping Automation Script

Visitor ✭ ✭ ✭
# 5
Visitor ✭ ✭ ✭
Wow Jon, thank you very much for your help and explanation, really I appreciate it very much. I'm not a programmer at all but I learn fast. I will test for sure but I wanted to ask, if I implement it, will it change the custom bids for the products I already reviewed, I usually have it at 32 cents but in some cases some I have lowered and some I have increased. Will the script change everything? Thank you very much Jon!

Kind Regards,

Jenny

Help to get a Shopping Automation Script

Visitor ✭ ✭ ✭
# 6
Visitor ✭ ✭ ✭

Wow, this seems like a great script. I would like to use it for my webshop Patipada.nl.
I am wondering,.. how many times does te script make a modification to the bidding. Is this daily?

Re: Help to get a Shopping Automation Script

Visitor ✭ ✭ ✭
# 7
Visitor ✭ ✭ ✭

Hi @Jon_Gritton and thank you very much for your amazing script !

 

It's working fine for a majority of products (in some of them the script don't extract the actual max cpc and put directly the new cpc to the minium volume) but the big issue is that the script is stopped after 30min due to the adwords script limit. I'm wondering if you have find any solutions to bypass this limit ?

 

Thanks again for your amazing help and contributions in this community.

 

Paul