Mandrill 데이터를 코딩 없이 ETL하기 2편
with Dataprep
1편에서 Mandrill 데이터를 Stitchdata라는 툴을 통해서 Google Bigquery에 넣는 방법을 소개했다.
이렇게 넣은 데이터는 주로 mandrill_events라는 컬럼에 들어 있다. 아래 내용이 유저가 이메일을 오픈한 mandrill 이벤트 예시이다.
[{"event":"open","ts":1536126328,"user_agent":"Mozilla\/5.0 (Linux; Android 8.0.0; SM-G950N Build\/R16NW; wv) AppleWebKit\/537.36 (KHTML, like Gecko) Version\/4.0 Chrome\/68.0.3440.91 Mobile Safari\/537.36;ref:nate_app;appver:5.2.4;ndruk:2018042020360748172;skai:20180428173121391ee;GadID:18a9760d-a7ba-4d25-81ee-742061148137","user_agent_parsed":{"type":"Mobile Browser","ua_family":"Android browser","ua_name":"Android browser 4.0","ua_version":"4.0","ua_url":"http:\/\/developer.android.com\/reference\/android\/webkit\/package-summary.html","ua_company":"Google Inc.","ua_company_url":"http:\/\/www.google.com\/","ua_icon":"http:\/\/cdn.mandrill.com\/img\/email-client-icons\/androidwebkit.png","os_family":"Android","os_name":"Android","os_url":"http:\/\/en.wikipedia.org\/wiki\/Android_%28operating_system%29","os_company":"Google, Inc.","os_company_url":"http:\/\/www.google.com\/","os_icon":"http:\/\/cdn.mandrill.com\/img\/email-client-icons\/android.png","mobile":true},"ip":"***.***.***.***","location":{"country_short":"KR","country":"Korea, Republic Of","region":"Seoul-t'ukpyolsi","city":"Seoul","latitude":**,"longitude":***,"postal_code":"-","timezone":"+09:00"},"_id":"***","msg":{"ts":1536105934,"_id":"***","state":"sent","subject":"***","email":"***","tags":[],"opens":[{"ts":1536126328,"ip":"***.***.***","location":"Seoul-t'ukpyolsi, KR","ua":"Mobile\/Android\/Android\/Android browser\/Android browser 4.0"}],"clicks":[],"smtp_events":[{"ts":1536105936,"type":"sent","diag":"250 2.5.0 Message accepted for delivery","source_ip":"***.***.***.***","destination_ip":"***","size":58177}],"resends":[],"_version":"LO5prLW11Jtqsl8q_dADDQ","sender":"***","template":"template-name"}}]
언제, 무슨 이메일, 누가 보낸 것, 누가 열었는지 등등의 데이터들이 mandrill_events 컬럼에 위와 같은 형태로 남아있다. json 타입이든, nested로 되어있든 사실 분석하려면 할 수는 있다. Bigquery에 데이터가 들어있다면 UNNEST를 쓰면 된다. 하지만 편하진 않다.
필요한 건 Mandrill 데이터를 받아오고 필요한 컬럼이 착착착 분석용 데이터베이스에 들어가 있는 것이다. 그 후엔 SQL query만 작성하면 된다. 그러나, stitchdata까지만 써서는 그렇게 되지 않는다.
Dataprep 데이터 정리를 위해서 이번에 소개할 툴은 Google Cloud의 Cloud Dataprep이라는 툴이다.
Dataprep은 눈으로 데이터를 보면서, 어떤 데이터를 어떻게 정리할지 정하고, 정한 것을 반복적으로 수행할 수 있게 도와준다. 간단하게 말하면 앞에서 원한 것처럼 착착착 컬럼으로 정리해준다. 무엇보다도 문서도 잘 정리되어있다. 위에 들어 있는 Mandrill 데이터를 정리해보자.
Import Data
Dataprep을 일단 Import Data부터 시작한다. Dataprep을 통해서 정리할 소스 데이터를 선택하는 일이다. csv 또는 xlsx 파일을 업로드해도 되고, 이미 Google Cloud Storage(GCS, 아마존으로 치면 S3)에 올라가 있는 파일을 선택해도 된다. Bigquery의 특정 table을 선택하는 것도 당연히 가능하다. 1편에서 Bigquery에 데이터를 넣어보았기 때문에 여기서 stitchdata를 통해 넣은 Bigquery 테이블을 선택하였다.
Create Flow
Dataprep의 예시 FlowDataprep에서 제공하는 예시 Dataset과 Flow이다. 왼쪽에는 소스가 되는 Dataset이 있고, 그걸 어떻게 / 어떤 순서로 변환 해갈 것인지 이렇게 그림으로 만들 수 있다. Create Flow를 처음 실행하면, Dataset에서 어떤 데이터를 이 Flow에서 컨트롤할지 선택하도록 되어있다. 앞에서 Import 한 데이터를 Dataset으로 선택하면 된다.
Dataset여기서 Add New Recipe를 하면 본격적으로 이 데이터를 어떻게 가공할 것인지 정할 수 있다.
Build Recipe
지금부터가 Dataprep의 핵심이다. Dataprep에서는 Recipe를 추가해서 데이터를 요리할 수 있다. 예시를 바로 보자.
json으로 데이터가 들어가 있는 걸 보면서 정리할 수 있다. 데이터가 위와 같이 json으로 마구 마구 들어가 있다. 여기서 뽑아내고 싶은 데이터를 선택해보자. 일단 event인 open, send, hard_bounce 등을 선택해보자. 마우스로 open을 드래그하면, 아래와 같이 제안을 해준다.
드래그만 했더니 필드로 정리해준다. 짜잔. 제안해준 것을 확인하고 Add 하면 반영이 된다.
Mandrill event 데이터에 맞는 디테일한 내용들을 좀 더 설명해보자.
json에 복잡하게 들어가 있는 필요한 필드들을 드래그만 하면 컬럼으로 쉽게 만들 수 있다. 이걸 이용해서 event, event_time, sender, receiver, template, subject, click_url 등등을 컬럼으로 분리해낸다.
_sdc라고 붙어있는 필드들은 stitchdata에서 보내는 필드들이기 때문에 primary key를 제외하고 모두 drop 했다.
ts라고 들어있는 event_time은 unix ime이어서 1000을 곱한 뒤, unixtimeformat으로 변경하였다. 그리고 timezone이 UTC인 데이터이기 때문에 9시간을 더해주었다.
제목에는 코드 없이 한다고 해놓고, 위에 적혀 있는 것 뭐냐!라고 생각할 수 있다. 그러나 위에 있는 recipe는 코드로 입력하는 것이 아니라 아래와 같은 UI를 통해 입력하게 되어있다. 엑셀 함수를 쓰는 것보다 쉽게 되어있으니 걱정하지 않아도 된다.
이런 UI로 Recipe를 만들 수 있다. 자동완성되고 설명도 나온다! 컬럼의 순서, 이름도 변환이 다 가능하므로 분석하는 사람들이 이해하기 쉽게 배치하면 된다.
Recipe를 다 만들고 나면, 오른쪽 위에 Run Job버튼을 눌러서 만들어 놓은 Recipe가 실제로 어떻게 돌아가는지 확인해보자. 확인하려면 데이터를 어느 곳에 저장할지(destination) 선택해야 한다. Bigquery에 있는 데이터를 각종 Recipe가 담긴 Flow로 정리하고, 그걸 다시 다른 Bigquery 테이블에 저장한다.
Destination이 될 곳을 고르자 이전에 있던 원본 데이터를 덮어 씌우면 안 되기 때문에 새 테이블을 만들어 주었다.
Output 옵션에 보면 4가지가 있는데, 사실 매번 새로운 필드만을 추가하는 Append를 선택하면 좋지만 Append를 하라면 소스가 되는 Dataset이 업데이트할 부분만 들어가 있어야 한다. 그렇지 않으므로 여기서는 Truncate를 선택했다.
Run Job and Scheduling
Run Job을 하면, 이렇게 돌아가는 걸 확인할 수 있다. Transform 하는 Recipe가 얼마나 복잡하냐에 따라 조금씩 다르겠지만 5만 줄 정도의 데이터를 위에 적힌 걸 처리하는데 7분 정도 걸렸다.
스케쥴을 걸어서 자동으로 돌게 해두자 위와 같이 Scheduling을 설정해서 매일 자동으로 돌도록 할 수 있다. 지금 다니는 회사에서 중요한 테이블의 경우, 1시간마다 업데이트를 제공하고 있으나, 이메일 데이터는 실시간 분석이 필요한 데이터는 아니라고 판단해서 Daily로 설정하였다. Stitchdata 같은 ETL 툴에서 만약 Daily로 처리했다면 그쪽에 설정해둔 시간을 고려해서 Dataprep에 설정하면 될 듯.
여기까지 다 하고 나면, mandrill event가 꽤 깔끔한 테이블로 정리된다. 그리고 추가적인 개발 없이 자동으로 매일 Bigquery에 데이터가 ETL 후 정리되어 들어간다. 그럼 아래와 같이 상대적으로 간단한 쿼리로 open rate, click rate 같은 것을 뽑을 수 있다 (기간을 고려하지 않는다면..).
select week(b.event_time) as week, a.template as template, count(distinct(if(a.event='send', a.email_id ,null))) as send, count(distinct(if(a.event='open', a.email_id ,null))) as unique_open, count(distinct(if(a.event='click', a.email_id ,null))) as unique_click, count(distinct(if(a.event='open', a.email_id ,null)))/count(distinct(if(a.event='send', a.email_id ,null))) as open_rate, count(distinct(if(a.event='click', a.email_id ,null)))/count(distinct(if(a.event='send', a.email_id ,null))) as click_rate
from [bigquery-test:.events] as a join [bigquery-test:mandrill.events] as b on b.email_id = a.email_id where b.event = 'send'
group by 1,2
order by send desc
또한 특정 이벤트 메일을 오픈한 사람이 구매를 했는지 하지 않았는지 등등의 이메일 아이디와 엮어서 쉽게 쿼리로 작성해서 볼 수 있다.
1, 2편에 나눠서 쓸 정도로 내용이 조금 길기는 했지만, 난이도가 있는 작업은 아니다.
처음에 dataprep이라는 것을 알고 난 다음에 문서 읽어보고, 첫 세팅을 마치는데 하루 밖에 걸리지 않았다. (글 쓴 사람은 개발자가 아니다) 물론 한 두번 돌려보고 나니까, 세세하게 아쉬운 점들이 있어서 세팅을 계속 조금씩 바꾸면서 작업을 했다. 또한 이 데이터를 분석하시는 분들이 필요하다고 요청한 필드들을 추가적으로 분리해내기도 했다. 이 정도 난이도의 작업으로 데이터를 잃지 않고, 분석하는 사람들이 쉽게 접근할 수 있는 테이블을 어느 정도 안정적으로 제공할 수 있다는 건, 이 툴들의 굉장한 장점이라 생각한다.
사실 하고 싶은 이야기는 따로 있다.
개발 리소스가 없고, 개발을 모른다고 누군가 해결해줄 때까지 기다리고 있을 때가 많다. 개발을 직접 배워서 직접 개발하는 기획자가 되세요. 는 절대 아니다. 각자의 영역에서 잘 할 수 있는 일이 있다고 생각하고, 기획자는 어쨌든 기획 업무를 잘해야 한다.
내가 해결해야 하는 문제의 개념을 정확하게 이해하고 + 해결 방법을 밖에서 찾으면 생각보다 할 수 있는 일이 많아지고, 생각의 폭이 넓어진다. 손 놓고 있지 말고, 직접 움직여 보자.
쓸데없을지도 모르는 각종 정보
이메일 open, click 이벤트인데, 어떤 template의 이메일을 누가 봤는지 등등의 데이터가 없는 케이스가 있다. 이건 어떻게 된 것인가?
Re) 발송 후에 30일이 지난 이메일에 대해서는 디테일한 정보를 제공해주지 않는다. 그렇기 때문에 open이나 click에 대한 정보를 정확하게 알 수 없을 수 있다.
Data Prep을 쓰면 추가적인 비용이 드는 것이 아니냐? Google Cloud 비용이 엄청 많이 나오면 어떻게...?
Re) 사람마다, 회사마다 느끼는 게 좀 다르겠지만, 개인적으로 보기에는 Dataprep 비용은 굉장히 싸다. 1 vCPU당 1시간에 $0.06 가격이다. 가격 정책 관련 문서
위 스크린샷을 보니 보니까 Fail난 job이 있던데 내용이 Fail 나면 어떻게 하냐?
Re) 아쉬운 점인데 Failed job에 대한 자세한 로그나 alert를 제공하지 않는다. 다른 방식으로 모니터링을 하고 있다. Failed job 관련 문서
이벤트 메일을 mandrill이 아니라 mailchimp로 발송하고 있다. 이 경우, 데이터를 하나의 테이블로 보고 싶은데 가능할까?
Re) Blendo라는 ETL Tool은 mailchimp 연동을 제공하고 있다. mailchimp로 만든 소스 테이블과 mandrill로 만든 소스 테이블 두 개를 하나의 테이블로 만드는 작업도 Data prep으로 가능하다.
How to schedule a BigQuery ETL job with Dataprep
