February 3, 2025
지난 수 년간 CRM 마케팅이 디지털 마케팅의 주요 패러다임으로 자리 잡으면서, 해외의 CRM 자동화 솔루션들이 한국 시장에 진출했다. 또, 국내의 여러 CRM 자동화 솔루션들이 등장하여 적극적인 마케팅 활동을 펼치고 있다.
CRM 자동화 솔루션은 마테크 시장에서 점점 포화 상태에 이르고 있으며, 그만큼 솔루션을 구매해야 하는 마케터나 의사결정권자들의 결정도 어려워지고 있다.
이번 글에서는 CRM 솔루션들을 비교해보고, 우리 회사에 가장 적합한 CRM 솔루션을 찾기 위한 고려사항들을 이야기하려 한다.
[참고]
이 글은 아래의 CRM 자동화 솔루션들을 대상으로 합니다.
- 브레이즈 (Braze)
- 원시그널 (OneSignal)
- 인사이더 (Insider)
- 아이쿠아 (AIQUA)
첫 번째 고려사항은 내가 일하고 있는 자사 서비스의 특성이다.
비슷해보이는 CRM 툴들 사이에서도 다른 특성들이 있으며, 각 특성들이 우리 서비스의 특성과 얼마나 어울리는지 살펴봐야 한다.
판매하는 프로덕트의 관여도, 타겟 유저의 특성, 상품의 수량, 수익 모델, 매출을 내는 유저의 비중, 재구매율을 비롯한 주요 지표들이 서비스 특성에 포함될 수 있다.
고관여 제품은 유저 설득에 시간이 오래 걸리고, 설득을 위해 보내야 하는 메시지도 많다.
반면 저관여 제품을 유저 설득에 시간이 상대적으로 적게 소요되지만, 그 짧은 시간안에 설득해내야 한다는 다른 유형의 챌린지가 있다.
• 정보성 메시지
가격이 비싼 만큼 유저들은 자신의 결정이 가질 리스크를 낮추려 한다. 정보성 메시지를 통해 이런 리스크를 낮추는 것 만으로도 구매전환율을 높일 수 있다.
• 높은 수준의 개인화
높은 수준의 개인화를 통해 본인이 고려하고 있는 제품을 반복적으로 노출시켜야 한다. 소셜프루프, 가격 변동 정보 등을 활용하여 유저로 하여금 "구매하는 것이 올바른 선택인 것 같은 느낌"을 주어야 한다.
• 긴 고객 여정에 적합한 단계별 메시지
구매에 오랜 시간이 걸리는 만큼, CRM으로 터치할 시간도 많이 주어진다. 다양한 채널과 메시지를 활용하여 유저에게 단계별 메시지를 발송하여 전환율을 높일 수 있다.
• 꾸준한 쿠폰 플레이, 할인 등 프로모션
값이 저렴한 저관여 제품일수록 유저들이 오히려 가격에 더 민감하게 반응한다. 가전은 비싸도 좋은 걸 사서 오래쓰고자 하지만, 물이나 휴지는 그렇지 않다. 가격 경쟁력을 갖추기 위한 쿠폰 플레이와 할인을 꾸준히 진행해야 한다.
•프로모션 참여 유도: 게이미피케이션
"꾸준한 프로모션"을 진행하다보니, 프로모션을 진행해도 유저들의 참여율이 낮아질 때가 있다. 이 경우 게이미피케이션이나 프로모션의 한정성을 강조하여 프로모션의 참여율을 높여야 한다.
• 크로스세일
저관여 제품은 상대적으로 크로스세일이 용이하다. 마트 계산대에 껌이나 캔디 같은 제품이 놓여 있는 이유이기도 하다. 짧은 구매여정 속에서도 크로스세일을 유도하여 구매당 단가를 높일 수 있다.
상품이 많을 수록, 고객의 수가 많을 수록 할 수 있는 CRM 시나리오의 종류도 다양해지는 편이다.
상품이 많다면 여러 상품별 구매 데이터를 활용한 AI 상품추천(Recommendation), 크로스세일이 더욱 힘을 받는다. 상품이 많으니 카테고리, 브랜드 등의 부가적인 데이터도 활용하기 용이하며, 데이터가 많으니 기능의 성능도 올라간다.
고객이 많다면 유저 세그멘테이션(User Segmentation)이나 유저 클러스터링(User Clustering)을 적극적으로 활용할 수 있다. 유저가 많은만큼 유저 집단별 특성을 명확하게 확인할 수 있다.
반대의 경우엔 위와 같은 부가 기능들의 중요도가 상대적으로 떨어진다.
상품 추천 로직을 만들었는데, 추천하는 상품이 다 똑같은 상품들일 수 있고, 유저 세그멘테이션을 진행했는데 세그먼트 당 유저 모수가 몇 명 되지 않을 수도 있다.
상품의 수, 고객의 수가 많지 않다면 위와 같은 마케팅 전략들의 영향력이 줄어든다. 그리고 쿠폰, 혜택 등의 프로모션이 CRM 성과에 기여하는 비중이 커진다.
CRM 자동화 솔루션들이 프로모션을 짜주지는 않으니, 상대적으로 가벼운 솔루션을 찾는 것이 가성비를 높이는 것도 방법일 수 있다.
BM에 따라서도 취할 수 있는 전략이 달라진다.
여러 브랜드의 제품을 파는 이커머스 플랫폼의 경우, 플랫폼보다 내가 판매하는 '브랜드'의 충성도에 의해 서비스를 이용할 가능성이 크다.
극단적인 예시가 플랫폼과 나이키의 관계다. 대부분의 플랫폼에서 나이키 제품은 판매량 최상위권을 차지한다. 유저는 나이키를 살 건데, 여러 쇼핑몰을 찾아다니며 나이키를 제일 싸게 파는 곳을 찾을 뿐인 것이다.
(그래서 이런 브랜드들은 분석에서 제외하고 보기도 한다.)
이런 브랜드의 충성도를 활용하여, 메시지에 유저가 관심을 보인 브랜드를 개인화한다면 전환율을 높일 수 있다.
F&B의 경우 재구매가 용이하다. 가격이 저렴하기도하고, 업종 자체가 '먹는 것'이기 때문이다. 그러나 유저에겐 선택지가 너무 많다. 보편적인 재구매주기를 찾고, 재구매주기를 단축시키기 위한 메시지가 유효할 수 있다. 또, 주문 프로세스에서의 크로스세일 유도를 통해 판매액을 높일 수도 있다.
교육, 여행처럼 오랜 기간 지속되는 서비스를 제공하는 경우, 구매 이후의 유저 경험이 더욱 중요하다.
사실 이런 서비스는 고객이 니즈가 없으면 판매를 유도하기 어렵다. 유저는 영어를 배울 각오를 하고, 휴가를 내고 해외 여행을 갈 계획을 세워야지만 구매한다.
그래서 구매이후 이용/경험을 돕는 지속적인 메시지를 통해 유저에게 긍정적 구매 경험을 심어주어야 한다. 긍정적 경험을 통해 재구매 전환율을 높이는 것이다.
내가 처한 환경을 고려했으니, 이젠 각 솔루션별 특징을 확인해볼 차례다.
CRM 자동화 솔루션들은 CRM 메시지 자동화뿐만 아니라 성과를 높일 수 있는 다양한 기능들을 제공하고 있으며, 이런 기능들이 각 솔루션들에게 차이점을 준다.
브레이즈 (Braze)
Braze의 경우 강력한 개인화 기능을 가지고 있다. Liquid, Connected Content와 같은 높은 수준의 개인화를 사용할 수 있는 기능들이 있다. 그 밖에도 Canvas내 다양한 종류의 스텝들을 통해 유저와의 관계를 구축할 수 있으며, 범용적인 마테크 연동성, 다양한 AI 기능들도 장점으로 꼽을 수 있다.
기능들이 다양하고 파워풀한만큼, 실행할 수 있는 시나리오가 가장 많은 솔루션이 아닐까 싶다.
원시그널 (OneSignal)
OneSignal의 강점은 '가성비'라고 볼 수 있다. 기본 SDK설치에 10분이 채 걸리지 않는 가벼운 설치에 가격도 상대적으로 저렴한 편이며, 초심자가 사용하기에 난이도도 상대적으로 쉬운 편이다.
설치와 비용, 활용이 가볍다고 해서 기능이 부족한 것은 아니다. 개인화를 위한 Liquid를 제공하며, Braze의 Connected Content 기능과 유사한 Custom Data 기능도 있다.
CRM 마케팅이 익숙치 않거나, 서비스 특성상 진행할 수 있는 CRM 시나리오가 명확한 경우 적합하다.
인사이더 (Insider)
Insider는 웹 환경에서의 '높은 자유도'와 '상품추천'이 강점이다. 앱 내 웹뷰 영역도 포함이다.
웹 환경에서 다양한 배너, 소셜프루프, 게이미피케이션, 인브라우저 메시지 등을 사용할 수 있다. 템플릿도 굉장히 다양해서 노코드로 여러 CRM 시나리오를 구현할 수 있다.
자사 서비스에 웹 영역의 중요도가 높고, 커스터마이징의 자유도를 원한다면 좋은 선택지가 될 수 있다.
아이쿠아 (AIQUA)
AIQUA는 AI에 강점이 있다. AI를 개발한 애피어(Appier)는 자신들을 'AI 회사'라고 칭할 정도로 AI에 많은 투자를 하고 자신이 있다. AI를 활용한 카피라이팅, 상품추천, 이탈/구매 예측, 타겟팅뿐만 아니라, CRM 시나리오 제작까지 가능하다.
CRM 솔루션의 기본적인 기능들과 더불어, AI를 활용해 CRM 마케팅 효율을 강화하고 싶은 경우 적합하다.
그 밖에도 아래의 사항들을 함께 참고해보면 좋다.
• 타 솔루션과의 연동성
Product Analytics, MMP 등 다양한 마테크 솔루션과의 CRM 솔루션을 연계 활용하고자 한다면, 내가 사용하는 마테크 솔루션들과의 연동이 지원되는지 살펴보면 좋다.
• 고객지원 / 기술지원
처음부터 마테크 솔루션들의 기능을 100% 활용하기는 매우 어려운 일이다.
내가 원하는 수준의 고객지원 / 기술지원을 받을 수 있는지 체크해보아야 한다.
* 보통 마테크는 고객지원과 기술지원이 따로 구성되어 있다.
• 컨설팅, 대행 등의 도움을 받을 수 있는지
CRM 시나리오를 구현하고, 마케팅 목표를 달성하기엔 마테크 솔루션들의 지원 만으로는 부족할 수 있다. 솔루션 사용을 돕지만, 비즈니스 성장을 위한 마케팅을 지원하진 못하기 때문이다.
어떠한 형태든 마케팅에 어려움을 겪고 있다면, 비즈니스를 성공시키는 마케팅을 위한 컨설팅 / 대행 서비스를 통해 도움을 받는 것이 좋다.
마티니(Martinee)에서는 이 글에서 언급된 모든 솔루션들의 컨설팅 / 대행 서비스를 제공한다.
다들 알다시피 가성비는 가격대비 '성능'의 비율을 의미한다.
가격은 솔루션 가격을 의미할 것이고, '성능'은 무엇을 기준으로 판단해야할까?
앞서 설명한 솔루션별 기능과 장점들도 성능에 포함되지만, 솔루션을 사용하는 사용자의 영향이 더 크다고 본다.
결국 성능을 결정하는 가장 큰 요인은 파일럿인 것이다.
많은 회사들과의 미팅, 컨설팅을 통해 솔루션 없이도 높은 수준의 CRM을 실행하는 곳도 발견한 반면, 그냥 CRM 솔루션으로 무분별한 메시지만 발송하는, 소위 '앱푸시 발사대'로만 사용하는 경우도 많이 보았다.
자신의 도메인에 적합한 솔루션을 선택하고, CRM 마케팅을 꾸준히 개선시키고 학습하는 마케터가 CRM 마케팅 솔루션의 가성비를 결정하는 키를 쥐고 있다.
November 13, 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를 활용하여 다양한 캠페인 운영이 가능하다.
.png)
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 23, 2024
엑셀과 스프레드시트를 어느정도 다루시던 분들은 조건부 서식에 어느정도 익숙하실 겁니다.
조건부 서식은 데이터를 보다 효과적으로 표현하고 분석하는 강력한 기능입니다. 이는 특정 조건에 따라 셀의 모양(글자 색상, 셀 색상)을 자동으로 변경하여 중요한 정보를 시각적으로 돋보이게 만드는 기능입니다.
위의 이미지 예시를 보면 더 쉽게 이해할 수 있습니다. 왼쪽은 아무런 설정을 하지 않은 차트라면 오른쪽은 숫자의 백분위수를 기준으로 색상을 표현하였습니다. 오른쪽의 표가 일자별 노출수의 차이를 훨씬 쉽게 이해할 수 있습니다.
조건부 서식의 가장 큰 특징은 데이터에 기반한 동적인 시각화입니다. 사용자가 정의한 규칙에 따라 데이터가 변경될 때마다 서식도 자동으로 업데이트됩니다. 이는 단순히 정적인 색상이나 서식을 적용하는 것과는 다르게, 항상 최신 데이터를 반영한 시각적 표현을 제공합니다.
루커스튜디오와 같이 실시간으로 변하는 데이터 시각화 솔루션에서는 필수적으로 활용하면 좋을 기능입니다.
루커스튜디오에도 이러한 조건부 서식이 있으며 다른 엑셀과 Tableau와 같은 BI와 유사한 기능을 사용할 수 있습니다.
기본적으로 'Tablea' 차트와 'Score' 차트에서 활용가능합니다.
단색과 색상스케일에 따라 구분할 수 있습니다. KPI 달성이나 임계값 달성에 대한 강조를 원한다면 단색 유형이 유용합니다. 반면에 데이터의 양이 많고 데이터간 상대적 차이가 중요하다면 색상 스케일이 유용합니다.
규칙별로 하나의 조건만 가능하며 조건 형식은 셀 또는 전체 행에 적용할 수 있습니다.
September 13, 2024
행사명 : 마티니 AP 본부 채용설명회
장소: 서울 서초구 서초대로38길 12 마제스타시티 타워2 12F
일시: 2024년 9월 26일 목요일 오후 7시 ~ 9시
대상:
2024년 9월 26일 목요일 오후 7시 Marketing Intelligence People, 마티니와 마티니 AP 본부의 퍼포먼스 마케팅을 여러분들께 소개합니다.
AP 본부에서 진행했던 고객사 및 프로젝트 사례들을 기반으로 직무 및 채용 관련 설명을 드릴 예정입니다.
마티니 근무환경과 문화, 복지도 함께 살펴보세요!
19:00 - 19:20 Martinee Marketing Intelligence 2024 | 이선규 Martinee CEO
19:20 - 19:40 About Martinee Account Planning | 김영근 Martinee Account Planning Lead
19:40 - 19:50 사전 QnA (현장질문포함)
19:50 - 20:00 Break Time
20:00 - 20:15 About Martinee Culture & Benefits | Aimed People Team
20:15 - 21:00 Networking & QnA
- 자리가 한정되어 있어 별도로 선정 안내를 드릴 예정입니다.
- 주차권 제공 가능하며 리셉션 데스크 문의바랍니다.
- 참석자분들에게 간단한 음식이 제공됩니다.
- 문의사항은 mkt@martinee.io 로 문의바랍니다.
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
행사명 : 데이터 기반 고객 여정 설계를 위한 CRM과 PA 연계 전략 세미나
장소: 서울 서초구 서초대로38길 12 마제스타시티 타워2 12F
일시: 2024년 9월 25일 수요일 오후 4시 ~ 6시
대상
2024년 9월 25일 수요일 오후 4시 마티니, 앰플리튜드, 원시그널이 함께하는 세미나에서각 솔루션의 활용 사례와 고객 경험을 최적화할 수 있는 PA & CRM 솔루션 연계 활용법을 알려드립니다.
이번 세미나에서 PA 및 CRM 솔루션 활용 사례를 확인하고 비즈니스에 직접 적용할 수 있는 과정들을 살펴보세요.
16:05 ~ 16:30
[앰플리튜드 세션] 이재철 연사
앰플리튜드 국내 활용사례
16:30 ~ 16:55
[원시그널 세션] 서영진 연사
원시그널 글로벌 활용사례
16:55 ~ 17:20
[마티니 세션] 이건희 연사
CRM 자동화 & PA 솔루션 시너지 발휘하기
세션 이후에는 자유로운 네트워킹과 QnA를 할 수 있는 시간이 마련되어있어 솔루션 연계 활용 및 PA와 CRM 솔루션 도입 관련해서 고민을 나누고 소통할 수 있습니다.
- 신청 시 회사 이메일이 아닐 경우 등록이 제한될 수 있습니다.
- 자리가 한정되어 있어 별도로 선정 안내를 드릴 예정입니다.
- 주차권 제공 가능하며 리셉션 데스크 문의바랍니다.
- 참석자분들에게 간단한 음식이 제공됩니다.
- 문의사항은 mkt@martinee.io 로 문의바랍니다.
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 5, 2024
데이터를 다루면서 고객의 업무 효율을 높이는 것을 도와드리고 있지만, 정작 저의 일에서는 데이터 정리와 효율화는 잘 못하고 있더라고요. 그래서 요즘은 Make와 Zapier를 통해 최대한 많은 일들을 자동화 하면서 좀 더 저의 자유(?) 시간을 만들어가고 있습니다.
👉 Make 자동화 : https://www.make.com/en
그런데 어느순간 Make 자동화가 많아지면서 제가 만들고 운영중인 자동화가 뭔지 헷갈리기 시작했습니다. Make 자동화로 업무효율화를 만들었지만 그럴수록 자동화 솔루션이 정리가 되지 않는 아이러니...
Make에서는 하나의 자동화 과정을 시나리오라고 해서 각 시나리오를 Json 형식으로 저장해서 관리할 수 있습니다. 이러한 Make의 특징을 활용해서 Make에서 시나리오가 새롭게 만들어지거나 업데이트가 되면 각각 구글 드라이브와 노션에 저장 & 업데이트 되는 자동화를 만들어봤습니다.
- 자동화 솔루션 : Make
- DB : Notion, Make DB
- 자료 정리 : 구글 드라이브
1️. Make 어드민의 다양한 시나리오들입니다. 카테고리를 만들 수 있긴하지만 그것만으로는 한번에 어떤게 있는지 확인이 쉽지 않습니다.
2️. Make 자동화 설계 화면 입니다.
3️. Notion에 저장된 최종적인 모습입니다.
👉 업무를 하다보면 고객 리드, 업무 파일, 데일리 보고 등 DB화 & 자료를 정리해야하는 업무들이 빈번하게 있습니다. 해당 시나리오처럼 매번 생산되는 자료를 구글 드라이브와 노션에 자동으로 기록한다면 생각보다 많은 업무를 효율화 할 수 있습니다.
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 30, 2024
B2B 비즈니스를 하다보면 고객 리드를 확보하는 마케팅을 많이합니다. 특히 블로그를 통해 자사 비즈니스의 관심과 이해를 높이면서 자연스럽게 리드를 확보하는 전략을 잘 활용합니다. 마티니도 마찬가지로 다양한 자료들을 블로그와 링크드인 등을 통해 공유 하면서 고객 리드를 자연스럽게 확보하고 있습니다.
처음에는 자료도 많지 않고 리드 인입도 드물어서 고객 리드에 대한 대응이 큰 문제가 없었습니다. 아마 대부분의 B2B 회사처럼 정보성 자료로 고객 리드를 확보하는 경우, 리드가 들어온 것을 확인(인지) 하고 리드의 정보를 확인하고(리드 확인) 고객 세일즈 메일(메일 발송)을 보내면서 자사의 서비스를 알리는 단계를 진행하였습니다.
문제는 리드 수집을 위한 정보성 자료의 수와 경로가 서서히 많아지면서 발생합니다. 어느 수준 이상이되면 고객 리드를 잘 확인하는 것도 쉽지 않습니다. Typeform이나 Googleform을 통해 수집하는 경우 스프레드시트와 Slack으로 리드 수집 현황을 보내주긴 하지만 담당자가 부재하거나 다른일을 하는 경우에는 리드 인지 자체를 놓치는 경우도 존재합니다.
실제로 저희도 리드가 많아지면서 리드 담당자의 업무 부하가 늘고 이에따라 리드 피드백이 늦어지면서 대응이 누락되는 경우가 종종 발생하였습니다.
현재는 해당 과정을 모두 자동화해서 리드 인입부터 리드 고객 정리, 리드 정보 요약, 메일 발송까지 모두 자동화 했습니다.
- 리드 수집 : Featpaper
- 리드 알람 : Slack
- 리드 정보 요약 : Chat GPT
- 리드 DB 정리 : Spreadsheet
- 리드 메일 보내기 : Gmail
- 업무 자동화 : Zapier
1️. 이미지는 B2B 리드 마케팅 프로세스로 마티니의 As-is / To-be 모습입니다. 빨간색 블럭이 자동화된 영역입니다.
2️. Zapier 자동화 설계 화면 입니다.
3️. 리드 획득 후 해당 회사의 정보를 찾아보고 정리하는 것도 생각보다 시간이 많이 듭니다. 해당 프로세스를 Chat GPT를 이용해서 일부 도움을 받을 수 있었습니다. 다만, GPT의 정보 최신정 문제와 정보 신뢰도 문제가 있습니다. 프롬프트 엔지니어링을 통해 정보를 못 찾는 경우 정보가 없다는 결과값을 뱉어낼 수 있게 하였습니다.
🙋♂️ 더 해볼 것 : GPT가 정보를 잘 못 찾는 문제는 Perplexity 같은 URL과 검색 기반으로 정보를 수집하는 AI 솔루션으로 대체하면 어느정도 해결이 가능합니다. 아쉽게도 zapier의 연동 app 목록에는 현재 없어서 추후 Make를 통해 구현할까 생각 중입니다. 더 써보면서 자동화 가능 영역을 찾아 업무 생산성을 높이는 고민을 계속 해보려고 합니다.
👉 자동화 너무 재밌네요. 여러분도 AI와 Automation 사용해서 업무 생산성 높여보세요!
August 30, 2024
이 글을 읽고 계시다면 코호트 분석을 이미 하고 계실 건데 측정 기준에 대해서 의문이 생기신 분이 보실 것 같네요
구글에 '코호트 분석 SQL' 라고 검색하면 정말 많은 글들이 많습니다.
글에서 소개하는 쿼리 예시는 대부분 datediff함수를 활용해서 Date Granularity를 계산합니다.
이해하기 쉽게 예를 들어보겠습니다.
유저 1 : 23:30 에 회원가입 후 다음날 다시 들어왔습니다.
유저 2 : 13:30에 회원가입 후 다음날 다시 들어왔습니다.
day 단위로 계산을 하면 유저 1 은 우리 서비스를 30분 경험하고 다음날 재방문했다고 계산됩니다.
유저 2는 약 10시간 30분 서비스를 경험하고 재방문을 했다고 계산됩니다.
동일한 조건일까요? 그렇지 않죠?
만일 시간 단위로 계산을 하게 되면 특정 행동을 수행한 시간부터 다음 행동까지의 Time window를 24시간 뒤로 하면 이 유저는 다음날이 아닌 모레 재방문했다고 계산되겠죠?
DATEDIFF( [first_event_dt], [second_event], DAY )
DATEDIFF( [first_event_dt], [second_event], HOUR ) / 24 )
월단위로 계산할 때도 마찬가지입니다.
월별 일자수가 모두 다릅니다. 1월(31일), 2월(28일), 4월(30일)...
월 단위로 측정할 때도 30일로 모두 통일해줍니다.
DATEDIFF( [first_event_dt], [second_event], HOUR ) / 24 * 30)
이렇게 계산되면 유저별로 경과 시간은 모두 통일 되었습니다!!
실제로 Amplitude(앰플리튜드)의 코호트 분석 기능에는 이런 기능들이 존재합니다. 만약 안 쓰고 계시다면 직접 쿼리를 날려서...
여기 가이드를 보시면 앰플리튜드가 24시간 단위로 경과 시간을 측청 하는 방식을 설명해 두었습니다.
24시간 윈도우 기준, 캘린더 기준으로 경과 시간(t)을 측정하는 옵션이 있죠?
얼마나 차이를 보였는지 가상의 데이터로 확인을 해보았습니다.
(참고로 더미 데이터는 kaggle 이나 Mockaroo 에서 생성하실 수 있습니다)
참고로 해당 데이터 계산 기준은 월별 첫 구매 기준 재구매율입니다.
t = 1 지점부터 차이를 보이기 시작하는데 t = 0 이 100%라서 차이가 잘 안 보입니다. 로그 스케일을 통해 다시 확인해 보면
확실히 달력 기준의 리텐션율이 조금 더 높아 보이네요
얼마나 차이 나는지 두 기준의 리텐션율을 나눠 보겠습니다 최대 1.27배까지 납니다. (아래 차트에서는 0은 무시합니다. t = 0 은 100%이기 때문에)
t = 1 : 1.15배
t = 22 : 1.27배
데이터에 따라서 차이가 달라지겠지만
코호트의 기준이 만일 회원가입일 기준의 재구매율이거나 회원가입일 기준 재방문율을 측정한다면 더 많은 차이를 보일 수 있을 걸로 예상됩니다.
제가 사용한 쿼리는 아래와 같습니다.
WITH tb_pay_first AS (
SELECT country
,user_id
,min(pay_datetime_id) first_pay_datetime_id
FROM order
GROUP BY 1,2
)
, tb_base_ AS (SELECT st0.*
, FLOOR(TIMESTAMPDIFF(HOUR, st1.first_pay_datetime_id, st0.pay_datetime_id) / 24) AS days_since_first_pay
, FLOOR(TIMESTAMPDIFF(HOUR, st1.first_pay_datetime_id, st0.pay_datetime_id) / (24 * 30)) AS months_since_first_pay_period_24h
, (YEAR(pay_datetime_id) - YEAR(first_pay_datetime_id)) * 12 + (MONTH(pay_datetime_id) - MONTH(first_pay_datetime_id)) AS months_since_first_pay_period_day
, st1.first_pay_datetime_id
FROM order st0
LEFT JOIN tb_pay_first st1
ON st0.user_id = st1.user_id
AND st0.country = st1.country
WHERE 1 = 1
)
, tb_base_24h AS (
SELECT time_id_
, country
, since_time_period_24h
, CASE
WHEN 'acc' = 'normal' THEN SUM(SUM(IF(since_time_period_24h = max_since_time_period_24h, repurchase_user_cnt, 0))) OVER
(PARTITION BY time_id_, country ORDER BY since_time_period_24h DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
ELSE SUM(repurchase_user_cnt)
END AS repurchase_user_cnt
, count(1) pay_user_cnt
, sum(sales) AS sales
FROM (SELECT *
, CASE WHEN since_time_period_24h = 0 AND pay_cnt > 1 THEN 1
WHEN since_time_period_24h = 0 AND pay_cnt <= 1 THEN 0
ELSE 1
END AS repurchase_user_cnt
, MAX(since_time_period_24h) OVER (PARTITION BY country, user_id) as max_since_time_period_24h
FROM
(SELECT tmp0.time_id_
, tmp0.country
, tmp0.since_time_period_24h
, tmp0.user_id
, SUM(tmp0.pay_cnt) AS pay_cnt
, SUM(tmp0.sales) AS sales
FROM
(SELECT DATE_FORMAT(first_pay_datetime_id ,'%Y-%m-01') time_id_
, country
-- , months_since_first_pay_period_day AS since_time_period_day
, months_since_first_pay_period_24h AS since_time_period_24h
, user_id
, COUNT(distinct order_id) AS pay_cnt
, SUM(sales) as sales
FROM tb_base_
-- WHERE DATE_FORMAT(first_pay_datetime_id ,'%Y-%m-01') >= '2023-01-01'
GROUP BY 1,2,3,4) tmp0
GROUP BY tmp0.time_id_
, tmp0.country
, tmp0.since_time_period_24h
, tmp0.user_id
) tmp
) tmp1
GROUP BY time_id_
, country
, since_time_period_24h
)
, tb_base_day AS (
SELECT time_id_
, country
, since_time_period_day
, CASE
WHEN 'acc' = 'normal' THEN SUM(SUM(IF(since_time_period_day = max_since_time_period_day, repurchase_user_cnt, 0))) OVER
(PARTITION BY time_id_, country ORDER BY since_time_period_day DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
ELSE SUM(repurchase_user_cnt)
END AS repurchase_user_cnt
, count(1) pay_user_cnt
, sum(sales) AS sales
FROM (SELECT *
, CASE WHEN since_time_period_day = 0 AND pay_cnt > 1 THEN 1
WHEN since_time_period_day = 0 AND pay_cnt <= 1 THEN 0
ELSE 1
END AS repurchase_user_cnt
, MAX(since_time_period_day) OVER (PARTITION BY country, user_id) as max_since_time_period_day
FROM
(SELECT tmp0.time_id_
, tmp0.country
, tmp0.since_time_period_day
, tmp0.user_id
, SUM(tmp0.pay_cnt) AS pay_cnt
, SUM(tmp0.sales) AS sales
FROM
(SELECT DATE_FORMAT(first_pay_datetime_id ,'%Y-%m-01') time_id_
, country
, months_since_first_pay_period_day AS since_time_period_day
-- , months_since_first_pay_period_24h AS since_time_period_24h
, user_id
, COUNT(distinct order_id) AS pay_cnt
, SUM(sales) as sales
FROM tb_base_
GROUP BY 1,2,3,4) tmp0
GROUP BY tmp0.time_id_
, tmp0.country
, tmp0.since_time_period_day
, tmp0.user_id
) tmp
) tmp1
GROUP BY time_id_
, country
, since_time_period_day
)
, cohort_base_24h AS
(SELECT time_id_
, country
, since_time_period_24h
, repurchase_user_cnt
, pay_user_cnt
, sales
, SUM(sales) OVER w AS acc_sales
, FIRST_VALUE(pay_user_cnt) OVER w AS cohort_user_cnt
, COUNT(1) OVER (PARTITION BY country) AS cohort_cnt
FROM tb_base_24h
WINDOW w AS (PARTITION BY time_id_, country ORDER BY since_time_period_24h RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
)
, cohort_base_day AS
(SELECT time_id_
, country
, since_time_period_day
, repurchase_user_cnt
, pay_user_cnt
, sales
, SUM(sales) OVER w AS acc_sales
, FIRST_VALUE(pay_user_cnt) OVER w AS cohort_user_cnt
, COUNT(1) OVER (PARTITION BY country) AS cohort_cnt
FROM tb_base_day
WINDOW w AS (PARTITION BY time_id_, country ORDER BY since_time_period_day RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
)
SELECT *
, (pay_user_cnt * 100) / cohort_user_cnt AS retention_rate
FROM
(SELECT 1 AS time_id
, country
, since_time_period_24h
, SUM(repurchase_user_cnt) AS repurchase_user_cnt
, SUM(pay_user_cnt) AS pay_user_cnt
, FIRST_VALUE(sum(cohort_user_cnt)) OVER(PARTITION BY country RANGE BETWEEN UNBOUNDED PRECEDING and CURRENT ROW) as cohort_user_cnt
FROM cohort_base_24h
GROUP BY 1
, country
, since_time_period_24h) tmp3분석의 기준은 굉장히 중요합니다. 어떤 기준으로 분석하느냐에 따라서 의사결정 방향이 달라질 수 있겠죠?
코호트 분석은 시간을 계산해서 집계하는 분석인만큼 시간의 기준을 제대로 설정하는 게 중요합니다.
저도 실제로 분석해 보면서 분석 기준의 중요성을 다시 한번 깨달을 수 있었습니다.
혹시 지금 day 단위로 코호트 활용해 재구매율을 측정하고 계신다면 24시간 단위로 계산해 보시면 어떨까요?
Reference
https://medium.com/@paul.levchuk/how-to-build-user-cohort-properly-b70a74e5e1c8
