온라인 쇼핑몰이나 오픈마켓을 운영하다 보면 판매량이 많은 상품이라도 반품이 자주 발생하면 실제 수익이 줄어들 수 있습니다. 특히 의류, 신발, 생활용품처럼 사이즈나 품질, 고객 기대와 실제 상품의 차이로 반품이 발생하는 상품은 반품률을 정기적으로 확인하는 것이 중요합니다.
엑셀을 이용하면 상품별 판매수량과 반품수량을 비교하여 반품률을 자동으로 계산할 수 있습니다. 여기에 챗GPT를 활용하면 계산 수식 작성부터 반품률이 높은 상품 분석, 개선이 필요한 상품 구분까지 쉽게 처리할 수 있습니다.
1. 상품별 반품률이란?
상품별 반품률은 판매한 상품 중 고객이 반품한 상품의 비율을 의미합니다.
반품률(%) = 반품수량 ÷ 판매수량 × 100
예를 들어 티셔츠를 200개 판매하고 12개가 반품되었다면 반품률은 다음과 같습니다.
12 ÷ 200 × 100 = 6%
즉, 판매한 티셔츠 100개당 평균 6개가 반품된 셈입니다.
반품률이 높다고 무조건 상품에 문제가 있는 것은 아닙니다. 상품 특성, 사이즈 선택, 배송 중 파손, 고객의 단순 변심 등 다양한 원인이 있을 수 있으므로 반품 사유도 함께 살펴보는 것이 좋습니다.
2. 엑셀에 상품별 판매·반품 데이터 입력하기
먼저 엑셀에서 다음과 같이 관리표를 작성합니다.
| 상품명 | 판매수량 | 반품수량 | 반품률 |
|---|---|---|---|
| 여성 티셔츠 | 200 | 12 | 6.0% |
| 여성 청바지 | 150 | 18 | 12.0% |
| 남성 셔츠 | 180 | 9 | 5.0% |
| 운동화 | 120 | 15 | 12.5% |
| 가디건 | 100 | 4 | 4.0% |
| 합계 | 750 | 58 | 7.7% |
엑셀에서는 다음과 같이 열을 배치합니다.
A열: 상품명
B열: 판매수량
C열: 반품수량
D열: 반품률
첫 번째 상품 데이터는 2행부터 입력합니다.
판매수량과 반품수량은 반드시 같은 집계 기준을 사용해야 합니다. 예를 들어 판매는 이번 달 주문 기준인데 반품은 지난달 판매분까지 포함하면 계산 결과가 왜곡될 수 있습니다.
3. 엑셀 수식으로 반품률 자동 계산하기
D2 셀에 다음 수식을 입력합니다.
=IFERROR(C2/B2,0)
수식을 입력한 다음 D2 셀의 표시 형식을 백분율(%)로 변경하고 소수점 한 자리까지 표시합니다.
그러면 여성 티셔츠의 반품률이 6.0%로 나타납니다.
D2 셀의 오른쪽 아래 채우기 핸들을 D6까지 끌어내리면 나머지 상품도 자동 계산됩니다.
IFERROR 함수는 판매수량이 0인 경우 나눗셈 오류가 표시되지 않도록 합니다. 다만 판매수량이 0일 때 나타나는 0%는 실제 반품률이 0%라는 의미가 아니라 계산할 수 없다는 의미이므로 구분해서 관리하는 것이 좋습니다.
판매수량이 0인 상품을 별도로 표시하려면 다음 수식을 사용할 수 있습니다.
=IF(B2=0,"계산불가",C2/B2)
4. 전체 상품의 평균 반품률 계산하기
상품별 반품률과 전체 반품률은 계산 방법이 다릅니다.
전체 반품률은 상품별 반품률을 단순 평균하는 것이 아니라 전체 반품수량을 전체 판매수량으로 나누어야 합니다.
B7 셀에 입력합니다.
=SUM(B2:B6)
C7 셀에 입력합니다.
=SUM(C2:C6)
D7 셀에 입력합니다.
=IFERROR(C7/B7,0)
계산 결과는 다음과 같습니다.
전체 판매수량: 750개
전체 반품수량: 58개
전체 반품률: 약 7.7%
이 수치를 매월 기록하면 쇼핑몰의 반품률이 증가하는지 감소하는지 확인할 수 있습니다.
5. 챗GPT로 반품률 계산 수식 만들기
엑셀 함수에 익숙하지 않다면 챗GPT에 필요한 조건을 설명하고 수식을 요청할 수 있습니다.
다음과 같이 질문합니다.
“엑셀 A열에는 상품명, B열에는 판매수량, C열에는 반품수량이 있습니다. D열에 상품별 반품률을 자동 계산하는 수식을 만들어 주세요. 판매수량이 0이면 계산불가로 표시하고, 결과는 백분율로 표시하고 싶습니다.”
챗GPT가 제안하는 수식은 다음과 같습니다.
=IF(B2=0,"계산불가",C2/B2)
이 수식을 D2에 입력하고 아래로 복사하면 됩니다.
챗GPT는 수식을 만드는 데 도움을 주지만 실제 판매 데이터가 정확한지까지 자동으로 보장하지는 않습니다. 따라서 판매수량과 반품수량의 집계 기준을 먼저 확인해야 합니다.
6. 반품률이 높은 상품 자동 표시하기
반품률이 높은 상품을 쉽게 찾으려면 엑셀의 조건부 서식을 활용하면 됩니다.
예를 들어 반품률이 10% 이상인 상품을 빨간색으로 표시해 보겠습니다.
- D2부터 D6까지 선택합니다.
- 홈 메뉴에서 조건부 서식을 클릭합니다.
- 셀 강조 규칙에서 ‘보다 크거나 같음’을 선택합니다.
- 기준값에 10%를 입력합니다.
- 빨간색 채우기를 선택하고 확인합니다.
설정이 완료되면 다음 상품이 강조됩니다.
| 상품명 | 반품률 | 확인 결과 |
|---|---|---|
| 여성 티셔츠 | 6.0% | 일반 |
| 여성 청바지 | 12.0% | 점검 대상 |
| 남성 셔츠 | 5.0% | 일반 |
| 운동화 | 12.5% | 점검 대상 |
| 가디건 | 4.0% | 일반 |
여기서 10%는 실습을 위한 임의 기준입니다. 실제 관리 기준은 상품 종류, 판매 규모, 과거 반품률 등을 고려하여 정해야 합니다.
7. 상품별 반품 원인 분석하기
반품률만 계산하는 것보다 반품 사유까지 기록하면 상품 개선에 도움이 됩니다.
엑셀에 반품 사유 열을 추가하여 다음과 같이 관리할 수 있습니다.
| 상품명 | 반품수량 | 주요 반품 사유 |
|---|---|---|
| 여성 티셔츠 | 12 | 색상 차이 |
| 여성 청바지 | 18 | 사이즈 불일치 |
| 남성 셔츠 | 9 | 단순 변심 |
| 운동화 | 15 | 착용감 불만 |
| 가디건 | 4 | 배송 지연 |
위 표의 반품 사유는 설명을 위한 예시입니다.
여성 청바지의 반품률이 높다면 상세페이지의 허리둘레, 엉덩이둘레, 총장 등 실측 정보를 보완할 필요가 있습니다.
운동화는 발볼이나 착용감에 대한 안내를 추가하는 것이 도움이 될 수 있습니다.
반품 사유가 여러 개인 경우에는 각 반품 건을 별도로 기록하고 사유별 건수를 집계해야 정확한 분석이 가능합니다.
8. 반품률과 실제 손실금액 함께 계산하기
반품률이 높아도 판매금액이 적으면 손실 규모가 작을 수 있습니다. 반대로 반품률이 낮더라도 고가 상품의 반품이 많으면 부담이 커질 수 있습니다.
예를 들어 상품별 판매가격과 반품수량을 이용해 반품 상품금액을 계산할 수 있습니다.
| 상품명 | 판매가격 | 반품수량 | 반품 상품금액 |
|---|---|---|---|
| 여성 티셔츠 | 20,000원 | 12 | 240,000원 |
| 여성 청바지 | 35,000원 | 18 | 630,000원 |
| 남성 셔츠 | 30,000원 | 9 | 270,000원 |
| 운동화 | 50,000원 | 15 | 750,000원 |
| 가디건 | 40,000원 | 4 | 160,000원 |
| 합계 | 58 | 2,050,000원 |
판매가격이 E열, 반품수량이 C열이라면 F2 셀에 다음 수식을 입력합니다.
=C2*E2
전체 반품 상품금액은 다음과 같이 계산합니다.
=SUM(F2:F6)
계산 결과는 2,050,000원입니다.
다만 이 금액은 반품된 상품의 판매가격 합계이며 실제 순손실액은 아닙니다.
실제 손실을 계산하려면 반품 배송비, 회수 비용, 재판매 가능 여부, 상품 훼손, 플랫폼 수수료 환급 여부 등을 추가로 반영해야 합니다.
9. 월별 반품률 변화 확인하기
상품별 반품률을 월별로 비교하면 개선 효과를 확인할 수 있습니다.
예를 들어 여성 청바지의 월별 반품률을 기록합니다.
| 월 | 판매수량 | 반품수량 | 반품률 |
|---|---|---|---|
| 7월 | 100 | 15 | 15.0% |
| 8월 | 120 | 15 | 12.5% |
| 9월 | 150 | 12 | 8.0% |
| 10월 | 180 | 9 | 5.0% |
7월에는 반품률이 15%였지만 10월에는 5%로 낮아졌습니다.
이처럼 월별 반품률을 기록하면 상품 상세페이지 수정이나 사이즈 안내 개선 이후 실제로 반품률이 낮아졌는지 확인하는 데 도움이 됩니다.
다만 판매 시점과 반품 접수 시점이 다를 수 있으므로 월별 비교에서는 집계 기준을 일관되게 유지해야 합니다.
10. 반품률 관리에서 주의할 점
반품률 계산 시 다음 사항을 확인해야 합니다.
첫째, 판매수량과 반품수량의 단위를 동일하게 사용해야 합니다. 주문 건수와 상품 개수를 섞어서 계산하면 안 됩니다.
둘째, 취소 주문과 실제 반품을 구분해야 합니다. 배송 전에 취소한 주문을 반품으로 처리하면 반품률이 실제보다 높아질 수 있습니다.
셋째, 교환 상품을 반품에 포함할지 미리 기준을 정해야 합니다. 교환과 반품을 구분하여 관리하면 고객 불만의 원인을 더 정확하게 분석할 수 있습니다.
넷째, 판매수량이 적은 상품은 반품 한두 건만 발생해도 반품률이 크게 변할 수 있습니다. 따라서 반품률뿐 아니라 실제 반품수량도 함께 확인해야 합니다.
다섯째, 반품률이 높다는 이유만으로 상품 판매를 즉시 중단하기보다는 반품 사유와 수익성을 함께 분석하는 것이 좋습니다.
11. 상품별 반품률 관리표 활용 방법
상품별 반품률 관리표는 한 번 만들어 두면 매월 판매수량과 반품수량만 변경하여 반복 사용할 수 있습니다.
특히 온라인 쇼핑몰에서는 다음과 같은 관리가 가능합니다.
- 반품률이 높은 상품 우선 점검
- 사이즈 불일치가 많은 상품의 상세정보 보완
- 배송 중 파손이 많은 상품의 포장 방법 개선
- 월별 반품률 변화 비교
- 반품 상품금액과 추가 비용 확인
- 상품별 수익성 개선 대상 선정
엑셀 수식과 챗GPT를 함께 활용하면 상품별 반품률 계산뿐 아니라 판매 데이터 분석과 개선 대상 선정까지 체계적으로 진행할 수 있습니다.
중요한 것은 단순히 반품률을 낮추는 것이 아니라 반품이 발생하는 원인을 찾아 고객 만족도와 실제 수익성을 함께 개선하는 것입니다.
답글 남기기