Approach to Querying All Data

If you are reading this, chances are you have interest in using the Engagement History API to add data to your data warehouse or want to leverage the functionality to get to the raw transcript data. In this post, I am going to cover some approaches we have used to extract all of the data in a meaningful way.  The examples will be leveraging R but I will be discussing a general approach other languages will be able to leverage

 

*Note- I am assuming you already understand how to connect to the API, access it via a POST request and return data.

 

Getting All of the Data

 

When you are ready to start accessing the data in the API, the following concepts from the API will be important to understand.

 

Count and Offset

Before we dive into the Count and Offset, let’s review a part of the POST url that will be used in our code: In the POST url, you will define both an offset and limit.  A description of these can be found below.

 

 

Due to the max limit of 100, you will likely need to make multiple calls using the same POST parameters in the original call.  You can combine the offset and limit with the "count" in the first JSON return to build a loop to get all of the records.

 

Each call that is made to the Engagement History API will return a JSON object that contains a value called “Count.” The Count value is the total number of records that are available based on the body parameters you included in your POST.   

 

Creating a While Loop to Aggregate Data

 

A simple “while” loop, will help us return all of the data.  Let’s take a look at how we would do this:

 

 

For now, let’s ignore what I am doing the URL and Body for the POST and focus on the while loop.  

 

Step 1: Before I begin the code which queries the API, I initialize two variables, offset and count. I set these to 0 and 1, respectively.

 

 

 

My while loop is set up so the conditions are immediately met, which brings us to

 

Step 2: The code contained in the while loop runs and  submits a POST request to the API.  I return the JSON to a variable called “ehdata”.  

 

 

I parse the return from the “ehdata” variable into a new variable - “ehdata2”.

 

 

 

The ehdata2 variable is a complex list of lists which I will cover in my next blog post.  The important thing about the data, it contains a value called “Count” in the metadata of the return.  The count will give me the total records which matched the parameters in my POST.

 

Step 3: I immediately update the count variable we created in Step 1 to match what was returned.  Once this is completed, my while loop now knows the total records which need to be returned before it can stop.

 

 

 

Step 4: The offset value will also need to be incremented to reflect we pulled 100 records.  I simply add 100 to the current value of the variable and we are set!

 

 

 

Now, let’s revisit the posturl variable I created.  We have to dynamically build the URL in the POST request. I simply concatenate the different pieces of our query string together into one variable, which I feed into my POST request.

 

Dynamically building this allows me to use the new offset value, which picks up on the next group of records.

 

 

If we simulate what records will return if our “Count” is 250,

 

Query 1 - 0-100 records

Query 2 - 101-200

Query 3 - 201-250 *

 

*Even though we have a limit of 100, the API will pull the remaining records

 

One final comment, because we are reusing the variables to save the data that is returned, we will need to manipulate or create a different variable which concatenates the returns before the while loop executes multiple times.

 

My full code can be found at the following URL:

 

https://github.com/atroiano/Reporting/blob/master/Download/chattranscripttable.R

 

 

More Resources

 

You can read more about the Engagement History API here:Engagement History API PDF

 

Best Practices - Engagement History API: Engagement History API Best Practices