Document toolboxDocument toolbox

Subscription (bi_subscriptions)

Owned by Chris Laun
Feb 28, 2023
3 min read

Overview: This is an incremental batch feed of subscription events.

Frequency: Every 30 minutes
Data Source: Advantage
Delivery Method: SFTP upload to csv import work
Filename: Subscriptions_<Lytics_Group>_<Date>_<Time>.txt
LQL Advice: Most of these fields will need to be mapped to Publication_Code for context.
Field NameDescriptionSource FieldSample Value
Publication_NameA concatenation of a pub code and its descriptionCDSITM_M.ITM_NUM
CDSITM_M.ITM_DSC1		PUB - Cool Pub Name
Publication_TypeA grouping of the publicationCDSITM_M.PROD_FAMFRONTEND
Customer_NumberAdvantage identifier for this customerCIRSUB_M.CTM_NBR000012345678
Subscription_ReferenceAdvantage identifier for this subscription recordCIRSUB_M.SUB_REF000012345678
Subscription_Term_NumberThe number of times the pub has been purchased/renewedCIRSUB_M.TRM_NBR1
Term_LengthA derived field that converts Subscription_Issues into a text value<n/a>Annual
Subscription_IssuesThe total number of issues to be fulfilled in the current termCIRSUB_M.TERM52
Issues_To_GoThe number of issues left on the current term. Generally its better to use Final_Issues_To_GoCIRSUB_M.ITG10
AmountThe monetary amount from the current termCIRSUB_M.RATE

50

Order_DateThe date that the current term was orderedCIRSUB_M.CTG_DTE2021-01-01
Billing_StatusConcatenated billing status code and descriptionCIRSUB_M.BIL_STS

P - Paid Up
M - Cancellation Credit Memo
C - Complimentary
B - Billed
R - Reinstated Subscription

Order_NumberAdvantage identifier for the order recordCIRSUB_M.ORD_NBRAA012345
Circulation_StatusStatus code that indicates whether the subscription is active or otherwise. Concatenated with its descriptionCIRSUB_M.CRC_STS

R - Regular, Receiving Magazine
C - Customer Request Canceled
P - Perpetual Subscription
N - Non-Pay Canceled by System
E - Expired
B - Billing Suspended
U - Undeliverable as Addressed
S - Indefinite Suspension
T - Temporary Suspension
K - In Error
W - Future Start

Owning_OrganizationThe org code indicating who owns the publicationCDSITM_M.OWN_ORG100
Renewal_FlagIndicator of whether and when the subscription will be renewed. Concatenated with the description.CIRSUB_M.RN_FLG

C - Autocharge
A - Lifetime or Comp
U - Closed Account
Y - Initial Order Non-AR
D - Discontinue AR
Z - Limited Renewals
N - Do Not Renew
CIR/AMB-Do not Promote for Renewal
CIR-Binding Issues
CIR-Promote to Donee
AMB-Special Renewal Handling

AffiliateThe owner of the publication
International Living
PromotionPromo Code and description from the promo code on the subscriptionCIRSUB_M.PMO_CDEEABCS100 - New Promotion
CampaignCampaign Code and description that the promo code belongs to.CPNPMO_M.CPN_CDEEABCS1A - Cool Campaign
OfferOffer Code and description from the promo code on the subscriptionCPNPMO_M.OFR_CDEOFFERTEST - Offer example
Acquisition_MethodCode and description indicating what channel the subscription was purchased throughCIRSUB_M.BIL_CURWI - Website Internal
Cancel_Policy

Cancel Policy Code and description from the promo code on the subscription


CPT101 - CIR: full 60days; no refund after 60
CreativeCreative Code and version from the promo code on the subscriptionCPN_PMO.CTV_CDECTVCODE - 01
Delivery_MethodDelivery Method and descriptionCIRSUB_M.DLV_CDEEM - Email
CurrencyBilling currency
USD
Last_Login_UTCLast website login sourced from Advantage

2020-12-31

Member_CategoryThe member category that this subscription belongs toMBRMBR_M.MBR_CATMB - Member Category
Member_OrganizationThe member organization that this subscription belongs toCIRSUB_M.MBR_ORGM2 - Member Org
Current_Term_Remaining_LiabilityRemaining Liability on the current term. Generally preferred to use Total_Remaining_Liability.CIRSUB_M.REM_LIAB10
Total_Remaining_LiabilityTotal Remaining Liability for this subscription, including any future terms.CIRSUB_M.TOT_LIAB20
Final_Issues_To_GoThe total number of issues left to fulfill for this subscription, including any future termsCIRSUB_M.FITG10
Final_Expiration_DateThe date that this subscription will expire, including any future termsCIRSUB_FIN_EXP2021-12-31
Future_Terms_FlagDerived Y/N indicator whether this subscription has any future terms<n/a>N
Payment_CodeIndicates how the subscription was paid forCIRSUB_M.PAY_CODEMC - MasterCard
Payment_TypeIndicates how the subscription was paid forCIRSUB_M.PAY_TYPECC - Credit Card
Subscription_ChoiceThe subscription choice groups together details about the subscription purchase (e.g. amount charged, length of subscription)CIRDTL_M.SUB_CHC49ILVM - US$49 - YEARLY A/R
Promotion_ChoiceThe promotion choice groups together details about the subscription purchase (e.g. amount charged, length of subscription)CIRSUB_M.PMO_CHC89ILVS - ILV 2 Yr - Second Passport - $89
EmailAddressEmail address from the subscriptionCDSEML_M.ADR_EMAL (using CIREML_M.EMAL_ID)sample@example.com
OperationCode associated with CDC changes in Advantage.
U
Cancel_ReasonCancel reason code and description that indicate the reason given for the customer's cancellation of the subscriptionCIRSUB_M.CAN_REAC-CM - Changed Mind
Operation_TimeTimestamp of the event
2020-11-21 20:00:00.0000000 -05:00
Last_AR_Notice_SentDate of the most recent auto renew notice for this subscription
2021-01-01
Subscription_TypeSubscription type codeCIRSUB_M.SUB_TYPLIFE
Publication_CodePublication Code. Similar to Publication_Name but without the descriptionCIRSUB_M.PUB_CDEILV
First_DateThe date of the first issue sent for this subscriptionCIRSUB_M.FRST_DTE2021-01-01
Email_StatusStatus of this email address with this subscriptionCIRSUB_M.EML_STAA
Is_Active_PubIndicator whether the pub code is currently being sold. Can be used to limit the events written to customer profiles in Lytics.
Y
com.atlassian.confluence.content.render.xhtml.migration.exceptions.UnknownMacroMigrationException: The macro 'html-macro' is unknown.