Scrape deliverability information out of Cuttlefish.

[jamezpolley]

• [ ] Ideally, get a list of emails with a datetime of the most recent successful delivery or the most recent hard bounce.

• [ ] Import this into CampaignMonitor, adding the hard bounce or successful delivery dates as custom fields.

• [ ] Create segments based on successfully bigger pools - say, successful deliveries in the last day, then in the last week, then in the last month - which we can use for progressively larger mailouts to test the list.

[mlandauer]

This sounds like a very good plan. @jamezpolley let me know if at any stage you need any help interpreting any weirdness in cuttlefish.

[jamezpolley]

it looks like the query I used for this is:

SELECT addresses.text, deliveries.created_at, deliveries.status
FROM deliveries INNER JOIN addresses
       ON deliveries.address_id = addresses.id
WHERE deliveries.id IN (SELECT MAX("deliveries"."id")
                        FROM "deliveries" 
                        WHERE "deliveries"."app_id" = 3
                           AND "deliveries"."status" IN ('delivered','hard_bounce')
                        GROUP BY "deliveries"."address_id");

[mlandauer]

@jamezpolley thanks for this! Did we end up using the information for the delivery time last year? Or did we just use it to populate the campaign monitor suppression list?

Also was there a reason that you didn't just export the cuttlefish blocklist to get the list of email addresses that had permanent delivery problems?