This article explains how to export Product Engagement Score (PES) data at the account level, alongside adoption, stickiness, and growth, directly into Google Sheets through the API. This allows you to analyze the PES easily at the account level and share this data with other users and teams in your organization, even if they don’t have access to Pendo.
The resulting report will contain one row for each Account ID. Each row will include separate columns for the stickiness, growth, adoption, and overall PES scores for that specific Account ID.
Important: While you can use the solution provided in this article as a starting point to customize your own API calls and create advanced reporting within Google Sheets, this is considered custom code and is thus outside the scope of Pendo Technical Support. If additional help is needed, it needs to be evaluated by your Pendo account representative and potentially addressed through a paid engagement with our Professional Services team.
Requirements
Before continuing with the guidelines below, confirm the following:
- API access is included in your Pendo contract. If you’re unsure, contact your Pendo account representative.
- You have an integration key. This isn’t the key that appears in the Pendo install script, and only Admins can create integration keys. To create an integration key in Pendo, go to Settings > Integrations > Integration Keys and select Add Integration Key.
- You have access to Google Sheets and the Google Apps Script extension.
Create the Google script
Follow the steps below to extract the contents of a visitor or account report directly to Google Sheets.
- Create a new Google Sheet.
- From the top menu bar, select Extensions > Apps Script to open Apps Script in a new tab.
- In the new tab, your focus defaults to the Code.gs file. Delete all of the existing content, including the empty function Google adds by default, and replace it with the following text:
//----------------------------------------------------------------------------------
// SPREADSHEET CONSTANTS
//----------------------------------------------------------------------------------
/**
* SPREADSHEET_ID_PES: ID of the spreadsheet where data will be exported to
* SHEET_NAME_PES: Name of the sheet where you want to replace all data on the spreadsheet
* INTEGRATION_KEY_PES: x-pendo-integration-key you obtained from the API to be passed as header
* APP_ID: The ID of the Pendo application you want to export data from. If you only have one web app in the Pendo subscription, the ID will be -323232 and you don't need to make any changes.
* BASE_URL_PES : If you access Pendo through the US instance (app.pendo.io), take no action. The default value is already correct. If your Pendo subscription is in the EU instance, change the BASE_URL value to “app.eu.pendo.io”. If your Pendo subscription is in the US1 instance, change the BASE_URL value to “app.us1.pendo.io”. If your Pendo subscription is in the JPN instance, change the BASE_URL value to “app.jpn.pendo.io”. If your Pendo subscription is in the US1 instance, change the BASE_URL value to “us1.app.pendo.io”.
*/
const SPREADSHEET_ID_PES = "ENTER-YOUR-SPREADSHEET-ID";
const SHEET_NAME_PES = "ENTER-YOUR-SPREADSHEET-TAB";
const INTEGRATION_KEY_PES = "ENTER-YOUR-INTEGRATION-KEY";
const APP_ID = -323232; //No quotes here
const BASE_URL_PES = "app.pendo.io";
//----------------------------------------------------------------------------------
// PES Configuration
//----------------------------------------------------------------------------------
/** Enter the number of days you want to analyze. Example: 180 to analyze the last 180 days */
const NUMBER_OF_DAYS = ENTER-NUMBER-OF-DAYS; //No quotes here
/** Enter the same selection you see in Pendo > Behavior > PES > Manage PES. Example given below; no need to modify if it matches your configuration in Pendo */
const ADOPTION_USERBASE = "visitors";
const STICKINESS_USERBASE = "visitors";
const STICKINESS_NUMERATOR = "weekly";
const STICKINESS_DENOMINATOR = "monthly";
const STICKINESS_EXCLUDE_WEEKENDS = true; //No quotes here
const GROWTH_USERBASE = "visitors";
/***********************************************************************************
------------------------------------------------------------------------------------
------------------PLEASE DO NOT MODIFY ANYTHING BELOW THIS SECTION------------------
------------------------------------------------------------------------------------
***********************************************************************************/
// Don't change these headers, these are for the API response and PES result filtering
const PES_HEADERS = ['account', 'stickiness', 'growth', 'adoption', 'productEngagementScore'];
// PES Query for the application defined in the constants area
const PES_QUERY = {
"response": {
"mimeType": "application/json"
},
"request": {
"name": "PES",
"pipeline": [
{
"pes": {
"appId": APP_ID,
"firstDay": "now()",
"dayCount": -1 * NUMBER_OF_DAYS, //negative number (e.g. -30 days)
"groupBy": "account",
"config": {
"adoption": {
"userBase": ADOPTION_USERBASE
},
"stickiness": {
"userBase": STICKINESS_USERBASE,
"numerator": STICKINESS_NUMERATOR,
"denominator": STICKINESS_DENOMINATOR,
"excludeWeekends": STICKINESS_EXCLUDE_WEEKENDS
},
"growth": {
"userBase": GROWTH_USERBASE
}
}
}
}
]
}
}
/**
* Get all accounts PES data for last 180 days from PES aggregation and extract the results.
* This call utilizes a predefined PES_QUERY Object, that has the appId and other specifications of the request.
*/
function apiCallPES_() {
let options, endpoint, response, events;
// HTTP Post request, with authentication key in header and json payload
options = {
'method': 'post',
'payload': JSON.stringify(PES_QUERY),
"headers": {
'x-pendo-integration-key': INTEGRATION_KEY_PES,
'content-type': "application/json"
},
'muteHttpExceptions': true
};
// Hit the aggregation endpoint and grab required details
endpoint = `https://${BASE_URL_PES}/api/v1/aggregation`;
response = UrlFetchApp.fetch(endpoint, options);
// If the request was successful, then proceed; otherwise return false
if (response.getResponseCode() == 200) {
// Prase the response Content to JSON
pesResults = JSON.parse(response.getContentText());
// If there are one or more results, then return them; otherwise return false
if (pesResults['results'].length > 0) return pesResults['results'];
}
// Failed request or no PES results will cause the method to return false
return false;
}
/**
* Convert array of objects to two-dimensional array with only considering keys that are in
* headers and ignoring the rest
*/
function formatDataPES_(pesResults, headers) {
let arr, arrNested;
// Add the headers as list on first index (because they go on top of all rows in sheet)
arr = [headers];
// For each account in the pesResults, grab their values and combine all in matrix format
// Where each new account goes on next row
pesResults.forEach((accountRes) => {
arrNested = [];
headers.forEach((header) => { arrNested.push(accountRes[header]) });
arr.push(arrNested);
});
return arr;
}
/**
* Get clean and formatted PES data and write it to the sheet
*/
function writePESToSheet_(pesData) {
// Open handle to workbook, if not found then break the process
let workbook = SpreadsheetApp.openById(SPREADSHEET_ID_PES);
if (!workbook) {
Logger.log("WORKBOOK NOT FOUND ");
return;
}
// Open handle to worksheet, if not found then break the process
worksheet = workbook.getSheetByName(SHEET_NAME_PES);
if (!workbook) {
Logger.log("WORKSHEET NOT FOUND");
return;
}
// Clear whatever you see in the sheet at this point
worksheet.getDataRange().clear();
// Write the new PES data to the sheet
worksheet.getRange(1, 1, pesData.length, pesData[0].length).setValues(pesData);
// bold, center, and make the background green for first row (header row)
worksheet.getRange(1, 1, 1, 5)
.setBackground('#DCC9F9')
.setFontWeight("bold")
.setHorizontalAlignment('center');
}
/**
* Command the whole process in one method; doesn't do much on its own, but is helpful to organize the code
*/
function mainPES() {
// Grab the pesData by making API call to the aggregation endpoint
let pesData = apiCallPES_();
// If not successful, then halt the process
if (!pesData) {
Logger.log("API Call Failed or there is no data to grab");
return;
}
// Format the data, converted from object to 2D array
pesData = formatDataPES_(pesData, PES_HEADERS);
// Write the 2D ARR to spreadsheet tab
writePESToSheet_(pesData);
} - Update the constants that are present on lines 12 through 32, which allow you to use the script with different sheets, spreadsheets, reports, apps, and PES metrics.
- For const SPREADSHEET_ID_PES = "ENTER-YOUR-SPREADSHEET-ID", replace ENTER-YOUR-SPREADSHEET-ID with your spreadsheet ID. To retrieve this value, copy the ID in the URL: https://docs.google.com/spreadsheets/d/SPREADSHEET-ID-HERE/edit#gid=0.
- For const SHEET_NAME_PES = "ENTER-YOUR-SPREADSHEET-TAB", replace ENTER-YOUR-SPREADSHEET-TAB with the name of the Sheet tab.
- For const INTEGRATION_KEY_PES = "YOUR-INTEGRATION-KEY", replace YOUR-INTEGRATION-KEY with a supported integration key. As described in Pendo Integration Key, only Pendo Admins can create integration keys. To extract data from Pendo, you only need Viewer access.
- For const APP_ID = ENTER-YOUR-APP-ID, replace ENTER-YOUR-APP-ID with the ID of the Pendo application you want to extract data from. If you only have one web app in your Pendo subscription, the ID is -323232 and you don't need to make any changes to this value. If you have more than one app in your subscription, see our Multi-App API documentation to learn how to retrieve this value.
-
For Const BASE_URL = “app.pendo.io”, ensure that this is the URL you use to access Pendo.
- If you access Pendo through the US instance (app.pendo.io), take no action, as the default value is already correct.
- If your Pendo subscription is in the EU instance, change app.pendo.io to app.eu.pendo.io.
- If your Pendo subscription is in the JPN instance, change app.pendo.io to app.jpn.pendo.io.
- If your Pendo subscription is in the US1 instance, change app.pendo.io to app.us1.pendo.io.
- For const NUMBER_OF_DAYS = ENTER-NUMBER-OF-DAYS, enter the number of days you want to analyze. For example, to analyze the last 180 days, enter 180.
-
For the remaining constants below, update the values in quotes based on your PES configuration under Behavior > PES > Manage PES in Pendo:
const ADOPTION_USERBASE = "visitors";
const STICKINESS_USERBASE = "visitors";
const STICKINESS_NUMERATOR = "weekly";
const STICKINESS_DENOMINATOR = "monthly";
const STICKINESS_EXCLUDE_WEEKENDS = "true";
const GROWTH_USERBASE = "visitors";
- For const SPREADSHEET_ID_PES = "ENTER-YOUR-SPREADSHEET-ID", replace ENTER-YOUR-SPREADSHEET-ID with your spreadsheet ID. To retrieve this value, copy the ID in the URL: https://docs.google.com/spreadsheets/d/SPREADSHEET-ID-HERE/edit#gid=0.
-
Select the Save icon, then select Run. This opens a dialog that requires you to authorize Google access to the data in the script.
- Select Review permissions, select your Google account, then select Allow.
- Confirm that the Google Sheet updates with the report information and that it shows the same data as the report in Pendo.
Update report contents with a button
If desired, you can add a button to your spreadsheet so that the code runs and updates the spreadsheet with the latest report contents every time you select that button.
- In Google Sheets, go to Insert > Drawing.
- Choose your shape and format it to your preference, then select Save and Close.
- Once you add the drawing to the spreadsheet, right-click on the drawing, select the ellipsis (three dots) in the upper-right corner, and then select Assign script.
- Enter mainPES, the name of the function included in the code, then select OK.
- Select the button to confirm that it updates the table as expected.
Update report contents on a schedule
You can also set up automation to pull the report contents from Pendo on a recurring basis.
- In Google Sheets, select Extensions > Apps Script.
- From the left navigation menu, select Triggers (the clock icon).
- In the bottom-right corner of the page, select Add Trigger.
- Configure the trigger to meet your needs. Each field is detailed below:
- Choose which function to run. This should automatically detect the getReport function added in the code on step 4 in the previous section. Don’t change this selection.
- Choose which deployment should run. This option defaults to Head. Don’t change this selection.
- Select event source. Select Time-driven in the dropdown menu. This updates the next two dropdown options.
- Select type of time based trigger. If you want to update the report every day, select Day timer.
- Select time of day. Select whichever time best suits your needs. Google automatically detects your time zone.
-
Failure notification settings. We recommend Notify me immediately, but there are other options available. You can select more than one option by selecting the Plus (+) icon next to the field name.
- Select Save to add the trigger to run the script at the scheduled time.