Doing periodic reporting always feels like it's going to be easy, just make another copy of everything, change some dates, and run it again! But stakeholders have a way of looking at your hard work, thanking you for (if you're lucky) and asking for exactly the new view that was the hardest to implement. How do you build out a report that won't become tangled mess when all of these requests pile up?
The first thing to understand is that you can't predict the future. You, an analyst, should understand this better than almost anyone in the organization. You spend all day every day trying to help decision makers predict the future, and the impact of their decisions will have on it. You know how many factors can throw off carefully laid plan, and need to have the humility to incorporate flexibility into your own project. The most important thing you can do is go into planning and understanding that things will change. So do it by hand first time, isn't inefficiency -- it's a rational response to the exploratory nature of your first round of reporting. Think of it another way, don't make a huge investment in building out an optimized data pipeline to a data lake until you've manually collected and tested some of its water.
My next piece of advice, after "do it by hand first time" is even more controversial -- do it by hand the SECOND time as well! And third, if you can get away with it. The reason is that your stakeholders don't know exactly what they want until they see it, and it's only when they are regularly taking your results without additional requests that it makes sense to start setting the pipeline in stone.
During these first two rounds by hand will be painful, since at least half of the work will probably be exactly the same. A famous advertising executive once said "half of my money is wasted, I just don't know which half". Your situation is a little bit different: Half of your effort is wasted, and you can't PREDICT which half. Fortunately, once you have a few examples under your belt, you will have a much better understanding of what the stakeholder will need each reporting cycle. That is how your manual slog through the first few iterations will pay off in spades, when you build your automated pipeline right the first time.
Creative Commons Image Attribution: https://www.flickr.com/photos/dicau58/
Read the original version on my blog here