Board activity

1-- core.monday.board_activity
2-- Daily activity per board, user and event type, from the monday activity log.
3-- Grain: day (UTC) x board x user x event.
4--
5-- `event` is monday's own event name: create_pulse (item created),
6-- update_column_value, move_pulse_into_group, update_name, delete_pulse and so on.
7-- `items_touched` counts distinct items (monday calls them pulses) the events
8-- refer to; it is distinct within a row only, so do not sum it across rows.
9--
10-- Only covers the activity log retention window of your monday plan.
11
12WITH logs AS (
13    SELECT
14        DATE(created_at)                                  AS activity_date,
15        board_id,
16        user_id,
17        event,
18        JSON_VALUE(data_json, '$.pulse_id')               AS item_id
19    FROM {{staging.monday.activity_log}}
20)
21
22SELECT
23    l.activity_date,
24    l.board_id,
25    b.board_name,
26    l.user_id,
27    u.user_name,
28    l.event,
29    COUNT(*)                       AS events,
30    COUNT(DISTINCT l.item_id)      AS items_touched
31FROM logs l
32LEFT JOIN {{staging.monday.board}} b ON b.board_id = l.board_id
33LEFT JOIN {{staging.monday.user}} u ON u.user_id = l.user_id
34GROUP BY 1, 2, 3, 4, 5, 6