MMP(AppsFlyer), GA4, Braze 데이터를 활용해 시각화할 때 반드시 고려해야할 사항(2)- 데이터 전처리하기

지난 편에서는 각 솔루션별 데이터 수집 방법(바로가기)에 대해서 알아보았습니다.

MMP(AppsFlyer), GA4, Braze 데이터를 활용해 시각화할 때 반드시 고려해야할 사항(1)- Third Party 데이터

 

1. Third Party 데이터 수집 자동화
2. 수집된 데이터 전처리하기
3. 시각화하기

이번에는 수집된 데이터를 활용하여 어떻게 전처리 해야 되는지 알아보겠습니다. 

 

각 데이터 셋들은 분석하려는 차원(dimension)을 기준으로 집계(group by)를 통해서 동일한 형태로 전처리한 후 조인 연산을 통해 데이터를 통합하는 과정이라고 생각하시면 됩니다.

 

마케팅 성과를 분석하기 위해서 필요한 데이터 다음과 같습니다. 

(필요에 따라서 Dimension 의 개수는 추가될 수 있습니다. ex)device_category, country, 등등)

Dimension  : 날짜(일별), 유입소스(GA4 = utm_source, Appsflyer = Media_source), 캠페인

Metric :  구매수, 매출, 인스톨 수 

 

구매 데이터(MMP, GA4) 통합하기 

GA4가 웹, 앱 모두 트래킹을 할 수 있지만 WEB은 GA4, APP은 MMP로 성과를 합쳐서 보실 겁니다. 

GA4, Firebase는 앱과 웹 내 고객 행동 분석에 주로 쓰이는 툴이고 MMP 는 광고 성과를 측정하는 툴이니깐요

그러면 이렇게 됩니다.

 

<GA4 데이터 집계> - WEB

Dimension  : 날짜(일별), 세션 소스, 캠페인

Metric :  구매수, 매출

 

<MMP 데이터 집계> - APP

Dimension  : 날짜(일별), Media_source, 캠페인

Metric :  구매수, 매출, 인스톨 수

 

집계된 두개의 테이블을 UNION 다시 한번 집계를 해줍니다. 

이렇게 하면 집계된 웹앱 데이터 통합이 되었습니다. (MMP 데이터 전처리 과정은 생략)

Dimension  : 날짜(일별), Media_source, 캠페인

Metric :  구매수, 매출, 인스톨 수

 

브레이즈 커런츠 데이터와 구매 데이터(MMP, GA4) 연결하기

 

브레이즈 커런츠(braze currents)데이터는 유저 인게이지먼트 데이터입니다. 

 

여기에는 유저별로 어떻게 우리 서비스로 참여를 하고있는지를 기록한 데이터들이 기록되어 있으니 메시지 발송 수단별로 노출, 클릭 성과들이 들어있습니다. 하지만 우리는 성과(구매) 판단은 MMP, GA4로 해야 하므로 서로 다른 데이터들을 어떻게 통합해서 볼지를 알아보겠습니다. 

커런츠 데이터는 아래와 같이 구성이 되어있습니다. 

 

(차원 데이터 생성을 위한 함수는 태블로 함수입니다)

 

유니크한 ID 차원 만들기

위와 같은 데이터를 일자, 메세지 발송 타입, 캠페인 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])

 

발송 타입별 유니크한 ID 차원 만들기

{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 칼럼을 생성하였습니다. (이전 글을 참고해 주세요)

 

1. ['users.messages.email.Open.avro',
2.  'users.messages.email.Click.avro',
3.  'users.messages.pushnotification.Send.avro',
4.  'users.messages.inappmessage.Click.avro',
5.  'users.messages.email.Delivery.avro',
6.  'users.messages.pushnotification.Open.avro',
7.  '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 데이터는 아래와 같이 집계되었습니다.

Braze Currents 전처리 / 집계한 테이블 예시

 

통합된  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 예시

 

 

본론으로 돌아와서! 

 

Full Outer Join을 수행하기 전에 앞서 GA4, MMP 데이터를 합친 후 우리는 CRM 데이터만 필요하기 때문에 CRM 데이터만 필터를 합니다.

 

이제 Braze Current 데이터와 성과 데이터(GA4, MMP)를 날짜, 유입소스명, 캠페인명을 조인키로 두고 Full Outer Join 을 수행합니다.(분석하고자 하는 차원데이터가 추가로 더 있다면 추가로 필요한 차원도 조인키로 활용하시면 됩니다.)

 

이와 같은 방식으로 Paid 성과 데이터를 전처리 할 때도 위와 같은 방법으로 수행하면 됩니다.

 

데이터를 전처리 할 때 중요한 건 분석하고자 하는 결과물을 먼저 그려본 뒤에 결과를 도출하기 위해서 각 테이블을 어떻게 만들어 갈 것인지 생각하면서 만들어가면 됩니다. 실제로 전처리하다 보면 자잘하게 처리해야 되는 부분이 상당히 많습니다.

 

특히 데이터를 통합하기 위해서는 무엇보다 네이밍 컨벤션이 가장 중요한 점은 강조하지 않을 수 없습니다.

데이터 수집까지 잘했는데 캠페인 네이밍 컨벤션이 서로 다르다?.. 데이터 통합은 불가능합니다.. 

 

네이밍 컨벤션은 말 그대로 명명 규칙입니다. 

 

위에서 언급한 대로 우리가 데이터 통합을 위해 차원 데이터를 조인키로 활용한다고 했었는데 캠페인 차원이 MMP, GA4, Braze or 광고 데이터가 모두 다르면 안 되겠죠? 아래와 같이 통일을 시켜야만 데이터를 연결할 수 있습니다.

MMP	GA4
Media Source(ex. meta)	UTM_SOURCE(ex. meta)
Campaign(ex. reach_asc_all)	Campaign(ex. reach_asc_all)

 

여기까지 마케팅 데이터 수집부터 전처리 과정까지 알아보았습니다.

 

다음 글에서는 이 데이터를 활용해서 시각화 하는 방법을 살펴보겠습니다.