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 7 days ago