True AOV

true_aov

Overview

True Average Order Value (True AOV) is the average order value (minus shipping and taxes) for orders with a non-zero (positive) revenue.

📘

True AOV = (Non-Zero Order Revenue - Shipping - Taxes) / Orders

The calculation is based on data from the Orders table.

Detailed Breakdown

The formula above is derived from the following components:

Non-Zero Order Revenue = SUM(IF(order_revenue > 0, order_revenue, 0)) --> Orders table
Shipping = SUM(shipping_price) --> Orders table
Taxes = SUM(taxes) --> Orders table
Orders = SUM(orders_quantity) --> Orders table

Insights and Actions

True Average Order Value (True AOV) offers a refined look at the revenue generated per order, accounting only for profitable transactions. Efficiently leveraging True AOV data can guide strategic business decisions:

• Optimize Pricing Strategies: Analyze True AOV alongside customer feedback and market trends to adjust pricing, ensuring it aligns with value perception and demand.
• Tailor Marketing and Promotions: Develop targeted promotions that encourage higher-value purchases, focusing on products or bundles that can boost True AOV.
• Enhance Product Mix: Identify which products or categories contribute most to True AOV and consider expanding these lines or featuring them more prominently in marketing efforts.
• Improve Customer Segmentation: Use True AOV to segment customers based on their spending behavior, customizing marketing messages to encourage repeat high-value purchases.

Example Use

Prompt

What's my true AOV over the last 90 days?

Response

Query

SQL

SELECT
  COALESCE(
    SUM(ot.order_revenue - ot.shipping_price - ot.taxes) / NULLIF(
      COUNT(
        CASE
          WHEN ot.order_revenue > 0 THEN 1
        END
      ),
      0
    ),
    0
  ) AS true_aov
FROM
  orders_table AS ot
WHERE
  ot.event_date BETWEEN CURRENT_DATE() - 90 AND CURRENT_DATE()  - 1
ORDER BY
  true_aov DESC;