Mandrill 데이터를 코딩 없이 ETL 하기 1편
with Stitchdata
이메일은 서비스를 만들고 운영하는 입장에서 싸고 부담 없이 유저에게 정보를 보낼 수 있는 매체다. Mailchimp의 Mandrill 기준으로 이메일 1건당 0.9원 수준이며, 이는 SMS보다 20배 정도 저렴하다. (Amazon SES는 더 저렴하다)
많은 서비스들은 이벤트 안내뿐만 아니라 유저에게 꼭 보내야 하는 각종 소식(결제 완료, 결제 취소, 포인트 소멸 등등)을 보통 이메일로 보낸다. 이메일을 보내고 받는 것이 개인들에게는 이미 익숙한 일이기 때문에 서비스의 이메일 발송 작업도 만만하게 보일 수 있는데, 대량으로 메일을 보내는 건 개인의 이메일 수발신과는 다르다.
만약 서비스에서 보내는 메일을 gmail 개인 계정을 생성해서 발송 진행한다고 가정해보자. gmail의 무료 계정은 하루에 이메일 발송 500개(이메일 50개를 100명의 주소로 전달하면 순수신자는 500개) 제한에 걸린다. 유료 계정의 경우도 일일 순 수신자는 3,000개이다.
그럼 이메일을 발송하기 위한 서버를 구축하는 일의 난이도가 어려우냐? 하면 사실 어려워서 못할 정도는 아니다. 리눅스에 메일을 보내기 위한 sendmail을 설정하는 문서는 네이버에서도 쉽게 찾을 수 있다. 그러나 서비스를 만들고 운영하기 위해서는 안정적으로 단 시간에 많은 이메일을 보낼 수 있어야 하고, 메일을 보내는 IP가 스팸 서버로 등록이 되지 않아야 한다. (Reverse DNS 설정, SPF 레코드 등록 등등) 이걸 다 고려하면, 직접 만들어서 운영하는 것보다 잘 만들어져 있는 서비스를 사용하는 것이 낫다. 많은 스타트업들은 이메일 발송을 Amazon SES, SendGrid, Mandrill 등등의 서비스(이를 Transactional Email Service라 부른다)를 써서 처리한다.
여기서 이야기할 내용은 메일 발송에 대한 것이 아니라, 메일 발송 이후 데이터에 대한 것이다.
이메일이 정상적으로 발송되는 것도 중요하지만, 어떤 사람들이 열고 클릭하는지, 받는 사람들이 얼마나 열어보는지(open rate), 열어서 얼마나 클릭하시는지(click rate) 같은 것이 궁금할 수 있다. 물론 이런 통계적 수치뿐만 아니라, 운영 상으로도 중요 이메일을 보냈는데 유저가 정상적으로 받았는지 / 튕겼는지 / 열어보았는지 여부는 중요하다. 추가적으로는 메일을 열어서 클릭한 사람들이 발생시킨 매출까지도 궁금할 테지만 일단 기본적인 궁금증부터 해결해보자.
Mandrill Outbound Page @maichimp blogMandrill 기준으로 Outbound 페이지에 가면, 위에 언급한 데이터를 다 볼 수 있다.
'오! 이것만 가지고 데이터 분석을 하면 되겠구나'라는 생각을 했겠지만, Mandrill은 summary 데이터가 아닌 아래의 raw 데이터는 30일밖에 저장해주지 않는다. Bounced message data는 90일까지 저장해주지만, 우리가 보고 싶은 주요 데이터는 30일 치 밖에 유지해주지 않는다. Mandrill, Sendgrid, Mailgun 모두 다 Data retention 기간이 30일로 같다. 어쨌든 내버려두면 데이터가 사라진다. 옮겨야 한다.
이 데이터를 주기적으로 다른 곳에 옮겨야 할 텐데, 방법은 여러 가지가 있다.
한 달에 한 번씩 들어가서 export 기능을 이용해서 csv로 다운로드한다.
부지런하면 몸이 불편하다. 이 생각은 하지 않도록 하자.
Zapier 나 webhook을 이용해서 mandrill data를 slack이나 google docs 포스팅한다.
머리를 한 번 써서 쉬운 방법을 찾았다. Zapier는 굉장히 좋은 자동화 툴이다.
그러나, 이메일을 하루에 한 두 개 보낼 것도 아니고, 단순히 이메일에 대한 분석뿐만 아니라 이메일 데이터를 기반으로 해서 내부 데이터를 엮어서 분석하려면 데이터베이스화 되어있는 것이 필요하다.
여기까지 비개발자가 생각할 수 있는 Mandrill의 이벤트 데이터를 받는 방법이다. Mandrill에서 API를 이용해 데이터를 받아서 우리 데이터베이스에 넣어주세요~라고 개발자에게 요청하지만, 아무래도 데이터 관련한 업무는 기능 구현보다 우선순위가 떨어지는 경우가 많아서 개발이 쉽지 않다.
왼쪽에서 데이터를 받아서 오른쪽에 넣어준다. @blendo이럴 때는 나 대신 일 해주는 서비스를 찾아보자. ETL(Extract, Transform, Load)로 검색하면, 어딘가에서 데이터를 받아서(Source) 어딘가로 쌓아주는(Destination) 서비스들을 찾을 수 있다.
나에게 맞는 ETL 서비스 찾기
좋은 ETL서비스를 찾으려면, 보안, 데이터 퀄리티, 툴 사용성, 가격 등등을 고려해야 한다. 기획자에게 가장 중요한 건, 일단 내가 필요한 게 되는지를 확인하는 것이다. 나머지는 개발자에게 물어보자.
필요한 것이 가능한지 확인하려면 Integration 페이지를 잘 살펴보면 된다. Stitchdata, Blendo, Fivetran 등등은 모두 어디서 데이터를 받을 수 있는지, 어디로 옮겨 주는지를 정리해서 알려주고 있다.
Source: 일단 데이터를 받아오는 곳에 대해서 확인해보자. 나는 Mandrill의 데이터를 가져오고 싶은 것이기 때문에 ETL 서비스의 Integration 페이지의 Source 목록에서 Mandrill을 찾아서 선택하면 된다. 위에 말한 세 개 중에는 Blendo에서 Mandrill 을 Source 목록에 기본으로 노출하고 있다. 이렇게 서비스 레벨로 연동이 지원되지 않더라도 Mandrill에서는 Webhooks 방식의 Source 연동을 지원하고 있기 때문에 Webhooks 으로 데이터를 수신할 수 있는 ETL 서비스는 모두 사용이 가능하다. Stitchdata, Blendo, Fivetran 모두 Webhooks을 지원하기 때문에 OK.
Destination: 이제 데이터를 쌓을 곳에 대해서 확인해보자. 대부분의 서비스가 Redshift, Bigquery, PostgreSql 등등을 지원한다. 내부 데이터베이스가 Mysql로 되어 있어서 꼭 Mysql을 선택해야 한다면, Fivetran을 선택하면 된다.
ETL Tool에서 ETL을 설정하자
지금부터 Stitchdata 기준으로 설명을 적어본다. Blendo나 Fivetran의 문서도 살펴보았는데, 내용들은 비슷하다. 이 툴들은 Source과 Destination 사이에서 파이프 역할을 하는 것이라, 양쪽의 연결 정보를 입력하면 된다. Facebook 광고 데이터나 Adwords 데이터를 가져오고 싶으면, 광고 데이터에 권한 있는 사람 아이디로 로그인하면 되고, 설정도 굉장히 간단하다.
설명하려고 했던 Mandrill 데이터로 돌아가 보자.
Mandrill 데이터는 Stitchdata에서는 Webhooks 방식으로 연동해야 한다. Mandrill에서 설정한 이벤트가 생길 때마다 Stitchdata 쪽에 지정된 URL로 데이터를 보내준다고 생각하면 쉽다.
위와 같이 이름을 정하고 나면, 아래처럼 URL을 확인할 수 있다.
이벤트가 있을 때 호출할 webhook url을 복사하자Webhooks 설정을 하자
Mandrill > Setting > Webhooks에 stitchdata에서 복사한 URL로 Mandrill 데이터를 보내도록 설정해주자. 이때 Mandrill의 이벤트 중 어떤 종류의 이벤트가 발생했을 때, Webhooks을 보낼 것인지 선택할 수 있다. 모든 종류를 다 받아도 되고, 꼭 필요한 것만 받아도 된다. 데이터를 일단 받아서 후가공하면 되기 때문에 send, open, click, hard_bounce, soft_bounce, spam, reject 정도는 다 받도록 하자. 여기서 받지 않으면 데이터가 사라져 버림을 잊지 말고.
복사한 URL을 넣고 webhook 설정을 하자 이제는 데이터를 어디다가 쌓을지를 정할 타이밍이다. 개발팀과 잘 논의해보자. 기존에 쓰고 있는 인프라에 넣는 것이 편하다. 이걸 가지고 논의하는데 시간이 걸릴 것 같으면, Fully Managed 서비스인 Google Bigquery 같은 곳에 일단 넣는 것도 방법이다.(Fully Managed = 운영하기 위해 내가 할 것은 돈을 내는 것뿐)
Google Cloud에 계정을 만들고, 본인 계정을 연결하면 데이터를 바로 넣을 수 있다. 이때 주의할 점은 IAM에서 본인 계정에 Google Bigquery와 Google Cloud Storage Admin 권한을 줘야 한다는 점이다.
https://www.stitchdata.com/docs/destinations/bigquery/
stitchdata의 destination에 bigquery 설정위에 같이 설정해주고 나면, Stitchdata로 데이터가 들어오기를 기다리기만 하면 된다.
실제 데이터가 들어와서 쌓이면 아래와 같이 Load에 표시가 된다.
stitchdata에서 확인실제 데이터가 쌓였는지(Load) Bigquery 에 들어가서 확인해보면, 아래와 같이 데이터가 있는 것을 확인할 수 있다.
bigquery 콘솔에서 조회하면 데이터를 확인할 수 있다여기까지 하면 일단 Mandrill에서 90일만 지나면 사라지는 소중한 이메일 데이터를 잃지 않고 쌓을 수 있다.
그러나 mandrill_events라는 필드에 쌓이는 데이터를 분석 가능한 형태로 변경하려면 쉽지 않다.
2편에 이어서 이야기해보자.
쓸데없을지도 모르는 각종 정보
Mandrill의 내부 페이지 디자인은 2014년에 리 디자인되었다고 한다. 내가 본 이후로 거의 변화가 없었는데 진짜였다.
Modeanalytics가 ETL Tool을 고르면서 고민했던 이야기: ETL Tool 비교글 중에 가장 재미있게 읽었던 글.
11 Great ETL Tools, And The Case For Saying "No" To ETL
* stitchdata를 찾아주신 현정님께 감사를 ( __)
