Data Refactor - Subscription (CF)

Item Code

pub_cde

itm_num

item_code

Source Code

key_cde

pmo_cde

key_code

Subscription Status

crc_sts

agr_sts

subscription_status

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

crc_sts

agr_sts

active_reason_code

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

crc_sts

agr_sts

cancellation_reason_code

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

crc_sts

agr_sts

suspension_reason_code

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

exp_dte

exp_dte

expiration_date

Billing Status 

bil_sts

bil_sts

billing_status

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

eml_sta

email_active

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

Final Expire

fin_exp

afin_date

final_expire

Final Issue to go

fin_itg

final_issues_to_go

Issue to go

itg

issue_to_go

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

pmo_chc

pmo_chc

promotion_choice

Price

rate

unit_prc

price

Renewal Type

rn_flg

agr_ren

renewal_type

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

lrnw_dte

last_renewal_date

Membership Type

mbr_org

membership_type

Date Joined

org_ctgd

date_joined

Subscription Type

sub_typ

sub_typ

subscription_type

Term Length

term

term_length

Last Renewal Key Code

lrnw_key

last_renewal_key_code

Membership Level

mbr_cat

membership_level

Acquisition Method

pmo_acq

pmo_acq

acquisition_method

Campaign Code

cpn_cde

cpn_cde

campaign_code

Creative Code

ctv_cde

ctv_cde

creative_code

Offer Code

ofr_cde

ofr_cde

offer_code