Messages

1-- core.gmail.messages
2-- One row per email with what every report needs: direction, sender, recipients, subject
3-- and position in its thread. The base for the other Gmail models, and useful on its own.
4--
5-- Grain: one row per message (drafts excluded).
6-- Depends on: staging.gmail.message, .message_label, .message_header, .profile
7--
8-- Direction comes from Gmail's SENT label: a message carrying it was sent from this
9-- mailbox, anything else was received. That holds for mail sent from other clients over
10-- the same account, and for "send as" aliases.
11--
12-- Addresses are pulled out of the From, To and Cc headers with a regular expression, so a
13-- display name like "Ada <ada@example.com>" becomes ada@example.com. `counterpart` is the
14-- other side of the conversation: the sender for received mail, the first To address for
15-- sent mail.
16--
17-- Timestamps are UTC. Convert before taking DATE() if your day should follow your office.
18
19WITH labels AS (
20    SELECT
21        message_id,
22        LOGICAL_OR(label_id = 'SENT')           AS is_sent,
23        LOGICAL_OR(label_id = 'DRAFT')          AS is_draft,
24        LOGICAL_OR(label_id = 'INBOX')          AS is_in_inbox,
25        LOGICAL_OR(label_id = 'UNREAD')         AS is_unread,
26        LOGICAL_OR(label_id = 'IMPORTANT')      AS is_important,
27        MAX(IF(STARTS_WITH(label_id, 'CATEGORY_'),
28                     LOWER(SUBSTR(label_id, 10)), NULL)) AS inbox_category
29    FROM {{staging.gmail.message_label}}
30    GROUP BY 1
31),
32
33headers AS (
34    SELECT
35        message_id,
36        MAX(IF(header_name = 'from', header_value, NULL))    AS from_header,
37        STRING_AGG(IF(header_name = 'to', header_value, NULL), ', ') AS to_header,
38        STRING_AGG(IF(header_name = 'cc', header_value, NULL), ', ') AS cc_header,
39        MAX(IF(header_name = 'subject', header_value, NULL)) AS subject
40    FROM {{staging.gmail.message_header}}