How to show MySQL data in Zendesk

In this tutorial we're going to explain step-by-step how to show MySQL data in Zendesk with FactBranch. We'll walk you through connecting FactBranch to your MySQL database, writing an SQL query in FactBranch and generating a nice data display for FactBranch's Zendesk app.

Intro to MySQL and Zendesk

MySQL is a free, open source relational database management system. It's a popular choice for online shops like Magento or WooCommerce, or your custom built web application.

If you're delivering customer service via Zendesk Support and have customer data in a MySQL database, chances are your agents need to see that data to answer tickets. You should show them the data directly in Zendesk with the FactBranch app.

Connect FactBranch to your MySQL database

If you haven't done so already, create a FactBranch account first. Then go to your account dashboard and click on data sources. Click on add data source and select MySQL to create a MySQL data source in FactBranch.

Connect FactBranch to a MySQL database

Fill out the form and click "Connect". FactBranch will try to connect to your database.

Allowing access in a firewall

If your database sits behind a firewall and you have to allow access for a specific IP address, enable "Use a static IP when making database requests" and allow access from the IP that's displayed in the yellow box.

Creating a database user

We recommend you create a MySQL user specifically for FactBranch, that only has read access on the tables you want to query. Here is the very simple MySQL code that should get you started in the right direction:

CREATE USER 'factbranch'@'yourhostname' IDENTIFIED BY 'yourpassword';

This will create a user called factbranch with the password yourpassword. Be sure to replace yourhostname and yourpassword with your own values.

Next we allow that user to run SELECT queries on the table customer in the database yourdatabase.

GRANT SELECT ON yourdatabase.customer TO 'factbranch'@'yourhostname';

This is just a quick intro and you should consult with your database admin on your specific requirements. You can find a more in-depth look at creating MySQL users here.

Create a data pipeline that displays data in Zendesk

Now that you've connected FactBranch to your MySQL database, it's time to create a data pipeline. Head to the Flows dashboard, click on Create flow and select Show data in the FactBranch Zendesk app.

Data pipelines are called Flows in FactBranch and allow you to run different nodes. So for example your first node could query your database and the next node specifies how that data is displayed in the Zendesk app.

In order for the Flow to run, it first has to be triggered. This Flow is triggered when you open the FactBranch app in Zendesk. This is exactly what we're going to do to collect some test data from a real Zendesk ticket. We can later use this as test data for placeholders inside your MySQL query.

Collect sample data from a Zendesk ticket

First, inside your newly created Flow, find the box that's called "Zendesk App Trigger" and click on Config.

Follow the instructions under "Installing the Zendesk App" to install the FactBranch app from the Zendesk marketplace.

Once the app is installed, let's record some test data from a real ticket. In Zendesk Support open a ticket or - if you're using Zendesk Sell - a deal and make sure the FactBranch app is loaded. At this point it will not display anything because we haven't actually built the data pipeline to load data.

Now return to the trigger config in FactBranch and next to Incoming data click on refresh. Select one of the requests and you should see data like email address or custom fields. Click on Use as test data to make this the primary incoming test data for this Flow. The next node in the pipeline will use this as its input and you can use all the values you see here as placeholders.

Incoming data with record requests activated

Add the MySQL node to your data pipeline

Once you have selected sample data, you will need to add a MySQL node to your data pipeline. Return to the Flow page, click on the Create Node button and select MySQL. Drag the MySQL node from the unused nodes pile between trigger and Zendesk App Display node. Your Flow should now look like this:

Flow with Zendesk trigger, MySQL, Zendesk node

Open the MySQL node by clicking on edit. Then, in order for the node to use the credentials you've entered earlier, click on Authentication > Select a data source... and select MySQL.

Now return to the Query tab and enter your SQL query.

A typical query to find a row in the customers table by email address would look like this:

SELECT * FROM customers WHERE email=$data.email

Note that we're using the placeholder $data.email. This will substitute in the value of email from the input data of this node.

In the following screen it will replace the placeholder with test@example.com before running the query. All values are of course escaped to guard against SQL injections.

A screenshot of the MySQL editor

Design the output for the Zendesk app

Once you're happy with the data your query generates, move on to the last node Zendesk App Display.

You'll see that this node uses the output data from the MySQL node as its input data.

Click Generate template to automatically generate an HTML template from that input data. This will now look something like this:

A generated HTML template in the Zendesk App Display node

Of course you can edit the HTML code as you like. We use a Liquid-style templating language. For a more detailed explanation on what you can do with templating head over to our docs on the Zendesk App Display node.

Check the results in Zendesk

Once you're happy with the preview, head back to Zendesk and reload the ticket. You should now see results from your MySQL query directly inside the ticket sidebar or - if you're using Zendesk Sell - next to your deals and contacts.