Converting Report Builder time-based metrics in Excel | Community
Skip to main content
S
scareywa
October 16, 2015
Solved

Converting Report Builder time-based metrics in Excel

  • October 16, 2015
  • 2 replies
  • 2364 views

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?

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
    Best answer by scareywa

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

    This worked perfectly!  

     

    Scott 

    2 replies

    B
    Adobe Employee
    BrianAu1Adobe Employee
    Adobe Employee
    October 16, 2015

    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

      S
      scareywaAuthorAccepted solution
      October 16, 2015

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

      This worked perfectly!  

       

      Scott 

      Terms & ConditionsAccessibility statement

      Loading footer...