Make a guess based on the most common patterns

@MarqueeCrew
So I have looked at Rocket reach before however to onboard that vendor would take time, and the cost also needs to be considered.

But you’re right, conceptually I am trying to do the same thing! The key thing is speed to market, so trying to find a crude solution in alteryx

@Masond3

Interesting problem. This isn't the most elegant solution, but you can brute force your way through it by matching the email patterns with a Formula tool. Hope that gets you in the right direction!

email_guess.yxmd

@geoff_zath - Hi - So your solution is very interesting, after playing around, i have found a flaw in my thinking which in turn has affected the solution that you have provided.

What i have noticed is that the solution is taking the highest percentage email pattern and applying across all the contacts regardless of companyid

When actually, we should find the email pattern for a given companyid, and apply that email pattern for those contacts where the email is blank

Current Input

Contactid	First Name	LastName	Email	Company Name	Companyid
111111	Richard	Piper	Richard.Piper@Nike.com	Nike Inc	001f100001InnV5AAJ
222222	Danielle	Collins	Danielle.Collins@Nike.com	Nike Inc	001f100001InnV5AAJ
333333	Dane	Smith	Dane.Smith@Nike.com	Nike Inc	001f100001InnV5AAJ
44444	Robert	Atleryx	RAlteryx@Nike.com	Nike Inc	001f100001InnV5AAJ
55555	John	King		Nike Inc	001f100001InnV5AAJ
666666	Chris	Dannher		Nike Inc	001f100001InnV5AAJ
AAAA	Richard	Piper	Richard@Alteryx.com	Alteryx	11111
bbbb	Danielle	Collins	Daniel@Alteryx.com	Alteryx	11111
cccc	Dane	Smith	Dane@Alteryx.com	Alteryx	11111
dddd	john	Smith		Alteryx	11111

Expected Outcome

Contactid	First Name	LastName	Email	Predicted Email	Rational	Company Name	Companyid
111111	Richard	Piper	Richard.Piper@Nike.com			Nike Inc	001f100001InnV5AAJ
222222	Danielle	Collins	Danielle.Collins@Nike.com			Nike Inc	001f100001InnV5AAJ
333333	Dane	Smith	Dane.Smith@Nike.com			Nike Inc	001f100001InnV5AAJ
44444	Robert	Atleryx	RAlteryx@Nike.com			Nike Inc	001f100001InnV5AAJ
55555	John	King		John.King@Nike.com	Common Email Pattern " First+.+Last"	Nike Inc	001f100001InnV5AAJ
666666	Chris	Dannaher		Chris.Dannaher@Nike.com	Common Email Pattern " First+.+Last"	Nike Inc	001f100001InnV5AAJ
AAAA	Richard	Piper	Richard@Alteryx.com			Alteryx	11111
BBBB	Danielle	Collins	Daniel@Alteryx.com			Alteryx	11111
CCCC	Dane	Smith	Dane@Alteryx.com			Alteryx	11111
DDDD	john	Smith		John@alteryx.com	Common Email Pattern " First"	Alteryx	11111

@Masond3 That's a quick fix! See attached.

email_guess_v2.yxmd

@geoff_zath I will take a look at your solution tomorrow when back in the office.

Random question- how do you handle multiple contacts with different email patterns associated to same company that have different email formats

Example -
If you have three contacts, and two of those three contacts have email addresses associated to same company and both emails addresses have a different format, meaning that they both have a 50% association to them . For that one contact that has the missing email, what should the email pattern be assigned to it?

in those scenarios do you think it’s best to flag these exceptions

Just curious on the use case application of this request. @Masond3 could you share a bit more of what you intend to do with this workflow? Trying to understand it.

From the looks of it, this allows you to get the info without RocketReach, right?

@caltang so my use case is to solve two particular problems statements

1)we have a high level of bounces

2) we have a high % of contacts that have no email

the first scenario wouldn’t be covered in this flow, however the goal would be to find those contacts which have a different email pattern than the rest at a given company and try and use the recomended pattern to change the email and hopefully reduce the bounces

the second use case is we want to grow market share , so predicting an email address could be a great way to do that.

Interesting. Thanks for sharing!

Though you may hit a lot of blanks and/or wrong PICs with same names. Getting to the decision maker seems to be a better bet if you can find their LinkedIn (if any), no?

@caltang so for the key decision makers at the firm we are pretty good and have the right communications.

However your point is still valid ! The holy grail would be a multiple third party vendors to cross reference , however we as a firm are a long way off that. So was hoping to use a data driven approach using existing data, do a small poc and then measure the ROI.

what is pics ? I assume (personal information communication services ?)

Person-in-charge - if the decision maker part is settled, then that's great!

Good call on using Alteryx. Your post actually gave me some ideas! Can also be applied for other purposes / cold outreach.

Thanks for asking this question!

@caltang - ooh I am glad you able to use some of my concept. Can you elaborate on some of your ideas ? A problem shared is a problem halved !

Keeping old records is tough. Some suppliers have changed emails, and the contact points have gone cold. It’s just names passing through and no proper email filing is in place. This is one application I see working well!

@Masond3 See if this solves the problem (assuming I understand it correctly). I've added another company with email formats A, B, and C. With 40% using A and B and 20% using C. In this case, it will format your email guesses in formats A and B and add a column that flags it as an even split.

email_guess_v3.yxmd

@geoff_zath Cool. I will take a look when back in the office.

I have amended the flow slightly as when I am running my data approx 800k it was stalling around the “append” function.

I tried to get around this using the “ using joins” and that seemed to work better, but. let me see if I can upload my flow tomorrow so you can take a browse

@Masond3 Oh yes that would cause an issue! Not sure why I didn't use the Join tool in the first place 😅 See if this works better without the Append tool.

email_guess_v4.yxmd

@Masond3 This version also takes into account different email domains per company ID. It will associate the most common ID with the predicted email structure. At least it "should"...

email_guess_v5.yxmd

@geoff_zath lol. The only way I found that scenario was creating some test data, where data wasn’t perfect and tried to understand how it would be handled.

I think the common domain logic by company id needs to be before the logic where we flags it as an even split.

il be home in a couple of hours I can try and validate if not it be earliest tomorrow.

It might already be...check some of the wireless connections, it is not totally linear in how things are calculated.