Page MenuHomePhabricator

Re-run omnirecipient repair to catch the few missed ones
Closed, ResolvedPublic1 Story Points

Description

Per https://phabricator.wikimedia.org/T200240#4946379 I want to track re-running the omnirecipient repair job until the remaining rows are removed

For now I propose to run the job again with a 5 hour gap & then do some checks - & run again as appropriate

In preparation I reset the drupal param

drush vset civicrm_repair_omnirecipient_last_processed_timestamp "2017-02-16 00:00:00"

I have put up a gerrit for changing to 300 mins BUT it is failing on the time change part & to fix that I need to double check the time it should be changed to by retrieving a relevant record from spop & doing a quick check (although if it has already aged out of spop I will just adjust the test to match & it won't matter that much as there is no risk of us doing a re-retrieve & getting new dupes - I suspect that might be the case)

Event Timeline

Eileenmcnaughton triaged this task as Normal priority.Feb 12 2019, 6:14 AM
Eileenmcnaughton created this task.
Eileenmcnaughton updated the task description. (Show Details)
Eileenmcnaughton updated the task description. (Show Details)

Change 489969 had a related patch set uploaded (by Eileen; owner: Eileen):
[wikimedia/fundraising/crm@master] Adjust different on omnirecipient repair - then I can kick it off & catch a few more

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

Eileenmcnaughton updated the task description. (Show Details)

Change 489969 merged by jenkins-bot:
[wikimedia/fundraising/crm@master] Adjust different on omnirecipient repair - then I can kick it off & catch a few more

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

Change 492941 had a related patch set uploaded (by Eileen; owner: Eileen):
[wikimedia/fundraising/crm@master] Repair omnirecipient with 14 hour gap

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

Change 492941 merged by jenkins-bot:
[wikimedia/fundraising/crm@master] Repair omnirecipient with 14 hour gap

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

Change 493134 had a related patch set uploaded (by Eileen; owner: Eileen):
[wikimedia/fundraising/crm@deployment] (cherry-picked from master) Repair omnirecipient with 14 hour gap

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

Change 493134 abandoned by Eileen:
(cherry-picked from master) Repair omnirecipient with 14 hour gap

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

Eileenmcnaughton closed this task as Resolved.Mar 4 2019, 9:54 PM
Eileenmcnaughton reopened this task as Open.Mar 4 2019, 10:47 PM

Dang re-opening this - I realise I filtered on 'Sent' - perhaps for priority reasons but

SELECT      mp.contact_identifier, mp.recipient_action_datetime, mp.event_type,mp.contact_id,      mp2.contact_identifier as ci2, mp2.recipient_action_datetime as date_time2, mp2.event_type as event_type2,         TIMESTAMPDIFF(MINUTE,mp.recipient_action_datetime , mp2.recipient_action_datetime) as time_diff                  FROM civicrm_mailing_provider_data mp               INNER JOIN civicrm_mailing_provider_data mp2           ON mp.contact_identifier = mp2.contact_identifier              AND mp.mailing_identifier = mp2.mailing_identifier             AND mp.event_type = mp2.event_type  AND mp.recipient_action_datetime < mp2.recipient_action_datetime                     WHERE mp2.`contact_identifier` IS NOT NULL             AND TIMESTAMPDIFF(MINUTE,mp.recipient_action_datetime , mp2.recipient_action_datetime) IN (60,120,180,240,300,360,420,480,540,600,660,720,780,840,900,960,1020,1080,1140,1200,1260,1320,1380,1440)             AND mp.event_type = mp2.event_type           AND mp.recipient_action_datetime < '2017-04-18'       LIMIT 200;

OK so here is current for that search (it takes a long time to run - longer with out some filtering like

AND mp.recipient_action_datetime < '2017-04-18'
SELECT    count(*),        TIMESTAMPDIFF(MINUTE,mp.recipient_action_datetime , mp2.recipient_action_datetime) as time_diff                  FROM civicrm_mailing_provider_data mp               INNER JOIN civicrm_mailing_provider_data mp2           ON mp.contact_identifier = mp2.contact_identifier              AND mp.mailing_identifier = mp2.mailing_identifier             AND mp.event_type = mp2.event_type  AND mp.recipient_action_datetime < mp2.recipient_action_datetime                     WHERE mp2.`contact_identifier` IS NOT NULL             AND TIMESTAMPDIFF(MINUTE,mp.recipient_action_datetime , mp2.recipient_action_datetime) IN (840)     AND mp.recipient_action_datetime < '2017-04-18'         AND mp.event_type = mp2.event_type ;
count(*)time_diff
25794840
Eileenmcnaughton added a comment.EditedMar 11 2019, 8:09 PM

This is the generic query with variables to make it easy to re-use & edit

SET @fromDate = '2010-01-01';
SET @toDate= '2017-04-18';


SELECT    count(*),        TIMESTAMPDIFF(MINUTE,mp.recipient_action_datetime , mp2.recipient_action_datetime) as time_diff                 
 FROM civicrm_mailing_provider_data mp             
 INNER JOIN civicrm_mailing_provider_data mp2          
   ON mp.contact_identifier = mp2.contact_identifier    
   AND mp.mailing_identifier = mp2.mailing_identifier             
   AND mp.event_type = mp2.event_type  
   AND mp.recipient_action_datetime < mp2.recipient_action_datetime        
WHERE mp2.`contact_identifier` IS NOT NULL   
   AND TIMESTAMPDIFF(MINUTE,mp.recipient_action_datetime , mp2.recipient_action_datetime) IN (60,120,180,240,300,360,420,480,540,600,660,720,780,840,900,960,1020,1080,1140,1200,1260,1320,1380,1440)  
   AND mp.recipient_action_datetime < @toDate
   AND mp.recipient_action_datetime > @fromDate
   AND mp.event_type = mp2.event_type ;

Just checked status on the 420 run - it's up to

civicrm_repair_omnirecipient_last_processed_timestamp: "2017-07-16 00:00:00"

Eileenmcnaughton added a comment.EditedMar 26 2019, 12:36 AM

civicrm_repair_omnirecipient_last_processed_timestamp: "2017-10-09 00:00:00"

(I think this run finishes early Nov when daylight savings kicks in so probably a few more days & then tweak the number)

Looks like it's crawling at the moment "2017-10-14 00:00:00'

 drush vget | grep omni
civicrm_repair_omnirecipient_last_processed_timestamp: "2017-10-29 00:00:00"

@CCogdill_WMF I just did a dig into whether any duplicates remain. I'm comfortable now that there are not any due to systemic error. However, we do still have a large number of 'legitimate duplicate actions' - The queries I ran tracked duplicates with an exact number of hours between them - so we can see that til the end of 2016 we have

count(*)event_type
829Click Through
604Clickstream
922576Open
2Reply Mail Block

2017

count(*) | event_type |

611Click Through
206123Clickstream
5579008Open
1Opt Out
1Reply Mail Block

2018

count(*)event_type
1115Click Through
2813092Clickstream
313660Open
21Reply Mail Block

2019

count(*)event_type
822Click Through
8Clickstream
99682Open

I think these are pretty much caused by email clients polling

Note this is the query I used - broken into years as they are still crazy slow

SET @fromDate = '2010-01-01';
SET @toDate= '2016-12-31';

SELECT    count(*),            mp.event_type           FROM civicrm_mailing_provider_data mp                   INNER JOIN civicrm_mailing_provider_data mp2                   ON mp.contact_identifier = mp2.contact_identifier             AND mp.mailing_identifier = mp2.mailing_identifier                      AND mp.event_type = mp2.event_type          AND mp.recipient_action_datetime < mp2.recipient_action_datetime             WHERE mp2.`contact_identifier` IS NOT NULL        AND TIMESTAMPDIFF(MINUTE,mp.recipient_action_datetime , mp2.recipient_action_datetime) IN (60,120,180,240,300,360,420,480,540,600,660,720,780,840,900,960,1020,1080,1140,1200,1260,1320,1380,1440)        AND mp.recipient_action_datetime < @toDate         AND mp.recipient_action_datetime > @fromDate        AND mp.event_type = mp2.event_type  GROUP BY mp.event_type;

Hey @Eileenmcnaughton, thank you! When you say "I think these are pretty
much caused by email clients polling", do you mean this is just reporting
gross events vs unique, and some email clients might be overreporting?

We're having an analyst dig through this behavioral data, and it looks like
there's a lot of variance, especially with opens. I just want to them a
clear sense of why the dupes exist.

Eileenmcnaughton added a comment.EditedApr 25 2019, 6:51 AM

@CCogdill_WMF

There are 2 ways that I know of to record whether a remote email client has opened an email

  1. email client reports an email has been opened
  2. a micro-image in the email has a url for which loads can be tracked.

I have identified at least one contact that records an 'Open' every 2 minutes - getting up to 4000 Opens on the same email- that will be as a result of automated email client behaviour of some sort.

So the data we have I think reflects what Silverpop is recording as 'Opens' - but from an email campaigner's pov I think we probably only care about individual behaviour not automated behaviour - (automated behaviour is what I referred to as polling).

I think any effort to cull this duplication on the way into our database would slow down our retrieval since we are relying on mysql to ensure uniqueness & I suspect php would be slower. We could cull it after the fact (kinda like we just did for the ones that were created by an error in our script) - or just deal with the duplication when querying it

Eileenmcnaughton closed this task as Resolved.Apr 30 2019, 7:39 PM