Message errors daily

1-- core.twilio.message_errors_daily
2-- Failed and undelivered messages by Twilio error code: the Errors view in Messaging
3-- Insights. The first place to look when delivery rate drops.
4--
5-- Grain: one row per day (UTC) x account x messaging service x error code.
6-- Depends on: staging.twilio.message, .messaging_service
7--
8-- Common codes: 30003 unreachable handset, 30005 unknown number, 30006 landline or
9-- unreachable carrier, 30007 carrier filtering (often spam), 30008 unknown error,
10-- 21610 recipient replied STOP. The full list is in Twilio's error dictionary, linked in
11-- the README. `example_error_message` is one of the messages Twilio returned for that
12-- code on that day.
13--
14-- `messaging_service_name` is the service's current name, looked up after aggregation so
15-- the join cannot change any count. Messages sent straight from a number have no service.
16
17WITH daily AS (
18    SELECT
19        DATE(COALESCE(sent_at, created_at))                         AS date,
20        account_sid,
21        messaging_service_sid,
22        error_code,
23        MAX(error_message)                                          AS example_error_message,
24        COUNT(*)                                                    AS messages,
25        COUNTIF(status = 'failed')                                  AS failed,
26        COUNTIF(status = 'undelivered')                             AS undelivered,
27        COUNT(DISTINCT to_number)                                   AS distinct_recipients
28    FROM {{staging.twilio.message}}
29    WHERE error_code IS NOT NULL
30      AND error_code != 0
31    GROUP BY 1, 2, 3, 4
32)
33
34SELECT
35    d.*,
36    ms.messaging_service_name
37FROM daily d
38LEFT JOIN {{staging.twilio.messaging_service}} ms
39       ON ms.messaging_service_sid = d.messaging_service_sid