As a marketing intern in college, I was tasked with the glamorous job of building a 5,000-person database. The vice president of sales didn’t care how I did it. He simply said, “Get me 5,000 emails. You have until the end of summer to do it.”
With no knowledge of any tool outside the Microsoft Office suite, I did the one thing that made sense: I cold called. Over three months, I called just south of 5,000 companies (unless I scored a “big win” and landed two contacts within the same firm). It was brutal.
Good news, though: You don’t need to sacrifice hours on the phone or hunt for emails online to build a contact list. Existing business relationships are all around us; they tend to be spread across multiple apps and services. Even if you have a contact list in your address book, chances are you have contacts outside of it that should join the others in one place. You can use this contact database for:
- Marketing, to invite people to your webinar
- Company announcements, like the launch of a new product or service
- Customer relationships, such as sending a client 'Thank You'
- Events, like hosting a book signing or meetup
- Sales, to give your sales team a list of leads
It takes a little grunt work at the beginning, but after the initial setup, you can automatically add new contacts to your list as they come. But first, let’s compile your existing contacts into one, centralized location.
- How to Export Contacts from Google Contacts
- How to Export Contacts from Outlook
- How to Export Contacts from QuickBooks Online
- How to Export Contacts from LinkedIn
- Add Phone Numbers to Your List
- Determine How You Want to Segment Your Database
- How to Clean Your List In Excel
- Import Your List to Different Apps
- Automatically Add New Contacts To Your List
How to Export Contacts from Google Contacts
The easiest place to find existing contacts is your email inbox. Your vendors, clients, potential clients, and even old job applicants are stored there, making it a great starting point for any database.
Every person you ever contacted via email is stored within your address book. Your job is to export them from your inbox into Excel, clean up the list, and store your database in another app, where you can better manage the contacts. Here's how to do it:
1. Navigate to Google Contacts
If you have a G Suite account (business addresses, or those with a custom domain), you can access contacts through Gmail or your Google account. Otherwise, select the Google apps icon toward the top right corner of your screen, and select Contacts.
2. Ensure You Use the Old Version of Google Contacts
For the next step, you'll need to use the old version of Google Contacts, since exporting isn't supported yet in the new version of Google Contacts. Not sure which version you’re using? Look at the left-side panel of the page. If you see Switch to the Old Version at the bottom, you’re on the latest version. Click to revert to the old version of Google Contacts (don't worry—you can switch back after exporting your contacts).
3. Export Everyone You Ever Emailed
Click More, then select Export from the dropdown menu. On the pop-up screen, under Which contacts do you want to export?, select All contacts.
The number in parentheses tells you how many people this entails. Chances are it’s in the thousands. There are bound to be a few people you don’t want to contact, but we’ll clean this list up before importing into a different app.
4. Export Contacts into an Excel CSV file
When it asks you "Which export format?" Select Outlook CSV. Although you’re exporting out of Google Contacts, the purpose is to import into another service. CSV stands for comma separated values—the format you’ll need to import your list into a spreadsheet.
How to Export Contacts Out of Outlook or Microsoft 365
If you have a work or personal email through Outlook or a Microsoft 365 subscription, you can follow a similar process to export contacts associated with your account.
1. Navigate to People Inside Outlook
If you’re already logged in to Outlook, you can do so by clicking here. Otherwise, log in, then navigate to the People app in the top left corner of your screen.
2. Click Manage, then Export Contacts
On the pop-up screen, the default option should be to export All Contacts. Click the Export button, and a CSV file is automatically downloaded to your computer.
How to Export Clients and Vendors from QuickBooks Online
Perhaps you’ve been invoicing clients and keeping tabs on vendors through your online accounting software, such as QuickBooks. This is an excellent place to find contact phone numbers, addresses, and other demographic data to add to the database you’re compiling.
While there are several approaches to exporting contacts from QuickBooks, the easiest way is to use the Reports tab. With QuickBooks Online Simple Start, Essentials, and Plus plans (sorry, fellow freelancers!) you are automatically given vendor, clients, and employee contact lists with your subscription. Here’s how to access them:
1. Navigate Directly to your Contact List of Choice
Already signed into QuickBooks Online? Great. This will be incredibly easy. Just click the links below to go straight to the following lists:
Otherwise, log in to QuickBooks Online, navigate to Reports on the left side bar, and choose your preferred list from the list of options.
2. Select the Data You Wish To Export
Click the Customize button in the top right corner of the screen. Use the various checkboxes to select which data you wish to export, from contact names and addresses to tax IDs.
Once you’re happy with your selections, click Run Report. On your new report screen, select the export icon, then Export to Excel from the dropdown menu.
How to Export Connections Out of LinkedIn
All right, so, you exported everyone from your inbox. What about people you connect with on social media?
There’s no easy way to extract email addresses from Instagram or Facebook (where contact info is kept more private) but there is one social platform which allows you to easily export your connections into Excel: LinkedIn.
In LinkedIn, you can export your connections’ full name, email address, current employer, and job title. It even tells you when your connection with that particular individual began. That’s the foundation for an excellent database—one you can later segment by company, industry (if you know the companies well enough), or position level.
Ready to export out of LinkedIn? Here’s what you do:
1. Access Your Connections in LinkedIn
Navigate to My Network at the top of your LinkedIn. Once inside your network, click My Connections on the left side bar.
2. Manage Your Contacts
Click Manage Synced and Imported Contacts on the right side of your screen. This one is almost hidden. You’ll find it in light grey, which is a little hard to read.
3. Export Your Contacts
Under Advanced Actions on the right, click Export Contacts.
Under How LinkedIn uses your data, select Connections, then click Request Archive. You may need to re-enter your email address to download your contacts. Just a heads up, it may take a minute to receive your connections.
How to Easily Add Phone Numbers to Your List
Want to know the most common missing piece of data within any database? Phone numbers. At this point, you probably have several Excel files available with a healthy list of emails. But what about phone numbers?
For both Android and iPhones, we'll cover:
- How to sync your contacts in your mobile device to an email address for both iPhone and Android
- How to export contacts from your phone (Apple users, only)
- And finally, how to import mobile phone contacts into you email and re-export as a CSV file
How to Sync Contacts from iPhone and Android to an Email Address
Both iPhone and Android have built-in contact systems. The easiest way to get contacts from your phone and into a master database is to sync your contacts from your iPhone to iCloud, or your Android to Google contacts.
Apple Users: How to Sync iPhone Contacts to iCloud
- Go to Settings > Your Name > iCloud.
- Make sure Contacts is turned on.
- When you're asked if you want to Merge or Cancel, tap Merge.
- Now, all your contacts on your phone are uploaded to iCloud, so you can access your contacts from any device.**
Apple Users: How to Sync iPhone Contacts to Google Contacts
Alternatively, if you want to sync your contacts between your phone and an associated email address, do the following:
- Open Settings
- Tap Accounts & Passwords
- Select the email address you wish to sync
- Ensure Contacts is turned on
Android Users: How To Sync Contacts to Google Contacts
On most Android devices, your contacts automatically sync when you turn on your phone and sign in to your Google Account for the first time.
That means you don't need to export from Android—you only need to ensure your Android contacts are still syncing to Google Contacts. Just sync your contacts to your Google Contacts account and export from Google Contacts.
Here's how to double-check your contact sync:
- Open Settings
- Open Accounts and Sync
- Tap your Google account
- Slide the "Contacts" button to the "On" position
- Press Sync Now
Now when you sign into Google Contacts, you should see your Android contacts stored. To export them, follow the steps listed above on How to Export Contacts from Google Contacts.
Apple/iPhone Users: How To Export Contacts from Your iCloud
If you have a Mac or iPhone, the simplest way to export your contacts is online through iCloud.
- Sign into iCloud.com. If you’re already signed-in on your computer, skip to step two
- Navigate to Contacts
- In the bottom left corner, click the settings icon
- Click on Select All from the dropdown
- Once all contacts are selected, select Export vCard
- Save your .VCF file on your computer
Apple Users: How to Import iCloud Contacts into Gmail
While you can export your contacts from these lists, they’re not exported as a CSV file. Instead, each contact is exported as a “vCard.” The simplest way to convert VCF to CSV is to simply import them into your email, then export them back out as CSV.
- Open Google Contacts
- Select Import Contacts on the left side bar
- Select your .VCF file from your computer
- Click Import
Then, repeat the export process by selecting the group you just imported, instead of All Contacts, like before.
Determine How You Want to Segment Your Database
All right, you now have multiple lists of contacts stored in Excel. Phone numbers have been added. Now we just need to combine and import them, right?
Hold up.
You only want to do this once. Meaning, you should be strategic about who you import into your app of choice. You should also consider how you want each person represented within your database.
Before we do anything drastic, let’s think strategically about how you want segment your list.
If something sparked your interest in creating a database—an event, new hire, or new sales strategy—ask yourself: What other events might prove this list useful in the future? The various scenarios running through your head can provide excellent guidance when segmenting a list for the first time.
Not everyone on your list is going to be a prospective client. Period. That doesn’t mean those individuals don’t have a purpose in your business.
Who will make the guest list to this year’s office Christmas party? Who adores and respects you enough to rate n’ review you on iTunes when you launch your podcast? Who do you respect in your industry—enough to recruit them when you land that round of funding?
You might come up with a few handfuls of names—perhaps a few dozen—for each of the above situations. Meanwhile, your list of contacts from Google Contacts and LinkedIn is currently sitting in the thousands. How will you find these individuals amidst the four-digit rows you now have going within Excel?
That’s where segmentation comes in, and why we need to divvy up these contacts before we import them into a more permanent tool.
Remember: You cannot find, sort, or report on information if it doesn’t exist.
Basic Data to Segment Your List
Consider adding the following columns to your file(s):
- Lead Source: Where did the contact come from? For simplicity sake, you can simply write “Google Contacts” or “LinkedIn,” or type something more specific.
- Industry: For your LinkedIn connections, you at least know each contact’s employer. Can you sort by your Employer column and add their industry? This might help send out targeted collateral or industry-specific blog posts in the future.
- Contact Type: Again, not everyone’s going to be a potential prospect, and that’s ok. Consider dividing your list up by friends, vendors, recruits, or media personnel. Who’s left? What other categories can you assign?
- Other contact info: Do you have phone numbers, Twitter handles, or an alternate email address saved somewhere? If you can dump this information easily into Excel, it’s well worth your time to add those columns.
Adding these three to four columns is the bare minimum you might consider starting with. Now, with your segmentation strategy in mind let’s clean-up this list.
Hacks to Clean-Up Your Contact List in Excel or Google Sheets
Before you import your list into another app, let’s clean it up. This will decrease headaches later on, and prevent you from importing your ex-boyfriend into your company database (I’ve seen it happen).
Note: While the following steps are explained using Excel, the same formula will work for Google Sheets. It will simply add one more step to your process. When you first export your CSV file from Google Contacts or other platform, save the file on your computer. To edit your contact list in Google Sheets, simply open a blank sheet, then click File > Import > Upload to upload the CSV file from your computer.
Here are a few Excel tricks that might come in handy.
Run an Index-Match to Consolidate Multiple Sheets Into One
If you have half-populated databases strewn all over your desktop and need to combine them into one, Index-Match will be your favorite (and certainly most-used) formula. For example, let’s say your old business mentor, Gary, is in both your Google Contacts and LinkedIn lists. In the first file, you have his phone number, cell, and business address; in the second, you have his company and job title.
Two Excel files, both with valuable information. Rather than copy-paste your life away, run an Index-Match to bring information from one sheet to the other.
In the first sheet, add columns for his company and job title (Columns E and F). To pull company into Column E, type =INDEX(**click the company column in your other sheet**),MATCH(D2,**click the email column in your other sheet**,0)).
Your formula should look like this. Hover over the bottom right corner of the cell with your formula (in this case, E2) until a (+) sign appears. Double-click it to drag the formula to the end of your sheet.
Extract URLs from contact email addresses
Not sure which company a contact works for? Run this formula, where B2 is the column with email addresses:
=RIGHT(B2,LEN(B2)-FIND("@",B2))
The formula returns the domain name for each of the email addresses in your list (Hey! Another column you can add.)
Use Text-to-Columns to Separate Names, Addresses, and Companies
Now that you have the domain URL, use text-to-columns to separate “.com” from the rest of the domain, hopefully resulting in the company name (or something very similar). You can run the same formula to separate first name from last name, or street, city, and state into three distinct columns.
To do this:
- Select the column you wish to divide into two. For example, in column H in the Excel sheet below, I have extracted the domain from the individual’s email address. I would like to divide this into two columns, separating the company name from “.com”.
- Insert a new column(s) to the right of that column. Since I am dividing one column into two, I only need to add one column. If you are dividing an Address column into Street Address, City, State, and Zip Code, you will have to insert three columns.
Tip: I always add an extra column for rare circumstances, like an individual having two last names. You can always delete it later.
- Highlight the original column. In my case, this is column H.
- Click Data then Text to Columns from the dropdown.
- Click Next.
- Select the barrier for which to separate your column. For example, a space significies the separation between a first and last name. However, a comma might separate the space between city and state within an address column.
Import Your Compiled Contact List into Another App
Depending on your needs and the type of business you have, you can store and develop your list within any number of apps. You can keep your database in a:
- CRM system: Great for logging information between customers and prospects.
- Email automation software: Ideal for one-to-many communication, such as invitations, automated birthday emails, or sending client surveys. Note: If you plan to email these individuals, be sure to use a double-opt so you don’t go against any anti-spam laws.
- Spreadsheet app: Which is a digital, automated version of Excel. Tools like Airtable and Smartsheet can be customized to the point of developing your own CRM system within a spreadsheet.
Now that your initial list is compiled, cleaned up, and imported into an app, you can use Zapier so you (almost) never repeat this process.
The few times you might find yourself returning to this tutorial is with the expansion of your team. If you hire a new business development rep because of his or her connections, then ideally you want those connections added to your database. Likewise, if a team member parts ways with your company, you may want to ensure their connections remain with your firm.
From now on, you can add new contacts as they come in—without returning to Excel.
Automatically Add New Contacts to Your CRM System
Automatically Add New Contacts to Email List
Automatically Add New Contacts to Your Spreadsheet App
Want to learn more?
The Beginner’s Guide to CRM: Customer Relationship Management
Spreadsheets, Simplified: Ditch Data Entry with Excel and Zapier
How to Segment Emails Lists For More Opens, Click-Throughs, and Conversions
source https://zapier.com/blog/consolidate-contacts-one-list/
No comments:
Post a Comment