Unified Loyalty table

The Unified Loyalty table has 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 dropdown, 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 dropdown, 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 dropdown 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

 1WITH loyalty_members AS (
 2  SELECT DISTINCT
 3    amperity_id
 4    ,CASE
 5      WHEN latest_opt_in_datetime > (current_date - interval '1' year)
 6      THEN 'Joined in Last Year'
 7      ELSE 'Joined Before Last Year'
 8    END enrollment_period
 9  FROM Unified_Loyalty
10  WHERE is_opted_in
11)
12
13SELECT
14  CASE
15    WHEN loy.amperity_id IS NULL
16    THEN 'Not in Loyalty'
17    ELSE 'In Loyalty'
18  END AS Loyalty_status
19  ,enrollment_period
20  ,CASE
21    WHEN latest_order_datetime >= (current_date - interval '1' year)
22    AND first_order_datetime < (current_date - interval '1' year)
23    THEN 'Retained'
24    WHEN latest_order_datetime <= (current_date - interval '1' year)
25    THEN 'Churned'
26    WHEN first_order_datetime >= (current_date - interval '1' year)
27    THEN 'Acquired Last Year'
28  END AS retention_status
29  ,COUNT(DISTINCT tae.amperity_id) AS number_of_customers
30  ,1.00*COUNT(DISTINCT tae.amperity_id) / SUM(COUNT(DISTINCT tae.amperity_id)) OVER (PARTITION BY
31    CASE
32      WHEN loy.amperity_id IS NULL
33      THEN 'Not in Loyalty'
34      ELSE 'In Loyalty'
35    END,
36    enrollment_period
37  ) AS pct_of_loyalty_status_and_enrollment_period
38  ,SUM(l12m_order_revenue) AS L12m_revenue
39  ,SUM(l12m_order_revenue) / COUNT(DISTINCT tae.amperity_id) AS L12M_revenue_per_customer
40  ,1.00 * SUM(l12m_order_frequency) / COUNT(DISTINCT tae.amperity_id) AS L12M_orders_per_customer
41  ,CASE
42    WHEN SUM(l12m_order_frequency) = 0 THEN Null
43    ELSE SUM(l12m_order_revenue) / SUM(l12m_order_frequency)
44  END AS L12M_average_order_value
45FROM (
46  SELECT *
47  FROM Transaction_Attributes_Extended
48  WHERE first_order_datetime > (current_date - interval '2' year)
49) tae
50LEFT JOIN loyalty_members loy
51ON loy.amperity_id = tae.amperity_id
52GROUP BY 1,2,3
53ORDER 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

 1 WITH loyalty_members AS (
 2  SELECT DISTINCT
 3    amperity_id
 4    ,CASE
 5      WHEN latest_opt_in_datetime > (current_date - interval '1' year)
 6      THEN 'Joined in Last Year'
 7      ELSE 'Joined Before Last Year'
 8    END enrollment_period
 9  FROM Unified_Loyalty
10  WHERE is_opted_in
11)
12
13SELECT
14  CASE
15    WHEN loy.amperity_id IS NULL
16    THEN 'Not in Loyalty'
17    ELSE 'In Loyalty'
18  END AS Loyalty_status
19  ,enrollment_period
20  ,COUNT(DISTINCT tae.amperity_id) AS number_of_customers
21  ,SUM(l12m_order_revenue) AS L12m_revenue
22  ,SUM(l12m_order_revenue) / COUNT(DISTINCT tae.amperity_id) AS L12M_revenue_per_customer
23  ,1.00 * SUM(l12m_order_frequency) / COUNT(DISTINCT tae.amperity_id) AS L12M_orders_per_customer
24  ,SUM(l12m_order_revenue) / SUM(l12m_order_frequency) AS L12M_average_order_value
25FROM Transaction_Attributes_Extended tae
26LEFT JOIN loyalty_members loy
27ON loy.amperity_id=tae.amperity_id
28GROUP BY 1,2
29ORDER BY 1,2

Column reference

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

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.