No one wants to spend money on fraudulent Google Display Network (GDN) placements. No one wants to drive unrealistic traffic to websites. However, fraudulent placements are still a big problem for marketeers.
In this post, we’ll show you the entire journey of using Google Analytics and Google Cloud infrastructure to exclude fraudulent placements on the Google Display Network (GDN) by implementing IP scoring.
Google Analytics to BigQuery exportation
First, we need to have Google Analytics conversions with GCLID reporting available. Once GCLID is enabled, export Google Analytics conversions with GCLID data to BigQuery.
Tracking function to BigQuery
To store visitor IPs and placement URLs in BigQuery, a new Google Cloud function was created. This function is an HTTP-driven function that is triggered for each visitor to the website. The function takes the visitor IP and placement information and stores it in the BigQuery table. This data is used together with Google Analytics Conversions with GCLID report in our analysis.
IP Scoring with API
Google Cloud Function is a good choice to automatically check the status of IPs, as we can easily schedule the function to run daily. We can trigger the function using Pub/Sub. Then, the results are stored in BigQuery.
To check IPs, the IP Quality Score API is used. This API can provide a fraud score for each IP. It can also indicate if there is a current abuse status for each IP. The IP Quality Score API has a free plan with 5000 requests per month, but shouldn’t exceed 200 requests per day.
We decided to use the free IP Quality Score API plan, but the API has limitations on a monthly and daily basis. For this reason, we implemented a daily scheduled feature that only checks 200 IPs. However, what if we have more than 200 IPs to check that day? In this case, 200 IPs will be checked first. Then, the rest are stored in a temporary BigQuery table to be checked tomorrow with the next day’s IPs. In addition, before checking an IP and sending a request to see their fraud score, we also check in the BigQuery table whether the IPs have already been scanned or not. If they have already been scanned, we don’t need to check them again and can save the API quota for the other IPs.
The free plan will be enough for us if we use this structure. In the beginning, it’ll take a few days for us to score the IPs because there are no scored IPs. However, eventually our IP scoring database will have so many entries that the function won’t score all IPs, but only the new ones.
Scheduled queries
Now we have the scored IPs and related placements to them. Using the IP scores, we can write an SQL query to select all fraudulent IPs with their associated placements and use them to exclude placements. Since we receive new IP scores every day, it makes sense to schedule an SQL query to select these fraudulent placements.
After we have these excluded placements, we need to transfer them to cloud storage as a CSV file to have them ready for bulk upload of offline conversions. Therefore, a daily scheduled data export from BigQuery to the cloud storage has been implemented.
Header transformation
Since BigQuery doesn’t accept that column headers contain spaces, they’re replaced by “_”. Now we need another cloud function that will be triggered when there is a new file in the cloud storage. This file will make the header transformations suitable for ads bulk uploads. Header transformations include replacing “_” with spaces and capitalizing each keyword in the headers. After the header transformation, the function saves the modified data again as a CSV file in the cloud storage. Now the data is ready to be uploaded.
Upload data to Google Ads
Once we’ve prepared the file, we’re ready to upload the data to Google Ads. Log into your Google Ads account where you want to upload your conversions and follow the steps below.
- Sign in to your Google Ads account.
- In the top menu, click “Tools and Settings” and then “Conversions”.
- Click “Uploads” in the left menu.
- Click the “+” button.
- Select “HTTPS” from the dropdown menu on the left and type the URL of your CSV file into the Google Storage you created earlier in the “Source URL”.
- You can preview the results by clicking “Preview” or complete the upload process by clicking “Apply”.
Display Placement IP Grouper
Display Placement IP Grouper is an excellent tool for simplifying the process of grouping IP addresses for ad placement. This tool offers numerous benefits, including superior efficiency, precision, and convenience in ad placement. It allows users to group IP addresses effortlessly and with minimal risk of error, saving valuable time. In addition, the tool helps maximize the accuracy of ad targeting and ensures that ads are displayed to the most relevant audiences. Want to improve your ad targeting and placement strategies? Then, this tool is ideal for you.
Need a custom solution? Contact our experts now.
