분석 기간 2026-08-01 ~ 2026-08-31 (31일) · 미리캔버스 템플릿 화면 · 한국.
| 슬롯 | 노출유저 | 채택유저 | 채택률 |
|---|---|---|---|
| 1XXX님을 위한 Pick추천1 · style_semantic | 700,132 | 11,605 | 1.66% |
| 2취향이 닮은 사람들의 추천추천2 · collaborate | 467,156 | 21,027 | 4.50% |
| 3최근 눈여겨본 스타일추천3 · style_semantic (전체) | 218,624 | 5,983 | 2.74% |
| 4취향 분석 완료! 다음 디자인은 이거죠?추천7 · semantic_sasrec | 126,651 | 2,384 | 1.88% |
| 5'{업종}' {용도}에 쓰이는 템플릿추천5 · mdi_neighbors | 95,567 | 1,242 | 1.30% |
| 6Top20구독전환 많이 일으킨 템플릿 20개 | 83,112 | 567 | 0.68% |
| 7키워드카테고리추천 슬롯 아님 | 79,765 | 771 | 0.97% |
| 8추천 템플릿 둘러보기추천4 · semantic | 68,865 | 4,428 | 6.43% |
추천 슬롯 7종의 채택률과 모수. 추천 슬롯이 아닌 키워드카테고리를 비교 기준으로 함께 넣었어요. 퍼널 단계가 다른 템플릿 화면과 에디터 템플릿패널은 따로 집계했어요.
유저-일 기준 채택률 (%). 맨 오른쪽 키워드카테고리는 추천 슬롯이 아니라 비교 기준이에요. 두 화면의 퍼널 단계가 달라 절대값 비교보다 슬롯 간 순위로 읽어요.
두 화면에서 슬롯 순위가 같아요. 둘러보기(semantic)가 가장 높고 취향이 닮은 사람들(collaborate)이 그 다음이에요. Top20이 양쪽 모두 꼴찌예요. 키워드카테고리는 템플릿 화면에서 1.50%, 에디터에서 2.25%인데 이 값에는 추천 비대상 유저가 섞여 있어요. 모집단을 맞춘 수치는 아래 추천 대상 유저 한정 비교에서 봐요.
채택 = 같은 날 같은 슬롯에서 상세 → 채택시도 → 채택성공까지 도달한 유저. 분모는 해당 슬롯 노출 유저. 순 노출·채택유저는 8월 전체 순유저예요. 헤더를 클릭하면 정렬돼요.
| 슬롯 | 노출 유저-일 | 채택 유저-일 | 채택률 | 상위10위 | 노출 건수 | 채택 건수 | 건수 채택률 | 순 노출유저 | 순 채택유저 | 월간 채택률 |
|---|---|---|---|---|---|---|---|---|---|---|
| 추천1XXX님을 위한 추천 템플릿 style_semantic | 700,132 | 11,605 | 1.66% | 1.57% | 1,207,159 | 29,694 | 2.46% | 382,487 | 10,567 | 2.76% |
| 추천2취향이 닮은 사람들의 추천 collaborate | 467,156 | 21,027 | 4.50% | 3.37% | 1,690,513 | 166,383 | 9.84% | 279,369 | 19,029 | 6.81% |
| 추천3좋아할 만한 스타일 (최근 눈여겨본 스타일) style_semantic (전체) | 218,624 | 5,983 | 2.74% | 1.78% | 806,082 | 48,487 | 6.02% | 154,531 | 5,555 | 3.59% |
| 추천4추천 템플릿 둘러보기 semantic | 68,865 | 4,428 | 6.43% | 1.51% | 959,617 | 162,554 | 16.94% | 56,470 | 4,029 | 7.13% |
| 추천5MDI 업종 추천 mdi_neighbors | 95,567 | 1,242 | 1.30% | 0.98% | 345,281 | 10,359 | 3.00% | 76,305 | 1,196 | 1.57% |
| 추천6TOP 100 metric_ranking | 83,112 | 567 | 0.68% | 0.55% | 202,557 | 3,826 | 1.89% | 67,287 | 547 | 0.81% |
| 추천7취향 분석 완료 semantic_sasrec | 126,651 | 2,384 | 1.88% | 1.17% | 474,267 | 20,933 | 4.41% | 97,896 | 2,221 | 2.27% |
| 키워드카테고리추천 슬롯 아님 · 비교 기준 키워드 카테고리 · 추천더보기 경유 | 85,110 | 1,280 | 1.50% | 1.31% | 316,341 | 7,311 | 2.31% | 68,623 | 1,217 | 1.77% |
| 키워드카테고리추천1 당일 노출 유저 한정 | 79,765 | 771 | 0.97% | 0.81% | 298,850 | 5,415 | 1.81% | 64,671 | 740 | 1.14% |
둘러보기는 노출 모수가 6.9만 유저-일로 가장 작은데 채택률은 6.43%로 가장 높아요. 슬롯 위치가 아래쪽 고정이라 여기까지 내려온 유저 자체가 탐색 의도가 강한 집단이에요. 반대로 추천1은 70만 유저-일을 먹으면서 1.66%에 머물러요. 다만 키워드카테고리와의 비교는 모집단을 맞춰야 해요 (아래 섹션).
미리캔버스 템플릿 화면에 실제로 보이는 위쪽부터의 순서. 슬롯명은 유저에게 보이는 문구이고, 작은 글씨가 대시보드 슬롯번호와 specKey예요. 키워드카테고리는 인기 Top20 바로 아래 자리라 7번에 놓았고, 추천1을 본 유저로 모집단을 맞춘 값이에요.
| 슬롯 | 노출 유저-일 | 채택 유저-일 | 채택률 | 상위10위 | 노출 건수 | 채택 건수 | 건수 채택률 | 순 노출유저 | 순 채택유저 | 월간 채택률 |
|---|---|---|---|---|---|---|---|---|---|---|
| 1XXX님을 위한 Pick추천1 · style_semantic | 700,132 | 11,605 | 1.66% | 1.57% | 1,207,159 | 29,694 | 2.46% | 382,487 | 10,567 | 2.76% |
| 2취향이 닮은 사람들의 추천추천2 · collaborate | 467,156 | 21,027 | 4.50% | 3.37% | 1,690,513 | 166,383 | 9.84% | 279,369 | 19,029 | 6.81% |
| 3최근 눈여겨본 스타일추천3 · style_semantic (전체) | 218,624 | 5,983 | 2.74% | 1.78% | 806,082 | 48,487 | 6.02% | 154,531 | 5,555 | 3.59% |
| 4취향 분석 완료! 다음 디자인은 이거죠?추천7 · semantic_sasrec | 126,651 | 2,384 | 1.88% | 1.17% | 474,267 | 20,933 | 4.41% | 97,896 | 2,221 | 2.27% |
| 5'{업종}' {용도}에 쓰이는 템플릿추천5 · mdi_neighbors | 95,567 | 1,242 | 1.30% | 0.98% | 345,281 | 10,359 | 3.00% | 76,305 | 1,196 | 1.57% |
| 6Top20구독전환 많이 일으킨 템플릿 20개 | 83,112 | 567 | 0.68% | 0.55% | 202,557 | 3,826 | 1.89% | 67,287 | 547 | 0.81% |
| 7키워드카테고리추천 슬롯 아님 | 79,765 | 771 | 0.97% | 0.81% | 298,850 | 5,415 | 1.81% | 64,671 | 740 | 1.14% |
| 8추천 템플릿 둘러보기추천4 · semantic | 68,865 | 4,428 | 6.43% | 1.51% | 959,617 | 162,554 | 16.94% | 56,470 | 4,029 | 7.13% |
화면 위쪽 세 칸이 노출을 거의 다 먹지만 채택률은 중간이에요. 반대로 가장 아래 추천 템플릿 둘러보기가 6.43%로 가장 높고, Top20은 0.68%로 가장 낮아요. 화면 순서와 성과가 맞지 않는 구간이 4~7번 자리예요.
상세 단계가 없어 노출 → 채택 2단 퍼널. 그래서 위 템플릿 화면 수치와 직접 비교하면 안 돼요.
| 슬롯 | 노출 유저-일 | 채택 유저-일 | 채택률 | 상위10위 | 노출 건수 | 채택 건수 | 건수 채택률 | 순 노출유저 | 순 채택유저 | 월간 채택률 |
|---|---|---|---|---|---|---|---|---|---|---|
| 추천2취향이 닮은 사람들의 추천 collaborate | 3,001,634 | 66,200 | 2.21% | 1.99% | 13,044,037 | 861,208 | 6.60% | 811,842 | 55,677 | 6.86% |
| 추천3좋아할 만한 스타일 (최근 눈여겨본 스타일) style_semantic (전체) | 2,986,705 | 38,300 | 1.28% | 1.11% | 12,894,248 | 582,032 | 4.51% | 809,558 | 32,159 | 3.97% |
| 추천4추천 템플릿 둘러보기 semantic | 312,835 | 24,561 | 7.85% | 4.27% | 1,845,651 | 461,137 | 24.99% | 195,867 | 21,124 | 10.78% |
| 추천5MDI 업종 추천 mdi_neighbors | 1,978,586 | 9,146 | 0.46% | 0.39% | 7,729,585 | 113,077 | 1.46% | 591,987 | 8,526 | 1.44% |
| 추천6TOP 100 metric_ranking | 697,692 | 1,870 | 0.27% | 0.26% | 2,012,029 | 12,812 | 0.64% | 303,135 | 1,795 | 0.59% |
| 추천7취향 분석 완료 semantic_sasrec | 2,685,402 | 14,885 | 0.55% | 0.45% | 11,302,151 | 250,006 | 2.21% | 739,749 | 12,975 | 1.75% |
| 키워드카테고리추천 슬롯 아님 · 비교 기준 키워드 카테고리 칩 경유 | 546,546 | 12,300 | 2.25% | 2.25% | 1,486,353 | 34,326 | 2.31% | 271,202 | 11,461 | 4.23% |
| 키워드카테고리추천 슬롯 당일 노출 유저 한정 | 535,861 | 12,281 | 2.29% | 2.29% | 1,447,800 | 34,253 | 2.37% | 265,547 | 11,442 | 4.31% |
추천1(XXX님을 위한 추천 템플릿)은 8월 내내 행이 하나도 없어요. 템플릿패널에는 이 슬롯 노출 로그가 없어요. MDI 업종 추천은 198만 유저-일을 쓰면서 0.46%로, 노출 대비 회수가 가장 나쁜 조합이에요. 키워드카테고리가 2.25%(모집단 보정 후 2.29%)인 걸 보면 에디터에서는 추천 슬롯 대부분이 칩 탐색보다 못한 상태예요.
추천 대상이 아닌 유저는 화면 구성이 달라서, 키워드카테고리 전체 수치에는 두 모집단이 섞여 있어요. 추천1 노출 이력으로 모집단을 맞춰 다시 계산했어요.
템플릿 화면 · 유저-일 기준 채택률 (%)
| 슬롯 | 노출 유저-일 | 채택 유저-일 | 채택률 | 상위10위 | 노출 건수 | 채택 건수 | 건수 채택률 | 순 노출유저 | 순 채택유저 | 월간 채택률 |
|---|---|---|---|---|---|---|---|---|---|---|
| 추천1슬롯 자체 · 분모가 곧 추천1 노출 유저 | 700,132 | 11,605 | 1.66% | 1.57% | 1,207,159 | 29,694 | 2.46% | 382,487 | 10,567 | 2.76% |
| 키워드카테고리전체 (v2·v3 수치) | 85,110 | 1,280 | 1.50% | 1.31% | 316,341 | 7,311 | 2.31% | 68,623 | 1,217 | 1.77% |
| 키워드카테고리추천1 당일 노출 유저 | 79,765 | 771 | 0.97% | 0.81% | 298,850 | 5,415 | 1.81% | 64,671 | 740 | 1.14% |
| 키워드카테고리추천1 기간 내 노출 유저 | 82,445 | 1,025 | 1.24% | 1.06% | 307,825 | 6,441 | 2.09% | 66,016 | 964 | 1.46% |
| 키워드카테고리추천1 미노출 유저 | 2,665 | 255 | 9.57% | 8.82% | 8,516 | 870 | 10.22% | 2,607 | 253 | 9.70% |
모집단을 맞추면 결론이 뒤집혀요. 키워드카테고리는 1.50% → 0.97%로 떨어지고, 같은 유저가 보는 추천1은 1.66%라 추천1이 1.7배 높아요. 앞선 v3의 "둘이 비슷하다"는 읽기는 모집단 차이가 만든 착시였어요.
차이를 만든 건 추천1 미노출군이에요. 노출의 3%(2,665 유저-일)밖에 안 되는데 채택률이 9.57%로 10배 높고, 키워드카테고리 채택의 20%를 이 집단이 만들어요. 추천을 못 받는 화면에서는 키워드 칩이 사실상 유일한 진입점이라 클릭이 몰리는 구조로 보여요.
에디터 템플릿패널 · 추천1이 없어 추천 슬롯 전체 노출로 대체
| 슬롯 | 노출 유저-일 | 채택 유저-일 | 채택률 | 상위10위 | 노출 건수 | 채택 건수 | 건수 채택률 | 순 노출유저 | 순 채택유저 | 월간 채택률 |
|---|---|---|---|---|---|---|---|---|---|---|
| 키워드카테고리전체 (v2·v3 수치) | 546,546 | 12,300 | 2.25% | 2.25% | 1,486,353 | 34,326 | 2.31% | 271,202 | 11,461 | 4.23% |
| 키워드카테고리추천 슬롯 당일 노출 유저 | 535,861 | 12,281 | 2.29% | 2.29% | 1,447,800 | 34,253 | 2.37% | 265,547 | 11,442 | 4.31% |
| 키워드카테고리추천 슬롯 기간 내 노출 유저 | 544,825 | 12,293 | 2.26% | 2.26% | 1,480,350 | 34,295 | 2.32% | 269,523 | 11,454 | 4.25% |
| 키워드카테고리추천 슬롯 미노출 유저 | 1,721 | 7 | 0.41% | 0.41% | 6,003 | 31 | 0.52% | 1,679 | 7 | 0.42% |
에디터는 모집단을 맞춰도 2.25% → 2.29%로 거의 그대로예요. 키워드카테고리 노출의 98%가 이미 추천 슬롯도 함께 본 유저라 자를 게 별로 없어요. 에디터에서 키워드카테고리가 추천 슬롯 대부분을 앞선다는 v3 결론은 그대로예요.
대시보드 슬롯별 Raw 데이터셋의 정의를 그대로 따랐어요.
| 항목 | 정의 |
|---|---|
| 모수 | 2022년 이후 채택 경험이 있는 유저만. 당일 첫 채택자는 템플릿 화면 집계에서 제외돼요(first_action_date < p_date). |
| 노출 유저-일 | 일별 순 노출유저를 8월 31일간 합산한 값. 대시보드의 일별 채택률과 같은 축이에요. |
| 월간 순 유저 | 8월 전체를 한 덩어리로 본 순 유저. 재방문이 합쳐지므로 채택률이 유저-일 기준보다 높게 나와요. |
| 상위10위 채택률 | priority2 기준 10위 이내 템플릿을 채택한 경우. 이름과 달리 "20위 초과"가 아닌 점 주의. |
| 공통 필터 | 한국 · staging 제외 · 템플릿 화면은 /templates, 에디터는 /v2/ + source_tab='템플릿패널'. |
| 키워드카테고리 | 추천 슬롯이 아닙니다. 채택 경로 분해 쿼리의 채널 정의를 그대로 가져와 추천 7종을 먼저 걸러낸 뒤 남은 search_keyword_category 보유 노출(키워드 카테고리 칩 · type:KEYWORD_CATEGORY · RECOMMEND · 추천더보기)만 집계했어요. 나머지 계산 방식은 슬롯과 같습니다. |
| 슬롯 이름 | 표의 이름은 GA category_name(집계 키)이고 괄호가 실제 화면 문구예요. 이름이 세 겹이라 슬롯 판정은 specKey로 해요. |
키워드카테고리는 원래 채널 분해에서 미분류·조건 탈락까지 흡수하는 자리라 순수한 칩 성과보다 조금 넓게 잡힐 수 있어요. 슬롯별 수치는 노출 위치와 모수 규모가 제각각이니 절대값보다 같은 화면 안에서의 순위로 읽는 게 안전해요.
이 리포트의 모든 수치를 만든 쿼리예요. 버튼을 누르면 펼쳐져요.
전부 Databricks 웨어하우스 f851823d32e682bc에서 실행했어요. 기간 파라미터는 2026-08-01 ~ 2026-08-31로 고정해 넣었어요.
대시보드 데이터셋 55c2e07d([v2] 템플릿페이지 슬롯별 Raw)를 8월 단일 구간으로 재현. 추천1~7 채택률·모수의 원천.
with action_exp as (
SELECT user_id, MIN(event_date) AS first_action_date
from silver.miricanvas_searching.action_template
where event_date >= '2022-01-01' AND user_id IS NOT NULL GROUP BY user_id
), visit as (
select
p_date,
source_tab,
COALESCE(NULLIF(ge.user_id, '0'), NULLIF(ge.account_id, '0')) AS user_id,
concat(COALESCE(NULLIF(ge.user_id, '0'), NULLIF(ge.account_id, '0')),'_',ga_session_id) as ga_session_id,
case when source_tab = '템플릿화면_상단추천영역' then '추천1'
WHEN source_tab = '템플릿화면' AND search_keyword_category LIKE '%"category_name":"XXX님을 위한 추천 템플릿"%' THEN '추천1'
when source_tab = '템플릿화면' and (search_keyword_category = '취향이 닮은 사람들의 추천' OR search_keyword_category LIKE '%"category_name":"취향이 닮은 사람들의 추천"%') then '추천2'
when source_tab = '템플릿화면' and (search_keyword_category = '좋아할 만한 스타일' OR search_keyword_category LIKE '%"category_name":"좋아할 만한 스타일"%') then '추천3'
when source_tab = '템플릿화면' and (search_keyword_category = '추천 템플릿 둘러보기' OR search_keyword_category LIKE '%"category_name":"추천 템플릿 둘러보기"%') then '추천4'
when p_date >= '2026-02-10' AND source_tab = '템플릿화면' and (search_keyword_category = 'MDI 업종 추천' OR search_keyword_category LIKE '%"category_name":"MDI 업종 추천"%') then '추천5'
when source_tab = '템플릿화면' and (search_keyword_category = 'TOP 100' OR search_keyword_category LIKE '%"category_name":"TOP 100"%') then '추천6'
when source_tab = '템플릿화면' and (search_keyword_category = '취향 분석 완료' OR search_keyword_category LIKE '%"category_name":"취향 분석 완료"%') then '추천7'
end as search_keyword_category
from bronze.google_analytics_miricanvas.events_except_miridih ge
join action_exp a ON COALESCE(NULLIF(ge.user_id, '0'), NULLIF(ge.account_id, '0')) = a.user_id AND a.first_action_date < ge.p_date
where ge.event_name = 'view_template_workspace'
and p_date between '2026-08-01' and '2026-08-31'
and ge.page_location not like '%staging%'
and ge.page_location like '%/templates%'
AND `geo.country` = 'South Korea'
AND ((search_keyword_category IN ('취향이 닮은 사람들의 추천', '좋아할 만한 스타일', '추천 템플릿 둘러보기', 'MDI 업종 추천','TOP 100','취향 분석 완료') OR (search_keyword_category LIKE '%"category_name":"취향이 닮은 사람들의 추천"%'
OR search_keyword_category LIKE '%"category_name":"좋아할 만한 스타일"%'
OR search_keyword_category LIKE '%"category_name":"추천 템플릿 둘러보기"%'
OR search_keyword_category LIKE '%"category_name":"MDI 업종 추천"%'
OR search_keyword_category LIKE '%"category_name":"TOP 100"%' OR search_keyword_category LIKE '%"category_name":"취향 분석 완료"%'))
OR source_tab = '템플릿화면_상단추천영역' OR (source_tab = '템플릿화면' AND search_keyword_category LIKE '%"category_name":"XXX님을 위한 추천 템플릿"%'))
), detail_base as (
SELECT
p_date,
COALESCE(NULLIF(ge.user_id, '0'), NULLIF(ge.account_id, '0')) AS user_id,
plan_type,
source_tab,
case when cast(priority2 as int) <= 10 then 1 when cast(priority2 as int) <= 20 then 2 when cast(priority2 as int) <= 30 then 3 when cast(priority2 as int) <= 40 then 4 when cast(priority2 as int) <= 50 then 5 else 6 END AS rank_range2,
CASE
WHEN source_tab = '템플릿화면_상단추천영역' AND ((p_date <= '2026-01-27' AND search_keyword_category IS NULL) OR (p_date >= '2026-01-27' AND search_keyword_category = 'XXX님을 위한 추천 템플릿' OR search_keyword_category LIKE '%"category_name":"XXX님을 위한 추천 템플릿"%')) THEN '추천1'
WHEN source_tab = '템플릿화면' AND (search_keyword_category = '취향이 닮은 사람들의 추천' OR search_keyword_category LIKE '%"category_name":"취향이 닮은 사람들의 추천"%') THEN '추천2'
WHEN source_tab = '템플릿화면' AND (search_keyword_category = '좋아할 만한 스타일' OR search_keyword_category LIKE '%"category_name":"좋아할 만한 스타일"%') THEN '추천3'
WHEN source_tab = '템플릿화면' AND (search_keyword_category = '추천 템플릿 둘러보기' OR search_keyword_category LIKE '%"category_name":"추천 템플릿 둘러보기"%') THEN '추천4'
when p_date >= '2026-02-10' AND source_tab = '템플릿화면' and (search_keyword_category = 'MDI 업종 추천' OR search_keyword_category LIKE '%"category_name":"MDI 업종 추천"%') then '추천5'
when source_tab = '템플릿화면' and (search_keyword_category = 'TOP 100' OR search_keyword_category LIKE '%"category_name":"TOP 100"%') then '추천6'
when source_tab = '템플릿화면' and (search_keyword_category = '취향 분석 완료' OR search_keyword_category LIKE '%"category_name":"취향 분석 완료"%') then '추천7'
ELSE search_keyword_category
END AS search_keyword_category
from bronze.google_analytics_miricanvas.events_except_miridih ge
join action_exp a ON COALESCE(NULLIF(ge.user_id, '0'), NULLIF(ge.account_id, '0')) = a.user_id AND a.first_action_date < ge.p_date
WHERE event_name = 'detail_template_workspace'
and p_date between '2026-08-01' and '2026-08-31'
AND page_location NOT LIKE '%staging%'
AND page_location LIKE '%/templates%'
AND ((search_keyword_category IN ('취향이 닮은 사람들의 추천', '좋아할 만한 스타일', '추천 템플릿 둘러보기','MDI 업종 추천','TOP 100','취향 분석 완료') OR (search_keyword_category LIKE '%"category_name":"취향이 닮은 사람들의 추천"%'
OR search_keyword_category LIKE '%"category_name":"좋아할 만한 스타일"%'
OR search_keyword_category LIKE '%"category_name":"추천 템플릿 둘러보기"%'
OR search_keyword_category LIKE '%"category_name":"MDI 업종 추천"%'
OR search_keyword_category LIKE '%"category_name":"TOP 100"%' OR search_keyword_category LIKE '%"category_name":"취향 분석 완료"%'))
OR source_tab = '템플릿화면_상단추천영역')
and `geo.country` = 'South Korea'
), detail AS (
SELECT p_date, user_id, source_tab, search_keyword_category, min(rank_range2) as min_rank
from detail_base group by 1,2,3,4
), action_base AS (
SELECT
p_date,
COALESCE(NULLIF(ge.user_id, '0'), NULLIF(ge.account_id, '0')) AS user_id,
source_tab,
case when cast(priority2 as int) <= 10 then 1 when cast(priority2 as int) <= 20 then 2 when cast(priority2 as int) <= 30 then 3 when cast(priority2 as int) <= 40 then 4 when cast(priority2 as int) <= 50 then 5 else 6 END AS rank_range2,
CASE
WHEN source_tab = '템플릿화면_상단추천영역' AND ((p_date <= '2026-01-27' AND search_keyword_category IS NULL) OR (p_date >= '2026-01-27' AND search_keyword_category = 'XXX님을 위한 추천 템플릿' OR search_keyword_category LIKE '%"category_name":"XXX님을 위한 추천 템플릿"%')) THEN '추천1'
WHEN source_tab = '템플릿화면' AND (search_keyword_category = '취향이 닮은 사람들의 추천' OR search_keyword_category LIKE '%"category_name":"취향이 닮은 사람들의 추천"%') THEN '추천2'
WHEN source_tab = '템플릿화면' AND (search_keyword_category = '좋아할 만한 스타일' OR search_keyword_category LIKE '%"category_name":"좋아할 만한 스타일"%') THEN '추천3'
WHEN source_tab = '템플릿화면' AND (search_keyword_category = '추천 템플릿 둘러보기' OR search_keyword_category LIKE '%"category_name":"추천 템플릿 둘러보기"%') THEN '추천4'
when p_date >= '2026-02-10' AND source_tab = '템플릿화면' and (search_keyword_category = 'MDI 업종 추천' OR search_keyword_category LIKE '%"category_name":"MDI 업종 추천"%') then '추천5'
when source_tab = '템플릿화면' and (search_keyword_category = 'TOP 100' OR search_keyword_category LIKE '%"category_name":"TOP 100"%') then '추천6'
when source_tab = '템플릿화면' and (search_keyword_category = '취향 분석 완료' OR search_keyword_category LIKE '%"category_name":"취향 분석 완료"%') then '추천7'
ELSE search_keyword_category
END AS search_keyword_category
from bronze.google_analytics_miricanvas.events_except_miridih ge
join action_exp a ON COALESCE(NULLIF(ge.user_id, '0'), NULLIF(ge.account_id, '0')) = a.user_id AND a.first_action_date < ge.p_date
WHERE ge.event_name = 'action_template_workspace'
and p_date between '2026-08-01' and '2026-08-31'
AND page_location NOT LIKE '%staging%'
AND page_location LIKE '%/templates%'
AND ((search_keyword_category IN ('취향이 닮은 사람들의 추천', '좋아할 만한 스타일', '추천 템플릿 둘러보기','MDI 업종 추천','TOP 100','취향 분석 완료') OR (search_keyword_category LIKE '%"category_name":"취향이 닮은 사람들의 추천"%'
OR search_keyword_category LIKE '%"category_name":"좋아할 만한 스타일"%'
OR search_keyword_category LIKE '%"category_name":"추천 템플릿 둘러보기"%'
OR search_keyword_category LIKE '%"category_name":"MDI 업종 추천"%'
OR search_keyword_category LIKE '%"category_name":"TOP 100"%' OR search_keyword_category LIKE '%"category_name":"취향 분석 완료"%'))
OR source_tab = '템플릿화면_상단추천영역')
and `geo.country` = 'South Korea'
), action AS (
SELECT p_date, user_id, source_tab, search_keyword_category, min(rank_range2) as min_rank
from action_base group by 1,2,3,4
), action2_base AS (
SELECT
p_date,
COALESCE(NULLIF(ge.user_id, '0'), NULLIF(ge.account_id, '0')) AS user_id,
source_tab,
case when cast(priority2 as int) <= 10 then 1 when cast(priority2 as int) <= 20 then 2 when cast(priority2 as int) <= 30 then 3 when cast(priority2 as int) <= 40 then 4 when cast(priority2 as int) <= 50 then 5 else 6 END AS rank_range2,
CASE
WHEN source_tab = '템플릿화면_상단추천영역' AND ((p_date <= '2026-01-27' AND search_keyword_category IS NULL) OR (p_date >= '2026-01-27' AND search_keyword_category = 'XXX님을 위한 추천 템플릿' OR search_keyword_category LIKE '%"category_name":"XXX님을 위한 추천 템플릿"%')) THEN '추천1'
WHEN source_tab = '템플릿화면' AND (search_keyword_category = '취향이 닮은 사람들의 추천' OR search_keyword_category LIKE '%"category_name":"취향이 닮은 사람들의 추천"%') THEN '추천2'
WHEN source_tab = '템플릿화면' AND (search_keyword_category = '좋아할 만한 스타일' OR search_keyword_category LIKE '%"category_name":"좋아할 만한 스타일"%') THEN '추천3'
WHEN source_tab = '템플릿화면' AND (search_keyword_category = '추천 템플릿 둘러보기' OR search_keyword_category LIKE '%"category_name":"추천 템플릿 둘러보기"%') THEN '추천4'
when p_date >= '2026-02-10' AND source_tab = '템플릿화면' and (search_keyword_category = 'MDI 업종 추천' OR search_keyword_category LIKE '%"category_name":"MDI 업종 추천"%') then '추천5'
when source_tab = '템플릿화면' and (search_keyword_category = 'TOP 100' OR search_keyword_category LIKE '%"category_name":"TOP 100"%') then '추천6'
when source_tab = '템플릿화면' and (search_keyword_category = '취향 분석 완료' OR search_keyword_category LIKE '%"category_name":"취향 분석 완료"%') then '추천7'
ELSE search_keyword_category
END AS search_keyword_category
from bronze.google_analytics_miricanvas.events_except_miridih ge
join action_exp a ON COALESCE(NULLIF(ge.user_id, '0'), NULLIF(ge.account_id, '0')) = a.user_id AND a.first_action_date < ge.p_date
WHERE event_name = 'action_success_template_workspace'
and p_date between '2026-08-01' and '2026-08-31'
AND page_location NOT LIKE '%staging%'
AND ((search_keyword_category IN ('취향이 닮은 사람들의 추천', '좋아할 만한 스타일', '추천 템플릿 둘러보기','MDI 업종 추천','TOP 100','취향 분석 완료') OR (search_keyword_category LIKE '%"category_name":"취향이 닮은 사람들의 추천"%'
OR search_keyword_category LIKE '%"category_name":"좋아할 만한 스타일"%'
OR search_keyword_category LIKE '%"category_name":"추천 템플릿 둘러보기"%'
OR search_keyword_category LIKE '%"category_name":"MDI 업종 추천"%'
OR search_keyword_category LIKE '%"category_name":"TOP 100"%' OR search_keyword_category LIKE '%"category_name":"취향 분석 완료"%'))
OR source_tab = '템플릿화면_상단추천영역')
and `geo.country` = 'South Korea'
), action2 AS (
SELECT p_date, user_id, source_tab, search_keyword_category, min(rank_range2) as min_rank
from action2_base group by 1,2,3,4
), funnel_base as (
SELECT
v.p_date, v.search_keyword_category, v.user_id, a.min_rank,
CASE WHEN d.user_id IS NOT NULL THEN 1 ELSE 0 END AS detail_flag,
CASE WHEN a.user_id IS NOT NULL THEN 1 ELSE 0 END AS action_flag,
CASE WHEN a2.user_id IS NOT NULL THEN 1 ELSE 0 END AS action2_flag
FROM visit v
LEFT JOIN detail d ON v.user_id = d.user_id AND v.p_date = d.p_date and v.source_tab = d.source_tab AND v.search_keyword_category = d.search_keyword_category
LEFT JOIN action a ON v.user_id = a.user_id AND v.p_date = a.p_date and v.source_tab = a.source_tab AND v.search_keyword_category = a.search_keyword_category
LEFT JOIN action2 a2 ON v.user_id = a2.user_id AND v.p_date = a2.p_date and v.source_tab = a2.source_tab AND v.search_keyword_category = a2.search_keyword_category and a.min_rank = a2.min_rank
), daily AS (
SELECT p_date, search_keyword_category AS slot,
COUNT(DISTINCT user_id) AS visit_user_cnt,
COUNT(DISTINCT CASE WHEN action2_flag = 1 AND detail_flag = 1 THEN user_id END) AS action2_user_cnt,
COUNT(DISTINCT CASE WHEN action2_flag = 1 and detail_flag = 1 and action_flag = 1 and min_rank = 1 THEN user_id END) AS action2_under_10,
COUNT(*) AS visit_cnt,
SUM(CASE WHEN action2_flag = 1 AND detail_flag = 1 THEN 1 ELSE 0 END) AS action2_cnt
FROM funnel_base WHERE search_keyword_category not in ('추천외') GROUP BY 1,2
), mon AS (
SELECT search_keyword_category AS slot,
COUNT(DISTINCT user_id) AS mau_visit_user,
COUNT(DISTINCT CASE WHEN action2_flag=1 AND detail_flag=1 THEN user_id END) AS mau_action_user
FROM funnel_base WHERE search_keyword_category not in ('추천외') GROUP BY 1
)
SELECT d.slot,
SUM(d.visit_user_cnt) AS expose_user_days,
SUM(d.action2_user_cnt) AS adopt_user_days,
ROUND(SUM(d.action2_user_cnt)*100.0/NULLIF(SUM(d.visit_user_cnt),0),2) AS adopt_rate_pct,
ROUND(SUM(d.action2_under_10)*100.0/NULLIF(SUM(d.visit_user_cnt),0),2) AS top10_rate_pct,
SUM(d.visit_cnt) AS expose_cnt, SUM(d.action2_cnt) AS adopt_cnt,
ROUND(SUM(d.action2_cnt)*100.0/NULLIF(SUM(d.visit_cnt),0),2) AS adopt_rate_cnt_pct,
m.mau_visit_user AS mau_expose_user, m.mau_action_user AS mau_adopt_user,
ROUND(m.mau_action_user*100.0/NULLIF(m.mau_visit_user,0),2) AS mau_adopt_rate_pct
FROM daily d JOIN mon m ON d.slot = m.slot
GROUP BY d.slot, m.mau_visit_user, m.mau_action_user
ORDER BY d.slot대시보드 데이터셋 685938a1([v2] 에디터페이지 슬롯별 Raw) 재현. 상세 단계가 없는 2단 퍼널.
WITH action_exp AS (
SELECT user_id FROM silver.miricanvas_searching.action_template
WHERE event_date >= '2022-01-01' AND user_id IS NOT NULL GROUP BY user_id
),
view_base AS (
SELECT ge.p_date, COALESCE(NULLIF(ge.user_id, '0'), NULLIF(ge.account_id, '0')) AS user_id,
CASE
WHEN ge.search_keyword_category = 'XXX님을 위한 추천 템플릿' AND ge.search_status = '뷰' THEN '추천1'
WHEN (ge.search_keyword_category = '취향이 닮은 사람들의 추천'
OR (ge.search_keyword_category IS NULL AND ge.search_type = '취향이 닮은 사람들이 고른 템플릿' AND ge.priority != 4))
AND ge.search_status = '뷰' THEN '추천2'
WHEN (ge.search_keyword_category IN ('좋아할 만한 스타일','최근 눈여겨본 스타일')
OR (ge.search_keyword_category IS NULL AND ge.search_type = '내 디자인 감각에 어울리는 스타일' AND ge.priority != 4))
AND ge.search_status = '뷰' THEN '추천3'
WHEN (ge.search_keyword_category = '추천 템플릿 둘러보기'
OR (ge.search_keyword_category IS NULL AND ge.search_type = '기타' AND ge.priority = 4))
AND ge.search_status = '뷰' THEN '추천4'
WHEN ge.search_keyword_category = 'MDI 업종 추천' AND ge.p_date >= '2026-02-10' AND ge.search_status='뷰' THEN '추천5'
WHEN ge.search_keyword_category = 'TOP 100' AND ge.p_date >= '2026-03-11' AND ge.search_status='뷰' THEN '추천6'
WHEN ge.search_keyword_category = '취향 분석 완료' AND ge.p_date >= '2026-04-21' AND ge.search_status='뷰' THEN '추천7'
ELSE '추천외'
END AS slot
FROM bronze.google_analytics_miricanvas.events_except_miridih ge
JOIN action_exp a ON COALESCE(NULLIF(ge.user_id, '0'), NULLIF(ge.account_id, '0')) = a.user_id
WHERE ge.event_name = 'view_template'
AND ge.p_date BETWEEN '2026-08-01' AND '2026-08-31'
AND ge.page_location NOT LIKE '%staging%'
AND ge.page_location LIKE '%/v2/%'
AND ge.`geo.country` = 'South Korea'
AND ge.source_tab = '템플릿패널'
AND COALESCE(NULLIF(ge.user_id, '0'), NULLIF(ge.account_id, '0')) IS NOT NULL
),
visit AS (SELECT * FROM view_base WHERE slot <> '추천외'),
action_full AS (
SELECT ge.p_date, COALESCE(NULLIF(ge.user_id, '0'), NULLIF(ge.account_id, '0')) AS user_id, ge.priority2,
CASE
WHEN ge.search_keyword_category = 'XXX님을 위한 추천 템플릿' AND ge.search_status='뷰' THEN '추천1'
WHEN (ge.search_keyword_category = '취향이 닮은 사람들의 추천'
OR (ge.search_keyword_category IS NULL AND ge.search_type = '취향이 닮은 사람들이 고른 템플릿' AND ge.priority != 4))
AND ge.search_status='뷰' THEN '추천2'
WHEN (ge.search_keyword_category IN ('좋아할 만한 스타일','최근 눈여겨본 스타일')
OR (ge.search_keyword_category IS NULL AND ge.search_type = '내 디자인 감각에 어울리는 스타일' AND ge.priority != 4))
AND ge.search_status='뷰' THEN '추천3'
WHEN (ge.search_keyword_category = '추천 템플릿 둘러보기'
OR (ge.search_keyword_category IS NULL AND ge.search_type = '기타' AND ge.priority = 4))
AND ge.search_status='뷰' THEN '추천4'
WHEN ge.search_keyword_category = 'MDI 업종 추천' AND ge.p_date >= '2026-02-10' AND ge.search_status='뷰' THEN '추천5'
WHEN ge.search_keyword_category = 'TOP 100' AND ge.p_date >= '2026-03-11' AND ge.search_status='뷰' THEN '추천6'
WHEN ge.search_keyword_category = '취향 분석 완료' AND ge.p_date >= '2026-04-21' AND ge.search_status='뷰' THEN '추천7'
ELSE '추천외'
END AS slot
FROM bronze.google_analytics_miricanvas.events_except_miridih ge
JOIN action_exp a ON COALESCE(NULLIF(ge.user_id, '0'), NULLIF(ge.account_id, '0')) = a.user_id
WHERE ge.event_name = 'action_template'
AND ge.p_date BETWEEN '2026-08-01' AND '2026-08-31'
AND ge.page_location NOT LIKE '%staging%'
AND ge.page_location LIKE '%/v2/%'
AND ge.`geo.country` = 'South Korea'
AND ge.source_tab = '템플릿패널'
AND COALESCE(NULLIF(ge.user_id, '0'), NULLIF(ge.account_id, '0')) IS NOT NULL
),
action_reco AS (
SELECT p_date, user_id, slot, MIN(cast(priority2 AS INT)+1) AS min_rank
FROM action_full WHERE slot <> '추천외' GROUP BY p_date, user_id, slot
),
funnel_base AS (
SELECT v.p_date, v.user_id, v.slot,
CASE WHEN a.user_id IS NOT NULL THEN 1 ELSE 0 END AS action_flag,
CASE WHEN a.min_rank IS NOT NULL AND a.min_rank <= 10 THEN 1 ELSE 0 END AS top10_flag
FROM visit v
LEFT JOIN action_reco a ON v.p_date=a.p_date AND v.user_id=a.user_id AND v.slot=a.slot
),
daily AS (
SELECT p_date, slot,
COUNT(DISTINCT user_id) AS visit_user_cnt,
COUNT(DISTINCT CASE WHEN action_flag=1 THEN user_id END) AS action_user_cnt,
COUNT(DISTINCT CASE WHEN action_flag=1 AND top10_flag=1 THEN user_id END) AS action_user_under_10,
COUNT(*) AS visit_cnt,
SUM(action_flag) AS action_cnt
FROM funnel_base GROUP BY 1,2
),
mon AS (
SELECT slot, COUNT(DISTINCT user_id) AS mau_expose_user,
COUNT(DISTINCT CASE WHEN action_flag=1 THEN user_id END) AS mau_adopt_user
FROM funnel_base GROUP BY 1
)
SELECT d.slot,
SUM(d.visit_user_cnt) AS expose_user_days,
SUM(d.action_user_cnt) AS adopt_user_days,
ROUND(SUM(d.action_user_cnt)*100.0/NULLIF(SUM(d.visit_user_cnt),0),2) AS adopt_rate_pct,
ROUND(SUM(d.action_user_under_10)*100.0/NULLIF(SUM(d.visit_user_cnt),0),2) AS top10_rate_pct,
SUM(d.visit_cnt) AS expose_cnt, SUM(d.action_cnt) AS adopt_cnt,
m.mau_expose_user, m.mau_adopt_user,
ROUND(m.mau_adopt_user*100.0/NULLIF(m.mau_expose_user,0),2) AS mau_adopt_rate_pct
FROM daily d JOIN mon m ON d.slot=m.slot
GROUP BY d.slot, m.mau_expose_user, m.mau_adopt_user
ORDER BY d.slot채택 경로 분해(d607ed0f)의 채널 정의에서 추천 7종을 먼저 걸러낸 뒤 키워드카테고리만 슬롯과 같은 퍼널로 계산.
WITH action_exp AS (
SELECT user_id, MIN(event_date) AS first_action_date
FROM silver.miricanvas_searching.action_template
WHERE event_date >= '2022-01-01' AND user_id IS NOT NULL GROUP BY user_id
),
base AS (
SELECT ge.p_date, COALESCE(NULLIF(ge.user_id,'0'), NULLIF(ge.account_id,'0')) AS user_id,
ge.event_name, ge.source_tab, ge.search_type, ge.search_keyword_category, ge.search_status,
CASE WHEN cast(ge.priority2 as int) <= 10 THEN 1 WHEN cast(ge.priority2 as int) <= 20 THEN 2
WHEN cast(ge.priority2 as int) <= 30 THEN 3 WHEN cast(ge.priority2 as int) <= 40 THEN 4
WHEN cast(ge.priority2 as int) <= 50 THEN 5 ELSE 6 END AS rank_range2
FROM bronze.google_analytics_miricanvas.events_except_miridih ge
JOIN action_exp a ON COALESCE(NULLIF(ge.user_id,'0'), NULLIF(ge.account_id,'0')) = a.user_id
AND a.first_action_date < ge.p_date
WHERE ge.p_date BETWEEN '2026-08-01' AND '2026-08-31'
AND ge.event_name IN ('view_template_workspace','detail_template_workspace','action_template_workspace','action_success_template_workspace')
AND ge.page_location NOT LIKE '%staging%'
AND ge.page_location LIKE '%/templates%'
AND ge.`geo.country` = 'South Korea'
AND COALESCE(NULLIF(ge.user_id,'0'), NULLIF(ge.account_id,'0')) IS NOT NULL
),
labeled AS (
SELECT p_date, user_id, event_name, rank_range2,
CASE
WHEN search_type = '비슷한템플릿찾기' AND search_keyword_category IS NULL
AND COALESCE(source_tab,'') <> '템플릿화면_상단추천영역' AND search_status='검색' THEN '비템찾(검색)'
WHEN source_tab = '템플릿화면_상단추천영역' THEN '추천'
WHEN event_name <> 'detail_template_workspace' AND source_tab = '템플릿화면' AND search_keyword_category LIKE '%"category_name":"XXX님을 위한 추천 템플릿"%' THEN '추천'
WHEN source_tab = '템플릿화면' AND (search_keyword_category = '취향이 닮은 사람들의 추천' OR search_keyword_category LIKE '%"category_name":"취향이 닮은 사람들의 추천"%') THEN '추천'
WHEN source_tab = '템플릿화면' AND (search_keyword_category = '좋아할 만한 스타일' OR search_keyword_category LIKE '%"category_name":"좋아할 만한 스타일"%') THEN '추천'
WHEN source_tab = '템플릿화면' AND (search_keyword_category = '추천 템플릿 둘러보기' OR search_keyword_category LIKE '%"category_name":"추천 템플릿 둘러보기"%') THEN '추천'
WHEN p_date >= '2026-02-10' AND source_tab = '템플릿화면' AND (search_keyword_category = 'MDI 업종 추천' OR search_keyword_category LIKE '%"category_name":"MDI 업종 추천"%') THEN '추천'
WHEN source_tab = '템플릿화면' AND (search_keyword_category = 'TOP 100' OR search_keyword_category LIKE '%"category_name":"TOP 100"%') THEN '추천'
WHEN source_tab = '템플릿화면' AND (search_keyword_category = '취향 분석 완료' OR search_keyword_category LIKE '%"category_name":"취향 분석 완료"%') THEN '추천'
WHEN search_keyword_category LIKE '%"type":"RECOMMEND"%' THEN '키워드카테고리'
WHEN search_keyword_category LIKE '%"type":"KEYWORD_CATEGORY"%' THEN '키워드카테고리'
WHEN search_keyword_category IS NOT NULL THEN '키워드카테고리'
WHEN search_type = '추천더보기' THEN '키워드카테고리'
ELSE '기타'
END AS channel
FROM base
),
kw AS (SELECT * FROM labeled WHERE channel = '키워드카테고리'),
visit AS (SELECT DISTINCT p_date, user_id FROM kw WHERE event_name='view_template_workspace'),
detail AS (SELECT DISTINCT p_date, user_id FROM kw WHERE event_name='detail_template_workspace'),
action AS (SELECT p_date, user_id, MIN(rank_range2) AS min_rank FROM kw WHERE event_name='action_template_workspace' GROUP BY 1,2),
action2 AS (SELECT p_date, user_id, MIN(rank_range2) AS min_rank FROM kw WHERE event_name='action_success_template_workspace' GROUP BY 1,2),
visit_ev AS (SELECT p_date, user_id FROM kw WHERE event_name='view_template_workspace'),
funnel_base AS (
SELECT v.p_date, v.user_id, a.min_rank,
CASE WHEN d.user_id IS NOT NULL THEN 1 ELSE 0 END AS detail_flag,
CASE WHEN a.user_id IS NOT NULL THEN 1 ELSE 0 END AS action_flag,
CASE WHEN a2.user_id IS NOT NULL THEN 1 ELSE 0 END AS action2_flag
FROM visit_ev v
LEFT JOIN detail d ON v.p_date=d.p_date AND v.user_id=d.user_id
LEFT JOIN action a ON v.p_date=a.p_date AND v.user_id=a.user_id
LEFT JOIN action2 a2 ON v.p_date=a2.p_date AND v.user_id=a2.user_id AND a.min_rank=a2.min_rank
),
daily AS (
SELECT p_date,
COUNT(DISTINCT user_id) AS visit_user_cnt,
COUNT(DISTINCT CASE WHEN action2_flag=1 AND detail_flag=1 THEN user_id END) AS action2_user_cnt,
COUNT(DISTINCT CASE WHEN action2_flag=1 AND detail_flag=1 AND action_flag=1 AND min_rank=1 THEN user_id END) AS action2_under_10,
COUNT(*) AS visit_cnt,
SUM(CASE WHEN action2_flag=1 AND detail_flag=1 THEN 1 ELSE 0 END) AS action2_cnt
FROM funnel_base GROUP BY 1
),
mon AS (
SELECT COUNT(DISTINCT user_id) AS mau_expose_user,
COUNT(DISTINCT CASE WHEN action2_flag=1 AND detail_flag=1 THEN user_id END) AS mau_adopt_user
FROM funnel_base
)
SELECT '키워드카테고리' AS slot,
SUM(d.visit_user_cnt) AS expose_user_days,
SUM(d.action2_user_cnt) AS adopt_user_days,
ROUND(SUM(d.action2_user_cnt)*100.0/NULLIF(SUM(d.visit_user_cnt),0),2) AS adopt_rate_pct,
ROUND(SUM(d.action2_under_10)*100.0/NULLIF(SUM(d.visit_user_cnt),0),2) AS top10_rate_pct,
SUM(d.visit_cnt) AS expose_cnt, SUM(d.action2_cnt) AS adopt_cnt,
ROUND(SUM(d.action2_cnt)*100.0/NULLIF(SUM(d.visit_cnt),0),2) AS adopt_rate_cnt_pct,
MAX(m.mau_expose_user) AS mau_expose_user, MAX(m.mau_adopt_user) AS mau_adopt_user,
ROUND(MAX(m.mau_adopt_user)*100.0/NULLIF(MAX(m.mau_expose_user),0),2) AS mau_adopt_rate_pct
FROM daily d CROSS JOIN mon m에디터 채널 분해(c8493582) 정의 기준 키워드카테고리.
WITH action_exp AS (
SELECT user_id FROM silver.miricanvas_searching.action_template
WHERE event_date >= '2022-01-01' AND user_id IS NOT NULL GROUP BY user_id
),
base AS (
SELECT ge.p_date, COALESCE(NULLIF(ge.user_id,'0'), NULLIF(ge.account_id,'0')) AS user_id,
ge.event_name, ge.search_type, ge.search_keyword_category, ge.search_status, ge.priority,
cast(ge.priority2 as int) + 1 AS rnk
FROM bronze.google_analytics_miricanvas.events_except_miridih ge
JOIN action_exp a ON COALESCE(NULLIF(ge.user_id,'0'), NULLIF(ge.account_id,'0')) = a.user_id
WHERE ge.p_date BETWEEN '2026-08-01' AND '2026-08-31'
AND ge.event_name IN ('view_template','action_template')
AND ge.page_location LIKE '%/v2/%'
AND ge.page_location NOT LIKE '%staging%'
AND ge.`geo.country` = 'South Korea'
AND ge.source_tab = '템플릿패널'
AND COALESCE(NULLIF(ge.user_id,'0'), NULLIF(ge.account_id,'0')) IS NOT NULL
),
labeled AS (
SELECT p_date, user_id, event_name, rnk,
CASE
WHEN search_type = '비슷한템플릿찾기' AND search_keyword_category IS NULL AND search_status = '검색' THEN '비템찾(검색)'
WHEN search_keyword_category = 'XXX님을 위한 추천 템플릿' AND search_status = '뷰' THEN '추천'
WHEN (search_keyword_category = '취향이 닮은 사람들의 추천'
OR (search_keyword_category IS NULL AND search_type = '취향이 닮은 사람들이 고른 템플릿' AND priority != 4))
AND search_status = '뷰' THEN '추천'
WHEN (search_keyword_category IN ('좋아할 만한 스타일', '최근 눈여겨본 스타일')
OR (search_keyword_category IS NULL AND search_type = '내 디자인 감각에 어울리는 스타일' AND priority != 4))
AND search_status = '뷰' THEN '추천'
WHEN (search_keyword_category = '추천 템플릿 둘러보기'
OR (search_keyword_category IS NULL AND search_type = '기타' AND priority = 4))
AND search_status = '뷰' THEN '추천'
WHEN search_keyword_category = 'MDI 업종 추천' AND p_date >= '2026-02-10' AND search_status='뷰' THEN '추천'
WHEN search_keyword_category = 'TOP 100' AND p_date >= '2026-03-11' AND search_status='뷰' THEN '추천'
WHEN search_keyword_category = '취향 분석 완료' AND p_date >= '2026-04-21' AND search_status='뷰' THEN '추천'
WHEN search_keyword_category IN ('최근사용', 'Recently used') THEN '최근사용'
WHEN search_keyword_category IS NOT NULL AND search_keyword_category <> '' THEN '키워드카테고리'
ELSE '기타'
END AS channel
FROM base
),
kw AS (SELECT * FROM labeled WHERE channel='키워드카테고리'),
visit AS (SELECT p_date, user_id FROM kw WHERE event_name='view_template'),
act AS (SELECT p_date, user_id, MIN(rnk) AS min_rank FROM kw WHERE event_name='action_template' GROUP BY 1,2),
funnel_base AS (
SELECT v.p_date, v.user_id,
CASE WHEN a.user_id IS NOT NULL THEN 1 ELSE 0 END AS action_flag,
CASE WHEN a.min_rank IS NOT NULL AND a.min_rank <= 10 THEN 1 ELSE 0 END AS top10_flag
FROM visit v LEFT JOIN act a ON v.p_date=a.p_date AND v.user_id=a.user_id
),
daily AS (
SELECT p_date,
COUNT(DISTINCT user_id) AS visit_user_cnt,
COUNT(DISTINCT CASE WHEN action_flag=1 THEN user_id END) AS action_user_cnt,
COUNT(DISTINCT CASE WHEN action_flag=1 AND top10_flag=1 THEN user_id END) AS action_user_under_10,
COUNT(*) AS visit_cnt, SUM(action_flag) AS action_cnt
FROM funnel_base GROUP BY 1
),
mon AS (
SELECT COUNT(DISTINCT user_id) AS mau_expose_user,
COUNT(DISTINCT CASE WHEN action_flag=1 THEN user_id END) AS mau_adopt_user
FROM funnel_base
)
SELECT '키워드카테고리' AS slot,
SUM(d.visit_user_cnt) AS expose_user_days,
SUM(d.action_user_cnt) AS adopt_user_days,
ROUND(SUM(d.action_user_cnt)*100.0/NULLIF(SUM(d.visit_user_cnt),0),2) AS adopt_rate_pct,
ROUND(SUM(d.action_user_under_10)*100.0/NULLIF(SUM(d.visit_user_cnt),0),2) AS top10_rate_pct,
SUM(d.visit_cnt) AS expose_cnt, SUM(d.action_cnt) AS adopt_cnt,
ROUND(SUM(d.action_cnt)*100.0/NULLIF(SUM(d.visit_cnt),0),2) AS adopt_rate_cnt_pct,
MAX(m.mau_expose_user) AS mau_expose_user, MAX(m.mau_adopt_user) AS mau_adopt_user,
ROUND(MAX(m.mau_adopt_user)*100.0/NULLIF(MAX(m.mau_expose_user),0),2) AS mau_adopt_rate_pct
FROM daily d CROSS JOIN mon m추천1 노출 이력으로 모집단을 맞춘 세그먼트 4종(전체·당일노출·기간내노출·미노출).
WITH action_exp AS (
SELECT user_id, MIN(event_date) AS first_action_date
FROM silver.miricanvas_searching.action_template
WHERE event_date >= '2022-01-01' AND user_id IS NOT NULL GROUP BY user_id
),
base AS (
SELECT ge.p_date, COALESCE(NULLIF(ge.user_id,'0'), NULLIF(ge.account_id,'0')) AS user_id,
ge.event_name, ge.source_tab, ge.search_type, ge.search_keyword_category, ge.search_status,
CASE WHEN cast(ge.priority2 as int) <= 10 THEN 1 WHEN cast(ge.priority2 as int) <= 20 THEN 2
WHEN cast(ge.priority2 as int) <= 30 THEN 3 WHEN cast(ge.priority2 as int) <= 40 THEN 4
WHEN cast(ge.priority2 as int) <= 50 THEN 5 ELSE 6 END AS rank_range2
FROM bronze.google_analytics_miricanvas.events_except_miridih ge
JOIN action_exp a ON COALESCE(NULLIF(ge.user_id,'0'), NULLIF(ge.account_id,'0')) = a.user_id
AND a.first_action_date < ge.p_date
WHERE ge.p_date BETWEEN '2026-08-01' AND '2026-08-31'
AND ge.event_name IN ('view_template_workspace','detail_template_workspace','action_template_workspace','action_success_template_workspace')
AND ge.page_location NOT LIKE '%staging%'
AND ge.page_location LIKE '%/templates%'
AND ge.`geo.country` = 'South Korea'
AND COALESCE(NULLIF(ge.user_id,'0'), NULLIF(ge.account_id,'0')) IS NOT NULL
),
labeled AS (
SELECT p_date, user_id, event_name, rank_range2, source_tab, search_keyword_category,
CASE
WHEN search_type = '비슷한템플릿찾기' AND search_keyword_category IS NULL
AND COALESCE(source_tab,'') <> '템플릿화면_상단추천영역' AND search_status='검색' THEN '비템찾(검색)'
WHEN source_tab = '템플릿화면_상단추천영역' THEN '추천'
WHEN event_name <> 'detail_template_workspace' AND source_tab = '템플릿화면' AND search_keyword_category LIKE '%"category_name":"XXX님을 위한 추천 템플릿"%' THEN '추천'
WHEN source_tab = '템플릿화면' AND (search_keyword_category = '취향이 닮은 사람들의 추천' OR search_keyword_category LIKE '%"category_name":"취향이 닮은 사람들의 추천"%') THEN '추천'
WHEN source_tab = '템플릿화면' AND (search_keyword_category = '좋아할 만한 스타일' OR search_keyword_category LIKE '%"category_name":"좋아할 만한 스타일"%') THEN '추천'
WHEN source_tab = '템플릿화면' AND (search_keyword_category = '추천 템플릿 둘러보기' OR search_keyword_category LIKE '%"category_name":"추천 템플릿 둘러보기"%') THEN '추천'
WHEN p_date >= '2026-02-10' AND source_tab = '템플릿화면' AND (search_keyword_category = 'MDI 업종 추천' OR search_keyword_category LIKE '%"category_name":"MDI 업종 추천"%') THEN '추천'
WHEN source_tab = '템플릿화면' AND (search_keyword_category = 'TOP 100' OR search_keyword_category LIKE '%"category_name":"TOP 100"%') THEN '추천'
WHEN source_tab = '템플릿화면' AND (search_keyword_category = '취향 분석 완료' OR search_keyword_category LIKE '%"category_name":"취향 분석 완료"%') THEN '추천'
WHEN search_keyword_category LIKE '%"type":"RECOMMEND"%' THEN '키워드카테고리'
WHEN search_keyword_category LIKE '%"type":"KEYWORD_CATEGORY"%' THEN '키워드카테고리'
WHEN search_keyword_category IS NOT NULL THEN '키워드카테고리'
WHEN search_type = '추천더보기' THEN '키워드카테고리'
ELSE '기타'
END AS channel
FROM base
),
/* 추천1 노출 유저 = 상단추천영역 뷰 또는 템플릿화면의 'XXX님을 위한 추천 템플릿' 뷰 */
r1_day AS (
SELECT DISTINCT p_date, user_id FROM labeled
WHERE event_name='view_template_workspace'
AND (source_tab = '템플릿화면_상단추천영역'
OR (source_tab='템플릿화면' AND search_keyword_category LIKE '%"category_name":"XXX님을 위한 추천 템플릿"%'))
),
r1_any AS (SELECT DISTINCT user_id FROM r1_day),
kw AS (SELECT * FROM labeled WHERE channel = '키워드카테고리'),
detail AS (SELECT DISTINCT p_date, user_id FROM kw WHERE event_name='detail_template_workspace'),
action AS (SELECT p_date, user_id, MIN(rank_range2) AS min_rank FROM kw WHERE event_name='action_template_workspace' GROUP BY 1,2),
action2 AS (SELECT p_date, user_id, MIN(rank_range2) AS min_rank FROM kw WHERE event_name='action_success_template_workspace' GROUP BY 1,2),
visit_ev AS (SELECT p_date, user_id FROM kw WHERE event_name='view_template_workspace'),
funnel_base AS (
SELECT v.p_date, v.user_id, a.min_rank,
CASE WHEN d.user_id IS NOT NULL THEN 1 ELSE 0 END AS detail_flag,
CASE WHEN a.user_id IS NOT NULL THEN 1 ELSE 0 END AS action_flag,
CASE WHEN a2.user_id IS NOT NULL THEN 1 ELSE 0 END AS action2_flag,
CASE WHEN r.user_id IS NOT NULL THEN 1 ELSE 0 END AS r1_same_day,
CASE WHEN ra.user_id IS NOT NULL THEN 1 ELSE 0 END AS r1_any_day
FROM visit_ev v
LEFT JOIN detail d ON v.p_date=d.p_date AND v.user_id=d.user_id
LEFT JOIN action a ON v.p_date=a.p_date AND v.user_id=a.user_id
LEFT JOIN action2 a2 ON v.p_date=a2.p_date AND v.user_id=a2.user_id AND a.min_rank=a2.min_rank
LEFT JOIN r1_day r ON v.p_date=r.p_date AND v.user_id=r.user_id
LEFT JOIN r1_any ra ON v.user_id=ra.user_id
),
seg AS (
SELECT 'ALL' AS segment, * FROM funnel_base
UNION ALL SELECT '추천1_당일노출' AS segment, * FROM funnel_base WHERE r1_same_day=1
UNION ALL SELECT '추천1_기간내노출' AS segment, * FROM funnel_base WHERE r1_any_day=1
UNION ALL SELECT '추천1_미노출(기간내)' AS segment, * FROM funnel_base WHERE r1_any_day=0
),
daily AS (
SELECT segment, p_date,
COUNT(DISTINCT user_id) AS visit_user_cnt,
COUNT(DISTINCT CASE WHEN action2_flag=1 AND detail_flag=1 THEN user_id END) AS action2_user_cnt,
COUNT(DISTINCT CASE WHEN action2_flag=1 AND detail_flag=1 AND action_flag=1 AND min_rank=1 THEN user_id END) AS action2_under_10,
COUNT(*) AS visit_cnt,
SUM(CASE WHEN action2_flag=1 AND detail_flag=1 THEN 1 ELSE 0 END) AS action2_cnt
FROM seg GROUP BY 1,2
),
mon AS (
SELECT segment, COUNT(DISTINCT user_id) AS mau_expose_user,
COUNT(DISTINCT CASE WHEN action2_flag=1 AND detail_flag=1 THEN user_id END) AS mau_adopt_user
FROM seg GROUP BY 1
)
SELECT d.segment,
SUM(d.visit_user_cnt) AS expose_user_days,
SUM(d.action2_user_cnt) AS adopt_user_days,
ROUND(SUM(d.action2_user_cnt)*100.0/NULLIF(SUM(d.visit_user_cnt),0),2) AS adopt_rate_pct,
ROUND(SUM(d.action2_under_10)*100.0/NULLIF(SUM(d.visit_user_cnt),0),2) AS top10_rate_pct,
SUM(d.visit_cnt) AS expose_cnt, SUM(d.action2_cnt) AS adopt_cnt,
ROUND(SUM(d.action2_cnt)*100.0/NULLIF(SUM(d.visit_cnt),0),2) AS adopt_rate_cnt_pct,
m.mau_expose_user, m.mau_adopt_user,
ROUND(m.mau_adopt_user*100.0/NULLIF(m.mau_expose_user,0),2) AS mau_adopt_rate_pct
FROM daily d JOIN mon m ON d.segment=m.segment
GROUP BY d.segment, m.mau_expose_user, m.mau_adopt_user
ORDER BY d.segment에디터엔 추천1 로그가 없어 추천 슬롯 전체 노출로 대체한 세그먼트.
WITH action_exp AS (
SELECT user_id FROM silver.miricanvas_searching.action_template
WHERE event_date >= '2022-01-01' AND user_id IS NOT NULL GROUP BY user_id
),
base AS (
SELECT ge.p_date, COALESCE(NULLIF(ge.user_id,'0'), NULLIF(ge.account_id,'0')) AS user_id,
ge.event_name, ge.search_type, ge.search_keyword_category, ge.search_status, ge.priority,
cast(ge.priority2 as int) + 1 AS rnk
FROM bronze.google_analytics_miricanvas.events_except_miridih ge
JOIN action_exp a ON COALESCE(NULLIF(ge.user_id,'0'), NULLIF(ge.account_id,'0')) = a.user_id
WHERE ge.p_date BETWEEN '2026-08-01' AND '2026-08-31'
AND ge.event_name IN ('view_template','action_template')
AND ge.page_location LIKE '%/v2/%' AND ge.page_location NOT LIKE '%staging%'
AND ge.`geo.country` = 'South Korea' AND ge.source_tab = '템플릿패널'
AND COALESCE(NULLIF(ge.user_id,'0'), NULLIF(ge.account_id,'0')) IS NOT NULL
),
labeled AS (
SELECT p_date, user_id, event_name, rnk,
CASE
WHEN search_type = '비슷한템플릿찾기' AND search_keyword_category IS NULL AND search_status = '검색' THEN '비템찾(검색)'
WHEN search_keyword_category = 'XXX님을 위한 추천 템플릿' AND search_status = '뷰' THEN '추천'
WHEN (search_keyword_category = '취향이 닮은 사람들의 추천'
OR (search_keyword_category IS NULL AND search_type = '취향이 닮은 사람들이 고른 템플릿' AND priority != 4))
AND search_status = '뷰' THEN '추천'
WHEN (search_keyword_category IN ('좋아할 만한 스타일', '최근 눈여겨본 스타일')
OR (search_keyword_category IS NULL AND search_type = '내 디자인 감각에 어울리는 스타일' AND priority != 4))
AND search_status = '뷰' THEN '추천'
WHEN (search_keyword_category = '추천 템플릿 둘러보기'
OR (search_keyword_category IS NULL AND search_type = '기타' AND priority = 4))
AND search_status = '뷰' THEN '추천'
WHEN search_keyword_category = 'MDI 업종 추천' AND p_date >= '2026-02-10' AND search_status='뷰' THEN '추천'
WHEN search_keyword_category = 'TOP 100' AND p_date >= '2026-03-11' AND search_status='뷰' THEN '추천'
WHEN search_keyword_category = '취향 분석 완료' AND p_date >= '2026-04-21' AND search_status='뷰' THEN '추천'
WHEN search_keyword_category IN ('최근사용', 'Recently used') THEN '최근사용'
WHEN search_keyword_category IS NOT NULL AND search_keyword_category <> '' THEN '키워드카테고리'
ELSE '기타'
END AS channel
FROM base
),
reco_day AS (SELECT DISTINCT p_date, user_id FROM labeled WHERE channel='추천' AND event_name='view_template'),
reco_any AS (SELECT DISTINCT user_id FROM reco_day),
kw AS (SELECT * FROM labeled WHERE channel='키워드카테고리'),
visit AS (SELECT p_date, user_id FROM kw WHERE event_name='view_template'),
act AS (SELECT p_date, user_id, MIN(rnk) AS min_rank FROM kw WHERE event_name='action_template' GROUP BY 1,2),
funnel_base AS (
SELECT v.p_date, v.user_id,
CASE WHEN a.user_id IS NOT NULL THEN 1 ELSE 0 END AS action_flag,
CASE WHEN a.min_rank IS NOT NULL AND a.min_rank <= 10 THEN 1 ELSE 0 END AS top10_flag,
CASE WHEN r.user_id IS NOT NULL THEN 1 ELSE 0 END AS reco_same_day,
CASE WHEN ra.user_id IS NOT NULL THEN 1 ELSE 0 END AS reco_any_day
FROM visit v
LEFT JOIN act a ON v.p_date=a.p_date AND v.user_id=a.user_id
LEFT JOIN reco_day r ON v.p_date=r.p_date AND v.user_id=r.user_id
LEFT JOIN reco_any ra ON v.user_id=ra.user_id
),
seg AS (
SELECT 'ALL' AS segment, * FROM funnel_base
UNION ALL SELECT '추천_당일노출', * FROM funnel_base WHERE reco_same_day=1
UNION ALL SELECT '추천_기간내노출', * FROM funnel_base WHERE reco_any_day=1
UNION ALL SELECT '추천_미노출(기간내)', * FROM funnel_base WHERE reco_any_day=0
),
daily AS (
SELECT segment, p_date,
COUNT(DISTINCT user_id) AS visit_user_cnt,
COUNT(DISTINCT CASE WHEN action_flag=1 THEN user_id END) AS action_user_cnt,
COUNT(DISTINCT CASE WHEN action_flag=1 AND top10_flag=1 THEN user_id END) AS action_user_under_10,
COUNT(*) AS visit_cnt, SUM(action_flag) AS action_cnt
FROM seg GROUP BY 1,2
),
mon AS (
SELECT segment, COUNT(DISTINCT user_id) AS mau_expose_user,
COUNT(DISTINCT CASE WHEN action_flag=1 THEN user_id END) AS mau_adopt_user
FROM seg GROUP BY 1
)
SELECT d.segment,
SUM(d.visit_user_cnt) AS expose_user_days,
SUM(d.action_user_cnt) AS adopt_user_days,
ROUND(SUM(d.action_user_cnt)*100.0/NULLIF(SUM(d.visit_user_cnt),0),2) AS adopt_rate_pct,
ROUND(SUM(d.action_user_under_10)*100.0/NULLIF(SUM(d.visit_user_cnt),0),2) AS top10_rate_pct,
SUM(d.visit_cnt) AS expose_cnt, SUM(d.action_cnt) AS adopt_cnt,
ROUND(SUM(d.action_cnt)*100.0/NULLIF(SUM(d.visit_cnt),0),2) AS adopt_rate_cnt_pct,
m.mau_expose_user, m.mau_adopt_user,
ROUND(m.mau_adopt_user*100.0/NULLIF(m.mau_expose_user,0),2) AS mau_adopt_rate_pct
FROM daily d JOIN mon m ON d.segment=m.segment
GROUP BY d.segment, m.mau_expose_user, m.mau_adopt_user
ORDER BY d.segment추천1과 추천3의 리스트 길이 차이를 상세 클릭 순위(priority2)로 확인.
WITH v AS (
SELECT
CASE WHEN source_tab = '템플릿화면_상단추천영역' THEN '추천1'
WHEN source_tab = '템플릿화면' AND (search_keyword_category = 'XXX님을 위한 추천 템플릿' OR search_keyword_category LIKE '%"category_name":"XXX님을 위한 추천 템플릿"%') THEN '추천1'
WHEN source_tab = '템플릿화면' AND (search_keyword_category = '좋아할 만한 스타일' OR search_keyword_category LIKE '%"category_name":"좋아할 만한 스타일"%') THEN '추천3'
END AS slot,
cast(priority2 as int) AS p2
FROM bronze.google_analytics_miricanvas.events_except_miridih
WHERE p_date BETWEEN '2026-08-25' AND '2026-08-31'
AND event_name = 'detail_template_workspace'
AND page_location LIKE '%/templates%' AND page_location NOT LIKE '%staging%'
AND `geo.country` = 'South Korea'
)
SELECT slot, COUNT(*) ev, MIN(p2) p2_min, MAX(p2) p2_max,
percentile_approx(p2, 0.95) p95, percentile_approx(p2, 0.99) p99,
SUM(CASE WHEN p2 <= 9 THEN 1 ELSE 0 END) le9,
SUM(CASE WHEN p2 BETWEEN 10 AND 19 THEN 1 ELSE 0 END) r10_19,
SUM(CASE WHEN p2 >= 20 THEN 1 ELSE 0 END) ge20
FROM v WHERE slot IS NOT NULL AND p2 IS NOT NULL GROUP BY slot ORDER BY slot