6 Replies Latest reply on Aug 22, 2013 8:46 AM by Joshua Milligan

    How to filter bad e-mail domains?

    Charu Kapoor

      Hi All,

       

      We have a list of e-mail domains that I would like to get filtered out ALL the times from a set of customers that is dynamically derived based on certain combinations chosen by user. What I have tried so far is to add a custom e-mail filter that has 'If' clause in it but this is not a scale-able solution as the list of bad e-mail domains will grow over time.

       

      The list of bad domain names is stored in a separate table in database.

       

      What is the best way to handle this situation? Essentially this is like a SQL-NOT IN clause scenario.

       

      Thanks and regards, Charu

        • 1. Re: How to filter bad e-mail domains?
          Joshua Milligan

          Charu,

           

          You might need to supply a little more detail to get a specific answer.  For example, are you connecting live (what databse)?  Using the multiple tables option?  Custom SQL?

           

          What if you did something like left join from your primary table to the bad domain table?  Then filter out anything where BadDomainID is not NULL.

           

          Regards,

          Joshua

          • 2. Re: How to filter bad e-mail domains?
            Joshua Milligan

            By the way, welcome to the forums!

            • 3. Re: How to filter bad e-mail domains?
              Charu Kapoor

              Thanks Josh,

               

              I am connecting live to MS SQL Server 2008 R2 using custom SQL. The custom SQL runs fine in SQL Server without any performance issues and generates around 300k records. The e-mail exclusion table has less than 500 records. There are couple of reasons I may not want to do a table join:

               

              a. The join will have to work on first extracting the domain name from e-mail column and then matching it against the exclusion list. The operation to do this for all the records will be expensive and may not be needed b/c i may be only interested in a subset of these customers to begin with.

              b. The e-mail exclusion list will be generic across all the worksheets so my preference will be to save it and always apply it wherever needed.

              c. Third, if i do this in SQL it will get fired every time I load this worksheet vs. if this is stored in Tableau then tableau engine could take care of it than sending query to back-end every time (just a thought if that works)

               

              There is an alternative to do this in database by extending our customer table but I am curious if Tableau can handle it with us not having to go to IT.

               

              Please let me know if you have more questions.

              Thanks and regards, Charu

              • 4. Re: Re: How to filter bad e-mail domains?
                Joshua Milligan

                Charu,

                 

                One possibility is to use data blending.  You'll create two connections.  One for the emails and another for the exclusion list.  I've attached a workbook that demonstrates how this could work.  Step through the tabs to see how it comes together.

                 

                Regards,

                Joshua

                • 5. Re: How to filter bad e-mail domains?
                  Charu Kapoor

                  Thank You. This looks slick and i will try it.

                   

                  Followup question on best practice:

                  Shall one strip the domain name in SQL or in tableau using string calc w.r.t. performance in mind?

                  • 6. Re: How to filter bad e-mail domains?
                    Joshua Milligan

                    You're welcome.  Please let me know how it works!