Filtered 360 databases

For tenants with more than one marketing unit, such as companies that have multiple brands and/or geographic regions, Amperity offers the ability to perform identity resolution across all marketing units, but then filter the data for each specific marketing unit into its own database.

This approach ensures that:

  • Unique customer profiles exist across all marketing units

  • Each marketing unit has its own set of data that is separated from any other marketing unit

  • Users of Amperity only have access to the databases to which they have been given access

  • Predictive modeling may be calculated for each marketing unit-specific database

Requirements

To enable filtering customer 360 databases by marketing unit you must do the following:

  1. All domain tables to which semantic tags are applied must use the database-key semantic tag to specify which field in that domain table identifies the marketing unit.

  2. Use custom core tables to define shared functionality that should be available to each filtered 360 database.

  3. Each database must use a Database_Filter table to identify a marketing unit.

  4. SEMI JOIN the Database_Filter table to each table in the filtered 360 database that contains data about marketing units.

    This step ensures that data is filtered to return only data that is associated with the marketing unit that is defined in the Database_Filter table.

database-key semantic tag

All domain tables that contain marketing unit-specific data must have a field in that table that can be used to identify the marketing unit to which values in that table belong.

This field should be assigned the database-key semantic tag.

Note

Domain tables that contain data for a single marketing unit do not require the database-key semantic tag; however, for consistency across all data sources should apply the semantic tag to help prevent situations where the name of a domain table does not clearly identify the name of the marketing unit.

If the semantic tag grouping has a prefix, include the prefix in the semantic tag. For example, use the txn-item/database-key semantic tag to add the values of that field to the database_key column in the Unified Itemized Transactions table.

Important

Values associated with database-key should be consistent across data sources. For example, to filter by country the value for Japan should be one of “JP” or “Japan” and not a mix of both values.

Use custom domain tables to standardize these values across data sources, if necessary.

Custom core tables

A custom core table is built using Spark SQL and may reference one (or more) core tables and/or domain tables. Use custom core tables to extend the normalized foundation to support additional use cases beyond what the set of standard core tables provides.

Sharing tables across filtered 360 databases

Custom core tables are reusable across filtered 360 databases. Core tables store SQL logic that can be referenced by multiple databases, but can be adapted to provide database-specific logic as well.

For example, the Customer Attributes table typically requires customization to align features within that table to how your brand defines churn prevention status indicators, historical purchase lifecycles, and customer types. This table can be extended to support custom features, and each marketing unit may have different requirements.

If each brand has unique requirements, then each filtered 360 database may require its own customized version of the Customer Attributes table, with each maintained separately.

Another option is to extend the custom core table to support database-specific logic, such as by using a CASE statement to enable different thresholds for considering customers “lapsed” by marketing unit. For example:

CASE
  WHEN database_key = 'ACME Essentials' THEN 730
  WHEN database_key = 'Socktown' THEN 365
END AS lapsed_threshold

Each filtered 360 database can then reference the custom core table:

SELECT *
FROM Customer_Attributes_Core ca
SEMI JOIN Database_Filter df
ON ca.database_key = df.database_key

When the Customer Attributes core table is updated, those updates will be applied to each filtered 360 database automatically.

Database_Filter table

Each filtered database must contain a Database_Filter table, which contains a single record with a hard-coded string value that defines a specific brand or geographic region.

For example, the Database_Filter table in a region-specific database for Japan would use SQL similar to:

SELECT 'JP' AS database_key

Filter tables by marketing unit

For each table in the filtered 360 database that contains data the database_key column you must use a SEMI JOIN to ensure data within that table is filtered to match only the value defined in the Database_Filter table.

For example, to filter the Unified Coalesced table use SQL similar to:

SELECT *
FROM Unified_Coalesced uc
SEMI JOIN Database_Filter df
ON uc.database_key = df.database_key