SQL로 전환율 측정
전체 전환율이 결과를 알려줍니다. 마일스톤 이벤트는 사용자가 퍼널을 떠나는 위치와 변경된 세그먼트를 보여줌으로써 이를 설명합니다.
인접 단계 전환율을 통해 가장 큰 하락폭을 즉시 확인할 수 있습니다.
이 가이드는 하나의 이벤트 테이블과 하나의 SQL 쿼리에 사용자 수준 가입 퍼널을 구축합니다. 각 단계에서 개별 사용자 수를 계산하면 한 사람이 작업을 반복할 때 전환이 부풀려지는 것을 방지할 수 있습니다.
1. 이벤트를 추가하기 전에 유입경로를 정의하세요
실패할 수 있는 버튼 클릭이 아닌 완료된 결과를 나타내는 마일스톤을 사용하세요.
signup_completedworkspace_createdfirst_event_receiveddashboard_created
user_id 또는 team_id와 같은 안정적인 주체 식별자 하나를 선택합니다. 분모와 모든 이후 단계에서는 동일한 주어를 사용해야 합니다.
2. 일관된 이벤트 형태를 기록합니다.
npm install telemetry-sh
import telemetry from "telemetry-sh";
telemetry.init(process.env.TELEMETRY_API_KEY);
await telemetry.log("product_events", {
event_name: "signup_completed",
user_id: user.id,
team_id: team.id,
acquisition_channel: "docs",
plan: "free",
});
동일한 식별자 및 분할 필드를 사용하여 동일한 product_events 테이블에 이후 마일스톤을 기록합니다.
await telemetry.log("product_events", {
event_name: "first_event_received",
user_id: user.id,
team_id: team.id,
acquisition_channel: user.acquisitionChannel,
plan: team.plan,
});
Telemetry는 timestamp_utc를 자동으로 추가합니다. 안정적인 내부 식별자로 충분할 경우 이메일, 이름, 쿠키 또는 원시 요청 콘텐츠를 기록하지 마세요.
3. 하나의 쿼리에서 유입경로 계산
아래 쿼리는 사용자당 하나의 행을 할당하고 각 단계가 발생했는지 기록한 다음 원래 가입 집단의 개수와 전환을 계산합니다.
WITH user_funnel AS (
SELECT
user_id,
MIN(CASE WHEN event_name = 'signup_completed' THEN timestamp_utc END)
AS signed_up_at,
MIN(CASE WHEN event_name = 'workspace_created' THEN timestamp_utc END)
AS workspace_created_at,
MIN(CASE WHEN event_name = 'first_event_received' THEN timestamp_utc END)
AS first_event_at,
MIN(CASE WHEN event_name = 'dashboard_created' THEN timestamp_utc END)
AS dashboard_created_at
FROM product_events
WHERE timestamp_utc >= now() - INTERVAL '30 days'
AND event_name IN (
'signup_completed',
'workspace_created',
'first_event_received',
'dashboard_created'
)
GROUP BY user_id
),
stage_counts AS (
SELECT 1 AS step, 'Signed up' AS stage, COUNT(*) AS users
FROM user_funnel
WHERE signed_up_at IS NOT NULL
UNION ALL
SELECT 2, 'Created workspace', COUNT(*)
FROM user_funnel
WHERE workspace_created_at >= signed_up_at
UNION ALL
SELECT 3, 'Sent first event', COUNT(*)
FROM user_funnel
WHERE first_event_at >= workspace_created_at
UNION ALL
SELECT 4, 'Created dashboard', COUNT(*)
FROM user_funnel
WHERE dashboard_created_at >= first_event_at
)
SELECT
step,
stage,
users,
ROUND(
100.0 * users /
NULLIF(MAX(CASE WHEN step = 1 THEN users END) OVER (), 0),
2
) AS conversion_from_signup_pct,
ROUND(
100.0 * users /
NULLIF(LAG(users) OVER (ORDER BY step), 0),
2
) AS conversion_from_previous_pct
FROM stage_counts
ORDER BY step;
첫 번째 단계에는 이전 단계 비율이 없으므로 conversion_from_previous_pct는 해당 행에 대해 null입니다. 이후의 모든 행에는 가입으로 인한 총 전환과 인접 단계 전환이 모두 표시됩니다.
4. 정의를 변경하지 않고 세그먼트화
획득 채널 또는 계획을 비교하려면 가입 이벤트의 필드를 유지하고 이를 user_funnel에 추가한 다음 해당 필드별로 단계 수를 그룹화합니다. 사용자에서 이벤트로 분모를 자동으로 전환하지 마십시오.
유용한 세그먼트 확인에는 다음이 포함됩니다.
- 획득 채널;
- 초기 계획 또는 제안;
- 장치 또는 적용 표면;
- 가입 주;
- 결과가 나오기 전에 실험 할당이 기록됩니다.
분할되지 않은 유입경로를 신뢰할 수 있을 때까지 카디널리티가 높은 차원을 피하세요.
5. 결과 시각화 및 검증
step로 정렬된 users에 대한 가로 막대 차트를 사용하고 결과 테이블에 두 요율 열을 모두 표시합니다. 차트를 공유하기 전에 다음을 확인하세요.
- 이전 타임스탬프 이전에 이후 단계를 완료하는 사용자
- 누락되거나 재사용된 식별자
- 내부, 테스트 및 봇 계정
- 늦게 도착한 이벤트;
- 선택한 기간 이전에 가입한 사용자
- 최신 코호트가 전환할 충분한 시간을 가졌는지 여부.
깔때기는 단순히 두 개의 독립적인 이벤트 수가 아닌 코호트 측정입니다. 전환 기간을 더 길게 하려면 가입 날짜별로 사용자 집단을 분류하고 고정된 관찰 기간을 허용하세요.
다음 단계
결과 예시와 시각화를 보려면 전체 가입 활성화 퍼널 SQL 레시피를 열어보세요. 활성화가 정의되면 주간 코호트 유지를 사용한 다음 업그레이드 전 기능 채택을 비교하세요.