광고와 매출 그리고 트래픽 데이터를 AI 챗봇으로 통합한 과정

요약

3개 브랜드의 광고, 매출, 트래픽 데이터를 하나의 DB에 자동 수집하고, 팀 누구나 자연어로 질의할 수 있는 챗봇을 만들었다. 월 운영비는 약 8만원이다.

상황

3개 글로벌 브랜드를 운영하는 회사에서 그로스 마케팅 리드를 맡고 있다. 한국 시장 브랜드 2개, 미국 시장 D2C 브랜드 1개. 광고 채널은 Meta, Google Ads, 네이버, 카카오 등이고, 매출은 Shopify, 트래픽은 GA4로 본다.

우리가 기존에 데이터를 보는 방식은:

매일 아침 디지털 팀원이 직접 각 채널에 로그인해서 CSV를 다운로드하고, 구글 스프레드시트에 붙여넣고, Google Apps Script로 전처리를 돌려서 시각화했다. 일상적인 모니터링은 각 채널의 리포트를 그대로 보는 수준이었다.

이 방식의 문제는 세 가지였는데,

각 플랫폼 리포트만으로는 가공에 한계가 있어서 뎁스 있는 분석이 어려웠다. 프로모션 기획이나 새 프로젝트를 시작할 때마다, 어떤 데이터를 어디서 뽑을지부터 전처리와 시각화까지 매번 처음부터 설계해야 했다.
그래서 의사결정까지 시간이 많이 걸렸다.
수작업 과정에서 데이터를 잘못 뽑거나 가공하는 리스크가 항상 있었다.

왜 BI 도구가 아니었나

BigQuery + Looker Studio도 검토했다. 결론은 맞지 않았다.

BI 대시보드는 정해진 차트를 모니터링하는 도구다. 실무를 집행하다보면 매번 질문이 바뀐다. 지난주 대비 이 캠페인의 ROAS가 왜 떨어졌는지 보다가, 30초 뒤에는 이 제품의 오프라인 매장별 재고 잔여일수를 봐야 한다. 고정된 대시보드로는 이 속도를 따라갈 수 없다.

질문을 던지면 바로 답이 나오고, 꼬리 질문도 이어갈 수 있는 환경이 필요했다. 그래서 챗봇 인터페이스를 선택했다.

비용도 현실적인 제약이었다. 전담 데이터 엔지니어가 없는 팀에서, 저렴하고 혼자 구축&유지할 수 있으면서 확장 가능한 조합이어야 했다.

구조

레이어

시스템은 세 개의 레이어로 되어 있다.

수집

Python 스크립트가 GitHub Actions 위에서 매일 아침 자동으로 돌아간다.

소스	API	수집 대상
Meta Ads	Marketing API	캠페인/광고셋/광고별 일간 퍼포먼스
Google Ads	REST API v20	캠페인/광고그룹/광고별 + 검색어 리포트
Shopify	GraphQL Admin API	주문/라인아이템/환불 + 제품/재고 스냅샷 + 세션 애널리틱스
GA4	Data API v1beta	트래픽/랜딩페이지/페이지뷰/이커머스

워크플로우 4개가 KST 07:00~07:45에 순차 실행된다. GitHub Actions 무료 티어(월 2,000분)로 충분하다.

저장

Supabase PostgreSQL 위에 21개 테이블을 설계했다. (이 과정에서는 클로드 코드의 도움을 정말 많이 받음)

광고 채널별로 테이블을 분리했다. Meta와 Google Ads는 데이터 구조가 근본적으로 다르다. 하나의 테이블에 합치면 NULL이 범벅이 되거나, 채널 고유 메트릭(frequency, search impression share 등)을 포기해야 한다.

설계 원칙은: API에서 가져올 수 있는 데이터는 최대한 풍부하게 수집하고, 분석 시점에 유연하게 가공한다. 모든 테이블에 UNIQUE 제약조건을 걸어서 UPSERT 패턴을 적용했다. 같은 날짜를 다시 수집해도 중복 없이 최신 값으로 업데이트된다.

일부 브랜드의 경우 2020년부터 6년치 데이터를 백필했다. Shopify 주문 22만 건, 라인아이템 33만 행 이상. 재고 스냅샷(90일 보존, 이후 주별 롤업)까지 포함하면 연간 약 1GB 수준이다. Supabase Pro(월 $25, 8GB)로 수년간 운영 가능하다.

챗봇

Chainlit + Claude Sonnet으로 만든 자연어 질의 챗봇이다. Railway에 Docker로 배포했다.

Claude에게 두 개의 Tool을 준다.

execute_sql : 자연어를 SQL로 바꾸고 DB에서 실행
create_chart : 결과를 Plotly 차트로 시각화

시스템 프롬프트에 DB 스키마 전체와 Few-shot 예시 5개를 넣었다. 제품 검색, 기간 비교, 재고 잔여일수 계산, 크로스 도메인 분석, 오프라인 매장별 실적. 우리 팀이 실제로 자주 하는 질문 유형이다.

설계 시 신경 쓴 부분

보안

AI가 생성한 SQL을 DB에 직접 실행하는 구조이기 때문에, 보안을 4단계로 쌓았다. (개발 지식이 많지 않은 상태에서 진행한 프로젝트기 때문에 특히나 보안을 최우선으로 신경썼다)

시스템 프롬프트에서 SELECT만 허용하도록 명시
생성된 SQL을 Regex로 검증 (INSERT/UPDATE/DELETE/DROP 탐지)
DB 연결 자체를 PostgreSQL read-only 세션으로 설정
Supabase RLS(Row Level Security)로 최종 방어

크레덴셜은 전부 GitHub Secrets와 Railway 환경변수로 관리했다. Shopify 주문 데이터에서 개인정보(이메일, 주소)는 제거하고 국가코드만 유지한다.

비용

프롬프트 캐싱을 적용했다. 시스템 프롬프트에 스키마 정보(~3,200 토큰)가 들어가는데, cache_control: ephemeral을 걸면 반복 호출 시 입력 토큰 비용이 90% 줄어든다. 팀원 여러 명이 하루에 수십 번 질의해도 API 비용이 월 $30~50 수준으로 유지된다.

*처음 구현 시 생각했던 것보다 앤트로픽 api 토큰이 너무 많이 소모되었다. 방법을 찾다가 프롬프트 캐싱을 적용하게 된 것.

스키마 설계

어떤 데이터를 어떤 단위로 뽑아야, 나중에 원하는 분석이 가능한지가 핵심이었다. 예를 들어 Meta 광고 데이터를 campaign 레벨로 뭉치면 편하지만, 그러면 ad 레벨의 빈도&도달 분석이 불가능해진다. Shopify 주문에서 sales channel과 POS location을 분리해야 온&오프라인 채널별 분석이 된다. 재고 데이터는 시점 데이터이기 때문에 일별 스냅샷이 필수다.

*다행히도 우리가 운용하는 브랜드는 오프라인 데이터 또한 Shopify를 통해 정직하게 적재되고 있었다. 분류 로직이 다소 복잡했지만 브랜드 운영을 직접 했었기 때문에 결과값을 보면서 계속 파인튜닝 할 수 있었다.

구현

코드는 대부분 Claude Code가 작성했다. 나는 개발자가 아니고, 유튜브로 CS50 강의를 수강해서 컴퓨터 지식이 조금 있는 게 전부였다.

내가 한 일은 위에서 설명한 설계: 스키마 구조, 보안 레이어, 비용 구조, 프롬프트 설계를 결정하는 것이었고, 구현 단에서 발생하는 문제들은 Claude Code에게 상황을 설명하고 해결을 맡겼다.

예를 들면 이런 것들이다.

Shopify Bulk API가 반환하는 JSONL의 __parentId가 문서와 다르게 동작해서 재고 데이터가 0건으로 파싱되는 문제. Google Ads 공식 Python 라이브러리가 GitHub Actions에서 gRPC 호환 문제를 일으켜서, REST API 직접 호출로 전환한 것. GA4 API의 요청당 메트릭 10개 제한 때문에, 같은 dimension 조합으로 2회 호출하고 merge한 것.

방향을 제시하고(“라이브러리 대신 REST API를 직접 호출하자”), 구체적인 코드 구현은 Claude Code가 처리하는 식으로 진행했다.

*복잡한 것에 대한 문제를 발견하고 결과값을 보면서 내가 원하는 답이 나올 때까지 오차를 줄여나가는 방식이 유효했다.

결과

항목	Before	After
일간 데이터 적재	수동 CSV 다운로드 + 스프레드시트 (매일 30분+)	자동 (0분)
데이터 질의	플랫폼별 로그인 or 스프레드시트 가공	자연어 질문 → 즉시 응답
분석 깊이	채널별 리포트 수준	크로스 채널 & 크로스 도메인 분석 가능
데이터 접근 가능 인원	데이터 담당자 1명	팀 전원
월 운영비	-	~$80

매일 아침 CSV를 다운로드하고 정리하던 시간이 사라졌고, 리포트 조작에 익숙하지 않은 다른 팀 구성원들도 챗봇으로 직접 데이터를 확인하게 되었다. 쓰면 쓸수록 개선점은 늘어날 것이고, 직접적으로 데이터를 여러번 딥하게 봐야하는 조직의 경우 빌드된 파이프라인을 직접 ai와 연결하여 터미널에서 조작 가능하도록 구현해볼 생각이다.

기술 스택

레이어	기술
수집	Python, GitHub Actions (cron)
API	Meta Marketing API, Google Ads REST API v20, Shopify GraphQL Admin API (Bulk Operation), GA4 Data API v1beta
DB	PostgreSQL (Supabase), 21개 테이블
챗봇	Chainlit, Claude Sonnet (Anthropic API), Tool Use, 프롬프트 캐싱
구현	Claude Code
배포	Railway (Docker), GitHub Actions

비용 내역

항목	월 비용
Supabase Pro	$25
Railway	$5
Claude API	~$50
GitHub Actions	$0 (무료 티어)
합계	~$80