1 2 Previous Next 16 Replies Latest reply on Nov 6, 2013 5:11 AM by Vahini Rajagopal

    Average duration in days between website visits

    Xavier P

      Another tricky one with dates!

       

      The workbook attached contains visit records on website, broken down by users and per day.

       

      I am trying to calculate the average number of days between visits for each user.  Well, will end up creating groups but for now, would like the calculation for each user.  Like number of visits on the second tab.

       

      Thanks for checking, bigger thanks if you can help!

        • 1. Re: Average duration in days between website visits
          Neal Smoller

          not to hijack this question, but i am looking for the same in a retail environment.  id imagine the theory is the same.

           

          to add to this, i'd then like to know days since last visit.  how can that be done in tableau?

          • 2. Re: Average duration in days between website visits
            Andrew Ball

            Hi Xavier,

             

            I've attached a workbook that gives the average days between visits for users. As it has been achieved by using table calculations, you need to keep all the relevant fields in play for it to work. If you look through, I've also used the FIRST()=0 trick to remove duplicates.

             

            If you want to do this in a more comprehensive way, you will need to use some manner of custom SQL.

             

             

            Hope this helps. Let me know if it needs tweaking, or if you want some further explanation.

             

            Andrew

            1 of 1 people found this helpful
            • 3. Re: Average duration in days between website visits
              Neal Smoller

              @Andrew Ball

               

              Thanks for the workup.  I know i'm not OP, but I wouldn't mind a bit of explanation.

               

              I'm connected to an Access DB, if that matters for this, let me know.

               

              I created [Previous Date].

              I created [Day Diff]

              I added Day(Date) and converted it to discrete under level of detail

              I added [Previous Date] under level of detail and did the table calculation

               

              everything looks the same.  the only difference i see is under edit table calculation for Day Diff i see this: 

              Results are computed along Day of FillDate (sorted ascending by Count of FillDate) for each Pt Name.

               

              I don't know if this matters.

               

              Since my data is pharmacy data, I unfortunately can't send any records for you to look at it.

               

              If you can help i'd be extremely thankful!

              • 4. Re: Average duration in days between website visits
                Xavier P

                Thanks Andrew for the advanced calculations and Neal for taking part into the conversation.

                 

                Andrew, I replicated all calculations onto a production workbook with full data set but Date diff returns zero.

                 

                The only difference is that username is a calculated field resulting from raw data with different cases on the original calculated field.


                Any idea why this would occur?

                • 5. Re: Average duration in days between website visits
                  Andrew Ball

                  Hi Neal & Xavier,

                   

                  Glad that we are getting there - I know what it is like to get stuck with something like this!

                  So, in turn of asking:

                   

                  Neal

                  Connecting to Access won't cause an issue, as we haven't used an database specific coding.

                  The table calculation to find the previous day is relying on the fact that the dates are ascending coming from your data source. If you have multiple records for the same person on the same day, ordering by the count of FillDate will make the table calculations fail. So try removing the sort and it should be ok. If your data is not coming through in ascending order (or if you want to be sure), you will need to create a field like DATEDIFF('day',#1/1/1900#,[FillDate]) and then sort ascending on that field

                   

                  Xavier

                  Is the username calculation a standard Calculated field that can be classed as a dimension, or is it a table calc? If it is a standard calculation, then make sure that both table calculations (and both parts of the Day diff calculation both are computed along date for each username, restarting every username.

                  If this doesn't work, take it back a step and just see if the previous date calculation gives you the results you'd expect.

                   

                   

                  Hope that helps you both. If not, I'll probably need to see a copy of the workbook, or at least some but close data if you can.

                   

                  Andrew

                  • 6. Re: Average duration in days between website visits
                    Xavier P

                    Hi Andrew

                     

                    Its a calculation that results into a dimension table, it's simple, Username=LOWER(User Name) where [User Name] is the original field filled with both 'Andrew' and 'andrew' instances.

                     

                    Trying your solution now, thanks ever so much.

                    • 7. Re: Average duration in days between website visits
                      Xavier P

                      No luck Andrew, I replicated my set-up on the sample workbook and calculations are actually faulty now.  Can you review and let me know what is wrong?

                      • 8. Re: Average duration in days between website visits
                        Andrew Ball

                        Hi Xavier,

                         

                        The last step is going through the table calcs, of which there are three in the view. There is one standard (Previous Date) and one nested (Previous Date within Day Diff) table calc (this nested counts as two table calcs). You need to ensure all three have the compute using set to:

                        • Username
                        • Day of Date

                        and then also set to restart every "Username".

                         

                        That should do it. If not, let's make things simpler by combining calcs. Try editing the day diff to be

                        "IF FIRST()=0 THEN WINDOW_AVG( DATEDIFF('day',LOOKUP( ATTR( [Date] ),-1 ),ATTR( [Date] )),0,IIF(FIRST()==0,LAST(),0) ) END"

                         

                        Then set the compute as before. This combines the two calcs into one - I left them seperate initially so you could follow the logic of how I got to the result, but nesting can be a pain, and isn't needed in this case.

                         

                        Andrew

                        1 of 1 people found this helpful
                        • 9. Re: Average duration in days between website visits
                          Xavier P

                          Thanks again Andrew, just to be clear, the 3rd calculation you are referring to is the LOWER () one?

                          • 10. Re: Average duration in days between website visits
                            Andrew Ball

                            Xavier,

                             

                            I suggest that you change the Day Diff to simplify things - if nothing else, for future maintenance!

                             

                            To clarify, in the original view, the three table calculations were:

                             

                            Previous date

                            LOOKUP( ATTR( [Date] ),-1 )

                            Day diff

                            IF FIRST()=0 THEN WINDOW_AVG( DATEDIFF('day',[Previous date],ATTR( [Date] )),0,IIF(FIRST()==0,LAST(),0) ) END

                            [Previous date] (as part of the Day diff calculated Field)

                            LOOKUP( ATTR( [Date] ),-1 )

                             

                            Can be a bit confusing having the previous date twice, I realise. As mentioned, I'd recommend simplifying things.

                             

                            Andrew

                            • 11. Re: Average duration in days between website visits
                              Xavier P

                              Aha! Way simpler indeed.  So no need to add any filter either. Works pretty well, thanks again Andrew!

                              • 12. Re: Average duration in days between website visits
                                Neal Smoller

                                Andrew,

                                Thanks for the answer.  It is working now.

                                 

                                Since you seem to be a brain, i'd like to compare average days between visits to the time since the last visit, if it is larger, then i would assume that the customer may be leaving us and using a competitor.

                                 

                                Can you walk me through that one?

                                 

                                Thanks in advance!

                                 

                                edit:  I've noticed the tooltip displays DAY of FILLDATE as some point in the past.  It's still calculating average days between visits, not just time between two points?

                                • 13. Re: Average duration in days between website visits
                                  Andrew Ball

                                  Hi Neal,

                                   

                                  I've made a few changes to the workbook and added in the test of whether a customer may be leaving.

                                   

                                  Change made:

                                  • I simplified and renamed the day diff calc so it is just one table calc
                                  • I modified all table calcs that used FIRST() to instead use LAST() - reason for this is so that it now keeps the latest date, so you also have the day of previous visit in the tooltip (addresses your last point). Additionally, I needed this to do the comparison against the latest date.
                                  • I added a calculated field to show the days since last visit. I made it into a table calc as I needed to use DAY(Date) - adding this dimension normally splits the bar into as many sections as there are unique dates for that person, so the table calc keeps only the latest
                                  • Added a test between the two dates, and colour coded the chart.
                                  • Made sure all table calcs are using username and date and restarting every username

                                   

                                  I think that's about all. Hope it helps

                                   

                                  Andrew

                                  • 14. Re: Average duration in days between website visits
                                    Neal Smoller

                                    Hey Andrew Ball

                                     

                                    I wanted to update this.  I can't get this to work.  I'm wondering if I could send you privately my data and take a look at it.

                                     

                                    My current issue is just with "Days since last visit".

                                     

                                    Some of the patients are working, some are not.  Some are showing their max date as a date in the past.  I've made sure the data is in ascending order by that date field.  i even tried your date order piece.

                                     

                                    The customers in question, i've done a max([InvDate]) on a worksheet and it returns the correct value (7/15/13).  On the table calc, it shows a date way in the past (3/22/13).

                                     

                                    i dont know if this needs updating for tableau 8 or not.

                                     

                                    Thanks in advance

                                    1 2 Previous Next