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
Product catalogs
Transactions
Other interaction record types

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.
Run Stitch.

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.
Run the customer 360 database.
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
High variance
Item-level revenue
Low completion rates
Missing required fields
Poor data quality
Poor distribution
Returned items
Unique or recycled order IDs

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, or NULL 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.

Apply semantic tags¶

After you have reviewed the incoming data sources and have determined that they can meet all requirements for shaping them into itemized transactions, product catalogs, and transactions, use the following sections to step through the process of applying semantic tags:

Itemized transactions
Product catalogs
Transactions

Domain SQL vs. feeds¶

The most important decision to make when providing interaction records to Amperity is which method to use when applying semantic tags. You can do this directly from the Feed Editor and you can do this as the second step of defining a custom domain table using domain SQL.

Important

If you are using AmpIQ, this decision is critical, as some fields must be tagged in certain ways to enable the downstream predictive analytics that are available from the Metrics and Segments tabs.

These decisions exist independently of validating data quality and are focused on what is required to transform your data into the shape that is required by AmpIQ.

Some things to consider when determining the best approach for applying semantic tags:

What shape is the data after it has been loaded to the Feed Editor? Can all of the required tags be applied to fields that exist within the data?
What shape do you want to work with downstream, including the customer 360 database, segments that are sent from Amperity to downstream systems, and the Campaigns tab within Amperity.
Can the data be trimmed using a saved query prior to loading it to Amperity? Consider clickstream data, which offers a very wide set of fields, many of which are not applicable to using interaction records to associate your customers with their most important activities.
Do high quality customer records and interaction records exist side-by-side within this data source? In some cases, using domain SQL to build custom domain tables to capture the customer records separately from the interactions records may be necessary. In this scenario, the same feed can be used to load the data, after which two custom tables are built using domain SQL.

to itemized transactions¶

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 itemized transactions (txn-item/) semantic tags can be applied.

Tip

Use a custom domain table per data source, and then apply the semantic tags. This can minimize the amount of effort required to troubleshoot situations where itemized transactions data is being filtered incorrectly.

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.

To apply semantic tags to item-level records

Identify the field to which the order-id semantic tag will be applied. This field will be the order_id column in the Unified_Itemized_Transactions table.

Important

If the order ID corresponds to multiple distinct orders that occur across different dates concatenate the field that contains the order ID to the field that contains the order datetime as order-id.

Caution

The order ID and product ID combination, i.e. CONCAT(order_id, product_id) must be unique for a given data source.

Note

Transforms made in support of order ID semantic tagging may also be required for interaction records that contain the corresponding transaction-level details to provide support for downstream JOIN operations.
Identify the field to which a foreign key will be applied.

Tip

Is there an appropriate foreign key that is already in use with customer records that are made available to the Stitch process? Is it well-distributed across interaction records?

If fields in the data source do not (or cannot) meet the requirements for a foreign key, use the customer-id semantic tag. This field will be the customer_id column in the Unified_Itemized_Transactions table. There can be only one customer ID per order.

Warning

One field may be tagged as a foreign key on an item-level (non-stitched) data source. An Amperity ID column is not automatically added when more than one field is tagged.

Note

Transforms made in support of foreign key semantic tagging may also be required for interaction records that contain the corresponding transaction-level details to provide support for downstream JOIN operations.
Identify the field to which the product-id semantic tag will be applied. This field will be the product_id column in the Unified_Itemized_Transactions table.

Tip

Is the product ID shared across items in a product catalog, if available? When a product catalog is available you should concatenate the product ID to include variants of products, such as color, size, and other identifiers.
Identify the field to which the order-datetime semantic tag will be applied. This field will be the order_datetime column in the Unified_Itemized_Transactions table.

Caution

Convert the field associated with order-datetime to a datetime data type, if required.
Identify the field to which the is-return semantic tag will be applied. This field will be the is_return column in the Unified_Itemized_Transactions table.

Important

The field to which the is-return semantic is applied must represent a value that is TRUE when items are returns and FALSE when items are purchases and NULL when the value is unknown.
Identify the field to which the is-cancellation semantic tag will be applied. This field will be the is_cancellation column in the Unified_Itemized_Transactions table.

Important

The field to which the is-cancellation semantic is applied must represent a value that is TRUE when items are cancelled and FALSE when items are purchases and NULL when the value is unknown.
Identify the field to which the item-quantity semantic tag will be applied. This field will be the item_quantity column in the Unified_Itemized_Transactions table.

Important

The definition of quantity is often customer-specific and individual data sources may have different ways of measuring this value. For Amperity, define quantity as a field with a positive value when the line item represents a purchase and a negative value when the line item represents a return or a cancellation.
Identify the field to which the item-revenue semantic tag will be applied. This field will be the item_revenue column in the Unified_Itemized_Transactions table.

Important

The definition of revenue is often customer-specific and individual data sources may have different ways of measuring this value. For Amperity, define revenue as a field with a positive value when the line item represents a purchase and a negative value when the line item represents a return or a cancellation.

If the data source does not have a field that directly maps to revenue, build the field by tagging the following semantic tags to the best available fields, in addition to any custom semantic tags that may be required: item-cost, item-discount-amount, item-profit, item-subtotal, total-list-price, unit-cost, unit-discount-amount, unit-list-price, unit-profit, unit-revenue, and unit-subtotal.
Optional. Identify the field to which the item-discount-amount semantic tag will be applied. This field will be the item_discount_amount column in the Unified_Itemized_Transactions table.

Note

This field is required for discount sensitivity analysis workflows.

If the data source does not have a field that directly maps to item discount amounts, build the field by tagging the following semantic tags to the best available fields, in addition to any custom semantic tags that may be required: item-list-price, item-subtotal, unit-discount-amount, unit-list-price, and unit-subtotal.
Optional. Identify the field to which the item-profit semantic tag will be applied. This field will be the item_profit column in the Unified_Itemized_Transactions table.

Note

This field is required for profit analysis workflows.

If the data source does not have a field that directly maps to item profits, build the field by tagging the following semantic tags to the best available fields, in addition to any custom semantic tags that may be required: item-cost, item-revenue, unit-cost, unit-profit, and unit-revenue.
Identify any fields to custom semantic tags will be applied. These may be necessary for custom workflows or downstream ad hoc analysis of data quality.

to product catalogs¶

For each incoming data source that contains product catalog data, you may be able to apply the semantic tags directly from the Feed Editor. 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.

To apply semantic tags to product catalogs

Identify the field to which the product-id semantic tag will be applied. This field will be the product_id column in the Unified_Product_Catalog table.
Identify the field to which the product-category semantic tag will be applied.
Optional. Identify the fields to which the product-subcategory and product-description semantic tags will be applied. These fields are not available in all data sources.
Tip

If the data source does not contain obvious fields for product-subcategory and product-description, use empty fields in domain SQL to create them in the customer 360 database tables with empty values. For example:
```
,' ' AS `product_subcategory`
,' ' AS `product_description`
```

to transactions¶

For each incoming data source that contains order-level data, use the domain SQL editor to reshape the data into a schema to which transactions (txn/) semantic tags can be applied.

Important

Transactions must be transformed in way that allows each row to represent a distinct transaction for a given order.

To apply semantic tags to order-level records

Identify the field to which the order-id semantic tag will be applied. This field will be the order_id column in the Unified_Transactions table.

Important

If the order ID corresponds to multiple distinct orders that occur across different dates concatenate the field that contains the order ID to the field that contains the order datetime as order-id.

Caution

The order ID and product ID combination, i.e. CONCAT(order_id, product_id) must be unique for a given data source.

Note

Transforms made in support of order ID semantic tagging may also be required for interaction records that contain the corresponding transaction-level details to provide support for downstream JOIN operations.
Identify the field to which a foreign key will be applied.

Tip

Is there an appropriate foreign key that is already in use with customer records that are made available to the Stitch process? Is it well-distributed across interaction records?

If fields in the data source do not (or cannot) meet the requirements for a foreign key, use the customer-id semantic tag. This field will be the customer_id column in the Unified_Transactions table. There can be only one customer ID per order.

Note

Transforms made in support of foreign key semantic tagging may also be required for interaction records that contain the corresponding transaction-level details to provide support for downstream JOIN operations.
Identify the field to which the product-id semantic tag will be applied. This field will be the product_id column in the Unified_Transactions table.

Tip

Is the product ID shared across items in a product catalog, if available? When a product catalog is available you should concatenate the product ID to include variants of products, such as color, size, and other identifiers.
Identify the field to which the order-datetime semantic tag will be applied. This field will be the order_datetime column in the Unified_Transactions table.

Caution

Convert the field associated with order-datetime to a datetime data type, if required.
Identify the field to which the order-revenue semantic tag will be applied. This field will be the order_revenue column in the Unified_Transactions table.
Important

The definition of revenue is often customer-specific and individual data sources may have different ways of measuring this value. Identify any calculations that are required for validation, such as:
```
order_revenue = SUM(item_revenue) + order_discount
```
Optional. Identify the field to which the order-discount-amount semantic tag will be applied. This field will be the order_discount_amount column in the Unified_Transactions table.

Note

This field is required for discount sensitivity analysis workflows.

If the data source does not have a field that directly maps to order discount amounts, build the field by tagging the following semantic tags to the best available fields, in addition to any custom semantic tags that may be required: order_discount_percent_amount.
Optional. Identify the field to which the order-profit semantic tag will be applied. This field will be the order_profit column in the Unified_Transactions table.

Note

This field is required for profit analysis workflows.

Important

If order profit is the sum if item profit, roll this up from itemized transactions data. If order profit is the sum of item-level profit along with order-level details, use the order-cost semantic tag, along with any custom semantic tags that may be required.
Identify any fields to custom semantic tags will be applied. These may be necessary for custom workflows or downstream ad hoc analysis of data quality.

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
Transaction_Attributes
Transaction_Attributes_Extended
Unified_Itemized_Transactions
Unified_Product_Catalog
Unified_Transactions

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 the FROM 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 ), and fa-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 or fas 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.