Analytics Weekly Sales / Contributions

Hi David,

What is the metric you’re trying to monitor in Pulse? For example, is the variance between today’s ticket sales and the average daily ticket sales this fiscal week?

Cheers,

Chris

Right now, Marketing produces a spreadsheet with the following metrics: total sales to date, weekly sales, prior week sales, percentage of goal, current year revenue vs prior year revenue, current year seats vs prior year seats. This is done weekly and sent to Senior staff. In trying to replicate this in Analytics, my first task - figure out what the weekly sales are for a given week. Next task, compare to weekly sales from the prior week's sales. Next task, compare to same week sales for prior year. I can kind of do this with a chart by fiscal year week, but they really want to see in quick form the sales for the "current" and "prior" weeks compared to the same week the prior year (or several prior years, depending on what I can figure out). I just can't figure out a simple(ish) way to do this. I'm also concerned about the fiscal year week calculation. I'm not 100% how that is determined. So, for example, our fiscal year starts on Oct 1. Is week 1 Oct 1-Oct 7, week 2 Oct 8-Oct14, etc.? If so, then the week calculation won't work for us because they want a comparable Sunday-Saturday or Monday-Sunday week, not a partial sales week just because of the day of the week a fiscal year starts. I figure this is going to have to be some messy calculations or we would need some kind of modified / custom Batch Period table to figure out the "sales week" for a given date from year to year. I don't have money to spend on the custom work, so I'm hoping with some formula work, I can figure out how to filter sales into a calculated time period, then replicate this for each specific time period I need for the values they want.

Hi David,

To review how the fiscal weeks are coming out for you in Analytics, you can create a pivot table with a combination of Fiscal Year, Fiscal Week of Year, Date and Day of Week Name Sort, for a given date field (eg. Order Date). Filter to one fiscal year and see how that first week is laid out. Then change to the prior fiscal year, and the one before that.

Here's the very short answer to the beginnings of what you're working toward for this week, last week, and this week last year: WeeklySales.dash. It's not pretty, but the formulas are there I think. Now for the long, long version...

Assuming a Widget Filter, or inherited Dashboard Filter for Order Date > Fiscal Current Year Offset = 0 (which limits the results to the current fiscal year). And for the date related fields below, they're coming from the Order Date. Also I'm using "{}" to indicate filtering details within a value formula.

Then, taking a baby step of getting Analytics to filter on the "current fiscal week," it could be as simple as the maximum fiscal week of year that's in the current fiscal year, which a filter already applied to the widget:
MAX([Fiscal Week of Year])

However, if there are future dates, this could return unexpected results. Therefore I'd look for the maximum fiscal week of year filtered by an order date of today (or yesterday):
([Max Fiscal Week Of Year],
    [Days in Date1]{Time Frame = Days = Today})

Then using that, take it a step further to a count of tickets, filtered to the current fiscal week:
(sum([Ticket  Count]),
    [Fiscal Week Of Year1]{Ranking = Top 1 {([Max Fiscal Week Of Year],[Days in Date1]{Time Frame = Days = Today})}})

To extend that to a ticket count of the previous week, it's the same except for the Time Frame on the order date filter at the end. Leaving "Today" selected, click Advanced and change the "offset" value to 7. That's the fiscal week in which the order date 7 days ago falls.
(sum([Ticket Count]),
    [Fiscal Week Of Year1]{Ranking = Top 1 {([Max Fiscal Week Of Year],[Days in Date1]{Time Frame = Days = Today {{
  "last": {
    "count": 1,
    "offset": 7
  }
}}})}})

However that will return the whole of sales in that prior week. If you only want sales to the current day of week in that prior week, then on the initial Ticket Count formula, we would add another order date filter, limiting the tickets counted to those with an order date in a Time Frame of the Last 7 Days, but clicking Advanced, setting again an offset of 7 days.
(sum([Ticket Count]),
    [Fiscal Week Of Year1]{Ranking = Top 1 {([Max Fiscal Week Of Year],[Days in Date1]{Time Frame = Days = Today {{
  "last": {
    "count": 1,
    "offset": 7
  }
}}})}},
    [Days in Date]{Time Frame = Days = Last 7 Days {
  "last": {
    "count": 7,
    "offset": 7
  },
  "custom": true
}})

In other words, if today is Wednesday, the 4th day of the week, then the above will return ticket counts in the fiscal week that contains the order date 7 days prior to today. That'll be say Sunday a week and a half ago through Sunday 4 days ago. But results are limited to tickets that have an order date between 14 and 7 days before today. So that's orders between Thursday 2 weeks ago and last Wednesday. The resulting cross section of those two filters is Sunday a week and a half a go through last Wednesday.

Finally, this week last year. We start with the same formula as we had for tickets in the current week that's listed above:
(sum([Ticket  Count]),
    [Fiscal Week Of Year1]{Ranking = Top 1 {([Max Fiscal Week Of Year],[Days in Date1]{Time Frame = Days = Today})}})

We don't have to lookup the fiscal week from a year ago because we want the same week of year as is the current week of year, we just need an Order Date > Fiscal Current Year Offset filter set to -1, which will over-ride the widget/dashboard level filter.
(sum([Ticket  Count]),
    [Fiscal Current Year Offset]{=-1},
    [Fiscal Week Of Year1]{Ranking = Top 1 {([Max Fiscal Week Of Year],[Days in Date1]{Time Frame = Days = Today})}})

And again, to limit that result to only days prior to the current day of week, add an Order Date filter, but offset 365 days instead of 7.
(sum([Ticket  Count]),
    [Fiscal Current Year Offset]{=-1},
    [Fiscal Week Of Year1]{Ranking = Top 1 {([Max Fiscal Week Of Year],[Days in Date1]{Time Frame = Days = Today})}},
    [Days in Date]{Time Frame = Days = Last 7 Days {
  "last": {
    "count": 7,
    "offset": 365
  },
  "custom": true
}}))

I'll admit, this is not very intuitive, so we have plans to surface some more relative date fields that will make this and other relative reporting easier in the future.

Best,
Chris

Hi David,

To review how the fiscal weeks are coming out for you in Analytics, you can create a pivot table with a combination of Fiscal Year, Fiscal Week of Year, Date and Day of Week Name Sort, for a given date field (eg. Order Date). Filter to one fiscal year and see how that first week is laid out. Then change to the prior fiscal year, and the one before that.

Here's the very short answer to the beginnings of what you're working toward for this week, last week, and this week last year: WeeklySales.dash. It's not pretty, but the formulas are there I think. Now for the long, long version...

Assuming a Widget Filter, or inherited Dashboard Filter for Order Date > Fiscal Current Year Offset = 0 (which limits the results to the current fiscal year). And for the date related fields below, they're coming from the Order Date. Also I'm using "{}" to indicate filtering details within a value formula.

Then, taking a baby step of getting Analytics to filter on the "current fiscal week," it could be as simple as the maximum fiscal week of year that's in the current fiscal year, which a filter already applied to the widget:
MAX([Fiscal Week of Year])

However, if there are future dates, this could return unexpected results. Therefore I'd look for the maximum fiscal week of year filtered by an order date of today (or yesterday):
([Max Fiscal Week Of Year],
    [Days in Date1]{Time Frame = Days = Today})

Then using that, take it a step further to a count of tickets, filtered to the current fiscal week:
(sum([Ticket  Count]),
    [Fiscal Week Of Year1]{Ranking = Top 1 {([Max Fiscal Week Of Year],[Days in Date1]{Time Frame = Days = Today})}})

To extend that to a ticket count of the previous week, it's the same except for the Time Frame on the order date filter at the end. Leaving "Today" selected, click Advanced and change the "offset" value to 7. That's the fiscal week in which the order date 7 days ago falls.
(sum([Ticket Count]),
    [Fiscal Week Of Year1]{Ranking = Top 1 {([Max Fiscal Week Of Year],[Days in Date1]{Time Frame = Days = Today {{
  "last": {
    "count": 1,
    "offset": 7
  }
}}})}})

However that will return the whole of sales in that prior week. If you only want sales to the current day of week in that prior week, then on the initial Ticket Count formula, we would add another order date filter, limiting the tickets counted to those with an order date in a Time Frame of the Last 7 Days, but clicking Advanced, setting again an offset of 7 days.
(sum([Ticket Count]),
    [Fiscal Week Of Year1]{Ranking = Top 1 {([Max Fiscal Week Of Year],[Days in Date1]{Time Frame = Days = Today {{
  "last": {
    "count": 1,
    "offset": 7
  }
}}})}},
    [Days in Date]{Time Frame = Days = Last 7 Days {
  "last": {
    "count": 7,
    "offset": 7
  },
  "custom": true
}})

In other words, if today is Wednesday, the 4th day of the week, then the above will return ticket counts in the fiscal week that contains the order date 7 days prior to today. That'll be say Sunday a week and a half ago through Sunday 4 days ago. But results are limited to tickets that have an order date between 14 and 7 days before today. So that's orders between Thursday 2 weeks ago and last Wednesday. The resulting cross section of those two filters is Sunday a week and a half a go through last Wednesday.

Finally, this week last year. We start with the same formula as we had for tickets in the current week that's listed above:
(sum([Ticket  Count]),
    [Fiscal Week Of Year1]{Ranking = Top 1 {([Max Fiscal Week Of Year],[Days in Date1]{Time Frame = Days = Today})}})

We don't have to lookup the fiscal week from a year ago because we want the same week of year as is the current week of year, we just need an Order Date > Fiscal Current Year Offset filter set to -1, which will over-ride the widget/dashboard level filter.
(sum([Ticket  Count]),
    [Fiscal Current Year Offset]{=-1},
    [Fiscal Week Of Year1]{Ranking = Top 1 {([Max Fiscal Week Of Year],[Days in Date1]{Time Frame = Days = Today})}})

And again, to limit that result to only days prior to the current day of week, add an Order Date filter, but offset 365 days instead of 7.
(sum([Ticket  Count]),
    [Fiscal Current Year Offset]{=-1},
    [Fiscal Week Of Year1]{Ranking = Top 1 {([Max Fiscal Week Of Year],[Days in Date1]{Time Frame = Days = Today})}},
    [Days in Date]{Time Frame = Days = Last 7 Days {
  "last": {
    "count": 7,
    "offset": 365
  },
  "custom": true
}}))

I'll admit, this is not very intuitive, so we have plans to surface some more relative date fields that will make this and other relative reporting easier in the future.

Best,
Chris