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. Use for promotion-level reporting and attribution. |
Yes |
sku_id |
INTEGER | FK | Foreign key to ccdw_dim_product 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. 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 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 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 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 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 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 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 (language and country). Represents the session locale when the promotion was applied. |
Yes |
site_id |
SMALLINT | FK | Foreign key to ccdw_dim_site for the site where the order was placed (submission site). |
Yes |
business_channel_id |
SMALLINT | FK | Foreign key to ccdw_dim_business_channel, for example, storefront, call center). Use for channel-level reporting. |
Yes |
user_agent_id |
INTEGER | FK | Foreign key to ccdw_dim_user_agent. Represents device or browser context when the promotion was applied. |
Yes |
campaign_id |
INTEGER | FK | Foreign key to ccdw_dim_campaign 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. Use for source code or affiliate attribution. Nullable. |
No |
coupon_id |
INTEGER | FK | Foreign key to ccdw_dim_coupon 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. 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 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 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 for the shipping address (region/country). Use for geographic reporting. |
No |
invoice_geography_id |
INTEGER | FK | Foreign key to ccdw_dim_geography for the invoice or billing address (region/country). |
No |
customer_geography_id |
INTEGER | FK | Foreign key to ccdw_dim_geography 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. 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 |