Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Calculate a drop out rate

 

 

Hi there

 

I would like to calculate the drop out rate by date and by page.

How can I achieve this please? Only by Date and Page so there should be only 2 dates for page 2 and 4 dates for page 1.

 

Thank you so much

 

 

DateID Page visitors

01/11/2006page 110
02/11/2006page 19
03/11/2006page 19
04/11/2006page 16
01/11/2006page 23
02/11/2006page 21
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi Anonymous,

     

    According to your description, you want to get the give up visitors' count and rate, right?

    If it is a case, you can refer to below formula to achieve your requirement.

     

    Base table:

     

    Table formula:

     

    Datail Info =
var summary=SUMMARIZE('drop visit id',[Page],"FirstDate",MINX(FILTER(ALL('drop visit id'),[Page]=EARLIER([Page])),[DateID]),"LastDate",MAXX(FILTER(ALL('drop visit id'),[Page]=EARLIER([Page])),[DateID]),
"MinVisitors",MINX(FILTER(ALL('drop visit id'),[Page]=EARLIER([Page])),[visitors]),"MaxVisitors",MAXX(FILTER(ALL('drop visit id'),[Page]=EARLIER([Page])),[visitors]))

var detail=ADDCOLUMNS(summary,"FirstVisitors",MAXX(FILTER(ALL('drop visit id'),'drop visit id'[DateID]=EARLIER([FirstDate])&&'drop visit id'[Page]=EARLIER([Page])),[visitors]),"LastVisitors",MAXX(FILTER(ALL('drop visit id'),'drop visit id'[DateID]=EARLIER([LastDate])&&'drop visit id'[Page]=EARLIER([Page])),[visitors]))

return

SELECTCOLUMNS(detail,"Page",[Page],"FirstDate",[FirstDate],"LastDate",[LastDate],"FirstVisitor",[FirstVisitors],"LastVisitors",[LastVisitors],"MaxVisitors",[MaxVisitors],"MinVisitors",[MinVisitors],
"GiveUp(ByDate)",if([FirstVisitors]-[LastVisitors]>0,[FirstVisitors]-[LastVisitors],0),
"GiveUpRage(ByDate)",if([FirstVisitors]-[LastVisitors]>0,FORMAT(([FirstVisitors]-[LastVisitors])/[FirstVisitors],"Percent"),"0%"),
"GiveUp(ByAmonut)",[MaxVisitors]-[MinVisitors],
"GiveUpRage(ByAmonut)",if([MaxVisitors]>0,FORMAT(([MaxVisitors]-[MinVisitors])/[MaxVisitors],"Percent"),"0%"))

     

     

     

    Regards

    Xiaoxin Sheng

5 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      I would like to see average drop out rate of page 1 over time, and page 2 and so on

      thanks :)

      • TristanKuesters's avatar
        TristanKuesters
        Icon for Resolver I rankResolver I
        9 years ago
        Don'T you try to set up a measure e.g "DropOut Average" = average(drops) and then use the page no or the date as a lookup value in a matrix, bar chart or whatever?

        Ho does the raw data / tables look like?
  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi Anonymous,

     

    According to your description, you want to get the give up visitors' count and rate, right?

    If it is a case, you can refer to below formula to achieve your requirement.

     

    Base table:

     

    Table formula:

     

    Datail Info =
var summary=SUMMARIZE('drop visit id',[Page],"FirstDate",MINX(FILTER(ALL('drop visit id'),[Page]=EARLIER([Page])),[DateID]),"LastDate",MAXX(FILTER(ALL('drop visit id'),[Page]=EARLIER([Page])),[DateID]),
"MinVisitors",MINX(FILTER(ALL('drop visit id'),[Page]=EARLIER([Page])),[visitors]),"MaxVisitors",MAXX(FILTER(ALL('drop visit id'),[Page]=EARLIER([Page])),[visitors]))

var detail=ADDCOLUMNS(summary,"FirstVisitors",MAXX(FILTER(ALL('drop visit id'),'drop visit id'[DateID]=EARLIER([FirstDate])&&'drop visit id'[Page]=EARLIER([Page])),[visitors]),"LastVisitors",MAXX(FILTER(ALL('drop visit id'),'drop visit id'[DateID]=EARLIER([LastDate])&&'drop visit id'[Page]=EARLIER([Page])),[visitors]))

return

SELECTCOLUMNS(detail,"Page",[Page],"FirstDate",[FirstDate],"LastDate",[LastDate],"FirstVisitor",[FirstVisitors],"LastVisitors",[LastVisitors],"MaxVisitors",[MaxVisitors],"MinVisitors",[MinVisitors],
"GiveUp(ByDate)",if([FirstVisitors]-[LastVisitors]>0,[FirstVisitors]-[LastVisitors],0),
"GiveUpRage(ByDate)",if([FirstVisitors]-[LastVisitors]>0,FORMAT(([FirstVisitors]-[LastVisitors])/[FirstVisitors],"Percent"),"0%"),
"GiveUp(ByAmonut)",[MaxVisitors]-[MinVisitors],
"GiveUpRage(ByAmonut)",if([MaxVisitors]>0,FORMAT(([MaxVisitors]-[MinVisitors])/[MaxVisitors],"Percent"),"0%"))

     

     

     

    Regards

    Xiaoxin Sheng