Authentication
Admin
Previewing Messages
Message Sending
Account Administration
Campaign Administration
Reporting
Engage
Segment
Analytics
Powered by Stoplight

Accelerator Campaign Triggers

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. Definitions:

Pre-campaign trigger: A SQL action, or set of actions, run before any other campaign launch action has occured. This can be especially helpful with data-prep before Audience extraction.

Post-campaign trigger: A SQL action, or set of actions, that is run as the last step of a campaign launch. Your trigger will more than likely be used as a post-campaign trigger on a campaign.

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.

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

    *Note - these fields can only be used in post-campaign triggers.

Setting up a Campaign Metadata Table

At least one campaign metadata table is needed on your database to store job metadata to empower internal tracking and reporting. The first step is to determine which metadata values above you want to store for your reporting. This table will be joined with activity events (delivery, open, click, etc.) to understand job-level details about the action. The MessageGears solutions or support team will help you build your table and provide a DDL for you. Here is an example DDL that might be provided using MySQL:

MySQL DDL Example
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;	

Please contact support@messagegears.com to help create a DDL for your specific database and use-case. We have suggested tables for every database type.

Creating Campaign Triggers

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.

Below is a SQL example of a Trigger Action query adding a row to your created campaign metadata table, 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.

Below is an example of a trigger action (using Freemarker), along with the rendered version of the action in SQL. The “Preview SQL” function will help render the action and test the SQL on your database. 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.

Action Example (templatized with Freemarker)
insert into myschea.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}'
	);
Rendered SQL Example
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

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:


Three final thoughts on campaign triggers:

  1. You’re not limited to just campaign metadata insertion - this is just what we recommend to start with! Any SQL statement can be passed through.
  2. Think carefully about selecting which attributes you want to store. You don’t need to store everything. Also remember, it’s very easy to add a column to your metadata table and triggers!
  3. If you have questions, the solutions or support team is ready to talk to you, we’re just a ticket away: support@messagegears.com