How to Connect Redshift to Marketo?: 2 Easy Methods

on Amazon Redshift, Data Warehouses, Hevo Activate, Marketing Automation, Marketo • June 27th, 2022 • Write for Hevo

RedShift to Marketo_FI

In today’s digital environment, everything is accessible and managed online. Due to the volume of data produced by this process, it is difficult to perform real-time analysis, develop insights, identify fraud and anomalies, alert users, and perform other duties.

Marketo is a marketing automation platform that improves lead and customer engagement for your team by fusing behavioral data with built-in intelligence. These computerized marketing techniques can help companies convert consumer satisfaction into sales.

AWS (Amazon Web Services) offers Amazon Redshift, a petabyte-scale, fully-managed data warehouse, to assist businesses in storing and analyzing their data. Fast response times for data queries distinguish Amazon Redshift Data Warehouse. To swiftly provide query replies, it makes use of methods like Columnar Data Storage and Massive Parallel Processing (MPP).

This blog post aims to discuss Marketing Communications platform, Marketo and a Cloud-hosted data warehouse, Redshift. You will also know how you can connect Redshift to Marketo.

Table of Contents

What is Redshift?

RedShift to Marketo: RedShift logo
Image Source

A well-liked, completely scalable, and dependable data warehouse, Amazon Redshift was created in 2012. With a collection of nodes known as Amazon clusters, businesses can begin using Amazon Redshift. Redshift Console or the Amazon Command Line Interface can be used to manage Amazon clusters. A column-oriented database included in Amazon Redshift enables businesses to quickly analyze data by connecting SQL-based clients such as BI tools. Organizations don’t have to worry about administrative duties like Memory Management, Resource Allocation, Configuration Management, and more because Amazon Redshift is a fully scalable data warehouse.

Organizations can use the AWS Software Development Kit or Amazon Redshift Query API to programmatically manage clusters using Amazon Redshift.

Key Features of Redshift

  • Strong Security: Users can secure data warehouses using Amazon Redshift at no extra cost. Users of Amazon Redshift can set up firewalls to regulate network access to a particular cluster of data warehouses. To make sure that only people with authorized access may view the data, users of Amazon Redshift can train the column- and row-level security control.
  • Result Caching: For repeated queries, Amazon Redshift’s result caching functionality can provide a sub-second response time. In Amazon Redshift, a query can search the cache to get any search results from earlier queries.
  • Quick Performance: Thanks to its distinctive features including massively parallel processing, columnar data storage, result caching, query optimizer, data compression, generated code, and more, Amazon Redshift provides fast performance.
  • ANSI-SQL: You can utilize your current SQL clients and business intelligence tools since Amazon Redshift is based on ANSI-SQL, which employs industry-standard ODBC and JDBC connections. With ANSI-SQL, you can easily query files like CSV, JSON, ORC, Avro, Parquet, and more.
  • AQUA (Advanced Query Accelerator): AQUA is a distributed, hardware-accelerated cache that is part of Amazon Redshift. Compared to competing enterprise cloud data warehouses, Amazon Redshift is 10 times faster.

What is Marketo?

RedShift to Marketo: Marketo logo
Image Source

For tracking and managing marketing processes, Marketo is a well-liked SaaS tool. It offers a variety of services relating to marketing. Marketo is a global leader in business solutions that put scale and sophisticated procedures first. It is constructed using a modular design, with each module sold and utilized independently. To offer a thorough marketing automation experience, these modules can be utilized singly or in bundles. Email Marketing, Lead Management, Revenue Attribution, Customer-based Marketing, Account-based Marketing, etc. are some of the modules offered by Marketo.

Customer Relationship Management (CRM) is a feature that Marketo does not have on its own, but it can interact with through Salesforce, Microsoft Dynamics, and SAP, among other well-known CRMs. Marketo’s tools make it possible to automate several phases of digital advertising across a variety of channels, including email, the web, mobile devices, and others. As a result, leads can be found, developed, and watched in real time.

Key Features of Marketo

Marketo provides a range of services that can assist businesses and individuals in increasing revenue and customer satisfaction. Marketo features a lot of noteworthy traits, such as:

  • Account-Based Marketing (ABM): This technology enables businesses to identify the personas they want to target, develop a smart list of characters to describe those personas, enhance those Leads’ website experiences, and engage those Leads via multiple channels, including emails, web ads, and websites.
  • Tools for SEO: One of the most crucial elements of successful marketing is search engine optimization. To assist you in raising your marketing standards, Marketo incorporates a number of SEO tools in one location.
  • A/B Testing: Marketo provides services like email testing that assist you in choosing the kind of content to put in your emails and the best time to send them in order to boost conversion rates.
  • Lead Nurturing: Marketo gives organizations the ability to divide their leads into groups based on Marketing Personas, Target Industries, or the nature of their interactions. Additionally, it enables you to strengthen ties with customers and increase conversion rates.
  • Engagement Engine: You may conduct research, gain understanding of your consumers’ behavior, and develop adaptable, sector-specific email campaigns for them.

Benefits of Connecting Redshift to Marketo

Marketo is a marketing automation tool that combines behavioral data and in-built intelligence to improve lead and customer engagement for your team. These automated marketing strategies can assist businesses in turning customer satisfaction into sales.

  • You can quickly sync all of the vital information about your prospective leads by integrating Redshift to Marketo. You may instantly combine your contacts with more specific data on your leads and prospects, which will help to boost the effectiveness of your marketing activities. 
  • Using the information from your leads, you can quickly run SQL queries in Redshift and feed the results to the information of your Marketo subscribers. In addition to saving you time, doing this will greatly increase your productivity.
  • All of the data for your organization will be saved in one place since Redshift is your Single Source of Truth. Additionally, you don’t need to create brand-new databases or tables each time you wish to input Marketo data. Instead, you can run queries on the aggregated data you’ve saved in Redshift to remove the data that isn’t actually necessary. Also, Marketo can be used to load this data.

Ways to Connect Redshift to Marketo

Method 1: Connect Redshift to Marketo using Hevo Activate

Hevo Activate provides a hassle-free solution and helps you directly transfer data from Amazon Redshift, Snowflake, Google BigQuery, Facebook, etc., to Marketo, Intercom, Google Sheets, Salesforce, HubSpot, Zendesk, etc without any intervention in an effortless manner. Hevo Activate 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.

Hevo’s pre-built integration with Redshift and CRM Sources will take full charge of the data transfer process, allowing you to focus on key business activities. Hevo also provides Marketo and Redshift as a Destination for seamlessly loading data into it.

Get Started with Hevo for Free

Method 2: Connect Redshift to Marketo Manually Using CSV Files 

This method would be time-consuming and somewhat tedious to implement. You will have to first manually export data from RedShift in CSV format and then again import that data into Marketo using CSV files.

Methods to Connect Redshift to Marketo

Method 1: Connect Redshift to Marketo using Hevo Activate

Redshift to Marketo
Source: Self

Hevo Activate helps you directly transfer data from Redshift, Snowflake, etc., and various other sources to CRMs such as Marketo, Intercom, HubSpot, various SaaS applications, and a lot more for free, in a completely hassle-free & automated manner.

Hevo Activate handles all of your data preparation requirements, allowing you to focus on essential business operations and get a far more powerful understanding of how to create more leads, keep customers, and push your firm to new heights of profitability.  It offers a consistent solution for managing data in real-time and ensuring that you always have analysis-ready data at your preferred destination.

Take a look at what makes Hevo Activate so special:

Get Started with Hevo for Free
  • Real-time Data Sync: With its excellent integration with multiple sources, Hevo Activate enables you to transfer data fast and efficiently. This guarantees that bandwidth is used efficiently on both ends.
  • Data Transformation: It gives a straightforward interface for perfecting, changing, and enriching the data you wish to transmit.
  • Secure: Hevo Activate features a fault-tolerant design that ensures data is handled securely and consistently, with no data loss.
  • Massive Connector Availability: Hevo Activate has a huge number of connections that allow you to bring data from various Data Warehouses and load it into Marketing & SaaS apps like Salesforce, HubSpot, Zendesk, Intercom, and others in an integrated and analysis-ready format.
  • Simplicity: Using Hevo Activate is simple and straightforward, guaranteeing that your data is exported in a matter of seconds.
  • Completely Managed Platform: Hevo Activate is a fully managed platform. You do not need to devote time or effort to maintaining or monitoring the infrastructure required in code execution.
  • Live Assistance: The Hevo Activate team is accessible 24 hours a day, 7 days a week to provide outstanding support to its clients via chat, email, and support calls.

The steps to Connect Marketo to Redshift using Hevo Activate: 

Step 1: Configure Redshift as your Warehouse

  • Step 1.1: In the Asset Palette, click ACTIVATE, and then:
    • Select + CREATE ACTIVATION in the ACTIVATIONS tab.
Marketo to RedShift: hevo activate activations
Image Source
  • Click + ADD WAREHOUSE on the Select Warehouse page.
Marketo to RedShift: hevo activate select warehouse
Image Source
  • Choose Redshift from the drop-down menu on the Select Warehouse Type page.
  • Step 1.2: In the Asset Palette, click DESTINATIONS, and then:
    • In the Destinations List View, click + CREATE.
    • Select Redshift as the destination type on the Add Destination page.
  • Step 1.3: Set the following parameters on the Configure your Redshift Warehouse page: Warehouse Name, Account Name, Region, Database User, Database Password, Database Name, Schema Name and Warehouse.
RedShift to Marketo: hevo activate configure your redshift warehouse
Image Source
  • Step 1.4: To test connectivity with the Redshift warehouse, click TEST CONNECTION.
  • Step 1.5: Click SAVE & CONTINUE when the test is completed successfully.

Step 2: Configure Marketo as your Target Destination

To set Marketo as your Target destination, you can follow the simple steps given below:

  • Step 1.1: Click Activate in the Asset Palette.
  • Step 1.2: Do one of the following:
Marketo to RedShift: Create Target
Image Source
Marketo to RedShift: Create Activation
Image Source
  1. In the Select Warehouse page, select your Activate Warehouse or click + ADD WAREHOUSE to add a new warehouse. Read Activate Warehouses to configure the selected Warehouse type.
  2. In the Select a Target page, click + ADD TARGET.
RedShift to Marketo: Select a Target
Image Source
  • Step 1.3: In the Select a Target Type page, click on Marketo.
RedShift to Marketo: Select a Target Type
Image Source
  • Step 1.4: n the Configure your Marketo Target page, specify the following information:
    • Target Name: A unique name for your Target.
    • Client ID: The ID generated by your custom service.
    • Client Secret: The secret key generated by your custom service.
    • Sub Domain: Your Marketo sub-domain. Extract this from the base URL, which Hevo uses to make all the API calls. For example, if the base URL is https://064-CCO-728.mktorest.com/rest, then your sub-domain is 064-CCO-728.
Marketo to RedShift: Configure Marketo Source
Image Source
  • Step 1.5: Click TEST & CONTINUE. You can view the new Target in the Targets List View. If you are creating an Activation, you return to the Select Data to Synchronize page. To finish creating the Activation, you must map the Warehouse fields to the Target fields. Refer to section, Mapping Fields in Marketo.
Marketo to RedShift: Test
Image Source

Method 2: Connect Redshift to Marketo Manually using CSV Files

You cannot directly export the data from Redshift to Marketo. To export data from Redshift to Marketo, first you will have to export data from Redshift as CSV files and then load the CSV files into Marketo.

Step 1: Export Data from Redshift as CSV

The first step in exporting data from Redshift to Marketo is exporting data from Redshift as CSV files. There are different ways to do so:

1) Using UNLOAD Command

With some rather straightforward SQL, you can rapidly export your data from Redshift to CSV. The editor button can be found in the menu on the left after logging into the Redshift interface. To connect to a database, hover over it and go to the query editor. You can begin running SQL queries once you are connected. Your data can be exported using the following simple syntax.

You query the data you want to export on the first line. Redshift only permits a LIMIT clause in an inner SELECT statement, thus be aware of that.

UNLOAD ('SELECT * FROM your_table')
TO 's3://object-path/name-prefix'
IAM_ROLE 'arn:aws:iam::<aws-account-id>:role/<role-name>'
CSV;

The TO clause, which is seen in the second line, is where you specify the target S3 bucket path. To be able to perform the query, you must have write permission. 

One of various ways you can approve is on the third line, which is your authorization. If you choose to use the above approach, you may access the support center by clicking on your account name in the navigation bar and finding your 12-digit account ID there.

The final instruction in the fourth line instructs Redshift to save your data as CSV, which is not the default.

You can modify the aforementioned query to suit your needs by including a variety of other options. There are a few in particular that can be helpful:

  • HEADER: This increases the number of columns in your output file’s top row (s). In almost every situation, you should take this action.
  • USE DELIMITER AS “character”: A comma is the standard character for CSV files. Commas in your data could provide surprising consequences. You could, for example, use a pipe (|) in this situation.
  • ADDQUOTES: Another technique to ensure that commas in your data don’t provide unexpected results is to add quotes around every field by doing this.
  • ZSTD, GZIP, or BZIP2: Your file’s size will be greatly reduced by using one of these compression choices, making it easier to email or download.
2) Using AWS SDK

One of AWS’s SDKs, which come in a variety of computer languages like JavaScript, Python, Node.js, and Ruby, allows you to communicate with the service through programming. With the aid of an SDK, you may execute SQL queries to store data in a variable within your code, which you can then save as a CSV file.

You’ll use Python in this illustration. The example should be sufficient to help you understand how you can approach this even if you plan to use a different language. You must install boto3, which is the AWS SDK for Python.

pip install boto3

Install the library, import it, then declare a client. When signed into the Redshift console, you can identify the region at the beginning of the URL, which you should use for region name because that is where your resources are already located. Using Amazon’s Redshift V2 as an example: https://us-east-2.console.aws.com/home.

import boto3

client = boto3.client('redshift-data', region_name='us-east-2', 
            aws_access_key_id='your-public-key', aws_secret_access_key='your-secret-key')

You are now prepared to run a query to obtain your data.

response = client.execute_statement(
    ClusterIdentifier='your-cluster',
    Database='your-database',
    DbUser='your-user',
    Sql='SELECT * FROM users;' # Insert your SQL query here
)

A dictionary containing details about the most recent request you made is the response. The universally unique ID of your query, Id, is one of the keys.

{'ClusterIdentifier': 'your-cluster',
 'CreatedAt': datetime.datetime(2021, 9, 9, 21, 29, 29, 521000, tzinfo=tzlocal()),
 'Database': 'your-database',
 'DbUser': 'your-user',
 'Id': 'query-id', # You'll need this
 'ResponseMetadata': {'RequestId': '4af####-########',
 'HTTPStatusCode': 200,
 'HTTPHeaders': {'x-amzn-requestid': '4af####-#######',
 'content-type': 'application/x-amz-json-1.1',
 'content-length': '150',
 'date': 'Thu, 09 Sep 2021 19:29:29 GMT'},
 'RetryAttempts': 0}}
{'ClusterIdentifier': 'your-cluster',
 'CreatedAt': datetime.datetime(2021, 9, 9, 21, 29, 29, 521000, tzinfo=tzlocal()),
 'Database': 'your-database',
 'DbUser': 'your-user',
 'Id': 'query-id', # You'll need this
 'ResponseMetadata': {'RequestId': '4af####-########',
 'HTTPStatusCode': 200,
 'HTTPHeaders': {'x-amzn-requestid': '4af####-#######',
 'content-type': 'application/x-amz-json-1.1',
 'content-length': '150',
 'date': 'Thu, 09 Sep 2021 19:29:29 GMT'},
 'RetryAttempts': 0}}
{'ClusterIdentifier': 'your-cluster',
 'CreatedAt': datetime.datetime(2021, 9, 9, 21, 29, 29, 521000, tzinfo=tzlocal()),
 'Database': 'your-database',
 'DbUser': 'your-user',
 'Id': 'query-id', # You'll need this
 'ResponseMetadata': {'RequestId': '4af####-########',
 'HTTPStatusCode': 200,
 'HTTPHeaders': {'x-amzn-requestid': '4af####-#######',
 'content-type': 'application/x-amz-json-1.1',
 'content-length': '150',
 'date': 'Thu, 09 Sep 2021 19:29:29 GMT'},
 'RetryAttempts': 0}}

query_id = response['Id']

To examine the current status of your query, enter this query ID into the description_statement. If the key Status doesn’t yet equal FINISHED, there is no use in attempting to access your data.

print(client.describe_statement(Id=query_id)['Status'])

Get the information using get_statement_result and save it to a variable after the status shows that your query is complete.

data = client.get_statement_result(Id=query_id)

To ensure that none of your data is missing, always double-check the NextToken key. Your data has been paginated and more has to be retrieved if there is a NextToken.

next_token = data['NextToken']
more_data = client.get_statement_result(Id=query_id, NextToken=next_token)

You might want to create a for loop to iterate through your data and append it all to one variable, depending on how much data you queried. Assuming you now have all of your data, you’ll discover that it is presented in a highly awkward style. Convenient here refers to the fact that you can’t immediately convert it into a pandas DataFrame, at least not one that makes sense. Here is a function to assist you with that.

def redshift_to_dataframe(data):
    df_labels = []
    for i in data['ColumnMetadata']:
        df_labels.append(i['label'])
    df_data = []
    for i in data['Records']:
        object_data = []
        for j in i:
            object_data.append(list(j.values())[0])
        df_data.append(object_data)
        df = pd.DataFrame(columns=df_labels, data=df_data)
    return df

That handles the difficult lifting for you. The function to generate a DataFrame and save it to CSV must now be called.

df = redshift_to_dataframe(data)
df.to_csv('your-file.csv')

After that, you have a CSV with a great layout that you can utilize for your use case.

3) Using SQL Client

Finally, you can connect to Redshift on your local PC using a SQL client. Only choose this route if you want to query Redshift more frequently because it may take some time to set up your tool and connection. There are many programs available that can handle the job, such as MySQL Workbench. The precise buttons to press and directions to type will change depending on the tool, but the steps are always the same.

  • Connect to your desired database.
  • Query Data.
  • Exported the Output as CSV.

However, the above methods are quicker if you only sometimes need to export a CSV file from Redshift.

The first step in transferring data from RedShift to Marketo is to export the data as a CSV file.

Step 2: Load CSV Data into Marketo

The second step in exporting data from RedShift to Marketo is importing CSV data into Marketo. 

  • Step A: A list of individuals who sign up for a programme automatically can be imported. Here is what to do. In Spreadsheet, create a typical CSV file similar to the one shown below.
Image Source
  • Step B: Navigate to the Members area of your programme. Click Import Members. Click Next after selecting the CSV.
Image Source
  • Step C: Click Next after mapping the data values from the list to the appropriate Marketo Fields. For your list, select the Member Status as Visited or Engaged and click import.
Image Source
  • Step D: Close the confirmation dialogue after Marketo has finished importing. The new members you imported ought to be visible.
Image Source

Limitations of Connecting Redshift to Marketo Manually

  • Data can only be sent from Redshift to Marketo in one method. Two-way sync is necessary to keep both tools current.
  • Because the records need to be updated frequently, the manual procedure takes time. Time and resources that could be spent for more important business tasks are being wasted on this.
  • It can be annoying for some consumers when engineering bandwidth is required to maintain workflows across many platforms and update current data.
  • Data transfer does not allow for any transformation. Businesses that want to modify their data before transferring it from Redshift to Marketo may find this to be a big disadvantage.

Conclusion  

In this article, you have learned how to effectively connect Redshift to Marketo using 2 different methods. The second method uses the CSV Files to connect Redshift to Marketo. 

However, creating & managing multiple connections between Redshift, Marketo and all the applications in your business is a time-consuming & resource-intensive task. Using the first method, you can opt for a more economical & effortless approach by automating your workflow via a Cloud-Based Reverse ETL Tool like Hevo Activate

visit our website to explore hevo

Hevo Activate helps you directly transfer data from a source of your choice such as Amazon Redshift, Snowflake, Google BigQuery, Facebook, etc., to any SaaS application like Marketo, Google Sheets, Intercom, etc., in a fully automated and secure manner without having to write the code repeatedly. It will make your life easier and make data migration hassle-free. It is user-friendly, reliable, and secure.  

Hevo, with its strong integration with CRM Tools like Intercom, HubSpot, Salesforce, allows you to not only export & load data but also transform & enrich your data & make it analysis-ready in a jiffy.

Want to take Hevo for a ride? sign up for a 14-day free trial and simplify your Data Integration process. Do check out the pricing details to understand which plan fulfills all your business needs.

Tell us about your experience of connecting Redshift to Marketo! Share your thoughts with us in the comments section below.

No-code Data Pipeline for RedShift