Versions Compared

Version Old Version 1 New Version Current
Changes made by

Viktor Balog

Lukas Sadurski

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

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

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

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

Code Block
languagesql
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

...

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

Field name

Type

date

DATE

recipients

INTEGER

opens

INTEGER

clicks

INTEGER

bounces

INTEGER

unsubscriptions

INTEGER

Code Block
languagesql
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

...

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

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

contact_id

INTEGER

external_id

STRING

email

STRING

datetime

DATETIME

mailing_id

INTEGER

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

...

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

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

Code Block
SELECT contact_id,
       external_id,
       email,
       datetime,
       mailing_id,
       link_id,
       link_url,
       link_tag
FROM `maileon-for-bigquery.xqueue_<your dataset>.response`
WHERE TYPE = 'click'

...

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

Field name

Type

contact_id

INTEGER

external_id

STRING

email

STRING

datetime

DATETIME

mailing_id

INTEGER

bounce_code

STRING

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

...

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

Field name

Type

contact_id

INTEGER

external_id

STRING

email

STRING

datetime

DATETIME

mailing_id

INTEGER

Code Block
languagesql
SELECT contact_id,
       external_id,
       email,
       datetime,
       mailing_id
FROM `maileon-for-bigquery.xqueue_<your data>.response`
WHERE TYPE = 'unsubscribe'

...