In addition to “getting directly from the management screen” and “getting it using Ads API/Script”, there are “directly exporting to BigQuery” for Google Ads report data. “BigQuery” sounds like a difficult method for engineers, but using ” BigQuery Data Transfer Service” makes it relatively easy to use.
In this article, I would like to introduce how to set up to use ” Data Transfer Service ” and how to use the data exported using this service.
What is the BigQuery Data Transfer Service?
The BigQuery Data Transfer Service is a service that automates the transfer of data from Google Ads, Google Ad Manager, Google Play, and other third-party applications to BigQuery. You don’t need to write any programming code to use this service.
As a data source supported by Google itself,
- Google campaign manager
- Google Ad Manager
- Google Ads
- Google Merchant Center (Beta)
- Google Play (Beta)
- Youtube channel report
- Youtube content owner report
- Cloud Storage (Beta)
- Amazon S3 (Beta)
there is. In addition to this, in 2019, this function was released to third parties, and through the “GCP Marketplace”, it became possible to easily transfer data to BigQuery from many other applications. Currently, data can be transferred from various platforms such as Adroll, Adobe Analytics, Salesforce and Marketo to BigQuery via 3rd party data connector platforms such as Supermetrics and Fivetran.
Regarding the usage cost, I would like to expect it to be free because it is a service developed by Google, but it is actually a paid service, not free. In addition to the basic charges related to BigQuery (storage cost, query cost, data input cost, etc.), the usage fee will be charged for the connector used.
For example, Google Ads charges $2.50 per month for each linked ad account . Converted to Japanese yen, it costs about 300 yen a month, so I think you can feel that you can work together at a fairly low price. Although it may be difficult to decide to use ).
How Google Ads data is transferred
If you’re already using BigQuery, you’ll be set up in minutes. If you are not using BigQuery (including Google Cloud Platform), please create a Google Cloud Platform project by referring to How to Start a GCP Project for Beginners .
Below, we will explain how to set up the Data Transfer Service step by step.
1. Select “Transfer” from the BigQuery menu
2. Enable Data Transfer API
4. Select data source type
5. Set data source
For “Customer ID”, specify the “Customer ID” of Google Ads. You can also specify the parent account ID of the MCC account here. If you specify an MCC account, you can export all child account data.
6. Wait until past transfer is complete
Transfer to BigQuery will start, including data for a certain period of time in the past. Depending on the amount of data, it may take some time, so please be patient.
List of data exported to BigQuery
When Google Ads data is transferred to BigQuery using the Data Transfer Service, 87 tables are automatically generated in BigQuery as of August 2019. This is supposed to contain all report types that can be retrieved using the Google Ads API.
The specific generated table is
- Account performance report
- p Customer *
- p HourlyAccountConversionStats *
- pAccountConversionStats * _
- p Hourly Account Stats *
- pAccountNonClickStats * _
- pAccountBasicStats * _
- p AccountStats *
- Ad performance report
- pAdBasicStats * _
- p AdCrossDeviceStats *
- p AdConversionStats *
- p AdStats *
- p AdCrossDeviceConversionStats *
- p Ad *
- Ad group performance report
- pAdGroupStats * _
- pAdGroupBasicStats * _
- p AdGroupCrossDeviceStats *
- p HourlyAdGroupConversionStats *
- p Hourly Ad Group Stats *
- p AdGroupConversionStats *
- p AdGroupCrossDeviceConversionStats *
- pAdGroup * _
- Performance report by age group
- p AgeRange *
- p AgeRangeBasicStats *
- p AgeRangeStats *
- p AgeRangeConversionStats *
- p AgeRangeNonClickStats *
- User performance report
- p Audience *
- pAudienceConversionStats * _
- pAudienceNonClickStats * _
- pAudienceBasicStats * _
- pAudienceStats * _
- Bid target performance report
- p Bid Goal *
- p Bid Goal Stats *
- p HourlyBid Goal Stats *
- pBidGoalConversionStats * _
- budget performance report
- p Budget *
- pBudgetStats * _
- Campaign Location Target Report
- p CampaignLocationTargetStats *
- pLocationBasedCampaignCriterion * _
- Campaign performance report
- p Campaign *
- p CampaignBasic Stats *
- p CampaignConversionStats *
- p CampaignCrossDeviceStats *
- p HourlyCampaignConversionStats *
- p Campaign Stats *
- p Hourly Campaign Stats *
- p CampaignCrossDeviceConversionStats *
- p Campaign Cookie Stats *
- Click performance report
- p ClickStats *
- Criteria performance report
- p Criteria *
- p Criteria Basic Stats *
- p Criteria Stats *
- p Criteria Conversion Stats *
- p CriteriaNonClickStats *
- Gender performance report
- p Gender *
- p Gender Basic Stats *
- p Gender Stats *
- p Gender Conversion Stats *
- pGenderNonClickStats * _
- Geographic performance report
- p GeoConversionStats *
- p GeoStats *
- Keyword performance report
- p Keyword *
- p KeywordBasicStats *
- p KeywordCrossDeviceStats *
- p KeywordStats *
- p KeywordCrossDeviceConversionStats *
- p KeywordConversionStats *
- Paid and organic search terms report
- p Paid Organic Stats *
- Parental status report
- p Parental Status *
- pParentalStatusBasicStats * _
- p Parental Status Stats *
- pParentalStatusConversionStats * _
- pParentalStatusNonClickStats * _
- Placement performance report
- pPlacementBasicStats * _
- pPlacementNonClickStats * _
- p Placement Stats *
- Placement * _
- p Placement Conversion Stats
- Search terms report
- p SearchQueryStats *
- p SearchQueryConversionStats *
- Shopping performance report
- p ShoppingProductConversionStats *
- p ShoppingProductStats *
- Video performance report
- pVideoBasicStats * _
- pVideoConversionStats * _
- p Video Stats *
- p Video *
- pVideoNonClickStats * _
(The * part is the advertising account ID). In this way, a table is generated that covers almost all patterns of reports that can be obtained with the API. In some cases, there are tables that hold similar content, so when you actually use it, you need to reconfirm whether the expected values are stored.
Precautions and usage examples of export data
Precautions when referencing monetary columns
If you only think about the Japanese yen, you usually don’t think much about it, but with a tool that assumes global use, it is necessary to be able to express amounts below the decimal point. However, when trying to represent decimal numbers using a computer, the accuracy of the numbers is inevitably degraded due to the internal processing, resulting in a situation where accurate calculations cannot be performed.
In Google Ads, in order to maintain sufficient accuracy for the amount, we internally take the solution of managing the amount series column in “micro” units. In microcurrency units, the normal amount is multiplied by 1,000,000. In other words, if you spend 10,000 yen on advertising, it will be expressed as “10,000,000,000” in microcurrency units. And APIs, including the Data Transfer Service, return amounts in this micro-currency unit. In order to convert microcurrency units to normal currency, we need to divide by “1,000,000”. Even if you forget to do this, the amount of money that comes out will be a huge value, so you can notice it immediately, but let’s remember that it is expressed like this.
Be careful when using calculated metrics
For example, be careful when using metrics such as CTR, CPC, conversion rate, average position, and impression share. Since the Data Transfer Service data is basically divided into as many items as possible, when actually using it in a report etc., the generated report will be aggregated and used. increase. At that time, metrics such as CTR and CPC should not be aggregated directly. After aggregating the metrics on which the calculation is based, it must be recalculated according to the definition.
You can imagine formulas for recalculating metrics such as CTR and CPC, but metrics such as average position and impression share require more complex formulas. This article will omit what kind of calculation formula to use, but let’s understand the meaning and definition of each indicator properly and use it.
Usage example of export data
The exported data contains a lot of information in your ad account. In the case of Google Ads, it is possible to connect directly to Data Studio (Data Portal), but by putting BigQuery in between, it is necessary to write SQL, but the range of reports that can be created expands. For example, one of the challenges we often encounter in advertising operations is reporting when multiple conversion points are set. Dividing by “conversion type” makes it difficult to report because the divided rows are not associated with indicators such as impressions, clicks, and costs.
Although the SQL is relatively complex, once you have data in BigQuery, you can generate complex reports.
What we want from other advertising platforms in the future
Current status of API usage
Various advertising platforms such as Yahoo Promotional Ads, Facebook Ads, and Criteo now offer APIs to automate advertising reporting. Also, some advertising platforms do not provide APIs.
When APIs are provided for the platform side, it is necessary to have server resources in order to process API requests that do not know when and how often they will occur. Of course, you can use cloud services to optimize your server resources. However, in order for users to use the API, it is necessary to prepare libraries and sample codes for various programming languages, as well as many documents. Due to such troubles, there are cases where small and medium-sized platforms do not provide APIs.
In order for users to use the API, it is necessary to read and decipher difficult documents and keep up with repeated API version upgrades. In the case of supporting six types of advertising platforms with an API, even if each platform is updated once every six months, the user side will be busy with the API version upgrade work of some platform every month. Also, in order to use the API, programmers with a certain level of skill are required.
What users really want
I think there are many cases where “Providing reports by API” is received as a function request to advertising platform providers that do not provide APIs. However, I believe that what users really want is not “additional API functions”.
What users actually want is not “providing an API that requires development on the user side,” but “a function that can automatically retrieve advertising report data from the advertising platform.” The problem is that at this point in time, the only way to achieve this is thought to be an “API” .
Alternatives to APIs
The function that users really want, “a function that can automatically retrieve advertising report data from the advertising platform” can be realized by methods other than API. One of them is the “Data Transfer Service” introduced in this article. The Data Transfer Service is the enabler in Google’s case.
Perhaps for other advertising platforms,
- CSV file (or JSON, XML, etc.) generated by the schema provided by the advertising platform
- Automatically on a daily (or weekly, monthly) basis
- Export to specified FTP server
A function like this would suffice. Regarding the schema, since a schema that can meet various needs is required, multiple files such as “advertising report”, “keyword report”, “search query report”, and “placement report” are generated, and dimensions and indicators are kept as much as possible. It is recommended to have everything full.
Of course, I wrote that the export destination is an FTP server, but I think it can be any database (FTP is not locked in by a third party vendor, so it should be easy for platformers to work on it. ). After exporting, you can easily process the data using some kind of ETL tool or analyze it directly with a BI tool without having to develop it by an engineer.
Cases where you really need an API
Among the APIs provided by advertising platforms, the most used ones are considered to be the reporting APIs (hereafter referred to as reference APIs). We estimate that reference APIs probably account for more than 90% of all API requests. As mentioned above, 90% of this should be handled by automatic export to FTP, etc., not by API.
The remaining 10% or less of all API requests are thought to be used not for reports but for updating settings on the management screen (hereafter referred to as update APIs). This includes adjusting budgets and unit prices, adding/updating/deleting keywords, ads, and targeting. These update APIs cannot be done with FTP, so APIs are essential. However, since the number of ad accounts that require the update API is only a small part of the total, in that case I think it would be fine to provide it for a fee.
Therefore, advertising platformers
- We do not provide a reference API, instead exporting to an FTP server and providing it to all clients and agents
- Provide only the update API for a fee to some clients and agencies
I think the structure is appropriate.
summary
In this article, we introduced the ” BigQuery Data Transfer Service for Google Ads ” , an advertising data export service provided by Google that is unknown to advertising managers and advertising agencies .
Such a very good mechanism would be meaningless if only Google Ads provided it, and it is a mechanism that we would like to introduce to other advertising platforms that are difficult to provide APIs with high availability. If you are interested in advertising platform Y, which is one step behind in terms of advertising APIs, or other DSP vendors, please consider this article.
