Merge Conversion Action Segments with Performance Metrics: Free Google Ads Script

Making optimizations on secondary conversion actions in google ads is not straight forward, because you can’t combine them with metrics in the reporting API or UI. In this post, we are sharing a script solution that is doing this job with google ads search term data. We think this is the perfect example to show some surprising quick gains regarding poor traffic and also show the short comings of machine learning models using rare conversion events as target variable.

What are Conversion Actions in Google Ads and why you should use them?

There are two ways you can send conversion actions to your Google Ads Account:

  • Primary Conversion Actions: If you use this type of conversion setting you will find the conversion events shown in the “Conversions” and “Conversion Value” reporting fields in google ads. This column is also used when running google bidding strategies. Be careful: You do not want to use different funnel conversions summed up by using always primary conversions. For primary conversionsi it is no problem to calculate conversion rates or cost per orders or ROIs because you can use them in the standard reports.
  • Secondary Conversion Actions: These conversions are not counted in “Conversions” and “Conversion Value” columns. They appear only in “All Conversions” and “All Conversion Value”. Secondary conversions are not considered for bidding. When you are using secondary conversions already you might know that it is not that easy to calculate CPOs or conversion rates. Google tells you that segments can not be combined with e.g. click or cost data. To do that you need two reports and some data transformation operations. This is exactly what our script solution is doing.

Why you should heavily use secondary conversion actions? In our opinion it is crucial to use your own data in a smart way to drive different optimizations in your google ads account. Secondary conversion actions are a great way to bring that business knowledge to your accounts. Here are some ideas how to get started:

  • Engaged Users: If your website visitors had some interaction with your website, e.g. stayed there longer for 20 seconds or browsed more then 2 pages
  • Engaged B2B Users: Of course you can further narrow down your conversion events. When you are operating in a B2B environment we would combine this with company information from services like kickfire, clearbit or IP2Location (just to name some of the available services).
  • Predictive User Scoring: That’s the big deal. And this will be the only way do differentiate from your competition when google ads is fully automated by google and the only ask for your credit card number and a goal to optimize for. Machine Learning is no rocket science. When you have rare conversion events the prediction results will be poor. When you use bad features in your machine learning models, results will also be poor. Googles smart bidding will only consider the data they have to optimize – but you have lots of first party data to crush googles prediction results. Use the behavior of the user on your website, the interaction with your sales team, the way the a trial software is used, etc to derive quality model features that are good predictors for your rare conversion events. If you send those predicted conversions to google ads as optimization goal instead of the (rare) real conversions your bidding will be superior.

But before you start with the big deal, you might want some more proof on how googles machine learning goes wrong when observation numbers of the target variable is too low. This is the reason we used the search term report in our script to merge with various conversion actions available in your account:

  • Close variant matches are often very poor
  • If you have rare conversion events it needs a lot of clicks to judge performance
  • you are probably wasting a lot of money waiting for a bid correction of smart bidding

Use the earliest soft conversion you can think of for your business (e.g. engaged users) and analyze your search terms based on that. Even on low observation numbers you are able to make stable decisions: In that scenario it is about cutting off poor search patterns in the earliest stage – if you have 20 Clicks with zero engagement you can block this search pattern with negative keywords. If you have 20 Clicks and you are looking at e.g. (rare) Lead Signups as conversion goal you are not able to make decision yet. For that reason also smart bidding will not bid down.
Rest assure that you will be surprised for which search terms you are paying for when you start using that perspective. When you want to go further down the rabbit hole, have a look at PhraseOn, our traffic cleaning solution, where we combine NLP techniques with multiple conversion actions to make automated decisions for negative keywords.

I hope I gave you some reasons to set up the following script to make use of multiple conversion actions to optimize your google ads performance.

Free Ads Script Solution To merge conversion action segments with performance data

Although you can see your Conversion Action data through Google Ads UI, it is not easy to report and export it. So, it’s possible that you need a custom solution. With our solution below, you can transfer your Conversion Action data to Google SpreadSheet effortlessly and completely free of charge, and you can browse your report there.

How Does Free Script To Import Conversion Actions To Google SpreadSheet Work?

This script briefly works using the search_term_view report provided by the Google Ads API. This report includes the metrics of your search terms, as well as the values and names of your conversion actions.

Get Conversion Actions Data From search_term_view Report

To import your Google Ads Conversion Actions to Google SpreadSheet, first, we need to fetch conversion action data from the search_term_view report. This process will bring the conversion actions and names of the search terms within the date range we have determined. We will store this data of search terms after we sum all conversion for each conversion action name according to the resource_name of the search term.

Get conversion actions of each search terms in selected date range

Get Search Term Metrics From search_term_view Report

The next process will be to pull the metrics of the search terms. (clicks, impressions and costs) So, we can combine these metrics with the conversion actions we collected in the previous step, by resource_name. Finally, we will have an object with search term, search term metrics, conversion action names and values.

Import Conversion Actions With Search Terms Metrics To The Google SpreadSheets

We have collected all the necessary information in our object and now we are ready to format it to match the table view. First, we will need to create rows from our object that stores the information about search terms and conversion actions. After we have seperate rows for each search term with related KPIs and conversion values of related conversion action names, we are ready to write our rows into Google SpreadSheets. Thanks to Google Ads Script, we can do this easily with using SpreadsheetApp. Just go to Google SpreadSheets and create new SpreadSheet, then get URL of your recently created empty SpreadSheet and remember editing your Google SpreadSheet URL in the script.

Final Script: Import Conversion Actions to Google SpreadSheet

Copy the script below to and use it in Google Ads Scripts to apply this solution easily to your Google Ads account for free!

var SCRIPT_URL = "place your spreadsheet URL here"
var lastXdays = 365
var CONFIG = {
    REPORTS: [{
            NAME: 'search_term_view',
            CONDITIONS: 'WHERE metrics.clicks > 0',
            //customer.id, , ,, ,
            FIELDS_REPORT: {
                "customer.id": "STRING",
                "search_term_view.search_term": "STRING",
                "search_term_view.resource_name": "STRING",

                "metrics.clicks": "INTEGER",
                "metrics.cost_micros": "MICRO",
                "metrics.impressions": "INTEGER"
            },
            FIELDS: {
                /*     'customer.id': 'STRING',
                    'ad_group_criterion.keyword.text': 'STRING',
                    'ad_group_criterion.criterion_id': 'STRING',
                    'metrics.clicks': 'INTEGER',
                    'metrics.cost_micros': 'FLOAT',
                    'metrics.impressions': 'INTEGER', */
                'DATE': 'DATE',
                'CUSTOMER_ID': 'STRING',
                'SEARCH_TERM': 'STRING',
                'RESOURCE_NAME': 'STRING',

                'CLICKS': 'INTEGER',
                'COSTS': 'FLOAT',
                'IMPRESSIONS': 'INTEGER',
            }
        }

    ],

    RECIPIENT_EMAILS: [
        'RECIPIENT_EMAIL'
    ]
};


//Check if number is below 10 or not, if so add 0
function checkDigits(x) {
    var ret = x
    if (x < 10) {
        ret = "0" + x
    }
    return ret
}

function main() {
    Logger.log("Starting script")

    var accountSelector = AdsManagerApp.accounts()
     //var accountSelector = AdsManagerApp.accounts().withIds(['111-111-1111'])



    Logger.log("exists")
    accountSelector.executeInParallel("processClientAccount", "doneProcessing");


}



function doneProcessing(results) {

    Logger.log("Script completed. Waiting for big query jobs to complete.")


}






function getConversionActions(name) {

    var date = new Date();
    var yesterday = new Date(Date.now() - 86400000)
    var dateAgo = new Date(Date.now() - 86400000 - (lastXdays * 86400000))

    var dates = [dateAgo.getFullYear() + "-" + checkDigits(dateAgo.getMonth() + 1) + "-" + checkDigits(dateAgo.getDate()), yesterday.getFullYear() + "-" + checkDigits(yesterday.getMonth() + 1) + "-" + checkDigits(yesterday.getDate())]
    var conversiondata = {}

    var query = "";

    query = 'SELECT search_term_view.resource_name, segments.keyword.ad_group_criterion, metrics.all_conversions, segments.conversion_action_name FROM ' + name + ' WHERE segments.date BETWEEN "' + dates[0] + '" AND "' + dates[1] + '"'


    // Logger.log(query)


    var report = AdsApp.report(query)
    var rows = report.rows()
    while (rows.hasNext()) {
        var row = rows.next();
        var conversions = row['metrics.all_conversions']
        var key = ""
        if (name == "search_term_view") {
            key = row['search_term_view.resource_name']

        } else if (name == "keyword_view") {
            key = row['ad_group_criterion.criterion_id']
        }
        var type = row['segments.conversion_action_name']

        if (!(key in conversiondata)) {
            conversiondata[key] = {}
        }
        if (!(type in conversiondata[key])) {
            conversiondata[key][type] = 0

        }
        //Logger.log(conversions)
        //Logger.log(conversiondata[key][type])

        conversiondata[key][type] += conversions


        //Logger.log(conversiondata[key][type])
    }


    return conversiondata
}

function processClientAccount() {


    var yesterday = new Date(Date.now() - 86400000)
    var dateAgo = new Date(Date.now() - 86400000 - (lastXdays * 86400000))

    var dates = [dateAgo.getFullYear() + "-" + checkDigits(dateAgo.getMonth() + 1) + "-" + checkDigits(dateAgo.getDate()), yesterday.getFullYear() + "-" + checkDigits(yesterday.getMonth() + 1) + "-" + checkDigits(yesterday.getDate())]




    for (var i = 0; i < CONFIG.REPORTS.length; i++) {


        CONFIG.REPORTS[i].CONDITIONS += ' AND segments.date BETWEEN "' + dates[0] + '" AND "' + dates[1] + '"'
        var conversionActions = getConversionActions(CONFIG.REPORTS[i].NAME)




        var csvData = retrieveAdsReport(CONFIG.REPORTS[i], conversionActions);

        // If configured, back up data.




        saveCompressedCsvFile(csvData);
        Logger.log('Exported data to Drive folder ');



    }


    return "Done"

}






function saveCompressedCsvFile(csvData) {
    // var file = Utilities.newBlob(csvData, 'application/octet-stream', fileName)


    var ss = SpreadsheetApp.openByUrl(SCRIPT_URL);
    sheet = ss.getSheetByName("Sheet1");
    var lastRow = sheet.getLastRow();

    if (lastRow > 0) {
        csvData.shift()
    } else {

    }
    Logger.log(lastRow)

    var row = csvData.length;
    var column = csvData[0].length;

    console.log(sheet.getLastRow() + 1, 1, row, column)
    console.log(csvData)
    sheet.getRange(sheet.getLastRow() + 1, 1, row, column).setValues(csvData);


    createFilter(row, column)

    sort()

}


function retrieveAdsReport(reportConfig, conversionActions) {
    var fieldNames = Object.keys(reportConfig.FIELDS_REPORT)
    var fieldNames_display = Object.keys(reportConfig.FIELDS)

    var report = AdsApp.report(
        'SELECT ' + fieldNames.join(',') +
        ' FROM ' + reportConfig.NAME + ' ' + reportConfig.CONDITIONS)

    var rows = report.rows();
    var chunks = [];
    var chunkLen = 0;
    var csvRows = [];
    var totalRows = 0;
    // Header row
    var header = fieldNames_display.join(',');

    var keysCols = []
    var data = []


    for (var item in conversionActions) {

        var itemKeys = Object.keys(conversionActions[item])
            //    Logger.log(itemKeys)
        for (var i = 0; i < itemKeys.length; i++) {
            if (keysCols.indexOf(itemKeys[i].trim()) >= 0) {

            } else {
                keysCols.push(itemKeys[i].trim())
            }
        }
    }
    header = header + "," + keysCols.join(",")
    header = header.split(",")
    csvRows.push(header);

    // Iterate over each row.
    while (rows.hasNext()) {
        var row = rows.next();


        var csvRow = [];
        var hasConversionAction = false
        var conversionActionField = ""
        csvRow.push(new Date().toLocaleDateString('en-us', { weekday: "long", year: "numeric", month: "short", day: "numeric" }));
        for (var i = 0; i < fieldNames.length; i++) {

            var fieldName = fieldNames[i];
            var fieldValue = row[fieldName].toString();
            var fieldType = reportConfig.FIELDS_REPORT[fieldName];

            if (conversionActions[fieldValue]) {
                hasConversionAction = true
                conversionActionField = fieldValue
            }
            // Strip off % and perform any other formatting here.
            if (fieldType == 'FLOAT' || fieldType == 'INTEGER') {
                if (fieldValue.charAt(fieldValue.length - 1) == '%') {
                    fieldValue = fieldValue.substring(0, fieldValue.length - 1);
                }
                fieldValue = fieldValue.replace(/,/g, '');
            }
            if (fieldType == 'MICRO') {
                if (fieldValue.charAt(fieldValue.length - 1) == '%') {
                    fieldValue = fieldValue.substring(0, fieldValue.length - 1);
                }
                fieldValue = fieldValue.replace(/,/g, '');
                fieldValue = fieldValue / 1000000
            }

            // Add double quotes to any string values.
            if (fieldType == 'STRING') {
                fieldValue = fieldValue.replace(/"/g, '""');

            }
            if (fieldType == 'STRING2') {
                fieldValue = fieldValue.replace(/"/g, '""');
                fieldValue = fieldValue.split("~")[1]
            }

            csvRow.push(fieldValue);
        }

        if (hasConversionAction == true) {




            var keysObj = Object.keys(conversionActions[conversionActionField])

            for (var k = 0; k < keysCols.length; k++) {

                if (keysObj.indexOf(keysCols[k]) >= 0) {

                    csvRow.push(conversionActions[conversionActionField][keysCols[k]])

                } else {
                    csvRow.push("0")
                }
            }













        } else {
            for (var k1 = 0; k1 < keysCols.length; k1++) {
                csvRow.push("0")
            }

        }

        var rowString = csvRow

        csvRows.push(rowString);

    }
    if (csvRows) {
        totalRows += csvRows.length;

    }

    var account = AdsApp.currentAccount()
    Logger.log('Downloaded ' + reportConfig.NAME + ' for account ' + account.getCustomerId() +
        ' with ' + totalRows + ' rows, in ' + chunks.length + ' chunks.');
    return csvRows;
}



function prepareFloat(str) {
    var newStr = str.replace(",", "")
    return parseFloat(newStr).toFixed(2)

}

function prepareCustomerId(str) {
    return str.replace(/[-]/g, "")
}








function createFilter(row, col) {
    var ss = SpreadsheetApp.openByUrl(SCRIPT_URL);
    var spreadsheet = ss.getSheetByName("Sheet1");
    if (spreadsheet.getFilter()) {
        spreadsheet.getFilter().remove();
    }

    spreadsheet.getRange(1, 1, row, col).createFilter();
};

function sort() {
    var ss = SpreadsheetApp.openByUrl(SCRIPT_URL);
    var spreadsheet = ss.getSheetByName("Sheet1");
    spreadsheet.getFilter().sort(6, false);
}

function paint(row, col) {
    var ss = SpreadsheetApp.openByUrl(SCRIPT_URL);
    var spreadsheet = ss.getSheetByName("Sheet1");
    var conditionalFormatRules = spreadsheet.getConditionalFormatRules();
    for (var i = 5; i < col; i++) {

        conditionalFormatRules.push(SpreadsheetApp.newConditionalFormatRule()
            .setRanges([spreadsheet.getRange(1, i, row, i)])
            .setGradientMinpoint('#FFFFFF')
            .setGradientMaxpoint('#57BB8A')
            .build());

    }
    spreadsheet.setConditionalFormatRules(conditionalFormatRules);
}

Nevertheless, you can see a diagram below which explains how the whole process works altogether.

Google Ads Script

More Similar Posts

Menu