Points expiry import profile

The Points Expiry import profile lets you bulk-update the expiry date of loyalty points you've already awarded to your members. Use it to extend or shorten a validity period across many members at once, or to expire points immediately, for example, to correct a data issue or to run a points-expiry promotion.

Note: This profile supports Update only. You can't insert new points-awarded records through this profile.

Note: If the expiry date you set falls today or earlier, the system deducts the points immediately as part of the import and creates the corresponding ledger entry. If the expiry date is in the future, the points keep their current status and expire later, on their normal schedule.

Who can use this


Audience	Customer Success Manager, brand admin
Access	Self-serve
Product	Capillary Data Platform - Data Import

Before you begin

Identify the loyalty program and the customer identifier type (mobile, email, external ID, or user ID) your source data uses.
A till code isn't required. If you don't provide one, the system uses the till already associated with the points-awarded record.

Source data format

Field	Display name	Required	Description
Customer identifier	Customer's unique ID	Yes	The mobile number, email, external ID, or user ID that identifies the member, matching the identifier type you select in the wizard.
`point_awarded_ref_type`	Points table	Yes	Which table the points to expire come from. See Points table values.
`point_awarded_ref_id`	Points record ID	No	The ID of a specific points record to target. Leave blank to apply the update to every matching record for that customer, program, and table.
`expiry_type`	Expiry type	Yes	`IMMEDIATE`, `RELATIVE`, or `FIXED`. See Expiry types.
`expiry_unit`	Expiry unit	Only for `RELATIVE`	`DAYS` or `MONTHS`.
`expiry_value`	Expiry value	For `RELATIVE` and `FIXED`	A positive whole number of days or months for `RELATIVE`, or a date in `yyyy-MM-dd` format for `FIXED`. Not needed for `IMMEDIATE`.
`till_code`	Till code	No	The till code to associate with the update. If left blank, the system uses the till already on the points-awarded record.
`expiry_update_reason_note`	Reason for updating points expiry	No	A free-text note explaining the update, for your own reference.

Points table values

Value	What it targets
`PA`	Points awarded
`PAL`	Points awarded, line item
`PABP`	Points awarded, bill promotion
`PACP`	Points awarded, customer promotion
`PABLP`	Points awarded, bill line-item promotion

Expiry types

Type	What it does
Immediate	Sets the expiry to the current date and time. Because this is never in the future, the system deducts the points right away.
Relative	Adds the number of days or months you specify to the points' existing expiry date. This always results in a future date, so the points aren't deducted now, they expire later on their normal schedule.
Fixed	Sets the expiry to the exact date you provide. A past or present date deducts the points immediately; a future date just updates the expiry date.

Note: By default, an update is rejected if the record's current expiry date is already in the past, or if the new expiry date you're setting is in the past. If your organization needs to allow either case, raise a ticket with the Capillary support team to enable the relevant configuration.

Create an import job

Creating a points expiry import job follows the same wizard as other Data Import profiles. For Step 1: Job settings (job type, schedule, approval thresholds, and notifications) and Step 2: Data to import, see Creating an import job. The steps below cover what's specific to this profile.

Step 3: Profile and method

Under Import profile, select Points Expiry. The Import method is automatically set to Update; Insert and Upsert aren't available for this profile.
Under Additional configurations:
- Loyalty program: select the loyalty program the points belong to. If your organization has only one program, it's selected automatically.
- Customer's unique ID: select the identifier type your source data uses to identify members.
- Reason for updating points expiry: optionally, enter a note explaining the update.

Step 4: Fields mapping

Map the mandatory fields: Customer identifier, Points table, and Expiry type.
If Expiry type is RELATIVE, also map Expiry unit and Expiry value. If it's FIXED, map Expiry value as a date. Expiry unit and Expiry value aren't needed for IMMEDIATE.
Optionally, map Points record ID, Till code, and Reason for updating points expiry.

Step 5: Review and proceed

Review your configuration summary and field mapping, then select Proceed to validate. The system validates every row before writing valid records to the database.

Tracking what changed

After the import runs, the job report includes these additional columns so you can confirm exactly what changed for each record:

Column	Description
`old_expiry_date`	The expiry date before the update.
`new_expiry_date`	The expiry date after the update.
`points_expired`	Populated only when the update caused an immediate points deduction.
`db_expiry_target_table`	The points table the update was applied to.
`db_expiry_record_id`	The ID of the specific points record that was updated.

Validation errors

Error message	What it means	How to fix
`at least one identifier required`	No customer identifier was provided or it doesn't match the selected identifier type.	Add a valid customer identifier for every row.
`point_awarded_ref_type is mandatory`	The Points table column is empty.	Set it to one of the supported values. See Points table values.
`point_awarded_ref_type is invalid`	The value isn't one of the supported points table values.	Correct the value.
`expiry_type is mandatory`	The Expiry type column is empty.	Set it to `IMMEDIATE`, `RELATIVE`, or `FIXED`.
`expiry_unit is mandatory for RELATIVE`	Expiry type is `RELATIVE` but Expiry unit is missing.	Add `DAYS` or `MONTHS`.
`expiry_value must be a positive number`	Expiry value isn't a positive whole number for a `RELATIVE` expiry.	Correct the value.
`expiry_value is not a valid date`	Expiry value isn't a valid `yyyy-MM-dd` date for a `FIXED` expiry.	Correct the date format.
`no matching points record found`	The system couldn't find a points record matching the customer, program, and points table.	Confirm the customer, program, and points table are correct.
`update rejected, expiry date is in the past`	The record's current or new expiry date is in the past, and your organization's configuration doesn't allow this.	Raise a ticket with the Capillary support team if you need this allowed.

FAQs

Can I insert new points-awarded records with this profile?
No, this profile supports updating existing points records only. Points are originally awarded through your loyalty program's regular earn rules, not through this import.

Do I need to provide a till code?
No, it's optional. If you leave it blank, the system uses the till already associated with the points-awarded record.

Can I target every points record for a customer at once?
Yes. Leave Points record ID blank to apply the update to every matching record for that customer, program, and points table. Provide a specific ID to target only one record.

Updated 4 days ago

Did this page help you?