Best Practices for Phone and Email Cleanup

Sharea Rodgers

Hello All, Happy Monday! My team and I are doing some database cleanup in RE database view with phone and email types and are looking for best ways or practices to go about. Any suggestions?

Austen Brown

@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

@Austen Brown, this is great feedback! Thank you so much! This is a great start for us to begin the process.

Dennis Ladnier

@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

Hi @Dennis Ladnier, can you share the query you created for #5?

Dennis Ladnier

@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.

Alex Wong

@Sara Barnaby
SKY API allows for all emails to be exported, and if that information is stored in a medium that allows for data to be queried (SQL), then most of the items mentioned by Dennis can be automated.

However, if you are not going that route, then you will have to use RE database view > Admin > Import > Constituent Phone > Create import file for all records

here you will be able to select all the needed fields to make “correction” to phone and email.

once you exported all the phone and email, you can use excel's formula COUNTIFS() to locate dup email within the same constituent id and use import to update the phone/email type to a temporarily created delete phone/email type, then you will be able to use a plugin to delete all phone/email that has that phone/email type.

Sharea Rodgers

@Dennis Ladnier, thanks so much! This is very helpful!