Sendgrid Event Model


#1

Hi all,

We have Sendgrid email system up and running and piping data into Redshift, and it’s working nicely.

We now want to report on both aggregate email numbers as well as campaign metrics, and contact history (by user & email address) for the raw Sendgrid tables.

Are there any pre-written event model scripts that we can run and expand on for our users? The SQL is beyond my personal capabilities to aggregate multiple emails across multiple tables, so any help is appreciated.

Thanks in advance.

Jordan


#2

If no one chimes in and you still want some help writing SQL for your email data models, let me know…I’d be happy to take this on as a consulting project at a reasonable rate


#3

Hi Travis - I work with Jordan and this may have potential. Could you please contact me : andrew@first10.co.uk


#4

So I managed to write some SQL that I think gives a line per campaign and aggregates sends, opens and clicks from the raw Sendgrid tables - See below:

WITH opens_and_clicks AS
(SELECT *
 FROM 
      (SELECT
         DISTINCT (category) AS campaign,
         count (email) AS opens
       FROM atomic.com_sendgrid_open_1
       GROUP BY 1) AS o
   JOIN (SELECT
           DISTINCT (category) AS campaigns,
           count(email) AS clicks
         FROM atomic.com_sendgrid_click_1
         GROUP BY 1) AS c
     ON campaigns = o.campaign)

SELECT
  DISTINCT (category),
  count(email) AS sends,
  opens,
  clicks
FROM atomic.com_sendgrid_delivered_1 AS s
  JOIN opens_and_clicks ON opens_and_clicks.campaign = category
GROUP BY category, opens, clicks

Go easy on me! I know it’s messy and inefficient!

Is this sort of on the right lines?

Jordan


#5

Hi Jordan,

Here’s some SQL code that you can use for your purposes, including filtering by month / week. It’s all based on unique sends / opens / clicks.

-- sends table

WITH sends AS (

  SELECT
    category AS campaign,
    date_trunc('month', root_tstamp)::DATE AS month,
    date_trunc('week', root_tstamp)::DATE AS week,
    count(DISTINCT email) AS unique_sends

  FROM atomic.com_sendgrid_delivered_1

  GROUP BY 1, 2, 3),

-- opens table

     opens AS (

  SELECT
    category AS campaign,
    date_trunc('month', root_tstamp)::DATE AS month,
    date_trunc('week', root_tstamp)::DATE AS week,
    count (DISTINCT email) AS unique_opens

  FROM atomic.com_sendgrid_open_1

  GROUP BY 1, 2, 3),

-- clicks table

     clicks AS (

  SELECT
    category AS campaign,
    date_trunc('month', root_tstamp)::DATE AS month,
    date_trunc('week', root_tstamp)::DATE AS week,
    count(DISTINCT email) AS unique_clicks

  FROM atomic.com_sendgrid_click_1

  GROUP BY 1, 2, 3),

-- combine the three tables

     sends_opens_and_clicks AS (

  SELECT
    s.campaign,
    s.month,
    s.week,
    s.unique_sends,
    o.unique_opens,
    c.unique_clicks

  FROM sends AS s

  LEFT JOIN opens AS o -- left join ensures no loss of data in case of no match
    ON s.campaign = o.campaign AND s.month = o.month AND s.week = o.week

  LEFT JOIN clicks AS c
    ON o.campaign = c.campaign AND o.month = c.month AND o.week = c.week

  GROUP BY 1, 2, 3, 4, 5, 6)

-- aggregate all

SELECT
  campaign,
  month, -- or week
  sum(unique_sends) AS unique_sends,
  sum(unique_opens) AS unique_opens,
  sum(unique_clicks) AS unique_clicks,
  sum(unique_opens)::FLOAT / sum(unique_sends)::FLOAT * 100||'%' AS open_rate, -- FLOAT removes rounding issues
  sum(unique_clicks)::FLOAT / sum(unique_sends)::FLOAT * 100||'%' AS ctr

FROM sends_opens_and_clicks

WHERE month = '2017-02-01' -- or, eg week = '2017-02-27'
-- filter by campaign: AND campaign = 'campaign'

GROUP BY 1, 2
ORDER BY 1, 2