Using the Google AdWords Query Component in Matillion ETL for Google BigQuery

Matillion uses the Extract-Load-Transform (ELT) approach to use the power of your MPP database/platform to power your data-transformation. This helps us deliver quick results for a wide range of data processing purposes.

The Google AdWords Query component in Matillion ETL for Google BigQuery presents an easy-to-use graphical interface, enabling you to pull data from a Google AdWords account (yours or of a 3rd party) directly into your Data Warehouse. Many of our customers are using this component to extract data from one or more Google AdWords account into their data warehouse to analyze their Spend, Performance of Campaigns/Ads and where appropriate, even compare with their investments on other marketing platforms.

The connector is completely self-contained: no additional software installation is required. It’s within the scope of an ordinary Matillion license, so there is no additional cost for using the component.

Video

Watch our tutorial video for a demonstration on how to set up and use the Google Adwords Component with Matillion ETL for Google BigQuery.

Data Extraction

The image above represents the data extraction workflow for the component.

1. First, Matillion sends appropriate requests to the Google AdWords service.
2. Then Google AdWords returns relevant data to Matillion.
3. Next Matillion streams the data as it arrives to Google Cloud Storage Bucket. Please note, the data is not persisted to disk.
4. Once all data is in Cloud-Storage, Matillion issues a COPY command to Google BigQuery; passing the names of the files and other relevant metadata.
5. Lastly, Google BigQuery accesses the data and loads it into appropriate target tables.

Matillion automatically deletes any files it has created in Google Cloud Storage irrespective of the result of the operation – success/failure.

Prerequisites

1. A Google Account with access to AdWords data. Google also requires that you authenticate via a Google account to access AdWords related information. You may not own the data in which case your client/customer needs to allow your Google account access to their AdWords related data.
2. A Developer Token that has been authorised for use with production data. Please contact your Google support representative if you need help with your developer token – see FAQ.
3. A Customer ID for the AdWords account.
4. ClientID and Secret key from https://console.developers.google.com. Please see next section on Authentication

Authentication

Matillion supports OAuth to authenticate with Google services. Please see the following link for more information on OAuth registration with Matillion – Setting up OAuth in Matillion. The process involves accessing the Google developer console where you will generate a ClientID and Secret Key so it can then be used in Matillion for OAuth registration.

Additionally, if required, you may register multiple Google accounts to access data for various google services these accounts may have access to.

On registering for OAuth, you may use this with other Google related components like Google Analytics, Google AdWords, Google Sheets, etc. The component has an Authentication property which lists Google OAuth registrations. Choose the relevant OAuth registration to work with data from that account.

Configuring the component

To start configuring the component first create/ppen an orchestration job and add a Google AdWords Component to the job panel. Next you will need to set relevant properties; and once green, test the component.

Data Source

Google AdWords component models Google AdWords entities as a relational table. The Data Source property presents a list of tables that will give you access to your Google AdWords report data using the Google AdWords Reporting API. Choose the relevant table from the dropdown.

Please read the data model page for information on available tables and the relevant columns.

Data Selections

This property presents you with a list of metrics, segments, and attributes that are available for that table. Select the ones that suit your requirement.

Google AdWords, notably, doesn’t allow you to select every column in a single query because some data will conflict if selected together. Therefore, in the event that you select all columns, only the default metrics, segments, and attributes will be returned. In general, these defaults are the same fields that the AdWords console exposes.

Data Source Filter

You may specify some Filters here. By default, All filters are joined by the AND keyword, as OR is not supported by the AdWords API.

Filtering for a Date Range

By default, Google AdWords component returns data for the last 7 days. You may alternatively specify an explicit range using the StartDate and EndDate PseudoColumns.

By default, however, StartDate and EndDate, may be hidden. Please enable these by adding a Connection Option PseudoColumns with value *=*. See here for more information on this connection option.

Basic/Advanced Mode

Basic mode is the default mode and allows a user to choose the data source and columns using a point and click interface.

In contrast, Advanced mode hides the Data Source and Data Selection options and presents a SQL property where the user can write simple SELECT statements to select relevant data. Some examples are below:
> Return all columns:

SELECT * FROM CampaignPerformance

> Rename a column:

SELECT [Device] AS MY_Device FROM CampaignPerformance

> Search data:

SELECT * FROM CampaignPerformance WHERE Device = 'Mobile devices with full browsers'

> Restrict a result set to the specified number of rows:

SELECT Clicks, Device FROM CampaignPerformance LIMIT 10

> Specify a date range

SELECT Clicks, Device FROM CampaignPerformance WHERE StartDate='2018-01-01' and EndDate='2018-02-01'

Running the Google AdWords Query component in Matillion ETL for Google BigQuery

Once you have configured the minimum required properties (Source – Google AdWords, Target – Google BigQuery) the component changes to Green at which point you may test the component by Right-Clicking on it and choose Run Component. This will execute just that component and load any available data into the specified target table in Google BigQuery.

Transforming the Data

Once your data is in Google BigQuery, you may also use a transformation job to embellish the data by applying various transforms which may include adding additional columns (load date?) or even combining it with other datasets to derive useful information.

Conclusion

I hope that gave you a good overview of the Google AdWords Query component in Matillion ETL for Google BigQuery. If you need further help, please refer to the links below or contact us at [email protected]

Useful Links

Google AdWords Query – Component documentation
Component Data Model
Setting up OAuth in Matillion
Google AdWords FAQ
Video

Find out more about how you can Optimize Google BigQuery in our eBook.