Database Connectors
AgencyAnalytics offers seamless integrations to a collection of database integrations, including Google BigQuery, MySQL, Amazon Redshift, PostgreSQL and Snowflake.
Note: Currently, AgencyAnalytics does not support connecting to Azure SQL.
Database connections are reusable across your account. After you connect an instance once, you can add it to any other Client without re-entering its details - just select the existing connection when adding the data source.
These integrations allow you to pull custom data sets directly into your agency's dashboards and Client reports, enabling thorough and effective reporting on the metrics that matter most. Additionally, the platform supports Row Level Security (RLS) for Google BigQuery, ensuring that only authorized data is accessible based on the user’s Google account permissions.
Note: We strongly recommend that you have a technical resource available to set up your database connector integration. The AgencyAnalytics Support Team cannot assist with your setup or writing queries.
How to use database integration widgets
First, open a report or dashboard in edit mode. Then, in the sidebar to the right, click Integration metrics at the top, before selecting the Database integration from the list.
In this example, we use Google BigQuery. However, the same steps apply to all database connectors.
Locate the widget format you want to add, then click, hold, and drag the widget over to the section and release it to add the widget.
Unlike other integration widgets, database connector widgets must be told via query what data to pull and display.
After adding a widget, the edit widget sidebar will automatically update to the Data tab. If needed, you can click the widget and then the Data tab on the right.
Enter the query in the Query field, then click Update Query to pull the data from your database instance into the selected widget.
If the SQL is faulty, an error message describing the nature of the error will appear above the Update Query button.
Simple Query Example
The query below can be used to pull data from a database connector. 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 Query Example
The below query can be used to pull in time series data by replacing the test variables with 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;
💬 Need additional help?
If you have any questions, please contact our friendly support team by following these instructions! We're available 24/5 to help 😄