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.

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.

Recipient group names

Use the {{ group_name }} variable to define where the name of a recipient 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 recipient group name as the filename. For example, when the filename template is set to {{ group_name }} and the name of the recipient 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.

Campaign examples

The following examples show various campaign template configurations for a campaign named “ACME Churn” and three recipient groups named “Facebook”, “Google”, and “Bing” that are configured to send the campaign to Facebook Ads, 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 Facebook Ads)

  • 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 Facebook Ads)

  • 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 Facebook Ads)

  • 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 Facebook Ads)

  • 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:

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 local filter to shift a datetime to a given timezone using a string in Joda-Time format. For example: local:'America/Los_Angeles'.

format

Use the format filter to format a datetime with a string in Joda-Time format. For example: format:'MM-dd-yyyy'.

next day

Use the next day filter to shift the datetime to the next day. For example: now|local:'America/Los_Angeles'|next_day.

Tokens

Use a token to specify how to apply a datetime to a file. The following tokens are available:

Token

Description

now

Use the now token to apply a datetime to a file that is current at the time a file is written.

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:

  1. Quotes all fields

  2. Quotes all non-null fields

  3. Quotes fields with special characters only

  4. Quotes all non-numeric fields

  5. 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, Facebook Ads, 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, visiblity, PII, and template settings that were configured for the destination.

To add a data template

  1. From the Destinations tab, open the menu for a destination, and then select Add data template.

  2. 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.

  3. Select Allow query authors to use this data template to ensure use of this data template by query authors.

  4. 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.

  5. Review the data template settings.

  6. 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

  1. From the Destinations tab, open the menu in the same row as the destination to be edited, and then select Edit Settings.

  2. Select the Allow query authors to use this data template option.

  3. 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

  1. From the Destinations tab, open the menu in the same row as the destination to be edited, and then select Edit Settings.

  2. Select the Allow query authors with limited PII access to use with orchestrations option.

  3. 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

  1. From the Destinations tab, open the menu in the same row as the data template to be deleted, and then select Delete.

  2. Click Confirm.

Edit data template

Use the Edit Settings option in the row for a specific data template to make changes, including to credentials, visiblity, PII, and template settings. Verify that both upstream and downstream processes are not affected by these changes.

To edit a data template

  1. From the Destinations tab, open the menu in the same row as the data template to be edited, and then select Edit Settings.

  2. Make changes to the data template.

  3. 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

  1. From the Destinations tab, open the menu in the same row as the data template to be deleted, and then select Edit.

  2. Under Template Settings, select the Include success file upon completion option.

  3. Click Save.

Map to external schemas

Warning

This section applies ONLY to the Facebook Ads 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

  1. From the Destinations tab, open the menu in the same row as the data template to be edited, and then select Edit Settings.

  2. Identify the external schema to which Amperity data will be mapped.

  3. 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.

  4. 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 campaaigns

  1. From the Destinations tab, open the menu in the same row as the destination to be edited, and then select Edit Settings.

  2. Select the Make available to campaigns option.

  3. Click Save.