About Data Tables

A customer 360 database is built using standard outputs of the Stitch process that provide a unified view of customer data, including customer profile and interaction records that are linked together by the Amperity ID, organized, merged, and ready for use in segmentation.

About the data model

The data model represents the “out-of-the-box” tables that are available to every tenant. (AmpIQ tables are available only to tenants that have added AmpIQ to Amperity and have completed the process of enabling predictive attributes.)

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

The core data model for Amperity.

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

The color associated with the customer profile table group.

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

The color associated with the interaction records table group.

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

The color associated with the Stitch results table group.

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. Togehter they provide visibility into how Amperity grouped (or did not group) individual customer records to a single Amperity ID.

AmpIQ

The color associated with the AmpIQ table group.

AmpIQ enables customer-centric marketing campaigns. Use segment insights to build high-value segments. Use those segments to add audiences to campaigns. Build campaigns that send those audiences to any combination of downstream marketing workflows.

AmpIQ tables are enabled for users of AmpIQ and 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:1 or 1:many relationship between those three tables and AmpIQ 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 tab:

Name

Description

1:1, 1:many

An indicator that this column has a 1:1 or 1:many relationship with another table.

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

An indicator that this column has a many:1 relationship with another table.

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

An indicator that this column is a primary key.

Indicates this column is the primary key for this table.

Linking key

An indicator that this column is a 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 tab.

Data source

An indicator that this column is associated to the original data source.

Indicates this column is associated to an original customer data source in the Sources tab, with the value of this field being the name of that data source.

Campaign_Recipients

The Campaign_Recipients table contains a list of Amperity IDs associated with any campaign (i.e., sub-audiences) that were sent from Amperity. Each campaign is associated with the segment used to generate the list of recipients, the control and recipient 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 leverage 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 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.

treatment_id

String

The ID for the recipient group to which the associated campaign was sent.

treatment_name

String

The name of the recipient group to which the associated campaign was sent. One or more recipient 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 Customer360 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

This column is input to AmpIQ predictive modeling on multi-brand tenants.

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 an individual customer record.

Also in: Merged_Customers, Unified_Coalesced, Unified_Customer, Unified_Preprocessed_Raw

surname

String

The last name that is associated with an individual customer record.

Also in: Merged_Customers, Unified_Coalesced, Unified_Customer, Unified_Preprocessed_Raw

email

String

The email address that is associated with an individual customer record. A customer record may be associated with multiple email addresses.

Also in: Merged_Customers, Unified_Coalesced, Unified_Customer, Unified_Preprocessed_Raw

phone

String

The phone number that is associated with an individual customer record. A customer record may be associated with multiple phone numbers.

Also in: Merged_Customers, Unified_Coalesced, Unified_Customer, Unified_Preprocessed_Raw

address

String

The address that is associated with the location of an individual customer record. For example: 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 an individual customer record.

Also in: Merged_Customers, Unified_Coalesced, Unified_Customer, Unified_Preprocessed_Raw

state

String

The state or province that is associated with the location of an individual customer record.

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 an individual customer record.

Also in: Merged_Customers, Unified_Coalesced, Unified_Customer, Unified_Preprocessed_Raw

birthdate

Date

The date of birth that is associated with an individual customer record.

Also in: Merged_Customers, Unified_Coalesced, Unified_Customer, Unified_Preprocessed_Raw

gender

String

The gender that is associated with an individual customer record.

Also in: Merged_Customers, Unified_Coalesced, Unified_Customer, Unified_Preprocessed_Raw

Customer_Attributes

The Customer_Attributes table contains a series of flags that identify attributes about individuals, such as if that individual can be contacted, if a marketable email address, physical address, or phone number is available, if they are an employee, reseller, or if the individual represents a test account, along with identifying each individuals’s revenue relationship with a brand.

The Customer_Attributes table 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

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 can be contacted by an email address.

contactable_global

Boolean

A flag that indicates if a customer can be contacted by phone number, email address, or physical mailing address.

contactable_phone

Boolean

A flag that indicates if a customer can be contacted by phone.

customer_classifications

String

A string that contains all of the classifications for a customer. For example: "is_reseller, is_outlier" or "is_test_account". A value of " " indicates that all classifications are false.

customer_type

String

The type of user: “customer” or “prospect”. A customer has a historical revenue relationship with a brand. A prospect does not have a revenue relationship with a brand.

historical_purchaser_lifecycle_status

String

The status for a customer, based on their history and recency of interactions a brand. Possible values: “active”, “lapsed”, and “dormant”.

Customer states are defined as “active”, “lapsed”, “dormant”, and “prospect”. Purchase behaviors 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”.

historical_prospect_lifecycle_status

String

The status for a customer who does not have a purchase history with your brand. A prospect is a potential customer to whom you may want to conduct marketing activity. Possible values: “prospect”.

is_business

Boolean

A flag that indicates if a customer has purchased items for business use.

is_employee

Boolean

A flag that indicates if a customer has been an employee of the brand at any time.

is_employee_current

Boolean

A flag that indicates if a customer is a current employee of the brand.

is_gift_giver

Boolean

A flag that indicates if a customer has purchased items as gifts.

is_outlier

Boolean

A flag that indicates if the customer has abnormally high purchase behaviors in comparison to other purchasers.

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 as a threshold, such as > 50)

is_test_account

Boolean

A flag that indicates if the customer is a known test account for a brand.

marketable_address

Boolean

A flag that indicates if a customer has a marketable physical mailing address and has given permission to be contacted.

marketable_email

Boolean

A flag that indicates if a customer has a marketable email mailing address and has given permission to be contacted.

marketable_global

Boolean

A flag that indicates if a customer has a marketable phone number, email address, or physical mailing address and has given permission to be contacted.

marketable_phone

Boolean

A flag that indicates if a customer has a marketable phone number and has given permission to be contacted.

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 an individual customer record. For example: 123 Main Street.

birthdate_a

birthdate_b

String

The date of birth that is associated with an individual customer record.

case_count

String

city_a

city_b

String

The city that is associated with the location of an individual customer record.

country_a

country_b

String

The country that is associated with the location of an individual customer record.

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 an individual customer record. A customer record may be associated with multiple email addresses.

given_name_a

given_name_b

String

The first name that is associated with an individual customer record.

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

No conflict

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 an individual customer record. A customer record may be associated with multiple phone numbers.

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 an individual customer record.

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 an individual customer record.

surname_a

surname_b

String

The last name that is associated with an individual customer record.

Email_Engagement_Attributes

The Email_Engagement_Attributes table contains all fields from the Email_Engagement_Summary table, unique by Amperity ID and by email address. This table may be extended for related email engagement and transaction attributes.

The Email_Engagement_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

brand

String

The brand or company from which an email was sent.

email

String

The email address to which an email was sent.

event_datetime

Datetime

The date and time at which email event occurred.

event_type

String

The type of email event. Possible values:

  • Open

  • Click

  • Sent

  • Opt-in

  • Bounced

  • Converted

region

String

The region or location from which an email was sent. The region or location is typically associated to a single brand.

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.

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.

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.

Lifecycle_Events

The Lifecycle_Events table enables the use of event-driven customer behaviors within campaigns by providing a set of fields that combine predictions for customer behaviors with transaction details from the Unified_Transactions table.

The Lifecycle_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

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

event_status

String

The status for a lifecycle event. Possible values:

  • Active

  • Cooling down

  • At risk

  • Highly at risk

  • Lost

event_status_start_date

Datetime

The date on which the current lifecycle event started.

event_type

String

The type of lifecycle event. For example: “Predicted Lifecycle Status”.

is_current

Boolean

A flag that indicates if this is the most recent lifecycle event associated with the Amperity ID.

Tip

When the value of is_current is true only the most recent event_status is returned.

previous_event_status

String

The lifecycle event status for the previous lifecycle event associated with the Amperity ID.

prev_event_status_start_date

Datetime

The date on which the previous lifecycle event started. This value is NULL when there is no previous lifecycle event.

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 AmpIQ 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 an individual customer record. For example: 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 an individual customer record. For example: Apt #9.

Also in: Unified_Coalesced, Unified_Customer

birthdate

Date

This column is input to AmpIQ predictive modeling.

The date of birth that is associated with an individual customer record.

Also in: Customer_360, Unified_Coalesced, Unified_Customer, Unified_Preprocessed_Raw

city

String

This column is input to AmpIQ predictive modeling.

The city that is associated with the location of an individual customer record.

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 an individual customer record.

country

String

The country that is associated with the location of an individual customer record.

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 AmpIQ predictive modeling.

The email address that is associated with an individual customer record. A customer record may be associated with multiple email addresses.

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) that is associated with an individual customer record and is stored as a combined value in a single field within customer data. A full name may include a middle name or initial.

Also in: Unified_Coalesced, Unified_Customer

gender

String

This column is input to AmpIQ predictive modeling.

The gender that is associated with an individual customer record.

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 AmpIQ predictive modeling.

The first name that is associated with an individual customer record.

Also in: Customer_360, Unified_Coalesced, Unified_Customer, Unified_Preprocessed_Raw

loyalty_id

String

The identifier for a loyalty program that is associated with an individual customer record.

Also in: Unified_Coalesced

phone

String

This column is input to AmpIQ predictive modeling.

The phone number that is associated with an individual customer record. A customer record may be associated with multiple phone numbers.

Also in: Customer_360, Unified_Coalesced, Unified_Customer, Unified_Preprocessed_Raw

postal

String

This column is input to AmpIQ predictive modeling.

The zip code or postal code that is associated with the location of an individual customer record.

Also in: Customer_360, Unified_Coalesced, Unified_Customer, Unified_Preprocessed_Raw

state

String

This column is input to AmpIQ predictive modeling.

The state or province that is associated with the location of an individual customer record.

Also in: Customer_360, Unified_Coalesced, Unified_Customer, Unified_Preprocessed_Raw

surname

String

This column is input to AmpIQ predictive modeling.

The last name that is associated with an individual customer record.

Also in: Customer_360, Unified_Coalesced, Unified_Customer, Unified_Preprocessed_Raw

title

String

The title that precedes a full name that is associated with an individual customer record. For example: 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. 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

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 incude ~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

This score is used internally by AmpID and does not directly correlate to ranking and/or audience size. Sort results by ranking, and then compare 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 AmpIQ.

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:

  1. Active (likelihood to purchase is greater than 60%)

  2. Cooling down (likelihood to purchase is between than 50% and 60%)

  3. At risk (likelihood to purchase is between than 35% and 50%)

  4. Highly at risk (likelihood to purchase is between than 20% and 35%)

  5. Lost (likelihood to purchase is less than 20%)

For one-time buyers, groupings include the following tiers:

  1. Active (purchased within 60 days)

  2. Cooling down (purchased 60-120 days ago)

  3. At risk (purchased 120-180 days ago)

  4. Highly at risk (purchased 180-240 days ago)

  5. Lost (purchased 240+ days ago)

predicted_customer_lifetime_value_tier

String

A percentile grouping of customers by pCLV. Groupings include:

  1. Platinum: top 1% of customers

  2. Gold: top 1%-5% of customers

  3. Silver: top 5%-10% of customers

  4. Bronze: top 10%-25% of customers

  5. Medium: top 25%-50% of customers

  6. Low: bottom 50% of customers

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_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_value 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).

No conflict

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. This table is added automatically when semantics related to transactions are applied to interaction records.

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 cancelled 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 cancelled 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 cancelled 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 cancelled 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 cancelled 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 cancelled 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 added to the Transaction_Attributes_Extended table, which is an output of Stitch. Extended transaction attributes are fully available to the customer 360 database and to segments.

Extended transaction attributes are presented as a single table (with many columns), including an Amperity ID, and fit into the following categories:

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 is 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_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>_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_revenue

Integer

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>_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

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

Column Name

Data type

PII

Description

L12M_rfm_score

Integer

The RFM score for the customer based on transactions that occurred within the last 12 months. The RFM score is represented as an integer between 0 and 999 and is concatenated using the individual recency, frequency, and monetary scores or is NULL if the customer has zero purchases within the last 12 months.

L12M_recency

Integer

The recency score for customer transactions that occurred within the last 12 months. The recency score is represented as an integer between 0 and 9. Each integer represents 10%:

  • 0: 0-10th percentile

  • 1: 10-20th percentile

  • 2: 20-30th percentile

  • 3: 30-40th percentile

  • 4: 4-50th percentile

  • 5: 5-60th percentile

  • 6: 60-70th percentile

  • 7: 70-80th percentile

  • 8: 80-90th percentile

  • 9: 90-100th percentile

L12M_frequency

Integer

The frequency score for customer transactions that occurred within the last 12 months. The frequency score is represented as an integer between 0 and 9. Each integer represents 10%:

  • 0: 0-10th percentile

  • 1: 10-20th percentile

  • 2: 20-30th percentile

  • 3: 30-40th percentile

  • 4: 4-50th percentile

  • 5: 5-60th percentile

  • 6: 60-70th percentile

  • 7: 70-80th percentile

  • 8: 80-90th percentile

  • 9: 90-100th percentile

L12M_monetary

Integer

The monetary score for customer transactions that occurred within the last 12 months. The monetary score is represented as an integer between 0 and 9. Each integer represents 10%:

  • 0: 0-10th percentile

  • 1: 10-20th percentile

  • 2: 20-30th percentile

  • 3: 30-40th percentile

  • 4: 4-50th percentile

  • 5: 5-60th percentile

  • 6: 60-70th percentile

  • 7: 70-80th percentile

  • 8: 80-90th percentile

  • 9: 90-100th percentile

Unified_Changes

The Unified_Changes table contains a 30-day rolling history of every change that occurred during a Stitch run. This table is also useful for downstream processes associated with non-customer 360 databases, such as synchronizing Amperity output to external customer-owned systems and the Stitch QA process.

DEPRECATED

This table has been replaced by the Unified_Changes_Clusters and Unified_Changes_PKS tables. Please refer to those sections.

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: add, delete, and updated:

  • The add change type indicates a primary key was added to the cluster.

  • The delete 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 every row from every stitched table that is associated with an Amperity ID, with defined semantics coalesced into a single column. A unique Amperity ID may appear in more than one row. Columns are added to this table when semantic values match values in the bad-values blocklist.

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 an individual customer record. For example: 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 an individual customer record. For example: 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 an individual customer record.

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 an individual customer record.

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 an individual customer record.

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 an individual customer record. A customer record may be associated with multiple email addresses.

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

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) that is associated with an individual customer record and is stored as a combined value in a single field within customer data. A full name 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 an individual customer record.

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 an individual customer record.

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 an individual customer record.

Also in: Merged_Customers

phone

String

The phone number that is associated with an individual customer record. A customer record may be associated with multiple phone numbers.

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 an individual customer record.

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 datasource that is associated with the rep_pk column.

rep_pk

Integer

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 nearly-identical records that were grouped together by Stitch early in the identity resolution process. (These nearly-identical records are also referred to as a “trivial duplicate”.)

All of these nearly-identical records 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 an individual customer record.

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 an individual customer record.

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 an individual customer record. For example: 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. 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 consists of all the tables tagged with the email, request-id, request-strategy, and request-type semantic tags, which are used to suppress data per customer deletion requests or a data subject access request (DSAR).

The Unified_Compliance table consolidates sources tagged with:

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

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_Custoers

Customer profile

String

This contains the columns for the following customer profile fields: address, birthdate, city, email, gender, given_name, loyalty_id, phone, postal, surname, and state

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.

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

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

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.

request_datasource

String

An identifier that represents the first grouping of records done by Stitch. This grouping is based on identical semantic patterns.

request_email

String

The email address for the customer. This is used to find their records in Amperity.

request_id

Integer

The tracking identifier for the customer’s compliance workflow. This ID should be provided by the customer and must be unique.

request_strategy

String

The compliance request strategy used for matching exact email data, semantic tags, and Amperity IDs.

request_type

String

The type of compliance request. Possible values: delete or data subject access request (DSAR).

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 an individual customer record. For example: 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 an individual customer record. For example: 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 an individual customer record.

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 an individual customer record.

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 an individual customer record.

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 an individual customer record. A customer record may be associated with multiple email addresses.

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

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) that is associated with an individual customer record and is stored as a combined value in a single field within customer data. A full name 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 an individual customer record.

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 an individual customer record.

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 an individual customer record.

phone

String

The phone number that is associated with an individual customer record. A customer record may be associated with multiple phone numbers.

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 an individual customer record.

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 an individual customer record.

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 an individual customer record.

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 an individual customer record. For example: 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. 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

  • Opt-in

  • Bounced

  • Converted

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 every row of every stitched table with all transactional data summarized to the item level, and then coalesced into a single column for each unique combination of order ID and product ID. The order ID is associated with an Amperity ID.

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):

An example of the Unified_Itemized_Transactions data table.

The Unified_Itemized_Transactions table contains the following columns:

Column Name

Data type

Description

amperity_id

String

This column is input to AmpIQ 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

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 by 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 cancelled.

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 AmpIQ 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

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 AmpIQ predictive modeling.

Item quantity is the total number of items in an order. When an item has been returned or an order has been cancelled, item quantity is the total number of items that were returned and/or cancelled.

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 AmpIQ 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 cancelled, the total revenue for all items that were returned and/or cancelled.

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.

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 AmpIQ 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 cancelled.

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_id

String

This column is input to AmpIQ 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 cancelled, the order ID is the unique identifier for the original order, including the returned or cancelled 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 cancelled.

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_id

String

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 defintition 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 AmpIQ, 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 AmpIQ 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

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 cancelled, the total revenue for a single unit of an item that was returned and/or cancelled.

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 AmpIQ, 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.

Note

What is the Unified_Product_Catalog table? The Unified_Product_Catalog table represents the taxonomy for your products and brands. You may apply product catalog semantic tags to any table that contains the taxonomy for your products and brands, and then use the product identifier as the basis for AmpIQ predictive modeling.

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 an individual customer record. For example: 123 Main Street.

Also in: Customer_360, Merged_Customers, Unified_Coalesced, Unified_Customer

birthdate

Date

The date of birth that is associated with an individual customer record.

Also in: Customer_360, Merged_Customers, Unified_Coalesced, Unified_Customer

city

String

The city that is associated with the location of an individual customer record.

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 an individual customer record. A customer record may be associated with multiple email addresses.

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

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 an individual customer record.

Also in: Customer_360, Merged_Customers, Unified_Coalesced, Unified_Customer

given_name

String

The first name that is associated with an individual customer record.

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 an individual customer record. A customer record may be associated with multiple phone numbers.

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 an individual customer record. For example: Apt #9.

postal

String

The zip code or postal code that is associated with the location of an individual customer record.

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 an individual customer record.

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 an individual customer record.

Also in: Customer_360, Merged_Customers, Unified_Coalesced, Unified_Customer

Unified_Product_Catalog

The Unified_Product_Catalog table contains a row for every product SKUs, broken down by product ID and base ID, along with list prices and costs.

Note

What is the Unified_Product_Catalog table? The Unified_Product_Catalog table represents the taxonomy for your products and brands. You may apply product catalog semantic tags to any table that contains the taxonomy for your products and brands, and then use the product identifier as the basis for AmpIQ 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_category

String

This column is input to AmpIQ predictive modeling when joined to Unified_Itemized_Transactions.

A category to which the product belongs.

product_description

String

A description of the product.

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_subcategory

String

A subcategory or secondary variant to which a product belongs.

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

No conflict

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.

Note

These columns are created when txn semantics are applied to interaction records that contain transactions data.

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

An example of the Unified_Transactions data table.

The Unified_Transactions table contains the following columns:

Column Name

Data type

Description

amperity_id

String

This column is input to AmpIQ 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.

Values in this column depend on fields that are tagged with the txn/currency semantic.

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.

Values in this column depend on fields that are tagged with the txn/customer-id semantic or a foreign key.

Also in: Unified_Itemized_Transactions

digital_channel

String

The digital channel by 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/digital-channel semantic.

order_cancelled_quantity

Integer

This column is input to AmpIQ predictive modeling.

The total number of items in the original transaction that were cancelled.

This value should match the sum of all items in the itemized transactions that were cancelled for the same order ID.

Important

This value must be less than or equal to 0 when is_cancelled is TRUE.

Values in this column depend on fields that are tagged with the txn/order-cancelled-quantity semantic.

order_cancelled_revenue

Decimal

This column is input to AmpIQ predictive modeling.

The total amount of revenue for all cancelled items in the transaction.

This value should match the sum of the revenue for all items in the itemized transactions that were cancelled.

Important

This value must be less than or equal to 0 when is_cancelled is TRUE.

Values in this column depend on fields that are tagged with the txn/order-cancelled-revenue semantic.

order_cost

Decimal

Order cost represents the total cost of goods sold (COGS) for a single transaction.

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.

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.

Values in this column depend on fields that are tagged with the txn/order-cost semantic.

order_datetime

Datetime

This column is input to AmpIQ 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.

Values in this column depend on fields that are tagged with the txn/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_Itemized_Transactions

order_discount_amount

Decimal

This column is input to AmpIQ 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.

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.

Values in this column depend on fields that are tagged with the txn/order-discount-amount semantic.

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.

Values in this column depend on fields that are tagged with the txn/order-discount-percent semantic.

order_id

String

This column is input to AmpIQ 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 cancelled, the order ID is the unique identifier for the original order, including the returned or cancelled 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 cancelled.

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/order-id semantic.

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

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.

Values in this column depend on fields that are tagged with the txn/order-list-price semantic.

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.

Values in this column depend on fields that are tagged with the txn/order-profit semantic.

order_quantity

Integer

This column is input to AmpIQ 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 cancelled 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.

Values in this column depend on fields that are tagged with the txn/order-quantity semantic.

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

Values in this column depend on fields that are tagged with the txn/order-returned-quantity semantic.

order_returned_revenue

Decimal

This column is input to AmpIQ 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.

Values in this column depend on fields that are tagged with the txn/order-returned-revenue semantic.

order_revenue

Decimal

This column is input to AmpIQ 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.

Values in this column depend on fields that are tagged with the txn/order-revenue semantic.

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.

Values in this column depend on fields that are tagged with the txn/order-shipping-amount semantic.

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.

Values in this column depend on fields that are tagged with the txn/order-subtotal semantic.

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.

Values in this column depend on fields that are tagged with the txn/order-tax-amount semantic.

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/payment-method semantic.

purchase_brand

String

This column is input to AmpIQ 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.

Values in this column depend on fields that are tagged with the txn/purchase-brand semantic.

This column is used to calculate the following transaction attributes: multi_purchase_brand.

purchase_channel

String

This column is input to AmpIQ 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/purchase-channel semantic.

store_id

String

This column is input to AmpIQ predictive modeling.

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/store-id semantic.

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.

Values in this column depend on fields that are tagged with the txn/sum-item-discount-amount semantic.

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.

Values in this column depend on fields that are tagged with the txn/sum-item-revenue semantic.