Calculating ROI (total con. value / cost) in adwords script

[Žygimantas Balsys]

Hi,

Could you please help me with a script to calculate ROI? I am using a code like below. My goal is to increase bids for product groups which has total conv. value / cost > X and search impr. share < Y. But I don't know how to write a script to calculate conv. value / cost. After all this value is the adwords column and it is calculated. 

I'd appreciate any help.

Thank you.

function main() {

//Raise bids by 5% when... - 3 month

updateProductGroupBid();

  

function updateProductGroupBid() {

  var productGroups0 = AdWordsApp.productGroups()

     .withCondition('CampaignName CONTAINS "Shopping"')

     .withCondition("Conv. value / cost > 3")

     .forDateRange("ALL_TIME")

     .get();

   while (productGroups0.hasNext()) {

    var productGroup = productGroups0.next();

    productGroup.setMaxCpc(productGroup.getMaxCpc() * 1.05)

  }

}

}

[Hiroyuki Miyauchi (AdWords Scripts Team)]

Hello Žygimantas,

 

Regarding fetching productGroups with "total conv. value / cost > X" condition, it is not possible to filter using withCondition. You can see the current available operators in this documentation.

As a workaround, you may utilize Product Partition Report which has ConversionValue field and Cost field. After getting those two fields for each row iteration, you may perform an if-condition to check if ConversionValue / Cost > 3.

Please let me know if you have further clarifications.

Regards,

Hiroyuki

AdWords Scripts Team

[Žygimantas Balsys]

Thank you for the suggestion. Unfortunately I am by far not that skilled to perform Product Partition Report. I am going to look for someone who will be able to write a necessary script for me.

Thanks.

[Hiroyuki Miyauchi (AdWords Scripts Team)]

Hello Žygimantas,

To help you get started, you may refer to the sample code below, but feel free to modify this based on your preference:

function main() {

  var report = AdWordsApp.report(
      'SELECT ConversionValue, Cost, CpcBid ' +
      'FROM   PRODUCT_PARTITION_REPORT ' +
      'WHERE  CampaignName CONTAINS "Shopping"');

  var rows = report.rows();  
 
  while (rows.hasNext()) {
    var row = rows.next();
    var conversionValue = row['ConversionValue'];
    var cost = row['Cost'];
    var cpcBid = row['CpcBid'];
   
    if(conversionValue / cost > 3){
      //get the ProductGroup object then perform your bid updates
    }    
  }

}

Please let me know if you have any clarifications.

[Žygimantas Balsys]

Thanks. I was trying to go with something like this for instead of this:

    //get the ProductGroup object then perform your bid updates 

Again probably this is very simple but not for me who has zero coding skills. If you could also include the part of script to get ProductGroup object I would be very grateful. Else thank you for the huge help you've already been. 

 var productGroup = rows.next();

    productGroup.setcpcBid(productGroup.CpcBid() * 1.2)

[Hiroyuki Miyauchi (AdWords Scripts Team)]

Hello Žygimantas,

With regards to your concern, you may include ProductGroup id to the Product Partition Report's SELECT clause and use the id in withIds method to fetch its corresponding ProductGroup objects. You may utilize the sample code below, but feel free to modify this based on your preference.

function main() {

    var report = AdWordsApp.report(

        'SELECT ConversionValue, Cost, id ' +

        'FROM   PRODUCT_PARTITION_REPORT ' +
        'WHERE  CampaignName CONTAINS "Shopping"');

    var rows = report.rows();

    while (rows.hasNext()) {
        var row = rows.next();
        var conversionValue = row['ConversionValue'];
        var cost = row['Cost'];

        var id = row['Id'];

        if (conversionValue / cost > 3) {

            //get the ProductGroup object then perform your bid updates

            var productGroups = AdWordsApp.productGroups().withIds(id).get();
            while (productGroups.hasNext()) {
                var productGroup = productGroups.next();
                var setCPCvalue = productGroup.CpcBid() * 1.2;
                productGroup.setMaxCpc(setCPCvalue);
                Logger.log("ProductGroup Id: " + productGroup.getId() + " Max cpc bid: " + setCPCvalue);
            }
        }
    }

}

I would suggest that you test this script using the Preview button first to see possible changes.

Regards,

Hiroyuki

AdWords Scripts Team

[Žygimantas Balsys]

Thank you very much again for your time.

After PREVIEW it says:

9/6/2018 2:31:48 PMColumn 'id' is not valid for report type PRODUCT_PARTITION_REPORT. Double-check your SELECT clause. (file Code.gs, line 3)

Then I've changed id -> Id on line 3.

Then it says:

9/6/2018 2:32:44 PMTypeError: Cannot find function map in object 423255658219. (file Code.gs, line 18)