Average Ticket

Technical Explanation

Average Ticket (or Average Order Value - AOV) is the average amount spent per transaction. It's a key metric for understanding customer purchasing behavior and identifying opportunities to increase revenue.

Formula

Average Ticket = Total Revenue / Number of Transactions

Why It Matters

Insight	What It Tells You
High AOV	Customers are spending more per visit
Low AOV	May indicate price sensitivity or small carts
AOV Trend	Is spending behavior changing?

Factors Affecting AOV

• Product pricing
• Cross-selling/upselling effectiveness
• Cart size
• Customer segment
• Seasonality

---

Code Examples

Using the CatCafe dataset:

-- Basic AOV calculation
SELECT
    COUNT(*) as total_orders,
    SUM(total_amount) as total_revenue,
    SUM(total_amount) / COUNT(*) as average_ticket,
    AVG(total_amount) as avg_function
FROM orders
WHERE status = 'completed';

-- AOV by customer segment
SELECT
    c.segment,
    COUNT(*) as order_count,
    SUM(o.total_amount) as revenue,
    AVG(o.total_amount) as avg_ticket,
    MEDIAN(o.total_amount) as median_ticket  -- Better for skewed data
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'completed'
GROUP BY c.segment
ORDER BY avg_ticket DESC;

-- AOV trend over time
SELECT
    DATE_TRUNC('month', order_date) as month,
    COUNT(*) as orders,
    SUM(total_amount) as revenue,
    AVG(total_amount) as avg_ticket,
    LAG(AVG(total_amount)) OVER (ORDER BY DATE_TRUNC('month', order_date)) as prev_month_avg,
    AVG(total_amount) - LAG(AVG(total_amount)) OVER (ORDER BY DATE_TRUNC('month', order_date)) as mom_change
FROM orders
WHERE status = 'completed'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

-- AOV with percentiles (not just average)
SELECT
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY total_amount) as p25,
    PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY total_amount) as p50_median,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY total_amount) as p75,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY total_amount) as p95
FROM orders
WHERE status = 'completed';

-- AOV by day of week
SELECT
    TO_CHAR(order_date, 'Dy') as day_of_week,
    COUNT(*) as orders,
    AVG(total_amount) as avg_ticket
FROM orders
WHERE status = 'completed'
GROUP BY TO_CHAR(order_date, 'Dy'), EXTRACT(DOW FROM order_date)
ORDER BY EXTRACT(DOW FROM order_date);

-- AOV with customer lifetime value
SELECT
    c.customer_id,
    COUNT(o.id) as order_count,
    SUM(o.total_amount) as lifetime_value,
    AVG(o.total_amount) as avg_ticket,
    CASE
        WHEN AVG(o.total_amount) > (SELECT AVG(total_amount) FROM orders WHERE status = 'completed')
        THEN 'Above Average'
        ELSE 'Below Average'
    END as ticket_category
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.status = 'completed'
GROUP BY c.customer_id
HAVING COUNT(o.id) >= 3;

---

The Cat Analogy

Think of AOV like cat treat spending per visit:

Visit 1: Whiskers buys $5 of treats
Visit 2: Mochi buys $15 of treats (got a scratching post!)
Visit 3: Luna buys $3 of treats (just treats)
Visit 4: Shadow buys $25 of treats (bed + toys + treats)

Average Ticket = ($5 + $15 + $3 + $25) / 4 = $12 per visit

Insights:

• Most visits are small ($3-$5 treats)
• Occasional big spends ($15-$25) skew the average up
• The median ($9) might be more representative

How to increase AOV:

• Cross-sell: "Your cat might like this toy with those treats"
• Bundle: "Cat care package for $20" instead of individual items
• Upsell: Premium organic treats for $8 instead of regular $5

---

Common Pitfalls

Average vs Median

-- Average can be misleading with outliers
SELECT
    AVG(total_amount) as avg_ticket,     -- Skewed by outliers
    PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY total_amount) as median_ticket
FROM orders;
-- If one order is $10,000, average jumps even if most are $30

-- Always check both!

Ignoring Volume

-- High AOV but low volume might not be better
SELECT
    segment,
    AVG(total_amount) as avg_ticket,
    COUNT(*) as order_count,
    AVG(total_amount) * COUNT(*) as "total impact"
FROM orders
GROUP BY segment;
-- Small segment with high AOV might have less total impact

---

Exercises

Exercise 1

Write a query comparing median vs average ticket for CatCafe. Why might they differ significantly?

Exercise 2

Write a query to find the average ticket by hour of day.

Exercise 3

What factors might cause AOV to increase? Decrease?

Exercise 4

Write a query showing AOV for customers who bought cat food vs customers who bought toys.

Exercise 5

How would you calculate the "minimum profitable ticket size" considering costs?

---

Key Takeaways

• AOV = Total Revenue / Number of Transactions
• Always look at median alongside average (outliers skew averages)
• AOV trends show if spending behavior is changing
• Segment AOV to find which customers spend more
• Increase AOV through cross-selling, bundling, upselling
• AOV × Transaction frequency = Customer Value