cAFdG0D9

1#!/bin/sh
2
3echo "id,date,sends,opens,gmail_sends,gmail_opens,yahoo_sends,yahoo_opens,hotmail_sends,hotmail_opens,aol_sends,aol_opens,comcast_sends,comcast_opens,msn_sends,msn_opens,sbcglobal_sends,sbcglobal_opens,att_sends,att_opens,verizon_sends,verizon_opens,earthlink_sends,earthlink_opens" > mailings.csv
4
5for i in {7161..15712}
6do
7  echo $i
8
9  actionkit -N -e "
10    SELECT
11      m.id,
12      DATE(m.started_at),
13      COUNT(DISTINCT s.user_id) AS sends,
14      COUNT(DISTINCT o.user_id) AS opens,
15      COUNT(DISTINCT IF(u.email LIKE '%@gmail.com', u.id, NULL)) AS gmail_sends,
16      COUNT(DISTINCT IF(u.email LIKE '%@gmail.com', o.user_id, NULL)) AS gmail_opens,
17      COUNT(DISTINCT IF(u.email LIKE '%@yahoo.com', u.id, NULL)) AS yahoo_sends,
18      COUNT(DISTINCT IF(u.email LIKE '%@yahoo.com', o.user_id, NULL)) AS yahoo_opens,
19      COUNT(DISTINCT IF(u.email LIKE '%@hotmail.com', u.id, NULL)) AS hotmail_sends,
20      COUNT(DISTINCT IF(u.email LIKE '%@hotmail.com', o.user_id, NULL)) AS hotmail_opens,
21      COUNT(DISTINCT IF(u.email LIKE '%@aol.com', u.id, NULL)) AS aol_sends,
22      COUNT(DISTINCT IF(u.email LIKE '%@aol.com', o.user_id, NULL)) AS aol_opens,
23      COUNT(DISTINCT IF(u.email LIKE '%@comcast.net', u.id, NULL)) AS comcast_sends,
24      COUNT(DISTINCT IF(u.email LIKE '%@comcast.net', o.user_id, NULL)) AS comcast_opens,
25      COUNT(DISTINCT IF(u.email LIKE '%@msn.com', u.id, NULL)) AS msn_sends,
26      COUNT(DISTINCT IF(u.email LIKE '%@msn.com', o.user_id, NULL)) AS msn_opens,
27      COUNT(DISTINCT IF(u.email LIKE '%@sbcglobal.net', u.id, NULL)) AS sbcglobal_sends,
28      COUNT(DISTINCT IF(u.email LIKE '%@sbcglobal.net', o.user_id, NULL)) AS sbcglobal_opens,
29      COUNT(DISTINCT IF(u.email LIKE '%@att.net', u.id, NULL)) AS att_sends,
30      COUNT(DISTINCT IF(u.email LIKE '%@att.net', o.user_id, NULL)) AS att_opens,
31      COUNT(DISTINCT IF(u.email LIKE '%@verizon.net', u.id, NULL)) AS verizon_sends,
32      COUNT(DISTINCT IF(u.email LIKE '%@verizon.net', o.user_id, NULL)) AS verizon_opens,
33      COUNT(DISTINCT IF(u.email LIKE '%@earthlink.net', u.id, NULL)) AS earthlink_sends,
34      COUNT(DISTINCT IF(u.email LIKE '%@earthlink.net', o.user_id, NULL)) AS earthlink_opens
35    FROM core_mailing m
36    JOIN core_usermailing s ON m.id = s.mailing_id
37    JOIN core_user u ON u.id = s.user_id
38    LEFT JOIN core_open o ON o.mailing_id = m.id AND o.user_id = u.id
39    WHERE
40      m.started_at > '2016-01-01' AND
41      m.id = $i
42    HAVING sends > 0
43  " | csvcut -t >> mailings.csv
44done