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

1. From the Database Editor, click Add Table.

2. Name the table “Customer_Attributes”.

3. Set the build mode to SQL.

4. Click Apply template, and then select Customer_Attributes.

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

6. Click Validate to verify the SQL runs without error.

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

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

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

10. From the Table Semantics drop-down, select Customer_Attributes.

11. Click Save.

SQL query

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

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

• If customers can be contacted

• If customers have provided consent for marketing campaigns

• If customers are employees (current or historical)

• If transaction items were gifts

• If a customer is also a business or reseller

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

• If a customer’s transaction behavior is an outlier

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

Source tables for customer attributes include:

1. Merged_Customers

2. Transaction_Attributes

3. Unified_Customer

4. Unified_Transactions

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

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

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

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

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

• Define threshold for lifetime order revenue

• Define test accounts

• Adjust outlier threshold

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:

• Marketability

• Employees (current and/or historical)

• Gift items

• Businesses

• Resellers

• Customer types and classifications

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

1. Open the Customer_Attributes table in Edit mode.

2. 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 and Email_Aggregate ea references the correct table from which this data is available.

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

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

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

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

7. Validate the Customer_Attributes table, and then click Next.

8. Verify the columns are correct, and then click Activate.

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

1. Open the Customer_Attributes table in Edit mode.

2. 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 and Email_Aggregate ea reference the correct tables from which this data is available.

3. 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 and Email_Aggregate ea reference the correct tables from which this data is available and AND (YEAR(ut.order_datetime) >= 2020) THEN true is updated to represent the time period you want to use to identify current employees.

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

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

6. Validate the Customer_Attributes table, and then click Next.

7. Verify the columns are correct, and then click Activate.

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

1. Open the Customer_Attributes table in Edit mode.

2. 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 where gift_indicator_a and address_indicator_b represent conditions that are available in the Transaction_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 the CASE statement for how your tenant provides this information.

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

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

5. Validate the Customer_Attributes table, and then click Next.

6. Verify the columns are correct, and then click Activate.

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

1. Open the Customer_Attributes table in Edit mode.

2. 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 and household_size > 12 is the threshold at which a household is considered to be a business.

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

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

5. Validate the Customer_Attributes table, and then click Next.

6. Verify the columns are correct, and then click Activate.

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

1. Open the Customer_Attributes table in Edit mode.

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

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

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

5. Validate the Customer_Attributes table, and then click Next.

6. Verify the columns are correct, and then click Activate.

7. 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: "is_reseller, is_outlier" or "is_test_account". A value of " " indicates that all classifications are false.
customer_type	String	The type of user: “customer” or “prospect”. A customer has a historical revenue relationship with a brand. A prospect does not have a revenue relationship with a brand.
historical_purchaser_lifecycle_status	String	The status for a customer, based on their history and recency of interactions a brand. Possible values: “active”, “lapsed”, and “dormant”. Customer states are defined as “active”, “lapsed”, “dormant”, and “prospect”. Purchase behaviors assigned across a 5 year window. A customer who has purchased within the previous 365 days (1 year) is assigned to “active” and within the previous 730 days (2 years) is assigned to “lapsed”. A customer who has not purchased within 2 years is assigned to “dormant”.
historical_prospect_lifecycle_status	String	The status for a customer who does not have a purchase history with your brand. A prospect is a potential customer to whom you may want to conduct marketing activity. Possible values: “prospect”.
is_business	Boolean	A flag that indicates if a customer has purchased items for business use.
is_employee	Boolean	A flag that indicates if a customer has been an employee of the brand at any time.
is_employee_current	Boolean	A flag that indicates if a customer is a current employee of the brand.
is_gift_giver	Boolean	A flag that indicates if a customer has purchased items as gifts.
is_outlier	Boolean	A flag that indicates if the customer has abnormally high purchase behaviors in comparison to other purchasers.
is_reseller	Boolean	A flag that indicates if the customer is a known or likely reseller of a product. Tip A reseller should be defined as an early repeat purchaser with a high lifetime order frequency. Use the following fields from the Transaction_Attributes table to define a reseller: early_repeat_purchaser (set to true) lifetime_order_frequency (define as a threshold, such as > 50)
is_test_account	Boolean	A flag that indicates if the customer is a known test account for a brand.
marketable_address	Boolean	A flag that indicates if a customer has a marketable physical mailing address and has given permission to be contacted.
marketable_email	Boolean	A flag that indicates if a customer has a marketable email mailing address and has given permission to be contacted.
marketable_global	Boolean	A flag that indicates if a customer has a marketable phone number, email address, or physical mailing address and has given permission to be contacted.
marketable_phone	Boolean	A flag that indicates if a customer has a marketable phone number and has given permission to be contacted.