Build customer profiles

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

The customer 360 database is the most important database you can build in Amperity. It is the source from which all segments are created and from which data will be sent to external systems for downstream workflows.

Amperity relies on data sources that contain personally identifiable information (PII) about your customers to build customer profiles.

For each data source that your brand makes available to Amperity that contains PII:

1. Apply customer profile semantic tags to individual fields within data sources

2. Run input validations

3. Make domain tables that contain PII available to Stitch

4. Build your brand’s customer 360 database

5. Activate database

Apply semantics

Use a feed or custom domain table to apply customer profile semantic tags to every data source that contains personally identifiable information (PII) about your customers.

Customer profile semantic tags include address, birthdate, city, email, gender, given-name, phone, postal, state, and surname. Apply them to individual fields within data sources to define a common schema across customer profiles in your customer 360 database.

Customer profile semantic tags do not require you to make any changes to the schemas that exist within an incoming data sources.

When a feed is activated, Amperity loads the data from the data source into a domain table.

Multi-brand databases

To support multi-brand databases, you must ensure that a brand column exists within the data source. This enables filtering customer profiles by brand. Adding a brand column may require using a custom domain table.

Review validations

The quality of the data sources your brand chooses to make available to Amperity matters when it comes to building unified customer profiles because your brand uses those profiles to activate your customers across a wide variety of downstream use cases. More accurate profiles lead to higher activation rates, better match rates, and increased returns on advertiser spend.

Amperity includes a series of input validation reports that help your brand measure the quality of email addresses, phone numbers, and transactions. Use them to quickly identify data quality issues so that your brand can work to resolve those data quality issues as soon as possible.

Input validations are run against domain tables that have been published, and then made available to the Queries page. Use input validation reports help your brand discover data quality issues with email addresses, phone numbers, and transactions.

Note

Input validation reports are meant to be informative and to provide a way for your brand to explore data and discover when data that is made available to Amperity is missing values or has values that do not match the expected format.

Input validation reports do not have pass or fail thresholds and will not stop automated workflows within Amperity.

Some input validations measure against a single semantic tag, while others use a combination of semantic tags. All input validations are returned as a series of columns that describe the quality of your data as it relates to a specific each report, with a row for each data source.

You do not need to run Stitch or have a working customer 360 database to run input validations. Just publish the domain tables to make them available to the Queries page.

INPUT VALIDATIONS CHECKLIST

Review customer profile reports Email addresses and phone numbers are validated for the percentage of missing values, correctly formatted values, and the frequency at which unique values occur. For example, for email addresses: The percentage of customer profiles with missing email addresses. The sum of email addresses that do not have a valid format. The sum of unique email addresses by data source. The uniqueness of email addresses by data source, compared across all data sources with email addresses. and for phone numbers: The percentage of customer profiles with missing phone numbers. The sum of phone numbers that do not have a valid format. The uniqueness of phone numbers by data source, compared across all data sources with phone numbers. The percentage of records with phone numbers that do not contain 10 digits.

Review transaction reports A complete transaction with good underlying data has a combination of values that work together to describe what was purchased, when it was purchased, where it was purchased, and so on. Incomplete transactions are shown using a variety of reports. For example: All items should be associated with a unique product ID. All orders should be associated with a store ID. All orders should have a quantity, a revenue amount, and an order date. All orders should have a unique order ID. An item cannot be returned and canceled. canceled item revenue should be negative Discount amounts should be positive Discount percent should be “discount amount / list price” From which store was a purchase made? Quantity should be negative when items were canceled or returned Quantity should be positive Returned item revenue should be negative Revenue should be “list price - discount amount” Revenue should be positive Through which channel was a purchase made? Was an item canceled or returned? This should be true or false. What percentage of product IDs are unique? Each individual validation report shows the data sources that are associated with the report, the semantic tags that were validated, and the results of the validation. Explore the data that is associated with this report by clicking the Validation query link, which opens in the Queries page.

Run Stitch

Stitch runs on a daily basis after all of your brand’s data sources have provided refreshed data. This updates your brand’s unified customer profiles and refreshes the transactions and engagment data that is associated with each unique customer profile.

STITCH CHECKLIST

	Make domain tables available to Stitch All domain tables in which customer profile semantic tags were applied to one (or more) fields should be made available to Stitch.
	Run Stitch Stitch evaluates all of the data to which customer profiles were applied and builds a unified customer profile for all of your customers, including linking each customer’s interactions with your brand to their customer profile. Tip Use courier groups to configure Amperity to run Stitch automatically.

Build database

Your customer 360 database must be configured before you can use customer profiles to build audiences that support your brand’s use cases and downstream workflows.

Important

Most of the work required to build your customer 360 database happens during initial configuration.

Depending on the types of data sources your brand adds to Amperity over time, you may need to make specific changes to specific tables in your customer 360 database to support these updates.

For example, if your brand adds a data source that contains PII you may need to update the source and field priorities that are defined in the Merged Customers table.

The initial configuration of your customer 360 database requires using SQL to add (and extend) a series of tables that are the foundation of your brand’s set of unified customer profiles.

1. Unified Coalesced

2. Best email address

3. Merged Customers

4. Customer 360

Your customer 360 database is typically refreshed on a daily basis, after Stitch has finished the process of analyzing and updating your customer profiles.

Unified coalesced

The Unified Coalesced table is an output of the Stitch identity resolution process. This table is refreshed every time Stitch runs and contains one row for each unique record from every data source that contains customer PII.

Individual rows within the Unified Coalesced table may not represent complete profiles. For example:

• Row 1 contains details from data source A and has customer email addresses, first and last names, and postal codes

• Row 2 contains details from data source B and has phone numbers and first names

• Row 3 contains details from data source C and has first and last names, postal codes, and phone numbers

• And so on …

Each row within the Unified Coalesced table is assigned an Amperity ID.

UNIFIED COALESCED CHECKLIST

	Initial configuration only The Unified Coalesced table is added to your customer 360 database as part of the “Customer 360” database template. The table is refreshed automatically after every Stitch run.
	Multi-brand databases only To support multi-brand databases, you must update the customer 360 database to use the multi-brand SQL template for the Unified Coalesced table.

Best email address

The Email Ampid Assignment table identifies the best email address to use for each customer profile in the Merged Customers table. This configuration is enabled by default and is configurable.

BEST EMAIL ADDRESS CHECKLIST

	Initial configuration only Add the Email Ampid Assignemnt table to your customer 360 database. Use the SQL build mode option, and then select “Email Ampid Assigment” from the Template drop-down.
	Optional. Use email priority instead of Amperity ID assignment The following steps are necessary to update the Merged_Customers table to use email address priority and completion values instead of the best email address identified by the Email Ampid Assignment table: Add NAMED_STRUCT for email addresses Add email address priority Replace email completion Undo LEFT JOIN

Merged customers

The Merged Customers table contains one row for each Amperity ID in the Unified Coalesced table.

Individual rows within the Merged Customers table represent unique customer profiles. This is done by collapsing all of the rows in the Unified Coalesced table that share the same Amperity ID into a single row.

MERGED CUSTOMERS CHECKLIST

	Initial configuration only Add the Merged Customers table to your customer 360 database. Use the SQL build mode option, and then select “Merged Customers” from the Template drop-down.
	Repeat for all data sources that contain PII A data source that is made available to Stitch may be assigned a value for source priority. An integer value will assign priority, where 1 has a higher priority than 2. Domain tables may be assigned the same priority. For each data source that is made available to Stitch, review the source priority section in the Merged Customers table and verify that each data source is assigned a source priority. Tables that are not included in the source priority list are assigned a 999 priority.
	Repeat for all data sources that contain PII A data source that is made available to Stitch may assign field priorities for names, physical addresses, email addresses, birthdates, and gender. A NULL field priority value uses the source priority value as the field priority value. An integer priority value takes precedence over source priority when the field priority value is higher than the source priority value. The list of tables defined for field priority should be the same as the list of tables defined for source priority. For each data source that is made available to Stitch, review the field priority section in the Merged Customers table and verify that each field within a data source is assigned a field priority.
	Configure best email address Narrow the many-to-many relationships between Amperity IDs and email addresses down to a one-to-one relationship, and then make that email address available to a customer profile in the Merged Customers table. Important The Email Ampid Assignment table determines the best email address to be associated with each unique customer in the Merged Customers table.

Customer 360

The Customer 360 table contains all of your brand’s unified customer profiles combined with the individual actions each of your customers have had with your brand.

Individual rows within the Customer 360 table represent customer profiles (and their interactions), unique by Amperity ID.

CUSTOMER 360 CHECKLIST

Initial configuration only Add the Customer 360 table to your customer 360 database. Use the SQL build mode option, and then select “Customer 360” from the Template drop-down. - or - You may extend the Customer 360 table to include attributes from the Unified Transactions and Transaction Attributes Extended tables. Use the SQL build mode option, select “Customer 360 with Transaction Attributes” from the Template drop-down, and then uncomment the attributes you want to include. You may add any attribute in the Unified Transactions and Transaction Attributes Extended tables to the Customer 360 table.

Optional. Extend for custom attributes You may extend the Customer 360 table to include attributes that may be required by downstream use cases. For example, adding hashed email addresses or hashed phone numbers.

Activate database

To make data available in your customer 360 database you must activate, and then run the database. This is typically done manually during initial configuration, after which the process is automated to run on a daily basis after Stitch has finished updating customer profiles.

ACTIVATE DATABASE CHECKLIST

Multi-brand databases only Use the SQL build mode option, select “Database Key” from the Template drop-down, and then configure the SQL in that template to support the brand that should be associated with this customer 360 database. Important Add the Database Key table before running the customer 360 database. Caution Multi-brand databases also require using a multi-brand SQL template for the Unified Itemized Transactions table. That table should be configured before running the customer 360 database. You can extend the Unified Itemized Transactions table after, but should finish extending that table before you use the initial customer 360 database as a template for each multi-brand database.

Run the customer 360 database Active, and then run the customer 360 database. After the run is complete review the output for each table.