Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
barbkuntzbarbkuntz 

Lead Scoring - I need formula help!

Hi there -
 
I'm new (really new!) to formula writing and need some help with a lead scoring formula.
 
What I want to achieve is this (in English):  If the lead status is 'duplicate lead' or 'no potential' score the entire lead as zero.  If the above does not apply and the email address contains 'yahoo,' 'gmail,' 'rr.com,' 'aol,' 'verizon,' 'pacbell' or 'att,' score the entire lead as '1.'  If neither of the above apply, score the lead (see below).
 
Here's what I have so far which works for scoring the leads, but doesn't take the first two IF's into account - I need to move the last IF statement as it isn't working where it is.
 
Can someone help please? 
 
CASE (LeadSource , "Advertising", 1, "Directory Listing (paid)", 3, "Directory Listing (unpaid)", 1, "Email", 4, "E Newsletter", 4, "Inbound Call MUC", 4, "Inside Sales", 1, "Other", 1, "Press Release", 1, "Rep Lead", 1, "Seminar / Conference", 3, "Technical Article", 2, "Top 10", 1, "Tradeshow", 4, "Web", 4, "Organic - Google", 4, "PPC - Google", 4, "Google AdWords", 3,0)+
CASE (Industry , "Solar Cells", 4, "Medical", 3, "Electronic Components", 3, "Automotive", 3, "Alternative Energy", 4, "Batteries", 4, "Aerospace", 3,0)+
 IF( CONTAINS("yahoo:hotmail:gmail:rr.com:aol:verizon:pacbell:att", Email ) , 0, 2)
 
Thanks!
Barbara
 
 


Message Edited by barbkuntz on 11-18-2008 11:47 AM
AdminisaurusRexAdminisaurusRex
It looks like you'll need a number custom formula field.  The formula below assumes that LeadStatus is a picklist.
 
It checks to see if the Lead Status is "Dup Lead" or "No Potential" and marks it as 0 if it that is true.  If not, it checks the email address for "yahoo", "hotmail", etc.  If it finds that it marks it as 1.  If not then it will score the lead as you specified.
 
IF( OR(
            ISPICKVAL ( LeadStatus , "Dup Lead" )  ,
            ISPICKVAL ( LeadStatus , "No Potential" )
            ) ,
      0 , 
         IF( OR( 
                    Contains (EMAIL, "yahoo" )
                    Contains (EMAIL, "hotmail" )
                    Contains (EMAIL, "gmail" )
                    Contains (EMAIL, "rr.com" )
                    Contains (EMAIL, "aol" )
                    Contains (EMAIL, "verizon" )
                     ),
                     1 ,                     
                    CASE (LeadSource , "Advertising", 1, "Directory Listing (paid)", 3, "Directory Listing (unpaid)", 1, "Email", 4, "E Newsletter", 4, "Inbound Call MUC", 4, "Inside Sales", 1, "Other", 1, "Press Release", 1, "Rep Lead", 1, "Seminar / Conference", 3, "Technical Article", 2, "Top 10", 1, "Tradeshow", 4, "Web", 4, "Organic - Google", 4, "PPC - Google", 4, "Google AdWords", 3,0) +
CASE (Industry , "Solar Cells", 4, "Medical", 3, "Electronic Components", 3, "Automotive", 3, "Alternative Energy", 4, "Batteries", 4, "Aerospace", 3,0)
)
 
You may need to close one or two more parenthesis, but this should get you started.


Message Edited by AdminisaurusRex on 11-18-2008 12:19 PM

Message Edited by AdminisaurusRex on 11-18-2008 12:20 PM
barbkuntzbarbkuntz
Thanks!  So here's how new I am - how do I know where to close the parentheses?  It seems to me that I need two closing parentheses, but I've tried several different places and can't find one that 'passes' the syntax test.  The formula as is returns Error: Syntax error. Missing ')' and the word 'Contains' (next to 'hotmail') is highlighted.  What is this telling me?
 
I've tried these combos (color coded in red, blue and green):
 
IF( OR(
            ISPICKVAL ( LeadStatus , "Dup Lead" )  ,
            ISPICKVAL ( LeadStatus , "No Potential" )
            ) ,
      0) ,
         IF( OR(
                    Contains (EMAIL, "yahoo" )
                    Contains (EMAIL, "hotmail" )
                    Contains (EMAIL, "gmail" )
                    Contains (EMAIL, "rr.com" )
                    Contains (EMAIL, "aol" )
                    Contains (EMAIL, "verizon" )
                     ),
                     1) ,))                    
                    CASE (LeadSource , "Advertising", 1, "Directory Listing (paid)", 3, "Directory Listing (unpaid)", 1, "Email", 4, "E Newsletter", 4, "Inbound Call MUC", 4, "Inside Sales", 1, "Other", 1, "Press Release", 1, "Rep Lead", 1, "Seminar / Conference", 3, "Technical Article", 2, "Top 10", 1, "Tradeshow", 4, "Web", 4, "Organic - Google", 4, "PPC - Google", 4, "Google AdWords", 3,0) +
CASE (Industry , "Solar Cells", 4, "Medical", 3, "Electronic Components", 3, "Automotive", 3, "Alternative Energy", 4, "Batteries", 4, "Aerospace", 3,0)
)))
 
Finally - once I get this all together - is there some place you can point me where I can learn a little bit about forming these kinds of queries?
AdminisaurusRexAdminisaurusRex
Ah! I forgot to put commas after the domain name triggers.  I also included a SUM function.  When you nest IFs, you have to leave the " ) " for the end.  Each nest if is part of the one preceding it.  Its tough to comprehend, but I just open up a blank text file and put each condition on its own line and indent for each different function.
 
There are probably some good editors that you can download too.
 
Try this:
 
IF( OR(
       ISPICKVAL ( LeadStatus , "Dup Lead" )  ,
       ISPICKVAL ( LeadStatus , "No Potential" )
       ) ,
      0  ,
      IF(
          OR(
             Contains (EMAIL, "yahoo" ) ,
             Contains (EMAIL, "hotmail" ) ,
             Contains (EMAIL, "gmail" ) ,
             Contains (EMAIL, "rr.com" ) ,
             Contains (EMAIL, "aol" ) ,
             Contains (EMAIL, "verizon" )
            ) ,
            1 ,                    
            SUM(
                CASE ( LeadSource ,
                       "Advertising", 1,
                       "Directory Listing (paid)", 3,
                       "Directory Listing (unpaid)", 1,
                       "Email", 4, "E Newsletter", 4,
                       "Inbound Call MUC", 4,
                       "Inside Sales", 1,
                       "Other", 1,
                       "Press Release", 1,
                       "Rep Lead", 1,
                       "Seminar / Conference", 3,
                       "Technical Article", 2,
                       "Top 10", 1,
                       "Tradeshow", 4,
                       "Web", 4,
                       "Organic - Google", 4,
                       "PPC - Google", 4,
                       "Google AdWords", 3,
                        0)
                  +
                CASE (Industry ,
                       "Solar Cells", 4,
                       "Medical", 3,
                       "Electronic Components", 3,
                       "Automotive", 3,
                       "Alternative Energy", 4,
                       "Batteries", 4,
                       "Aerospace", 3,
                       0)
                )
         )
  )
 
I learned formulas by tirelessy working with Excel and consulting a few trusty interent sources for guidance like Mr.Excel.  But I'm sure the good people of this community will help you along too.


Message Edited by AdminisaurusRex on 11-18-2008 01:56 PM
AdminisaurusRexAdminisaurusRex
IF( OR(
       ISPICKVAL ( LeadStatus , "Dup Lead" )  ,
       ISPICKVAL ( LeadStatus , "No Potential" ) \\ < -- the condition to test\\
       ) \\ < -- closes the "OR"\\ ,
      0  , \\ < -- puts "0" when condition for first IF is true\\
      IF( 
          OR(
             Contains (EMAIL, "yahoo" ) ,
             Contains (EMAIL, "hotmail" ) ,
             Contains (EMAIL, "gmail" ) ,
             Contains (EMAIL, "rr.com" ) ,
             Contains (EMAIL, "aol" ) ,
             Contains (EMAIL, "verizon" ) \\ < -- the condition to test for when the first IF is false\\
            ) , \\ < -- closes the "OR" for the nested IF\\
            1 ,  \\ < -- puts "1" when second nested IF is true \\                  
            SUM(
                CASE ( LeadSource ,
                       "Advertising", 1,
                       "Directory Listing (paid)", 3,
                       "Directory Listing (unpaid)", 1,
                       "Email", 4, "E Newsletter", 4,
                       "Inbound Call MUC", 4,
                       "Inside Sales", 1,
                       "Other", 1,
                       "Press Release", 1,
                       "Rep Lead", 1,
                       "Seminar / Conference", 3,
                       "Technical Article", 2,
                       "Top 10", 1,
                       "Tradeshow", 4,
                       "Web", 4,
                       "Organic - Google", 4,
                       "PPC - Google", 4,
                       "Google AdWords", 3,
                        0)
                  +
                CASE (Industry ,
                       "Solar Cells", 4,
                       "Medical", 3,
                       "Electronic Components", 3,
                       "Automotive", 3,
                       "Alternative Energy", 4,
                       "Batteries", 4,
                       "Aerospace", 3,
                       0) \\ < -- performs this calculation if the second nested IF is false\\
                ) \\ < -- closes the "SUM"\\
         ) \\ < -- closes the nested "IF"\\
  ) \\ < -- closes the first  "IF"\\
barbkuntzbarbkuntz

Thank you sooo much! 

The only adjustment I had to make was to change the word "SUM" to "+" and it checked out - we're lead scoring now!

I love the Salesforce community!