[고객 세그먼테이션] 데이터 전처리하기

jul ee·2025년 4월 9일

BigQuery customer segmentation sql 데이터사이언티스트 모두의연구소

데이터 성장기

목록 보기

28/139

🖇 데이터 불러오기
🖇 데이터 전처리: 결측치 처리
🖇 데이터 전처리: 중복값 처리
🖇 데이터 전처리: 오류값 처리

지금부터 진행할 '고객 세그먼테이션' 프로젝트는
Kaggle에 공개된 E-Commerce Data를 바탕으로 고객의 구매 행동 데이터에 기반해 RFM 분석 및 다양한 피처 엔지니어링을 수행한다. 이를 통해 고객을 정량적으로 세분화한 뒤 클러스터링 및 시각화를 거쳐 인사이트를 도출하는 것을 목표하고 있다.

사용한 기술 스택 및 분석 프로세스 등은 [고객 세그먼테이션] 프로젝트 마무리에 기술해 두었다.

🖇 데이터 불러오기

아래와 같은 순서로 빅쿼리에 data.csv 파일을 업로드하였다.

탐색기 옆 +추가 > 추가 - 인기 소스 - 로컬 파일

테이블을 만들 소스: 업로드

파일 선택: 찾아보기 클릭 후 data.csv 선택

파일 형식: CSV 선택

프로젝트:  프로젝트 명

데이터 세트: 새 데이터 세트 만들기 클릭

데이터 살펴보기

대용량 데이터를 분석할 때에는 데이터 업로드와 처리에 많은 시간이 걸리는 걸 방지하기 위해, 적당한 LIMIT을 넣어 데이터 일부를 먼저 확인해보는 것이 좋다.

일단 테이블에 있는 10개의 행만 출력해 보기

    SELECT *
    FROM project_name.dataset.data
    LIMIT 10;

전체 데이터는 몇 행으로 구성되어 있는지 확인하기

  SELECT COUNT(*) AS row_count
  FROM project_name.dataset.data;

  -- 541,909개의 행으로 구성되어 있음을 확인할 수 있다.

해당 데이터셋의 스키마도 살펴보자.

각 컬럼의 데이터 타입을 확인해 보면 연산이 가능한 숫자형 데이터인지, 아니면 텍스트 데이터인지와 같이 이해할 수 있기 때문에 보다 깊이 있게 분석할 수 있게 된다.

각각의 컬럼은 아래의 데이터를 가진다.

컬럼명	설명
InvoiceNo	각 거래의 고유 식별 코드
	'C'로 시작하면 해당 거래는 취소를 의미함
	하나의 InvoiceNo에는 여러 제품이 연결될 수 있음
StockCode	각 제품에 할당된 고유 코드
Description	각 제품에 대한 설명
Quantity	거래에서 구매한 제품 수량
InvoiceDate	거래가 발생한 날짜와 시간
UnitPrice	제품 1개당 가격 (영국 파운드 기준)
CustomerID	각 고객에게 부여된 고유 식별자
Country	주문이 발생한 국가

데이터 수 세기

현실에서 마주하게 되는 데이터는 항상 결측치나 이상치가 있는 noisy한 데이터셋이 많다. 그렇기 때문에 각 컬럼에 데이터가 꽉 차 있는지, 혹은 누락된 값이 있는지를 파악하는 것이 중요하다.

컬럼별로 데이터가 얼마나 있는지 데이터 포인트 수 확인하기

SELECT COUNT(InvoiceNo) AS COUNT_InvoiceNo,
       COUNT(StockCode) AS COUNT_StockCode,
       COUNT(Description) AS COUNT_Description,
       COUNT(Quantity) AS COUNT_Quantity,
       COUNT(InvoiceDate) AS COUNT_InvoiceDate,
       COUNT(UnitPrice) AS COUNT_UnitPrice,
       COUNT(CustomerID) AS COUNT_CustomerID,
       COUNT(Country) AS COUNT_Country
FROM project_name.dataset.data;

Description 컬럼과 CustomerID 컬럼에 누락된 값이 존재하는 것을 확인할 수 있다.

🖇 데이터 전처리: 결측치 처리

모든 데이터 분석의 시작은 전처리이다.

데이터가 깨끗해야 분석도 정직해지기 때문에, 분석할 데이터의 특성을 고려하여 전처리 방법을 설계해 보자.

다양한 데이터 전처리 방법은 데이터 전처리는 데이터 분석을 결정한다 해당 글을 참고해도 좋다 :)

결측치 확인

결측치(Missing Values)는 데이터 수집 과정에서 발생할 수 있으며, 실제 값이 존재하지 않거나 기록되지 않은 경우에 나타난다.

CASE WHEN 으로 각 컬럼에 대해 누락된 값을 구한 후, 누락 값을 UNION ALL 을 통해 합치는 방식으로 비율을 계산하였다.

컬럼별 결측치 비율 계산하기

-- InvoiceNo
SELECT 'InvoiceNo' AS column_name,
        ROUND(SUM(CASE WHEN InvoiceNo IS NULL THEN 1
                       ELSE 0
                  END) / COUNT(*) * 100, 2) AS missing_percentage
FROM project_name.dataset.data
UNION ALL
-- StockCode
SELECT 'StockCode',
        ROUND(SUM(CASE WHEN StockCode IS NULL THEN 1
                       ELSE 0
                  END) / COUNT(*) * 100, 2)
FROM project_name.dataset.data
UNION ALL

-- 생략 --

-- Country
SELECT 'Country',
        ROUND(SUM(CASE WHEN Country IS NULL THEN 1
                       ELSE 0
                  END) / COUNT(*) * 100, 2)
FROM project_name.dataset.data;

결측값에 대한 데이터를 확인해 보면
CustomerID 는 누락된 데이터가 24.93%, Description 은 누락된 데이터가 0.27%만큼 있음을 알 수 있다.

결측치 처리 전략

CustomerID (24.93%)

CustomerID는 고객을 클러스터링할 때 필수적인 정보인데, 결측치의 비중이 약 4분의 1이나 된다.

이렇게 큰 비율의 누락된 값을 다른 값으로 대체하는 것은 분석에 상당한 편향을 주고 노이즈가 될 수 있다. 또한 궁극적으로 RFM 분석 기법에 따라 고객 세그먼테이션을 진행해야 하기 때문에 고객 식별자 데이터는 정확해야 한다. 따라서 누락된 CustomerID가 있는 행을 제거하는 것이 가장 합리적인 접근 방법으로 보인다.

| 💡 데이터 편향이란?
데이터가 특정 경향을 가지고 있어 전체적인 분석이나 결과가 왜곡되는 현상을 말한다.
| 💡 데이터 노이즈란?
데이터에 관련 없는 정보가 포함되어 있어 데이터의 질이 저하되고 분석의 정확도가 떨어지는 것을 말한다.

Description (0.27%)

Description은 결측치가 비교적 적다.

하지만 아래를 보면, 같은 제품(StockCode)이 항상 같은 상세 설명(Description)을 가지고 있지 않다는 데이터의 일관성 문제가 발견되었다.

    SELECT DISTINCT Description
    FROM project_name.dataset.data
    WHERE StockCode = '85123A';

이는 제품 설명(Description)이 일관적으로 기록되지 않은 오류로 해석할 수 있다.

일관성 결여를 고려할 때, StockCode를 기반으로 누락된 설명을 대체하는 것은 신뢰할 수 없을 가능성이 있다. 또한 누락된 비율이 0.27%로 매우 낮기 때문에 데이터의 일관성 문제가 후속 분석 과정에 영향을 주지 않게 하기 위해 누락된 Description이 있는 행을 제거하는 것이 현명할 것이라는 판단을 할 수 있다.

결측치 제거하기

결측치 제거하기

    DELETE FROM project_name.dataset.data
    WHERE CustomerID IS NULL
          OR Description IS NULL;

CustomerID와 Description의 결측값이 있는 행을 제거함으로써, 더 정돈되고 신뢰할 수 있는 데이터셋을 구축할 수 있게 된다.

🖇 데이터 전처리: 중복값 처리

중복값 확인

우선 중복된 행들이 있는지를 확인한 다음, 이를 해결하기 위한 가장 효과적인 전략을 선택해 보자.

중복된 행의 수를 확인하기

    SELECT COUNT(*) AS duplicate_count
    FROM (
      SELECT InvoiceNo, StockCode, Description, Quantity, InvoiceDate,
             UnitPrice, CustomerID, Country,
             COUNT(*) AS cnt
      FROM project_name.dataset.data
      GROUP BY InvoiceNo, StockCode, Description, Quantity, InvoiceDate,
               UnitPrice, CustomerID, Country
      HAVING cnt > 1
    );

중복된 값이 있는 행이 총 4,837개 있는 것을 알 수 있다.

중복값 처리 전략

프로젝트의 맥락에서 완전히 동일한 행들, 특히 동일한 거래 시간을 포함한 동일한 행은 데이터 오류일 가능성이 높다. 이러한 중복 행을 유지하면 분석 결과에 영향을 줄 수가 있다. 따라서 데이터셋에서 완전히 동일한 중복 행들을 제거하도록 하겠다.

중복값 제거하기

    CREATE OR REPLACE TABLE `cproject_name.dataset.data` AS
    SELECT DISTINCT *
    FROM project_name.dataset.data;

중복값 처리 후 전체 데이터 행이 2,2190개로 줄었음을 알 수 있다.

🖇 데이터 전처리: 오류값 처리

지금까지 전체 데이터의 결측치와 중복값을 찾았다면, 이번에는 더 세부적으로 데이터를 살펴보자.

컬럼 단위로 데이터를 살펴보면서 클렌징이 필요한 값들이 있는지 확인해 보도록 하겠다.

`InvoiceNo` 살펴보기

고유(unique)한 InvoiceNo 개수 출력하기

    SELECT COUNT(DISTINCT InvoiceNo) AS unique_invoice_count
    FROM project_name.dataset.data;

중복값 처리 후 확인할 수 있었듯 고유한 InvoiceNo 를 가진 행은 2,2190개가 존재한다.

고유한 InvoiceNo를 100개를 출력해 보기

    SELECT DISTINCT InvoiceNo
    FROM project_name.dataset.data
    LIMIT 100;

앞서 각 컬럼과 데이터를 설명한 표에서도 설명했듯이, 취소된 거래의 InvoiceNo는 'C'로 시작한다는 것을 볼 수 있을 것이다.

고객 행동과 제품 선호도에 대한 이해를 높이기 위해서는 이렇게 취소된 거래들도 고려해야 한다. 이 행들을 분석하여 공통적인 특성이나 패턴이 있는지 파악할 수 있다.

최소된 거래 데이터의 비율(%) 구하기

    SELECT ROUND(SUM(CASE WHEN InvoiceNo LIKE 'C%' THEN 1
                          ELSE 0
                     END) / COUNT(*) * 100, 1) AS cancel_percent
    FROM project_name.dataset.data;
    
    -- cancel_percent는 전체의 2.2%인 것을 확인할 수 있었다.

최소된 거래 데이터 행을 필터링하기

    SELECT *
    FROM project_name.dataset.data
    WHERE InvoiceNo LIKE 'C%'
    LIMIT 100;

데이터를 자세히 보면 InvoiceNo 컬럼에서 'C'가 붙은 거래 취소 건들은 Quantity가 음수인 것을 알 수 있다.

InvoiceNo에 따른 추가 처리를 해야 할까?

실제 분석 과정에서는 분석가의 주관적 판단에 의해 전처리 여부와 그 방식을 결정 짓는다고 한다. 위의 경우 프로젝트의 초기 목표가 고객들의 구매 최신성, 구매 빈도, 구매 금액에 따라 세그멘테이션하는 것이었기 때문에, 고객의 취소 패턴을 이해하는 것도 중요할 것이다. 예를 들어, 취소된 거래에 공통점이 있는지 살펴보거나, 추천될 가능성이 높은 제품을 찾아서 문제를 진단할 수도 있다.
따라서 취소된 거래 데이터는 유지하되, 명확하게 표시하여 추가 분석을 용이하게 만들어주는 것도 전략 중 하나이다.

`StockCode` 살펴보기

StockCode는 각 제품에 할당된 고유 코드이다.

고유한 StockCode 개수 출력하기

    SELECT COUNT(DISTINCT StockCode) AS unique_stock_count
    FROM project_name.dataset.data;
    
    -- 총 3,684개의 제품들이 있는 것을 알 수 있다.

어떤 제품이 가장 많이 판매되었는지 보기 위해

StockCode별 등장 빈도 확인하기

    SELECT StockCode, 
           COUNT(*) AS sell_cnt 
    FROM project_name.dataset.data
    GROUP BY StockCode
    ORDER BY sell_cnt DESC
    LIMIT 10;

가장 판매가 많이 일어난 상위 10개의 제품 코드를 자세히 살펴보면, 고객들이 자주 구매하는 인기 제품군이나 카테고리에 대한 인사이트도 얻을 수가 있다.

제품 코드는 대부분 5~6자리의 숫자와 문자 조합으로 구성되어 있는 반면, 'POST'처럼 다른 형태의 제품 코드도 보인다. 이러한 이상 현상은 실제 제품보다는 서비스나 배송비 같은 형태를 코드로 남긴 것일 수도 있다.

현재 진행하고 있는 프로젝트는 고객들의 제품 구매에 초점이 맞춰져 있기 때문에 이런 값들은 제거하는 것이 좋을 것이다.

'POST'와 같은 이상치들이 몇 개나 있는지 확인하기 위해 StockCode의 문자열 내 숫자의 길이를 살펴보자.

StockCode의 문자열 내 숫자의 길이 구하기

    WITH UniqueStockCodes AS (
      SELECT DISTINCT StockCode
      FROM project_name.dataset.data
    )
    # REGEXP_REPLACE(): 텍스트를 처리하는 정규 표현식
    # 특정 조건에 부합한 텍스트를 다른 텍스트로 대체한다.
    SELECT LENGTH(StockCode) - LENGTH(REGEXP_REPLACE(StockCode, r'[0-9]', '')) AS number_count,
           COUNT(*) AS stock_cnt
    FROM UniqueStockCodes
    GROUP BY number_count
    ORDER BY stock_cnt DESC;

출력 결과를 보면,

8개를 제외하고는 StockCode에 5개의 숫자들이 포함되어 있고, 숫자가 0개인 코드는 7개, 숫자가 1개인 코드는 1개인 것을 알 수 있다.

StockCode의 컬럼에 있던 값 중에서 숫자를 제외한 문자만 남기고 문자가 몇 자리 수 인지 세고,
숫자가 0~1개인 값들에는 어떤 코드들이 들어가 있는지를 확인해 보자.

숫자가 0~1개인 값들의 제품 코드 출력하기

    SELECT DISTINCT StockCode, number_count
    FROM (
      SELECT StockCode,
             LENGTH(StockCode) - LENGTH(REGEXP_REPLACE(StockCode, r'[0-9]', '')) AS number_count
      FROM project_name.dataset.data
    )
    WHERE number_count <= 1
    ORDER BY number_count, StockCode;

숫자가 0~1개인 값들을 가지고 있는 데이터 수는 전체 데이터 수 대비 몇 퍼센트인지 구하기

    WITH StockNumberCount AS (
      SELECT *,
             LENGTH(StockCode) - LENGTH(REGEXP_REPLACE(StockCode, r'[0-9]', '')) AS number_count
      FROM project_name.dataset.data
    ),
    StockNumberFilter AS (
      SELECT *
      FROM StockNumberCount
      WHERE number_count <= 1
    )
    SELECT ROUND(COUNT(*) /
          (SELECT COUNT(*)
           FROM project_name.dataset.data) * 100, 2) AS percent_of_total
    FROM StockNumberFilter;

분석에 따르면 전체 데이터 중 매우 작은 비율인 0.48%의 데이터가 일반적인 형식에서 벗어난 값을 가지고 있었습니다.
이 코드들은 'BANK CHARGES, POST' 등 제품과 관련되지 않은 거래 기록으로 보인다.

진행하고 있는 프로젝트의 목표는 고객들의 '제품 구매'에 기반하여 세그먼테이션을 하는 것이므로, 이런 StockCode가 포함된 일반적인 형식에서 벗어난 기록은 데이터셋에서 제외하도록 하겠습니다.

이처럼 각 StockCode 내 숫자의 개수를 살펴보면서 값의 특성에 대한 통찰력을 얻을 수 있었다.

이제 제품과 관련되지 않은 거래 기록을 제거해 보자.

제품과 관련되지 않은 거래 기록을 제거하기

    DELETE FROM project_name.dataset.data
    WHERE StockCode IN (
      SELECT StockCode
      FROM (
        SELECT DISTINCT StockCode,
               LENGTH(StockCode) - LENGTH(REGEXP_REPLACE(StockCode, r'[0-9]', '')) AS number_count
        FROM capable-sled-456102-t0.modulabs_project.data
      )
      WHERE number_count <= 1
    );

`Description` 살펴보기

이번엔 Description 컬럼을 더 깊이 있게 살펴보겠습니다.

고유한 Description 별 출현 빈도를 계산하고 상위 30개를 출력하기

    SELECT Description,
           COUNT(*) AS description_cnt
    FROM project_name.dataset.data
    GROUP BY Description
    ORDER BY description_cnt DESC
    LIMIT 30;

Description을 살펴보면 모든 설명이 대문자로 되어 있는데, 이는 데이터베이스에 제품 설명을 입력할 때 사용되는 표준화된 형식일 수도 있다.

그러나 혹시라도 대문자와 소문자가 혼합된 스타일로 입력된 설명이 있는지 확인해 보는 것이 현명할 것이다.

대소문자가 혼합된 Description이 있는지 확인해 보기

    SELECT DISTINCT Description
    FROM project_name.dataset.data
    # REGEXP_CONTAINS(): 특정 패턴이 문자열에 포함되어 있는지 여부 확인
    WHERE REGEXP_CONTAINS(Description, r'[a-z]');

빅쿼리에서 실행 결과의 스크롤을 내리면 총 19개의 Description이 대소문자를 혼합하고 있다는 것을 알 수 있다.

출력 결과를 보면 사이즈(cm)나 무게(g) 등의 단위를 나타내거나, 'Next Day Carriage'나 'High Resolution Image'처럼 실제 제품에 대한 Description이 아닌 것도 있는 것을 알 수 있다. 이 데이터들은 실제 제품 정보와 관련이 없어 보인다. 아마도 다른 유형의 정보나 서비스 세부사항을 나타내는 것일수도 있다.

이를 처리하기 위한 몇가지 전략들을 생각해볼 수 있다.

대소문자를 혼합해서 사용하는 경우, 대문자로 표준화하여 데이터셋 전체에서 일관성을 유지할 수 있다. 이는 대소문자에 의한 중복 항목의 가능성을 줄이는 데에도 도움이 될 것이다.

따라서 'Next Day Carriage'와 'High Resolution Image'와 같은 서비스 관련 정보를 포함하는 행들을 제거하도록 하겠다.

이러한 전략을 선택함으로써, 데이터셋의 품질을 향상시킬 수 있고 프로젝트의 분석 단계에 더 적합한 데이터셋을 완성할 수 있다.

서비스 관련 정보를 포함하는 행들을 제거하기

    DELETE FROM project_name.dataset.data
    WHERE Description IN (
      'Next Day Carriage',
      'High Resolution Image'
    );

대소문자를 혼합하고 있는 데이터를 대문자로 표준화 하기

    CREATE OR REPLACE TABLE project_name.dataset.data AS
    SELECT * EXCEPT (Description),
           UPPER(Description) AS Description
    FROM project_name.dataset.data

`Quantity`와 `InvoiceDate`에 대한 데이터를 분석했을 때에는 크게 문제가 되는 이상 데이터가 없어 넘어가도록 하겠다.

데이터를 분석할 때 전처리하면 좋을 것 같은 데이터가 나타난다면, 분석가나 과학자의 판단에 따라 처리 방법을 선택할 수 있다.

`UnitPrice` 살펴보기

이번에는 UnitPrice에서 이상치를 찾아보자.

최솟값, 최댓값, 평균 데이터를 확인해 봄으로써, 단위 가격의 요약 통계량을 살펴보겠다.

단위 가격(UnitPrice)이란?
상품 1개당 가격, 즉 "단위 당 가격"을 의미한다.

UnitPrice의 최솟값, 최댓값, 평균 구하기

    SELECT MIN(UnitPrice) AS min_price,
           MAX(UnitPrice) AS max_price,
           AVG(UnitPrice) AS avg_price
    FROM project_name.dataset.data;

단위 가격의 요약 통계량을 보면 최소 단위 가격(min_price)이 0인 것을 알 수 있다.
이는 단가가 0원인 데이터가 존재한다는 것을 의미하고, 이는 이 제품이 무료 제품이거나 데이터 오류일 수도 있다는 의미이다.

단가가 0원인 거래의 성격을 제대로 이해하기 위해서는 데이터를 더 자세히 살펴볼 필요가 있다. 단가가 0원인 제품을 상세하게 분석해보면서 특정한 패턴이 있는지 살펴보겠다.

단가가 0원인 거래의 개수, 구매 수량(Quantity)의 최솟값, 최댓값, 평균 구하기

    SELECT COUNT(*) AS cnt_quantity,
           MIN(Quantity) AS min_quantity,
           MAX(Quantity) AS max_quantity,
           AVG(Quantity) AS avg_quantity
    FROM project_name.dataset.data
    WHERE UnitPrice = 0;

UnitPrice가 0인 행의 수는 33개로 비교적 적음을 알 수 있다. 구매 수량(Quantity)은 최소 1개부터 최대 12,540개에 이르기까지 굉장히 큰 편차를 가진다.

데이터의 수가 적은 걸 보니 무료 제품이라기보다 데이터 오류일 가능성이 더 높을 것 같다. 그래서 이 데이터(UnitPrice = 0)를 제거하고 일관된 데이터셋을 유지하도록 하겠다.

UnitPrice = 0를 제거하고 일관된 데이터셋을 유지하기

    CREATE OR REPLACE TABLE project_name.dataset.data AS 
    SELECT *
    FROM cproject_name.dataset.data
    WHERE UnitPrice > 0;

나머지 컬럼들에 대해서도 이와 같은 순서로 분석을 진행하면 된다.

다만 다른 컬럼들에 대해서는 크게 오류값 처리를 할 것이 없기 때문에, 오류값 처리는 여기까지 하고 넘어가도록 하겠다.

jul ee

AI에 관심을 가지고, 데이터로 가치를 만들어 나가는 과정을 기록합니다.

[고객 세그먼테이션] 데이터 전처리하기

데이터 성장기

🖇 데이터 불러오기

데이터 살펴보기

데이터 수 세기

🖇 데이터 전처리: 결측치 처리

결측치 확인

결측치 처리 전략

결측치 제거하기

🖇 데이터 전처리: 중복값 처리

중복값 확인

중복값 처리 전략

🖇 데이터 전처리: 오류값 처리

`InvoiceNo` 살펴보기

`StockCode` 살펴보기

`Description` 살펴보기

`UnitPrice` 살펴보기

데이터 전처리는 데이터 분석을 결정한다

[고객 세그먼테이션] RFM 지표로 고객별 구매 패턴 분석하기

0개의 댓글

[고객 세그먼테이션] 데이터 전처리하기

데이터 성장기

🖇 데이터 불러오기

데이터 살펴보기

데이터 수 세기

🖇 데이터 전처리: 결측치 처리

결측치 확인

결측치 처리 전략

결측치 제거하기

🖇 데이터 전처리: 중복값 처리

중복값 확인

중복값 처리 전략

🖇 데이터 전처리: 오류값 처리

InvoiceNo 살펴보기

StockCode 살펴보기

Description 살펴보기

UnitPrice 살펴보기

데이터 전처리는 데이터 분석을 결정한다

[고객 세그먼테이션] RFM 지표로 고객별 구매 패턴 분석하기

0개의 댓글

`InvoiceNo` 살펴보기

`StockCode` 살펴보기

`Description` 살펴보기

`UnitPrice` 살펴보기