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 NameDescription
allocated_pointsThis is points awarded on a particular event. Can be on bill level, lineitem level or customer level
auto_update_time_paDate and time when the Points Awarded table is recently updated (Unix timestamp)
auto_update_time_pabpDate and time when the Points Awarded Bill Promotion table is recently updated (Unix timestamp)
auto_update_time_pacpDate and time when the Points Awarded Customer Promotion table is recently updated (Unix timestamp)
auto_update_time_palDate and time when the Points Awarded Line item table is recently updated (Unix timestamp)
auto_update_time_palpDate and time when the Points Awarded Line item Promotion table is recently updated (Unix timestamp)
auto_update_time_pdDate and time when the Points Deductions table is recently updated (Unix timestamp)
awarded_ref_idThe reference id of the awarded points that were deducted
bill_idLoyalty 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_numberThe 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_pointsNumber of redeemed, returned, or expired points which are deducted from the total points
event_idIt 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 IdLoyalty log ID or bill ID for which the points are redeemed. The billing ID is generated by Capillary's system.
Bill Lineitem IdIt is the id against each line item where points are awarded.
Redemption Bill NumberBill number for which the points are redeemed.
deduction_currency_valueHolds the currency value of the deducted points.
Deduction Summary idDeducted points related to a single event are grouped using a deduction summary ID for analysis.

Dimensions

Dimension NameDescription
DateEvent date
points_promotionContains the promotion name against which the points were allocated/redeemed/expired (the event type)
Refers to the id column of points_promotions table ,
TimeMeasures the timestamp when the event has occurred , it captures upto minutes and seconds is not captured.
UserUser identifier for the user to whom points have been allocated. This is an integer set internally by capillary.
User SegmentsThe segment to which the user belongs
points_event_typeSpecifies if the points were awarded or deducted
Store HierarchyThe store where event occurred
Concept HierarchyThe concept where the event occurred
OU HierarchyThe Organisation Unit where the event occurred
deduction_typeIt gives us information about the type of deduction

More details here : Points Deducted Scenarios
awarded_dateIt is the date when points are awarded to the customer.
awarded_zone_tillCaptures the till_id of the zone where points were awarded.
latest_updated_dateDate when the records were last updated.
latest_updated_timeTime when the records were last updated
categoryIt tells the type of points which are awarded/deducted.

More details here : points_category
awarded_expiry_dateIt is the date when the points expire if left unused.
Awarded ProgramIt is the Loyalty program id.
Event ProgramIt 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 NameDescription
Points Category IdIt 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 RedeemedTotal points redeemed in a particular redemption. The value is populated from the points_redemption_summary table available at the source.
Source TypeSource from where the redemption data is collected (API or redemption data import).
Reference Id-
Redemption Bill IdIdentifier for a transaction (loyalty log id or bill id) for which the points are redeemed. It is generated by Capillary.
Redemption Bill NumberBill number for which the points are redeemed. If the source data does not provide this number, the system shows 'Not Captured'.
NotesAny additional information or comments provided during the redemption process.
Points Redemption TimeTime when the points were redeemed, populated from the points_redemption_summary table available at the source.
Auto Update TimeDate 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 IdUnique 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 NameDescription
Latest Updated DateDate when the data corresponding to this event/row is changed in the source table.
Latest Updated TimeTime when the data corresponding to this event/row is changed in the source table.
ProgramLoyalty program associated with the points redemption.
UserIdentifier of the user, set internally by Capillary.
User SegmentsThe segment to which the user belongs.
User AttributesAttributes of the user.
Store HierarchyThe store where event occurred.
Concept HierarchyThe concept where the event occurred.
Ou HierarchyThe Organisation Unit where the event occurred.
DateDate of the points redemption.
Redemption TypeIndicates 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 NameDescription
Points ExpiringThe number of points that are going to expire for which reminder will be sent.
Reminded Before DaysThe number of days before which reminder needs to be sent for the points expiring.
Auto Update Time Point Exp ReminderDate and time when the points_reminder_expiry_info Fact is recently updated (Unix timestamp)
Reminder Idprimary key of expiry_reminder_sent_stats_customer_level table

Dimensions

Dimension NameDescription
UserUser identifier for the user to whom points have expired. This is an integer set internally by capillary.
User SegmentsThe segment to which the user belongs
expiry_dateDate when the allocated points are supposed to expire if unused.
expiry_timeTime when the allocated points are supposed to expire if unused.
communication_channelThis refers to the communication_channel table and the id is a unique id for different communication channels.

More details here : communication_channel
DateMeasures the date when the event has occurred. In this case event is point expiry
TimeMeasures the time when the event has occurred. In this case event is point expiry
latest_updated_dateDate when the records were last updated.
latest_updated_timeTime when the records were last updated