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:
Recommended. A data source that identifies which of your customers will allow you to contact them by sending them an email, using a phone number to call or text, and/or by sending mail to their physical address. Use that data source to add marketable_address, marketable_address, marketable_phone, and marketable_global columns to the Customer_Attributes table.
Optional. A data source that identifies if a customer is an employee (either current or historical). Use that data source to add a is_employee and is_employee_current columns to the Customer_Attributes table.
Optional. A data source that identifies if a customer purchased an item as a gift. Use that data source to add an is_gift_giver column to the Customer_Attributes table.
Optional. A data source that identifies if a customer is a business. Use that data source to add an is_business column to the Customer_Attributes table.
Optional. A data source that identifies if a customer is a reseller. Use that data source to add an is_reseller column to the Customer_Attributes table.
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
From the Database Editor, click Add Table.
Name the table “Customer_Attributes”.
Set the build mode to SQL.
Click Apply template, and then select Customer_Attributes.
Review the required updates, and then make any adjustments that are necessary for your tenant.
Click Validate to verify the SQL runs without error.
Click Next. This opens the Database Table Definition page.
Add a table description. This enables a tooltip that is visible from other areas in Amperity.
Verify that the db/required and db/unique database field semantics were applied to the amperity_id column.
From the Table Semantics drop-down, select Customer_Attributes.
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:
Merged_Customers
Transaction_Attributes
Unified_Customer
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:
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.
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.
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.
Recommended starting point¶
The following query represents the recommended starting point for the Customer_Attributes table. This query requires updates in some locations.
WITH customer_base AS (
SELECT amperity_id
FROM Merged_Customers
GROUP BY amperity_id
),
customer_type_table AS (
SELECT
amperity_id
,CASE
WHEN lifetime_order_frequency > 0 THEN 'customer'
ELSE 'prospect'
END AS customer_type
FROM Transaction_Attributes
WHERE amperity_id IS NOT NULL
GROUP BY
amperity_id
,CASE
WHEN lifetime_order_frequency > 0 THEN 'customer'
ELSE 'prospect'
END
),
customer_lifecycle_status AS (
SELECT
ub.amperity_id
,CASE
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'
ELSE 'ERROR'
END AS historical_customer_lifecycle_status
FROM customer_base ub
LEFT JOIN Transaction_Attributes ta ON ub.amperity_id = ta.amperity_id
),
prospect_lifecycle_status AS (
SELECT
ub.amperity_id
,CASE
WHEN true THEN 'prospect'
END AS historical_prospect_lifecycle_status
FROM customer_base ub
LEFT JOIN Transaction_Attributes ta ON ub.amperity_id = ta.amperity_id
),
contactability_base AS (
SELECT
amperity_id
,CASE
WHEN phone RLIKE('^(\\+\\d{1,2}\\s)?\\(?\\d{3}\\)?[\\s.-]?\\d{3}[\\s.-]?\\d{4}$')
AND phone_completion > 0 THEN true
ELSE false
END AS contactable_phone
,CASE
WHEN email RLIKE('^[a-zA-Z0-9+_.-]+@[a-zA-Z0-9.-]+$')
AND email_completion > 0 THEN true
ELSE false
END AS contactable_email
,CASE
WHEN address_completion = 4 THEN true
ELSE false
END AS contactable_address
,CASE
WHEN email RLIKE('^[a-zA-Z0-9+_.-]+@[a-zA-Z0-9.-]+$')
AND email_completion > 0
OR phone RLIKE('^(\\+\\d{1,2}\\s)?\\(?\\d{3}\\)?[\\s.-]?\\d{3}[\\s.-]?\\d{4}$')
AND phone_completion > 0
OR address_completion = 4 THEN true
ELSE false
END AS contactable_global
FROM Merged_Customers
),
-- -------------------------------------------------------
-- Extend for marketability here
-- -------------------------------------------------------
-- -------------------------------------------------------
-- Extend for employees here
-- -------------------------------------------------------
-- -------------------------------------------------------
-- Extend for gift items here
-- -------------------------------------------------------
-- -------------------------------------------------------
-- Extend for businesses here
-- -------------------------------------------------------
-- -------------------------------------------------------
-- Extend for resellers here
-- -------------------------------------------------------
test_account_table AS (
SELECT
amperity_id
,true AS is_test_account
FROM Unified_Customer
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
GROUP BY
amperity_id
,true
),
outlier_table_base AS (
SELECT
amperity_id
,total_spend
,(
total_spend - AVG(total_spend) OVER (PARTITION BY purchase_brand)
) / (
STDDEV(total_spend) OVER (PARTITION BY purchase_brand)
) AS zscore
,purchase_brand
,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
FROM
(
SELECT
amperity_id
,SUM(order_revenue) AS total_spend
,purchase_brand
FROM Unified_Transactions
WHERE amperity_id IS NOT NULL
GROUP BY
amperity_id
,purchase_brand
)
),
outlier_table AS (
SELECT
amperity_id
,is_outlier
FROM outlier_table_base
WHERE is_outlier = true
GROUP BY
amperity_id
,is_outlier
),
customer_attributes_final AS (
SELECT
ub.amperity_id
,CASE
WHEN ut.customer_type IS NOT NULL THEN ut.customer_type
ELSE 'prospect'
END AS customer_type
,CASE
WHEN ut.customer_type = 'customer' THEN cls.historical_customer_lifecycle_status
END AS historical_customer_lifecycle_status
,CASE
WHEN ut.customer_type = 'prospect'
OR ut.customer_type IS NULL THEN pls.historical_prospect_lifecycle_status
END AS historical_prospect_lifecycle_status
,cb.contactable_phone
,cb.contactable_email
,cb.contactable_address
,cb.contactable_global
-- -------------------------------------------------------
-- Add CASE statement for marketability here
-- -------------------------------------------------------
-- -------------------------------------------------------
-- Add CASE statements for employees here
-- -------------------------------------------------------
-- -------------------------------------------------------
-- Add CASE statement for gift givers here
-- -------------------------------------------------------
-- -------------------------------------------------------
-- Add CASE statements for businesses here
-- -------------------------------------------------------
-- -------------------------------------------------------
-- Add CASE statements for resellers here
-- -------------------------------------------------------
,CASE
WHEN test.is_test_account IS NOT NULL THEN test.is_test_account
ELSE false
END AS is_test_account
,CASE
WHEN o.is_outlier IS NOT NULL THEN o.is_outlier
ELSE false
END AS is_outlier
,CONCAT(
CASE
WHEN test.is_test_account IS NOT NULL AND test.is_test_account != false THEN 'is_test_account' ELSE ''
END
,CASE
WHEN o.is_outlier IS NOT NULL AND o.is_outlier != false THEN 'is_outlier' ELSE ''
END
) AS customer_classifications
FROM customer_base ub
LEFT JOIN customer_type_table ut ON ub.amperity_id = ut.amperity_id
LEFT JOIN customer_lifecycle_status cls ON ub.amperity_id = cls.amperity_id
LEFT JOIN prospect_lifecycle_status pls ON ub.amperity_id = pls.amperity_id
LEFT JOIN contactability_base cb ON ub.amperity_id = cb.amperity_id
-- -------------------------------------------------------
-- Add LEFT JOIN for marketability here
-- -------------------------------------------------------
-- -------------------------------------------------------
-- Add LEFT JOINs for employees here
-- -------------------------------------------------------
-- -------------------------------------------------------
-- Add LEFT JOIN for gift givers here
-- -------------------------------------------------------
-- -------------------------------------------------------
-- Add LEFT JOIN for businesses here
-- -------------------------------------------------------
-- -------------------------------------------------------
-- Add LEFT JOIN for resellers here
-- -------------------------------------------------------
LEFT JOIN test_account_table test ON ub.amperity_id = test.amperity_id
LEFT JOIN outlier_table o ON ub.amperity_id = o.amperity_id
)
SELECT *
FROM customer_attributes_final
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
Recommended updates¶
This section describes recommended updates for the Customer_Attributes table:
Important
Recommended updates often require tenant-specific SQL. Use the examples in this section as a starting point for understanding what is required to enable these columns in the Customer_Attributes table.
Marketability¶
Marketability is the ability to contact a customer by sending them an email, a text message or a phone call, or direct mail to their physical address. This ability is often brand-specific, where a customer has shopped at brand A and has opted in to email and text marketing, but has not opted in to brands B and C because they have not shopped at or purchased from those brands.
An extension for marketability must add the following columns to the Customer_Attributes table:
marketable_email
marketable_address
marketable_phone
Note
Marketability requires consolidated email data that indicates if a customer has given consent to receive marketing to their email account, phone number, and physical address. This data is often available from a customer’s data source that can be provided to Amperity as a feed. In some cases, this data requires the use of a custom domain table or a custom database table. This section represents the location of this data using Email_Aggregate as the name of that data source.
To extend the Customer_Attributes table for marketability
Open the Customer_Attributes table in Edit mode.
Add a table block for email addresses that returns the field
marketable_email
:marketability_email_table AS ( SELECT um.amperity_id ,CASE WHEN ea.email IS NOT NULL AND (email_permission_status_brand1 = 1 OR email_permission_status_brand2 = 1 OR email_permission_status_brandetc = 1 ) THEN true ELSE false END AS marketable_email FROM Email_Aggregate ea JOIN Unified_Merged um ON ea.email = um.email GROUP BY um.amperity_id ,CASE WHEN ea.email IS NOT NULL AND (email_permission_status_brand1 = 1 OR email_permission_status_brand2 = 1 OR email_permission_status_brandetc = 1 ) THEN true ELSE false END ),
where each
email_permission_status_brand
entry is updated for your brands andEmail_Aggregate ea
references the correct table from which this data is available.Add a table block for physical addresses that returns the field
marketable_address
:marketability_address_table AS ( WHEN address IS NOT NULL AND address_permissioned_status_each_brand IS NOT NULL THEN true ELSE false END AS marketable_address, ),
where
address_permissioned_status_each_brand
represents the field in the data source that identifies if a customer has given permission to be contacted at their physical address. Extend this block for each individual brand, as necessary. For example:AND (address_permissioned_status_brand1 IS NOT NULL OR address_permissioned_status_brand2 IS NOT NULL OR address_permissioned_status_brandetc IS NOT NULL ) THEN true
Add a table block for phone numbers that returns the field
marketable_phone
:marketability_phone_table AS ( WHEN phone IS NOT NULL AND phone_permissioned_status_each_brand IS NOT NULL THEN true ELSE false END AS marketable_phone, ),
where
phone_permissioned_status_each_brand
represents the field in the data source that identifies if a customer has given permission to be contacted at their phone number. Extend this block for each individual brand, as necessary. For example:AND (phone_permissioned_status_brand1 IS NOT NULL OR phone_permissioned_status_brand2 IS NOT NULL OR phone_permissioned_status_brandetc IS NOT NULL ) THEN true
Add a series of CASE statements for marketability to the
customer_attributes_final
table, similar to:,mp.marketable_phone ,CASE WHEN me.marketable_email IS NOT NULL THEN me.marketable_email ELSE false END AS marketable_email ,CASE WHEN ma.marketable_address IS NOT NULL THEN ma.marketable_address ELSE false END AS marketable_address ,CASE WHEN me.marketable_email = true OR ma.marketable_address = true OR mp.marketable_phone = true THEN true ELSE false END AS marketable_global
Left join the Amperity ID in the marketability tables to the
customer_attributes_final
table:LEFT JOIN marketability_email_table me ON ub.amperity_id = me.amperity_id LEFT JOIN marketability_phone_table mp ON ub.amperity_id = mp.amperity_id LEFT JOIN marketability_address_table ma ON ub.amperity_id = ma.amperity_id
Validate the Customer_Attributes table, and then click Next.
Verify the columns are correct, and then click Activate.
Run the customer 360 database to update the table.
Employees¶
An employee is someone who has worked for your company, either for the parent organization or for a specific brand. An extension for employees must add the following columns to the Customer_Attributes table:
is_employee
is_employee_current
To extend the Customer_Attributes table for employees
Open the Customer_Attributes table in Edit mode.
Add a table block for all employees that returns the field
is_employee
:employee_table AS ( SELECT t.amperity_id ,CASE WHEN t.employee_flag = true OR ea.employee_flag = 1 THEN true ELSE false END AS is_employee FROM Transaction_Aggregate t JOIN Merged_Customers mc ON t.amperity_id = mc.amperity_id JOIN Email_Aggregate ea ON mc.email = ea.email ),
where
Transaction_Aggregate t
andEmail_Aggregate ea
reference the correct tables from which this data is available.Add a table block that identifies current employees and returns the field
is_employee_current
:employee_current_table AS ( SELECT t.amperity_id ,CASE WHEN t.employee_flag = true OR a.employee_flag = 1 AND (YEAR(ut.order_datetime) >= 2020) THEN true ELSE false END AS is_employee_current FROM Transaction_Aggregate t JOIN Unified_Transactions ut ON t.amperity_id = ut.amperity_id JOIN Merged_Customers mc ON t.amperity_id = mc.amperity_id JOIN Email_Aggregate a ON mc.email = a.email ),
where
Transaction_Aggregate t
andEmail_Aggregate ea
reference the correct tables from which this data is available andAND (YEAR(ut.order_datetime) >= 2020) THEN true
is updated to represent the time period you want to use to identify current employees.Add a series of CASE statements for employees to the
customer_attributes_final
table:,CASE WHEN e.is_employee IS NOT NULL THEN e.is_employee ELSE false END AS is_employee ,CASE WHEN ec.is_employee_current IS NOT NULL THEN ec.is_employee_current ELSE false END AS is_employee_current
Left join the Amperity ID in the employee tables to the
customer_attributes_final
:LEFT JOIN employee_table e ON ub.amperity_id = e.amperity_id LEFT JOIN employee_current_table ec ON ub.amperity_id = ec.amperity_id
Validate the Customer_Attributes table, and then click Next.
Verify the columns are correct, and then click Activate.
Run the customer 360 database to update the table.
Gift items¶
A gift is an item that was purchased for someone else. Common indicators of gift giving include:
Different bill-to and ship-to physical addresses
A printed gift receipt
A request for an item to be flagged as a gift in an online transaction
An item that has been gift wrapped
A purchase that was made from a gift, baby, or wedding registry
An extension for gift givers must add the following column to the Customer_Attributes table:
is_gift_giver
To extend the Customer_Attributes table for gift items
Open the Customer_Attributes table in Edit mode.
Add a table block for all employees that returns the field
is_employee
:gift_giver_table AS ( SELECT amperity_id ,CASE WHEN gift_indicator_a = 1 OR address_indicator_b = true THEN true ELSE false END AS is_gift_giver FROM Transaction_Aggregate WHERE amperity_id IS NOT NULL ),
where
Transaction_Aggregate
references the correct tables from which this data is available and wheregift_indicator_a
andaddress_indicator_b
represent conditions that are available in theTransaction_Aggregate
that identify when items were purchased as gifts and/or when items were shipped to addresses that are not associated to a customer’s Amperity ID. Update the conditions in theCASE
statement for how your tenant provides this information.Add a CASE statement for gift givers to the
customer_attributes_final
table:,CASE WHEN gg.is_gift_giver IS NOT NULL THEN gg.is_gift_giver ELSE false END AS is_gift_giver
Left join the Amperity ID in the gift givers table to the
customer_attributes_final
table:LEFT JOIN gift_giver_table gg ON ub.amperity_id = gg.amperity_id
Validate the Customer_Attributes table, and then click Next.
Verify the columns are correct, and then click Activate.
Run the customer 360 database to update the table.
Businesses¶
A business could represent an entity that is acting like a customer, but on behalf of many people (such as purchasing items for employees), or it may be an address to which a customer ships an item (instead of their physical home address). An extension for businesses must add the following column to the Customer_Attributes table:
is_business
To extend the Customer_Attributes table for businesses
Open the Customer_Attributes table in Edit mode.
Add a table block for businesses that returns the field
is_business
:business_table AS ( SELECT amperity_id ,CASE WHEN household_size > 12 AND address2 IS NULL THEN true ELSE false END AS is_business FROM Merged_Households ),
where
Merged_Households
represents a table that identifies customers by their physical address andhousehold_size > 12
is the threshold at which a household is considered to be a business.Add a CASE statement for business to the
customer_attributes_final
table:,CASE WHEN b.is_business IS NOT NULL THEN b.is_business ELSE false END AS is_business
Left join the Amperity ID in the business table to the
customer_attributes_final
table:LEFT JOIN business_table b ON ub.amperity_id = b.amperity_id
Validate the Customer_Attributes table, and then click Next.
Verify the columns are correct, and then click Activate.
Run the customer 360 database to update the table.
Resellers¶
A reseller is a customer that is associated with a high volume of transactions. A reseller is generally not the type of customer that should be associated with marketing campaigns. An extension for resellers must add the following column to the Customer_Attributes table:
is_reseller
To extend the Customer_Attributes table for resellers
Open the Customer_Attributes table in Edit mode.
Add a table block for resellers that returns the field
is_reseller
:reseller_table AS ( SELECT amperity_id ,CASE WHEN early_repeat_purchaser = true AND lifetime_order_frequency > 50 THEN true ELSE false END AS is_reseller FROM Transaction_Attributes ),
where
> 50
represents the order frequency above which indicates a reseller purchase volume for your tenant.Add a CASE statement for resellers to the
customer_attributes_final
table:,CASE WHEN r.is_reseller IS NOT NULL THEN r.is_reseller ELSE false END AS is_reseller
Left join the Amperity ID in the resellers table to the
customer_attributes_final
table:LEFT JOIN reseller_table r ON ub.amperity_id = r.amperity_id
Validate the Customer_Attributes table, and then click Next.
Verify the columns are correct, and then click Activate.
Run the customer 360 database to update the table.
Customer types and classifications¶
Depending on the updates made for marketability, employees (current and/or historical), gift items, businesses, and resellers, corresponding updates need to be made to the customer classifications CASE
statement.
The following example shows all of the possible updates:
,CONCAT(
CASE
WHEN e.is_employee IS NOT NULL
AND e.is_employee != false
THEN 'is_employee'
ELSE ''
END
,CASE
WHEN ec.is_employee_current IS NOT NULL
AND ec.is_employee_current != false
THEN 'is_employee_current'
ELSE ''
END
,CASE
WHEN r.is_reseller IS NOT NULL
AND r.is_reseller != false THEN 'is_reseller'
ELSE ''
END
,CASE
WHEN b.is_business IS NOT NULL
AND b.is_business != false
THEN 'is_business'
ELSE ''
END
,CASE
WHEN gg.is_gift_giver IS NOT NULL
AND gg.is_gift_giver != false
THEN 'is_gift_giver'
ELSE ''
END
,CASE
WHEN test.is_test_account IS NOT NULL
AND test.is_test_account != false
THEN 'is_test_account'
ELSE ''
END
,CASE
WHEN o.is_outlier IS NOT NULL
AND o.is_outlier != false
THEN 'is_outlier'
ELSE ''
END
) AS customer_classifications
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: |
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:
|
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. |