지식센터 / 레퍼런스

📐 JUVIS UTM 풀퍼널 + 지점블로그 탭 설계 v1.0 (옥경화 상무 요청)

# JUVIS UTM 풀퍼널 + 지점블로그 탭 설계 v1.0

> 작성: 2026-04-20 | DATA_ENG_BQ | Phase: 📋 PLAN (G1 대기)

> 요청: 옥경화 상무 (Asana 1210900334445452)

> 출처: 다츠 광고대행사 요구사항 2건 + 쥬비스 UTM 코딩 가이드 PDF

---

0. 요구사항 요약 (옥경화 상무 2026-04-20)

광고 대행사(다츠) 요구 2건 — 기여모델 미적용

요구 ①: UTM 기준 풀퍼널 분석

• Dimension: UTM Source / Medium / Campaign / Term / Content 5차원
• 퍼널: 유입 → 활성 사용자 → 문의 → 상담 예약 → 결제
• 매출은 퍼널 단계가 아닌 성과 지표로 반영
• utm_term(소재) 상세 영역: 소재명 + 광고 썸네일 노출 또는 원본 링크 연결

요구 ②: 지점 블로그 콘텐츠 성과 탭 (신규)

• 구분 기준: 지점=utm_campaign, 콘텐츠 유형=utm_term, 작성자=utm_content
• 지표: 유입 수 / 활성 사용자 수 / 총 상담 신청 / 결제 수 / 결제 수익
• 조회: 지점별 대시보드 분리 없이 하나의 탭에 필터/뷰로 통합

제약사항

• 기여모델 적용하지 않음 → attribution_model='last' 단일 고정
• 기존 리포트 유지 가능, 신규 탭 2개 추가로 접근

---

1. UTM 코딩 가이드 (쥬비스 제공 PDF 반영)

1-1. 파라미터 설계안

1-2. 지점 코드 매핑 (42개)

서울 (19개)

경기/인천 (10개)

일산 jv_ilsan / 평촌 jv_pyeongchon / 분당 jv_bundang / 송도 jv_songdo / 수원영통 jv_suwon_yt / 수원인계 jv_suwon_ig / 수지구청 jv_suji / 부천 jv_bucheon / 인천 jv_incheon / 의정부 jv_uijeongbu

충청/전라 (7개)

순천 jv_suncheon / 전주 jv_jeonju / 대전둔산 jv_daejeon_ds / 대전유성 jv_daejeon_ys / 광주광천 jv_gwangju_gc / 광주수완 jv_gwangju_sw / 청주 jv_cheongju

경상 (6개)

울산 jv_ulsan / 부산서면 jv_busan_sm / 부산센텀시티 jv_busan_ct / 대구죽전 jv_daegu_jj / 대구범어 jv_daegu_bo / 창원 jv_changwon

1-3. utm_term 5종

1-4. utm_content 규칙

• 형식: {YYMMDD}_{직책}_{이니셜} (예: 260415_manager_cgh)
• 목적: 지점 내 개인별 기여도 측정

---

2. 현황 분석 — 즉시 활용 가능한 인프라

2-1. 테이블 — lift_utm_attribution_v2

결론: 신규 테이블 불필요. 현재 구조에 5개 UTM 차원 + 풀퍼널 지표가 이미 모두 존재.

2-2. 기존 RPC

• ga4_get_utm_attribution(client_code, attribution_model, date_from, date_to) — 현재 기여분석 탭이 이 함수 사용. 반환은 UTM source/medium 단위로 집계된 채널 리스트.

2-3. 갭 분석

---

3. 아키텍처 설계

[데이터 레이어]
  BQ gp-juvis-107339 mart_tables (원본)
    ↓ FDW
  Supabase.lift_utm_attribution_v2 (기존, 재사용)
    ├─ attribution_model='last' 단일 사용

[RPC 레이어] — 신규 2개
  ga4_get_utm_fullfunnel(client_code, date_from, date_to, dims[])
    → dims별 그룹핑으로 풀퍼널 6개 집계 반환
    → dims = ['utm_source','utm_medium','utm_campaign','utm_term','utm_content']
       중 요청한 조합으로 GROUP BY
  
  ga4_get_branch_blog_perf(client_code, date_from, date_to, branch?, term?, author?)
    → utm_medium='branch_post' 필터
    → 지점(campaign) × 콘텐츠유형(term) × 작성자(content) 3축 드릴다운
    → 5개 지표 반환 (landing, active_user, g6, odr_count, order_amount)

  ga4_get_creative_meta(client_code) — 기존 재사용 (썸네일용)

[프론트 레이어] — 신규 탭 2개
  ┌── 원천 데이터 탐색 (기존 그룹)
  │   ├── 일별 KPI 트렌드 (기존)
  │   ├── 전환 퍼널 분석 (기존)
  │   ├── 매체별 성과 (기존)
  │   ├── 데이터 정합성 비교 (기존)
  │   ├── UTM 풀퍼널 탐색 ⭐ NEW
  │   └── 지점 블로그 성과 ⭐ NEW
  
  각 탭은 AiInsightSection 불포함 (기여모델 미적용이므로 AI 인사이트 불필요)

---

4. 신규 탭 ① UTM 풀퍼널 탐색

4-1. UX 와이어프레임

┌─────────────────────────────────────────────────────────────┐
│ UTM 풀퍼널 탐색                          기간: [2026-04-01 ~ 04-20] │
├─────────────────────────────────────────────────────────────┤
│ 드릴다운 축 (다중선택):                                      │
│ [✓ Source] [✓ Medium] [  Campaign] [  Term] [  Content]    │
│                                                              │
│ 퍼널 요약 바 (전체)                                          │
│  유입(54,904) ──▶ 활성사용자(48,231) ──▶ 문의(1,344) ──▶      │
│  상담예약(198) ──▶ 결제(79)    매출: ₩441,494,700           │
│                                                              │
│ 매체 × 퍼널 히트맵                                           │
│ source/medium      유입      활성    문의    상담예약  결제  매출 │
│ ─────────────────────────────────────────────────────────── │
│ (not set)          131,936   54,904  989     43      79   ₩441M │
│ Meta / feed         …                                       │
│ Naver / BSA_MO      …                                       │
│                                                              │
│ [🔽 Naver/BSA_MO 행 클릭 → Campaign 드릴다운]                │
│                                                              │
│ Term 상세 (소재 썸네일 포함)                                 │
│ ┌───────────┬─────────────┬──────┬──────┬──────┐           │
│ │ [썸네일]  │ utm_term     │ 유입  │ 문의  │ 결제 │           │
│ │ 📷100x100 │ tiktok_tcpa  │ 12,070│ 2     │ 2    │           │
│ └───────────┴─────────────┴──────┴──────┴──────┘           │
└─────────────────────────────────────────────────────────────┘

4-2. 기능 요구

• 축 다중 선택: Source, Medium, Campaign, Term, Content 중 1~5개 선택. 선택 조합으로 GROUP BY
• 기본 선택: Source + Medium (기존 기여분석과 동일)
• 지표 6개 고정: landing / active_user / g6 / g7 / odr_count / order_amount
• 소재 썸네일: utm_term 축 선택 시 자동으로 ga4_creative_meta 조인 → 썸네일 표시
• 드릴다운: 행 클릭 시 해당 값 고정 후 다음 축 추가
• 정렬: 모든 숫자 컬럼 클릭 정렬
• CSV 다운로드: 현재 필터된 데이터 내보내기

4-3. RPC 설계

CREATE OR REPLACE FUNCTION ga4_get_utm_fullfunnel(
  p_client_code varchar,
  p_date_from date,
  p_date_to date,
  p_dims text[] DEFAULT ARRAY['utm_source','utm_medium']
)
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  v_result jsonb;
  v_dim_cols text;
  v_group_cols text;
BEGIN
  -- 허용 dims만 필터링 (SQL injection 방어)
  SELECT string_agg(quote_ident(d), ', '), string_agg(quote_ident(d), ', ')
  INTO v_dim_cols, v_group_cols
  FROM unnest(p_dims) d
  WHERE d IN ('utm_source','utm_medium','utm_campaign','utm_term','utm_content');
  
  -- 동적 SQL로 GROUP BY 실행
  EXECUTE format($sql$
    SELECT jsonb_build_object(
      'dims', %L::jsonb,
      'date_range', jsonb_build_object('from', %L::text, 'to', %L::text),
      'client_code', %L,
      'totals', jsonb_build_object(
        'landing', COALESCE(SUM(landing), 0),
        'active_user', COALESCE(SUM(active_user), 0),
        'g6', COALESCE(SUM(g6), 0),
        'g7', COALESCE(SUM(g7), 0),
        'odr_count', COALESCE(SUM(odr_count), 0),
        'order_amount', COALESCE(SUM(order_amount), 0)
      ),
      'rows', COALESCE(jsonb_agg(row_data ORDER BY landing_sum DESC), '[]'::jsonb)
    )
    FROM (
      SELECT 
        jsonb_build_object(
          %s,
          'landing', SUM(landing),
          'active_user', SUM(active_user),
          'g6', SUM(g6),
          'g7', SUM(g7),
          'odr_count', SUM(odr_count),
          'order_amount', SUM(order_amount)
        ) AS row_data,
        SUM(landing) AS landing_sum
      FROM lift_utm_attribution_v2
      WHERE client_code = %L
        AND stat_date BETWEEN %L AND %L
        AND attribution_model = 'last'
      GROUP BY %s
    ) t
  $sql$,
    to_jsonb(p_dims), p_date_from, p_date_to, p_client_code,
    -- dim key 페어 생성
    (SELECT string_agg(quote_literal(d)||', '||quote_ident(d), ', ')
     FROM unnest(p_dims) d
     WHERE d IN ('utm_source','utm_medium','utm_campaign','utm_term','utm_content')),
    p_client_code, p_date_from, p_date_to, v_group_cols
  ) INTO v_result;
  
  RETURN v_result;
END;
$$;

핵심 안전장치:

• 허용된 dim 이름만 whitelist (SQL injection 방어)
• 동적 SQL에 리터럴 값 주입 (format + quote_literal/ident)
• SECURITY DEFINER (FDW RPC 3초 제한 회피)

4-4. 신규 파일: UtmFullFunnelTab.tsx

---

5. 신규 탭 ② 지점 블로그 콘텐츠 성과

5-1. UX 와이어프레임

┌─────────────────────────────────────────────────────────────┐
│ 지점 블로그 콘텐츠 성과          기간: [2026-04-01 ~ 04-20] │
├─────────────────────────────────────────────────────────────┤
│ 필터                                                         │
│ 지점: [전체 42개 ▼] 콘텐츠유형: [전체 ▼] 작성자: [전체 ▼]    │
│                                                              │
│ 전체 요약                                                    │
│ ┌──────┬──────┬──────┬──────┬────────┐                    │
│ │ 유입  │활성사용자│총상담신청│ 결제  │ 결제수익 │                    │
│ │ 3,241 │ 2,876 │ 47    │ 12   │ ₩68M   │                    │
│ └──────┴──────┴──────┴──────┴────────┘                    │
│                                                              │
│ [지점별] 탭 │ [콘텐츠유형별] 탭 │ [작성자별] 탭 │ [상세] 탭  │
│ ─────────────────────────────────────────────────────────── │
│ 지점 (campaign)   유입  활성  문의  결제  매출  CVR          │
│ ─────────────────────────────────────────────────────────── │
│ 강남점            812   721   14    4     ₩22M  1.7%        │
│ 분당점            543   489   9     3     ₩16M  1.7%        │
│ …                                                            │
│                                                              │
│ [전체 테이블 CSV 다운로드]                                   │
└─────────────────────────────────────────────────────────────┘

5-2. 기능 요구

• 자동 필터: utm_medium = 'branch_post' 항상 고정 (일반 광고 제외)
• 3축 필터: 지점(campaign) / 콘텐츠유형(term) / 작성자(content) 각각 드롭다운

- 지점 드롭다운: 지점코드+한글명 매핑 테이블로 표시 ("jv_gangnam → 강남점")

- 콘텐츠유형 드롭다운: success/info/program/event/daily 5종

- 작성자 드롭다운: DB에서 distinct utm_content 추출

• 4개 서브탭:

1. 지점별: campaign으로 그룹핑, 전 지표 표시

2. 콘텐츠유형별: term으로 그룹핑

3. 작성자별: content로 그룹핑

4. 상세(교차): campaign × term × content 전체 교차

• 5개 지표: 유입 / 활성사용자 / 총상담신청(g6) / 결제(odr_count) / 결제수익(order_amount)
• 파생 지표: CVR (결제/유입 %)
• 보조 테이블: 지점 코드→한글명 매핑 (lift_juvis_branch_mapping 신규)

5-3. RPC 설계

CREATE OR REPLACE FUNCTION ga4_get_branch_blog_perf(
  p_client_code varchar,
  p_date_from date,
  p_date_to date,
  p_group_by text DEFAULT 'campaign',   -- campaign | term | content | all
  p_branch text DEFAULT NULL,           -- 필터: utm_campaign 값
  p_term text DEFAULT NULL,             -- 필터: utm_term 값
  p_author text DEFAULT NULL            -- 필터: utm_content 값
)
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  v_result jsonb;
  v_group_col text;
BEGIN
  -- 그룹핑 컬럼 결정 (whitelist)
  v_group_col := CASE p_group_by
    WHEN 'campaign' THEN 'utm_campaign'
    WHEN 'term' THEN 'utm_term'
    WHEN 'content' THEN 'utm_content'
    WHEN 'all' THEN 'utm_campaign, utm_term, utm_content'
    ELSE 'utm_campaign'
  END;
  
  EXECUTE format($sql$
    WITH base AS (
      SELECT utm_campaign, utm_term, utm_content,
             landing, active_user, g6, odr_count, order_amount
      FROM lift_utm_attribution_v2
      WHERE client_code = %L
        AND stat_date BETWEEN %L AND %L
        AND attribution_model = 'last'
        AND utm_medium = 'branch_post'
        AND (%L IS NULL OR utm_campaign = %L)
        AND (%L IS NULL OR utm_term = %L)
        AND (%L IS NULL OR utm_content = %L)
    )
    SELECT jsonb_build_object(
      'totals', jsonb_build_object(
        'landing', COALESCE(SUM(landing), 0),
        'active_user', COALESCE(SUM(active_user), 0),
        'g6', COALESCE(SUM(g6), 0),
        'odr_count', COALESCE(SUM(odr_count), 0),
        'order_amount', COALESCE(SUM(order_amount), 0)
      ),
      'rows', (
        SELECT COALESCE(jsonb_agg(
          jsonb_build_object(
            %s,
            'landing', landing_sum,
            'active_user', active_user_sum,
            'g6', g6_sum,
            'odr_count', odr_count_sum,
            'order_amount', order_amount_sum,
            'cvr', CASE WHEN landing_sum > 0 
              THEN ROUND((odr_count_sum::numeric / landing_sum) * 100, 2) 
              ELSE 0 END
          ) ORDER BY landing_sum DESC
        ), '[]'::jsonb)
        FROM (
          SELECT %s,
                 SUM(landing) AS landing_sum,
                 SUM(active_user) AS active_user_sum,
                 SUM(g6) AS g6_sum,
                 SUM(odr_count) AS odr_count_sum,
                 SUM(order_amount) AS order_amount_sum
          FROM base
          GROUP BY %s
        ) g
      )
    )
    FROM base
  $sql$,
    p_client_code, p_date_from, p_date_to,
    p_branch, p_branch, p_term, p_term, p_author, p_author,
    -- jsonb 키-값 페어
    '...',  -- group_by에 따라 동적 생성
    v_group_col, v_group_col
  ) INTO v_result;
  
  RETURN v_result;
END;
$$;

5-4. 보조 테이블: lift_juvis_branch_mapping

CREATE TABLE lift_juvis_branch_mapping (
  branch_code varchar(50) PRIMARY KEY,       -- 'jv_gangnam'
  branch_name varchar(50) NOT NULL,          -- '강남점'
  region varchar(20) NOT NULL,               -- '서울'|'경기/인천'|'충청/전라'|'경상'
  is_active boolean DEFAULT true,
  created_at timestamptz DEFAULT now()
);

-- 42개 지점 INSERT (UTM 코딩 가이드 기준)

5-5. 신규 파일: BranchBlogTab.tsx

---

6. 프론트 라우팅

6-1. 메뉴 트리 확장

원천 데이터 탐색 (기존 그룹, 펼침)
  ├── 일별 KPI 트렌드
  ├── 전환 퍼널 분석
  ├── 매체별 성과
  ├── 데이터 정합성 비교
  ├── UTM 풀퍼널 탐색         ⭐ NEW
  └── 지점 블로그 성과        ⭐ NEW (JUVIS에서만 표시)

6-2. 테넌트 조건부 표시

• "지점 블로그 성과" 탭은 lift_tenant_config_v2.display_metrics에 새 필드 show_branch_blog: true 추가하여 JUVIS만 노출
• "UTM 풀퍼널 탐색" 탭은 모든 테넌트 노출 (UTM은 보편적)

---

7. 작업 산정

---

8. 리스크 & 검토사항

🚨 데이터 존재성 확인 필요

• 현재 lift_utm_attribution_v2에 utm_medium='branch_post' 레코드가 0건일 가능성 — 다츠가 블로그 UTM 태깅을 아직 시작 안 했으면 탭은 비어있게 됨
• → G1 승인 전 즉시 SQL로 체크 필요

⚠️ 썸네일 매핑 범위

• ga4_get_creative_meta RPC는 현재 CreativePathsTab에서 utm_term 기준 썸네일 반환 (소재명 → 썸네일 URL 매핑)
• 블로그 콘텐츠의 경우 썸네일은 없고 블로그 원본 URL 연결만 가능 → 다츠에 확인 필요

⚠️ 지점 코드 표준화

• PDF는 42개 지점이라고 했으나 리스트 열거는 서울 19 + 경기/인천 10 + 충청/전라 7 + 경상 6 = 42개 맞음
• 다만 "여의도점"이 수도권 리스트에 2번 등장 (오타로 추정) — 확인 필요
• 지점 추가/폐점 시 mapping 테이블만 수정하면 반영됨

⚠️ JUVIS 블로그 마케팅 확산 일정

• UTM 코딩 가이드가 지점장/마케팅 담당자에게 배포 전이면, 우선 대시보드 구축 후 점진적으로 데이터 쌓임
• 초기에 "아직 데이터 없음" 빈 화면 보일 수 있음 → 친절한 empty state 메시지 필요

---

9. G1 결정 필요 사항

찰스 승인 필요 항목 4가지:

Q1. 데이터 존재성 확인

현재 branch_post medium 데이터가 있는지 먼저 확인하고, 0건이면 다츠에 "태깅 가이드 배포 후 대시보드 오픈" 순서로 할까요, 아니면 UI 먼저 만들까요?

• (A) 데이터 확인 후 진행 여부 결정
• (B) UI 선개발 (empty state 포함)

Q2. 썸네일 처리

블로그 콘텐츠는 썸네일이 없는 경우가 많은데:

• (A) 블로그 원본 URL 링크만 제공 (썸네일 없음)
• (B) 네이버/티스토리 블로그 메타태그(og:image) 크롤링해서 썸네일 확보 (추가 공수 1일)
• (C) 우선 A로 시작, 필요 시 B로 확장

Q3. 구현 순서

• (A) 탭 ① UTM 풀퍼널 먼저 → ② 지점 블로그 순차
• (B) 탭 ② 지점 블로그 먼저 (옥 상무 요청 핵심)
• (C) 병렬 진행

Q4. 기존 "매체별 성과" 탭과의 관계

"원천 데이터 탐색 > 매체별 성과" 탭과 신규 "UTM 풀퍼널 탐색"이 겹칠 수 있음.

• (A) 둘 다 유지 (역할 분리: 매체별=전체 성과 요약, 풀퍼널=드릴다운 탐색)
• (B) 매체별 성과를 UTM 풀퍼널로 흡수
• (C) 매체별을 유지하고 풀퍼널에 "매체별 뷰" 서브탭 추가

추천안: Q1=A(데이터 먼저 확인), Q2=C(점진적), Q3=C(병렬), Q4=A(역할 분리)

---

10. 연관 문서

• 쥬비스 UTM 코딩 가이드 PDF (옥경화 상무 첨부)
• Asana 태스크: 1210900334445452
• LIFT 스키마 표준 v1.0: KHub 0ff4d7df
• JUVIS Task DevLog: KHub e365d66c
• 기존 RPC: ga4_get_utm_attribution, ga4_get_creative_meta
• 기존 테이블: lift_utm_attribution_v2 (재사용)

관련 문서

이 문서를 참조하는 문서 (역링크)

• 없음

다른 표현: Markdown · JSON · 최종 갱신 2026-08-24