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:

The fk-loyalty-id semantic tag is applied to the same source field as the loy/loyalty-id field.
The loy/email semantic tag is applied to fields that contain email addresses.
The loy/birthdate semantic tag is applied to fields that contain birthdates.

(Source tables for loyalty profiles should not be made available to Stitch.)

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

From the Customer 360 page, under All Databases, select the menu for the customer 360 database, and then click Edit.
From the Database Editor, click Add Table.
Name the table “Unified Loyalty”.
Set the build mode to Passthrough.
From the Source Table drop-down, select the Unified Loyalty table.
Click Next.
Add a table description. This enables a tooltip that is visible from other areas in Amperity.
From the Table Semantics drop-down, select Unified Loyalty.
Under Version History, select Enable table version history.
Click Save.

Query examples¶

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

Retention analysis
Value analysis

Tip

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

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: The fk-loyalty-id semantic tag is applied to the same source field as the loy/loyalty-id field. The loy/email semantic tag is applied to fields that contain email addresses. The loy/birthdate semantic tag is applied to fields that contain birthdates. (Source tables for loyalty profiles should not be made available to Stitch.)
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.