Order metrics dashboard data

Optimizely Commerce Connect has a dashboard that displays metrics when you log in, including Total Sales, Average Order Value, GMV, and Orders.

Query data

Commerce Connect has a table (OrderPerHourData) to improve performance for querying data on the dashboard. This table collects order data per hour by running a scheduled job.

The table fields include the following:

• DateTimeByHour – Format by time "yyyyMMddHH"
• SiteId
• MarketId
• Currency
• OrderCount – Total completed orders plus total partial orders
• GMVValue – Total of PlacedPrice multiplied by item quantity in the current hour
• ShippingValue – Total shipping value on shipments in the current hour
• HandlingValue – Total handling value on completed or partial orders
• ShippingDiscountValue – Total shipping discount value on shipments in the current hour
• ShippingTaxValue – Total shipping tax value on shipments in the current hour
• ShippingTaxIncludeInShippingValue – Total shipping tax value included in the shipping value in the current hour
• SalesTaxValue – Total tax value on LineItem in the current hour
• SalesTaxIncludeInPriceValue – Total tax value included in the price of LineItem in the current hour
• OrderLevelDiscountValue – Total discount on order level in the current hour
• LineItemDiscountValue – Total discount on LineItem level in the current hour
• ReturnValue – Total returned items value in the current hour. This is calculated by multiplying PlacedPrice by ReturnQuantity and subtracting SalesTaxValue (in case the price does not include tax), OrderLevelDiscountValue, and LineItemDiscountValue.

Calculation formulas

• Total GMV Value (TotalGMVValue) = Sum(GMVValue)
• Total Order Value (TotalOrderValue) = TotalGMVValue + Sum(ShippingValue + HandlingValue + ShippingTaxValue + SalesTaxValue - ShippingTaXincludeInShippingValue - SalesTaxIncludeInPriceValue - ShippingDiscountValue - OrderLevelDiscountValue -LineItemDiscountValue)
• Total Orders (TotalOrders) = Sum(OrderCount)
• Average Order Value = TotalOrderValue / TotalOrders
• Total Return Value (TotalReturnValue) = Sum(ReturnValue)
• Total Sales Value = TotalOrderValue - TotalReturnValue

Scheduled job

Use the Order per hour schedule job to collect and save order data by hour. See Scheduled jobs for information.

Notes

The data is calculated using shipped shipments on completed orders. The return item value is calculated for when it occurs, not when the order was created. Data for total sales at a specific time may include the returned item value of a previously created order (another time).