Quote Table

The quote table (sales_flat_quote on M1) contains records on every shopping cart created in your store, whether they were abandoned or converted to a purchase. Each row represents one cart. Due to the potential size of this table, Adobe recommends you periodically delete records if certain criteria are met, such as if there are any unconverted carts older than 60 days.

NOTE

Analyzing historical, abandoned carts is only possible if you do not delete records from the quote table. If you do delete records, you are only able to see the carts not yet removed from your database.

Common Native Columns

Column Name Description
base_currency_code Currency for all values captured in base_* fields (that is base_grand_total, base_subtotal, and so on). This typically reflects the Commerce store’s default currency
base_grand_total Final price quoted to the customer for the cart, after all taxes, shipping, and discounts are applied. Although the precise calculation is customizable, in general the base_grand_total is calculated as base_subtotal + base_tax_amount + base_shipping_amount + base_discount_amount - base_gift_cards_amount - base_customer_balance_amount
base_subtotal Gross merchandise value of all items included in the cart. Taxes, shipping, discounts, and so on, are not included
created_at Creation timestamp of the cart, 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
customer_email Email address of the customer who created the cart
customer_id Foreign key associated with the customer_entity table, if the customer is registered. Join to customer_entity.entity_id to determine customer attributes associated with the user who created the cart. If the cart was created through guest checkout, this field is NULL
entity_id (PK) Unique identifier for the table, and commonly used in joins to other tables within the Commerce instance
is_active Boolean field that returns “1” if the cart was created by a customer and has not yet converted to an order. Returns “0” for converted carts, or carts created through the admin
items_qty Sum of the total quantity of all items included in the cart
reserved_order_id Foreign key associated with the sales_order table. Join to sales_order.increment_id to determine order details associated with a converted cart. For carts that are not associated with a converted order, the reserved_order_id remains NULL
store_id Foreign key associated with the store table. Join to store.store_id to determine which Commerce store view is associated with the cart

Common Calculated Columns

Column Name Description
Order date Timestamp reflecting order creation date for converted carts. Calculated by joining quote.reserved_order_id to sales_order.increment_id and returning the sales_order.created_at field
Seconds between cart creation and order Elapsed time between cart creation and order creation. Calculated by subtracting created_at from Order date, returned as an integer number of seconds
Seconds since cart creation Elapsed time between the cart’s creation date and now. Calculated by subtracting created_at from the server timestamp at the time the query is executed, returned as an integer number of seconds. Most commonly used to identify the age of a cart
Store name The name of the Commerce store associated with this order. Calculated by joining quote.store_id to store.store_id and returning the name field

Common Metrics

Metric Name Description Construction
Number of abandoned carts The count of carts that meet specific “abandonment” conditions Operation: Count
Operand: entity_id
Timestamp: created_at
Filters:

- [A] is_active = 1
- [B] items_count > 0
- [C] Seconds since cart creation > x, where “x” corresponds to the elapsed time (in seconds) since cart creation beyond which a cart is considered abandoned
Avg time to cart conversion The average time between cart creation and order creation for converted carts Operation: Average
Operand: Seconds between cart creation and order
Timestamp: created_at
Abandoned cart value The sum of the total abandoned cart potential revenue, where revenue is defined as the base_grand_total field Operation: Sum
Operand: base_grand_total
Timestamp: created_at
Filters:

- [A] is_active = 1
- [B] items_count > 0
- [C] Seconds since cart creation > x, where “x” corresponds to the elapsed time (in seconds) since cart creation beyond which a cart is considered abandoned

Foreign Key Joining Paths

customer_entity

  • Join to customer_entity table to create new customer-level columns associated with the customer who created the cart.
    • Path: quote.customer_id (many) => customer_entity.entity_id (one)

sales_order

  • Join to sales_order table to create columns that return order details associated with a converted cart.
    • Path:quote.reserved_order_id (many) => sales_order.increment_id (one)

store

  • Join to store table to create columns that return details related to the Commerce store associated with the cart.
    • Path: quote.store_id (many) => store.store_id (one)

On this page