Common Calculated Columns

Column NameDescription
Billing address cityBilling city for the order. Calculated by joining sales_order.billing_address_id to sales_order_address.entity_id and returning the city field
Billing address countryBilling country code for the order. Calculated by joining sales_order.billing_address_id to sales_order_address.entity_id and returning the country_id
Billing address regionBilling region (most often state or province) for the order. Calculated by joining sales_order.billing_address_id to sales_order_address.entity_id and returning the region field
Customer's first order dateTimestamp of the first order placed by this customer. Often considered to be the “acquisition date” for a customer. Calculated by returning the minimum sales_order.created_at value for each unique customer
Customer's first order's billing regionAcquisition billing region for the customer who placed the order. Calculated by returning the Billing address region associated with the customer’s first order
Customer's first order's coupon_codeAcquisition coupon code for the customer who placed this order. Calculated by returning the coupon_code associated with the customer’s first order
Customer's group codeGroup name of the customer who placed this order. Calculated by joining sales_order.customer_group_id to customer_group.customer_group_id and returning the customer_group_code field
Customer's lifetime number of couponsTotal quantity of coupons applied to all orders placed by this customer. Calculated by counting the number of orders where the coupon_code is not NULL for each unique customer
Customer's lifetime number of ordersTotal count of orders placed by this customer. Calculated by counting the number of rows in the sales_order table for each unique customer
Customer's lifetime revenueSum total of revenue for all orders placed by this customer. Calculated by summing the base_grand_total field for all orders for each unique customer
Customer's order numberSequential order rank for this customer’s order. Calculated by identifying all orders placed by a customer, sorting them in ascending order by the created_at timestamp, and assigning an incrementing integer value to each order. For example, the customer’s first order returns a Customer's order number of 1, the customer’s second order returns a Customer's order number of 2, and so on.
Customer's order number (previous-current)Rank of customer’s previous order concatenated with the rank of this order, separated by a - character. Calculated by concatenating (“Customer's order number - 1”) with “-” followed by “Customer's order number”. For example, for the order associated with the customer’s second purchase, this column returns a value of 1-2. Most often used when representing the time between two order events (that is, in the “Time between orders” chart)
Is customer's last order?Determines whether the order corresponds to the customer’s last, or most recent, order. Calculated by comparing the Customer's order number value with Customer's lifetime number of orders. When these two fields are equal for the given order, this column returns Yes; otherwise it returns No
Number of items in orderTotal quantity of items included in the order. Calculated by joining sales_order.entity_id to sales_order_item.order_id and summing the sales_order_item.qty_ordered field
Seconds between customer's first order date and this orderElapsed time between this order and the customer’s first order. Calculated by subtracting Customer's first order date from the created_at for each order, returned as an integer number of seconds
Seconds since previous orderElapsed time between this order and the customer’s immediately preceding order. Calculated by subtracting the created_at for the previous order from the created_at of this order, returned as an integer number of seconds. For example, for the order record corresponding to a customer’s third order, this column returns the number of seconds between the customer’s second order and third order. For the customer’s first order, this field returns NULL
Shipping address cityShipping city for the order. Calculated by joining sales_order.shipping_address_id to sales_order_address.entity_id and returning the city field
Shipping address countryShipping country code for the order. Calculated by joining sales_order.Shipping_address_id to sales_order_address.entity_id and returning the country_id
Shipping address regionShipping region (most often state or province) for the order. Calculated by joining sales_order.shipping_address_id to sales_order_address.entity_id and returning the region field
Store nameThe name of the Commerce store associated with this order. Calculated by joining sales_order.store_id to store.store_id and returning the name field

Common Metrics

Metric NameDescriptionConstruction
Avg order valueThe average revenue per order, where revenue is defined as the base_grand_totalOperation: Average
Operand: base_grand_total
Timestamp: created_at
Avg time between ordersThe average time between a customer’s (n-1) order and nth order, for all customers and ordersOperation: Average
Operand: Seconds since previous order
Timestamp: created_at
GMVThe sum of the gross merchandise value for all orders, where GMV is defined as the subtotal, before all taxes and discounts are appliedOperation: Sum
Operand: base_subtotal
Timestamp: created_at
Median time between ordersThe median time between a customer’s (n-1) order and nth order, for all customers and ordersOperation: Median
Operand: Seconds since previous order
Timestamp: created_at
OrdersThe total count of orders placedOperation: Count
Operand: entity_id
Timestamp: created_at
RevenueThe sum of the revenue for all orders, where revenue is defined as the final price paid by the customer, after all taxes, discounts, shipping, and so on, are appliedOperation: Sum
Operand: base_grand_total
Timestamp: created_at
ShippingThe sum of the shipping amount for all ordersOperation: Sum
Operand: base_shipping_amount
Timestamp: created_at
TaxThe sum of the taxes applied to all ordersOperation: Sum
Operand: base_tax_amount
Timestamp: created_at
Unique CustomersThe number of unique customers placing an order in the given reporting time interval. For example if the report’s interval was weekly, each customer who places at least one order in a given week is counted exactly once, regardless of how many orders they placed in that weekOperation: Count Distinct
Operand: customer_email
Timestamp: created_at