Tue, Nov 24
Moska is out of office, but I'm pretty sure QA checks are almost complete except one last thing Briana needs Moska for. That's all I know for now, but Moska returns tomorrow so she may be able to chime in then. Thanks!
Thu, Nov 12
Thanks @jgleeson ! I checked out the recurring import jobs and they processed yesterday's again but also the correct day's files right after. We're all set today for sending out emails :)
Wed, Nov 11
Sure thing, @DStrine ! Apologies, I was under the impression that re-using the same task was preferred, as the issue replicates the same way on the Acoustic side. I'll make new tasks from now on. Shall I resolve this one now and make a new task for tomorrow if needed?
Issue is resolved for today! Big thanks to @Ejegg and @jgleeson. From what I read in the chat and email today, it seems that in the short-term, fr-tech will need to fix data manually for each query before re-running the export job. @jgleeson will kindly check back in tomorrow to see if the export needs redoing then. Thank you, Jack!
Thanks! @Ejegg looked into it and send over the files again. I processed them on my side and so I think we are good. I do want to quickly note that the Unsubscribes file doesn't seem to be increasing (like it usually does) for the last few days. Dropping in yesterday's job and today's job:
The civi export file failed again this morning, but we already had emails go out at 10:00 UTC. Donor services has been notified. We have another batch of emails scheduled for tomorrow, so it's urgent for us to have the export running today and tomorrow. Is there someone working today who is willing to look into this? Thanks!
Tue, Nov 10
This issue is now resolved. We had a bump in the road with manual exports failing a few times, and then a bad data issue with fields including a "b'" before the values.
Thanks for working on this today!
Wed, Nov 4
Noting this issue happened again today (Nov 4, 2020). @jgleeson noticed and kicked off a rerun this morning, so we are clear to send emails today.
Fri, Oct 30
The conversation about the few un-updated contacts has been moved to this phab T254304 so we can "officially" close this one out. Thanks!
Adding in some new mystery contacts from another phab (T265700) into this one to investigate why a small number of contacts did not get modified by a full database update and are also not on the Master Suppression List.
Acoustic import ran successfully and looks good to me. Thanks for the help! Resolving this task now.
Met with fr-tech team today! @Ejegg found that the "uploader" is being kicked off too soon before the "builder" is finished. He kicked off a new file for me and is working on making sure the uploads wait to deploy until after the building job completes. Let me know if I got that right :)
Whoops, just realized Jack, Eileen, and Maggie are OOO today. @Ejegg - are you available to triage this?
Hey @jgleeson. I'm opening this ticket again as the export sent over yesterday's files again. Luckily, there's no emails going out today, but we pick back up on Monday.
Wed, Oct 28
Also, just wanting to realign on the main issue of this task:
yahoo.com is bouncing at 3% now based on the latest enUS send we deployed, which seems alarming since it's the second most popular domain group in every email send. @EYener is it possible to pull a civi report tracking yahoo specifically?
Confirming that the import jobs completed successfully now and we are rescheduling emails. Do we want to keep this phab open for the re-try code or should I resolve this task?
Just confirming that there was a send scheduled to go out in 30 mins for Major Gifts, we're unscheduling that send now.
US sends from 9/16, 9/30, 10/22, and 10/26 specifically @EYener
Oct 27 2020
Oct 22 2020
@mepps Hi! The primary issue is resolved yes, but there's some straggler contacts that didn't get the update that we're trying to figure out. I think we could probably close this one out and then figure out which phab to put these edge cases in. I'll do some digging around!
Thanks @jgleeson ! Just ran and checked the recurring jobs and they've all processed successfully. Emails are being scheduled out for 16:00 UTC today (~8 mins)
Thanks @jgleeson - To be on the safe side, we unscheduled emails today until the new file processes. Please let me know once it's available :)
Hi all, Re-opening this task as today's import processed yesterday's file again - DatabaseUpdate-20201021033741.csv
Oct 19 2020
Thanks @MNoorWMF for sharing how you found those contacts in the database. I looked at the 1.00 and 2.00 donation count contacts and most of them are old donors with both_funds_latest_donation_date predating 2015, which we don't target for emails anymore. There are a few that we've sent to fairly recently, so I'm dropping some CIDs below for fr-tech to investigate. These contacts are getting emails from us, but did not get updated with the full file push and I'm having trouble figuring out why.
@MNoorWMF - Can you show me how you checked this? I unfortunately cannot create a query to check for formatting changes.
Oct 16 2020
The recurring jobs ran successfully and so I checked the database and at a glance it looks like most contacts still have both_funds_donation_count in decimal format. I think the fix may have only applied to recent contacts, so we will need a full file push of _all_wikimedia to correct the format across the board. Could this be deployed today?
Oct 15 2020
Ok, just set up the recurring update and ran the recent file from the FTP. Here's the "receipt" from the job:
Thanks, Eileen! Will attempt setting up the recurring import now.
Oct 14 2020
I found the email addresses through a query in Acoustic. I just amended it to show the 5 contact IDs so you'll be able to see the email addresses there. Do you have Acoustic access?
@mepps I go to Data > Databases > Suppression Lists > Master Suppression List. When you click into the Master Suppression List in Acoustic, there's a search function where you can search for email address. If there's no search results, then that's how I know they're not in the suppression list. That's unfortunately the only way for me to check that I'm aware of.
Hi @Eileenmcnaughton - the ones you linked aren't the contacts. I have the 5 Contact IDs but I've checked Civi for them and they're not in there:
Oct 13 2020
Thanks @jgleeson! I just ran them and they look good, so we're back on track. Were you able to find the source of the data integrity issues?
Thanks @jgleeson ! Will be around to process the new files if the job completes.
Oct 9 2020
@DStrine Just chiming in that we just embed survey URLs directly into the Thank You pages, if that helps! I'm thinking more about blockages for our SurveyMonkey account users if we ever need to reset a password, for instance. Let me know if this should be flagged elsewhere!
Oct 5 2020
No rush at all on this task. In fact, we have a small break in sending emails late next week so setting up a new recurring import would be more convenient next week :)
@mepps Yes I think so! Would it be possible for fr-tech to send a duplicate Unsubscribes file under a different name than "Unsubscribes" so I can differentiate/organize the recurring jobs in Acoustic?
Oct 2 2020
Hey all - I just ran the test trial of this with a MSL file from Tuesday. The opt-out job took 3 minutes and the _all_wikimedia recalculation job took 11 minutes. I'm adding the job results below:
Sep 29 2020
I think after I run the test on Friday we will have a good understanding of how many contacts in general are opted out, and it might be a good practice to have this be a 3rd recurring import job if it doesn't take too long to run. I'll report back on how long the job takes and how many contacts update.
Thanks @mepps - that makes sense. I just got a training from Brian on how to run this job. In the spirit of being overly cautious, I will execute the suppressed contact opt-out from the main database job this Friday, so that it doesn't run while we are sending emails. Will ping here after it runs so we get a sense of how long it takes to process and if it's viable to set up a nightly recurring job for it.
Sep 28 2020
Sep 17 2020
Addressing Brian's questions:
- I think @mepps is talking about Acoustic, but @bsisolak is right about how it will slowly get out of sync again and we will have to make a routine of opting out folks in the main database. This is synonymous to what we currently do in Acoustic twice a year: purge the suppressed contacts out of the main database (instead of re-labeling). I guess the solution we want is to figure out an ongoing way to keep suppressed contacts out of the main database regularly, removing the need to manually purge when we happen to remember.
Sep 15 2020
No need, I see the Sent Date info in the forwarded email and it looks like the control from this morning's send.
Hi @MBeat33 - Thanks for flagging. I'm looking up this contact in Acoustic and their information looks updated to me, latest_donation_date is the 2020 donation and it's saying that the last time we sent them an email was in Feb 2020 (Thank You Campaign). The same is reflected in "mailing events" in Civi.
Sorry, I'm a bit confused. Fr-tech sends the Master Suppression List export file to us and we have it mapped to update nightly. Would the fix be to change the settings of this data job?
Sep 14 2020
Oh, that's helpful - thanks @mepps
Sep 10 2020
@mepps No that doesn't reflect suppression list contacts. We have people labeled as "Opted In" in our database that also have their email on the suppression list so they are always suppressed, but that field doesn't notate it. I'm not aware if it's being used for something else, but if not, it would be super helpful if that field updated when people are put on the suppression list!
Sep 3 2020
Thanks @Eileenmcnaughton I see it processed early this morning. Checking the database now.
Sep 2 2020
Ah, ok. Thanks for clarifying and working on this. @MNoorWMF moved our next email send day to Monday, so if you want to send over the full database file tonight, it'll process as scheduled and I can check it in the morning to make sure the contacts we listed in this task are updated. Does that sounds good?
Cool, thanks @Eileenmcnaughton - will we need to do a full database update tonight or tomorrow to get all contacts situated?
@krobinson Confirming that those three CIDs you commented do not have updated latest_donation_date criteria within Acoustic. CID 16626014 was found via email address, which had CID 26931866 instead in Acoustic.
Just added another note from investigating: Looks like CID 16801314 has a matching CID and email address between civi and Acoustic, but Acoustic is listing AF_latest_donation_date as Nov 2016. Looks like this contact was not updated from the donor's Aug 26 donation, so something must be up with the civi to Acoustic nightly export.
Aug 31 2020
New phab for investigating the weird contacts we can't place: T261705
Updating this thread, the two new fields are in the database and functioning. However, I cannot confirm with certainty that all contacts in the database have all been backfilled, as there's not a clear way for me to run queries on these changes. When I run a query that I expect to return 0 contacts, I get a large return that warrants a closer look at the contacts case by case - similar to the issue we surfaced in ticket T254304. For example, there are contacts sitting in our database that have blanks for both_funds_latest_currency when Annual_Fund_latest_currency is populated. There's suppression list contacts in our database and old contacts that have been deduped, all of which get pulled into query counts. In general, there's some data hygiene that needs to get done at some point, so that my queries pull as accurately as possible. So, I made a long term task for this here: T261705
@CCogdill_WMF Were you going to contact legal? I'm not sure what we need to reach Legal for - but this task still stands and I was planning on creating a new task to link to this. It seems to be pointing to a bigger issue that our database is carrying contacts that don't match what's in civi, and so I cannot verify with fr-tech when new fields are successfully backfilled. We have quite a few contacts in IBM with nonsensical data and it meddles with our query counts.
Aug 24 2020
Thanks @jgleeson - I ran the export job you sent over and it the bad data issue seems to be fixed. Pasting in rows below. The 1 bad data seems to be a row that did not have an email address. Since the export covers all contact updates in the last 7 days, we should be fine now to continue forward with the frFR send tomorrow.
@Eileenmcnaughton @mepps - Hey all updating this task again - looks like the nightly import is showing tons of bad data again, I can track it that it's been doing this over the weekend so I think we need another full file push. Are you able to see on your end if modified date is back in the export? All I can see on my end for the bad data error is: "Date provided is not a valid date/format. Valid format: MM/dd/yyyy"
Aug 20 2020
Just updating that we have a Trilogy rep looking into this, but I have some questions for fr-tech:
Per our convo in T260708 - moving my comment to here.
Aug 19 2020
I agree and yes, sorry that other email address is in fact on the suppression list when I double checked. It's difficult to search within the suppression list because it takes forever to load my search results. I wish there was a way in IBM to filter out suppression list contacts from my queries so it's not as confusing for everyone - may be a future phab in that.
Ah, ok when I search the database for that contact without restrictions, CID 1768225 has two email address tied to it. Contact hash is also the same. I can only find one email address out of the two in civi. They have different AF_latest_donation_dates
Got it, thanks. I ran a query to check for any contacts in en6C and FR who donated within the last 5 years and are not opted out that have blanks for those fields ^. The count wasn't huge, but it returned a bit over 6k records. I'm pasting a few cids from that query below for you to look at @Eileenmcnaughton . Let me know what you think.
The file @Eileenmcnaughton sent over processed successfully with bad data back to what it normally is. See aggregate row totals below:
Just ran the latest import and there's a lot of bad data. @Eileenmcnaughton the error is saying: Date provided is not a valid date/format. Valid format: MM/dd/yyyy Could someone fix the date formatting and send the file over again?
Jumping in - the nightly recurring data jobs ran several times this morning, but it looks like there's another file (maybe more) in the queue that I missed. I'm manually running it now. Sorry for any confusion. I'll update this phab again when it's finished.
Aug 18 2020
Hey just jumping in to confirm, yes it's looking like the following Acoustic fields are not populating accurately:
AF_latest_currency (civi equivalent = foundation_latest_currency)
AF_latest_currency_symbol (civi equivalent = foundation_latest_currency_symbol)
The data jobs seems to be running successfully on my end. Just making sure I understand correctly, will the backfill export file be similar to how it used to look for just one run?
Aug 17 2020
This makes sense to me. Thanks @Eileenmcnaughton. I was out of office last Friday but will work on remapping the file today. Will update here when it runs successfully. Let me know if there's a way I can help on the backfilling project.
Aug 11 2020
Three file bundles processed last night, I'm listing the DatabaseUpdate files below. Thanks for letting me know about the change, confirming that total rows looks to be around the number you specified, 1.34k for the August 11 files. I didn't list the unsubscribes files below, as they look normal and the same as before, but let me know if you need them too.
Aug 7 2020
Just updating on that last comment ^
Hi there, looks like the import job failed this morning because there was no file to retrieve. I wonder if the civi server upgrade from last night could have cancelled the file export? If that's the case, maybe it'll start working normally again tomorrow. I know Eileen is out today, so tagging in @Ejegg or @jgleeson. Could one of you take a look if you have time? Thanks :)
Aug 6 2020
Just updating that our Trilogy rep is going to look into this clicks issue. In case it's helpful, here's the URL for that mailing, requested from the meeting:
Aug 4 2020
I'll flag this with our Trilogy rep this week, thanks!
Jul 29 2020
Could someone from email team drop in the full email URL for that track?
Jul 21 2020
Awesome, thanks for the work on this! I can confirm that the nightly import data jobs are running smoothly with aggregate totals that make sense. Thanks for the heads up that we will soon change to processing recently modified contacts only - I will keep that in mind for my checks.
Jul 17 2020
Oh ok, I'm understanding that there could be a 'delay' in what populates the duplicate addresses row. Sounds like no issue then. Today's import job looks great.
Jul 16 2020
Just clarifying here - I can't actually see the file showing up in Acoustic but I know the file is being sent over because jgleeson mentioned it in IRC a while back. I dug up the file name from IRC chat history: MatchingGifts-[dateandtimestamp].csv so @MNoorWMF when you and I set up this recurring data job we will mark down the file name as: MatchingGifts-*
Hi @Eileenmcnaughton . Sorry for the delay, I was off work yesterday. Looking at the results of the import jobs, Two July 15th files were processed - once on July 15 and again today. The July 16th file processed this morning as expected and looks healthy.
Jul 10 2020
Fresh data job run looks ok. One thing I'll point out just in case is that the Master Suppression List total rows has increased by 12k since yesterday. I'm used to MSL increasing but since we haven't sent out emails for a while, it looks a little strange. Does that seem right to you, @Eileenmcnaughton ?
Oh wait, I actually see there's a file ready to run right now. Starting that job now and will report back here on how it looks.