How to Connect Redshift and Marketo

Product-led growth (PLG) businesses deal with tons of data coming in from siloed sources every day. Data warehouses are the ideal places to centralize all that data for the purposes of storing, managing, analyzing, and operationalizing the data as they scale.

Amazon Redshift is one of the most popular data warehouses. More than 14,000 businesses use Redshift to get actionable insights from their data. Using the data for launching automated marketing campaigns is at top of the list of use cases powered by Redshift. And, Marketo is one such highly-rated marketing automation platform used by large enterprises.

So, Redshift users need to establish a custom data integration with Marketo to exchange data that powers the marketing campaigns. There are two ways to do that.

Manual Migration Setup

Transferring data from Marketo to Redshift can be set up in 4 steps.

1. First, generate an access token for Marketo API

• Go to the Admin page in your Marketo account.
• Go to User & Roles on the left panel and select New Role. Name the role and add a description.

• Expand the Access API section and scroll down to select the Read-only Lead option.

• Create an API-only user by checking the API Only box when creating the new user and connecting it with the API role created in the earlier step. Click Invite when done.

• You next need a custom service for the purpose of identifying a client application uniquely. For this, go to Admin settings> Launchpoint and click New Service.

• Enter the name, service, and description. Select the API-only user you created earlier in the API Only User field.
• Once you’re done creating the new service, click View Details to see the Client Id and Client Secret, and the token.

2. Copy the token and use it to extract lead details from Marketo in JSON format using the following command:

curl -o marketo_leads.json <Identity URL>/rest/v1/leads.json?access_token=<Access Token>

Note: Marketo has a return limit of 300 results per request. If the results are more than 300, use the nextPageToken parameter to fetch the next batch of results.

3. Convert the JSON data to CSV format using any free tool of your choice. Copy and save the CSV file locally.

Note: This is a necessary step because unfortunately, Redshift does not have native support for JSON.

4. Transfer the CSV data exported from Marketo to Redshift.

• Log in to the AWS Management Console and open Amazon S3 Console and click Create Bucket.

• Choose a unique name for the AWS S3 Bucket, choose a region, and click Create.

• Open the AWS S3 Bucket again and click Create Folder, choose a unique name, and Save.

• Click Upload and select the files to transfer into the newly created folder.

• Use the COPY command to get the data into Amazon Redshift Cluster from Amazon S3.

• Use any SQL Workbench tool and run this query:
  
  COPY table_name
  FROM 's3://<your-bucket-name>/load/file_name.csv'
  credentials 'aws_access_key_id=<Your-Access-Key-ID>'
  CSV;

Now you can access and query from Redshift.

Via Third-Party Tools

You can also use any of the popular ETL/Reverse ETL or automation tools like Zapier to connect Redshift and Marketo.

A major drawback to these solutions is that you need to set up mapping for every field individually, including the API sync. This is not practical when you have hundreds of events. Additionally, the product events are likely to be severely delayed (sometimes as much as 24 hours), causing issues for creating campaigns that need to happen in the moment - which is most campaigns that drive users to more usage and revenue!

Before Connecting Redshift and Marketo

There sure are different ways to connect Amazon Redshift to Marketo to run marketing automation campaigns powered by your data warehouse. But, before you commit to that integrations, here are a few things you ought to know:

1. The manual approach is only good for a one-off first-time upload. You need additional logic and possibly dev resources to implement real-time updates.

2. As mentioned in the manual process of establishing the sync, Marketo API paginates results if they are more than 300. So, the app logic should be able to use nextPageToken parameter dynamically when necessary.

3. The API quota of Marketo is a nagging or very (very) expensive problem for PLG use cases. Marketo has an API instance limit of 100 calls per 20 seconds and a concurrent API call limit of 10 maximum on top of the daily quota (depending on pricing tier).

4. Redshift does not implement Primary Key Constraints - the mechanism to prevent duplicates. This along with the batch API processing due to Marketo’s limitations means data duplication. Which again costs a lot when using Marketo.

5. To get the data into Redshift, the COPY command described in the manual process might not hold well depending on the data type. Unless specifically mapped, Redshift uses automatic data type mapping, which might result in corrupted data.

Apart from the common limitations like the volume of API calls and duplication, choosing Amazon AppFlow or any third-party tools for the ease of the process and real-time data exchange comes with a price and some complications of their own.

Redshift and Inflection for PLG

Inflection is the first B2B growth automation platform purpose-built for product-led growth businesses. Inflection’s native integration to Redshift, means your data warehouse can be the single source of truth for all your customer communications.

Inflection combines product activity and CRM data to show a unified view of your customer. You can assess the intent signals better with a wholesome understanding of user behavior in-product combined with the touch points with your company outside the product. A potent combination of product events and firmographic data means you can send the right personalized PLG emails at the right time.

Being purpose-built for PLG, Inflection supports a huge volume of product events typical for a product-led company with a low entry bar to their users. And, our usage-based pricing is designed to be PLG-friendly.

See Inflection in action.