Using the Marketo Query component in Matillion ETL for BigQuery
Matillion uses the Extract-Load-Transform (ELT) approach to delivering quick results for a wide range of data processing purposes: everything from customer behaviour analytics, financial analysis, and even reducing the cost of synthesising DNA.
The Marketo Query component in Matillion ETL for BigQuery presents an easy-to-use graphical interface, enabling you to pull data from Marketo directly into BigQuery. As a result, many of our customers are using this to combine Marketo data with Sales data to measure the effectiveness of their marketing.
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 features.
Watch our tutorial video for a demonstration on how to set up and use the Marketo Query component in Matillion ETL for BigQuery.
The first step in configuring the Marketo Query component is to authenticate Matillion to access the Marketo data. This is done by configuring OAuth. Further details on setting up OAuth are available on the support portal. Following OAuth configuration, select your Marketo instance from the dropdown in the component:
This is a URL giving the REST API Endpoint for your Marketo data. Matillion will then use this to connect into and pull the data. This is available in the Marketo Admin app under Web Services.
Next, use the Data Source drop down to choose what data to load into BigQuery. This is a list of all objects in your Marketo Organization:
After choosing the data source, the next step is to choose the required fields from the data source in the Data Selection. This is a list of all fields in the Data Selection. Consequently, the selected fields will form the new table created in BigQuery.
If required, add a Data Source Filter to filter the returned records. The most common use case to filter data is to filter by date to support incremental updates.
Connection options are additional parameters supported by the driver. The Marketo driver usually provides sensible defaults and therefore does not mandate the configuration of Connection Options. However, further details on Connection Options are available here.
Running the Marketo Query component in Matillion ETL for BigQuery
Before you run the component, give the Target Table a name. This is subsequently the name of the new table created to write the data into BigQuery. Also a Staging Area must be specified. This is a Cloud Storage bucket which is used to temporarily store the results of the query before loading it into BigQuery.
Force an upper limit on the number of records returned using the Limit property feature that comes with the component. We recommend using either a limit or a filter to reduce the number of rows returned to improve the speed of your job.
You can run the Orchestration job, either manually or using the Scheduler, to query your data and bring it into BigQuery.
Transforming the Data
Once the required data has been brought into BigQuery from Marketo, it can then be used in a Transformation job. A noteworthy transformation is to enhance existing data:
In this way, you can build out the rest of your downstream transformations and analysis, taking advantage of BigQuery’s power and scalability.