Orders (bi_orders)

Overview: This is a stream of all order, refund, and chargebacks received. These transactions indicate cases where money is exchanged and would not include comped orders, test orders, credit card declines, or orders paid with account credits.

Frequency: Daily

Data Source: Circbase

Delivery Method: SFTP upload to csv import work

Filename: Orders_<Lytics Group>_Date_Time.txt

LQL Advice: Most of these fields will need to be mapped to Item for context.

Field Name	Description	Source Field	Sample Value
Transaction_Date	The date of the transaction	Tran_Deposit_Date	2020-12-11 00:00:00 -05:00
Item	Item code that was purchased/refunded, concatenated with its description	Tran_Pub_Code	INA - The Savvy Retiree
Currency	Currency that was used in the transaction	Tran_Currency_Code	USD
Amount	Amount of the transaction	Tran_Amount	42.11
Tax_Amount	Tax amount of the transaction	Tran_PS_Tax	7.95
Freight_Amount	Freight/shipping amount of the transaction	Tran_PS_Freight	7.95
Order_Number	Advantage identifier of the order	Tran_Dbt_no	TX112296
Promotion	Promo Code from Advantage associated with the order	Tran_Source_code	EILVV968 - 5 Free Issues Front End - ILPG
Customer_Number	Advantage identifier of the customer	MSTORD_M.CTM_NBR	000012345678
Subscription_Reference	Advantage identifier of the subscription record	CIREVT_M.SUB_REF	000012345678
Subscription_Term_Number	The term number of the subscription that was purchased for this customer	CIREVT_M.TRM_NBR	2
Acquisition_Method	Code and description indicating what marketing channel the order was acquired through		WI - Website Internal
Channel	First letter of the promo code, plus description, which indicates which channel the promotion was marketed		W - Your Website
Campaign	The campaign that the promo code was associated with		EILVX1D - 295999-2021 - Retirement Index
Internal_List_Code	The internal list code where the promo was marketed (if applicable)		ILPOSTCA
External_List_Code	The external list code where the promo was marketed (if applicable)		FACEBOOK
Affiliate	The group who owns the item sold		International Living
Transaction_Type	The type of transaction	Derived field	Payment Refund Payment Reversal Chargeback Chargeback Reversal
Owning_Organization	The org code indicating who owns the publication	CDSITM_M.OWN_ORG	120
ChoiceCode	The choice groups together details about the subscription purchase (e.g. amount charged, length of subscription)	Tran_Choice	49NON
Email_Address	Email of the customer associated with the order	Derived field (orders aren't associated with an email in Circbase natively)	example@sample.com
Item_Code	Item code that was purchased/refunded (without the concatenated description)	Tran_Pub_Code	INA