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:
- bao_posts: Like any other wordpress post, the information of the
email that is sent was there. (When the post_type = ‘newsletter’) - 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!
2
Answers
I finally got it to work using only the new tables that Mailster created (it seems that finally they did move all the info to the new tables with the update) and with 4 LEFT JOINS.
I leave the code in case someone else finds it useful:
P.S: As I expected, Mailster's support has not helped at all :'(
I suggest that you use UNION ALL to join all the tables in a CTE.You can then use this in your query. I have modified the name because we cannot have to records with the same name.
db<>fiddle here