Page Comparison

Item Code

item_code

pub_cde

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.

Customer Number

advantage_customer_number

ctm_nbr

ctm_nbr

Customer Number from Advantage

Source Code

source_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

abil_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

ambeml_m.eml_sta

SUB

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

AMB

CASE

WHEN Trim(aeml.eml_sta) IN ('A','') THEN 'Active'

WHEN aeml.eml_sta = 'I' THEN 'OptOut'

WHEN aeml.eml_sta = 'B' THEN 'Bounce'

WHEN aeml.eml_sta = 'X' THEN 'CsSpam'

WHEN aeml.eml_sta = 'S' THEN 'Spam'

WHEN aeml.eml_sta IN ('C','Q') THEN 'Cleaned'

ELSE 'UNKNOWN'

END AS email_status

Final Expire

final_expire

fin_exp

afin_

exp

Final Issue to go

final_issues_to_go

fin_itg

✖️

Issue to go

issues_to_go

itg

✖️

Term Number

term_number

trm_nbr

agr_trm

Future Term

future_term

✖️

✖️

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_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

lrnw_dte

Membership Type

membership_type

mbr_org

✖️

Date Joined

date_joined

org_ctgd

ctg_dte

Subscription Type

subscription_type

sub_typ

sub_typ

Term Length

term_length

term

agr_day

Last Renewal Key Code

last_renewal_key_code

lrnw_key

lrnw_pmo

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.