helpneeded2
Board Regular
- Joined
- Jun 25, 2021
- Messages
- 110
- Office Version
- 365
- Platform
- Windows
I started a new position in my company and I am looking into trying to find the most optimal way to analyze and report on data in our database.
We have very limited reporting options for querying data in our database (minimal viable product) is the key phrase my bosses keep using.
Currently, our database collects data on customers. (i.e. which location they frequent, and each purchase or return transaction they make.) We prepare a monthly report in Excel which shows a basic trend analysis of any specific customer which we wish to review.
Our current process is this:
1. Obtain data on all customers (27,000) via a SQL query that outputs into .xlsx format, for a specified period.
2. The above process is done for each period we wish to review: Life Time, 2016, 2017, 2018, 2019, 2020, 2021, 2022 YTD, Last 6 months, Last 3 Months, Last Month.
3. For each of those periods, we copy and paste the data into a tab on a master worksheet.
4. Each tab on the master worksheet then calculates all of our analytics for that period (ranks frequency, top purchasers, etc).
5. Our customer review tab, then allows us to type in a customer number, which will then pull in all the information from all the tabs, and display it on a table on the customer review tab, which displays the stats for each of the periods listed above.
To say that this spreadsheet is slow is an understatement. There is no desire from management to enhance our existing user interface with the database to obtain the information we need to do these monthly reports which are required by management and government regulators.
We do have an option via our application interface to download all the transactional data, which does include the customer number, the transaction type done, and the amount of the transaction.
Questions:
We have very limited reporting options for querying data in our database (minimal viable product) is the key phrase my bosses keep using.
Currently, our database collects data on customers. (i.e. which location they frequent, and each purchase or return transaction they make.) We prepare a monthly report in Excel which shows a basic trend analysis of any specific customer which we wish to review.
Our current process is this:
1. Obtain data on all customers (27,000) via a SQL query that outputs into .xlsx format, for a specified period.
2. The above process is done for each period we wish to review: Life Time, 2016, 2017, 2018, 2019, 2020, 2021, 2022 YTD, Last 6 months, Last 3 Months, Last Month.
3. For each of those periods, we copy and paste the data into a tab on a master worksheet.
4. Each tab on the master worksheet then calculates all of our analytics for that period (ranks frequency, top purchasers, etc).
5. Our customer review tab, then allows us to type in a customer number, which will then pull in all the information from all the tabs, and display it on a table on the customer review tab, which displays the stats for each of the periods listed above.
To say that this spreadsheet is slow is an understatement. There is no desire from management to enhance our existing user interface with the database to obtain the information we need to do these monthly reports which are required by management and government regulators.
We do have an option via our application interface to download all the transactional data, which does include the customer number, the transaction type done, and the amount of the transaction.
Questions:
- I am beginning the early stages of planning a new sheet in which I will dump all 180,000 rows of transactions into a raw data tab, and then I will extract the per period data directly into the customer review tab. Does this seem viable, or will the sheet likely be equally as slow?
- Since we have access to the raw data via SQL, is there any other tools / applications that people would recommend for importing this data into, to build the reports we require? While I have an intermediate level of expertise with Excel, I am unfamiliar if there is a more viable option for creating these types of reports. Any recommendations that people might have would be appreciated.
