Unified Loyalty table

The Unified Loyalty table contains a row for every customer who belongs to your loyalty program, unique by combination of Amperity ID and loyalty ID.

Note

An Amperity ID may be associated with multiple loyalty programs or may be associated with multiple accounts within the same loyalty program.

Note

Loyalty profiles are unique by Amperity ID when:

  1. The fk-loyalty-id semantic tag is applied to the same source field as the loy/loyalty-id field.

  2. The loy/email semantic tag is applied to fields that contain email addresses.

  3. The loy/birthdate semantic tag is applied to fields that contain birthdates.

Add table

The Unified Loyalty table is an optional table for the customer 360 database. It may be built as a passthrough table.

To add the Unified Loyalty table

  1. From the Customer 360 page, under All Databases, select the menu for the customer 360 database, and then click Edit.

  2. From the Database Editor, click Add Table.

  3. Name the table “Unified Loyalty”.

  4. Set the build mode to Passthrough.

  5. From the Source Table drop-down, select the Unified Loyalty table.

  6. Click Next.

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

  8. From the Table Semantics drop-down, select Unified Loyalty.

  9. Under Version History, select Enable table version history.

  10. Click Save.

Query examples

The following examples show how you can analyze retention rates and customer value for your loyalty program.

Tip

Add these queries directly to your tenant from the Queries page. Click Create, select Folder, and then choose from the drop-down list Analytics Reports.

Retention analysis

Use a query to analyze customer retention rates by loyalty status. The following example finds active members in your brand’s loyalty program based on enrollment time, and then builds retention rates by:

  • Churn for customers who belong to your brand’s loyalty program vs. churn for customers who do not

  • Differences by when customers joined the loyalty program

  • Customer value differences among groups

WITH loyalty_members AS (
  SELECT DISTINCT
    amperity_id
    ,CASE
      WHEN latest_opt_in_datetime > (current_date - interval '1' year)
      THEN 'Joined in Last Year'
      ELSE 'Joined Before Last Year'
    END enrollment_period
  FROM Unified_Loyalty
  WHERE is_opted_in
)

SELECT
  CASE
    WHEN loy.amperity_id IS NULL
    THEN 'Not in Loyalty'
    ELSE 'In Loyalty'
  END AS Loyalty_status
  ,enrollment_period
  ,CASE
    WHEN latest_order_datetime >= (current_date - interval '1' year)
    AND first_order_datetime < (current_date - interval '1' year)
    THEN 'Retained'
    WHEN latest_order_datetime <= (current_date - interval '1' year)
    THEN 'Churned'
    WHEN first_order_datetime >= (current_date - interval '1' year)
    THEN 'Acquired Last Year'
  END AS retention_status
  ,COUNT(DISTINCT tae.amperity_id) AS number_of_customers
  ,1.00*COUNT(DISTINCT tae.amperity_id) / SUM(COUNT(DISTINCT tae.amperity_id)) OVER (PARTITION BY
    CASE
      WHEN loy.amperity_id IS NULL
      THEN 'Not in Loyalty'
      ELSE 'In Loyalty'
    END,
    enrollment_period
  ) AS pct_of_loyalty_status_and_enrollment_period
  ,SUM(l12m_order_revenue) AS L12m_revenue
  ,SUM(l12m_order_revenue) / COUNT(DISTINCT tae.amperity_id) AS L12M_revenue_per_customer
  ,1.00 * SUM(l12m_order_frequency) / COUNT(DISTINCT tae.amperity_id) AS L12M_orders_per_customer
  ,CASE
    WHEN SUM(l12m_order_frequency) = 0 THEN Null
    ELSE SUM(l12m_order_revenue) / SUM(l12m_order_frequency)
  END AS L12M_average_order_value
FROM (
  SELECT *
  FROM Transaction_Attributes_Extended
  WHERE first_order_datetime > (current_date - interval '2' year)
) tae
LEFT JOIN loyalty_members loy
ON loy.amperity_id = tae.amperity_id
GROUP BY 1,2,3
ORDER BY 2,3,1

Value analysis

Use a query to analyze customer value by loyalty status. The following example finds active members in your brand’s loyalty program based on enrollment time, and then compares customer value by loyalty status and enrollment period by:

  • Revenue by customer during the previous 12 months

  • Orders by customer during the previous 12 months

  • Average order value for all purchases in the previous 12 months

 WITH loyalty_members AS (
  SELECT DISTINCT
    amperity_id
    ,CASE
      WHEN latest_opt_in_datetime > (current_date - interval '1' year)
      THEN 'Joined in Last Year'
      ELSE 'Joined Before Last Year'
    END enrollment_period
  FROM Unified_Loyalty
  WHERE is_opted_in
)

SELECT
  CASE
    WHEN loy.amperity_id IS NULL
    THEN 'Not in Loyalty'
    ELSE 'In Loyalty'
  END AS Loyalty_status
  ,enrollment_period
  ,COUNT(DISTINCT tae.amperity_id) AS number_of_customers
  ,SUM(l12m_order_revenue) AS L12m_revenue
  ,SUM(l12m_order_revenue) / COUNT(DISTINCT tae.amperity_id) AS L12M_revenue_per_customer
  ,1.00 * SUM(l12m_order_frequency) / COUNT(DISTINCT tae.amperity_id) AS L12M_orders_per_customer
  ,SUM(l12m_order_revenue) / SUM(l12m_order_frequency) AS L12M_average_order_value
FROM Transaction_Attributes_Extended tae
LEFT JOIN loyalty_members loy
ON loy.amperity_id=tae.amperity_id
GROUP BY 1,2
ORDER BY 1,2

Column reference

The Unified Loyalty 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

Loyalty profiles are unique by Amperity ID when:

  1. The fk-loyalty-id semantic tag is applied to the same source field as the loy/loyalty-id field.

  2. The loy/email semantic tag is applied to fields that contain email addresses.

  3. The loy/birthdate semantic tag is applied to fields that contain birthdates.

Birthdate

Date

The date of birth for the customer who belongs to the loyalty profile.

Current Balance

Integer

The customer’s current rewards balance.

Current Balance Expiration Datetime

Datetime

The date and time at which a customer’s current rewards balance will expire.

Current Tier

String

The name of the rewards tier to which a customer belongs.

Current Tier Expiration Datetime

Datetime

The date and time at which a customer’s membership in their current rewards tier will end.

Current Tier Start Datetime

Datetime

The date and time at which a customer’s membership in their current rewards tier started.

Email

String

The email address that is associated with a loyalty ID.

Also in: Unified Loyalty Events

Is Opted-in

Boolean

Required.

Indicates if the customer associated with the loyalty ID has given consent to being contacted by your loyalty program.

Latest Opt-out Datetime

Datetime

The date and time at which a customer most recently opted out from being contacted by your loyalty program.

Latest Opted-in Datetime

Datetime

Required.

The date and time at which a customer most recently opted in to being contacted by your loyalty program.

Latest Update Datetime

Datetime

The date and time at which the information associated with the loyalty profile was updated.

Lifetime Balance

Integer

The lifetime reward balance associated with the loyalty ID.

Loyalty ID

String

Required.

The unique ID for a loyalty profile.

Also in: Unified Loyalty Events

Next Tier

String

The name of the next loyalty tier to which, pending points accumulation, a customer will belong.

Sign-up Channel

String

The channel through which the customer signed up for the loyalty program.

Sign-up Method

String

The method used by the customer to sign-up for the loyalty program.

Spend To Keep Tier

Decimal

The amount of money a customer must spend to stay in their current loyalty tier.

Spend To Next Tier

Decimal

The amount of money a customer must spend to move to the next loyalty tier.