Interaction Records¶
An interaction record is a row in a customer data table that contains information about customer behavior, such as purchases (items bought, items returned, costs of items, etc.) and preferences (brands, products, cart adds, etc.).
Note
This topic describes how to configure Amperity for interaction record types, specifically transactions, itemized transactions, and product catalogs, so they are ready for use with 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.
Important
Interactions records may be configured for use within Amperity even when AmpIQ is not enabled. The patterns and processes used to configure interaction records is the same in both scenarios.
About interaction records¶
The following interaction record types should be configured as data sources for Amperity:
Itemized transactions¶
An itemized transaction represents data that captures orders at the item level, with multiple rows per order and one order per item with clearly identified returns and cancellations.
Apply itemized transactions semantic tags to fields within data tables that contain item-level details. This collection of semantic tags will generate the Unified_Itemized_Transactions table. The Unified_Itemized_Transactions and Unified_Transactions tables are used to generate the Transaction_Attrbutes and Transaction_Attributes_Extended tables.
Important
Many features of AmpIQ depend on the correct application of itemized transaction semantic tags to item-level details. In some cases, this may be done directly from the Feed Editor, but in most cases the use of domain SQL is required to reshape the data prior to applying semantic tags.
Product catalogs¶
A product catalog represents data that describes purchased items, including unique identifiers, stock keeping units (SKUs), and other product attributes.
Apply product catalog semantic tags to fields within data tables that contain product catalog details. This collection of semantic tags will generate the Unified_Product_Catalog table. This table should be joined to the Unified_Itemized_Transactions table in the customer 360 database.
Note
The product catalog semantic tags group does not have any required fields. You should use these tags to identify your brands, products, and product descriptions (which could be lists of sub-brands and/or sub-products) so that this data may be associated with the customer purchasing behavior that has been captured by itemized transaction semantic tagging.
Transactions¶
A transaction represents data that captures orders at the order level, with a summary row per order.
Apply transactions semantic tags to fields within data tables that contain order-level details. This collection of semantic tags will generate the Unified_Transactions table. The Unified_Transactions and Unified_Itemized_Transactions tables are used to generate the Transaction_Attrbutes and Transaction_Attributes_Extended tables.
Important
Many features of AmpIQ depend on the correct application of transaction semantic tags to order-level details. In some cases, this may be done directly from the Feed Editor, but in many cases the use of domain SQL is required to reshape the data prior to applying the semantic tags.
Other¶
An interaction record is any data source that you can use to associate your customers to how they have interacted with your brands. This is most often related to purchase behavior, but it may also include any of the following types of data:
Point of sale data
First-party data, i.e. “clickstream”, captured from a mobile application or website
Loyalty program data
Note
AmpIQ requires interaction records for transactions and itemized transactions and recommends interaction records for product catalogs. No other interaction record types are required by AmpIQ.
Add interaction records¶
The process for adding interaction records to Amperity can be complex and depends on individual data sources that provide order-level, item-level, and product catalog data. The actual steps will vary from tenant to tenant, depending on the amounts and types individual data sources that are available.
At a high level, you will need to decide how to apply semantic tags to interaction records, and then configure a series of tables in the customer 360 database. Another series of steps is required to enable AmpIQ.
Tip
The following steps apply (generally) to all interaction record types. Use these steps as an outline, and then refer to the linked sections for additional details as necessary.
To add interaction records
Identify interaction record types and analyze the quality provided by each data source.
Interaction records fall primarily into three main categories: order-level transaction records, item-level transaction records, and product catalogs.
Determine how to apply semantic tags.
In most cases this requires the use of domain SQL to reshape the data prior to applying semantic tags to order-level transaction records, to item-level transaction records, and to product catalogs.
-
Note
Interaction records are typically not made available to Stitch, but require Stitch to associate the Amperity ID to the tables output by Stitch for interaction records.
Configure tables in the customer 360 database.
This step requires you to add the Transaction_Attributes, Transaction_Attributes_Extended, Unified_Itemized_Transactions, Unified_Product_Catalog, and Unified_Transactions tables to the customer 360 database.
The Customer360 will require an update and the Unified_Itemized_Transactions may require an update if you want to roll up product catalog attributes.
Add Validate interaction records in the customer 360 database.
Optional. Enable AmpIQ.
Analyze data quality¶
Before you apply semantic tags to interaction records take a close look at the source data and look for any of the following situations:
Cancelled items¶
Are cancelled items expressed as separate line items or are they inline with purchases? Will transforms be required? Make sure that fields that contain cancellation data are tagged with the is-cancellation semantic tag.
High variance¶
Do some fields have unreasonably high variance? Does this high variance indicate that data is invalid or mislabeled?
When a high variance is present for fields that are expected to have a low variance, this may indicate a field was mislabeled or that the data is not in the correct format.
Data should be monitored daily to determine the threshold at which variance is considered to be unexpectedly high.
Use the following query to discover fields that have a high variance:
SELECT
COUNT(DISTINCT field_name)
FROM
source_name
GROUP BY 1
Item-level revenue¶
How is item-level revenue defined in the data and by the customer? How is order-level revenue defined in the data and by the customer? What is the relationship between item-level and order-level revenue?
For example, raw item-level revenue is available, and then order-level revenue is defined as the sum of item-level revenue plus order-level discounts.
Low completion rates¶
A completion rate is the percent of records that contain data for a field or column. A low completion rate indicates that a high percentage of records contain NULL
values. For example, a completion rate of 17% indicates that only 17% of those fields contain data and that 83% are NULL
.
Do some fields have extremely low completion rates? Are there indications that data is missing or is the behavior expected?
Some common causes include:
A flag that is
TRUE
when true, orNULL
when false. For example: a flag that captures when customers paid with a gift card.Missing customer IDs across a large number of records. For example: point of sale records that do not completely capture customer information.
A malformed source file.
Parsing errors due to incorrect date formats.
Low completion for revenue often indicates that revenue was captured in another data source or another field within this data source.
Data should be monitored daily to determine the threshold at which variance is considered to be unexpectedly low.
Use the following query to discover fields that have low completion rates:
SELECT
SUM(IF(field_name IS NULL, 1, 0)) AS num_empty
,SUM(IF(field_name IS NOT NULL, 1, 0)) AS num_complete
FROM
source_name
Missing required fields¶
Are all required fields present?
Important
These details have downstream dependencies on functionality available from the Metrics tab, Segment Insights (a component within the Segments tab), predicted attributes models, and the Campaigns tab.
The following fields are required for itemized transactions:
An order ID
A customer ID, if also provided in corresponding itemized transactions data sources
A product ID
The order datetime
A way to identify if the item is a returned item
A way to identify if the item is a cancellation
The item quantity
The item revenue, as defined by the customer
The following fields are required for transactions:
An order ID
A customer ID, if also provided in corresponding itemized transactions data sources
The order datetime
The order quantity
The order revenue, as defined by the customer
If a required field cannot be provided from a data source, use domain SQL to set that field to NULL
while maintaining the columns in the domain table.
Poor data quality¶
A careful review of data dictionaries and data schemas should be done prior to configuring Amperity for interactions records. Look for situations where the source data might causes problems downstream as it is processed within Amperity. Is some data unreliable, misleading, or inaccurate? Should it be ignored?
Use the following query to filter out records that should be ignored:
SELECT
*
FROM
source_name
WHERE field_name LIKE '%TEST%'
where source
is the name of the data source and field
is the name of the fields.
Poor distribution¶
Are some fields poorly distributed? Does this indicate that data is invalid or mislabeled?
A poorly distributed customer ID may indicate that a placeholder value, such as
0000
is being used to represent an unknown customer ID, order ID, or product ID.A poorly distributed product ID may indicate a discount or gift with purchase code being used in place of a SKU.
Use the following query to identify poor distribution, and then filter out that data:
SELECT
field_name
,COUNT(*)
FROM
source_name
GROUP BY 1
HAVING COUNT(*) > 5
ORDER BY 2 DESC
LIMIT 10
Returned items¶
Are returns expressed as separate line items or are they inline with purchases? Will transforms be required? Make sure that fields that contain returns data are tagged with the is-return semantic tag.
Unique or recycled order IDs¶
Are order IDs unique or are they recycled across data sources? Order IDs must be unique and must match across the Unified_Transactions and Unified_Itemized_Transactions tables. If order IDs are recycled, use the following SQL to ensure a unique order ID:
CONCAT(order_id, order_date)
The following query can be used to examine the uniqueness of order IDs:
SELECT
order_id
,COUNT(*)
FROM
source_name
GROUP BY 1
HAVING COUNT(*) > 1
where order_id
is the name of the field in which the order ID is located and source
is the name of the data source.
Run Stitch¶
Run Stitch to process interaction records. These types of records do not play a role in identity resolution, but Stitch maintains a series of tables with a column for the Amperity ID that can be used to associate interaction records to customer records using the Amperity ID.
When Stitch is finished processing, the following tables are made available to the customer 360 database:
Customer_Attributes
Transaction_Attributes
Transaction_Attributes_Extended
Unified_Itemized_Transactions
Unified_Product_Catalog
Unified_Transactions
Configure interactions tables¶
The customer 360 database must be updated for interaction records. This requires adding tables that are generated by Stitch when semantics are used to tag item- and order-level transaction data, along with product catalogs. Two tables contain calculated fields that are based on this data.
In addition, existing tables in the customer 360 database must be updated to ensure that data is rolled up correctly and is added to the customer profile.
Warning
It is recommended to add at least one source of high quality interactions records to the Sources tab with correctly tagged semantics before using the “Customer 360” template to create the customer 360 database.
The following tables are added by the “Customer 360” template when profile semantic tags are applied to customer records:
Customer_360
Merged_Customers
Unified_Coalesced
Unified_Customer
Unified_Scores
The following tables are added by the “Customer 360” template when transaction and itemized transaction semantic tags are applied to interactions records:
Customer_Attributes
Transaction_Attributes
Transaction_Attributes_Extended
Unified_Itemized_Transactions
Unified_Transactions
All of these tables are required to link customer interactions with your brands to their customer profiles and are a specific requirement of AmpIQ.
If the “Customer 360” template is used to create the customer 360 database before interactions records are also available, you must add these tables to the customer 360 database manually.
You must add the following tables to the customer 360 database:
Customer_Attributes¶
The Customer_Attributes table contains a series of flags that identify attributes about individuals, such as if that individual can be contacted, if a marketable email address, physical address, or phone number is available, if they are an employee, reseller, or if the individual represents a test account, along with identifying each individuals’s revenue relationship with a brand.
The Customer_Attributes table is automatically added to the customer 360 database when it is generated using the “Customer 360” database template.
Note
See Customer_Attributes Table for more information about this table, including a column reference and how to configure it for use within the customer 360 database.
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. This table is added automatically when semantics related to transactions are applied to interaction records.
The Transaction_Attributes table is a required table for the customer 360 database and is always built as a passthrough table from Stitch output.
To add the Transaction_Attributes table
From the Customer 360 tab, under All Databases, select the menu for the customer 360 database, and then click Edit.
From the Database Editor, click Add Table.
Name the table “Transaction_Attributes”.
Set the build mode to Passthrough.
From the Source Table drop-down, select the Transaction_Attributes table.
Click Next.
Add a table description. This enables a tooltip that is visible from other areas in Amperity.
From the Table Semantics drop-down, select Transaction_Attributes.
Under Version History, select Enable table version history.
Click Save.
Transaction_Attributes_Extended¶
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.
Extended transaction attributes are added to the Transaction_Attributes_Extended table, which is an output of Stitch. Extended transaction attributes are fully available to the customer 360 database and to segments.
The Transaction_Attributes_Extended table is a required table for the customer 360 database and is always built as a passthrough table from Stitch output.
To add the Transaction_Attributes_Extended table
From the Customer 360 tab, under All Databases, select the menu for the customer 360 database, and then click Edit.
From the Database Editor, click Add Table.
Name the table “Transaction_Attributes_Extended”.
Set the build mode to SQL.
From the Source Table drop-down, select the Transaction_Attributes_Extended table.
Click Next.
Add a table description. This enables a tooltip that is visible from other areas in Amperity.
Under Version History, select Enable table version history.
Click Save.
Unified_Itemized_Transactions¶
The Unified_Itemized_Transactions table contains every row of every stitched table with all 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.
The Unified_Itemized_Transactions table is a required table for the customer 360 database when transactions and itemized transaction data is available to your tenant and fields are tagged with txn-items/
semantic tags. The Unified_Itemized_Transactions table is built as a passthrough table from Stitch output.
To add the Unified_Itemized_Transactions table
From the Customer 360 tab, under All Databases, select the menu for the customer 360 database, and then click Edit.
From the Database Editor, click Add Table.
Name the table “Unified_Itemized_Transactions”.
Set the build mode to Passthrough.
From the Source Table drop-down, select the Unified_Itemized_Transactions table.
Click Next.
Add a table description. This enables a tooltip that is visible from other areas in Amperity.
Verify that semantic tags were applied correctly.
Tip
You can clear incorrectly tagged semantics individually or for the entire table. To clear semantic tags for the entire table, under Semantic Suggestions click Clear semantics.
Caution
Revenue and quantity fields should contain positive values when they represent a sale and negative values when they represent a return or cancellation.
From the Table Semantics drop-down, select Unified_Itemized_Transactions.
Under Version History, select Enable table version history.
Click Save.
Unified_Product_Catalog¶
The Unified_Product_Catalog table contains a row for every item in your product catalog, including descriptive attributes, such as name, brand, size, and color, grouping attributes, such as category, sub-category, class, and sub-class, and other attributes like MSRP, gender, and description.
The Unified_Product_Catalog table is a required table for the customer 360 database when transactions and itemized transaction data is available to your tenant and fields are tagged with pc/
semantic tags. The Unified_Product_Catalog table is built as a passthrough table from Stitch output.
To add the Unified_Product_Catalog table
From the Customer 360 tab, under All Databases, select the menu for the customer 360 database, and then click Edit.
From the Database Editor, click Add Table.
Name the table “Unified_Product_Catalog”.
Set the build mode to Passthrough.
From the Source Table drop-down, select the Unified_Product_Catalog table.
Click Next.
Add a table description. This enables a tooltip that is visible from other areas in Amperity.
Verify that semantic tags were applied correctly.
Tip
You can clear incorrectly tagged semantics individually or for the entire table. To clear semantic tags for the entire table, under Semantic Suggestions click Clear semantics.
From the Table Semantics drop-down, select Unified_Product_Catalog.
Click Save.
Unified_Transactions¶
The Unified_Transactions table contains one row for each unique transaction record, with each order ID associated to an Amperity ID.
The Unified_Transactions table is a required table for the customer 360 database when transactions and itemized transaction data is available to your tenant and fields are tagged with txn/
semantic tags. The Unified_Transactions table is built as a SQL table that is based on Stitch output.
To add the Unified_Transactions table
From the Customer 360 tab, under All Databases, select the menu for the customer 360 database, and then click Edit.
From the Database Editor, click Add Table.
Name the table “Unified_Transactions”.
Set the build mode to SQL.
Click Apply template, and then select Unified_Transactions.
Update the placeholder names (shown as “Domain:Table” in the query) and set field-level priorities.
Click Validate to verify the SQL runs without error.
Click Next. This opens the Database Table Definition page.
Add a table description. This enables a tooltip that is visible from other areas in Amperity.
Verify that the db/required and db/unique database field semantics were applied to the amperity_id column.
Verify that semantic tags were applied correctly.
Tip
You can clear incorrectly tagged semantics individually or for the entire table. To clear semantic tags for the entire table, under Semantic Suggestions click Clear semantics.
From the Table Semantics drop-down, select Unified_Transactions.
Under Version History, select Enable table version history.
Click Save.
Edit Customer360 table¶
You should update the Customer_360 table to rollup attributes from the Transaction_Attributes table.
To add profile attributes to the Customer360 table
From the Customer 360 tab, under All Databases, open the menu for the customer 360 database, and then select Edit. The Database Editor page opens.
Select the Customer_360 table.
Edit the Customer_360 table to update the
SELECT
statement for attributes from the Transaction_Attributes table, similar to:--- Transaction Attributes from Transaction_Attributes TA ,ta.multi_purchase_brand AS `multi_purchase_brand` ,ta.one_and_done AS `one_and_done` ,ta.early_repeat_purchaser AS `early_repeat_purchaser` ,ta.first_order_id AS `first_order_id` ,ta.first_order_datetime AS `first_order_datetime` ,ta.first_order_revenue AS `first_order_revenue` ,ta.first_order_total_items AS `first_order_total_items` ,ta.second_order_id AS `second_order_id` ,ta.second_order_datetime AS `second_order_datetime` ,ta.second_order_revenue AS `second_order_revenue` ,ta.second_order_total_items AS `second_order_total_items` ,ta.latest_order_id AS `latest_order_id` ,ta.latest_order_datetime AS `latest_order_datetime` ,ta.latest_order_revenue AS `latest_order_revenue` ,ta.latest_order_total_items AS `latest_order_total_items` ,ta.lifetime_preferred_purchase_brand AS `preferred_purchase_brand` ,ta.lifetime_order_revenue AS `lifetime_order_revenue` ,ta.lifetime_order_frequency AS `lifetime_order_frequency` ,ta.lifetime_total_items AS `lifetime_total_items` ,ta.lifetime_largest_order_value AS `lifetime_largest_order_value` ,ta.lifetime_average_order_value AS `lifetime_average_order_value` ,ta.lifetime_average_item_price AS `lifetime_average_item_price` ,ta.lifetime_average_num_items AS `lifetime_average_num_items`
Add a
LEFT JOIN
for the Transaction_Attributes table below theFROM
clause, similar to:LEFT JOIN Transaction_Attributes pa ON ta.amperity_id = mc.amperity_id
Click Next.
Click the icon or empty space in the Icon column to open the Select an Icon dialog box, after which you can choose an icon or search for additional icons on the Font Awesome website.
You may use any icon in the Font Awesome library.
Icon identifiers start with
fa-
, and then a unique string. For example:fa-home
(for ),fa-star
(for ), andfa-project-diagram
for ( ).Icons are available in four styles: regular (
far
), solid (fas
), light (fal
), and duotone (fad
).Use a style prefix in front of an icon identifier to apply that style. For example:
fad fa-home
for a duotone icon orfas fa-home
for a solid icon.Click Save.
Click Activate.
Run the customer 360 database.
Add product catalog rollups¶
You should update the Unified_Itemized_Transactions table to rollup attributes from the Unified_Product_Catalog table.
To add product catalog rollups to the Unified_Itemized_Transactions table
From the Customer 360 tab, under All Databases, open the menu for the customer 360 database, and then select Edit. The Database Editor page opens.
Select the Unified_Itemized_Transactions table.
Edit the Unified_Itemized_Transactions table to update the
SELECT
statement for attributes from the Unified_Product_Catalog table, similar to:SELECT uit.* ,upc.product_category AS `product_category` ,upc.product_description AS `product_description` ,upc.product_subcategory AS `product_subcategory` FROM Unified_Itemized_Transactions uit LEFT JOIN Unified_Product_Catalog upc ON uit.product_id = upc.product_id
Click Next. This opens the Database Table Definition page. Verify the information on this page.
Click Save.
Click Activate.
Run the customer 360 database¶
Run the customer 360 database to update the tables and make that updated data available to the Queries and Segments tabs.
Validate transactions data¶
Amperity provides a collection of SQL queries that you can use to validate the quality of your interactions records.
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.