About data templates¶
A data template defines how columns in Amperity data structures are sent to downstream workflows. A data template is part of the configuration for sending query and segment results from Amperity to an external location.
Campaign templates¶
Data templates that are made available to campaigns may use variables to apply campaign names, group names, and send dates to the names of campaigns that are sent from Amperity.
Note
A date is automatically appended to the filename for one-time campaigns.
Important
Campaign templates use the same tokens and Joda-Time filters as file-based templates.
Recommended patterns¶
The recommended pattern for using campaign templates with one-time campaigns is to use the {{ campaign_name }} and {{ group_name }} variables, along with a timestamp:
{{ campaign_name }} - {{ group_name }}_{{ now|format'yyyy-mm-dd' }}.csv
For recurring campaigns you should avoid using a timestamp:
{{ campaign_name }} - {{ group_name }}.csv
Campaign variables¶
You may use any of the following campaign template variables:
Campaign names¶
Use the {{ campaign_name }} variable to define where the name of the campaign is added to the filename for a campaign, as it will be received by the downstream system.
Use this variable by itself to use the campaign name as the filename. For example, when the filename template is set to {{ campaign_name }} and the name of the campaign is acme_subscriber_bogo_20220815_1 the filename will be acme_subscriber_bogo_20220815_1.
You may use this variable by itself or with {{ group_name }} in any order.
Treatment group names¶
Use the {{ group_name }} variable to define where the name of a treatment group is added to the filename for a campaign, as it will be received by the downstream system.
Use this variable by itself to use the treatment group name as the filename. For example, when the filename template is set to {{ group_name }} and the name of the treatment group is Group1_bogo_20220815_1 the filename will be Group1_bogo_20220815_1.
You may use this variable by itself or with {{ campaign_name }} in any order.
List names¶
Use the {{ list_name }} variable to use the name of the campaign as the filename for a campaign, as it will be received by the downstream system.
Caution
You should avoid using this campaign template with one-time or recurring campaigns and instead use {{ campaign_name }} (with or without a timestamp).
Campaign examples¶
The following examples show various campaign template configurations for a campaign named “ACME Churn” and three treatment groups named “Facebook”, “Google”, and “Bing” that are configured to send the campaign to Meta Ads Manager, Google Ads, and Microsoft Ads.
campaign name, group name, datestamp
{{ campaign_name }} - {{ group_name }}_{{ now|format'yyyy-mm-dd' }}.csv
would send three campaigns with names similar to:
ACME_Churn_Bing_2022-06-15.csv (sent to Microsoft Ads)
ACME_Churn_Facebook_2022-06-15.csv (sent to Meta Ads Manager)
ACME_Churn_Google_2022-06-15.csv (sent to Google Ads)
group name, datestamp
{{ group_name }}_{{ now|format'yyyy-mm-dd' }}.csv
would send three campaigns with names similar to:
Bing_2022-06-15.csv (sent to Microsoft Ads)
Facebook_2022-06-15.csv (sent to Meta Ads Manager)
Google_2022-06-15.csv (sent to Google Ads)
campaign name, datestamp
{{ campaign_name }}_{{ now|format'yyyy-mm-dd' }}.csv
would send three campaigns with names similar to:
ACME_Churn_2022-06-15.csv (sent to Microsoft Ads)
ACME_Churn_2022-06-15.csv (sent to Meta Ads Manager)
ACME_Churn_2022-06-15.csv (sent to Google Ads)
campaign name, no datestamp
{{ campaign_name }}.csv
would send three campaigns with names similar to:
ACME_Churn.csv (sent to Microsoft Ads)
ACME_Churn.csv (sent to Meta Ads Manager)
ACME_Churn.csv (sent to Google Ads)
File templates¶
Data templates for file-based destinations share a set of common settings and then have specific settings, depending on if you are sending those files to any SFTP site, Amazon S3, Azure Blob Storage, or Google Cloud Storage.
Common settings¶
Most file-based destinations share common settings:
.DONE files¶
Amperity may be configured to include a “success file” to indicate when an orchestration has finished sending data. A success file has a .DONE file extension and will be the last file added by Amperity.
Tip
If a downstream sensor is listening for files sent from Amperity, configure that sensor to listen for the presence of the .DONE file.
Encoding methods¶
Amperity supports the following encoding methods:
GZIP
TAR
TGZ
ZIP
Escape characters¶
An escape character is specific to a data format and allows certain character sequences to be interpreted differently from characters that are not prefixed by that escape character.
If an escape character is not specified and quote mode is set to “None” this may result in unescaped, unquoted files. When an escape character is not specified, you should select a non-“None” option.
File formats¶
You can send results from Amperity in the following file formats:
Custom delimiters¶
Some formats will accept a custom delimiter. From the File format drop-down, select “Custom delimiter” and then in the Delimiter box type a single character to use as a custom delimiter.
Filename templates¶
A filename template defines the naming patterns for files that are sent by Amperity to a location in which files are dropped. A filename template specifies the name of the file and then uses Jinja-style string formats to append a date to the filename to ensure that any downstream process can identify which file is the one to be picked up.
Joda-Time is an open-source date and time library that is used by Amperity to establish consistency in filename patterns. The recommended pattern is “Segment_Name_MM-dd-YYYY”, where “Segment_Name” is the name of the segment and “MM-dd-YYYY” will append the current date.
Text variable¶
Strings in a filename template are literal by default. Use the {{ text }} variable to apply special rendering to the text value.
Filters¶
Use a filter to shift a timezone, and then format it as a string. The following filters are available:
Filter |
Description |
|---|---|
local |
Use the |
format |
Use the |
next day |
Use the |
Tokens¶
Use a token to specify how to apply a datetime to a file. The following tokens are available:
Token |
Description |
|---|---|
now |
Use the |
Template examples¶
To ensure an orchestration’s datetime is applied to the Pacific Time Zone:
C360_incremental_{{now|local:'America/Los_Angeles'|format:'MM-dd-yyyy'}}.csv.gz
To shift the date for an orchestration to be the day after its run:
customer_360/{{now|local:'America/Los_Angeles'|next_day|format:'YYYY/MM/dd'}}/Customer_360.tsv.gz
Header rows¶
Files, such as CSV, PSV, or TSV, may (or may not) require header rows, which define the headers for that data set. Configure the settings to match if header rows are required by the downstream workflow.
Line endings¶
Some files require a specific line ending. Amperity supports LF and CRLF line endings. Configure the settings to match if specific line endings are required by the downstream workflow.
Quote modes¶
Quote mode can be set to one of the following:
Quotes all fields
Quotes all non-null fields
Quotes fields with special characters only
Quotes all non-numeric fields
None
Specific settings¶
File-based destinations have specific settings, depending on the type of location to which files are sent:
Amazon S3¶
Amazon S3 requires the name of the S3 bucket and the S3 prefix.
Azure Blob Storage¶
Azure Blob Storage requires the name of the container and the blob prefix.
Google Cloud Storage¶
Google Cloud Storage requires the object prefix.
SFTP¶
SFTP requires the path to the remote folder.
API templates¶
A data template for a destination that uses an API will vary, depending on the type of destination and other configuration details. Data templates that are associated with APIs include HubSpot, Klaviyo, SailThru, Zendesk, Meta Ads Manager, Google Ads, Campaign Monitor, Microsoft Dynamics, and Salesforce Marketing Cloud.
Snowflake templates¶
A data template for a destination that uses Snowflake must specify the location of the tables to which Amperity will write data, and then the action that will be performed against those tables.
Location¶
A Snowflake location is a period-delimited list that specifies the Snowflake database name, the Snowflake schema name, and the Snowflake table name. For example:
SNOWFLAKE_DATABASE_NAME.SNOWFLAKE_SCHEMA.TABLE_NAME
Table actions¶
Amperity can perform three actions against Snowflake tables: create, drop, and truncate.
Create¶
Use the Create table option to create the table in Snowflake when it is not already there.
Caution
Only use this option when Snowflake is not managed by Business Intelligence Connect and when user roles in Snowflake have permissions set on a per-table basis.
Drop¶
Use the Drop table option to overwrite the table and apply an updated schema when the upstream segment or table changes.
Important
Always use this option when Snowflake is managed by Business Intelligence Connect or when user roles in Snowflake have permission to access all tables in the database.
Truncate¶
Use the Truncate table option when the contents of the table should be emptied prior to loading data.
Caution
Only use this option when Snowflake is not managed by Business Intelligence Connect and when user roles in Snowflake have permissions set on a per-table basis.
How-tos¶
This section describes tasks related to managing data templates in Amperity:
Add data template¶
Use the Add data template button option in the row for an existing destination to add a data template. The data template inherits the credentials, visibility, PII, and template settings that were configured for the destination.
To add a data template
From the Destinations page, open the menu for a destination, and then select Add data template.
In the Add Data Template dialog box, enter the name of the data template and add a description.
Note
The data template inherits the credentials and template settings that are configured by the destination.
Select Allow query authors to use this data template to ensure use of this data template by query authors.
Select Allow query authors with limited PII access to use with orchestrations to enable the use of this data template by users with limited PII access.
Important
This option must be enabled for the destination and the data template.
Review the data template settings.
Click Save.
Allow to send queries¶
Query authors must be allowed to send queries to a destination. This is enabled by the Allow query authors to use this data template option. When this option is enabled for a data template, it must also be enabled for the destination to which the query author wants to send a query.
To allow query authors to use a data template
From the Destinations page, open the menu in the same row as the destination to be edited, and then select Edit Settings.
Select the Allow query authors to use this data template option.
Click Save.
Allow to send PII¶
Some users of Amperity are allowed only limited access to view PII data. Users with limited access must be allowed to send PII data. This is enabled by the Allow query authors with limited PII access to use with orchestrations option. When this option is enabled for a data template, it must also be enabled for the destination to which PII data is to be sent.
To allow users with limited access to PII data to send data
From the Destinations page, open the menu in the same row as the destination to be edited, and then select Edit Settings.
Select the Allow query authors with limited PII access to use with orchestrations option.
Click Save.
Delete data template¶
Use the Delete Settings option to remove a data template from Amperity. This should be done carefully. Verify that both upstream and downstream processes no longer depend on this data template prior to deleting it.
To delete a data template
From the Destinations page, open the menu in the same row as the data template to be deleted, and then select Delete.
Click Confirm.
Edit data template¶
Use the Edit Settings option in the row for a specific data template to make changes, including to credentials, visibility, PII, and template settings. Verify that both upstream and downstream processes are not affected by these changes.
To edit a data template
From the Destinations page, open the menu in the same row as the data template to be edited, and then select Edit Settings.
Make changes to the data template.
Click Save.
Enable success file¶
Amperity may be configured to include a “success file” to indicate when an orchestration has finished sending data. A success file has a .DONE file extension and will be the last file added by Amperity.
Tip
If a downstream sensor is listening for files sent from Amperity, configure that sensor to listen for the presence of the .DONE file.
To enable a success file
From the Destinations page, open the menu in the same row as the data template to be deleted, and then select Edit.
Under Template Settings, select the Include success file upon completion option.
Click Save.
Map to external schemas¶
Warning
This section applies ONLY to the Meta Ads Manager and Google Ads destinations.
Data mappings support two options:
Use Visual and SQL queries to allow mapping for this template to apply to queries created by the visual Query Editor and/or the SQL Query Editor.
Use SQL queries only to limit mapping for this template to apply only queries created by the SQL Query Editor.
If the destination does not require Amperity fields to be mapped, leave the value NULL. Otherwise, replace with a data mapping structure that maps fields to the required naming pattern.
To define a data mapping
From the Destinations page, open the menu in the same row as the data template to be edited, and then select Edit Settings.
Identify the external schema to which Amperity data will be mapped.
As part of adding or editing a data template from within the Data Template dialog box, under Data Mappings, define the data mappings that are required to map Amperity fields with fields in the external schema.
Click Save.
Make available to campaigns¶
A destination may be used with campaigns only after it is enabled for those workflows.
To make a data template available to campaigns
From the Destinations page, open the menu in the same row as the destination to be edited, and then select Edit Settings.
Enable the Available to campaigns option.
Click Save.