Case Study: the client has over 700 different product groups and we need an annual report of organic search traffic to each one. Oh, and they use fiscal months, not calendar months. When faced with a challenge like this, you have a choice: should you just brute force your way through it, creating a unique query for each product and fiscal month? Or should you spend the time building a solution that is more automated, using macros and lookup tables to get the job done? You may want to stop and consider a few things before choosing…
Brute Force
With Analytics Edge Quick Queries, you could build this report in a brute force manner — create a sample query, then go to the Query Worksheet and make copies, modify the date range and filter for each, and choose a destination worksheet cell. Copy/paste, repeat, copy/paste, repeat…a little [lot] tedious, but it gets the job done [eventually]. If you are comfortable with search and replace, and you don’t like the thought of ‘programming’, this is a viable solution that is easy to understand.
Aside from the effort required to get the job done in the first place, making changes over time to add or modify a product group can be just as tedious. Inserting or removing rows in the results can require just as much effort as the initial setup. Don’t underestimate this effort needed to change the report in the future.
Old rule-of-thumb: Software maintenance is usually 60% of the total cost.
Initial development is only 40% of the effort.
If you have chosen the brute force approach in the past, don’t feel bad — most people do. But don’t fool yourself: this is the first step to creating what has become known as “Spreadsheet Hell”: a legacy of spreadsheets with tons of queries and formulas that are fine so long as they never break. When they require a change, it can take as much or more effort to fix them as it would to recreate them.
A Programmatic Solution
Avoiding spreadsheet hell usually starts with a move to a more programmatic solution that makes it easier to modify over time. The Analytics Edge Add-in has the ability to build wizard-based ‘macros’ — programming without the code. While many customers create simple macros to get their reports built, the tool can also be used to build some very elaborate solutions. In this case, the best approach would be a loop-in-a-loop:
- for each filter expression:
- get data for each time period
The ‘loops’ would be created using Repeat Macros, and you would pass information from one macro loop to another through the workbook itself. The key to this solution is a ‘template’ worksheet. The template worksheet would hold the results for all time periods for a single product. The inner loop would populate each of the months, then the outer loop would repeat for each of the products.
Using Repeat Macros usually involves 3 distinct steps:
- initial setup for the loop
- repeating the loop
- processing the results