Versions Compared

Old Version 3

changes.mady.by.user Alex Yeager

Saved on

compared with

New Version Current

changes.mady.by.user Alex Yeager

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Info

  • The most recent transaction is sent to Blueshift using the combination of Control Group Date, Item Number, Email, and Update Date. This was done because Product records are not automatically aggregated by Advantage.

  • The Product transaction can be used solely to target email addresses whereas in the legacy account, you needed to target a List transaction along with the Product transaction in order to return an email count.

  • Only items that have the Item status code set to ‘DS' and Saleable set to 'S' in Advantage will be sent to Blueshift

Attribute

Advantage Field

Blueshift Field

Logic/Rules

Customer Number

ctm_nbr

advantage_customer_number

Item Number

itm_num

item_code

Order Date

ctg_dte

initial_order_date

Promotion Choice

pmo_chc

source_choice

Unit Price

unit_prc

price

Quantity Backordered

qty_bko

quantity_backordered

Quantity Ordered

qty_ord

quantity_ordered

Promotion Code

pmo_cde

source_code

Total Cost

tot_cst

total_price

Campaign Code

cpn_cde

campaign_code

Acquisition Method

pmo_acq

acquisition_method

Creative Code

ctv_cde

creative_code

Offer Code

ofr_cde

offer_code

Affiliate ID

dim_affiliate_division.affiliate_id

affiliate_id

Franchise

off_nam

franchise

Next Ship Date

DATEADD(dd, BCLCTC_M.SHP_FRQ, BCLCTC_M.PSR_DTE)

next_ship_date

Estimated Ship Date

ITW.EST_SHP

estimated_ship_date

Product Status

OLN.QTY_RET

product_status

CASE WHEN OLN.QTY_RET < OLN.QTY_ORD AND OLN.UNIT_PRC > 0 AND OLN.QTY_BKO < OLN.QTY_ORD THEN 'Purchased'

WHEN OLN.QTY_RET < OLN.QTY_ORD AND OLN.UNIT_PRC = 0 AND OLN.QTY_BKO < OLN.QTY_ORD THEN 'Complimentary'

WHEN OLN.QTY_RET < OLN.QTY_ORD AND OLN.QTY_BKO >= OLN.QTY_ORD THEN 'Backordered'

WHEN OLN.QTY_RET >= OLN.QTY_ORD THEN 'Returned'

ELSE 'UNKNOWN'

Return Date

OLN.QTY_RET

return_date

CASE WHEN OLN.QTY_RET >= OLN.QTY_ORD THEN OLN.UPD_DTE
ELSE NULL

Auto Delivery Status

TBCTBL_T.FLD_DSC

auto_delivery_status

Suspension Resolution Date

BCLCTC_M.RES_DTE

suspension_resolution_date

Auto Delivery Last Status Update Date

BCLCTC_M.LSTS_DTE

auto_delivery_last_status_update_date

Auto Delivery Frequency Days

BCLCTC_M.SHP_FRQ

auto_delivery_frequency_days

is_deleted

N/A

is_deleted

A snapshot of the product object is taken from Advantage data from the past hour and it differentiates against the current snapshot to produce the delta and send to Blueshift. If what’s in the current snapshot does not exist in the snapshot from an hour ago, this flag is set to true. This usually happens when records are hard deleted from Advantage.

...