Google Ads Script – Lin Rodnitzky Ratio

“Is my account spending its budget efficiently?”

It’s a question every Google Ads advertiser should be asking every month if not every week.

Yet…answering this question is hard!

How do you know?

You could look at all the individual search terms and decide if their clicks are worth the money.

With THOUSANDS of search terms in your account, this could easily take days!

But, what if you could quickly calculate the efficiency of your Google Ads account, and summarize it in a single score?

Turns out, YOU CAN!

Just Script It!

Introducing …

GOOGLE ADS SCRIPT: Lin Rodnitzky Ratio

What it does
This script monitors the Lin Rodnitzky Ratio and creates a graph displaying this ratio over time.
The Lin Rodnitzky Ratio is calculated by dividing the CPA of all search queries by the CPA of all your search queries with at least 1 conversion.

Why you care
The Lin Rodnitzky Ratio can be used as a sort of loose indicator of the efficiency of an account.
It’s not set in stone and the absolute values should be taken with a grain of salt, they can vary between accounts, but here’s a rough indication of the meaning of different values for the ratio.

The inventors of the ratio have reviewed hundreds of accounts over the years using the Lin-Rodnitzky Ratio, and they’ve concluded that healthy accounts typically have a Lin-Rodnitzky Ratio between 1.5-2.0. (NOTE: my personal take is, anywhere between 1.5 and 2.5 is healthy).

On a continuum of scores, this is what each score range generally means according to the authors:

1.0-1.5:
The account is too conservative. Most likely this means that the only queries getting any traffic are brand terms or the absolutely most targeted queries. This means that the account is likely missing out on a lot of incremental conversions, most of which are likely to still be highly profitable for the business.

1.5-2.0:
The account is well-managed. There is a combination of consistent winners that always bring in sales and experimental queries that are being tested to identify growth opportunities.

2.0-2.5:
The account is too aggressive. There are too many queries getting clicks that are not driving conversions. This is either due to excessive use of broad match, a lack of attention to the account, or a lack of rigorous analysis of metrics.

2.5+:
The account is being mismanaged. Money is being wasted daily, and simple changes can save the business a lot of money

How I use it

I like to use the LR Ratio as a quick guide during my Google Ads account audits, and I look at the graph displayed by the script.
Three cases stand out:

1. 1. If the score is relatively high (>2.5) and the client prefers short-term returns (profit) over long-term growth, I recommend cutting waste in the account by adding negatives, less budget on DSA campaigns, and/or using stricter keyword match types.
   2. If the score is relatively low (<1.5) and the client is looking to grow the account revenue, I recommend spending more on DSA campaigns, and/or setting up experiments using less strict keyword match types.
   3. If the graph shows sudden changes in the value of the ratio (like in the screenshot above), I go looking for the cause of the change and translate what I discover into new advice. Ie: change was caused by auto-applied recommendation to add broad match keywords -> turn of auto-apply recommendations. (something that should be done in all accounts anyways!).

 

INSTRUCTIONS:

1. 1. See the script code below. Install the script in your account.
      Don’t worry if you have never done this before. You do not need any coding skills. It is as simple as copy-paste. Simply follow these instructions on how to set up and schedule google ads scripts.
   2. Make a copy of this Google Sheet : https://docs.google.com/spreadsheets/d/1hAPXanpxohx0Wk2Z7_N2DVSsuSdUzMr69qxpjJo_S4o/copy
   3. Add the complete URL of the spreadsheet to the SPREADSHEET_URL below (line 26)
   4. Authorize and Preview
   5. Schedule to run daily (I prefer daily at 6AM)

Subscribe to my mailing list to receive more scripts and updates on how to start learning to write your own Google Ads Scripts.

/**
*
* Google Ads Script - Lin Rodnitzky Ratio (FREE version)
*
* (C) Nils Rooijmans , https://nilsrooijmans.com
*
* This script monitors the Lin Rodnitzky Ratio and creates a graph displaying the ratio over time.
*
* INSTRUCTIONS:
* 
* If you are new to scripts -> follow these instructions on how to copy-paste and install Google Ads Scripts:
* https://nilsrooijmans.com/how-to-set-up-and-run-google-ads-scripts/
* 
* Next:
* 1. Make a copy of this Google Sheet : https://docs.google.com/spreadsheets/d/1hAPXanpxohx0Wk2Z7_N2DVSsuSdUzMr69qxpjJo_S4o/copy
* 2. Add the complete URL of the spreadsheet to the SPREADSHEET_URL below (line 26)
* 3. Authorize and Preview 
* 4. Schedule to run daily (I prefer daily at 6AM)
*
* Version: 1.0
*
* ---------------------------------------------------
**/

// create a copy of this Google Sheet and add the complete url of the new sheet between the quotes -> https://docs.google.com/spreadsheets/d/1hAPXanpxohx0Wk2Z7_N2DVSsuSdUzMr69qxpjJo_S4o/copy
var SPREADSHEET_URL = '';


/*** DON'T CHANGE ANYTHING BELOW THIS LINE UNLESS YOU KNOW WHAT YOU ARE DOING ***/

var INITIAL_PERIOD = 180; // last number of days for which LR will be calculated and added to the report on the first run 
var LOOKBACK_WINDOW = 28; // the number of days used for calculating the LR score

function main() {
  
  var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = spreadsheet.getSheetByName('LR Ratio');
  addHeaderToOutputSheet(sheet);
  
  var firstRun = isFirstRun();
  var date = new Date();
  
  if (firstRun) {
    console.log(`First run of the script, let's populate the report with historic data`);
    for (var days = INITIAL_PERIOD; days > 0; days--) {
      console.log(`days: ${days}`);
      var newDate = new Date(date);
      newDate.setDate(newDate.getDate() - days);
      var linRodnitzkyRatio = getLRRatio(newDate, LOOKBACK_WINDOW); 
      console.log(`Lin-Rodnitzky Ratio: ${ linRodnitzkyRatio.toFixed(2)}\n\n`);
      writeToSheet(spreadsheet, 'LR Ratio', newDate, linRodnitzkyRatio );      
    }
    endFirstRun();
  } 
  var linRodnitzkyRatio = getLRRatio(date, LOOKBACK_WINDOW); 
  console.log(`Lin-Rodnitzky Ratio: ${ linRodnitzkyRatio.toFixed(2)}`);
  writeToSheet(spreadsheet, 'LR Ratio', date, linRodnitzkyRatio );
  console.log(`Finished reporting the LR Ratio, you can find the results here:\n ${SPREADSHEET_URL}`);
}


function getLRRatio(date, lookbackWindow) {
  
  var timeZone = AdsApp.currentAccount().getTimeZone();
  var startDate = getStartDate(date, lookbackWindow);
  var endDate = getEndDate(date);
  console.log(`startDate: ${startDate}, endDate: ${endDate}`);

  var totalQuery = `
      SELECT
        metrics.cost_micros,
        metrics.conversions
      FROM
        search_term_view
      WHERE
        segments.date BETWEEN '${startDate}' AND '${endDate}'
        AND metrics.clicks > 0
    `;  

  var totalCPA = getCPA(totalQuery);
  console.log(`totalCPA: ${totalCPA}`);
    
  var convertingQuery = `
      SELECT
        metrics.cost_micros,
        metrics.conversions
      FROM
        search_term_view
      WHERE
        segments.date BETWEEN '${startDate}' AND '${endDate}'
        AND metrics.conversions > 0
    `;
  
  var convertingCPA = getCPA(convertingQuery);
  console.log(`convertingCPA: ${convertingCPA}`);  

  // Calculate Lin-Rodnitzky Ratio
  var linRodnitzkyRatio = convertingCPA > 0 ? totalCPA / convertingCPA : 0;
  
  return linRodnitzkyRatio;
}


function getCPA(query) {

  console.log(`Executing query: ${query}`);

  var report = AdsApp.report(query);
  var rows = report.rows();
  var costMicros = 0;
  var conversions = 0;
  var countRows = 0;

  while (rows.hasNext()) {
    countRows++;
    var row = rows.next();
    costMicros += parseFloat(row['metrics.cost_micros']);
    conversions += parseFloat(row['metrics.conversions']);
  }
  console.log(`Nr Of Rows: ${countRows}`);
  console.log(`cost: ${costMicros/ 1e6}`);
  console.log(`conversions: ${conversions}`);

  // Calculate CPA
  return conversions > 0 ? (costMicros / 1e6) / conversions : 0;
}


function getStartDate(date, nrOfDaysAgo) {
  var timeZone = AdsApp.currentAccount().getTimeZone();
  var startDate = new Date(date);
  startDate.setDate(startDate.getDate() - nrOfDaysAgo);
  return Utilities.formatDate(startDate, timeZone, 'yyyy-MM-dd');
}

function getEndDate(date) {
  var timeZone = AdsApp.currentAccount().getTimeZone();
  var endDate = new Date(date);
  endDate.setDate(endDate.getDate() - 1);
  return Utilities.formatDate(endDate, timeZone, 'yyyy-MM-dd');
}


function writeToSheet(spreadsheet, sheetName, date, linRodnitzkyRatio ) {
  var timeZone = AdsApp.currentAccount().getTimeZone();  
  var sheet = spreadsheet.getSheetByName(sheetName);  
  sheet.appendRow([Utilities.formatDate(date, timeZone, 'd MMM yyyy'), linRodnitzkyRatio.toFixed(2)]);
}


function addHeaderToOutputSheet(sheet) {
 
  try {
    var headerSheet = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1NxoZJgzCFjC663WM4PzKyTnW71X5X8MTVo2JxiiF1dE/").getSheetByName('header_sheet');
  } catch(e) {
    console.log(`### There was an issue opening the header sheet. Please download the latest version of this script at https://nilsrooijmans.com\n${e}`);
    throw `### There was an issue opening the header sheet. Please download the latest version of this script at https://nilsrooijmans.com\n${e}`;
  }
   
  var headerRange = headerSheet.getRange(1, 1, 2, headerSheet.getLastColumn());
  var headerData = headerRange.getValues();
   
  console.log("Adding header to the output sheet"); 
 
  var range=sheet.getRange(1,1,2,headerData[1].length);
  range.clear();
  range.clearFormat();
  range.setValues(headerData)
  range.setFontWeight("bold");
  range = sheet.getRange(1,1,1,headerData[1].length);
  range.setFontColor('#007BFF')
  sheet.setFrozenRows(2);
   
}


function isFirstRun() {
  var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheetName = 'config';
  var sheet = spreadsheet.getSheetByName(sheetName); 
  var value = sheet.getRange('B1').getValue();
  console.log(`firstRun value from config sheet: ${value}`);
  if (value == true) {
    return true;
  } else {
    return false;
  } 
}


function endFirstRun() {
  var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheetName = 'config';
  var sheet = spreadsheet.getSheetByName(sheetName); 
  var value = sheet.getRange('B1').setValue('FALSE');
}

 

Join thousands of PPC geeks who already have access:

TIP: Get Access to my Ultimate List of 400+ Google Ads Scripts >>