How to calculate the Lin Rodnitzky Ratio using GAPandas

The Lin Rodnitzky Ratio is designed to assess paid search account management quality. Here's how you can calculate it in Python using GAPandas, and check your PPC account performance.

How to calculate the Lin Rodnitzky Ratio using GAPandas
Picture by Canva Studio, Pexels.
18 minutes to read

The Lin Rodnitzky Ratio is a calculation designed to help search engine marketers assess the management of paid search campaigns and account structure. When managing paid search advertising accounts you need to find a balance between being too conservative and too aggressive.

PPC accounts (whether they’re Google Adwords or Bing Ads) that are managed too conservatively miss out potential sales and customer acquisitions through attempts to conserve profit, while those that are managed too aggressively over-spend on non-converting search terms and erode profit.

There are numerous ways to monitor the health and management of PPC advertising account structure, however, the Lin Rodnitzky Ratio is one of the simplest. Devised by PPC practitioners Will Lin and David Rodnitzky from agency 3Q Digital, the Lin Rodnitzky Ratio (or L/R Ratio for short) measures the overall cost per conversion across search terms in the account by the cost per conversion of all search terms with at least one conversion over a given date range.

The value returned provides a simple score that the authors claim can be used to assess the quality of management style on the PPC account. In this project, we’ll take a look at how we can automate the calculation of the Ratio in Python using GAPandas and use it as a way of keeping track on your PPC account’s performance.

Understanding the Lin Rodnitzky Ratio

According to Lin and Rodnitzky, they’ve reviewed hundreds of accounts over the past few years at 3Q Digital using the Ratio, and they believe “healthy accounts” typically have a L/R Ratio of 1.5 to 2.0.

They bin the scores into four bands: a L/R Ratio of 1.0 to 1.5 suggests that the account management is too conservative; a L/R Ratio of 1.5 to 2.0 suggests the account is well-managed; a L/R Ratio of 2.0 to 2.5 suggests the account management is too aggressive, while anything over 2.5 is considered mismanaged. While overspending can cost a lot of money, being over-cautious and not utilising keyword testing can miss out on lots of incremental conversions or opportunities to grow the account.

The idea is that well managed accounts should include a mixture of search terms and match types. By analysing conversion data from the search queries used on the Google Ads account, you would expect to find some solid high conversion search terms, such as those for your best-selling products or your brand terms, but also some new keywords that are being tested to identify whether they can bring additional incremental conversions to the account.

When accounts are being managed too conservatively there will often be relatively few keywords present and a high ROAS for the account, however, there will search queries generating fewer conversions will likely be missing, and there may be a lack of testing to identify growth opportunities.

Lin Rodnitzky Ratio Assessment
1.0 to 1.5 The account is too conservative
Scores below 1.5 imply that the queries are highly targeted or based mainly on brand terms. The account is likely to missing out on potentially profitable conversions from other terms.
1.5 to 2.0 The account is well-managed
An L/R Ratio of 1.5 to 2.0 is considered the optimum. Scores in this area tend to include a mix of highly targeted phrases and brand terms that bring in profit, plus some experimental queries that have been added to the account to try and identify potential areas for growth.
2.0 to 2.5 The account is too aggressive
Scores between 2.0 and 2.5 include too many queries with low conversion rates. They're driving clicks, but not sales. Lin and Rodnitzky say that this tend to be due to excessive use of broad match keywords, a lack of rigorous analysis, and a general lack of attention given to the account.
2.5+ The account is being mismanaged
For scores over 2.5, Lin and Rodnitzky classify the accounts as mismanaged. They say at this level "money is being wasted daily". Simple changes to the account at this level can save businesses a significant amount of money and increase profit.

Load the packages

Most articles covering this technique explain how you can extract the conversion tracking data from Google Adwords, by extracting the cost per conversion for search queries with one conversion and the same for those with 0 conversions. However, this can also be done in Google Analytics using the Python API via my GAPandas package.

First open a Jupyter notebook and load the pandas, numpy, and gapandas packages. GAPandas is my Python package for querying the Google Analytics API, and we’ll be using this to extract PPC data from your GA account to assess the performance of your Google Ads account management. You can install GAPandas using the command below.

!pip3 install gapandas
import pandas as pd
import gapandas as gp
import numpy as np

Connect to the Google Analytics API

Next, you need to use the get_service() function to connect to the Google Analytics API. This requires a client secrets JSON keyfile with permissions to query your GA data. For more details on getting one of these, please see my beginner’s guide to using GAPandas.

To make the subsequent code a bit easier to read, I’ve assigned the service object to a variable called service, set the view ID, and set the date range. This makes it quick and easy to check different periods, without the need to edit code in multiple places.

service = gp.get_service('client_secrets.json')
view = '123456789'
start_date = '2020-02-01'
end_date = '2020-02-28'

Query your data

Now we have a connection to Google Analytics, we can create an API query. This consists of dictionary called a payload that contains the relevant API query parameters. To extract the search terms from our Google Ads data in Google Analytics we need the adClicks, cpc, transactions, transactionRevenue, and adCost metrics from GA, plus the keyword dimension.

Since we only want the data for Google Ads (or Google Adwords), and specifically want the PPC keyword campaign data (marked CPC in Google Analytics), we’ll create a filter to select the exact data we need. Yours will look similar to this, but the exact filters you need to pass will depend on your GA implementation.

payload = {
    'start_date': '30daysAgo',
    'end_date': 'today',
    'metrics': 'ga:adClicks, ga:cpc, ga:transactions, ga:transactionRevenue, ga:adCost',
    'dimensions': 'ga:keyword',
    'filters': 'ga:medium==cpc;ga:source==google;ga:keyword!=(Remarketing/Content targeting);ga:keyword!=*;ga:keyword!=(not set)'
}

Now we can pass our service object, the view ID for our GA account, and the payload dictionary above to the run_query() function. This will query the Google Analytics API and return our results in a Pandas dataframe a couple of seconds later.

I’ve removed the actual search terms to anonymise the data, but we can see from the data below which phrases are driving conversions. If you want to get further insight into the types of queries being used, you can also pull in the match type data from the API, to see whether a search term is broad match, exact match, or whatever.

df = gp.run_query(service, view, payload)
df.head()
keyword adClicks cpc transactions transactionRevenue adCost
0 [keyword removed] 219 0.34520547945205476 6 396.703091 75.60
1 [keyword removed] 11 0.3481818181818182 1 43.977708 3.83
2 [keyword removed] 37 0.6845945945945946 0 0.000000 25.33
3 [keyword removed] 31 0.47451612903225804 0 0.000000 14.71
4 [keyword removed] 80 0.524375 1 126.916964 41.95

Calculate the cost per conversion

To calculate the L/R Ratio we’ll need to know the cost per conversion for the search account as a whole. You can calculate this for each keyword by dividing the adCost data with the transactions data. Since we’ll generate some inf values, we’ll fill these in with zeroes to avoid issues in later steps.

df['cost_per_conversion'] = (df['adCost'] / df['transactions'])
df = df.replace([np.inf, -np.inf], np.nan).fillna(value=0)
df.head()
keyword adClicks cpc transactions transactionRevenue adCost cost_per_conversion
0 [keyword removed] 219 0.34520547945205476 6 396.703091 75.60 12.60
1 [keyword removed] 11 0.3481818181818182 1 43.977708 3.83 3.83
2 [keyword removed] 37 0.6845945945945946 0 0.000000 25.33 0.00
3 [keyword removed] 31 0.47451612903225804 0 0.000000 14.71 0.00
4 [keyword removed] 80 0.524375 1 126.916964 41.95 41.95

Now we have the cost_per_conversion calculate for every keyword, we can calculate the mean cost per conversion for all queries across the whole account. We get back a figure of £5.67.

avg_cost_per_conversion_all_queries = df['cost_per_conversion'].mean()
avg_cost_per_conversion_all_queries
5.6701301988186525

Calculating the total adCost and total transactionRevenue for this period shows that we spent £38,883 and generated £289,562. This looks pretty good. It’s a lot of money to spend, but the return on investment is great. However, it could be possible that we’d have generated a lot more with a different PPC management approach.

One of the best practices in PPC account management is that new keywords and ads are added and tested to see if they can deliver additional conversions, and this often gets missed, especially when staff or agencies are working towards a ROAS (Return on Advertising Spend) or CoS (Cost of Sale) target in addition to a revenue target.

df['adCost'].sum()
38883.409999999996
df['transactionRevenue'].sum()
289562.917149

Analyse non-converting keywords

Next, we need to analyse the non-converting keywords in our PPC account’s search campaigns. We can do this with a simple filter on the transactions column of the dataframe. By calculating the sum() of the adCost column we see that we spent £3235 on non-converting keywords.

This might sound bad, but every search account needs some of these. By regularly testing new keywords, you can find new ones that convert and can become permanent additions to the account. Too little spend in this area can be a sign that the staff member or agency managing your account is undertaking little or no testing to expand your account.

results_non_converting = df[df['transactions']==0]
results_non_converting.head()
keyword adClicks cpc transactions transactionRevenue adCost cost_per_conversion
2 [keyword removed] 37 0.6845945945945946 0 0.0 25.33 0.0
3 [keyword removed] 31 0.47451612903225804 0 0.0 14.71 0.0
11 [keyword removed] 10 0.551 0 0.0 5.51 0.0
12 [keyword removed] 11 0.5254545454545454 0 0.0 5.78 0.0
13 [keyword removed] 143 0.2832867132867133 0 0.0 40.51 0.0
results_non_converting['adCost'].sum()
3235.79

Analyse converting keywords

Next, we’ll repeat this step for the converting keywords. We’ll just look for all the keywords where there was more than one transaction recorded.

results_converting = df[df['transactions'] > 0]
results_converting.head()
keyword adClicks cpc transactions transactionRevenue adCost cost_per_conversion
0 [keyword removed] 219 0.34520547945205476 6 396.595455 75.60 12.600
1 [keyword removed] 11 0.3481818181818182 1 43.965775 3.83 3.830
4 [keyword removed] 80 0.524375 1 126.882528 41.95 41.950
5 [keyword removed] 29 0.4789655172413793 1 88.205639 13.89 13.890
6 [keyword removed] 45 0.27711111111111114 2 114.183108 12.47 6.235

Doing a couple of quick sums on the data reveals that we spent £35,627 on converting keywords, which gave us an average cost per conversion of £10.13 for these keywords.

results_converting['adCost'].sum()
35627.15
avg_cost_per_conversion_queries_with_one_conversion_or_more = results_converting['cost_per_conversion'].mean()
avg_cost_per_conversion_queries_with_one_conversion_or_more
10.133894933121399

Calculate Lin Rodnitzky Ratio

Finally, we can calculate the L/R Ratio. This is actually dead easy, despite the fanciful sounding name. We simply divide the average cost per conversion for all queries by the average cost per conversion for converting queries.

def lin_rodnitzky_ratio(avg_cost_per_conversion_all_queries,
                        avg_cost_per_conversion_queries_with_one_conversion_or_more):
    """Return the Lin Rodnitzky Ratio describing the quality of paid search account managemnent.
    Args:
        avg_cost_per_conversion_all_queries (float): Average cost per conversion on the whole PPC account.
        avg_cost_per_conversion_queries_with_one_conversion_or_more (float): Average cost per conversion for only
        conversions where there was one or more conversions.
    Returns:
        Lin Rodnitzky Ratio (float).
        1.0 to 1.5 - Account is too conservatively managed.
        1.5 to 2.0 - Account is well-managed.
        2.0 to 2.5 - Account is too aggressively managed.
        2.5 or more - Account is being mismanaged.
    """

    return avg_cost_per_conversion_all_queries / avg_cost_per_conversion_queries_with_one_conversion_or_more

By running the function and passing it the two variables we calculated above, we get back the LR Ratio for this search account. The score is 0.55. According to the authors, a ratio of 1.0 to 1.5 would fall into the “too conservatively managed” bracket, so this account seems to be being handled potentially over-cautiously.

The very low score could be a sign that the person responsible has been managing the account by disabling low performing keywords, but doing very little to expand the account by adding in new test keywords, to find new phrases that might generate more sales.

lr_ratio = lin_rodnitzky_ratio(avg_cost_per_conversion_all_queries,
                        avg_cost_per_conversion_queries_with_one_conversion_or_more)
lr_ratio
0.5591958302308265

Does it work?

According to the creator David Rodnitzky from 3Q Digital, the metric is designed to be used as a guide, rather than a hard and fast rule. The intention was to use it as a way of checking that enough testing was being undertaken to find new search queries that may convert well but “not so much that you are spending tons on non-converting queries.”

Rodnitzky says that the ratio tends to be higher in long-tail accounts, simply because conversion tracking on some search queries shows they only pay off every so often. As a result, he suggests that the actual L/R Ratio you get may depend on the market in which you apply the technique.

Does it work? Well, it’s probably true on this account that things are being managed very conservatively - not much money is being wasted. There seem to be relatively few new keywords appearing, and lots of the activity has really comprised turning off things that are under-performing, rather than testing new things to grow the account.

Most of the keywords present are driving conversions, but the account is likely missing lots of potentially useful search terms that could contribute towards growth.

Further reading

  • Rodnitzky, D. (2015) - The Lin Rodnitzky Ratio. 3Q Digital’s simple way to measure Adwords account performance. Harte Hanks Inc.

Matt Clarke, Saturday, June 05, 2021

Matt Clarke Matt is a Digital Director who uses data science to help in his work. He has a Master's degree in Internet Retailing (plus two other Master's degrees in different fields) and specialises in the technical side of ecommerce and marketing.

Marketing Analytics in Spreadsheets

Learn how to ensure clean data entry and build dynamic dashboards to display your marketing data.

Start course for FREE

Comments