Conversion Rate Analysis: SQL Funnel Query, Excel Template, and Segment Checklist

Q: What is the difference between conversion analysis and funnel analysis?

Conversion analysis asks whether users completed a desired action. Funnel analysis breaks the journey into steps so the analyst can locate where users dropped off.

What is conversion rate analysis?

Conversion rate analysis is the process of measuring how many eligible users complete a desired action, then identifying the funnel step, segment, or tracking issue that explains why the rate changed. In practice, analysts use it for signup funnels, checkout flows, trial activation, lead forms, onboarding, product adoption, and paid acquisition quality.

A useful conversion analysis does not stop at "conversion rate is down." It answers four questions: what exactly counts as a conversion, where users are dropping off, which users or channels explain the movement, and what action should the business take next.

Free Excel workbook

Record the analysis in a Conversion Analysis Cover Sheet

Use five editable tabs for Funnel Summary, Period Comparison, Segment Analysis, Tracking QA, and Recommendation.

Download the Conversion Analysis Cover Sheet

Define the conversion before optimizing it

Conversion rate can mean signup, activation, payment, deposit, checkout completion, lead submission, trial start, or repeat order. Before analyzing a drop, define the exact conversion event, eligible population, time window, and exclusions. Most weak conversion reports fail here because the numerator and denominator are unclear.

Conversion rate = converted users / eligible users

Example:
Payment conversion rate =
users with payment_success / users with checkout_start

Signup conversion rate =
users with signup_complete / users with signup_start

Copy this conversion analysis template

Conversion event:
Eligible users:
Entry event:
Conversion window:
Previous period:
Current period:
Funnel steps:
Priority segments:
Tracking checks:
Largest drop-off:
Likely driver:
Recommended test or fix:

Map the funnel

A funnel table is usually more useful than one overall conversion number. It tells you which step lost users and whether the issue is acquisition quality, product friction, payment failure, or tracking.

landing_page_view
  -> signup_start
  -> signup_complete
  -> product_view
  -> checkout_start
  -> payment_success

SQL example: user-level funnel table

This query creates one row per user with flags for each funnel step. User-level grain prevents duplicate events from inflating the funnel.

WITH user_steps AS (
  SELECT
    user_id,
    MAX(CASE WHEN event_name = 'landing_page_view' THEN 1 ELSE 0 END) AS viewed,
    MAX(CASE WHEN event_name = 'signup_start' THEN 1 ELSE 0 END) AS signup_started,
    MAX(CASE WHEN event_name = 'signup_complete' THEN 1 ELSE 0 END) AS signed_up,
    MAX(CASE WHEN event_name = 'checkout_start' THEN 1 ELSE 0 END) AS checkout_started,
    MAX(CASE WHEN event_name = 'payment_success' THEN 1 ELSE 0 END) AS paid
  FROM events
  WHERE event_date BETWEEN DATE '2026-06-01' AND DATE '2026-06-30'
  GROUP BY 1
)
SELECT
  COUNT(*) AS users,
  SUM(viewed) AS viewed,
  SUM(signup_started) AS signup_started,
  SUM(signed_up) AS signed_up,
  SUM(checkout_started) AS checkout_started,
  SUM(paid) AS paid,
  1.0 * SUM(signed_up) / NULLIF(SUM(signup_started), 0) AS signup_completion_rate,
  1.0 * SUM(paid) / NULLIF(SUM(checkout_started), 0) AS payment_completion_rate
FROM user_steps;

If you need a dedicated version of this query, use the funnel conversion rate SQL example. It includes a stricter step-order query and a version for daily trend reporting.

SQL example: segment the drop-off

After finding the weakest funnel step, segment it by channel, device, country, campaign, user type, and product surface.

SELECT
  acquisition_channel,
  device_type,
  COUNT(DISTINCT user_id) AS checkout_users,
  COUNT(DISTINCT CASE WHEN event_name = 'payment_success' THEN user_id END) AS paid_users,
  1.0 * COUNT(DISTINCT CASE WHEN event_name = 'payment_success' THEN user_id END)
    / NULLIF(COUNT(DISTINCT user_id), 0) AS payment_rate
FROM checkout_events
WHERE event_date BETWEEN DATE '2026-06-01' AND DATE '2026-06-30'
GROUP BY 1, 2
ORDER BY payment_rate;

How to read the funnel result

Start by separating volume changes from rate changes. If traffic increased but conversion rate dropped, the issue may be acquisition quality. If traffic is flat but one step rate fell, the issue is more likely product friction, pricing, inventory, payment failure, page speed, or tracking.

Funnel readout:
Entry volume: up / flat / down
Step with largest rate drop:
Segment with largest absolute lost conversions:
Segment with largest percentage drop:
Tracking confidence: high / medium / low
Business action: fix / test / investigate

Excel template structure

A simple Excel workbook should make the analysis repeatable. Use one tab for raw funnel output, one tab for daily trend, one tab for segment comparison, and one tab for the final recommendation. The goal is not a beautiful dashboard. The goal is a reusable decision sheet that a stakeholder can audit.

Funnel summary: users, converted users, conversion rate, drop-off rate, and lost users by step.
Period comparison: previous period, current period, percentage-point change, and relative change.
Segment table: channel, device, country, campaign, new versus returning users, and product surface.
Recommendation: issue, evidence, expected impact, owner, and next action.

For a ready-to-use workbook, download the Conversion Analysis Cover Sheet. It implements these five decision tabs directly. For the workbook structure and formulas, use the conversion rate analysis Excel template guide.

Checklist: do not skip tracking checks

Did the event name or event trigger change?
Did the denominator include users who were never eligible to convert?
Did paid traffic, campaign mix, or geography change?
Did a release affect one platform more than another?
Did payment, inventory, pricing, or login systems change?
Did refunds or cancellations change the final business outcome?

Prioritize by lost conversions, not only percentage drop

A small segment can show a dramatic percentage decline while contributing almost no lost conversions. A larger segment can show a smaller rate decline but explain most of the business impact. Rank segments by absolute lost conversions before recommending work.

lost_conversions =
previous_period_users * previous_conversion_rate
- current_period_users * current_conversion_rate

If the question is about user groups rather than funnel steps, continue with the user conversion analysis guide.

Turn findings into experiments

Good conversion analysis ends with a testable action. If mobile payment completion dropped, the next step may be a payment error review, a UX fix, or an A/B test. If one campaign brought low-intent users, the action may be channel targeting rather than product redesign.

Recommendation template:
The largest conversion loss occurred at [funnel step].
The drop was concentrated in [segment].
Tracking checks [did / did not] show a measurement issue.
The likely driver is [driver].
Recommended next action: [fix, test, or deeper analysis].

Worked example: from funnel movement to a recommendation

Suppose entry users grew from 100,000 to 112,000, while payment success fell from 8,000 to 6,700. The overall conversion rate moved from 8.0% to 6.0%. Segment analysis then shows mobile paid social accounts for the largest absolute lost conversions, while Tracking QA shows a mobile checkout release in the same period.

The recommendation is not "redesign checkout." It is: reconcile payment success against backend data, review the mobile release and payment errors, then test clearer checkout guidance only after tracking is trusted. The Conversion Analysis Cover Sheet records this evidence and decision in one reusable document.

Editorial method

Analyst Field Notes examples are illustrative workflows. Each guide makes its eligibility rule, user-level grain, numerator, denominator, and tracking checks explicit so readers can adapt the method to their own product and data model. Read the editorial method.

FAQ

What is a good conversion rate?

A good conversion rate depends on the funnel, traffic source, user intent, price point, and conversion definition. For analysis, the more useful benchmark is your own historical rate by channel and segment.

What is the difference between conversion analysis and funnel analysis?

Conversion analysis asks whether users completed the desired action. Funnel analysis breaks the journey into steps so you can locate where users dropped off.

Should I use users, sessions, or events as the denominator?

Use users when the business question is about people converting, sessions when the question is about visits, and events only when the action can happen repeatedly and duplicates are meaningful.

When should conversion analysis lead to an A/B test?

Use an A/B test when the root cause is uncertain and a product or messaging change needs causal validation. If the issue is a broken event, failed payment method, or missing inventory, fix it first.