Recipe: Prepare Your Data for BI Visualization Tools

Overview

As Pendo is deployed in more and more Enterprise applications, we find that our customer base in that segment is eager to pull specific Pendo data into their BI reporting environment so they can visualize Pendo data by itself or join it with other data sources such as Salesforce, Marketo, etc. using their own data warehouse/lake and BI tools.

Ingredients (What Do I Need?)

In this Recipe, the following applications were used to accomplish the objective:

• Pendo API using Postman (a RESTful API client) - used for narrowing down the data for extracting, transforming and loading

  Note: Purchase required: The Pendo API Package isn’t enabled by default. It is a feature you can purchase. Speak with your Pendo representative to gain access.
• Xplenty - used as an extract, transform and load (ETL) tool
• Google BigQuery - used as a data warehouse

How Do I Make It?

By the end of the instructions, the customer should be able to pull data from Pendo using the Pendo API, load it into a BigQuery data warehouse, and visualize it via Looker.

Part 1. Pendo API

Using the Pendo Aggregation endpoint, pull the data you need to load into your data repository.

Step 1. Build your first Aggregation API call using the following information.

Type = Post URL = https://app.pendo.io/api/v1/aggregation Headers: X-Pendo-Integration-Key = your Pendo Integration API Key Content-Type = application/json

Request body: Add the aggregation request body that will pull the data you’re looking to explore here. Use these sample ETL API calls to help you get started.

Step 2. Run and refine the API call as you need to get to the exact data you want to use in your ETL process and BI tool.

Take a look at these sample ETL API calls to help you get started on finding the right calls for you.

Part 2. Xplenty - Setup Your Source Data as Pendo

This part is used to extract, transform and load (ETL) your Pendo data into a data warehouse using the API you created in the previous part. To get started, log into Xplenty and click on New Package to designate Pendo as your Source data.

Step 1. To get started, login and create a “New Package.”

1. Name the package
2. Leave the Type as Dataflow
3. Leave Template set to Blank
4. Click Create Package

Step 2. Add component

Then, click on “Add component.”

Step 3. Choose your source Destination

Select API - Rest API under Sources

Step 4. Build the source API call

Name the API component something meaningful.

For the Authentication section:

• Leave the authentication type as “none.”

For the Request & Response section:

1. Click the drop arrow next to GET and select POST
2. Copy the API URL from your RESTful API client and paste into the URL field next to Post
3. Copy the Key names and Values from your RESTful API client’s Headers and paste as keys and values under Headers
4. Copy the request body from your RESTful API client and paste it into the Body field

For the Response Section:

1. Leave the Response type set to JSON
2. Enter $.results[*] into the Base record JSONPath Expression field
3. Click Next

For the Response Schema:

• Once it renders, click Select all in Available Fields to add all the columns to the Selected Fields

• Once you’re done adding fields to Selected Fields, click Save. This will close the API building interface.

You should see your finished component like this:

Part 2. Xplenty - Setup your Destination as Google BigQuery

Step 1. Click the Add Component button and then, under Destinations, select Google BigQuery.

Step 2. Select a target connection within BigQuery and select next.

Step 3. Add the following Destination Properties

1. Enter a name for this component
2. Select a target connection
3. Click Next

Step 4. Add the following Destination Properties

1. Enter a name for a Target table you want to create
2. Set the checkboxes
3. Operation type to meet your data needs
4. Scroll down and click Next

Step 5. Map Input to Target Table Columns

Click the Auto-fill button to match the Input Fields to the Destination Columns and Save.

Step 6. Save & run job

Click the Save & Run job button to see if what you built will run successfully.

Step 7. Select a cluster to run the job on and click Next.

Step 8. Select the package you just created and click Next

Step 9. Skip edit packaging variables step since there are no User or System variables to define in this case and click Run Job

You should see a confirmation message indicating the job number referencing the cluster the job is running on in the top center of the interface.

Watch your status in real-time.

Once your job is done, you should see a 100% Completed status. If you see a 100% Running status, the job is still finishing up the job.

Part 3. Google BigQuery

Step 1. Open BigQuery and select your table to validate your data

Step 2. Validate your table

If all went as planned, you should see schema information that matches what you defined for columns in Xplenty.

Click Query Table. Then edit the query in the upper panel to read SELECT * FROM… then click Run Query.

A pop-up will generate. Select Go To Table Preview.

• You should see a preview of the data you loaded in the panel under your table name.
• Check to see if what you’re viewing is what you expected
  • If what you’re seeing isn’t what you expected, circle back through the steps, starting with the API call, and refine until you get the data you want

Next Steps

Now that you have data from your Pendo subscription in a data repository like Google BigQuery, the next step is to work with the administrator of your BI visualization tool to incorporate this data into that environment. Once added to the BI visualization environment, you’ll be able to join it with other data to identify interesting trends, valuable insights, etc. to help accelerate and grow your business.

More on Xplenty Data Transformations

Xplenty Transformation Components

You’ll have to add a transformation component to your package. Click the + Add component button and select a transformation component.

In this example, we used a simple Select transformation component.

Add it to your package, connect it upstream to your Source and downstream to you Destination database, refresh available fields.

Xplenty Transformation Functions

Xplenty makes transformations simple, although their syntax was a little counterintuitive at first See the screenshot to the right…I would have expected a date transformation to follow a ToDate then within that transformation a ToString for the column

To learn more, take a look at their Xplenty Transformation Functions article.