Customer Attributes table

The Customer Attributes table contains a series of columns 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 extend it to be more specific to the use cases that are required by your tenant.

Note

This topic assumes that the data in your tenant has the following types of data already configured:

  1. Customer profile data sources, such as names, addresses, email addresses, and phone numbers. This data is made available from the the Merged Customers table and is required by the Customer Attributes table.

  2. Customer interactions, such as orders and items. This data is made available from the Transaction Attributes table (and is optional).

  3. Customer consent data for email address and SMS/phone opt-in to receiving communication from your brand. This data is made available from the Email Opt Status and SMS Opt Status tables (and is optional).

Important

Many columns in the Customer Attributes table are built to enable specific features within Amperity, such as segment insights, predictive scoring, and churn prevention campaigns.

Any column that exists in the column reference must maintain the exact column name and data type, even if your tenant requires custom SQL to enable the feature.

This table may be extended to support use cases that are not described in this topic. The implementation for these types of use cases are always tenant-specific, but should follow a similar approach as the extensions that are described in this topic. Custom fields are not used by segment insights and predictive scoring, but may be used by workflows that you build within your tenant, and then enable downstream.

Start here

The Customer Attributes table has a small number of specific requirements, which are described below. A series of recommended and optional configurations are also available. You may enable any of the optional configurations for your tenant. In some cases, an optional configuration has its own set of specific requirements.

Required configuration

The Customer Attributes table has the following requirements:

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

  • The Merged Customers table is extended to support custom email or custom phone semantics that may be present when 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.

Recommended configuration

This topic assumes that your tenant will use transactions and customer consent tables for email addresses and SMS/phone numbers. These tables are enabled within the SQL that shows the recommended starting point for the Customer Attributes table.

Note

You can comment out the SQL for Transaction attributes, Email opt-in status, and SMS opt-in status if you do not plan to use these features within your tenant.

Optional configurations

The following options may be configured in your tenant:

  1. Non-customer email addresses

  2. Households

  3. Churn prevention

  4. Classifications

  5. Contactability

Add table

The Customer Attributes table contains a series of columns 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.

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.

    Tip

    Review the recommended starting point and then review each of the optional sections, including:

    1. Non-customer email addresses for businesses, employees, gift givers, outliers, resellers, and test accounts.

    2. Households and primary buyers.

    3. Churn prevention campaigns.

    4. Customer classifications.

  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.

About the SQL query template

The Customer Attributes SQL template is available from the database editor and is the starting point for building out the Customer Attributes table for your tenant.

This table is designed to consolidate various data points about your customers into a single table and is customizable to match the use cases and requirements of your tenant.

Important

Review the column reference to familiarize yourself with the table schema. If you extend and/or custommize this table you must use column names that match the names of columns that are in the defined table schema.

For example, email and SMS opt-in status must return a Boolean value for is_email_opted_in and is_sms_opted_in.

You may extend and/or customize this table for columns that are not defined in the schema.

Required updates

The Customer Attributes table builds a list of customers who are contactable. A contactable customer has at least one of the following:

  1. A complete email address

  2. A valid phone number

  3. A complete physical address

If a customer has a complete email address and phone number they are considered to be contactable on social media.

The Merged Customers table is the “source of truth” for this data within your customer 360 database and is required by the Customer Attributes table. Customers for which you do not have any personally identifiable information (PII) are also identified.

Note

When the Email Opt Status and SMS Opt Status tables are available and are configured in this table, the contactable_email and contactable_phone records are updated to be email addresses and phone numbers that are complete in the Merged Customers table and also belong to customers who have given their consent to be contacted by your brand at their email address and/or phone number.

Optional updates

A series of optional updates are available in the Customer Attributes table:

Custom inputs

If your tenant has a table (or a set of tables) that represents the value your customers have to your brand you may configure those tables to provide attributes to the Customer Attributes table. Use a pattern similar to how the Transaction Attributes table is configured.

Non-customer email addresses

The following sections may be added to the Customer Attributes table to update how your brand wants to handle non-customer email addresses:

Important

Add the utility queries for optional updates to the Customer Attributes table. This is done from the Queries page. Click Create, select Folder, and then choose “Utility Queries: Customer Attributes”.

Each of these options follows a similar series of steps:

  1. Use a utility query in the Queries page to fine-tune the logic for your tenant

  2. Add that query to the SQL for Customer Attributes table

  3. Use a LEFT JOIN to include it with the classification_config CTE

  4. Update the list in the customer_attributes_final CTE

Note

Refer to the businesses section in this topic to see a more complete walkthrough of the series of steps required to add each of the non-customer email address sections to the Customer Attributes table. The other sections highlight important details within the utility queries for employees, gift givers, outliers, resellers, and test accounts.

Businesses

Use the “is_business” utility query to identify customer records that likely belong to a business. This query selects street addresses, first names, and last names from the Merged Customers table, and then builds a list of strings that are indicators a name or address may belong to a business.

Review the utility query, and then configure it to identify likely business email addresses. There are two locations in this query that must be updated for your tenant.

The first is the likely_business CTE. Update the values in the CASE statement to match known business address patterns that exist in your tenant:

,CASE
  WHEN given_name LIKE 'inc'
  OR given_name LIKE 'srl'
  OR given_name LIKE 'llc'
  OR given_name LIKE 'service'
  OR given_name LIKE 'business'
  OR surname LIKE 'inc'
  OR surname LIKE 'srl'
  OR surname LIKE 'llc'
  OR surname LIKE 'service'
  OR surname LIKE 'business'
  OR address LIKE 'suite'
  OR address LIKE 'space'
  OR address LIKE 'floor'
  OR address2 LIKE 'suite'
  OR address2 LIKE 'space'
  OR address2 LIKE 'floor'
  THEN true
  ELSE false
END AS is_likely_business

The second is for when you need to define exceptions for names that should not match known business address patterns:

,CASE
  WHEN given_name IN ('name1','name2','name3','...') THEN true
  WHEN surname IN ('name1','name2','name3','...') THEN true
  WHEN address IN ('address1','address2','address3','...') THEN true
  WHEN address2 IN ('address21','address22','address23','...') THEN true
  ELSE false
END AS is_exception

After you have defined business address patterns and exceptions, return the list of likely businesses:

SELECT
  *
FROM likely_businesses
WHERE is_likely_business
AND NOT is_exception
ORDER BY is_likely_business, is_exception

Run the query to validate the results. For example, a query that defines an exception for given name “Dallas” and surname “Good” will return a list of likely businesses similar to:

------------- ------------ --------- ---------------- ---------- ------------- --------------
 amperity_id   given_name   surname   address          address2  is_business   is_exception
------------- ------------ --------- ---------------- ---------- ------------- --------------
 abc123-...    Dallas       Box Co.   123 Main St.     LLC       true          false
 def456-...    Dallas       Good      12 5th Ave       Floor 2   false         true
 ghi789-...    Shug's       Bagels    20 Robin Lane              true          false
------------- ------------ --------- ---------------- ---------- ------------- --------------

When the query is ready, under the following section in the Customer Attributes SQL template:

-- -------------------------------------------------------
-- Extend for non-customer email addresses
-- -------------------------------------------------------

add the updated is_likely_business query:

-- -------------------------------------------------------
-- Extend for business email addresses
-- -------------------------------------------------------

WITH mc_processed AS (
  SELECT
    mc.amperity_id
    ,TRIM(LOWER(mc.address)) address
    ,TRIM(LOWER(mc.address2)) address2
    ,TRIM(LOWER(mc.given_name)) given_name
    ,TRIM(LOWER(mc.surname)) surname
  FROM Merged_Customers mc
)

,likely_businesses as (
  SELECT
    mc_processed.amperity_id
    ,mc_processed.given_name
    ,mc_processed.surname
    ,mc_processed.address
    ,mc_processed.address2
    ,CASE
      WHEN given_name LIKE 'inc'
      OR given_name LIKE 'srl'
      OR given_name LIKE 'llc'
      OR given_name LIKE 'service'
      OR given_name LIKE 'business'
      OR surname LIKE 'inc'
      OR surname LIKE 'srl'
      OR surname LIKE 'llc'
      OR surname LIKE 'service'
      OR surname LIKE 'business'
      OR address LIKE 'suite'
      OR address LIKE 'space'
      OR address LIKE 'floor'
      OR address2 LIKE 'suite'
      OR address2 LIKE 'space'
      OR address2 LIKE 'floor'
      THEN true
      ELSE false
    END AS is_likely_business
    ,CASE
      WHEN given_name IN ('name1','name2','name3','...') THEN true
      WHEN surname IN ('name1','name2','name3','...') THEN true
      WHEN address IN ('address1','address2','address3','...') THEN true
      WHEN address2 IN ('address21','address22','address23','...') THEN true
      ELSE false
    END AS is_exception
  FROM mc_processed
)

SELECT
  *
FROM likely_businesses
WHERE is_likely_business
AND NOT is_exception
ORDER BY is_likely_business, is_exception

Extend classification_config by adding a CASE statement and a LEFT JOIN similar to:

,classification_config AS (
  SELECT
    mc.amperity_id AS amperity_id
    ,CASE
      WHEN DATEDIFF(CURRENT_DATE,ta.first_order_datetime) <= 30 THEN 'New'
      WHEN DATEDIFF(CURRENT_DATE, ta.latest_order_datetime) <= 365 THEN 'Active'
      WHEN DATEDIFF(CURRENT_DATE, ta.latest_order_datetime) > 365
      AND DATEDIFF(CURRENT_DATE, ta.latest_order_datetime) <= 730 THEN 'Lapsed'
      WHEN DATEDIFF(CURRENT_DATE, ta.latest_order_datetime) > 730 THEN 'Dormant'
      WHEN ta.latest_order_datetime IS NULL AND ta.first_order_datetime IS NULL THEN 'Prospect'
    END AS historical_purchaser_lifecycle_status
    ,CASE
      WHEN likely_businesses.is_likely_business
      AND NOT likely_businesses.is_exception
      THEN true
      ELSE false
    END AS is_likely_business
  FROM mc_cte mc
  LEFT JOIN ta_cte ta ON mc.amperity_id = ta.amperity_id
  LEFT JOIN likely_businesses ON likely_businesses.amperity_id=mc.amperity_id
)

Add the likely businesses to customer_attributes_final:

,customer_attributes_final AS (
  SELECT DISTINCT
    cl.amperity_id AS amperity_id
    ,co.contactable_address
    ,co.contactable_email
    ,co.contactable_global
    ,co.contactable_paid_social
    ,co.contactable_phone
    ,co.is_no_pii_amperity_id
    ,cl.is_business
  FROM classification_config cl
  LEFT JOIN contactability_config co
  ON cl.amperity_id = co.amperity_id
)

Run and validate the Customer Attributes table and make any necessary adjustments.

Employees

A customer is an employee when they are employed (or were employed) by your brand. You may choose to include employees within your marketing campaigns, but often it’s preferable to exclude them. Use this extension to identify employees of your brand within your customer profile data.

Note

This utility query follows steps that are similar to ones described in the businesses utility query: build the utility query in Queries page, add the updated query to the Customer Attributes table, extend the classification_config section, and then update the customer_attributes_final section.

Review the is_employee utility query, and then configure it to identify customers who have an email address that is associated with your brand.

,CASE
  WHEN email LIKE '%domain.com'
  THEN true
  ELSE false
END AS is_email

and then allows you to define exceptions:

,CASE
  WHEN email IN ('email1','email2','email3','...') THEN true
  ELSE false
END AS is_exception

Run the query and validate the results. When the query is ready, under the following section in the Customer Attributes SQL template:

-- -------------------------------------------------------
-- Extend for non-customer email addresses
-- -------------------------------------------------------

add the updated is_employee query, extend classification_config by adding a CASE statement and a LEFT JOIN, and then add is_employee to customer_attributes_final.

Gift givers

A gift giver is a customer who purchased an item for someone else.

Note

This utility query follows steps that are similar to ones described in the businesses utility query: build the utility query in Queries page, add the updated query to the Customer Attributes table, extend the classification_config section, and then update the customer_attributes_final section.

The is_gift_giver utility query returns product descriptions from the Unified Itemized Transactions table, and then analyzes those descriptions for indicators of product purchases that were gifts. Review the is_gift_giver utility query, and then configure it to match the approaches your brand uses to identify purchases that are gifts.

,CASE
  WHEN product_description LIKE '%gift%'
  THEN true
  ELSE false
END AS likely_gift_purchase

and then allows you to define exceptions:

,CASE
  WHEN product_description IN ('description1','description2','description3','...') THEN true
  ELSE false
END AS is_exception

Run the query and validate the results. When the query is ready, under the following section in the Customer Attributes SQL template:

-- -------------------------------------------------------
-- Extend for non-customer email addresses
-- -------------------------------------------------------

add the updated is_gift_giver query, extend classification_config by adding a CASE statement and a LEFT JOIN, and then add is_gift_giver to customer_attributes_final.

Outliers

An outlier is a customer with lifetime order revenue greater than a configurable threshold. Customers who meet this threshold should be excluded from your marketing campaign activities.

Note

This utility query follows steps that are similar to ones described in the businesses utility query: build the utility query in Queries page, add the updated query to the Customer Attributes table, extend the classification_config section, and then update the customer_attributes_final section.

Review the is_outlier utility query, and then configure it to identify customers who have outlier lifetime order revenue amounts that exceed a configurable threshold.

The query starts by defining the revenue outlier threshold:

WITH outlier_param AS (
  SELECT
    995 AS percentile
)

Note

The outlier threshold specifies a value between 0-1000, where 1000 is 100% and 995 is 99.5%.

The query builds a list of outliers by comparing records that exceed the record count threshold to lifetime order revenue. In this topic, the outlier threshold is “995” which returns a list of customers whose lifetime revenue is in your top 0.5%.

Run the query and validate the results. When the query is ready, under the following section in the Customer Attributes SQL template:

-- -------------------------------------------------------
-- Extend for non-customer email addresses
-- -------------------------------------------------------

add the updated is_outlier query, extend classification_config by adding a CASE statement and a LEFT JOIN, and then add is_outlier to customer_attributes_final.

Resellers

A reseller is a customer who purchases large quantities of items from your brand for the purpose of reselling them. Customers who meet this threshold should be excluded from your marketing campaign activities.

Note

This utility query follows steps that are similar to ones described in the businesses utility query: build the utility query in Queries page, add the updated query to the Customer Attributes table, extend the classification_config section, and then update the customer_attributes_final section.

Review the is_reseller utility query, and then configure it to identify customers who are likely resellers. This query returns a list of supersized clusters from the Unified Coalesced table that have a record count that exceeds a configurable threshold, and then uses the Transaction Attributes table to associate those records with a configurable top percentage of revenue. A reseller is identified when they belong to that configurable percentage and are a supersized cluster with a record count that exceeds the configurable threshold.

The query starts by defining the revenue outlier threshold:

WITH outlier_param AS (
  SELECT
    990 AS percentile
)

Note

The outlier threshold specifies a value between 0-1000, where 1000 is 100% and 990 is 99%.

The following CTE identifies supersized clusters that exceed the defined threshold. In the following example, the threshold is “50”:

,uc_cte AS (
  SELECT
    amperity_id
    ,is_supersized
    ,COUNT(*) record_count
  FROM Unified_Coalesced
  GROUP BY amperity_id ,is_supersized
  having count(*) > 50
)

The query builds a list of likely resellers by comparing supersized cluster records that exceed the record count threshold to lifetime order revenue that exceeds the configured revenue threshold. In this topic, the supersized records threshold is “50” and the revenue threshold is “990” which returns a list of supersized clusters whose lifetime revenue is in your top 1%.

Run the query and validate the results. When the query is ready, under the following section in the Customer Attributes SQL template:

-- -------------------------------------------------------
-- Extend for non-customer email addresses
-- -------------------------------------------------------

add the updated is_reseller query, extend classification_config by adding a CASE statement and a LEFT JOIN, and then add is_reseller to customer_attributes_final.

Test accounts

A test account is an email address that does not belong to a customer, such as test@acme.com or support@yourbrand.com. Email addresses that are identified as test accounts should be excluded from your marketing campaign activities.

Note

This utility query follows steps that are similar to ones described in the businesses utility query: build the utility query in Queries page, add the updated query to the Customer Attributes table, extend the classification_config section, and then update the customer_attributes_final section.

Review the is_test_accounts utility query, and then configure it to identify email addresses that do not belong to a customer and are likely test email accounts. Use a CASE statement to define the patterns that identify test accounts. For example:

CASE
  WHEN given_name LIKE 'test'
  OR given_name LIKE 'default'
  OR surname LIKE 'test'
  OR surname LIKE 'default'
  OR full_name LIKE 'test'
  OR full_name LIKE 'default'
  OR email LIKE 'test'
  OR email LIKE 'test%'
  OR email LIKE 'default'
  OR email LIKE 'default%'
  THEN true
  ELSE false
END AS is_likely_test_account

and then allows you to define exceptions:

,CASE
  WHEN given_name IN ('name1','name2','...') THEN true
  WHEN surname IN ('name1','name2','...') THEN true
  WHEN full_name IN ('full_name1','full_name2','...') THEN true
  WHEN email IN ('email1','email2','...') THEN true
  ELSE false
END AS is_exception

Run the query and validate the results. When the query is ready, under the following section in the Customer Attributes SQL template:

-- -------------------------------------------------------
-- Extend for non-customer email addresses
-- -------------------------------------------------------

add the updated is_test_account query, extend classification_config by adding a CASE statement and a LEFT JOIN, and then add is_test_account to customer_attributes_final.

Households

The Merged Households table is a data table that must be added to a customer 360 database to support address-based householding. The purpose of the Merged Households table is to apply address-based householding and address standardization to the output of the Merged Customers table. The contents of this table contain the Household ID and can be used to improve the results of certain campaign types, such as direct mail.

Note

The Merged Households table generates a household ID (as a UUID) for records with an exact match on full_address and surname. The Merged Households table requires the Merged Customers table to ensure that household IDs are applied after the best address has been identified.

You can configure the Customer Attributes table to use specific columns from the Merged Households and Transaction Attributes tables to identify the primary buyer within a household.

Under the following section in the Customer Attributes SQL template:

-- -------------------------------------------------------
-- Extend for households
-- -------------------------------------------------------

configure the following SQL:

-- -------------------------------------------------------
-- Extend for households
-- -------------------------------------------------------

,primary_buyer_logic AS (
  SELECT
    mh.amperity_id AS amperity_id
    ,mh.household_id
    ,mh.household_size
    ,ta.lifetime_order_revenue
    ,ROW_NUMBER() OVER(PARTITION BY mh.household_id ORDER BY ta.lifetime_order_revenue desc) AS buyer_rank
  FROM (
    SELECT
      amperity_id
      ,household_id
      ,household_size
    FROM merged_households
  ) mh
  INNER JOIN transaction_attributes ta
  ON mh.amperity_id = ta.amperity_id
)

,primary_buyer_cte AS (
  SELECT distinct
    amperity_id
  FROM primary_buyer_logic
  WHERE buyer_rank = 1
)

and then extend classification_config for primary buyers:

,CASE
  WHEN pb.amperity_id IS NULL
  THEN false
  ELSE true
END AS is_primary_buyer_household

...

LEFT JOIN primary_buyer_cte pb
ON mc.amperity_id=pb.amperity_id

Churn prevention

Brands use churn prevention campaigns to win back customers who have not purchased within defined time windows, such as 90 days, 120 days, or even up to 2 years. The length of the defined time window and the types of promotions and offers you will send depends on your goals for each audience within the churn prevention campaign.

Each churn prevention campaign is unique, but they all have something in common: a series of messages that are sent when customers enter (or exit) specific stages within that churn prevention campaign. Each stage is associated with a threshold that defines a customer’s current churn status.

The Customer Attributes table can be configured to provide your customers’ current churn status – one of “active”, “cooling down”, “at risk”, “highly at risk”, or “lost” – along with the date on which your customers were assigned to that status.

Important

The Customer Attributes table requires a table that tracks churn prevention events to be present in your customer 360 database. The name of this table may be unique to your tenant. This topic refers to this table as the Churn Events table.

You have two choices for defining churn events for this table:

  1. Use predicted modeling to define your customers’ current churn status. This option is available when your tenant has predictive modeling enabled and a Churn Events table is configured for use in your tenant.

  2. Use historical order history to define your customers’ churn status. For example, you could configure the Churn Events table to define churn status as “active” as “within the past 60 days”, “at risk” as “within the past 365 days”, and so on, using SQL similar to:

    ,historical_churn_classification AS (
  SELECT
    mc.amperity_id AS amperity_id
    ,CASE
      WHEN DATEDIFF(CURRENT_DATE, ta.latest_order_datetime) <= 60 THEN 'active'
      WHEN DATEDIFF(CURRENT_DATE, ta.latest_order_datetime) <= 120 THEN 'cooling down'
      WHEN DATEDIFF(CURRENT_DATE, ta.latest_order_datetime) <= 240 THEN 'at risk'
      WHEN DATEDIFF(CURRENT_DATE, ta.latest_order_datetime) <= 365 THEN 'highly at risk'
      WHEN DATEDIFF(CURRENT_DATE, ta.latest_order_datetime) <= 730 THEN 'lost'
    END AS historical_churn_status
  FROM mc_cte mc
  LEFT JOIN ta_cte ta ON mc.amperity_id = ta.amperity_id
)

    Update the values for each threshold to align to your products and customer purchase histories and to the stages your brand uses within churn prevention campaigns.

If your brand will use the Customer Attributes table as part of your churn prevention campaign activities, replace the following SQL:

-- -------------------------------------------------------
-- Extend for churn prevention
-- -------------------------------------------------------

with:

-- -------------------------------------------------------
-- Extend for churn prevention
-- -------------------------------------------------------

,churn_events_cte AS (
  SELECT
    amperity_id
    ,CONCAT(current_event_status,' event') AS churn_event
    ,event_status_start_datetime AS churn_event_start_datetime
  FROM Churn_Events

and then update the list of customer attributes to include historical churn status.

Classifications

The recommended starting SQL, as described in this topic, configures using the Transaction Attributes table. This enables two flags that differentiate your customers into “purchasers” and “prospects”. These flags are enabled by the following SQL in classification_config:

,CASE
  WHEN ta.lifetime_order_frequency >= 1
  THEN true
  ELSE false
END AS is_purchaser
,CASE
  WHEN ta.lifetime_order_frequency < 1
  OR ta.lifetime_order_frequency IS NULL
  THEN true
  ELSE false
END AS is_prospect

and both attributes must be available in the list of customer attributes:

-- -------------------------------------------------------
-- Extend for customer attributes
-- -------------------------------------------------------

,customer_attributes_final AS (
  SELECT DISTINCT
    ...
    ,cl.is_purchaser
    ,cl.is_prospect
    ...
  FROM classification_config cl
)

Contactability

A customer is considered to be “contactable” when they have a complete email address, phone number, or physical address (including all of street address, city, state, postal code) in the Merged Customers table.

When a customer has a complete email address and phone number they are considered to be contactable on paid social media.

When the Email Opt Status and SMS Opt Status tables are available to the Customer Attributes table, contactable email addresses and phone numbers are updated to be only those for which customers have given consent to your brand to use for marketing activities.

The contactable_phone, contactable_email, contactable_address, contactable_paid_social and contactable_global attributes provide input to the Reachability card in segment insights, similar to:

Reachability card in segment insights.

The following example shows the configuration for contactability, including for when customer consent for email addresses and phone numbers is available:

-- -------------------------------------------------------
-- Extend for contactability
-- -------------------------------------------------------

,contactability_config AS (
  SELECT
    mc.amperity_id
    ,eo.is_email_opted_in AS contactable_email
    ,so.is_sms_opted_in AS contactable_phone
    ,CASE
      WHEN mc.address_completion = 4
      THEN true
      ELSE false
    END AS contactable_address
    ,CASE
      WHEN mc.phone_completion > 0
      OR mc.email_completion > 0
      THEN true
      ELSE false
    END AS contactable_paid_social
    ,CASE
      WHEN mc.phone_completion = 0
      AND mc.email_completion = 0
      AND mc.address_completion = 0
      AND mc.name_completion = 0
      THEN true
      ELSE false
    END AS is_no_pii_amperity_id
    ,CASE
      WHEN mc.phone_completion = 0
      AND mc.email_completion = 0
      AND mc.address_completion = 0
      AND mc.name_completion = 0
      THEN false
      ELSE true
    END AS contactable_global
  FROM mc_cte mc
  LEFT JOIN email_opt_status eo ON mc.amperity_id = eo.amperity_id
  AND COALESCE(mc.email, mc.amperity_id) = eo.email
  LEFT JOIN sms_opt_status so ON mc.amperity_id = so.amperity_id
  AND COALESCE(mc.phone, mc.amperity_id) = so.phone
)

Note

If your tenant does not have access to email and SMS consent status, update the configuration for the contactable_email and contactable_phone columns to:

,CASE
  WHEN mc.email_completion = 1
  THEN true
  ELSE false
END AS contactable_email
,CASE
  WHEN mc.phone_completion = 1
  THEN true
  ELSE false
END AS contactable_phone

Extended customer attributes

The list of customer attributes must be extended to include all of the updates you have made. Review the section for business email addresses for an example of adding an attribute to this list.

-- -------------------------------------------------------
-- Extend for customer attributes
-- -------------------------------------------------------

,customer_attributes_final AS (
  SELECT DISTINCT
    cl.amperity_id AS amperity_id
    ,co.contactable_address
    ,co.contactable_email
    ,co.contactable_global
    ,co.contactable_paid_social
    ,co.contactable_phone
    ,co.is_no_pii_amperity_id
  FROM classification_config cl
  LEFT JOIN contactability_config co
  ON cl.amperity_id = co.amperity_id
)

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

Churn Trigger

String

The churn status for a customer. Possible values:

  • Active

  • Cooling down

  • At risk

  • Highly at risk

  • Lost

  • Blank

Tip

Use these values as part of your churn prevention campaigns.

Churn Trigger Start Datetime

Datetime

The date and time at which the Churn Trigger status begins.

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 has an email address with a valid format.

Contactable Global

Boolean

A flag that indicates if a customer can be contacted by phone number, email address, or physical mailing address.

Contactable Paid Social

Boolean

A flag that indicates if a customer has personally identifiable information (PII) that could be used to contact them using paid social media channels.

Contactable Phone

Boolean

A flag that indicates if a customer has a phone number with a valid format.

Historical Purchaser Lifecycle Status

String

The status for a customer, based on their history and recency of interactions a brand. Possible values: “new”, “active”, “lapsed”, “dormant”, and “prospect”.

Customer states are defined as “active”, “lapsed”, “dormant”, and “prospect”. Purchase behaviors are 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”.

Note

The ranges for this field are customizable. The default ranges represent:

  • “New” represents customers who have purchased within the previous 30 days.

  • “Active” represents customers who have purchased within the previous 365 days.

  • “Lapsed” represents customers who have purchased between 365 and 730 days ago.

  • “Dormant” represents customers who have not purchased within the previous 730 days (two years).

  • “Prospect” represents individuals who have not made a purchase.

Is Business

Boolean

A flag that indicates if a customer is a known or likely business.

Is Employee

Boolean

A flag that indicates if a customer is or has been an employee of the brand at any time.

Is Gift Giver

Boolean

A flag that indicates if a customer has purchased items as gifts.

Is No PII Amperity ID

Boolean

A flag that indicates if the customer does not have personally identifiable information (PII) for name (given name, surname, full name), address (street address, city, state, postal code), email address, or phone number.

Is Opted Into Email

Boolean

A flag that indicates if the customer has given consent to receive email communications from your brand.

Is Opted Into SMS

Boolean

A flag that indicates if the customer has given consent to receive SMS messages from your brand.

Is Outlier

Boolean

A flag that indicates if the customer has abnormally high purchase behaviors in comparison to other purchasers.

Is Primary Buyer Household

Boolean

A flag that indicates if a customer is the individual within a household who represents the highest lifetime revenue for that household.

Note

This attribute requires the Merged Households table.

Is Prospect

Boolean

A flag that indicates if a customer does not have a purchase history with a brand.

Is Purchaser

Boolean

A flag that indicates if the customer has a purchase history with a brand.

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 an order frequency threshold, such as “greater than 50”)

Is Test Account

Boolean

A flag that indicates if the customer is a known test account for a brand.