This post will help you sync your Zendesk data with Amazon Redshift. By doing so, you will be able to perform advanced analytics on a system designed for this type of data, such as Amazon Redshift. Alternatively, you can simplify the process of syncing data from Zendesk to Amazon Redshift usingrudder stack, where RudderStack will take care of the whole process and you can focus on what matters, analyzing your data.
Pull your data from Zendesk
Zendesk APIs are not specific to data extraction, Zendesk provides over a hundred different APIs for you to integrate with. So you can easily manage your users, increase your team's productivity, and create seamless integrations. You can create integrations or even enrich Zendesk with data from external sources. The Zendesk API is a RESTful API that can be accessed over HTTP.
As a RESTful API, interaction with it can be accomplished using tools like CURL or Postman or by using HTTP clients for your favorite language or framework.
Some suggestions:
- Apache HttpClient para Java
- Client spray for Scala
- hyper for rust
- Ruby rest client
- Python http-cliente
In addition, Zendesk provides several SDKs and libraries so that you can access the API of the framework of your choice without having to deal with the technicalities of HTTP. API clients are available for the following languages:
Zendesk API authentication
The Zendesk API is an SSL-only API, regardless of how your account is configured. you must be averified userto make API requests. You can authorize in the API using basic authentication with your email address and password, with your email address and an API token, or with an OAuth access token.
Zendesk rate limiting
The API is rate limited. It only allows a certain number of requests per minute, depending on your plan and endpoint. Zendesk reserves the right to adjust the rate cap for certain endpoints in order to provide high-quality service to all customers. The current limits are as follows:
Pagination
By default, most list endpoints return a maximum of 100 records per page. You can change the number of records per request by passing a per_page parameter in the request URL parameters. Example: per_page=50. However, it cannot exceed 100 records per page on most endpoints.
When the response exceeds the maximum per page, you can loop through the records by incrementing the page parameter. Example: page=3. List results include the URLs of the next page and the previous page in the response body for easy navigation:
Endpoints and available resources
The Zendesk REST API exposes a large number of functions and endpoints that allow the user to interact with the platform in all possible ways. Thus, it is possible to create new applications on the Zendesk platform, integrate external systems to it and, of course, extract data from the platform. The most important characteristics are the following:
- Tickets that your customers create through Zendesk.
- Ticket events. Changes in tickets.
- Organizations.
- Users.
- Input metrics. These are metrics related to your tickets.
- Data related to the Net Promoter Score.
- Articles
Let's say we want to get all the tickets we have in Zendesk. To do this, we need to make a GET request to the appropriate endpoint, like this:
JAVASCRIPT
TO TAKE /API/v2/incremental/Appetizer.json?start time=1332034771
JAVASCRIPT
rizo https://{subdomain}.zendesk.com/API/v2/incremental/Appetizer.json?start time=1332034771\
-v-you{Email address}:{password}
And an example response:
JAVASCRIPT
state: 200 OK
{
"End of time": 1383685952,
"next page": "https://{subdominio}.zendesk.com/api/v2/incremental/tickets.json?start_time=1383685952",
"say": 1,
"Appetizer": [
{
"Url address": "https://{subdominio}.zendesk.com/api/v2/tickets/1.json",
"I was going": 2,
"created in": "2012-02-02T04:31:29Z",
"generated_timestamp": 1390362285
...
},
...
]
}
A complete ticket object can contain the following fields:
Zendesk API results are always in JSON format. The API gives you the opportunity to get very granular data about your accounting activities and use it for analysis and reporting purposes.
About Zendesk
Zendeskprovides a cloud-based customer service platform that includes ticketing, self-service options, and customer support capabilities. Zendesk focuses on improving communication between your customers and your business. Gather all customer communication in one place. The supported communication channels are:
- Correspondence. Zendesk helps you organize all the email you receive from your customers.
- Social. You can connect your Facebook and Twitter accounts to Zendesk.
- Voice. Answer customer calls from Zendesk.
- Chat. Zopim Chat allows you to communicate with your customers from within your product.
Zendesk help desk software helps simplify customer service with time-saving tools like triggers and automation. And it's intuitive, built with the experience of customer service and support agents in mind. Some important features of the ticketing system that Zendesk offers are:
- Helps to better resolve tickets with teammates through a collaborative environment where information can be shared.
- Get access to relevant information on all computers. Zendesk comes with a built-in knowledge base that allows agents to quickly refer to information and processes without losing their place.
- Turn everyone within your company into a support agent. With lightweight agents, everyone in your company can view tickets and make private comments.
Additionally, Zendesk offers a suite of analytics tools to help you get closer to the user through data. With these tools you can:
- Gain visibility into customer interactions
- Measure the performance of your team
- See the business impacts of a great service
You can track a large number of metrics related to your customers, support teams, and your business.
But whether you want to run more compelling analytics on your Zendesk data or merge your customer service-related data with data from other sources, like your database and transactional logs, Zendesk exposes a rich ecosystem of APIs and tools that can use to access and extract your data among other functionalities.
Prepare your Zendesk data for Amazon Redshift
Amazon Redshift is based on industry-standard SQL with additional functionality to handle very large data sets and perform high-performance analytics. So to load your data, you'll need to follow your data model, which is a typical relational database model. The data you pull from your data source must be mapped to tables and columns. Where you can think of the table as a map of the resource you want to store and the columns the attributes of that resource. Additionally, each attribute must comply with the data types supported by Redshift, currently the supported data types are as follows:
- LITTLE
- WHOLE
- STARTING
- DECIMAL
- REAL
- DOUBLE PRECISION
- BOLEANO
- CHARACTERS
- VARCHAR
- DATA
- TIME STAMP
Since your data probably comes in a JSON-like representation that supports a much smaller range of data types, you should be very careful about the data you feed Redshift and make sure you map your types to one of thedata types that are supportedby redshift. Designing a schema for Redshift and assigning data from your data source to it is a process that should be taken seriously, as it can affect the performance of your cluster and the questions it can answer. It is always good to keep in mind theBetter practicesthat Amazon posted about designing a Redshift database. When you have finished designing your database, you need to load your data into one of the data sources supported as input by Redshift, which are the following:
About Amazon Redshift
Amazon Redshiftis one of the most popular data storage solutions that is part of Amazon Web Services (AWS) ecosystem. It is a petabyte-scale data storage solution that is fully managed as a service solution running in the cloud. It is based on SQL and you can communicate with it as you would with PostgreSQL, in fact you can use the same driver, although it is better to use thedriversrecommended by amazon You can connect through JDBC or ODBC connections.
Upload data from Zendesk to Redshift
The first step in loading your Zendesk data into Redshift is to put it in a source that Redshift can pull from. As mentioned above, there are three main supported data sources, Amazon S3, Amazon DynamoDB, and Amazon Kinesis Firehose, with Firehose being the most recent addition as a way to get data into Redshift.
To upload your data to Amazon S3, you'll need to use the AWS REST API, as we see again that APIs play an important role in both extracting and loading data into our data warehouse. The first task you should perform is to create abucket, it does this by performing an HTTP PUT to the Amazon AWS REST API endpoints for S3. You can do this using a tool like CURL or Postman. Or use thelibrariesprovided by Amazon for your favorite language. You can find more information by reading theAPI Referencefor bucket operations in the Amazon AWS documentation.
Once you've created your bucket, you can start sending your data to Amazon S3, again using the same AWS REST API but using endpoints for object operations. As with Bucket, you can access HTTP endpoints directly or use your preferred library.
DynamoDB imports data back from S3, adds another step between S3 and Amazon Redshift, so if you don't need it for other reasons, you can skip it.
Amazon Kinesis Firehose is the latest addition as a way to get data into Redshift and offers a streaming approach to importing data. The steps required to add data to Redshift through Kinesis Firehose are as follows:
- create a delivery flow
- add data to stream
Every time you add new data to the stream, Kinesis takes care of adding that data to S3 or Redshift, going through S3 again, in which case it's redundant if your goal is to move your data to Redshift. The execution of the previous two steps can be done through the REST API or through your favorite library, just like in the previous two cases.
The difference here is that to send your data to the stream you will use aKinesis Agent.
Amazon Redshift supports two methods for loading data into it. The first is by calling an INSERT statement. You can connect to your Amazon Redshift instance with your client using a JDBC or ODBC connection, and then run an INSERT command on your data.
JAVASCRIPT
insert in scenario_categoryvalues
(12, 'Concerts', 'Comedy', 'All stand-up comedy performances');
The way to call the INSERT command is the same as you would with any other SQL database, for more information you can refer to theINSERT example pagein the Amazon Redshift documentation.
Redshift is not designed for INSERT operations, rather the most efficient way to load data into it is to bulk load it using a COPY command. You can run a COPY command on data that lives as flat files inS3one by oneAmazon DynamoDBtable. When you run COPY commands, Redshift is capable of reading multiple files simultaneously and automatically distributes the workload to the cluster nodes and runs the load in parallel. as a commandCOPY OFit's quite flexible and allows for many different ways of using it depending on your use case. Making a COPY to Amazon S3 is as simple as the following command:
JAVASCRIPT
copy list
of 's3://mybucket/data/listing/'
credentials'aws_access_key_id=;aws_secret_access_key=';
For more examples on how to invoke a COPY command, you can refer to theCOPY examplespage in the Amazon Redshift documentation. As in the INSERT case, the way to run the COPY command is by connecting to your Amazon Redshift instance using a JDBC or ODBC connection and then calling the desired commands using theSQL referencefrom the Amazon Redshift documentation.
The best way to load Zendesk data into Amazon Redshift and possible alternatives
So far, we've only scratched the surface of what you can do with Amazon Redshift and how to load data into it. How you proceed largely depends on the data you want to load, what service it came from, and your use case requirements. Things can get even more complicated if you want to integrate data from different sources.
A possible alternative, instead of writing, hosting, and maintaining a flexible data infrastructure, is to use a product like RudderStack that can handle this type of problem automatically.
RudderStack integrates with various sources or services such as databases, CRM, email campaigns, analytics, and more. Quickly and securely move all your data from Zendesk to Redshift and start generating insights from your data.
Sign up for free and start sending data
Try our event flow, ELT, and reverse ETL pipelines. Use our HTTP feed to send data in less than 5 minutes or install one of our 12 SDKs on your website or app.