Points Standard Export Templates
There are 3 export templates for points data.
Points
The Points template is based on the Points fact.
- With this template you can export the data of points related events such as points issual, and points deduction (redeemed, expired, and returned).
- 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 | Points awarded for a specific event. The points are awarded either at the bill level, line item level, or customer level. |
| auto_update_time_pa | Date and time when the Points Awarded table was last updated (Unix timestamp). |
| auto_update_time_pabp | Date and time when the Points Awarded Bill Promotion table was last updated (Unix timestamp). |
| auto_update_time_pacp | Date and time when the Points Awarded Customer Promotion table was last updated (Unix timestamp). |
| auto_update_time_pal | Date and time when the Points Awarded Line item table was last updated (Unix timestamp). |
| auto_update_time_palp | Date and time when the Points Awarded Line item Promotion table was last updated (Unix timestamp). |
| auto_update_time_pd | Date and time when the Points Deductions table was last updated (Unix timestamp). |
| awarded_ref_id | Identifier that is created when a point is awarded. |
| 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 | Date attributes. |
| 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 up to minutes. |
| User | User details such as user id, source, slab name, external id, fraud status, mobile, email, etc. |
| 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 the points were awarded or deducted. |
| Concept Hierarchy | The concept where the points were awarded or deducted. |
| OU Hierarchy | The Organisation Unit where the points were awarded or deducted. |
| deduction_type | Gives information about the type of points deduction. |
| awarded_date | 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 | Type of points which are awarded/deducted. |
| awarded_expiry_date | Date when the points are supposed to expire. |
| Awarded Program | It is the Loyalty program id. |
| Event Program | Identifier of the loyalty program associated with the points event. |
Points Redemption Summary
The Points Redemption Summary template is based on the Points Redemption Summary fact. The template 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 | A unique 6-character long string for each redemption or reversal request, auto-generated by the Loyalty System. |
| External Reference Number | Refers to the number, unique to each org, linked to the Points Redemption Summary (PRS) table. It is populated from the API Payload. |
| Event Id | Unique identifier of the points redemption event, correlating to Deduction_Summary_Id in the Points Fact 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 points redemption occurred. |
| Concept Hierarchy | The concept where the points redemption occurred. |
| Ou Hierarchy | The Organisation Unit where the points redemption 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 Points Expiry Reminder Info fact. 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. |
| expiry_time | Time when the allocated points are supposed to expire. |
| 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 about 2 months ago