Versions Compared

Old Version 2

changes.mady.by.user Anthony Fontana

Saved on

compared with

New Version 3

changes.mady.by.user Alex Yeager

Saved on

Key

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

The Product data object is created to allow Blueshift users to target customers based on specific product order attributes.

...

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.

Attribute

Advantage Field

Blueshift Field

Logic/Rules

Customer Number

ctm_nbr

bill_toadvantage_customer_number

Item Choice

itm_chc

item_choice

Item Number

itm_num

Itemitem_number

Order Number

ord_num

order_number

Order Type

ord_type

order_type

C=Credit memo/Return order 
G=Gratis order 
I=Invoiceable order/Invoiceable Order
M=Modify order w/o shipping itms 
O=Pro forma order 
P=Post shipped order 
R=Repeating Order 
V=Preview order 
W=Warehouse transfer  

else UNKNOWN 

Order Date

ordr_dte

code

Order Date

ctg_dte

initial_order_date

Promotion Choice

pmo_chc

promotionsource_choice

Ship to Customer Number

shp_ctm

ship_to_customer_number

Unit Price

unit_prc

unit_price

Quantity Backordered

qty_bko

quantity_backordered

Quantity ClaimOrdered

qty_clmord

quantity_claim

Quantity Lost

qty_los

quantity_lost

Quantity Ordered

qty_ord

quantity_ordered

Quantity Returned

qty_ret

quantity_returned

Promotion Code

pmo_cde

promotion_code

Quantity shipped

qty_shp

quantity_shipped

Quantity Remaining

qty_rem

quantity_remaining

Order Sequence

ord_seq

order_sequence

Order Shipment Sequence

ord_spsq

order_shipment_sequence

Total Cost

tot_cst

total_cost

Control Group Name

ord_ctg

control_group_name

Campaign Code

cpn_cde

campaign_code

Acquisition Method

pmo_acq

acquisition_method

Creative Code

ctv_cde

creative_code

Offer Code

ofr_cde

offer_codeordered

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

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.