Best Practices for Phone and Email Cleanup

@Sharea Rodgers - Welcome to BB Community forums! It depends on how your database is set up - do spouses share one record, or each have their own - and how you utilize or plan to use phones/emails.

I recommend thinking through the ‘how’ to determine what you'll need. For instance, if you're doing an SMS campaign, you'd need to know cellphones vs other phone types. Try to reduce redundancy, eliminate types that are no longer used, document the ‘why’ for the process you've landed on and then do your best to keep it consistent across the board.

@Sharea Rodgers here are some basics that I typically use - these are generally all automated so there is not a lot of manual work.

1. emails which are phones and visa versa, also some emails are websites and should be move to ‘online presence’ instead of email
2. email typos (ex. gmial.com instead of gmail.com), emails with spaces and odd char's - generally find LOTS of typos which can then increase the overall number of ‘good’ emails to communicate with
3. emails which do not contain an “@” or contain multiple “@”s
4. phones which contain chars (except for common ‘ext’ and other common text)
5. duplicate emails on the same record
6. duplicate emails with some entries marked ‘do not email’ and others are not
   

Anyway, just a few tactical things to keep them in a bit better shape.

@Sara Barnaby I do not use a query - I pull all data from the SkyAPI, utilize a python script to identify ‘data quality’ issues, correct the data then write it back to RE. Specific to duplicate emails we keep the one with the most recent ‘date modified’.

However, if did use a query I would export the system_record_id, constituent id, email address, do_not_email. Then pull it into excel assuming you have <1.2M rows - if you have >1.2M rows you can filter your query to do up to a certain constituent id - basically, break the file up.

I would then sort by system_record_id and email address then add a new column comparing the previous email_address row to the current and the current to then next. You can then filer that field for ‘True' to get all the dups. I would also create a column using your NXT URL and the system_record_id so you could just ‘click’ the spreadsheet column to directly access the NXT record.