광고 성과 보고서 만들기: 네이버·메타·구글 광고 리포트로 주간 ROAS 보고서를 만들 수 있나요? | 클로드 엑셀왕 7

이 장에서 다루는 것

ROAS 읽는 법

ROAS(광고비 대비 매출)는 광고로 생긴 매출을 광고비로 나눈 값입니다. 광고비 100만 원으로 매출 300만 원이 생겼으면 ROAS는 300%입니다.

이 장에서는 두 가지로 나눠 씁니다. 매체가 리포트에 적은 전환매출로 계산하면 ‘보고 ROAS’, 자사몰 주문 중 유입경로가 그 매체인 주문(취소 제외)으로 계산하면 ‘실제 ROAS’입니다. 광고비는 둘 다 VAT 별도 금액입니다.

엑셀왕 TIP 합치기 전에 원본 합계부터 적어 두기

리포트마다 비용 열과 전환매출 열을 선택해 상태 표시줄의 합계를 봅니다. 주문내역은 결제금액 열 합계와 주문 건수를 봅니다. 이 숫자들을 메모해 두면 합친 뒤에 맞는지 바로 확인할 수 있습니다.

이 장의 예제에서 비용 합계는 광고A 1,345,454원, 광고B 1,197,550원, 광고C 651,430원이고, 주문내역 결제금액 합계는 7,746,900원(255건)입니다.

클로드에서는 파일 네 개를 한 대화에

클로드 앱(웹·데스크톱·모바일)의 대화창에 리포트 세 개와 주문내역을 함께 첨부하고 시작합니다. 주문내역과 리포트를 한 대화에 두어야 두 쪽의 매출을 비교할 수 있습니다.

자사몰 주문내역에 주문자 이름·연락처·주소 열이 있다면 지우고 첨부하세요. 이 장에 필요한 열은 주문일시, 주문번호, 주문상태, 결제금액, 유입경로뿐입니다.

이렇게 말하기

광고 리포트 세 개와 자사몰 주문내역으로 주간 ROAS 보고서를 만들 거야. 합치기 전에 열 대응표와, 파일마다 다르게 적힌 부분을 먼저 보여 줘.

그러면 처리되는 일

· 세 리포트의 머리글을 읽고 일자·광고비·전환수·전환매출로 나눈 대응표를 보여 줍니다.

· 날짜가 글자로 적힌 파일, VAT 포함인 비용 열, 세 리포트 전환매출 합계가 자사몰 매출보다 크다는 점을 짚고, 무엇을 기준으로 할지 묻습니다.

이렇게 답하기

광고비는 VAT 별도로 맞춰 줘. 광고A만 1.1로 나누면 돼. 실제 매출은 자사몰 주문 중 취소를 빼고, 유입경로가 그 매체인 주문만 그 매체 매출로 봐 줘. 한 주는 월요일부터 일요일까지야.

그러면 처리되는 일

· 정한 기준을 기준표 시트에 적어 두고, 이 기준으로 합칩니다.

· 매체가 보고한 전환매출은 고치지 않고 그대로 둡니다. 보고 ROAS는 이 숫자로, 실제 ROAS는 자사몰 주문으로 따로 계산합니다.

이렇게 말하기

정한 기준대로 리포트 세 개를 광고통합 시트로, 주문내역을 주문정리 시트로 정리해서 엑셀 파일로 줘. 원본 파일 이름과 행 번호를 남기고, 날짜는 모두 진짜 날짜로 바꾸고, 주 시작일 열도 넣어 줘.

그러면 처리되는 일

· 세 리포트의 112행을 같은 열 구조의 광고통합 시트로 합칩니다. 매체·원본파일·원본행 열이 있어 어느 리포트의 몇 번째 줄인지 바로 찾아갈 수 있습니다.

· 글자로 적힌 날짜를 날짜로 바꾸고, 주시작 열에 그 주 월요일을 구하는 수식(=D2-WEEKDAY(D2,3))을 넣습니다.

· 비용(원본) 옆에 VAT 열을 두고, 비용(VAT별도) 열은 VAT가 ‘포함’인 줄만 1.1로 나눈 수식으로 채웁니다.

· 주문정리 시트에는 주시작, 광고구분(광고/광고 외), 집계매출(취소면 0) 열을 붙입니다. 취소는 8건입니다.

이렇게 말하기

매체마다 광고통합의 비용·전환매출 합계와 행 수가 원본 리포트와 같은지, 주문정리의 결제금액 합계와 주문 수가 원본 주문내역과 같은지 보는 검산 시트를 만들어 줘.

그러면 처리되는 일

· 검산 시트에 매체별 원본 합계와 통합 시트 합계, 차이를 나란히 놓습니다. 통합 쪽은 SUMIFS 수식입니다.

· 주문내역은 결제금액 합계, 주문 수, 취소 건수와 금액을 비교합니다. 광고A 비용을 1.1로 나눈 값이 맞는지도 한 줄로 확인합니다.

· 차이가 모두 0이면 ‘일치’가 나오게 하고, 아래에 세 매체 보고 전환매출 합계와 자사몰 매출을 참고로 나란히 둡니다.

이렇게 말하기

주간보고 시트에 주마다 광고비(VAT 별도), 매체 보고 전환매출과 보고 ROAS, 자사몰 광고 유입 매출과 실제 ROAS를 나란히 보여 줘. 아래에 매체별 4주 합계 표도 넣어 줘.

그러면 처리되는 일

· 주간 표는 광고통합의 주시작 열과 주문정리의 주시작 열을 조건으로 SUMIFS를 써서 주마다 더합니다.

· 매체별 표에는 보고 매출을 실제 매출로 나눈 ‘보고 ÷ 실제’ 열을 붙여, 매체마다 얼마나 넓게 세는지 보이게 합니다.

· ROAS 정의와 실제 ROAS의 기준을 표 아래에 적어 둡니다.

수식 한 줄 읽기 화면 7-5의 F5 셀

=SUMIFS(주문정리!$H$2:$H$256, 주문정리!$F$2:$F$256, $A5, 주문정리!$G$2:$G$256, "광고")

· 주문정리!$H$2:$H$256 → 더할 열은 집계매출 열입니다. 취소 주문은 이미 0으로 들어 있습니다.

· 주문정리!$F$2:$F$256, $A5 → 주시작 열이 이 행의 주 시작일(9월 28일)과 같은 주문만 고릅니다.

· 주문정리!$G$2:$G$256, “광고” → 그중 유입경로가 광고A·광고B·광고C인 주문만 고릅니다.

한 줄로 읽으면 “9월 28일에 시작하는 주에, 광고로 들어온 주문의 결제금액을 취소를 빼고 더한다”입니다. 옆의 G5는 이 값을 같은 주 광고비 C5로 나눈 실제 ROAS입니다.

엑셀왕 노트 실제 ROAS도 하나의 기준

유입경로는 주문 직전에 마지막으로 거쳐 온 곳만 기록합니다. 광고B를 보고 가게 이름을 기억했다가 며칠 뒤 검색으로 들어온 손님은 광고B 매출에 들어가지 않습니다. 그래서 실제 ROAS가 낮은 매체가 쓸모없는 매체라고 단정할 수는 없습니다.

클로드에게 “어느 광고를 줄일까?”라고 물으면 표의 숫자로 그럴듯한 답을 내놓을 수 있습니다. 예산은 두 ROAS를 함께 보고, 매체를 쓰는 목적(새 손님 찾기, 다시 찾아오게 하기)과 함께 사람이 정합니다.

클로드에서는 기준표를 프로젝트에 두기

매주 같은 보고서를 만든다면 클로드 프로젝트를 하나 만들고, 완성 파일의 기준표 시트를 프로젝트 지식에 올려 두세요. 프로젝트 지침에는 “광고 리포트는 기준표대로 합치고, 광고비는 VAT 별도로, 실제 매출은 자사몰 유입경로로 계산한다. 열 이름이나 VAT 표시가 기준표와 다르면 합치기 전에 묻는다”라고 적습니다.

그러면 다음 주 월요일에는 그 프로젝트에서 새 파일 네 개를 첨부하고 “지난주분도 같은 방식으로”라고만 해도 됩니다.

한 번에 말하는 요청

광고 리포트 세 개와 자사몰 주문내역을 합치기 전에 열 대응표와 파일마다 다르게 적힌 부분(날짜 형식, VAT, 겹쳐 센 전환)을 먼저 보여 줘.
광고비는 VAT 별도로, 실제 매출은 자사몰 주문 중 취소를 빼고 유입경로 기준으로, 한 주는 월요일부터 세서 광고통합·주문정리 시트로 정리해 줘.
원본과 맞춰 보는 검산 시트와, 주마다 보고 ROAS와 실제 ROAS를 나란히 놓은 주간보고 시트도 넣어서 엑셀 파일로 줘.

이 장의 핵심

합치기 전에 열 대응표와 파일마다 다른 부분(날짜 형식, VAT, 겹쳐 센 전환)을 먼저 받는다.

광고비 VAT 기준과 실제 매출 기준은 사람이 정하고, 기준표 시트에 남긴다.

원본 합계와 검산한 뒤, 매체 보고 ROAS와 자사몰 기준 실제 ROAS를 나란히 보고 판단한다.

답 예시

이번 광고A 파일은 이미 VAT 별도라 나누지 말고 그대로 써 줘. 기준표도 “열 이름에 VAT포함이 있을 때만 1.1로 나눈다”로 고쳐 줘.

이렇게 답하면 광고A 4주 광고비는 1,223,140원으로 완성 파일과 같고, 실제 ROAS도 176% 그대로입니다. 기준표대로 한 번 더 나누면 광고비가 1,111,945원으로 줄어, 실제 ROAS가 194%로 올라 보입니다. 같은 매체라도 내려받을 때 설정에 따라 VAT 표시가 달라질 수 있으니, 기준은 매체 이름보다 열 이름에 붙여 두는 편이 안전합니다.