Points Standard Export Templates
There are 3 export templates for points data.
Points
The Points template is based on the fact - Points.
- With this template you can export the data of points related events such as points issual, and points deduction (redeemed, expired, and returned) including source details.
- You will get only the detailed break up of deducted points but not the summarized one.
- The template will have the filters “redeemable” and “not applicable (deducted)”, applied by default.
*Note: Points that are redeemed will be tagged to a transaction only if both the transaction and points redemption occurs within the span of +/- 15 minutes
Measures
Measure Name | Description |
---|---|
allocated_points | This is points awarded on a particular event. Can be on bill level, lineitem level or customer level |
auto_update_time_pa | Date and time when the Points Awarded table is recently updated (Unix timestamp) |
auto_update_time_pabp | Date and time when the Points Awarded Bill Promotion table is recently updated (Unix timestamp) |
auto_update_time_pacp | Date and time when the Points Awarded Customer Promotion table is recently updated (Unix timestamp) |
auto_update_time_pal | Date and time when the Points Awarded Line item table is recently updated (Unix timestamp) |
auto_update_time_palp | Date and time when the Points Awarded Line item Promotion table is recently updated (Unix timestamp) |
auto_update_time_pd | Date and time when the Points Deductions table is recently updated (Unix timestamp) |
awarded_ref_id | The reference id of the awarded points that were deducted |
bill_id | Loyalty log id or bill id is a unique identifier for a transaction for which the points are awarded or redeemed. It is populated ( primarily for transactional point award entries)from PA, PAL, PALP, PABP tables on the source side. This id has reference to the particular bill where points are awarded or redeemed. Note: PA - Points Awarded PAL - Points Awarded Line Items PALP-Points Awarded Line Item Promotion PABP -Points Awarded Bill Promotion |
bill_number | The system-generated bill number for which the points are awarded or redeemed. bill_number is populated if there is a valid bill_id populated in source loyalty side or else empty or null is populated as bill_number |
deducted_points | Number of redeemed, returned, or expired points which are deducted from the total points |
event_id | It is the id of the event occurrence and is populated from PA, PAL, PALP, PABP, PACP and PD tables on the source side. Note: PA - Points Awarded PAL - Points Awarded Line Items PALP-Points Awarded Lineitem Promotion PABP -Points Awarded Bill Promotion PACP -Points Awarded customer Promotion PD - Points Deducted |
Redemption Bill Id | Loyalty log ID or bill ID for which the points are redeemed. The billing ID is generated by Capillary's system. |
Bill Lineitem Id | It is the id against each line item where points are awarded. |
Redemption Bill Number | Bill number for which the points are redeemed. |
deduction_currency_value | Holds the currency value of the deducted points. |
Deduction Summary id | Deducted points related to a single event are grouped using a deduction summary ID for analysis. |
Dimensions
Dimension Name | Description |
---|---|
Date | Event date |
points_promotion | Contains the promotion name against which the points were allocated/redeemed/expired (the event type) Refers to the id column of points_promotions table , |
Time | Measures the timestamp when the event has occurred , it captures upto minutes and seconds is not captured. |
User | User identifier for the user to whom points have been allocated. This is an integer set internally by capillary. |
User Segments | The segment to which the user belongs |
points_event_type | Specifies if the points were awarded or deducted |
Store Hierarchy | The store where event occurred |
Concept Hierarchy | The concept where the event occurred |
OU Hierarchy | The Organisation Unit where the event occurred |
deduction_type | It gives us information about the type of deduction More details here : Points Deducted Scenarios |
awarded_date | It is the date when points are awarded to the customer. |
awarded_zone_till | Captures the till_id of the zone where points were awarded. |
latest_updated_date | Date when the records were last updated. |
latest_updated_time | Time when the records were last updated |
category | It tells the type of points which are awarded/deducted. More details here : points_category |
awarded_expiry_date | It is the date when the points expire if left unused. |
Awarded Program | It is the Loyalty program id. |
Event Program | It gives us information about the loyalty program for which the data is shown. More details here : program |
Points Redemption Summary
The Points Redemption Summary template is based on the fact - Points Redemption Summary. The fact lets your export the details of the points redemption done by the customers.
Measures
Measure Name | Description |
---|---|
Points Category Id | 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. |
Points Redeemed | Total points redeemed in a particular redemption. The value is populated from the points_redemption_summary table available at the source. |
Source Type | Source from where the redemption data is collected (API or redemption data import). |
Reference Id | - |
Redemption Bill Id | Identifier for a transaction (loyalty log id or bill id) for which the points are redeemed. It is generated by Capillary. |
Redemption Bill Number | Bill number for which the points are redeemed. If the source data does not provide this number, the system shows 'Not Captured'. |
Notes | Any additional information or comments provided during the redemption process. |
Points Redemption Time | Time when the points were redeemed, populated from the points_redemption_summary table available at the source. |
Auto Update Time | 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. |
Redemption Id | - |
External Reference Number | - |
Event Id | 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. |
Dimensions
Dimension Name | Description |
---|---|
Latest Updated Date | Date when the data corresponding to this event/row is changed in the source table. |
Latest Updated Time | Time when the data corresponding to this event/row is changed in the source table. |
Program | Loyalty program associated with the points redemption. |
User | Identifier of the user, set internally by Capillary. |
User Segments | The segment to which the user belongs. |
User Attributes | Attributes of the user. |
Store Hierarchy | The store where event occurred. |
Concept Hierarchy | The concept where the event occurred. |
Ou Hierarchy | The Organisation Unit where the event occurred. |
Date | Date of the points redemption. |
Redemption Type | Indicates the type of redemption. |
Points Expiry Reminder Info
The Points Expiry Reminder template is based on the fact - Points Expiry Reminder Info. The fact lets you export the details of points expiry reminders sent to customers through Loyalty Engine. These are automated reminders configured for points expiry.
Measures
Measure Name | Description |
---|---|
Points Expiring | The number of points that are going to expire for which reminder will be sent. |
Reminded Before Days | The number of days before which reminder needs to be sent for the points expiring. |
Auto Update Time Point Exp Reminder | Date and time when the points_reminder_expiry_info Fact is recently updated (Unix timestamp) |
Reminder Id | primary key of expiry_reminder_sent_stats_customer_level table |
Dimensions
Dimension Name | Description |
---|---|
User | User identifier for the user to whom points have expired. This is an integer set internally by capillary. |
User Segments | The segment to which the user belongs |
expiry_date | Date when the allocated points are supposed to expire if unused. |
expiry_time | Time when the allocated points are supposed to expire if unused. |
communication_channel | This refers to the communication_channel table and the id is a unique id for different communication channels. More details here : communication_channel |
Date | Measures the date when the event has occurred. In this case event is point expiry |
Time | Measures the time when the event has occurred. In this case event is point expiry |
latest_updated_date | Date when the records were last updated. |
latest_updated_time | Time when the records were last updated |
Updated 25 days ago