Most modern businesses now rely on Digital Marketing Tools to market their products, automate the implementation of their marketing strategies, measure the performance of their efforts using marketing reports and manage their Leads.

One of the most well-known Marketing Tools is Marketo. Marketo offers various features such as Marketing Automation, Email Marketing, Revenue Attribution, and Lead Management. Although Marketo is widely used by a large number of businesses, its analytical capabilities are limited. This article will provide you with an in-depth understanding of how you can migrate your Leads data from Marketo to Redshift to perform a comprehensive analysis of it and derive useful insights from it.

Introduction to Marketo

Marketo Logo
Image Source:

Marketo is a Marketing software solution provided as a Web Service by Adobe. Marketo allows users to seamlessly perform Lead Management, Lead Generation, and all activities involved in converting a lead to a customer. Solutions like Marketo help companies concentrate on their core value offering rather than spending resources creating and managing softwares for Marketing purposes. Marketo also provides a customer experience management dashboard and capabilities for related analytics.

In spite of this, most companies typically export the data from Marketo to their own Data Warehouse systems for better flexibility in running analysis.
This blog explores all approaches so you can objectively evaluate the benefits and drawbacks of both approaches. If you wish to skip this part directly to other pages, use the links below to navigate.

Marketo also offers users access to its API in two forms, i.e., Lead APIs and Asset APIs. Lead APIs help users import, export, and manipulate the leads stored in Marketo. Asset APIs help users programmatically manage Marketing assets like images, videos, etc.

Marketo APIs are rate-limited with a maximum limit of 100 requests per 20 seconds for every instance. There is also a daily quota of 50000 requests per account. Concurrent requests are limited to a maximum of 10.
Marketo APIs are authenticated using a two-legged OAuth protocol. The first leg involves using the Client ID and Client-Secret key to get hold of an Access Token. This Access Token is to be used for requests until it expires. The token will be valid for 1 hour of use.

More information about Marketo can be found here.

Understanding the Key Features of Marketo

The key features of Marketo are as follows:

  • Lead Nurturing: Marketo gives businesses the ability to segment their leads by Target Industries, Marketing Personas, or the kind of interactions taking place. Based on the triggers that users set up, they can filter leads into Engagement Streams, or Drip Campaigns targeted precisely towards their needs and designed to keep the business top of mind. It also allows businesses to easily set transition triggers between streams so that their leads will always be in the right part of the funnel at any point in time.
  • Account-Based Marketing: Marketo’s Account-based Marketing allows businesses to name target accounts and roles within the company they wish to reach. Marketo Account-Based Marketing (ABM) tools let businesses build a smart list of characters describing the persona they wish to target, optimize their website experience for those leads, and engage them across channels, including Web, Email, Advertisements, etc.
  • SEO Tools:  Marketo houses numerous SEO tools that allow businesses to get insights into key SEO metrics such as the level of competition for keywords, pages that those keywords are ranked highest for, current Search Engine Results Page (SERP) rank, etc. Businesses can even get a list of suggested keywords for a specific page based on related target keywords or the page’s URL.

Introduction to Amazon Redshift

Amazon Redshift Logo
Image Source:

Amazon Redshift is a fully-managed petabyte-scale Cloud-based Data Warehouse, that was developed by Amazon. It was designed for the storage and analysis of petabyte-scale data. Amazon Redshift is built on a Column-oriented Architecture and designed to connect with numerous SQL-based clients, Business Intelligence, and Data Visualization tools and make data available to users in real-time. Based on PostgreSQL 8, Amazon Redshift delivers significantly enhanced performance and more efficient querying as compared to all other Data Warehouses. This helps teams make sound business analyses and decisions. More than 15,000 businesses now use Amazon Redshift globally, including large Enterprises such as Pfizer, McDonald’s, Facebook, etc.

More information on Amazon Redshift can be found here.

Understanding the Key Features of Amazon Redshift

The key features of Amazon Redshift are as follows:

  • Massively Parallel Processing (MPP): Massively Parallel Processing is a distributed design approach in which the divide and conquer strategy is applied by several processors on large data jobs. A large processing job is broken down into smaller jobs which are then distributed among a cluster of Compute Nodes. These Nodes perform their computations parallelly rather than sequentially. As a result, there is a considerable reduction in the amount of time Redshift requires to complete a single, massive job.
  • Fault Tolerance: Data Accessibility and Reliability are of paramount importance for any user of a database or a Data Warehouse. Amazon Redshift monitors its Clusters and Nodes around the clock. When any Node or Cluster fails, Amazon Redshift automatically replicates all data to healthy Nodes or Clusters.
  • Redshift ML: Amazon Redshift houses a functionality called Redshift ML that gives data analysts and database developers the ability to create, train, and deploy Amazon SageMaker models using SQL seamlessly.
  • Column-Oriented Design: Amazon Redshift is a Column-oriented Data Warehouse. This makes it a simple and cost-effective solution for businesses to analyze all their data using their existing Business Intelligence tools. Amazon Redshift achieves optimum query performance and efficient storage by leveraging Massively Parallel Processing (MPP), Columnar Data Storage, along efficient and targeted Data Compression Encoding schemes.
Methods to Set up Marketo to Redshift Migration

Method 1: Manual Marketo to Redshift Migration

This method involves manually extracting data from Marketo using its API and then loading it into Amazon Redshift to set up Marketo to Redshift Migration.

Method 2: Marketo to Redshift Migration Using Hevo Data

Hevo provides a hassle-free solution and helps you directly transfer data from Marketo to Redshift without any intervention in an effortless manner. Hevo is fully managed and completely automates the process of not only loading data from your desired source but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code.

Get Started with Hevo for Free

Hevo’s pre-built integration with Marketo and 100+ Sources (including 30+ free Data Sources) will take full charge of the data transfer process, allowing you to set up Marketo to Redshift Migration seamlessly and focus solely on key business activities. 

Methods to Set up Marketo to Redshift Migration

Users can set up Marketo to Redshift Migration by implementing one of the following methods:

Method 1: Manual Marketo to Redshift Migration

Here are the basic building blocks of implementing a custom code to transfer data from Marketo to Redshift:

Step 1: Creating Access Token for Marketo API

  • Login to your Marketo account and open the Admin page.
Marketo Admin Page
Image Source:
  • Open the Users & Roles section from the left panel and click on New Role.
Users and Roles Marketo
Image Source:
  • Give a suitable name and description for the role and expand the Access API section.
Marketo Access API
Image Source:
  • Scroll down and select Read-only Lead or any other access control option based on your requirements.
Marketo API Permissions
Image Source:
  • You will now need to create an API-only user and associate it with the API role that was created previously. Once the required information about the user has been created, click Invite.
Marketo API role user
Image Source:
  • A custom service would also be required to uniquely identify the client application. You can create a custom application by clicking on New Service in the Launchpoint section of Admin settings.
Marketo New Service
Image Source:
  • Provide a name, service, and description based on your requirements. For the API Only User, select the user that was created previously.
Marketo New API User
Image Source:
  • Now click on View Details next to the service that was created in the last step.
Marketo View User details
Image Source:
  • You will not be able to see your Client Id and Client Secret that can be used by your client applications or to generate an access token.
  • Click on GET TOKEN and copy the generated authentication string in a secure location.
Marketo Get Token
Image Source:
  • Copy this Token and save it in a secure location.

Step 2: Extract Leads Data using Marketo API

You can extract the Lead details from Marketo in JSON form using the Access Token generated previously by executing the following command:

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

The result will be a JSON structure as below.


Marketo can return a maximum of 300 results per request. If the results consist of more than this number of entries, the API response will contain two additional parameters, i.e., moreResults and nextPageToken. The latter can be used along with the next request to fetch the next batch.

Step 3: Transforming JSON Data to CSV

Unfortunately, Amazon Redshift does not have native support for JSON so, for simplicity’s sake, you can convert the JSON output to a CSV file. Luckily, there are a number of tools available that can make this possible. One of the most widely used tools can be accessed here.

The CSV data can then be copied and saved in a file in your local system.

Step 4: Loading the CSV Data to Redshift

Users can load their data exported from Marketo to Redshift by implementing the following steps:

Amazon S3 Create Bucket
  • Pick a suitable unique name for your AWS S3 Bucket, select a region as per requirement, and click on Create.
Amazon S3 Bucket Name
  • Open the AWS S3 Bucket that you just created, click on Create Folder, provide a suitable unique name for it, and click on Save.
Amazon S3 Bucket Folder
  • Upload the LinkedIn Ads data exported previously to the newly created folder by clicking on Upload selecting the necessary files in the Upload Wizard.
  • The data in Amazon S3 can be imported into Amazon Redshift Cluster using the COPY Command.
  • Connect to the Cluster using a SQL Workbench tool of your choice and run the following query:
COPY table_name 
FROM 's3://<your-bucket-name>/load/file_name.csv' 
credentials 'aws_access_key_id=<Your-Access-Key-ID>' 
  • If you wish to ignore the file header rows in the CSV files, then you may also specify that by running the following query:
COPY table_name 
FROM 's3://<your-bucket-name>/load/file_name.csv' 
credentials 'aws_access_key_id=<Your-Access-Key-ID>' 

Your data should now be accessible and queriable in your Amazon Redshift database.

Limitations of Setting up Manual Marketo to Redshift Migration

The limitations of setting up manual Marketo to Redshift Migration are as follows:

  • The Marketo API is paginated in case the result has more than 300 entries. So the application logic should be able to keep track of the nextPageToken parameter and dynamically forming the next request if required.
  • The API is rate limited with a daily quota, a 20-second quota, and a concurrency quota. The application logic should have provisions to work with these limits and handle exceptions in case the quota exceeds.
  • The manual approach is suited for a one-off load or a first-time load. In case of a real-time update or recurrent load operation, additional logic to handle deletes, updates, and duplicates must be implemented. The fact that Amazon Redshift does not enforce Primary Key constraints will make this even more difficult.
  • If not provided with configuration files to specify data type mapping, Amazon Redshift will leverage its automatic data type mapping, which can result in corrupted data. So a simple COPY command as above may not be sufficient depending upon the data types involved.
  • This approach needs access to an intermediate instance where the CSV and JSON files are stored before loading to Amazon S3 and Amazon Redshift. This is not always feasible for completely Software-as-a-Service (SaaS) based architectures.

Method 2: Marketo to Redshift Migration Using Hevo Data

Hevo Logo
Image Source: Self

Hevo helps you directly transfer data from Marketo and various other sources to Amazon Redshift, Business Intelligence tools, Data Warehouses, or a destination of your choice in a completely hassle-free & automated manner. Hevo is fully managed and completely automates the process of not only loading data from your desired source but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. Its fault-tolerant architecture ensures that the data is handled in a secure, consistent manner with zero data loss.

Sign up here for a 14-Day Free Trial!

Hevo takes care of all your data preprocessing needs required to set up Marketo to Redshift Migration and lets you focus on key business activities and draw a much powerful insight on how to generate more leads, retain customers, and take your business to new heights of profitability. It provides a consistent & reliable solution to manage data in real-time and always have analysis-ready data in your desired destination. 

The following steps can be implemented to set up Marketo to Redshift Migration using Hevo:

  • Configure Source: Connect Hevo Data with Marketo by providing a unique name for your Pipeline, along with details about your Marketo client such as the Client ID, Endpoint, etc.
Configure Marketo Hevo Source
Image Source:
  • Integrate Data: Complete Marketo to Redshift migration by providing information about your Amazon Redshift database and its credentials such as database name, username, and password, along with information about port number associated with your Amazon Redshift database. You’ll also need to need to provide the Schema name for your database, and its cluster, along with a unique name for your destination.
Configure Amazon Redshift Hevo Destination
Image Source:


This article provided you with a step-by-step guide on how you can set up Marketo to Redshift Migration manually or using Hevo. However, there are certain limitations associated with the manual method. If those limitations are not a concern to your operations, then using it is the best option but if it is, then you should consider using automated Data Integration platforms like Hevo.

Visit our Website to Explore Hevo

Hevo helps you directly transfer data from 100+ sources of your choice to a Data Warehouse, Business Intelligence, or desired destination in a fully automated and secure manner without having to write the code. It will make your life easier and make data migration hassle-free. It is User-Friendly, Reliable, and Secure.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. Details on Hevo’s pricing can be found here.

No-code Data Pipeline For Redshift