About databases

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.

Customer 360 page

The Customer 360 page allows databases to be created from stitched output. In addition to the database, databases may be created to provide visibility into the quality of the Stitch process, for additional non-customer 360 use cases, or for experimentation.

Customer profile

Customer profile details are pulled from the Customer 360 table, which represents each customer’s unified profile. This table contains common profile attributes, such as:

• Names (first name, last name), email address, physical address, phone

• Transaction details (first purchase, last purchase, total purchases, etc)

• Other custom profile values that are unique to your company.

These details are summarized in the Customer Profile section of the Customer 360 page.

Each customer profile is a collection of common attributes (first name, last name, email, phone, etc.), transaction attributes (first purchase, last purchase, total purchases, etc.), and other custom values that are unique to each customer’s data set. These details are summarized on the Customer 360 page under Customer Profile.

All databases

The Customer 360 page may define any number of individual databases, in addition to your database. For example, it is common to add a parallel database for use with the Stitch QA process.

The All Databases section of the Customer 360 page lists all of the databases that have been created, including both active and draft states.

Other databases may be created to support any desired use case. Each database has a specific schema (i.e. collection of tables and attributes). This is called a data model. The data model and individual database tables Amperity provides should make data easy to understand and explore for its specific use case and client.

Note

Only databases in an Active state may be run against stitched data.

Customer 360 database

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.

A customer 360 database contains a set of standard tables, and then optional source and custom tables that may be added as passthrough tables.

Multiple databases

You may configure more than one database to be a customer 360 database. Each database will contain a set of standard tables, and then may have an optional set of source and custom tables added as passthrough tables.

Flexible merge rules

Some customer data platforms require using an inflexible merge rule across multiple fields, which results in lower quality data across your customer 360 profile. This problem is magnified when that inflexible merge rule must also be applied to multiple databases.

Amperity combines the use of flexible merge rules with a patented system that allows multiple databases to exist within the same tenant. This ensures that:

1. Merge rules are 100% configurable

2. Each field can have its own merge rule

3. Each database can have its own set of merge rules

4. Each tenant can support a variety of merge rules to meet all of the requirements for any individual use case

For example, data sources from call centers, online transactions, and email platforms may contain slightly different sets of customer profile data.

After loading this data to Amperity and assigning the Amperity ID to each of your customers, you can use flexible merge rules to support multiple customer 360 databases.

• Your operations teams can combine prioritizing the most common values for each customer with deterministic matching

• Your email marketing team can combine prioritizing customer profile values from your email platform with probabilistic matching

• Your paid media team can combine all possible values to improve match rates on platforms like Google Ads and Facebook

Ask your Amperity implementation team for recommendations and best practices for how you can configure flexible merge rules to support all of your use cases.

Table groups

Tables are organized into the following groups:

• Standard

• Source and custom

• QA

Standard

Amperity standard tables form the foundation of the customer profile. Standard tables include:

• Customer 360

• Customer Attributes

• Merged Customers

• Transaction Attributes

• Unified Coalesced

• Unified Customer

• Unified Itemized Transactions

• Unified Scores

• Unified Transactions

Source and custom

Source and custom tables extend the customer profile with raw source data and custom views. Source and custom tables will vary from tenant to tenant, but typically represent data that can provide useful context to your customer profile data and to support a variety of upstream and downstream workflows, such as:

• Behavioral data

• Clickstream data

• Marketing campaign response data

• Display advertising performance data

• And so on …

QA

QA tables are Amperity-generated data assets used to validate customer profiles. Amperity provides a database template for Stitch QA.

How-tos

This section describes tasks related to managing building and maintaining databases and database tables in Amperity.

• Activate database

• Add database

• Add database from template

• Add table

• Configure database

• Configure table

• Delete database

• Delete table

• Discard drafts

• Download schema

• Edit database

• Edit table

• Enforce static schemas

• Explore customer profile

• Explore database

• Export databases and tables

• Run databases

• View all databases

• View database

• View run history

• View notifications

• View statistics

Activate database

A database may be in one of the following states: active and draft:

• An active database is fully configured to use stitched output tables, is ready to be run, is ready for queries and segmentation, and may be added to a workflow.

• A draft database is in an inactive state that is not ready for segmentation. A draft database may used only for testing or experimentation. It may also be progressing toward being put into an active state.

Only databases in an Active state may be run against stitched data. Activation is a required step–“Click the Activate button.”–at the end of any process that adds or edits a database.

Add database

Add an empty database to support use cases, and also as the starting point for the customer 360 database.

To add a database

1. From the Customer 360 page, under All Databases, click Create Database.

2. Enter a name for the database, such as Customer 360.

3. Leave the Template drop-down set to None.

4. Click Create.

Add database from template

You can add a database from a template:

• Add customer 360 database

• Add passthrough database

• Add Stitch QA database

Add customer 360 database

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.

To add a customer 360 database using a template

1. From the Customer 360 page, under All Databases, click Create Database.

2. Add the name for the customer 360 database.

   Tip

   Append “360” to the name of all databases that are configured to be a customer 360 database.

3. From the Template drop-down, select Customer 360.

4. Click Create.

Add passthrough database

A passthrough database contains all tables in the customer 360 database.

To add the Passthrough database using a template

1. From the Customer 360 page, under All Databases, click Create Database.

2. From the Template drop-down, select Passthrough.

   Note

   The name of the database is automatically assigned based on the name of the passthrough table. For passthrough tables built for core AmpID, Amp360, and AmpID tables, it is recommended to keep the automatically assigned name.

3. Click Create.

Add Stitch QA database

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.

A Stitch QA database loaded from a template will contain a list of pre-loaded SQL queries that may be used to help validate the quality of Stitch output.

To add the Stitch QA database using a template

1. From the Customer 360 page, under All Databases, click Create Database.

2. Enter a name for the database.

3. From the Template drop-down, select Stitch QA.

4. Click Create.

Add table

Tables may be added to any database in the Customer 360 page. Open the database and click Add Table. Tables may be of two types: passthrough tables (that already have Amperity IDs) or SQL tables (that may have Amperity IDs and/or associate unique IDs in tables to the Amperity ID).

• Add passthrough table

• Add SQL table

• Add SQL table from a template

Add passthrough table

A passthrough table adds a table to the customer 360 database using an existing table without making any changes to its schema.

If a table already contains an Amperity ID, you may use a passthrough table.

To add a table as a passthrough table

1. From the Customer 360 page, under All Databases, select the menu for a database, and then click Edit.

2. Click Add Table.

3. Set Build Mode to “passthrough”, and then select a table from the drop-down menu.

   Note

   The name of the database is automatically assigned based on the name of the passthrough table. For passthrough tables built for core AmpID, Amp360, and AmpIQ tables, it is recommended to keep the automatically assigned name.

4. Click Activate to update the database with your changes.

   Tip

   You can the SQL for a passthrough table directly in the database.

Add SQL table

A SQL table adds a table to the customer 360 database using Spark SQL and a SELECT statement to define the table’s schema.

If a table was not stitched and/or does not have an Amperity ID, you must use a SQL table to associate the unique ID in the table to the Amperity ID.

For example, CCUST associates the unique ID in the table to the Amperity ID:

SELECT
CCUST.amperity_id,
CTX.customer_id,
CTX.orderid,
CTX.purchasedate,
CTX.transactiontotal,
CTX.pointsearned,
CTX.numberofitems,
CTX.avgitemprice,
CTX.productcode,
CTX.productcategory,
CTX.storeorwebid

FROM POS_transactions AS CTX
LEFT JOIN POS_customers AS CCUST ON (CCUST.customer_id = CTX.customer_id)

To add a table as a SQL table

1. From the Customer 360 page, under All Databases, select the menu for the database, and then click Edit.

2. Click Add Table.

3. Set Build Mode to “SQL”, and then define a SQL query using Spark SQL.

4. Click Validate to verify that the SQL query runs correctly.

5. Click Activate to update the database with your changes.

Add SQL table from a template

You can use a SQL template to build a table. Templates are provided for all standard tables (Customer 360, Merged Customers. Transaction Attributes, Transaction Attributes Extended, Unified Itemized Transactions, Unified Transactions, and Customer Attributes) along with additional templates for use with multi-brand databases and to support use cases like joining transaction attributes to the Customer 360 table, merged households, and customer lifecycle events.

To add a SQL table from a template

1. From the Customer 360 page, under All Databases, select the menu for the database, and then click Edit.

2. Click Add Table.

3. Set the build mode to SQL.

4. Click Apply template, and then select a template from the list of templates.

5. Update the table template for placeholders and other requirements that are specified in the template comments.

6. Click Validate to verify that the SQL query runs correctly.

7. Click Activate to update the database with your changes.

Override standard table

A standard database table forms the foundation of your databases. Standard tables are output by Stitch and provide unified data structures for all data sources to which semantic tags were applied.

Some use cases require a standard database table to have different information than what is generated by upstream workflows, such as tables that are output by Stitch.

Tip

You should avoid overriding standard database tables because it creates a new (custom) database table with the same name as an existing standard table; all references to that table name will resolve to the new (overriding) database table, and not to the original standard table.

Standard tables include:

• Campaign Recipients

• Customer 360

• Customer Attributes

• Email Engagement Attributes

• Email Engagement Summary

• Lifecycle Events

• Merged Customers

• Transaction Attributes

• Transaction Attributes Extended

• Unified Customer

• Unified Coalesced

• Unified Email Events

• Unified Itemized Transactions

• Unified Product Catalog

• Unified Scores

• Unified Transactions

along with tables that are associated with predictive modeling and AmpIQ, such as Predicted CLV Attributes.

Caution

You cannot override non-standard database tables and will receive an error message similar to:

Table name "table" is already used by a domain table, upstream of database "database". Database tables cannot be configured to override non-standard tables.

For example, the Unified Itemized Transactions requires a product ID. The product_id column is created by applying the product-id semantic tag to itemized interactions records.

However, in some cases another table contains the preferred product ID. The following example shows how to override the standard Unified Itemized Transactions table and to join more complete product ID information from the Unified Product Catalog table:

SELECT
  uit.*
  ,upc.product_category AS `product_category`
  ,upc.product_description AS `product_description`
  ,upc.product_subcategory AS `product_subcategory`
FROM
  Unified_Itemized_Transactions uit
  LEFT JOIN Unified_Product_Catalog upc ON uit.product_id = upc.product_id

To override a standard table

1. From the Customer 360 page, under All Databases, select the menu for the database, and then click Edit.

2. Click Add Table.

   Important

   The table name must be the same as the original standard database table.

3. Set the build mode to SQL, and then define a SQL query using Spark SQL.

   Note

   The custom definition must refer to the original table, i.e. “must refer to the table that is being overridden”. In some cases, this requires using a placeholder reference. For example:

   WITH placeholder AS (
     SELECT *
     FROM override_allowed_table
     LIMIT 1
   )
   ...
   
   SELECT *
   FROM unrelated_table
   

4. Click Validate to verify that the SQL query runs correctly.

5. Click Activate to update the database with your changes.

Configure database

Each table in the database has its own set of configuration settings:

• Allow use in segments

• Hide from users

• Set as Customer 360

• Set description

Allow use in segments

A database must be made visible to non-admin users of Amperity so they may access database tables from the Segments page. Visibility is required to build segments via the Segment Editor or the SQL Segment Editor. To make a database visible to non-admin users, enable the In VSE? checkbox.

To allow database tables to be used in segments

1. From the Customer 360 page click Create Database.

2. Name the database, and then click Save. This opens the Database Editor page.

3. Under Settings, enable the In VSE option. This will allow this database to be visible to users who are working in the segment editor.

4. Click Save.

Hide from users

A table may be hidden from users when they access the Data Explorer or Queries page.

Important

This setting does not prevent users from directly querying the table in the Queries page by referencing it by name.

To hide this table from users

1. From the Customer 360 page click Create Database.

2. Name the database, and then click Save. This opens the Database Editor page.

3. Under Advanced Settings, expand Visibility and Access.

4. Select the box next to Hide in Data Explorer and Queries tab.

5. Click Save.

Set as Customer 360

The Set as Customer 360 option must be selected and the following tables must be added:

• Customer360

• Unified_Customer

• Unified_Scores

After these tables are added and tagged correctly, the database may be activated.

To set a database as a customer 360 database

1. From the Customer 360 page click Create Database.

2. Name the database, and then click Save. This opens the Database Editor page.

3. Under Database Type, select Set as Customer 360 Database. This will make this database the default starting point in the visual Query Editor.

   Note

   The customer 360 database may only be activated when the following tables are present: Customer 360, Unified Customer, and Unified Scores.

4. Click Activate.

Set description

You can add descriptions for each table in a database. This is especially important for any tables that will be interacted with by others in your organization.

To set the table description

1. From the Customer 360 page click Create Database.

2. Name the database, and then click Save. This opens the Database Editor page.

3. Under Settings, add the table description in the Description box.

4. Click Save.

Configure table

Tables are configured from the Database Table page. This is done in two steps:

1. Choosing how to build the table: as a passthrough, from a template, or by using Spark SQL.

2. Configuring table settings, including * marking certain fields when they contain PII, if fields are unique and/or required, setting up pick-lists, adding field descriptions, choosing icons for fields that are part of the customer profile, and enable access to this table from the visual Segment Editor.

Add field description

You can add descriptions for each field in a table. This is especially important for any fields that were created within Amperity that are not from the customer’s original data sources.

Note

Field descriptions may be edited at any time.

To add a description for a field

1. Open a table in the database editor.

2. In the second step (after the SQL vs. passthrough step), add a field descriptions in the Description column.

3. Click Save.

Apply table tags

A tag associates a table in a database with a table requirement for the database: “Customer360”, “Unified Customers”, and “Unified Scores”. Tags should be assigned by Amperity automatically as the tables are built. You can see which tag is assigned to a table from the Tag column in the Database Viewer.

To apply table tags

1. Open a table in the database editor.

2. In the second step (after the SQL vs. passthrough step), on the right, under Tag, select the name of a table from the list.

3. Click Save.

Enable visual segments

The Segment Editor is the user interface for building segments in AmpIQ. This editor uses a series of drop-downs, picklists, conditions, and values to define an attribute profile. Refresh segment insights to see how many customers match the profile, and then activate it as a segment.

Every table in the database that has an Amperity ID should be made available to the Visual Segment Editor. For segments that require more functionality than what the Visual Segment Editor allows, you may switch from visual queries to SQL queries (though you may not switch back).

Note

Only tables that contain an Amperity ID can be made available to the Visual Segment Editor.

The Customer 360 table is made available to the Segment Editor automatically. Other tables may be made available by selecting the checkbox in the Show in VSE column in the Database Editor. When working in the Segments page creating visual segments, the list of available tables appears under Additional Attributes.

To enable visual segments

1. Open a table in the database editor.

2. In the second step (after the SQL vs. passthrough step), for each table to be visible from the Segment Editor, select the checkbox in the Show in VSE? column.

   Note

   Tables that do not contain an Amperity ID may not be available to the Visual Segment Editor.

3. Click Activate.

Mark columns as PII

Columns that contain PII—names (first, last, and full), addresses, phone numbers, email addresses, birthdates, gender, and other profile details—should be marked for all database tables.

What is restricted PII access?

Users of Amperity who are assigned the AmpIQ User and/or Amp360 User policies along with the Restrict PII access policy option are prevented from viewing PII when that data was marked as PII in the database table.

When the PII checkbox is selected, the following locations will not show PII values to users who do not have permission to view PII data:

• The Examples page in the Data Explorer.

• Data that is returned by a query in the Queries page.

• Data that is returned by a segment in the Segments page.

When PII data is hidden, users are shown the following message:

Some personally identifiable information has been redacted from these results.

To mark columns as PII

1. Open a table in the database editor.

2. Click Next. In the second step, for each field that contains PII, verify that the box in the PII column is selected.

   Amperity automatically selects the PII checkbox for columns that were tagged with profile (PII) semantics. Verify that all columns associated with PII have the checkbox selected in the PII column.

3. Activate the table. Restrictted access to PII will be updated the next time this database table is refreshed.

Pick-lists

A pick-list is a pre-generated list of available options for a column and is presented as a drop-down list in the visual Query Editor. A pick-list is configured when building tables in the Customer 360 page.

• Add pick-list, sorted by alphabetical

• Add pick-list, sorted by most frequent

• Add pick-list, sorted by natural

• Delete pick-list

• Edit pick-list

Add pick-list, sorted by alphabetical

A alphabetical sort order is best for strings, such as for columns with values for cities, states, and proper names. For example:

• 1, 11, 2, and 3

• a, b, and c

To add a pick-list with an alphabetical sort order

1. Open a table in the database editor.

2. In the second step (after the SQL vs. passthrough step), select the checkbox in the Pick-list column in the row for which a picklist is to be created.

3. Click the Setup link next to the checkbox. This opens the Configure Pick-List dialog box.

4. Select the Alphabetical sort order.

5. Select the method with which pick-list values are applied. In nearly all situations, you should use pick-list values that are generated Automatically.

6. Click Save.

Add pick-list, sorted by most frequent

A most frequent sort order is best for columns with a smaller selection of frequently selected items, such as for columns with values for loyalty tiers, acquisition channels, or shopping preferences.

Note

Most frequent is the default sorting option for pick-lists.

To add a pick-list with a most frequent sort order

1. Open a table in the database editor.

2. In the second step (after the SQL vs. passthrough step), select the checkbox in the Pick-list column in the row for which a picklist is to be created.

3. Click the Setup link next to the checkbox. This opens the Configure Pick-List dialog box.

4. Select the Most Frequent sort order.

5. Select the method with which pick-list values are applied. In nearly all situations, you should use pick-list values that are generated Automatically.

6. Click Save.

Add pick-list, sorted by natural

A natural sort order is best for alphanumeric lists, such as for columns with scores and rankings. For example:

• 1, 2, 3, and 11

• a, b, and c

To add a pick-list with a natural sort order

1. Open a table in the database editor.

2. In the second step (after the SQL vs. passthrough step), select the checkbox in the Pick-list column in the row for which a picklist is to be created.

3. Click the Setup link next to the checkbox. This opens the Configure Pick-List dialog box.

4. Select the Natural sort order.

5. Select the method with which pick-list values are applied. In nearly all situations, you should use pick-list values that are generated Automatically.

6. Click Save.

Define custom values and frequencies

A pick-list may be configured with custom values, where each item in the pick-list is specified on its own line.

Caution

Using custom values in a pick-list is uncommon. It is generally best to use automatically generated values.

To define custom values for a pick-list

1. Open a table in the database editor.

2. In the second step (after the SQL vs. passthrough step), select the checkbox in the Pick-list column in the row for which a picklist is to be created.

3. Click the Setup link next to the checkbox. This opens the Configure Pick-List dialog box.

4. Select a sort order.

5. Select the Custom method for the pick-list, and then enter each custom value on a new line. For example:

   Value one
   Value two
   Value three
   ...
   

6. Click Save.

Delete pick-list

A pick-list may be deleted. This will disable any selection options in the visual Query Editor.

To delete a pick-list

1. Open a table in the database editor.

2. In the second step (after the SQL vs. passthrough step), de-select the checkbox in the Pick-list column in the row for which a picklist is to be deleted.

3. Click Save.

Edit pick-list

Many pick-lists remain static over time, but if the data inputs change, you may reconfigure the items in a pick-list.

To edit a pick-list

1. Open a table in the database editor.

2. In the second step (after the SQL vs. passthrough step), click the Setup link next to the checkbox. This opens the Configure Pick-List dialog box.

3. Make your changes.

4. Click Save.

Set validation rules

A field in a database table may be flagged as required, as unique, or as both required and unique. These flags are validated by Amperity. When the validation conditions are not met a warning is raised. These flags should be used for specific types of fields in a table to help sure that data within Amperity remains healthy and to ensure that downstream workflows are built on top of the correct data. Database field semantics are preceded by a db/ in the drop-down menu for semantics in the Database Editor.

Warning

Validation warnings appear in the Notifications pane as part of the notification for a database update. Each validation warning specifies the table name and the field name that failed validation.

To set validation rules

1. From the Customer 360 page, under All Databases, select the menu for a database, and then click Edit. This opens the Database Editor page.

2. Click Add Table. This opens the Database Table page.

3. Set Build Mode to “passthrough” or “SQL”, and then (for SQL tables only) add the SQL for the table. Click Next.

4. Select the combination of validation rules for each field.

   For a field that is unique, apply the db/unique semantic tag.

   For a field that is required, apply the db/required semantic tag.

   Tip

   Some fields, such as amperity_id, are both unique and required. Apply both database semantic tags to these fields. Most fields do not require any validation rules.

5. Click Save.

Unique

A field that is assigned the unique semantic requires every value for that field within the same table to be unique. Fields with NULL values are ignored by validation, but all other values, including zero-length strings, must pass.

Required

A field that is assigned the required semantic requires every value for that field within the same table to have a non-NULL value, but does not require values to be unique. NULL values will cause an error during validation. All other values, including zero-length strings, will pass validation.

Both

A field may be assigned the required and unique semantics. Use this only for fields that must be present and unique, such as for the Amperity ID.

Delete database

A database may be deleted. This should not be done without considering the upstream and downstream effects of those changes. If the database is required by a downstream process, that process will fail. If an upstream process cannot find the database, that process will fail.

To delete a database

1. From the Customer 360 page, under All Databases, open the menu for the database you want to edit, select Delete.

2. Click Delete to confirm.

Delete table

A table in a database may be deleted. This should not be done without considering the upstream and downstream effects of those changes. If the table is required by a downstream process, such as a query, the process will fail. If an upstream process cannot find the table or the schema does not match, that process will fail.

To delete a table

1. From the Customer 360 page, under All Databases, from the menu for the database you want to view, select View. The Database Editor page opens.

2. Under Database Tables, from the menu for the table you want to edit, select Delete.

3. Click Delete to confirm.

Discard drafts

Any database or table that has not been activated may be discarded.

To discard a draft

From the Customer 360 page, under All Databases, open the menu for a database with a “Draft” label, and then select Discard.

Warning

Discarding draft databases does not open a confirmation dialog box. Discarded draft databases are removed immediately.

Download schema

You can download the schema for your customer 360 database. This download is configurable, but may contain all of the tables in your database. For each included table, a description of each column in that table is also provided (when that description is defined within Amperity), dependencies that may exist for each table, along with examples of the data in each column.

To download the database schema

1. From the Customer 360 page, open the Data Explorer, and then click Download schema. This opens the Download schema dialog.

2. From the Download schema dialog, select the tables you want to include in the downloaded schema, and then click Download.

3. An XSLX file is downloaded to your local machine’s “Downloads” directory. You may open this up in any application that supports that file type.

Edit database

A database may be edited. This should not be done without considering the upstream and downstream effects of those changes. If the database is required by a downstream process, that process will fail. If an upstream process cannot find the database, that process will fail.

To edit a database

1. From the Customer 360 page, under All Databases, open the menu for the database you want to edit, and then select Edit. The Database Editor page opens.

2. Make your changes.

3. Click Activate.

Edit table

Tables in a database may be edited. This should not be done without considering the upstream and downstream effects of those changes. Changes may have unintended consequences: semantics, column names, pick-lists, and so on may not be immediately available to a downstream process, which may cause that downstream process to fail. If an upstream process cannot find the table or the schema does not match, that process will fail.

To edit a database table

1. From the Customer 360 page, under All Databases, open the menu for the database you want to view, select View. The Database Editor page opens.

2. Under Database Tables, open the menu for the table you want to edit, select Edit. The Database Table page opens.

3. Make your changes.

4. Click Next. This opens the Database Table page.

5. Make additional changes.

6. Click Save.

Enforce static schemas

To prevent tables from being updated automatically you can enforce a static schema for the database table. When enabled, a user cannot save, activate, or run a database table when there is inconsistency between the database table’s current schema and its upstream dependency.

For example, a custom domain table with three fields is used to build a custom database table using SELECT * FROM Custom_Domain_Table. With this pattern, if the custom domain table is updated to have four fields the custom database table will also be updated.

To resolve this inconsistency, do one of the following:

1. Disable static schema enforcement for the custom database table.

2. Update the schema in the custom database table to match the updated schema in the custom domain table.

3. Update the schema in the custom domain table to match the schema required the custom database table.

To enforce static schemas

1. From the Database Table editor, under Advanced Settings, expand Table Schema.

2. Select Enforce static schema.

   Note

   A table’s schema cannot be changed when this option is enabled.

3. Click Save.

Explore customer profile

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.

Each customer profile is a collection of common attributes (first name, last name, email, phone, etc.), transaction attributes (first purchase, last purchase, total purchases, etc.), and other custom values that are unique to each customer’s data set. These details are summarized on the Customer 360 page under Customer Profile.

• Add query from profile

• Open Data Explorer

• View interactions

• View other attributes

• View profile attributes

Add query from profile

Use the visual Query Editor to define queries using a combination of filters for WHERE and AND clauses, along with drop-down menus and pick-lists that build SQL queries against the database. The underlying queries use Presto SQL syntax.

To add a visual query from the customer profile

1. From the Customer 360 page, under Customer Profile click Create Query. This opens the visual Query Editor.

2. Define the query using drop-down menus and pick-lists.

3. Click Run Query and debug any issues that may arise.

4. Click Activate.

Open Data Explorer

Use the Explore option to open the Data Explorer.

To open the Data Explorer

1. From the Customer 360 page, under All Databases, select a database to explore, and then click Explore. This opens the Data Explorer page.

2. Browse the table schema, examples, and details.

3. When finished, click Close.

View interactions

A customer interaction is represented by one (or more) tables in the customer 360 database that are available for segmentation via the visual Segment Editor, in addition to the Customer 360 table. Every customer interactions table has an Amperity ID column and often contains stitched output that was based on non-PII semantic tagging, such as transactions, behaviors, and so on.

Interactions are visible from the Customer 360 page under Customer Interactions.

View other attributes

Other attributes are visible from the Customer 360 page under Customer Profile.

View profile attributes

Personally identifiable information (PII) is any data that could potentially identify a specific individual. PII data includes details like names, addresses, email addresses, and other profile attributes, but can also include attributes like a loyalty number, customer relationship management (CRM) system identifiers, and foreign keys in customer data.

PII profile attributes commonly available as part of the customer profile include:

Icon	Attribute Name	Description
	First Name	The first name that is associated with an individual customer record.
	Last Name	The last name that is associated with an individual customer record.
	Email	The email address that is associated with an individual customer record. A customer record may be associated with multiple email addresses.
	Phone	The phone number that is associated with an individual customer record. A customer record may be associated with multiple phone numbers.
	Address	The address that is associated with the location of an individual customer record. For example: 123 Main Street.
	City	The city that is associated with the location of an individual customer record.
	State	The state or province that is associated with the location of an individual customer record.
	Zip Code	The zip code or postal code that is associated with the location of an individual customer record.
	Birthdate	The date of birth that is associated with an individual customer record.
	Gender	The gender that is associated with an individual customer record.

Note

The profile attribute names that are shown in the profile attributes section are the same strings that were entered as friendly names when creating the Customer 360 database table. These strings will default to the same as the field name if not specified as friendly names, including lower-case strings and underscores. The list of profile attributes will depend on the SQL query used to create the Merged Customers table.

Profile attributes are visible from the Customer 360 page under Customer Profile.

Explore database

The Data Explorer provides a detailed way to navigate through data tables in Amperity. The Data Explorer displays each column in the data table as a row, with the column name, data type, associated semantic, and a data example. A sample of real table data is available available on another tab.

A full-screen mode for the data explorer is available from most areas within Amperity that show data tables in the database. This mode enables detailed exploration of each table, including an overview, the data table schema, examples of data, and source table details.

• Search Data Explorer

• View details

• View example data

• View table schema

Search Data Explorer

You can search for databases and columns in the database from the search bar in the Data Explorer.

To search domain tables

1. From the Customer 360 page, open the Data Explorer.

2. Enter the search term.

   The search results are filtered automatically, grouped by database table, then by column name, and then by database property.

3. Select a table name to open the data explorer for that table.

View details

The Details view in the Data Explorer shows summary data about the table, along with information about which source tables were used to build it.

To view database details

1. From the Customer 360 page, under All Databases, select a database to explore, and then click Explore. This opens the Data Explorer page.

2. Click the name of a table.

3. Click the Details tab at the top of the table.

4. When finished browsing the table details, click Close.

View example data

The Examples view in the Data Explorer shows actual data for a randomly selected set of rows in the data table.

Note

Users with restricted access to PII will not see data for PII-restricted columns, but will see data for all other columns.

Cardinality vs. uniqueness

Cardinality is a measure of how many unique values are present in data. A higher cardinality indicates a larger percentage of unique values, whereas a lower cardinality indicates a higher percentage of repeat values.

Uniqueness divides the number of unique values – cardinality – by the number of rows in a table.

Use cardinality and uniqueness to help guide the creation of well-behaved JOIN operations when authoring SQL queries.

• Avoid using JOIN operations when columns have lower cardinality. The high frequency of duplicate values will result in a row for every possible match.

• Columns with low uniqueness values as keys on both sides of a JOIN operation will run more slowly and is less likely to return the desired results.

• Empty fields (i.e. NULL values) are counted as duplicates, i.e. “not unique”. For example: a field with 90% completion and 90% uniqueness has different values for each of the non-empty rows.

Completion percentages

Completion is a simple percentage of data rows that contain some value within a column.

To view example data

1. From the Customer 360 page, under All Databases, select a database to explore, and then click Explore. This opens the Data Explorer page.

2. Click the name of a table.

3. Click the Examples tab at the top of the table.

4. When finished browsing example data, click Close.

View table schema

The Schemas view in the Data Explorer displays information about each column in the table, along with an example, and information about completion, uniqueness, and cardinality.

To view database table schema

1. From the Customer 360 page, under All Databases, select a database to explore, and then click Explore. This opens the Data Explorer page.

2. Click the name of a table.

3. Click the Schema tab at the top of the table.

4. When finished browsing the table schema, click Close.

Export databases and tables

A database may be configured to export one (or more) tables (or even the entire database) from Amperity. Each database export must be assigned a unique name, and then is configured to select one (or more) tables to be included in the export. A database export must be associated with a configured destination and must be added to an orchestration.

Profile icons

Icons are visible under Customer Profile from the Customer 360 page. Use profile icons to assign a visual element to important fields in the customer profile. Up to 10 profile icons may be visible, along with up to 29 behavioral attributes may be visible.

Tip

Amperity will assign icons to PII fields automatically for the table that is assigned the Customer 360 table semantic in the database.

You may use any icon in the Font Awesome library.

Icon identifiers start with fa-, and then a unique string. For example: fa-home (for   ), fa-star (for   ), and fa-project-diagram for (  ).

Icons are available in four styles: regular (far), solid (fas), light (fal), and duotone (fad).

Use a style prefix in front of an icon identifier to apply that style. For example: fad fa-home for a duotone icon or fas fa-home for a solid icon.

Note

Fields in the customer profile are sorted in the following groups:

1. Starred

2. With icon

3. Without icon

Apply customer profile icons

Profile icons are applied automatically, but you may configure them when building the database.

To apply icons to a field

1. Open the Customer 360 table in the database editor.

2. Click Next.

3. Click the icon or empty space in the Icon column to open the Select an Icon dialog box, after which you can choose an icon or search for additional icons on the Font Awesome website.

   You may use any icon in the Font Awesome library.

   Icon identifiers start with fa-, and then a unique string. For example: fa-home (for   ), fa-star (for   ), and fa-project-diagram for (  ).

   Icons are available in four styles: regular (far), solid (fas), light (fal), and duotone (fad).

   Use a style prefix in front of an icon identifier to apply that style. For example: fad fa-home for a duotone icon or fas fa-home for a solid icon.

4. Click Refresh Semantics to apply the a default icon to all profile attributes.

5. Click Save.

Refresh customer profile icons

Refresh the profile icons for the Customer 360 table any time the fields are modified before activating the table.

To refresh profile icons

1. Open the Customer 360 table in the database editor.

2. In the second step (after the SQL vs. passthrough step), under Semantic Suggestions, click Refresh Semantics.

3. Verify the profile icons for all refreshed fields. Verify the PII column status for any field that is associated with PII.

4. Click Save.

Run databases

A database may be run from the Customer 360 page.

• Run database

• Run with express refresh

• Run with full refresh

• Run with normal refresh

• Run tables sequentially (Advanced)

• Stop

Run database

There are two locations in Amperity from which you can manually run the database that is configured to be the database:

1. Click the Run link at the top of the Customer 360 page.

2. From the database menu under All Databases for the database, select Run.

Run with express refresh

An express refresh will only update database tables and custom domain tables with new domain data or table definitions. This is the fastest option and is the recommended option for testing incremental changes.

Note

When a database is run, any custom domain table that has changed is run first, and then Stitch will run if any of those custom domain tables are configured for Stitch. If there are no changes to custom domain tables or if custom domain tables have changed that are not configured for Stitch, Stitch will not run.

To run a database with express refresh

1. From the Customer 360 page, under All Databases, open the menu for the database you want to run.

2. Select Run. The Run <Database> dialog box opens.

3. Under Run Options, select Express.

4. Under Databases to Run, select one (or more) databases to be run.

5. Click Run.

Run with full refresh

A full refresh will update all tables, including custom domain tables. This is the slowest option and is best used for estimating runtime performance.

Note

When a database is run, any custom domain table that has changed is run first, and then Stitch will run if any of those custom domain tables are configured for Stitch. If there are no changes to custom domain tables or if custom domain tables have changed that are not configured for Stitch, Stitch will not run.

To run a database with full refresh

1. From the Customer 360 page, under All Databases, open the menu for the database you want to run.

2. Select Run. The Run <Database> dialog box opens.

3. Under Run Options, select Full Refresh.

4. Under Databases to Run, select one (or more) databases to be run.

5. Click Run.

Run with normal refresh

Any table with new data, new table definitions, or time-dependent clauses will be refreshed. A normal refresh is the default option.

Note

When a database is run, any custom domain table that has changed is run first, and then Stitch will run if any of those custom domain tables are configured for Stitch. If there are no changes to custom domain tables or if custom domain tables have changed that are not configured for Stitch, Stitch will not run.

Time-dependent clauses include any query that uses the CURRENT_DATE(), CURRENT_TIMESTAMP(), UNIX_TIMESTAMP(), or RAND() functions.

To run a database with normal refresh

1. From the Customer 360 page, under All Databases, open the menu for the database you want to run.

2. Select Run. The Run <Database> dialog box opens.

3. Under Run Options, select Normal.

4. Under Databases to Run, select one (or more) databases to be run.

5. Click Run.

Run tables sequentially (Advanced)

Run tables sequentially in the selected databases, rather than in parallel, to help identify tables that may be causing issues in your database.

Note

When a database is run, the system computes the tables in parallel by default. If you choose the option to run tables sequentially, this may increase the duration of the database run and should be used for debugging purposes only.

Run tables sequentially in the selected databases to do the following:

• Identify a specific table that is causing issues in your database.

• Optimize resource capacity.

• Review individual table run times to identify issues.

Note

When you run a database in this mode, the duration of the database run may increase substantially.

To run tables sequentially

1. From the Customer 360 page, under All Databases, open the menu for the database you want to run.

2. Select Run. The Run <Database> dialog box opens.

3. At the bottom of the dialog box, expand the Advanced Options section.

4. Select the Disable parallel table execution checkbox to apply this setting to the current database run.

5. Click Run.

Stop

You can stop a database that is running in the Customer 360 page.

To stop running a database

1. From the Customer 360 page, under All Databases, open the menu for the database you want to stop, select Abort.

2. The database run will be stopped.

View all databases

In addition to the database, the Customer 360 page may contain additional databases. For example, Amperity solutions engineers always build a database that exists in-parallel with the database called Stitch QA that is used to monitor the quality of Stitch runs for that customer.

The All Databases section of the Customer 360 page lists all of the databases that have been created, including both active and draft states.

Other databases may be created to support any desired use case. Each database has a specific schema (i.e. collection of tables and attributes). This is called a data model. The data model and individual database tables Amperity provides should make data easy to understand and explore for its specific use case and client.

Note

Only databases in an Active state are run against stitched data.

View database

From the Customer 360 page, under All Databases, open the menu for the database you want to view, select View. The Database Viewer page opens.

View notifications

Notifications for databases that run in the Customer 360 page appear after Amperity has processed data for that database. Notifications typically indicate successful outcomes. Less often, notifications contain details for non-successful outcomes, such as failures related to upstream or downstream processes.

If a notification is about a non-successful outcome, the details for why and what happened can be found in the notification itself. Click More to view the full notification. Click View Workflow to open the workflow in the Workflows page.

In some cases viewing the log files may be helpful. In many cases, fix the root cause of the non-successful outcome, and then rerun the process manually. The Workflows page will provide a set of workflow actions that you can initiate directly.

View run history

You can view the run history for a database on the Customer 360 page.

To view the run history of a database

1. From the Customer 360 page, in the Databases section, select the    icon.

2. From the list of options select View run history.

The Run History window appears with details about the run history of the database.

Tip

You can view the workflow information associated with the database by clicking on View Workflow on the top-right corner of the Run History window.

View statistics

The Customer 360 page shows the date on which the database was last updated, how long it took to complete the update, and the number of customer profiles in the database.