@Ejegg task we chatted about.

Hi all, wanted to check on the status of this. Any chance we can get this into the current or next sprint? :)
cc: @spatton @Ejegg @AKanji-WMF

Hi team - for some reason this didn't have our tag on it - bringing it into triage now so the team can look at it next time we meet.

@Eileenmcnaughton would it make sense to tackle this as part of our upcoming mapping/API work?

@bsisolak looking at the code & docs and your comments we do the things you say but our requests do not pass the optional campaign tags. How do they work? ie

say we pass in CAMPAIGN_ACTIVE - does that mean we ONLY get campaign active, or that we get all, including campaign_active?

CAMPAIGN_ACTIVE - Retrieves active automated messages (including programs messages).

CAMPAIGN_COMPLETED - Retrieves completed groups of automated messages.

CAMPAIGN_CANCELLED - Retrieves canceled groups of automated messages.

@AKanji-WMF this doesn't need to be tied to the outage - it might be as simple as this - https://gerrit.wikimedia.org/r/c/wikimedia/fundraising/crm/+/1076896 - that is an untried patch but I dug into the code enough to have a possible answer so I figured it was worth posting up for when this is picked up on.

You would want to loop through all three statues to get all CAMPAIGNS (aka, program/automation emails).

Change #1084933 had a related patch set uploaded (by Eileen; author: Eileen):

[wikimedia/fundraising/crm@master] Also fetch 'non-campaign' mailings from Acoustic

https://gerrit.wikimedia.org/r/1084933

@JMando we are close to deploying this - just so we are clear

1. these emails will be saved in the tables you mention exactly the same as the ones we are currently saving (there is no way to differentiate them from the ones we have been saving already)
2. if we do a back fill it will update the various statistics for all emails in the time period for which we back fill - in some cases these change subtlely in Acoustic over time for unknown reasons so we would be sucking in the updated stats from Acoustic - we don't have to do a back fill but we optionally can for up to a year or so I believe (the limit comes from Acoustic)

• is that OK?

This should be fine so long as none of the column names have changed. And I think let's do a backfill for the last year (or whatever the acoustic limit is).

CC @IAckerman-WMF

I don't have the context you all have, but will distinguishing between campaign and non-campaign emails be helpful for reporting? If so, do we want to also add a new boolean field accordingly (e.g., is_campaign_email)?

@JMando can hopefully answer that... as far as I know the civi tables are a store of information about the mailings but not the source for 'what does our mailing universe look like'

Change #1084933 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Also fetch 'non-campaign' mailings from Acoustic

https://gerrit.wikimedia.org/r/1084933

@JMando I just deployed this - we should have the last 16 weeks or so including campaign mails now-ish - I'm just not sure of an example one to check to see if it worked

Also ouch - is that link to a google doc your current process?

OK - we have been trying to do QA on 74498193

We are not seeing the same sorts of numbers for sent on this as Natalie has in the UI & I think the issue lies in this comment by @bsisolak

"The trick is you get a new ReportId for each day, so you might want to store these somewhere else so you can pivot the data by day"

I guess these are maybe materially different to the other mailings we have been getting in how the data is handled - hoping to ask Brian tomorrow

Just updating this to note I asked Brian about this and here is what is going on

• normal email sends have a maiing ID & a report ID - we request the data & we get it, we record it
• these 'non campaign' emails have a mailing ID & many report IDs - currently we request the data and get only a single report ID, which we record - but the data we need is distributed across many.

We probably need a new table to store the individual report ID information & then we can total them up to store in the main table. I haven't looked to see how much code re-writing is involved

Change #1092408 had a related patch set uploaded (by Eileen; author: Eileen):

[wikimedia/fundraising/crm@master] Remove legacy entityType declaration

https://gerrit.wikimedia.org/r/1092408

Change #1092955 had a related patch set uploaded (by Eileen; author: Eileen):

[wikimedia/fundraising/crm@master] Add report_id field to civicrm_mailing_stat

https://gerrit.wikimedia.org/r/1092955

Change #1092962 had a related patch set uploaded (by Eileen; author: Eileen):

[wikimedia/fundraising/crm@master] Omnimail-silverpop update

https://gerrit.wikimedia.org/r/1092962

Change #1092963 had a related patch set uploaded (by Eileen; author: Eileen):

[wikimedia/fundraising/crm/vendor@master] Update Omnimail-silverpop

https://gerrit.wikimedia.org/r/1092963

@JMando I have deployed some code updates on staging (ie the dev_civicrm database) - if you can QA there we can push live if the data is good. There are 2 new fields on the civicrm_mailing_stats table

is_multiple_report (bool)
report_id

My expectation is that after I have pulled the data from Acoustic the is_multiple_report will be 1 for 'non campaign' emails - which will ALSO have multiple report IDs where as those with is_multiple_report = 0 will have only one and be 'campaign emails' - However, expecting things with Acoustic has gotten me in trouble before

If the above gives us sane data then group by is your friend

OK - looks like it is OK

select mailing_id, is_multiple_report, count(*) as c, sum(recipients) FROM civicrm_mailing_stats GROUP BY mailing_id HAVIN
G c > 1 LIMIT 10;
+------------+--------------------+----+-----------------+

+------------+--------------------+----+-----------------+
10 rows in set (0.286 sec)

MariaDB [dev_civicrm]> select mailing_id, is_multiple_report, count(*) as c, sum(recipients) FROM civicrm_mailing_stats WHERE is_multiple_report = 0 GROUP BY mailing_id HAVING c > 1 LIMIT 10;

0 rows

Hey there, have not had a chance to look yet. Will try to this week.

So the non campaign sends will have mailing_id's with multiple entries? Right now I am not aggregating the data when i pull from civicrm_mailing_stats. So I will need some time to re write some of the sql for the mailing_stats cube (probably just adding a group by to the mailing portion as you said).

@JMando yep that is correct - it seems like the RML ones etc have a report per day whereas the 'campaign' ones only have a single report - so prior to this we didn't save the report ID

Also @JMando From my understanding / investigations your existing queries would not break when we roll this out as the 'campaign emails' don't need the group by to be correct & the 'non campaign emails' are already broken

Change #1092963 merged by Eileen:

[wikimedia/fundraising/crm/vendor@master] Update Omnimail-silverpop

https://gerrit.wikimedia.org/r/1092963

Change #1092962 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Omnimail-silverpop update

https://gerrit.wikimedia.org/r/1092962

Change #1094565 had a related patch set uploaded (by Eileen; author: Eileen):

[wikimedia/fundraising/crm@master] Civix Update civicrm-extendedmailingstats to Entity 2

https://gerrit.wikimedia.org/r/1094565

Change #1094566 had a related patch set uploaded (by Eileen; author: Eileen):

[wikimedia/fundraising/crm@master] Remove commented out boilerplate

https://gerrit.wikimedia.org/r/1094566

Change #1094568 had a related patch set uploaded (by Eileen; author: Eileen):

[wikimedia/fundraising/crm@master] Port schema support for bigint

https://gerrit.wikimedia.org/r/1094568

Change #1094571 had a related patch set uploaded (by Eileen; author: Eileen):

[wikimedia/fundraising/crm@master] Whitespace fixes

https://gerrit.wikimedia.org/r/1094571

Change #1094572 had a related patch set uploaded (by Eileen; author: Eileen):

[wikimedia/fundraising/crm@master] Superficial layout changes

https://gerrit.wikimedia.org/r/1094572

Change #1094573 had a related patch set uploaded (by Eileen; author: Eileen):

[wikimedia/fundraising/crm@master] Remove Omnihell test setup

https://gerrit.wikimedia.org/r/1094573

Change #1094574 had a related patch set uploaded (by Eileen; author: Eileen):

[wikimedia/fundraising/crm@master] Remove omnihell call

https://gerrit.wikimedia.org/r/1094574

Change #1094575 had a related patch set uploaded (by Eileen; author: Eileen):

[wikimedia/fundraising/crm@master] Test data fix

https://gerrit.wikimedia.org/r/1094575

Change #1094568 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Port schema support for bigint

https://gerrit.wikimedia.org/r/1094568

Change #1094566 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Remove commented out boilerplate

https://gerrit.wikimedia.org/r/1094566

Change #1094565 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Civix Update civicrm-extendedmailingstats to Entity 2

https://gerrit.wikimedia.org/r/1094565

Change #1094571 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Whitespace fixes

https://gerrit.wikimedia.org/r/1094571

Change #1094572 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Superficial layout changes

https://gerrit.wikimedia.org/r/1094572

Change #1092408 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Remove legacy entityType declaration

https://gerrit.wikimedia.org/r/1092408

Change #1094573 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Remove Omnihell test setup

https://gerrit.wikimedia.org/r/1094573

Change #1094575 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Test data fix

https://gerrit.wikimedia.org/r/1094575

Change #1094574 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Remove omnihell call

https://gerrit.wikimedia.org/r/1094574

Change #1092955 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Add report_id field to civicrm_mailing_stat

https://gerrit.wikimedia.org/r/1092955

@JMando we are looking at pushing the change from staging to prod tomorrow - I don't think it will mess with you even if you don't get your sql updated as the non-RML rows will still only have 1 row & the others were not really working anyway

@JMando per comments - I pushed out this change to get multiple rows in the mailing_stats table for RML emails - I have not done any backfill at this point