Mailchimp

Sample Queries

1. How to get all recipients for multi-variate campaigns?

   WITH variate_campaigns AS (
       SELECT
           id,
           winning_campaign_id
       FROM campaign
       WHERE type = 'variate'
   ),
   winning_campaign_recipients AS (
       SELECT
           v.id AS variate_campaign_id,
           *
       FROM campaign_recipient r
       JOIN variate_campaigns v
       ON r.campaign_id = v.winning_campaign_id
   )
   SELECT
       v.id AS variate_campaign_id,
       *
   FROM campaign_recipient r
   JOIN variate_campaigns v
   ON r.campaign_id = v.id
   UNION SELECT * FROM winning_campaign_recipients;
   

2. How to get the average click rate for list members?

   WITH sends AS (
     SELECT
       member_id,
       list_id,
       COUNT(campaign_id) AS sends
     FROM (
       SELECT DISTINCT(member_id, list_id, campaign_id) FROM campaign_recipient
     )
     GROUP BY member_id
   ),
   clicks AS (
       SELECT
       member_id,
       list_id,
       COUNT(campaign_id) AS clicks
     FROM (
       SELECT DISTINCT(member_id, list_id, campaign_id)
       FROM campaign_recipient_activity
       WHERE action = "click"
     )
     GROUP BY member_id
   )
   SELECT
     member_id,
     list_id,
     COUNT(COALESCE(clicks, 0)) / COUNT(sends) AS avg_click_rate
   FROM sends
   LEFT JOIN clicks
   ON sends.member_id = clicks.member_id;