Enable Predicted Models

Predicted models are a feature of AmpIQ that predicts customer behavior, such as predicted customer lifetime value (predicted CLV), churn propensity, product affinity, and lifecycle events.

Each predicted model outputs a collection of predicted attributes. A predicted attribute is represented by a column in a table that can be used during segmentation. Use predicted attributes to define segments that filter customers based on predicted behavior.

Caution

Predicted models are an optional component of AmpIQ. If you plan to enable predicted models for AmpIQ it is recommended to review the data requirements, and then take steps to configure columns and tables for predicted models at the same time as AmpIQ.

Predicted models workflow

Predicted models are built outside of Amperity and require data from certain tables in the customer 360 database to be made available to it on a daily basis, after which the results are then pulled to Amperity and the related data science tables are updated. The steps in the predicted models workflow include:

  1. The Merged_Customers, Unified_Transactions, and Unified_Itemized_Transactions tables are exported on a daily basis from Amperity to an Amazon S3 bucket that is used for predicted models.

  2. Predicted models are run against the Merged_Customers, Unified_Transactions, and Unified_Itemized_Transactions tables and the results are returned to the Amazon S3 bucket.

  3. A set of couriers are configured to pull these results from the Amazon S3 bucket to your tenant on a daily basis.

  4. Predicted models tables are loaded to Amperity using feeds, after which the associated predicted models domain tables are refreshed.

  5. After the Stitch process is complete, refreshed predicted models tables are passed through to your customer 360 database.

  6. Queries and segments that rely on predicted models table attributes will use the refreshed data the next time they are run and any new queries and segments will have access to the refreshed data.

  7. A campaign will use refreshed data the next time it is scheduled to run.

Predicted models components

What components of predicted models are visible within Amperity?

Tab

Description

Sources

Feeds that define the data model for predictive modeling tables within Amperity:

  • PCLV

  • PCLV - Changes

  • Predicted_Affinity_ProductAttribute

  • Predicted_Affinity_Audience_ProductAttribute

Domain tables that are refreshed on a daily basis:

  • Predicted_Pclv

  • Predicted_PclvChanges

  • Predicted_Affinity_ProductAttribute

  • Predicted_Affinity_Audience_ProductAttribute.

Couriers to pull the results of predictive modeling to Amperity on a daily basis:

  • PCLV (including PCLV Changes and PCLV Metadata)

  • PCLV - Validation

  • Predicted Affinity

  • Predicted Affinity Audience

Customer 360

A series of passthrough database tables:

  • PclvValidation

  • Predicted_Affinity_ProductAttribute

  • Predicted_Affinity_Audience_ProductAttribute

Queries

and

Segments

Access to predicted models attributes from the following tables: Predicted pCLV Attributes and Affinity (for recommended audiences). Updated attributes are applied automatically the next time you run the segment or query.

Destinations

A series of orchestrations that use database exports to send the following tables to predicted models on a daily basis:

  • Merged_Customers

  • Unified_Itemized_Transactions

  • Unified_Transactions

Verify prerequisites

Predicted models have the same prerequisites as AmpIQ.

Data requirements

Predicted models have the same requirements as AmpIQ, along with the following additional requirements:

  1. Data sources must provide at least four years of historical data to support predicted models; five (or more) years is recommended. Providing as much historical data as possible improves model performance and prediction quality.

  2. A defined product catalog with 20-2000 distinct values is required to support recommended audience sizes for product affinity.

    Important

    SKU-level recommendations are not supported.

  3. If required fields are not available in customer data, maintain the column on the table with NULL values.

The following sections describe requirements for columns and tables, and fields.

Columns and tables

Predicted models use specific columns across the following tables:

  • Merged_Customers

  • Transaction_Attributes

  • Unified_Customer

  • Unified_Itemized_Transactions

  • Unified_Transactions

The Unified_Customer table is built as part of Stitch output when data sources with customer records have profile (PII) semantic tags applied.

The Transaction_Attributes, Unified_Itemized_Transactions, and Unified_Transactions are built as part of Stitch output when data sources with interactions records have transactions and itemized transactions semantic tags applied.

The Merged_Customers table is a version of the Unified_Customer table that you must build in the customer 360 database to filter profile data. The Merged_Customers table must be updated to roll-up transactions and itemized transactions.

These tables contain fields that are important components of predicted models.

Fields

The following columns are required by predicted models and must exist in the following tables as an output of the Stitch process or be part of the configuration of the customer 360 database:

Column Name

Required for …

Description

amperity_id

Predicted models

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.

AmpIQ expects this column to be available from the following tables in the customer 360 database:

  • Merged_Customers

  • Unified_Customer

  • Unified_Itemized_Transactions

  • Unified_Transactions

  • Transaction_Attributes

birthdate

Predicted models

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

AmpIQ expects this column to be available from the following tables in the customer 360 database:

  • Merged_Customers

  • Unified_Customer

city

Predicted models

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

AmpIQ expects this column to be available from the following tables in the customer 360 database:

  • Merged_Customers

  • Unified_Customer

country

Predicted models

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

AmpIQ expects this column to be available from the following tables in the customer 360 database:

  • Merged_Customers

  • Unified_Customer

digital_channel

Predicted models

The digital channel by which a transaction was made. For example: Facebook, Google Ads, email, etc.

AmpIQ expects this column to be available from the following tables in the customer 360 database:

  • Unified_Itemized_Transactions

  • Unified_Transactions

email

Predicted models

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

AmpIQ expects this column to be available from the following table in the customer 360 database:

  • Merged_Customers

gender

Predicted models

The gender that is associated with an individual customer record.

AmpIQ expects this column to be available from the following tables in the customer 360 database:

  • Merged_Customers

  • Unified_Customer

given_name

Predicted models

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

AmpIQ expects this column to be available from the following tables in the customer 360 database:

  • Merged_Customers

  • Unified_Customer

order_id

Predicted models

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.

AmpIQ expects this column to be available from the following tables in the customer 360 database:

  • Unified_Itemized_Transactions

  • Unified_Transactions

order_datetime

Predicted models

Order datetime is the date (and time) on which an order was placed.

AmpIQ expects this column to be available from the following tables in the customer 360 database:

  • Unified_Itemized_Transactions

  • Unified_Transactions

order_revenue

Predicted models

The total amount of revenue for all items in a transaction after discounts are applied, ignoring returns and/or cancellations.

AmpIQ expects this column to be available from the following table in the customer 360 database:

  • Unified_Transactions

postal

Predicted models

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

AmpIQ expects this column to be available from the following tables in the customer 360 database:

  • Merged_Customers

  • Unified_Customer

purchase_brand

Predicted models

The brand associated with a customer interaction.

AmpIQ expects this column to be available from the following table in the customer 360 database:

  • Unified_Itemized_Transactions

  • Unified_Transactions

purchase_channel

Predicted models

A channel is the purchase method used by a customer. For example: online, point-of-sale, loyalty, in-store, and so on.

AmpIQ expects this column to be available from the following table in the customer 360 database:

  • Unified_Itemized_Transactions

  • Unified_Transactions

state

Predicted models

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

AmpIQ expects this column to be available from the following tables in the customer 360 database:

  • Merged_Customers

  • Unified_Customer

surname

Predicted models

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

AmpIQ expects this column to be available from the following table in the customer 360 database:

  • Merged_Customers

Request to enable

After you have configured Amperity for the recommended patterns and workflows for data sources that contain interactions records, AmpIQ is enabled, and all verification steps have been completed, contact your Amperity representative and request to enable predicted models.

Important

By default, predicted models are fit across all brands for a tenant, even if a tenant has multiple brands. To fit predictions for each brand:

  1. A customer must purchase predicted models for each individual brand.

  2. The tenant must be configured to support brand-specific databases, with a database for each brand with dedicated Merged_Customers, Unified_Itemized_Transactions, and Unified_Transactions tables.

  3. A taxonomy that defines product categories and brands.

    This taxonomy must be defined in the Customer 360 database by extending the Unified_Itemized_Transactions table to include all of the columns that define the taxonomy.

The typical process for setting up predicted models follows a series of general steps:

  1. (~1 week) Ensure that data is made available to Amperity that meets the semantic tagging requirements for AmpIQ (along with any predicted-models-specific fields), including for transactions, itemized transactions, and product catalog. Use domain SQL to perform pre-Stitch data transforms as necessary.

    Verify that data is being processed correctly to the following tables in the customer 360 database: Unified_Product_Catalog, Unified_Transactions, Unified_Itemized_Transactions, Transaction_Attributes, and Transaction_Attributes_Extended.

  2. (~1 week) Amperity begins the process of training the models, validating backtesting, and optimizing predictions.

  3. (~1-2 weeks) Amperity performs stability testing on the models and the predicted results. This step may require some tuning of the model and will take longer for very large data sets.

Once this process is complete, the predicted models attributes are available on a 1-day delayed rolling basis. These tables are added to your tenant as a feed in the Sources tab and are availble for use with segments and queries.

Tip

Ask in the #iq-office-hours Slack channel for specific details around the timing for predicted model enablement for your tenant. If you do not have access to that channel, you may ask your Amperity representative to ask on your behalf.

Configure predicted tables

A series of predicted tables is added to your tenant under a feed in the Sources tab named Predicted. This is done as part of the enablement process for predicted models within AmpIQ. These tables are refreshed automatically on a daily basis, are not processed by Stitch, and are made available to your customer 360 database, the Queries tab, and the Segments tab as passthrough tables:

  • Predicted_Affinity_ProductAttribute

  • Predicted_Pclv

  • Predicted_PclvChanges

  • Predicted_Affinity_Audience_ProductAttribute

where ProductCategory is a string that best represents the unique products and brands within your tenant.

  1. The Predicted_Pclv series of tables provides data to the Predicted_CLV_Attrbutes table, which represents the current view of your customer and transaction data.

  2. The Predicted_Affinity_ProductAttribute and Predicted_Affinity_Audience_ProductAttribute tables are used for product affinity and are associated with the Affinity table.

Make the following updates in your customer 360 database:

Add Affinity table

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.

To add a Predicted_Affinity table you must extend the customer 360 database to add a table that joins the Predicted_Affinity_ProductAttribute passthrough table to the Predicted_Affinity_Audience_ProductAttribute passthrough table.

To add the Affinity table

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

  2. From the Database Editor, click Add Table.

  3. Name the table “Predicted Affinity” (or some other name that clearly identifies this table as the product affinity table for your tenant).

  4. Set the build mode to SQL.

  5. Add the following SQL:

    SELECT
      r.product_attribute AS `product_sub_category`
      ,r.amperity_id
      ,r.score
      ,r.ranking
      ,r.ranking <= s.audience_size_small AS audience_size_small
      ,r.ranking <= s.audience_size_medium AS audience_size_medium
      ,r.ranking <= s.audience_size_large AS audience_size_large
    FROM Predicted_Affinity_ProductAttribute AS r
    LEFT JOIN Predicted_Affinity_Audience_ProductAttribute AS s
    ON r.product_attribute = s.product_attribute
    
  6. Click Validate to verify the SQL runs without error.

  7. Click Next. This opens the Database Table Definition page.

  8. Add a table description. This enables a tooltip that is visible from other areas in Amperity.

  9. Verify that the db/required and db/unique database field semantics were applied to the amperity_id column.

  10. Under Version History, select Enable table version history.

  11. Click Save.

Add Lifecycle_Events table

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.

Add pCLV attributes table

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

Add the Predicted_CLV_Attributes table to the customer 360 database.

To add the Predicted_CLV_Attributes table

  1. From the Customer 360 tab, 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 the Predicted_CLV_Attributes table from the drop-down menu.

  4. Hide the table from the Visual Segment Editor by verifying that Show in VSE? is unselected.

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

Define product catalog

A product catalog must be defined, and then joined to the Unified_Itemized_Transactions table.

  1. A data source must be available with product catalog semantic tags applied.

    Note

    Amperity documentation refers to this data source as the Unified_Product_Catalog table, but it may have a different name in your tenant.

  2. Apply the product-id semantic tag to product catalog data as required for AmpIQ predictive modeling; the product-category, product-description, and product-subcategory semantic tags are optional.

    Important

    Product affinity modeling requires the field that defines product categories – product-category in the Unified_Product_Catelog table – to contain between 20 and 2000 unique values.

  3. A list of custom attributes may be defined. These must be available as columns in the Customer 360 database and must be added to the Unified_Itemized_Transactions table.

  4. Tables that define product catalog semantic tags and custom attributes are managed as a data source (and associated feed) and should be maintained as part of your organization’s regular Amperity workflow.

Extend the Unified_Itemized_Transactions to include the taxonomy that defines your products and brands:

  1. Add the column to which the product-id semantic tag was applied

  2. Add other columns to which product catalog semantic tags were applied

  3. Add any columns that define custom attributes, as necessary

The following example extends the Unified_Itemized_Transactions table using a table named Unified_Product_Catalog that has columns for each of the optional product catalog semantic tags:

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

Some brands require a single parameter to define a taxonomy and some require more than one. The following example shows a taxonomy that defines custom attributes for a class and subclass:

SELECT
  uit.*,
  ,product_class AS `product_class`
  ,product_subclass AS `product_subclass`
FROM Unified_Itemized_Transactions uit
LEFT JOIN Product_Metadata pm ON uit.product_id = pm.product_id
WHERE uit.is_return IS NULL AND uit.is_cancellation IS NULL
AND uit.item_revenue >= 0

Important

Every brand is unique. Discuss with your Amperity representative how to extend the Unified_Itemized_Transactions table to support the taxonomy that is required for your products and brands.

Add pCLV attributes to Customer360 table

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.

You must edit the SELECT statement in the Customer_360 table to select attributes from the Predicted_CLV_Attributes table.

To add predicted models table attributes to the Customer360 table

  1. From the Customer 360 tab, under All Databases, open the menu for the customer 360 database, and then select Edit. The Database Editor page opens.

  2. Select the Customer_360 table.

  3. Edit the Customer_360 table to update the SELECT statement for attributes from the Predicted_CLV_Attributes table, similar to:

    -- Predicted Attributes from Predicted_CLV_Attributes pa
    ,pa.predicted_probability_of_transaction_next_365d AS `predicted_probability_of_transaction_next_365d`
    ,pa.predicted_order_frequency_next_365d AS `predicted_order_frequency_next_365d`
    ,pa.predicted_average_order_revenue_next_365d AS `predicted_average_order_revenue_next_365d`
    ,pa.predicted_clv_next_365d AS `predicted_clv_next_365d`
    ,pa.historical_order_frequency_lifetime AS `historical_order_frequency_lifetime`
    ,pa.days_since_last_order AS `days_since_last_order`
    ,pa.predicted_customer_lifecycle_status AS `predicted_customer_lifecycle_status`
    ,pa.predicted_customer_lifetime_value_tier AS `predicted_customer_lifetime_value_tier`
    
  4. Add a LEFT JOIN for the Predicted_CLV_Attributes table below the FROM clause, similar to:

    LEFT JOIN Predicted_CLV_Attributes pa ON pa.amperity_id = mc.amperity_id
    
  5. Click Next.

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

  7. Click Save.

  8. Click Activate.

  9. Run the customer 360 database.