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