Points Fact Tables
Points represent loyalty points of an organization that are issued to the loyalty customers through different sources such as Loyalty Program, Data Import (import profiles) and Member Care (goodwill points). Customers can redeem their points against transactions. Given below are the fact tables associated with the points data:
Points Fact Table
This table captures events related to allocation and deduction of points.
Databricks Table Name: points
Points - Entity Relationship Diagram (ERD)
Zoom in the table for more clarity. Click the table title to view its details.
Note
The points fact and points_redemption_summary fact are linked and share the same ERD.
Points Fact Table
Column Name | Data Type | Description | Linked Table | Availability for Export in the Template |
|---|---|---|---|---|
allocated_points | double | Points awarded for a specific event. The points are awarded either at the bill level, line item level, or customer level. | Yes; Measure Name: Allocated Points | |
auto_update_time_pa | bigint | Date and time when the corresponding record in the points awarded (PA) source table was last updated. It is in the Unix timestamp format. | Yes; Measure Name: Auto Update Time Pa | |
auto_update_time_pabp | bigint | Date and time when the corresponding record in the Points Awarded Bill Promotion (PABP) source table was last updated. It is in the Unix timestamp format. | Yes; Measure Name: Auto Update Time Pabp | |
auto_update_time_pacp | bigint | Date and time when the corresponding record in the Points Awarded Customer Promotion (PACP) source table was last updated. It is in the Unix timestamp format. | Yes; Measure Name: Auto Update Time Pacp | |
auto_update_time_pal | bigint | Date and time when the corresponding record in the Points Awarded Lineitem (PAL) source table was last updated. It is in the Unix timestamp format. | Yes; Measure Name: Auto Update Time Pal | |
auto_update_time_palp | bigint | Date and time when the corresponding record in the Points Awarded Lineitem Promotion (PALP) source table was last updated. It is in the Unix timestamp format. | Yes; Measure Name: Auto Update Time Palp | |
auto_update_time_pd | bigint | Date and time when the corresponding record in the Points Deductions (PD) source table was last updated. It is in the Unix timestamp format. | Yes; Measure Name: Auto Update Time Pd | |
awarded_ref_id | bigint | It is the identifier that is created when a point is awarded. | Yes; Measure Name: Awarded Ref Id | |
bill_id | bigint | Unique identifier for a transaction (loyalty log id or bill id) for which points are awarded or redeemed. This is populated primarily from the Points Awarded (PA), Points Awarded Lineitem (PAL), Points Awarded Lineitem Promotion (PALP), Points Awarded Bill Promotion (PABP) tables available at the source. | Yes; Measure Name: Bill Id | |
bill_lineitem_id | string | The line item id against which points have been allocated. | Yes; Measure Name: Bill Lineitem Id | |
bill_number | string | System-generated bill number for which points are awarded or redeemed. If there is no valid bill_id in the source table, this field would be empty. | Yes; Measure Name: Bill Number | |
deducted_points | double | The number of points that have been redeemed, returned, or expired and thus deducted from the total points. | Yes; Measure Name: Deducted Points | |
deducted_ref_id | bigint | The original redemption or transfer deduction id against which a reversal has occurred. | Yes; Measure Name: Deducted Ref Id | |
deduction_summary_id | bigint | Identifier to group deducted points related to a single event. It is the primary key of the PRS (Point Redemption Summary) table. | points_redemption
| Yes; Measure Name: Deduction Summary Id |
dim_awarded_date_id | bigint | Date when points were awarded to the customer. | Yes; Dimension Name: Awarded date > Date | |
dim_awarded
| bigint | Date when the awarded points are set to expire. | Yes; Dimension Name: Awarded Expiry Date > Date | |
dim_awarded_program_id | bigint | Identifier for the loyalty program associated with the awarded points. | Yes; Dimension Name: Awarded Program > Program Id | |
dim_awarded
| bigint | Identifier assigned to the point-of-sale (POS) terminal within a store, where the points were awarded. It distinguishes one checkout location from another within the same store. | Yes; Dimension Name: Awarded Zone Till > Till Id | |
dim_category_id | bigint | Identifier which indicates the type of points awarded or deducted. The points categories can be - regular points, promised points, alternate currencies, trackers, and external trigger based points. | Yes; Dimension Name: Category > Category Id | |
dim_deduction_type_id | bigint | Identifier indicating the type of deduction. Possible values are - cancelled, expired, migrated, redeemed, returned, etc. | Yes; Dimension Name: Deduction Type > Id | |
dim_event_date_id | bigint | Date when the points were allocated/ deducted. | Yes; Dimension Name: Date > Date | |
dim_event_log_id | bigint | Unique identifier for the points event. Corresponds to the id in the event_log table and is mapped to the point awarded (PA) / points deducted (PD) table. | Yes; Measure Name: Dim Event Log Id | |
dim_event_program_id | bigint | Identifier of the loyalty program associated with the points event. | Yes; Dimension Name: Event Program > Program Id | |
dim_event_time_id | bigint | Time when the points were allocated/ deducted. | Yes; Dimension Name: Time > Time Id | |
dim_event_user_id | bigint | Identifier of the user, set internally by Capillary. | Yes; Dimension Name: User > User Id | |
dim_event_zone_till_id | bigint | Identifier assigned to the point-of-sale (POS) terminal within a store, where the points event occurred. It distinguishes one checkout location from another within the same store. | Yes; Dimension Name: Store Hierarchy > Till Id | |
dim_expiry_date_id | bigint | Date when the allocated points are set to expire. | ||
dim_expiry_time_id | bigint | Time when the allocated points are set to expire. | ||
dim_latest_updated
| bigint | Date when the data corresponding to this event/ row is changed in the source table. | Yes; Dimension Name: Latest Updated Date > Date | |
dim_latest_updated
| bigint | Time when the data corresponding to this event/ row is changed in the source table. | Yes; Dimension Name: Latest Updated Time > Time Id | |
dim_points_awarded
| bigint | Identifier for the type of points that are awarded. Possible values are - bill promotion, line item, line item promotion, and customer promotion. This is the primary key of the table. | points_awarded
| Yes; Dimension Name: Points Awarded Type > Id |
dim_points_event_type_id | bigint | Identifier for the type of event, indicating whether points were awarded or deducted. This is the primary key of the table. | Yes; Dimension Name: Points Event Type > Id | |
dim_points_promotion_id | bigint | Identifier for the promotion against which points were allocated, redeemed, or expired. | Yes; Dimension Name: Points Promotion > Id | |
dim_redemption
| bigint | Identifier of the loyalty program associated with the points redemption. | ||
event_id | bigint | Unique identifier of the points event. It is the primary key of the table. | Yes; Measure Name: Event Id | |
external_reference_number | string | Refers to the number, unique to each org, linked to the Points Redemption Summary (PRS) table. It is populated from the API Payload. | Yes; Measure Name: External Reference Number | |
redemption_bill_id | bigint | Identifier for a transaction (loyalty log id or bill id) for which the points are redeemed. It is generated by Capillary. | Yes; Measure Name: Redemption Bill Id | |
redemption_bill_number | string | Bill number for which the points are redeemed. If the source data does not provide this number, the system shows 'Not Captured'. | Yes; Measure Name: Redemption Bill Number | |
redemption_id | string | A unique 6-character long string for each redemption or reversal request, auto-generated by the Loyalty System. | Yes; Measure Name: Redemption Id | |
dim_awarded_points
| bigint | Identifier for the promotion associated with the awarded points. | Yes; Dimension Name: Awarded Points Promotion > Id | |
year | int | Year when the points were awarded/ deducted. | Yes; Dimension Name: Year | |
dim_old_category_id | bigint | Refers to point_category_id in individual points tables, indicating the category of points, such as redeemable, tracker, or delayed accrual. | ||
event_log_id | bigint | Event log id in which the point is getting awarded, from the event log table. | Yes; Measure Name: Event Log Id |
Points_redemption_summary Fact Table
This table captures the event where the customer redeems their points. The table captures the date and time of redemption, the loyalty program associated with the points, the category to which the points belong, the bill number associated with the points redemption, etc.
Databricks Table Name: points_redemption_summary
Points Redemption Summary - Entity Relationship Diagram (ERD)
Zoom in the table for more clarity. Click the table title to view its details.
Note
The points fact and points_redemption_summary fact are linked and share the same ERD.
Points Redemption Summary Fact Table
Column Name | Data Type | Description | Linked Table | Available for Export in Template |
|---|---|---|---|---|
auto_update_time | bigint | Date and time when the corresponding record in the points redemption summary table available at the source was last updated. It is in the Unix timestamp format. | _ | Yes; Measure Name: Auto update time |
dim_event_date_id | bigint | Date when the points were redeemed. | Yes; Dimension Name: Date > Date | |
dim_event_user_id | bigint | Identifier of the user, set internally by Capillary. | Yes; Dimension Name: User > User Id | |
dim_event_zone_till_id | bigint | Identifier assigned to the point-of-sale (POS) terminal within a store, where the points were redeemed. It distinguishes one checkout location from another within the same store. | Yes Dimension: Concept_Hierarchy > Till_Id | |
dim_latest_updated_date_id | bigint | Date when the data corresponding to this event/ row is changed in the source table. | Yes; Dimension Name: Latest Updated Date > Date | |
dim_latest_updated_time_id | bigint | Time when the data corresponding to this event/ row is changed in the source table. | Yes; Dimension Name: Latest Updated Time > Time Id | |
dim_redemption_program_id | bigint | Identifier of the loyalty program associated with the points redemption. | Yes; Dimension Name: Program > Program Id | |
dim_redemption_type_id | string | Identifier for the points redemption type. Possible values are redemption, reversal, group_redemption, and reversal_on_return. | Yes; Dimension Name: Redemption Type > Id | |
event_id | bigint | Unique identifier of the points redemption event, correlating to Deduction_Summary_Id in the Points Fact table. It is the primary key of this table. | Yes; Measure Name: Event Id | |
notes | string | Any additional information or comments provided during the redemption process. | _ | Yes; Measure Name: Notes |
point_category_id | bigint | It refers to the Point category Id column of PRS table. This id comes from points_category source table, which indicates whether the points category is main, delayed accrual, trigger based or trackers. In case of Group Redemption, this ID will be set to -1. | _ | Yes; Measure Name: Points Category Id |
points_redeemed | double | Total points redeemed in a particular redemption. The value is populated from the points_redemption_summary table available at the source. | _ | Yes; Measure Name: Points Redeemed |
points_redemption_time | bigint | Time when the points were redeemed, populated from the points_redemption_summary table available at the source. | _ | Yes; Measure Name: Points Redemption Time |
redemption_bill_id | bigint | Identifier for a transaction (loyalty log id or bill id) for which the points are redeemed. It is generated by Capillary. | Yes; Measure Name: Redemption Bill Id | |
redemption_bill_number | string | Bill number for which the points are redeemed. If the source data does not provide this number, the system shows 'Not Captured'. | Yes; Measure Name: Redemption Bill Number | |
source_type | string | Source from where the redemption data is collected (API or redemption data import). | _ | Yes; Measure Name: Source Type |
year | int | Year when the points were redeemed. | _ | Yes; Dimension Name: Year |
Points_expiry_reminder_info Fact Table
This table captures the event where communication is made with the customer to remind them about their points, which would expire after a certain date. It captures the date and time of the communication, the channel used, the number of points expiring, and the date and time the points will expire, among other details.
Databricks Table Name: points_expiry_reminer_info
Points Expiry Reminder Info - Entity Relationship Diagram (ERD)
Zoom in the table for more clarity. Click the table title to view its details.
Points Expiry Reminder Info Fact Table
Column Name | Data Type | Description | Linked Table | Availability for Export in the Template |
|---|---|---|---|---|
auto_update_time
| bigint | Date and time when the corresponding record in the points reminder expiry info Fact table available at the source was last updated. It is in Unix timestamp format. | Yes; Measure Name: Auto Update Points Expiry Reminder | |
dim_communication
| bigint | Indicates the channel used for communicating with the customer. Possible values are - SMS, email, voicemail, Wechat, Facebook, Viber, etc. | communication
| Yes; Dimension Name : Communication Channel > Id |
dim_event_date_id | bigint | Date when the points expiry reminder is sent. | Yes; Dimension Name : Date > Date | |
dim_event_time_id | bigint | Time when the points expiry reminder is sent. | Yes; Dimension Name : Time > Time Id | |
dim_event_user_id | bigint | Identifier for the customer who has performed the redemption or reversal, part of the points_redemption_summary fact. | Yes; Dimension Name : User > User Id | |
dim_expiry_date_id | bigint | Date when the allocated points are set to expire. | Yes; Dimension Name : Expiry Date > Date | |
dim_expiry_time_id | bigint | Time when the allocated points are set to expire. | Yes; Dimension Name : Expiry Time > Time Id | |
dim_latest_updated_date_id | bigint | Date when the data corresponding to this event/row is changed in the source table. | Yes; Dimension Name : Latest Updated Date > Date | |
dim_latest_updated_time_id | bigint | Time when the data corresponding to this event/row is changed in the source table. | Yes; Dimension Name : Latest Updated Time > Time Id | |
points_expiring | double | Total number of points that are set to expire, for which the reminder is sent. | Yes; Measure Name: Points Expiring | |
reminded_before_days | int | The number of days in advance a reminder should be sent before the points are set to expire. | Yes; Measure Name: Reminded Before Days | |
reminder_id | bigint | Unique identifier for the table. It is the primary key of this table. | Yes; Measure Name: Reminder Id | |
year | int | Year in which the points expiry reminder is sent. | Yes; Dimension Name : Year |
Use cases
Reporting of points data helps brands understand how loyalty points are distributed and used. By looking at this data, brands can see patterns in how points are redeemed and issued, track trends in points activity, find which loyalty programs are most utilized, and understand customer behavior related to points.
Following are two use cases, where points promotion data can be used in reporting:
Streak and Milestone Based Promotions
- A brand sets up a promotion tied to a streak or milestone, where the brand issues points to the customers after they achieve a streak/ milestone.
- The brand wants to track how many customers have achieved the streak or milestone and earned promotional points.
- This is done through the custom fields in the
points_promotionsdimension table. - The brand defines the target id in custom field which is mapped to the target table in the milestones module. This connects the milestone and the points promotion data.
Example: Suppose a brand creates a milestone requiring customers to spend $500 in a month to earn extra points. The brand adds a custom field in points_promotions where the target ID of this milestone is mapped to the corresponding target in the milestones module. This allows tracking of all customers who achieved the $500 spend milestone and were awarded points based on their milestone achievement.
Behavioral Event Based Promotions
- A brand creates a promotion linked to a specific behavioral event, such as customers earning points for completing actions (e.g., updating their profile picture or writing product reviews).
- The brand can track how many customers have participated in the event and how many points have been issued based on their actions.
- This is done by mapping the
behavioral_event_namein thepoints_promotionsdimension table, which shows the event to which the promotion is linked. - The brand can therefore track how many customers have completed the event and earned points.
Example: Suppose a brand offers 50 points to customers who update their profile picture on their account. The behavioral_event_name in the points_promotions dimension table links this promotion to the "Profile Picture Update" event. This allows the brand to track how many customers updated their profile picture, and were awarded points for this behavioral event; and how many total points were issued for this promotion.
Updated 2 days ago