Cofacts GA4 Migration design doc

Previous attempt</summary>

SQL

SELECT
    event_timestamp,
    event_name,
    items.item_id,
    items.item_category,
    items.item_list_id,
    items.item_list_name,
    items.item_list_index
FROM `..._intraday_...`, UNNEST(items) AS items WHERE event_name = "view_item" LIMIT 1000

Result

</details>

webVisit, webUser

For analytics, we can just count item_id to get the view count of that specific article / reply.

In addition, we can also list the view count of a specific reply (in item_id, item_cateogry being Reply), and the view count distribution (can come from reply detail, or from different article details).

SQL

SELECT event_date, item_category, item_id,
  count(*) as webVisit,COUNT(DISTINCT user_pseudo_id) as webUser
FROM `${GA4_DATASET}.events_*`, UNNEST (items)
WHERE event_name = 'view_item'
  AND stream_id = '${GA_WEB_STREAM_ID}'
  AND ${event_date condition}
group by event_date, item_category, item_id

Verification

Web visit & web user of SQL result matches looker studio.

Note: we cannot verify reply count because Looker Studio do not allow us to calculate event count under a certain item_id (we use “page path” on the screenshot above, which works foraritcle detail page).

liff

SQL

WITH t AS (
  SELECT 
    event_date, item_category, item_id,
    struct(
      collected_traffic_source.manual_source as source,
      count(*) as visit, COUNT(DISTINCT user_pseudo_id) as user
    ) as liffObj
  FROM `${GA4_DATASET}.events_*`, UNNEST (items)
  WHERE event_name = 'view_item'
    AND stream_id = '${GA_LIFF_STREAM_ID}' -- LIFF stream
  GROUP BY event_date, item_category, item_id, collected_traffic_source.manual_source
)
SELECT event_date, item_category, item_id, ARRAY_AGG(liffObj) AS liff FROM t
GROUP BY event_date, item_category, item_id

• We should use collected_traffic_source.manual_source to match UA stat
  • The field collected_traffic_source is newly added on 2023/06/05 . Its documentation was not there in 2023 May.
  • If we use traffic_source.source (cross-channel source), we will include an extra “(direct)” for tmcheck with exactly 1 user & 1 view count. Also, it is not populated in intra-day tables.
• To create liff array with counts inside, we must
  1. first group by (event_date, item_category, item_id, source) to calculate count
  2. then merge liff objects of same (event_date, item_category, item_id) into array liff

Verification

Details of LINE bot

lineUser, lineVisit

SQL

SELECT
  DATE(evt.time, "${TIMEZONE}") as event_date, evt.category AS item_category, evt.label AS item_id,
  count(*) as lineVisit, COUNT(DISTINCT userId) as lineUser
FROM `${LINE_BOT_EVENT_DATASET}.events`, UNNEST (events) as evt
WHERE evt.action = 'Selected'
GROUP BY event_date, item_category, item_id

Verification

Note that UA contains JA bot’s group usage, thus has more users. But for less popular messages the view count is somewhat consistent.

Combined SQL

Constants

• LINE_BOT_EVENT_DATASET
• GA4_DATASET
• GA_WEB_STREAM_ID
• GA_LIFF_STREAM_ID

Parameters

• startDate
• endDate

WITH
  lineStats AS (
    SELECT
      FORMAT_DATE("%Y%m%d", DATE(createdAt, "+08:00")) AS event_date,
      evt.category AS item_category,
      evt.label AS item_id,
      COUNT(*) AS lineVisit,
      COUNT(DISTINCT userId) AS lineUser
    FROM `industrious-eye-145611.line_bot.events`, UNNEST (events) AS evt
    WHERE evt.action = 'Selected'
      -- Use createdAt so that BQ can select correct partition
      AND createdAt >= TIMESTAMP("2023-05-10T00:00:00.000+08:00")
      AND createdAt <= TIMESTAMP("2023-05-20T23:59:59.999+08:00")
    GROUP BY event_date, item_category, item_id
  ),
  webStats AS (
    SELECT
      event_date,
      item_category,
      item_id,
      COUNT(*) AS webVisit,
      COUNT(DISTINCT user_pseudo_id) AS webUser
    FROM `industrious-eye-145611.analytics_315002472.events_*`, UNNEST (items)
    WHERE event_name = 'view_item' AND stream_id = '3561738274'
      -- Optimize BQ table selection
      AND (_table_suffix between '20230510' and '20230520' OR _table_suffix between 'intraday_20230510' and 'intraday_20230520')
    GROUP BY event_date, item_category, item_id
  ),
  liffStats AS (
    WITH t AS (
      SELECT 
        event_date, item_category, item_id,
        struct(
          collected_traffic_source.manual_source as source,
          count(*) as visit, COUNT(DISTINCT user_pseudo_id) as user
        ) as liffObj
      FROM `industrious-eye-145611.analytics_315002472.events_*`, UNNEST (items)
      WHERE event_name = 'view_item'
        AND stream_id = '4697477273' -- LIFF stream
        -- Optimize BQ table selection
        AND (_table_suffix between '20230510' and '20230520' OR _table_suffix between 'intraday_20230510' and 'intraday_20230520')
      GROUP BY event_date, item_category, item_id, collected_traffic_source.manual_source
    )
    SELECT event_date, item_category, item_id, ARRAY_AGG(liffObj) AS liff FROM t
    GROUP BY event_date, item_category, item_id
  )
SELECT
  event_date AS dateStr,
  LOWER(item_category) AS type,
  item_id AS docId,
  STRUCT(lineUser, lineVisit, webUser, webVisit, liff) AS stat
FROM lineStats
FULL JOIN webStats USING (event_date, item_category, item_id)
FULL JOIN liffStats USING (event_date, item_category, item_id)

Notes on _table_suffix

It is possible that intraday tables contain 2 days.

Therefore when we do _table_suffix filter, we should handle both the case that has intraday_ and no intraday_ together.

Analysis of LINE bot number drop

2203-6-16~22

Event / category that drops:

• Article / Search
• UserInput / ArticleSearch
• UserInput / MessageType
• Article / Selected
• Reply / Selected
• Group / Leave
• Group / Join

Event / category that does not drop:

• Reply / Type
• ContextProxy / Forward
• Reply / Search
• Tutorial / Step
• Article / NoReply
• Article / Create
• UserInput / IsForwarded
• UserInput / ChatWithBot
• Cronjob / Send Notification

Obsolete: UserInput/Feedback-Vote (LIFF via Tag Manager)

2203-5-20~6-22

Event / category that drops:

• Article / Search (15% drop)
• UserInput / ArticleSearch (11% drop)
• UserInput / MessageType (1% drop)
• Article / Selected (6% drop)
• Reply / Selected (2% drop)
• Tutorial / Step (Just by 1, omitable)
• Article / NoReply (Just by 1, omitable)
• Group / Join
• Group / Leave

Event / category that does not drop

• Reply / Search
• Reply / Type (Actually BQ has +1 more)
• ContentProxy / Forward (Actually BQ has +1 more)
• UserInput / IsForwarded (BQ Has 1% more…)
• Article / Create (BQ has 1% more…)
• UserInput / ChatWithBot
• Cronjob / Send Notification

Segment by category & message source:

Dropping combinations

• UserInput / user (just by 0.04%)
• Article / group (by 33%)
• UserInput / group (by 49%)
• Tutorial / user (just by 1)
• Reply / group (by 50%)
• Group / group (by 62%)
• Article / room (by 62%)
• UserInput / room (by 72%)

Not dropping combinations

• Article / user (BQ has 0.04% more)
• Reply / user (BQ has +2 more)
• Cronjob / user

Focus on 6/10

Check filter alignment: if message source is user, the numbers are accurate

Group: UA > BQ

Even when category / action / label are the same, the count is still missing.

• For the same line of code, sometimes it works, but some times it’s not sent.
• Only happens for group chat.

Matching logs

One UserInput / ArticleSearch / ArticleFound should match to one console.debug('[GROUP]')

zh log stream: 127 records

zh, en log stream: 261 records (BQ result)

zh, en, ja log stream: 401 records (UA result)

[!TIP]

Conclusion

The number difference of UA and BQ is due to JA bots not updated to latest version.

• Users of EN & JA bot users almost do not use 1-1; groups are used instead
• We do remember to update EN bot to latest, but not JA bots. JA bots are not connected to BQ at all, hence the difference.