The sales_flat_quote
(quote
in Magento 2.x) table 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, many businesses delete records from this table after a certain set of criteria are met (i.e. delete all non-converted carts 60 days after created).
Note that analyzing historical abandoned carts is only possible if you do not delete records from the sales_flat_quote
table. If you do delete records, you will only be able to see the carts not yet removed from your database.
Common Native Columns
Column Name | Description |
entity_id | Unique identifier for the table. Each `entity_id` represents a unique cart |
created_at | Creation date for the cart, usually stored in UTC |
customer_id | Foreign key associated with the `customer_entity` table, if the customer is registered |
customer_email | Email address of the customer creating the cart. This will only contain a value if the customer is signed in to their account |
reserved_order_id | Foreign key associated with the `sales_flat_order` table, if the cart converted to an order |
is_active | Indicates status of a cart. By default all carts have a status of `is_active` = 1, unless they have converted to an order, in which case the `is_active` value switches to 0 |
subtotal | Sum of the prices for all items included in cart. Taxes, shipping, etc are not included. Matches the `subtotal` column in `sales_flat_order` for converted carts |
grand_total | Cart's total value, including subtotal, tax, shipping and discounts. Matches the `grand_total` column in `sales_flat_order` for converted carts |
items_count | Represents the count of distinct SKUs added to a cart |
items_qty | Sum of the quantity for all items included in the cart |
Common Calculated Columns
Column Name | Description |
Seconds since cart creation | Total elapsed time since the cart was created |
Seconds between cart creation and order | Total elapsed time from chart creation to conversion |
Cart's conversion date | Order date associated with a converted cart |
Common Metrics
Click to view full listMetric Name | Metric Description | Metric Construction |
Number of abandoned carts | The count of carts that meet specific "abandonment" conditions |
Operation Column: entity_id Operation: Count Timestamp Column: created_at Filters: [A] is_active = 1 [B] items_count > 0 [C] Seconds since cart creation > x, where "x" corresponds to the number of seconds since cart creation before it is considered abandoned |
Average time to cart conversion | The average time from cart creation to order creation for converted carts |
Operation Column: Seconds between cart creation and order Operation: Average Timestamp Column: created_at |
Abandoned cart value | The sum of the total abandoned cart value |
Operation Column: grand_total Operation: Sum Timestamp Column: created_at Filters: [A] is_active = 1 [B] items_count > 0 [C] Seconds since cart creation > x, where "x" corresponds to the number of seconds since cart creation before it is considered abandoned
|
Connections to Other Tables
customer_entity
- Create joined columns to segment and filter by customer-level attributes on the
sales_flat_quote
table via the following join: - Magento 1.x:
sales_flat_quote.customer_id
(many) =>customer_entity.entity_id
(one) - Magento 2.x:
quote.customer_id
(many) =>customer_entity.entity_id
(one)
sales_flat_order
- Link carts to order-level attributes, to calculate metrics like time to cart conversion along the following path:
- Magento 1.x:
sales_flat_quote.reserved_order_id
(many) =>sales_flat_order.increment_id
(one) - Magento 2.x:
quote.reserved_order_id
(many) =>sales_order.increment_id
(one)
sales_flat_quote_item
- Create joined columns to aggregate item-level details at the cart-level along the following join:
- Magento 1.x:
sales_flat_quote_item.quote_id
(many) =>sales_flat_quote.entity_id
(one) - Magento 2.x:
quote_item.quote_id
(many) =>quote.entity_id
(one)