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.
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.
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.
Fill out the form and click "Connect". FactBranch will try to connect to your database.
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.
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
sure to replace
yourpassword with your own values.
Next we allow that user to run
SELECT queries on the table
customer in the
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.
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.
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.
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:
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
email@example.com before running the query. All values are of course escaped to
guard against SQL injections.
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:
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.
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.