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:
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.
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 dropdown, 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 dropdown, 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.
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:
|
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. |
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. |