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 cancelled 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:
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:
A row for each purchase.
A row for each return.
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 (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. They 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 report.
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
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:
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
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:
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 cancelled items
Required. Apply the is-cancellation semantic tag to transaction data sources that contain data for cancelled items.
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
Pre-requisite. Apply fiscal calendar attributes to a data source that contains the data that defines your brand’s fiscal calendar.
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 cancelled items, but not including discounted amounts.
To add attributes for Net Order Revenue
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
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.
Use the MAX() function when discount amounts are consistent across records.
Update to use the SUM() function when discount amounts are prorated across records.
Returns¶
To add extended attributes for returned items
Required. Apply the is-return semantic tag to transaction data sources that contain data for returned items.
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
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.
Enable AmpIQ¶
AmpIQ is 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. Ask your Amperity representative about enabling AmpIQ.