Page Comparison

Attribute	Blueshift Field	Advantage Field (CIR)	Advantage Field (AMB)	Logic/Rules
Item Code	item_code	pub_cde

srv_typ

Subscription ID

subscription_id

sub_ref

✖️

Subscription reference from Advanatge

itm_num
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


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_

exp

date
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

source

promotion_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.

...

Versions Compared

Old Version 8

New Version 9

Key