Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- #!/bin/sh
- echo "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
- for i in {7161..15712}
- do
- echo $i
- actionkit -N -e "
- SELECT
- m.id,
- DATE(m.started_at),
- COUNT(DISTINCT s.user_id) AS sends,
- COUNT(DISTINCT o.user_id) AS opens,
- COUNT(DISTINCT IF(u.email LIKE '%@gmail.com', u.id, NULL)) AS gmail_sends,
- COUNT(DISTINCT IF(u.email LIKE '%@gmail.com', o.user_id, NULL)) AS gmail_opens,
- COUNT(DISTINCT IF(u.email LIKE '%@yahoo.com', u.id, NULL)) AS yahoo_sends,
- COUNT(DISTINCT IF(u.email LIKE '%@yahoo.com', o.user_id, NULL)) AS yahoo_opens,
- COUNT(DISTINCT IF(u.email LIKE '%@hotmail.com', u.id, NULL)) AS hotmail_sends,
- COUNT(DISTINCT IF(u.email LIKE '%@hotmail.com', o.user_id, NULL)) AS hotmail_opens,
- COUNT(DISTINCT IF(u.email LIKE '%@aol.com', u.id, NULL)) AS aol_sends,
- COUNT(DISTINCT IF(u.email LIKE '%@aol.com', o.user_id, NULL)) AS aol_opens,
- COUNT(DISTINCT IF(u.email LIKE '%@comcast.net', u.id, NULL)) AS comcast_sends,
- COUNT(DISTINCT IF(u.email LIKE '%@comcast.net', o.user_id, NULL)) AS comcast_opens,
- COUNT(DISTINCT IF(u.email LIKE '%@msn.com', u.id, NULL)) AS msn_sends,
- COUNT(DISTINCT IF(u.email LIKE '%@msn.com', o.user_id, NULL)) AS msn_opens,
- COUNT(DISTINCT IF(u.email LIKE '%@sbcglobal.net', u.id, NULL)) AS sbcglobal_sends,
- COUNT(DISTINCT IF(u.email LIKE '%@sbcglobal.net', o.user_id, NULL)) AS sbcglobal_opens,
- COUNT(DISTINCT IF(u.email LIKE '%@att.net', u.id, NULL)) AS att_sends,
- COUNT(DISTINCT IF(u.email LIKE '%@att.net', o.user_id, NULL)) AS att_opens,
- COUNT(DISTINCT IF(u.email LIKE '%@verizon.net', u.id, NULL)) AS verizon_sends,
- COUNT(DISTINCT IF(u.email LIKE '%@verizon.net', o.user_id, NULL)) AS verizon_opens,
- COUNT(DISTINCT IF(u.email LIKE '%@earthlink.net', u.id, NULL)) AS earthlink_sends,
- COUNT(DISTINCT IF(u.email LIKE '%@earthlink.net', o.user_id, NULL)) AS earthlink_opens
- FROM core_mailing m
- JOIN core_usermailing s ON m.id = s.mailing_id
- JOIN core_user u ON u.id = s.user_id
- LEFT JOIN core_open o ON o.mailing_id = m.id AND o.user_id = u.id
- WHERE
- m.started_at > '2016-01-01' AND
- m.id = $i
- HAVING sends > 0
- " | csvcut -t >> mailings.csv
- done
Add Comment
Please, Sign In to add comment