sales_order_item
TableThe sales_order_item
table (sales_flat_order_item
on M1) contains records of all products that were purchased in an order. Each row represents a unique sku
included in an order. The quantity of units that were purchased for a specific sku
is most often represented by the qty_ordered
field.
The sales_order_item
captures details on all product types that were purchased. A common practice in Adobe Commerce is to offer configurable products, or in other words, a product that can be customized according to size, color, and other product attributes. Although a configurable product has its own sku
, it can relate to multiple simple products, where each simple product represents a unique product configuration. Refer to configuring products for more information.
For example, consider a configurable product such as a t-shirt. When a customer checks out, they select options to alter the color and size. If the customer selects a color of blue
, and a size of small
, they end up purchasing a simple product like t-shirt-blue-small
which relates back to the parent product of t-shirt
.
When a configurable product is included in an order, two rows are generated in the sales_order_item
table: one for the simple sku
and one for the configurable parent. These two records in the sales_order_item
table can be related to each other through the following join:
sales_order_item.parent_item_id
=> (configurable) sales_order_item.item_id
Therefore it is possible to report on sales of products either at the simple level or at the configurable level. By default, all standard order-item-level
metrics in Commerce Intelligence are configured to exclude the simple products, and only report on the configurable versions. This is accomplished through the Ordered products we count
filter set, which filters on the condition where parent_item_id
is NULL
.
Column Name | Description |
---|---|
base_price |
Price of an individual unit of a product at the time of sale after catalog price rules, tiered discounts, and special pricing are applied and before any taxes, shipping, or cart discounts are applied. This is represented in the base currency of the store. |
created_at |
Creation timestamp of the order item, stored locally in UTC. Depending on your configuration in Commerce Intelligence, this timestamp may be converted to a reporting time zone in Commerce Intelligence that differs from your database time zone. |
item_id (PK) |
Unique identifier for the table. |
name |
Text name of the order item. |
order_id |
Foreign key associated with the sales_order table. Join to sales_order.entity_id to determine order attributes associated with the order item. |
parent_item_id |
Foreign key that relates a simple product to its parent bundle or configurable product. Join to sales_order_item.item_id to determine parent product attributes associated with simple product. For parent order items (that is, bundle or configurable product types), the parent_item_id is NULL . |
product_id |
Foreign key associated with the catalog_product_entity table. Join to catalog_product_entity.entity_id to determine product attributes associated with the order item. |
product_type |
Type of product that was sold. Potential product types include: simple, configurable, grouped, virtual, bundle, and downloadable. |
qty_ordered |
Quantity of units included in the cart for the particular order item at the time of sale. |
sku |
Unique identifier for the order item that was purchased. |
store_id |
Foreign key associated with the store table. Join to store.store_id to determine which Commerce store view associated with the order item. |
Column Name | Description |
---|---|
Customer's email |
Email address of the customer placing the order. Calculated by joining sales_order_item.order_id to sales_order.entity_id and returning the customer_email field. |
Customer's lifetime number of orders |
Total count of orders placed by this customer. Calculated by joining sales_order_item.order_id to sales_order.entity_id and returning the Customer's lifetime number of orders field. |
Customer's lifetime revenue |
Sum total of revenue for all orders placed by this customer. Calculated by joining sales_order_item.order_id to sales_order.entity_id and returning the Customer's lifetime revenue field. |
Customer's order number |
Sequential order rank for this customer’s order. Calculated by joining sales_order_item.order_id to sales_order.entity_id and returning the Customer's order number field. |
Order item total value (quantity * price) |
Total value of an order item at the time of sale after catalog price rules, tiered discounts, and special pricing are applied and before any taxes, shipping, or cart discounts are applied. Calculated by multiplying the qty_ordered by the base_price . |
Order's coupon_code |
Coupon applied to the order. Calculated by joining sales_order_item.order_id to sales_order.entity_id and returning the coupon_code field. |
Order's increment_id |
Unique identifier of the order. Calculated by joining sales_order_item.order_id to sales_order.entity_id and returning the increment_id field. |
Order's status |
Status of the order. Calculated by joining sales_order_item.order_id to sales_order.entity_id and returning the status field. |
Store name |
Name of the Commerce store associated with the order item. Calculated by joining sales_order_item.store_id to store.store_id and returning the name field. |
Metric Name | Description | Construction |
---|---|---|
Products ordered |
The total quantity of products included in carts at the time of sale | Operation: Sum Operand: qty_ordered Timestamp: created_at |
Revenue by products ordered |
Total value of products included in carts at the time of sale after catalog price rules, tiered discounts, and special pricing are applied and before any taxes, shipping, or cart discounts are applied | Operation: Sum Operand: Order item total value (quantity * price) Timestamp: created_at |
Foreign Key
Joining Pathscatalog_product_entity
catalog_product_entity
table to create columns that return product attributes associated with the order item.
sales_order_item.product_id
(many) => catalog_product_entity.entity_id
(one)sales_order
sales_order
table to create new order-level columns associated with the order item.
sales_order_item.order_id
(many) => sales_order.entity_id
(one)sales_order_item
sales_order_item
to create columns that associate details of the parent configurable or bundle SKU with the simple product. Contact support for assistance in configuring these calculations, if building in the Data Warehouse manager.
sales_order_item.parent_item_id
(many) => sales_order_item.item_id
(one)store
store
table to create columns that return details related to the Commerce store associated with the order item.
sales_order_item.store_id
(many) => store.store_id
(one)