구글 스프레드시트로 읽는 마케팅 데이터 | Week 03-5

1. VLOOKUP으로 데이터 연결하기

광고 플랫폼에서 내려받은 성과표에는 광고비·클릭 수만 있고, 별도 기획표에는 캠페인명·채널·타깃 정보가 남아 있는 경우가 많다. 이 둘이 연결돼야 어느 타깃과 소재에 예산을 더 쓸지 판단할 수 있다. VLOOKUP은 두 표에서 같은 값을 찾아 필요한 정보를 연결하는 함수다.

함수를 쓰지 않으면 채널명을 하나씩 검색해 복사·붙여 넣어야 한다. 데이터가 많아지면 누락이나 휴먼 에러가 생기기 쉽다.
표기 방식이 달라도 문제다. 같은 회사여도 VISTAWOO, 비스타우, (주)비스타우, ㈜비스타우처럼 적혀 있으면 스프레드시트는 서로 다른 값으로 본다. 이 경우 #N/A가 나오므로, 가능하면 ID를 기준으로 연결하거나 이름 표기부터 통일해야 한다.

예를 들어 성과표의 A2에 캠페인 ID가 있고, 기준표 F:G에 ID와 채널명이 있다면 아래 수식을 아래로 채우기만 하면 된다. 광고비와 클릭 수만 보던 표에 채널 정보가 더해져, 채널별 클릭률과 예산을 바로 비교할 수 있다.

=VLOOKUP(찾을 값, 범위, 열 번호, [일치 방식])

=VLOOKUP(A2, $F$2:$G$10, 2, FALSE)

• A2: 찾을 값. 여기서는 캠페인 ID다.
• $F$2:$G$10: 찾을 값과 가져올 정보가 함께 있는 기준 범위다. $는 수식을 복사해도 범위가 움직이지 않게 한다.
• 2: 범위의 두 번째 열 값을 가져온다.
• FALSE: 같은 값을 정확히 찾는다.

VLOOKUP은 첫 번째 열에서만 찾고 오른쪽 값만 가져온다. 같은 ID가 여러 번 있으면 첫 결과만 가져오므로, 잘못된 타깃이나 채널 정보가 붙지 않도록 기준표의 중복도 먼저 확인해야 한다.

2. IF 함수로 조건에 따라 값 나누기

고객이 쿠폰을 썼는지, 광고를 클릭했는지처럼 성과를 가르는 조건은 ㅇ, x 같은 문자로 기록되기도 한다. 이 상태로는 조건별 성과를 한눈에 비교하기 어렵다. IF 함수는 조건이 맞는지 확인하고, 결과에 따라 서로 다른 값을 돌려주는 함수다.

비가 온 날과 오지 않은 날의 판매량을 비교할 때, 왔음·ㅇ을 모두 1로, 나머지는 0으로 바꾸면 비교하기 쉬워진다. 첫 행에 IF 수식을 넣고 아래로 채우면 자동으로 정리된다. 광고 클릭 여부, 쿠폰 사용 여부, 신규·기존 고객처럼 서로 다른 집단의 반응을 나눌 때도 같은 방식으로 활용할 수 있다.

=IF(조건, 조건이 맞을 때 값, 조건이 맞지 않을 때 값)

=IF(B4="왔음", 1, IF(B4="ㅇ", 1, 0))

• B4=”왔음”: B4 셀의 값이 왔음인지 먼저 확인한다.
• 1: 조건이 맞으면 비가 온 날이라는 뜻으로 1을 입력한다.
• IF(B4=”ㅇ”, 1, 0): 첫 조건이 아니면 ㅇ인지 한 번 더 확인한다. 이 경우도 1, 둘 다 아니면 0을 입력한다.

IF는 조건이 두세 개일 때 빠르게 분석 기준을 만드는 데 좋다. 하지만 왔음, ㅇ, 비, 우천처럼 표기가 계속 늘어나면 IF 안에 조건을 계속 덧붙여야 해서 수식이 길어진다.

이때는 왔음, ㅇ, 비는 1로, 안왔음, x는 0으로 정해 둔다. 이후 =VLOOKUP(B4, 기준표 범위, 2, FALSE)로 분석값을 찾아오면 된다. 새 표기가 생겨도 긴 IF 수식을 고치는 대신 기준표에 한 줄만 추가하면 되므로, 조건이 많을수록 관리하기 편하다.

꼭 1과 0만 써야 하는 것은 아니다. 비가 온 날은 RAIN, 오지 않은 날은 NO_RAIN처럼 분석용 코드를 만들어 묶어도 된다. 캠페인 리포트에서는 계산할 값인지, 비교를 위해 구분할 값인지에 따라 형식을 정하면 된다.

3. 차트로 데이터 흐름 보기

차트는 표에서 놓치기 쉬운 변화를 빠르게 찾는 화면이다. 캠페인 리포트에서 숫자를 길게 나열하는 대신, 언제 성과가 달라졌고 어떤 조건이 겹쳤는지 먼저 확인하게 해 준다. 이번에는 날짜별 판매량과 날씨를 함께 놓아 비가 온 날의 판매량 변화를 확인해 보았다.

판매량은 수십 단위이고 날씨는 0과 1뿐이라 같은 축을 쓰면 날씨 변화가 잘 보이지 않는다. 그래서 판매량은 왼쪽 축에, 날씨는 오른쪽 축에 두었다.

• 날짜: 판매량이 언제 달라졌는지 확인한다.
• 판매량: 변화의 크기와 방향을 읽는다.
• 날씨: 판매량 변화와 함께 살펴볼 외부 조건으로 둔다.

차트는 비가 온 날과 판매량이 함께 움직이는 구간을 보여 준다. 다만 함께 움직였다는 사실이 곧 원인은 아니다. 요일·프로모션·시즌 같은 조건도 같이 확인해야 다음 캠페인에서 무엇을 유지하거나 바꿀지 근거를 만들 수 있다.

Reflection