여러 CSV 파일 병합 및 레코드 수 집계 방법

모든 시스템, 쿼리, 캠페인 또는 보고 기간이 각각 별도의 CSV로 내보내질 때, 가장 어려운 부분은 집계 그 자체가 아닙니다.

진짜 어려운 부분은 데이터의 의미를 잃지 않으면서 파일들을 통합하는 것입니다.

이 글은 여러 CSV 파일에 걸친 항목을 병합하고 집계하는 방법에 대한 실제 Super User 질문을 바탕으로 작성되었습니다. 질문자는 이메일 주소가 담긴 마스터 CSV와 50개 이상의 쿼리 CSV 파일을 가지고 있었습니다. 각 쿼리 파일에는 해당 쿼리에 응답한 이메일 주소들이 들어 있었고, 최종 목표는 각 이메일 주소가 몇 개의 쿼리 파일에 응답했는지 보여주는 마스터 리스트를 만드는 것이었습니다.

이것은 매우 실무적인 스프레드시트 문제입니다. 설문 조사, 캠페인, 쿼리, 제품, 벤더, 티켓 대기열 또는 보고 기간마다 하나의 CSV를 내보내는 팀이라면 어디서나 발생할 수 있는 일입니다.

어떤 사용자는 모든 내보내기 파일에 나타나고, 어떤 사용자는 몇 군데에만 나타납니다. 어떤 쿼리 파일은 헤더가 다를 수도 있고, 응답이 중복될 수도 있습니다. 예상했던 사용자가 전혀 나타나지 않을 수도 있죠. 질문은 간단해 보이지만, 실제 작업 과정은 꽤나 복잡합니다.

유용한 결과물은 단순한 숫자가 아닙니다. 어떤 레코드가 일치했는지, 얼마나 자주 나타났는지, 어떤 파일에서 누락되었는지, 그리고 어떤 행을 검토해야 하는지를 보여주는 요약 보고서여야 합니다.

이와 똑같은 문제가 복잡한 RSVP(응답) 명단에서도 발생합니다. 어떤 파일은 Yes를 사용하고, 다른 파일은 Y, 또 다른 파일은 X를 사용할 수 있습니다. 사용자 응답 보고서의 경우, 이러한 변형된 값들을 집계 전에 표준화하는 응답 상태 규칙이 반드시 선행되어야 데이터의 신뢰성을 확보할 수 있습니다.

정확한 소스 구조에서 시작하기

Super User 스타일의 문제에는 두 가지 종류의 파일이 있습니다.

가장 안전한 출력 형태는 단순히 email과 reply_count만 있는 것이 아닙니다. 더 나은 결과물에는 보통 다음 항목들이 포함됩니다.

• 이메일
• 이 이메일이 응답한 쿼리 파일의 수
• 이메일이 나타난 소스 파일 목록
• 이메일이 누락된 예상 쿼리 파일 목록
• 중복 응답 플래그
• 검토 메모

이러한 구조는 사용자의 실제 질문에 답하는 동시에 결과의 감사(Audit)가 가능하도록 해줍니다.

병합 전 집계 규칙 정의하기

무엇이든 병합하기 전에, '집계'가 무엇을 의미하는지 정의하십시오.

다음을 집계하고 싶으신가요?

• 모든 파일에 걸친 사용자당 총 응답 수?
• 각 사용자가 나타난 파일의 개수?
• 쿼리 그룹당 응답 수?
• 내보내기당 고유 사용자 수?
• 특정 날짜 범위 내의 응답 수?

이것들은 모두 서로 다른 보고서입니다.

집계 규칙이 불분명하면 표는 그럴싸해 보이지만 엉뚱한 질문에 답하는 결과가 나올 수 있습니다.

예를 들어, 동일한 이메일이 query_07.csv에 두 번 나타난다면, 이를 한 번의 응답으로 쳐야 할까요, 아니면 두 개의 응답 행으로 쳐야 할까요? 원래 질문의 경우, 비즈니스 규칙은 아마도 '쿼리 파일당 사용자별 1회 집계'일 것입니다. 즉, 한 파일 내의 중복은 무조건 두 번 세는 것이 아니라 검토 대상으로 표시해야 합니다.

워크북을 만들기 전에 이 규칙을 평이한 글로 먼저 작성해 보세요.

CSV 내보내기 파일 목록 먼저 파악하기

각 파일을 다른 파일과 완벽하게 일치하지 않을 수 있는 소스로 취급하십시오.

모든 CSV에 대해 다음 사항을 기록하세요.

• 소스 또는 쿼리 이름
• 날짜 범위
• 행 수
• 사용자 식별자 필드
• 응답 필드
• 파일별 필터
• 누락된 컬럼
• 중복 레코드
• 명명 규칙의 불일치

이 단계에서 진짜 문제가 드러나는 경우가 많습니다. 어떤 파일은 사용자 이름을 사용하고 다른 파일은 사용자 ID를 사용할 수 있습니다. 어떤 파일은 응답당 한 행인 반면, 다른 파일은 사용자당 한 행에 집계 필드가 있을 수도 있습니다.

필드가 정렬되지 않으면 집계 로직은 깨지게 됩니다.

집계 전 사용자 식별 정보 정규화하기

사용자 이름은 안정적인 식별자가 아닙니다.

가능하다면 표시 이름보다는 고유 ID로 집계하십시오. 이름만 사용할 수 있는 경우, 다음 사항을 정규화하는 매핑 테이블을 만드세요.

• 대소문자
• 공백
• 문장 부호
• 별칭(Alias)
• 대체 철자
• 누락된 접두사 또는 접미사

이는 한 사용자가 여러 CSV 파일에 나타날 때 특히 중요합니다. 이름 하나가 일치하지 않으면 한 사람의 데이터가 두 개의 서로 다른 집계로 나뉠 수 있습니다.

안정적인 사용자 ID가 없다면 보고서에 그 사실을 명시하십시오. 집계 수치가 여전히 유용할 수는 있지만, 확실성은 떨어집니다.

최종 결과물을 가공되지 않은 스프레드시트가 아니라 검토 및 공유용으로 만들어야 한다면, 가벼운 AI 보고 워크플로우를 활용하기 좋은 지점입니다.

요약하기 전 통합 테이블 구축하기

곧바로 합계로 건너뛰지 마세요.

먼저 다음과 같은 컬럼을 가진 하나의 작업용 테이블로 파일들을 통합하십시오.

• 소스 파일
• 사용자 ID 또는 정규화된 사용자 이름
• 응답 수
• 응답 텍스트 또는 상태
• 날짜
• 쿼리 또는 그룹 레이블
• 검토 플래그

데이터가 통합되면 다음을 계산할 수 있습니다.

• 사용자당 총 응답 수
• 사용자당 파일 수
• 파일당 평균 응답 수
• 누락된 파일 참여도
• 이상치 사용자
• 중복 레코드

이러한 구조는 보고서 감사를 쉽게 만듭니다. 또한 요약된 모든 행을 소스 CSV까지 추적할 수 있는 방법을 제공합니다.

응답 집계 보고서의 경우, 통합 작업 테이블은 다음과 같을 수 있습니다.

그러면 마스터 요약은 다음과 같은 모습이 됩니다.

누락된 사용자 별도로 검토하기

누락된 사용자가 집계 결과 속으로 사라져서는 안 됩니다.

사용자가 한 파일에는 나타나고 다른 파일에는 나타나지 않는 것은 정상일 수도 있고, 내보내기가 불완전하다는 의미일 수도 있습니다.

다음을 위한 별도의 검토 리스트를 만드세요.

• 일부 파일에서 누락된 사용자
• 예상 사용자의 레코드가 없는 파일
• 식별자가 일치하지 않는 사용자
• 행 수가 비정상적인 내보내기 파일
• 정상적으로 로드되지 않은 파일

이는 보고서를 검토하는 사람이 낮은 집계 수치가 실제 신호인지 아니면 단순한 데이터 문제인지를 이해하는 데 도움이 됩니다.

이 워크플로우가 매달 또는 매주 반복된다면, 파일 처리와 보고 단계가 일관되게 유지되도록 더 넓은 범위의 월간 CSV 보고 워크플로우에 연결하십시오.

RowSpeak에게 해결 방법 요청하기

RowSpeak는 CSV 파일이 너무 복잡해서 집계 로직이 계속 바뀌거나, 팀에서 파워 쿼리(Power Query) 단계를 직접 구축하고 싶지 않을 때 적합합니다.

마스터 CSV와 쿼리 응답 CSV 파일들을 함께 업로드할 수 있습니다. RowSpeak는 한 번의 채팅에서 30개 이상의 파일을 포함한 다중 파일 워크플로우를 지원하므로, 쿼리 배치, 캠페인 배치, 내보낸 보고서 폴더 등을 처리하기에 아주 좋습니다.

효과적인 프롬프트에는 파일 설명, 집계 규칙, 그리고 필요한 출력 탭을 명시해야 합니다.

마스터 사용자 파일 하나와 여러 개의 쿼리 응답 CSV 파일을 업로드했습니다.

마스터 파일의 'email' 컬럼에는 예상되는 전체 사용자 목록이 들어 있습니다.
각 쿼리 CSV에는 해당 쿼리에 응답한 사용자들이 들어 있으며, 역시 'email'로 식별됩니다.

다음 시트들을 포함하는 다운로드 가능한 Excel 워크북을 만들어 주세요:
1. Master Reply Count: 마스터 리스트의 이메일당 한 행씩, 해당 이메일이 나타난 쿼리 파일의 수를 표시.
2. Combined Replies: 모든 쿼리 CSV 파일을 하나의 테이블로 합치고 'Source File' 컬럼 추가.
3. Missing Users Review: 각 이메일별로 응답이 없었던 쿼리 파일 목록 표시.
4. File QA: 각 소스 파일의 행 수, 중복 이메일, 누락된 이메일 값, 비정상적인 헤더 표시.

각 이메일은 쿼리 파일당 최대 한 번만 집계하세요. 동일한 쿼리 파일에 이메일이 두 번 나타나면 두 번 세지 말고 중복으로 표시하세요.
Copy

더 간단한 출력을 요청할 수도 있습니다.

이메일과 reply_count가 포함된 마스터 테이블을 만들어 주세요. 업로드된 쿼리 CSV 파일 중 각 이메일이 포함된 파일의 개수를 집계하세요. 마스터 사용자 리스트를 기준으로 전체 출력 리스트를 만들고, 응답이 0인 사용자도 포함해 주세요.
Copy

RowSpeak는 다음을 도와줄 수 있습니다.

• 올바른 집계 필드 식별
• 이름 또는 ID 정규화
• 파일을 검토 가능한 하나의 테이블로 통합
• 누락된 사용자 및 의심스러운 공백 표시
• 참여 패턴 요약
• 검토를 위한 보고서 뷰 생성

이는 일반적인 챗봇에게 단순히 "응답을 세어줘"라고 요청하는 것보다 훨씬 유용합니다. 문제는 산술적인 계산뿐만 아니라 파일 구조, ID 매칭, 그리고 설명에 있기 때문입니다.

최종 결과를 팀과 공유해야 한다면, RowSpeak는 통합된 데이터를 가공되지 않은 집계표 대신 더 읽기 쉬운 Excel-to-dashboard 워크플로우로 변환하는 것을 도와줄 수 있습니다.

유용한 RowSpeak 프롬프트는 단순히 합계를 묻는 것이 아니라, 응답 규칙과 검토 결과물의 이름을 명시해야 합니다.

사용자 응답 그 이상의 패턴 활용

중요한 패턴은 이렇습니다: 마스터 리스트, 다수의 내보내기 파일, 키(Key) 값으로 병합, 출현 횟수 집계, 그리고 누락 또는 중복 레코드 검토.

이 패턴은 비즈니스 팀 전반에서 나타납니다.

재무 팀의 경우:

• 어떤 코스트 센터(Cost Center)가 월간 예산 파일을 제출했는지 집계.
• 얼마나 많은 은행 명세서 내보내기 파일에 특정 거래 ID가 포함되어 있는지 집계.
• 여러 지출(AP) 내보내기 파일에서 벤더 송장을 대조하고 지급 실행에서 누락된 벤더 표시.

이커머스 팀의 경우:

• 각 SKU가 얼마나 많은 마켓플레이스 내보내기 파일에 포함되어 있는지 집계.
• 한 채널에는 있지만 다른 채널에는 없는 제품 식별.
• 여러 플랫폼 CSV에 걸친 반품, 리뷰 또는 환불 사례 집계.

마케팅 팀의 경우:

• 각 리드(Lead) 이메일이 얼마나 많은 캠페인 내보내기 파일에 포함되어 있는지 집계.
• 웨비나, 뉴스레터, 양식 응답 파일을 하나의 참여 점수로 통합.
• 유료 캠페인 파일에는 나타나지만 후속 응답 파일에는 나타나지 않는 리드 표시.

공급망 팀의 경우:

• 얼마나 많은 공급업체가 주간 확인 요청에 응답했는지 집계.
• 창고, 운송업체, 벤더 내보내기 파일 전체에서 배송 ID 대조.
• 수요 파일에는 있지만 가용 재고 파일에는 없는 SKU 표시.

각 사례에서 동일한 프롬프트 구조가 작동합니다. 마스터 리스트를 지정하고, 소스 파일을 지정하고, 무엇을 유효한 출현으로 간주할지 정의한 다음, RowSpeak에게 소스 파일 추적을 유지하도록 요청하세요.

실무적인 집계 워크플로우

다음 순서를 따르세요.

1. 집계 규칙 결정
   총 응답 수, 파일 참여도 또는 고유 사용자 수 중 무엇을 셀지 정합니다.

2. 모든 CSV 목록 파악
   헤더, 필드, 행 수 및 시간 범위를 기록합니다.

3. 사용자 식별 정보 정규화
   ID를 우선적으로 사용하고, 필요한 경우 이름을 표준화합니다.

4. 모든 파일을 하나의 테이블로 통합
   소스 파일을 확인할 수 있도록 유지합니다.

5. 요약 테이블 구축
   필요에 따라 응답, 파일 또는 참여도를 집계합니다.

6. 누락 사용자 검토 리스트 생성
   데이터 공백과 실제 활동 저조를 구분합니다.

7. 짧은 설명 추가
   독자에게 집계의 의미와 여전히 검토가 필요한 부분이 무엇인지 알려줍니다.

피해야 할 흔한 실수들

별칭(Alias)을 확인하지 않고 표시 이름만으로 집계하지 마세요.

모든 CSV가 동일한 행 구조를 가지고 있다고 가정하지 마세요.

누락된 사용자를 유효한 집계 테이블 안에 그냥 섞어버리지 마세요.

보고서가 응답 수, 사용자 수, 파일 수, 아니면 고유 출현 횟수 중 무엇을 집계한 것인지 설명하는 것을 잊지 마세요.

결론

여러 CSV 파일에 걸쳐 레코드를 병합하고 집계하는 것은 사실 '보고(Reporting)'의 문제입니다.

유용한 결과물은 누가 어디에 나타나는지, 얼마나 자주 나타나는지, 그리고 어떤 레코드를 주의 깊게 봐야 하는지를 보여주는 통합되고 검토 가능한 요약본입니다.

엑셀은 로직을 처리할 수 있고, 파워 쿼리는 이를 반복 가능하게 만들 수 있습니다. RowSpeak는 누락된 사용자나 복잡한 파일 구조를 놓치지 않으면서, 수많은 내보내기 파일을 팀과 공유할 수 있는 보고서로 바꾸고 싶을 때 가장 적합한 도구입니다.

시작하기: CSV 응답 내보내기 파일을 검토 가능한 보고서로 바꾸기

응답 데이터가 여러 CSV 파일에 흩어져 있다면, 해당 파일들을 RowSpeak에 업로드하고 집계 규칙을 일상적인 언어로 설명해 보세요. 파일을 통합하고, 사용자 식별 정보를 정규화하고, 응답을 집계하고, 누락되거나 의심스러운 레코드를 별도로 나열하도록 요청하세요.

지금 RowSpeak를 사용해 보세요. 수동으로 CSV를 집계하는 대신 팀이 실제로 검토할 수 있는 보고서를 만드실 수 있습니다.