에디터에서 가장 많이 채택되는 템플릿 타입

리포트 전체 수치의 원천. 단일 타입 템플릿과 채택 시 필터가 일치한 건만 남겨 타입별 채택·유저수·노출·채택률을 한 번에 냈어요.

-- 확정분만: 단일 타입 템플릿 + (다중 타입이지만 채택 시 타입 필터가 후보와 일치한 건)
-- 추정 구간(다중 타입 + 필터 없음)은 전면 제외. 노출 분모도 같은 규칙 적용.
WITH a AS (
  SELECT user_id, search_template_type stt,
         COALESCE(try_cast(action_item AS BIGINT),
         try_cast(regexp_extract(action_item,'TEMPLATE__[^_]+__(\\d+)__',1) AS BIGINT)) tpl
  FROM silver.miricanvas_searching.action_template
  WHERE event_date BETWEEN '2026-08-11' AND '2026-09-10'
    AND country='South Korea' AND event_name='action_template' AND page_type='에디터'
    AND nullif(action_item,'') IS NOT NULL
),
n AS (SELECT template_idx, count(DISTINCT template_type_id) ntype,
        max(template_type_id) only_tid
      FROM bronze.miridih_miricanvas.template_type_link GROUP BY 1),
lab AS (
  SELECT t.id tid, COALESCE(g.title, t.title) cat, CONCAT(COALESCE(g.title||' ',''), t.title) typ
  FROM bronze.miridih_miricanvas.template_type t
  LEFT JOIN bronze.miridih_miricanvas.template_type_group_item gi ON gi.template_type_id=t.id
  LEFT JOIN bronze.miridih_miricanvas.template_type_group g ON g.id=gi.group_id),
ra AS (
  SELECT a.user_id,
         CASE WHEN n.ntype=1 THEN n.only_tid ELSE h.template_type_id END tid
  FROM a
  JOIN n ON n.template_idx=a.tpl
  LEFT JOIN bronze.miridih_miricanvas.template_type_link h
         ON h.template_idx=a.tpl AND h.template_type_id=a.stt
  WHERE n.ntype=1 OR h.template_type_id IS NOT NULL),
ag AS (SELECT lab.cat, lab.typ, count(*) acts, count(DISTINCT ra.user_id) users
       FROM ra JOIN lab ON lab.tid=ra.tid GROUP BY 1,2),
vv AS (
  SELECT stt, try_cast(trim(k) AS BIGINT) tpl FROM (
  SELECT search_template_type stt, explode(split(view_item_key,',')) k
  FROM silver.miricanvas_searching.view_template
  WHERE event_date BETWEEN '2026-08-11' AND '2026-09-10' AND country='South Korea' AND page_type='에디터'
    AND view_load_num='1' AND split(view_item_type,',')[0]='TEMPLATE'
    AND nullif(view_item_key,'') IS NOT NULL)),
rv AS (
  SELECT CASE WHEN n.ntype=1 THEN n.only_tid ELSE h.template_type_id END tid
  FROM vv
  JOIN n ON n.template_idx=vv.tpl
  LEFT JOIN bronze.miridih_miricanvas.template_type_link h
         ON h.template_idx=vv.tpl AND h.template_type_id=vv.stt
  WHERE n.ntype=1 OR h.template_type_id IS NOT NULL),
vg AS (SELECT lab.cat, lab.typ, count(*) views FROM rv JOIN lab ON lab.tid=rv.tid GROUP BY 1,2)
SELECT ag.cat, ag.typ, ag.acts, ag.users, COALESCE(vg.views,0) views,
       ROUND(100.0*ag.acts/NULLIF(vg.views,0),3) cvr_pct
FROM ag LEFT JOIN vg ON vg.cat=ag.cat AND vg.typ=ag.typ
ORDER BY ag.acts DESC

채택 템플릿 idx가 타입 사전에 얼마나 붙는지 확인했어요. 결과는 100%(미매핑 0건)였어요.

WITH a AS (
  SELECT COALESCE(try_cast(action_item AS BIGINT),
         try_cast(regexp_extract(action_item,'TEMPLATE__[^_]+__(\\d+)__',1) AS BIGINT)) tpl
  FROM silver.miricanvas_searching.action_template
  WHERE event_date BETWEEN '2026-08-11' AND '2026-09-10'
    AND country='South Korea' AND event_name='action_template'
    AND nullif(action_item,'') IS NOT NULL
),
l AS (SELECT DISTINCT template_idx FROM bronze.miridih_miricanvas.template_type_link)
SELECT count(*) acts, count(a.tpl) acts_parsed,
  sum(CASE WHEN l.template_idx IS NOT NULL THEN 1 ELSE 0 END) matched,
  round(100.0*sum(CASE WHEN l.template_idx IS NOT NULL THEN 1 ELSE 0 END)/count(*),2) cov_pct
FROM a LEFT JOIN l ON l.template_idx=a.tpl

채택을 단일 타입(A) · 다중 타입이지만 필터 일치(B) · 다중 타입에 필터 없음(C)으로 나눴어요. C 96.6만 건을 리포트에서 제외했어요.

WITH a AS (
  SELECT search_template_type stt,
         COALESCE(try_cast(action_item AS BIGINT),
         try_cast(regexp_extract(action_item,'TEMPLATE__[^_]+__(\\d+)__',1) AS BIGINT)) tpl
  FROM silver.miricanvas_searching.action_template
  WHERE event_date BETWEEN '2026-08-11' AND '2026-09-10'
    AND country='South Korea' AND event_name='action_template' AND page_type='에디터'
    AND nullif(action_item,'') IS NOT NULL
),
n AS (SELECT template_idx, count(DISTINCT template_type_id) ntype
      FROM bronze.miridih_miricanvas.template_type_link GROUP BY 1)
SELECT CASE WHEN n.ntype=1 THEN 'A. 단일 타입 (확정)'
            WHEN h.template_type_id IS NOT NULL THEN 'B. 다중 + 필터 일치 (확정)'
            ELSE 'C. 다중 + 필터 없음 (추정)' END k,
       count(*) acts, round(100.0*count(*)/sum(count(*)) OVER (),2) pct
FROM a
LEFT JOIN n ON n.template_idx=a.tpl
LEFT JOIN bronze.miridih_miricanvas.template_type_link h
       ON h.template_idx=a.tpl AND h.template_type_id=a.stt
GROUP BY 1 ORDER BY acts DESC

C 구간 템플릿들이 어떤 규격 묶음에 동시 등록돼 있는지 봤어요. 현수막·인쇄 포스터·전단지·웹 포스터가 한 묶음으로 나와요.

WITH a AS (
  SELECT search_template_type stt,
         COALESCE(try_cast(action_item AS BIGINT),
         try_cast(regexp_extract(action_item,'TEMPLATE__[^_]+__(\\d+)__',1) AS BIGINT)) tpl
  FROM silver.miricanvas_searching.action_template
  WHERE event_date BETWEEN '2026-08-11' AND '2026-09-10'
    AND country='South Korea' AND event_name='action_template' AND page_type='에디터'
    AND nullif(action_item,'') IS NOT NULL
),
n AS (SELECT template_idx, count(DISTINCT template_type_id) ntype,
        array_join(array_sort(collect_set(template_type_id)),' + ') combo
      FROM bronze.miridih_miricanvas.template_type_link GROUP BY 1)
SELECT n.combo, count(*) acts
FROM a JOIN n ON n.template_idx=a.tpl
LEFT JOIN bronze.miridih_miricanvas.template_type_link h ON h.template_idx=a.tpl AND h.template_type_id=a.stt
WHERE n.ntype>1 AND h.template_type_id IS NULL
GROUP BY 1 ORDER BY acts DESC LIMIT 12

유저가 채택할 때 실제로 걸어둔 타입 필터의 분포예요. 61.4%가 모든 템플릿 상태라 이 구간은 신호가 없어요.

SELECT CASE WHEN search_template_type IS NULL OR search_template_type='' THEN '(빈값)'
            WHEN lower(search_template_type) IN ('undefined','template','모든 템플릿','all templates') THEN '(모든 템플릿)'
            ELSE search_template_type END v,
       count(*) c, round(100.0*count(*)/sum(count(*)) OVER (),2) pct
FROM silver.miricanvas_searching.action_template
WHERE event_date BETWEEN '2026-08-11' AND '2026-09-10' AND country='South Korea'
  AND event_name='action_template' AND page_type='에디터'
GROUP BY 1 ORDER BY c DESC LIMIT 25

첫 시도. template_type.priority가 낮은 쪽을 대표로 골랐더니 일반 현수막이 허위 1위가 됐어요. 근거 기록용으로만 남겨요.

WITH rep AS (
  SELECT template_idx, l.template_type_id,
    ROW_NUMBER() OVER (PARTITION BY template_idx ORDER BY t.priority NULLS LAST, l.template_type_id) rn
  FROM bronze.miridih_miricanvas.template_type_link l
  LEFT JOIN bronze.miridih_miricanvas.template_type t ON t.id=l.template_type_id
),
map AS (
  SELECT r.template_idx, COALESCE(g.title, t.title) cat
  FROM rep r
  LEFT JOIN bronze.miridih_miricanvas.template_type t ON t.id=r.template_type_id
  LEFT JOIN bronze.miridih_miricanvas.template_type_group_item gi ON gi.template_type_id=r.template_type_id
  LEFT JOIN bronze.miridih_miricanvas.template_type_group g ON g.id=gi.group_id
  WHERE r.rn=1
),
a AS (
  SELECT user_id, COALESCE(try_cast(action_item AS BIGINT),
         try_cast(regexp_extract(action_item,'TEMPLATE__[^_]+__(\\d+)__',1) AS BIGINT)) tpl
  FROM silver.miricanvas_searching.action_template
  WHERE event_date BETWEEN '2026-08-11' AND '2026-09-10'
    AND country='South Korea' AND event_name='action_template' AND page_type='에디터'
    AND nullif(action_item,'') IS NOT NULL
),
ag AS (SELECT COALESCE(m.cat,'(미매핑)') cat, count(*) acts, count(DISTINCT a.user_id) users
       FROM a LEFT JOIN map m ON m.template_idx=a.tpl GROUP BY 1),
v AS (
  SELECT explode(split(view_item_key,',')) k
  FROM silver.miricanvas_searching.view_template
  WHERE event_date BETWEEN '2026-08-11' AND '2026-09-10' AND country='South Korea' AND page_type='에디터'
    AND view_load_num='1' AND split(view_item_type,',')[0]='TEMPLATE'
    AND nullif(view_item_key,'') IS NOT NULL
),
vg AS (SELECT COALESCE(m.cat,'(미매핑)') cat, count(*) views
       FROM v LEFT JOIN map m ON m.template_idx=try_cast(trim(v.k) AS BIGINT) GROUP BY 1)
SELECT ag.cat, ag.acts, ag.users, COALESCE(vg.views,0) views,
  ROUND(100.0*ag.acts/NULLIF(vg.views,0),3) cvr_pct
FROM ag LEFT JOIN vg ON vg.cat=ag.cat
ORDER BY ag.acts DESC

C 구간을 버리지 않고 선택 빈도로 추정 배정한 버전. 웹 포스터가 128만 건 1위로 나오지만 그중 41%가 추정값이에요.

WITH a AS (
  SELECT user_id, search_template_type stt,
         COALESCE(try_cast(action_item AS BIGINT),
         try_cast(regexp_extract(action_item,'TEMPLATE__[^_]+__(\\d+)__',1) AS BIGINT)) tpl
  FROM silver.miricanvas_searching.action_template
  WHERE event_date BETWEEN '2026-08-11' AND '2026-09-10'
    AND country='South Korea' AND event_name='action_template' AND page_type='에디터'
    AND nullif(action_item,'') IS NOT NULL
),
w AS (SELECT a.stt tid, count(*) wt FROM a
      JOIN bronze.miridih_miricanvas.template_type t ON t.id=a.stt GROUP BY 1),
rep AS (
  SELECT l.template_idx, l.template_type_id,
    ROW_NUMBER() OVER (PARTITION BY l.template_idx
      ORDER BY COALESCE(w.wt,0) DESC, t.priority NULLS LAST, l.template_type_id) rn
  FROM bronze.miridih_miricanvas.template_type_link l
  LEFT JOIN w ON w.tid=l.template_type_id
  LEFT JOIN bronze.miridih_miricanvas.template_type t ON t.id=l.template_type_id),
fb AS (SELECT template_idx, template_type_id FROM rep WHERE rn=1),
lab AS (
  SELECT t.id tid, COALESCE(g.title, t.title) cat, CONCAT(COALESCE(g.title||' ',''), t.title) typ
  FROM bronze.miridih_miricanvas.template_type t
  LEFT JOIN bronze.miridih_miricanvas.template_type_group_item gi ON gi.template_type_id=t.id
  LEFT JOIN bronze.miridih_miricanvas.template_type_group g ON g.id=gi.group_id),
ra AS (
  SELECT a.user_id, COALESCE(h.template_type_id, fb.template_type_id) tid
  FROM a
  LEFT JOIN bronze.miridih_miricanvas.template_type_link h
         ON h.template_idx=a.tpl AND h.template_type_id=a.stt
  LEFT JOIN fb ON fb.template_idx=a.tpl),
ag AS (SELECT COALESCE(lab.cat,'(미매핑)') cat, COALESCE(lab.typ,'(미매핑)') typ,
              count(*) acts, count(DISTINCT ra.user_id) users
       FROM ra LEFT JOIN lab ON lab.tid=ra.tid GROUP BY 1,2),
vv AS (
  SELECT search_template_type stt, explode(split(view_item_key,',')) k
  FROM silver.miricanvas_searching.view_template
  WHERE event_date BETWEEN '2026-08-11' AND '2026-09-10' AND country='South Korea' AND page_type='에디터'
    AND view_load_num='1' AND split(view_item_type,',')[0]='TEMPLATE'
    AND nullif(view_item_key,'') IS NOT NULL),
rv AS (
  SELECT COALESCE(h.template_type_id, fb.template_type_id) tid
  FROM (SELECT stt, try_cast(trim(k) AS BIGINT) tpl FROM vv) v
  LEFT JOIN bronze.miridih_miricanvas.template_type_link h
         ON h.template_idx=v.tpl AND h.template_type_id=v.stt
  LEFT JOIN fb ON fb.template_idx=v.tpl),
vg AS (SELECT COALESCE(lab.cat,'(미매핑)') cat, COALESCE(lab.typ,'(미매핑)') typ, count(*) views
       FROM rv LEFT JOIN lab ON lab.tid=rv.tid GROUP BY 1,2)
SELECT ag.cat, ag.typ, ag.acts, ag.users, COALESCE(vg.views,0) views,
       ROUND(100.0*ag.acts/NULLIF(vg.views,0),3) cvr_pct
FROM ag LEFT JOIN vg ON vg.cat=ag.cat AND vg.typ=ag.typ
ORDER BY ag.acts DESC