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 loadingnote: 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
your Pendo Integration API Key
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.”
- Name the package
- Leave the Type as Dataflow
- Leave Template set to Blank
- 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:
- Click the drop arrow next to GET and select POST
- Copy the API URL from your RESTful API client and paste into the URL field next to Post
- Copy the Key names and Values from your RESTful API client’s Headers and paste as keys and values under Headers
- Copy the request body from your RESTful API client and paste it into the Body field
For the Response Section:
- Leave the Response type set to JSON
$.results[*]into the Base record JSONPath Expression field
- 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
- Enter a name for this component
- Select a target connection
- Click Next
Step 4. Add the following Destination Properties
- Enter a name for a Target table you want to create
- Set the checkboxes
- Operation type to meet your data needs
- 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
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.