무료배송 기준 금액 점검법: 주문 데이터 1천 원 구간 분석

"5만 원 이상 무료배송"처럼 무료배송 기준 금액을 정해 두고 나면, 그 기준이 실제로 장바구니 금액을 올리는지 따로 확인하지 않는 경우가 많습니다. 주문 수와 매출이 늘었다면 기준이 효과를 냈다고 보기 쉽지만, 고객이 기준을 채우려고 상품을 더 담은 것인지, 그렇게 늘어난 주문에서 실제로 남는 금액이 있는지는 전체 매출만 봐서는 알 수 없습니다.

두 가지는 지난 석 달 주문 내역 하나로 확인할 수 있습니다. 주문을 기준 금액 위아래로 1천 원씩 나눠 기준 바로 위에 주문이 몰렸는지 보고, 그 구간 주문의 마진과 받지 않은 배송비, 반품 비용을 비교하는 방법입니다. 이 글에서는 엑셀이나 구글 시트로 이 과정을 진행하는 순서를 단계별로 정리합니다.

점검에 필요한 주문 데이터 열

먼저 주문 한 건을 한 줄로 두고 아래 열을 준비합니다. 쇼핑몰에서 내려받는 주문 파일에 바로 들어 있는 열도 있고, 상품 원가처럼 따로 붙여야 하는 열도 있습니다.

마진은 상품마다 원가가 다르므로 상품 목록에 원가를 적어 두고 주문 파일에 상품별로 붙여 계산합니다. 원가를 상품마다 붙이기 어렵다면 처음에는 판정 금액에 브랜드 전체의 평균 마진율을 곱해 시작합니다. 이 값으로도 구간 사이의 대략적인 차이는 볼 수 있습니다.

카페24와 아임웹에서 주문 내역 내려받기

카페24는 관리자 화면 통합 엑셀 메뉴의 엑셀 파일 요청에서 주문 관리 탭의 주문 내역을 엑셀 파일로 요청합니다. 기본 양식과 직접 구성한 양식은 품목번호 기준이라 상품을 여러 개 담은 주문은 주문번호가 여러 줄에 반복됩니다. 이 분석에서는 주문 한 건이 한 줄이어야 하므로 주문번호 기준 양식으로 내려받거나, 품목 줄을 주문번호로 묶어 합친 뒤 씁니다. 1개월을 넘고 3,000건 이상인 요청은 대용량으로 분류되어 요청 다음 날 생성되고, 생성된 파일은 같은 메뉴의 요청 파일 다운로드에서 요청일로부터 7일 안에 받을 수 있습니다. (엑셀 파일 요청, 요청 파일 다운로드, 주문 데이터 엑셀 다운로드 FAQ)

아임웹은 관리자 페이지 쇼핑 메뉴의 주문 화면 상단에 있는 내보내기 버튼을 누른 뒤 "주문일 내 모든 주문"을 고르면 기간 단위로 내려받을 수 있습니다. 내보내기 창의 양식관리에서 필요한 항목만 골라 양식을 만들 수 있고, 항목에는 총 품목합계금액, 총 합계 할인금액, 총 합계 배송비, 반품승인일이 있어 위 표의 판정 금액과 고객 결제 배송비, 반품 여부를 만드는 데 쓸 수 있습니다. (주문 내보내기와 양식설정)

다른 플랫폼을 쓴다면 그 플랫폼의 주문 엑셀 다운로드 도움말을 찾아 같은 열을 맞추면 됩니다. 파일에는 고객 이름과 연락처 같은 개인정보가 함께 들어 있으므로, 분석에 필요한 열만 남기고 나머지는 지운 뒤 작업하는 편이 안전합니다.

무료배송 판정과 같은 금액으로 구간 나누기

구간을 나누기 전에 정할 것은 어느 금액으로 나눌지입니다. 쇼핑몰마다 무료배송 여부를 할인 전 상품 금액으로 판정하기도 하고, 쿠폰과 할인을 적용한 뒤의 금액으로 판정하기도 합니다. 구간도 반드시 쇼핑몰 배송비 설정에서 무료배송을 판정하는 금액과 같은 금액으로 나눕니다.

구간을 나눈 금액과 판정 금액이 어긋나면 결과가 달라집니다. 기준이 5만 원이고 할인 후 금액으로 판정하는 쇼핑몰에서, 상품 합계 52,000원에 3,000원 쿠폰을 쓴 주문은 판정 금액이 49,000원이라 고객이 배송비를 냈습니다. 이 주문을 할인 전 금액으로 나누면 기준 위 52,000원 구간에 들어가 무료배송 주문처럼 집계됩니다.

판정 금액 열을 만든 뒤 구간 열을 추가합니다. 판정 금액이 C열에 있다면 아래 수식은 엑셀과 구글 시트 모두에서 1천 원 단위로 내림한 값을 돌려줍니다.

=FLOOR(C2, 1000)

49,800원 주문은 49,000, 50,300원 주문은 50,000이 되어 각각 "49,000~49,999원", "50,000~50,999원" 구간에 들어갑니다. 그다음 판정 금액이 기준보다 5천 원 낮은 금액 이상이고 기준보다 5천 원 높은 금액 미만인 주문만 남깁니다. 기준이 5만 원이면 45,000원 이상 55,000원 미만입니다.

마지막으로 무료배송 때문에 받지 않은 배송비 열을 만듭니다. 기본 배송비가 3,000원이라면 =IF(C2>=50000, 3000, 0)처럼 판정 금액이 기준 이상인 주문에만 배송비를 적습니다.

피벗 표로 구간별 주문 수, 마진, 반품 비용 집계

피벗 표는 행 하나하나의 데이터를 원하는 기준으로 묶어 개수와 합계를 한 번에 보여 주는 표입니다. 엑셀과 구글 시트 모두 삽입 메뉴의 피벗 테이블로 만들 수 있습니다.

1. 주문 데이터 전체를 선택하고 피벗 테이블을 삽입합니다.
2. 행에 구간 열을 넣습니다. 45,000부터 54,000까지 열 개 구간이 위에서부터 나열됩니다.
3. 값에 아래 여섯 가지를 넣습니다.
   • 주문번호: 개수(구글 시트는 COUNTA)
   • 판정 금액: 합계(구간 매출)
   • 마진: 합계
   • 반품 여부: 합계(반품 건수)
   • 반품 비용: 합계
   • 받지 않은 배송비: 합계
4. 구간 순서가 금액 순서와 다르면 행을 오름차순으로 정렬합니다.

이 표 하나로 몰림 확인과 남는 금액 계산을 모두 진행합니다.

기준 바로 위 구간의 주문 몰림 판단

아래 표는 무료배송 기준 5만 원인 쇼핑몰의 석 달 주문을 피벗 표로 집계했다고 가정한 가상 예시입니다. 주문 수 열만 먼저 봅니다.

기준 바로 위 50,000~50,999원 구간의 주문이 231건으로, 바로 아래 49,000~49,999원 구간 96건의 2.4배입니다. 기준 아래 다섯 구간은 평균 108건 정도이고 기준에 가까워질수록 조금씩 줄다가, 기준을 넘자마자 크게 늘어납니다. 이런 모양이면 기준 근처에서 장바구니를 채운 고객이 기준을 넘기려고 상품을 더 담았을 가능성이 큽니다.

다만 특정 상품의 가격 때문에 생긴 몰림일 수도 있습니다. 예를 들어 25,000원짜리 인기 상품을 두 개씩 사는 주문이 많으면 무료배송과 상관없이 50,000원 구간에 주문이 모입니다. 몰린 구간의 주문을 몇십 건 열어 보고, 저가 상품 하나를 더해 기준을 넘긴 주문이 많은지 확인하면 두 경우를 구분할 수 있습니다.

반대로 기준 위아래 구간의 주문 수가 비슷하다면, 기준 금액이 고객의 장바구니 금액에 주는 영향은 크지 않다고 볼 수 있습니다. 이때는 기준이 객단가보다 너무 높아 채울 엄두가 나지 않는지, 배송비 조건이 상품 목록이나 상세 화면에 보이지 않아 고객이 기준을 모르는지부터 확인합니다.

기준을 넘긴 주문이 남는 주문인지 계산하기

몰림을 확인했다면 다음은 기준을 넘긴 주문이 실제로 남는 주문인지 확인할 차례입니다. 기준 위 주문은 주문 금액이 커진 만큼 마진이 늘어납니다. 대신 쇼핑몰은 배송비를 받지 않고 택배비를 부담합니다. 유럽의 한 온라인 소매업체 데이터를 분석한 2020년 연구에서는 무료배송 프로모션이 반품 위험이 큰 상품 구매를 늘려 반품률을 올렸습니다. 그래서 반품 비용까지 함께 봅니다.

같은 가상 예시에서 피벗 표의 나머지 값을 기준 아래 다섯 구간, 기준 위 다섯 구간, 몰림이 가장 큰 50,000~50,999원 구간으로 묶으면 아래와 같습니다. 마진율은 40%, 기본 배송비는 3,000원, 반품 한 건 비용은 6,000원으로 가정했고, 기준 아래 주문은 고객이 낸 배송비로 택배비를 충당한다고 봤습니다.

주문당 남는 금액은 주문당 마진에서 받지 않은 배송비와 반품 비용을 뺀 값입니다. 이 예시에서 기준 위 주문은 평균 판정 금액이 4,500원 커서 주문당 마진이 1,800원 늘었지만, 받지 않은 배송비 3,000원이 그보다 큽니다. 반품률도 3.0%에서 5.0%로 올라, 주문당 남는 금액은 기준 아래보다 1,320원 적습니다. 기준을 넘기려고 담은 것으로 보이는 50,000~50,999원 구간은 반품률이 6.1%로 가장 높고 남는 금액도 가장 적습니다.

이런 결과라면 기준 금액을 조금 올려 늘어나는 마진이 배송비를 넘게 하거나, 기준을 채울 때 함께 담기는 상품을 마진이 높은 상품으로 바꾸거나, 몰린 구간의 반품 사유부터 확인하는 쪽을 검토해 볼 수 있다고 봅니다. 반대로 기준 위 주문의 남는 금액이 기준 아래와 같거나 크다면, 지금 기준은 장바구니를 키우면서 수익도 지키고 있다고 판단할 수 있습니다.

분석 기간과 제외할 주문

같은 계산이라도 어떤 주문을 넣느냐에 따라 결과가 달라집니다. 피벗 표를 만들기 전에 아래 조건을 먼저 정리합니다.

• 반품 신청 기간이 지나지 않은 주문: 최근 주문은 아직 반품이 들어오지 않아 반품률이 낮게 나옵니다. 석 달을 잡되, 반품 신청 기간이 지난 날짜까지의 주문만 넣습니다.
• 프로모션 기간: 쿠폰 행사나 기준 금액을 임시로 낮춘 기간에는 장바구니 금액이 평소와 다르게 움직입니다. 행사 날짜를 빼거나 따로 집계해 비교합니다.
• 기준 변경 전 주문: 석 달 안에 기준 금액을 바꿨다면, 바꾼 날 이후 주문만 씁니다.
• 기준과 상관없이 배송비가 빠진 주문: 무료배송 쿠폰, 회원 등급 혜택, 상품별 무료배송으로 배송비를 내지 않은 주문은 기준을 의식하지 않은 주문이라 뺍니다.
• 취소 주문: 결제 후 취소된 주문은 배송이 일어나지 않았으므로 뺍니다. 반품 주문은 반품 비용을 계산해야 하므로 남깁니다.

점검 결과에 따라 다음에 확인할 것

정리하면 결과는 세 가지 경우로 나뉩니다.

처음 점검한다면 주문 수 열로 몰림부터 보고, 몰림이 보일 때 마진과 반품 비용 열을 붙이면 작업량을 줄일 수 있습니다. 기준 금액을 바꿨다면 바꾼 뒤 석 달 주문으로 같은 표를 다시 만들어, 기준 위아래의 주문당 남는 금액이 어떻게 달라졌는지 비교합니다.