Using the Marketo Query component in Matillion ETL for Amazon Redshift
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 Amazon Redshift presents an easy-to-use graphical interface, enabling you to pull data from Marketo directly into Amazon Redshift. 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 Amazon Redshift.
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 Amazon Redshift. 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 Amazon Redshift.
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 on the support portal.
Running the The Marketo Query component in Matillion ETL for Amazon Redshift
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 Amazon Redshift. Additionally, specify a S3 Staging Area; this is a S3 bucket which will temporarily store the results of the query before loading it into Amazon Redshift.
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 alternatively by using the Scheduler, to query your data and bring it into Amazon Redshift.
Transforming the Data
Once the required data has been brought in from Marketo into Amazon Redshift, 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 Amazon Redshift’s power and scalability.
Want to try the Marketo Query component in Matillion ETL for Amazon Redshift? Arrange a free 1-hour training session now, or start a free 14-day trial.