How to calculate year-to-date unique visitors with report builder? | Community
Skip to main content
nic_lloyd1
nic_lloyd1
October 16, 2015
Solved

How to calculate year-to-date unique visitors with report builder?

  • October 16, 2015
  • 5 replies
  • 2315 views

Using Omniture + Report Builder (Excel) , I'm trying to calculate year-to-date unique visitors on a weekly basis. Right now, this is a manual process:

The "Year" start date is September 30. So to find out YTD unique visitors for each day last week, I calculate unique visitors for September 30 - December 9...then September 30 - December 10...then September 30 - December 11, etc. etc. 

We realize this isn't an ideal way to get this metric, but it's what the client wants. Anyone have a suggestion for a formula in order to automate this through Report Builder?

    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 Gigazelle

    The method you're using isn't bad at all, plus it can be automated.

    What you can do is use requests that reference cells as the date range, and input the following:

    Cell A1: 09/30/2013
    Cell B1: =TODAY()

    Then just take unique visitors from the date in cell A1 to cell B1, and you have the exact numbers you're looking for.

    5 replies

    Gigazelle
    Adobe Employee
    GigazelleAdobe EmployeeAccepted solution
    Adobe Employee
    October 16, 2015

    The method you're using isn't bad at all, plus it can be automated.

    What you can do is use requests that reference cells as the date range, and input the following:

    Cell A1: 09/30/2013
    Cell B1: =TODAY()

    Then just take unique visitors from the date in cell A1 to cell B1, and you have the exact numbers you're looking for.

      nic_lloyd1
      nic_lloyd1Author
      October 16, 2015

      Gigazelle wrote...

      The method you're using isn't bad at all, plus it can be automated.

      What you can do is use requests that reference cells as the date range, and input the following:

      Cell A1: 09/30/2013
      Cell B1: =TODAY()

      Then just take unique visitors from the date in cell A1 to cell B1, and you have the exact numbers you're looking for.

       

      Ah, yes. We tried that method, but for this to work, we'd have to run it every day. But I probably should have mentioned that we only want to run this report once a week on Mondays. We look back at the previous week Monday - Sunday. So for this metric, Monday would be unique visitors September 30 - December 16. Tuesday would be unique visitors September 30 - December 17. Same goes the rest of the week.

      Any insights with this in mind? Thanks!

      H
      HaiTechEric
      Level 3
      October 16, 2015

      You can create customized rolling date range expressions (i.e. "cy+9m-1d") that should cover what you want to achieve except when you enter the new calendar year. Or you can simply extend what @Gigazelle is saying, create a column with the start and end dates for each of your requests, like so:

      A1: 09/30/2013

      A2: =TODAY()-7

      A3: =TODAY()-6

      ... and so on until you have completed your date formulations for the past week. Then, you would create seven requests that would all reference cell A1 as the start date and then A2, A3, etc. for each day of the past week for the end date. Simply do not display the date or metric headers in the output of your request on step 2, and then you can specify the output location to be in the next column over in cells B2, B3, etc.

      N
      nic_lloyd
      October 16, 2015

      That would work, but would require creating 365 X 5 X 3 = 5,475 separate ReportBuilder queries – one for each day for each of our five platforms (Android, iOS, etc.) for the three sheets of data I'm looking to fill out. Thoughts ways to get around this?

      H
      HaiTechEric
      Level 3
      October 16, 2015

      If you are creating the request from a traffic variable, you can request that Yearly Unique Visitors be activated for it and create a single request of daily granularity with that metric, but that metric value is calculated on the calendar year.

      Terms & ConditionsAccessibility statement

      Loading footer...