I'm not sure what the latter means - but if you are wanting us to filter out any Double Opens then we need to have another process that goes through & cleans them up or something. We can't 'clean on save' at the volumes we are dealing with. We always understood our goal was to get the silverpop data & store it somewhere it could be accessed rather than to wrangle it

Hi Eileen - I completely agree. I would prefer the data coming in to be as raw as possible so that we're future-proofed against any changes we want to make in terms of how we want to analyze the data. So if the donor opens the email multiple times, multiple opens are great to see. That second ask is more to just confirm that (which I think we're both saying) that "what the user does, the data reflects" without any wrangling, cleaning, or summarizing at this stage.

Change 550454 had a related patch set uploaded (by Eileen; owner: Eileen):
[wikimedia/fundraising/crm@master] Revisit Omnimail timestamp misery.

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

Change 550454 merged by jenkins-bot:
[wikimedia/fundraising/crm@master] Revisit Omnimail timestamp misery.

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

Just an update that I have found a problem as you identified & have been working to fill some of the gaps. I wouldn't plan to look more at this data this week if I were you as it is a moving target right now

Change 552119 had a related patch set uploaded (by Eileen; owner: Eileen):
[wikimedia/fundraising/crm@master] Address timeout fail mail on repair job

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

Sorry for the delay in responding. Because I was able to rectify most of the missing data using a slow process this was a bit of a moving target but here is the current situation

1. A number of civicrm_mailing_provider_data rows were missing from certain times of day - I have backfilled these going back to 450 days ago from Silverpop data.
2. There were a couple of emails with missing rows older than this which I couldn’t backfill
3. There were a couple of mailings I could not retrieve any data for - I think they might be deleted
4. I’m still confirming whether the underlying issue is fixed. I thought the fix I put up at the start of this was a ‘final fix’ but as of today I note the mailing data for just-sent mailings has not turned up yet
5. Some of the re-population resulted in duplicate rows due to timezone issues. I didn’t get to the bottom of why drupal is not setting the php time correctly but we have deployed a patch to force the php time and mysql time to GMT. I anticipated this would also fix the underlying issue of missed rows but still confirming.
6. We have a repair job to fix those rows that are duplicated - I expect this to complete in the next half day
7. There remains a much less significant discrepancy for some of the mailings (e.g for sp60428551 the discrepancy is 180 from 37045 sent). Once the repair has finished I will post a full table of all the mailings & their counts into this phab & also try to re-retrieve & analyse a specific mailing. My best guess at the moment is that Silverpop suppresses some contacts from what is retrieved. Some appear to be system contacts but possibly others are contacts that were since subject to GDPR or put on the suppression list.
8. I’ll update this with a table tomorrow am once the repair is complete

Thanks so much for all the digging, Eileen! Looking forward to seeing how
things look tomorrow. I'll be happy to research the mailings that you think
may be deleted or do any other QA on the ESP side. Let me know how I can
help!

Re: #7, I can't find a good reason why there would be a discrepancy of 180
for that mailing--at least not by looking at the email data. We show 36326
received the mailing (IBM's metric for sent minus bounced). 180 isn't a
very concerning number, but I was hoping for an obvious reason anyway :)

This is great! Thank you @Eileenmcnaughton! And thanks for digging on the ESP side, @CCogdill_WMF.

Since it sounds like a manual backfill fix for now, would you consider mailing data up to 450 days up to date in terms of ongoing activity like opens?