Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

First Touch Attribution DAX Measure with end-user filter

I have a DB with Sessions as this:

Lead_id__cSession_Start_Time__cFirst Lead Conversion Session TimeSession IDChannelTraffic Type
x111/18/2019 18:3211/26/2019 0:00s100GoogleNon-Direct
x111/19/2019 19:2611/26/2019 0:00s101YahooNon-Direct
x111/25/2019 20:2611/26/2019 0:00s102DirectDirect
x37/20/2019 6:111/15/2020 0:00s108DirectDirect
x311/21/2019 22:501/15/2020 0:00s105LinkedInNon-Direct
x35/15/2020 9:381/15/2020 0:00s106LinkedInNon-Direct
x37/19/2020 18:101/15/2020 0:00s107YahooNon-Direct
x37/23/2020 6:241/15/2020 0:00s109DirectDirect
x37/23/2020 8:101/15/2020 0:00s110DirectDirect
x38/2/2020 15:461/15/2020 0:00s111GoogleNon-Direct
x38/3/2020 7:181/15/2020 0:00s112SearchNon-Direct

 

I would like to have a measure that counts the first session per "Channel" that was before or at the same time as "First Lead Conversion Session Time". I would also like to have a user filter on "traffic type", this filter will impact the 1 st session time as some potential sessions that were first might be filtered out. 

 

This is what I have tried so far:

-First--

I have created a calculated column in the table "session order rank by Lead ID]" that ranks the sessions by dates per lead ID.

-Then-

Lead 1st Touch Attribution Meassure =
var TB =
CALCULATETABLE(
'Lead Attribution',
ALLSELECTED('Lead Attribution'[Lead_id__c])
FILTER(
'Lead Attribution','Lead Attribution'[Session_Start_Time__c]<='Lead Attribution'[First Lead Conversion Session Time]),
TOPN(1,'Lead Attribution','Lead Attribution'[session order rank by Lead ID],ASC)
 
)
 
var calc =
sumx(
TB,
CALCULATE(
DISTINCTCOUNT(
'Lead Attribution'[Session ID])
)
)
 
RETURN calc

 ---

 

This gives me this:

 
 

I expected this:

Filter = Non-Direct 
Row LabelsFirst Touch
Google1
LinkedIn1
Grand Total2
  
Filter = Direct 
Row LabelsFirst Touch
Direct2
Grand Total2
  
No Filter 
Row LabelsFirst Touch
Direct1
Google1
Grand Total2

 

Any suggestions?  PBX File : https://www.dropbox.com/t/dNHdObA0HFUjVDwB 

 

Need Help
  • MFelix's avatar
    MFelix
    5 years ago

    Hi Anonymous ,

     

    That has to do with the context replace your measure by:

    Counts Lead IDs FirstLead = 
VAR MinimumDatesTable =
    ADDCOLUMNS (
        SUMMARIZE ( 'DIm Channel', 'DIm Channel'[Channel] ),
        "@DateMin", MIN ( 'Lead Attribution'[First Lead Conversion Session Time] )
    )
VAR MinStarDateSelection =
    MINX( MinimumDatesTable, [@DateMin] )

VAR MinStarSessionStartTime =
    CALCULATE(MIN('Lead Attribution'[Session_Start_Time__c]),ALLEXCEPT('Lead Attribution','Lead Attribution'[Lead_id__c],'Lead Attribution'[Traffic Type]))
VAR AttributionFiltered =
    FILTER (
        'Lead Attribution',
        'Lead Attribution'[Session_Start_Time__c] = MinStarSessionStartTime&&'Lead Attribution'[Session_Start_Time__c]<=MinStarDateSelection
    )

VAr CountAXX =COUNTAX(
        AttributionFiltered
        ,DISTINCTCOUNT('Lead Attribution'[Lead_id__c])
    )
Return 
IF(HASONEVALUE('DIm Channel'[Channel]),CountAXX,SUMX(ALLSELECTED('Lead Attribution'[Lead_id__c]),CountAXX))

    This may need some changes around the SUMX part.

7 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    5 years ago

    Hi Anonymous ,

     

    Not really sure if I understand the logic based on your data because I cannot reach the result you present on the end of your post.

     

    I have created the following measure:

     

    Counts Value FirstLead = 
VAR MinimumDatesTable =
    ADDCOLUMNS (
        SUMMARIZE ( 'DIm Channel'; 'DIm Channel'[Channel] );
        "@DateMin"; MIN ( 'Lead Attribution'[First Lead Conversion Session Time] )
    )
VAR MinStarDateSelection =
    MINX ( MinimumDatesTable; [@DateMin] )
VAR AttributionFiltered =
    FILTER (
        'Lead Attribution';
        'Lead Attribution'[Session_Start_Time__c] <= MinStarDateSelection
    )
RETURN
    COUNTROWS ( AttributionFiltered )

     

    Result is on attach file.

     

    If this is not the expected result can you share what are the lines that are counting for each of the calculations you present and how you select that is that line.

     

    What I assume in my calculation is that you pick up for each channel what is the lowest First Lead and then all the dates that are lower than that.

     

    for the No filter I have picked up the following lines:

    Lead_id__c Session_Start_Time__c First Lead Conversion Session Time Session ID Channel Traffic Type
    x1 18/11/2019 18:32 26/11/2019 00:00 s100 Google Non-Direct
    x1 19/11/2019 19:26 26/11/2019 00:00 s101 Yahoo Non-Direct
    x1 25/11/2019 20:26 26/11/2019 00:00 s102 Direct Direct
    x3 20/07/2019 06:11 15/01/2020 00:00 s108 Direct Direct
    x3 21/11/2019 22:50 15/01/2020 00:00 s105 LinkedIn Non-Direct
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      MFelix You are on to something. This is how I end up with my expected filtered results. I guess the inclusion of the "First Lead Conversion Session Time" is superfluous in this calculation but is important when I will do the same for the last touch happening before or at "First Lead Conversion Session Time". 

      • MFelix's avatar
        MFelix
        Icon for Super User rankSuper User
        5 years ago

        Hi Anonymous ,

         

        Sorry but still not getting wath is the dates you want to compare and with what values.

         

        Looking at you data what is the minimum date that you compare to and why you consider one date or not in your calculation.

         I'm only confused about why you consider some dates, for example when you have no filter why is the value consider the 18 November and not July 20? Is it because the Sesseion is lower or because the first lead is lower?