2 Replies Latest reply on Sep 28, 2017 1:18 PM by Flavia Lan

    what will be the best way to do 3 dimensions de-duplication?

    Flavia Lan

      This is the 1st time that I try to use forum for work . Yes, I have asked Google, but try this must be really good too-- I want to thank everyone in advance for your help!

       

      For a financial organization--let's say we have 200,000 loan applications from 180,000 loan applicants (just made up some numbers here)

      Each time when a loan applicants re-apply for a loan, there will be a new loan application number, well, same person UNDER DIFFERENT home phone, OR cell phone OR email address

       

      Our database can come up with a giant data source, which is essentially a giant table, that has many columns of:

      • loan application number,  home phone number, cell phone number, and email address.

       

      Let's say some loan applicants applied the 1st time, got rejected, however they got approved when they attempted with the 2nd, or 3rd application UNDER DIFFERENT home phone, OR cell phone OR email address, with updated income info--so they have a loan with us.

       

      Recently we have made change on the underwriting policies and now thinking we can reach out (re-target) to the loan applicants, WHO DOES NOT HAVE A LOAN WITH US at all, to ask them to re-apply and maybe there will be a chance that we can approve them.

       

      Some applicants may look "unapproved" under the 1st attempt, however we do NOT want to reach out to them if they got approved under their 2nd or 3rd attempt.

      What will be the best solution to do this in combination of using SQL query and Tableau functionalities?

       

      Many Many thanks-- now I am heading to work with best wishes there must be answers that is better than what I can think of

        • 1. Re: what will be the best way to do 3 dimensions de-duplication?
          Jennifer VonHagel

          Hi Flavia, the best option is to have a reliable person identifier. In your case, you probably have the SSN somewhere in your source data, as I am sure that info is necessary on a loan.  I used to work at a bank and investment firms, so I know that access to that field may be restricted, but do you have some kind of masked identifier or person identifier to work with? I think I would find these folks in your database before bringing the data to tableau. And personally, I would never bring SSNs into Tableau (unless maybe my company had bought Tableau server, and even then I would use some masked identifier or key if at all possible).

           

          So in your database, you can retrieve customers who have never been approved by doing something like the following...

           

          select cust_id, home_phone, cell_phone, email, etc.

          from loan_applications

          where cust_id =

          (select cust_id

          from loan_applications

          group by cust_id

          having max(case when status = 'Approved' then 1 else 0 end) = 0) sub;

           

          If you don't have access to the SSN or some customer identifier, I'd ask someone with clearance to it to run this query and return to you the records with some key instead of an SSN that you can use as a customer identifier to tie together phone, email, etc. to one person. 

           

          Hope this gets you started. Even if you get the results of this query, you might get multiple records per cust_id. Say someone uses one email address on 1st application, and another on a second application. In this case, you can decide you will send follow-ups to both email addresses, or you could make your query find and return only the single most recent address. These decisions will need to be made according to your company's rules and preferences.

           

          Best,

          Jennifer

          • 2. Re: what will be the best way to do 3 dimensions de-duplication?
            Flavia Lan

            It is great to see how other people will approach this-- it is true that I can't use SSN, and there is no customer ID.

            Each situation is slightly complicated because of limitation of here and there, but the process writing (thinking how to write) and reading the thread helps me to think clearer. Thank you Jennifer for the ideas!!