Redshift to Intercom Data Replication: 2 Easy Methods

• June 22nd, 2022

redshift to intercom: FI

AWS Redshift is an Amazon Web Services Data Warehouse service. It’s commonly used for large-scale data storage and analysis, as well as large database migrations.

Intercom provides several products for live chat, marketing, feedback, and support. By using tags, Intercom also enables the grouping of customers (users and businesses), as well as conversations between customers and support teams.

This article talks about the different ways to Connect Redshift to Intercom. In addition to that, it explains Redshift and Intercom briefly.

Table of Contents

What is Amazon Redshift?

redshift to intercom: redshift logo
Image Source

AWS Redshift is Amazon Web Services’ solution for data warehousing. The service, like many others provided by AWS, can be set up in a matter of minutes and offers a variety of import options.

You can extract useful information from a large amount of data using Redshift. AWS provides a simple interface for creating clusters automatically, removing the need for infrastructure management.

For storing and analyzing large data sets, Amazon Redshift is a fully managed petabyte-scale cloud data warehouse. One of Amazon Redshift’s key advantages is its ability to handle large amounts of data – it can process unstructured and structured data up to exabytes.

To know more about AWS Redshift, follow the official documentation here.

Key Features Of Amazon Redshift

  • The Advanced Query Accelerator (AQUA) in Amazon Redshift speeds up queries 10 times faster than other cloud data warehouses.
  • For ETL, batch job processing, and dashboarding, Amazon Redshift’s Materialistic view allows you to achieve faster query performance.
  • Amazon Redshift’s architecture scales up to petabytes and scales down quickly as needed.
  • Amazon Redshift allows for data securely sharing between Amazon Redshift clusters.
  • Amazon Redshift consistently delivers fast results, even when thousands of queries are running at the same time.
  • With the help of ANSI SQL, Amazon Redshift can directly query files such as CSV, Avro, Parquet, JSON, and ORC.

What is Intercom?

redshift to intercom: intercom logo
Image Source

Through conversational, messenger-based experiences throughout the customer journey, Intercom assists you in developing customer relationships.

Every customer interaction is an opportunity to establish a rapport and win a devoted following. Each interaction with a customer through Our Messenger feels warm, convenient, and friendly, resulting in satisfied, devoted clients and sky-high business growth.

Intercom offers next-generation solutions for sales, marketing, and support teams to work together to convert prospects into paying customers, engage customers throughout their journey, and provide world-class support with its all-in-one customer communications platform.

Key Features of Intercom

  • Easy Collaboration: Intercom’s interface allows for quick and easy collaboration. You can also access old customer interactions from over a year ago.
  • Automated Campaigns: Intercom allows you to automatically trigger an E-Mail or Chat Message campaign based on user or visitor activity.
  • Real-time Metrics: Intercom allows you to quickly access metrics like Open Rates and Click-Throughs. However, often, businesses would need to import this data into BigQuery to combine it with other sources and get deeper insights.
  • Outbound Messaging: You may also utilize mobile push notifications, customized emails, and in-app messages to entice users to take action or convert leads into loyal customers.
  • Team Inbox: This Intercom feature allows you to provide scalable and personalized help to your customers.

Explore Methods to Connect Redshift to Intercom

Method 1: Using CSV Files to Manually Connect Redshift to Intercom

This method of connecting Redshift to Intercom involves manually converting your Redshift data into CSV Files using certain SQL commands. The data present in the CSV Files then has to be modified according to the format specified by Intercom. Afterward, the data can be imported and mapped directly into Intercom. 

Method 2: Connecting Amazon Redshift to Intercom using Hevo Activate

Hevo Activate provides a hassle-free, one-stop solution to Connect Redshift to Intercom in an effortless manner. Hevo Activate syncs customer /product data in the warehouse to your Business Software. Hevo Activate brings the data to the fingertips of your business teams, where they need it the most – Business Applications. Teams can now make faster, smarter actions. Sync customer & product usage data, analyze the customer journeys and create personalized experiences with Hevo Activate.

GET STARTED WITH HEVO ACTIVATE FOR FREE

Redshift to Intercom ELT Integration 

Method 1: Using CSV Files to Manually Connect Redshift to Intercom

To connect Redshift to Intercom, you use an indirect method, first you connect Redshift to CSV and CSV to Intercom. 

Redshift to CSV 

To connect Redshift to Intercom, You have to connect Redshift to CSV first. You can do that in the following ways:

UNLOAD Command
  • This is an easy way that helps connect Redshift to Intercom. Redshift supports the quick exporting of data to CSV using some straightforward SQL. You can access the Editor button in the menu on the left after logging into the Redshift console. 
  • Navigate to the Query Editor by hovering over it to connect to a database. After connecting, you can begin executing SQL queries. The fundamental syntax for exporting your data is as follows.
UNLOAD ('SELECT * FROM your_table')
TO 's3://object-path/name-prefix'
IAM_ROLE 'arn:aws:iam::<aws-account-id>:role/<role-name>'
CSV;
  • The data you want to export is questioned on the first line. A LIMIT clause can only be used in an inner SELECT statement in Redshift.
  • You specify the target S3 bucket path in the TO clause, which is on the second line. The query cannot be executed unless you have written permission.
  • You can authorize in several ways, one of which is on the third line. When using the method described above, go to the support center and click on your account name in the navigation bar to find your 12-digit account ID.
  • The fourth line instructs Redshift to save your data as CSV, which is not the default, and this completes the command.
  • You can modify the aforementioned query to suit your needs by including a variety of additional 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.
    • DELIMITER AS ‘character’: A comma is the standard character for CSV files. Commas in your data could produce unexpected results. You could, for example, use a pipe (|) in this situation.
    • ADDQUOTES: Another way to ensure that commas in your data don’t produce unexpected results is to add quotes around every field by doing this.
    • BZIP2, GZIP, or ZSTD: By significantly reducing the size of your file, one of these compression options will enable you to send or download it more quickly.
  • You can discover all alternatives withinside the AWS Database Developer guide. Once you have stored your information on your S3 bucket, the very best manner to download it on your nearby device is to navigate on your bucket and document withinside the AWS console, from wherein you may download it directly.
AWS SDK
  • One of AWS’s SDKs, which comes in a variety of programming languages like JavaScript, Python, Node.js, and Ruby, allows you to communicate with the service through programming. With the aid of an SDK, you can 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 logged into the Redshift console, you can find the region at the beginning of the URL, which you should use for the 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 gather 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 that contains details about the request you just made is the response. Id, the query’s globally distinctive 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}}
query_id = response['Id']
  • To view the status of your query, enter the following query ID into describe statement. If the key Status does not yet equal FINISHED, it is useless to attempt to access your data.
print(client.describe_statement(Id=query_id)['Status'])
  • Get the information using the get statement result and save it to a variable once the status indicates 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 needs to be retrieved if there is a NextToken.
next_token = data['NextToken']
more_data = client.get_statement_result(Id=query_id, NextToken=next_token)
  • Depending on the amount of data you accessed, you might want to create a for loop to iterate through your data and append it to a single variable.
  •  In this case, “inconvenient” refers to the fact that you can’t just convert it into a pandas DataFrame right away, at least not into one that makes sense. For your convenience, we created a function.
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 create a DataFrame and save it to CSV must now be called.
df = redshift_to_dataframe(data)
df.to_csv('your-file.csv')
AWS Command Line Interface (CLI)
  • The AWS CLI is a good alternative for connecting Redshift to Intercom, but you’ll need to install it first. Amazon’s installation guidelines since each OS has a different installation procedure. You are now prepared to query and export your data after installation. Here is the fundamental syntax.
aws redshift-data execute-statement 
    --cluster-identifier my_cluster
    --database my_database                
    --secret arn:aws:secret:us-west-2:123456789012:secret:my_secret 
    --sql "select * from your_table"
  • You can execute a SQL query from the CLI using the execute-statement command. You are free to create any SQL query you require to obtain the proper data. To further customize the execute-statement command to your liking, there are a few additional options that you can add. You’ll get an Id for your SQL statement as part of the response after running this command. To download your data, plug this into the command listed below.
aws redshift-data get-statement-result --id your_sql_id
SQL Client

This is a simple way that helps connect Redshift to Intercom. On your local computer, you can connect to Redshift using a SQL client. Only choose this route if you plan to query Redshift more frequently because it may take some time to set up your tool and connection. There are many tools available that can handle the job, like MySQL Workbench.

  • Link up with a database.
  • Consult a table.
  • The output is exported as CSV.

CSV to Intercom

Now, to connect Redshift to Intercom, you need to connect CSV to Intercom. To do that, follow these steps:

  • Step 1: Simply select “Open CSV Import Tool.”
  • Step 2: To import a list of customers or users who have signed in to your product, choose “Import users.” A list of leads can also be imported into your Intercom workspace by selecting “Import leads.”
redshift to intercom: import csv files
Image Source
  • Step 3: Upload by dragging and dropping your CSV file. 20 mb is the maximum file size allowed. If the file is bigger than that, you should split it up.
  • Step 4: You will now be prompted by Intercom to choose the relevant columns that contain the email addresses, IDs, full names, and sign-up dates of your users. To select a column for each, select it and then click “Confirm.”
redshift to intercom: import csv files select columns
Image Source
  • Step 5: Intercom will ask you to give your custom attributes a specific name if you’ve added extra columns. Once you have given your custom attribute a name, click “Confirm.”
  • Step 6: Put a memorable term in your CSV list’s tags, like “New Sign-Ups 2015.”
  • Step 7: Once imported, then Redshift to Intercom connection is established and you can look for the phrase “tag: New Sign-Ups 2015” in your Intercom User list. Now that you’ve created a segment for that group, you can send them a specific automated message or send a manual message to everyone on the list.

Method 2: Connecting Amazon Redshift to Intercom using Hevo Activate

redshift to intercom: hevo logo
Image Source

Hevo Activate helps you directly transfer data from Redshift, Snowflake, Xero, etc., and various other sources to CRMs such as Intercom, various SaaS applications, and a lot more for free, in a completely hassle-free & automated manner. Hevo Activate is fully controlled and automates the process of not only importing data from your preferred source but also enriching the data and putting it into an analysis-ready format without the need for you to write a single line of code. Its fault-tolerant architecture ensures that data is handled securely and consistently, with no data loss.

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:

  • 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, Zendesk, Intercom, and others in an integrated and analysis-ready format.
  • Simplicity: Using Hevo Activate is simple, 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.
Sign up here for a 14-Day Free Trial!

The steps to Connect Redshift to Intercom using Hevo Activate: 

  • Step 1: Configure your Redshift as your Warehouse
    • Step 1.1: In the Asset Palette, click ACTIVATE, and then:
      • Select + CREATE ACTIVATION in the ACTIVATIONS tab.
redshift to intercom: activations tab
Image Source
  • Click + ADD WAREHOUSE on the Select Warehouse page.
redshift to intercom: select warehouse
Image Source
  • Choose Amazon 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 Amazon Redshift as the destination type on the Add Destination page.
  • Step 1.3: Set the following parameters on the Configure your Amazon Redshift Warehouse page:
redshift to intercom: configure amazon redshift as warehouse
Image Source
  • Warehouse Name: Your Warehouse should have a unique name.
  • Database Cluster Identifier: The cluster that houses your database. This could be the DNS or IP address of the Amazon Redshift host that you retrieved in Step 3.
  • Database Port: The port number where connections are accepted by your Amazon Redshift server. The port number you retrieved in Step 3 or the one you entered when configuring the database can be used here. Standard value: 5439.
  • Database User: You’ve created a Hevo user. In the Amazon Redshift database, this user has a non-administrative role.
  • Database Password: The database user’s password.
  • Database Name: The database name from which data will be synchronized.
  • Schema Name: The name of the schema that the database user has USAGE permissions for. PUBLIC is the default value. The name of the schema is case-sensitive.
  • Step 1.4: To test connectivity with the Snowflake warehouse, click TEST CONNECTION.
  • Step 1.5: Click SAVE & CONTINUE when the test is completed successfully.
  • Step 2: To set Intercom as the Target in your Activation, follow these instructions:
    • Step 2.1: In the Asset Palette, select Activate.
    • Step 2.2: Choose one of these:
      • Click + CREATE TARGET after selecting the TARGETS tab in the Targets List View.
redshift to intercom: targets tab
Image Source
  • Click + CREATE ACTIVATION after selecting the ACTIVATIONS tab in the Activations List View.
redshift to intercom: activations tab
Image Source
  • Step 2.3: Choose your active warehouse from the Select Warehouse page, or click + ADD WAREHOUSE to add a new warehouse. To set up the chosen Warehouse type.
  • Step 2.4: Click + ADD TARGET on the Select a Target page.
redshift to intercom: select a target
Image Source
  • Step 2.5: Select Intercom from the Select a Target Type page.
redshift to intercom: select target type
Image Source
  • Step 2.6: Click + ADD INTERCOM ACCOUNT on the page for adding Intercom targets.
redshift to intercom: add intercom as target
Image Source
  • Step 2.7: To grant Hevo access to your data, sign in to your Intercom account and click Authorize access.
redshift to intercom: authorize access
Image Source
  • Step 2.8: Enter a distinctive Target Name on the Add the Intercom Target page.
redshift to intercom: add the intercom target
Image Source
  • Step 2.9: Choose CONTINUE. The Targets List View allows you to see the new Target. You return to the Select Data to Synchronize page if you are creating an activation.
redshift to intercom: target list view
Image Source

Conclusion

This article describes the different methods to connect Redshift to Intercom in a few easy steps. It also gives an overview of Redshift and Intercom.

VISIT OUR WEBSITE TO EXPLORE HEVO ACTIVATE

With Hevo Activate all these processes can be eliminated and seamlessly integrated within the Hevo dashboard. It allows you to immediately transfer data from a source of your choosing, such as Snowflake, Amazon Redshift, and so on, to any SaaS application, CRM like Intercom, and so on, in a fully automated and safe manner. It will make your life easier and data movement less difficult. It is simple to use, dependable, and secure. Hevo Activate, with its strong integration with numerous sources & BI tools, 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 Activate for a spin?

SIGN UP and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.

Sync your Data to Intercom Seamlessly