I'm trying to come up with a solution that essentially boils down to visualising sales data, across many depts, in many stores, in many areas. The most significant challenge is a way to conceptualise/store this data in a meaningful way.
- 8 stores per area
- 3 areas per region
- 3 regions nationally
- 50 micro departments per store (think “chocolate”, “confectionary”, “headphones”) that belong to:
- 8 major departments (e.g. “chocolate” and “confectionary” belong to “grocery”, “headphones” and “CDs” belong to “entertainment”) that, in turn, belong to:
- 2 department divisions (each division would hold 4 of the major depts)
Sales data is generated on a weekly basis and then we aggregate it how we see fit. Each week a new spreadsheet is created and each store has three columns devoted to their store, which breaks down each micro department, the sales this year, the sales last year (and then we look at % gains/losses).
So what I'm trying to do is conceptualise a way to store the data meaningfully (all weeks, all departments, all stores) and then we'll perform some analysis on it later to extract what we need.
My current thinking is:
- each store instance would have a store ID, name, area & region.
- each micro department instance would have an ID, name and the major dept it belongs to
- each week would have a week ID (we use financial weeks) and alternatively a date
- we then create sales instances with a store ID, department ID, sales TY, $ sales LY and a week #
Is there a better way of representing the data or something that I'm missing?