Magento Business Intelligence Help Center

The Sales_Flat_Order Table

The sales_flat_order table is where each transaction is stored. Each row represents one transaction.

This table may store customers which checkout via guest checkout. If your store accepts guest orders please find more information about this use case: here.

 

Common Columns

Column Name Description
entity_id Unique identifier for the table, usually considered the 'order_id'
created_at Creation date timestamp of the order, 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 placing the order. This will be populated in all situations, including guest checkouts
status Order's status. May return values such as 'complete', 'processing', 'cancelled', 'refunded'. Subject to changes as the order gets processed
billing_address_id  Foreign key associated with the `sales_flat_order_address` table
shipping_address_id Foreign key associated with the `sales_flat_order_address` table
subtotal Order's price of items included in the order. Taxes, shipping, etc are not included
grand_total Order's total value, including subtotal, tax, shipping, and discounts
tax_amount Tax value applied to order 
shipping_amount Shipping value applied to order 
discount_amount Discount value applied to order 
coupon_code Associated coupon applied to order 

 

Common Calculated Columns

Column Name Description
Customer's order number This is the chronological order number associated with this customer. A customer's first order will always return a one here. A customer's second order will always return a two
Seconds since previous order The number of seconds between an order and the customer's previously completed order
Seconds between customer's first order date and this order The number of seconds between an order and the customer's first completed order
Number of items in order The quantity of items included in an order
Billing address state The state (location) associated with this order's billing address
Shipping address state The state (location) associated with this order's shipping address

 

Common Metrics

Click to view full list
Metric Name Metric Description Metric Construction
Average order value The average value of orders

Operation Column: grand_total 

Operation: Average 

Timestamp Column: created_at

Average time between orders The average time between orders

Operation Column: Seconds since previous order 

Operation: Average 

Timestamp Column: created_at

 
Number of Orders The number of new orders placed

Operation Column: entity_id 

Operation: Count 

Timestamp Column: created_at 

 
Revenue The revenue generated  

Operation Column: grand_total 

Operation: Sum 

Timestamp Column: created_at

Distinct Customers The number of unique customers (identified via email address), given the time interval. For example if the report's interval was By Week, a customer would appear once in that week, regardless of the number of orders placed, as long as they placed a single order   

Operation Column: customer_email 

Operation: Count Distinct 

Timestamp Column: created_at

 

Connections to Other Tables

customer_entity

  • Create joined columns to segment and filter by customer-level attributes on the sales_flat_order table via the following join:
  • sales_flat_order.customer_id (many) => customer_entity.entity_id (one)

sales_flat_order_address

  • Create joined columns related to shipping and billing address fields via the following joins:
  • sales_flat_order.shipping_address_id (many) => sales_flat_order_address.entity_id (one)
  • sales_flat_order.billing_address_id (many) => sales_flat_order_address.entity_id (one)
Was this article helpful?
0 out of 0 found this helpful

Comments

Powered by Zendesk