How to Connect Pendo to Redshift? | 2 Methods Simplified

Easily move your data from Pendo To Redshift to enhance your analytics capabilities. With Hevo’s intuitive pipeline setup, data flows in real-time—check out our 1-minute demo below to see the seamless integration in action!

Product teams in software companies are often under tremendous pressure to optimize their solutions and provide a better customer experience. One of the best ways to enhance user experience on digital solutions is by tracking user engagement.

Although companies track engagement with Google Analytics, it does not provide valuable information for SaaS solutions. Consequently, organizations use Pendo, a product experience, and digital adoption solution, for their SaaS products.

Pendo eases this workload and assists software companies by providing practical analysis and insights into their product. However, to get in-depth insights, you must transfer data from Pendo to a Data Warehouse like Amazon Redshift. 

In this article, you will learn how to connect Pendo to Redshift to help you enhance your user experience and better understand your data.

What is Redshift?

Amazon Redshift is a fully managed, petabyte-scale cloud data warehouse service for analyzing large datasets. It allows businesses to run complex queries and perform structured and semi-structured data analytics using standard SQL. Redshift leverages columnar storage, data compression, and parallel processing to deliver fast query performance. It supports integration with various data sources and tools, making it ideal for Online Analytical Processing (OLAP) workloads.

Features of Redshift

• Machine Learning: Redshift ML integrates with Amazon SageMaker, enabling users to build, train, and deploy machine learning models using simple SQL commands, requiring no prior knowledge of ML or programming languages.
• Data Sharing: Easily share data across single and multi-cluster deployments without data duplication, ensuring instant and granular access while maintaining data fidelity.
• Easy Analytics: The Amazon Redshift serverless option simplifies data analysis for users, allowing data analysts, scientists, and business professionals to gain insights without technical assistance, as Redshift optimizes data structure automatically.

What is Pendo?

Pendo helps software-based companies perform product analysis and better resonate with their target customers. It assists software developers in enhancing their products with its vast collection of tools like in-app guides, product analytics, and sentimental analytics.

Pendo supports product companies in acquiring better results and enables your company to identify the areas where your product management team should focus on. Identifying and dealing with these grey areas can result in an enhanced product experience.

By implementing Pendo with your product, you can understand the performance of your product and lighten the workload of your sales, marketing, R&D, and support teams.

Features of Pendo

• Product Analytics: Monitor your software product from deployment, accessing analytics without feature tags. Key features like Engagement Scores and Data Explore help identify performance areas and upsell/cross-sell opportunities without technical assistance.
• Tooltips: Personalize the user experience with tooltips that guide new users. Create real-time campaigns to determine the best tooltip combinations and access reports on tooltip engagement metrics.
• In-app Messaging: Communicate directly with users through in-app messaging to share updates on new features, resources, and how-to guides. This proactive support helps address bugs and issues, fostering positive business outcomes.

Methods to Connect Pendo to Redshift

Method 1: Automated Pendo to Redshift Replication Using Hevo Data

Hevo Data is an Automated No-Code Data Pipeline Solution that helps you move your Pendo to Redshift. Hevo is fully-managed and completely automates the process of not only loading data from your 150+ Data Sources (including 60+ Free Sources) 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.

Using Hevo Data, you can connect Pendo to Redshift in the following 2 steps:

Step 1: Configuring Pendo as a Source

Perform the following steps to configure Pendo as the Source in your Pipeline:

1. In the Asset Palette, Click on PIPELINES.
2. In the Pipelines List View, Click + CREATE.
3. In the Select Source Type page, select Pendo.

1. In the Configure your Pendo Source page, specify the following:
   • Pipeline Name: A unique name for your Pipeline, not exceeding 255 characters.
   • Region: The subscription region of your Pendo account. Default value: Region – US.
   • Integration Key: A secret value with read-write access to your Pendo data via v1 APIs. Note: This key is specific to the subscription region of your account.
   • Historical Sync Duration: The duration for which the existing data in the Source must be ingested. Default value: 3 Months. Note: If you select All Available Data, Hevo fetches all the data created since January 01, 2013 for your account.
2. Click TEST & CONTINUE.
3. Proceed to configuring the data ingestion and setting up the Destination.

Step 2: Configure Amazon Redshift Connection Settings

Perform the following steps to configure Amazon Redshift as a Source in Hevo:

1. In the Asset Palette, Click on PIPELINES.
2. In the Pipelines List View, Click + CREATE.
3. In the Select Source Type page, select Amazon Redshift.

• In the Configure your Amazon Redshift Source page, specify the following:
  • Pipeline Name: A unique name for your Pipeline.
  • Database Cluster Identifier: Amazon Redshift host’s IP address or DNS name.
  • Database Port: The port on which your Amazon Redshift server is listening for connections. Default value: 5439.
  • Database User: The authenticated user who has permission to read tables in your database.
  • Database Password: The password for the database user.
  • Select an Ingestion Mode: The desired mode by which you want to ingest data from the Source. The available Ingestion Modes are Table, and Custom SQL. Read Ingestion Modes. For Ingestion mode as Table, read Object Settings to configure the objects to be replicated.
  • Database Name: The database that you wish to replicate.
  • Connect through SSH: Enable this option to connect to Hevo using an SSH tunnel, instead of directly connecting your Amazon Redshift database host to Hevo. This provides an additional level of security to your database by not exposing your Amazon Redshift setup to the public. Read Connecting Through SSH. If this option is disabled, you must whitelist Hevo’s IP addresses.
• Click TEST & CONTINUE to proceed with setting up the Destination.

Method 2: Manually Migrating Data From Pendo to Redshift

This method consists of two steps to connect Pendo and Redshift. These steps include exporting the data from Pendo, followed by uploading this data into Redshift for secure storage and analysis. Ensure you follow these given steps in order to connect Pendo and Redshift effectively:

Step 1: Exporting Data From Pendo

All users except for Read-Only can create and save reports in the Data Explorer section. You will get your data in the form of a CSV file. This file will have a dataset with the score, status, tags, efforts, id, title, description, etc. Follow these steps to download your CSV file from Pendo successfully:

Step 1.1: Create Your Report

• Go to the Data Explorer tab under the Behavior section.

You will need a Data Source and an object to create a new report. However, you must provide two Data Sources and ten objects for advanced analysis. Note that the Data Explorer will analyze data daily for a default of 30 days.

Step 1.2: Build Desirable Query

Building the desirable query is essential for creating the dataset you want. You can choose the date range, segment, group, and other parameters. Follow these steps to build an effective query:

• Select two Data Sources.
• Select a measurement for each Data Source.

• You can select a different app for each Data Source used for multiple applications.
• Select the data range of your choice.

• Select the data breakdown (optional).

• Compare data range. 
• Click on the desirable segment you want. Note that the default for a segment is “to Everyone.”
• Create a draft segment by clicking create a draft segment, adding rules, and running it.
• Choose the Group by available Metadata.

Step 1.3: Run the Created Query

Step 1.4: Go to Visualization and Side Panel

Once you have executed your query, Pendo will display the breakdown and chart table. Both of these tools will help you to visualize the data easily. You can download the chart as well.

Step 1.5: Go to the Breakdown Table

• Click the Download button to export the data as a CSV file.

• Save the report

Step 2: Uploading Data Into Redshift

You can use Amazon Redshift with Amazon S3 or remote hosts to safely store data. We will load data from Pendo to Redshift with the help of Amazon S3 buckets. You can use the INSERT command to enter the files, but this method is time-consuming and tedious. Instead, we suggest using the COPY command to save your time.

Follow the instructions to load data into Amazon Redshift properly:

Step 2.2: Entering Data Into the Amazon S3 Bucket

The containers of Amazon S3 are known as buckets. Each user has a maximum of 100 buckets to store their data, documents, and files. On request, you can increase the number of buckets of your account.

First, you must create a cluster; if you already have one, skip this step. Now create a new bucket, select a region and load your data in the bucket. Note that the bucket’s name should be unique among your account’s existing bucket names.

Step 2.2: Loading Data to Amazon Redshift

Follow the given steps to load the data to Amazon Redshift correctly:

• Create a sample table with apportioned attributes.
• Execute the COPY command to load the data.

COPY table_name [ column_list ] FROM data_source CREDENTIALS access_credentials [options] 

• Run the VACUUM and ANALYZE command after every CRUD operation.
• Drop your clusters.

Limitations of Using Manual Method to Migrate Data From Pendo to Redshift

Connecting Pendo to Redshift properly through a manual process is time-consuming and opens a window for human errors, Data Quality issues, and Data Integrity problems.

Hence you should use ELT tools or No-code Platforms like Hevo Data. With Hevo, there is no need to download your data on your device or even use Amazon S3 buckets. Hevo Data delivers easy and effective real-time Data Replication from Pendo to Redshift.

Conclusion

If you have or are going to deploy a product-based application, then ensure you use Pendo to monitor the progress and detect flaws in your product. With these insights, you understand the customer’s behavior and enhance your application, thereby increasing the user experience.

You can also store this data and essential insights in Amazon Redshift for further analysis. Redshift, along with the assistance of the Amazon SageMaker, helps in discovering critical insights to make better decisions for your business.

There are various sources that companies use as it provides many benefits, but, transferring data from these sources into a Data Warehouse is a hectic task. Automated Data Pipeline Solutions help in solving this issue and this is where Hevo comes into the picture. Hevo Data is a No-code Data Pipeline and has awesome 100+ pre-built integrations such as Pendo and Redshift to choose from.

FAQ on Pendo to Redshift