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
DISTINCT (category) AS campaign,
count (email) AS opens
GROUP BY 1) AS o
DISTINCT (category) AS campaigns,
count(email) AS clicks
GROUP BY 1) AS c
ON campaigns = o.campaign)
count(email) AS sends,
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?