Magento Business Intelligence Help Center

The Sales_Flat_Quote Table

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 list
Metric 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)
Was this article helpful?
0 out of 0 found this helpful

Comments

Powered by Zendesk