Guest User

Untitled

a guest
Dec 19th, 2018
341
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.45 KB | None | 0 0
  1. #!/bin/sh
  2.  
  3. 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
  4.  
  5. for i in {7161..15712}
  6. do
  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
  44. done
Add Comment
Please, Sign In to add comment