Message volume daily

1-- core.sprout_social.message_volume_daily
2-- Daily message volume per profile and message type: what came in, what went out,
3-- how much of it went out through Sprout, and how much became a case. The basis of
4-- Sprout's inbox and engagement volume reporting.
5--
6-- Grain: date_day (UTC) x customer_profile_id x post_type.
7-- Depends on: staging.sprout_social.message, .profile
8--
9-- Counts only. The connector does not sync Sprout's analytics endpoints, so there
10-- are no impressions, reach, engagements or follower counts here.
11
12WITH daily AS (
13    SELECT
14        DATE(created_at)                                  AS date_day,
15        customer_profile_id,
16        network,
17        post_category,
18        post_type,
19        COUNT(*)                                          AS messages,
20        COUNTIF(NOT is_sent)                              AS received_messages,
21        COUNTIF(is_sent)                                  AS sent_messages,
22        COUNTIF(sent_by_user_id IS NOT NULL)              AS sent_from_sprout,
23        COUNTIF(case_id IS NOT NULL)                      AS messages_in_cases,
24        COUNT(DISTINCT IF(NOT is_sent, author_guid, NULL)) AS unique_authors
25    FROM {{staging.sprout_social.message}}
26    GROUP BY 1, 2, 3, 4, 5
27)
28
29SELECT
30    d.date_day,
31    p.customer_id,
32    d.customer_profile_id,
33    p.profile_name,
34    COALESCE(d.network, p.network_type) AS network,
35    d.post_category,
36    d.post_type,
37    d.messages,
38    d.received_messages,
39    d.sent_messages,
40    d.sent_from_sprout,