Google BigQuery Integration

1 Short description
2 Installation
3 Usage
- 3.1 Mailing Summary Statistics
  - 3.1.1 master
  - 3.1.2 mailings
  - 3.1.3 totals
- 3.2 Extended Detail Reporting
  - 3.2.1 response
  - 3.2.2 response_open
  - 3.2.3 response_click
  - 3.2.4 response_bounce
  - 3.2.5 response_unsubscribe

Short description

This integration allows you to synchronize Maileon statistics to Google BigQuery. There are two levels of statistics that can be synchronized with this integration:

contact level response information
mailing summary statistics

Installation

The plugin settings can be customized in Maileon by navigating to Settings/Plugins.

The plugin settings dialog

The integration has the following options:

Option	Description

Option	Description
Maileon API key	An API key in the account that is enabled and active, with at least Read access.
Synchronization Start Date	The date the mailing level statistics are synchronized from. If not specified it is assumed to be the first day the plugin is enabled.
Google User Email Address	The Google user that should be granted access to the dataset in BigQuery.
Google Groups	The Google group that should be granted access to the dataset in BigQuery.
Google Service Accounts	The Google service account that should be granted access to the dataset in BigQuery.
Extended detail reporting	Whether to enable synchronizing contact level response information.
Export of email address with extended reporting	Whether the contact level response information should contain email addresses.
Google Cloud Location	The Google Cloud Location to use for the dataset. This is important if you are planning to use the Maileon dataset with other datasets or tools already present in a location. Set to EU to provide access for all `europe-` regions.

Usage

After the plugin has been successfully enabled in an account the datasets will be created in 5-15 minutes. The entities defined by Google User Email Address, Google Groups, Google Service Users will be given access to the dataset. The dataset is contained in the project maileon-for-bigquery and it is named xqueue_<cloud location>_<maileon account id> You can find more information about the synchronized data below.

Mailing Summary Statistics

This is the default mode for synchronizing data to Google BigQuery. These views are updated daily and provide mailing level statistics for each mailing.

master

This view contains a row for each mailing for each day in the synchronized time interval.

Field name	Type	Description

Field name	Type	Description
date	DATE	The date these statistics are aggregated for. For example a `date` of `2025-02-10` means that the recipients/opens/clicks/unsubscriptions are aggregate numbers for events that happened between `2025-02-10 00:00:00` and `2025-02-11 00:00:00`.
mailing_id	INTEGER	The ID of the mailing.
mailing_name	STRING	The display name of the mailing.
mailing_subject	STRING	The subject line of the mailing.
mailing_type	STRING	The type of the mailing. Valid values: `doi`, `trigger`, `regular`.
mailing_schedule_time	DATETIME	The time the mailing was dispatched.
mailing_tags	STRING	A list of mailing tags separated by `#`.
recipients	INTEGER	The number of recipients.
opens	INTEGER	The number of opens.
clicks	INTEGER	The number of clicks.
bounces	INTEGER	The number of bounces.
unsubscriptions	INTEGER	The number of unsubscriptions.

mailings

This view aggregates the summary statistics for each mailing in the synchronized time interval. See the master view for field descriptions.

Field name	Type

Field name	Type
mailing_id	INTEGER
mailing_name	STRING
mailing_subject	STRING
mailing_type	STRING
mailing_schedule_time	DATETIME
mailing_tags	STRING
recipients	INTEGER
opens	INTEGER
clicks	INTEGER
bounces	INTEGER
unsubscriptions	INTEGER

SELECT mailing_id,
       mailing_name,
       mailing_subject,
       mailing_type,
       mailing_schedule_time,
       any_value(mailing_tags) AS mailing_tags,
       sum(recipients) AS recipients,
       sum(opens) AS opens,
       sum(clicks) AS clicks,
       sum(bounces) AS bounces,
       sum(unsubscriptions) AS unsubscriptions
FROM `maileon-for-bigquery.xqueue_<your dataset>.master`
GROUP BY mailing_id,
         mailing_name,
         mailing_subject,
         mailing_type,
         mailing_schedule_time
ORDER BY mailing_schedule_time DESC

totals

This view aggregates the summary statistics for each day in the synchronized time interval. See the master view for field descriptions

Field name	Type

Field name	Type
date	DATE
recipients	INTEGER
opens	INTEGER
clicks	INTEGER
bounces	INTEGER
unsubscriptions	INTEGER

SELECT date, sum(recipients) AS recipients,
             sum(opens) AS opens,
             sum(clicks) AS clicks,
             sum(bounces) AS bounces,
             sum(unsubscriptions) AS unsubscriptions,
FROM `maileon-for-bigquery.xqueue_<your dataset>.master`
GROUP BY date
ORDER BY date DESC

Extended Detail Reporting

This is an advanced option for synchronizing Maileon data to Google BigQuery. These views will only be created if the Extended detail reporting option is enabled. The data contains contact level response information updated hourly.

response

This view contains a row for each response event (open, click, bounce, unsubscription) in the synchronized time interval.

Field name	Type	Description

Field name	Type	Description
mailing_id	INTEGER	The mailing ID for the event.
type	STRING	The type of the event. Valid values: `open`, `click`, `bounce`, `unsubscription`
contact_id	INTEGER	The contact ID for the event.
external_id	STRING	The external ID of the contact for the event.
datetime	DATETIME	The time this event happened.
email	STRING	The email address of the contact for the event. This field is `NULL` if Export of email address with extended reporting is disabled.
link_id	INTEGER	The ID of the link clicked for a `click` type response, `NULL` otherwise.
link_url	STRING	The URL of the link clicked for a `click` type response, `NULL` otherwise.
link_tag	STRING	A list of tags of the link for a `click` type response, `NULL` otherwise. The list is separated by `,`
bounce_code	STRING	The bounce code for a `bounce` type response. e.g.: `5.5.0`

response_open

This view filters the response statistics for open events. See the response view for field descriptions

Field name	Type

Field name	Type
contact_id	INTEGER
external_id	STRING
email	STRING
datetime	DATETIME
mailing_id	INTEGER

SELECT contact_id,
       external_id,
       email,
       datetime,
       mailing_id
FROM `maileon-for-bigquery.xqueue_<your dataset>.response`
WHERE TYPE = 'open'

response_click

This view filters the response statistics for click events. See the response view for field descriptions

Field name	Type

Field name	Type
contact_id	INTEGER
external_id	STRING
email	STRING
datetime	DATETIME
mailing_id	INTEGER
link_id	INTEGER
link_url	STRING
link_tag	STRING

response_bounce

This view filters the response statistics for bounce events. See the response view for field descriptions

Field name	Type

Field name	Type
contact_id	INTEGER
external_id	STRING
email	STRING
datetime	DATETIME
mailing_id	INTEGER
bounce_code	STRING

response_unsubscribe

This view filters the response statistics for unsubscribe events. See the response view for field descriptions

Field name	Type

Field name	Type
contact_id	INTEGER
external_id	STRING
email	STRING
datetime	DATETIME
mailing_id	INTEGER

Maileon Standard Integrations