광고비 정산 자동화 소개 영상
문제
이 고객은 여러 광고대행사의 매체 집행을 대행하는 미디어렙이었습니다. 매달 네이버, 카카오, 구글에서 정산 데이터가 내려오면, 담당자는 이 원본을 대행사별로 나눠 정산서를 만들어야 합니다. 문제는 규모와 양식입니다. 마스터 파일에는 대행사 시트가 90개 안팎이고 매체 3사의 원본은 컬럼 구조부터 계정 표기까지 전부 다릅니다.
수작업 절차는 이랬습니다. 매체 파일을 열어 행마다 계정 번호를 확인하고, 그 계정이 어느 대행사 것인지 매핑 표에서 찾고, 해당 대행사 시트에 행을 옮겨 적은 뒤 부가세와 수수료를 수식으로 계산합니다. 이 일을 수백, 수천 행에 걸쳐 반복합니다. 시간이 오래 걸리는 것은 물론이고, 잘못 분류된 한 행이 두 대행사의 정산서를 동시에 틀리게 만들었습니다.
더 까다로운 건 매체마다 다른 파일의 성격이었습니다. 같은 "정산 데이터"라도 계정 식별 컬럼의 위치가 다르고 구글 파일은 헤더 위치조차 고정돼 있지 않았습니다. 카카오는 광고 상품별로 파일이 네 종류나 됐습니다.
접근
핵심 설계는 매핑 테이블 하나를 기준으로 전 과정을 자동화하는 것이었습니다. 고객이 이미 관리하던 마스터 파일의 계정 정리 시트(계정과 대행사, 수수료율의 매핑)를 그대로 기준 데이터로 삼아 새 시스템을 위한 별도 관리 화면을 만들지 않았습니다. 담당자의 기존 관리 습관이 곧 시스템 설정이 됩니다.
기능
파일만 올리면 매체를 알아서 판별
정산 기간을 고르고 마스터 템플릿과 매체 원본 파일들을 올리면, 파일명 패턴으로 매체 유형(네이버, 카카오 4종, 구글)을 자동 판별해 해당 파서로 보냅니다. 사람이 파일마다 매체를 지정할 필요가 없습니다.
기능
매체별 양식 차이를 파서 계층에서 흡수
계정 컬럼 위치가 매체마다 다르고 구글은 헤더 행 위치도 유동적입니다. 매체별 파서가 각 양식의 구조를 알고 있어 헤더를 탐색해 찾고 합계 행 같은 잡행을 걸러낸 뒤 계정 식별자만 일관되게 뽑아냅니다. 카카오 4개 상품은 공통 포맷이라 파서 하나로 처리합니다.
기능
계정 라우팅과 대행사 시트 자동 전기
추출된 계정을 매핑 테이블에서 찾아 대행사를 결정하고 해당 대행사 시트에 매체·광고주·공급가액·부가세·수수료를 계산해 기입합니다. 기존 시트의 서식과 데이터를 보존한 채 이어 쓰고 동시에 매체 통합 시트에 원본 행을 그대로 남겨 가공 결과와 원본의 추적 관계를 유지합니다.
기능
미매칭은 숨기지 않고 리포트로
매핑에 없는 계정은 억지로 분류하지 않고 미매칭 목록으로 수집해 화면에 보여줍니다. 담당자는 처리 결과의 매칭률과 대행사별 분포, 미매칭 계정 목록을 보고 매핑 테이블을 보정한 뒤 다시 돌리면 됩니다. 조용한 오분류 대신 시끄러운 미매칭을 선택한 설계입니다.
처리 흐름은 다음과 같습니다.
세액과 수수료 계산도 자동입니다. 부가세는 정률로 계산하고 수수료율은 계정 정리 시트에 적힌 계정별 값을 우선 적용하되 값이 없으면 매체별 기본률로 처리합니다. 수식이 사람 손을 타던 구간이 사라지면서 계산 실수도 함께 사라집니다. 기술적으로는 Rails와 Hotwire로 만든 웹앱이라 설치 없이 브라우저에서 쓰고 결과는 타임스탬프가 붙은 정산 엑셀로 다운로드됩니다.
결과
담당자의 월 정산 작업이 파일 업로드, 결과 확인, 다운로드의 세 동작으로 줄었습니다. 화면에는 총 데이터 대비 매칭률, 대행사별 배분 분포, 미매칭 계정 목록이 표시되어 처리 품질을 그 자리에서 검증할 수 있습니다. 원본은 매체 통합 시트에 그대로 보존되므로, 가공 결과에 의문이 생기면 언제든 원본 행으로 거슬러 올라갈 수 있습니다.
구조적으로 중요한 변화는 오류의 성격입니다. 수작업 시절의 오류는 잘못 분류된 채 조용히 정산서에 섞였지만 지금의 오류는 미매칭 리포트로 시끄럽게 드러납니다. 드러난 오류는 매핑 한 줄을 고치면 끝나는 운영 업무가 됩니다.
이 프로젝트의 기술적 결정들은 아래 글에서 더 자세히 다룹니다.
- 매체별 정산서 양식 차이: 네이버·카카오·구글 파일은 어떻게 다른가
- 계정 매핑 라우팅 설계: 매핑 테이블 하나로 90개 시트를 라우팅
- 엑셀 템플릿 전기 자동화: 기존 서식을 보존하며 이어 쓰기
- 미매칭 큐 설계: 조용한 오분류 대신 시끄러운 미매칭
- 수수료·부가세 자동 계산: 계정별 요율과 기본률의 우선순위
- 정산 자동화 도입 시점: 언제 자동화가 남는 장사가 되나
자주 묻는 질문
광고비 정산 자동화에 별도 관리 시스템이 필요한가요?
이 사례에서는 만들지 않았습니다. 고객이 이미 관리하던 마스터 엑셀의 계정 정리 시트를 그대로 매핑 기준으로 읽어 기존 관리 습관이 곧 시스템 설정이 되게 했습니다. 관리 화면을 새로 만들면 데이터가 두 곳으로 갈라져 오히려 불일치의 원인이 됩니다.
매체가 정산 파일 양식을 바꾸면 어떻게 되나요?
영향은 해당 매체의 파서 하나로 격리됩니다. 매체별 양식 지식이 파서 계층에 모여 있어 컬럼이 이동하거나 헤더 위치가 바뀌면 그 파서만 수정하면 됩니다. 헤더를 위치가 아니라 텍스트로 탐색하는 방식이라 소소한 변동은 수정 없이 흡수됩니다.
매핑에 없는 새 계정이 들어오면 어떻게 처리되나요?
억지로 분류하지 않고 미매칭 목록으로 분리해 리포트에 표시합니다. 담당자가 계정 정리 시트에 매핑을 추가하고 다시 실행하면 반영됩니다. 새 계정 등록이 반복 업무가 아니라 예외 처리가 되는 구조입니다.
기존 정산 결과와 숫자가 달라질 위험은 없나요?
계산 규칙 자체를 고객의 기존 수식(부가세 정률, 계정별 수수료율)에서 그대로 가져왔고 원본 행을 통합 시트에 보존해 결과와 원본을 대조할 수 있게 했습니다. 도입 초기에 기존 수작업 결과와 병행 비교하는 기간을 두면 전환 위험을 더 줄일 수 있습니다.