User Conversion Analysis: Segments, Cohorts, and Drop-off Diagnosis

Start with the user question

User conversion analysis asks which group of users changed, not just which funnel step changed. It is especially useful when the overall conversion rate moved but the product team needs to know where to act.

Useful segmentation dimensions

• Acquisition: channel, campaign, source, medium, keyword, landing page.
• Device: desktop, mobile web, iOS app, Android app, browser, app version.
• Market: country, language, currency, region, shipping zone.
• Lifecycle: new user, returning user, trial user, activated user, subscriber, churn-risk user.
• Product behavior: category viewed, feature used, cart size, plan selected, session count.

SQL: conversion by user segment

WITH eligible_users AS (
  SELECT
    user_id,
    acquisition_channel,
    device_type,
    country,
    user_type
  FROM users
  WHERE created_at >= DATE '2026-06-01'
    AND created_at < DATE '2026-07-01'
),
conversions AS (
  SELECT DISTINCT user_id
  FROM events
  WHERE event_name = 'payment_success'
    AND event_time >= TIMESTAMP '2026-06-01'
    AND event_time < TIMESTAMP '2026-07-08'
)
SELECT
  acquisition_channel,
  device_type,
  country,
  user_type,
  COUNT(*) AS eligible_users,
  COUNT(conversions.user_id) AS converted_users,
  1.0 * COUNT(conversions.user_id) / NULLIF(COUNT(*), 0) AS conversion_rate
FROM eligible_users
LEFT JOIN conversions USING (user_id)
GROUP BY 1, 2, 3, 4
HAVING COUNT(*) >= 100
ORDER BY conversion_rate;

Compare current and previous periods

A segment with low conversion is not automatically the problem. The better question is whether that segment got worse, got larger, or both.

Segment readout:
Previous period users:
Current period users:
Previous conversion rate:
Current conversion rate:
Volume mix change:
Rate change:
Estimated lost conversions:

Diagnose the likely driver

• If one paid channel grew and conversion dropped, check traffic quality and landing-page match.
• If mobile conversion dropped but desktop did not, check page speed, responsive layout, app version, and payment flow.
• If one country dropped, check localization, currency, shipping, tax, payment options, and legal notices.
• If new users dropped but returning users did not, check onboarding, first-session UX, and acquisition intent.

Read next

Use the broader conversion rate analysis guide to connect segments to funnel steps. If you need query detail, copy the funnel conversion rate SQL example.