Customer Attributes table

The Customer Attributes table has a series of columns that identify attributes about individuals. For example:

  • Is an individual contactable?

  • Is there a marketable email address?

  • Is the physical address known?

  • Is there a phone number?

  • Are they are an employee, reseller, or a test account?

  • What is the individual’s revenue relationship with the 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 Extended 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 has a series of columns that identify attributes about individuals. For example:

  • Is an individual contactable?

  • Is there a marketable email address?

  • Is the physical address known?

  • Is there a phone number?

  • Are they are an employee, reseller, or a test account?

  • What is the individual’s revenue relationship with the 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 dropdown, 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:

 1,CASE
 2  WHEN given_name LIKE 'inc'
 3  OR given_name LIKE 'srl'
 4  OR given_name LIKE 'llc'
 5  OR given_name LIKE 'service'
 6  OR given_name LIKE 'business'
 7  OR surname LIKE 'inc'
 8  OR surname LIKE 'srl'
 9  OR surname LIKE 'llc'
10  OR surname LIKE 'service'
11  OR surname LIKE 'business'
12  OR address LIKE 'suite'
13  OR address LIKE 'space'
14  OR address LIKE 'floor'
15  OR address2 LIKE 'suite'
16  OR address2 LIKE 'space'
17  OR address2 LIKE 'floor'
18  THEN true
19  ELSE false
20END AS is_likely_business

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

1,CASE
2  WHEN given_name IN ('name1','name2','name3','...') THEN true
3  WHEN surname IN ('name1','name2','name3','...') THEN true
4  WHEN address IN ('address1','address2','address3','...') THEN true
5  WHEN address2 IN ('address21','address22','address23','...') THEN true
6  ELSE false
7END AS is_exception

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

1SELECT
2  *
3FROM likely_businesses
4WHERE is_likely_business
5AND NOT is_exception
6ORDER 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:

 1-- -------------------------------------------------------
 2-- Extend for business email addresses
 3-- -------------------------------------------------------
 4
 5WITH mc_processed AS (
 6  SELECT
 7    mc.amperity_id
 8    ,TRIM(LOWER(mc.address)) address
 9    ,TRIM(LOWER(mc.address2)) address2
10    ,TRIM(LOWER(mc.given_name)) given_name
11    ,TRIM(LOWER(mc.surname)) surname
12  FROM Merged_Customers mc
13)
14
15,likely_businesses as (
16  SELECT
17    mc_processed.amperity_id
18    ,mc_processed.given_name
19    ,mc_processed.surname
20    ,mc_processed.address
21    ,mc_processed.address2
22    ,CASE
23      WHEN given_name LIKE 'inc'
24      OR given_name LIKE 'srl'
25      OR given_name LIKE 'llc'
26      OR given_name LIKE 'service'
27      OR given_name LIKE 'business'
28      OR surname LIKE 'inc'
29      OR surname LIKE 'srl'
30      OR surname LIKE 'llc'
31      OR surname LIKE 'service'
32      OR surname LIKE 'business'
33      OR address LIKE 'suite'
34      OR address LIKE 'space'
35      OR address LIKE 'floor'
36      OR address2 LIKE 'suite'
37      OR address2 LIKE 'space'
38      OR address2 LIKE 'floor'
39      THEN true
40      ELSE false
41    END AS is_likely_business
42    ,CASE
43      WHEN given_name IN ('name1','name2','name3','...') THEN true
44      WHEN surname IN ('name1','name2','name3','...') THEN true
45      WHEN address IN ('address1','address2','address3','...') THEN true
46      WHEN address2 IN ('address21','address22','address23','...') THEN true
47      ELSE false
48    END AS is_exception
49  FROM mc_processed
50)
51
52SELECT
53  *
54FROM likely_businesses
55WHERE is_likely_business
56AND NOT is_exception
57ORDER BY is_likely_business, is_exception

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

 1,classification_config AS (
 2  SELECT
 3    mc.amperity_id AS amperity_id
 4    ,CASE
 5      WHEN DATEDIFF(CURRENT_DATE,ta.first_order_datetime) <= 30 THEN 'New'
 6      WHEN DATEDIFF(CURRENT_DATE, ta.latest_order_datetime) <= 365 THEN 'Active'
 7      WHEN DATEDIFF(CURRENT_DATE, ta.latest_order_datetime) > 365
 8      AND DATEDIFF(CURRENT_DATE, ta.latest_order_datetime) <= 730 THEN 'Lapsed'
 9      WHEN DATEDIFF(CURRENT_DATE, ta.latest_order_datetime) > 730 THEN 'Dormant'
10      WHEN ta.latest_order_datetime IS NULL AND ta.first_order_datetime IS NULL THEN 'Prospect'
11    END AS historical_purchaser_lifecycle_status
12    ,CASE
13      WHEN likely_businesses.is_likely_business
14      AND NOT likely_businesses.is_exception
15      THEN true
16      ELSE false
17    END AS is_likely_business
18  FROM mc_cte mc
19  LEFT JOIN tae_cte ta ON mc.amperity_id = ta.amperity_id
20  LEFT JOIN likely_businesses ON likely_businesses.amperity_id=mc.amperity_id
21)

Add the likely businesses to customer_attributes_final:

 1,customer_attributes_final AS (
 2  SELECT DISTINCT
 3    cl.amperity_id AS amperity_id
 4    ,co.contactable_address
 5    ,co.contactable_email
 6    ,co.contactable_global
 7    ,co.contactable_paid_social
 8    ,co.contactable_phone
 9    ,co.is_no_pii_amperity_id
10    ,cl.is_business
11  FROM classification_config cl
12  LEFT JOIN contactability_config co
13  ON cl.amperity_id = co.amperity_id
14)

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.

1,CASE
2  WHEN email LIKE '%domain.com'
3  THEN true
4  ELSE false
5END AS is_email

and then allows you to define exceptions:

1,CASE
2  WHEN email IN ('email1','email2','email3','...') THEN true
3  ELSE false
4END 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.

1,CASE
2  WHEN product_description LIKE '%gift%'
3  THEN true
4  ELSE false
5END AS likely_gift_purchase

and then allows you to define exceptions:

1,CASE
2  WHEN product_description IN ('description1','description2','description3','...') THEN true
3  ELSE false
4END 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:

1WITH outlier_param AS (
2  SELECT
3    995 AS percentile
4)

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 Extended 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:

1WITH outlier_param AS (
2  SELECT
3    990 AS percentile
4)

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

1,uc_cte AS (
2  SELECT
3    amperity_id
4    ,is_supersized
5    ,COUNT(*) record_count
6  FROM Unified_Coalesced
7  GROUP BY amperity_id ,is_supersized
8  having count(*) > 50
9)

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:

 1CASE
 2  WHEN given_name LIKE 'test'
 3  OR given_name LIKE 'default'
 4  OR surname LIKE 'test'
 5  OR surname LIKE 'default'
 6  OR full_name LIKE 'test'
 7  OR full_name LIKE 'default'
 8  OR email LIKE 'test'
 9  OR email LIKE 'test%'
10  OR email LIKE 'default'
11  OR email LIKE 'default%'
12  THEN true
13  ELSE false
14END AS is_likely_test_account

and then allows you to define exceptions:

1,CASE
2  WHEN given_name IN ('name1','name2','...') THEN true
3  WHEN surname IN ('name1','name2','...') THEN true
4  WHEN full_name IN ('full_name1','full_name2','...') THEN true
5  WHEN email IN ('email1','email2','...') THEN true
6  ELSE false
7END 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 applies address-based householding and address standardization to the output of the Merged Customers table and adds a column for household ID. Use the Merged Households table to improve campaigns that send offers to shared physical addresses, such as direct mail campaigns.

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 Extended 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:

 1-- -------------------------------------------------------
 2-- Extend for households
 3-- -------------------------------------------------------
 4
 5,primary_buyer_logic AS (
 6  SELECT
 7    mh.amperity_id AS amperity_id
 8    ,mh.household_id
 9    ,mh.household_size
10    ,ta.lifetime_order_revenue
11    ,ROW_NUMBER() OVER(PARTITION BY mh.household_id ORDER BY ta.lifetime_order_revenue desc) AS buyer_rank
12  FROM (
13    SELECT
14      amperity_id
15      ,household_id
16      ,household_size
17    FROM merged_households
18  ) mh
19  INNER JOIN transaction_attributes_extended ta
20  ON mh.amperity_id = ta.amperity_id
21)
22
23,primary_buyer_cte AS (
24  SELECT distinct
25    amperity_id
26  FROM primary_buyer_logic
27  WHERE buyer_rank = 1
28)

and then extend classification_config for primary buyers:

 1,CASE
 2  WHEN pb.amperity_id IS NULL
 3  THEN false
 4  ELSE true
 5END AS is_primary_buyer_household
 6
 7...
 8
 9LEFT JOIN primary_buyer_cte pb
10ON 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.

    Note

    Churn status classifications for predictive modeling results in the Churn Events table are not configurable.

    For repeat customers, groupings include the following tiers:

    • Active (likelihood to purchase is greater than 60%)

    • Cooling down (likelihood to purchase is between than 50% and 60%)

    • At risk (likelihood to purchase is between than 35% and 50%)

    • Highly at risk (likelihood to purchase is between than 20% and 35%)

    • Lost (likelihood to purchase is less than 20%)

    For one-time buyers, groupings include the following tiers:

    • Active (purchased within 60 days)

    • Cooling down (purchased 60-120 days ago)

    • At risk (purchased 120-180 days ago)

    • Highly at risk (purchased 180-240 days ago)

    • Lost (purchased 240+ days ago)

  2. Use historical order history to define your customers’ churn status. For example, you could configure the Customer Attributes 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:

     1,historical_churn_classification AS (
 2  SELECT
 3    mc.amperity_id AS amperity_id
 4    ,CASE
 5      WHEN DATEDIFF(CURRENT_DATE, ta.latest_order_datetime) <= 60 THEN 'active'
 6      WHEN DATEDIFF(CURRENT_DATE, ta.latest_order_datetime) <= 120 THEN 'cooling down'
 7      WHEN DATEDIFF(CURRENT_DATE, ta.latest_order_datetime) <= 240 THEN 'at risk'
 8      WHEN DATEDIFF(CURRENT_DATE, ta.latest_order_datetime) <= 365 THEN 'highly at risk'
 9      WHEN DATEDIFF(CURRENT_DATE, ta.latest_order_datetime) <= 730 THEN 'lost'
10    END AS historical_churn_status
11  FROM mc_cte mc
12  LEFT JOIN tae_cte ta ON mc.amperity_id = ta.amperity_id
13)

    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:

 1-- -------------------------------------------------------
 2-- Extend for churn prevention
 3-- -------------------------------------------------------
 4
 5,churn_events_cte AS (
 6  SELECT
 7    amperity_id
 8    ,CONCAT(current_event_status,' event') AS churn_event
 9    ,event_status_start_datetime AS churn_event_start_datetime
10  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 Extended table. This enables two flags that differentiate your customers into “purchasers” and “prospects”. These flags are enabled by the following SQL in classification_config:

 1,CASE
 2  WHEN ta.lifetime_order_frequency >= 1
 3  THEN true
 4  ELSE false
 5END AS is_purchaser
 6,CASE
 7  WHEN ta.lifetime_order_frequency < 1
 8  OR ta.lifetime_order_frequency IS NULL
 9  THEN true
10  ELSE false
11END AS is_prospect

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

 1-- -------------------------------------------------------
 2-- Extend for customer attributes
 3-- -------------------------------------------------------
 4
 5,customer_attributes_final AS (
 6  SELECT DISTINCT
 7    ...
 8    ,cl.is_purchaser
 9    ,cl.is_prospect
10    ...
11  FROM classification_config cl
12)

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:

 1-- -------------------------------------------------------
 2-- Extend for contactability
 3-- -------------------------------------------------------
 4
 5,contactability_config AS (
 6  SELECT
 7    mc.amperity_id
 8    ,eo.is_email_opted_in AS contactable_email
 9    ,so.is_sms_opted_in AS contactable_phone
10    ,CASE
11      WHEN mc.address_completion = 4
12      THEN true
13      ELSE false
14    END AS contactable_address
15    ,CASE
16      WHEN mc.phone_completion > 0
17      OR mc.email_completion > 0
18      THEN true
19      ELSE false
20    END AS contactable_paid_social
21    ,CASE
22      WHEN mc.phone_completion = 0
23      AND mc.email_completion = 0
24      AND mc.address_completion = 0
25      AND mc.name_completion = 0
26      THEN true
27      ELSE false
28    END AS is_no_pii_amperity_id
29    ,CASE
30      WHEN mc.phone_completion = 0
31      AND mc.email_completion = 0
32      AND mc.address_completion = 0
33      AND mc.name_completion = 0
34      THEN false
35      ELSE true
36    END AS contactable_global
37  FROM mc_cte mc
38  LEFT JOIN email_opt_status eo ON mc.amperity_id = eo.amperity_id
39  AND COALESCE(mc.email, mc.amperity_id) = eo.email
40  LEFT JOIN sms_opt_status so ON mc.amperity_id = so.amperity_id
41  AND COALESCE(mc.phone, mc.amperity_id) = so.phone
42)

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:

 1,CASE
 2  WHEN mc.email_completion = 1
 3  THEN true
 4  ELSE false
 5END AS contactable_email
 6,CASE
 7  WHEN mc.phone_completion = 1
 8  THEN true
 9  ELSE false
10END 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.

 1-- -------------------------------------------------------
 2-- Extend for customer attributes
 3-- -------------------------------------------------------
 4
 5,customer_attributes_final AS (
 6  SELECT DISTINCT
 7    cl.amperity_id AS amperity_id
 8    ,co.contactable_address
 9    ,co.contactable_email
10    ,co.contactable_global
11    ,co.contactable_paid_social
12    ,co.contactable_phone
13    ,co.is_no_pii_amperity_id
14  FROM classification_config cl
15  LEFT JOIN contactability_config co
16  ON cl.amperity_id = co.amperity_id
17)

Column reference

The Customer Attributes table contains the following columns:

Column name

Data type

Description

Amperity ID

String

The unique identifier assigned to clusters of customer profiles that all represent the same individual. The Amperity ID does not replace primary, foreign, or other unique customer keys, but exists alongside them within unified profiles.

Note

The Amperity ID is a universally unique identifier (UUID) that is 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 “active”, “lapsed”, “dormant”, or “prospect”. Purchase behaviors use a 5 year window. A customer who purchased within the previous 365 days–1 year–is “active” and within the previous 730 days–2 years–is “lapsed”. A customer who has not purchased within 2 years is “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 Extended 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.