ccdw_fact_line

This table captures details of individual line items within orders. Each row represents a line item in a placed order.

Column Alias	Data Type	Key	Description	Index?
`line_item_id`	BIGINT	PK	Unique identifier for the line item.	Yes
`nline_item_id`	VARCHAR(50)		The natural key representing the line item in the source system.	No
`order_id`	BIGINT	FK	A surrogate key representing the order associated with the line item.	Yes
`norder_id`	VARCHAR(50)		The natural key representing the order in the source system.	Yes
`document_no`	VARCHAR(50)		Document number associated with the order.	No
`utc_submit_timestamp`	TIMESTAMP		UTC timestamp when the line item was submitted.	Yes
`utc_submit_day_id`	SMALLINT	FK	Foreign key to `ccdw_dim_date` for the UTC submission day.	Yes
`utc_submit_time_id`	SMALLINT	FK	Foreign key to `ccdw_dim_time` for the UTC submission time.	No
`utc_update_timestamp`	TIMESTAMP		Last modified UTC timestamp of this record.	Yes
`std_submit_timestamp`	TIMESTAMP		Timestamp when the line item was submitted, in standard time.	No
`std_submit_day_id`	SMALLINT	FK	Foreign key to `ccdw_dim_date` for the standard submission day.	Yes
`std_submit_time_id`	SMALLINT	FK	Foreign key to `ccdw_dim_time` for the standard submission time.	No
`std_timezone_id`	SMALLINT	FK	Foreign key to `ccdw_dim_timezone` for the standard time zone.	No
`sku_id`	INTEGER	FK	Foreign key to `ccdw_dim_product` for the SKU.	Yes
`locale_id`	SMALLINT	FK	Foreign key to `ccdw_dim_locale`.	Yes
`site_id`	SMALLINT	FK	Foreign key to `ccdw_dim_site` for the submission site.	Yes
`business_channel_id`	SMALLINT	FK	Foreign key to `ccdw_dim_business_channel`.	Yes
`user_agent_id`	INTEGER	FK	Foreign key to `ccdw_dim_user_agent`.	Yes
`source_code_group_id`	INTEGER	FK	Foreign key to `ccdw_dim_source_code_group`.	No
`visitor_id`	VARCHAR(100)		Unique identifier for a visitor session. This ID persists across sessions and is associated with an anonymous tracking cookie (for example, dwanonymous).	No
`customer_id`	BIGINT	FK	Foreign key to `ccdw_dim_customer`. For unregistered buyers, this value is `1` and can't distinguish one unregistered buyer from another.	Yes
`buyer_email`	VARCHAR(256)		Hashed email address of the buyer. If order was placed in session with tracking disabled (dw_dnt = 1) or shopper subsequently requested deletion of personal data, this will return `anonymous`	No
`is_anonymous`	BOOLEAN		Indicates whether customer is anonymous or registered.	No
`do_not_track`	BOOLEAN		Indicates whether the customer has opted out of tracking.	No
`lcl_currency_id`	SMALLINT	FK	Foreign key to `ccdw_dim_currency` for local currency.	No
`std_currency_id`	SMALLINT	FK	Foreign key to `ccdw_dim_currency` for standard currency.	No
`shipping_geography_id`	INTEGER	FK	Foreign key to `ccdw_dim_geography` for the shipping address provided at checkout. Populated for orders placed after October 2024.	No
`invoice_geography_id`	INTEGER	FK	Foreign key to `ccdw_dim_geography` for the billing or invoice address. Populated for orders placed after October 2024.	No
`customer_geography_id`	INTEGER	FK	Foreign key to `ccdw_dim_geography` for the customer's profile address. Populated for registered customers only (orders placed after October 2024).	No
`quantity`	NUMERIC		The number of units of this line item that were purchased.	No
`lcl_li_retail_price`	NUMERIC		Line item retail price in local currency.	No
`lcl_li_markdown`	NUMERIC		Line item markdown amount in local currency.	No
`lcl_li_markdown_price`	NUMERIC		Line item markdown price in local currency.	No
`lcl_li_unit_price`	NUMERIC		The unit price, before promotional or manual discounts, associated with the line item.	No
`lcl_li_markdown_disc`	NUMERIC		Line item markdown discount in local currency.	No
`lcl_li_promo_disc`	NUMERIC		The line item promotional discounts associated with the line item.	No
`lcl_li_promo_disc_order_alloc`	NUMERIC		The order level promotional discounts associated with the line item (pro-rated).	No
`lcl_li_promo_disc_ship_alloc`	NUMERIC		The line item shipping level promotional discounts associated with the line item (pro-rated).	No
`lcl_li_manual_disc`	NUMERIC		The line item manual discounts associated with the line item.	No
`lcl_li_manual_disc_order_alloc`	NUMERIC		The order level manual discounts associated with the line item (pro-rated).	No
`lcl_li_manual_disc_ship_alloc`	NUMERIC		The shipping level manual discounts associated with the line item (pro-rated).	No
`lcl_li_tax`	NUMERIC		Line item tax in local currency.	No
`lcl_li_tax_order_alloc`	NUMERIC		The order level tax (pro-rated).	No
`lcl_li_shipping`	NUMERIC		The line item level shipping.	No
`lcl_li_shipping_order_alloc`	NUMERIC		The order level shipping (pro-rated).	No
`lcl_li_gross_merchandise_value`	NUMERIC		The Gross Merchandise Value (GMV) of the line item in the tenderd currency. GMV is derived using the following row-based calculation `(quantity * lcl_li_unit_price) - ((lcl_li_promo_disc + lcl_li_manual_disc_order_alloc) + (lcl_li_manual_disc + lcl_li_manual_disc_order_alloc))`	No
`lcl_li_net_merchandise_value`	NUMERIC		Line item net merchandise value in tenderd currency. This is derived using the following row-based calculation `lcl_li_gross_merchandise_value + (lcl_li_tax + lcl_li_tax_order_alloc) + (lcl_li_shipping + lcl_li_shipping_order_alloc) - (lcl_li_promo_disc_ship_alloc + lcl_li_manual_disc_ship_alloc)`	No
`std_li_retail_price`	NUMERIC		Line item retail price in standard currency.	No
`std_li_markdown`	NUMERIC		Line item markdown amount in standard currency.	No
`std_li_markdown_price`	NUMERIC		Line item markdown price in standard currency.	No
`std_li_unit_price`	NUMERIC		The unit price, before promotional or manual discounts, associated with the line item.	No
`std_li_markdown_disc`	NUMERIC		Line item markdown discount in standard currency.	No
`std_li_promo_disc`	NUMERIC		The line item promotional discounts associated with the line item.	No
`std_li_promo_disc_order_alloc`	NUMERIC		The order level promotional discounts associated with the line item (pro-rated).	No
`std_li_promo_disc_ship_alloc`	NUMERIC		The line item shipping level promotional discounts associated with the line item (pro-rated).	No
`std_li_manual_disc`	NUMERIC		The line item manual discounts associated with the line item.	No
`std_li_manual_disc_order_alloc`	NUMERIC		The order level manual discounts associated with the line item (pro-rated).	No
`std_li_manual_disc_ship_alloc`	NUMERIC		The shipping level manual discounts associated with the line item (pro-rated).	No
`std_li_tax`	NUMERIC		The line item level tax.	No
`std_li_tax_order_alloc`	NUMERIC		The order level tax (pro-rated).	No
`std_li_shipping`	NUMERIC		The line item level shipping.	No
`std_li_shipping_order_alloc`	NUMERIC		The order level shipping (pro-rated).	No
`std_li_gross_merchandise_value`	NUMERIC		The Gross Merchandise Value (GMV) of the line item in the standard currency. GMV is derived using the following row-based calculation `(quantity * std_li_unit_price) - ((std_li_promo_disc + std_li_manual_disc_order_alloc) + (std_li_manual_disc + std_li_manual_disc_order_alloc))`	No
`std_li_net_merchandise_value`	NUMERIC		Line item net merchandise value in standard currency. This is derived using the following row-based calculation `std_li_gross_merchandise_value + (std_li_tax + std_li_tax_order_alloc) + (std_li_shipping + std_li_shipping_order_alloc) - (std_li_promo_disc_ship_alloc + std_li_manual_disc_ship_alloc)`	No
`traffic_source_id`	SMALLINT	FK	Foreign key to `ccdw_dim_traffic_source`. Identifies the attributed external traffic source for this order.	No
`traffic_campaign`	VARCHAR		Campaign name associated with the attributed external traffic.	No
`referer_net_loc`	VARCHAR		Network location (hostname) of the referring site.	No
`referer_term`	VARCHAR		Search term from the referring site, when present.	No

ccdw_fact_line_item