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!

CodePudding user response：

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.

> create table if not exists bao_mailster_action_sent
( campaign_id int,count int);
create table if not exists bao_mailster_action_opens
( campaign_id int,count int);
create table if not exists bao_mailster_action_clicks
( campaign_id int,count int);
create table if not exists bao_mailster_action_unsubscribes
( campaign_id int,count int);
CREATE TABLE if not exists bao_posts(
        post_modified date,
        ID int,
        post_title varchar(50) );


insert into bao_mailster_action_sent values
(1,88),(2,4),(4,6);
insert into bao_mailster_action_opens values
(2,4),(3,5),(4,10);
insert into bao_mailster_action_clicks values
(1,3),(2,3),(4,6);
insert into bao_mailster_action_unsubscribes values
(1,4),(3,5),(4,5);

INSERT INTO bao_posts values
( '2021-03-01',1,'first post'),
( '2021-06-01',2,'second opion'),
( '2021-09-01',3,'third way'),
( '2021-12-01',4,'last post');

WITH bao_mailster_actionsent AS
( SELECT campaign_id,count, 1 type FROM
bao_mailster_action_sent
UNION ALL
SELECT  campaign_id,count,2 FROM
bao_mailster_action_opens
UNION ALL
SELECT  campaign_id,count,3 FROM
bao_mailster_action_clicks
UNION ALL
SELECT  campaign_id,count,4 FROM
bao_mailster_action_unsubscribes)
SELECT bao_mailster_actionsent.campaign_id,
       COUNT(bao_mailster_actionsent.count) AS TotalCount,
       SUM(bao_mailster_actionsent.count) AS TotalNumber,
       'type'
FROM bao_mailster_actionsent
GROUP BY bao_mailster_actionsent.campaign_id,'type' ;

 WITH baoMailsterAction  AS
( SELECT campaign_id,count, 1 type FROM
bao_mailster_action_sent
UNION ALL
SELECT  campaign_id,count,2 FROM
bao_mailster_action_opens
UNION ALL
SELECT  campaign_id,count,3 FROM
bao_mailster_action_clicks
UNION ALL
SELECT  campaign_id,count,4 FROM
bao_mailster_action_unsubscribes)

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

campaign_id | TotalCount | TotalNumber | type
----------: | ---------: | ----------: | ---:
          1 |          1 |          88 |    1
          2 |          1 |           4 |    1
          4 |          1 |           6 |    1
          2 |          1 |           4 |    2
          3 |          1 |           5 |    2
          4 |          1 |          10 |    2
          1 |          1 |           3 |    3
          2 |          1 |           3 |    3
          4 |          1 |           6 |    3
          1 |          1 |           4 |    4
          3 |          1 |           5 |    4
          4 |          1 |           5 |    4

post_modified | ID | post_title   | Number_People_Reached | Opens | Clicks | Unsubs
:------------ | -: | :----------- | --------------------: | ----: | -----: | -----:
2021-03-01    |  1 | first post   |                     1 |     0 |      1 |      1
2021-06-01    |  2 | second opion |                     1 |     1 |      1 |      0
2021-09-01    |  3 | third way    |                     0 |     1 |      0 |      1
2021-12-01    |  4 | last post    |                     1 |     1 |      1 |      1

db<>fiddle here

CodePudding user response：

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:

SELECT P.post_modified, 
        P.ID, 
        P.post_title, 
        IFNULL(S.count,0) as 'Total',
        IFNULL(O.count,0) as 'Aperturas',
        IFNULL(C.count,0) as 'Clicks', 
        IFNULL(U.count,0) as 'Bajas' from bao_posts as P
LEFT JOIN (select campaign_id, count(DISTINCT subscriber_id) as count from bao_mailster_action_clicks group by campaign_id) as C ON C.campaign_id = P.ID
LEFT JOIN (select campaign_id, count(DISTINCT subscriber_id) as count from bao_mailster_action_opens group by campaign_id) as O ON O.campaign_id = P.ID
LEFT JOIN (select campaign_id, count(DISTINCT subscriber_id) as count from bao_mailster_action_sent group by campaign_id) as S ON S.campaign_id = P.ID
LEFT JOIN (select campaign_id, count(DISTINCT subscriber_id) as count from bao_mailster_action_unsubs group by campaign_id) as U ON U.campaign_id = P.ID

WHERE P.post_type = 'newsletter'
ORDER BY P.post_modified ASC ;

P.S: As I expected, Mailster's support has not helped at all :'(