The AgencyAnalytics user connecting MySQL will need the following information from your MySQL instance: Host, Port, User, Password, and Database. The User entered must have read permissions for the entered database.
How to Connect MySQL
Database connectors like MySQL are an optional add-on that are available for purchase on all plans.
Once purchased, navigate to the campaign where you'd like to connect MySQL, then click Integrations on the menu to the left.
From the integrations page, either search MySQL in the upper right search box or click Database, then MySQL.
On the next page, click Connect Account in the bottom left.
From here, click Connect New Account at the bottom to authorize a new connection. If you have previously connected a MySQL instance, this should be included in the connection list.
On the next page enter the connection details for your MySQL database. Once finished, click Save in the bottom left to add this MySQL instance to your account.
Your MySQL instance can now be found in the list of available connections. Click the circle to the left of the instance name, then click Connect in the bottom left to finalize the connection.
Once done, the selected instance will be connected within your campaign, and you can start using MySQL widgets on dashboard and reports!
How to use MySQL widgets
First open a report or dashboard in edit mode, then in the widget sidebar to the right scroll down until you find MySQL. Click MySQL to expand the widget menu.
Once expanded all available widget types will be listed. Locate the widget format you want to add then click, hold, and drag the widget over to the section and release to add the widget.
Unlike other integration widgets, MySQL must be told via query what data to pull and display.
After adding a MySQL widget the edit widget sidebar will automatically update to the Data tab or you can click the widget then the Data tab on the right.
In the Query field enter in the MySQL query, then click Update Query to pull the data from your MySQL instance into the selected widget.
Not sure where to start with MySQL queries? Click here to view MySQL query examples!
If there's an issue with the SQL an error message will display above the Update Query button specifying the nature of the error.
Remember to click save in the upper right of the dashboard or report to save your query.
Simple Query Example
The below query can be used to pull in data from MySQL. Simply change the test variables to your own!
SELECT
my_field,
SUM(number)AS total
FROM
`my_table`
GROUP BY
my_field
ORDER BY
total DESC
LIMIT
10;
Time Series Example
The below query can be used to pull in time series data, simply change the test variables to your own!
SELECT
DATE(date) as date,
count(*) as my_field_count
FROM
`my_table`
WHERE
$__dateFilter(date)
GROUP BY
date
ORDER BY
date ASC;