{
  "id": "d07f8f43-406a-4932-b8c8-0e36275ea87e",
  "slug": "ref/khub/data-eng-bq-juvis-utm-풀퍼널-지점블로그-탭-설계-v1-0-옥경화-상무-요청",
  "doc_type": "ref",
  "title": "📐 JUVIS UTM 풀퍼널 + 지점블로그 탭 설계 v1.0 (옥경화 상무 요청)",
  "title_en": null,
  "one_liner": "# JUVIS UTM 풀퍼널 + 지점블로그 탭 설계 v1.0 > 작성: 2026-04-20 | DATA_ENG_BQ | Phase: 📋 PLAN (G1 대기) > 요청: 옥경화 상무 (Asana 1210900334445452) > 출처: 다츠 광고대행사 요구사항 2건 + 쥬비스 UT",
  "summary_bullets": [],
  "body_md": "# JUVIS UTM 풀퍼널 + 지점블로그 탭 설계 v1.0\n\n> 작성: 2026-04-20 | DATA_ENG_BQ | Phase: 📋 PLAN (G1 대기)\n> 요청: 옥경화 상무 (Asana 1210900334445452)\n> 출처: 다츠 광고대행사 요구사항 2건 + 쥬비스 UTM 코딩 가이드 PDF\n\n---\n\n## 0. 요구사항 요약 (옥경화 상무 2026-04-20)\n\n**광고 대행사(다츠) 요구 2건 — 기여모델 미적용**\n\n### 요구 ①: UTM 기준 풀퍼널 분석\n- Dimension: **UTM Source / Medium / Campaign / Term / Content** 5차원\n- 퍼널: **유입 → 활성 사용자 → 문의 → 상담 예약 → 결제**\n- 매출은 퍼널 단계가 아닌 성과 지표로 반영\n- `utm_term`(소재) 상세 영역: 소재명 + 광고 **썸네일 노출** 또는 원본 링크 연결\n\n### 요구 ②: 지점 블로그 콘텐츠 성과 탭 (신규)\n- 구분 기준: 지점=`utm_campaign`, 콘텐츠 유형=`utm_term`, 작성자=`utm_content`\n- 지표: 유입 수 / 활성 사용자 수 / 총 상담 신청 / 결제 수 / 결제 수익\n- 조회: 지점별 대시보드 분리 없이 **하나의 탭에 필터/뷰**로 통합\n\n### 제약사항\n- 기여모델 적용하지 않음 → `attribution_model='last'` 단일 고정\n- 기존 리포트 유지 가능, **신규 탭 2개 추가**로 접근\n\n---\n\n## 1. UTM 코딩 가이드 (쥬비스 제공 PDF 반영)\n\n### 1-1. 파라미터 설계안\n\n| 파라미터 | 권장 값 | 정의/활용 |\n|---------|--------|----------|\n| `utm_source` | `blog` | 유입 채널 구분 (네이버 블로그, 티스토리 등) |\n| `utm_medium` | `branch_post` | 영업팀 지점 게시물 명시 (본사 광고와 구분) |\n| `utm_campaign` | `jv_gangnam` 등 | **[핵심]** 각 지점의 고유 영문 코드 |\n| `utm_term` | `success`, `info`, `program`, `event`, `daily` | 콘텐츠 성격 구분 |\n| `utm_content` | `260415_manager_cgh` | 작성일자_직책_이니셜 |\n\n### 1-2. 지점 코드 매핑 (42개)\n\n#### 서울 (19개)\n| 지점 | 코드 | 지점 | 코드 |\n|------|------|------|------|\n| 강남점 | `jv_gangnam` | 마포점 | `jv_mapo` |\n| 송파점 | `jv_songpa` | 강서점 | `jv_gangseo` |\n| 잠실점 | `jv_jamsil` | 시청점 | `jv_sicheong` |\n| 노원점 | `jv_nowon` | 관악점 | `jv_gwanak` |\n| 왕십리점 | `jv_wangsimni` | 성북점 | `jv_seongbuk` |\n| 삼성동점 | `jv_samseong` | 묵동점 | `jv_mukdong` |\n| 목동점 | `jv_mokdong` | 문정점(6그룹) | `jv_munjeong` |\n| 여의도점 | `jv_yeouido` | 도곡점(6그룹) | `jv_dogok` |\n| 성수점 | `jv_seongsu` | — | — |\n| 은평점 | `jv_eunpyeong` | — | — |\n| 서초점 | `jv_seocho` | — | — |\n\n#### 경기/인천 (10개)\n일산 `jv_ilsan` / 평촌 `jv_pyeongchon` / 분당 `jv_bundang` / 송도 `jv_songdo` / 수원영통 `jv_suwon_yt` / 수원인계 `jv_suwon_ig` / 수지구청 `jv_suji` / 부천 `jv_bucheon` / 인천 `jv_incheon` / 의정부 `jv_uijeongbu`\n\n#### 충청/전라 (7개)\n순천 `jv_suncheon` / 전주 `jv_jeonju` / 대전둔산 `jv_daejeon_ds` / 대전유성 `jv_daejeon_ys` / 광주광천 `jv_gwangju_gc` / 광주수완 `jv_gwangju_sw` / 청주 `jv_cheongju`\n\n#### 경상 (6개)\n울산 `jv_ulsan` / 부산서면 `jv_busan_sm` / 부산센텀시티 `jv_busan_ct` / 대구죽전 `jv_daegu_jj` / 대구범어 `jv_daegu_bo` / 창원 `jv_changwon`\n\n### 1-3. utm_term 5종\n\n| 값 | 콘텐츠 테마 | 분석 목적 |\n|----|------------|----------|\n| `success` | 성공 후기 (감량 전/후, 인터뷰) | 신뢰도 기반 결제 유도 효과 분석 |\n| `info` | 정보성 팁 (식단/원리/운동법) | 정보 검색 유입의 상담 전환율 |\n| `program` | 프로그램 소개 (기기/푸드/컨설팅) | 상품별 관심도 직접 측정 |\n| `event` | 이벤트/혜택 (월별 프로모션) | 단기 프로모션 반응 속도 |\n| `daily` | 지점 일상 (매니저 소개/분위기) | 장기 브랜딩의 결제 영향 |\n\n### 1-4. utm_content 규칙\n- 형식: `{YYMMDD}_{직책}_{이니셜}` (예: `260415_manager_cgh`)\n- 목적: 지점 내 개인별 기여도 측정\n\n---\n\n## 2. 현황 분석 — 즉시 활용 가능한 인프라\n\n### 2-1. 테이블 — `lift_utm_attribution_v2`\n**결론: 신규 테이블 불필요.** 현재 구조에 5개 UTM 차원 + 풀퍼널 지표가 이미 모두 존재.\n\n| 컬럼 그룹 | 보유 필드 |\n|----------|----------|\n| UTM 5차원 | `utm_source`, `utm_medium`, `utm_campaign`, `utm_term`, `utm_content` |\n| 퍼널 지표 | `landing`, `active_user`, `g6`(문의), `g7`(상담예약), `odr_count`(결제), `order_amount`(매출) |\n| 부가 | `stat_date`, `attribution_model`, `g1~g10`, `g6_user`, `pageview`, `landing_user/session` |\n| 규모 | 228MB, 기간별 파티션 없음(단순 테이블) |\n\n### 2-2. 기존 RPC\n- `ga4_get_utm_attribution(client_code, attribution_model, date_from, date_to)` — 현재 기여분석 탭이 이 함수 사용. 반환은 UTM source/medium 단위로 집계된 채널 리스트.\n\n### 2-3. 갭 분석\n\n| 요구 | 기존 대응 | 신규 필요 |\n|------|----------|---------|\n| UTM 5차원 드릴다운 | ❌ (source/medium만 반환) | RPC 확장 또는 신규 |\n| 풀퍼널 지표 6개 | ✅ 모든 컬럼 존재 | 그대로 사용 |\n| 소재 썸네일 | ⚠️ `ga4_get_creative_meta` RPC 존재(CreativePathsTab 사용) | 재사용 |\n| 지점(campaign) 필터 | ❌ 프론트 필터 없음 | 프론트 추가 |\n| 작성자(content) 집계 | ❌ | RPC + 프론트 |\n\n---\n\n## 3. 아키텍처 설계\n\n```\n[데이터 레이어]\n  BQ gp-juvis-107339 mart_tables (원본)\n    ↓ FDW\n  Supabase.lift_utm_attribution_v2 (기존, 재사용)\n    ├─ attribution_model='last' 단일 사용\n\n[RPC 레이어] — 신규 2개\n  ga4_get_utm_fullfunnel(client_code, date_from, date_to, dims[])\n    → dims별 그룹핑으로 풀퍼널 6개 집계 반환\n    → dims = ['utm_source','utm_medium','utm_campaign','utm_term','utm_content']\n       중 요청한 조합으로 GROUP BY\n  \n  ga4_get_branch_blog_perf(client_code, date_from, date_to, branch?, term?, author?)\n    → utm_medium='branch_post' 필터\n    → 지점(campaign) × 콘텐츠유형(term) × 작성자(content) 3축 드릴다운\n    → 5개 지표 반환 (landing, active_user, g6, odr_count, order_amount)\n\n  ga4_get_creative_meta(client_code) — 기존 재사용 (썸네일용)\n\n[프론트 레이어] — 신규 탭 2개\n  ┌── 원천 데이터 탐색 (기존 그룹)\n  │   ├── 일별 KPI 트렌드 (기존)\n  │   ├── 전환 퍼널 분석 (기존)\n  │   ├── 매체별 성과 (기존)\n  │   ├── 데이터 정합성 비교 (기존)\n  │   ├── UTM 풀퍼널 탐색 ⭐ NEW\n  │   └── 지점 블로그 성과 ⭐ NEW\n  \n  각 탭은 AiInsightSection 불포함 (기여모델 미적용이므로 AI 인사이트 불필요)\n```\n\n---\n\n## 4. 신규 탭 ① UTM 풀퍼널 탐색\n\n### 4-1. UX 와이어프레임\n\n```\n┌─────────────────────────────────────────────────────────────┐\n│ UTM 풀퍼널 탐색                          기간: [2026-04-01 ~ 04-20] │\n├─────────────────────────────────────────────────────────────┤\n│ 드릴다운 축 (다중선택):                                      │\n│ [✓ Source] [✓ Medium] [  Campaign] [  Term] [  Content]    │\n│                                                              │\n│ 퍼널 요약 바 (전체)                                          │\n│  유입(54,904) ──▶ 활성사용자(48,231) ──▶ 문의(1,344) ──▶      │\n│  상담예약(198) ──▶ 결제(79)    매출: ₩441,494,700           │\n│                                                              │\n│ 매체 × 퍼널 히트맵                                           │\n│ source/medium      유입      활성    문의    상담예약  결제  매출 │\n│ ─────────────────────────────────────────────────────────── │\n│ (not set)          131,936   54,904  989     43      79   ₩441M │\n│ Meta / feed         …                                       │\n│ Naver / BSA_MO      …                                       │\n│                                                              │\n│ [🔽 Naver/BSA_MO 행 클릭 → Campaign 드릴다운]                │\n│                                                              │\n│ Term 상세 (소재 썸네일 포함)                                 │\n│ ┌───────────┬─────────────┬──────┬──────┬──────┐           │\n│ │ [썸네일]  │ utm_term     │ 유입  │ 문의  │ 결제 │           │\n│ │ 📷100x100 │ tiktok_tcpa  │ 12,070│ 2     │ 2    │           │\n│ └───────────┴─────────────┴──────┴──────┴──────┘           │\n└─────────────────────────────────────────────────────────────┘\n```\n\n### 4-2. 기능 요구\n- **축 다중 선택**: Source, Medium, Campaign, Term, Content 중 1~5개 선택. 선택 조합으로 GROUP BY\n- **기본 선택**: Source + Medium (기존 기여분석과 동일)\n- **지표 6개 고정**: landing / active_user / g6 / g7 / odr_count / order_amount\n- **소재 썸네일**: utm_term 축 선택 시 자동으로 ga4_creative_meta 조인 → 썸네일 표시\n- **드릴다운**: 행 클릭 시 해당 값 고정 후 다음 축 추가\n- **정렬**: 모든 숫자 컬럼 클릭 정렬\n- **CSV 다운로드**: 현재 필터된 데이터 내보내기\n\n### 4-3. RPC 설계\n\n```sql\nCREATE OR REPLACE FUNCTION ga4_get_utm_fullfunnel(\n  p_client_code varchar,\n  p_date_from date,\n  p_date_to date,\n  p_dims text[] DEFAULT ARRAY['utm_source','utm_medium']\n)\nRETURNS jsonb\nLANGUAGE plpgsql\nSECURITY DEFINER\nAS $$\nDECLARE\n  v_result jsonb;\n  v_dim_cols text;\n  v_group_cols text;\nBEGIN\n  -- 허용 dims만 필터링 (SQL injection 방어)\n  SELECT string_agg(quote_ident(d), ', '), string_agg(quote_ident(d), ', ')\n  INTO v_dim_cols, v_group_cols\n  FROM unnest(p_dims) d\n  WHERE d IN ('utm_source','utm_medium','utm_campaign','utm_term','utm_content');\n  \n  -- 동적 SQL로 GROUP BY 실행\n  EXECUTE format($sql$\n    SELECT jsonb_build_object(\n      'dims', %L::jsonb,\n      'date_range', jsonb_build_object('from', %L::text, 'to', %L::text),\n      'client_code', %L,\n      'totals', jsonb_build_object(\n        'landing', COALESCE(SUM(landing), 0),\n        'active_user', COALESCE(SUM(active_user), 0),\n        'g6', COALESCE(SUM(g6), 0),\n        'g7', COALESCE(SUM(g7), 0),\n        'odr_count', COALESCE(SUM(odr_count), 0),\n        'order_amount', COALESCE(SUM(order_amount), 0)\n      ),\n      'rows', COALESCE(jsonb_agg(row_data ORDER BY landing_sum DESC), '[]'::jsonb)\n    )\n    FROM (\n      SELECT \n        jsonb_build_object(\n          %s,\n          'landing', SUM(landing),\n          'active_user', SUM(active_user),\n          'g6', SUM(g6),\n          'g7', SUM(g7),\n          'odr_count', SUM(odr_count),\n          'order_amount', SUM(order_amount)\n        ) AS row_data,\n        SUM(landing) AS landing_sum\n      FROM lift_utm_attribution_v2\n      WHERE client_code = %L\n        AND stat_date BETWEEN %L AND %L\n        AND attribution_model = 'last'\n      GROUP BY %s\n    ) t\n  $sql$,\n    to_jsonb(p_dims), p_date_from, p_date_to, p_client_code,\n    -- dim key 페어 생성\n    (SELECT string_agg(quote_literal(d)||', '||quote_ident(d), ', ')\n     FROM unnest(p_dims) d\n     WHERE d IN ('utm_source','utm_medium','utm_campaign','utm_term','utm_content')),\n    p_client_code, p_date_from, p_date_to, v_group_cols\n  ) INTO v_result;\n  \n  RETURN v_result;\nEND;\n$$;\n```\n\n**핵심 안전장치:**\n- 허용된 dim 이름만 whitelist (SQL injection 방어)\n- 동적 SQL에 리터럴 값 주입 (format + quote_literal/ident)\n- SECURITY DEFINER (FDW RPC 3초 제한 회피)\n\n### 4-4. 신규 파일: `UtmFullFunnelTab.tsx`\n\n---\n\n## 5. 신규 탭 ② 지점 블로그 콘텐츠 성과\n\n### 5-1. UX 와이어프레임\n\n```\n┌─────────────────────────────────────────────────────────────┐\n│ 지점 블로그 콘텐츠 성과          기간: [2026-04-01 ~ 04-20] │\n├─────────────────────────────────────────────────────────────┤\n│ 필터                                                         │\n│ 지점: [전체 42개 ▼] 콘텐츠유형: [전체 ▼] 작성자: [전체 ▼]    │\n│                                                              │\n│ 전체 요약                                                    │\n│ ┌──────┬──────┬──────┬──────┬────────┐                    │\n│ │ 유입  │활성사용자│총상담신청│ 결제  │ 결제수익 │                    │\n│ │ 3,241 │ 2,876 │ 47    │ 12   │ ₩68M   │                    │\n│ └──────┴──────┴──────┴──────┴────────┘                    │\n│                                                              │\n│ [지점별] 탭 │ [콘텐츠유형별] 탭 │ [작성자별] 탭 │ [상세] 탭  │\n│ ─────────────────────────────────────────────────────────── │\n│ 지점 (campaign)   유입  활성  문의  결제  매출  CVR          │\n│ ─────────────────────────────────────────────────────────── │\n│ 강남점            812   721   14    4     ₩22M  1.7%        │\n│ 분당점            543   489   9     3     ₩16M  1.7%        │\n│ …                                                            │\n│                                                              │\n│ [전체 테이블 CSV 다운로드]                                   │\n└─────────────────────────────────────────────────────────────┘\n```\n\n### 5-2. 기능 요구\n- **자동 필터**: `utm_medium = 'branch_post'` 항상 고정 (일반 광고 제외)\n- **3축 필터**: 지점(campaign) / 콘텐츠유형(term) / 작성자(content) 각각 드롭다운\n  - 지점 드롭다운: 지점코드+한글명 매핑 테이블로 표시 (\"jv_gangnam → 강남점\")\n  - 콘텐츠유형 드롭다운: success/info/program/event/daily 5종\n  - 작성자 드롭다운: DB에서 distinct utm_content 추출\n- **4개 서브탭**:\n  1. **지점별**: campaign으로 그룹핑, 전 지표 표시\n  2. **콘텐츠유형별**: term으로 그룹핑\n  3. **작성자별**: content로 그룹핑\n  4. **상세(교차)**: campaign × term × content 전체 교차\n- **5개 지표**: 유입 / 활성사용자 / 총상담신청(g6) / 결제(odr_count) / 결제수익(order_amount)\n- **파생 지표**: CVR (결제/유입 %)\n- **보조 테이블**: 지점 코드→한글명 매핑 (lift_juvis_branch_mapping 신규)\n\n### 5-3. RPC 설계\n\n```sql\nCREATE OR REPLACE FUNCTION ga4_get_branch_blog_perf(\n  p_client_code varchar,\n  p_date_from date,\n  p_date_to date,\n  p_group_by text DEFAULT 'campaign',   -- campaign | term | content | all\n  p_branch text DEFAULT NULL,           -- 필터: utm_campaign 값\n  p_term text DEFAULT NULL,             -- 필터: utm_term 값\n  p_author text DEFAULT NULL            -- 필터: utm_content 값\n)\nRETURNS jsonb\nLANGUAGE plpgsql\nSECURITY DEFINER\nAS $$\nDECLARE\n  v_result jsonb;\n  v_group_col text;\nBEGIN\n  -- 그룹핑 컬럼 결정 (whitelist)\n  v_group_col := CASE p_group_by\n    WHEN 'campaign' THEN 'utm_campaign'\n    WHEN 'term' THEN 'utm_term'\n    WHEN 'content' THEN 'utm_content'\n    WHEN 'all' THEN 'utm_campaign, utm_term, utm_content'\n    ELSE 'utm_campaign'\n  END;\n  \n  EXECUTE format($sql$\n    WITH base AS (\n      SELECT utm_campaign, utm_term, utm_content,\n             landing, active_user, g6, odr_count, order_amount\n      FROM lift_utm_attribution_v2\n      WHERE client_code = %L\n        AND stat_date BETWEEN %L AND %L\n        AND attribution_model = 'last'\n        AND utm_medium = 'branch_post'\n        AND (%L IS NULL OR utm_campaign = %L)\n        AND (%L IS NULL OR utm_term = %L)\n        AND (%L IS NULL OR utm_content = %L)\n    )\n    SELECT jsonb_build_object(\n      'totals', jsonb_build_object(\n        'landing', COALESCE(SUM(landing), 0),\n        'active_user', COALESCE(SUM(active_user), 0),\n        'g6', COALESCE(SUM(g6), 0),\n        'odr_count', COALESCE(SUM(odr_count), 0),\n        'order_amount', COALESCE(SUM(order_amount), 0)\n      ),\n      'rows', (\n        SELECT COALESCE(jsonb_agg(\n          jsonb_build_object(\n            %s,\n            'landing', landing_sum,\n            'active_user', active_user_sum,\n            'g6', g6_sum,\n            'odr_count', odr_count_sum,\n            'order_amount', order_amount_sum,\n            'cvr', CASE WHEN landing_sum > 0 \n              THEN ROUND((odr_count_sum::numeric / landing_sum) * 100, 2) \n              ELSE 0 END\n          ) ORDER BY landing_sum DESC\n        ), '[]'::jsonb)\n        FROM (\n          SELECT %s,\n                 SUM(landing) AS landing_sum,\n                 SUM(active_user) AS active_user_sum,\n                 SUM(g6) AS g6_sum,\n                 SUM(odr_count) AS odr_count_sum,\n                 SUM(order_amount) AS order_amount_sum\n          FROM base\n          GROUP BY %s\n        ) g\n      )\n    )\n    FROM base\n  $sql$,\n    p_client_code, p_date_from, p_date_to,\n    p_branch, p_branch, p_term, p_term, p_author, p_author,\n    -- jsonb 키-값 페어\n    '...',  -- group_by에 따라 동적 생성\n    v_group_col, v_group_col\n  ) INTO v_result;\n  \n  RETURN v_result;\nEND;\n$$;\n```\n\n### 5-4. 보조 테이블: `lift_juvis_branch_mapping`\n\n```sql\nCREATE TABLE lift_juvis_branch_mapping (\n  branch_code varchar(50) PRIMARY KEY,       -- 'jv_gangnam'\n  branch_name varchar(50) NOT NULL,          -- '강남점'\n  region varchar(20) NOT NULL,               -- '서울'|'경기/인천'|'충청/전라'|'경상'\n  is_active boolean DEFAULT true,\n  created_at timestamptz DEFAULT now()\n);\n\n-- 42개 지점 INSERT (UTM 코딩 가이드 기준)\n```\n\n### 5-5. 신규 파일: `BranchBlogTab.tsx`\n\n---\n\n## 6. 프론트 라우팅\n\n### 6-1. 메뉴 트리 확장\n\n```\n원천 데이터 탐색 (기존 그룹, 펼침)\n  ├── 일별 KPI 트렌드\n  ├── 전환 퍼널 분석\n  ├── 매체별 성과\n  ├── 데이터 정합성 비교\n  ├── UTM 풀퍼널 탐색         ⭐ NEW\n  └── 지점 블로그 성과        ⭐ NEW (JUVIS에서만 표시)\n```\n\n### 6-2. 테넌트 조건부 표시\n- \"지점 블로그 성과\" 탭은 `lift_tenant_config_v2.display_metrics`에 새 필드 `show_branch_blog: true` 추가하여 JUVIS만 노출\n- \"UTM 풀퍼널 탐색\" 탭은 모든 테넌트 노출 (UTM은 보편적)\n\n---\n\n## 7. 작업 산정\n\n| 작업 | 담당 | 공수 |\n|------|------|------|\n| ① BranchMapping 테이블 생성 + 42개 INSERT | 찰스 | 0.5일 |\n| ② `ga4_get_utm_fullfunnel` RPC 개발+테스트 | 찰스 | 1일 |\n| ③ `ga4_get_branch_blog_perf` RPC 개발+테스트 | 찰스 | 1일 |\n| ④ `UtmFullFunnelTab.tsx` 프론트 (Lovable) | Claude | 1일 |\n| ⑤ `BranchBlogTab.tsx` 프론트 (Lovable) | Claude | 1일 |\n| ⑥ 메뉴 라우팅 + config `show_branch_blog` | Claude | 0.3일 |\n| ⑦ UTM 코딩 가이드 Asana 첨부 + 다츠 공유 | 옥경화/찰스 | 0.2일 |\n| ⑧ 검증 (브라우저, 데이터 정합성) | 찰스 | 0.5일 |\n| **합계** | | **약 5.5일(MD)** |\n\n---\n\n## 8. 리스크 & 검토사항\n\n### 🚨 데이터 존재성 확인 필요\n- 현재 `lift_utm_attribution_v2`에 **`utm_medium='branch_post'` 레코드가 0건일 가능성** — 다츠가 블로그 UTM 태깅을 아직 시작 안 했으면 탭은 비어있게 됨\n- → G1 승인 전 즉시 SQL로 체크 필요\n\n### ⚠️ 썸네일 매핑 범위\n- `ga4_get_creative_meta` RPC는 현재 CreativePathsTab에서 utm_term 기준 썸네일 반환 (소재명 → 썸네일 URL 매핑)\n- 블로그 콘텐츠의 경우 썸네일은 없고 **블로그 원본 URL 연결만 가능** → 다츠에 확인 필요\n\n### ⚠️ 지점 코드 표준화\n- PDF는 42개 지점이라고 했으나 리스트 열거는 서울 19 + 경기/인천 10 + 충청/전라 7 + 경상 6 = **42개 맞음**\n- 다만 \"여의도점\"이 수도권 리스트에 2번 등장 (오타로 추정) — 확인 필요\n- 지점 추가/폐점 시 mapping 테이블만 수정하면 반영됨\n\n### ⚠️ JUVIS 블로그 마케팅 확산 일정\n- UTM 코딩 가이드가 지점장/마케팅 담당자에게 배포 전이면, 우선 대시보드 구축 후 점진적으로 데이터 쌓임\n- 초기에 \"아직 데이터 없음\" 빈 화면 보일 수 있음 → 친절한 empty state 메시지 필요\n\n---\n\n## 9. G1 결정 필요 사항\n\n찰스 승인 필요 항목 4가지:\n\n**Q1. 데이터 존재성 확인**\n현재 `branch_post` medium 데이터가 있는지 먼저 확인하고, 0건이면 다츠에 \"태깅 가이드 배포 후 대시보드 오픈\" 순서로 할까요, 아니면 UI 먼저 만들까요?\n- (A) 데이터 확인 후 진행 여부 결정\n- (B) UI 선개발 (empty state 포함)\n\n**Q2. 썸네일 처리**\n블로그 콘텐츠는 썸네일이 없는 경우가 많은데:\n- (A) 블로그 원본 URL 링크만 제공 (썸네일 없음)\n- (B) 네이버/티스토리 블로그 메타태그(og:image) 크롤링해서 썸네일 확보 (추가 공수 1일)\n- (C) 우선 A로 시작, 필요 시 B로 확장\n\n**Q3. 구현 순서**\n- (A) 탭 ① UTM 풀퍼널 먼저 → ② 지점 블로그 순차\n- (B) 탭 ② 지점 블로그 먼저 (옥 상무 요청 핵심)\n- (C) 병렬 진행\n\n**Q4. 기존 \"매체별 성과\" 탭과의 관계**\n\"원천 데이터 탐색 > 매체별 성과\" 탭과 신규 \"UTM 풀퍼널 탐색\"이 겹칠 수 있음. \n- (A) 둘 다 유지 (역할 분리: 매체별=전체 성과 요약, 풀퍼널=드릴다운 탐색)\n- (B) 매체별 성과를 UTM 풀퍼널로 흡수\n- (C) 매체별을 유지하고 풀퍼널에 \"매체별 뷰\" 서브탭 추가\n\n**추천안**: Q1=A(데이터 먼저 확인), Q2=C(점진적), Q3=C(병렬), Q4=A(역할 분리)\n\n---\n\n## 10. 연관 문서\n\n- 쥬비스 UTM 코딩 가이드 PDF (옥경화 상무 첨부)\n- Asana 태스크: `1210900334445452`\n- LIFT 스키마 표준 v1.0: KHub `0ff4d7df`\n- JUVIS Task DevLog: KHub `e365d66c`\n- 기존 RPC: `ga4_get_utm_attribution`, `ga4_get_creative_meta`\n- 기존 테이블: `lift_utm_attribution_v2` (재사용)\n",
  "related_slugs": [],
  "faq": [],
  "jsonld": null,
  "keywords": [],
  "source_id": "a3e10b80-b747-4dc0-bd1b-0a19ea204145",
  "video_url": null,
  "duration_sec": null,
  "thumbnail_url": null,
  "image_url": null,
  "caption": null,
  "solution_slug": null,
  "captured_at": null,
  "width": null,
  "height": null,
  "created_at": "2026-08-24T11:53:44.684733+00:00",
  "updated_at": "2026-08-24T11:53:44.684733+00:00",
  "visibility": "public",
  "source_stage": "1",
  "anonymized": true,
  "source_key": "legacy-khub",
  "origin": "ingest",
  "locked": false,
  "locked_by": null,
  "locked_at": null,
  "gate_state": {
    "g1": {
      "hits": [],
      "pass": true
    },
    "g2": {
      "pass": true,
      "total": 57,
      "issues": [
        "숫자·규격 같은 검증 가능한 구체가 부족합니다",
        "핵심 요약 불릿이 3개 미만입니다",
        "Q&A 쌍이 3개 미만입니다"
      ],
      "applies": true
    },
    "g3": {
      "pass": true,
      "required": false
    },
    "stage": "1",
    "decided_at": "2026-08-24T11:53:44.334Z"
  },
  "raw_id": "6def94e3-df3b-4b40-bf72-20b924463703",
  "search_text": "📐 JUVIS UTM 풀퍼널 + 지점블로그 탭 설계 v1.0 (옥경화 상무 요청)  # JUVIS UTM 풀퍼널 + 지점블로그 탭 설계 v1.0 > 작성: 2026-04-20 | DATA_ENG_BQ | Phase: 📋 PLAN (G1 대기) > 요청: 옥경화 상무 (Asana 1210900334445452) > 출처: 다츠 광고대행사 요구사항 2건 + 쥬비스 UT  # JUVIS UTM 풀퍼널 + 지점블로그 탭 설계 v1.0\n\n> 작성: 2026-04-20 | DATA_ENG_BQ | Phase: 📋 PLAN (G1 대기)\n> 요청: 옥경화 상무 (Asana 1210900334445452)\n> 출처: 다츠 광고대행사 요구사항 2건 + 쥬비스 UTM 코딩 가이드 PDF\n\n---\n\n## 0. 요구사항 요약 (옥경화 상무 2026-04-20)\n\n**광고 대행사(다츠) 요구 2건 — 기여모델 미적용**\n\n### 요구 ①: UTM 기준 풀퍼널 분석\n- Dimension: **UTM Source / Medium / Campaign / Term / Content** 5차원\n- 퍼널: **유입 → 활성 사용자 → 문의 → 상담 예약 → 결제**\n- 매출은 퍼널 단계가 아닌 성과 지표로 반영\n- `utm_term`(소재) 상세 영역: 소재명 + 광고 **썸네일 노출** 또는 원본 링크 연결\n\n### 요구 ②: 지점 블로그 콘텐츠 성과 탭 (신규)\n- 구분 기준: 지점=`utm_campaign`, 콘텐츠 유형=`utm_term`, 작성자=`utm_content`\n- 지표: 유입 수 / 활성 사용자 수 / 총 상담 신청 / 결제 수 / 결제 수익\n- 조회: 지점별 대시보드 분리 없이 **하나의 탭에 필터/뷰**로 통합\n\n### 제약사항\n- 기여모델 적용하지 않음 → `attribution_model='last'` 단일 고정\n- 기존 리포트 유지 가능, **신규 탭 2개 추가**로 접근\n\n---\n\n## 1. UTM 코딩 가이드 (쥬비스 제공 PDF 반영)\n\n### 1-1. 파라미터 설계안\n\n| 파라미터 | 권장 값 | 정의/활용 |\n|---------|--------|----------|\n| `utm_source` | `blog` | 유입 채널 구분 (네이버 블로그, 티스토리 등) |\n| `utm_medium` | `branch_post` | 영업팀 지점 게시물 명시 (본사 광고와 구분) |\n| `utm_campaign` | `jv_gangnam` 등 | **[핵심]** 각 지점의 고유 영문 코드 |\n| `utm_term` | `success`, `info`, `program`, `event`, `daily` | 콘텐츠 성격 구분 |\n| `utm_content` | `260415_manager_cgh` | 작성일자_직책_이니셜 |\n\n### 1-2. 지점 코드 매핑 (42개)\n\n#### 서울 (19개)\n| 지점 | 코드 | 지점 | 코드 |\n|------|------|------|------|\n| 강남점 | `jv_gangnam` | 마포점 | `jv_mapo` |\n| 송파점 | `jv_songpa` | 강서점 | `jv_gangseo` |\n| 잠실점 | `jv_jamsil` | 시청점 | `jv_sicheong` |\n| 노원점 | `jv_nowon` | 관악점 | `jv_gwanak` |\n| 왕십리점 | `jv_wangsimni` | 성북점 | `jv_seongbuk` |\n| 삼성동점 | `jv_samseong` | 묵동점 | `jv_mukdong` |\n| 목동점 | `jv_mokdong` | 문정점(6그룹) | `jv_munjeong` |\n| 여의도점 | `jv_yeouido` | 도곡점(6그룹) | `jv_dogok` |\n| 성수점 | `jv_seongsu` | — | — |\n| 은평점 | `jv_eunpyeong` | — | — |\n| 서초점 | `jv_seocho` | — | — |\n\n#### 경기/인천 (10개)\n일산 `jv_ilsan` / 평촌 `jv_pyeongchon` / 분당 `jv_bundang` / 송도 `jv_songdo` / 수원영통 `jv_suwon_yt` / 수원인계 `jv_suwon_ig` / 수지구청 `jv_suji` / 부천 `jv_bucheon` / 인천 `jv_incheon` / 의정부 `jv_uijeongbu`\n\n#### 충청/전라 (7개)\n순천 `jv_suncheon` / 전주 `jv_jeonju` / 대전둔산 `jv_daejeon_ds` / 대전유성 `jv_daejeon_ys` / 광주광천 `jv_gwangju_gc` / 광주수완 `jv_gwangju_sw` / 청주 `jv_cheongju`\n\n#### 경상 (6개)\n울산 `jv_ulsan` / 부산서면 `jv_busan_sm` / 부산센텀시티 `jv_busan_ct` / 대구죽전 `jv_daegu_jj` / 대구범어 `jv_daegu_bo` / 창원 `jv_changwon`\n\n### 1-3. utm_term 5종\n\n| 값 | 콘텐츠 테마 | 분석 목적 |\n|----|------------|----------|\n| `success` | 성공 후기 (감량 전/후, 인터뷰) | 신뢰도 기반 결제 유도 효과 분석 |\n| `info` | 정보성 팁 (식단/원리/운동법) | 정보 검색 유입의 상담 전환율 |\n| `program` | 프로그램 소개 (기기/푸드/컨설팅) | 상품별 관심도 직접 측정 |\n| `event` | 이벤트/혜택 (월별 프로모션) | 단기 프로모션 반응 속도 |\n| `daily` | 지점 일상 (매니저 소개/분위기) | 장기 브랜딩의 결제 영향 |\n\n### 1-4. utm_content 규칙\n- 형식: `{YYMMDD}_{직책}_{이니셜}` (예: `260415_manager_cgh`)\n- 목적: 지점 내 개인별 기여도 측정\n\n---\n\n## 2. 현황 분석 — 즉시 활용 가능한 인프라\n\n### 2-1. 테이블 — `lift_utm_attribution_v2`\n**결론: 신규 테이블 불필요.** 현재 구조에 5개 UTM 차원 + 풀퍼널 지표가 이미 모두 존재.\n\n| 컬럼 그룹 | 보유 필드 |\n|----------|----------|\n| UTM 5차원 | `utm_source`, `utm_medium`, `utm_campaign`, `utm_term`, `utm_content` |\n| 퍼널 지표 | `landing`, `active_user`, `g6`(문의), `g7`(상담예약), `odr_count`(결제), `order_amount`(매출) |\n| 부가 | `stat_date`, `attribution_model`, `g1~g10`, `g6_user`, `pageview`, `landing_user/session` |\n| 규모 | 228MB, 기간별 파티션 없음(단순 테이블) |\n\n### 2-2. 기존 RPC\n- `ga4_get_utm_attribution(client_code, attribution_model, date_from, date_to)` — 현재 기여분석 탭이 이 함수 사용. 반환은 UTM source/medium 단위로 집계된 채널 리스트.\n\n### 2-3. 갭 분석\n\n| 요구 | 기존 대응 | 신규 필요 |\n|------|----------|---------|\n| UTM 5차원 드릴다운 | ❌ (source/medium만 반환) | RPC 확장 또는 신규 |\n| 풀퍼널 지표 6개 | ✅ 모든 컬럼 존재 | 그대로 사용 |\n| 소재 썸네일 | ⚠️ `ga4_get_creative_meta` RPC 존재(CreativePathsTab 사용) | 재사용 |\n| 지점(campaign) 필터 | ❌ 프론트 필터 없음 | 프론트 추가 |\n| 작성자(content) 집계 | ❌ | RPC + 프론트 |\n\n---\n\n## 3. 아키텍처 설계\n\n```\n[데이터 레이어]\n  BQ gp-juvis-107339 mart_tables (원본)\n    ↓ FDW\n  Supabase.lift_utm_attribution_v2 (기존, 재사용)\n    ├─ attribution_model='last' 단일 사용\n\n[RPC 레이어] — 신규 2개\n  ga4_get_utm_fullfunnel(client_code, date_from, date_to, dims[])\n    → dims별 그룹핑으로 풀퍼널 6개 집계 반환\n    → dims = ['utm_source','utm_medium','utm_campaign','utm_term','utm_content']\n       중 요청한 조합으로 GROUP BY\n  \n  ga4_get_branch_blog_perf(client_code, date_from, date_to, branch?, term?, author?)\n    → utm_medium='branch_post' 필터\n    → 지점(campaign) × 콘텐츠유형(term) × 작성자(content) 3축 드릴다운\n    → 5개 지표 반환 (landing, active_user, g6, odr_count, order_amount)\n\n  ga4_get_creative_meta(client_code) — 기존 재사용 (썸네일용)\n\n[프론트 레이어] — 신규 탭 2개\n  ┌── 원천 데이터 탐색 (기존 그룹)\n  │   ├── 일별 KPI 트렌드 (기존)\n  │   ├── 전환 퍼널 분석 (기존)\n  │   ├── 매체별 성과 (기존)\n  │   ├── 데이터 정합성 비교 (기존)\n  │   ├── UTM 풀퍼널 탐색 ⭐ NEW\n  │   └── 지점 블로그 성과 ⭐ NEW\n  \n  각 탭은 AiInsightSection 불포함 (기여모델 미적용이므로 AI 인사이트 불필요)\n```\n\n---\n\n## 4. 신규 탭 ① UTM 풀퍼널 탐색\n\n### 4-1. UX 와이어프레임\n\n```\n┌─────────────────────────────────────────────────────────────┐\n│ UTM 풀퍼널 탐색                          기간: [2026-04-01 ~ 04-20] │\n├─────────────────────────────────────────────────────────────┤\n│ 드릴다운 축 (다중선택):                                      │\n│ [✓ Source] [✓ Medium] [  Campaign] [  Term] [  Content]    │\n│                                                              │\n│ 퍼널 요약 바 (전체)                                          │\n│  유입(54,904) ──▶ 활성사용자(48,231) ──▶ 문의(1,344) ──▶      │\n│  상담예약(198) ──▶ 결제(79)    매출: ₩441,494,700           │\n│                                                              │\n│ 매체 × 퍼널 히트맵                                           │\n│ source/medium      유입      활성    문의    상담예약  결제  매출 │\n│ ─────────────────────────────────────────────────────────── │\n│ (not set)          131,936   54,904  989     43      79   ₩441M │\n│ Meta / feed         …                                       │\n│ Naver / BSA_MO      …                                       │\n│                                                              │\n│ [🔽 Naver/BSA_MO 행 클릭 → Campaign 드릴다운]                │\n│                                                              │\n│ Term 상세 (소재 썸네일 포함)                                 │\n│ ┌───────────┬─────────────┬──────┬──────┬──────┐           │\n│ │ [썸네일]  │ utm_term     │ 유입  │ 문의  │ 결제 │           │\n│ │ 📷100x100 │ tiktok_tcpa  │ 12,070│ 2     │ 2    │           │\n│ └───────────┴─────────────┴──────┴──────┴──────┘           │\n└─────────────────────────────────────────────────────────────┘\n```\n\n### 4-2. 기능 요구\n- **축 다중 선택**: Source, Medium, Campaign, Term, Content 중 1~5개 선택. 선택 조합으로 GROUP BY\n- **기본 선택**: Source + Medium (기존 기여분석과 동일)\n- **지표 6개 고정**: landing / active_user / g6 / g7 / odr_count / order_amount\n- **소재 썸네일**: utm_term 축 선택 시 자동으로 ga4_creative_meta 조인 → 썸네일 표시\n- **드릴다운**: 행 클릭 시 해당 값 고정 후 다음 축 추가\n- **정렬**: 모든 숫자 컬럼 클릭 정렬\n- **CSV 다운로드**: 현재 필터된 데이터 내보내기\n\n### 4-3. RPC 설계\n\n```sql\nCREATE OR REPLACE FUNCTION ga4_get_utm_fullfunnel(\n  p_client_code varchar,\n  p_date_from date,\n  p_date_to date,\n  p_dims text[] DEFAULT ARRAY['utm_source','utm_medium']\n)\nRETURNS jsonb\nLANGUAGE plpgsql\nSECURITY DEFINER\nAS $$\nDECLARE\n  v_result jsonb;\n  v_dim_cols text;\n  v_group_cols text;\nBEGIN\n  -- 허용 dims만 필터링 (SQL injection 방어)\n  SELECT string_agg(quote_ident(d), ', '), string_agg(quote_ident(d), ', ')\n  INTO v_dim_cols, v_group_cols\n  FROM unnest(p_dims) d\n  WHERE d IN ('utm_source','utm_medium','utm_campaign','utm_term','utm_content');\n  \n  -- 동적 SQL로 GROUP BY 실행\n  EXECUTE format($sql$\n    SELECT jsonb_build_object(\n      'dims', %L::jsonb,\n      'date_range', jsonb_build_object('from', %L::text, 'to', %L::text),\n      'client_code', %L,\n      'totals', jsonb_build_object(\n        'landing', COALESCE(SUM(landing), 0),\n        'active_user', COALESCE(SUM(active_user), 0),\n        'g6', COALESCE(SUM(g6), 0),\n        'g7', COALESCE(SUM(g7), 0),\n        'odr_count', COALESCE(SUM(odr_count), 0),\n        'order_amount', COALESCE(SUM(order_amount), 0)\n      ),\n      'rows', COALESCE(jsonb_agg(row_data ORDER BY landing_sum DESC), '[]'::jsonb)\n    )\n    FROM (\n      SELECT \n        jsonb_build_object(\n          %s,\n          'landing', SUM(landing),\n          'active_user', SUM(active_user),\n          'g6', SUM(g6),\n          'g7', SUM(g7),\n          'odr_count', SUM(odr_count),\n          'order_amount', SUM(order_amount)\n        ) AS row_data,\n        SUM(landing) AS landing_sum\n      FROM lift_utm_attribution_v2\n      WHERE client_code = %L\n        AND stat_date BETWEEN %L AND %L\n        AND attribution_model = 'last'\n      GROUP BY %s\n    ) t\n  $sql$,\n    to_jsonb(p_dims), p_date_from, p_date_to, p_client_code,\n    -- dim key 페어 생성\n    (SELECT string_agg(quote_literal(d)||', '||quote_ident(d), ', ')\n     FROM unnest(p_dims) d\n     WHERE d IN ('utm_source','utm_medium','utm_campaign','utm_term','utm_content')),\n    p_client_code, p_date_from, p_date_to, v_group_cols\n  ) INTO v_result;\n  \n  RETURN v_result;\nEND;\n$$;\n```\n\n**핵심 안전장치:**\n- 허용된 dim 이름만 whitelist (SQL injection 방어)\n- 동적 SQL에 리터럴 값 주입 (format + quote_literal/ident)\n- SECURITY DEFINER (FDW RPC 3초 제한 회피)\n\n### 4-4. 신규 파일: `UtmFullFunnelTab.tsx`\n\n---\n\n## 5. 신규 탭 ② 지점 블로그 콘텐츠 성과\n\n### 5-1. UX 와이어프레임\n\n```\n┌─────────────────────────────────────────────────────────────┐\n│ 지점 블로그 콘텐츠 성과          기간: [2026-04-01 ~ 04-20] │\n├─────────────────────────────────────────────────────────────┤\n│ 필터                                                         │\n│ 지점: [전체 42개 ▼] 콘텐츠유형: [전체 ▼] 작성자: [전체 ▼]    │\n│                                                              │\n│ 전체 요약                                                    │\n│ ┌──────┬──────┬──────┬──────┬────────┐                    │\n│ │ 유입  │활성사용자│총상담신청│ 결제  │ 결제수익 │                    │\n│ │ 3,241 │ 2,876 │ 47    │ 12   │ ₩68M   │                    │\n│ └──────┴──────┴──────┴──────┴────────┘                    │\n│                                                              │\n│ [지점별] 탭 │ [콘텐츠유형별] 탭 │ [작성자별] 탭 │ [상세] 탭  │\n│ ─────────────────────────────────────────────────────────── │\n│ 지점 (campaign)   유입  활성  문의  결제  매출  CVR          │\n│ ─────────────────────────────────────────────────────────── │\n│ 강남점            812   721   14    4     ₩22M  1.7%        │\n│ 분당점            543   489   9     3     ₩16M  1.7%        │\n│ …                                                            │\n│                                                              │\n│ [전체 테이블 CSV 다운로드]                                   │\n└─────────────────────────────────────────────────────────────┘\n```\n\n### 5-2. 기능 요구\n- **자동 필터**: `utm_medium = 'branch_post'` 항상 고정 (일반 광고 제외)\n- **3축 필터**: 지점(campaign) / 콘텐츠유형(term) / 작성자(content) 각각 드롭다운\n  - 지점 드롭다운: 지점코드+한글명 매핑 테이블로 표시 (\"jv_gangnam → 강남점\")\n  - 콘텐츠유형 드롭다운: success/info/program/event/daily 5종\n  - 작성자 드롭다운: DB에서 distinct utm_content 추출\n- **4개 서브탭**:\n  1. **지점별**: campaign으로 그룹핑, 전 지표 표시\n  2. **콘텐츠유형별**: term으로 그룹핑\n  3. **작성자별**: content로 그룹핑\n  4. **상세(교차)**: campaign × term × content 전체 교차\n- **5개 지표**: 유입 / 활성사용자 / 총상담신청(g6) / 결제(odr_count) / 결제수익(order_amount)\n- **파생 지표**: CVR (결제/유입 %)\n- **보조 테이블**: 지점 코드→한글명 매핑 (lift_juvis_branch_mapping 신규)\n\n### 5-3. RPC 설계\n\n```sql\nCREATE OR REPLACE FUNCTION ga4_get_branch_blog_perf(\n  p_client_code varchar,\n  p_date_from date,\n  p_date_to date,\n  p_group_by text DEFAULT 'campaign',   -- campaign | term | content | all\n  p_branch text DEFAULT NULL,           -- 필터: utm_campaign 값\n  p_term text DEFAULT NULL,             -- 필터: utm_term 값\n  p_author text DEFAULT NULL            -- 필터: utm_content 값\n)\nRETURNS jsonb\nLANGUAGE plpgsql\nSECURITY DEFINER\nAS $$\nDECLARE\n  v_result jsonb;\n  v_group_col text;\nBEGIN\n  -- 그룹핑 컬럼 결정 (whitelist)\n  v_group_col := CASE p_group_by\n    WHEN 'campaign' THEN 'utm_campaign'\n    WHEN 'term' THEN 'utm_term'\n    WHEN 'content' THEN 'utm_content'\n    WHEN 'all' THEN 'utm_campaign, utm_term, utm_content'\n    ELSE 'utm_campaign'\n  END;\n  \n  EXECUTE format($sql$\n    WITH base AS (\n      SELECT utm_campaign, utm_term, utm_content,\n             landing, active_user, g6, odr_count, order_amount\n      FROM lift_utm_attribution_v2\n      WHERE client_code = %L\n        AND stat_date BETWEEN %L AND %L\n        AND attribution_model = 'last'\n        AND utm_medium = 'branch_post'\n        AND (%L IS NULL OR utm_campaign = %L)\n        AND (%L IS NULL OR utm_term = %L)\n        AND (%L IS NULL OR utm_content = %L)\n    )\n    SELECT jsonb_build_object(\n      'totals', jsonb_build_object(\n        'landing', COALESCE(SUM(landing), 0),\n        'active_user', COALESCE(SUM(active_user), 0),\n        'g6', COALESCE(SUM(g6), 0),\n        'odr_count', COALESCE(SUM(odr_count), 0),\n        'order_amount', COALESCE(SUM(order_amount), 0)\n      ),\n      'rows', (\n        SELECT COALESCE(jsonb_agg(\n          jsonb_build_object(\n            %s,\n            'landing', landing_sum,\n            'active_user', active_user_sum,\n            'g6', g6_sum,\n            'odr_count', odr_count_sum,\n            'order_amount', order_amount_sum,\n            'cvr', CASE WHEN landing_sum > 0 \n              THEN ROUND((odr_count_sum::numeric / landing_sum) * 100, 2) \n              ELSE 0 END\n          ) ORDER BY landing_sum DESC\n        ), '[]'::jsonb)\n        FROM (\n          SELECT %s,\n                 SUM(landing) AS landing_sum,\n                 SUM(active_user) AS active_user_sum,\n                 SUM(g6) AS g6_sum,\n                 SUM(odr_count) AS odr_count_sum,\n                 SUM(order_amount) AS order_amount_sum\n          FROM base\n          GROUP BY %s\n        ) g\n      )\n    )\n    FROM base\n  $sql$,\n    p_client_code, p_date_from, p_date_to,\n    p_branch, p_branch, p_term, p_term, p_author, p_author,\n    -- jsonb 키-값 페어\n    '...',  -- group_by에 따라 동적 생성\n    v_group_col, v_group_col\n  ) INTO v_result;\n  \n  RETURN v_result;\nEND;\n$$;\n```\n\n### 5-4. 보조 테이블: `lift_juvis_branch_mapping`\n\n```sql\nCREATE TABLE lift_juvis_branch_mapping (\n  branch_code varchar(50) PRIMARY KEY,       -- 'jv_gangnam'\n  branch_name varchar(50) NOT NULL,          -- '강남점'\n  region varchar(20) NOT NULL,               -- '서울'|'경기/인천'|'충청/전라'|'경상'\n  is_active boolean DEFAULT true,\n  created_at timestamptz DEFAULT now()\n);\n\n-- 42개 지점 INSERT (UTM 코딩 가이드 기준)\n```\n\n### 5-5. 신규 파일: `BranchBlogTab.tsx`\n\n---\n\n## 6. 프론트 라우팅\n\n### 6-1. 메뉴 트리 확장\n\n```\n원천 데이터 탐색 (기존 그룹, 펼침)\n  ├── 일별 KPI 트렌드\n  ├── 전환 퍼널 분석\n  ├── 매체별 성과\n  ├── 데이터 정합성 비교\n  ├── UTM 풀퍼널 탐색         ⭐ NEW\n  └── 지점 블로그 성과        ⭐ NEW (JUVIS에서만 표시)\n```\n\n### 6-2. 테넌트 조건부 표시\n- \"지점 블로그 성과\" 탭은 `lift_tenant_config_v2.display_metrics`에 새 필드 `show_branch_blog: true` 추가하여 JUVIS만 노출\n- \"UTM 풀퍼널 탐색\" 탭은 모든 테넌트 노출 (UTM은 보편적)\n\n---\n\n## 7. 작업 산정\n\n| 작업 | 담당 | 공수 |\n|------|------|------|\n| ① BranchMapping 테이블 생성 + 42개 INSERT | 찰스 | 0.5일 |\n| ② `ga4_get_utm_fullfunnel` RPC 개발+테스트 | 찰스 | 1일 |\n| ③ `ga4_get_branch_blog_perf` RPC 개발+테스트 | 찰스 | 1일 |\n| ④ `UtmFullFunnelTab.tsx` 프론트 (Lovable) | Claude | 1일 |\n| ⑤ `BranchBlogTab.tsx` 프론트 (Lovable) | Claude | 1일 |\n| ⑥ 메뉴 라우팅 + config `show_branch_blog` | Claude | 0.3일 |\n| ⑦ UTM 코딩 가이드 Asana 첨부 + 다츠 공유 | 옥경화/찰스 | 0.2일 |\n| ⑧ 검증 (브라우저, 데이터 정합성) | 찰스 | 0.5일 |\n| **합계** | | **약 5.5일(MD)** |\n\n---\n\n## 8. 리스크 & 검토사항\n\n### 🚨 데이터 존재성 확인 필요\n- 현재 `lift_utm_attribution_v2`에 **`utm_medium='branch_post'` 레코드가 0건일 가능성** — 다츠가 블로그 UTM 태깅을 아직 시작 안 했으면 탭은 비어있게 됨\n- → G1 승인 전 즉시 SQL로 체크 필요\n\n### ⚠️ 썸네일 매핑 범위\n- `ga4_get_creative_meta` RPC는 현재 CreativePathsTab에서 utm_term 기준 썸네일 반환 (소재명 → 썸네일 URL 매핑)\n- 블로그 콘텐츠의 경우 썸네일은 없고 **블로그 원본 URL 연결만 가능** → 다츠에 확인 필요\n\n### ⚠️ 지점 코드 표준화\n- PDF는 42개 지점이라고 했으나 리스트 열거는 서울 19 + 경기/인천 10 + 충청/전라 7 + 경상 6 = **42개 맞음**\n- 다만 \"여의도점\"이 수도권 리스트에 2번 등장 (오타로 추정) — 확인 필요\n- 지점 추가/폐점 시 mapping 테이블만 수정하면 반영됨\n\n### ⚠️ JUVIS 블로그 마케팅 확산 일정\n- UTM 코딩 가이드가 지점장/마케팅 담당자에게 배포 전이면, 우선 대시보드 구축 후 점진적으로 데이터 쌓임\n- 초기에 \"아직 데이터 없음\" 빈 화면 보일 수 있음 → 친절한 empty state 메시지 필요\n\n---\n\n## 9. G1 결정 필요 사항\n\n찰스 승인 필요 항목 4가지:\n\n**Q1. 데이터 존재성 확인**\n현재 `branch_post` medium 데이터가 있는지 먼저 확인하고, 0건이면 다츠에 \"태깅 가이드 배포 후 대시보드 오픈\" 순서로 할까요, 아니면 UI 먼저 만들까요?\n- (A) 데이터 확인 후 진행 여부 결정\n- (B) UI 선개발 (empty state 포함)\n\n**Q2. 썸네일 처리**\n블로그 콘텐츠는 썸네일이 없는 경우가 많은데:\n- (A) 블로그 원본 URL 링크만 제공 (썸네일 없음)\n- (B) 네이버/티스토리 블로그 메타태그(og:image) 크롤링해서 썸네일 확보 (추가 공수 1일)\n- (C) 우선 A로 시작, 필요 시 B로 확장\n\n**Q3. 구현 순서**\n- (A) 탭 ① UTM 풀퍼널 먼저 → ② 지점 블로그 순차\n- (B) 탭 ② 지점 블로그 먼저 (옥 상무 요청 핵심)\n- (C) 병렬 진행\n\n**Q4. 기존 \"매체별 성과\" 탭과의 관계**\n\"원천 데이터 탐색 > 매체별 성과\" 탭과 신규 \"UTM 풀퍼널 탐색\"이 겹칠 수 있음. \n- (A) 둘 다 유지 (역할 분리: 매체별=전체 성과 요약, 풀퍼널=드릴다운 탐색)\n- (B) 매체별 성과를 UTM 풀퍼널로 흡수\n- (C) 매체별을 유지하고 풀퍼널에 \"매체별 뷰\" 서브탭 추가\n\n**추천안**: Q1=A(데이터 먼저 확인), Q2=C(점진적), Q3=C(병렬), Q4=A(역할 분리)\n\n---\n\n## 10. 연관 문서\n\n- 쥬비스 UTM 코딩 가이드 PDF (옥경화 상무 첨부)\n- Asana 태스크: `1210900334445452`\n- LIFT 스키마 표준 v1.0: KHub `0ff4d7df`\n- JUVIS Task DevLog: KHub `e365d66c`\n- 기존 RPC: `ga4_get_utm_attribution`, `ga4_get_creative_meta`\n- 기존 테이블: `lift_utm_attribution_v2` (재사용)\n",
  "embedding": null,
  "embedded_at": null,
  "embedding_hash": null,
  "backlinks": [],
  "html_url": "https://bizspring.ai/kb/ref/khub/data-eng-bq-juvis-utm-풀퍼널-지점블로그-탭-설계-v1-0-옥경화-상무-요청",
  "markdown_url": "https://bizspring.ai/kb/ref/khub/data-eng-bq-juvis-utm-풀퍼널-지점블로그-탭-설계-v1-0-옥경화-상무-요청.md"
}