in Plan: 계획

GA와 구글스프레드시트, DB로 활용하기위한 함수 6가지

Share

최근 업무중에 구글스프레드시트를 자주 활용하면서 그 활용도와 기능에 다시 한 번 놀라고 있습니다. 빅데이터 시대에서 저평가된 툴 중 하나가 Google Spreadsheet 와 앱스스크립트 (Apps Script) 라는 말에 크게 공감합니다.

특히 여러 부가기능과 스크립트 (GAS) 를 활용하면 자동화된 테이블 형태로 각종 성과데이터를 관리할 수 있습니다.

왜 구글 스프레드시트를 사용하는가?

  • 자동화, 자동화, 자동화! 대부분의 리포트 작업은 반복적인 경우가 많습니다. CSV 추출, 데이터 가공, 복사+붙여넣기 등 반복작업을 자동화할 수 있습니다.
리포트 자동화

자동화가 핵심입니다.

  • 다양한 부가기능 (Add-on): 구글애널리틱스, Solver, BigML 등 파워풀한 부가기능
  • Background 작업: 엑셀 VBA 은 프로그램 실행후에만 일(?)하지만, 구글 시트는 별도 실행없이 정해진 시간/규칙에 따라 알아서 일을 해놓습니다. MS엑셀 부가기능 형태로 제공되는 플러그인보다 (예: Adobe Report Builder) 구글시트 Add-on 활용도가 높은것도 이때문입니다.
  • 무료+편리+협업: 구글답게 무료에다가 다른 팀원들과의 협업/공유도 편리합니다. 구축/개발 리소스나 속도같은 부분도 물론 신경쓸 필요 없습니다

물론 데이터양이 많아지면 제한이 있지만, 간단한 어플리케이션이나 CRM 캠페인, Ad-hoc 마케팅 분석이나 대시보드에 Database 로 활용하기에는 매우 효율적입니다.

특히 큰 분석보다, 빠르고 심플한 의사결정이 필요할 때 유용합니다. 빅데이터 시대에서도 스몰데이터로 해결할 수 있는 건 여전히 많습니다.

“데이터는 많은데, 시간은 너무 부족하다… 빅데이터보다는 패스트데이터(Fast Data) 가 더욱 중요하다” – Entrepreneur.com

아름다운 DB 자동화를 위한 함수 6가지!

추가분석 및 시각화가 수월한 테이블 형태로, 수작업 없이, 데이터를 적재하기위해 자주 쓰이는 Google Sheets 함수 6가지를 공유합니다.

FILTER(범위,조건)

  • 사용예시
     FILTER('sheet1'!A16:A,'sheet1'!A16:A<>"")
    (=‘Sheet1’ A16:A 데이터만 보여줘, ‘Sheet1’ A16:A 가 빈칸이 아닌 경우에)
  • 언제, 왜 쓰는가? API 형태로 데이터를 가져오게 되면, 종종 NULL값이 빈칸으로 표시되어 불필요한 공간을 잡아먹는데, FILTER 함수로 빈 행을 걸러낼 수 있습니다.

QUERY(데이터범위,”쿼리”)

  • 사용예시
     QUERY('sheet1'!A16:A,"select A where A is not null")
    (=‘Sheet1’ A16:A 데이터에서, A열이 빈칸이 아닌 경우만 보여줘)
  • 언제, 왜 쓰는가? 위 FILTER함수와 비슷한데, 기존 분석가들에게 친숙한  SELECT ... WHERE ... GROUP BY... SQL구문을 통해 특정조건의 데이터를 추출합니다.

{범위1;범위2;범위3} UNION

  • 사용예시
     {FILTER('sheet1'!A16:A,'sheet1'!A16:A<>"");FILTER('sheet2'!A16:A,'sheet2'!A16:A<>"")}
    (=‘Sheet1’ A16:A 내  A열이 빈칸아닌 경우만 보여주고, 그 데이터 아래에 ‘Sheet2’ A16:A 내 A열이 빈칸아닌 경우를 이어붙여줘)
  • 언제, 왜 쓰는가? 여러개 시트에 있는 데이터를 UNION 형태로 통합할 수 있습니다. 예를들어, 같은 Header의 데이터셋이 용량제한으로 인해 1~10000 행까지 Sheet1 에 있고, 10001~20000행까지 Sheet2 에 있다면 손쉽게 중괄호{} 를 통해서 하나의 시트로 합칠 수 있습니다 (별도 UNION 함수보다 개인적으로는 이 방식을 더 선호합니다).

IMPORTRANGE(“스프레드시트키값”, “범위”)

  • 사용예시
     IMPORTRANGE("1qHqrNrG","Sheet1!A16:C360")
    (=‘1qHqrNrG’ 스프레스시트Sheet1!A16:C360 를 가져와서 보여줘)
  • 언제, 왜 쓰는가? 만약 다른 유저가 소유한 스프레스시트에서 CRM 수치나 Feed 데이터가 정기적으로 업데이트 중이라면, 이 데이터를 매번 복사+붙여넣기 할 필요 없이, IMPORTRANGE 로 손쉽게 불러올 수 있습니다 (스프레드시트키값은 URL의 /spreadsheets/d/ 뒤에서 확인가능)

ARRAYFORMULA(수식)

  • 사용예시
     ARRAYFORMULA(H2:H/G2:G)
    (=H2:H 나누기 G2:G)
  • 언제, 왜 쓰는가? 하나의 수식으로 여러개의 행 / 범위에 적용할 수 있습니다. 특히 범위가 유동적으로 바뀔때마다 매번 드래그할 필요가 없어집니다. 가장 많이 쓰이고 유용한 함수 중 하나입니다.
엑셀 드래그 다운

이제 수식을 아래로 드래그 하지 않아도 됩니다.

TRANSPOSE(SPLIT(REPT(“구분값”&”;”,반복횟수,”;”))

  • 사용예시
     TRANSPOSE(SPLIT(REPT("PC"&";",ROWS(FILTER('Sheet1'!A16:A,'Sheet1'!A16:A<>""))),";"))
    TRANSPOSE(SPLIT(REPT("APP"&";",ROWS(FILTER('Sheet2'!A16:A,'Sheet2'!A16:A<>""))),";"))
    (=”PC” 라는 값을 Sheet1 A16:A내 값이 있는 행의 횟수만큼 입력해줘.)
    (=”APP” 이라는 값을 Sheet2 A16:A내 값이 있는 행의 횟수만큼 입력해줘.)
  • 언제, 왜 쓰는가? 칼럼내 구분값을 입력할때에 사용하며, 특히 속성이 다른 여러개 시트의 데이터를 하나로 합칠때에 유용합니다.
구글스프레드시트 칼럼구분

위와같이 칼럼내 Dynamic 값을 구분할 때에 효과적입니다.

적용사례

  • 다양한 플랫폼에 걸쳐 서비스를 하는 A사는 특정 성별/연령대의 고객군이 전환율이 상승하는 날이 있는지, 있다면 어떤 기기에서 전환이 일어나는지 GA 데이터를 통해 알고싶었습니다 (여기까진 크게 특별할 것이 없습니다).
  • 문제는 여기서부터인데요, 앱, PC웹, 모바일웹 등 디바이스별로 GA 속성을 분리해놓았기 때문에, 기존에는 각각 다른 보기에서 데이터를 구한 후 엑셀에서 합쳐야만 했습니다 (노가다의 기운이 느껴집니다).
  • 또 GA 데이터의 몇몇 Default 영문값을 한글로 치환(VLOOKUP) 하길 원했습니다 (예: male/female 대신 남성/여성)
  • 이 일련의 작업을 자동화해서 테이블 형태로 데이터를 관리하고 싶었습니다.

… 이렇게해서 완성된 샘플 테이블!

위 6가지 Spreadsheet 함수와 GA 부가기능 (아직 않으신 분은 여기로) 을 이용해, 간단한 데이터베이스가 만들어졌습니다!

각 탭별로 설명하자면,

  • Report Configuration: App, PC웹, 모바일웹 리포트 설정 (2016-10-21 부터 2016-11-20 까지 한달간)
  • pc-web: PC웹 데이터 (ga:1234567)
  • mobile-web: 모바일웹 데이터 (ga:2345678)
  • app: 앱 데이터 (ga:3456789)
  • Lookup: male/female 값을 남성/여성으로 치환하기 위한 Lookup table
  • RAW DATA (최종테이블): 일자별/기기별/연령대별/성별/세션수/전환수 (2행 수식 참조) 

이곳을 클릭하셔서 파일 복사 후 사용하세요.

정기리포트 (일간 / 주간 / 월간) 를 반복적으로 만들어 모니터링 하려는 경우, 위 함수를 참조하면 훨씬 효율적입니다.

특히 Scheduling 을 통해 정해진 시간에 자동으로 업데이트시킬 수 있기 때문에, 수작업은 말그대로 ZERO 입니다.

이렇게 완성된 데이터소스는 이후 데이터스튜디오태블로에 연결해서 시각화할 수 있습니다 (다음 포스트에서 이어서 설명하겠습니다).

 

  • kayros

    안녕하세요! 페이스북 구글 애널리틱스 클럽 운영자 카이로스입니다 ^^
    잘 지내고 계시죠~?? +_+ 너무나 소중한 정보를 포스팅 해주셔서 감사드립니다.
    페이스북에 올려주신 내용 공유하도록 할께요!!! 🙂

    • 물론이죠! 감사합니다 새해 복 많이받으세요~

  • 김준성

    셀의 병합이나 테두리 변경 정보를 가져오는 함수도 있을까요? importrange를 예제를 보고 해보았는데
    특정 범위의 셀 값들은 들어오나 나머지 값들은 들어오지 않아서 통합 시트에서 또한번 셀을 손봐야 하는
    번거로움이 생겨서요. 혹시 아시는 분들은 조언 부탁드립니다.

  • 김준성

    게시글 잘 보았습니다. 도움도 많이 되었구요 특히 importrange를 이용하여 다른 시트의 정보를 가지고 오는 방법은 너무 좋았습니다. 그런데 새로운 궁금증이 각 셀의 데이터 값은 불러 올수 있으나 셀의 병합이나 테두리에 대한 부분까지 가져오고 싶은데 그 방법에 대한 자동화 함수 를 잘 모르겠습니다. 웹 서치를 해도 잘 보이지 않았습니다. 혹시 자동화 함수 중에 셀의 병합이나 테두리에 대한 부분도 가져올수 있는 함수가 있을까요?

    • 댓글 감사합니다. 회신이 늦었네요 ^^;
      셀 병합이나 테두리 적용을 importrange 활용해서 실행하시고 싶다는 말씀이신가요?
      위 함수들은 아무래도 테이블화 하는데 주로쓰이다보니, 셀병합이나 테두리같은 부분들 (DB용 아닌 formatting 함수들) 은 제가 잘 모르겠습니다.ㅠ

      • 김준성

        답변 감사드립니다. 막연한 아이디어에서 시작한 궁금중이었는데 친절히 답해주셔서 고맙습니다. 저도 좀 더 리서치해서 알아보도록 하겠습니다^^

  • 신혜정

    안녕하세요,
    importrange 함수를 사용하여 여러 시트의 데이터를 순차적으로 하나의 시트에 불러오기 하려고 하는데요, 각 시트의 데이터가 매일 증가하다보니 overwrite 에러가 발생합니다.
    여러 시트의 값을 자동으로 가져올 수 있는 함수가 있을까요?

    • 안녕하세요~ sheet 예시를 볼 수 있을까요?

  • 최경옥

    안녕하세요?
    스프레드 시트를 활용하는 책이 있으면 추천받고 싶어요…

    • 안녕하세요~ 아쉽게도 책이 별도로 나온것은 없는것으로 압니다 ㅠㅠ

  • joe

    안녕하세요, 엑셀에서 잘 활용하던 이중유효성검사를 활용한 이중드롭다운메뉴 만드는 것을 구글스프레드시트에 써보려 하는데, 방법을 찾기도 쉽지 않고… 찾았는데 너무 어렵네요 ㅠㅠ… 혹시 도움을 주실 수 있을까요?