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, Message must be enabled within Accelerator.
Table of Contents
Adding a Trigger to a Campaign
Creating a Campaign Metadata Table
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 Message → Delivery → 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
|
Element Name |
Description (*Notes) |
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 (*Notes) |
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 (*Notes) |
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 (*Notes) |
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 (*Notes) |
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 (*Notes) |
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;
| Tip: 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:
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.
| Note: 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. |
Comments
0 comments
Please sign in to leave a comment.