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

[nic_lloyd1]

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?

[Gigazelle · Accepted solution]

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]

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!

[HaiTechEric]

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.

[nic_lloyd]

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?

[HaiTechEric]

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.