Email volume daily

1-- core.gmail.email_volume_daily
2-- How much mail the mailbox sends and receives per day, and with how many people.
3--
4-- Grain: one row per day (UTC) x direction (sent, received).
5-- Depends on: core.gmail.messages
6--
7-- `threads` counts distinct conversations with activity that day; a thread active on
8-- three days counts on each. `counterparts` counts distinct addresses on the other side,
9-- per day, so it cannot be summed into unique people over a week.
10-- Received mail includes newsletters and notifications. Filter on `inbox_category` in
11-- core.gmail.messages (primary, promotions, social, updates, forums) if you only want
12-- mail from people.
13
14SELECT
15    date,
16    direction,
17    COUNT(*)                                AS messages,
18    COUNT(DISTINCT thread_id)               AS threads,
19    COUNT(DISTINCT counterpart)             AS counterparts,
20    COUNTIF(position_in_thread = 1)         AS new_threads,
21    COUNTIF(direction = 'received' AND inbox_category = 'primary') AS received_primary,
22    SUM(size_bytes)                         AS total_bytes
23FROM {{core.gmail.messages}}
24GROUP BY 1, 2