Prioritize and Pick Unique Contacts from a Company for Your Cold Outreach Campaign Using Spreadsheets
Cold email outreach is one of the critical components of outbound marketing strategies. At its core, a successful email campaign hinges on delivering the right message to the right person. For teams managing outbound emails, challenges often arise in data cleanup, sifting through vast databases to pinpoint ideal candidates for a campaign. Fortunately, with proper use of spreadsheets, we can address these challenges efficiently.
We encountered a challenge within one of our large target audience databases, which included names, emails, and designations. Multiple contacts from the same company, bearing similar or varied designations, populated the dataset. Mass emailing several contacts within a single company isn't a good idea. We must extract one key contact from each company for our campaign to address this. The solution was more complex than selecting the first contact from each group. We had a list of designations with their priority.
The aim was to select contacts with the highest-priority designation from each company. If that designation weren't present, we would move to the next one on the list, and so on. The first contact would suffice if none of our top-tier designations are available in a company.
Our dataset has:
- Email Address
To address the issue, we attributed a score to each contact based on their designation's priority. A smaller score signifies a higher priority. Designations absent from our preferred list are allocated the highest priority value, set at 9999.
Extract the Email Domain
To categorize individuals by company, we introduced the 'Email Domain' column.
=IF(B2="", "", REGEXEXTRACT(B2, "@(.+)"))
This leverages regular expressions to discern the domain from an email address. Assume that the emails are in column B.
Assign priority to the Designation
Our priority-driven designations list is housed in a sheet titled 'Designations'. A 'Priority' column was subsequently established.
=IF(ISNUMBER(MATCH(D2, Designations!A:A, 0)), MATCH(D2, Designations!A:A, 0), 9999)
This formula scrutinizes if the designation aligns with our priority list ("Designations"). If a match is found, it assigns a priority based on its list ranking. Non-matches are designated a 9999 priority. Column D should have the email domains to get this formula to work. Also, the designations ordered in the priority you need should be in a sheet named Designations.
Identify the Optimal Contact
This step selects the contacts suitable for the campaign.
=IF(AND(COUNTIFS($D$2:$D2, $D2, $E$2:$E2, E2)=1,MIN(FILTER($E$2:$E, $D$2:$D=$D2))=E2), TRUE, FALSE)
The formula assesses:
- The uniqueness of the email domain and priority within the list.
- Whether the current priority is the minimal value for its respective email domain.
- Meeting both criteria, the formula yields a 'TRUE', earmarking the contact as the prime choice.
This formula assumes that Column D has the emails and Column E has the priorities.
By the end of this process, We've identified which individual stands out as the ideal contact to target from each organization. The 'Selected' column will illuminate the chosen candidates with a 'TRUE' marker in the dataset used as an example.
The sample template I have used is available here. Please use it and let me know your feedback.
As the database grows or changes or the preferred designations evolve, we can easily update the Google Sheet and instantly identify the best contact from each company. Efficient and effective contact targeting can significantly improve the success rates of our campaigns.