Customer Forum

Reporting on GatorMail Mailshot Performance

Sam (Workbooks Online) Posted: 2018-07-13 18:45

GatorMail Mailshot Performance Report

This provides an example Report that you can build to provide an overview of the performance and success of each of your individual Mailshots. This Report looks at metrics such as unique click throughs, click to open rate, and Deliverability Rate allowing you to adapt future Mailshots based on the performance of previous Mailshots.

To build this Report you will need to go to Start > New > Report > Pre-populated Reports > Integrated Mailshot Report. Give the Report a suitable name such as “GatorMail Mailshot Performance” and save the Report.

On the Details Tab you will need to add the following Columns:

Unique hits
Emails delivered
Unique emails opened
Attempted Sends
Hard bounced emails
Soft bounced emails
Undelivered emails
Unique user sends

Once these Columns have been added you can now add the following Calculated Columns:

Click Through Rate

(count_total_unique_user_hits/ count_total_delivered)*100

Click to Open Rate

(count_total_unique_user_hits/ count_total_unique_user_opens)*100

Deliverability Rate

(count_total_delivered/ count_initial_send)*100

NOTE: Each of these calculated columns can be displayed as a percentage by using the Advanced tab and selecting Display as percentage.

GatorMail Campaign Performance Report

This Report is designed to look at the overall performance of a Campaign, which may consist of multiple Mailshots and can be used to assess not only the overall performance but additionally which Mailshot had the most interactions. This Report allows you to review which of the Mailshots within a single Campaign was the most effective allowing you to use this information in the design of Mailshots in the future helping drive more traffic and interactions in the future.

This Campaign is built at the start to be identical as the Mailshot Performance Report, however, in this case you would add the following additional columns to the Details Tab:

Marketing Campaign > Name
Marketing Campaign > Marketing Campaign reference

You could also add another column of Marketing Campaign > Status if you wanted to segregate Active and Completed Campaigns.

Once all of the columns have been added you will now need to create a Summary View, setting the Marketing Campaign reference column as your grouping column and Campaign Name as a value column.

You can now create a number of subtotal columns to show you the key metrics you are interested in such as:

Total Emails Sent 
Total Emails Delivered
Total Emails Opened (Unique)
Total Clicks (Unique)
Total Hard Bounced Emails
Total Other Bounces
Total Soft Bounced Emails
Total Undelivered Emails
Total Unique user sends
Total Unsubscribes

This will give you an initial impression of how effective the overall campaign information is. In order to see the Click Through Rate, Click to Open Rate and Deliverability Rate you will need to create a Summarised Column and use the Average operator in order to give you the overall percentage rate.


Additionally, you could create Average Summarised columns to look at the Average number of click throughs, however, unless each mailshot is being sent to the same sized mailing list this may not provide quality information.

It is however, possible to see which of your Mailshots was the best performers within the Campaign through calculated columns.

For example, to see which Mailshot had the most Opens you can use the following formula:

SUBSTRING_INDEX(GROUP_CONCAT( PARENT('Name') ORDER BY PARENT('Emails Opened (Unique)') DESC SEPARATOR 'abc123'), 'abc123',1)

This same formula can be applied to see whichever Campaign was the most successful in any given scenario providing that it follows the same format changing the ORDER BY to be the metric in question.

For example:

Campaign with the highest Open Rate


Campaign with the highest Emails Delivered


Conversely, if you wanted to see which Campaign had the lowest metric, such as the least Undelivered Emails, you will need to change the DESC operator to an ASC so it will follow:

Campaign with the least Undelivered Emails

SUBSTRING_INDEX(GROUP_CONCAT( PARENT('Name') ORDER BY PARENT('Undelivered Emails') ASC SEPARATOR 'abc123'), 'abc123',1)

In this case, the SEPATOR ‘abc123’ is being used to separate each of the Campaigns with a string of abc123. The following SUBSTRING_INDEX function removes everything after the first abc123, meaning that only the first result is shown. This is either the highest or lowest performer depending on whether the DESC or ASC operator is used respectively. More information on this can be found in our Forum.