2020. 12. 4. 01:42ㆍ데이터 분석/SQL
이번 글은 "데이터 분석을 위한 SQL 레시피" 도서와 생활코딩 동영상 강의를 참고하여 정리한 글입니다.
"데이터 분석을 위한 SQL 레시피"는 데이터 처리부터 매출, 유저 파악, 리포팅 등 각종 데이터 분석과 활용에 관한 내용을 담고 있는 책입니다. 이 책은 단순하게 빅데이터와 관련된 SQL만 다루는 것이 아닌 데이터 분석자가 해야 할 일을 전반적으로 다루고, 다양한 지표를 추출하기 위한 SQL 내용도 포함되어 있습니다.
"생활코딩"은 이고잉님이라는 익명의 프로그래머가 운영하는 비영리 코딩 교육 사이트입니다.
순서
1. SQL과 NoSQL?
2. 업무 데이터와 로그 데이터
2.1 업무 데이터
2.2 로그 데이터
2.3 데이터 적절하게 활용하기
1. SQL과 NoSQL
SQL(Structured Query Language)과 NoSQL(Non Structured Query Language)은 DataBase(DB)와 대화하기 위한 언어로 데이터를 저장, 수정, 삭제 및 검색하기 위한 기초적인 언어입니다. DB는 엄격한 데이터 스키마를 가지고 관계를 통해서 연결된 여러 테이블에 데이터가 저장되어 있는 RDB(Relational DataBase)와 스키마와 관계가 없는 Non Relational DataBase로 나뉩니다. 각각 DB는 다른 언어를 이용하여 대화해야 하고SQL은 Relational DataBase와 대화하는 언어, NoSQL은 Non Relational DataBase와 대화하는 언어입니다. SQL에는 MySQL, sqlite, PostgreSQL이 있고 NoSQL에는 mongoDB, DynamoDB가 있습니다.
SQL은 다른 컴퓨터 언어보다 쉽고 매우 중요한 언어입니다. 분석 담당자로서 SQL을 모른다면 SQL을 사용하면서 겪는 시행착오에 시간이 더 오래 걸려서 보고서의 품질을 담보할 수 없게 됩니다.
2. 업무 데이터? 로그 데이터?
마케팅 담당자가 다루는 데이터를 크게 2가지로 구분하면 업무에 필요한 업무 데이터와 업무에 직접적으로 필요하지 않지만 분석을 위해 추출해야 하는 로그 데이터로 나눌 수 있습니다.
업무 데이터는 서비스와 시스템을 운용하기 위한 목적으로 구축된 DB에 존재하는 데이터입니다. 업무 데이터 대부분이 갱신형 데이터입니다. 가격 변동과 같이 새로운 데이터를 삽입하는 대신 기존의 데이터를 갱신하는 데이터입니다. 업무 데이터는 트랜잭션 데이터와 마스터 데이터로 나눌 수 있습니다.
2.1 업무 데이터
트랜잭션 데이터는 구매 데이터, 리뷰 데이터처럼 사용자의 행동과 같은 특정 시점에 발생한 모든 이벤트의 내역이 기록된 데이터입니다. 날짜, 시각, 회원 ID, 상품 ID, 수량, 가격 등이 포함됩니다. 마스터 데이터는 카테고리 마스터, 상품 마스터처럼 서비스와 시스템이 정의하고 있는 데이터입니다.
트랜잭션 데이터만으로는 분석 범위가 한정되어 버리므로 트랜잭션 데이터에 포함된 마스터 데이터는 리포트 업무 전에 대충 확인하는 것이 좋습니다.
특징
1. 업무 데이터는 데이터의 정밀도가 높습니다. 데이터 처리 중 문제가 발생하면 트랜잭션과 롤백 기능을 이용하여 문제를 해결할 수 있다.
2. 사용자가 탈퇴하는 경우 데이터를 제거하거나 사용자의 주소가 변경 시 정보를 갱신하는 등 데이터 추가, 갱신, 제거된다는 특징이 있습니다. 데이터 추출 시점에 따라 추출되는 데이터가 바뀔 수 있다는 것을 주의해야 한다.
3. ER 다이어그램이라고 부르는 데이터 구조를 나타낸 설계 문서를 파악하고 여러 테이블을 결합하여 전체 데이터를 파악해야 한다.
언제 사용하는가?
업무 데이터는 트랜잭션 기능으로 데이터의 정합성이 보장되기 때문에 추출 결과를 신뢰할 수 있습니다. 정확한 값을 요구할 때는 업무 데이터를 사용합니다. 하지만 데이터가 변경될 수 있으므로 추출 시점에 따라 추출 결과가 바뀔 수 있기 때문에 데이터가 모든 레코드의 스냅샷을 날짜별로 저장되어 축적된 건지, 어제와의 차이만 누적되어 축적된 건지 등 어떻게 축적되는지 이해할 필요가 있습니다. 리포트를 제출할 때는 추출 시점의 정보를 기반으로 작성된 리포트다라고 명시를 해줘야 혼동을 막을 수 있습니다. 업무 데이터 변경의 영향을 최소화하고 리포트 요건에 따라 적절하게 데이터를 축적할 수 있는 방법을 찾는 것이 좋습니다.
어떤 상품이 계절성을 가지는지, 특정 시간에 많이 팔리는지 등을 분석하면 구매율을 높이기 위해 무엇을 해야 하는지 파악할 수 있습니다. (예를 들면 적절한 이벤트를 준비하는 것과 같은)
2.2 로그 데이터
로그 데이터는 웹 서버의 접근 로그 또는 파일로서 저장된 데이터입니다. 출력 시점의 정보를 축적해두는 누적형 데이터로 사용자 정보가 변경되더라도 기존의 데이터가 모두 남아있습니다.
특징
1. 서비스와 시스템을 구축할 때 필요한 데이터인 업무 데이터와 달리 서비스의 처리에 영향이 거의 없는 사용자 IP주소, Cookie 등의 정보가 포함된다.
2. 추출 방법, 집계 대상 데이터가 어떠한 상태로 있는지에 따라 데이터의 정밀도가 달라진다.
3. 기록이 계속 추가되더라도 과거 데이터가 수정되지 않는다.
언제 사용하는가?
로그 데이터는 열람 페이지, 사용자 에이전트 등이 저장되어 있기 때문에 사이트 방문 횟수, 페이지뷰, 액션 수, 장치별
방문 수 등의 지표를 구할 때 사용할 수 있습니다. 누적형 데이터로 수정되거나 삭제되는 데이터가 없으므로 기간을 지정해서 집계하면 결과가 바뀌지 않습니다. 또한 로그 추출 방법에 따라서 사용자가 누락되거나 크롤러의 로그가 함께 포함되어 집계될 수도 있기 때문에 정확한 값이 필요한 경우에는 업무 데이터를 사용해야 합니다. 로그 데이터를 활용할 땐 어떤 데이터인지 파악하고 어떤 방법으로 사용할지, 설명할 범위 등을 명확하게 결정해야 합니다.
그렇다면 로그 데이터는 어떻게 축적해야 할까요?
로그 데이터를 전송하고 축적하는 방법은 크게 2가지입니다. GA(Google Analytics)처럼 HTML에 특정 태그를 추가하여 데이터를 전송하는 비컨 형태와 서버에서 데이터를 추출하고 출력하는 서버 형태입니다. 비컨 형태는 웹사이트에서 자바스크립트를 통해 로그 데이터를 전송하는 경우, 자바스크립트를 해석할 수 없는 크롤러나 브라우저의 데이터는 로그로 출력되지 않아 크롤러의 영향을 적게 받습니다. 하지만 서버 형태는 클라이언트 쪽에서 별도의 처리를 하지 않는 방법으로 서버에 요청이 있을 때 출력하므로 크롤러의 접근을 확인하고 조건을 걸지 않으면 크롤러의 접근도 함께 출력됩니다. 이러한 데이터를 사용해서 사용자의 행동을 집계하거나 분석하면 잘못된 결론을 도출할 수도 있어서 서버 형태로 출력한 데이터는 의도하지 않은 로그가 있는지 확인하고 제거해야 합니다. 웹사이트에 비컨 형식으로 탑재되어 축적된 로그 데이터는 로그를 전송하므로 로그 기반으로 리포트를 제공합니다. 데이터 수집, 가공, 집계를 자유롭게 할 수 있으므로 접근 분석 도구의 제한을 받지 않고 원하는 결과를 도출할 수 있다는 장점이 있습니다.
2.3 데이터 적절하게 활용하기
웹사이트에서 오프라인으로 사용자를 유도하는 서비스라면, 로그 데이터와 업무 데이터는 함께 활용했을 때 분석 가능성이 더 넓어집니다. 로그 데이터는 주로 웹사이트에서의 행동이 기록되어있고 업무 데이터는 웹사이트뿐만 아니라 오프라인에서의 데이터도 기록되기 때문입니다. 두 데이터 모두 사용하여 웹사이트에서의 행동이 오프라인에서 어떤 영향을 미치는지 파악하여 서비스를 더 잘 운영할 수 있습니다. 예를 들어 특정 광고로 유입된 사용자가 오프라인에서 계약할 가능성이 높다면, 해당 광고를 더 활용하는 방안을 마련할 수 있습니다.
가치 있는 데이터 분석을 통해 사용자와 서비스(조직)에 가치를 제공할 수 있어야 합니다. 가치 있는 데이터 분석은 어떻게 할 수 있을까요?
1. 서비스가 지향하는 목표가 현재 어느 정도 진행되었는지 파악하고 부족하 경우 시책을 검토하고 실시하여 서비스의 성장에 기여한다.
2. 사용자의 행동을 기반으로 경향을 발견하고 매출 향상과 서비스 개선에 기여한다.
3. 과거의 경향을 기반으로 미래의 행동을 예측한다. 예를 들어 특정 행동을 하는 사용자가 탈퇴하는 경향이 있다면 사전에 대응하거나 추천 시스템을 이용하는 등.
SQL에 대해 알아보고 SQL을 이용하여 가치 있는 데이터 분석을 하기 위해 업무 데이터와 로그 데이터의 차이와 사용 시 주의할 점을 알아보았습니다. 다음 글에서는 SQL문법을 간략하게 설명하고 직접 SQL을 이용하여 데이터를 가공하고 분석해보겠습니다.
그림 참고