Forum Discussion

Alessandro-laba's avatar
Alessandro-laba
Helper I
6 years ago

Lead scoring model

Hi there, 

 

I have recently started working with Power BI and I would like to create a lead scoring model based on a different set of data. 

 

At the moment I have some variables such as: Email opened, Email clicked, Event registration and Meeting and based on these criteria I would like to create a lead scoring model. The commun denominator would be the Email address so that once I select a specif contact I could see his score.

 

I was thinking to apporach it with an 'Else - If' statement but I actually don't know how to do that. 

 

Does anybody have any knowledge or idea to achieve what I am trying to do? 

 

Thanks in advance, 

Ale

Need Help

5 Replies

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    Hi Alessandro-laba ,

    I create a simple example. Is this something like what you want?

     

    You can create columns or measures like so:

    Columns:

    Custom Score = 
DIVIDE (
    'Ranking of best and worst states for retirement'[Affordability] 
    + 'Ranking of best and worst states for retirement'[Crime] 
    + 'Ranking of best and worst states for retirement'[Culture] 
    + 'Ranking of best and worst states for retirement'[Weather] 
    + 'Ranking of best and worst states for retirement'[Wellness]
    ,
    5
)

    Custom Rank = 
RANKX (
    'Ranking of best and worst states for retirement',
    'Ranking of best and worst states for retirement'[Custom Score],
    ,
    DESC,
    DENSE
)

     

    Or measures:

    Custom Score Measure = 
DIVIDE (
    MAX('Ranking of best and worst states for retirement'[Affordability] )
    + MAX( 'Ranking of best and worst states for retirement'[Crime] )
    + MAX( 'Ranking of best and worst states for retirement'[Culture] )
    + MAX( 'Ranking of best and worst states for retirement'[Weather] )
    + MAX( 'Ranking of best and worst states for retirement'[Wellness] )
    ,
    5
) 
    Custom Rank Measure = 
RANKX (
    ALL('Ranking of best and worst states for retirement'),
    [Custom Score Measure],
    ,
    DESC,
    DENSE
)

    You can get details in the attached PBIX file.

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • mgernhardtRVA's avatar
    mgernhardtRVA
    Regular Visitor
    4 years ago

    Did you work out a solution for lead scoring?  We are trying to accomplish the same thing at my firm right now.

    • Alessandro-laba's avatar
      Alessandro-laba
      Helper I
      4 years ago

      Hi mgernhardtRVA , yes I believe i was able to worked it out. Basically what i did was to take all the actions i wanted to track and give them a score to reach a total of 100. To give you a quick example I had email opens and email clicks so I said: If a the open rate of a contact is less the 1% give a score of 5, if the open rate of a contact is between 1% and 5% give a score of 10 and so on. Same thing for the click rate. One thing to keep in mind is that the sum of these 2 activities can give a max of 100, so let's say the maximum value for open rate could be 40 and the maximum value for click rate could be 60. 

      Once this  logic is set up the I run another formula to get the average and I did it with AverageX. Please see below how I set up the formulas: 

       

      Open rate: 

      Value Opens 2021&2022 = SWITCH(TRUE(),'Elq 2021&2022'[%Unique Opens]>=0.01 && 'Elq 2021&2022'[%Unique Opens]<=0.109,5,'Elq 2021&2022'[%Unique Opens]>=0.11 && 'Elq 2021&2022'[%Unique Opens]<=0.509,10,'Elq 2021&2022'[%Unique Opens]>=0.51 && 'Elq 2021&2022'[%Unique Opens]<=1,15)

      Clik values: 

      Value Clicks 2021&2022 = SWITCH(TRUE(),'Elq 2021&2022'[%Unique Clicks]>=0.01 && 'Elq 2021&2022'[%Unique Clicks]<=0.109,20,'Elq 2021&2022'[%Unique Clicks]>=0.11 && 'Elq 2021&2022'[%Unique Clicks]<=0.509,25,'Elq 2021&2022'[%Unique Clicks]>=0.51 && 'Elq 2021&2022'[%Unique Clicks]<=1,30)
      Lead scoring: 
      Scoring2021&2022 = AVERAGEX(VALUES('Elq 2021&2022'[Email Address]),[Value Opens 2021&2022]+[Value Clicks 2021&2022])
       
      Let me know if this all makes sense.
       
      Thanks,