- v1.0.0-latest-prod
The following data shows the key tables used by the Offer Service.
The Offer table contains most of the data that drives an Offer.
| Field | Description |
|---|---|
ID |
Unique id of the row |
CONTEXT_ID |
Logical id of the offer. Allows for sandbox versions. |
EXTERNAL_ID |
This is an arbitrary ID, typically assigned by or used by 1st or 3rd party systems |
NAME |
The friendly name of the offer |
DESCRIPTION |
Description of the offer |
CART_LABEL |
A label for this offer typically used by the storefront |
DISC_AMOUNT |
Amount of the discount |
DISC_TARGET_TYPE |
One of ORDER, ORDER_ITEM, FULFILLMENT_GROUP, or FULFILLMENT_GROUP_ITEM |
DISC_METHOD_TYPE |
One of PERCENT_OFF, AMOUNT_OFF, or FIXED_PRICE |
DISC_TIMING_TYPE |
One of ORDER_DISCOUNT or FUTURE_CREDIT |
DISC_PRORATION_TYPE |
One of TARGET_ONLY or TARGET_AND_QUALIFIER |
DISC_APPLICABLE_TO_SALE |
"Y" if the discount can apply to items that are on sale |
DISC_APPLICABLE_TO_DEP_ITEMS |
"Y" if a the discount can apply to dependent order items |
OFFER_DISCOUNT_STRATEGY |
One of STANDARD, TIERED_ITEM_QTY, TIERED_ITEM_SUBTOTAL, TIERED_CART_SUBTOTAL, TIERED_CART_SEGMENT_SUBTOTAL. |
TIERED_DISCOUNT |
Indicator if this offer uses a tiered discount (DEPRECATED in favor of |
DISCOUNT_TIERS |
Json structure representing the discount tiers, see DiscountTier |
PRIORITY |
Offer evaluation priority |
ACTIVE_START_DATE |
valid dates for the offer |
ACTIVE_END_DATE |
valid dates for the offer |
COMBINABILITY_TYPE |
One of DEFAULT, ANY, OTHER_TYPE, or NONE |
STACKABLE |
N if this offer cannot be used with offers of the same type |
AUTOMATICALLY_CONSIDERED |
N if this offer requires a code |
PREFER_CART_BUNDLING |
Y if passing a flag on the response to group item targets by discounts |
DISTRIBUTE_AMT_TO_TARGETS |
Y if the discount amount should be distributed between targets |
REQUIRES_TARGET_QUALIFIER |
Advanced property used when a qualifier and target must descend from the same parent item (e.g. bundle) |
MIN_TOT_QUAL_PRICES |
Minimum total for qualifier items |
QUALIFIERS_CAN_BE_QUALIFIERS |
Normally "N", indicates that a qualifier can be used to qualify for more than one target discount |
QUALIFIERS_CAN_BE_TARGETS |
Normally "N", indicates that a qualifier can also receive a discount |
APPLY_DISCOUNT_TO_ALL_TARGETS |
Y if item offer is applicable to all discountable items, no target criteria required |
EXCLUDE_DISC_ITEMS_FROM_TOTALS |
Y if discounted items should not be included when calculating subtotal criteria |
TRGT_ITEM_CRIT_CNNCTV_OPRTR |
One of AND or OR. |
TRGT_ITEM_CRIT_RULES |
Rule string and required number of matches for target criteria |
MIN_TOT_TARGET_PRICES |
Minimum total prior to discounting for the items receiving the discount |
MAX_USES |
The maximum number of times this offer can be used |
MAX_TOTAL_DISCOUNT |
The maximum savings this offer is valid for |
MAX_USAGE_STATUS |
One of MAX_USES_EXCEEDED, TOTAL_DISCOUNT_EXCEEDED, VIABLE. |
MAX_USES_PER_ORDER |
The maximum number of times this offer can be used in a single order |
MAX_SAVINGS_PER_ORDER |
The maximum savings this offer can apply in a single order |
MAX_USES_PER_USER |
The maximum number of times this offer can be used by a single user |
MAX_USES_PER_USER_FREQUENCY |
Period between uses for a user |
MAX_USES_PER_USER_STRAT |
One of CUSTOMER or ACCOUNT |
ORDER_CRITERIA |
Rule string for order criteria |
CART_SUBTOTAL |
Cart subtotal needed to qualify for the offer |
FG_ITEM_SUBTOTAL |
Item subtotal within the fulfillment group needed to qualify for the offer |
FULFILLMENT_GROUP_CRITERIA |
Rule string for fulfillment group criteria |
TIME_CRITERIA |
Rule string for time criteria |
SEGMENT |
The segment this offer can apply to |
USE_QTY_ONLY_TIER_CALC |
For tiered offers, should the quantity match all item counts or each set of items (e.g. on a buy 1 shirt and 1 hat) does the quantity refer to 1 set of both items or to the raw number of items |
QUALIFY_FULFILLMENT_ACROSS_ALL |
Normally "N", indicates to only use the items in a fulfillment group to qualify for a fulfillment offer |
SUB_DISC_APPLY_FUTURE |
Y if this discount should apply at a later time |
SUB_DISC_CURRENT_ORDER |
Y if this discount should apply to the current order |
SUB_DISC_BEGIN_PERIOD |
The first billing cycle this discount should apply to |
SUB_DISC_END_PERIOD |
The last billing cycle this discount should apply to |
TARGET_GROUPING_STRATEGY |
One of CHEAPEST or COSTLIEST. Default is COSTLIEST to provide best discount |
IS_FREE_GIFT |
Y if offer should apply a free gift to cart |
FREE_PRODUCT |
The ID of the free gift product |
IS_VOUCHER |
Y if the offer should produce a voucher code |
VOUCHER_CAMPAIGN |
The ID of the voucher campaign |
ESTIMATED_VALUE |
Provides a pre-determined value for discounts that are not calculated during offer processing (e.g. Voucher and Free Gift offers) |
ATTRIBUTES |
Map of additional info for the offer |
Embeddable JSON structure in BLC_OFFER table.
Field |
Description |
minQuantity |
The minimum quantity of items needed to reach this tier |
minAmount |
The minimum amount needed to reach this tier |
amount |
The discount amount for this tier, overrides the base offer discount |
methodType |
One of PERCENT_OFF, AMOUNT_OFF, FIXED_PRICE |
Offers can be associated with a number of marketing messages to display on Product Detail pages and within the Cart.
| Field | Description |
|---|---|
ID |
Unique Id for the Marketing Message |
CONTEXT_ID |
Logical id of the marketing message to support sandbox edits |
OFFER_CONTEXT_ID |
Ref to the containing offer |
MESSAGE_TEXT |
Message to display |
PRIORITY |
If multiple messages apply in a given context, this determines the order |
TARGET_TYPE |
Determines if the message relates to the QUALIFIER, TARGET, or EITHER |
LOCATION_TYPE |
One of CART , SEARCH, BROWSE, PRODUCT_DETAIL, or EVERYWHERE (DEPRECATED in favor of |
LOCATION_TYPES |
List of location types for display |
LOCALE |
Used to determine the locale / language for the message (DEPRECATED in favor of translations) |
ACTIVE_START_DATE |
Start date to display this message |
ACTIVE_END_DATE |
End date to display this message |
DISPLAY_HINT |
Provides a hint on how this message should be displayed |
SHOW_IF_OFFER_APPLIED |
Y if the message should show for applied offers |
USE_CART_SUBTOTAL |
Y if the message display should be based on cart subtotal |
MIN_CART_SUBTOTAL |
The minimum subtotal to display the message |
MAX_CART_SUBTOTAL |
The maximum subtotal to display the message |
Offer codes are intended as a way to apply an offer or campaign. These codes can either be
Assigned directly to an offer in use cases where the code can be shared across users.
Assigned to a campaign to manage customer-specific promotion campaigns.
In either case, these codes can be restricted to a max number of uses, or max uses per customer.
| Field | Description |
|---|---|
ID |
Unique Id for the code |
EXTERNAL_ID |
This is an arbitrary ID, typically assigned by or used by 1st or 3rd party systems |
CODE |
The code string |
ACTIVE_START_DATE |
Start date when the code is active |
ACTIVE_END_DATE |
End date when the code is no longer active |
MAX_USES |
Use 0 for unlimited usage |
MAX_USES_PER_USER |
Use 0 for unlimited usage |
EMAIL_REGEX |
Stores a qualifying customer email regex if the code are restricted to be used by a specific email or pattern |
IS_ASSIGNED |
Has this code been allocated to a user, relevent only for generated codes |
SEGMENT |
The segment this offer code can apply to |
OFFER_ID |
Reference key to the containing offer record |
CAMPAIGN_ID |
Reference key to the containing campaign record |
GENERATOR_ID |
Reference key to the containing generator |
ATTRIBUTES |
Map of additional info for the offer codes |
An offer can be restricted to certain customers
| Field | Description |
|---|---|
OFFER_ID |
Key to the containing offer record |
TARGET_TYPE |
The type of user restriction (e.g. CUSTOMER, ACCOUNT) |
TARGET_VALUE |
The identifier associated with the target type (e.g. id, email) |
LABEL |
A friendly display label to use instead of the target value, which is usually a ULID. Falls back to target value if null. |
This table associates offers with a campaign which allows the campaign-related offer codes to be used to qualify for the Offer
| Field | Description |
|---|---|
OFFER_ID |
Key to the containing offer record |
TRACKING_ID |
The campaign identifier |
Stores details for each usage of the offer
| Field | Description |
|---|---|
ID |
Unique Id for the offer audit detail |
OFFER_ID |
Key to the offer record |
OFFER_CODE_ID |
Key to the offer code record, if exists |
OFFER_AUDIT_SUMMARY_ID |
Key to the offer audit summary record |
TRACKING_ID |
Key to the campaign record |
USER_TARGET_TYPE |
The user type associated with the AUDIT |
USER_TARGET_VALUE |
The value for the user type |
TRANSACTION_TYPE |
The transaction type associated with the AUDIT |
TRANSACTION_ID |
The value for the transaction type |
DATE_APPLIED |
Date the audit was recorded |
Stores a summary of totals for offer usage
| Field | Description |
|---|---|
ID |
Unique Id for the offer audit summary |
OFFER_ID |
Key to the offer record |
TOTAL_USAGES |
The total number of times this offer has been used |
TOTAL_DISCOUNTED |
The total savings across all usages of the offer |
Stores a summary of totals for shared code usage
| Field | Description |
|---|---|
ID |
Unique Id for the shared code audit summary |
OFFER_CODE_ID |
Key to the offer code record |
TOTAL_USAGES |
The total number of times this offer code has been used |
TOTAL_DISCOUNTED |
The total savings across all usages of the offer code |
Each time codes are generated, a code generator record is created with the instructions used in the code generation process.
| Field | Description |
|---|---|
ID |
Unique Id for the Code Generator |
CODE_PREFIX |
Code prefix used in code generation, typically defaults to the campaign code prefix |
NUMBER_TO_GENERATE |
Number of unique codes to generate |
NUMBER_GENERATED |
Updated as each batch of codes is created to reflect progress towards the goal |
STATUS |
The status of the code generation (PENDING, IN_PROCESS, SUCCESS, ERROR, CANCELLED) |
CODE_FORMAT |
A format string that can be used to control the characters used in code |
CODE_LENGTH |
Use to specify the desired length of the code (alternative to code-format) |
MAX_USES |
Typically set to 1 if each code can only be used once |
SEGMENT |
The segment the generated offer codes can apply to |
CREATE_DATE |
Date code generation was created |
UPDATE_DATE |
Date code generation was last updated |
MESSAGE |
Tracks progress of codes generated |
EMAIL_REGEX |
Stores a qualifying customer email regex if the codes are restricted to be used by a specific email pattern |
ACTIVE_START_DATE |
Start date the code is active (leave blank to default to campaign dates) |
ACTIVE_END_DATE |
End date the code is active (leave blank to default to campaign dates) |
IS_FOR_VOUCHER |
Y if this generator is for voucher offers |
ALLOWED_LETTERS |
A string of allowed letters for the generated codes |
ALLOWED_ NUMBERS |
A string of allowed numbers for the generated codes |
CAMPAIGN_ID |
Foreign key to the containing campaign record |
The Campaign object provides a wrapper for all of the codes that are generated.
| Field | Description |
|---|---|
ID |
Unique Id for the Campaign |
EXTERNAL_ID |
This is an arbitrary ID, typically assigned by or used by 1st or 3rd party systems |
NAME |
Name of this campaign |
CODE_PREFIX |
The prefix that will be used for any generated codes. It is best to use a unique prefix for each campaign to avoid code collisions |
ACTIVE_START_DATE |
Start date the campaign is active |
ACTIVE_END_DATE |
End date the campaign is active |
Offer entities are Application Trackable and Sandbox Trackable - to allow application specific offers and the ability to modify offers using the preview and approval workflows.
Campaign and code entities are Application Trackable to allow for application specific changes.