# ccdw_fact_promotion_line_item This table records the application of promotions to individual line items. Each row represents a promotion line item in an order. | Column Alias | Data Type | Key | Description | Index? | | -------------------------------- | ------------ | ------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------ | | `promotion_line_item_id` | BIGINT | **PK** | Surrogate key uniquely identifying this promotion line item. Use for joins and deduplication. | Yes | | `promotion_id` | INTEGER | **FK** | Foreign key to [`ccdw_dim_promotion`](/docs/commerce/b2c-commerce/guide/jdbc_ccdw_dim_promotion.md). Use for promotion-level reporting and attribution. | Yes | | `sku_id` | INTEGER | **FK** | Foreign key to [`ccdw_dim_product`](/docs/commerce/b2c-commerce/guide/jdbc_ccdw_dim_product.md) for the SKU affected by the promotion. Use for product- or SKU-level promotion analysis. | Yes | | `promotion_instance_id` | VARCHAR(50) | | Unique key for one application of a promotion within an order. A single order-level promotion can generate multiple instances (one per qualifying line). | No | | `line_item_id` | BIGINT | **FK** | Foreign key to [`ccdw_fact_line_item`](/docs/commerce/b2c-commerce/guide/jdbc_ccdw_fact_line_item.md). Links to the fact line item for quantity, unit price, and other line-level details. | No | | `nline_item_id` | VARCHAR(50) | | Natural key for the line item in the source system, for example, OCAPI/SCAPI line item identifier). Normalized for cross-system use. | No | | `order_id` | BIGINT | **FK** | Surrogate key for the order. Use to join with [`ccdw_fact_line_item`](/docs/commerce/b2c-commerce/guide/jdbc_ccdw_fact_line_item.md) and other order-level facts. | Yes | | `norder_id` | VARCHAR(50) | | Natural key for the order in the source system, for example, order number or document ID). | No | | `utc_submit_timestamp` | TIMESTAMP | | UTC timestamp when the promotion was applied to the line item. Use for exact-time filtering and incremental loads. | Yes | | `utc_submit_day_id` | SMALLINT | **FK** | Foreign key to [`ccdw_dim_date`](/docs/commerce/b2c-commerce/guide/jdbc_ccdw_dim_date.md) for the UTC submission day. Use for UTC-based date partitioning and calendar joins. | Yes | | `utc_submit_time_id` | SMALLINT | **FK** | Foreign key to [`ccdw_dim_time`](/docs/commerce/b2c-commerce/guide/jdbc_ccdw_dim_time.md) for the UTC submission time. Use for time-of-day analysis in UTC. | No | | `utc_update_timestamp` | TIMESTAMP | | Last modified UTC timestamp for this record. Use for incremental loads or change detection. | Yes | | `std_submit_timestamp` | TIMESTAMP | | Timestamp when the promotion was applied, expressed in the realm's standard (reporting) time zone. | No | | `std_submit_day_id` | SMALLINT | **FK** | Foreign key to [`ccdw_dim_date`](/docs/commerce/b2c-commerce/guide/jdbc_ccdw_dim_date.md) for the standard submission day. Use for reporting-date partitioning and day-level reporting. | Yes | | `std_submit_time_id` | SMALLINT | **FK** | Foreign key to [`ccdw_dim_time`](/docs/commerce/b2c-commerce/guide/jdbc_ccdw_dim_time.md) for the standard submission time. Use for time-of-day analysis in reporting time zone. | No | | `std_timezone_id` | SMALLINT | **FK** | Foreign key to [`ccdw_dim_timezone`](/docs/commerce/b2c-commerce/guide/jdbc_ccdw_dim_timezone.md) for the time zone used to derive `std_submit_*` date and time columns (realm reporting time zone). | No | | `locale_id` | SMALLINT | **FK** | Foreign key to [`ccdw_dim_locale`](/docs/commerce/b2c-commerce/guide/jdbc_ccdw_dim_locale.md) (language and country). Represents the session locale when the promotion was applied. | Yes | | `site_id` | SMALLINT | **FK** | Foreign key to [`ccdw_dim_site`](/docs/commerce/b2c-commerce/guide/jdbc_ccdw_dim_site.md) for the site where the order was placed (submission site). | Yes | | `business_channel_id` | SMALLINT | **FK** | Foreign key to [`ccdw_dim_business_channel`](/docs/commerce/b2c-commerce/guide/jdbc_ccdw_dim_business_channel.md), for example, storefront, call center). Use for channel-level reporting. | Yes | | `user_agent_id` | INTEGER | **FK** | Foreign key to [`ccdw_dim_user_agent`](/docs/commerce/b2c-commerce/guide/jdbc_ccdw_dim_user_agent.md). Represents device or browser context when the promotion was applied. | Yes | | `campaign_id` | INTEGER | **FK** | Foreign key to [`ccdw_dim_campaign`](/docs/commerce/b2c-commerce/guide/jdbc_ccdw_dim_campaign.md) for the marketing campaign associated with this promotion, if any. Nullable. | No | | `source_code_group_id` | INTEGER | **FK** | Foreign key to [`ccdw_dim_source_code_group`](/docs/commerce/b2c-commerce/guide/jdbc_ccdw_dim_source_code_group.md). Use for source code or affiliate attribution. Nullable. | No | | `coupon_id` | INTEGER | **FK** | Foreign key to [`ccdw_dim_coupon`](/docs/commerce/b2c-commerce/guide/jdbc_ccdw_dim_coupon.md) for the coupon applied with this promotion line item, if any. Null when no coupon was used. | No | | `visitor_id` | VARCHAR(100) | | Anonymous or persistent visitor identifier from the session. Use for visit-level or anonymous-user analysis. | No | | `customer_id` | BIGINT | **FK** | Foreign key to [`ccdw_dim_customer`](/docs/commerce/b2c-commerce/guide/jdbc_ccdw_dim_customer.md). Use for customer-level promotion analysis. Might reference guest or registered customer. | 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`](/docs/commerce/b2c-commerce/guide/jdbc_ccdw_dim_currency.md) for the tendered (local) currency—the currency in which the order was placed. | No | | `std_currency_id` | SMALLINT | **FK** | Foreign key to [`ccdw_dim_currency`](/docs/commerce/b2c-commerce/guide/jdbc_ccdw_dim_currency.md) for the realm's reporting currency. All `std_*` monetary values in this row are converted to this currency. | No | | `shipping_geography_id` | INTEGER | **FK** | Foreign key to [`ccdw_dim_geography`](/docs/commerce/b2c-commerce/guide/jdbc_ccdw_dim_geography.md) for the shipping address (region/country). Use for geographic reporting. | No | | `invoice_geography_id` | INTEGER | **FK** | Foreign key to [`ccdw_dim_geography`](/docs/commerce/b2c-commerce/guide/jdbc_ccdw_dim_geography.md) for the invoice or billing address (region/country). | No | | `customer_geography_id` | INTEGER | **FK** | Foreign key to [`ccdw_dim_geography`](/docs/commerce/b2c-commerce/guide/jdbc_ccdw_dim_geography.md) for the customer's address (region/country). | No | | `quantity` | NUMERIC | | Number of units of the line item to which this promotion application applies (quantity purchased for this line). | No | | `lcl_li_promo_disc` | NUMERIC | | Line-item-level promotional discount amount in tendered (local) currency. | No | | `lcl_li_promo_disc_order_alloc` | NUMERIC | | Portion of an order-level promotional discount allocated to this line item (prorated), in tendered currency. | No | | `lcl_li_promo_disc_ship_alloc` | NUMERIC | | Portion of a shipping-level promotional discount allocated to this line item (prorated), in tendered currency. | No | | `lcl_li_manual_disc` | NUMERIC | | Line-item-level manual discount amount in tendered (local) currency. | No | | `lcl_li_manual_disc_order_alloc` | NUMERIC | | Portion of an order-level manual discount allocated to this line item (prorated), in tendered currency. | No | | `lcl_li_manual_disc_ship_alloc` | NUMERIC | | Portion of a shipping-level manual discount allocated to this line item (prorated), in tendered currency. | No | | `lcl_li_tax` | NUMERIC | | Line-item-level tax amount in tendered (local) currency. | No | | `lcl_li_tax_order_alloc` | NUMERIC | | Portion of order-level tax allocated to this line item (prorated), in tendered currency. | No | | `lcl_li_shipping` | NUMERIC | | Line-item-level shipping charge in tendered (local) currency. | No | | `lcl_li_shipping_order_alloc` | NUMERIC | | Portion of order-level shipping allocated to this line item (prorated), in tendered currency. | No | | `lcl_li_gross_merchandise_value` | NUMERIC | | Gross Merchandise Value (GMV) of the line item in tendered currency allocated to the promotion line item. | No | | `std_li_promo_disc` | NUMERIC | | Line-item-level promotional discount in standard (reporting) currency. Converted from tendered currency for cross-currency reporting. | No | | `std_li_promo_disc_order_alloc` | NUMERIC | | Portion of order-level promotional discount allocated to this line item (prorated), in standard reporting currency. | No | | `std_li_promo_disc_ship_alloc` | NUMERIC | | Portion of shipping-level promotional discount allocated to this line item (prorated), in standard reporting currency. | No | | `std_li_manual_disc` | NUMERIC | | Line-item-level manual discount in standard (reporting) currency. Converted from tendered currency. | No | | `std_li_manual_disc_order_alloc` | NUMERIC | | Portion of order-level manual discount allocated to this line item (prorated), in standard reporting currency. | No | | `std_li_manual_disc_ship_alloc` | NUMERIC | | Portion of shipping-level manual discount allocated to this line item (prorated), in standard reporting currency. | No | | `std_li_tax` | NUMERIC | | Line-item-level tax in standard (reporting) currency. Converted from tendered currency. | No | | `std_li_tax_order_alloc` | NUMERIC | | Portion of order-level tax allocated to this line item (prorated), in standard reporting currency. | No | | `std_li_shipping` | NUMERIC | | Line-item-level shipping charge in standard (reporting) currency. Converted from tendered currency. | No | | `std_li_shipping_order_alloc` | NUMERIC | | Portion of order-level shipping allocated to this line item (prorated), in standard reporting currency. | No | | `std_li_gross_merchandise_value` | NUMERIC | | Gross Merchandise Value (GMV) of the line item in standard currency allocated to the promotion line item. | No | | `traffic_source_id` | SMALLINT | **FK** | Foreign key to [`ccdw_dim_traffic_source`](/docs/commerce/b2c-commerce/guide/jdbc_ccdw_dim_traffic_source.md). 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 |