Versions Compared

Old Version 7

changes.mady.by.user Francisco Reyes

Saved on

compared with

New Version 8

changes.mady.by.user Alex Yeager

Saved on

Key

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

Attribute

Blueshift Field

Advantage Field (CIR)

Advantage Field (AMB)

Logic/Rules

Item Code

item_code

pub_cde

itm_num

srv_typ

Subscription ID

subscription_id

sub_ref

✖️

Subscription reference from Advanatge

customer_id

customer_id

✖️

✖️

This is the profile key which is comprised of the affiliate id and email address which is MD5 hashed.

Source Code

key_code

key_cde

pmo_cde

Subscription Status

subscription_status

crc_sts

agr_sts

SUB

CASE WHEN CRC_STS IN ('P','Q','R','W','G') THEN 'Active' WHEN CRC_STS IN ('C','N','X') THEN 'Cancel' WHEN CRC_STS = 'E' THEN 'Expire' WHEN CRC_STS IN ('B','L','S','T','U','K') THEN 'Suspended' ELSE 'UNKNOWN' END

AMB

CASE

WHEN ver.agr_ver < trm.rls_ver THEN 'Suspended'
WHEN ver.rls_dte IS NULL THEN 'Suspended'
WHEN ver.agr_sts = 'R' THEN 'Suspended'
WHEN ver.agr_sts = 'N' THEN 'Cancel'
WHEN ver.agr_sts = 'C' THEN 'Cancel'
WHEN ver.agr_sts = 'M' THEN 'Cancel'
WHEN (
ver.ver_strt IS NOT NULL
AND DATEADD(S,-1,Dateadd(dd, trm.gis_day +
CASE
WHEN opt.opt_val <> 'Y' THEN trm.days_spn
ELSE 0
END + ver.free_day + ver.agr_day + ver.adj_days + ver.extn_day +
CASE
WHEN ver.agr_trm >= agr.rls_trm THEN ver.grc_day
ELSE 0
END, ver.ver_strt)) < Getdate()
AND (
trim(agr.susp_flg) IN ('',
'N')
OR agr.spn_dte > DATEADD(S,-1,Dateadd(dd, trm.gis_day
+
CASE
WHEN Ifnull(opt.opt_val, opd.opt_dfl) <> 'Y' THEN trm.days_spn
ELSE 0
END + ver.free_day + ver.agr_day + ver.adj_days + ver.extn_day, ver.ver_strt)) )
AND prd.per_unt IS NULL )
OR ver.usg_bse = 'Y'
AND NOT EXISTS
(
SELECT 1
FROM access_price as apt
WHERE apt.agr_id = ver.agr_id
AND apt.agr_trm = ver.agr_trm
AND apt.agr_ver = ver.agr_ver
AND apt.del_flg <> 'Y'
AND apt.acc_rem > 0 ) THEN 'Expire'
WHEN agr.susp_flg = 'B' THEN 'Suspended'
WHEN agr.susp_flg IN ('P',
'Y') THEN 'Suspended'
WHEN ver.agr_typ = 'P' THEN 'Active'
WHEN ver.ver_strt > Getdate() THEN 'Active'
WHEN ver.grc_day > 0
AND Getdate() >= DATEADD(S,-1,Dateadd(dd, trm.gis_day +
CASE
WHEN Ifnull(opt.opt_val, opd.opt_dfl) <> 'Y' THEN trm.days_spn
ELSE 0
END + ver.free_day + ver.agr_day + ver.adj_days + ver.extn_day, ver.ver_strt)) THEN 'Active'
WHEN ver.extn_day > 0
AND Getdate() >= DATEADD(S,-1,Dateadd(dd, trm.gis_day
+
CASE
WHEN Ifnull(opt.opt_val, opd.opt_dfl) <> 'Y' THEN trm.days_spn
ELSE 0
END + ver.free_day + ver.agr_day + ver.adj_days, ver.ver_strt)) THEN 'Active'
WHEN ver.free_day > 0
AND Getdate() BETWEEN ver.ver_strt AND DATEADD(S,-1,Dateadd(dd, trm.gis_day +
CASE
WHEN Ifnull(opt.opt_val, opd.opt_dfl) <> 'Y' THEN trm.days_spn
ELSE 0
END + ver.free_day, ver.ver_strt)) THEN 'Active'
ELSE 'Active'

Active Reason Code

active_reason_code

crc_sts

agr_sts

SUB

CASE WHEN CRC_STS = 'R' THEN 'Regular Receiving' WHEN CRC_STS = 'P' THEN 'Perpetual' WHEN CRC_STS = 'W' THEN 'Future Start' WHEN CRC_STS = 'G' THEN 'Gracing' WHEN CRC_STS = 'Q' THEN 'Controlled' ELSE 'NULL'

AMB

CASE

WHEN ver.agr_ver < trm.rls_ver THEN 'NULL'
WHEN ver.rls_dte IS NULL THEN 'NULL'
WHEN ver.agr_sts = 'R' THEN 'NULL'
WHEN ver.agr_sts = 'N' THEN 'NULL'
WHEN ver.agr_sts = 'C' THEN 'NULL'
WHEN ver.agr_sts = 'M' THEN 'NULL'
WHEN (
ver.ver_strt IS NOT NULL
AND DATEADD(S,-1,Dateadd(dd, trm.gis_day +
CASE
WHEN Ifnull(opt.opt_val, opd.opt_dfl) <> 'Y' THEN trm.days_spn
ELSE 0
END + ver.free_day + ver.agr_day + ver.adj_days + ver.extn_day +
CASE
WHEN ver.agr_trm >= agr.rls_trm THEN ver.grc_day
ELSE 0
END, ver.ver_strt)) < Getdate()
AND (
Trim(agr.susp_flg) IN ('', 'N')
OR agr.spn_dte > DATEADD(S,-1,Dateadd(dd, trm.gis_day +
CASE
WHEN Ifnull(opt.opt_val, opd.opt_dfl) <> 'Y' THEN trm.days_spn
ELSE 0
END + ver.free_day + ver.agr_day + ver.adj_days + ver.extn_day, ver.ver_strt)) )
AND prd.per_unt IS NULL )
OR ver.usg_bse = 'Y'
AND NOT EXISTS
(
SELECT 1
FROM access_price as apt
WHERE apt.agr_id = ver.agr_id
AND apt.agr_trm = ver.agr_trm
AND apt.agr_ver = ver.agr_ver
AND apt.del_flg <> 'Y'
AND apt.acc_rem > 0 ) THEN 'NULL'
WHEN agr.susp_flg = 'B' THEN 'NULL'
WHEN agr.susp_flg IN ('P',
'Y') THEN 'NULL'
WHEN ver.agr_typ = 'P' THEN 'Proforma'
WHEN ver.ver_strt > Getdate() THEN 'Waiting'
WHEN ver.grc_day > 0
AND Getdate() >= DATEADD(S,-1,Dateadd(dd, trm.gis_day +
CASE
WHEN Ifnull(opt.opt_val, opd.opt_dfl) <> 'Y' THEN trm.days_spn
ELSE 0
END + ver.free_day + ver.agr_day + ver.adj_days + ver.extn_day, ver.ver_strt)) THEN 'Grace Days'
WHEN ver.extn_day > 0
AND Getdate() >= DATEADD(S,-1,Dateadd(dd, trm.gis_day +
CASE
WHEN Ifnull(opt.opt_val, opd.opt_dfl) <> 'Y' THEN trm.days_spn
ELSE 0
END + ver.free_day + ver.agr_day + ver.adj_days, ver.ver_strt)) THEN 'Extension Days'
WHEN ver.free_day > 0
AND Getdate() BETWEEN ver.ver_strt AND DATEADD(S,-1,Dateadd(dd, trm.gis_day +
CASE
WHEN Ifnull(opt.opt_val, opd.opt_dfl) <> 'Y' THEN trm.days_spn
ELSE 0
END + ver.free_day, ver.ver_strt)) THEN 'Pre Free Days'
ELSE 'Active'

Cancellation Reason Code

cancellation_reason_code

crc_sts

agr_sts

SUB

CASE WHEN CRC_STS = 'C' THEN 'Cancel' WHEN CRC_STS = 'N' THEN 'Non Pay Cancel' WHEN CRC_STS = 'X' THEN 'Folded' ELSE 'NULL'

AMB

CASE
WHEN ver.agr_ver < trm.rls_ver THEN 'NULL'
WHEN ver.rls_dte IS NULL THEN 'NULL'
WHEN ver.agr_sts = 'R' THEN 'NULL'
WHEN ver.agr_sts = 'N' THEN 'Non Pay Cancel'
WHEN ver.agr_sts = 'C' THEN 'Cancel'
WHEN ver.agr_sts = 'M' THEN 'Migration Cancel'
WHEN (
ver.ver_strt IS NOT NULL
AND DATEADD(S,-1,Dateadd(dd, trm.gis_day +
CASE
WHEN If null(opt.opt_val, opd.opt_dfl) <> 'Y' THEN trm.days_spn
ELSE 0
END + ver.free_day + ver.agr_day + ver.adj_days + ver.extn_day +
CASE
WHEN ver.agr_trm >= agr.rls_trm THEN ver.grc_day
ELSE 0
END, ver.ver_strt)) < Getdate()
AND (
Trim(agr.susp_flg) IN ('', 'N')
OR agr.spn_dte > DATEADD(S,-1,Dateadd(dd, trm.gis_day +
CASE
WHEN Ifnull(opt.opt_val, opd.opt_dfl) <> 'Y' THEN trm.days_spn
ELSE 0
END + ver.free_day + ver.agr_day + ver.adj_days + ver.extn_day, ver.ver_strt)) )
AND prd.per_unt IS NULL )
OR ver.usg_bse = 'Y'
AND NOT EXISTS
(
SELECT 1
FROM access_price as apt
WHERE apt.agr_id = ver.agr_id
AND apt.agr_trm = ver.agr_trm
AND apt.agr_ver = ver.agr_ver
AND apt.del_flg <> 'Y'
AND apt.acc_rem > 0 ) THEN 'NULL'
WHEN agr.susp_flg = 'B' THEN 'NULL'
WHEN agr.susp_flg IN ('P',
'Y') THEN 'NULL'
WHEN ver.agr_typ = 'P' THEN 'NULL'
WHEN ver.ver_strt > Getdate() THEN 'NULL'
WHEN ver.grc_day > 0
AND Getdate() >= DATEADD(S,-1,Dateadd(dd, trm.gis_day +
CASE
WHEN Ifnull(opt.opt_val, opd.opt_dfl) <> 'Y' THEN trm.days_spn
ELSE 0
END + ver.free_day + ver.agr_day + ver.adj_days + ver.extn_day, ver.ver_strt)) THEN 'NULL'
WHEN ver.extn_day > 0
AND Getdate() >= DATEADD(S,-1,Dateadd(dd, trm.gis_day +
CASE
WHEN Ifnull(opt.opt_val, opd.opt_dfl) <> 'Y' THEN trm.days_spn
ELSE 0
END + ver.free_day + ver.agr_day + ver.adj_days, ver.ver_strt)) THEN 'NULL'
WHEN ver.free_day > 0
AND Getdate() BETWEEN ver.ver_strt AND DATEADD(S,-1,Dateadd(dd, trm.gis_day +
CASE
WHEN Ifnull(opt.opt_val, opd.opt_dfl) <> 'Y' THEN trm.days_spn
ELSE 0
END + ver.free_day, ver.ver_strt)) THEN 'NULL'
ELSE 'NULL'

Suspension Reason Code

suspension_reason_code

crc_sts

agr_sts

SUB

CASE WHEN CRC_STS = 'B' THEN 'Billing Suspension' WHEN CRC_STS = 'S' THEN 'Indefinite Suspension' WHEN CRC_STS = 'T' THEN 'Temporary Suspension' WHEN CRC_STS = 'U' THEN 'Undeliverable' WHEN CRC_STS = 'K' THEN 'In Error' WHEN CRC_STS = 'L' THEN 'Delayed Entry' ELSE 'NULL'

AMB

CASE
WHEN ver.agr_ver < trm.rls_ver THEN 'NULL'
WHEN ver.rls_dte IS NULL THEN 'NULL'
WHEN ver.agr_sts = 'R' THEN 'Reversal'
WHEN ver.agr_sts = 'N' THEN 'NULL'
WHEN ver.agr_sts = 'C' THEN 'NULL'
WHEN ver.agr_sts = 'M' THEN 'NULL'
WHEN (
ver.ver_strt IS NOT NULL
AND DATEADD(S,-1,Dateadd(dd, trm.gis_day +
CASE
WHEN Ifnull(opt.opt_val, opd.opt_dfl) <> 'Y' THEN trm.days_spn
ELSE 0
END + ver.free_day + ver.agr_day + ver.adj_days + ver.extn_day +
CASE
WHEN ver.agr_trm >= agr.rls_trm THEN ver.grc_day
ELSE 0
END, ver.ver_strt)) < Getdate()
AND (
Trim(agr.susp_flg) IN ('', 'N')
OR agr.spn_dte > DATEADD(S,-1,Dateadd(dd, trm.gis_day +
CASE
WHEN Ifnull(opt.opt_val, opd.opt_dfl) <> 'Y' THEN trm.days_spn
ELSE 0
END + ver.free_day + ver.agr_day + ver.adj_days + ver.extn_day, ver.ver_strt)) )
AND prd.per_unt IS NULL )
OR ver.usg_bse = 'Y'
AND NOT EXISTS
(
SELECT 1
FROM access_price as apt
WHERE apt.agr_id = ver.agr_id
AND apt.agr_trm = ver.agr_trm
AND apt.agr_ver = ver.agr_ver
AND apt.del_flg <> 'Y'
AND apt.acc_rem > 0 ) THEN 'NULL'
WHEN agr.susp_flg = 'B' THEN 'Billing Suspension'
WHEN agr.susp_flg IN ('P',
'Y') THEN 'Indefinite Suspension'
WHEN ver.agr_typ = 'P' THEN 'NULL'
WHEN ver.ver_strt > Getdate() THEN 'NULL'
WHEN ver.grc_day > 0
AND Getdate() >= DATEADD(S,-1,Dateadd(dd, trm.gis_day +
CASE
WHEN Ifnull(opt.opt_val, opd.opt_dfl) <> 'Y' THEN trm.days_spn
ELSE 0
END + ver.free_day + ver.agr_day + ver.adj_days + ver.extn_day, ver.ver_strt)) THEN 'NULL'
WHEN ver.extn_day > 0
AND Getdate() >= DATEADD(S,-1,Dateadd(dd, trm.gis_day +
CASE
WHEN Ifnull(opt.opt_val, opd.opt_dfl) <> 'Y' THEN trm.days_spn
ELSE 0
END + ver.free_day + ver.agr_day + ver.adj_days, ver.ver_strt)) THEN 'NULL'
WHEN ver.free_day > 0
AND Getdate() BETWEEN ver.ver_strt AND DATEADD(S,-1,Dateadd(dd, trm.gis_day +
CASE
WHEN Ifnull(opt.opt_val, opd.opt_dfl) <> 'Y' THEN trm.days_spn
ELSE 0
END + ver.free_day, ver.ver_strt)) THEN 'NULL'
ELSE 'NULL'

Expiration Date

expiration_date

exp_dte

exp_dte

 

Billing Status 

billing_status

bil_sts

bil_sts

SUB

CASE WHEN BIL_STS IN ('P','R') THEN ‘Paid' WHEN BIL_STS = 'M' THEN 'Cancel' WHEN BIL_STS = 'B' THEN 'Unpaid' WHEN BIL_STS = 'C' THEN 'Comp' WHEN BIL_STS = 'A' THEN 'Approval' ELSE 'UNKNOWN’ END

AMB

CASE
WHEN trm.abil_sts IN ('P',
'R') THEN 'Paid'
WHEN trm.abil_sts = 'M' THEN 'Cancel'
WHEN trm.abil_sts = 'B' THEN 'Unpaid'
WHEN trm.abil_sts = 'C' THEN 'Comp'
WHEN trm.abil_sts = 'A' THEN 'Approval'
ELSE 'UNKNOWN'

Email Active

email_active

eml_sta

✖️

CASE WHEN cml.eml_sta IN ('A','') AND sub.dlv_cde IN ('EM','ME') THEN 'true'  ELSE 'false' END AS email_status

Final Expire

final_expire

fin_exp

afin_

date

exp

Final Issue to go

final_issues_to_go

fin_itg

✖️

Issue to go

issue_to_go

itg

✖️

 

Future Term

future_term

exp_dte

✖️

SUB

CASE WHEN SUB.FIN_EXP >  SUB.EXP_DTE AND SUB.CRC_STS IN ('R','E','W','S') THEN 'true' ELSE 'false' END as 'future_term'

AMB

CASE
WHEN xap.afin_exp > xap.exp_dte THEN 'true'
ELSE 'false'

Promotion Choice

promotion

source_choice

pmo_chc

pmo_chc

Price

price

rate

unit_prc

Renewal Type

renewal_type

rn_flg

agr_ren

SUB

CASE WHEN RN_FLG = 'A' THEN 'Auto Renewal' WHEN RN_FLG = 'C' THEN 'Auto Charge' WHEN RN_FLG = 'D' THEN 'Auto Discontinued' WHEN RN_FLG = 'E' THEN 'Promote Done' WHEN RN_FLG = 'N' THEN 'Do Not Renew' WHEN RN_FLG = 'S' THEN 'Special Renewal' WHEN RN_FLG = 'U' THEN 'Auto Charge Bad Card' WHEN RN_FLG = 'X' THEN 'Do non Promote for Renewal' WHEN RN_FLG = 'Y' THEN 'Promote for Renewal' WHEN RN_FLG = 'Z' THEN 'Promote Limited Offers' ELSE 'UNKNOWN' END

AMB

CASE
WHEN ver.agr_ren = 'A' THEN 'Auto Renewal'
WHEN ver.agr_ren = 'C' THEN 'Auto Charge'
WHEN ver.agr_ren = 'D' THEN 'Auto Discontinued'
WHEN ver.agr_ren = 'E' THEN 'Promote Done'
WHEN ver.agr_ren = 'N' THEN 'Do Not Renew'
WHEN ver.agr_ren = 'S' THEN 'Special Renewal'
WHEN ver.agr_ren = 'U' THEN 'Auto Charge Bad Card'
WHEN ver.agr_ren = 'X' THEN 'Do non Promote for Renewal'
WHEN ver.agr_ren = 'Y' THEN 'Promote for Renewal'
WHEN ver.agr_ren = 'Z' THEN 'Promote Limited Offers'
ELSE 'UNKNOWN'

Last Renewal Date

last_renewal_date

lrnw_dte

✖️

Membership Type

membership_type

mbr_org

✖️

Date Joined

date_joined

org_ctgd

✖️

Subscription Type

subscription_type

sub_typ

sub_typ

Term Length

term_length

term

✖️

Last Renewal Key Code

last_renewal_key_code

lrnw_key

✖️

Membership Level

membership_level

mbr_cat

✖️

Acquisition Method

acquisition_method

pmo_acq

pmo_acq

Campaign Code

campaign_code

cpn_cde

cpn_cde

Creative Code

creative_code

ctv_cde

ctv_cde

Offer Code

offer_code

ofr_cde

ofr_cde

SMS Active

sms_active

✖️

✖️

When a customer opts in to receive paid SMS alerts flag will be switched to True. If a customer opts out this flag will be set to False

is_deleted

is_deleted

✖️

✖️

A snapshot of the subscription 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.

 

Info

It is important to include the Email Active = True attribute in your transaction mixins or segments as customers may only want to receive fulfillment via the Web but would still like to receive SMS alerts. For example if there is an omnichannel campaign that includes email and SMS messages and the customer has opted for Web only content then they would not receive the email message but would receive the SMS message if eligible.

Another important attribute to include is the is_deleted = False as this flag would be flipped to True if the email address is removed or replaced with another email address in Advantage. Unless this flag is set to False the email address that is no longer under the subscription record would still be eligible for to get picked up by the segment.