Hello All!
As pretty much all e-mail processes de-duplicate by default (we use Wordfly, just in case anyone cares), this has always been a rather "low priority" thing for me, but in a recent data examination, I found that we have north of 10K accounts with 2 or more identical e-mail addresses (including one account that somehow gathered 19 identical records), so I figure it is about time to do something about it.
Firstly, does anyone have a good script for this? Secondly, assuming I will have to do it myself (and happy to do so), does anyone know all the places e-mail addresses are saved that I need to consider? The problem of course being just because Patron A has 3 identical e-mail addresses does not mean that they can all be summarily deleted. Identical address 1 might be the "primary" on account while #2 might be the one tied to the TNEW login with #3 being tied to an old order. Are those the only locations to which an e-mail address can be tied, or am I forgetting a few?
Note, for these purposes, at least at the moment, I am referring specifically to the situation of account A having 2 or more identical e-mail addresses; ignoring any such account B or C that has its own email address identical to those on account A. We may pursue that direction in the future, but for now, this is a good starting point.
Thanks for any thoughts!
John
TNEW? If so I have a story for that.
I also tried to do this a while back, I'll dig for my scripts.
Yes, TNEW, sorry, meant to put that above. And I am always good for a story.
So there's a bug in TNEW (I'm pretty sure still: it's kind of structural) where if you have an account linked to a certain email address, but you don't realize that, and you've logged in to an account with a different email address, and then you try to change the login/email on that account to the the first email address, the following happens:
If you're a normal computer user, you try to change your login again. We had accounts with 10+ email addresses added in this way. Funny statistic: the more times a custom had tried to change their email address, the less happy they were.
Still looking for that script, but you might want to query your accounts to see how many people have duplicate emails and how many of them they have.
Good story. And, oddly enough, your funny statistic rings true with my general experiences as well.
As far as accounts go, we have approximately 5K with 3 or more identical e-mail addresses, and another 10K+ with 2. Admittedly, some of these might be due to issues with our pre-TNEW era custom site. But either way, I did want to look into doing something now, so if you do end up finding that script, I would be curious to see it. No rush though.
Sorry, coming up blank. I may have just run a few queries and directed the box office to clean up the worst offenders manually.
It happens, no worries. Well, that is what TEST is for. I suppose I will get to experimenting a little bit and see what I can devise over the next few months.
John A. Moskal II said:Are those the only locations to which an e-mail address can be tied, or am I forgetting a few?
I think these are the ones that matter most:
T_AFFILIATIONT_CUST_LOGINT_EADDRESST_ORDER
There's also an eaddress_no column in T_WEB_ORDER - so if you are deleting/merging eaddress records and haven't cleared your web tables in a while you may also need to look at that.
Thanks to both of you! We clear our web tables going back 180 days, so hopefully that would not be too messy; but definitely something to consider.
And now to look into putting something together. Of course, this being a low priority item, it might be a while before I have anything to report.
> T_WEB_ORDER
Yeah, there are a couple more, but failing to update this one won't really hurt anything. YMMV...