plugins – JOIN and SUM different statement results (Mailster Database)

Question

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:

campaign_id Number_People_Reached
9785 300
9786 305

(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):

post_modified ID post_title Number_People_Reached Opens Clicks Unsubs
2021-04-29 13:13:03 9785 Prueba email 300 102 30 1
2021-04-30 15:12:01 9786 Segundo email 305 97 56 0

Thanks in advance!

0
JPX 6 months 2022-02-24T10:55:58-05:00 0 Answers 0 views 0

Leave an answer

Browse
Browse