Campaign triggers

Overview

A Campaign Trigger is a custom SQL action set to be performed in relation to a campaign launch. This can prevent replication of data in your database by performing actions automatically, such as changing or updating database fields, or simply record campaign launches to empower your internal campaign tracking and reporting. Campaign Triggers are used within Marketing Campaigns, where users can identify triggers as Pre-Campaign or Post-Campaign. You won't need to select whether a trigger is Pre- or Post-Campaign until applying the trigger to a campaign in the Campaign Settings section, though creating the Trigger Actions with the end goal in mind is helpful.

The most common use of Campaign Triggers is to write Campaign Metadata into a table for reporting purposes. With this data, complex reporting can be developed and extracted from the metadata table.

In order to use Campaign Triggers, Campaigns must be enabled within Accelerator.

Data Elements

The following data elements are available to use in Campaign Triggers, though certain campaign data won’t be available to use in Pre-Campaign triggers, such as recipient count or request ID, as these values are not defined until the campaign has been launched successfully.
Template Metadata

Element Name	Type
templateId	Integer
templateName	String (64 characters)
templateDescription	String (2,000 characters)
fromAddress	String
fromName	String
subjectLine	String
replyToAddress	String
approxTemplateSize	Long
spamAssassinScore	Double
charSet	String
recipientDataSourceName	Long
recipientDataParameterData	Long
recipientDataSource	Long
contextDataSourceName	Long
contextDataParameterName	Long
contextDataSource	Long

Job Metadata

Element Name	Type
campaignId	Integer
campaignName	String (64 characters)
campaignDescription	String (2,000 characters)
accountId	Integer
accountName	String (256 characters)
ipSelector	String
jobId	Integer
notificationEmailAddress	String (1,000 characters)
category	String (255 characters)
urlAppend	String
jobStatus	String
error	Boolean
errorMessage	Long
firstRecipientXml	String
messageGearsRequestId	String (64 characters)
recipientCount	Integer
s3RecipientDataUrl	String
startTime	Date
variantName	String
dataExtractionStartTime	Date
dataTransferStartTime	Date
jobSubmissionStartTime	Date

Process

Creating a Campaign Trigger

Whether creating a Pre-Campaign Trigger or a Post-Campaign Trigger, both are created the same way. To begin you will need to Navigate to Admin → Campaign Triggers. This opens a screen to manage any existing Campaign Triggers.

You can now click an existing Campaign Trigger to open the Campaign Trigger settings. Click the Create Trigger button in the top right to create a new Trigger. If creating a new Trigger, the screen will prompt the user to enter a name for the new Trigger.

After naming a new Trigger, the Trigger Overview screen will display.

Currently, this Trigger exists within Accelerator, but it doesn’t do anything. View the details of the Trigger to see information about the Trigger. Underneath the details of the Trigger is the Add a Campaign Trigger Action button.

Click this button to create a query for the Campaign Trigger.

Adding a Campaign Trigger Action is the functionality that executes when using this Campaign Trigger in a Marketing Campaign. Configure the Action Name to be descriptive and choose the appropriate database connection to allow this Campaign Trigger to interact with appropriate data sources.

The right side of the screen is the data source explorer. Use this interface to view tables and columns within the data source.

There are unique variables available within the Campaign Trigger Action. A list of variables are found below the Query Editor. Click on the variables to copy the variables in the correct format. Paste the text within the Query Editor to use the variable.

A full list of available variables can be found within this document. View variables in this section.

Once the Query is configured, click Save at the bottom of the screen. Once saved, the screen will display the Campaign Trigger Overview.

A Campaign Trigger can contain multiple Actions and Queries. If needed, continue to add additional Trigger Actions to the Campaign Trigger.

When using multiple Actions within a Campaign Trigger, the Actions are processed sequentially. This means that the order that the Actions are displayed is important. Arrange the actions in the proper order using the arrow buttons that display when using multiple Actions.

Adding a Trigger to a Campaign

The process to add a Campaign Trigger to an already existing Campaign requires a different set of steps than simply creating one from a new Campaign.

To begin, you will need to Navigate to Campaigns> Marketing to view existing Marketing Campaigns. Create a new Campaign or open an existing Campaign.

For more information on creating Marketing Campaigns, refer to End-to-End Newsletter Creation.

Once the Campaign Overview screen is open, select the Settings option on the right side of the screen.

Scroll to the bottom of the Campaign Settings and expand the View Advanced Options dropdown. Within the Advanced Options is a field to select a Pre-Campaign Trigger or a Post-Campaign Trigger.

Use the dropdown to select an existing Campaign Trigger.

A Campaign Trigger created within Accelerator can be selected for either a Pre-Campaign Trigger or a Post-Campaign Trigger.

Save the configuration of the Campaign.

Campaign Trigger Variables

There are a number of Campaign Trigger Variables that you can use for your campaign triggers. Below you will find a list of these variables followed by a detailed description of each item:

Job Metadata

Template Metadata

Audience/ContextData Metadata

Campaign Metadata

Account Metadata

Other Post-Campaign Metadata

Job Metadata

Element Name	Description	Data Type	Nullable
jobId	The internal Accelerator job ID. Note: Suggested Primary Key	Int	No
startTime	The Accelerator job execution timestamp.	Datetime	No

Template Metadata

Element Name	Description	Data Type	Nullable
templateId	The internal Accelerator template ID.	Int	No
templateName	The specified template name.	String	No
templateDescription	The specified template description.	String	Yes
fromAddress	The unrendered From Address specified in the template.	String	No
fromName	The unrendered From Name specified in the template.	String	Yes
subjectLine	The unrendered subject line specified in the template.	String	No
replyToAddress	The unrendered reply-too address specified in the template.	String	Yes
approxTemplateSize	The approximate template size based on the rendered template preview.	Int	No
spamAssassinScore	The approximate Spam Assassin Score based on the rendered template preview.	Double	No

Audience/ContextData Metadata

Element Name	Description	Data Type	Nullable
recipientDataSourceName	The specified Audience name.	String	No
recipientDataSource	- Pre-campaign trigger: The unrendered SQL or API URL. - Post-campaign trigger: The rendered SQL or API URL.	String	No
contextDataSourceName	The specified Audience name.	String	No
contextDataSource	- Pre-campaign trigger: The unrendered SQL or API URL. - Post-campaign trigger: The rendered SQL or API URL.	String	No

Campaign Metadata

Element Name	Description	Data Type	Nullable
campaignId	The internal Accelerator campaign ID.	Int	No
campaignName	The specified Accelerator campaign name.	String	No
campaignDescription	The specified Accelerator campaign description.	String	Yes
category	The specified Accelerator campaign (job) category.	String	Yes
urlAppend	The specified Accelerator campaign URL Append.	String	Yes
notificationEmailAddress	The specified Accelerator campaign notification email address.	String	Yes

Account Metadata

Element Name	Description	Data Type	Nullable
accountId	The account ID used to send the job.	Int	No
accountName	The account name used to send the job.	Int	No

Other Post-Campaign Metadata

Element Name	Description	Data Type	Nullable
messageGearsRequestId	The global unique MessageGears job identifier. *Note - this will be null for a 0 recipient job.	String	Yes
recipientCount	The total recipient count from the audience for the job.	Int	No
firstRecipientXml	The first recipient transformed into the XML format sent to Cloud. *Note - this will be null for a 0 recipient job.	String	Yes
dateExtractionStartTime	The timestamp when Accelerator began extraction from the external datasource/database.	Timestamp	No
dataTransferStartTime	The timestamp when Accelerator began transferring the recipient data to Cloud.	Timestamp	No
jobSubmissionStartTime	The timestamp when Accelerator received a successful job submission response from Cloud.	String	No
variantName	The MVT variant name of the job if an MVT was used.	String	Yes
error	"true" if the job resulted in an error. "false" otherwise.	Boolean	No
errorMessage	The corresponding error message if there was a job error.	String	Yes

Bolded fields can only be used in post-campaign triggers.

All variables are accessible through the format:

${trigger.variableName}

For example:

${trigger.subjectLine}

Creating a Campaign Metadata Table

It is recommended that a table exists within the data source to hold metadata. An example of the SQL code used to create this table is shown below:

CREATE or REPLACE TABLE 'campaign_metadata' (  
  'job_id' int(11) NOT NULL,  
  'request_id' varchar(50) NOT NULL,  
  'start_time' timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  
  'job_category' varchar(255) DEFAULT NULL,  
  'recipient_count' int(11) DEFAULT NULL,  
  'campaign_name' varchar(255) DEFAULT NULL,  
  PRIMARY KEY ('job_id')  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;        

Be sure to make note of the data types for each field.

Example of a Campaign Trigger

Below is an example of a Campaign Trigger Query with variables:

INSERT INTO myschema.campaign_metadata   
  (  
    job_id,  
    request_id,  
    start_time,  
    job_category,  
    recipient_count,  
    campaign_name  
  )  
  VALUES  
  (  
    ${trigger.jobId},  
    '${trigger.messageGearsRequestId}',  
    '${trigger.startTime[0..19]}',  
    '${trigger.category}',  
    ${trigger.recipientCount},  
    '${trigger.campaignName}'  
  );

When the variables are populated, the SQL query will be rendered as:

INSERT INTO myschema.campaign_metadata   
  (  
    job_id,  
    request_id,  
    start_time,  
    job_category,  
    recipient_count,  
    campaign_name  
  )  
  VALUES  
  (  
    1  
    'p4-2020345-145df83a93819417ab9372daf',  
    '2020-02-30T18:53:11',  
    'NEWSLETTER',  
    13013981,  
    'US - Weekly Newsletter'  
  );

Managing Campaign Triggers

On the main page is a list of Campaign Triggers which can be searched and sorted. To drill into a specific Trigger, click its name.

Within each individual Campaign Trigger is information about its creation, and a list of the Actions related to the Campaign Trigger. To add a new Action, select the Add Action button.

To test, edit, duplicate, or delete individual Actions, click the name of the Action. The testing function is found below of the details and SQL query.

When a Trigger contains multiple Actions, the actions are performed in a sequential order. Since Actions occur sequentially, arrange them in the order you want the SQL actions to take place using the up/down arrows placed on the left side of each item:

Example Use Case:

Save Campaign Details to your Database

A commonly used Campaign Trigger is to add each send of a campaign to a database, including details such as IDs and recipient counts. In a case like this, you’ll want to use this Trigger as a Post-Campaign Trigger, which are best used when working with campaign details.

To the left is SQL example of a Trigger Action query adding a row to the database, including details such as the ID, timestamp, recipient count, and other unique campaign details.

Note that a pre-campaign trigger won’t be able to include the request ID or number of recipients, since those details won’t be available yet. Once a campaign is sent, those details will be available for inclusion in SQL queries.

The Sample SQL image below is the same Trigger Action SQL from above, run through the “Preview SQL” function. You can see placeholder values are populating the FreeMarker portion of the query, indicating what sort of values to expect based on the campaign this Action is triggered by.

The myschema.campaign_history table is NOT defined by Accelerator. The format of this table is defined and maintained by your team, and FreeMarker is used to populate the appropriate fields. Other schema or table structures may be used as required by your team.