구글시트 QUERY: 쇼핑몰 채널별 다른 데이터 통합해서 매출 분석하기

방법 요약

1. 채널별 엑셀파일을 다운받아 데이터를 구글 시트에 복붙한다.
2. 데이터에서 추출할 공통부분을 만들기 위해 데이터 정제를 진행한다.
3. 채널별로 QUERY를 작성하며 오류를 테스트한다.
4. QUERY를 대괄호로 통합한다.

샘플링크

구글시트 샘플

설명

쇼핑몰을 운영하신다면, 사방넷, 샵링커들을 사용해서 상품을 통합 관리하고 계실텐데요.

메인 상품을 두고, 채널별로 상품을 매칭시키고, 가격/이름/할인 등을 나눠서 관리하는 것이 만만치 않은 일이죠.

또 실제 판매금액과 프로그램에 찍히는 판매금액에 차이가 있을 때도 있어서 실제 매출이 얼마인지, 이익이 얼마인지 명확하게 계산하기 어렵다는 말씀도 많이 들었습니다.

월별로 통장에 들어온 금액, 나간 금액을 계산해서 이익을 확인하시는 경우가 많은 것 같습니다.

---

대표님이 직접 모든 것을 하시는 회사라면 괜찮지만, 사원으로 일하고 있는데 주간 판매 보고를 작성해야 할 때 일이 번거로워집니다.

각 쇼핑몰에서 데이터를 받고, 필요한 열만 복붙하고, 이름이 다른 것은 찾아바꾸기 하고, 피벗테이블을 돌리고, 보고서 양식에 맞춰 복붙하는 일련의 노가다 과정을 하다보면, 정작 중요한 일은 못하는 경우가 있죠.

구글 시트 QUERY를 사용하면, 노가다를 최소화하고 업무 처리 시간도 꽤 줄일 수 있습니다.

QUERY 라고 해서 어렵다고 생각하실 수 있는데, 그냥 FILTER같은 새로운 함수라고 생각하시면 좋습니다.

QUERY가 무엇인지 처음 들으신다면 소개하는 포스팅을 읽어보세요.

---

그럼 방법을 살펴보시죠.

1. 데이터 다운로드

입점하고 있는 CAFE24, 스마트스토어, 11번가 등 다양한 채널의 관리자에서 매출 기록을 엑셀로 다운로드 받습니다.

또는 사방넷과 같은 프로그램을 사용하신다면, 통합된 엑셀을 받으셔도 관계없습니다. 가장 정확한 데이터일수록 좋겠지요?

2. 구글 시트에 데이터 복붙

구글 시트 파일에 소스별로 시트를 만들어서 그대로 전체 복붙을 진행합니다.

노가다를 최소화할 것이기 때문에 데이터 수정을 하지 않고, 전체를 붙여 넣습니다.

단, 추가 데이터에서는 라벨에 해당하는 행은 제외하고 데이터만 복붙합니다.

3. 마스터 양식 만들기

각 채널별로 데이터의 양식이 다르기 때문에 통합을 위해서 마스터 양식을 만듭니다.

모든 채널에 공통적으로 들어있는 라벨로 양식을 만듭니다.

가끔씩 어떤 채널은 이름+옵션이 하나의 셀에 들어 있고, 다른 채널은 이름, 옵션이 각각의 셀에 들어있습니다.

이런 경우에는 복붙한 데이터를 한 번 가공해서 동일한 양식으로 만들고 나서 QUERY를 진행해야 합니다.

TIP) 상품코드를 올바르게 만들면 통합이 매우 간단해집니다.

상품코드에 상품명, 옵션, 사이즈 등의 상품을 표현하는 모든 데이터가 포함되어 있다면, 통합은 상품코드 기준으로 진행할 수 있어서 단계가 간단해집니다.

채널별로 상품코드를 부여하기 때문에 무슨 소용이 있나 싶을 수 있는데, 다음과 같이 처리할 수 있습니다.

1) 추가 정보에 자사의 코드를 기록하여 매칭

2) 채널-상품코드 매칭 테이블로 매칭

4. QUERY 짜기

데이터 정제 작업까지 되어서 이제 공통의 데이터 열이 확보되었다면, QUERY를 작성합니다.

여러 시트를 통합 예정이니, ‘채널1’ 과 같이 표시를 해주면 좋겠죠? 모든 행에 동일하게 들어갑니다.

대괄호로 묶기 전에 에러가 나지 않는지 보기 위해서 QUERY를 하나씩 테스트하는 것이 좋습니다.

조건에 들어가는 일반적인 항목들이 있지요.

1. 일자 지정
2. 매출취소 여부
3. 반품 여부

매출 취소나 반품의 경우에 채널별로 표현방식이 다르지만 QUERY에서 NOT CONTAINS 구문을 사용할 때가 많이 있습니다.

예를 들어, 매출 취소 열(H)이 있고 “취소” 라고 표시가 된다면, 아래과 같이 써서 취소가 들어간 행은 제외시킬 수 있습니다.

반대로 반품여부 열(H)에 “정상”, “반품” 과 같이 표시가 된다면, 아래처럼 “정상” 만 포함시킬 수도 있습니다.

4. QUERY 통합

각 채널별로 QUERY를 짜고, 성공한다면 이제 대괄호를 사용해서 통합해줍니다.

QUERY 사이에 세미콜론 (;) 을 사용해서 데이터를 아래로 붙여넣기 합니다. 쉼표 (,)를 사용하면 오른쪽으로 붙여넣기되니 유의하세요.

이제 통합된 데이터로 피벗테이블을 돌리거나 INDEX 함수를 사용해서 보고서를 짜는 것이 가능합니다.

이전에는 보고서 기간별로 찾아바꾸기, 행 제거 등과 같이 노가다가 많았는데, 이렇게 통합을 해두면 이제 새로운 데이터를 복붙만 하면 됩니다.