November 19, 2024
CRM 마케팅이 중요해지면서 많은 기업들이 Braze를 도입하고 있다.
대부분 앱 푸시 발송과 개인화 마케팅을 위해 Braze를 사용하지만, 다양한 기능을 활용해 마케팅을 고도화하는 경우는 많지 않다.
아직 Braze 관련 학습 자료나 강의가 부족해 공식 문서에만 의존해야 하다 보니, 많은 마케터들이 Braze의 기능을 제대로 활용하지 못하고 있다.
이 글에서는 Braze를 제대로 활용하고 있는지 점검하고, 놓치고 있는 유용한 기능들을 소개하려 한다.
아래 Braze 용어 중 내가 사용한 적이 있거나, 사용하지 않았더라도 들어본 용어가 있는지 확인해 보자.
(Braze 이용자라면 누구나 사용하는, 꼭 알아야 하는 기능은 빼두었다.)
3개 이상 사용해 봤다면 Braze를 잘 활용하고 있는 셈이다.
하나도 사용해 보지 않았더라도 걱정하지 말자. 지금부터 각 기능의 활용법을 자세히 설명할 예정이다.
Frequency Cappping이란 사용자가 받는 메시지 수를 제한해 피로감을 줄여주는 기능이다.
설정 예시
위와 같이 채널별로 기간과 수신 횟수를 설정할 수 있고, Campaign이나 Canvas에 Tag를 추가하면 특정 캠페인에만 제한을 걸 수도 있다.
예를 들어 이벤트 태그가 있는 캠페인은 하루 1개만 발송하는 식이다.
"푸시가 너무 많이 와요", "인앱메시지가 자주 떠서 불편해요" 같은 VOC를 자주 받는다면 Frequency Capping을 적극 활용해보자. 사용자 경험도 개선하고 고객 만족도도 높일 수 있다.
Braze에서 자주 쓰는 필터로 'X Custom Event Property In Y Days'와 'X Purchase Property In Y Days'가 있다. 실시간으로 반영된다는 장점이 있지만, 몇 가지 제한사항이 있다.
반면 Segment Extension은 아래와 같은 장점이 있다.
예를 들어 일반 필터로는 '지난 30일간 패딩 구매자'만 찾을 수 있지만, Extension으로는 '지난 1년간 패딩 구매자' 세그먼트를 만들 수 있다.
단, Extension은 실시간 업데이트가 아닌 정해진 주기로 업데이트된다. 기존에는 매일 오전 12시마다 업데이트 되었는데, 최근 Weekly, Monthly 옵션이 추가됐다.
Webhook으로 카카오톡, 문자 메시지를 보내는 것 뿐만 아니라 빈 웹훅인 Spacer를 발송하여 A/B Test를 진행하거나, 성과를 측정하는 것도 가능하다.
Spacer 활용 사례
또한 잘못 설정된 Conversion 지표를 보완할 때도 유용하다.
Connected Content는 API를 통해 외부 데이터를 실시간으로 가져와 메시지에 활용하는 기능이다.
활용 가능한 데이터:
이러한 데이터는 Braze에 저장되지 않아 보안성이 높고, 실시간 데이터로 더 정확한 개인화가 가능하다.
API Response 값을 메시지에 바로 사용하거나, Liquid 구문으로 메시지 발송 조건으로 활용할 수도 있다.
API 개발이 필요하지만, 활용하면 한층 더 다양한 개인화 메시지를 만들 수 있다.
Connected Content 사용 사례
1. Open API 활용 : 누구나 이용할 수 있는 Open API를 활용하여 다양한 캠페인을 진행할 수 있다.
2. 내부 API 활용 : 기개발된 API가 있다면 해당 API를 활용하여 다양한 캠페인 운영이 가능하다.
Query Builder는 SQL Query를 사용해 데이터를 출력하는 기능이다.
Campaign Analytics와 Engagement Report를 통해 캠페인 발송 수와 전환 수는 확인할 수 있지만, 유저가 어떤 상품을 구매했는지, 혹은 다른 이벤트가 발생했는지는 알 수 없다.
유저 행동을 더 자세히 분석하고 싶다면 쿼리빌더를 활용해보자. SQL에 익숙하다면 직접 쿼리를 작성할 수 있고, 그렇지 않다면 Query Template이나, AI Query Builder를 통해 쿼리를 생성하여 사용하면 된다.
Query Builder를 통해 N Day Retention과 같은 데이터도 확인할 수 있다.
N Day Retention 활용 사례 보러가기
어트리뷰트 데이터 테이블은 지원하지 않지만, 캠페인, 캔버스, 이벤트, 세션 정보 같은 유용한 데이터는 쉽게 추출할 수 있다. 다양한 분석을 원한다면 Query Builder를 적극 활용하자.
(단, Query Builder는 매월 사용할 수 있는 크레딧이 있으니, 쿼리 실행 시 크레딧이 줄어드는 점을 주의해야 한다!)
앞서 언급한 기능 외에도 Braze를 더 깊이 활용할 수 있는 방법은 많다.
실무로 바빠서 Braze를 자세히 살펴볼 시간이 없더라도, 틈틈이 다양한 기능을 활용해 보다 효율적이고 정교한 CRM 마케팅을 진행하길 바란다.
또한 기존 기능에 새로운 요소가 추가되거나 새로운 기능이 출시되니, 매월 업데이트되는 Braze Release Note를 확인하는 것을 추천한다.
*글의 원문은 최영아님의 브런치스토리 에서도 읽어보실 수 있습니다.
September 27, 2024
엑셀과 스프레드시트를 어느정도 다루시던 분들은 조건부 서식에 어느정도 익숙하실 겁니다.
조건부 서식은 데이터를 보다 효과적으로 표현하고 분석하는 강력한 기능입니다. 이는 특정 조건에 따라 셀의 모양(글자 색상, 셀 색상)을 자동으로 변경하여 중요한 정보를 시각적으로 돋보이게 만드는 기능입니다.
위의 이미지 예시를 보면 더 쉽게 이해할 수 있습니다. 왼쪽은 아무런 설정을 하지 않은 차트라면 오른쪽은 숫자의 백분위수를 기준으로 색상을 표현하였습니다. 오른쪽의 표가 일자별 노출수의 차이를 훨씬 쉽게 이해할 수 있습니다.
조건부 서식의 가장 큰 특징은 데이터에 기반한 동적인 시각화입니다. 사용자가 정의한 규칙에 따라 데이터가 변경될 때마다 서식도 자동으로 업데이트됩니다. 이는 단순히 정적인 색상이나 서식을 적용하는 것과는 다르게, 항상 최신 데이터를 반영한 시각적 표현을 제공합니다.
루커스튜디오와 같이 실시간으로 변하는 데이터 시각화 솔루션에서는 필수적으로 활용하면 좋을 기능입니다.
루커스튜디오에도 이러한 조건부 서식이 있으며 다른 엑셀과 Tableau와 같은 BI와 유사한 기능을 사용할 수 있습니다.
기본적으로 'Tablea' 차트와 'Score' 차트에서 활용가능합니다.
단색과 색상스케일에 따라 구분할 수 있습니다. KPI 달성이나 임계값 달성에 대한 강조를 원한다면 단색 유형이 유용합니다. 반면에 데이터의 양이 많고 데이터간 상대적 차이가 중요하다면 색상 스케일이 유용합니다.
규칙별로 하나의 조건만 가능하며 조건 형식은 셀 또는 전체 행에 적용할 수 있습니다.
September 24, 2024
대부분의 비즈니스 문제는 두 가지 이상의 데이터 소스들을 결합하여 바라봐야 의미 있는 인사이트를 도출 할 수 있습니다. 광고데이터와 성과데이터, 매출데이터와 쿠폰데이터, 사용자데이터와 행동데이터 등과 같은 여러 데이터 조합들이 존재합니다.
루커 스튜디오의 기능 중 하나인 데이터 혼합은 두 가지 이상의 데이터간 결합을 제공하는 기능으로 데이터 시각화 및 분석을 더욱 풍부하게 만들어줍니다.
이러한 데이터 혼합 방식은 루커 스튜디오만의 방식은 아닙니다. 이미 데이터를 처리하는 데이터베이스 분야에서는 널리 사용 되고 있습니다.
이번 시간에는 루커 스튜디오 데이터 혼합 기능에 대해 배워보고 루커 스튜디오의 대시보드를 더 깊이 있게 만들어봅시다.
데이터 혼합 방식을 처음 접한 분들은 개념이 생소할 수 있습니다. 뿐만 아니라 이미 SQL 등을 공부하면서 혼합 방식을 알고 계셨던 분들도 혼합은 헷갈리는 개념입니다. 먼저 루커 대시보드를 가지고 혼합에 대한 개념을 가볍게 살펴보도록 하겠습니다.
루커 스튜디오가 제공하는 5가지 조인방식을 동일한 데이터를 가지고 혼합하였을 경우 혼합된 최종 결과 데이터가 어떻게 구성되는지 시각적으로 본다면 이해가 좀 더 편할 것입니다. 아래의 이미지에 각 혼합 방식에 따른 결과 테이블을 살펴보실 수 있습니다.
먼저, 루커 스튜디오 데이터 혼합에 대한 기본적인 내용을 알아보겠습니다. 데이터 혼합은 최대 5개의 데이터 소스를 조합하여 차트를 생성하는 기능을 제공합니다. 이를 통해 서로 다른 데이터 소스 간 측정항목과 측정기준을 조합한 새로운 차트를 만들 수 있습니다. 데이터 혼합을 통해 다양한 데이터 소스의 구성을 효과적으로 활용할 수 있습니다.
1) 기본 : 리소스 → 혼합 소스 관리 클릭 후 데이터 혼합
2) 설정탭 이용 : 설정탭 내 데이터 소스 → 데이터 혼합 클릭
3) 차트 이용 : 두 개 이상의 차트를 선택하고 데이터 혼합 생성
혼합 데이터는 루커 스튜디오의 데이터 혼합 부분에서 새롭게 만들거나 업데이트를 합니다. 해당 화면과 데이터 혼합에 대한 기본적인 구성을 살펴봅시다.
왼쪽 조인 유형은 가장 기본적인 조인 유형입니다. 왼쪽 테이블을 기준으로 모든 행을 반환하고 오른쪽 테이블은 왼쪽 테이블 조인 키값과 일치하는 행만 반환합니다.
ㅤ
오른쪽 테이블에 존재하지 않거나 누락된 값은 최종 혼합 데이터에서 빈칸 혹은 null 값으로 표현됩니다.
라이트 아우터 조인 유형은 왼쪽 조인과 반대로 오른쪽 테이블을 기준으로 왼쪽 테이블을 모두 반환합니다. 테이블이 반대일 뿐 왼쪽 조인과 동일한 원리 입니다.
활용
2개의 보완적인 데이터 소스로 하나의 데이터 소스를 만드는 경우에 활용 가능합니다. 예를 들어 아래와 같이 매출 데이터 소스를 확장하기 위해 한쪽에는 유저 소스를 레프트 조인으로(키값은 유저 ID), 다른 한쪽에는 제품 정보 소스(키값은 판매 상품)를 넣어서 매출 데이터 분석을 더욱 풍부하게 만들 수 있습니다.
ㅤ
ㅤ
내부 조인은 두 테이블 조인 조건 모두에서 일치하는 행만 반환합니다. 각 데이터 세트의 다른 모든 행은 제거됩니다.
두 데이터 소스 간 중복에 관련한 분석을 하는 경우 많이 사용합니다. 또한 동적 데이터로 데이터 세트를 필터링하는 쉬운 방법입니다.
ㅤ
활용
ㅤ
ㅤ
외부 조인은 조인 조건이 충족되지 않더라도 두 테이블의 모든 행을 반환합니다.
활용
데이터베이스(1st party data)와 GA4(3rd party data)를 연결하며 데이터베이스에서 삭제된 데이터가 GA4에서 확인 가능하며 GA4에서 추적되지 않은 값이 데이터베이스에서 확인 가능합니다. 이처럼 양쪽의 환경을 모두 고려해야 하면서 서로의 데이터가 모두 필요한 경우 사용합니다.
ㅤ
ㅤ
교차 조인은 모든 테이블 행의 데카르트 곱(두 소스의 모든 구성요소의 순서 쌍)을 반환합니다.
즉, 왼쪽 데이터 세트의 모든 행은 반복을 통해 오른쪽 데이터 세트의 모든 행과 곱해져서 데이터를 반환합니다.
이는 조인 키가 필요하지 않은 유일한 조인 유형입니다.
ㅤ
활용
데이터의 각 행 별 조인이 필요한 경우에 사용합니다.
데이터 혼합의 조인 조건을 잘 활용한다면 분석을 더욱 풍부하게 만들어주는 게임 체인저가 될 수 있습니다.
이러한 기능을 활용하기 위해서는 처음에는 까다로울 수 있지만 연습해서 여러분들 것으로 만든다면 멋진 대시보드를 만드시는데 도움이 되실 겁니다.
September 20, 2024
데이터마케팅이란 [데이터]를 활용하여 마케팅한다는 뜻으로 데이터드리븐마케팅(Data-driven Marketing)으로도 불릴 수 있습니다.
데이터마케팅과 데이터드리븐마케팅의 차이는 미미하나, 데이터마케팅은 데이터를 [활용] 하나 데이터 드리븐 마케팅은 데이터를 [기반]으로 하기에 후자에서 데이터의 중요성이 좀 더 강조된다고 볼 수 있습니다.
데이터마케팅을 위해서 필수 조건은 (당연하게도) 데이터 분석입니다. 결괏값을 측정하여 데이터를 잘 쌓아두고, 보유한 데이터를 가공하여 상황을 해석하고 문제나 개선점에 대한 인사이트를 도출하는 것이 기본적인 데이터 마케팅의 프로세스라고 할 수 있습니다.
그로스 조직(=그로스팀)은 기본적으로 데이터 드리븐 마케팅을 하기에, 프로세스가 같습니다.
데이터를 기반으로 가설을 세우고, 실험을 바탕으로 검증하고, 배움을 축적하는 과정을 빠르게 반복합니다. (*출처: 양승화 님의 그로스해킹)
마티니에서 진행한 컨설팅 프로젝트였던, 패션 커머스를 기준으로 데이터마케팅의 사례를 보겠습니다.
커머스 내에서도 여러 안건의 데이터 분석이 있는데요. 1. 에디토리얼(=콘텐츠), 2. 프로모션 간의 비교 (미드세일 vs 시즌오프세일), 3. 주요 대시보드 (KPI, AARRR) 4. 특정 프로모션 (블랙프라이데이) 등입니다.
블랙프라이데이 프로모션을 세부 사례로 앰플리튜드(Amplitude)를 활용했던 분석을 예시로 들어보겠습니다.
*앰플리튜드는 SaaS(Software as a Service: 클라우드 기반의 소프트웨어 제공 모델) 솔루션으로 웹/앱 서비스 내의 사용자 행동 분석을 할 수 있는 프로덕트 애널리틱스(Product Analytics)입니다.
왜 프로모션 데이터 분석을 해야 할까요? 그 배경부터 먼저 짚고 넘어갑시다.
다양한 형태로 스스로에게 질문을 해봅니다.
Q. 프로모션을 기획하고 운영한 후 가장 궁금한 것은?
Q. 프로모션을 운영한 이유는 무엇일까요?
여러 가설을 세워봅니다.
이렇게 물어보면, 보통은 '셋 다'라고 대답하는 경우가 많은데요. 충분히 이해는 하지만(^^...!) 우선순위는 정해야 합니다. 대개 우선순위는 [매출]이기에, 매출 관련 분석을 먼저 진행합니다.
매출의 기본적인 구성 요소를 먼저 파악합니다.
*건단가와 객단가
건단가와 객단가는 혼용되어 쓰이기도 하는데요. 주문[건]의 건, 고[객]의 [객]을 생각하시면 됩니다.
즉 어제 제가 배민에서 점심 주문 건으로 1.5만 원을 쓰고 저녁 주문 건으로 2.5만 원을 썼다면 일 기준 제 건단가는 [1.5만 원] / [2.5만 원] 두 건일 것이고 제 객단가는 [1.5만 원]+[2.5만 원]의 4만 원이 될 수도 있습니다.
*물론 건단가와 객단가는 내부적으로 정의하기 마련입니다! 일간 건단가를 평균으로 낼 수도 있으니까요.
이에 따라 앰플리튜드(Amplitude)에서 매출, 주문수/건단가, 구매자수/객단가로 그래프를 구성합니다. 우선 매출로 전체적인 추이를 보고 주문수/건단가, 구매자수/객단가를 개별로 쪼개보는 것이죠.
해당 프로모션에서는 객단가와 건단가가 유사한 추이를 보이기에 특이 사항이 없다고 판단되었지만, 가끔 특정 프로모션에서 객단가와 건단가의 차이가 크게 발생하는 경우도 있습니다. (리셀러의 등장?!)
이외 위 그래프에서 두 개의 선이 있는데요. 데이터를 볼 때의 꼭 필요한 [비교 기준]입니다. 비교 기준은 사용자 특성이 될 수도, 행동이 될 수도 있지만 [기간]을 가장 기본적으로 고려합니다.
*비교 기준: 기간(일간, 주간, 월간, 분기, 반기, 연간… 시즌성 고려!
블랙프라이데이 프로모션의 분석이라면 전년도 11월과 비교하는 것 vs 전월인 10월과 비교하는 것 - 어떤 것이 더 합리적일까요? 당연히 전년도일 것입니다.
전월 10월과 당월 11월의 할인율, 마케팅 수준 등이 다를 테니까요. 물론 전년 대비 회원수도 브랜드수도 많아졌고 등의 변동 요인들이 많아 YoY만 비교하는 것이 의미가 없다고 판단된다면 결국 전년도(YoY)/전월(MoM)/전주(WoW) 등 비교 대상들이 많아질 수 있습니다.
현대의 직장인이라면 대개 모든 업무의 결과를 [숫자]로 보긴 합니다. 그렇다면 그냥 숫자를 확인하는 것과 데이터 분석의 가장 큰 차이점은 무엇일까요?
예를 들어 2024년 7월 A 커머스의 구매 전환율 (메인 페이지 조회 > 결제 완료)이 10%라고 했을 때, 어떤 해석을 할 수 있을까요?
전월 대비 높아졌다/낮아졌다, 전년 대비 높아졌다/낮아졌다의 판단을 위해서는 전월 데이터, 전년도 데이터가 필요합니다.
전년, 전월, 전주의 데이터를 보며 추이를 확인했을 때 눈에 띄게 높거나/낮은, 혹은 변동이 생기는 시점을 찾아내어 그 배경이 무엇이었는지 파악하는 것이 필요합니다.
혹은 유사한 프로모션이 진행되었던 시기와 비교하여 구매전환율이나, 구매수, 유입수, 가입수 등의 주요 지표에서 차이가 있었는지를 파악해 보는 것도 좋습니다.
프로모션 vs 프로모션 간의 비교 외에도 uiux를 개선하거나 특정 기능을 배포했을 때 그 시점 이후의 변화가 있는지를 확인할 수 있습니다.
매출을 구매수와 건단가, 구매자수와 객단가로 나눠 전반적인 추이를 파악한 후 세부 분석을 진행합니다.
매출은 결국 [사용자]가 [상품]을 [구매]하여 발생하는 결괏값입니다. 이에 따라 [사용자]와 [상품]의 측면에서도 분석을 진행합니다.
: 카테고리별, 브랜드별, 상품별 매출 추이
프로모션의 매출을 브랜드, 카테고리 등으로 나눠서 확인해 봅니다.
더 개별적으로는 브랜드를 기준으로 매출과 월별 성장률 등을 확인하며 주요 브랜드를 도출하기도 합니다.
프로모션을 진행하고 쌓인 데이터를 분석하고 회고하고 다음 프로모션에 적용하는 것. 그것이 프로모션 측면에서 데이터를 활용한 데이터 마케팅이라고 볼 수 있습니다.
이외 데이터마케팅은 어떤 부문에도 동일하게 적용됩니다. 정량적인 수치로 표현되는 데이터를 쌓고, 데이터를 여러 측면에서 분석하고, 다음 유사 업무 시 배운 점을 적용하고 또 실험하는 것입니다.
September 12, 2024
MRR 이란?
Monthly Recurring Revenue로 월간 반복 매출 구독형 서비스의 경우 핵심 지표로 활용됩니다.
요즘은 누구나 한 번 쯤은 구독형 서비스를 결제해본 경험이 있을텐데 구독 비즈니스는 사용자가 카드를 등록하면 자동으로 월마다 반복적으로 자동결제가 됩니다. (유튜브 프리미엄 구독, 넷플릭스 등)
MRR 산출 공식은 다음과 같습니다.
예를 들면, 유튜브 프리미엄 월 구독 비용이 10,000원이고 이용자가 10명이라면 MRR 은 100,000원이 되는 겁니다.
그런데 MRR이 100,000원 입니다. 에서 끝나면 안되겠죠?
MRR이 어떻게 변화했는지를 분석하는 것도 굉장히 중요합니다.
MRR 지표를 쪼개보면 아래와 같습니다.
그러면 이 지표를 가지고 우리는 New MRR을 구할 수 있습니다.
실무하면서 MRR calculation 하는데 고생 고생을 했는데 이 방법으로 도움을 받을 수 있는 누군가를 위해 공유합니다.
우선 방법은 DB에 적재된 결제 데이터와 구글 스프레드 시트에서 세금계산서로 처리되는 고객 결제 내역을 바탕으로 Rawdata를 준비하고 파이썬을 활용해서 frequency(월결제, 연결제)를 flatten 해주고 이를 Looker Studio로 시각화해줍니다.
1. DB에서 데이터 불러오기
2. 구글 스프레드시트에서 데이터 불러오기
3. 파이썬으로 데이터 클렌징하기
4. 클렌징한 DB, 스프레드시트 데이터 합치기
5. 클렌징 결과 시각화를 위해 구글 스프레드시트로 보내기
6. Looker Studio로 시각화 하기
아래는 실제 활용했던 MRR 대시보드 입니다.
첫 번째 대시보드는 월매출과, MRR 지표 현황을 살펴보고 고객사별 월간 구독 현황을 확인 해볼 수 있고 ARR, 이번달 예상 매출도 확인 할 수 있게 구성했었습니다.
두 번째 대시보드는 위에서 언급한 대로 MRR 지표를 쪼개서 모니터링하는 대시보드입니다.
이를 통해 MRR 상승하는데 어떤 지표 때문에 상승했는지 한눈에 볼 수 있습니다. 또한 MRR 성장추이도 함께 볼 수 있도록 구성되었습니다. 이제 이 대시보드를 통해서 전사가 우리 서비스의 MRR 현황을 볼 수 있고 이탈한 유저수가 특히 많았던 월에는 고객 인터뷰를 진행해보거나 해당 유저들의 특징을 파악해보면 어떤 지점에서 불편함을 느꼈는지 페인 포인트는 무엇이었는지 원인을 파악하고 제품 개선에 반영해볼 수 있겠죠?
DB에서 결제 데이터를 위의 컬럼만 파이썬 SQLAlchemy 를 활용해서 데이터를 불러옵니다.
SELECT
user_id,
company_name,
sales,
pay_datetime_id,
freq,
user_status
FROM
(SELECT tmp2.user_id,
tmp2.company_name,
tmp2.id,
tmp1.sales,
date_format(tmp1.auth_date, '%Y-%m-01') auth_date,
tmp1.subscription_id,
tmp1.user_status,
tmp1.card_updated_at
FROM
(SELECT B.user_id,
A.sales,
A.auth_date,
A.subscription_id,
A.user_status,
A.card_updated_at
FROM
(SELECT st0.*,
st1.user_status,
st1.card_updated_at
FROM
-- billing_payment_history 테이블에서 조건에 맞는 데이터 가져오기
(SELECT billing_id,
subscription_id,
auth_date,
IF(cancelled_at IS NULL ,amount, IF (amount <= cancellation_cancel_amount,0,cancellation_remain_amt)) AS sales
FROM nicepay_billing_payment_history
) st0
left join
-- nicepay_card_info 테이블에서 가장 최근의 카드 정보(created_at이 최대인)를 가져오며, user_status를 is_deleted와 is_active 값에 따라 ‘churned_user’ 또는 ‘active_user’로 설정
(select t1.billing_id,
t1.created_at,
t1.is_deleted,
t1.is_active,
t1.created_at card_updated_at,
(case when t1.is_deleted = 1 and t1.is_active = 0 then 'churned_user' else 'active_user' END) user_status
from nicepay_card_info t1
inner join (select billing_id,
max(created_at) max_date
from nicepay_card_info
group by 1) t2
on t1.billing_id = t2.billing_id and t1.created_at = t2.max_date) st1
ON (st0.billing_id = st1.billing_id)) A
LEFT OUTER JOIN
(SELECT billing_id,
user_id
FROM nicepay_billing_info) B
ON (A.billing_id = B.billing_id)) tmp1
INNER JOIN (SELECT user_id,
company_name,
id
FROM user
) tmp2
ON (tmp1.user_id = tmp2.user_id)) tmp3
inner join
(select seq,
freq,
date_format(plan_start_datetime_id, '%Y-%m-01') subscription_plan_start_datetime_id
from subscription) tmp4
ON (tmp3.subscription_id = tmp4.seq)
where sales > 0;
먼저 계산을 위해 필요한 데이터를 DB에서 추출해주고 flatten을 해줄껍니다.
flatten이 뭐냐 뜻 그대로 평탄화한다는 의미입니다. 예를들어 고객이 연간 결제를 1,200,000원을 했다고 하면 월별 결제액은 12개월로 나눠서 월별로 데이터를 평탄화 해주는 과정이라고 보시면 됩니다.
#expand the yearly records
mrr_base = mrr_df.loc[np.repeat(mrr_df.index, mrr_df['freq'].map({"years":12,"months": 1}))]
mrr_base.loc[mrr_base["freq"] == "years", "sales"] /= 12
mrr_base.loc[mrr_base["freq"] == "years", "pay_datetime_id"] += \
mrr_base.groupby(["user_id", "freq"]).cumcount().loc[mrr_base["freq"] == "years"]\
.map(lambda i: pd.DateOffset(months=i))
저의 경우 DB에 기록되지 않은 세금계산서 데이터를 가지고 있었는데
해당 데이터는 구글 스프레드시트에 기록하고 있으므로 구글 스프레드시트에서 데이터를 불러옵니다.
#구글 스프레드시트에서 data load하기
scope = ['https://spreadsheets.google.com/feeds',
'https://www.googleapis.com/auth/drive']
credentials = ServiceAccountCredentials.from_json_keyfile_name(
'credential 파일 경로(json파일)', scope)
gc = gspread.authorize(credentials)
spreadsheet_url = "가져올 스프레드시트 주소"
gc1 = gc.open_by_url(spreadsheet_url).worksheet('시트 이름')
#기존에 기록된 데이터 가져와서 리스트 형태로 리턴
gc2 = gc1.get_all_values()
#데이터프레임으로 판다스로 가져오기
gc2 = pd.DataFrame(gc2, columns=gc2[0])
gc2 = gc2.reindex(gc2.index.drop(0))
#날짜 형식으로 변경
gc2['pay_datetime_id'] = pd.to_datetime(gc2['pay_datetime_id'])
#sales 컬럼 숫자로 변경
gc2['sales'] = gc2['sales'].astype(str).astype(int)
#expand the yearly records
gc2_base = gc2.loc[np.repeat(gc2.index, gc2['freq'].map({"years":12, "months": 1, "2years":24}))]
# compute monthly fee and join date
#years 12개월로 나누기
gc2_base.loc[gc2_base["freq"] == "years", "sales"] /= 12
gc2_base.loc[gc2_base["freq"] == "years", "pay_datetime_id"] += \
gc2_base.groupby(["user_id", "freq"]).cumcount().loc[gc2_base["freq"] == "years"] \
.map(lambda i: pd.DateOffset(months=i))
#2years 24개월로 나누기 / years 12개월로 나누기
gc2_base.loc[gc2_base["freq"] == "2years", "sales"] /= 24
gc2_base.loc[gc2_base["freq"] == "2years", "pay_datetime_id"] += \
gc2_base.groupby(["user_id", "freq"]).cumcount().loc[gc2_base["freq"] == "2years"] \
.map(lambda i: pd.DateOffset(months=i))
gc2_base_result = gc2_base[['user_id', 'company_name', 'sales', 'pay_datetime_id', 'freq']]
출처: https://botongsaram.tistory.com/entry/B2B-SaaS-MRR-계산하기 [알랭드보통사람:티스토리]
위의 과정에서 DB에서 불러온 데이터와 구글 스프레드시트의 데이터 형태를 통일 시켰습니다.
이제 Raw Data를 만들기 위해서 합쳐줍니다.
#구글시트rawdata와 DB에서 불러온 데이터의 결합
df_union= pd.concat([mrr_result, gc2_base_result])
데이터를 통합한 다음에 데이터 시각화를 위해 데이터 시각화를 위해 스프레드시트에 최종 정리된 데이터를 다시 구글 스프레드시트로 전달합니다.
# union 된 결과를 다시 구글 스프레드시트로
scope = ['https://spreadsheets.google.com/feeds',
'https://www.googleapis.com/auth/drive']
credentials = ServiceAccountCredentials.from_json_keyfile_name(
'credential 파일 경로(json)', scope)
gc = gspread.authorize(credentials)
spreadsheet_url = '스프레드시트주소
gc_mrr = gc.open_by_url(spreadsheet_url).worksheet('새로 데이터를 업로드할 시트명')
#기존에 기록되어 있던 데이터 삭제(처음 업로드할 때는 필요 없음)
gc_mrr.clear()
# 오늘 가져온 데이터 업로드
gd.set_with_dataframe(gc_mrr,merge)
위의 결과는 MRR 대시보드 예시(1)에서 활용했던 과정입니다.
다음으로 MRR detail view 에 사용될 지표를 만들 차례입니다.
# pay_datetime_id 열에서 월과 연도를 추출하여 새로운 열 생성
mrr_detail_result['month_year'] = mrr_detail_result['pay_datetime_id'].dt.to_period("M")
# max_date 컬럼 만들기
mrr_detail_result['max_date'] = pd.NaT
mrr_detail_result.head()
# user_id를 기준으로 그룹화
grouped = mrr_detail_result.groupby('user_id')
# 각 그룹에서 최대 결제일을 찾고 max_date 열에 할당
for name, group in grouped:
max_date = group['pay_datetime_id'].max()
max_index = group['pay_datetime_id'].idxmax()
mrr_detail_result.loc[group.index, 'max_date'] = max_date
# 데이터프레임을 corporate_id와 pay_datetime_id 기준으로 정렬
transactions_ver2 = mrr_detail_result.sort_values(by=['corporate_id', 'pay_datetime_id'])
# 이전 거래 금액을 저장할 새로운 열 prev_amount 추가
transactions_ver2['prev_amount'] = transactions_ver2.groupby('corporate_id')['sales'].shift(1)
# 사용자 상태를 저장할 새로운 열 user_status 추가
transactions_ver2['mrr_status'] = 'new'
# 이전 거래 금액과 동일한 금액을 가진 사용자에게 'existing' 할당
transactions_ver2.loc[transactions_ver2['sales'] == transactions_ver2['prev_amount'], 'mrr_status'] = 'existing'
# 이전 거래 금액보다 높은 금액을 가진 사용자에게 'upgrade' 할당
transactions_ver2.loc[transactions_ver2['sales'] > transactions_ver2['prev_amount'], 'mrr_status'] = 'upgrade'
# 이전 거래 금액보다 낮은 금액을 가진 사용자에게 'downgrade' 할당
transactions_ver2.loc[transactions_ver2['sales'] < transactions_ver2['prev_amount'], 'mrr_status'] = 'downgrade'
# 이전에 'churned' 상태였고 이제 새로운 거래가 있는 사용자에게 'reactivation' 할당
# 모든 고유한 corporate_id 값을 포함하는 리스트 생성
corporate_ids = transactions_ver2['corporate_id'].unique()
# 각 corporate_id에 대해 루프를 돌며 각 거래의 상태를 업데이트
for corporate_id in corporate_ids:
user_data = transactions_ver2[transactions_ver2['corporate_id'] == corporate_id]
for i in range(1, len(user_data)):
prev_month = user_data.iloc[i-1]['month_year']
curr_month = user_data.iloc[i]['month_year']
if (curr_month - prev_month).n > 1:
transactions_ver2.loc[(transactions_ver2['corporate_id'] == corporate_id) & (transactions_ver2['month_year'] == curr_month), 'mrr_status'] = 'reactivation'
# user_status가 churn_user인 사용자의 마지막 결제일에 'churn' 상태 할당
transactions_ver2.loc[(transactions_ver2['user_status'] == 'churned_user') & (transactions_ver2['pay_datetime_id'] == transactions_ver2['max_date']), 'mrr_status'] = 'churned'
transactions_ver2.head()
이제 MRR 대시보드 예시(2)에 활용된 데이터가 전처리되었고 구글 스프레드시트로 데이터를 적재해주면 됩니다.
이걸 매일 하기는 귀찮으니 Airflow DAG를 활용해서 자동화해주면 됩니다.
여기까지 MRR계산을 위해 SQL, 구글 스프레드시트, python을 활용한 과정을 소개해봤습니다.
누군가 B2B SaaS에서 MRR 계산을 위해 고군분투하고 계시다면 이 코드가 도움이 되시면 좋겠네요
추가로 초기에 대시보드 기획에 많은 참고가 되었던 Baremetrics라는 MRR 대시보드 외산 툴이 있는데 상당히 잘만들었다고 생각되는 서비스입니다.
SaaS 비즈니스를 운영하신다면 참고해보시면 좋을 서비스네요!
September 9, 2024
분석 데이터의 하위 집합입니다.
사용자, 세션, 이벤트 데이터를 분리해서 세그먼트를 정의하면 분석하고자 하는 대상을 쉽게 정의할 수 있게 만드는 기능입니다.
세그먼트를 정의하게 되면 특정 유저의 그룹 vs 나머지 유저의 특징을 비교 분석해 볼 수 있습니다.
GA4에서 세그먼트 기능을 통해 웬만한 유저들의 특징을 잡아낼 수 있습니다.
그런데 GA4가 어떻게 유저들의 행동 데이터를 수집하고 활용하는지 제대로 알지 못하면 활용하기 힘들겠죠?
이번 글에서는 큰 틀에서 GA4가 어떤 원리로 조건이 설정되는지 알아보려고 합니다.
세그먼트 생성화면을 들여다보면 꽤 많은 조건들을 설정할 수 있는 기능들이 많습니다.
일단 크게 3가지 유형의 세그먼트가 있습니다. (아래 유형에 대한 이해를 잘하셔야 합니다.)
세그먼트 유형 선택은 세그먼트를 조건에 해당하는 결과와 관련이 있는 거라고 생각하시면 이해하시기 쉬울 겁니다.
특히! 각 유형별로 소스 / 매체 선택할 때 주의할 점을 꼭! 숙지하시길 바랍니다!
이렇게 정의는 그럭저럭 이해는 할 수 있지만 역시 예시를 통해 어떻게 데이터가 선택되는지 알아보겠습니다.
특정 유저가 2개의 세션 안에서 몇 가지 이벤트를 발생시켰다고 가정해 보겠습니다.
1. 사용자 세그먼트 예시
사용자 세그먼트 기준으로 구매한 유저를 세그먼트를 만들면 어떻게 데이터가 선택될까요?
총 7개의 이벤트가 모두 선택됩니다. 사용자 기준이니까 조회한 날짜에 있는 이벤트가 모두 포함되기 때문입니다.
2. 세션 세그먼트 예시
최소 한 개의 구매 이벤트가 발생한 모든 세션의 데이터기 때문에 이 기준으로 충족되는 데이터는 purchase(구매) 이벤트가 발생한 session - 2 만 선택됩니다( session - 1 에는 구매 이벤트가 없음)
3. 이벤트 세그먼트 예시
이벤트 기준이면 기준에 맞는 이벤트만 선택한다는 말이기 때문에 session - 2에서 발생한 purchase 이벤트만!! 선택됩니다. 다른 이벤트는 선택 안됩니다!
여기서는 어떤 조건의 유저를 선택할지 셋팅하는 옵션을 선택할 수 있습니다.
여기서 AND, OR 조건을 선택할 수 있는데 회원가입과 구매 이벤트를 발생시킨 유저 선택해 보겠습니다.
그런데 하단에 보면 포함할 조건 그룹 추가라는 버튼이 있습니다.
동일한 방식으로 회원가입과 구매를 한 조건을 설정하면 이렇게 할 수 있죠
즉 하나의 조건 그룹에 회원가입 이벤트와 구매 이벤트를 선택한 것이고 나머지 하나는 두 개의 조건 그룹에 회원가입 이벤트와 구매 이벤트가 각각 설정을 했습니다.
첫 번째 방식과 차이점은 뭘까요? 동일한 결과가 나올까요? 결과는 동일합니다.
???
이게 무슨 말이냐면 그룹 간 영역에서 오른쪽 상단에 보면 사람모양의 드롭다운 버튼이 있습니다.
이걸 클릭하면 조건 범위를 지정할 수 있습니다.
세그먼트 설정할 때랑 동일한 방식이죠? 사용자, 세션, 이벤트 단위로 조건설정이 가능합니다.
세션 및 이벤트 세그먼트에는 조건 지정 범위에 대한 옵션이 더 적습니다. 아래 표는 사용할 수 있는 세그먼트 유형별 조건 범위 간 조합입니다.
다시 예시를 들어볼게요
GA4에서 첫 구매 유저를 따로 이벤트를 개발하지 않으면 특정하기 힘든데 회원가입 후 첫 구매 유저를 세그먼트로 한 번 만들어 보겠습니다.
해당 유저들을 특정하기 위한 필요한 이벤트는 first_visit(첫 방문)과 purchase 이벤트겠죠?
첫 구매 유저를 특정한다고 해도 이를 어떻게 정의하느냐에 따라 결과는 달라집니다! (주의!!)
유저의 행동은 정말 엄청나게 많은 경우의 수로 발생을 하죠.
A유저 : 구매 의사 결정이 빠른 A유저는 동일한 세션 시간 내에서 구매
B유저 : 구매 의사 결정이 느린 B유저는 어제 상품을 처음 둘러보고 내일 구매
이 예시처럼 첫 구매를 동일한 세션 시간내 첫 구매를 한 유저를 특정할 것인지, 세션에 상관없이 첫 구매 유저를 식별한 것인지 정의하기 나름입니다.
첫 구매 유저 세그먼트를 만든다면 어떤 조건을 설정해야 될까요?
사용자 세그먼트를 기준으로 세그먼트를 설정하면 유저의 조건에 해당하지 않는 방문데이터도 포함되게 됩니다.
그러니까 첫 방문 이후 첫 세션에 구매를 하지 않아도 구매를 특정시킨 뒤에 제외 조건을 구매 조건을 2번 이상으로 설정하여 첫 구매한 유저를 식별할 수 있습니다.
그런데 first_visit, 첫 구매 사용자의 방문 데이터만 확인하고자 한다면 사용자 세그먼트가 아닌 세션 세그먼트를 기준으로 동일 세션 내의 조건 범위를 선택하여 세그먼트를 생성해야 합니다.
첫 구매 유저를 모든 세션 범위 조건으로 하여 세그먼트를 생성하고 재구매자(purchase 이벤트가 2번 이상)와 겹치는 부분이 없는지 세그먼트 중복 기능을 활용해 벤다이어그램으로 확인해 보겠습니다.
중복 없이 잘 나뉘었습니다. 이런 식으로 내가 가진 유저의 특징을 세그먼트로 만들어서 비교해 보는 과정이 굉장히 중요한 것 같습니다.
이 개념을 토대로 한 번 만들어 보시면 좋을 것 같습니다.
GA의 세그먼트는 생각보다 할 수 있는 게 많긴 합니다.
하지만 제대로 활용하기 위해서는 위에 설명한 개념들이 잘 정리가 되어 있어야 됩니다.
이번 글을 쓰면서 느낀 건 솔직히 GA4는 사실 Amplitude를 사용해 봤다면 이런게 다 있나 싶을 정도로 불편하고... 뭔가 찜찜한 느낌을 지울 수 없었습니다.
이번 글에서 첫 구매 유저 세그먼트를 예시로 들었는데 GA4에서 First time purchases라는 측정항목이 존재하긴 하지만
이를 세그먼트로 활용은 못합니다... 약간 독립적인 측정항목 같은 느낌입니다.
Amplitude에서는 사실 아주 간단하게 첫 구매 유저를 특정할 수 있는 Historical Count 기능이 있어서.. 아쉬웠습니다.
(물론 제약 조건은 있습니다. 날짜 범위가 시작되기 전 최대 1년까지 기간만 포함됩니다. 그래도 이건 혁명적인 기능!)
본질적으로 GA의 목적은 유저 획득에 초점을 맞춰져 있다면 Amplitude는 Product Analytics 툴로 사용자 행동 분석에 초점이 맞춰져 있긴 합니다. 그래서 목적에 맞지 않아서 해당 기능 개발을 하지 않은 건가 싶기도 합니다.
당장 앰플리튜드를 도입하지 않을 거라면 속 편하게 GA4에서 First Purchase 이벤트 개발을 요청하거나 혹은 일단 소개드린 방식대로 우선 트렌드만 확인하는 용도로 세그먼트를 생성해서 데이터를 분석하시는 걸 권장드립니다.
September 4, 2024
Google Analytics를 사용해 보셨다면 ‘세션’이라는 용어에 익숙하실 것입니다. Universal Analytics(GA3)에서는 세션 단위로 데이터를 수집하여 지표를 측정했지만, GA4에서는 데이터 수집 방식이 달라져 주의가 필요합니다. GA4의 세션 관련 지표는 혼란을 일으킬 수 있습니다.
이번 글에서는 세션의 개념을 자세히 살펴보고, GA4에서의 세션이 어떻게 다른지 알아보겠습니다.
세션 관련해서 구글 가이드 문서에 따르면
- 세션은 사용자가 웹사이트 또는 앱과 상호작용하는 기간입니다.
- 세션은 사용자가 앱을 포그라운드에서 열거나 페이지나 화면을 보고 현재 활성화된 세션이 없는 경우 시작됩니다.
- 세션 수 : 고유 세션 ID 수를 추정하여 사이트나 앱에서 발생하는 세션 수를 계산합니다.
예를 들어 유저가 브라우저 탭에서 페이지를 열고 이메일을 확인하거나 다른 일을 하다가 2시간 뒤에 다시 돌아와서 브라우징을 할 수 있겠죠? GA4에서는 이를 페이지 뷰가 있는 세션으로 보고 2시간 뒤에 사용자 참여로 간주하고 새로운 세션으로 기록합니다.
1. 첫 번째 세션:
2. 두 번째 세션:
이때 새로운 세션이 시작되지만 페이지 조회 이벤트는 기록되지 않습니다.→ 두 번째 세션이 사용자 참여로만 기록됩니다
빅쿼리로 실제 어떤 케이스인지 특정 유저의 로그를 한 번 확인 해보겠습니다.
이렇게 페이지뷰 이벤트가 없는 두 번째 세션이 생기며, 이는 참여율(Engagement Rate) 지표로 나타납니다.
참여율 = 참여 세션 수 / 총 세션 수
이런 유저가 많아지면 세션 기반의 지표(예: 세션당 페이지뷰, 세션당 평균 참여시간)가 낮아집니다.
세션당 페이지뷰 수 계산 예시:
페이지뷰 수 / 세션 수 = 10 / 1 = 10
위와 같은 유저의 행동이 늘어나면:
페이지뷰 수 / 세션 수 = 10 / 2 = 5
페이지뷰 이벤트가 포함되지 않은 세션이 발생하니 지표가 감소하게 됩니다.(분모가 커지므로)
따라서 GA3에서 사용하던 세션 기반의 지표는 주의해서 사용해야 하며, 이벤트나 참여 관련 지표(참여 세션)를 보는 것이 좋습니다.
(GA4와 GA3의 데이터 수집 방식도 다릅니다)
자.. 그리고 또 있습니다.
세션 데이터의 현실.. 빅쿼리를 열어보면 .. 더 조심해야겠구나 라는 생각이 들겁니다.
일단 절대 세션수 ≠ session_start 이벤트의 수 가 아닙니다.
왜그런지 직접 조회해보죠!
아래 특정 유저의 세션을 특정해서 조회해봤습니다.
event_name 컬럼에 session_start 이벤트는 없고 다른 이벤트만 있죠?
이런 상황은 빈번하지 않지만 발생할 수 있습니다. 하나의 세션에 두 개의 세션 이벤트가 발생했고, 심지어 사용자 아이디도 다릅니다.
GA4 인터페이스에서는 당연히 단일 세션으로 계산하지 않을 것 같지만 빅쿼리에서는 이런 케이스 때문에 user_pseudo_id와 ga_session_id를 조합해서 각 세션에 대한 고유 식별자를 만들어서 session 을 카운팅 해야됩니다.
concat(user_pseudo_id, (select value.int_value from unnest(event_params) where key = 'ga_session_id')) as session_id,
GA3에서는 세션 윈도우(30분)가 지나면 완전히 새로운 세션이 시작되지만, GA4에서는 기존 세션이 계속 되기 때문에 이렇게 소스가 1개 이상 발생할 수 있습니다.
구글 애널리틱스에서도 세션수를 집계할 때 추정값을 사용합니다.
실제로 빅쿼리에 count(distinct ga_sesssion_id) 를 집계하면 성능에 영향을 줍니다..
그런데 전 세계에서 이걸 조회하는데 이걸 진짜 집계를 ?? 불가능하죠
그래서 HyperLogLog ++ (가이드 링크)라는 알고리즘을 적용해서 추산한 값을 보여줍니다.
실제로 성능을 눈으로 확인해보죠
ga_session_id를 고유하게 카운팅 해보는 쿼리로 비교를 해보겠습니다.
COUNT(DISTINCT ga_session_id)
HLL_COUNT.EXTRACT(HLL_COUNT.INIT(ga_session_id, 14))
차이가 보이시나요? (참고로 데이터 하루치만 조회했고 쿼리 결과는 같습니다)
모든면에서 더 효율적인 처리를 하고 있음을 알 수 있습니다.
사실 GA4에서는 세션이라는 개념은 더 이상 의미가 없고 지금까지 위의 예시를 통해 확인할 수 있었습니다.
그럼에도 세션 지표를 무조건 써야된다면 참여 세션지표를 사용하는게 좋습니다.
이제 이걸 통해서 다음 글에서는 GA4의 꽃 세그먼트 분석에 대해서 알아보겠습니다.
(세그먼트 기능을 쓰려면 세션에 대한 이해가 꼭 필요하기 때문에 이번 글부터 시작하게 되었습니다.)
September 2, 2024
디지털 전환이란 무엇일까요? DT 또는 DX로도 불리는 디지털 전환은 Digital Transformation에서 유래했습니다. 여기서 Transformation, 전환은 상태의 변화를 말합니다. 즉 디지털이 아니던 것이 디지털 상태로 변화하는 것입니다.
디지털 전환, 어쩐지 거창합니다. 마티니의 그로스팀에서 큰 규모의 회사를 방문했을 때 주로 DX실, DT실이 명함에 기재된 경우가 많더라고요. 즉 큰 곳에서 시도하는 경우가 많다는 것이겠죠.
온라인 비즈니스는 진행 중입니다. 오프라인을 온라인으로 전환시키는 DX와 DT는 상당수 진척되었습니다. 평범한 일상만 생각해 봐도 그렇습니다.
즉 현재의 디지털 트랜스포메이션, 디지털 전환(DT, DX)의 주요 과제는 오프라인의 온라인 전환은 아닌 듯합니다.
우리 프로덕트의 사용자가 10명, 100명, 1,000명일 때는 수기가 가능할 수 있습니다. 10명에게는 매일 전화를 할 수도 있을 것이고, 100명에게는 문자를 보낼 수 있을 것이고, 1,000명까지는 어떻게 수기로 그룹화를 해서 카카오톡을 보낼 수도 있겠죠.
하지만 [10,000명] 에게는요? [100,000명] 에게는요? 예를 들어보겠습니다.
[CRM마케팅/수동]
#1 보유한 데이터베이스(DB)에 접근하여
#2 조건에 맞는 쿼리문을 작성하여#3 '고정된 시점'의 사용자 데이터를 추출함
#4 성과 분석 시, 동일 프로세스를 거쳐 특정 시점의 사용자 데이터를 재추출함
#5 엑셀 등을 활용하여 수기로 데이터 값을 비교함
[CRM마케팅/자동] *솔루션 활용
#1 보유한 데이터베이스(DB)를 CRM 솔루션의 클라우드에 연동하고
#2 CRM 솔루션의 어드민에서 변수를 조절하여 (클릭!)
#3 '실시간'으로 사용자 데이터를 추출함
#4 성과 분석 시 어드민에서 변수를 조절하여 (클릭!)
#4 솔루션에서 제공하는 대시보드/그래프 형태로 데이터 값을 비교함
[퍼포먼스마케팅/수동]
#1 광고 매체 별 광고관리자에서 성과를 엑셀로 다운로드 후
#2 보고용으로 맞춰둔 엑셀 형식에 맞춰 복붙 합니다. (ctrl+C, ctrl+V)
*매체 A, 매체 B, 매체 C, 매체 D.... 매체를 많이 쓸수록 이 절차는 많아집니다.
**혹시 글로벌이라면? 국가별로도 쪼개줘야 합니다.
***신규 사용자와 기존 사용자의 리타겟팅을 나눈다고요? 이것도 쪼개서...
#3 매체 성과와 자사 내부 DB 성과의 숫자가 맞지 않습니다.
기여 모델 및 기여 기간의 설정이 다르거나...
[퍼포먼스마케팅/자동]
#1 광고 매체 별 데이터를 연동합니다.
#2 광고 매체와 MMP, CRM 솔루션의 데이터를 통합합니다. (DW)
마케팅 업무 자동화, 마케팅 오토메이션(Automation)의 효율에 대해서 이야기를 종종 하게 되는데요. 업무 효율성을 높이는 것이 수익 상승에 기여하지는 않는단 의견을 종종 듣습니다.
문제 정의와 해결 방안 제시 및 대응. 문제 해결자(problem-solver)라는 직무도 존재하는 것처럼 사실 모든 직업은 분야와 내용과 형식이 다를 뿐, 어떠한 문제를 해결하는 것 아닐까요?
위의 사례로 들었던 CRM 메시지 수신자 추출도, 퍼포먼스마케팅 성과 분석도 고객(사용자)이 아닌 실무자에게 필요한 디지털 전환, 즉 마케팅 자동화의 일환인데요.
여러 기업들의 디지털 전환을 도우면서 가장 기본적이지만 가장 중요했던 것은 바로 '측정'입니다. 웹과 앱에서의 성과 측정을 위해 필수적인 것, 바로 UTM입니다.
웹페이지의 주소인 URL에 UTM 파라미터를 넣어 유입된 사용자들이 어떤 경로로 들어왔는지 파악할 수 있습니다.
보통 퍼포먼스 광고를 운영할 때 페이스북 광고관리자의 구성에 맞추어 캠페인/그룹/소재 단으로 구성하는 경우도 있습니다.
유상 광고(paid media)를 운영하는 퍼포먼스마케팅 외에, 인플루언서 마케팅(earned media)이나 유튜브/인스타그램/블로그 등에 자체 콘텐츠(owned media)를 게재할 때도 UTM을 삽입한 URL을 활용하면 좋습니다!
개인화 추천 시스템: 고객의 과거 구매 내역 및 검색 기록을 바탕으로 맞춤형 제품 추천
챗봇 및 가상 어시스턴트: 고객 문의 및 지원을 자동화하여 실시간으로 대응
고객 세그멘테이션: 고객 데이터를 분석하여 세분화된 마케팅 전략 수립
실시간 데이터 분석: 판매, 트래픽, 재고 등의 데이터를 실시간으로 분석하여 빠른 의사 결정 지원
스케일러블 인프라: 트래픽 변동에 유연하게 대응할 수 있는 클라우드 기반 인프라.
클라우드 기반 CRM: 고객 관계 관리 시스템을 클라우드에서 운영하여 언제 어디서나 접근 가능.
모바일 최적화 웹사이트 및 앱: 모바일 사용자를 위한 최적화된 사용자 경험 제공.
모바일 결제 시스템: 다양한 모바일 결제 옵션 지원.
온라인 및 오프라인 데이터 통합: 고객의 온/오프라인 행동 데이터를 통합하여 일관된 경험 제공.
클라우드 컴퓨팅, 증강 현실 (AR), 사물 인터넷 (IoT), 결제 기술, 로봇 프로세스 자동화 (RPA) 등이 디지털 전환에 필요한 주요 기술로 여겨집니다.
디지털 전환을 검색하면 정말 방대한 의미의 내용들이 나옵니다. 클라우드 컴퓨팅, 인공지능(AI)과 머신러닝(ML), 빅데이터 분석, 사물인터넷(IoT), 블록체인, 사이버 보안 등이 대표되는 단어죠.
생각해 보면 그로스 컨설팅이라고 꼭 디지털 전환이 완료된 상황에서만 될 수 있는 것은 아닙니다. 어느 영역의 디지털 전환이 그로스 컨설팅의 실행 방안이 될 수도 있는 것이죠.
Chat GPT가 생활화되고 AI에 대한 기사가 쏟아지는 요즘이지만, UTM을 잘 쓰는 것도 생각보다 어렵습니다. 디지털 전환을 위해 AI 도입보다 먼저인 것들이 있지 않을까요?
August 27, 2024
최근 데이터 시각화 및 분석 도구들이 발전하면서 사용자 친화적인 인터페이스가 중요해지고 있습니다. Google Looker Studio는 이러한 요구에 맞춰 다양한 기능을 제공하며, 그 중 하나가 선형(Line) 차트와 막대(Bar) 차트를 전환할 수 있는 토글 스위치 기능입니다. 물론 기본적인 제공 기능 아니지만 루커스튜디오의 기능을 활용하면 간단하게 구현 가능합니다.
데이터를 분석하는 과정에서 서로 다른 유형의 차트를 사용하면 데이터를 다양한 각도에서 시각화할 수 있습니다. 예를 들어, 막대 차트는 특정 시점의 데이터를 비교하는 데 유용하며, 선형 차트는 시간에 따른 변화를 나타내는 데 적합합니다. 이러한 차트들을 손쉽게 전환할 수 있는 기능이 있으면, 사용자는 더 쉽게 다양한 인사이트를 얻을 수 있습니다.
💡스위치 필터 사용 시 매개변수는 숫자형만 반응. 따라서 텍스트가 아닌 데이터 유형을 숫자로 설정하여 매핑.
💡BarIF(차트 타입 = 1, Revenue, null)
💡LineIF(차트 타입 = 2, Revenue, null)
이러한 토글 스위치 기능은 사용자에게 더 나은 데이터 시각화 경험을 제공합니다. 사용자는 필요에 따라 차트를 전환하며 데이터를 더 직관적으로 이해할 수 있습니다. 이는 데이터 분석의 효율성을 높이고, 더 나은 결정을 내리는 데 도움을 줍니다.
Google looker Studio에서 선형 및 막대 차트를 전환하는 토글 스위치를 구현하는 방법을 알아보았습니다. 이러한 기능은 데이터 분석을 더욱 직관적이고 효율적으로 만들어 줄 것입니다. 이제 여러분도 데이터 시각화를 한 단계 업그레이드 해보세요.
August 26, 2024
에어브릿지(Airbridge)는 데이터 수집부터 마케팅 성과 분석까지 하나의 대시보드에서 진행하는 마케팅 성과 분석 솔루션(MMP)으로, 라스트 터치 어트리뷰션, 멀티 터치 어트리뷰션, 마케팅 믹스 모델링 등 다양한 방법으로 앱과 웹사이트의 마케팅 성과를 함께 분석할 수 있는 통합 마테크 솔루션입니다.
오늘날 MMP 솔루션은 광고주들에게 필수적으로 사용되고 있습니다. 에어브릿지 역시 그 중 하나로, 별도의 연동 없이 통합된 데이터 분석과 어트리뷰션이 가능한 것이 강점입니다.
에어브릿지의 이벤트 구조는 타 플랫폼에 비해 비교적 복잡하기 때문에 이전에 GA4 및 타 분석 솔루션만을 사용하다 에어브릿지를 처음 접했다면 다소 혼란스러울 수 있습니다. 저 역시 꽤 헤맸던 것 같습니다.
이러한 계기로 에어브릿지 택소노미를 설계할 당시 이벤트 구조를 이해하는 데 실제로 도움이 됐던 자료들과 GA4의 구조를 비교하여 전체적인 구조를 설명드리고자 합니다.
ㅡ
에어브릿지의 이벤트 및 어트리뷰트 호출 코드의 작성 방법은 아래 세 가지 경로를 통해 확인하실 수 있습니다.
1. 에어브릿지 유저 가이드(Airbridge Help Center)
유저 가이드를 통해서도 코드를 작성하는 데에는 문제가 없지만 2번 자료의 코드 구조가 비교적 효율적이므로 가급적 2번 자료를 참고하시는 것을 권장드립니다.
2. AB180 깃허브(Github)
1번 유저 가이드의 코드 예시 보다 더욱 상세한 전체 코드를 확인하실 수 있습니다.
3. 유저 가이드 및 에어브릿지 공식 문서(Data Spec)
에어브릿지에서 제공하는 Event와 Attribute의 목록과 상세 정보들을 확인하실 수 있습니다.
에어브릿지 이벤트 구성 요소에는 카테고리(Event Category), 액션(Event Action), 라벨(Event Label), 밸류(Event Value), 어트리뷰트(Attribute), 트리거(Trigger)가 있습니다.
다소 복잡해 보이지만 조금만 들여다보면 이해하기 쉽습니다.
위 이벤트 구성 요소의 개념들이 조금 생소하신 분들은 GA4의 예시로 보면 이해하기 쉽습니다.
(GA4의 Metrics & Dimension에 대한 기본 개념이 궁금하신 분들은 관련 자료를 참고해 주세요)
- GA4 보고서
- Airbridge 보고서
GA4의 측정기준(Dimensions)이 에어브릿지의 카테고리, 액션, 라벨의 역할을 하고,
측정항목(Metrics)이 에어브릿지의 밸류의 역할을 한다고 비교해 볼 수 있습니다.
예를 들어, 의류를 판매하는 모 기업의 마케팅 담당자가 구매 이벤트 발생 시 아래와 같은 항목들의 데이터를 수집한다고 가정합니다.
Purchase
모두 시맨틱 어트리뷰트로 수집 가능한 항목들이지만 어트리뷰트의 경우 에어브릿지 리포트에서 확인할 수 없기 때문에 CDP와 같은 고객DB에 접근하거나 별도의 솔루션으로 전처리하여 확인해야 합니다. 이런 경우 유용하게 쓰일 수 있는 항목이 액션과 라벨입니다.
만일 자주 사용하는 어트리뷰트 항목을 에어브릿지의 리포트와 대시보드에서 활용하고자 한다면 어트리뷰트 항목을 액션과 라벨에 세팅하여 어트리뷰트에 대한 데이터를 리포트에서도 확인할 수 있습니다.
다시 말해, 1개의 카테고리와 2개의 어트리뷰트(액션, 라벨)에 대한 통계를 에어브릿지의 리포트와 대시보드에서 쉽고 빠르게 확인할 수 있습니다.
아래 이미지는 스탠다드 이벤트 중에서 일정 예약 이벤트(airbridge.subscribe)의 예시입니다. 일정을 예약한 지역은 액션 또는 라벨로 수집할 수 있으며, 시맨틱 어트리뷰트를 활용하면 예약한 ID(scheduleID)와 예약일시(datetime)를 수집할 수 있습니다.
밸류는 에어브릿지 이벤트가 수집한 숫자를 계산에 이용하기 위해서 선택해야 하는 구성요소입니다. 에어브릿지 이벤트의 밸류로 수집된 숫자만 계산에 이용할 수 있습니다. 밸류로 소수점 9자리 이하 숫자까지 수집할 수 있습니다.
예를 들어, 레비뉴 리포트(Revenue Report)에서 판매한 제품의 가격을 더해 전체 판매 가격을 계산하거나 디지털 서비스의 구독료를 전부 합해서 전체 구독료를 확인하기 위해서는 밸류를 반드시 이벤트 구성요소로 사용해야 합니다.
또한, 밸류로 수집된 데이터는 밸류로 수집된 다른 데이터와 계산할 수 있습니다. 액션이나 라벨로 수집된 데이터는 계산에 활용할 수 없습니다. 그러나 이벤트 발생 횟수는 밸류 사용 여부와 상관없이 확인할 수 있습니다.
예시) 구매 완료 이벤트
위 예시와 같이 액션과 라벨, 밸류 모두 숫자로 수집한다고 하더라도 밸류로 수집한 데이터만 계산할 수 있으며, 액션과 라벨로 수집한 데이터는 계산할 수 없습니다. 예시에서는 구매 완료 이벤트의 밸류로 수집한 데이터를 더해서 30,000이라는 수치를 얻을 수 있습니다.
매출 관련 데이터는 속성으로 수집하는 것이 일반적이나, 에어브릿지의 경우에는 Attribute가 아닌 Value로 수집합니다. 즉 밸류에는 보통 구매액이 들어가고, 어트리뷰트에서는 기타 정보들을 수집합니다.
Semantic Attribute로 사용할 수도 있지만, Actuals Report나 Revenue Report에서는 이벤트 밸류에 Semantic Attribute의 isRevenue 값을 True로 설정한 카테고리(이벤트)로부터 발생한 매출액(Value 값)을 기준으로 확인하기 때문에 이는 적절하지 않습니다. (설정 가능한 Revenue 이벤트 수: 최대 5개)
Revenue 이벤트를 설정할 때 한 가지 유의할 점은 Revenue의 구조가 다양한 서비스일 경우(전환 포인트: 포인트 충전, 제품 결제, 광고 충전 포인트 등), Revenue Report에서 확인할 최종 전환 기준 한 가지를 선정하셔야 합니다.
만일 아래와 같이 구매 완료 시 2개의 매출 관련 이벤트가 동시에 호출되고 2개의 이벤트 모두 Revenue 이벤트로 설정한 경우 중복집계가 될 수 있기 때문입니다.
예시)
- 주문 완료 이벤트 발생 시 단위별 이벤트 동시 호출
매출액 집계 기준을 제품 단위로 볼지, 주문서 단위로 볼지 결정하고 결정된 하나의 카테고리(이벤트)에 isRevenue를 세팅해야 중복집계를 방지할 수 있습니다.
따라서 Revenue로 집계할 이벤트와 기타 매출 관련 데이터를 집계해야 하는 이벤트를 별도로 관리하시는 것을 권장드립니다.
각 카테고리(이벤트)에는 다양한 속성 정보가 수집되는데, 플랫폼마다 정의하는 '속성명'이 상이합니다. GA의 경우 Parameter, 에어브릿지의 경우 Attribute로 정의합니다. 이벤트(카테고리)명 역시 GA4의 경우 Event, 에어브릿지의 경우 Standard Event라고 정의합니다. 각 플랫폼별로 기본적으로 제공되는 속성들이 있으며, 에어브릿지의 경우 Data Spec에서 확인이 가능합니다.
Airbridge Event
Airbridge Attribute
따라서 데이터의 수집 구조를 충분히 고려하여 분석 환경을 구축해 놓는 것이 좋습니다.
August 23, 2024
디지털 시대의 급속한 발전과 함께 마케팅 환경이 빠르게 변화하고 있습니다. 다양한 채널을 통한 고객 접점이 증가하면서 기업들은 복잡해진 고객 관계 관리(CRM)에 직면하고 있습니다. 이러한 상황에서 CRM 대시보드의 중요성이 더욱 부각되고 있습니다.
CRM 대시보드는 여러 채널에서 수집된 CRM 캠페인 데이터를 통합하여 분석하고, 현황을 한눈에 파악할 수 있게 해주는 강력한 방법입니다. 다양한 CRM 채널로 인해 단일 기준으로 분석하기 어려운 데이터들을 대시보드를 통해 효과적으로 통합하고 시각화함으로써, 마케터들은 보다 신속하고 정확한 CRM 마케팅 의사결정을 내릴 수 있게 됩니다.
이러한 CRM 대시보드의 활용은 단순히 데이터를 보여주는 것에 그치지 않습니다. 실시간으로 캠페인 성과를 모니터링하고, 고객 행동 패턴을 분석하며, 각 채널별 효과를 비교할 수 있게 해줍니다. 이를 통해 기업은 더욱 효율적인 마케팅 전략을 수립하고, 고객 경험을 개선하며, 궁극적으로는 ROI를 향상시킬 수 있습니다.
본 글에서는 범용적으로 활용가능한 CRM 대시보드를 템플릿을 제공합니다.
Looker Studio CRM 캠페인 대시보드 템플릿은 마케팅 캠페인의 성과를 실시간으로 모니터링하고, 데이터를 시각화하여 분석할 수 있도록 돕는 강력한 도구입니다. 이 템플릿을 통해 효과적인 의사결정을 지원하고, 전략을 최적화할 수 있습니다.
Looker Studio CRM 캠페인 대시보드 템플릿은 캠페인의 실시간 성과 모니터링, 데이터 시각화, 마케팅 전략 최적화를 목적으로 활용됩니다. 이를 통해 효과적인 의사결정과 전략적 개선이 가능합니다.
실시간 대시보드를 통해 데이터를 업데이트하고, 다양한 필터 기능을 사용하여 원하는 데이터를 정확히 추출할 수 있습니다. 맞춤형 리포트를 생성하여 필요에 따라 커스터마이즈된 분석이 가능합니다.
마케팅 팀, 경영진, 데이터 분석가 등 다양한 사용자들이 이 템플릿을 활용하여 캠페인 성과를 모니터링하고 분석할 수 있습니다.
1. 주기적인 업데이트
2. 필터 활용
3. 성과 지표 설정
4. 데이터 비교
5. 팀과 공유
템플릿은 마케팅 캠페인의 성과를 종합적으로 관리하고 분석하는 데 유용하며, 데이터를 기반으로 한 전략적 의사결정을 지원합니다.
August 21, 2024
지난 편에서는 각 솔루션별 데이터 수집 방법(바로가기)에 대해서 알아보았습니다.
1. Third Party 데이터 수집 자동화
2. 수집된 데이터 전처리하기
3. 시각화하기
이번에는 수집된 데이터를 활용하여 어떻게 전처리 해야 되는지 알아보겠습니다.
각 데이터 셋들은 분석하려는 차원(dimension)을 기준으로 집계(group by)를 통해서 동일한 형태로 전처리한 후 조인 연산을 통해 데이터를 통합하는 과정이라고 생각하시면 됩니다.
마케팅 성과를 분석하기 위해서 필요한 데이터 다음과 같습니다.
Dimension : 날짜(일별), 유입소스(GA4 = utm_source, Appsflyer = Media_source), 캠페인
Metric : 구매수, 매출, 인스톨 수
GA4가 웹, 앱 모두 트래킹을 할 수 있지만 WEB은 GA4, APP은 MMP로 성과를 합쳐서 보실 겁니다.
GA4, Firebase는 앱과 웹 내 고객 행동 분석에 주로 쓰이는 툴이고 MMP 는 광고 성과를 측정하는 툴이니깐요
그러면 이렇게 됩니다.
<GA4 데이터 집계> - WEB
Dimension : 날짜(일별), 세션 소스, 캠페인
Metric : 구매수, 매출
<MMP 데이터 집계> - APP
Dimension : 날짜(일별), Media_source, 캠페인
Metric : 구매수, 매출, 인스톨 수
집계된 두개의 테이블을 UNION 다시 한번 집계를 해줍니다.
이렇게 하면 집계된 웹앱 데이터 통합이 되었습니다. (MMP 데이터 전처리 과정은 생략)
Dimension : 날짜(일별), Media_source, 캠페인
Metric : 구매수, 매출, 인스톨 수
브레이즈 커런츠(braze currents)데이터는 유저 인게이지먼트 데이터입니다.
여기에는 유저별로 어떻게 우리 서비스로 참여를 하고있는지를 기록한 데이터들이 기록되어 있으니 메시지 발송 수단별로 노출, 클릭 성과들이 들어있습니다. 하지만 우리는 성과(구매) 판단은 MMP, GA4로 해야 하므로 서로 다른 데이터들을 어떻게 통합해서 볼지를 알아보겠습니다.
커런츠 데이터는 아래와 같이 구성이 되어있습니다.
위와 같은 데이터를 일자, 메세지 발송 타입, 캠페인 or 캔버스별 발송수, 노출수, 클릭수 데이터를 집계를 하기 위해서는 유니크한 ID를 나타내는 차원 데이터를 만들어 줘야 합니다.
동일한 유저라도 캠페인, 캔버스, 베리에이션 등 다양한 형태로 타겟이 될 수 있으므로, 아래와 같이 유니크한 차원 데이터를 만들기 위해 새로운 아이디를 생성합니다. (해당 아이디는 발송 타입별로 카운트하기 위해 활용됩니다.)
차원의 이름은 원하시는대로 명명하시면 되고 저는 user_id_dispatch_id 이렇게 명명하였습니다.
-- 태블로 계산식
IFNULL([User Id],'') + '-' +
IFNULL([Campaign Id],'') + '-' +
IFNULL([Message Variation Id],'') + '-' +
IFNULL([Canvas Id],'') + '-' +
IFNULL([Canvas Variation Id],'') + '-' +
IFNULL([Canvas Step Message Variation Id],'') + '-' +
IFNULL([Dispatch Id],'')
다음으로 캠페인 단위로 성과를 집계할 예정이기 때문에 캠페인 차원을 만들어줘야 합니다.
왜냐하면 브레이즈에는 캠페인과 캠버스로 나뉘는데 campaign_name 값이 존재하면 canvas_name 이 빈값이고 거꾸로 canvas_name 값이 존재하면 campaign_name이 빈값이기 때문입니다.
-- 태블로 계산식
IFNULL([Campaign Name],[Canvas Name])
{send_type}_click 이런 식으로 차원 이름을 명명하고 아래와 같은 태블로 계산식으로 차원을 만들어줍니다.
이렇게 되면 email_click, push_click, in_app_message_click 이벤트별로 ID(user_id_dispatch_id)를 생성됩니다.
-- 태블로 계산식
{ FIXED [user_id_dispatch_id],[Event Type] : COUNTD(IF CONTAINS([Event Type], 'inappmessage_click') THEN [user_id_dispatch_id] END)}
최초에 S3에 적재된 currents 데이터(avro 파일)를 DW에 적재할 때 파일별 Event 구분을 위해 Event_Type 칼럼을 생성하였습니다. (이전 글을 참고해 주세요)
['users.messages.email.Open.avro',
'users.messages.email.Click.avro',
'users.messages.pushnotification.Send.avro',
'users.messages.inappmessage.Click.avro',
'users.messages.email.Delivery.avro',
'users.messages.pushnotification.Open.avro',
'users.messages.inappmessage.Impression.avro']
-- 태블로 계산식
IF CONTAINS([Event_Type], 'email') then 'email'
elseif CONTAINS([Event_Type], 'push') then 'push'
elseif CONTAINS([Event_Type], 'inappmessage') then 'iam' end
이제 집계를 위한 전처리는 완료되었습니다(세세한 전처리 과정은 생략됨)
이제 위에서 만들었던 차원을 가지고 집계를 합니다.
일자별, send_type, campaign/canvas, device_category 별 오픈, 클릭, 노출, delivery 데이터는 아래와 같이 집계되었습니다.
통합된 GA4 / MMP 데이터와 커런츠 데이터를 통합해야 되는 과정이 또 남았습니다.
어떻게 이 두 데이터를 엮어야 될까요?
조인키를 위에서 집계한 차원 데이터로 잡고 Full Outer Join으로 데이터를 조인합니다.
이유는 특정 날짜에 브레이즈 커런츠 데이터는 존재하는데 성과 데이터(GA4, MMP) 데이터가 존재하지 않는다면 매칭될 수 없으니 누락이 되어버립니다. 거꾸로 성과 데이터(GA4, MMP) 데이터가 존재하는데 브레이즈 커런츠 데이터가 존재하지 않을 경우 누락이 되어버리기 때문입니다.
광고 성과 데이터 전처리하는 예시를 통해 Full Outer JOIN 이 어떻게 데이터를 처리되는지 예시를 통해 잠깐 확인해 보겠습니다
2024년 1월 1일 twitter 채널의 e 캠페인에서 비용이 1000원 소진했습니다. 그런데 GA4, MMP 데이터에는 해당 광고 채널의 캠페인에서 전환이 아예 일어나질 않았습니다. 하지만 비용이 발생했기 때문에 이 데이터를 버릴 수는 없겠죠? 무조건 살려야 됩니다.
거꾸로 광고 데이터에서 비용은 발생하지 않았는데 Attribution Window로 인해 전환이 발생했습니다. 이것도 버릴 수 없겠죠?
이 두 데이터에서 LEFT JOIN 또는 RIGHT JOIN을 수행하게 되면 조인키에 대응하지 않은 데이터는 매칭이 안되어 누락이 되어 버립니다. 이를 방지하기 위해서 INNER, LEFT OUTER, RIGHT OUTER 조인 집합을 생성하는 FULL OUTER JOIN을 수행합니다.
결과를 보면 각 테이블의 모든 데이터들이 출력이 되는 걸 알 수 있습니다.
본론으로 돌아와서!
Full Outer Join을 수행하기 전에 앞서 GA4, MMP 데이터를 합친 후 우리는 CRM 데이터만 필요하기 때문에 CRM 데이터만 필터를 합니다.
이제 Braze Current 데이터와 성과 데이터(GA4, MMP)를 날짜, 유입소스명, 캠페인명을 조인키로 두고 Full Outer Join 을 수행합니다.(분석하고자 하는 차원데이터가 추가로 더 있다면 추가로 필요한 차원도 조인키로 활용하시면 됩니다.)
이와 같은 방식으로 Paid 성과 데이터를 전처리 할 때도 위와 같은 방법으로 수행하면 됩니다.
데이터를 전처리 할 때 중요한 건 분석하고자 하는 결과물을 먼저 그려본 뒤에 결과를 도출하기 위해서 각 테이블을 어떻게 만들어 갈 것인지 생각하면서 만들어가면 됩니다. 실제로 전처리하다 보면 자잘하게 처리해야 되는 부분이 상당히 많습니다.
특히 데이터를 통합하기 위해서는 무엇보다 네이밍 컨벤션이 가장 중요한 점은 강조하지 않을 수 없습니다.
데이터 수집까지 잘했는데 캠페인 네이밍 컨벤션이 서로 다르다?.. 데이터 통합은 불가능합니다..
네이밍 컨벤션은 말 그대로 명명 규칙입니다.
위에서 언급한 대로 우리가 데이터 통합을 위해 차원 데이터를 조인키로 활용한다고 했었는데 캠페인 차원이 MMP, GA4, Braze or 광고 데이터가 모두 다르면 안 되겠죠? 아래와 같이 통일을 시켜야만 데이터를 연결할 수 있습니다.
여기까지 마케팅 데이터 수집부터 전처리 과정까지 알아보았습니다.
다음 글에서는 이 데이터를 활용해서 시각화 하는 방법을 살펴보겠습니다.
August 21, 2024
루커 스튜디오(Looker Studio)는 구글에서 제공하는 강력한 데이터 시각화 도구입니다. 다양한 데이터 소스를 연결하여 직관적인 대시보드를 만들 수 있으며, 이를 통해 복잡한 데이터를 쉽게 이해할 수 있습니다. 특히, 마케팅, 영업, 운영 등 다양한 분야에서 유용하게 활용될 수 있습니다. 루커스튜디오가 궁금한 분들은 해당 글을 참고해주세요.
인터랙티브 퍼널 차트는 사용자 행동을 단계별로 분석하여 시각적으로 표현하는 방식입니다. 특히, 마케팅 퍼널 분석에 유용하며, 고객이 제품이나 서비스를 구매하기까지의 과정을 시각화하여 각 단계에서의 전환율을 파악할 수 있습니다.
퍼널 분석에는 두 가지 전환율 분석 방식이 존재합니다:
이 두 가지 방식 모두 데이터의 절대값(absolute) 형태와 비율(rate) 형태로 나눠서 분석할 수 있습니다. 이를 통해 어떤 단계에 문제가 있는지 비교하면서 파악할 수 있습니다.
퍼널 분석을 통해 마케팅 전략의 효과를 검토하고 개선할 수 있는 인사이트를 얻을 수 있습니다. 예를 들어, 특정 단계에서 이탈율이 높다면 해당 단계의 문제점을 찾아 해결하는 방식으로 퍼널의 전반적인 전환율을 개선할 수 있습니다.
루커 스튜디오 인터렉티브 퍼널 분석 차트 만들기 | Looker studio
인터랙티브 퍼널 리포트를 작성하는 단계는 크게 데이터 준비, 바 차트 생성, 차트에 인터랙티브 기능 추가로 나눌 수 있습니다.
먼저, 데이터 소스를 연결한 후 필요한 데이터를 정리합니다. 예시는 GA4 데모 데이터를 활용 하였습니다.
계산된 필드와 매개변수 설정이 필요합니다.
아래 이미지에 활용할 매개변수 2가지를 만들어봅시다.
Drop-off type
Value type
데이터 혼합을 이후에 활용할 예정입니다. 데이터 혼합에서 매개변수 사용을 위해서는 매개변수를 측정기준으로 아래와 같이 변경하는 작업이 필요합니다.
앞서 살펴봤던 매개변수의 계산된 필드 변환을 포함하여 3가지 측정기준을 만들어 줍니다.
Drop-off type dim
Value type dim
Funnel step order #
GA4의 전자상거래 5개 이벤트를 선택하고 해당 단계의 순서를 지정하는 이벤트를 만들어줍니다.
Looker Studio 코드 1
CASE
WHEN 이벤트 이름 = "add_to_cart" THEN 1
WHEN 이벤트 이름 = "add_shipping_info" THEN 2
WHEN 이벤트 이름 = "begin_checkout" THEN 3
WHEN 이벤트 이름 = "add_payment_info" THEN 4
WHEN 이벤트 이름 = "purchase" THEN 5
ELSE NULL
END
GA4 – Checkout steps
앞서 정의한 5개 외 나머지 이벤트는 Null로 처리 되었습니다. 해당 이벤트는 분석에 활용하지 않을 예정이므로 필터를 이용하여 제외 처리를 해줍니다. 해당 필터는 이후 단계에서 활용하도록 하겠습니다.
퍼널 분석을 위해선 직전단계와 다음 단계 사용자의 차이를 구해야합니다. 다만 루커스튜디오는 단일 소스로 해당 계산을 하기가 어렵기 때문에 데이터 혼합의 교차 조인을 활용하여 단계별 유저 차이를 구하는 식을 만들 수 있습니다.
데이터 준비가 완료되면, '차트 추가' 메뉴에서 바 차트를 선택합니다. 바 차트를 생성한 후, X축과 Y축에 표시할 데이터를 설정합니다.
3.4.1 설정 탭
Abs users
Looker Studio 코드 2
IF(
Values type dim = "Absolute values"
,
Total users
,
null
)
Abs exited users
Looker Studio 코드 3
IF(
Values type dim = "Absolute values"
,
IF
(
Drop-off type dim = 'Funnel drop-off'
,
IF(Ref Funnel step order = 1 AND Funnel step order != 1, (Ref Total users - Total users),null)
,
IF(Ref Funnel step order = Funnel step order - 1, (Ref Total users - Total users),null)
)
,
null
)
% active
Looker Studio 코드 4
IF(
Values type dim != "Absolute values"
,
IF
(
Drop-off type dim = 'Funnel drop-off'
,
CASE
WHEN Funnel step order = 1 THEN 1
WHEN Ref Funnel step order = 1 AND Funnel step order != 1 THEN (Total users ) / Ref Total users
ELSE NULL
END
,
CASE
WHEN Funnel step order = 1 THEN 1
WHEN Ref Funnel step order = Funnel step order - 1 THEN (Total users ) / Ref Total users
ELSE NULL
END
)
,
null
)
% drop off
Looker Studio 코드 5
IF(
Values type dim != "Absolute values"
,
IF
(
Drop-off type dim = 'Funnel drop-off'
,
IF(Ref Funnel step order = 1 AND Funnel step order != 1, (Ref Total users - Total users)/Ref Total users,null)
,
IF(Ref Funnel step order = Funnel step order - 1, (Ref Total users - Total users)/Ref Total users,null)
)
,
null
)
3.4.2 스타일 탭
인터랙티브 퍼널 리포트는 다양한 마케팅 시나리오에서 유용하게 활용될 수 있습니다. 예를 들어, 고객의 구매 여정을 단계별로 분석하여 각 단계에서 발생하는 이탈율을 파악하고, 이를 개선하기 위한 전략을 세울 수 있습니다.
또한, 특정 마케팅 캠페인의 성과를 분석할 때도 퍼널 리포트를 사용하면 각 채널별로 고객의 반응을 비교할 수 있습니다. 이를 통해 어떤 채널이 가장 효과적인지 파악하고, 마케팅 예산을 효율적으로 분배할 수 있습니다.
July 29, 2024
쿠키(Cookie)란 사용자가 웹사이트를 방문할 때마다 웹 브라우저를 통해 인터넷 사용자의 컴퓨터나 다른 기기에 설치되는 기록 정보 파일을 뜻합니다. 이는 퍼포먼스 마케팅의 필수도구로써 마케팅 활동의 세부적인 추적, 개인화 및 최적화를 가능하게 했습니다.
퍼포먼스 마케팅에서 구체적인 쿠키의 기능은 다음과 같습니다.
1. 사용자 행동 추적
2. 개인화
3. 리타겟팅
4. A/B 테스트 및 최적화
하지만 데이터 보호 규정을 시작으로 데이터 보호를 강화하기 위한 전세계의 흐름이 있었고, 애플을 시작으로 구글 폭스 등 많은 웹 브라우저 회사들이 쿠키 제공 중단을 선언헀습니다.
쿠기 제공이 중단된다면, 기존의 쿠키를 이용해 진행하던 기능들을 더 이상 진행할 수 없게 됩니다. 그 결과 각 매체의 효율이 감소하고 신규 고객을 획득하는데 필요한 비용이 증가할 것으로 예상됩니다. 구글이 쿠키의 지원 중단 시점을 2025년으로 연기했다고는 하지만, 마케터들은 선택이 아닌 필수로 기존 광고 방식에 변화가 필요한 상황입니다.
쿠키 리스 상황에서 발생하는 여러 문제들에 대비하기 위해 진행할 수 있는 여러 방법들이 있습니다. 그 중 고객사에서는 현 상황에서 다양한 매체를 활용하기보다 최고의 효율을 낼 수 있는 매체를 선별해서 효율이 좋은 매체에 집중하고자 합니다. 이를 위해 각 광고매체의 성과를 실시간으로 모니터링할 수 있는 대시보드가 필요했습니다.
태블로를 통해 다양한 광고채널에 분산되어 모니터링이 어려웠던 지표들을 태블로를 통해 하나의 대시보드에서 관리함으로써 어느 채널의 어떤 광고가 효율이 좋은지를 빠르게 파악하고, 이를 통해 어떤 채널에 더 예산을 증가하거나 감소해야 할지에 대한 의사결정을 하는데 활용할 수 있습니다.
대시보드를 제작하기 위한 과정은 크게 환경점검, 데이터 수집, 데이터 저장, 데이터 시각화의 4단계로 구분할 수 있습니다.
첫 번째 환경 점검 단계는 대시보드 구축을 위한 대시보드 기획단계입니다. 이 단계에서는 데이터의 출처를 정의하고, 데이터 소스부터 시각화툴인 태블로까지 이어지는 데이터 파이프라인을 그립니다. 최종적으로 대시보드에서 어떤 데이터를 대시보드에 구현하고, 이를 어떤 방식으로 시각화할지에 대한 대시보드 기획단계라고 할 수 있습니다.
해당 광고성과 대시보드를 구현하기 위해서는 광고채널로부터 나오는 광고데이터를 데이터 소스로 정의하고, 이를 통합하는 과정이 필요합니다. 이를 위한 첫 번째 단계로 Meta, Google Ads등 여러 광고 채널에서 나온 데이터를 ETL툴인 Funnel io에서 통합합니다. 이후 MMP인 에어브릿지의 데이터를 통합해 이를 하나의 구글 스프레드시트 적재합니다. 최종적으로 태블로를 통해 시각화하는 방식으로 데이터 파이프라인을 구현했습니다.
해당 스텝으로 데이터 파이프라인을 구현했을 때의 장점은 어디서부터 데이터가 들어오게 되고, 문제가 발생했을 때 문제 발생원인을 직관적으로 확인가능하다는 점입니다.
두 번째 데이터 수집 단계는 실질적으로 필요한 데이터를 정의하는 단계입니다.
UTM 및 parameter를 기반한 광고성과 추적을 위한 계층 구조인 네이밍 컨벤션(Naming Convention)을 정의하고 이를 통해 광고의 위계질서를 설정합니다. 해당 데이터를 통해 광고성과 데이터 정의하고 수집할 수 있습니다.
네이밍 컨벤션(Naming Convention) 정의를 완료했다면, 이를 토대로 데이터 스키마를 작성해야 합니다. 데이터 스키마란 데이터베이스의 구조와 제약조건에 관해 기술한 것을 뜻합니다. 데이터 이름 및 형태(ex. 숫자/문자)에 대해 정의함으로써 향후 데이터 스키마 하나만 확인해도 어떤 데이터 형태인지 파악할 수 있도록 합니다.
세 번째 데이터 저장 단계는 네이밍 컨벤션(Naming Convention)과 데이터 스키마를 통해 정의한 데이터를 저장하는 단계입니다.
광고성과 확인 시, 데이터의 정확성을 확보하고, 다양한 광고채널의 기여도를 정확하게 측정하기 위해 미디어 데이터 뿐만 아니라 MMP 데이터를 함께 확인해야 합니다. 이를 위해 미디어(메타, 구글애즈,..)와 MMP의 소스를 조인하는 과정이 필요합니다. 이를 위해 키값을 정의하고, 해당 키값을 기준으로 데이터를 조인합니다.
마지막 데이터 시각화 단계는 정의된 데이터를 바탕으로 대시보드를 구현하는 단계입니다.
대시보드 구현에 앞서 퍼포먼스마케터들과 협의를 진행합니다. 실제 최종 대시보드를 사용할 퍼포먼스마케터들과의 논의를 통해 대시보드의 형태 및 기능을 구체화할 수 있습니다.
마지막으로 확정된 기획안을 토대로 태블로를 통해 최종 대시보드를 구현하면 대시보드 제작과정이 마무리됩니다.
July 19, 2024
KPI 대시보드 템플릿을 활용하여 비즈니스 주요 지표를 확인하며 데이터 기반 의사결정에 활용할 수 있습니다. 매출, 이익, 구매수 등 특히 지역별 구매 관점의 분석을 통해 실행 가능한 인사이트를 제공합니다.
1. 스프레드시트 사본 만들기
2. 루커스튜디오 사본 만들기 클릭
3. 리소스 → 추가된 데이터 소스 관리 클릭
4. 작업 → 수정 클릭
5. 루커스튜디오와 스프레드시트 연동 승인 진행
6. 앞서 사본을 만들었던 파일 찾아서 연결
7. 데이터 새로고침하면 데이터 확인이 됩니다.
July 18, 2024
프로모션 분석은 마케팅 캠페인의 일환으로 진행된 다양한 프로모션 활동들의 효과를 평가하고 측정하는 과정입니다. 이 과정을 통해 기업은 프로모션 활동이 소비자의 구매 결정, 브랜드 인식, 시장 점유율 등에 미치는 영향을 분석할 수 있습니다. 프로모션 분석을 위해 판매 데이터, 소비자 행동 데이터, 온라인 트래픽 데이터 등 다양한 데이터 소스를 활용할 수 있습니다.
프로모션 분석은 기업이 자원을 효율적으로 배분하고, 마케팅 목표를 달성하기 위해 필수적인 과정입니다. 이를 통해 기업은 다음과 같은 이점을 얻을 수 있습니다.
개별 프로모션 분석과 통합 프로모션 분석을 함께 수행함으로써, 보다 정교하고 효과적인 마케팅 전략을 개발할 수 있으며, 이는 최종적으로 기업의 성장과 수익성 향상에 기여하게 됩니다.
1. 전체적인 마케팅 전략의 효율성 평가
개별 프로모션 분석을 통해 특정 프로모션의 성공 여부를 파악할 수 있지만, 통합 프로모션 분석을 함께 수행하면 여러 프로모션 간의 상호작용과 그 영향을 이해할 수 있습니다. 이는 전체 마케팅 전략의 효율성을 평가하는 데 중요합니다.
2. 다채널 프로모션 전략의 최적화
현대 마케팅은 다양한 채널을 통해 이루어집니다. 개별 프로모션 분석과 통합 분석을 병행함으로써, 각 채널의 성과를 정확히 파악하고, 채널 간 시너지를 극대화하는 전략을 수립할 수 있습니다.
3. 고객 행동의 종합적 이해
고객은 다양한 프로모션에 노출되며, 이러한 노출이 고객의 구매 결정에 복합적으로 작용합니다. 개별 프로모션 분석과 통합 분석을 결합함으로써, 고객 행동의 더 깊은 이해를 도모할 수 있습니다.
4. 마케팅 자원의 효율적 배분
통합 프로모션 분석을 통해 얻은 인사이트는 마케팅 자원의 효율적인 배분을 가능하게 합니다. 각 프로모션의 효과를 개별적으로뿐만 아니라 전체적인 관점에서 평가함으로써, 예산을 더 효과적인 프로모션에 집중할 수 있습니다.
5. 장기적 마케팅 전략 수립
개별 프로모션의 성공은 단기적인 성과에 초점을 맞출 수 있지만, 통합 프로모션 분석은 장기적인 관점에서 마케팅 전략을 수립하는 데 도움을 줍니다. 다양한 프로모션의 장기적인 영향력과 지속 가능성을 평가할 수 있습니다.
6. 경쟁 우위 확보
통합 프로모션 분석을 통해 시장 내 경쟁 상황과 자사의 위치를 종합적으로 이해하고, 경쟁 우위를 확보할 수 있는 전략을 수립할 수 있습니다. 이는 경쟁사 대비 우위를 점하는 데 중요한 역할을 합니다.
고객사에서는 프로모션 분석 진행 시 앰플리튜드를 통한 단일 프로모션 위주의 분석만을 진행했었습니다.
앞서 언급했듯이 단일 프로모션 분석만 진행하게 된다면 결과적으로 장기적인 효과나 전체적인 마케팅 전략과의 연계성이 부족해지는 문제가 있습니다.
이러한 문제를 해결하기위해 프로모션을 통합적으로 확인하는 것에 대한 필요성이 대두되었습니다. 먼저, 통합 프로모션 분석을 위해 1차적으로 앰플리튜드를 통해 얻은 개별 프로모션별 데이터를 스프레드시트에서 취합해 프로모션들끼리 비교하며 인사이트를 도출했습니다.
하지만 시트를 통해 프로모션을 비교 분석하는 것에는 크게 2가지 정도의 한계가 있었습니다.첫째, 프로모션이 진행될 때마다 데이터를 확인하고, 직접 시트에 옮기는 작업을 해야 하기 때문에 자동화가 어렵다는 점입니다.둘째, 시트에 텍스트로만 적혀있다보니 해당 데이터가 무엇을 의미하는지 직관적으로 인지하기가 어렵다는 점입니다.이러한 한계를 극복하기 위해 고안한 것이 전체 프로모션을 한눈에 파악할 수 있는 대시보드를 구현하는 것이었습니다.
대시보드는 태블로(Tableau)를 사용하여 구현했습니다.
태블로는 데이터를 분석하고 시각적으로 표시할 수 있는 비즈니스 인텔리전스(BI)툴입니다. 태블로를 통해 대시보드를 제작했을 때의 장점은 다음과 같습니다.
1. 여러 raw 데이터 통합: 태블로는 스프레드시트, 데이터베이스, 클라우드 서비스 및 빅데이터 플랫폼과 같은 다양한 데이터 원본들을 원활하게 통합됩니다. 이러한 통합을 통해 다양한 소스의 데이터를 연결 가능하게 하여 데이터 사일로를 해결하고 포괄적인 분석을 가능하게 합니다.데이터 사일로(Data Silo*)**란? 서로 분리되어 기업의 다른 부서에서 액세스할 수 없는 데이터 스토리지 및 관리 시스템을 의미하며, 이는 전사관점의 의사결정을 방해하고, 비효율성이 증가시킵니다.
2. 실시간 데이터 시각화: 태블로는 라이브 데이터베이스 및 스트리밍 데이터를 비롯한 다양한 데이터 원본에 연결하여 실시간으로 데이터 업데이트할 수 있습니다. 이 기능을 사용하면 변경되는 데이터를 빠르게 모니터링하고 분석하여 빠른 의사 결정을 위한 시기적절한 통찰력을 제공할 수 있습니다.
3. 다양한 시각화 옵션: 태블로는 막대 차트, 선 차트, 산점도, 지도 등을 포함하여 다양한 시각화 옵션을 제공합니다. 정보를 효과적으로 전달하기 위해 데이터에 가장 적합한 시각화 유형을 선택할 수 있습니다.
4. 대화형 대시보드: Tableau를 사용하면 사용자가 직접 데이터를 탐색하고 상호 작용할 수 있는 고도의 대화형 대시보드를 만들 수 있습니다. 사용자는 세부 정보를 드릴다운하고, 데이터를 필터링하고, 임시 분석을 수행하여 데이터 탐색의 깊이를 향상할 수 있습니다.
통합 프로모션 분석을 위해 정의한 스케치입니다. 해당 스케치는 피그마(Figma)를 통해 작업했습니다. 스케치를 피그마로 제작한 이유는 여러가지가 있는데, 그 중 가장 중요한 이유는 피그마가 다양한 디자인 도구와 기능을 제공하기 때문에 원하는 디자인을 쉽고 효율적으로 제작할 수 있기 때문이었습니다.
스케치 대시보드를 기준으로 필요한 데이터를 정리해보면 데이터는 크게 3가지로 분류할 수 있습니다.
1. 프로모션 데이터
2. 매출 데이터
3. CRM 데이터
프로모션명, 진행일자, 분류, 컨셉 등 프로모션 진행 관련 기본 정보 및 프로모션 관련 아이템 정보가 프로모션 데이터에 해당하며, 각 프로모션에 관련된 실제 결제 정보가 매출 데이터에 해당합니다. 마지막으로 유저들에게 발송된 메시지의 발송일 및 캠페인명, 발송수 등이 CRM 데이터에 해당합니다.
다음으로 앞서 정리한 데이터를 좀 더 구체적으로 정리합니다. 각 항목별 필요한 정보가 정확히 무엇인지, 해당 데이터는 형태로 관리되어야 하는지를 비롯해 각 데이터를 연결하기 위해 어떤 컬럼을 키값으로 사용해야 하는지 등을 파악하여 정리합니다.
이때 테이블 색상에 차이를 두어 각 데이터가 어떤 소스에서 관리되는지 직관적으로 파악할 수 있도록 합니다.
필요 데이터 정의를 완료했다면, 데이터 추출을 위해 해당 데이터에 대해 알맞은 형태로 적재를 요청하는 단계가 필요합니다. 효율적인 작업을 위해 원하는 이벤트 및 세부 항목(*이벤트 프로퍼티, 유저 프로퍼티, 적재형식, 적재기준일, 요청사유 등) 관련 내용을 최대한 상세하게 기입합니다.
원하는 이벤트 및 세부 항목을 작성한 다음에 해당 내용을 정확히 어떤 형태로 적재되기 원하는지를 보여주는 샘플을 함께 작성합니다.
이제 데이터 연결을 진행해야 합니다. 해당 프로젝트에는 구글 스프레드시트, 엑셀 파일, 구글 클라우드(빅쿼리)의 소스가 사용되었습니다. 저는 그 중 ‘구글 클라우드’에 초점을 맞추어 데이터 연결과정에 대해 설명하려고 합니다.
구글 클라우드를 사용한 이유는 무엇일까요? 그 이유는 앰플리튜드(Amplitude)에 있는 매출 데이터를 연결하기 위함입니다. 앰플리튜드는 그 자체로 사용성이 높이며, 매출 데이터를 비롯하여 많은 데이터를 손쉽게 확인할 수 있는 툴입니다.
하지만 지금과 같이 더 많은 내용을 확인하기 위해서는 다른 로데이터와 결합을 해서 데이터를 확인하는 과정이 필요합니다. 이 때, 앰플리튜드에서 태블로로 데이터를 바로 전달할 수는 없습니다.
태블로로 데이터를 전송하기 위해서는 앰플리튜드의 로데이터를 가공하여 알맞은 형태로 테이블을 가공해주는 선행작업이 필요합니다. 앰플리튜드와 연동이 가능할 뿐만아니라 태블로와의 연동도 가능해야 하며 무엇보다 앰플리튜드로부터 전달받은 로데이터를 가공할 수 있는 플랫폼이여야합니다.
이 작업을 수행할 수 있는 플랫폼은 무엇일까요? 저희는 이러한 요건을 모두 고려할 때 해당 작업을 수행하기에 가장 적합한 것이 Google BigQuery라고 판단했고, 이를 활용하기로 했습니다.
앰플리튜드는 기본적으로 빅쿼리로 데이터를 바로 내보내는 기능을 제공합니다. 우선 해당 기능을 활용해 앰플리튜드 데이터를 빅쿼리에 내보내는 작업을 진행했습니다.
해당 방법을 통해 데이터를 받아올 경우, 이벤트 프로퍼티가 분리된 표 형식으로 넘어오는게 아니라 json이라는 괄호 안에 키와 값형태로 구성돼 있는 포맷(*”{”~”}”)으로 데이터가 불러와지는 문제가 발생했습니다.
태블로는 표 형태의 데이터 프레임을 인식하므로, 태블로에 데이터를 연결하기 위해서는 앰플리튜드 로데이터를 전처리하는 과정이 필요했습니다.
해당 데이터를 전처리하기 위해서는 로데이터의 형태를 파악해야 했습니다. 데이터를 확인한 결과, 텍소노미에 따라 로데이터의 구조가 다를 수 있음을 확인했습니다.
구매 이벤트의 경우 아이템 수량이 1개일 경우에는 값으로, 2개 이상일 경우에는 배열로 이벤트 프로퍼티 데이터가 들어오는 구조였습니다.
{"item_brand":"A",
"item_category":"JAJL",
"item_id":"99999",
"item_name":"GARAGEJACKET",
"item_price":"198000",
"order_id":"20240310105120687",
"total_order_items_quantity":1
…}
{"item_brand":["B","C"],
"item_category":["LFOT","반팔 셔츠"],
"item_id":["88888","77777"],
"item_name":["TRAVEL COMB","CUSTOM SHIRT"],
"item_price":[18000,113000],
"total_order_items_quantity":2}
따라서 데이터 전처리를 위해서는 각 케이스별로 다른 전처리과정을 거쳐야했습니다. 아이템 수량이 1개일경우는 JSON의 값을 파싱하는 처리를 하고, 2개 이상일 경우에는 JSON의 배열을 파싱하는 처리를 진행했습니다.
해당 내용을 처리하는 쿼리문 다음과 같습니다. 케이스별로 결과 테이블이 도출되면, 두 테이블을 유니온하여 한 테이블로 합치는 작업을 진행했습니다.
WITH single_item_orders AS (
SELECT event_time, user_id,
JSON_VALUE(event_properties, '$.order_id') AS order_id,
JSON_VALUE(event_properties, '$.item_category') AS item_category,
JSON_VALUE(event_properties, '$.item_brand') AS item_brand,
JSON_VALUE(event_properties, '$.item_name') AS item_name,
JSON_VALUE(event_properties, '$.item_id') AS item_id,
JSON_VALUE(event_properties, '$.item_price') AS item_price
FROM amplitude_test.EVENTS_353961
WHERE event_type='total_items_order_completed'
AND JSON_VALUE(event_properties,'$.total_order_items_quantity')='1'
),
multi_item_orders AS (
SELECT event_time, user_id,
JSON_VALUE(event_properties,'$.order_id') AS order_id,
item.item_category, item.item_brand, item.item_name, item.item_id, item.item_price
FROM amplitude_test.EVENTS_353961,
UNNEST(
ARRAY(
SELECT AS STRUCT
item_category, item_name, item_id, item_price, item_brand
FROM
UNNEST(JSON_VALUE_ARRAY(event_properties,'$.item_id')) AS item_id WITH OFFSET AS pos
JOIN UNNEST(JSON_VALUE_ARRAY(event_properties,'$.item_category')) AS item_category WITH OFFSET AS pos_cat ON pos = pos_cat
JOIN UNNEST(JSON_VALUE_ARRAY(event_properties,'$.item_brand')) AS item_brand WITH OFFSET AS pos_br ON pos = pos_br
JOIN UNNEST(JSON_VALUE_ARRAY(event_properties,'$.item_name')) AS item_name WITH OFFSET AS pos_name ON pos = pos_name
JOIN UNNEST(JSON_VALUE_ARRAY(event_properties,'$.item_price')) AS item_price WITH OFFSET AS pos_price ON pos = pos_price
)
) AS item
WHERE
JSON_VALUE(event_properties,'$.total_order_items_quantity')!='1'
)
-- 아이템 수량= 1인 경우의 결과와 아이템 수량= 2 이상인 경우의 결과를 합치기
SELECT *
FROM single_item_orders
UNION ALL
SELECT *
FROM multi_item_orders;
해당 작업을 완료한 결과는 다음과 같습니다.
UNNEST()함수를 포함한 단계를 거쳐 데이터 전처리를 진행할 경우 JSON을 데이터 프레임 형태, 즉 테이블 형식으로 바꾸는 것은 가능했습니다.
좀 더 직관적으로 UNNEST()함수를 이해하기 위해 예시를 들어보겠습니다. name이라는 컬럼은 값이지만, preferred langauge는 배열일 경우 빅쿼리는 테이블을 왼쪽처럼 인식합니다. 즉 값으로 구성된 행에 배열로 구성된 행을 묶어두는 구조인데요, 이 묶어둔다라는 것을 nest된 상태라고 하고, 값을 배열에 맞춰서 풀어주는 행위를 unnest라고 합니다.
하지만 UNNEST()함수를 사용할 경우 단점이 있었습니다.이 함수는 배열 내 각 요소를 별도의 행으로 확장하는 기능을 제공하기 때문에 비정규화된 데이터나 중첩된 데이터 구조를 다룰 때 필수적입니다. 그러나 비용관리의 관점에서 주의가 필요하다는 단점이 있습니다. UNNEST() 함수는 배열 내의 각 요소를 별도의 행으로 확장하기 때문에 배열에 많은 요소가 포함되어 있을 경우, 결과 데이터 세트의 크기가 급격히 증가할 수 있습니다. 이는 쿼리 처리 시간을 늘리고 처리해야 할 데이터 양이 증가함으로써 비용이 증가하는 원인이 될 수 있으며, 처리한 데이터 양에 따라 비용이 청구되는 빅쿼리에서는 쿼리 비용의 증가로 이어질 수 있습니다.
실제 프로젝트를 진행하면서 해당 쿼리문을 사용해 약 한달 간 매 시간마다 업데이트 되도록 쿼리를 돌려본 데이터 업데이트를 진행한 결과, 총 8GB를 사용해 월 40만원 가량의 비용이 소진되었습니다. 따라서 비용 효율성 측면을 고려해 전처리를 클라우드 SQL구문이 아닌 파이썬 코드로 처리하도록 우회했습니다.
앰플리튜드의 데이터를 빅쿼리로 바로 내보내는 것이 아니라 앰플리튜드 서버를 호출하여 데이터를 받아오는 방식으로, 빅쿼리에서 로우 데이터를 쌓은 후 이를 전처리하는 방식 대신 전처리를 완료한 후 가공된 데이터를 빅쿼리에 쌓는 방식으로 변경했습니다.
즉, 기존 Amplitude → Bigquery → Tableau의 단계에서 데이터 전처리를 위해 Google Cloud Storage를 추가한 Amplitude → Google Cloud Storage → Bigquery → Tableau 단계로 진행됩니다.
이 작업을 위해서는 우선 앰플리튜드와 Google Cloude Storage를 연결하는 작업을 진행해주어야 합니다. 먼저 Cloud Storage에서 고객사 프로젝트 관련 Bucket을 생성합니다.
다음으로 IAM 및 관리자 → 서비스 계정을 클릭하여 새로운 서비스 계정을 생성합니다.
서비스 계정을 생성한 후, 해당 서비스 계정에 해당하는 이메일을 확인합니다.
키 → 키 추가를 클릭하여 키를 생성합니다.
IAM 및 관리자 → 역할을 클릭하여 새로운 역할을 생성합니다. Send Amplitude Event Data to Google Cloud Storage(문서링크)를 참고해서 역할에 다음 5개의 권한을 부여해줍니다.
앞서 생성한 버킷으로 돌아가서 권한을 클릭한 후 액세스 권한 부여를 클릭합니다.
서비스 계정에서 추가한 새로운 계정에 해당하는 이메일을 입력한 후, 역할을 지정해줍니다.이때 역할은 1.저장소 기존 버킷 소유자와 2.역할 만들기를 통해 생성한 역할, 총 2개를 부여해줍니다.
해당 작업까지 완료했으면 앰플리튜드에서 Google Cloude Storage를 연결하는 작업을 진행해주어야 합니다. 앰플리튜드 Data → Destination에서 Google Cloud Storage를 클한 후 GCS로 보낼 데이터를 선택합니다.
서비스 계정 생성에서 생성한 JSON키 파일을 Service Account Key에 업로드 한 후, 하단의 Bucket Name에 구글 GCS에서 생성한 버킷 이름을 입력해줍니다.
해당 과정을 성공적으로 마치면 Google Cloud Storage Bucket에 데이터가 들어옵니다.
연동이 완료되면 다음과 같이 생성됩니다.
앰플리튜드는 export API라는 서비스를 제공하므로 이 API를 호출하여 데이터를 불러올 수 있습니다. 파이썬의 request모듈로 API요청을 보내는 함수를 구현하고, 다음으로 데이터 전처리 모듈은 pandas모듈을 활용하여 JSON 포맷을 테이블 형식의 데이터 프레임으로 전처리했습니다. 1차 시도당시 빅쿼리로 진행했던 조건문을 프로그래밍 언어로 대체한 것입니다. 마지막으로 빅쿼리 클라이언트 라이브러리를 설치하여 전처리한 테이블을 빅쿼리로 업로드하는 함수를 구현했습니다.
내용을 요약하면 다음과 같습니다.
1. 앰플리튜드
2. GCS
3. GCF
ㅤ: 버킷에 객체가 생성될 때마다 객체 데이터를 전처리한 후, 빅쿼리에 로드하는 함수 구현
해당 작업을 완료하면, 최종적으로 빅쿼리에 데이터가 정상적으로 저장되며, 태블로에 연결해서 시각화할 수 있습니다.
통합 프로모션 대시보드이므로 프로모션 간의 비교가 가능하도록 하는 것이 최우선순위 목표였습니다. 하지만 통합 프로모션 대시보드 내에서 개별 프로모션의 성과 및 관련 내용도 바로 파악이 가능하도록 대시보드를 구성하고자 했습니다.
프로모션 리스트 부분에서 프로모션 성과들을 직관적으로 비교해서 확인할 수 있도록 했으며, sales summary내의 각 KPI의 프로모션 평균값을 제공하여 프로모션의 평균값과의 비교가능하게함으로써 프로모션 간의 비교 분석이 가능하도록 대시보드를 구성했습니다. 또한 프로모션 리스트에서 개별 프로모션 클릭 시 해당 프로모션에 해당하는 내용으로 필터링이되어 표현되게함으로써 개별 프로모션의 성과 역시 파악할 수 있도록 대시보드를 구성했습니다.
데이터를 태블로로 넘긴 후 ERP 기준 매출 데이터와 정합성을 확인하는 과정에서 매출액의 약 0.2%의 차이가 발생함을 확인했습니다. 이는 최종 매출액에서 반품 및 교환 비용을 고려하지 못했기 때문에 발생한 결과였습니다.
따라서 앰플리튜드의 반품 및 교환관련 이벤트인 return_completed의 return_paid_shipping(교환 및 환불금액)관련 항목을 추가하여 해당 금액을 반영해줌으로써 데이터 정합성을 맞췄습니다.
해당 대시보드를 사용하는 고객사는 프로모션의 분류를 크게 할인과 쿠폰 2가지로 구분하고 있습니다.
즉, 1차적으로 가격을 낮춰 세일가에 제품을 구매하는 ‘할인’ 프로모션과 쿠폰을 소지하고 있는 고객이 쿠폰을 직접 사용해서 제품을 구매하는 ‘쿠폰’ 프로모션이 존재합니다. 이때 쿠폰 프로모션의 경우 총 매출 관련 항목과 쿠폰 매출 관련 항목으로 구분해서 성과를 확인할 수 있지만, 할인 프로모션의 경우 쿠폰 관련값이 존재하지 않으므로 쿠폰 매출 관련 성과는 확인이 불가능합니다. 따라서 성과를 확인함에 있어서 쿠폰 프로모션의 경우에는 쿠폰 매출 관련 항목을 확인해야 하고, 할인 프로모션의 경우에는 총 매출 관련 항목을 확인해야 합니다.
이를 적용했을 때 Sales Summary를 확인함에 있어서 결제상품수, 결제건수, 구매고객수의 경우 할인 프로모션의 경우에는 총 매출액 관련 항목으로, 쿠폰 프로모션의 경우에는 쿠폰 매출액 관련 항목으로 구분해서 표현해주어야 합니다.
이를 위해 케이스를 나누고, 쿠폰 아이디가 존재할 경우 쿠폰 프로모션으로 판단해서 쿠폰 매출 관련으로 카운트 하고, 쿠폰 아이디가 존재하지 않을 경우 할인 프로모션으로 판단해서 총 매출 관련항목으로 카운트할 수 있도록 계산된 필드를 생성합니다.
SQL
-- 결제상품수 예시
{FIXED [Promotion name1],[Classification],[Coupon Id]:
SUM(IF [Classification]='쿠폰' and ([# COUPON ID_1]= [Coupon Id]
OR [# COUPON ID_2]= [Coupon Id] OR [# COUPON ID_3]= [Coupon Id])
THEN 1
ELSEIF [Classification]='할인' THEN 1
END)}
최종적으로 제작된 통합 프로모션 대시보드는 다음과 같습니다.
대시보드 작동예시를 좀 더 자세히 살펴보면 다음과 같습니다.
1. Promotion List
Promotion List 우측 분류 및 테마 필터를 클릭하여 해당하는 프로모션을 확인할 수 있습니다.
2.Sales(sales summary)
해당 프로모션의 총 매출액, 쿠폰 매출액, 결제 상품수, 결제 건수, 구매고객수 및 각 항목에 해당하는 일평균 액수를 확인할 수 있습니다.
3. Item datail
좌측의 브랜드별, 카테고리별, 상품별 차트를 클릭해 각 항목에 해당하는 상품의 일자별 판매 현황을 확인할 수 있습니다.
우측 막대그래프에 마우스를 오버하면, 각 일자별 총 매출 및 쿠폰 매출, 총 매출 대비 쿠폰 매출 비율을 확인할 수 있습니다.
4. Sale Trend & 5.CRM Info
4. Sale Trend에서 프로모션 기간 동안의 총 매출 및 쿠폰 매출, 해당 기간 동안 CRM이 발송된 날짜에 대한 정보를 확인할 수 있습니다.
5.CRM Info에서 해당 CRM에 대한 세부 정보를 확인할 수 있습니다.
July 17, 2024
업무 시간을 데이터 수집과 데이터 전처리에 시간을 쓰는 마케터와 데이터 수집 자동화된 환경에서 성과 분석과 기획에 더 많은 시간을 쓰는 마케터 누가 더 많이 성장할까요?
답은 알고 계실 거에요. 당연히 성과 분석과 기획에 더 많은 시간을 쏟는 마케터가 장기적으로 많은 성장을 하겠죠
회사 내에서 GA4, MMP(AppsFlyer), Braze를 사용하고 있는데 엑셀로 데이터 수집해서 가공하는 시간을 대부분을 사용하고 있다면.. 하루 빨리 마케팅 데이터 수집 자동화하고 BI 구축을 시도해보세요.
관련 주제는 내용이 많기도 해서 3개로 나눠서 발행할 예정입니다.
이번 편에서는 MMP(AppsFlyer), GA4, Braze 데이터 수집을 자동화 시키는 프로세스를 설명해보려고 합니다.
데이터를 적재하고 시각화까지의 프로세스를 간단하게 도식화하면 아래와 같습니다.
브레이즈 currents 는 유저의 engagement 이벤트의 실시간 데이터 스트림입니다. 이 데이터를 Avro 파일로 제공해서 BI 및 분석 할 수 있게 지원을 해주는 장점이 있습니다. 단점은 가격이..
브레이즈 어드민에서 아래처럼 어떤 데이터를 보낼지 선택해서 어디에 저장할지 S3, Cloud Storage, Azure Blob Storage 중 선택해서 적재를 시작합니다. (가이드 링크)
예를 들어 Amazon S3 적재를 시작하게 되면 아래처럼 이벤트 별로 폴더가 생성되어 분리 적재됩니다.
브레이즈 Currents 는 At-least-once delivery 정책으로 1시간 단위로 데이터를 적재합니다.
각 폴더 안에는 Avro 파일(각 파일의 Schema는 동일한 형태)이 들어 있는데 이 파일 내에는 이벤트를 구분하는 필드가 없습니다.
일단 여기서 필요한 이벤트 데이터들을 지정해서 합쳐야 되는데 여기서 문제가 있습니다.
그래서 파일 안에 어떤 이벤트의 파일인지 지정해줘야 합니다.
아래와 같이 Avro 파일 명을 확인하여 event_type 필드의 key 값을 추가 필요합니다.
앱 데이터를 측정하는 앱스플라이어도 마찬가지로 데일리 리포트를 만드려면 OS별, UA, RT(리타겟팅)별로 csv 파일을 12번 클릭해서 받아 정리해야 되지만 API 를 활용하면 충분히 자동화 할 수 있습니다.
다만 앱스플라이어 데이터를 어떻게 볼지 기준에 대한 합의를 유관 부서와 먼저 하시는 걸 권장드리고 대행사에게도 우리 기준으로 맞춰 달라고 요청을 해야 되겠죠?
이런 기준으로 데이터를 쌓고 있었는데 데이터를 집계하는 기준이 서로 차이가 있다면 .. 다시 작업해야 되는 불상사가 생길 수 있습니다.
이게 무슨 말이냐면 앱스플라이어 Media Source 중 SRN 매체들의 경우 개인정보 보호 이슈로 rawdata에 포함되지 않아서 집약형 데이터를 활용합니다.(가이드 링크)
그래서 SRN 매체를 사용 중이시라면 집약형 데이터를 활용해서 집계를 해야 하는데 집약형 데이터는 LTV 데이터라서 조회 시점마다 Total Revenue 값이 달라집니다. (관련글 보러가기 링크)
1/1 데이터를 1/7일에 조회했을 때랑 1/14일에 조회했을 때의 total revenue가 달라진다는 의미입니다. 이러게 되면 가장 최근에 조회한 일자일 수록 ROAS가 높게 나오겠죠?
또한 skan 리포트도 마찬가지로 SRN 매체 광고를 운영 중에 있다면 해당 리포트도 받아야겠죠? 이것도 마찬가지로 조회시점마다 성과 숫자가 달라질 수 있습니다
앱스플라이어 데이터 자동화 수집을 위해선 크게 3가지 api 가 필요하고 세부적으로는 인스톨과 인앱이벤트 데이터를 가져와야 합니다. (추후에 앱스플라이어 데이터를 전처리하는 방법에 대해서 포스팅 해보겠습니다.)
GA4 빅쿼리는 실무에서 쓰고 있는 조직이 있나 싶을 정도로 .. 사용하기가 좀 꺼려집니다. 원시 데이터라서 데이터 가공의 자유도가 엄청나게 높지만.. 집계를 하는 입장에서는 굉장히 머리 아픕니다. 정합성을 어디다가 맞춰야 되는지..
그래서 광고 데이터 성과를 집계할 때는 특별한 이유가 없다면 GA4 API 데이터를 소싱하는 게 정합성 의심의 여지가 없기 때문에 무조건 낫다고 봅니다.
GA4 데이터를 소싱할 때 진짜 진짜 주의해야 할 점이 바로 샘플링입니다.
샘플링 진짜 …. 데이터량이 많으면 더 심해집니다.
GA4 API 를 14일치만 호출해도 샘플링된 양이 상당합니다.
GA4 Query Explorer 에서도 조회해도 어드민이랑 큰 차이가 있어서 구글 측에 문의를 해보니 여기도 데이터 조회 일자 범위가 넓으면 샘플링이 적용된다고 합니다. 추후에 해당 이슈 해소하겠다고 합니다.
일단 우리는 샘플링 되지 않을 정도의 날짜 범위로 api 를 호출해서 적재를 하는 것이 좋습니다.
이번 편은 마케팅 데이터 수집 자동화에 대해서 알아보았고 다음 편에서는 인사이트 도출을 위한 데이터 전처리 방법에 대해서 알아보겠습니다!
July 16, 2024
GA4 데이터를 빅쿼리로 보내면 뭐든 다 해결될 것 같은 이야기들이 많지만 실상은 그렇지 못합니다. GA4 인터페이스와 차이가 많고 많은 데이터 용량으로 ETL 과정으로 생각보다 리소스가 많이 듭니다. 그럼에도 불구하고 빅쿼리를 쓰는 이유가 뭘까요?
GA4 빅쿼리 데이터를 가공하다보면 어? 뭐지? 하는 현상들이 있는데 대표적인 하나의 케이스를 공유해볼까합니다. 바로! 빅쿼리에 저장되는 GA4 데이터에는 구글 관련 트래픽에 가장 큰 문제가 있다는 것입니다ㅜㅜ
gclid 파라미터(구글 애즈 캠페인으로 들어온 트래픽이라는 의미)가 있는 세션이 시작 되었을 때 빅쿼리는 utm_medium, utm_campaign 파라미터를 (organic)으로 기록해버립니다
다시 말하면 구글 애즈로 들어왔다는 데이터라고 식별은 되지만 캠페인 명이 증발해버리는 현상입니다.
with events as (
select
cast(event_date as date format 'YYYYMMDD') as date,
concat(user_pseudo_id, (select value.int_value from unnest(event_params) where key = 'ga_session_id')) as session_id,
user_pseudo_id,
(select value.int_value from unnest(event_params) where key = 'ga_session_id') as session_start,
if(
(select value.string_value from unnest(event_params) where key in ('campaign_id', 'campaign', 'source', 'medium', 'term', 'content', 'gclid', 'dclid', 'srsltid') and value.string_value is not null limit 1) is not null,
(
select as struct
(select value.string_value from unnest(event_params) where key = 'campaign_id') as manual_campaign_id,
(select value.string_value from unnest(event_params) where key = 'campaign') as manual_campaign_name,
(select value.string_value from unnest(event_params) where key = 'source') as manual_source,
(select value.string_value from unnest(event_params) where key = 'medium') as manual_medium,
(select value.string_value from unnest(event_params) where key = 'term') as manual_term,
(select value.string_value from unnest(event_params) where key = 'content') as manual_content,
(select value.string_value from unnest(event_params) where key = 'gclid') as gclid,
(select value.string_value from unnest(event_params) where key = 'dclid') as dclid,
(select value.string_value from unnest(event_params) where key = 'srsltid') as srsltid
),
null
) as collected_traffic_source,
event_timestamp,
stream_id,
(select value.string_value from unnest(event_params) where key = 'device_category') device_category,
event_name,
ecommerce.purchase_revenue_in_usd as purchase_revenue_in_usd,
case
when event_name = 'purchase'
then 1 else 0 end AS purchase
from
`테이블명`
where
_TABLE_SUFFIX >= '20231001' AND _TABLE_SUFFIX <= '20231001'
and (select value.string_value from unnest(event_params) where key = 'source') = 'google'
)
select * from events
order by session_id
이 현상을 해결하려면 UTM 파라미터를 자동 태깅(gclid)를 대신해서 사용하거나 URL에 커스텀 파라미터를 붙여서 해결할 수 있긴 합니다.
유저 레벨 분석이 아닌 마케팅 성과를 집계하기 위한 목적으로 GA4 데이터를 활용하고자 한다면 Bigquery Export 보다는 Google Analytics Data API 를 활용해서 일별로 내부 DataWarehouse에 저장하는 방식이 더 효율적일 것 같습니다.
참고로 Google Analytics Data API 를 활용한다면 집계된 데이터를 받아오기 때문에 Total Users 와 같은 유니크한 집계값 (Count Distinct)를 해야되는 지표는 제외하고 이벤트 단위의 지표만 활용해야합니다. 이유는 아래와 같습니다.
4명의 유저가 2024-01-01 ~ 2024-01-03 에 방문했습니다.(로우 데이터)
일별 Total User 를 집계해보면
전체 기간의 Total User 를 집계해보면
일별, 기간별 집계 했을 때 유니크 사용자수는 완전히 다릅니다. (DAU, MAU 개념과 동일)일별 테이블을 다 더해서 집계를 해서 사용하시면 쓸모없는 지표가 되어버리기 때문입니다.
그래도 유저레벨 단위로 혹은 GA4 인터페이스에서 처리 못하는 방식의 데이터 가공이 필요하다면 위의 케이스를 반드시 고려해서 빅쿼리를 사용하시는 걸 권장드립니다.
Why your BigQuery results don't (exactly) match with Google Analytics reports (GA4)