Subscription (CF)

Item Code

item_code

pub_cde

itm_num

Source Code

key_code

key_cde

pmo_cde

Subscription Status

subscription_status

crc_sts

agr_sts

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

Active Reason Code

active_reason_code

crc_sts

agr_sts

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'

Cancellation Reason Code

cancellation_reason_code

crc_sts

agr_sts

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

Suspension Reason Code

suspension_reason_code

crc_sts

agr_sts

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'

Expiration Date

expiration_date

exp_dte

exp_dte

Billing Status 

billing_status

bil_sts

bil_sts

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

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

Final Issue to go

final_issues_to_go

fin_itg

Issue to go

issue_to_go

itg

Future Term

exp_dte

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'

Promotion Choice

promotion_choice

pmo_chc

pmo_chc

Price

price

rate

unit_prc

Renewal Type

renewal_type

rn_flg

agr_ren

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

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

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.