Blog for internet marketing

Web Analytics

How to Measure Lost Revenue in Google AdWords With The Help Of Language R


How much of your revenue got lost in Google AdWords due to the lack of budget or low quality score of used keywords? Standard reports do not give an opportunity to analyze it, but we know how to calculate this data with the help of programming language R.

At the moment when I have been studying the algorithm for defining the quantity of lost conversions with the help of Supermetrics (special addition to Google Spreadsheets), a free of charge Supermetrics functionality was quite enough. But starting from the second half of February 2016, the current edition switched to a system of charging for treatment. Probably, not everyone is ready to pay $50 for the report on lost conversions. That is why I have found another way to realize the current algorithm.

Maybe, the current method will first seem to be more complicated, but after spending some time on studying it, you will be able to update data and control loses of displays and income for all advertising campaigns for free. Moreover, the algorithm was improved, and now it is rather aimed for ecommerce projects, and its main aim is the evaluation of lost revenue, not conversions.

The current solution has been realized in programming language R that is meant for statistical data processing and information visualization. Language R was elaborated over 20 years ago specifically for academic researches. As a majority of free of charge products, this programming language began to gain popularity very quickly, and that is why a lot of packages have been written for it, that seriously expanded its basic functionality. In the current article, we will look through two packages created for work with API Google AdWords and Google Analytics.

What do you need for the algorithm realization?

  1. Install an ecommerce tracking code on your website, and therefore data on committed transactions have to be tracked by Google Analytics.
  2. Connect your Google Analytics account with Google AdWords.
  3. Install a programming language R on your PC. To do this, follow the link, download and install its latest version.
  4. For more effective work in programming language R, I recommend you to also download and install RStudio, because that’s the development environment we will run the script in.
  5. Create Developer Client in Google developer’s console. We will talk about this point in details later in the post.
  6. The hardest thing you will have to do is to get the AdWords developer token for the access to API Google AdWords.

If the mentioned above points do not confuse you, then we can move forward to the next step.

How to Create Developer Client in Google developer’s console?

Almost every of the aforementioned points is followed by link to the reference information, so now we can go to the overview of process of Developer Client creation. 1. Follow this link to the developer console and open “Product & Services” menu with the help of a button in the top left corner.

How yo create Developer Client in Google developer console

2. At this stage, we are interested in “API Manager” point. Go to the current tab.

2.1. In the list of available APIs choose “Analytics API”.


2.2 Activate API by pressing the “Enable” button.

Activate API

3. Then, in the menu situated in the left part of the screen choose “Credentials” point.

3.1. In “create credential” dropdown list choose the “OAuth client ID” point.


3.2. Enter client’s name in the “Name” field and press “Create”.

Enter client's name in the

3.3. Next, you will be provided with the accounts "Client ID" and "Client Secret", which you will then need to add to the script and also indicate them during the authentication.


Running a Script in RStudio

To move forward to this stage, you have to realize six points that have been highlighted in the beginning of the article. This can take several days.

1. Connecting the necessary R packages.

Open RStudio and insert the following part of the R script in the “Source” field.

1. #1. Installation and connection of required packages
2.  install.packages("curl")
3. require("curl")
4. install.packages("devtools")
5. require("devtools")
6. install.packages("RGoogleAnalytics")
7. require(RGoogleAnalytics)
8. install_github('jburkhardt/RAdwords')
9. require(RAdwords)

To run the first part of a script, select it with the mouse and press “Ctrl + Enter”. Thus, the process of downloading the necessary packages will start and a window displaying the install process will appear on the screen.

Installation process

The following part of a script will install the packages required for further work: “devtools”, “RGoogleAnalytics” and “RAdwords”. “RGoogleAnalytics” and “RAdwords” packages are meant for working with API eponymous services. “Devtools” package is needed for installing the latest version of “RAdwords” package directly from Github, because all the packages are by default distributed through CRAN (the acronym Comprehensive R Archive Network). In this case, you can use CRAN to get penultimate version of “RAdwords”, in which the latest changes in API AdWords have not been taken into account.

2. Setting the variables

Now you have to set all the variables that will be needed for further work with the script. Insert a given above code after the first fragment of a code that you have added for the installation of required packages.

1. #2. Declaration of variables
2. clienid <- "" #Client ID from google console
3. secret <- "TUXXXXXXXXXXXX_TknUI" #Client secret из google console
4. ga_view <- "ga:XXXXXXXX" #ID views from Google Analytics
5. adwords_id <- "XXX-XXX-XXXX" #ID of Google AdWords account
7. #Time period, in which it is needed to determine the amount of lost revenue
8.  start_period  <- c(day = "01",
9.                     month = "01",
10.                    year = "2016")
11. end_period    <- c(day = "20",
12.                    month = "03",
13.                    year = "2016")

We will have to further develop this part of a script filling in the variables with required values. 2.1. For the “clienid” variable – insert client’s ID, which you have received in the developer console. 2.2 For the “secret” variable respectively, it is necessary to assign a Client Secret indicator mentioned in the previous point. 2.3. For the “ga_view” variable, you have to assign the ID of submission in Google Analytics. To do this, go to the “Administrator” menu in Google Analytics, then click on “View Settings” in the “View” section.

Copy the view ID and insert it into the script in “ga:xxxxxxxxx” format.

Copy the view ID

2.4. You can find the “adwords_id” variable in Google AdWords account.

2.5. The “start_period” and “end_period” variables are responsible for the period, for which you want to analyse the lost income. You have to specify a day, month and year of starting and final date. After all variables are filled in, highlight the second part of a script and press “Ctrl + Enter”. In case in this particular moment everything has been done right, all submitted variables will appear in the RStudio «Environment» field.

3. Authentication in Google Analytics

3.1. The next step is authentication in Google Analytics. For passing it through, there is an “Auth” command in “RGoogleAnalytics” package. Add the following lines to the script:

1. #3. Authentication in the services.
2. #3.1. Authentication in Google Analytics.
3. ga_auth <- Auth(clienid,secret)

Highlight this part of a script and press “Ctrl + Enter”. 3.2. Just after that in console, which is situated be default in the lower left of the RStudio, will appear a request: do/whether you want to save the authorization credentials in Google account. I recommend to answer this question with a refusal by typing “No”. Then, a link will be generated in the console. Copy it and insert in the browser.

The generated link

3.3. The link in a browser will open a list of available Google accounts. Choose the one that has an access to the required Google Analytics submission. Access to the required Google Analytics view

After that, you will need to provide access for viewing Google Analytics data.

Grant access to view data

3.4. Then, an access key is generated in browser. Copy it and paste in Rstudio console.

Access key

4. Authentication in Google AdWords.

4.1. For authentication on Google AdWords, use a “doAuth” command. Copy and paste the following part of a code after all previous lines in RStudio:

1. #3.2. Authentication in Google AdWords
2. adwords_auth <- doAuth(F)

Highlight this part of a script with a mouse and press “Ctrl + Enter”. 4.2. You will need to paste Client ID and Secret credentials that have been generated on the stage “How to create a Developer Client in Google developer console”. 4.3. Then, you will need to enter the AdWords API developer token (here you will find a detailed information on how to receive it). In case there already is an approved token, it can be found in Google AdWords managing account. To do this, go to account setting and click on the icon with a gear image on it in the top right corner of the interface. Choose “AdWords API Center” in the settings. I want to underline that for script to work, token has to obtain at least a basic access level.

4.4. Then a browser will be automatically opened, and there you will again need to choose a required Google account, permit access for viewing Google AdWords data, copy the generated key and paste it into the RStudio console.

RStudio Console

The authentication process is finished at this point, and all we have to do is to copy and run the last part of a script that will request data from Google Analytics and Google AdWords, gather all information in one spreadsheet and conduct additional calculations.

5. Add the last R Script fragment

Paste the final part of a script under all of previously pasted fragments, highlight it and press “Ctrl + Enter”.

1. #4.Requesting data from services.
2. #4.1. Receiving data from Google Analytics
3. #4.1.1. Describing the query to Google Analytics
4. query.list <- Init( = paste(start_period["year"],start_period["month"],start_period["day"],sep = "-"),
5.           = paste(end_period["year"],end_period["month"],end_period["day"],sep = "-"),
6.                    dimensions = "ga:adwordsCampaignID, ga:campaign",
7.                    metrics = "ga:transactions,ga:transactionRevenue",
8.                    filters = "ga:medium==cpc, ga:source==google",
9.           = ga_view)
11. #4.1.2. Creating an API query object to Google Analytics
12. ga.query <- QueryBuilder(query.list)
14. #4.1.3. Receiving data from Google Analytics in R
15. gaData <- GetReportData(ga.query, ga_auth, split_daywise = FALSE, paginate_query = FALSE)
17. #4.2. Reeceiving data from Google AdWords
18. #4.2.1. Describing the API query to Google AdWords.
19. body <- statement(select=c('CampaignId',
20.                            'Impressions',
21.                            'Clicks',
22.                            'Cost',
23.                            'Ctr',
24.                            'SearchBudgetLostImpressionShare',
25.                            'SearchRankLostImpressionShare  ',
26.                            'ContentBudgetLostImpressionShare',
27.                           'ContentRankLostImpressionShare'),
28.                   report="CAMPAIGN_PERFORMANCE_REPORT",
29.                   start=paste0(start_period["year"],start_period["month"],start_period["day"]),
30.                   end=paste0(end_period["year"],end_period["month"],end_period["day"]))
32. #4.2.2. Sending the query to Google AdWords
33. adwordsData <- getData(clientCustomerId = adwords_id,
34.                 google_auth = adwords_auth,
35.                 statement = body,
36.                 transformation = T,
37.                 apiVersion = "201509")
39. #5. Preparing the summary table.
40. #5.1. Combining data from Google Analytics and Google AdWords in one table
41. totalData <- merge(gaData, adwordsData, by.x = "adwordsCampaignID", by.y = "CampaignID", all.x = TRUE)
43. #5.2. Replacing missed values with zeros
44. for (i in 1:length(totalData)){
45.   totalData[which([i])),i] <- 0
46. }
48. #5.3. Final calculations of the number of lost transactions and revenue.
49. totalData$lostImpressionByBudgetSearch  <- round(totalData$Impressions / (1-totalData$`SearchLostIS(budget)`) - totalData$Impressions,0)
50. totalData$lostImpressionByRankSearch    <- round(totalData$Impressions / (1-totalData$`SearchLostIS(rank)`) - totalData$Impressions,0)
51. totalData$lostImpressionByBudgetDisplay <- round(totalData$Impressions / (1-totalData$`ContentLostIS(budget)`) - totalData$Impressions,0)
52.totalData$lostImpressionByRankDisplay   <- round(totalData$Impressions / (1-totalData$`ContentLostIS(rank)`) - totalData$Impressions,0)
53. totalData$lostImpressionByBudget        <- totalData$lostImpressionByBudgetSearch + totalData$lostImpressionByBudgetDisplay
54. totalData$lostImpressionByRank          <- totalData$lostImpressionByRankSearch  + totalData$lostImpressionByRankDisplay
55. totalData$lostClicksByBudget            <- round(totalData$lostImpressionByBudget * (totalData$CTR),0)
56. totalData$lostClicksByRank              <- round(totalData$lostImpressionByRank * (totalData$CTR),0)
57. totalData$lostTransactionsByBudget      <- round(totalData$lostClicksByBudget * (totalData$transactions / totalData$Clicks),0)
58. totalData$lostTransactionsByRank        <- round(totalData$lostClicksByRank * (totalData$transactions / totalData$Clicks),0)
59. totalData$lostTransactions              <- totalData$lostTransactionsByBudget + totalData$lostTransactionsByRank
60. totalData$lostRevenueByBudget           <- round(totalData$lostTransactionsByBudget * (totalData$transactionRevenue / totalData$transactions),0)
61. totalData$lostRevenueByRank             <- round(totalData$lostTransactionsByRank * (totalData$transactionRevenue / totalData$transactions),0)
62.totalData$lostRevenue                   <- totalData$lostRevenueByBudget + totalData$lostRevenueByRank
64. #6. Unloading the calculated table in csv file
65. write.table(totalData, file='lostRevenue.csv', sep = «;», dec = «,», row.names = FALSE)
67. #7. Visualization in the form of a pie chart
68. lost_revenue <- c('received revenue' = sum(totalData$transactionRevenue), 'lost by budget' =sum(totalData$lostRevenueByBudget), 'lost by rank' = sum(totalData$lostRevenueByRank))
69. pie(lost_revenue,col = c("green", "red", "firebrick"))

Now a script on estimating lost revenues is ready. In the bottom right corner of RStudio will appear a diagram estimating total income losses. In addition, it also shows losses connected with low rates and lack of budget resources.

Chart evaluating total loss of income

If you have any problems, download the full script listing that should emerge with correct implementation of instructions described in the article. Open it with RStudio and step by step go through the current manual, running certain parts of a code.

Three advices on using a script

1. You don’t have to run the whole script during the repeated start. In case you have interrupted session R, you don’t have to, for example, reinstall packages – you can just connect the “RAdwords” and “RGoogleAnalytics” packages. To do this, you have to activate the following script:

1. #connecting required packages
2. require(RGoogleAnalytics)
3. require(RAdwords)

2. Google Analytics token has a certain longevity. That is why some problems with authentication can emerge during the repeated start in the current service. If you have faced such problem, you can pass through a repeated token validation with the help of “ValidateToken” command.

1. ValidateToken(ga_auth)

3. For repeated authentication in Google Analytics, you will need credentials that you have already saved at the beginning of your working process. If you need to receive data from the other Google account, you have to delete “google.auth.RData” data in working directory in R. You can do this with the following script:

1. #Deleting file with credentials during the session in R:
2. system("rm .google.auth.RData")

Then, delete the previously created authentication object to Google AdWords or Google Analytics (within our example, AdWords is an «adwords_auth» object, while for Google Analytics — «ga_auth»). For passing through a complete repeated authentication in the services, use the following script:

1. #Deleting authentication objects during session in R
2. rm(adwords_auth)
3. rm(ga_auth)
4. #Re-authentication
5. adwords_auth <- doAuth(F)
6. ga_auth <- Auth(clienid,secret)

How to visualize the received information?

1. Build a diagram with the help of programming language R

Visualization opportunities in R are quite vast, but to master them you will have to spend quite a big amount of time. One of the variants of visualization in R is to build a diagram with the help of “ggplot2” package:

1.1. Install and connect the following package:

1. install.packages("ggplot2")
2. require("ggplot2")

1.2. Form a data frame, on the basis of which you will build a histogram.

1. #Framing data for histogram
2. HistData <- rbind(data.frame(subset(totalData, select = c("Campaign", "transactionRevenue")), Type = "GottenRevenue"),
3.                  setNames(data.frame(subset(totalData, select = c("Campaign", "lostRevenueByBudget")), Type = 3. "LostByBudget"), c("Campaign", "transactionRevenue","Type")),
4.                   setNames(data.frame(subset(totalData, select = c("Campaign", "lostRevenueByRank")), Type = "LostByRank"), c("Campaign", "transactionRevenue","Type")))
5. HistData <- HistData[!is.nan(HistData$transactionRevenue),]

1.3. Build a columnar diagram on the basis of received data in R.

1. #Building a histogram based on the package ggplot2
2. ggplot(HistData, aes(x = Campaign, y = transactionRevenue, fill = Type))+
3. geom_bar(stat = "identity", position = "fill")+
4. scale_fill_manual(values=c("forestgreen", "firebrick1", "tan1"))+
5. theme(axis.text.x = element_text(angle = 90, hjust = 1, size = 7))+
6. ggtitle("Lost Conversion rate")

Building a histogram with the help of language R

2. Work with data in CSV-file

For more routine work with diagrams you can unload the summary table with all calculations in CSV-file format into the working directory.

2.1. Unloading data. To find a folder in which the file was saved by default, run this command.

1. getwd()

After slightly changing one line of a script, you can yourself indicate the path for saving the CSV file that you have already planned. The line for saving file into the folder by default looks like this:

1. write.table(totalData, file='lostRevenue.csv', sep = ";",dec = ",", row.names = FALSE)

To change the destination folder to the other one, for example, to the Data folder on disk C of your computer, you have to specify a path before the file’s name. It is necessary to underline that the folder has to be created before the script launch.

1. write.table(totalData, file='c:/Data/lostRevenue.csv', sep = ";",dec = ",", row.names = FALSE)

2.2. CSV file description.

Field Description
adwordsCampaignID ID of the advertisement campaign
campaign Name of the advertisement campaign
transactions The amount of received transactions in the advertisement campaign
transactionRevenue The amount of transactions revenue received from the advertising campaign in the currency of your Google Analytics account
Impressions The amount of received impressions
Clicks The amount of received clicks
Cost The amount of money spent on an advertising campaign in the currency of your AdWords account
CTR The CTR of an advertising campaign
SearchLostIS(budget) The percentage of lost impressions in the search due to premature expenditure of the daily budget
SearchLostIS(rank) The percentage of lost impressions in the search due to low ranking
ContentLostIS(budget) The percentage of lost impressions in the context of media network in connection with a low rating
ContentLostIS(rank) The percentage of lost impressions in the context of media network due to premature expenditure of the daily budget
lostImpressionByBudgetSearch The number of lost impressions in the search due to premature expenditure of the daily budget
lostImpressionByRankSearch The number of lost impressions in the search due to low ranking
lostImpressionByBudgetDisplay The total number of lost impressions due to premature expenditure of the daily budget/td>
lostImpressionByRankDisplay The number of lost impressions in the context of media network in connection with a low rating
lostImpressionByBudget The total number of lost impressions due to premature expenditure of the daily budget
lostImpressionByRank The total number of lost clicks due to the low ranking
lostClicksByBudget The total number of lost clicks due to premature expenditure of the daily budget
lostClicksByRank The total number of lost clicks due to the low ranking
lostTransactionsByBudget The total number of lost transactions due to premature expenditure of the daily budget
lostTransactionsByRank The total number of lost transactions due to low ranking
lostTransactions The total number of lost transactions
lostRevenueByBudget The total amount of lost revenue due to premature expenditure of the daily budget
lostRevenueByRank The total amount of lost revenue due to low ranking
lostRevenue The total amount of lost revenue

On the basis of this file you can build graphs or spreadsheets for further analysis in any type of spreadsheet editor program: Microsoft Excel, Google Spreadsheets, LibreOffice and other.

Working with data in CSV file

A Microsoft Excel chart sample


1. For the algorithm realization you need:

  • A setup transaction monitoring in Google Analytics;
  • An established connection between Google Analytics and Google AdWords;
  • An installed programming language R on your PC and the instrument for working with it – RStudio;
  • Developer Client in Google developer console;
  • AdWords developer token for accessing API Google AdWords.

2. To run script in RStudio you have to:

  • Run the “devtools”, “RGoogleAnalytics” and “RAdwords”packages;
  • Set up the “clienid”, “secret”, “ga_view”, “adwords_id”, “start_period” и “end_period" variables;
  • Pass authentication in Google Analytics and Google AdWords;
  • Run the script.

3. In order to visualize information, you have to master the programming language R or to download data in CSV file format and process them in the routine spreadsheets editor. Using the received information you can define in which advertising campaigns and for what reason your income is getting lost. And on the basis of this information you can redistribute your budget, change your bids or to study the keywords in order to improve the quality indicator.

Comments (0)

To leave a comment, you have to log in.


to the most useful newsletter on internet marketing


discussed popular read