Every now and then I\u2019m fortunate enough to be able to publish guest posts<\/a> by illustrious people in the analytics and digital marketing industries. This time, I get to work with an old colleague of mine who\u2019s a veritable wizard when it comes to building solutions that make our daily work in the digital industry so much smoother.<\/p>\n Erik Gr\u00f6nroos<\/strong> works as an analyst in MarkkinointiAkatemia<\/a>, a Finnish digital customer acquisition agency. Erik\u2019s job is to utilize data and analytics to help grow the agency as well as the companies of the clients he works with.<\/p>\n In this guide, Erik will share with you how to build an application which helps you automate the monitoring of your Google Ads<\/a> accounts using the R programming language<\/a>, the AdWords API<\/a> and the Slack API<\/a>.<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n I\u2019m particularly excited to publish this article, as it\u2019s the first time (if I recall correctly) that Google Ads and R are covered in the blog.<\/p>\n Anyway, as the editor, all the mistake and errors that you can find in the article are 100% mea culpa<\/em>, so let me know in the comments if errors persisted to the final draft. Thank you!<\/p>\n \n \n Subscribe to the Simmer newsletter<\/a> to get the latest news and content from Simo Ahava into your email inbox!\n <\/p>\n <\/span><\/p>\n In this article I\u2019ll show you how to implement an automated monitoring application<\/strong> for keeping an eye on your Google Ads accounts. Since the nature of the implementation is a Proof of Concept (PoC), I\u2019ll try to keep it simple by using just one practical use case for demonstrating the end-to-end pipeline:<\/p>\n Zero-cost detection in Ads accounts, for monitoring credit card expirations or other issues that prevent your ads from rolling.<\/strong><\/p>\n This implementation utilizes R<\/strong> and the Google AdWords API<\/strong> for checking the data, Slack API<\/strong> for realtime alerting via your Slack bot if something critical is detected, and good ol\u2019 If you\u2019re a marketing agency, a typical situation is that you have plenty of Google Ads accounts to take care of. When the number of accounts breaches the hundreds or the thousands, manually managing, monitoring, and maintaining them isn\u2019t really an option anymore.<\/p>\n What you can do, of course, is set up Google Ads\u2019 built-in alerts<\/a> one by one for each of your accounts. You could even manage these alerts via the Google Ads API<\/a> or utilize Google Ads scripts<\/a>. But the problem with these is that they involve email alerts by default. Hey, it\u2019s 2019. Email is probably not the only channel you should use for building automation on.<\/p>\n So, we decided to harness the force of APIs and, as a result, saved the galaxy!<\/p>\n The script is populated with the list of Google Ads account IDs<\/strong> we want to monitor. The monitoring system itself queries the cost data<\/strong> of each ID using the Google AdWords API<\/strong>, and parses the results for those accounts that have accumulated no cost at all, signalling potential issues with your budgets or your configurations.<\/p>\n When this information has been parsed, the account IDs that were flagged are written to a database<\/strong> for further analysis, and a notification is dispatched to the Slack bot<\/strong> so that the team responsible can be immediately notified when problems arise.<\/p>\n R is a statistical programming language that today supports pretty much the same Machine Learning (ML) tricks that Python does. R vs. Python is a long-standing debate, so it really boils down to language preference: which one do you want to use for cool data stuff! Okay, I\u2019ll put my pants back on, hold on.<\/p>\n We don\u2019t need ML in this simple monitoring example, but we do use it for more advanced solutions. That\u2019s why R is the tool of choice for us.<\/p>\n RStudio is an IDE for R, it makes R easier to use. Quote from RStudio\u2019s website:<\/p>\n RStudio is an integrated development environment (IDE) for R. It includes a console, syntax-highlighting editor that supports direct code execution, as well as tools for plotting, history, debugging and workspace management. RStudio is available in open source and commercial editions and runs on the desktop (Windows, Mac, and Linux) or in a browser connected to RStudio Server or RStudio Server Pro (Debian\/Ubuntu, RedHat\/CentOS, and SUSE Linux).<\/p>\n<\/blockquote>\n If you don\u2019t have RStudio installed yet, grab yours from RStudio downloads<\/a>.<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n We won\u2019t get into the RStudio installation details in this article. RStudio\u2019s website provides comprehensive documentation and step-by-step how-to\u2019s for that.<\/p>\n Once you have your RStudio setup up and running, let\u2019s script!<\/p>\n We are using MariaDB<\/strong> to store the Google Ads Customer IDs of our clients, where they can be retrieved from for several other integration and automation purposes we need.<\/p>\n If you\u2019re using some other database server, you need to modify the DB part of the code to suit your needs. R has comprehensive support for a wide variety of databases, perhaps even all of them. Basically, if it\u2019s something to do with data, R can read from it.<\/p>\n If you\u2019re not using a database, you can skip the DB part. Optionally, R can also read the IDs from e.g. Google Sheets or a CSV file. How to do this in R is your homework!<\/p>\n You\u2019re probably thinking \u201cBut why aren\u2019t you reading the IDs directly from AdWords API\u201d?<\/p>\n Well, the unfortunate thing is that RAdwords<\/strong>, the R AdWords package, implements the Adwords API Reporting Service only, and the Reporting Service does not support querying Google Ads Customer IDs on the Manager Account level (formerly known as My Client Center<\/em> or Google MCC<\/em>). Instead, you\u2019d need access to AdWords API Managed Customer Service to do that.<\/p>\n In our case, this isn\u2019t a problem though, because we want to persist the IDs in our own custom environment and have different classifications and levels for different sort of contracts and clients. This way we are able to do the desired actions to those clients that need them, and we can also add monitoring capabilities to the setup.<\/p>\n If you need to read the IDs directly from the AdWords API, you can access the Managed Customer Service via a newer R package called adwordsR<\/a>.<\/p>\n Create a new R file in your RStudio with the following content, set up your DB user and pass, and save the file as Create another file including the following code and save the file as And finally the actual Ads R script, include the following code and save it as Now you have your Ads zero cost monitor script ready and you can move on to setting up your Slack bot.<\/p>\n Because the guys at Slack are awesome, they have written an excellent guide on how to set up your bot. They even serve you virtual cookies! Slack\u2019s guides are a textbook example about how documentation should be written – they\u2019re always a pleasure to read and follow! Thanks guys!<\/p>\n![\"Google](\"https:\/\/www.simoahava.com\/images\/2019\/04\/article-header.jpg#ZgotmplZ\")
<\/p>\n
\n X<\/span><\/p>\n
\n The Simmer Newsletter<\/strong>\n <\/p>\nIntroduction<\/h2>\n
cron<\/code> for scheduling the script.<\/p>\nHow the solution works<\/h2>\n
Requirements<\/h2>\n
About R<\/h2>\n
\n
\u00a0<\/p>\n<\/blockquote>\n\n
![\"R](\"https:\/\/www.simoahava.com\/images\/2019\/04\/r-studio.jpg#ZgotmplZ\")
<\/p>\nEpisode I: An R script<\/h2>\n
Now, let\u2019s script for real!<\/h3>\n
db_credentials.R<\/code> in \/YOUR_R_USER_ROOT_DIR\/auth\/<\/code>. This is your DB auth file.<\/p>\ndb_username = \"YOUR_USERNAME\"<\/span>\ndb_password = \"YOUR_PASSWORD\"<\/span><\/code><\/pre>\n<\/div>\nconnect_db.R<\/code> in \/YOUR_R_USER_ROOT_DIR\/<\/code>. This is your DB connector.<\/p>\n# Include DB credentials from your \/auth\/ dir<\/span>\nsource<\/span>(\"~\/auth\/db_credentials.R\"<\/span>)\n\n# Check if the required packages are installed, install if not<\/span>\nif<\/span>(!require<\/span>(DBI)) install.packages<\/span>(\"DBI\"<\/span>) \nif<\/span>(!require<\/span>(RMariaDB)) install.packages<\/span>(\"RMariaDB\"<\/span>)\n\n# Load packages<\/span>\nlibrary<\/span>(DBI)\nlibrary<\/span>(RMariaDB)\n\n# Create a DB connection handle<\/span>\ncon dbConnect(\n drv = RMariaDB::MariaDB<\/span>(),\n username = db_username,\n password = db_password,\n host = \"YOUR_DB_HOST\"<\/span>,\n port = 3306<\/span>,\n \"YOUR_DB_NAME\"<\/span>\n)<\/code><\/pre>\n<\/div>\ngoogle-ads-zero-cost-monitor.R<\/code> in \/YOUR_R_USER_ROOT_DIR\/<\/code>.<\/p>\n# Init the DB connection<\/span>\nsource<\/span>(\"~\/connect_db.R\"<\/span>)\n\n# Check if the required packages are installed, install if not<\/span>\nif<\/span>(!require<\/span>(RAdwords)) install.packages<\/span>(\"RAdwords\"<\/span>)\n\n# Load packages<\/span>\nlibrary<\/span>(RAdwords)\n\n# Define the file path where your AdWords API OAuth RData file should be stored to, we'll need this next<\/span>\ngoogle_ads_auth_file \"~\/auth\/google_ads_auth.RData\"\n\n# Check if the google_auth RData file already exists, create if not<\/span>\nif<\/span>(!file.exists<\/span>(google_ads_auth_file)) {\n # Auth steps:<\/span>\n # 1) You'll need Google API Client ID, Client Secret and AdWords Developer Token here for authenticating, see https:\/\/github.com\/jburkhardt\/RAdwords for details<\/span>\n # 2) Once you've provided those in your R console when R requests them, you'll get redirected to OAuth screen in your browser<\/span>\n # 3) Authenticate with your Google Account (the same that has access to your AdWords account)<\/span>\n # 4) You'll be redirected to a OAuth success screen that provides a final token code and asks you to copy\/paste it to your application (to your R console in this case)<\/span>\n google_auth doAuth(save = FALSE<\/span>)\n \n # Save the auth credentials, to be used the auth from a file from now on<\/span>\n save<\/span>(google_auth, file = google_ads_auth_file)\n}\n\n# Load Google auth credentials<\/span>\nload<\/span>(file = google_ads_auth_file)\n\n# Init date vars<\/span>\ndayBeforeYesterday Sys.Date() - 2<\/span>\nyesterday Sys.Date() - 1<\/span>\n\n# Fetch Google Ads Customer IDs from your DB<\/span>\nads_accounts dbGetQuery(con, \"SELECT ads_customer_id FROM ads_accounts\"<\/span>)\n\n# Create a list of the IDs for later looping<\/span>\naccount_list # Alternatively, if you want to skip the DB stuff, you can define your Google Ads Customer IDs manually by uncommenting the following line and listing the IDs here:\n# account_list \n\n# Init AdWords API request<\/span>\n# We're checking the cost data from two previous days<\/span>\nbody statement<\/span>(select = c<\/span>('Cost'<\/span>),\n report = \"ACCOUNT_PERFORMANCE_REPORT\"<\/span>,\n start = dayBeforeYesterday,\n end = yesterday)\n\n# Init a data frame for collecting the data and and an index variable for keeping track of progressing during the for loop<\/span>\nadsdata data.frame()\nprogressIndex 0\n\n# Get the Ads cost data<\/span>\nfor <\/span>(account in account_list) {\n\n # Get cost data<\/span>\n ads_api_response getData(clientCustomerId = account,\n google_auth = google_auth,\n statement = body)\n\n # Proceed only if the API doesn't return an error (happens when there's no data to return or when the API fails), this is for dropping off false zero cost alerts<\/span>\n if<\/span>(!is.null<\/span>(dim<\/span>(ads_api_response))) {\n \n # Init empty data frame<\/span>\n adsdata_temp setNames(data.frame<\/span>(matrix<\/span>(ncol = 2<\/span>, nrow = 1<\/span>)), c<\/span>(\"Cost\"<\/span>, \"Account\"<\/span>))\n \n # Include account id<\/span>\n adsdata_temp$Account # Include cost data\n # If there's no data, the result is doubled for some reason<\/span>\n # Fix this by always reading the 1st value only with [1]<\/span>\n adsdata_temp$Cost # If cost is zero, NA is returned, replace it with zero\n if<\/span>(is.na<\/span>(adsdata_temp$Cost)) adsdata_temp$Cost 0\n \n # Collect during each iteration<\/span>\n adsdata rbind(adsdata, adsdata_temp, stringsAsFactors = FALSE<\/span>)\n \n }\n \n # Print progress to R console<\/span>\n progressIndex 1\n cat<\/span>(paste<\/span>(\"Query \"<\/span>, progressIndex, \" of \"<\/span>, NROW<\/span>(account_list), \" done\\r\\n\"<\/span>, sep = \"\"<\/span>))\n\n}\n\n# Keep accounts with zero cost only<\/span>\nads_accounts_with_zero_cost subset(adsdata, Cost == 0<\/span>)\n\n# Count nbr of alerted accounts<\/span>\nnbr_of_accounts_with_alerts nrow(ads_accounts_with_zero_cost)\n\n# Save all alerts to DB, if there was any<\/span>\n# This assumes you have DB table named \"alerts\" existing with fields: <\/span>\n# ads_customer_id VARCHAR<\/span>\n# category VARCHAR<\/span>\n# status_id INT<\/span>\n# created_at DATETIME<\/span>\n# updated_at DATETIME<\/span>\n\nif<\/span>(nbr_of_accounts_with_alerts > 0<\/span>) {\n \n for <\/span>(i in 1<\/span>:nrow<\/span>(ads_accounts_with_zero_cost)) {\n row \"ALERT_ZERO_COST\" # For categorizing different types of alerts<\/span>\n status_id 0 # To be set to eg. 1 when the alert is handled via a custom UI<\/span>\n \n # Save each item to db<\/span>\n query paste(\"INSERT INTO alerts (\n<\/span> ads_customer_id,\n<\/span> category,\n<\/span> status_id,\n<\/span> created_at,\n<\/span> updated_at)\n<\/span> VALUES (\"<\/span>,\n \"'\"<\/span>, ads_customer_id, \"', \"<\/span>,\n \"'\"<\/span>, category, \"', \"<\/span>,\n status_id, \", \"<\/span>,\n \"now(), \"<\/span>,\n \"now())\"<\/span>,\n sep = \"\"<\/span>\n )\n dbExecute<\/span>(con, query)\n }\n \n# If you want to check\/debug what was stored, uncomment and run these lines<\/span>\n# db_alerts \n# View(db_alerts)<\/span>\n \n}<\/span><\/code><\/pre>\n<\/div>\nEpisode II: The Slack bot strikes back<\/h2>\n
![\"Webhook](\"https:\/\/www.simoahava.com\/images\/2019\/04\/slack-bot-token.jpg#ZgotmplZ\")
<\/p>\n![\"Slack](\"https:\/\/www.simoahava.com\/images\/2019\/04\/slack-alert.jpg#ZgotmplZ\")
<\/p>\n