Notify Service

Root > 3. Customisation
How To - Email Campaign Campaign Series
  Use the Database Triggered Emails/Alerts to build email campaigns or to take consistent actions when a condition merits the action.

Specific emails templates can be triggered to go as soon as a condition changes such as a new lead is received from your website or you are still waiting for paperwork to be returned from a Client and the due date is near or has passed.

Routine emails can be sent out at specific intervals such as anniversaries to contacts identified in the database. Once a 
contact is assigned to a campaign, specific emails are sent out at predetermined intervals as the contact is moved through the steps of the campaign.

Consider that some emails could be sent as soon as a field value changes perhaps in the case of a new lead from your website that was just created into your database. 

Subsequent emails would be sent every day at set times.

To build an email campaign, the following fields need to be created in the contact table:
  • EmailCampaign this is a varchar field containing the names of various campaigns your team will want to automate. Place the field somewhere on the contact.html layout, either on the main contact.html template, or one of the tabs, like Status.html. This field would be assigned a value that would indicate the contact is part of an email campaign. You may also want to have a number of fields that can cause the email campaign to stop running for example a field called DripFeed with a value of Active / Completed. Other fields such as Pause could be a simple Yes/No checkbox, or a dropdown where multiple values would be used to specify a specific campaign.
  • Create some new fields to support the Email Campaigns
    "EmailCampaignNext_Step" This field indicates which step is to follow once the current email is sent This field could be an integer (whole number with no decimals) field that would have steps 1, 2, 3 for example. Or it could be a text field that had descriptive steps, like "Initial Contact", "Example Email", "Product Email", etc.

  • Create an "EmailCampaign_sentDate" field that would indicate when the email was last sent - this must be a "Date" field, this field will be updated by the "Notify Service" as the current email is sent.
You may have contacts on multiple email campaigns at the same time, you would need a set of all three fields for each campaign the contact could be simultaneously assigned to.

Once you have the fields created, you would need to create email templates for each email that would be sent. Once those have been created, you would then need to use the Database Triggered Emails/Alerts (this is a SQL Query that located contacts who qualify to receive the email) to build the list of contacts who will be included within the email campaigns. You would need one Database Triggered Emails/Alerts profile for each step of each campaign. That profile would:

  1. Find the contacts that apply to the specific campaign step
  2. Send the email to the contact records for that campaign step 
  3. Update the Contact with the new values for "EmailCampaignNext_Step" and "EmailCampaignsent_Date"
To find the contacts, specify the SQL for that campaign step. For the first email of the campaign, the SQL statement would look like:
  • select * from wce_contact where [Email Campaign Field] = '[campaign name]' and [Email Campaign, Next Step field] is null

    Here is an actual Query for the FIRST email in the series simple cut and paste using your own field names.
    select contact, uniqueid, EmailCampaign from wce_contact where (EmailCampaign like '%IntroAuto%') AND ((Not Hold = 'Y') or (hold is null)) AND (DripFeed = 'Yes') AND (EmailCampaign_NextStep is null) 

    Note the last item is NULL so if the earlier conditions are met the email will go out.

    The above SQL statement also looks for contacts where the step matches, and the "EmailCampaignNext_Step" field is less than or equal to today's date, this can prevent multiple emails being sent on the same day to the same recipient.
  • Here is an example SQL Query for the second and subsequent BUT NOT YOUR LAST email in the series.
  • select contact, uniqueid, EmailCampaign from wce_contact where (EmailCampaign like '%IntroAuto%') AND ((Not Hold = 'Y') or (hold is null)) AND (DripFeed = 'Yes') AND EmailCampaign_NextStep = 2 and (EMailCampaign_Sent < <#date format=sql>)
YOUR LAST EMAIL IN THE SERIES SHOULD STOP ANY PREVIOUS EMAIL FROM QUALIFYING TO BE SENT AND THEREFORE GOING OUT AGAIN, in the example above the field DripFeed = 'Yes' was being checked. As the last email in the series goes out we will update the value in the DripFeed field from 'Yes' to 'Completed'
Here is the 4th in the series
  • select contact, uniqueid, EmailCampaign from wce_contact where (EmailCampaign like  '%IntroAuto%') AND ((Not Hold = 'Y') or (hold is null)) AND (DripFeed = 'Yes') AND EmailCampaign_NextStep = 4 and (EMailCampaign_Sent < <#date format=sql>)
It is essential to update the value that will stop the last email going out again simply update the DripFeed value from Yes to Complete which is achieved as shown below.
All emails will require "EmailCampaign_Nextstep" and "EmailCampaign_SentDate", to be updated. The LAST Email uses a different value TO STOP THE SERIES in the example above our Query has been checking the value in the DripFeed field of 'Yes', we now want to update this to complete as shown in the image above.
Specify the field=value pairs for the profile:
  • [EmailCampaignNext_Step=[Next Step Value] (1, 2, or Intro1, Intro2 etc)
    For example: EmailCampaignNext_Step=3
  • [EmailCampaign_Sent]=[a day earlier than today]
    For Example:EmailCampaign_Sent=<#date offset=7> - where offset=7 specifies a date 7 days from the current date.
As each campaign email is triggered so this panel allows you to Update the Contact record in preparation for the next time the notify service runs the next Query in the series so that it finds the values you have set and takes the actions you want. 
Once the campaign is setup, you may use the other WiredContact functions to setup reporting to see which contacts are on which step for which campaign and then have the reports automatically emailed to your staff.

Test Test Test
  • As with any automated procedure do setup test records that only go to a few selected people in your team, start the campaign and let it run to a point at which you should not receive any more emails. Once you are happy with the process try it on a small section of your database and tweak the results until you are happy.
There are various additional functions available in the weml templates that can
  • Suppress opt out email footer - useful when writing to your existing Clients
  • Alter who the email was sent from such as Accounts@ 
  • Always use a specific signature such as signature5
  • Write a value of your choosing to the History type such as "Auto" - ideal for reporting each day on what emails were sent using <#historytype value="Auto"> in the email template
If you want this setup we are happy to help you with the creation of campaigns and templates, please download and complete this document EmailCampaign_Setup.dotx

Add Feedback