As we talked about last week, can you estimate the amount of Civi records to be Dedupe?
This would be helpful to know, for scheduling contractors going forward.
Description
Description
Event Timeline
Comment Actions
"answer is 800k+"
Thank you so much, @Eileenmcnaughton this is great information!
email:
Eileen McNaughton
12:00 AM (10 hours ago)
to me, fr-tech-failmail
Hi Michael,
I was looking to see how many dupes we have in the context of the silver pop script - & I was aware you were after that ego (it a phab I can’t find right now)
I grouped the below query by the year of their last donation date - answer is 800k+
Eileen
SELECT c1.id, min(c2.id) as lowest_contact_id_in_range, max(c2.id) as highest_contact_id_in_range, count(*), YEAR(w2.last_donation_date)
-> FROM civicrm_email e1 -> INNER JOIN civicrm_email e2 ON e2.id > e1.id -> AND e1.email = e2.email -> AND e1.is_primary = 1 -> AND e2.is_primary = 1 -> INNER JOIN civicrm_contact c1 ON c1.id = e1.contact_id AND c1.is_deleted = 0 -> INNER JOIN civicrm_contact c2 ON c2.id = e2.contact_id AND c2.is_deleted = 0 -> LEFT JOIN wmf_donor w2 ON c1.id = w2.entity_id -> LEFT JOIN civicrm_dedupe_exception de -> ON de.contact_id1 = e2.contact_id and de.contact_id2 = e1.contact_id -> LEFT JOIN civicrm_dedupe_exception de2 -> ON de2.contact_id2 = e2.contact_id and de2.contact_id1 = e1.contact_id -> WHERE de.id IS NULL -> AND de2.id IS NULL -> -> GROUP BY YEAR(w2.last_donation_date);
+--------+----------------------------+-----------------------------+----------+-----------------------------+
| id | lowest_contact_id_in_range | highest_contact_id_in_range | count(*) | YEAR(w2.last_donation_date) |
+--------+----------------------------+-----------------------------+----------+-----------------------------+
| 98168 | 14825 | 42476721 | 12529 | NULL |
| 57135 | 9692208 | 9692208 | 1 | 2004 |
| 2392 | 429563 | 41832638 | 17 | 2006 |
| 19138 | 264643 | 41384637 | 46 | 2007 |
| 195443 | 188782 | 41879528 | 2195 | 2008 |
| 308106 | 53026 | 41878565 | 7806 | 2009 |
| 445688 | 17095 | 41881605 | 18059 | 2010 |
| 146511 | 14839 | 42476357 | 44522 | 2011 |
| 230565 | 5224 | 42476087 | 39653 | 2012 |
| 140831 | 16216 | 42476827 | 74950 | 2013 |
| 183402 | 13417 | 42476608 | 123355 | 2014 |
| 129240 | 10586 | 42476674 | 126685 | 2015 |
| 223213 | 10558 | 42476796 | 156368 | 2016 |
| 70866 | 11076 | 42476783 | 123813 | 2017 |
| 152781 | 12344 | 42476781 | 104497 | 2018 |
| 69228 | 38349 | 42476819 | 38619 | 2019 |
| 152921 | 78623 | 42476824 | 2765 | 2020 |
+--------+----------------------------+-----------------------------+----------+-----------------------------+