Conversion Rate

Technical Explanation

Conversion rate measures the percentage of users who complete a desired action out of a total who were exposed to the opportunity. It's a fundamental metric for understanding how effectively you turn prospects into customers.

Formula

Conversion Rate = (Conversions / Total Opportunities) × 100

Key Concepts

Concept	Definition
Conversion	The desired action completed
Opportunity	User exposed to the possibility
Funnel	Steps between opportunity and conversion
Drop-off	Users who don't complete the funnel

When Conversion Rate is Meaningful

Good Use	Bad Use
"Users who saw pricing page → signed up"	"Users who visited site → purchased" (too broad)
"Email recipients → clicked link"	"People in city → bought product"
"Cart viewers → completed checkout"	"Homepage visitors → subscribed"

---

Code Examples

Using the CatCafe dataset:

-- Table: customer_events
-- event_type: 'page_view', 'add_to_cart', 'checkout_start', 'purchase', 'signup'

-- Basic conversion rate: Signup rate
SELECT
    COUNT(DISTINCT CASE WHEN event_type = 'signup' THEN user_id END) as conversions,
    COUNT(DISTINCT CASE WHEN event_type = 'page_view' THEN user_id END) as opportunities,
    COUNT(DISTINCT CASE WHEN event_type = 'signup' THEN user_id END) * 100.0
        / NULLIF(
            COUNT(DISTINCT CASE WHEN event_type = 'page_view' THEN user_id END),
            0
        ) as conversion_rate
FROM customer_events;

-- Multi-stage funnel: Awareness → Purchase
WITH funnel AS (
    SELECT
        user_id,
        MAX(CASE WHEN event_type = 'page_view' THEN 1 END) as saw_product,
        MAX(CASE WHEN event_type = 'add_to_cart' THEN 1 END) as added_cart,
        MAX(CASE WHEN event_type = 'checkout_start' THEN 1 END) as started_checkout,
        MAX(CASE WHEN event_type = 'purchase' THEN 1 END) as purchased
    FROM customer_events
    GROUP BY user_id
)
SELECT
    COUNT(CASE WHEN saw_product = 1 THEN 1 END) as step1_awareness,
    COUNT(CASE WHEN added_cart = 1 THEN 1 END) as step2_cart,
    COUNT(CASE WHEN started_checkout = 1 THEN 1 END) as step3_checkout,
    COUNT(CASE WHEN purchased = 1 THEN 1 END) as step4_purchase,
    -- Stage-by-stage conversion rates
    COUNT(CASE WHEN added_cart = 1 THEN 1 END) * 100.0
        / NULLIF(COUNT(CASE WHEN saw_product = 1 THEN 1 END), 0) as view_to_cart_rate,
    COUNT(CASE WHEN started_checkout = 1 THEN 1 END) * 100.0
        / NULLIF(COUNT(CASE WHEN added_cart = 1 THEN 1 END), 0) as cart_to_checkout_rate,
    COUNT(CASE WHEN purchased = 1 THEN 1 END) * 100.0
        / NULLIF(COUNT(CASE WHEN started_checkout = 1 THEN 1 END), 0) as checkout_to_purchase_rate
FROM funnel;

-- Conversion rate by segment
SELECT
    u.segment,
    COUNT(DISTINCT e.user_id) as users,
    COUNT(DISTINCT CASE WHEN e.event_type = 'purchase' THEN e.user_id END) as converters,
    COUNT(DISTINCT CASE WHEN e.event_type = 'purchase' THEN e.user_id END) * 100.0
        / NULLIF(COUNT(DISTINCT e.user_id), 0) as conversion_rate
FROM customer_events e
JOIN user_segments u ON e.user_id = u.user_id
GROUP BY u.segment
ORDER BY conversion_rate DESC;

-- Time-based conversion analysis
SELECT
    DATE_TRUNC('week', event_date) as week,
    COUNT(DISTINCT CASE WHEN event_type = 'signup' THEN user_id END) as signups,
    COUNT(DISTINCT CASE WHEN event_type = 'purchase' THEN user_id END) as purchasers,
    COUNT(DISTINCT CASE WHEN event_type = 'purchase' THEN user_id END) * 100.0
        / NULLIF(COUNT(DISTINCT CASE WHEN event_type = 'signup' THEN user_id END), 0) as signup_to_purchase_rate
FROM customer_events
WHERE event_date >= CURRENT_DATE - INTERVAL '12 weeks'
GROUP BY DATE_TRUNC('week', event_date)
ORDER BY week;

---

The Cat Analogy

Think of conversion rate like getting a cat to use a new bed:

Opportunity:   You buy a fancy cat bed and place it on the floor
              (100 cats exposed to the bed)

Step 1: Cat sniffs the bed
         → 70% sniff rate (70 cats interested)

Step 2: Cat sits on the bed briefly
         → 40% sit rate (40 cats tried it)

Step 3: Cat sleeps on the bed (conversion!)
         → 25% sleep rate (25 cats converted)

"Overall conversion" from sniff to sleep:
  25 sleeping / 70 sniffed = 35.7% conversion

Key insight: 70% of cats sniffed, but only 25% actually slept there. The "opportunity" matters—measuring "cats in the house" vs "cats who sniffed" gives very different rates.

If you want to improve:

• Sniff→Sit: Maybe add catnip
• Sit→Sleep: Maybe warm it up first

---

Common Pitfalls

Wrong Denominator

-- WRONG: Using all visitors as denominator for purchase conversion
SELECT
    COUNT(CASE WHEN event_type = 'purchase' THEN 1 END) * 100.0
        / COUNT(*)  -- Includes non-visitors, too broad!
FROM customer_events;

-- RIGHT: Use the actual funnel stage as denominator
SELECT
    COUNT(CASE WHEN event_type = 'purchase' THEN 1 END) * 100.0
        / COUNT(CASE WHEN event_type = 'checkout_start' THEN 1 END)
FROM customer_events;
-- Only users who started checkout are the opportunity for purchase

Aggregating Rates Incorrectly

-- WRONG: Averaging individual conversion rates
SELECT
    AVG(conversion_rate)  -- Average of averages!
FROM (
    SELECT
        user_id,
        COUNT(CASE WHEN event_type = 'purchase' THEN 1 END) * 100.0
            / NULLIF(COUNT(*), 0) as conversion_rate
    FROM events
    GROUP BY user_id
) user_rates;
-- Mathematically wrong!

-- RIGHT: Aggregate then calculate
SELECT
    SUM(purchases) * 100.0 / NULLIF(SUM(opportunities), 0)
FROM (
    SELECT
        COUNT(CASE WHEN event_type = 'purchase' THEN 1 END) as purchases,
        COUNT(*) as opportunities
    FROM events
    GROUP BY user_id
) user_totals;

---

Exercises

Exercise 1

Write a query to calculate the conversion rate from "added to cart" to "completed purchase" for CatCafe.

Exercise 2

Explain why using COUNT(*) as denominator can mislead conversion rate calculations.

Exercise 3

Write a funnel query showing drop-off rates between:

• Page views
• Product views
• Add to cart
• Checkout
• Purchase

Exercise 4

What could cause conversion rate to go down while absolute conversions increase?

Exercise 5

Write a query to find the conversion rate by day of week. Which day has the highest conversion?

---

Key Takeaways

• Conversion Rate = (Conversions / Opportunities) × 100
• The denominator (opportunity) must be users who could have converted
• Stage-by-stage funnel rates show exactly where users drop off
• Average of rates ≠ overall rate (aggregate first, then calculate)
• Track conversion rate over time to detect changes
• Segment conversion rates to find patterns