2 Replies Latest reply on Feb 2, 2017 2:35 PM by Michelle Kosmicki

    DateTime Calculations and Google Analytics

    Michelle Kosmicki

      To be frank, I don't know if this is a Google Analytics issue or a Tableau issue.

       

      I pull "Time on Site" so that I can calculate the Avg time on site.  Every month I am forced to re-do the average time on site calculations for a set of dashboards I have that use Google Analytics data.

       

      It's not that they disappear, but that the date time field seems to change. I'm using the same data pull and append process every month. I'm not changing any date time formats. But it seems the format changes each time...forcing me to redo the calculations.

       

      Has anyone else had this happen?

       

      I have other dashboards that pull "Avg time on site" and calculate the "Avg Time On Site" from that field and it never changes. (Yes I know that taking an average of an average isn't good practice. The things we inherit!)

       

      Thoughts?

       

      Michelle

        • 1. Re: DateTime Calculations and Google Analytics
          Patrick A Van Der Hyde

          Hello Michelle, 

           

          Could you share the field name specifically and how the format changes?  I would like to reproduce the behavior so as much information as possible is helpful.  One thought I had is that you mention that you inherited this dashboard.  You might try setting up a new dashboard on GA that mimics what was already built and see how that goes in comparison.  I have found that a few of my older dashboards on GA data had to be rebuilt due to big changes on the GA side.  After building a fresh data connection and using the fields available, the reports ran faster and have been fine ever since.   I do not use the time on site measure though so I am not specifically familiar with changes to that one. 

           

          Patrick 

          • 2. Re: DateTime Calculations and Google Analytics
            Michelle Kosmicki

            Patrick A Van Der Hyde

             

            This is a dashboard I built. The dashboards with "Avg Time on Site" were built by a predecessor. Maybe that is why she used [Avg Time on Site].

             

            The field name I use is [Total time on site (h:m)].  It is a date time data type, just like [Avg Time on Site], but it requires different handling.

             

            Step 1: Convert [Total time on site (h:m)] to a string field.

            Step 2: Using trim and split functions, separate out the hours, minutes, and seconds.

            Step 3: Convert everything to seconds and add the three together.

            Step 4: Calculate the average seconds. Format to h:mm:ss.

             

            The first time, I had a date time field like this one: 1899/12/31 12:00:00 AM

            The second time, I had a date time field like this one: 1899-12-31 12:00:00AM

            The third time, I had a date time field like this one: 12:00:00 AM

             

            The data pull is using the same tool each time (SuperMetrics Data Grabber).

            The data is stored in Access (storing it in Excel or SQL Server made no difference).

             

            I'm tempted to pull all the data again using [Avg Time on Site] since that might be an easy way out. But I can't just let this go and do the simple thing yet. ;-)  I want to know why it's happening.

             

            Michelle