Learn Segmenting Funnels by User Attributes

Segmenting funnels by user attributes allows you to uncover trends and patterns that are not visible in overall funnel metrics. When you analyze funnel performance by characteristics such as signup_date or user cohort, you can answer questions like whether users who signed up in June convert at a higher rate than those who signed up earlier, or if certain groups drop off at specific steps more often.


              123456789
            
-- Segment funnel counts by user signup month
SELECT
  DATE_TRUNC('month', u.signup_date) AS signup_month,
  e.event_type,
  COUNT(DISTINCT e.user_id) AS users_at_step
FROM users u
JOIN events e ON u.user_id = e.user_id
GROUP BY signup_month, e.event_type
ORDER BY signup_month, e.event_type;

Note

Segmentation means breaking down analysis by specific user or event attributes, such as signup date, device, or campaign source. This approach helps you analyze how different user groups move through your funnel.

By breaking down funnel analysis by user attributes, you gain insights into how different groups behave. For example, you might notice that users who signed up in the first week of a campaign are more likely to complete a purchase than those who signed up later. Segmenting your funnel in this way helps you identify opportunities for targeted marketing or improvements in the user experience for specific segments.

Task

Swipe to start coding

Segmenting your funnel by user signup week can help you identify trends and opportunities for improvement. Your task is to write a SQL query that counts the number of distinct users at each funnel step, grouped by the week of their signup_date.

Follow these steps:

Group users by the week of their signup_date (use the week start date);
For each week, count distinct users at each funnel step (using event_type);
Your result should include the columns: signup_week, event_type, and users_at_step.

Solution

Everything was clear?

Thanks for your feedback!

Section 2. Chapter 3

single

Ask AI

Ask anything or try one of the suggested questions to begin our chat

Suggested prompts:

Can you explain how to interpret the segmented funnel results?

What other user attributes can I use to segment my funnel analysis?

How can I visualize these segmented funnel metrics?

Swipe to show menu


              123456789
            
-- Segment funnel counts by user signup month
SELECT
  DATE_TRUNC('month', u.signup_date) AS signup_month,
  e.event_type,
  COUNT(DISTINCT e.user_id) AS users_at_step
FROM users u
JOIN events e ON u.user_id = e.user_id
GROUP BY signup_month, e.event_type
ORDER BY signup_month, e.event_type;