Customer_Attributes Table

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.

This topic describes the starting point for the Customer_Attributes table, and then steps through the process required to update it to be specific to your tenant. Common ways of extending this table to support additional use cases are described, along with providing links to more detailed examples, when available.

Important

The fields that are described in this topic are used to enable downstream workflows in AmpIQ, including segments insights and predictive scoring. You must add these fields with the exact field name, even if the SQL that is required to enable that will vary from tenant to tenant. You may add custom fields to the Customer_Attributes table; custom fields are not used by segment insights and predictive scoring.

Requirements and recommendations

This topic assumes the following requirements are met:

  • PII semantic tags are applied consistently, including assigning the correct data types, to all feeds that contain the following types of customer records: email addresses, phone numbers, and physical addresses.

  • Feeds that contain these customer records are made available to Stitch.

  • The Merged_Customers table is extended to support custom email or custom phone semantics that may be present if individual data sources provide multiple email addresses and/or phone numbers.

  • The Merged_Customers table is extended to exclude common or “bad” email, phone, and/or physical address values.

The following fields should be enabled for your tenant if one (or more) data sources provide the data:

Add table

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.

Important

This table is added automatically when the “Customer 360” template is used to add the customer 360 database. Using a template is the recommended way to add the Customer_Attributes table. This section documents how to manually add this table, should it be necessary.

To add the Customer_Attributes table

  1. From the Database Editor, click Add Table.

  2. Name the table “Customer_Attributes”.

  3. Set the build mode to SQL.

  4. Click Apply template, and then select Customer_Attributes.

  5. Review the required updates, and then make any adjustments that are necessary for your tenant.

  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. From the Table Semantics drop-down, select Customer_Attributes.

  11. Click Save.

SQL query

The following SQL query is the recommended starting point for the Customer_Attributes table. It uses a window function to collect a series of details for customer attributes, including:

  • Customer types (customers or prospects), along with their historical status

  • If customers can be contacted

  • If customers have provided consent for marketing campaigns

  • If customers are employees (current or historical)

  • If transaction items were gifts

  • If a customer is also a business or reseller

  • If a customer’s contact information is related to a test account

  • If a customer’s transaction behavior is an outlier

These details are added to the Customer_Attributes as a series of flags and picklists that can be used within segments.

Source tables for customer attributes include:

  1. Merged_Customers

  2. Transaction_Attributes

  3. Unified_Customer

  4. Unified_Transactions

Certain fields require specific data sources and how you access that data depends on how data is provided to Amperity by the customer, how custom domain tables are built, how custom tables are added to the customer 360 database, or some combination:

  1. Merged_Household, which represents a table that identifies customers by their physical address, and then groups customers together when they share that same physical address.

  2. Email_Aggregate, which represents a custom table that consolidates data that identifies to which customers your marketing campaigns may be directed. This may also be available from a table table that was provided directly from a customer data source.

  3. Transaction_Aggregate, which represents a custom table that consolidates data that identifies to which customers have purchased items as a gift. This may also be available from a table that was provided directly from a customer data source or the Unified_Itemized_Transactions table if it was extended to include that data.

Required updates

Review the following sections, and then make any required updates for your tenant:

Define lifecycle status

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

You may update these terms and the associated durations to match your tenant’s preferences for state names and durations:

WHEN ABS(DATEDIFF(ta.latest_order_datetime, current_date)) <= 365 THEN 'active'
WHEN ABS(DATEDIFF(ta.latest_order_datetime, current_date)) > 365
AND ABS(DATEDIFF(ta.latest_order_datetime, current_date)) <= 730 THEN 'lapsed'
WHEN ABS(DATEDIFF(ta.latest_order_datetime, current_date)) > 730 THEN 'dormant'

The prospect customer state is defined in the following CASE statement:

,CASE
  WHEN true THEN 'prospect'
END AS historical_prospect_lifecycle_status

Define threshold for lifetime order revenue

The Customer_Attributes table sets the lifetime order revenue threshold to > 0 by default. You may change this to your preferred threshold for lifetime order revenue:

WHEN lifetime_order_revenue > 0 THEN 'customer'

Define test accounts

Update the following SQL to include all known test accounts:

WHERE
  (
    LOWER(given_name) LIKE 'test'
    OR LOWER(surname) LIKE 'test'
    OR LOWER(email) LIKE 'test'
    OR (
      LOWER(email) LIKE 'test%'
      AND LOWER(email) LIKE '@domain.ext'
    )
  )
AND amperity_id IS NOT NULL

For example, if there is more than one known test domain, extend the OR block to include each domain:

OR (
  LOWER(email) LIKE 'test%'
  AND LOWER(email) LIKE '@acme.com'
  OR LOWER(email) LIKE '@acme2.com'
)

If there is more than one known test surname, extend the OR block to include each surname:

OR (
  LOWER(surname) LIKE 'test'
  OR LOWER(surname) LIKE 'test2'
)

Adjust outlier threshold

The outlier threshold is a flag that indicates where the total spend (or the sum of order revenue) has a z score greater than 1.645, which corresponds to a ~90% confidence interval. This threshold may be carefully adjusted based on the observed results for the outlier threshold against your tenant’s data.

This threshold is defined in the following CASE statement:

,CASE
  WHEN ABS(
    (
      total_spend - AVG(total_spend) OVER (PARTITION BY purchase_brand)
    ) / (
      STDDEV(total_spend) OVER (PARTITION BY purchase_brand)
    )
  ) >= 1.645 THEN true
  ELSE false
END AS is_outlier

Column reference

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.