Making optimizations on secondary conversion actions in Google Ads isn’t straight forward, because you can’t combine them with metrics in the reporting API or UI. In this post, we’re sharing a script solution that is doing this job with google ads search term data. 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?
According to Google, a conversion action is a specific customer action that you’ve defined as valuable to your business, such as an online purchase or phone call.
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’ll 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 don’t want to use different funnel conversions summed up by using always primary conversions. For primary conversions, it is no problem to calculate CR (Conversion rates) or CPO (Cost per Orders) or ROIs (Return on Investment) because you can use them in the standard reports.
- Secondary conversion actions: These conversions aren’t counted in “Conversions” and “Conversion Value” columns. They appear only in “All Conversions” and “All Conversion Value”. Secondary conversions aren’t considered for bidding. When you’re using secondary conversions already you might know that it isn’t that easy to calculate CPOs or conversion rates. Google tells you that segments cann’t 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 should you heavily use secondary conversion actions? In our opinion, it’s 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’re 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 Google’s 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.
However, before you start with the big deal, you might want some more proof on how Google’s 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’re 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’re able to make stable decisions: In that scenario, it’s 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’re looking at e.g. (rare) lead signups as conversion goal you aren’t able to make decision yet. For that reason, also smart bidding won’t bid down.
Rest assure that you’ll be surprised for which search terms you’re 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.
We 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 yo merge conversion action segments with performance data
Although you can see your conversion action data through Google Ads UI, it isn’t easy to report and export it. Thus, it’s possible that you need a custom solution. With our solution below, you can transfer your conversion action data to Google Sheet effortlessly and completely free of charge, and you can browse your report there.
How does free script to import conversion actions to Google Sheet 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 Sheet, 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 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’ll have an object with search term, search term metrics, conversion action names and values.
Import conversion actions with search terms metrics to Google Sheets
We have collected all the necessary information in our object and now we’re ready to format it to match the table view. First, we’ll need to create rows from our object that stores the information about search terms and conversion actions. After we have separate rows for each search term with related KPIs and conversion values of related conversion action names, we’re ready to write our rows into Google Sheets. Thanks to Google Ads Script, we can do this easily with using SpreadsheetApp. Just go to Google Sheets and create new Sheet, then get URL of your recently created empty Sheet and remember editing your Google Sheet 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.
