이전 글 마지막에,

추가로 구현 하고 보완 해야 할 것들이 있습니다.

현재 구현한, x-axis 를 step 으로 하는 column chart 뿐 아니라, x-axis 를 시간 으로 하며 각 step-wise conversion rate 들을 series 로 그리는 line chart 도 조회 하고 싶을 것 입니다.

라고 적었었습니다. 이를 구현 합니다.

input

• funnel 을 구성하는 event name 들
• date range

를 input 으로 넘기면 되겠습니다.

{
    "event_names": [
        "first_open",
        "sign_up",
        "tutorial_completed"
    ],
    "date_range_start": "2023-05-01",
    "date_range_end": "2023-05-03"
}

output

input 을 받아서, 아래 같은 output 을 return 하면 되겠습니다.

[
    {
        "event_name1": "first_open",
        "event_name2": "sign_up",
        "data": [
            {
                "date": "2023-05-01",
                "value": 0.812
            },
            {
                "date": "2023-05-02",
                "value": 0.828
            },
            {
                "date": "2023-05-03",
                "value": 0.821
            }
        ]
    },
    {
        "event_name1": "sign_up",
        "event_name2": "tutorial_completed",
        "data": [
            {
                "date": "2023-05-01",
                "value": 0.711
            },
            {
                "date": "2023-05-02",
                "value": 0.729
            },
            {
                "date": "2023-05-03",
                "value": 0.736
            }
        ]
    }
]

input 으로 넘긴 event name 들에서 인접한 pair 마다 step-wise 로 conversion rate 를 집계 합니다. 위에 적은 예시 input 에서는,

• first_open
• sign_up
• tutorial_completed

였으니,

• first_open → sign_up
• sign_up → tutorial_completed

의 pair 들에 대한 conversion rate 를 집계 합니다.

sql 로 집계

event table 들에 대해 몇 가지를 가정 합니다.

• event dataset 에 event 마다 table 이 이다
• 유저를 식별 할 수 있는 user_id column 이 있다.
• event 시각을 알 수 있는, timestamp type 의 created_at column 이 있다.
• 그 created_at 의 date 값이, date type 의 _date column 으로 있다.

그러면, 어느 step-wise 의 conversion rate 를 집계하는 sql 에 대한 jinja template 을 만들 수 있습니다.

with

event as (

-- 두 event 를 union 하고,

select
  _date,
  user_id,
  '{{ event_name1 }}' as event_name,
  created_at,
from
  event.{{ event_name1 }}

union all

select
  _date,
  user_id,
  '{{ event_name2 }}' as event_name,
  created_at,
from
  event.{{ event_name2 }}

)

, event_augmented as (

-- 직후 event name 을 달고,

select
  _date,
  user_id,
  event_name,
  lead(event_name, 1) over (partition by user_id order by created_at) as event_name_next,
from
  event

)

, aggregated_by_date_and_user as (

-- date, user 에 대해 aggregate 하고,

select
  _date,
  user_id,
  logical_or(event_name_next = '{{ event_name2 }}') as is_converted,
from
  event_augmented
where
  event_name = '{{ event_name1 }}'
  and _date between '{{ date1 }}' and '{{ date2 }}'
group by
  _date, user_id

)

, aggregated_by_date as (

select
  _date,
  countif(is_converted) / count(1) as value,
from
  aggregated_by_date_and_user
group by
  _date

)

select
  '{{ event_name1 }}' as event_name1,
  '{{ event_name2 }}' as event_name2,
  array(
    select as struct
      _date as date,
      value,
    from
      aggregated_by_date
    order by
      date
  ) as data,

template 을 사용하여 sql 을 render 할 때, {{ event_name1 }} 같은 variable 값을 넘기면 됩니다.

이를 jinja 에서 macro 로 만들고,

{% macro line_chart_step_wise_conversion(event_name1, event_name2, date1, date2) %}

-- 위의 sql template 을 여기에 적는다.

{% endmacro %}

jinja 의 for loop 으로 step-wise 들을 iterate 하며, macro 를 호출 하고, union all 로 합쳐서,

{% import 'line_chart_step_wise_conversion.sql' as line_chart_step_wise_conversion %}

{% for (event_name1, event_name2) in event_name_pairs %}
  {% if not loop.first %}
union all
  {% endif %}
(

{{ line_chart_step_wise_conversion.line_chart_step_wise_conversion(event_name1, event_name2, date1, date2) }}

)
{% endfor %}

sql template 을 완성 합니다.

적당한 sample data 가 있으면 좋을텐데, 찾아보니, 어떤 game app 의 googla analytics 4 data 를 bigquery 에 export 한 것이 public dataset 으로 공개 되어 있습니다. 적당한 event_name 을 골라서, 이 글의 예시 funnel 을 구성하는 event 들에 mapping 하고, data 를 보니 2018년 중의 data 라서, 적당히 2023년으로 조정하여, table 을 만듭니다.

-- e.g.

create or replace table event.first_open
partition by _date
as

select
  user_pseudo_id as user_id,
  timestamp_micros(event_timestamp) + interval (5 * 365 - 2 * 30) day as created_at,
  date(timestamp_micros(event_timestamp) + interval (5 * 365 - 2 * 30) day) as _date,
from
  `firebase-public-project.analytics_153293282.*`
where
  event_name = 'level_start_quickplay';

그렇게 만든 sample data 에 대해, 생성된 sql 을 실행 하면, 위에서 정한 output schema 와 동일한 schema 의 결과를 얻을 수 있습니다.

결과

관련 code 는 github 에 있습니다.