Customer Forum

Workbooks & GatorMail for Transactional Emails - WORKED EXAMPLE

Jamie (Workbooks Online) Posted: 2019-08-16 15:27

This forum post follows on from our previous post which explains how you can use GatorMail to send out an email series such as renewal reminders. We'll use this as a basis for a worked example, laid out below.

Let's assume Acme Inc wants to out a series of subscription renewal reminder emails 1 month before renewal, 1 week before renewal and then again if their subscription expires and the customer hasn't renewed. They'd like to include the renewal date, plus the renewal price in their emails.

Step 1: Create a Workbooks Report

For this example, we will build a Contract report but the Plugin supports any transaction record type, and also Activities.

Firstly, we need to build a Workbooks Report to identify the customers that should receive our email. That's customers where their Contract Start Date is TODAY + 1 Month, TODAY + 1 Week and TODAY, but only if the customer has not already renewed. Typically, a business would create the Contract for a renewal upfront, several months in advance so that a pipeline can be managed in CRM so our report will be looking at "Renewal Contracts", not existing ACTIVE Contracts. We can build this as one Report, and then use Summary Views to split up our data into the different categories.

This post explains how to achieve a list of Contracts where the Start Date is 1 month from TODAY but the same can be applied for the other emails that we want to send by building additional Summary Views in our Report, and then tweaking the criteria.

Create a new Contracts Report by clicking Start > New > Report > Contracts. We'll assume that we have a DLI Custom Field created on our Contract records already created called "Primary Contact" which we will use to add Person information to our report. Add in the following columns:

  • Primary Contact (your field might be called something different) > Id
  • Primary Contact (your field might be called something different) > Email Address
  • Primary Contact (your field might be called something different) > Name
  • Contract Reference (object reference e.g. CON-123)
  • Id (this will be used to update our record after it is processed)
  • Lock version (this will be used to update our record after it is processed)
  • Line Items > Unit Price (this will appear on our email communication) - Name this "Renewal Price"
  • Contract Start Date (this will appear on our email communication) - Name this "Renewal Date"

Add the following calculated columns:

  • A column called "Email Reminder Status" with a value of "1 Month Reminder Sent". NOTE: To keep this example as simple as possible, we have hardcoded the value here as a string so every row on the report will output "1 Month Reminder Sent". You can apply logic to this if you require it using a formula to output different statuses based on other fields stored on your Contract

A column called "Renewal Due" with the following formula:

WHEN start_date = CURDATE() THEN "Expired"
ELSE "Unknown"

Carefully consider your criteria. Workbooks would recommend the following at a minimum:

  • Status = POSTED
  • Type = Renewal
  • Exclude any Contracts where the Primary Contact is NOT blank
  • Exclude any Contracts where the Primary Contact's Email is NOT blank
  • Exclude any Contracts where the field that you are updating as part of this process equals the value output from your calculated column e.g. if you are going to configure your process to update a field called "Email Status" with a value of "1 Month Reminder Sent". You should add criteria to your report to exclude any Contracts where the Email Status is equal to "1 Month Reminder Sent" to avoid sending the same email more than once
  • Exclude any Contracts where the customer has already agreed that they do/don't want to renew - this relies on your business having a method of tracking this on the Contract which could be as simple as a picklist indicating the Renewal Status

Next we'll create a new Summary View, grouped by Contract Reference. Add all of the columns from your Details View into the Summary except for the Unit Price. We need to create a Summary View as we want the total value of the Contract, and only one row for each Contract. If we run our process from the Details view, we could end up sending multiple emails to the same contact for the same Contract - one for each Line Item!

For Unit Price, click Add Value Column > Subtotal Column and then choose our Unit Price. This will give us the total value of our Contract.

Add two report criteria as follows - we only want to only show Contracts where:

  • Renewal Due EQUALS "1 Month"
  • Email Reminder Status is BLANK

This will identify any Contracts where the Start Date is 1 month from today, and where the email has not already been sent.

Step 2: Create a Custom Field

As part of this example, we will configure the script to update a field on our Contract record once an email has been sent to our Primary Contact. To do this, create a new Text Field on "Contract" records and name it appropriately. If you are about to configure a series of email reminders, you may wish to configure this field as a Picklist or even a Checkbox if this is a one time email. Ensure that the column called "Email Reminder Status" created above in Step 1, outputs a value suitable for the Custom Field data type that you are creating e.g. if you decide to create a Checkbox, then your calculated column should output TRUE, rather than a text value.

You will need this field as part of Step 4 in the Workbooks Plugin later.

Step 3: Create a GatorMail Email (Creative)

In GatorMail, before you run the Workbooks Plugin, you should create a GatorMail Email that can be used to send to each Primary Contact (each row) on the Report. In GatorMail, click Creative > Email.

The Workbooks Plugin will only find your GatorMail Email Creative if the name starts with the word "TEMPLATE" (case sensitive) so ensure your creative is named appropriately otherwise you won't be able to complete the Workbooks Plugin configuration fully.

Within your Email, you can use Campaign Merge Fields to pull data from your Workbooks Report Columns into your creative. For this example, add in Merge Fields 1 and 2 to your email (if you are not sure how to achieve this, see here). These Merge Fields will be configured in Step 3 of the Plugin to pull in Renewal Date and Renewal Price from the Report configured above. You can also add in Contact Merge Fields to personalise the email by adding in First & Last Name placeholders. At this time, no other fields are synced to a Contact record in GatorMail so they cannot be used as part of this integration.

Whilst this is a basic example to explain the functionality, more complex features exist in GatorMail of which you may wish to use. Spotler's Knowledge Base has lots of useful guides on how to build out an email creative further. See here for more information.

Step 4: Setup the Process to Send Email

You are now ready to step through the Workbooks Plugin to configure your process. Assuming you have already installed the Plugin, the option for "Gator Transactional Emails" should be visible under "Email & Integration". For each Step in the Plugin, we will configure the following settings:

Plugin Step Step Name Description
1 Select Process For this example, we're assuming it is the first Process that is configured on your database. Therefore this step will be skipped. If you have previously setup other processes using this plugin, you should select "New Gator Process" on this step to setup a new process.
2 Configure Process

There are lots of options on this page, we recommend you fill in the options as follows for this example:

  1. Process Name: Name appropriately so you can identify this process later. For this example, use "Renewal Email - 1 month before"
  2. Processing Batch Size: This setting should be configured based on expected volumes and the frequency that you are planning for this process to run. We'd recommend running "Frequently"
  3. Workbooks Report: Select the Report configured above
  4. Report Column (Person Id): As part of the Report build, you should have added this column to your Report. Select it here
  5. Report Column (Person Email Address): As part of the Report build, you should have added this column to your Report. Select it here
  6. Report Column (Person Name): As part of the Report build, you should have added this column to your Report. Select it here
  7. Gator Instance Name: Available under Your Account > Instance Name in GatorMail
  8. Gator Login Email: The email address that you wish Workbooks to use to authenticate with GatorMail to send emails on your behalf
  9. Gator Password: The password for the above supplied email address. NOTE: Your GatorMail password will expire every 90 days. You will need to login and change the password directly in GatorMail, then manually update Workbooks.
  10. Gator Client Name: Please contact Workbooks Support to obtain this value
  11. Gator Client Secret: Please contact Workbooks Support to obtain this value
  12. Enable Update Record After Email Send? Choose "Yes"
  13. Record Type: For our example, we want to update the Contract record after sending an email. Choose "Contract" here.
  14. Status Field: Select the Custom Field created in Step 2 here.
  15. Report Column (Status Update): As part of the Report build, you should have added this column to your report. Select it here.
  16. Report Column (Record Update Id Column): As part of the Report build, you should have added this column to your report. Select it here.
  17. Report Column (Record Update Lock Version Column): As part of the Report build, you should have added this column to your report. Select it here.

Each of the above options are explained here in detail.

3 Configure Mappings

On this page, let's configure our Renewal Due Date and Renewal Price mappings so that the data from our Workbooks Report can push through to GatorMail when an email is sent, and be personal to the Primary Contact.

  • Click Add New
  • Select the Report Column called "Renewal Due Date"
  • Choose Merge Field 1 from the Gator Merge Field picklist
  • Click "Add" - a new row will be added to the grid

Repeat for Renewal Price.

4 Configure GatorMail Campaign

To configure a new GatorMail Campaign via this integration, there are a few key pieces of information that need to be provided otherwise the creation will fail. Fill in the options as follows for this example:

  1. Gator Campaign Name: Set this to "Renewal Email - 1 Month Before"
  2. Gator Campaign Subject: Set this to "Your renewal is due shortly" - remember this is the subject line of your email seen by the recipient
  3. Gator Email Template: Select the Email Creative you configured in GatorMail as part of Step 3 of this guide
  4. Campaign Reply To Email: For this example, we'll set this to "" but configure this to meet your business requirements. When a user clicks "reply" in their email client this is the recipient used.
  5. Campaign From Name: For this example, we'll set this to "Workbooks Success" but again, configure this to meet your business requirements.
  6. Campaign From Address: Enter in a valid from address as configured in GatorMail as available under Tools > System Defaults > Sender E-Mail Address (MAIL-FROM). This guide assumes a domain is already configured thus simply type this value into the form. NOTE: If not configured in your instance, the Campaign creation will fail and you will not be able to use this process.

Each of the above options are explained here in detail.

5 Complete & Next Steps

This page should show a success page. Simply click "Copy to Clipboard" and move to Step 4 in this guide below. You can close the Plugin window now.

NOTE: If there were any errors, you may need to investigate these or raise them with Workbooks Support, prior to completing the remaining setup in this guide.

Step 4: Update your Workbooks Report

The final step once you have completed the Workbooks Plugin wizard, the last step of the configuration is to update your Workbooks Report to include the GatorMail Campaign Id as a new column. Without this column, the integration cannot send an email via GatorMail.

Create a new Calculated Column called "Gator Campaign Id" in your Report Summary View. You should have clicked "Copy to Clipboard" on the last page of the Workbooks Plugin. Paste the example formula into the Calculated Column - it will look something like the below:

# Gator Campaign Name: Testing Transactional Emails (Gator Campaign Id: 699)


We need to make some changes to this before we can save the Report Column based on our business needs. The example provided is a CASE statement and allows you to apply logic to which GatorMail Campaign Id is used. In our example, we are planning to send several different emails, with different email content so we could update the formula to output different Campaign Ids based on the logic we define. For example, if you had multiple brands, you could use a separate GatorMail Campaign for each brand, but use the same Workbooks Report. Assume we have 2 Brands (Brand 1 & Brand 2), our Contract tracks which Brand in a field simply called "Brand" which is a Picklist, so we may amend out formula as follows to account for Brand 1 and Brand 2 like so:

CASE cf_customer_contract_brand
WHEN "Brand 1" THEN 699

To keep things simple however, we don't need to worry about using any of this logic. Our formula simply needs to output a single Campaign Id of 699 which was returned from GatorMail when we created a Campaign. Simply remove all of the formula, and leave the 699 (the Campaign Id) in place. We'd also recommend leaving the formula comment in place (the text after the "#" so you know what Campaign this Id refers to if you need to amend it later),

# Gator Campaign Name: Testing Transactional Emails (Gator Campaign Id: 699)

Save the Report Column, then the Report.

Step 5: Test Email & Review Schedule

You should check what was created in GatorMail to ensure that everything was completed successfully. The Plugin should have created the following on your behalf in GatorMail:

  • A GatorMail Campaign as follows:
    • Check the Campaign Name
    • Sender Alias & From Address
    • Reply to Address
    • Check that the Campaign has been initiated automatically - your user will be added as a Contact to GatorMail and appear under the "Audience" tab.
      • NOTE: GatorMail Groups are not used as part of this integration after the Campaign has been initiated. Emails sent from this Campaign can be seen under "View Results" and also by searching for the Contact in GatorMail. You can also build separate reports in Workbooks based on the field created in Step 2 of this guide.
    • Under the "Emails" tab, the selected Email Creative should be visible

After you have completed this setup, GatorMail will send the user that configured the process a test email automatically. This can happen fairly quickly, or it could take a few minutes to arrive. Once you have received this email, your process is ready to be used. NOTE: The test email received will not have the Merge Fields added, these will be blank.

The Scheduled Process that is created as part of this configuration is setup with a default schedule. You may wish to visit this and enable it for every day (by default, the process is disabled on weekends) and also change the frequency to suit your needs.

If you wish to configure further emails in this series, repeat all of the above steps. For the Workbooks Report, you can create a new Summary View and tweak the criteria. Any subsequent processes can update the same field created in Step 2 so you may not necessary need separate fields either.