Transaction records

Transactions are a type of interaction record that contain information about your customers’ purchase histories with your brand. For example: what they purchased, when they purchased, how many items were purchased, how much money was spent, how often they made a purchase, and the locations from which each purchase was made.

The process for adding transaction records to Amperity requires individual data sources that provide order-level, item-level, and product catalog data. The actual steps will vary from tenant to tenant, depending on how the individual data sources provide data for canceled and returned items, quantities, discounts, and revenue.

At a high level, start with deciding how to apply semantic tags to your brand’s transaction records. After you have run Stitch you will then configure a series of tables that associate each transaction with a customer profile.

For each data source that your brand makes available to Amperity that contains transactions:

1. Apply transactions semantic tags

2. Review input validations

3. Associate to customer profiles

4. Extend database for transactions tables

5. Configure optional attributes

6. Refresh database

7. Review C360 validations

8. Enable segments and campaigns

Apply semantics

You should plan to use a custom domain table to apply transaction semantic tags to data sources that contains transactions data.

Transaction semantics include a set of required semantic tags — is-cancellation, is-return, item-quantity, item-revenue, order-datetime, order-discount-amount, order-id, product-id, purchase-brand, purchase-channel, and store-id — along with a set of optional semantic tags, such as item-cost and item-revenue.

Important

All transaction semantic tags are prefixed with txn-item/. For example: txn-item/order-datetime.

Apply these semantic tags to individual fields within data sources that describe the products your customers have purchased from your brand.

Multi-brand databases

To support multi-brand databases, you must ensure that a brand column exists within each data source. This enables filtering transactions by brand. Adding a brand column for transactions data should be done using a custom domain table.

Purchase histories

For each incoming data source that contains item-level data, you should plan to use the domain SQL editor to reshape the data into a schema against which individual transaction (txn-item/) semantic tags can be applied.

Tip

Use a custom domain table per data source to shape the data, and then apply semantic tags. This will make it easier to find specific transaction records when troubleshooting data quality issues.

Important

Itemized transactions must be transformed in way that allows each row to represent a distinct transaction for a given item for a given order:

1. A row for each purchase.

2. A row for each return.

3. A row for each cancellation.

Product catalogs

Use txn-item/pc semantic tags (a subset of txn-item/ semantic tags) when your brand wants to use your product catalog for segmentation, building audiences, and sending product catalog data alongside (or as part of) audiences. These tags add your brand’s product catalog to the Unified Itemized Transactions table alongside your customers’ purchase histories.

In some cases, you may need to use the domain SQL editor to reshape the data into a schema to which product catalog (txn-item/pc) semantic tags can be applied.

Review validations

The quality of the data sources your brand chooses to make available to Amperity matters when it comes to building unified customer profiles because your brand uses those profiles to activate your customers across a wide variety of downstream use cases. More accurate profiles lead to higher activation rates, better match rates, and increased returns on advertiser spend.

Amperity includes a series of input validation reports that help your brand measure the quality of email addresses, phone numbers, and transactions. Use them to quickly identify data quality issues so that your brand can work to resolve those data quality issues as soon as possible.

Input validations are run against domain tables that have been published, and then made available to the Queries page. Use input validation reports help your brand discover data quality issues with email addresses, phone numbers, and transactions.

Note

Input validation reports are meant to be informative and to provide a way for your brand to explore data and discover when data that is made available to Amperity is missing values or has values that do not match the expected format.

Input validation reports do not have pass or fail thresholds and will not stop automated workflows within Amperity.

Some input validations measure against a single semantic tag, while others use a combination of semantic tags. All input validations are returned as a series of columns that describe the quality of your data as it relates to a specific each report, with a row for each data source.

You do not need to run Stitch or have a working customer 360 database to run input validations. Just publish the domain tables to make them available to the Queries page.

INPUT VALIDATIONS CHECKLIST

Review customer profile reports Email addresses and phone numbers are validated for the percentage of missing values, correctly formatted values, and the frequency at which unique values occur. For example, for email addresses: The percentage of customer profiles with missing email addresses. The sum of email addresses that do not have a valid format. The sum of unique email addresses by data source. The uniqueness of email addresses by data source, compared across all data sources with email addresses. and for phone numbers: The percentage of customer profiles with missing phone numbers. The sum of phone numbers that do not have a valid format. The uniqueness of phone numbers by data source, compared across all data sources with phone numbers. The percentage of records with phone numbers that do not contain 10 digits.

Review transaction reports A complete transaction with good underlying data has a combination of values that work together to describe what was purchased, when it was purchased, where it was purchased, and so on. Incomplete transactions are shown using a variety of reports. For example: All items should be associated with a unique product ID. All orders should be associated with a store ID. All orders should have a quantity, a revenue amount, and an order date. All orders should have a unique order ID. An item cannot be returned and canceled. canceled item revenue should be negative Discount amounts should be positive Discount percent should be “discount amount / list price” From which store was a purchase made? Quantity should be negative when items were canceled or returned Quantity should be positive Returned item revenue should be negative Revenue should be “list price - discount amount” Revenue should be positive Through which channel was a purchase made? Was an item canceled or returned? This should be true or false. What percentage of product IDs are unique? Each individual validation report shows the data sources that are associated with the report, the semantic tags that were validated, and the results of the validation. Explore the data that is associated with this report by clicking the Validation query link, which opens in the Queries page.

Associate to profiles

Use a foreign key to link transaction records to customer records. A transaction record will inherit the Amperity ID of a customer profile when both share the same foreign key.

Extend database

Extend your customer 360 database for transactions by adding the following tables:

1. Itemized transactions

2. Unified transactions

3. Extended attributes

4. Transaction attributes

Important

Most of the work required to extend your customer 360 database for transaction histories and product catalogs happens one time.

Depending on the types of data sources your brand adds to Amperity over time, you may need to make specific changes to specific tables in your customer 360 database to support these updates. For example, if your brand adds a data source that contains PII you may need to update the source and field priorities that are defined in the Merged Customers table.

The initial configuration of your customer 360 database requires using SQL to add (and extend) a series of tables that are an extension that captures the interactions your brand has with your customers.

Itemized transactions

The Unified Itemized Transactions table contains rows of transactional data summarized to the item level, and then coalesced into a single column for each unique combination of order ID and product ID. The order ID is associated with an Amperity ID.

UNIFIED ITEMIZED TRANSACTIONS CHECKLIST

	Initial configuration only The Unified Itemized Transactions table is a required table for the customer 360 database when transactions are available to your tenant and fields have been tagged with txn-items/ semantic tags. The Unified Itemized Transactions table is built from Stitch output and is added automatically. Review the list of semantic tags that are available for txn-items/ and ensure that data sources that contain your brand’s purchase histories for your customers have these tags applied. Important Something_about_product_catalogs_goes_here. We support txn-items/product catalogs, but also pc/product catalogs. Can you do both to get both outcomes?
	Multi-brand databases only Switch to multi-brand template: SELECT uit.* FROM Unified_Itemized_Transactions uit INNER JOIN Database_Key dk ON uit.purchase_brand = dk.purchase_brand
	Initial configuration only The Unified Itemized Transactions table is a required table for the customer 360 database when transactions are available to your tenant and fields have been tagged with txn-items/ semantic tags. The Unified Itemized Transactions table is built from Stitch output and is added automatically.

Unified transactions

The Unified Transactions table contains one row for each unique transaction record, with each order ID associated to an Amperity ID.

UNIFIED TRANSACTIONS CHECKLIST

	Pre-requisite The Unified Itemized Transactions table must be available to your customer 360 database.
	Initial configuration only Add the Unified Transactions table to your customer 360 database.
	Initial configuration only Review the list of optional attributes to determine which attributes should be enabled for your tenant.

Extended attributes

The Transaction Attributes Extended table contains additional attributes for customer flags, customer orders, data differences, time period rollups, and RFM scores, many of which are represented by duration (days, months, years, lifetime), order position (first, second, last), frequency, and revenue.

Extended transaction attributes—customer flags, customer orders, date differences, time period rollups, and RFM—are also calculated by Amperity based on data sources that contain interaction records that were tagged with transactions and itemized transaction semantics.

TRANSACTION ATTRIBUTES EXTENDED CHECKLIST

	Pre-requisite The Unified Transactions table must be configured and activated in your customer 360 database.
	Initial configuration only Add the Transaction Attributes Extended table to your customer 360 database.
	Initial configuration only Review the list of optional attributes to determine which attributes should be enabled for your tenant.

Transaction attributes

The Transaction Attributes table describes the behavior and history of customer transactions, such as purchase history and frequency, brand preferences, order history, time between purchases, and so on.

TRANSACTION ATTRIBUTES CHECKLIST

	Pre-requisite The Transaction Attributes Extended table must be configured and activated in your customer 360 database.
	Initial configuration only Add the Transaction Attributes table to your customer 360 database.

Optional attributes

You can extend the set of attributes that are available from the Unified Transactions and Transaction Attributes Extended tables to include any of the following:

• Cancellations

• Fiscal calendars

• Net order revenue

• Order costs

• Order discount amounts

• Returns

• RFM

Note

These attributes are built into the SQL templates for the Unified Transactions and Transaction Attributes Extended tables, but are commented out. Some sets of attributes require updates be made to both tables, while others may only require updates be made to one table. Some sets allow for additional customization.

Cancellations

To add extended attributes for canceled items

1. Required. Apply the is-cancellation semantic tag to transaction data sources that contain data for canceled items.

2. Optional. Extended attributes for canceled items attributes may be added to the Transaction Attributes Extended table.

Fiscal calendars

A fiscal calendar is a yearly accounting period that aligns the weeks and months in a calendar year with holidays and a brand’s marketing goals to align the business for an entire calendar year. A common fiscal calendar used by brands is the 4-5-4 fiscal calendar.

A 4-5-4 calendar divides years into months using a 4 weeks - 5 weeks - 4 weeks pattern. Each week starts on a Sunday and ends on a Saturday. Each quarter has the same number of days. A 4-5-4 calendar can be useful for comparing like days for sales reporting purposes.

To add fiscal calendar attributes

1. Pre-requisite. Apply fiscal calendar attributes to a data source that contains the data that defines your brand’s fiscal calendar.

2. Optional. Extended attributes for fiscal calendars may be added to the Transaction Attributes Extended table.

Net order revenue

Net order revenue is the revenue for all items in an order, including returned and canceled items, but not including discounted amounts.

To add attributes for Net Order Revenue

1. Required. Add Net Order Revenue to the Unified Transactions table.

2. Optional. Extended attributes for calculated net order revenue may be added to the Transaction Attributes Extended table.

Order costs

Order cost represents the total cost of goods sold (COGS) for a single transaction, minus returns, cancellations, and discounts.

To add attributes for Order Cost

1. Required. Add Order Cost to the Unified Transactions table.

2. Optional. Extended attributes for calculated order cost attributes may be added to the Transaction Attributes Extended table.

Order discount amounts

An order discount amount represents the total amount for all discounts that were applied to all items in same transaction. You can extend the Unified Transactions table to include order discount amounts by rolling up itemized discount amount values from the Unified Itemized Transactions table. This is done by uncommenting a line of SQL that exists in the SQL template for the Unified Transactions table.

1. Use the MAX() function when discount amounts are consistent across records.

2. Update to use the SUM() function when discount amounts are prorated across records.

Returns

To add extended attributes for returned items

1. Required. Apply the is-return semantic tag to transaction data sources that contain data for returned items.

2. Optional. Extended attributes for returned items attributes may be added to the Transaction Attributes Extended table.

RFM

RFM is a method used to analyze customer value that represents three dimensions:

• Recency. How recently did the customer purchase?

• Frequency. How often does the customer purchase?

• Monetary. How much does the customer spend?

A score for recency, frequency, and monetary is assigned on a scale of 1-10, and is then aggregated into a combined RFM score that is assigned on a scale of “111” (the lowest possible RFM score) to “101010” (the highest possible RFM score, or “three ‘10’ scores”). Maximum scores represent preferred behaviors.

You can extend the Transaction Attributes Extended table to include calculated attributes for RFM scores.

Refresh database

To associate purchase histories with customer profiles apply transaction semantic tags to data sources that contain purchase histories, and then refresh the database. This is typically done manually during initial configuration, after which the process is automated to run on a daily basis after Stitch has finished updating customer profiles.

REFRESH DATABASE CHECKLIST

Run Stitch You must run Stitch to associate purchase histories with customer profiles. Important Data tables that contain purchase histories (and not customer PII) should not be made available to Stitch. The Stitch process evaluates your customers’ purchase histories, and then identifies which purchases should be associated to each unique customer profile. The results are added to the Unified Itemized Transactions table, which associates your customer’s Amperity ID to their purchase history. A row exists in the Unified Itemized Transactions table for each item that was purchased.

Refresh the customer 360 database Activate, and then run the customer 360 database. This will refresh the data to include your customers’ purchase histories as part of their unified customer profiles. When courier groups are configured in your tenant, this step will be done automatically after Stitch has finished processing. The Stitch process maintains the Unified Itemized Transactions and Unified Product Catalog tables.

C360 validations

A customer 360 validation report is generated as an output of running the database. You can access this report from the Notifications pane on the Databases page.

You should work through any of the individual validations in the report that indicate a validation error. In some cases, you may choose to ignore the error or adjust a threshold. In many cases, you will want to investigate the report to try to identify what is causing the error, and then determine if any adjustments can be made.

There are many types of C360 validation reports, including for customer attributes, email addresses, loyalty programs, fiscal calendars, and transactions. Each individual report describes the issue, suggests potential ways to address the issue, and then links to the SQL that generated the validation report. Use these reports to improve the quality of data in your tenant.

Customer attributes

Validation reports for customer attributes include:

• Are records associated with multiple Amperity IDs?

• Are known purchasers also classified as prospects?

• Are any prospects associated with a purchase history?

• Are physical addresses complete?

• Are email addresses complete?

• Are phone numbers complete?

• Are Amperity IDs grouped by household? (This validation is only available when your tenant uses the Merged Households table.)

Email addresses and phone numbers

Validation reports for email addresses and phone numbers include:

• Are email events associated with multiple Amperity IDs?

• Are email events unique by brand?

• Does an email event have an associated event type (open, click, etc.)?

• Are there conflicting opt-in preferences for email addresses and/or phone numbers?

Loyalty programs

Validation reports for loyalty programs include:

• Is the loyalty ID unique?

• Has the customer set an opt-in or opt-out preference?

• Do accrual events have an associated amount and datetime?

• Do redemption events have an associated amount and datetime?

• Is a customer’s current and previous tier status available?

Fiscal calenders

Validation reports for fiscal calenders include:

• Are fiscal calendar dates unique?

• Are fiscal calendars complete (when joined to the Unified Transactions table)?

Transactions

Validation reports for transactions include:

• Are there records without an associated Amperity ID?

• Are orders associated with multiple Amperity IDs?

• Are there records without an associated order ID?

• Are orders associated with multiple order IDs?

• Are orders associated with a store ID?

• Are there orders without associated product information (ID, category, subcategory, or description)?

• Are costs, quantities, and revenue non-positive for returns and cancellations?

• Are returns and cancellations associated with their original order ID?

• Are costs, list prices, discount amounts, discount percents, subtotals, and revenue positive for purchases?

• Is revenue revenue greater than purchase revenue?

• Does the differece between list price and revenue match discounted amounts?

• Does revenue equal the sum of cost and profit?

• Are discount percents accurate?

• Is item revenue missing?

• Is quantity positive for all purchases?

• Are order dates missing or in the future?

Enable segments and campaigns

Segments and campaigns are a collection of features that may be enabled after interaction records are configured. Once enabled, predictive analytics, metrics cards, segment insights, and campaigns are available from within Amperity.