ga:avgTimeOnPage returning total seconds in wrong format

[Jarrett Moreno]

We're running a report to get us a few metrics (ga:users, ga:pageviews, ga:timeOnPage, ga:avgTimeOnPage) by ga:date (dimension).
 
However, the report is returning the ga:timeOnPage and ga:avgTimeOnPage cells in a weird format. While the number that appears for each day matches the number in Google Analytics, when we try and convert the cell to a Duration format, we get a weird result.
 
For example, the cell that shows up for avg time on page returns 270.415887850467. Google Analytics shows 4 minutes 30 seconds for the same date. However, when we convert the cell with 270.415887850467 to Duration format, the cell shows 6489:58:53.
 
Is this an error? Or is there a work around to get the 270.415887850467 into a hh:mm:ss format?
 
Jarrett

[Philip Walton]

It looks like Sheets considers "1" using the duration format to be one day. So when you convert 270.415887850467 to a duration, it's assuming you mean 270.415887850467 days instead 270.415887850467 seconds.

You could create another sheet and reference the values in the report sheet and divide by 86400 (60 seconds * 60 minutes * 24 hours) to get your value in terms of days, and then Sheets should be able to convert it to a duration as you'd expect.

--
You received this message because you are subscribed to the Google Groups "google-analytics-spreadsheet-add-on" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-analytics-spread...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-analytics-spreadsheet-add-on/3aa09423-c32e-4387-adf1-d0037625c185%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Jarrett Moreno]

    
Philip, follow up q: can we set up a report to give us specific Acquisition paths (i.e. sources and mediums like ga:source=facebook, gs:source=twitter) by date and by page?

On Thursday, April 9, 2015 at 6:11:55 PM UTC-4, Jarrett Moreno wrote:

Amazing, many thanks Philip.

On Thu, Apr 9, 2015 at 6:08 PM, Philip Walton wrote:

It looks like Sheets considers "1" using the duration format to be one day. So when you convert 270.415887850467 to a duration, it's assuming you mean 270.415887850467 days instead 270.415887850467 seconds.

You could create another sheet and reference the values in the report sheet and divide by 86400 (60 seconds * 60 minutes * 24 hours) to get your value in terms of days, and then Sheets should be able to convert it to a duration as you'd expect.

On Thu, Apr 9, 2015 at 11:35 AM, Jarrett Moreno wrote:

We're running a report to get us a few metrics (ga:users, ga:pageviews, ga:timeOnPage, ga:avgTimeOnPage) by ga:date (dimension).
 
However, the report is returning the ga:timeOnPage and ga:avgTimeOnPage cells in a weird format. While the number that appears for each day matches the number in Google Analytics, when we try and convert the cell to a Duration format, we get a weird result.
 
For example, the cell that shows up for avg time on page returns 270.415887850467. Google Analytics shows 4 minutes 30 seconds for the same date. However, when we convert the cell with 270.415887850467 to Duration format, the cell shows 6489:58:53.
 
Is this an error? Or is there a work around to get the 270.415887850467 into a hh:mm:ss format?
 
Jarrett

--
You received this message because you are subscribed to the Google Groups "google-analytics-spreadsheet-add-on" group.

To unsubscribe from this group and stop receiving emails from it, send an email to google-analytics-spreadsheet-add-on+unsubscribe@googlegroups.com.

[Philip Walton]

You can likely do what you want with a segment:

https://developers.google.com/analytics/devguides/reporting/core/v3/segments

[Jarrett Moreno]

Won't using segments limit us to a single Acquisition source? If we want one report with all acquisition sources by date, should we use segments?

For example, we want Users from Facebook, Twitter, Search, Direct, by date. 

Thanks

[Philip Walton]

Honestly, I'm not the best person to answer that question. Since that's primarily related to the Core Report API, you'd probably have better luck asking on Stack Overflow or one of the reporting forums.

Here's some info on the best places to ask questions:

https://developers.google.com/analytics/help/

To unsubscribe from this group and stop receiving emails from it, send an email to google-analytics-spread...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-analytics-spreadsheet-add-on/29ce01b6-2715-4c68-bdb2-576d047e90b5%40googlegroups.com.

[Mike Sullivan]

Jarrett, restrict yourself to 2 dimensions and use a pivot table. You asked for Source, date and Page -- that's 3 dimensions.  You could pivot source+page by date, or as Philip suggested, use segments and create separate pivots for each specific source of interest.