Converting Report Builder time-based metrics in Excel

[scareywa]

I am having trouble with my time-based metrics (avg. time spent) when I pull it in using Report Builder. Excel seems to take it out of the HH:MM:SS format and makes it a number. So when I run a report with the Avg Time Spent metric in the Adobe Interface, the correct format is displayed but when I run it with Report Builder a random number is displayed. I can't seem to convert it to the correct time. Can anyone help?

[brian_au]

Hi,

I believe in Report Builder average time spent is expressed in minutes or fractions thereof (Help Link below). It is not random, but rather an aggregation depending on your date range selection for the given data block. So therefore you can multiple the base minute number by 60 to produce seconds.

Last step is then in Excel to express as HH:MM:SS you can convert via formula where X is cell reference =X/(1440*60) and then apply a custom Excel format of [h]:mm:ss

http://microsite.omniture.com/t2/kb/en_US/analytics/kb/average-time-spent-on-site.html

Average Time Spent on Site is the amount of time (in minutes) users have spent on your site across all visits. Though Average Time Spent on Site is not its own metric report in Adobe Analytics, this information is still available through several capabilities.

Method 1 - Use the report builder capability

The report builder Excel add-in can retrieve Average Time Spent on Site aggregated across a date ranged, or trended over time. If graphing is required, use Microsoft Office's Charting Tools.

1. Open Microsoft Excel and log in to report builder.
2. Click Create under the Add-ins tab.
3. Select the correct report suite, date range, and granularity.
4. Ensure that the report is set to Site Metrics > Traffic Report. Click Next.
5. Drag the Average Time Spent on Site metric to the Metrics canvas.
6. Select the insert location, then click Finish. Average Time Spent on Site is shown in minutes.
7. Use Excel's charting tools to create the graph, if desired.
   Note: Adobe Marketing Cloud Customer Care does not support Excel's charting tools. If you require assistance on these tools, contact Microsoft support.

Best,

Brian

[scareywa · Accepted solution]

Thank you! I was missing the formula =X/(1440*60)

This worked perfectly!  

Scott