After the last update of Mailster (email marketing plugin for wordpress), they have changed the way they store the information about opens, clicks, unsubscribes…

Until now, everything was stored in two databases:

1. bao_posts: Like any other wordpress post, the information of the
   email that is sent was there. (When the post_type=”newsletter”)
2. bao_mailster_actions: This is where the user’s actions with the
   email were stored. 1 when it was sent to a person, 2 when they
   opened it, 3 when they clicked on it and 4 when they unsubscribed.

And with this query, I could get a table with all the emails and the information of their openings, clicks, unsubscribed…

SELECT  bao_posts.post_modified, 
        bao_posts.ID, 
        bao_posts.post_title, 
        COUNT(CASE WHEN bao_mailster_actions.type = 1 then 1 ELSE NULL END) AS Number_People_Reached, 
        COUNT(CASE WHEN bao_mailster_actions.type = 2 then 1 ELSE NULL END) AS Opens,
        COUNT(CASE WHEN bao_mailster_actions.type = 3 then 1 ELSE NULL END) AS Clicks, 
        COUNT(CASE WHEN bao_mailster_actions.type = 4 then 1 ELSE NULL END) AS Unsubs
FROM bao_posts
LEFT JOIN bao_mailster_actions ON bao_mailster_actions.campaign_id = bao_posts.ID
WHERE bao_posts.post_type="newsletter"
GROUP BY bao_posts.ID ;

*Expected result of this query at the end of the post.

Now the problem is that this setting is kept for emails before the update, but it has changed for new ones and now bao_mailster_actions is separated into:

• bao_mailster_action_sent
• bao_mailster_action_opens
• bao_mailster_action_clicks
• bao_mailster_action_unsubscribes

I know how to get the count of each of these tables like this:

SELECT bao_mailster_action_sent.campaign_id, 
       COUNT(bao_mailster_action_sent.count) AS Number_People_Reached
FROM bao_mailster_action_sent
GROUP BY bao_mailster_action_sent.campaign_id;

To get:

(And so on with each of these 4 new tables).

So what I would like to do would be to join these 4 new queries to the original one. I’ve been trying to combine different JOINs, but I don’t quite understand how to do it.

*Bearing in mind that if an email ID matches in both, I would need it to add up their clicks, opens (or whatever).

The expected outcome would be something like this (the same as the first query but with the aggregate data):

Thanks in advance!