If you manage Google Ads accounts, you have likely added ads to ad groups and checked their performance over time. It is generally a good idea to have two or three ads in an ad group, running at the same time. Then you can compare them, pause the ads that are not performing well, then introduce new ads.

However, you may have noticed that ads are not always shown equally during a given period. Even if they are, there is often not enough data to determine that one ad is actually better than another. Since we are really looking at a sample, the outcome is not exactly definitive for the population.

Another issue is that you likely have a number of campaigns, with a number of ad groups. That leaves you with a lot of ads to review and determine which ones should be paused (so you can introduce new ads). A corollary to this function is what type of new ads should be introduced – that’ll be for another post.

At the heart of successfully managing media is analytics. In other words, your media success is in proportion to how deeply you utilize your data to make decisions. To that end, in this post I’m going to show you how to create an R Shiny app to run through your ads and display a list of ads that should be paused based on a statistical analysis.

The first step is to get access to data through Google Ads api. To do this you must set up an MCC (My Client Center) account. This is a must if you are an agency handling multiple accounts (you should know that if you are) as well as those that are only handling a single account internally (we’ll call that account the client account). Once you have set up the MCC account, connect to the client by sending a request for access using the account id.

Once that is all taken care of, you will need to set up api access. To do that, go to the MCC account, Tools & Settings, API Center and fill out the form for api access. You will need Basic Access, so fill out the Basic Access form. Then, wait…

It generally takes a few days for you to receive a confirmation for your Basic Access api request to be cleared. Once it does, we’re ready to build our Shiny app.

### Shiny App Set-up

I’m going to do this as a single file R Shiny app with a single (client) account, just to simplify the process. Because of that, I’m going to hard-code the account id as a variable. If you have multiple accounts, you can use a drop-down to make a selection or retrieve all accounts and output the list with the account.

To start, here is our global variables:

# Set the locale, otherwise, an error can be thrown.
Sys.setlocale('LC_ALL','C')

# if not installed.
if (!require("pacman")) install.packages("pacman")
tidyverse,
shinythemes,
config,
pwr,
DT,
scales)

# Authenicate access - this will open a browser window
# this will error later, when we try to retrive data.

This will load al the packages we are going to be using, plus setting up authentication for Google Ads. Sys.setlocale() may not be something you need to use, but it has solved issues for me, so I always include it.

### Creating the UI

Next, you will need a UI to interface with. To do that we set the ui to fluidPage(), which we’ll call at the end, to run the Shiny app. I set the theme to “lumen” because I like that theme. There are many others. We then add our rows and columns.

ui <- fluidPage(theme = shinytheme("lumen"),

fluidRow(

column(3,
dateInput("begin_date_select",
min = Sys.Date() - 180,
max = Sys.Date(),
value = Sys.Date()-90),

dateInput("end_date_select",
min = Sys.Date() - 180,
max = Sys.Date(),
value = Sys.Date()),

selectInput('conv_category', 'Evaluate by CTR or Conv. Rate (select one):',
c("Pick one" = "",
"CTR" = "ctr",
"Conversion Rate" = "conv_rate")),

actionButton("goButton", "Calculate")
),

column(9,

# A list of the poor performing ads.
div(DT::dataTableOutput("ads_table", width = "100%", height = "auto"),
style = "font-size:80%")

)

)

)

As you can see, there is just one row – a fluidRow(). That is because this is a rather simple UI. The columns can range from 1 to 12. So we are using 3 column spaces for the inputs and 9 column spaces for the table output. There is also a title. In this case, I’m using “Google Ads: Under Performing Ads”.

The inputs are the date range you want to use to calculate performance. Keep in mind that the way I have programmed this, it will only eveluate the ads that are currently running. The reason for this is that you don’t want to pause those ads, only to see them again and again in the output. So, I would recommend a recent date range to review.

There is also an actionButton. Without this, every time you updated one field, but before you were done, the app would run and try to retrieve the data. The actionButton allows you to update the dates and CTR/Conversion Rate options, then fetch the data.

Finally, there is the output – DT::dataTableOutput() – which outputs a table with the underperforming ads.

### Creating the Server

The server function is going to be inside a function called shinyServer(function(input, output){}). I’m going to go through this one piece at a time. At the end of this post I’ll have a link to the Shiny file in full.

To start, I’m going to use a function called ad_data(), in which I’ll pass the begin/end date, the Google Ads account id and whether to calcuate performance based on CTR or Conversion Rate. I’ve added a lot of comments in the code (I’m not usually this verbose in code comments – but this is a way to understand the code), so read through those to understand what is executing and why.

server <- shinyServer(function(input, output) {

# This fuction will do all the work on the ads
# and return the data to the calling function.
end_date,
account_id,
conv_category) {

# We're going to use the AD_PERFORMANCE_REPORT
# We want to make sure we're getting the raw data -
# CTR and Coversion Rate would be useless here since
# we need the raw numbers to feed into our model.
body <- statement(select = c('CampaignName',
'CampaignStatus',
'Id',
'Status',
'Impressions',
'Clicks',
'Conversions'),
start = begin_date,
end = end_date)

# Use Adwords Account Id (MCC Id will not work).
# If you do not have Basic Access API, this will error.
get_data <- getData(clientCustomerId = account_id,
statement = body)

# Now that we have the raw data, we're going to do a few things to it:
# 1. rename some of the columns to something more R like.
# 2. filter for those ads that have over 100 impressions (larger sample size),
#    finally, evaluate only ads that are enabled, in adgroups that are enabled
#    from campaigns that are enabled.
# 3. calcuate CTR, Conversion Rate, and add two new columns:
#    ctr_update and conv_rate_update.
# 4. Select only those columns needed going forward.
#    For example, we no longer need Campaignstate.
dplyr::rename(campaign = Campaign,
clicks = Clicks,
impressions = Impressions,
transactions = Conversions) %>%
dplyr::filter(impressions > 100,
Campaignstate == 'enabled',
dplyr::mutate(ctr = round(clicks/impressions, 5),
conv_rate = round(transactions/impressions, 5),
ctr_update = FALSE,
conv_rate_update = FALSE) %>%
dplyr::select(campaign,
clicks,
impressions,
transactions,
ctr,
conv_rate,
ctr_update,
conv_rate_update)

# At the beginning we passed in conv_category to the fuction.
# conv_category can be one of two values: 'ctr' for CTR or
# conv_rate for Conversion Rate.
# Calculate output for one or the other.
if(conv_category == 'ctr'){

# It is important to aggrange the data by
# the campaign, adgroup, and ctr (click-through rate).

# This will be the ad with the best CTR in the adgroup.
# We need to assign the ad group.

# i will equal the next ad.
i <- 2

# We'll loop through all the ads.

# Check if this is a new adgroup - if so,
# this is the best performing ad in the set -
# because we sorted it that way -

i <- i + 1
next
}

# The following is the sample size (# of impressions) and
# the # of clicks from the top CTR ad in the ad group.

# The following is the sample size (# of impressions) and
# the # of clicks from the current ad in the ad group.

# We're going to use the prop.test() and use the current
# ad as the NULL or A in an A/B test. We're checking to
# see if the top ctr ad is statistically significantly
# greater than the A (current ad). If it is, we'll signal to change
# We'll use a 0.13 as a p-value as the value for change.
# Why 0.13 - it's a prime and it seems resonable.
myProp <- prop.test(x = c (a_comps, b_comps),
n = c (a_sample_size, b_sample_size),
alternative = 'greater')

# Check to make sure you have a p-value.
if(is.nan(myProp$p.value)){ i <- i + 1 next } # If the p-value is less than 0.13, # update ctr_update to TRUE. if(myProp$p.value < 0.13) {

}

# Move to the next row.
i <- i + 1

}

# Output only the ads that have ctr_update == to TRUE.
output <- ads_data %>% dplyr::filter(ctr_update == TRUE) %>%
dplyr::select(campaign,
impressions,
clicks,
ctr) %>%
dplyr::mutate(ctr = percent(ctr)) %>%
dplyr::arrange(campaign,

} else {
# In the alternate - this is for conversion rate - so order by

# This will be the ad with the best CTR in the adgroup.
# We need to assign the ad group.

# i will equal the next ad.
i <- 2

# We'll loop through all the ads.

# Check if this is a new adgroup - if so,
# this is the best performing ad in the set -
# because we sorted it that way -

i <- i + 1
next
}

# The following is the sample size (# of impressions) and
# the # of transactions from the top converting ad in the ad group.

# The following is the sample size (# of impressions) and
# the # of transactions from the current ad in the ad group.

# We're going to use the prop.test() and use the current
# ad as the NULL or A in an A/B test. We're checking to
# see if the top converting ad is statistically significantly
# greater than the A (current ad). If it is, we'll signal to change
# We'll use a 0.13 as a p-value as the value for change.
# Why 0.13 - it's a prime and it seems resonable.
myProp <- prop.test(x = c (a_comps, b_comps),
n = c (a_sample_size, b_sample_size),
alternative = 'greater')

# Check to make sure you have a p-value.
if(is.nan(myProp$p.value)){ i <- i + 1 next } # If the p-value is less than 0.13, # update conv_rate_update to TRUE. if(myProp$p.value < 0.13) {

}

# Move to the next row.
i <- i + 1

}

# Output only the ads that have conv_rate_update == to TRUE.
output <- ads_data %>% dplyr::filter(conv_rate_update == TRUE) %>%
dplyr::select(campaign,
impressions,
transactions,
conv_rate) %>%
dplyr::mutate(conv_rate = percent(conv_rate)) %>%
dplyr::arrange(campaign,

}

# Return the dataset.
return(output)
}

To call the ad_data() function and retrieve the data, I’m going to use a reactive dataframe. A reactive dataframe has scope outside of a single calling function and can be shared for use in charts, tables, other functions without having to refetch the data. As you’ll see, this is a fairly simple call.

# If the actionButton ("goButton")
# was not pressed, exit the function.
if(input$goButton == 0){return()} # Isolate and assign the inputs. begin_date <- isolate(input$begin_date_select)
end_date <- isolate(input$end_date_select) conv_category <- isolate(input$conv_category)

# You can hard-code it here, or use a
# dropdown in the UI, as well as passing multiple
# which you can loop through by calling the
# ad_data() function and combining the dataframes
# with rbind().
account_id <- "XXX-XXX-XXXX"

# Validate the dates and conv_category.
validate_text <- paste('Woops! Your begin date is ', begin_date,
' and your end date is ', end_date,
'. I think you see the issue ;-)
Just adjust the dates and re-run. :-)',
sep = '')

validate(

need(begin_date < end_date, validate_text)

)

validate(

need(conv_category != '', "Opps! Select an Evaluation Category :-)")

)

end_date,
account_id,
conv_category)

# Return ad_data dataframe to the caller.

})

### Creating the Data Table

The last thing that we need to do is to create the data table that will display a list of ads that should be paused. The cool thing about this app is that when you pause the ads, they will not be considered again, next time you fetch the data.

You’ll see that there is not a lot to do here, since most of the work has been done already.

# ads_table will match our table in the UI -
# we use renderDataTable({}) from the DT package
# to create the table and send it to the UI.
# Output Data Table ####
output$ads_table <- DT::renderDataTable({ # If the goButton hasn't been pressed, # don't make a table. if(input$goButton == 0){return()}

# We're just going to assign
# the data returned from the
# reactive function to a new variable.
# The main reason I do this, is because
# later it can be easier to troubleshoot.

# Then simply pass the table
# to the datatable() function.
datatable(data_rtn)
})

}) # Close the opening shinyServer(function(input, output) {

### Conclusion

That’s about it, however, you do need to call the app with:

shinyApp(ui = ui, server = server)

Now you have an app that will list the ads that need to be paused because, statistically, they are not as good as top performing ad in the given ad group. This is better than just eye-balling the ads to guess poor performers and it scales. With some additional programming, you can scale this to numerous accounts.

It would seem that automatically pausing those ads would be a good idea. However, I’m not sure about that. I think you want to study the good and bad performers and figure out why some ads are performing better than others. Then pause the poor performing ads and introduce new ones based on what you learned.

You can find the full code to this app on my GitHub @ https://github.com/daranjjohnson/underperforming_google_ads.

### Sample App

To see a working example of the app, click here. This sample app is hosted on shinyapp.io and changing the dates does not change the data. However, which ads need updating change based on whether you select “Conversion Rate” or “CTR” as to how you want to evaluate the ad performance.

Here is the data used in the app: