안녕하세요 호랑이눈입니다
혹시 매일 아침 출근하자마자 광고 매체 대시보드를 열고 데이터를 엑셀에 작업하느라 오전 시간을 다 보내고 계신가요? 저도 신입 시절에는 그랬습니다^^ 네이버,구글,메타… 매체별로 시트를 따로 만들고 함수가 꼬여서 다시 작성할때마다 힘들었던 기억이 나네요 리포트 만드는데 시간을 너무 쓰다보니 정작 중요한 성과분석과 전략설정은 점심쯤이나 오후가 되서야 시작할 수 있었어요
그러다보니 광고대행사에서 근무할 때 회사에서는 전체 광고주의 리포트를 가공없는 완전 자동화하는걸 원했었는데 사실 광고주마다 KPI가 다르기 때문에 틀이 달라지는 부분이 있다보니 그건 현실적으로 어려운 부분이었어요
하지만! 리포트 구조를 반자동화 형태로 새로 만들고 나서 부터는 작성 시간이 대폭 감소되었어요 리포트 쓰는 시간이 단축되다보니 빠르게 성과를 파악하고 빠른 액션을 실행할 수 있었죠 이번에는 작성 시간을 단축할 수 있는 반 자동화 구조를 만드는 방법에 대해 얘기해볼게요
포인트1. 리포트 작성하는 시간단축
퍼포먼스 마케팅을 하면서 왜 리포트를 쓸까요? 그것은 성과를 확인하고 파악된 성과에 따라 전략 설정 및 액션을 하기 위해서인데요, 그러면 작성할 땐 어떤게 중요할까요? 제가 생각하는 첫번째 포인트는 “시간”입니다
리포트를 쓰고 성과를 파악하고 액션을 해줘야하는데 작성하는 시간이 오래걸리면 그만큼 액션을 해줘야하는 시간이 늦어지게 되고 그것이 전부 성과에 반영이 된다고 생각을 하는편입니다 그래서 저는 30분안에 작성 완료를 추구하는 편입니다
하지만 운영하는 광고 매체가 많아질수록 가공하는데 시간이 꽤 소요가 됩니다 광고 매체별로 제공해주는 데이터의 테이블이 다르거든요
포인트2. 캠페인/그룹 인덱스 분류하기
네이버, 구글, 메타, 카카오, 토스, 당근 등 광고 매체는 다양하지만 각 광고매체별로 제공해주는 데이터 양식이 다른데요 그래서 두번째 포인트는 “인덱스” 분류 하기에요
각 광고매체별로 제공해주는 데이터 양식은 통일이 되어있지 않지만, 캠페인/그룹 구조는 대부분의 광고매체는 통일되어 있어요. 그래서 엑셀에다가 내가 세팅한 광고매체별로 캠페인/그룹명을 등록하고 이걸 &함수를 사용해서 key로 만들어준 뒤 (ex: 캠페인명&그룹명) 그 뒤에 일별,주차별,월별,채널,매체,상품&서비스 등 보고자하는 데이터로 속성을 부여하면 나중에 간단히 불러올 수 있어서 운영하는 광고매체별로 정리를 해주면 됩니다. 이 작업을 하는 이유는 각 광고매체별로 통일되지 않은 필드를 통일해서 피벗테이블로 데이터를 합치기 위해서에요 아, key를 만들땐 중복이 생기지않게 주의하세요!
아래 이미지는 제가 운영하는 리포트의 인덱스 구성으로 참고로 보세요 제가 분석하고 싶은 데이터 필드로 구성하였어요 인덱스 필드에 utm 파라미터도 같이 정리를 해둬야 나중에 전환 데이터를 연결할 수 있고, 캠페인이나 그룹이 추가되면 인덱스 시트도 업데이트는 필수!!
포인트3. 매체별 RAW시트 만들기
광고매체별 캠페인/그룹명 인덱스를 정리가 완료가 되었다면, 이제는 각 광고매체별 raw시트를 만들어줄 차례에요 이때 포인트는 광고매체에서 다운받은 리포트 복붙용 영역과 내가 확인을 데이터 가공용 영역을 분리를 하는거에요 아래 그림처럼요
어떤 형태인지 아시겠나요? 오른쪽 흰 바탕 부분에 매체에서 다운받은걸 그대로 복사해서 붙여넣으면 왼쪽의 연두배경에 함수가 걸려있어서 자동으로 데이터가 가공이 되는 형태에요 왼쪽의 가공되는 데이터 필드는 KPI에 맞게 구성하시면 됩니다
이런식으로 raw 시트를 각 광고매체별로 만들어두면 제공하는 데이터 필드가 다르더라도 간단히 다운→복붙→자동가공의 반 자동화 시스템이 구축이 되는데요 이 방식으로 하면 각각 진행했던 광고매체별 데이터 가공 시간을 단축시킬 수 있어요
포인트4. 피벗테이블 사용 및 최종RAW 시트 만들기
앞에서 만든 광고매체별 raw 시트에서 자동 가공으로 만든 영역을 피벗테이블을 돌려주는데 이유는 데이터를 합쳐주면서, 데이터가 압축되서 파일도 가벼워집니다 이렇게 만든 각 매체별 피벗 데이터를 복사해서 최종RAW라는 시트를 만들어주고 거기다가 피벗에 있는 데이터를 붙여넣기해서 모아주면 끝이에요.
여기서 내부 어드민이나 GA4를 통해 트래킹이 가능해서 연결할 전환 데이터가 있다면 이것도 매체 데이터와 연결하기 위해 전용 raw시트를 만든 다음에 데이터를 넣고 연결할 key를 만든 다음에 vlookup, sumif, countif 등 함수를 사용해서 연결하면 됩니다 그러면 이렇게 최종 KPI까지 트래킹이 가능한 리포트가 됩니다
포인트5. 전용 리포트 파일로 데이터 옮기기
이렇게 리포트 작성이 완료가 되었다면 리포트 전용 파일로 데이터를 옮겨줍시다 그 이유는 데이터 기간을 최대한 길게 유지하려면 파일 크기가 작아야하기 때문이에요 함수가 엑셀 용량에서 차지하는 비중이 크거든요 그리고 데이터를 옮길때는 함수는 없애고 값붙여넣기로 옮기셔야해요
이렇게 다 만들었다면 피벗 테이블과 슬라이서를 조합해서 내가 원하는 데이터를 바로바로 확인이 가능하답니다 그리고 이 피벗테이블을 활용해서 요약 시트를 만들어 그래프나 표가 들어간 대쉬보드 형태로도 꾸밀 수 있어서 활용성이 높답니다
이 방식이 좋은 점
그리고 엑셀뿐만 아니라 이 방식 그대로 raw데이터를 구글 스프레드 시트를 만들어서 넣어놓는다면 루커스튜디오의 raw로도 사용할 수 있습니다 그리고 데이터를 얼마나 잘 정리하느냐에 따라 PPT로 옮기는 시간도 단축할 수 있구요
과거에 제가 아직 신입 레벨일때는 엑셀 시트별로 각 네이버,구글,카카오 등 광고매체별로 시트를 분리해서 각 매체별로 리포트를 다운받고 가공을 하고 붙여넣기 하는 방식으로 해서 작업시간이 너무 오래걸려서 액션이 늦어지는 것도 많았고 함수를 걸어서 데이터를 가공했던 방식은 파일이 너무 무거워져서 기간일 길게 비교하려면 파일을 여러개 켜서 비교하고는 했었는ㄴ데요 그 당시에는 잘 몰랐지만 지금 생각해보면 아주 비효율적이었네요 그래도 지금 방식으로 바꾸고나서는 업무의 효율화도 성과의 개선도 모두 잡아내게된 중요한 작업이었답니다
지난 포스팅은 “데이터기반 의사결정” 에 대해서 핵심만 얘기했었는데, 못보신 분들은 클릭해서 들어보면 도움이 될거에요
댓글 남기기