Duplicate Email Constituent Record Report?

A list or an extraction is only going to give you a list of customer numbers, so I could put together a manual query list for you that would give you all the constituent ids of customers who share an email address with another customer, but it wouldn't tell you which email address it was or who the other customers were.  So, better than checking every email address, but...

Yeah, we just wrote a custom report to tackle this particular area.

Ah, yeah not that helpful either. Maybe I will have to wait for Ongoing Services to pull it. Attached is what they sent me (don't mind my highlights, it's how I kept it all straight). I was kind of hoping to pull something similar myself, but it doesn't appear possible. 

(file has been removed)

Gawain Lavers said:

I could put together a manual query list

Hah, I've been poking around with it, and I can't think without CTEs anymore.

LOL. I hear ya there. I know I've used CTE's when I really didn't need to. Sometimes it is just easier to break up the logic into CTE parts and then put it all together at the end.

It's okay, I can do the same thing with a subquery of a subquery of a subquery and then copy and paste that in five different places.

Okay, so I came up with a manual List query to at least organize the work a bit.  If you create a list with one trivial criteria, save, and then go to "show query" you can delete all of the code in the box and replace it with this:

select
	distinct de.customer_no
from T_CUSTOMER as c
	inner join (
	select
		e.customer_no,
		case when e.login_no = -1 and d.login_no = -1 then 'N' else 'Y' end as connected_login,
		case when h.customer_no is not null then 'Y' else 'N' end as hh_dup,
		cust_count.customers
	from (
		select e1.*,
			ISNULL(cl.login_no, -1) as login_no
		from T_EADDRESS as e1
			inner join TR_EADDRESS_TYPE as et on et.id = e1.eaddress_type
				and et.email_ind = 'Y'
			left outer join T_CUST_LOGIN as cl on cl.eaddress_no = e1.eaddress_no
		) as e
		inner join (
			select e1.*,
				ISNULL(cl.login_no, -1) as login_no
			from T_EADDRESS as e1
				inner join TR_EADDRESS_TYPE as et on et.id = e1.eaddress_type
					and et.email_ind = 'Y'
				left outer join T_CUST_LOGIN as cl on cl.eaddress_no = e1.eaddress_no
		) as d on d.address = e.address and d.eaddress_no <> e.eaddress_no
		left outer join V_CUSTOMER_WITH_HOUSEHOLD as h on h.customer_no = e.customer_no
			and h.expanded_customer_no = d.customer_no
			and h.customer_no <> h.expanded_customer_no
		left outer join (
			select e1.address, COUNT(distinct e1.customer_no) as customers
			from T_EADDRESS as e1
				inner join TR_EADDRESS_TYPE as et on et.id = e1.eaddress_type
					and et.email_ind = 'Y'
			group by e1.address
		) as cust_count on cust_count.address = e.address
	) as de on de.customer_no = c.customer_no
where
	c.inactive = 1
	--and de.connected_login = 'Y' and de.customers = 1 --connected login, single customer account
	--and de.connected_login = 'N' and de.customers = 1 --no logins, single customer account
	--and de.connected_login = 'Y' and de.customers > 1 and de.hh_dup = 'Y' --connected login, dup emails within a household
	--and de.connected_login = 'N' and de.customers > 1 and de.hh_dup = 'Y' --no logins, dup emails within a household
	--and de.connected_login = 'Y' and de.customers > 1 and de.hh_dup = 'N' --connected login, dup emails outside a household
	--and de.connected_login = 'N' and de.customers > 1 and de.hh_dup = 'N' --no logins, dup emails outside a household
;

This will give you every customer who has at least one email address that is duplicated somewhere.  Probably a lot!  However, at the bottom of that block you see six "commented out" lines (i.e. they start with "--").  Remove the first two dashes on any one of them, and the results will be limited as the second comment says.  In this fashion you can prioritize your work.  I've identified where at least one of the email addresses found is connected to a login, since those are probably more important to fix first?  Then there are three other groupings.  First are email addresses where there are duplicates, but all duplicates are on the same constituent record.  With these you can just click through to the constituent and know that you just have to fix the duplicates that you can see.  The second are duplicates where the other email address is owned by a different member of the same household.  These, again, can be more easily corrected, since you can just hop between household members using the affiliations menu.  Lastly there are duplicates where the duplicate emails are on "unrelated" accounts.  Here you'll have to go to the account, look at its email addresses, an then use those in turn in the constituent search menu.

Since, in the case of household or unrelated customer account, the list will contain both customer records of a duplicated email address, periodically regenerating the list will ideally keep you from clicking into accounts you've already fixed.

Caveat 1: this will not work if you are in a consortium and your consortium members don't share constituent accounts.  In that case (we're one!) you'll get a ton of copied emails for each consortium member's record of the same person.

Caveat 2: I am not screening out inactive email addresses, nor inactive logins from consideration.

This is great! I'll try it out and see what happens. Thanks for this