I wonder if it would be best to do this in a contact save hook so we catch changes from the queue consumer and from the front-end.

Considering the performance implications, when we add this to the Contact pre hook, we will be adding a query to every contact update with Opt In = No, which could be a lot of them, only a few of which will be relevant (where the donor is in Italy or Austria and was double opted in). If we have the address available in the hook data, we can skip the query if not relevant because they are not in the relevant countries, which I think should cover updates from donations from the queue (@Laurabarluzzi can you confirm that we do have the country available by creating a donation on the donation form using an existing contact's details when you're implementing?). Otherwise, I don't see any other way to avoid doing a query, which will at least be fast as it is activities by contact id, activity type and status. I think that leaves us with DR actions, email prefs centre and Acoustic unsubscribes which would trigger this check. Only the Acoustic path would be a potential issue, as the others are just one contact at a time.

I considered if we could instead cancel these activities at the point where we are re-opting in a contact and want to send them another double opt in email, but I don't think that will work as we have already updated the contact at that point. We could switch from recording double opt in as activities and make it a custom field instead, but that will still require a query (a slightly faster query, but I think that the overhead is the issue here not the query itself).

@Eileenmcnaughton do you have thoughts?

Change #1309117 had a related patch set uploaded (by Laurabarluzzi; author: Laurabarluzzi):

[wikimedia/fundraising/crm@master] Cancel Double Opt-In activities if donor later opts out

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

@Lars I pushed a patch that implements the basic functionality of the task with basic test coverage. Could you review that for now? I marked it as work-in-progress since it is not optimized.

Unfortunately, I didn't find a way to capture the contact country id from within the pre hook event. As far as I could tell, the only way to capture the country id would be by making an api call to get the contact address information which wouldn't reduce the number of requests. Could you maybe double check if I missed something on my end and if I should find the country somewhere I haven't checked?

In case it was actually impossible to minimize the amount of requests with the current code, would it be possible to add the contact's country id to the event params? This way we could pass it along and solve the problem in a simple way. Also, on which exact country id value should we rely on? I see that a contact can have multiple.

What do you think?

Change #1309117 abandoned by Laurabarluzzi:

[wikimedia/fundraising/crm@master] Cancel Double Opt-In activities if donor later opts out

Reason:

Implementing the task with a cron job in process control for performance optimization

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

The first approach was aborted due to performance issues and not having access to the geo location inside the pre hook event. The task should be executed now by running a daily cron job using process control.

The idea is to run directly a command in the yaml file by leveraging a solution @Lars generated and shared from Civi API4 explorer and using a chain. In theory we should be able to run this command:

echo '{"join":[["Contact AS target","INNER","ActivityContact",["target.record_type_id","=",3]]],"where":[["target.Communication.opt_in","=",0],["activity_type_id:name","=","Double Opt-In"],["status_id:name","=","Completed"]],"limit":25,"chain":{"name_me_0":["Activity","update",{"where":[["id","=","$id"]],"values":{"status_id:name":"Cancelled"}}]},"checkPermissions":0,"debug":1}' | cv api4 Activity.get --in=json

While attempting to run this command against my local test data, I notice that my local contact in civi (generated by doing a test payments, and by enabling / disabling OptIn from the contact dashboard page and by creating a "new activity" for the double opt-in activity) the local database doesn't appear to generate any entry in the civicrm_activity table, making me wonder if that query/command covers all basis.

Could someone clarify exactly how and when the civicrm_activity table gets populated when it comes to Double Opt-In and OptIn activities? I am not sure if the test data is partial (not reflecting accurately the expected production behavior) or if the query does not cover all scenarios when it comes to tracking of Double Opt-In and OptIn activities. Could someone help me clarify this?

Also we can move this to the next sprint to keep this sprint combo wiki focused, if these questions require some time to be answered and addressed.

Update: after jumping on a call with @Damilare we managed to improve my local testing flow and I can now validate that the command appears to be cover all basis. I tested these scenarios in localhost:

• I created multiple contacts paying from Italy
• I tested some contacts going only through the Email Preferences page to perform the following actions: OptIn, Double OptIn and OptOut. Note: we changed the domain, from donorpreferences.wikimedia.org to localhost:9002, in the double opt-in email's link to trigger a full double opt-in flow)
• I tested also some scenarios with the optOut being done directly from the Summary page as admin (from the Communication section)
• I tested one contact having multiple Double Opt-In activities set with status completed
• I tested a scenario with a Double Opt-In activity being created from the GUI > Activites > New Activity option as well

As long as the OptIn / OptOut and Double Opt-In is set with the above flow, running the following command confirmed to work by setting _all_ the statuses of all the Double Opt-In's activities to 'Cancelled'. I think we can move forward with the process control specifics and the deployment stage.

@Laurabarluzzi That command should update the status of the existing double opt in activity, so no additional row, just a change in the activity_status_id of the existing row. You should be able to see this on the activity tab of your contact locally.

Also you'll want to remove the limit from the api call for real use, just in case that's not already on your radar.

And no problem asking questions any time.

@Lars Good catch on the limit, thanks! I definitely forgot to remove it. About the activity tables my concern was for the initial setup (the pre-requisite data) to being able to run the command against the expected scenarios. When trying to create test data, I wasn't sure why my activity tables were empty but this was fixed with Dami earlier today. Now I can easily and confidently setup up starting scenarios for then running the command. By now I have tested the command against a few scenarios outlined in the comment above and I am personally confident in moving to the next steps.

Does the following yaml file look good to you?

Notes:

• Currently it would run at 20:30 every day
• I set 5 min timeout just assuming it should last less; would be no timeout better in your opinion?
• I removed the limit from the command (thanks 🙏 )
• Should we add a note on the usage of the chain in the Description? I'm not sure if it's a bit hidden that step

Looks good. A few small details:

1. You'll want /usr/local/bin/wmf-cv api4 -vv instead of cv api4 (-vv just for more details in the logs)
2. You could replace name_me_0 with cancel_double_opt_ins or something like that (doesn't make any difference functionally, just for clarity). I don't think you need anything more than this to indicate that you're using a chain.
3. I think 5min timeout is fine
4. Finds all contacts who opted out within the last 24 hours and disables their double opt-in activities to ensure compliance. This isn't quite true, it finds all contacts who have opted out and have completed double opt in activities and cancels those activities. Otherwise maybe someone would think this would skip some folks if we missed a job run — probably not, but clearer to avoid the 24h thing.

If you want to make those updates and push it on frpm, I'll +2 virtually and then you can run it once manually to verify that it changes the expected contacts with SearchKit (can help with that if needed).

@Lars I updated the yaml file with all the changes you suggested to make, can you please have a last look?

Regarding SearchKit; thanks for the pointer. I was wondering how to go about monitoring the database to see if the runs were working as expected. I tried to create a custom SearchKit search, can you see it here? I named it "LBarluzzi_cancel_double_optin_job_checker". I'm not sure if it's visible only to me so please let me know.

I tried to capture contacts having both:

• a double opt-in activity that has status *Completed*
• a communication opt in set to *No*

This query currently returns 1708 results for me, does that sound correct? Can you see the results on this page?

SearchKit looks good, updated yaml looks good.

To monitor after the first manual run and after deployment you can use these:

• Grafana dashboard for jobs: https://frmon.wikimedia.org/d/gErnYixWk/process-control-jobs
• SearchKit search: https://civicrm.wikimedia.org/civicrm/search#/display/LBarluzzi_cancel_double_optin_job_checker

The job is now deployed 🎉

I could run it from civi1002 successfully while being on a call with @Damilare

The search in SearchKit showed no results after the manual run:

The final yaml version in production is the following, which is slightly different than the previous version since we had to wrap the previous command inside sh -c "previous pipe command":

Now we need to validate only that the job gets picked up at the scheduled time and that will appear in Grafana for monitoring. Does anyone know if it will all be automatically picked up?

You can see it in Grafana already and if you just verify that there are no more results in your searchkit after it runs at the scheduled time, I think we can call this good.

Jobs have been running on scheduled and double opt-ins get cancelled as expected. With all the information gathered during this task we updated the wiki process control page: https://wikitech.wikimedia.org/wiki/Fundraising/Cluster/Process_control