구글 스프레드 시트로 주기별 SQL 데이터 자동으로 수집하기

프로덕트 오너가 팀에 합류한 뒤에 가장 먼저 확인할 것은 제품의 건강함을 확인할 수 있는 기본적인 데이터 지표들의 축적 여부와 언제든지 지난 로그 데이터도 볼 수 있느냐이다. 물론 여기서 로그 데이터는 엔지니어들이 표현하는 로그와는 온도 차이가 조금 있지만 간단하게는 위클리, 데일리로 끊어서 활성사용자(Active User)를 볼 수 있다거나 결제한 이용자(Paid User)를 확인할 수 있어야 한다. 특히 데이터가 업데이트 되어버리게 마지막 로그인 시점만 체크하는 경우 데이터 조회 시점에 따라 데이터가 달라질 수 있으므로 꼭 어딘가에 기록이 되어 있어야 한다. PO가 이걸 체크하는 가장 큰 이유는 제품이 현재 어떤 상태인지 파악하고 어떤 시그널에도 반응할 수 있게끔 준비하는 것과 같다. 예를 들어 갑자기 구매 건수가 줄어든다면 그 이유를 찾기 위해 노력을 해야 한다. 그런데 이런 데이터가 없고 리포팅이 불가능한 곳에서는 PO가 할 수 있는 것은 그냥 회사의 중장기 전략에 맞춰 프로젝트를 관리하는 정도의 일이 전부이다. 당연히 어떠한 변화에도 대응하기 어려우며 책임도 권한도 갖기 어렵게 된다. 제품의 주인이자 최고 관리자가 되기 위해서는 당연히 제품의 상태를 늘 주시하고 있어야 한다.

 

그리고 이번에 스타트업에 합류하니 혹시나 했었는데 역시나 모든 데이터가 덮어씌워지고 있었다. 예를 들어 상품을 유효 기간 내에도 재구매가 충분히 가능한데 이런 재구매가 발생할 경우 구매일이 덮어씌워져서 이전 아이템의 구매일을 유추하고 있었다. 당연히 마지막 로그인 시점도 모두 업데이트 되버리기에 데이터 조회 시점에 따라 데이터가 들쑥날쑥 바뀔 수 밖에 없는 상황이었다. 그래서 우리가 봐야 하는 해적지표를 정의하고 데일리, 위클리, 먼슬리로 보관할 수 있게끔 구성하고자 했다. (심지어 테스터 DB도 없어 테스터의 활동에 따라 지표가 무지막지하게 달라지기도 했다.) 그런데 서버 개발자 한명이 인프라와 백엔드를 동시에 관리하면서 마케터와 사업개발 파트에서 쿼리 조회 요청하는 것들까지 모두 소화하고 있는 상황이었다. 심지어 새로운 기능 개발로 이터레이션이 돌고 있는 상태여서 내가 가장 높은 우선순위로 잡았던 로그 데이터 쌓기를 하기 위해 한달을 넘게 기다려야 하는 상황이었다.

 

그래서 궁여지책으로 R을 이용해서 서버에 스케줄러를 돌게끔 내가 직접 붙여야 하나 고민도 들었지만 진행 중인 스프린트와 계속되는 미팅 속에서 이런 일까지 하는 것은 부담스러웠다. 별 것 아니지만 iOS 웹뷰 띄우는 작업도 생각보다 옵션 설정과 분기 처리 해줄 것들도 있었던 터라서 파이프 라인 구축까지 내가 직접 하는 것은 무리이다 싶었다. 그러던 중 예전에 슬랙에서 올라온 연차를 웹훅으로 연결하여 스프레드 시트에 입력하는 구글 스크립트가 기억나서 혹시 SQL에 이 스크립트를 주기적으로 붙일 수 있지 않을까 생각했고 이게 가능하다면 DB에 기록까진 할 수 없더라도 구글 스프레드 시트에 주기적으로 수집된 데이터를 기록할 수 있겠다 싶었다. 다른 데이터와의 연계라던가 활용은 어렵겠지만 일단 지금도 지나가고 있는 데이터들이 아까워 급하게 데이터를 찾아보기 시작했고 좋은 자료를 발견하여 참고하면서 스크립트 작성을 시작했다.

 

참고로 구글 스프레드 시트에서 스크립트 편집기 작성은 도구 > 스크립트 편집기 를 누르면 열린다.

 

https://mitny.github.io/articles/2019-04/MySQL-to-Googlespreadsheet

[MySQL] MySQL 데이터를 구글 스프레드 시트로 불러오기

물론 위 블로그에서는 간단하게 구현된 상태였고 하나의 쿼리가 아닌 여러 개의 쿼리를 반복적으로 보내고 입력해야 했기에 아래와 같이 재편집을 했다. 특히 구글 스크립트로 Jdbc 호출할 때 Error: Statement cancelled due to timeout or client request 오류가 나서 setQueryTimeout을 설정해주었다. 아마 기본은 2초 정도로 굉장히 짧은 것 같다.

var conn = Jdbc.getConnection(dbUrl, user, userPwd); // DB 연결
var stmt = conn.createStatement();
stmt.setMaxRows(10);
stmt.setQueryTimeout(25);

또한 참고한 코드와 달리 매일, 매월, 매주 계속 행을 늘려나가며 작성해야 하기에 아래의 코드를 추가하여 알아서 다음 행을 불러오게끔 구성하였다.

var sheet = SpreadsheetApp.getActiveSheet(); // SpreadSheet 객체 생성
var lastRow = sheet.getLastRow()
var i = lastRow + 1;

그리고 매번 다른 쿼리를 함수로 묶어 전체를 한번에 호출하는 별도의 함수를 빼고 스케줄러를 작동할 수 있게끔 구성해야 했다. 일단 기본적으로 필요한 정보만 간추려보니 크게 이용자를 나누고 있는 세그먼트에 따라 활성사용자, 신규 가입자, 결제 고객, 매출, ARPU를 뽑기로 했다. (근데 많다..?!)

function getDailyReport() {
  getTotalDailyActiveUser()
  getSeg1DailyActiveUser()
  getSeg2DailyActiveUser()
  getTotalDailyJoinUser()
  getSeg1DailyJoinUser()
  getSeg2DailyJoinUser()
  getTotalDailyPaidUser()
  getSeg1DailyPaidUser()
  getSeg2DailyPaidUser()
  getDailyRevenue()
  getSeg1DailyRevenue()
  getSeg2DailyRevenue()
}

 

쿼리 결과에서 시트로 데이터를 입력할 때는 컬럼명을 기준으로 불러오게 되는데 SELECT FROM 사이에 as 를 넣어 컬럼 이름을 지정해주는 것이 편하다. 예를 들면 아래와 같다.

 

SELECT DATE as date, count(id) as count FROM

어느정도 구성이 끝나자 아래와 같은 긴 스크립트가 되어버렸다. 사실 스크립트 파일 자체를 나누고 싶었지만 귀찮기도 했고 동일한 파일을 Monthly와 Weekly로 따로 파일을 만들 생각이었기 때문에 복붙의 편의성을 위해 그냥 긴 하나의 파일로 만들었다.  

var connectionName = ''; // 접속할 MySQL 서버의 주소 또는 IP와 Port(Default:3306)
var user = ''; // MySQL 유저 ID
var userPwd = ''; // MySQL 유저 PW
var db = ''; // 접속할 MySQL DB명
var instanceUrl = 'jdbc:mysql://' + connectionName;
var dbUrl = instanceUrl + '/' + db;
var sheet = SpreadsheetApp.getActiveSheet(); // SpreadSheet 객체 생성
var conn = Jdbc.getConnection(dbUrl, user, userPwd); // DB 연결
var stmt = conn.createStatement();
stmt.setMaxRows(10);
stmt.setQueryTimeout(25);
var lastRow = sheet.getLastRow()
var i = lastRow + 1; // 마지막 row의 아래 행부터 채움

function getDailyReport() {
  getTotalDailyActiveUser()
  getSeg1DailyActiveUser()
  getSeg2DailyActiveUser()
  getTotalDailyJoinUser()
  getSeg1DailyJoinUser()
  getSeg2DailyJoinUser()
  getTotalDailyPaidUser()
  getSeg1DailyPaidUser()
  getSeg2DailyPaidUser()
  getDailyRevenue()
  getSeg1DailyRevenue()
  getSeg2DailyRevenue()
}

/* Active User */

function getTotalDailyActiveUser() {
  var results = stmt.executeQuery(""); // 쿼리
  while(results.next()) {
	  // getRange(Integer row, Integer Column)
      date = sheet.getRange(i, 1); // A2
      count = sheet.getRange(i, 2); // B2
      date.setValue(results.getString("date")); // 현재 row의 title 컬럼 값
      count.setValue(results.getString("count")); // 현재 row의 count 컬럼 값
  }
}

function getSeg1DailyActiveUser() {
  var results = stmt.executeQuery(""); // 쿼리
  while(results.next()) {
	  // getRange(Integer row, Integer Column)
      count = sheet.getRange(i, 3); // C2
      count.setValue(results.getString("count")); // 현재 row의 count 컬럼 값
  }
}

function getSeg2DailyActiveUser() {
  var results = stmt.executeQuery(""); // 쿼리
  while(results.next()) {
	  // getRange(Integer row, Integer Column)
      count = sheet.getRange(i, 4); // D2
      count.setValue(results.getString("count")); // 현재 row의 count 컬럼 값
  }
}


/* 신규회원 */

function getTotalDailyJoinUser() {
  var results = stmt.executeQuery(""); // 쿼리
  while(results.next()) {
	  // getRange(Integer row, Integer Column)
      count = sheet.getRange(i, 5); // E2
      count.setValue(results.getString("count")); // 현재 row의 count 컬럼 값
  }
}


function getSeg1DailyJoinUser() {
  var results = stmt.executeQuery(""); // 쿼리
  while(results.next()) {
	  // getRange(Integer row, Integer Column)
      count = sheet.getRange(i, 6); // F2
      count.setValue(results.getString("count")); // 현재 row의 count 컬럼 값
  }
}


function getSeg2DailyJoinUser() {
  var results = stmt.executeQuery(""); // 쿼리
  while(results.next()) {
	  // getRange(Integer row, Integer Column)
      count = sheet.getRange(i, 7); // G2
      count.setValue(results.getString("count")); // 현재 row의 count 컬럼 값
  }
}

/* 결제 회원 */

function getTotalDailyPaidUser() {
  var results = stmt.executeQuery(""); // 쿼리
  while(results.next()) {
	  // getRange(Integer row, Integer Column)
      count = sheet.getRange(i, 8); // H2
      count.setValue(results.getString("count")); // 현재 row의 count 컬럼 값
  }
}

function getSeg1DailyPaidUser() {
  var results = stmt.executeQuery(""); // 쿼리
  while(results.next()) {
	  // getRange(Integer row, Integer Column)
      count = sheet.getRange(i, 9); // I2
      count.setValue(results.getString("count")); // 현재 row의 count 컬럼 값
  }
}

function getSeg2DailyPaidUser() {
  var results = stmt.executeQuery(""); // 쿼리
  while(results.next()) {
	  // getRange(Integer row, Integer Column)
      count = sheet.getRange(i, 10); // J2
      count.setValue(results.getString("count")); // 현재 row의 count 컬럼 값
  }
}

/* 수익 */

function getDailyRevenue() {
  var results = stmt.executeQuery(""); // 쿼리
  while(results.next()) {
	  // getRange(Integer row, Integer Column)
      count = sheet.getRange(i, 11); // K2
      count.setValue(results.getString("revenue")); // 현재 row의 revenue 컬럼 값
  }
}

function getSeg1DailyRevenue() {
  var results = stmt.executeQuery(""); // 쿼리
  while(results.next()) {
	  // getRange(Integer row, Integer Column)
      count = sheet.getRange(i, 12); // L2
      count.setValue(results.getString("revenue")); // 현재 row의 revenue 컬럼 값
  }
}

function getSeg2DailyRevenue() {
  var results = stmt.executeQuery(""); // 쿼리
  while(results.next()) {
	  // getRange(Integer row, Integer Column)
      count = sheet.getRange(i, 13); // M2
      count.setValue(results.getString("revenue")); // 현재 row의 revenue 컬럼 값
  }
  results.close();
  stmt.close();
  conn.close();
}

 

몇가지 주의사항이 있는데 처음 실행하면 권한 승인 요청이 있는데 당연히 승인해야 하며, 되도록 저장 후에 함수를 실행해야 한다. 당연히 실행할 때 함수를 선택하는 항목이 있는데 해당 함수를 잘 선택해야 하며 서버와의 커넥션 종료에 유의해야 한다.

 

이렇게 완성된 스크립트를 쿼리 에러가 있나 확인만 해보고 잘 작성되는 것까지 보고 이제 매일, 매주, 매월 반복되게끔 구성을 해야 했다. 스크립트 편집기의 시계모양을 누르면 스크립트가 실행되는 스케쥴 트리거를 걸 수 있다. 

 

트리거 화면에서 우측 하단의 + 버튼을 누르면 아래와 같은 화면이 나오는데 원하는 주기별로 설정해서 실행하면 된다.

아쉽게도 자정을 정할 수는 없고 0~1시로 설정되어 약간의 오차는 있을 수 있으나 그래도 이건 지금의 내 상황에서는 어쩔 수 없는 선택이라 오차 범위에 들어올 것이라고 생각하기로 했다. (ㅠㅠ)

 

참고로 쿼리문을 만들며 daily와 weekly는 SQL의 CURDATE() 함수와 DATE_FORMAT을 적절히 활용해서 구현했는데 Monthly가 예상처럼 잘 안되어 아래와 같이 조건절을 추가해서 조회했다. 

WHERE DATE_FORMAT(lastLoginDate, '%Y') = YEAR(curdate()) 
and DATE_FORMAT(lastLoginDate, '%m') = MONTH(CURDATE())-1

 

이런 방식의 제일 단점은 구글 스크립트에서 쿼리문을 넣을 때 줄바꿈을 하면 안되어서 드래그앤드랍 하기도 번거롭고 가독성이 굉장히 떨어진다.