About unified data tables

A customer 360 database is built using standard core tables that are generated by the Stitch process. These tables provide a unified view of your brand’s customer data, including customer profiles and interaction records, that is organized, merged, and linked together by the Amperity ID.

About the data model

The data model represents the “out-of-the-box” tables that are available to every tenant.

Important

This data model represents the starting point for all tenants. It is common for a tenant to have additional tables that support specific data requirements and workflows.

Data model diagram

The following diagram shows the data model for core tables in Amperity. Color coded sections identify which groups of tables are associated with customer profiles, interactions records, Stitch QA, and predictive modeling.

Note

Click this diagram to open it in your full browser window. Click HERE to open this diagram in a new tab or right-click that link to save a copy to your computer.

There are four groups of tables in this diagram:

Group name	Description
Customer records	A customer profile is a collection of attributes that are associated with a single unique individual in the customer 360 database. The total number of customer profiles is equal to the total number of rows in the Customer 360 data table. This total correlates strongly, but not exactly, to the total number of Amperity IDs assigned to unique individuals in the same data set. The Customer 360 table represents your primary set of customer profiles and is the most common starting point for building segments. Each customer profile is built using a combination of the Merged Customers, Customer Attributes, Unified Customer, and Unified Coalesced tables.
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.). Interaction records rely on a series of tables: Transaction Attributes, Transaction Attributes Extended, Unified Itemized Transactions, Unified Transactions, and Unified Product Catalog. Each Amperity ID in the Customer 360 table can be associated to many rows in the Unified Transactions table, and then each Amperity ID in the Unified Transactions can be associated to many rows in the Unified Itemized Transactions table. Each Amperity ID in the Customer 360 table is associated to one Amperity ID in the Transaction Attributes table. Note The Transaction Attributes table has an associated “extended” table that captures additional attributes when data sources provide to Amperity the data required to complete the calculations for the attributes. It exists as a separate table in the customer 360 database, but should be viewed as a subset of transactions attributes data.
Stitch results	Stitch QA is a process that monitors the quality of Stitch results. Stitch QA has two components: a database and a set of queries. The results of these queries are analyzed to help identify values that should be labeled or blocklisted and discover situations where the results of the Stitch process require tuning to match your tenant’s data set. Stitch QA activities rely on a series of tables: Unified Coalesced, Unified Scores, Detailed Examples, Unified Preprocessed Raw, Unified Changes Clusters, and Unified Changes PKS. These tables are the basis for the Stitch QA process; the use of any specific table will vary from tenant to tenant. Together they provide visibility into how Amperity grouped (or did not group) individual customer records to a single Amperity ID.
Predictive models	Predictive modeling tables are the results of the configuration and tuning of Amperity for predictive analytics. These tables rely on the Merged Customers, Unified Itemized Transactions, and Unified Transactions tables for predictions, but there is not a 1-to-1 or 1-to-many relationship between those three tables and predictive modeling tables. The Predicted CLV Attributes table contains one row per Amperity ID, whereas the Predicted Affinity table contains many rows per Amperity ID. The Campaign Recipients table contains a history of all campaigns that have been sent from Amperity. This table is updated on a recurring basis and may be used like any other table in your customer 360 database.

Data model indicators

This diagram uses the following indicators to highlight relationships between tables and to call out fields that are primary keys, establish links between tables, or associate this table back to a domain table in the Sources page:

Name	Description
1:1, 1:many	Indicates that this table has a 1:1 or 1:many relationship with another table. For most tables, this relationship is based on the Amperity ID.
Many:1	Indicates that this table has a many:1 relationship with another table. For most tables, this relationship is based on the Amperity ID.
Primary key	Indicates this column is the primary key for this table.
Linking key	Indicates this column links customer records, such as those associated with a customer key or a foreign key that were defined as part of a feed or custom domain table from the Sources page.
Data source	Indicates this column is associated to an original customer data source in the Sources page, with the value of this field being the name of that data source.

All Opted In Emails

The All Opted In Emails table contains all opted-in email addresses (and associated Amperity IDs). This table is built from the Email Opt Status table, after which it can be extended to support brands, region, email program, and language preferences.

Warning

The All Opted-In Emails table is not unique by Amperity ID and should not be used within the Segment Editor.

The All Opted In Emails table contains the same columns as the Email Opt Status table. Unlike the Email Opt Status table, the All Opted In Emails table should be made available to the Segment Editor. This ensures that the email attribute in this table is available to campaigns. (Choose the email attribute from the All Opted In Emails table from the Edit Attributes menu.)

Campaign Recipients

The Campaign Recipients table contains a list of Amperity IDs associated with any campaign that was sent from Amperity. Each campaign is associated with the segment used to generate the list of recipients, the control and treatment groups (including sub-audiences) used for the campaign, its launch date, and all destinations to which the campaign was sent.

The Campaign Recipients table is generated by Amperity, and then made available as a standard database table. Use this table to access campaign attributes, perform historical campaign analysis, and to build segments and multi-stage campaigns that use the results of previous campaigns.

Important

Data in the Campaign Recipients table is updated after the next database run. If your database is configured to run once per day then data in the Campaign Recipients table will be available on a 1-day delayed rolling basis.

Note

See Campaign Recipients for more information about how this table is built and maintained within Amperity.

The Campaign Recipients table contains the following columns:

Column name	Data type	Description
Amperity ID	String	The unique identifier that is assigned to clusters of customer records that all represent the same individual. The Amperity ID does not replace primary and foreign keys, but exists alongside them within unified profiles. Note The Amperity ID is a universally unique identifier (UUID) that is represented by 36 characters spread across five groups separated by hyphens: 8-4-4-4-12. For example: 123e4567-e89b-12d3-a456-426614174000
Campaign ID	String	The unique ID for a campaign.
Campaign Name	String	The name of the campaign.
Campaign Type	String	A campaign is a message or offer that is sent to a specific group of customers or recipients. A campaign may be one of the following types: One-time Recurring A one-time campaign represents a specific campaign message that is configured to be sent only once. A recurring campaign is sent automatically based on an updated/refreshed audience that a customer wants to receive in their outbound destination or a state change, such as an accepted return, a change to a loyalty program, or an alert based on credit card status, with a predefined campaign message and cadence to a list of recipients.
Database ID	String	The unique ID for the database.
Database Name	String	The name of the database.
Dataset Version	String	A unique ID for the dataset used with this set of campaign recipients.
Delivery Datetime	Datetime	The date and time at which the associated campaign ID was delivered to the destination.
Destination ID	String	The unique ID for a destination.
Destination Name	String	The name of the destination to which the associated campaign ID was sent.
Is Control	Boolean	A flag that indicates if the Treatment ID represents a control group.
Launch Datetime	Datetime	The date and time at which the associated campaign ID was sent from Amperity to its downstream workflow.
PK	String	The combination of data source and primary key allows Amperity to uniquely identify every row in every data table across the entirety of customer data input to Amperity.
Segment ID	String	The unique ID for the segment that generated the list of recipients for the associated campaign ID.
Segment Name	String	The name of the segment used with the associated campaign ID.
Sub Audience ID	String	The unique ID for the sub-audience to which the associated campaign was sent.
Sub Audience Name	String	The name of the sub-audience to which the associated campaign was sent.
Target ID	String	The unique ID for the data template that was used to send campaign data to a downstream channel or workflow.
Target Name	String	The name of the data template that was used to send campaign data to a downstream channel or workflow.
Treatment ID	String	The ID for the treatment group to which the associated campaign was sent.
Treatment Name	String	The name of the treatment group to which the associated campaign was sent. One or more treatment groups, along with a control group, are used to measure the quality of a campaign.
Workflow ID	String	The unique ID for the workflow that managed the associated campaign.

Customer 360

The Customer 360 table is the unified view of the customer across all points of engagement, including attributes that cross systems. This table does not exist by default and must be created within the customer 360 database. Each row represent a complete record for a unique individual, including their Amperity ID, merged PII data, and summary attributes.

Note

See Customer 360 for more information about how this table is built and maintained within the customer 360 database.

Note

The columns that appear in the Customer 360 table will vary, depending on the SQL statement used to add columns to the table. The set of columns must include the Amperity ID and should include columns that contain profile (PII) data, along with columns that contain summary attributes for interaction records.

The following table represents a Customer 360 table with profile data and a few summary attributes based on interaction records for transaction data:

Column Name	Data type	PII	Description
Amperity ID	String		The unique identifier that is assigned to clusters of customer records that all represent the same individual. The Amperity ID does not replace primary and foreign keys, but exists alongside them within unified profiles. Note The Amperity ID is a universally unique identifier (UUID) that is represented by 36 characters spread across five groups separated by hyphens: 8-4-4-4-12. For example: 123e4567-e89b-12d3-a456-426614174000
Given Name	String		The first name that is associated with a customer. Also in: Merged Customers, Unified Coalesced, Unified Customer, Unified Preprocessed Raw
Surname	String		The last name that is associated with a customer. Also in: Merged Customers, Unified Coalesced, Unified Customer, Unified Preprocessed Raw
Email	String		The email address that is associated with a customer. A customer may have more than one email address. Also in: Merged Customers, Unified Coalesced, Unified Customer, Unified Preprocessed Raw
Phone	String		The phone number that is associated with a customer. A customer may have more than one phone number. Also in: Merged Customers, Unified Coalesced, Unified Customer, Unified Preprocessed Raw
Address	String		The address that is associated with the location of a customer, such as “123 Main Street”. Also in: Merged Customers, Unified Coalesced, Unified Customer, Unified Preprocessed Raw
City	String		The city that is associated with the location of a customer. Also in: Merged Customers, Unified Coalesced, Unified Customer, Unified Preprocessed Raw
State	String		The state or province that is associated with the location of a customer. Also in: Merged Customers, Unified Coalesced, Unified Customer, Unified Preprocessed Raw
Postal	String		The zip code or postal code that is associated with the location of a customer. Also in: Merged Customers, Unified Coalesced, Unified Customer, Unified Preprocessed Raw
Birthdate	Date		The date of birth that is associated with a customer. Also in: Merged Customers, Unified Coalesced, Unified Customer, Unified Preprocessed Raw
Gender	String		The gender that is associated with a customer. Also in: Merged Customers, Unified Coalesced, Unified Customer, Unified Preprocessed Raw

Customer Attributes

The Customer Attributes table contains a series of columns 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 contains the following columns:

Column name	Data type	Description
Amperity ID	String	The unique identifier that is assigned to clusters of customer records that all represent the same individual. The Amperity ID does not replace primary and foreign keys, but exists alongside them within unified profiles. Note The Amperity ID is a universally unique identifier (UUID) that is represented by 36 characters spread across five groups separated by hyphens: 8-4-4-4-12. For example: 123e4567-e89b-12d3-a456-426614174000
Churn Trigger	String	The churn status for a customer. Possible values: Active Cooling down At risk Highly at risk Lost Blank Tip Use these values as part of your churn prevention campaigns.
Churn Trigger Start Datetime	Datetime	The date and time at which the Churn Trigger status begins.
Contactable Address	Boolean	A flag that indicates if a customer can be contacted by a physical mailing address.
Contactable Email	Boolean	A flag that indicates if a customer has an email address with a valid format.
Contactable Global	Boolean	A flag that indicates if a customer can be contacted by phone number, email address, or physical mailing address.
Contactable Paid Social	Boolean	A flag that indicates if a customer has personally identifiable information (PII) that could be used to contact them using paid social media channels.
Contactable Phone	Boolean	A flag that indicates if a customer has a phone number with a valid format.
Historical Purchaser Lifecycle Status	String	The status for a customer, based on their history and recency of interactions a brand. Possible values: “new”, “active”, “lapsed”, “dormant”, and “prospect”. Customer states are defined as “active”, “lapsed”, “dormant”, and “prospect”. Purchase behaviors are assigned across a 5 year window. A customer who has purchased within the previous 365 days (1 year) is assigned to “active” and within the previous 730 days (2 years) is assigned to “lapsed”. A customer who has not purchased within 2 years is assigned to “dormant”. Note The ranges for this field are customizable. The default ranges represent: “New” represents customers who have purchased within the previous 30 days. “Active” represents customers who have purchased within the previous 365 days. “Lapsed” represents customers who have purchased between 365 and 730 days ago. “Dormant” represents customers who have not purchased within the previous 730 days (two years). “Prospect” represents individuals who have not made a purchase.
Is Business	Boolean	A flag that indicates if a customer is a known or likely business.
Is Employee	Boolean	A flag that indicates if a customer is or has been an employee of the brand at any time.
Is Gift Giver	Boolean	A flag that indicates if a customer has purchased items as gifts.
Is No PII Amperity ID	Boolean	A flag that indicates if the customer does not have personally identifiable information (PII) for name (given name, surname, full name), address (street address, city, state, postal code), email address, or phone number.
Is Opted Into Email	Boolean	A flag that indicates if the customer has given consent to receive email communications from your brand.
Is Opted Into SMS	Boolean	A flag that indicates if the customer has given consent to receive SMS messages from your brand.
Is Outlier	Boolean	A flag that indicates if the customer has abnormally high purchase behaviors in comparison to other purchasers.
Is Primary Buyer Household	Boolean	A flag that indicates if a customer is the individual within a household who represents the highest lifetime revenue for that household. Note This attribute requires the Merged Households table.
Is Prospect	Boolean	A flag that indicates if a customer does not have a purchase history with a brand.
Is Purchaser	Boolean	A flag that indicates if the customer has a purchase history with a brand.
Is Reseller	Boolean	A flag that indicates if the customer is a known or likely reseller of a product. Tip A reseller should be defined as an early repeat purchaser with a high lifetime order frequency. Use the following fields from the Transaction Attributes table to define a reseller: Early Repeat Purchaser (set to true) Lifetime Order Frequency (define an order frequency threshold, such as “greater than 50”)
Is Test Account	Boolean	A flag that indicates if the customer is a known test account for a brand.

Detailed Examples

The Detailed Examples table contains detailed examples of Stitch results. Use these examples to help identify which features lead to scores with the biggest effect on overall Stitch results, including how they are associated with various combinations of fields that contain PII data.

Note

The Detailed Examples table is a subset of the Unified Scores table.

The Detailed Examples table contains the following columns, as its starting point. This table is typically updated to add more pairs, enable use for blocklisted values, and to support additional tenant-specific use cases.

Column name	Data type	Description
Amperity ID	String	The unique identifier that is assigned to clusters of customer records that all represent the same individual. The Amperity ID does not replace primary and foreign keys, but exists alongside them within unified profiles. Note The Amperity ID is a universally unique identifier (UUID) that is represented by 36 characters spread across five groups separated by hyphens: 8-4-4-4-12. For example: 123e4567-e89b-12d3-a456-426614174000
Address A Address B	String	The address that is associated with the location of a customer, such as “123 Main Street”.
Birthdate A Birthdate B	String	The date of birth that is associated with a customer.
Case Count	String
City A City B	String	The city that is associated with the location of a customer.
Country A Country B	String	The country that is associated with the location of a customer.
Datasource A Datasource B	String	The name of the data source from which this customer record originated. Tip The combination of PK and Datasource uniquely identifies a row in the Unified Coalesced table, which correlates to a single row in a domain table.
Email A Email B	String	The email address that is associated with a customer. A customer may have more than one email address.
Given Name A Given Name B	String	The first name that is associated with a customer.
Match Category	String	A match category is a classifier that is applied by Amperity to an individual record-pair within a cluster of record-pairs. The match category is the result of this classification. Match Category Description Exact Amperity has the highest confidence that these records represent the same person because all profile data exactly matches. Excellent Amperity has near perfect confidence that these records belong to the same person, despite select types of profile data not matching. High Using deductive reasoning, Amperity has very high confidence that these records match, despite some profile data not matching. Moderate Amperity has moderate confidence that these records match, due to weak or fuzzy matches between highly unique customer attributes (email, phone, address). Weak Amperity lacks confidence, but if asked to guess, Amperity would assert these records do belong to the same individual, because they match on non-unique customer attributes (name, state, zip code). Non-match Amperity has high confidence that these records do NOT match, because core profile data is in conflict. Also in: Unified Scores
Phone A Phone B	String	The phone number that is associated with a customer. A customer may have more than one phone number.
PK A PK B	String	The combination of data source and primary key allows Amperity to uniquely identify every row in every data table across the entirety of customer data input to Amperity. Tip The combination of PK and Datasource uniquely identifies a row in the Unified Coalesced table, which correlates to a single row in a domain table.
Postal A Postal B	String	The zip code or postal code that is associated with the location of a customer.
Score	Float	A score contains a value from 0.0 to 5.0 that represents the combined score assigned to the record pair by Stitch. There are two components of the score: the score itself, and then its strength. The record pair score correlates to the match category, which is a classifier that is applied by Amperity to individual record pairs. The record pair score corresponds to the classification: 5 for exact matches, 4 for excellent matches, 3 for high matches, 2 for moderate matches, 1 for weak matches, and 0 for no matches. The record pair strength represents the strength of the record pair score. It is a two digit number. For example: .31 is a lower strength and .93 is a very high strength. Note Scores are shown for records that end up in the same cluster, including any scores that are below threshold. Scores are not shown for records that do not end up in the same cluster. Also in: Unified Scores
Score Count	String
State A State B	String	The state or province that is associated with the location of a customer.
Surname A Surname B	String	The last name that is associated with a customer.

Email Engagement Attributes

The Email Engagement Attributes table contains many of the same fields as the Email Engagement Summary table, except for the addition of the Amperity ID field. Whereas the Email Engagement Summary table is unique by email and brand (if available), the Email Engagement Attributes table is unique by the Amperity ID and email for each brand combination.

Note

In the Email Engagement Attributes table, each Amperity ID should only have one email address, per brand.

Tip

The Email Engagement Attributes table pulls the email engagement data, for each Amperity ID, from the Email Engagement Summary table using the email associated with it in the Merged Customers table.

Note

The Email Engagement Attributes table can be selected as a source in the Segment Editor to segment customers based on their email behavior.

The Email Engagement Attributes table contains the following columns:

Column name	Data type	Description
Amperity ID	String	This column is input to predictive modeling. The unique identifier that is assigned to clusters of customer records that all represent the same individual. The Amperity ID does not replace primary and foreign keys, but exists alongside them within unified profiles. Note The Amperity ID is a universally unique identifier (UUID) that is represented by 36 characters spread across five groups separated by hyphens: 8-4-4-4-12. For example: 123e4567-e89b-12d3-a456-426614174000
Brand	String	The brand or company from which an email was sent.
Email	String	The email address to which an email was sent.
Email Clicks Last X Day	Integer	The number of email clicks in the last 1, 3, 5, 7, or 14 days.
Email Clicks Last X Months	Integer	The number of email clicks in the last 3, 6, 9, or 12 months.
Email Opens Last X Day	Integer	The number of email opens in the last 1, 3, 5, 7, or 14 days.
Email Opens Last X Months	Integer	The number of email opens in the last 3, 6, 9, or 12 months.
Engagement Frequency Last 15 Months	Varchar	A classification that measures engagement frequency click rates for email addresses that have received a low volume of emails. Possible values: Non-recipient (0 received emails) New (fewer than 5 received emails) Active (currently engaging with received emails) Inactive (not currently engaging with received emails) Important Send rates must be available.
Engagement Status Last 15 Months	Varchar	A classification that measures click rates for email addresses that have received a low volume of emails. Possible values: Non-recipient (0 received emails) New (fewer than 5 received emails) Low (modeled open rate is in the lower third) Medium (modeled open rate is in the middle third) High (modeled open rate is in the upper third) Important Send rates must be available.
First Email Click Datetime	Datetime	The date and time at which an email was first clicked.
First Email Open Datetime	Datetime	The date and time at which an email was first opened.
First Email Send Datetime	Datetime	The date and time at which an email was sent.
Most Recent Email Bounce Datetime	Datetime	The date and time for the most recent bounced email.
Most Recent Email Click Datetime	Datetime	The date and time at which a customer most recently clicked a link or offer within an opened email.
Most Recent Email Open Datetime	Datetime	The date and time at which a customer most recently opened an email.
Most Recent Email Optin Datetime	Datetime	The date and time at which a customer most recently opted-in to receiving email.
Most Recent Email Optout Datetime	Datetime	The date and time at which a customer most recently opted-out from receiving email.
Most Recent Email Send Datetime	Datetime	The date and time at which an email was most recently sent.
Purchase Before Signup	Boolean	This flag indicates whether an Amperity ID is associated with a previous transaction before signing up with this email address.
Signup To Purchase Days	Integer	The number of days between the time this email was used to signup and the next transaction associated with an Amperity ID. Note This field will appear null if there is no transaction(s) made after the email signup.

Email Engagement Summary

The Email Engagement Summary table contains a summary of email event statistics, such as counts for opens and clicks, the first open, and the most recent click, unique by email address.

Note

The Email Engagement Summary table can be used for analysis and to inform the selection of the best email in the Email Amperity ID Assignment table.

The Email Engagement Summary table contains the following columns:

Column name	Data type	Description
Brand	String	The brand or company from which an email was sent.
Email	String	The email address to which an email was sent.
Email Clicks Last X Day	Integer	The number of email clicks in the last 1, 3, 5, 7, or 14 days.
Email Clicks Last X Months	Integer	The number of email clicks in the last 3, 6, 9, or 12 months.
Email Opens Last X Day	Integer	The number of email opens in the last 1, 3, 5, 7, or 14 days.
Email Opens Last X Months	Integer	The number of email opens in the last 3, 6, 9, or 12 months.
Engagement Frequency Last 15 Months	Varchar	A classification that measures engagement frequency click rates for email addresses that have received a low volume of emails. Possible values: Non-recipient (0 received emails) New (fewer than 5 received emails) Active (currently engaging with received emails) Inactive (not currently engaging with received emails) Important Send rates must be available.
Engagement Status Last 15 Months	Varchar	A classification that measures click rates for email addresses that have received a low volume of emails. Possible values: Non-recipient (0 received emails) New (fewer than 5 received emails) Low (modeled open rate is in the lower third) Medium (modeled open rate is in the middle third) High (modeled open rate is in the upper third) Important Send rates must be available.
First Email Click Datetime	Datetime	The date and time at which an email was first clicked.
First Email Open Datetime	Datetime	The date and time at which an email was first opened.
First Email Send Datetime	Datetime	The date and time at which an email was sent.
Most Recent Email Bounce Datetime	Datetime	The date and time for the most recent bounced email.
Most Recent Email Click Datetime	Datetime	The date and time at which a customer most recently clicked a link or offer within an opened email.
Most Recent Email Open Datetime	Datetime	The date and time at which a customer most recently opened an email.
Most Recent Email Optin Datetime	Datetime	The date and time at which a customer most recently opted-in to receiving email.
Most Recent Email Optout Datetime	Datetime	The date and time at which a customer most recently opted-out from receiving email.
Most Recent Email Send Datetime	Datetime	The date and time at which an email was most recently sent.
Purchase Before Signup	Boolean	This flag indicates whether an Amperity ID is associated with a previous transaction before signing up with this email address.
Signup To Purchase Days	Integer	The number of days between the time this email was used to signup and the next transaction associated with an Amperity ID. Note This field will appear null if there is no transaction(s) made after the email signup.

Fiscal Calendar

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.

The Fiscal Calendar table is generated by Amperity when fiscal calendar semantic tags are applied to source data tables.

The Fiscal Calendar table contains the following columns:

Column name	Data type	Description
Calendar Date	Date	The standard calendar date. Important The field to which this semantic tag is applied must also be the primary key for the table.
Day Of Week	String	The day of the week.
Fiscal Month	String	The fiscal month that is associated with the calendar date.
Fiscal Quarter	String	The fiscal quarter that is associated with the calendar date.
Fiscal Week End	Date	The calendar date on which the fiscal week ends.
Fiscal Week Number	Integer	The number of the week within the fiscal year. This field indicates on which month a fiscal year starts.
Fiscal Week Start	Date	The calendar date on which the fiscal week starts.
Fiscal Year	Integer	The fiscal year that is associated with the calendar date.
Holiday Sale Name	String	The holiday date (or date range) to which this date belongs.

Merged Customers

The Merged Customers table is a data table that must be added to a customer 360 database. The purpose of the Merged Customers table is to collect rows from the Unified Coalesced table, and then collapse those into a single row per Amperity ID. Columns in the Merged Customers table are made available to segmentation via the Customer 360 table.

Note

See Merged Customers for more information about how this table is built and maintained within the customer 360 database.

The Merged Customers table contains the following columns:

Column name	Data type	Description
Amperity ID	String	This column is input to predictive modeling. The unique identifier that is assigned to clusters of customer records that all represent the same individual. The Amperity ID does not replace primary and foreign keys, but exists alongside them within unified profiles. Note The Amperity ID is a universally unique identifier (UUID) that is represented by 36 characters spread across five groups separated by hyphens: 8-4-4-4-12. For example: 123e4567-e89b-12d3-a456-426614174000
Address	String	The address that is associated with the location of a customer, such as “123 Main Street”. Also in: Customer 360, Unified Coalesced, Unified Customer, Unified Preprocessed Raw
Address2	String	Additional address information, such as an apartment number or a post office box, that is associated with the location of a customer, such as “Apt #9”. Also in: Unified Coalesced, Unified Customer
Birthdate	Date	This column is input to predictive modeling. The date of birth that is associated with a customer. Also in: Customer 360, Unified Coalesced, Unified Customer, Unified Preprocessed Raw
City	String	This column is input to predictive modeling. The city that is associated with the location of a customer. Also in: Customer 360, Unified Coalesced, Unified Customer, Unified Preprocessed Raw
Company	String	The company, typically an employer or small business, that is associated with a customer.
Country	String	The country that is associated with the location of a customer. Also in: Unified Coalesced, Unified Customer
Create DT	String	Apply the create-dt semantic tag to columns in customer records that identify when the data was created. The field to which this semantic is applied must be a datetime field type. Also in: Unified Coalesced, Unified Customer
Email	String	This column is input to predictive modeling. The email address that is associated with a customer. A customer may have more than one email address. Also in: Customer 360, Unified Coalesced, Unified Customer, Unified Preprocessed Raw
Full Name	String	A combination of given name (first name) and surname (last name) for a customer. May include a middle name or initial. Also in: Unified Coalesced, Unified Customer
Gender	String	This column is input to predictive modeling. The gender that is associated with a customer. Also in: Customer 360, Unified Coalesced, Unified Customer, Unified Preprocessed Raw
Generational Suffix	String	The suffix that identifies to which family generation a customer record belongs. For example: Jr., Sr. II, and III. Also in: Unified Coalesced, Unified Customer
Given Name	String	This column is input to predictive modeling. The first name that is associated with a customer. Also in: Customer 360, Unified Coalesced, Unified Customer, Unified Preprocessed Raw
Loyalty ID	String	The identifier for a loyalty program that is associated with a customer. This column is added when the loyalty-id semantic tag is applied to customer profiles. Also in: Unified Coalesced
Phone	String	This column is input to predictive modeling. The phone number that is associated with a customer. A customer may have more than one phone number. Also in: Customer 360, Unified Coalesced, Unified Customer, Unified Preprocessed Raw
Postal	String	This column is input to predictive modeling. The zip code or postal code that is associated with the location of a customer. Also in: Customer 360, Unified Coalesced, Unified Customer, Unified Preprocessed Raw
State	String	This column is input to predictive modeling. The state or province that is associated with the location of a customer. Also in: Customer 360, Unified Coalesced, Unified Customer, Unified Preprocessed Raw
Surname	String	This column is input to predictive modeling. The last name that is associated with a customer. Also in: Customer 360, Unified Coalesced, Unified Customer, Unified Preprocessed Raw
Title	String	The title that precedes a full name that is associated with a customer, such as “Mr.”, “Mrs”, and “Dr”. Also in: Unified Coalesced, Unified Customer
Update DT	String	Apply the update-dt semantic tag to columns in customer records that identify when the data was last updated in the source system. The field to which this semantic is applied must be a datetime field type. At least one customer record must have this semantic tag applied to ensure that the update_dt column is created in the Unified Coalesced table and to ensure that the Merged Customers table behaves correctly. Also in: Unified Coalesced, Unified Customer

Additional columns in the Merged Customers table

The Merged Customers table contains additional columns that help you understand how and why customer profile values are present in the Merged Customers table.

These column names start with one of Name, Address, Email, Phone, Birthday, or Gender, and then are grouped as described in the following table. For example: Name Completion, Name Datasource, Name PK, Name Priority, and Name Update DT.

Column Suffix	Description
Completion	The number of NOT NULL values that are present in a set of data, as defined in Merged Customers. Combine the use of this column with Priority to understand why a record was selected. A name has three possible values (Given Name, Surname, and Full Name). When all three values are NOT NULL, the value in the Name Completion column will be 3. An address has four possible values (Address, City, State, and postal). When all four values are NOT NULL, the value in the Address Completion column will be 4. An email address has one possible value (Email). When this value is NOT NULL, the value in the Email Completion column will be 1. A phone number has one possible value (Phone). When this value is NOT NULL, the value in the Phone Completion column will be 1. A birthdate has one possible value (Birthdate). When this value is NOT NULL, the value in the Birthdate Completion column will be 1. Gender has one possible value (Gender). When this value is NOT NULL, the value in the Gender Completion column will be 1.
Datasource	The source data table from which the customer profile value originates. Combine the use of this column with PK to find the record in the source domain table.
PK	The primary key for the record in Datasource. Combine the use of this column with Datasource to find the record in the source domain table.
Priority	The priority that is assigned to the source domain table in Merged Customers. Combine the use of this column with Completion to understand why a record was selected.
Update DT

Opt-in status tables

Standard output tables are available that make available your customer’s preferences for email and phone communications from your brand. Use these preferences to determine when and how your brand can use email and SMS to communicate with your customers.

1. Use the Email Opt Status table when your brand communicates with your customers using their email address.

2. Use the SMS Opt Status table when your brand communicates with your customers using their phone number.

Email Opt Status

The Email Opt Status table contains a row for each unique combination of email address, brand, region, and email program.

This table is generated when email-opt/ semantic tags are applied to data sources that contain data that describes your customer’s consent status and gives insight into which customers are available to be used as part of an email-based marketing campaign.

Important

Amperity is not the source of truth for email consent status. Email consent status can change quickly, including between the time of this table’s most recent update and the time at which your brand wants to send your customers an email as part of a campaign.

The source of truth for consent status exists downstream from Amperity, often directly within the marketing tool or application that you are using to configure the email campaign, such as Cordial, Braze, Klaviyo, or Attentive.

Use this table to filter audiences in Amperity to include customers who have consented to receiving email messages, and then build a step within the downstream marketing tool that verifies consent status immediately prior to sending an email.

Note

The Email Opt Status table represents every email address for which you have provided customer consent data to Amperity. There should be only one consent status by combination of email address, brand, region, or email program.

If you have multiple brands, regions or email programs, it’s possible for the same email address to have more than one customer consent status.

If a brand, region, or email program does not exist, there should be only one customer consent status for each unique email address.

The Email Opt Status table contains the following columns:

Semantic Name	Datatype	Description
Amperity ID	String	The unique identifier that is assigned to clusters of customer records that all represent the same individual. The Amperity ID does not replace primary and foreign keys, but exists alongside them within unified profiles.
Brand	String	The brand to which the opt-in status applies.
Email	String	The email address that is associated with a customer. A customer may have more than one email address.
Email Frequency	String	The preferred frequency for email messages.
Email Program	String	The email program to which the customer has opted-in.
Is Email Opted In	Boolean	Indicates whether a customer has given consent to being contacted by your brand using the customer’s email address.
Language Preference	String	The customer’s preferred language for email messages.
Region	String	The region to which the opt-in status applies.

SMS Opt Status

The SMS Opt Status table contains a row for each unique combination of phone number, brand, region, and SMS program.

This table is generated when sms-opt/ semantic tags are applied to data sources that contain data that describes your customer’s consent status and gives insight into which customers are available to be used as part of an SMS-based marketing campaign.

Important

Amperity is not the source of truth for SMS consent status. SMS consent status can change quickly, including between the time of this table’s most recent update and the time at which your brand wants to send your customers an SMS message as part of a campaign.

The source of truth for consent status exists downstream from Amperity, often directly within the marketing tool or application that you are using to configure the SMS campaign, such as Cordial, Braze, Klaviyo, or Attentive.

Use this table to filter audiences in Amperity to include customers who have consented to receiving SMS messages, and then build a step within the downstream marketing tool that verifies consent status immediately prior to sending an SMS message.

Note

The SMS Opt Status table represents every phone number for which you have provided customer consent data to Amperity. There should be only one consent status by combination of phone number, brand, region, or SMS program.

If you have multiple brands, regions or SMS programs, it’s possible for the same phone number to have more than one customer consent status.

If a brand, region, or SMS program does not exist, there should be only one customer consent status for each unique phone number.

The SMS Opt Status table contains the following columns:

Semantic Name	Datatype	Description
Amperity ID	String	The unique identifier that is assigned to clusters of customer records that all represent the same individual. The Amperity ID does not replace primary and foreign keys, but exists alongside them within unified profiles.
Brand	String	The brand to which the opt-in status applies.
Is SMS Opted In	Boolean	Indicates whether a customer has opted-in to being contacted by your brand using the customer’s phone number.
Language Preference	String	The customer’s preferred language for SMS messages.
Phone	String	The phone number that is associated with a customer. A customer may have more than one phone number.
Region	String	The region to which the opt-in status applies.
SMS Frequency	String	The preferred frequency for SMS communications.
SMS Program	String	The SMS program to which the customer has opted-in.

Predicted Affinity

An Affinity table associates individual customers to the products they are most likely to purchase. Use an Affinity table to help deliver personalized experiences to your customers.

Note

See Predicted Affinity for more information about how this table is built and maintained within the customer 360 database.

An Affinity table contains the following columns:

Column name	Data type	Description
Amperity ID	String	The unique identifier that is assigned to clusters of customer records that all represent the same individual. The Amperity ID does not replace primary and foreign keys, but exists alongside them within unified profiles. Note The Amperity ID is a universally unique identifier (UUID) that is represented by 36 characters spread across five groups separated by hyphens: 8-4-4-4-12. For example: 123e4567-e89b-12d3-a456-426614174000
Audience Size Large	Boolean	A flag that indicates the recommended audience size. When this value is True the recommended audience size is large. A large audience is predicted to include ~90% of future purchasers, while also including a high number of non-purchasers.
Audience Size Medium	Boolean	A flag that indicates the recommended audience size. When this value is True the recommended audience size is medium. A medium audience is predicted to include ~70% of future purchasers, though it may also include a moderate number of non-purchasers.
Audience Size Small	Boolean	A flag that indicates the recommended audience size. When this value is True the recommended audience size is small. A small audience is predicted to include ~50% of future purchasers, while including the fewest non-purchasers. Use a small audience size to help prevent wasted spend and reduce opt-outs.
Product Attribute	String	The field against which product affinity is measured. For example: a category, a class, or a brand.
Ranking	Integer	A ranking of customers by their score for this product. A rank that is less than or equal to X will provide the top N customers with an affinity for this product.
Score	Float	The strength of a customers’s affinity for this product, shown as an uncalibrated probability. Tip The score is used internally by Amperity, does not directly correlate to ranking and/or audience size, and should not be used in segments. Sort results by Ranking, and then compare those results to audience sizes. Higher rankings within smaller audience sizes correlate with stronger affinity.

Predicted CLV Attributes

The Predicted CLV Attributes table contains information, for each individual Amperity ID, about predicted future spend and the probability of churn.

Note

See Predicted CLV Attributes for more information about how this table is built and maintained within the customer 360 database and how it interacts with features within predictive modeling.

The Predicted CLV Attributes table contains the following columns:

Column name	Data type	Description
Amperity ID	String	The unique identifier that is assigned to clusters of customer records that all represent the same individual. The Amperity ID does not replace primary and foreign keys, but exists alongside them within unified profiles. Note The Amperity ID is a universally unique identifier (UUID) that is represented by 36 characters spread across five groups separated by hyphens: 8-4-4-4-12. For example: 123e4567-e89b-12d3-a456-426614174000
Predicted Average Order Revenue Next 365D	Decimal	The predicted average order revenue over the next 365 days.
Predicted CLV Next 365D	Decimal	The total predicted customer spend over the next 365 days.
Predicted Customer Lifecycle Status	String	A probabilistic grouping of a customer’s likelihood to purchase again. For repeat customers, groupings include the following tiers: Active (likelihood to purchase is greater than 60%) Cooling down (likelihood to purchase is between than 50% and 60%) At risk (likelihood to purchase is between than 35% and 50%) Highly at risk (likelihood to purchase is between than 20% and 35%) Lost (likelihood to purchase is less than 20%) For one-time buyers, groupings include the following tiers: Active (purchased within 60 days) Cooling down (purchased 60-120 days ago) At risk (purchased 120-180 days ago) Highly at risk (purchased 180-240 days ago) Lost (purchased 240+ days ago)
Predicted Customer Lifetime Value Tier	String	A percentile grouping of customers by predicted CLV. Groupings include: Platinum: top 1% of customers Gold: top 1%-5% of customers Silver: top 5%-10% of customers Bronze: top 10%-25% of customers Medium: top 25%-50% of customers Low: bottom 50% of customers Note This attribute returns only the customers who belong to the selected value tier. For example, to return all of your top 10% customers, you must choose platinum, gold, and silver. Silver by itself will return 5% of your customers, specifically those are in your 5-10%.
Predicted Order Frequency Next 365D	Decimal	The predicted number of orders over the next 365 days.
Predicted Probability Of Transaction Next 365D	Decimal	The probability a customer will purchase again in the next 365 days.

Stitch BadValues

The Stitch BadValues table contains all of the values that were added to the bad-values blocklist.

The Stitch BadValues table contains the following columns:

Column name	Data type	Description
Datasource	String	The name of the data source from which the value originated.
Semantic	String	The semantic tag that is associated with the value that was added to the bad-values blocklist.
Value	String	The value that was added to the bad-values blocklist.
Num Values	String	The number of times the value appeared in the data.
Num Proxy	String	The number of proxies to which the value was associated. When this number exceeds the threshold defined for in the blocklist a value is added to the bad-values blocklist.
Domain Table	String	The domain table from which the value originated.
Is Preprocessed	String	Indicates if the value was changed during pre-processing by Stitch. For example, phone numbers often have dashes within their values. Dashes are removed by Stitch during pre-processing. When this column is true, and a phone number value is “12065551212”, the original value for that phone number in the associated datasource may be “1-206-555-1212”.

Stitch Blocking Keys

The Stitch Blocking Keys table contains all of the blocking keys that are associated with the Stitch process.

The Stitch Blocking Keys table contains the following columns:

Column name	Data type	Description
BK	String	A blocking key defines a specific combination of characters to be used as a blocking strategy. For example, the first three characters in given-name, the first character in surname, and birthdate represent a blocking key.
Datasource	String	The name of the data source from which this customer record originated. Tip The combination of PK and Datasource uniquely identifies a row in the Stitch Blocking Keys table, which correlates to a single row in a domain table.
PK	String	The combination of data source and primary key allows Amperity to uniquely identify every row in every data table across the entirety of customer data input to Amperity. Tip The combination of PK and Datasource uniquely identifies a row in the Stitch Blocking Keys table, which correlates to a single row in a domain table.
Strategy	String	A blocking strategy acts like a filter against a very large data set. Each blocking strategy applies its filter and all records that match are grouped together into a block. Each record that matches a blocking strategy is a blocking key.

Stitch BlocklistValues

Note

The Stitch BlocklistValues table may be added after the bad-values blocklist is configured for your tenant.

The Stitch BlocklistValues table contains the following columns:

Column name	Data type	Description
Datasource	String	The name of the data source from which the blocklisted value originated. A value of * means a blocklisted value originated from more than one source.
Domain Table	String	The name of the domain table from which the blocklisted value originated.
Num Proxy	Integer	The distinct number of individuals to which Num Values is associated.
Num Values	Integer	The total number of values.
PK	String
Semantic	String	The semantic type associated with the value that was blocklisted. For example: Email.
Value	String	The value that was blocklisted. For example: paul.jackson@amperity.com

Stitch Scores

The Stitch Scores table contains all scores generated by Stitch, including scores that are not associated with an Amperity ID. Use this table to help understand why records were not associated with an Amperity ID.

The Stitch Scores table contains the following columns:

Column name	Data type	Description
Amperity ID1	String	The unique identifier that is assigned to clusters of customer records that all represent the same individual. The Amperity ID does not replace primary and foreign keys, but exists alongside them within unified profiles. The Amperity ID for the first of two compared records. Note The Amperity ID is a universally unique identifier (UUID) that is represented by 36 characters spread across five groups separated by hyphens: 8-4-4-4-12. For example: 123e4567-e89b-12d3-a456-426614174000
Amperity ID2	String	The Amperity ID for the second of two compared records.
Match Category	String	A match category is a classifier that is applied by Amperity to an individual record-pair within a cluster of record-pairs. The match category is the result of this classification. Match Category Description Exact Amperity has the highest confidence that these records represent the same person because all profile data exactly matches. Excellent Amperity has near perfect confidence that these records belong to the same person, despite select types of profile data not matching. High Using deductive reasoning, Amperity has very high confidence that these records match, despite some profile data not matching. Moderate Amperity has moderate confidence that these records match, due to weak or fuzzy matches between highly unique customer attributes (email, phone, address). Weak Amperity lacks confidence, but if asked to guess, Amperity would assert these records do belong to the same individual, because they match on non-unique customer attributes (name, state, zip code). Non-match Amperity has high confidence that these records do NOT match, because core profile data is in conflict.
Match Type	String	The type of score being applied. Possible values are as follows: “scored”, “scored_transitive”, and “trivial_duplicate”.
PK1	String
PK2	String
Score	Float	A score contains a value from 0.0 to 5.0 that represents the combined score assigned to the record pair by Stitch. There are two components of the score: the score itself, and then its strength. The record pair score correlates to the match category, which is a classifier that is applied by Amperity to individual record pairs. The record pair score corresponds to the classification: 5 for exact matches, 4 for excellent matches, 3 for high matches, 2 for moderate matches, 1 for weak matches, and 0 for no matches. The record pair strength represents the strength of the record pair score. It is a two digit number. For example: .31 is a lower strength and .93 is a very high strength.
Source1	String
Source2	String

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.

The Transaction Attributes table contains the following columns:

Column name	Data type	Description
Amperity ID	String	The unique identifier that is assigned to clusters of customer records that all represent the same individual. The Amperity ID does not replace primary and foreign keys, but exists alongside them within unified profiles. This column is added when a transaction is associated with an Amperity ID from the Unified Transactions table. Note The Amperity ID is a universally unique identifier (UUID) that is represented by 36 characters spread across five groups separated by hyphens: 8-4-4-4-12. For example: 123e4567-e89b-12d3-a456-426614174000
Early Repeat Purchaser	Boolean	Early repeat purchaser is a flag that indicates if a customer made a repeat purchase within the previous 90 days. Tip Use the Early Repeat Purchaser attribute as a leading indicator for overall conversion rate of one-time buyers to repeat customers, even when it does not capture the total number of one-time buyers who have returned to purchase again. This column is calculated from the First Order Datetime and Second Order Datetime columns in the Transaction Attributes table. Also in: Transaction Attributes Extended
First Order Datetime	Datetime	First order date is the date on which the first order was placed by a customer. This column is calculated from the Order Datetime column in the Unified Transactions table, which is created when the order-datetime semantic tag is applied to interaction records that contain transactions data. Also in: Transaction Attributes Extended
First Order ID	String	First order ID is the order ID for the first order that was made by a customer. This column is calculated from the Order ID column in the Unified Transactions and Unified Itemized Transactions tables, which is created when the order-id semantic tag is applied to interaction records that contain transactions and itemized transactions data. Also in: Transaction Attributes Extended
First Order Is Retained	Boolean	First order is retained is a flag that indicates if a customer has made a repeat purchase within 365 days of their first order.
First Order Revenue	Decimal	First order revenue is the total revenue that is associated with a customer’s first order, ignoring returned items and/or canceled items. This column is calculated from the Order Revenue column in the Unified Transactions table, which is created when the order-revenue semantic tag is applied to interaction records that contain transactions data. Also in: Transaction Attributes Extended
First Order Total Items	Integer	First order total items represents the number of items that were purchased as part of a customer’s first order, ignoring returned items and/or canceled items. This column is calculated from the Order Quantity column in the Unified Transactions table, which is created when the order-quantity semantic tag is applied to interaction records that contain transactions data. Also in: Transaction Attributes Extended
Latest Order Datetime	Datetime	Latest order date is the date (and time) on which the most recent order was placed by a customer. This column is calculated from the Order Datetime column in the Unified Transactions table, which is created when the order-datetime semantic tag is applied to interaction records that contain transactions data. Also in: Transaction Attributes Extended
Latest Order ID	String	Latest order ID is the order ID for the most recent order that was made by a customer. This column is calculated from the Order ID column in the Unified Transactions and Unified Itemized Transactions tables, which is created when the order-id semantic tag is applied to interaction records that contain transactions and itemized transactions data. Also in: Transaction Attributes Extended
Latest Order Revenue	Decimal	Latest order revenue is the total revenue that is associated with a customer’s most recent order, ignoring returned items and/or canceled items. This column is calculated from the Order Revenue column in the Unified Transactions table, which is created when the order-revenue semantic tag is applied to interaction records that contain transactions data. Also in: Transaction Attributes Extended
Latest Order Total Items	Integer	Latest order total items is the number of items that were purchased as part of a customer’s most recent order, ignoring returned items and/or canceled items. This column is calculated from the Order Quantity column in the Unified Transactions table, which is created when the order-quantity semantic tag is applied to interaction records that contain transactions data. Also in: Transaction Attributes Extended
Lifetime Average Item Price	Decimal	The average individual item price for all orders, ignoring returns and/or cancellations. This column is calculated from the Order Quantity and Order Revenue columns in the Unified Transactions table, which are created when the order-quantity and order-revenue semantic tags are applied to interaction records that contain transactions data.
Lifetime Average Num Items	Decimal	The average number of items purchased for all orders, ignoring returns and/or cancellations. This column is calculated from the Order ID and Order Quantity columns in the Unified Transactions table, which are created when the order-id and order-quantity semantic tags are applied to interaction records that contain transactions data.
Lifetime Average Order Value	Decimal	The average lifetime revenue for all orders, ignoring returns and/or cancellations. This column is calculated from the Order ID and Order Revenue columns in the Unified Transactions table, which are created when the order-id and order-revenue semantic tags are applied to interaction records that contain transactions data.
Lifetime Largest Order Value	Decimal	Lifetime largest order value identifies the largest order that is associated with a customer, ignoring returns and/or cancellations, for a customer’s entire purchase history. This column is calculated from the Order Revenue column in the Unified Transactions table, which is created when the order-revenue semantic tag is applied to interaction records that contain transactions data.
Lifetime Order Frequency	Integer	A lifetime order frequency is the total number of orders that that a customer has made during their entire relationship with your brand. This column is calculated from the Order ID column in the Unified Transactions and Unified Itemized Transactions tables, which is created when the order-id semantic tag is applied to interaction records that contain transactions and itemized transactions data.
Lifetime Order Revenue	Decimal	The lifetime revenue for all items, ignoring returns and/or cancellations. This column is calculated from the Order Revenue column in the Unified Transactions table, which is created when the order-revenue semantic tag is applied to interaction records that contain transactions data.
Lifetime Preferred Purchase Brand	String	The most frequent brand for all orders.
Lifetime Preferred Purchase Channel	String	The most frequent purchase-channel for all orders.
Lifetime Total Items	Integer	The lifetime number of individual items associated with the transaction, ignoring returns and/or cancellations. This column is calculated from the Order Quantity column in the Unified Transactions table, which is created when the order-quantity semantic tag is applied to interaction records that contain transactions data.
Multi Purchase Brand	Boolean	A flag that indicates if a customer has interacted with more than one brand. This column is calculated from the Purchase Brand column in the Unified Transactions table, which is created when the purchase-brand semantic tag is applied to interaction records that contain transactions data. Also in: Transaction Attributes Extended
Multi Purchase Channel	Boolean	Multi-purchase channel is a flag that indicates if a customer has purchased from more than one channel. This column is calculated from the Purchase Channel column in the Unified Transactions table, which is created when the purchase-channel semantic tag is applied to interaction records that contain transactions data. Also in: Transaction Attributes Extended
One And Done	Boolean	One and done is a flag that indicates if a customer has made only one purchase. Important Amperity uses the range of data that is provided to it to identify one-time buyers. For example, if Amperity is provided data for the years 2015-2022, purchases made in 2014 are not used to identify one-time buyers. This column is calculated from the Lifetime Order Frequency column in the Transaction Attributes table. Also in: Transaction Attributes Extended
Second Order Datetime	Datetime	Second order date is the date on which the second order was placed by a customer. This column is calculated from the Order Datetime column in the Unified Transactions and Unified Itemized Transactions tables, which is created when the order-datetime semantic tag is applied to interaction records that contain transactions and itemized transactions data. Also in: Transaction Attributes Extended
Second Order ID	String	Second order ID is the order ID for the second order that was made by a customer. This column is calculated from the Order ID column in the Unified Transactions and Unified Itemized Transactions tables, which are created when the order-id semantic tag is applied to interaction records that contain transactions and itemized transactions data. Also in: Transaction Attributes Extended
Second Order Is Retained	Boolean	Second order is retained is a flag that indicates if a customer has made a repeat purchase within 365 days of their second order.
Second Order Revenue	Decimal	Second order revenue is the total revenue that is associated with a customer’s second order, ignoring returned items and/or canceled items. This column is calculated from the Order Revenue column in the Unified Transactions table, which is created when the order-revenue semantic tag is applied to interaction records that contain transactions data.
Second Order Total Items	Integer	Second order total items is the number of items that were purchased as part of a customer’s second order, ignoring returned items and/or canceled items. This column is calculated from the Order Quantity column in the Unified Transactions table, which is created when the order-quantity semantic tag is applied to interaction records that contain transactions data. Also in: Transaction Attributes Extended

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 presented as a single table (with many columns), including an Amperity ID, and fit into the following categories:

• Customer flags

• Customer orders

• Date differences

• Time period rollups

• RFM

Customer flags

Each record has a set of flags that indicate if a customer has purchased, the number of brand interactions, the number of brand channels, and if that customer is an early repeat purchaser.

Column Name	Data type	PII	Description
Amperity ID	String		An Amperity ID is a patented unique identifier that is assigned to clusters of customer records. A single Amperity ID represents a single individual. Unlike other systems, the Amperity ID is reassessed every day for the most comprehensive view of your customers.
Early Repeat Purchaser	Boolean		Early repeat purchaser is a flag that indicates if a customer made a repeat purchase within the previous 90 days. Tip Use the Early Repeat Purchaser attribute as a leading indicator for overall conversion rate of one-time buyers to repeat customers, even when it does not capture the total number of one-time buyers who have returned to purchase again. Also in: Transaction Attributes
Multi Purchase Brand	Boolean		A flag that indicates if a customer has interacted with more than one brand. This column is calculated from the Purchase Brand column in the Unified Transactions table, which is created when the purchase-brand semantic tag is applied to interaction records that contain transactions data. Also in: Transaction Attributes
Multi Purchase Channel	Boolean		Multi-purchase channel is a flag that indicates if a customer has purchased from more than one channel. This column is calculated from the Purchase Channel column in the Unified Transactions table, which is created when the purchase-channel semantic tag is applied to interaction records that contain transactions data. Also in: Transaction Attributes
One And Done	Boolean		One and done is a flag that indicates if a customer has made only one purchase. Important Amperity uses the range of data that is provided to it to identify one-time buyers. For example, if Amperity is provided data for the years 2015-2022, purchases made in 2014 are not used to identify one-time buyers. Also in: Transaction Attributes

Customer orders

Each record contains a subset of order data from a customers first, second, and latest order. Each set of attributes is prefixed by first, second, or latest, depending on the order data that is being summarized.

Column Name	Data type	PII	Description
<X> Order Datetime	Datetime		The datetime on which the order was made. Available columns: First Order Datetime Second Order Datetime Latest Order Datetime Also in: Transaction Attributes
<X> Order ID	String		The ID of the order. Available columns: First Order ID Second Order ID Latest Order ID Also in: Transaction Attributes
<X> Order Purchase Brand	String		The brand of the order made by the customer. Available columns: First Order Purchase Brand Second Order Purchase Brand Latest Order Purchase Brand Also in: Transaction Attributes
<X> Order Purchase Channel	String		The channel in which the customer’s order was made. Available columns: First Order Purchase Channel Second Order Purchase Channel Latest Order Purchase Channel Also in: Transaction Attributes
<X> Order Revenue	Decimal		The total revenue for orders at each interval. Available columns: First Order Revenue Second Order Revenue Latest Order Revenue Also in: Transaction Attributes
<X> Store ID	String		The ID of the store where the customer made their order. This value may be NULL if the associated channel is not retail or some equivalent. Available columns: First Order Store ID Second Order Store ID Latest Order Store ID
<X> Total Items	Integer		The total number of items in the order. Available columns: First Order Total Items Second Order Total Items Latest Order Total Items Also in: Transaction Attributes

Date differences

Each record contains three attributes that specify the number of days between certain events.

Column Name	Data type	PII	Description
Days Since Latest Order	Integer		Days since latest order measures the number of days that have elapsed since a customer has placed an order. This column is calculated from the Latest Order Datetime column in the Transaction Attributes table.
First To Latest Order Days	Integer		First-to-latest order days is the number of days that have elapsed between the date of the first order and the date of the latest order. This column is calculated from the First Order Datetime and Latest Order Datetime columns in the Transaction Attributes table.
First To Second Order Days	Integer		First-to-second order days is the number of days that have elapsed between the date of the first order and the date of the second order. This column is calculated from the First Order Datetime and Second Order Datetime columns in the Transaction Attributes table.

Time period rollups

Each record contains time period rollups of the customer’s transaction history. The time periods used are lifetime, L12M (the 12 months of transaction history starting 12 months ago), LY12M (the 12 months of transaction history starting 24 months ago), and L30D (the last 30 days).

Column Name	Data type	PII	Description
<X> Average Item Price	Decimal		The average item price during the time period. Available columns: L30D Average Item Price L3M Average Item Price L6M Average Item Price L12M Average Item Price LY12M Average Item Price Lifetime Average Item Price
<X> Average Net Item Price	Decimal		The average net item price during the time period, minus returns, cancellations, and discounts. Available columns: L30D Average Net Item Price L3M Average Net Item Price L6M Average Net Item Price L12M Average Net Item Price LY12M Average Net Item Price Lifetime Average Net Item Price Note These attributes must be enabled in the SQL template for the Transaction Attributes Extended table.
<x> Average Num Items	Decimal		The average number of items during the time period. Available columns: L30D Average Num Items L3M Average Num Items L6M Average Num Items L12M Average Num Items LY12M Average Num Items Lifetime Average Num Items
<X> Average Order Value	Decimal		The average order value during the time period. Available columns: L30D Average Order Value L3M Average Order Value L6M Average Order Value L12M Average Order Value LY12M Average Order Value Lifetime Average Order Value
<X> Largest Net Order Value	Decimal		The total value for the largest orders in the time period, minus returns, cancellations, and discounts. Available columns: First Largest Net Order Value Second Largest Net Order Value Latest Largest Net Order Value LY12M Largest Net Order Value Lifetime Largest Net Order Value Note These attributes must be enabled in the SQL template for the Transaction Attributes Extended table.
<X> Net Order Revenue	Decimal		The net order revenue for orders in the time period, minus returns, cancellations, and discounts. Available columns: L30D Order Revenue L3M Order Revenue L6M Order Revenue L12M Order Revenue LY12M Order Revenue Lifetime Order Revenue Note These attributes must be enabled in the SQL template for the Transaction Attributes Extended table.
<X> Net Order Value	Decimal		The total value for orders in the time period, minus returns, cancellations, and discounts. Available columns: L30D Net Order Value L3M Net Order Value L6M Net Order Value L12M Net Order Value LY12M Net Order Value Lifetime Net Order Value Note These attributes must be enabled in the SQL template for the Transaction Attributes Extended table.
<X> Order Cost	Datetime		The cost for an order during the time period. Available columns: First Order Cost Second Order Cost Latest Order Cost L30D Average Order Cost L3M Average Order Cost L6M Average Order Cost L12M Average Order Cost LY12M Average Order Cost Lifetime Average Order Cost
<X> Order Frequency	Integer		The count of distinct order IDs that are associated with the customer during the time period. Available columns: L30D Order Frequency L3M Order Frequency L6M Order Frequency L12M Order Frequency LY12M Order Frequency Lifetime Order Frequency
<X> Order Canceled Frequency	Integer		The count of distinct order IDs that are associated with canceled items during the time period. Available columns: L30D Order Canceled Frequency L3M Order Canceled Frequency L6M Order Canceled Frequency L12M Order Canceled Frequency LY12M Order Canceled Frequency Lifetime Order Canceled Frequency Note These attributes must be enabled in the SQL template for the Transaction Attributes Extended table.
<X> Order Returned Frequency	Integer		The count of distinct order IDs that are associated with returned items during the time period. Available columns: L30D Order Returned Frequency L3M Order Returned Frequency L6M Order Returned Frequency L12M Order Returned Frequency LY12M Order Returned Frequency Lifetime Order Returned Frequency Note These attributes must be enabled in the SQL template for the Transaction Attributes Extended table.
<X> Order Revenue	Decimal		The total revenue for orders in the time period. Available columns: L30D Order Revenue L3M Order Revenue L6M Order Revenue L12M Order Revenue LY12M Order Revenue Lifetime Order Revenue
<X> Order canceled Revenue	Decimal		The total revenue for canceled items in the time period. Available columns: L30D Order canceled Revenue L3M Order canceled Revenue L6M Order canceled Revenue L12M Order canceled Revenue LY12M Order canceled Revenue Lifetime Order canceled Revenue Note These attributes must be enabled in the SQL template for the Transaction Attributes Extended table.
<X> Order Returned Revenue	Decimal		The total revenue for returned items in the time period. Available columns: L30D Order Returned Revenue L3M Order Returned Revenue L6M Order Returned Revenue L12M Order Returned Revenue LY12M Order Returned Revenue Lifetime Order Returned Revenue Note These attributes must be enabled in the SQL template for the Transaction Attributes Extended table.
<X> Preferred Purchase Brand	String		The brand with the greatest number of orders during the time period. Available columns: L30D Preferred Purchase Brand L3M Preferred Purchase Brand L6M Preferred Purchase Brand L12M Preferred Purchase Brand LY12M Preferred Purchase Brand Lifetime Preferred Purchase Brand
<X> Preferred Purchase Channel	String		The channel with the greatest number of orders during the time period. Available columns: L30D Preferred Purchase Channel L3M Preferred Purchase Channel L6M Preferred Purchase Channel L12M Preferred Purchase Channel LY12M Preferred Purchase Channel Lifetime Preferred Purchase Channel
<X> Preferred Store ID	String		The store ID with the greatest number of orders during the time period. Available columns: L30D Preferred Store ID L3M Preferred Store ID L6M Preferred Store ID L12M Preferred Store ID LY12M Preferred Store ID Lifetime Preferred Store ID
<X> Purchase Brands	Integer		The count of the distinct brands a customer interacted with during the time period. Available columns: L30D Purchase Brands L3M Purchase Brands L6M Purchase Brands L12M Purchase Brands LY12M Purchase Brands Lifetime Purchase Brands
<X> Purchase Channels	Integer		The count of the distinct channels (online, in store, etc.) that the customer interacted with during during the time period. Available columns: L30D Purchase Channels L3M Purchase Channels L6M Purchase Channels L12M Purchase Channels LY12M Purchase Channels Lifetime Purchase Channels
<X> Stores	Integer		The count of the distinct stores that the customer interacted with during the time period. Available columns: L30D Stores L3M Stores L6M Stores L12M Stores LY12M Stores Lifetime Stores
<X> Total Items	Integer		The total number of items purchased by the customer during the time period. Available columns: L30D Total Items L3M Total Items L6M Total Items L12M Total Items LY12M Total Items Lifetime Total Items
<X> Total canceled Items	Integer		The total number of items canceled by the customer during the time period. Available columns: L30D Total canceled Items L3M Total canceled Items L6M Total canceled Items L12M Total canceled Items LY12M Total canceled Items Lifetime Total canceled Items Note These attributes must be enabled in the SQL template for the Transaction Attributes Extended table.
<X> Total Returned Items	Integer		The total number of items returned by the customer during the time period. Available columns: L30D Total Returned Items L3M Total Returned Items L6M Total Returned Items L12M Total Returned Items LY12M Total Returned Items Lifetime Total Returned Items Note These attributes must be enabled in the SQL template for the Transaction Attributes Extended table.

RFM

Amperity calculates RFM scores against transactions that occurred within the last 12 months.

Each of the recency (R), frequency (F), and monetary (M) scores are represented by a number between 0 and 9. The final RFM score is a concatenation of the individual scores: recency first, then frequency, monetary last. The final RFM score is a number between 0 and 999.

Note

RFM uses approximate calculations to optimize the performance of the Transaction Attributes Extended table.

Column Name	Data type	PII	Description
L12M RFM Score	Integer		The RFM score for the customer is based on transactions that occurred within the last 12 months. The RFM score is represented as an integer between “111” and “101010”. This is a concatenated score that uses each of the individual recency, frequency, and monetary scores. The order is recency, then frequency, and then monetary. For example, you can build an audience that contains your top 20% customers for recency, your top 30% customers for frequency, and your top 10% customers for monetary by setting the L12M RFM Score attribute to “9810” (or “9” for recency, “8” for frequency, and then “10” for monetary).
L12M Recency	Integer		L12M Recency is a score that sorts customers by how recently they purchased during the previous 12 months. Each RFM score is split into ten percentile groups. The lowest percentile is 1 and the highest percentile is 10. Each percentile represents 10% of the customers who belong to that segment. 10 represents the 90-100th percentile and the customers who have the highest recency, frequency, or monetary scores. 9 represents the 80-90th percentile 8 represents the 70-80th percentile 7 represents the 60-70th percentile 6 represents the 50-60th percentile 5 represents the 40-50th percentile 4 represents the 30-40th percentile 3 represents the 20-30th percentile 2 represents the 10-20th percentile 1 represents the 0-10th percentile and the customers who have the lowest recency, frequency, or monetary scores. Tip Combine percentiles to build larger groups of customers. For example 9 and 10 together represent the “top 20%” while 8, 9, and 10 represent the “top 30%”.
L12M Frequency	Integer		L12M Frequency is a score that sorts customers by purchase frequency during the previous 12 months. Each RFM score is split into ten percentile groups. The lowest percentile is 1 and the highest percentile is 10. Each percentile represents 10% of the customers who belong to that segment. 10 represents the 90-100th percentile and the customers who have the highest recency, frequency, or monetary scores. 9 represents the 80-90th percentile 8 represents the 70-80th percentile 7 represents the 60-70th percentile 6 represents the 50-60th percentile 5 represents the 40-50th percentile 4 represents the 30-40th percentile 3 represents the 20-30th percentile 2 represents the 10-20th percentile 1 represents the 0-10th percentile and the customers who have the lowest recency, frequency, or monetary scores. Tip Combine percentiles to build larger groups of customers. For example 9 and 10 together represent the “top 20%” while 8, 9, and 10 represent the “top 30%”.
L12M Monetary	Integer		L12M Monetary is a score that sorts customers by spend amount during the previous 12 months. Each RFM score is split into ten percentile groups. The lowest percentile is 1 and the highest percentile is 10. Each percentile represents 10% of the customers who belong to that segment. 10 represents the 90-100th percentile and the customers who have the highest recency, frequency, or monetary scores. 9 represents the 80-90th percentile 8 represents the 70-80th percentile 7 represents the 60-70th percentile 6 represents the 50-60th percentile 5 represents the 40-50th percentile 4 represents the 30-40th percentile 3 represents the 20-30th percentile 2 represents the 10-20th percentile 1 represents the 0-10th percentile and the customers who have the lowest recency, frequency, or monetary scores. Tip Combine percentiles to build larger groups of customers. For example 9 and 10 together represent the “top 20%” while 8, 9, and 10 represent the “top 30%”.

UID2

Unified ID 2.0 (UID2) is an open-source framework that enables deterministic identity for advertising opportunities across the open internet for participants with access to the advertising ecosystem. UID2 is a standalone solution with a unique namespace and privacy controls that help participants meet local market requirements.

The UID2 table contains the results of UID2 token generation when enabled for your tenant.

The UID2 table contains the following columns:

Column name	Data type	Description
Bucket ID	String	A unique identifier for the salt bucket that is used to ensure that expired UID2 tokens are refreshed. This value is returned in the response from the POST /identity/map endpoint. Note Each UID2 token is associated with a salt bucket that links that token to a specific point in time. Salt buckets expire; approximately 1/365th of all salt buckets are rotated daily. Amperity monitors salt buckets on a daily basis to determine which UID2 tokens need to be refreshed.
Email	String	The email address for the customer. Amperity gets this value from the email field in the Unified Coalesced table.
Normalized Email	String	The normalized email address that was sent from Amperity to the POST /identity/map endpoint for mapping. This value is returned in the response from the POST /identity/map endpoint.
UID2	String	The raw UID2 value for the customer. This value, when encrypted, may be used as a UID2 token. This value is returned in the response from the POST /identity/map endpoint.

Unified Changes Clusters

The Unified Changes Clusters table contains a history of changes to cluster graphs, relative to the previous Stitch run.

The Unified Changes Clusters table contains the following columns:

Column name	Data type	Description
Amperity ID	String	The unique identifier that is assigned to clusters of customer records that all represent the same individual. The Amperity ID does not replace primary and foreign keys, but exists alongside them within unified profiles. Note The Amperity ID is a universally unique identifier (UUID) that is represented by 36 characters spread across five groups separated by hyphens: 8-4-4-4-12. For example: 123e4567-e89b-12d3-a456-426614174000
Change Type	String	The type of change that occurred within the Stitch job. Possible values include: added, deleted, and updated: The added change type indicates a new cluster was created during this run. The deleted change type indicates a cluster was deleted during this run. The updated change type indicates the cluster existed in the past run, and continues to exist in this run but the set of PKs that comprise it has changed. Also in: Unified Changes PKS
Job ID	String	The job ID for database table generation. For example: db-gen-20210202-12345-AbCd67EFgH8. Also in: Unified Changes PKS
Timestamp	Datetime	The timestamp that is associated with Job ID. Each row that shares the same job ID will have the same value for Timestamp. Also in: Unified Changes PKS

Unified Changes PKS

The Unified Changes PKS table contains a history of changes to primary keys, relative to the previous Stitch run.

The Unified Changes PKS table contains the following columns:

Column name	Data type	Description
Amperity ID	String	The unique identifier that is assigned to clusters of customer records that all represent the same individual. The Amperity ID does not replace primary and foreign keys, but exists alongside them within unified profiles. Note The Amperity ID is a universally unique identifier (UUID) that is represented by 36 characters spread across five groups separated by hyphens: 8-4-4-4-12. For example: 123e4567-e89b-12d3-a456-426614174000
Amperity ID Prev	String	The Amperity ID that was previously associated with the primary key (pk) as of the most recent Stitch run.
Change Type	String	The type of change that occurred within the Stitch job. Possible values include: added, deleted, and updated: The added change type indicates a primary key was added to the cluster. The deleted change type indicates a primary key was deleted from the cluster. The updated change type indicates the primary key existed in the past run, and continues to exist in this run but its cluster assignment has changed. Also in: Unified Changes Clusters
Datasource	String	The name of the data source from which this customer record originated. Tip The combination of PK and Datasource uniquely identifies a row in the Unified Coalesced table, which correlates to a single row in a domain table.
Job ID	String	The job ID for database table generation. For example: db-gen-20210202-12345-AbCd67EFgH8. Also in: Unified Changes Clusters
PK	String	The combination of data source and primary key allows Amperity to uniquely identify every row in every data table across the entirety of customer data input to Amperity. Tip The combination of PK and Datasource uniquely identifies a row in the Unified Coalesced table, which correlates to a single row in a domain table.
Timestamp	Datetime	The timestamp that is associated with Job ID. Each row that shares the same job ID will have the same value for Timestamp. Also in: Unified Changes Clusters

Unified Coalesced

The Unified Coalesced table contains all the PII data that has been processed through Stitch. The data is organized by the semantic tag as the column heading and then coalesced into one single table. A unique Amperity ID may appear in more than one row.

The Unified Coalesced table contains the following columns:

Column name	Data type	Description
Amperity ID	String	The unique identifier that is assigned to clusters of customer records that all represent the same individual. The Amperity ID does not replace primary and foreign keys, but exists alongside them within unified profiles. Note The Amperity ID is a universally unique identifier (UUID) that is represented by 36 characters spread across five groups separated by hyphens: 8-4-4-4-12. For example: 123e4567-e89b-12d3-a456-426614174000
Address	String	The address that is associated with the location of a customer, such as “123 Main Street”. Values in this column depend on fields that are tagged with the Address semantic. Also in: Customer 360, Merged Customers, Unified Customer, Unified Preprocessed Raw
Address2	String	Additional address information, such as an apartment number or a post office box, that is associated with the location of a customer, such as “Apt #9”. Values in this column depend on fields that are tagged with the Address2 semantic. Also in: Merged Customers, Unified Customer
Birthdate	Date	The date of birth that is associated with a customer. Values in this column depend on fields that are tagged with the birthdate semantic. Also in: Customer 360, Merged Customers, Unified Customer, Unified Preprocessed Raw
BLV Address	Boolean	When true, the address on this customer record matches a blocklist value.
BLV Email	Boolean	When true, the email on this customer record matches a blocklist value.
BLV Given Name	Boolean	When true, the given-name on this customer record matches a blocklist value.
BLV Phone	Boolean	When true, the phone on this customer record matches a blocklist value.
BLV Surname	Boolean	When true, the surname on this customer record matches a blocklist value.
City	String	The city that is associated with the location of a customer. Values in this column depend on fields that are tagged with the City semantic. Also in: Customer 360, Merged Customers, Unified Customer, Unified Preprocessed Raw
CK	String	The ck semantic tag may be applied to a column that contains pre-existing, tenant-specific customer IDs. When customer keys are applied, Amperity compares them to the Amperity ID as part of the deduplication process. Tip What happens to customer keys in the Unified Coalesced table? Records may have NULL customer keys. There may be only one customer key per data source. There may be multiple customer keys per Amperity ID. This is because customer keys may also be tagged as foreign keys. Also in: Unified Customer
Component ID	Integer	An identifier that represents a set of records that are transitively connected with a score above threshold as an outcome of blocking and initial scoring. Records that share a component ID, but have different Amperity IDs, were split during hierarchical comparison. Tip Records with different component_id values may show as having been blocked together. This can occur when a connecting record pair was removed because it scored below the pairwise comparison threshold. Also in: Unified Preprocessed Raw
Country	String	The country that is associated with the location of a customer. Values in this column depend on fields that are tagged with the Country semantic. Important The country field is added to the Unified Coalesced table when fields are tagged with the country profile semantic. Also in: Merged Customers, Unified Customer
Create DT	String	Apply the create-dt semantic tag to columns in customer records that identify when the data was created. The field to which this semantic is applied must be a datetime field type. Also in: Merged Customers, Unified Customer
Datasource	String	The name of the data source from which this customer record originated. Tip The combination of PK and Datasource uniquely identifies a row in the Unified Coalesced table, which correlates to a single row in a domain table.
Email	String	The email address that is associated with a customer. A customer may have more than one email address. Values in this column depend on fields that are tagged with the Email semantic. Also in: Customer 360, Merged Customers, Unified Customer, Unified Preprocessed Raw
FK [Name]	String	The fk-[namespace] semantic tag identifies a field as a foreign key. A foreign key semantic tag must be namespaced. For example: fk-customer, fk-interaction, fk-audience, or fk-brand. A column is added for each foreign key that is defined in the Sources page. Tip What happens to foreign keys in the Unified Coalesced table? Records may have NULL foreign keys. There may be multiple foreign keys in the data source, but there may not be duplicate foreign keys. There may be multiple foreign keys per Amperity ID. There should not be multiple Amperity IDs per foreign key. Also in: Unified Customer, Unified Preprocessed Raw
Full Name	String	A combination of given name (first name) and surname (last name) for a customer. May include a middle name or initial. Values in this column depend on fields that are tagged with the Full Name semantic. Also in: Merged Customers, Unified Customer
Gender	String	The gender that is associated with a customer. Values in this column depend on fields that are tagged with the Gender semantic. Also in: Customer 360, Merged Customers, Unified Customer, Unified Preprocessed Raw
Generational Suffix	String	The suffix that identifies to which family generation a customer record belongs. For example: Jr., Sr. II, and III. Also in: Merged Customers, Unified Customer
Given Name	String	The first name that is associated with a customer. Values in this column depend on fields that are tagged with the Given Name semantic. Also in: Customer 360, Merged Customers, Unified Customer, Unified Preprocessed Raw
Has BLV	Boolean	The has_blv column indicates if blocklist values for address, email, phone, given-name, or surname are present in customer records.
Is Supersized	Boolean	Indicates when a rough heuristic is applied to the first grouping of records (rep_pk) to partition supersized records into smaller components. Supersized records occur when more than 500 groups are associated with the first grouping of records.
Loyalty ID	String	The identifier for a loyalty program that is associated with a customer. This column is added when the loyalty-id semantic tag is applied to customer profiles. Also in: Merged Customers
Phone	String	The phone number that is associated with a customer. A customer may have more than one phone number. Values in this column depend on fields that are tagged with the Phone semantic. Also in: Customer 360, Merged Customers, Unified Customer, Unified Preprocessed Raw
PK	String	The combination of data source and primary key allows Amperity to uniquely identify every row in every data table across the entirety of customer data input to Amperity. Tip The combination of PK and Datasource uniquely identifies a row in the Unified Coalesced table, which correlates to a single row in a domain table.
Postal	String	The zip code or postal code that is associated with the location of a customer. Values in this column depend on fields that are tagged with the postal semantic. Also in: Customer 360, Merged Customers, Unified Customer, Unified Preprocessed Raw
Rep DS	Integer	The rep_ds column shows the datasource that is associated with the rep_pk column.
Rep PK	Integer	The rep_pk column is an identifier that represents the first grouping of records done by Stitch. This grouping is based on identical semantic patterns. Tip The combination of Rep DS and Rep PK represent qualified trivial duplications, which are records with enough identical PII to score 3.0 (or greater) and were grouped together by Stitch early in the identity resolution process. All qualified trivial duplicates are treated as a single record by downstream Stitch processes. The Rep DS and Rep PK fields are included in the Unified Coalesced table to help with situations where it’s necessary to understand why two records were not clustered together.
State	String	The state or province that is associated with the location of a customer. Values in this column depend on fields that are tagged with the state semantic. Also in: Customer 360, Merged Customers, Unified Customer, Unified Preprocessed Raw
Supersized ID	Integer	An identifier that represents supersized records that were partitioned into smaller components. Also in: Unified Preprocessed Raw
Surname	String	The last name that is associated with a customer. Values in this column depend on fields that are tagged with the Surname semantic. Also in: Customer 360, Merged Customers, Unified Customer, Unified Preprocessed Raw
Title	String	The title that precedes a full name that is associated with a customer, such as “Mr.”, “Mrs”, and “Dr”. Also in: Merged Customers, Unified Customer
Update DT	String	Apply the update-dt semantic tag to columns in customer records that identify when the data was last updated in the source system. The field to which this semantic is applied must be a datetime field type. At least one customer record must have this semantic tag applied to ensure that the update_dt column is created in the Unified Coalesced table and to ensure that the Merged Customers table behaves correctly. Also in: Merged Customers, Unified Customer

Unified Compliance

The Unified Compliance table supports privacy rights workflows and contains the search results for data subject access requests (DSAR) and customer delete requests. A row is added to the the Unified Compliance table for each matching record.

Column name	Data type	Description
Request Datasource	String	The location (database and table) from which the request to find matching records originated.
Request ID	Integer	An identifier for the request to find matching records.
Request Type	String	The request type for the compliance action. May be one of: delete, delete_pii, or dsar.
Request Strategy	String	The request strategy for the compliance action. May be one of: exact or connected_pii.
Request Semantic	String	The type of PII used to search for matching records. For example: email, address, or phone.
Request Semantic Value	String	The value that was used to search for matching records. For example: paul.jackson@amperity.com.
Request Match Category	String	The match category will be direct for matches on pii, connected for matches on the amperity-id of a direct match, and source_key or linkage_table for upstream source records.
Amperity ID	String	The unique identifier that is assigned to clusters of customer records that all represent the same individual. The Amperity ID does not replace primary and foreign keys, but exists alongside them within unified profiles.
Datasource	String	The location (database and table) in which the matched record was found.
PK	String	The primary key for the matched record.
PII columms	String	A series of column names. Each added PII column matches the name of a column in which matching records were found.

Unified Compliance Overview

The Unified Compliance Overview table contains an overview of the results of data subject access requests (DSAR) and customer delete requests, including the number of records found, the time at which the request was completed, and the type of request.

Column name	Data type	Description
Request Datasource	String	The location (database and table) from which the request to find matching records originated.
Record Completion Date	Datetime	A timestamp that indicates when the request was completed.
Request ID	String	An identifier for the request.
Request Type	String	The request type for the compliance action. May be one of: delete, delete_pii, or dsar.
Request Strategy	String	The request strategy for the compliance action. May be one of: exact or connected_pii.
Request Email	String	Optional The email address used to match to source table records, if provided.
Request Phone	String	Optional The phone number used to match to source table records, if provided.
Request Address	String	Optional The street address used as part of an address group to match to source table records, if provided.
Request Address2	String	Optional The address2 field used as part of an address group to match to source table records, if provided.
Request City	String	Optional The city used as part of an address group to match to source table records, if provided.
Request State	String	Optional The state used as part of an address group to match to source table records, if provided.
Request Postal	String	Optional The postal code used as part of an address group to match to source table records, if provided.
Request Country	String	Optional The country used as part of an address group to match to source table records, if provided.
Request Custom Key	String	Optional The custom key used to match to source table records, if provided.
Rows Found	String	The number of matching records that were discovered by the request.

Unified Customer

The Unified Customer table contains every row of every stitched table with all semantics coalesced into a single column, with two exceptions that are removed from the table: values from a bad-values blocklist and values associated with supersized clusters. A unique Amperity ID may appear in more than one row.

Note

See Unified Customers for more information about how this table is built and maintained within the customer 360 database.

Note

This table is similar to the Unified Coalesced table, but will not contain columns related to blocklists or supersized clusters.

The Unified Customer table contains the following columns:

Column name	Data type	Description
Amperity ID	String	The unique identifier that is assigned to clusters of customer records that all represent the same individual. The Amperity ID does not replace primary and foreign keys, but exists alongside them within unified profiles. Note The Amperity ID is a universally unique identifier (UUID) that is represented by 36 characters spread across five groups separated by hyphens: 8-4-4-4-12. For example: 123e4567-e89b-12d3-a456-426614174000
Address	String	The address that is associated with the location of a customer, such as “123 Main Street”. Values in this column depend on fields that are tagged with the Address semantic. Also in: Customer 360, Merged Customers, Unified Coalesced, Unified Preprocessed Raw
Address2	String	Additional address information, such as an apartment number or a post office box, that is associated with the location of a customer, such as “Apt #9”. Values in this column depend on fields that are tagged with the Address2 semantic. Also in: Merged Customers, Unified Coalesced
Birthdate	Date	The date of birth that is associated with a customer. Values in this column depend on fields that are tagged with the Birthdate semantic. Also in: Customer 360, Merged Customers, Unified Coalesced, Unified Preprocessed Raw
City	String	The city that is associated with the location of a customer. Values in this column depend on fields that are tagged with the City semantic. Also in: Customer 360, Merged Customers, Unified Coalesced, Unified Preprocessed Raw
CK	String	The ck semantic tag may be applied to a column that contains pre-existing, tenant-specific customer IDs. When customer keys are applied, Amperity compares them to the Amperity ID as part of the deduplication process. Also in: Unified Coalesced
Country	String	The country that is associated with the location of a customer. Values in this column depend on fields that are tagged with the Country semantic. Also in: Merged Customers, Unified Coalesced
Create DT	String	Apply the create-dt semantic tag to columns in customer records that identify when the data was created. The field to which this semantic is applied must be a datetime field type. Also in: Merged Customers, Unified Coalesced
Datasource	String	The name of the data source from which this customer record originated.
Email	String	The email address that is associated with a customer. A customer may have more than one email address. Values in this column depend on fields that are tagged with the Email semantic. Also in: Customer 360, Merged Customers, Unified Coalesced, Unified Preprocessed Raw
FK [Name]	String	The fk-[namespace] semantic tag identifies a field as a foreign key. A foreign key semantic tag must be namespaced. For example: fk-customer, fk-interaction, fk-audience, or fk-brand. A column is added for each foreign key that is defined in the Sources page. Tip What happens to foreign keys in the Unified Coalesced table? Records may have NULL foreign keys. There may be multiple foreign keys in the data source, but there may not be duplicate foreign keys. There may be multiple foreign keys per Amperity ID. There should not be multiple Amperity IDs per foreign key. Also in: Unified Coalesced, Unified Preprocessed Raw
Full Name	String	A combination of given name (first name) and surname (last name) for a customer. May include a middle name or initial. Values in this column depend on fields that are tagged with the full-name semantic. Also in: Merged Customers, Unified Coalesced
Gender	String	The gender that is associated with a customer. Values in this column depend on fields that are tagged with the Gender semantic. Also in: Customer 360, Merged Customers, Unified Coalesced, Unified Preprocessed Raw
Generational Suffix	String	The suffix that identifies to which family generation a customer record belongs. For example: Jr., Sr. II, and III. Also in: Merged Customers, Unified Coalesced
Given Name	String	The first name that is associated with a customer. Values in this column depend on fields that are tagged with the given-name semantic. Also in: Customer 360, Merged Customers, Unified Coalesced, Unified Preprocessed Raw
Loyalty ID	String	The identifier for a loyalty program that is associated with a customer.
Phone	String	The phone number that is associated with a customer. A customer may have more than one phone number. Values in this column depend on fields that are tagged with the Phone semantic. Also in: Customer 360, Merged Customers, Unified Coalesced, Unified Preprocessed Raw
PK	String	The combination of data source and primary key allows Amperity to uniquely identify every row in every data table across the entirety of customer data input to Amperity.
Postal	String	The zip code or postal code that is associated with the location of a customer. Values in this column depend on fields that are tagged with the Postal semantic. Also in: Customer 360, Merged Customers, Unified Coalesced, Unified Preprocessed Raw
State	String	The state or province that is associated with the location of a customer. Values in this column depend on fields that are tagged with the state semantic. Customer 360, Merged Customers, Unified Coalesced, Unified Preprocessed Raw
Surname	String	The last name that is associated with a customer. Values in this column depend on fields that are tagged with the surname semantic. Also in: Customer 360, Merged Customers, Unified Coalesced, Unified Preprocessed Raw
Title	String	The title that precedes a full name that is associated with a customer, such as “Mr.”, “Mrs”, and “Dr”. Also in: Merged Customers, Unified Coalesced
Update DT	String	Apply the update-dt semantic tag to columns in customer records that identify when the data was last updated in the source system. The field to which this semantic is applied must be a datetime field type. At least one customer record must have this semantic tag applied to ensure that the update_dt column is created in the Unified Coalesced table and to ensure that the Merged Customers table behaves correctly. Also in: Merged Customers, Unified Coalesced

Unified Email Events

The Unified Email Events table contains individual email event information, such as sends, opens, clicks, opt-in and opt-out preferences, bounces, and conversions. This table is added as Stitch output when email event semantic tags are applied to data sources.

Important

This table is only generated when email-events semantic tags are applied to data sources that provide at least 15 months of data for raw email events.

The Unified Email Events table contains the following columns:

Column name	Data type	Description
Brand	String	The brand or company from which an email was sent. Values in this column depend on fields that are tagged with the email-event/brand semantic.
Datasource	String
Email	String	The email address to which an email was sent. Values in this column depend on fields that are tagged with the email-event/email semantic.
Event Datetime	Datetime	The date and time at which email event occurred. Values in this column depend on fields that are tagged with the email-event/event-datetime semantic.
Event Type	String	The type of email event. Possible values: Open Click Sent Optin Bounce Converted Unsubscribe Important The values for email event types must spelled as expected to ensure the Email Engagement Summary can properly summarize email events. For example: “Optin” may not be “Opt-in”. Values in this column depend on fields that are tagged with the email-event/event-type semantic.
PK	String
Region	String	The region or location from which an email was sent. The region or location is typically associated to a single brand. Values in this column depend on fields that are tagged with the email-event/region semantic.
Send ID	String	The unique identifier for the email that was sent to an email address at a specific date and time. If a data source does not provide a send ID a unique key is generated. Values in this column depend on fields that are tagged with the email-event/send-id semantic.

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

Note

These columns are created when txn-item semantics are applied to interaction records that contain itemized transaction data.

The following diagram shows an example of the Unified Itemized Transactions table (click to view a larger diagram):

The Unified Itemized Transactions table contains the following columns:

Column name	Data type	Description
Amperity ID	String	The unique identifier that is assigned to clusters of customer records that all represent the same individual. The Amperity ID does not replace primary and foreign keys, but exists alongside them within unified profiles. Note The Amperity ID is a universally unique identifier (UUID) that is represented by 36 characters spread across five groups separated by hyphens: 8-4-4-4-12. For example: 123e4567-e89b-12d3-a456-426614174000
Customer ID	String	A custom semantic tag that is applied to interaction records to identify a field that is used in downstream processes to associate interaction records to the Amperity ID. A customer ID may appear once for each unique order ID in the itemized transactions table. Note A namespaced foreign key may be used instead of (or in addition to) a customer ID. Values in this column depend on fields that are tagged with the txn-item/customer-id semantic or a foreign key. Also in: Unified Transactions
Currency	String	Currency represents the type of currency that was used to pay for an item. For example: dollar. Note Currency must be consistent across all orders from the same data source. Values in this column depend on fields that are tagged with the txn-item/currency semantic.
Digital Channel	String	The digital channel through which a transaction was made. For example: Facebook, Google Ads, email, etc. Note This column should only have values when purchase-channel specifies an online channel. Values in this column depend on fields that are tagged with the txn-item/digital-channel semantic.
Is Cancellation	Boolean	A flag that indicates if the item was canceled. Values in this column depend on fields that are tagged with the txn-item/is-cancellation semantic. Important This value should be TRUE when items are canceled and FALSE when items are purchases and NULL when the value is unknown.
Is Return	Boolean	This column is input to predictive modeling. A flag that indicates if the item was returned. Values in this column depend on fields that are tagged with the txn-item/is-return semantic. Important This value should be TRUE when items are returns and FALSE when items are purchases and NULL when the value is unknown.
Item Cost	Decimal	Item cost is the cost to produce all units of an item. Note This value must be greater than or equal to 0 for purchases, but less than or equal to 0 for returns or cancellations. Values in this column depend on fields that are tagged with the txn-item/item-cost semantic.
Item Discount Amount	Decimal	Item discount amount is the discount amount that is applied to all units that are associated with a single item within a single transaction. This value should equal item quantity multiplied by unit discount amounts. This value is used by Amperity for discount sensitivity analysis. Note This value must be greater than or equal to 0 for purchases, but less than or equal to 0 for returns or cancellations. Values in this column depend on fields that are tagged with the txn-item/item-discount-amount semantic.
Item Discount Percent	Decimal	Item discount percent is the percentage discount that is applied to all units that are associated with a single item within a single transaction. This value is used by Amperity for discount sensitivity analysis. Note This value must be between 0 and 1. Values in this column depend on fields that are tagged with the txn-item/item-discount-percent semantic.
Item List Price	Decimal	Item list price is the manufacturer’s suggested retail price (MSRP) for all units of this item. The manufacturer’s suggested retail price (MSRP) is the price before shipping costs, taxes, and/or discounts have been applied. MSRP is sometimes referred to as the base price. This value should equal item revenue plus item discount amount. Note This value must be greater than or equal to 0 for purchases, but less than or equal to 0 for returns or cancellations. Values in this column depend on fields that are tagged with the txn-item/item-list-price semantic.
Item Profit	Decimal	Item profit represents the amount of profit that is earned when all units of an item are sold. Note This value must be greater than or equal to 0 for purchases, but less than or equal to 0 for returns or cancellations. Values in this column depend on fields that are tagged with the txn-item/item-profit semantic.
Item Quantity	Integer	This column is input to predictive modeling. Item quantity is the total number of items in an order. When an item has been returned or an order has been canceled, item quantity is the total number of items that were returned and/or canceled. Note This value must be less than or equal to 0 when is-return or is-cancellation are true. Values in this column depend on fields that are tagged with the txn-item/item-quantity semantic.
Item Revenue	Decimal	This column is input to predictive modeling. The total revenue for all units of an item, after discounts are applied. When an item has been returned or the order has been canceled, the total revenue for all items that were returned and/or canceled. This value should equal item quantity multiplied by unit revenue. Note This value must be less than or equal to 0 when is-return or is-cancellation are true. Tip A return or cancellation is stored as a separate record in the Unified Itemized Transactions table and is identified by a value of true in the Is Return or Is Cancellation column. Values in this column depend on fields that are tagged with the txn-item/item-revenue semantic.
Item Subtotal	Decimal	An item subtotal is the amount for an item, before discounts are applied. This value should equal unit list price times item quantity. This value is used by Amperity for discount sensitivity analysis. Note This value must be greater than or equal to 0 for purchases, but less than or equal to 0 for returns or cancellations. Values in this column depend on fields that are tagged with the txn-item/item-subtotal semantic.
Item Tax Amount	Decimal	An item tax amount is the total amount of taxes that are associated with the purchase of an item. Note This value must be greater than or equal to 0 for purchases, but less than or equal to 0 for returns or cancellations. Values in this column depend on fields that are tagged with the txn-item/item-tax-amount semantic.
Order Datetime	Datetime	This column is input to predictive modeling. Order datetime is the date (and time) on which an order was placed. Note Other dates associated with an order that are not specific to a transactions, such as dates associated with hotel stays and reservations, should be added to the Unified Product Catalog table. When Is Return is TRUE, the date and time on which the order was returned. When IS Cancellation is TRUE, the date and time on which the order was canceled. Values in this column depend on fields that are tagged with the txn-item/order-datetime semantic. This column is used to calculate the following transaction attributes: First Order Datetime Latest Order Datetime Second Order Datetime Also in: Unified Transactions
Order Discount Amount	Decimal	This column is input to predictive modeling. Order discount amount is the total discount amount that is applied to the entire order. Note This value must be greater than or equal to 0 for purchases, but less than or equal to 0 for returns or cancellations. Caution This value should match the customer’s definition of an order-level discount. For example, some customers associate this value to order value, whereas others associate this value to a subtotal. Use domain SQL to update this field for the desired calculation. Also in: Unified Transactions
Order ID	String	This column is input to predictive modeling. An order ID is the unique identifier for the order and links together all of the items that were part of the same transaction. When an item has been returned or when an order has been canceled, the order ID is the unique identifier for the original order, including the returned or canceled items. This field is often the primary key and associated with the pk semantic tag. Note For data that contains itemized transactions, where a single transaction includes more than one of the same item, the order ID will appear more than once. Note The order ID should never change, even when an item in the order is returned or canceled. Caution If order IDs are recycled and/or are otherwise not guaranteed to be unique over time, the unique identifier for the order must be updated to be a combination of the order ID and the date on which the order occurred. This must be done using domain SQL similar to: CONCAT(order_id, order_date). Values in this column depend on fields that are tagged with the txn-item/order-id semantic. The combination of Order ID and Product ID must be unique for each row in this table. This column is used to calculate the following transaction attributes: First Order ID L30D Average Num Items L30D Average Order Value L30D Order Frequency Latest Order ID lifetime Average Num Items Lifetime Average Order Value Lifetime Order Frequency Second Order ID Also in: Unified Transactions
Payment Method	String	A payment method is how a customer chose to pay for the items they have purchased. For example: credit card, gift card, or cash. Values in this column depend on fields that are tagged with the txn-item/payment-method semantic.
Product Catalogs	String	Optional Product catalog fields are added to the Unified Itemized Transactions table in two ways, depending on the approach your tenant used for defining your product catalog: When data sources use txn-item/ product catalog semantic tags. When product catalog fields are joined from the Unified Product Catalog table. Refer to the section in this topic about the columns that are available to product catalogs. Important The names of the columns that are available for product catalogs are identical. For example: Product Brand, Product Category, and Product Gender. The difference is the outcome of the approach your tenant used to define your product catalog within Amperity.
Product ID	String	This column is input to predictive modeling. The unique identifier for a product. A stock keeping unit (SKU) is an identifier that captures all of the unique details of any individual product, including specific attributes that differentiate by color, size, material, and so on. For example, a shirt with the same color and material, but with three different sizes would be represented by three unique SKUs and would also be represented by three unique product IDs. Note For data that contains itemized transactions, where a single transaction includes more than one of the same product, the product ID will appear more than once. Caution Every customer has their own definition for SKUs and product IDs. Be sure to understand this definition before applying semantic tags to fields with product IDs to ensure they accurately reflect the customer’s definition. Values in this column depend on fields that are tagged with the txn-item/product-id semantic. The combination of Order ID and Product ID must be unique for each row in this table; product quantity is counted using Item Quantity. Important This column is recommended when using a product catalog. A product catalog is a requirement for certain predictive features of Amperity, such as for product affinity and audience sizes.
Purchase Brand	String	The brand for which a transaction was made. Note This column should only have values when interaction records contain transactions data for more than one brand. Values in this column depend on fields that are tagged with the txn-item/purchase-brand semantic. This column is used to calculate the following transaction attributes: Multi Purchase Brand.
Purchase Channel	String	This column is input to predictive modeling. A purchase channel is the channel from which a transaction was made. For example: in-store or online. Values in this column depend on fields that are tagged with the txn-item/purchase-channel semantic.
Store ID	String	A store ID is a unique identifier that is identified with the location of a store. Values in this column depend on fields that are tagged with the txn-item/store-id semantic.
Unit Cost	Decimal	Unit cost is the cost to produce a single unit of one item. Note This value must be greater than or equal to 0 for purchases, but less than or equal to 0 for returns or cancellations. Values in this column depend on fields that are tagged with the txn-item/unit-cost semantic.
Unit Discount Amount	Decimal	Unit discount amount is the discount amount that is applied to a single unit of one item. This discount is often applied to all units of the same item within a single transaction. This value is used by Amperity for discount sensitivity analysis. Note This value must be greater than or equal to 0 for purchases, but less than or equal to 0 for returns or cancellations. Values in this column depend on fields that are tagged with the txn-item/unit-discount-amount semantic.
Unit List Price	Decimal	Unit list price is the manufacturer’s suggested retail price (MSRP) for a single unit of an item. The manufacturer’s suggested retail price (MSRP) is the price before shipping costs, taxes, and/or discounts have been applied. MSRP is sometimes referred to as the base price. This value should equal the unit discount amount plus the unit subtotal. Note This value must be greater than or equal to 0 for purchases, but less than or equal to 0 for returns or cancellations. Values in this column depend on fields that are tagged with the txn-item/unit-list-price semantic.
Unit Profit	Decimal	Unit profit represents the amount of profit that is earned when a single unit of an item is sold. Note This value must be greater than or equal to 0 for purchases, but less than or equal to 0 for returns or cancellations. Values in this column depend on fields that are tagged with the txn-item/unit-profit semantic.
Unit Revenue	Decimal	The total revenue for a single unit of an item. When an item has been returned or the order has been canceled, the total revenue for a single unit of an item that was returned and/or canceled. Note This value must be less than or equal to 0 when is-return or is-cancellation are true. Values in this column depend on fields that are tagged with the txn-item/unit-revenue semantic.
Unit Subtotal	Decimal	A unit subtotal is the amount for a single unit of one item, before discounts have been applied. This value is used by Amperity for discount sensitivity analysis. Note This value must be greater than or equal to 0 for purchases, but less than or equal to 0 for returns or cancellations. Values in this column depend on fields that are tagged with the txn-item/unit-subtotal semantic.
Unit Tax Amount	Decimal	A unit tax amount is the total amount of taxes that are associated with a single unit. Note This value must be greater than or equal to 0 for purchases, but less than or equal to 0 for returns or cancellations. Values in this column depend on fields that are tagged with the txn-item/unit-tax-amount semantic.
Custom attributes	Varies	You may extend the Unified Itemized Transactions table to define custom attributes for use with a product catalog. Important This column is recommended when using a product catalog. A product catalog is a requirement for certain predictive features of Amperity, such as for product affinity and audience sizes. Add custom attributes to the Unified Itemized Transactions table by extending it to add columns that support using a product catalog. Custom attributes should include the columns to which product catalog semantic tags were applied, but may include additional custom attributes that are unique to your tenant and your brands. Important The Unified Product Catalog table represents the taxonomy for your products and brands. Attributes are added to the Unified Product Catalog table when pc/ semantic tags are applied to your data sources. All pc/ semantic tags are optional. Use the ones that best define the shape of your product catalog and best describe the individual items within it. The product ID is used as an input to predictive modeling.

Unified Loyalty

The Unified Loyalty table contains a row for every customer who belongs to your loyalty program, unique by combination of Amperity ID and loyalty ID.

The Unified Loyalty table contains the following columns:

Column name	Data type	Description
Amperity ID	String	The unique identifier that is assigned to clusters of customer records that all represent the same individual. The Amperity ID does not replace primary and foreign keys, but exists alongside them within unified profiles. Note Loyalty profiles are unique by Amperity ID when: The fk-loyalty-id semantic tag is applied to the same source field as the loy/loyalty-id field. The loy/email semantic tag is applied to fields that contain email addresses. The loy/birthdate semantic tag is applied to fields that contain birthdates.
Birthdate	Date	The date of birth for the customer who belongs to the loyalty profile.
Current Balance	Integer	The customer’s current rewards balance.
Current Balance Expiration Datetime	Datetime	The date and time at which a customer’s current rewards balance will expire.
Current Tier	String	The name of the rewards tier to which a customer belongs.
Current Tier Expiration Datetime	Datetime	The date and time at which a customer’s membership in their current rewards tier will end.
Current Tier Start Datetime	Datetime	The date and time at which a customer’s membership in their current rewards tier started.
Email	String	The email address that is associated with a loyalty ID. Also in: Unified Loyalty Events
Is Opted-in	Boolean	Required. Indicates if the customer associated with the loyalty ID has given consent to being contacted by your loyalty program.
Latest Opt-out Datetime	Datetime	The date and time at which a customer most recently opted out from being contacted by your loyalty program.
Latest Opted-in Datetime	Datetime	Required. The date and time at which a customer most recently opted in to being contacted by your loyalty program.
Latest Update Datetime	Datetime	The date and time at which the information associated with the loyalty profile was updated.
Lifetime Balance	Integer	The lifetime reward balance associated with the loyalty ID.
Loyalty ID	String	Required. The unique ID for a loyalty profile. Also in: Unified Loyalty Events
Next Tier	String	The name of the next loyalty tier to which, pending points accumulation, a customer will belong.
Sign-up Channel	String	The channel through which the customer signed up for the loyalty program.
Sign-up Method	String	The method used by the customer to sign-up for the loyalty program.
Spend To Keep Tier	Decimal	The amount of money a customer must spend to stay in their current loyalty tier.
Spend To Next Tier	Decimal	The amount of money a customer must spend to move to the next loyalty tier.

Unified Loyalty Events

The Unified Loyalty Events table contains a row for loyalty program events, unique by Amperity ID and event datetime.

The Unified Loyalty Events table contains the following columns:

Column name	Data type	Description
Amperity ID	String	The unique identifier that is assigned to clusters of customer records that all represent the same individual. The Amperity ID does not replace primary and foreign keys, but exists alongside them within unified profiles. Note Loyalty events are unique by Amperity ID and event datetime when: The fk-loyalty-id semantic tag is applied to the same source field as the loy-event/loyalty-id field. The loy-event/email semantic tag is applied to fields that contain email addresses.
Accrual Amount	Integer	The loyalty rewards amount that was accrued.
Accrual Datetime	Datetime	The date and time at which loyalty rewards were accrued.
Award ID	String	The unique ID for an award that is associated with a redemption or accrual event.
Current Point Balance	Integer	The loyalty rewards balance that is associated with the current loyalty event.
Current Tier	String	The loyalty tier that is associated with the current loyalty event.
Email	String	The email address that is associated with a loyalty ID. Also in: Unified Loyalty
Event Datetime	Datetime	Required. The date and time at which a loyalty event occurred. Note For transactions that represent accruals this value can also represent the date and time of the transaction.
Event Description	String	A description of the loyalty event.
Event Type	String	Required. The loyalty event type. May be one of the following values: “redemption”, “opt-out”, or “tier change”, or a custom event type.
Expiration Datetime	Datetime	The date and time at which loyalty awards accrue or the loyalty tier changes.
Loyalty ID	String	Required. The unique ID for a loyalty profile. Also in: Unified Loyalty
Order Datetime	Datetime	The date and time at which an order that used accrued or redeemed loyalty rewards was made.
Order ID	String	The unique ID for an order that is associated with a redemption or accrual event.
Previous Point Balance	Integer	The loyalty rewards balance that is associated with the previous loyalty event.
Previous Tier	String	The loyalty tier that is associated with the previous loyalty event.
Redemption Amount	Decimal	The loyalty rewards amount that was redeemed.
Redemption Datetime	Datetime	The date and time at which loyalty rewards were redeemed.
Reservation Datetime	Datetime	The date and time at which a reservation that used accrued or redeemed loyalty rewards was made.
Reservation ID	String	The unique ID for a reservation that is associated with a redemption or accrual event.
Tier End Datetime	Datetime	The date and time at which the current loyalty tier ends (or ended).
Tier Start Datetime	Datetime	The date and time at which the current loyalty tier starts (or started).

Unified Preprocessed Raw

The Unified Preprocessed Raw table is an output of Stitch. Use this table as part of Stitch QA to view data exactly how values were used by Stitch after preprocessing and blocklisting. This table contains one row for each trivial duplicate. Only the semantics used by Stitch are shown. Semantic values represent pre-processing values and/or bad values that were removed (and replaced by NULL).

Tip

Because there is only one row per trivial duplicate, join this table on the Unified Coalesced table for ongoing usage. For example:

SELECT *
FROM Unified_Coalesced uc
JOIN Unified_Preprocessed_Raw up
ON uc.rep_ds=up.datasource AND uc.rep_pk=up.pk

The Unified Preprocessed Raw table contains the following columns:

Column name	Data type	Description
Amperity ID	String	The unique identifier that is assigned to clusters of customer records that all represent the same individual. The Amperity ID does not replace primary and foreign keys, but exists alongside them within unified profiles. Note The Amperity ID is a universally unique identifier (UUID) that is represented by 36 characters spread across five groups separated by hyphens: 8-4-4-4-12. For example: 123e4567-e89b-12d3-a456-426614174000
Address	String	The address that is associated with the location of a customer, such as “123 Main Street”. Also in: Customer 360, Merged Customers, Unified Coalesced, Unified Customer
Birthdate	Date	The date of birth that is associated with a customer. Also in: Customer 360, Merged Customers, Unified Coalesced, Unified Customer
City	String	The city that is associated with the location of a customer. Also in: Customer 360, Merged Customers, Unified Coalesced, Unified Customer
Component ID	Integer	An identifier that represents a set of records that are transitively connected with a score above threshold as an outcome of blocking and initial scoring. Records that share a component ID, but have different Amperity IDs, were split during hierarchical comparison. Tip Use this field during the Stitch QA process to help identify why certain records were grouped (or not grouped) together. Also in: Unified Preprocessed Raw
Datasource	String	The name of the data source from which this customer record originated. Tip The combination of PK and Datasource uniquely identifies a row in the Unified Preprocessed Raw table, which correlates to a single row in a domain table.
Email	String	The email address that is associated with a customer. A customer may have more than one email address. Also in: Customer 360, Merged Customers, Unified Coalesced, Unified Customer
FK [Name]	String	The fk-[namespace] semantic tag identifies a field as a foreign key. A foreign key semantic tag must be namespaced. For example: fk-customer, fk-interaction, fk-audience, or fk-brand. A column is added for each foreign key that is defined in the Sources page. Note If foreign keys are linked together by a trivial duplicate they will appear in the Unified Preprocessed Raw table as a comma-separated list. Also in: Unified Coalesced, Unified Customer
Gender	String	The gender that is associated with a customer. Also in: Customer 360, Merged Customers, Unified Coalesced, Unified Customer
Given Name	String	The first name that is associated with a customer. Also in: Customer 360, Merged Customers, Unified Coalesced, Unified Customer
Int ID	Integer	An identifier that represents a unique combination of datasource and pk (primary key) and their position in the Stitch record sort order after Stitch has identified (and removed) trivial duplicates. Important This identifier is based on Stitch record sort order and is determined each time Stitch runs. Records will not always have the same position in the record sort order; you should not expect this ID to be stable over time. Tip Use this field during the Stitch QA process to help identify why certain records were grouped (or not grouped) together.
Login Trimmed	String
Phone	String	The phone number that is associated with a customer. A customer may have more than one phone number. Also in: Customer 360, Merged Customers, Unified Coalesced, Unified Customer
PK	String	The combination of data source and primary key allows Amperity to uniquely identify every row in every data table across the entirety of customer data input to Amperity. Tip The combination of PK and Datasource uniquely identifies a row in the Unified Preprocessed Raw table, which correlates to a single row in a domain table.
PO Box	String	Additional address information, such as an apartment number or a post office box, that is associated with the location of a customer, such as “Apt #9”.
Postal	String	The zip code or postal code that is associated with the location of a customer. Also in: Customer 360, Merged Customers, Unified Coalesced, Unified Customer
SK Generational Suffix	String
SK Given Name	String
State	String	The state or province that is associated with the location of a customer. Also in: Customer 360, Merged Customers, Unified Coalesced, Unified Customer
Supersized ID	Integer	The identifier for a supersized record. Indicates when a rough heuristic is applied to the first grouping of records (rep_pk) to partition supersized records into smaller components. Supersized records occur when more than 500 groups are associated with the first grouping of records. Also in: Unified Coalesced
Surname	String	The last name that is associated with a customer. Also in: Customer 360, Merged Customers, Unified Coalesced, Unified Customer

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.

Important

The Unified Product Catalog table represents the taxonomy for your products and brands. Attributes are added to the Unified Product Catalog table when pc/ semantic tags are applied to your data sources. All pc/ semantic tags are optional. Use the ones that best define the shape of your product catalog and best describe the individual items within it. The product ID is used as an input to predictive modeling.

The Unified Product Catalog table contains the following columns:

Column name	Data type	Description
Datasource	String	The name of the data source from which this customer record originated. Tip The combination of PK and Datasource uniquely identifies a row in the Unified Coalesced table, which correlates to a single row in a domain table.
PK	String	The combination of data source and primary key allows Amperity to uniquely identify every row in every data table across the entirety of customer data input to Amperity. Tip The combination of PK and Datasource uniquely identifies a row in the Unified Coalesced table, which correlates to a single row in a domain table.
Product Brand	String	The brand name of a product or item.
Product Brand ID	String	The ID for the brand name of a product or item.
Product Category	String	This column is input to predictive modeling when joined to Unified Itemized Transactions. A category to which the product belongs.
Product Category ID	String	The ID for the category to which a product belongs.
Product Class	String	The name of the class (or grouping) to which a product or item belongs.
Product Class ID	String	The ID for the name of the class (or grouping) to which a product or item belongs.
Product Collection	String	The name of the collection to which a product or item belongs.
Product Collection ID	String	The ID for the name of the collection to which a product or item belongs.
Product Color	String	The color of a product or item.
Product Color ID	String	The ID for the color of a product or item.
Product Department	String	The department to which a product or item belongs.
Product Department ID	String	The ID for the department to which a product or item belongs.
Product Description	String	This column is input to predictive modeling when joined to Unified Itemized Transactions. A description of the product.
Product Division	String	The division to which a product or item belongs.
Product Division ID	String	The ID for the division to which a product or item belongs.
Product Fabric	String	The fabric used for a product or item.
Product Fabric ID	String	The ID for the fabric used for a product or item.
Product Gender	String	A list of gender options for products. For example: F, M, unisex, NULL (for unknown).
Product Group	String	The group to which a product or item belongs.
Product ID	String	The unique identifier for a product. Values in this column depend on fields that are tagged with the pc/product-id semantic.
Product Material	String	The material used for a product or item.
Product Material ID	String	The ID for the material used for a product or item.
Product MSRP	String	The manufacturer’s suggested retail price (MSRP) for a product or item. The manufacturer’s suggested retail price (MSRP) is the price before shipping costs, taxes, and/or discounts have been applied. MSRP is sometimes referred to as the base price.
Product Name	String	The name of the product or item.
Product Season	String	The season to which a product or item is associated.
Product Season ID	String	The ID for the season to which a product or item is associated.
Product Silhouette	String
Product Size	String	The size of a product or item.
Product Size ID	String	The ID for the size of a product or item.
Product Sku	String	The stock keeping unit, or SKU, for the product or item. A stock keeping unit (SKU) is an identifier that captures all of the unique details of any individual product, including specific attributes that differentiate by color, size, material, and so on.
Product Style	String	The style of a product or item.
Product Subcategory	String	This column is input to predictive modeling when joined to Unified Itemized Transactions. A subcategory or secondary variant to which a product belongs.
Product Subcategory ID	String	The ID for the subcategory or secondary variant to which a product belongs.
Product Subclass	String	The subclass to which a product or item is assigned.
Product Subclass ID	String	The ID for the subclass to which a product or item is assigned.
Product Subdepartment	String	The sub-department to which a product or item is assigned.
Product Subdepartment ID	String	The ID for the sub-department to which a product or item is assigned.
Product Type	String	The type assigned to a product or item.
Product UPC	String	The UPC code for the product or item. A Universal Product Code (UPC or UPC code) is a barcode that is widely used to track items in stores.

Unified Scores

The Unified Scores table records all of the pairwise comparison scores and match categories for all groups of records, and then for each group of records all of the pairwise scores that are present between records within that group.

Note

See Unified Scores for more information about how this table is built and maintained within the customer 360 database.

The Unified Scores table contains the following columns:

Column Name	Data type	PII	Description
Amperity ID	String		The unique identifier that is assigned to clusters of customer records that all represent the same individual. The Amperity ID does not replace primary and foreign keys, but exists alongside them within unified profiles. Note The Amperity ID is a universally unique identifier (UUID) that is represented by 36 characters spread across five groups separated by hyphens: 8-4-4-4-12. For example: 123e4567-e89b-12d3-a456-426614174000
Source1	String
Source2	String
PK1	String
PK2	String
Score	Float		A score contains a value from 0.0 to 5.0 that represents the combined score assigned to the record pair by Stitch. There are two components of the score: the score itself, and then its strength. The record pair score correlates to the match category, which is a classifier that is applied by Amperity to individual record pairs. The record pair score corresponds to the classification: 5 for exact matches, 4 for excellent matches, 3 for high matches, 2 for moderate matches, 1 for weak matches, and 0 for no matches. The record pair strength represents the strength of the record pair score. It is a two digit number. For example: .31 is a lower strength and .93 is a very high strength. Note Scores are shown for records that end up in the same cluster, including any scores that are below threshold. Scores are not shown for records that do not end up in the same cluster. Also in: Detailed Examples
Match Category	String		A match category is a classifier that is applied by Amperity to an individual record-pair within a cluster of record-pairs. The match category is the result of this classification. Match Category Description Exact Amperity has the highest confidence that these records represent the same person because all profile data exactly matches. Excellent Amperity has near perfect confidence that these records belong to the same person, despite select types of profile data not matching. High Using deductive reasoning, Amperity has very high confidence that these records match, despite some profile data not matching. Moderate Amperity has moderate confidence that these records match, due to weak or fuzzy matches between highly unique customer attributes (email, phone, address). Weak Amperity lacks confidence, but if asked to guess, Amperity would assert these records do belong to the same individual, because they match on non-unique customer attributes (name, state, zip code). Non-match Amperity has high confidence that these records do NOT match, because core profile data is in conflict. Also in: Detailed Examples
Match Type	String		The type of score being applied. Possible values are as follows: “scored”, “scored_transitive”, and “trivial_duplicate”. A match is assigned “scored_transitive” when that match was not identified during blocking. For example: three records (A, B, and C). If records A and B and records B and C were identified as matching during blocking, all three records will end up in the same group of records for pairwise comparison. Records A and C have a transitive connection.

Unified Transactions

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

The following diagram shows an example of the Unified Transactions table (click to view a larger diagram):

The Unified Transactions table contains the following columns:

Column name	Data type	Description
Amperity ID	String	This column is input to predictive modeling. The unique identifier that is assigned to clusters of customer records that all represent the same individual. The Amperity ID does not replace primary and foreign keys, but exists alongside them within unified profiles. Note The Amperity ID is a universally unique identifier (UUID) that is represented by 36 characters spread across five groups separated by hyphens: 8-4-4-4-12. For example: 123e4567-e89b-12d3-a456-426614174000
Currency	String	Currency represents the type of currency that was used to pay for an item. For example: dollar. Note Currency must be consistent across all orders from the same data source.
Customer ID	String	A custom semantic tag that is applied to interaction records to identify a field that is used in downstream processes to associate interaction records to the Amperity ID. A customer ID may appear once for each order ID in the transactions table. When a foreign key is not present, the customer ID is used to join interaction records (transactions and itemized transactions) to tables that contain the Amperity ID. Note A namespaced foreign key should be used along with a customer ID. When a foreign key is added to transactions data it: Must match a foreign key in a table that is output by Stitch Must be well-distributed across the data source (a high percentage of values must not be 0) May contain a NULL value Must be unique for each order ID in the Unified Transactions table. Also in: Unified Itemized Transactions
Digital Channel	String	The digital channel through which a transaction was made. For example: Facebook, Google Ads, email, etc. Note This column should only have values when purchase-channel specifies an online channel.
Fiscal Calendars	Varies	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. The SQL template for the Unified Transaction table contains a series of fields for use with fiscal calendars. For example: Fiscal Year Fiscal Quarter Fiscal Month Fiscal Week Number Fiscal Week Start Fiscal Day of Week Holiday Sale Name Before you can use fiscal calendar attributes in the Unified Transactions table you must configure the SQL template for your brand’s fiscal calendar.
Net Order Revenue	Decimal	Net order revenue is total revenue minus costs, returns, and discounts. This value must be configured in the SQL template for the Unified Transaction table.
Order Canceled Quantity	Integer	This column is input to predictive modeling. The total number of items in the original transaction that were canceled. This value should match the sum of all items in the itemized transactions that were canceled for the same order ID. Important This value must be less than or equal to 0 when is_canceled is TRUE.
Order Canceled Revenue	Decimal	This column is input to predictive modeling. The total amount of revenue for all canceled items in the transaction. This value should match the sum of the revenue for all items in the itemized transactions that were canceled. Important This value must be less than or equal to 0 when is_canceled is TRUE.
Order Cost	Decimal	Order cost represents the total cost of goods sold (COGS) for a single transaction, minus returns, cancellations, and discounts. Cost of goods sold (COGS) are the direct costs of producing goods that are sold by a brand, including the costs of materials and labor to produce the item, but excluding indirect expenses like distribution or sales. This value must be configured in the SQL template for the Unified Transaction table. Note This value must be greater than or equal to 0 for purchases, but less than or equal to 0 for returns or cancellations. Warning Only one of order-profit and order-cost may be present for a transaction.
Order Datetime	Datetime	This column is input to predictive modeling. Order datetime is the date (and time) on which an order was placed. The order date: Must have a consistent time zone across all dates in the transactions data. Should be a local time zone. Should be a timestamp, which is converted to datetime automatically when a date is present in the timestamp. Note Other dates associated with an order that are not specific to a transactions, such as dates associated with hotel stays and reservations, should be added to the Unified Product Catalog table. This column is used to calculate the following transaction attributes: First Order Datetime Latest Order Datetime Second Order Datetime Also in: Unified Itemized Transactions
Order Discount Amount	Decimal	This column is input to predictive modeling. Order discount amount is the total discount amount that is applied to the entire order. Note This value must be greater than or equal to 0 for purchases, but less than or equal to 0 for returns or cancellations. This value is used by Amperity for discount sensitivity analysis and must be configured in the SQL template for the Unified Transaction table. Caution This value should match the customer’s definition of an order-level discount. For example, some customers associate this value to order value, whereas others associate this value to a subtotal. Use domain SQL to update this field for the desired calculation. Also in: Unified Itemized Transactions
Order Discount Percent	Decimal	Order discount percent is the percentage discount that is applied to the order value for the entire transaction, in addition to any item or unit-specific discount percentages. This value may be used as an input to order discount amount. Note This value must be between 0 and 1. This value is used by Amperity for discount sensitivity analysis. Caution This value should match the customer’s definition of an order-level discount percentage. For example, some customers associate this value to order value, whereas others associate this value to a subtotal. Use domain SQL to update this field for the desired calculation.
Order ID	String	This column is input to predictive modeling. An order ID is the unique identifier for the order and links together all of the items that were part of the same transaction. When an item has been returned or when an order has been canceled, the order ID is the unique identifier for the original order, including the returned or canceled items. This column is the primary key and must be associated with the PK semantic tag. Each unique order ID must: Appear only once in the Unified Transactions. Match an order ID in the Unified Itemized Transactions table. (This order ID might be associated to multiple items within a single transactions.) Note The order ID should never change, even when an item in the order is returned or canceled. Caution If order IDs are recycled and/or are otherwise not guaranteed to be unique over time, the unique identifier for the order must be updated to be a combination of the order ID and the date on which the order occurred. This must be done using domain SQL similar to: CONCAT(order_id, order_date). This column is used to calculate the following transaction attributes: First Order ID L30D Average Num Items L30D Average Order Value L30D Order Frequency Latest Order ID Lifetime Average Num Items Lifetime Average Order Value Lifetime Order Frequency Second Order ID Also in: Unified Itemized Transactions
Order List Price	Decimal	Order list price is the total value for a transaction, as defined by the manufacturer’s suggested retail price (MSRP) for all units of this item. The manufacturer’s suggested retail price (MSRP) is the price before shipping costs, taxes, and/or discounts have been applied. MSRP is sometimes referred to as the base price. This value should match the sum of item list price amounts in the itemized transactions that are associated with the same order ID. Note This value must be greater than or equal to 0 for purchases, but less than or equal to 0 for returns or cancellations.
Order Profit	Decimal	Order profit is the amount of profit that is earned from a single transaction. Note This value must be greater than or equal to 0 for purchases, but less than or equal to 0 for returns or cancellations. Warning Only one of order-profit and order-cost may be present for a transaction.
Order Quantity	Integer	This column is input to predictive modeling. Order quantity is the total number of individual items associated with the transaction. This value should match the sum of all items in the itemized transactions that have not been returned or canceled for the same order ID. Note This value must be greater than or equal to 0 for purchases, but less than or equal to 0 for returns or cancellations. This column is used to calculate the following transaction attributes: First Order Total Items L30D Average Item Price L30D Average Num Items L30D Total Items Latest Order Total Items Lifetime Average Item Price Lifetime Average Num Items Lifetime Total Items Second Order Total Items
Order Returned Quantity	Integer	This column is input to predictive modeling. Order returned quantity is the total number of items in the original transaction that were returned. This value should match the sum of all items in the itemized transaction that were returned for the same order ID. Note This value must be less than or equal to 0. Important This value must be less than or equal to 0 when is_return is TRUE.
Order Returned Revenue	Decimal	This column is input to predictive modeling. Order returned revenue total amount of revenue for all returned items in a transaction. This value should match the sum of the revenue for all items in the itemized transactions that were returned. Important This value must be less than or equal to 0 when is_return is TRUE.
Order Revenue	Decimal	This column is input to predictive modeling. The total amount of revenue for all items in a transaction after discounts are applied, ignoring returns and/or cancellations. Note This value must be greater than or equal to 0 for purchases, but less than or equal to 0 for returns or cancellations. This column is used to calculate the following transaction attributes: First Order Revenue L30D Average Item Price L30D Average Order Value L30D Largest Order Value L30D Order Revenue Latest Order Revenue Lifetime Average Item Price Lifetime Average Order Value Lifetime Largest Order Value Lifetime Order Revenue Second Order Revenue
Order Shipping Amount	Decimal	The order shipping amount is the total cost of shipping all items in the same transaction. Note This value must be greater than or equal to 0 for purchases, but less than or equal to 0 for returns or cancellations.
Order Subtotal	Decimal	An order subtotal is the amount for an order, before discounts are applied. This value should match the sum of item subtotal revenue in the itemized transactions that are associated with the same order ID.
Order Tax Amount	Decimal	An order tax amount is the total amount of taxes that are associated with an entire order. Note This value must be greater than or equal to 0 for purchases, but less than or equal to 0 for returns or cancellations.
Payment Method	String	A payment method is how a customer chose to pay for the items they have purchased. For example: credit card, gift card, or cash.
Purchase Brand	String	This column is input to predictive modeling. The brand for which a transaction was made. Note This column should only have values when interaction records contain transactions data for more than one brand. This column is used to calculate the following transaction attributes: Multi Purchase Brand.
Purchase Channel	String	This column is input to predictive modeling. A purchase channel is the channel from which a transaction was made. For example: in-store or online.
Store ID	String	This column is input to predictive modeling. A store ID is a unique identifier that is identified with the location of a store.
Sum Item Discount Amount	Decimal	The sum of discount amounts is the total of all discount amounts that were applied to each item within a transaction. This value should match the sum of item discount amounts in the itemized transactions that are associated with the same order ID.
Sum Item Revenue	Decimal	The sum of itemized revenue for the original order, not including returns and/or cancellations. This value may be used as an input to order revenue. This value should match the sum of item revenue in the itemized transactions that are associated with the same order ID. Note This value must be greater than or equal to 0 for purchases, but less than or equal to 0 for returns or cancellations.