Database Connectors

Database Connectors let you pull custom datasets directly from your database into AgencyAnalytics dashboards and Client reports. This means you can report on the exact metrics that matter most to your agency or your Clients, beyond what standard platform integrations offer.

Connect to Google BigQuery, MySQL, Amazon Redshift, PostgreSQL, or Snowflake
Reuse a single database connection across multiple Clients without re-entering connection details
Write custom SQL queries to control exactly what data appears in a widget

Use Row Level Security (RLS) with Google BigQuery so users only see data their Google account is authorized to access through the connector integration.

Before You Start

AgencyAnalytics does not currently support connecting to Azure SQL
We strongly recommend having a technical resource on hand to set up your database connector integration and write your queries
The AgencyAnalytics Support Team cannot assist with your database setup or with writing queries
Once you've connected a database instance, you can reuse that same connection for any other Client. Just select the existing connection when adding the data source instead of re-entering its details

Set Up Database Connector

Each database type has its own connection steps. Follow the guide for the database you use:

Once a database instance is connected, it becomes available to add as a data source on any other Client in your account without reconnecting.

Add and Query a Database Connector Widget

Database Connector widgets work differently from other integration widgets. Instead of automatically pulling standard metrics, you write a SQL query that specifies exactly what data the widget should display.

The steps below use Google BigQuery as an example, but the same steps apply to all database connectors.

Step 1: Open a report or dashboard in edit mode.

Step 2: In the sidebar on the right, click Integration Metrics at the top.

Step 3: Select your Database integration (for example, Google BigQuery) from the list.

Step 4: Locate the widget format you want to add, then click, hold, and drag it over to the section. Release it to add the widget.

Step 5: After you add the widget, the edit widget sidebar automatically switches to the Data tab. If it doesn't, click the widget, then click the Data tab on the right.

Step 6: Enter your SQL query in the Query field.

Step 7: Click Update Query to pull the data from your database instance into the widget

If your SQL has an error, a message describing the issue appears above the Update Query button.

Query Examples

Use these example queries as a starting point for your own Database Connector widgets. Replace the test variables (like table and field names) with your own.

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 query below 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 😄

Understanding Database Connectors