LoyaltyPromotions

Create and query promotions for a Square Loyalty program. Requires filtering by ProgramId.

Table Specific Information

Select

The add-in uses the Square API to filter the results by the following columns and operators while the rest of the filter is executed client-side within the add-in.

Id supports the following operator: =.
ProgramId supports the following operator: =.
Status supports the following operator: =.

Retrieve all promotions for a loyalty program:

SELECT * FROM LoyaltyPromotions WHERE ProgramId = 'PROGRAM_ID'

Retrieve active promotions for a loyalty program:

SELECT * FROM LoyaltyPromotions WHERE ProgramId = 'PROGRAM_ID' AND Status = 'ACTIVE'

Insert

To create a loyalty promotion, you must specify the ProgramId, Name, IncentiveType, AvailableTimeTimePeriods, and IdempotencyKey columns.

INSERT INTO LoyaltyPromotions (ProgramId, Name, IncentiveType, IncentivePointsAdditionPoints, AvailableTimeTimePeriods, TriggerLimitTimes, TriggerLimitInterval, IdempotencyKey) VALUES ('PROGRAM_ID', 'Double Points Weekend', 'POINTS_MULTIPLIER', 2, '["BEGIN:VEVENT\nDTSTART:20240101T000000\nDURATION:PT24H\nRRULE:FREQ=WEEKLY;BYDAY=SA,SU\nEND:VEVENT"]', 1, 'DAY', 'unique-idempotency-key')

Columns


Name	Type	ReadOnly	Description
Id [KEY]	String	True	The Square-assigned ID of the loyalty promotion.
Name	String	False	The name of the loyalty promotion.
Status	String	True	The current status of the loyalty promotion. The allowed values are ACTIVE, ENDED, CANCELED, SCHEDULED.
IncentiveType	String	False	The type of loyalty incentive for the promotion. The allowed values are POINTS_MULTIPLIER, POINTS_ADDITION.
IncentivePointsAdditionPoints	Integer	False	The number of additional points to earn each time the promotion is triggered. For POINTS_ADDITION incentive type. Minimum: 1.
IncentiveMultiplierData	String	False	The points multiplier data for POINTS_MULTIPLIER type promotions.
AvailableTimeStartDate	Date	True	The start date of the promotion, in YYYY-MM-DD format. Read-only - Square populates this field based on the provided time_periods.
AvailableTimeEndDate	Date	True	The end date of the promotion, in YYYY-MM-DD format. Read-only - Square populates this field based on the provided time_periods.
AvailableTimeTimePeriods	String	False	A list of iCalendar (RFC 5545) VEVENT strings defining when the promotion is available. Each event uses DTSTART, DURATION, and optionally RRULE. Required to create a promotion.
TriggerLimitTimes	Integer	False	The maximum number of times a buyer can trigger the promotion during the specified interval. Minimum: 1, Maximum: 30.
TriggerLimitInterval	String	False	The time period for the trigger limit. Options: ALL_TIME, DAY, WEEK, MONTH. The allowed values are ALL_TIME, DAY, WEEK, MONTH.
MinimumSpendAmountMoneyAmount	Int64	False	The minimum purchase amount to earn points during the promotion, in the smallest denomination of the currency.
MinimumSpendAmountMoneyCurrency	String	False	The currency code of the minimum spend amount, in ISO 4217 format.
QualifyingItemVariationIds	String	False	The IDs of qualifying ITEM_VARIATION catalog objects. Purchase must include at least one. Cannot combine with qualifying_category_ids. Maximum 500 items.
QualifyingCategoryIds	String	False	The IDs of qualifying CATEGORY catalog objects. Purchase must include at least one item from these categories. Cannot combine with qualifying_item_variation_ids. Maximum 500 items.
LoyaltyProgramId	String	True	The ID of the loyalty program associated with this promotion. Populated by the server.
CanceledAt	Datetime	True	The timestamp of when the promotion was canceled, in RFC 3339 format.
CreatedAt	Datetime	True	The timestamp of when the promotion was created, in RFC 3339 format.
UpdatedAt	Datetime	True	The timestamp when the promotion was last updated, in RFC 3339 format.
ProgramId [KEY]	String	False	The ID of the loyalty program this promotion belongs to. Required for all operations and used as a URL path parameter.

Pseudocolumns

Pseudo column fields are used in the WHERE clause of SELECT statements (unless stated otherwise) and offer a more granular control over the tuples that are returned from the data source.