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