Recently, I collaborated with two agencies and both were looking for a simple and similar solution:
Automate your monthly and quarterly reporting processes and present data in visually appealing dashboards for your clients.
Both agencies were dealing with similar data challenges, which led me to think that these issues are probably common across many agencies. This prompted me to write this blog, with the goal of sharing useful ideas and offering practical solutions.
1. Agencies were spending too much time compiling reports in Excel.
Sometimes the reports took time days to complete. One agency had to compile reports from multiple global markets, while the other relied on multiple staff members from across the company to update and push data from their systems. If someone was on annual leave, that was simply marked as “TBC” in the reports.
2. Attempting any deeper level analysis proved to be challenging
Problems with Excel stability and scalability:
Both agencies were dealing with large volumes of data, and as many of us know all too well, Excel tends to struggle and fail under these workloads. This frequent freezing and crashing in Excel, particularly during pivot creation, made deeper analysis very cumbersome. Teams often had to force restart Excel, sometimes risking losing their work.
The limitations of Excel visualizations
Excel offers a relatively limited range of visualizations, making it difficult to present data in diverse and insightful ways. While simple visuals are often the best option for final presentations, the exploratory phase requires more advanced visuals to analyze data from multiple perspectives and uncover deeper insights.
Excel offers limited interactivity between visualizations compared to more advanced tools, which provide a more fluid and dynamic experience for data exploration. For example, in a tool like Power BI, you can click on a region within a visual and all related visuals (such as sales trends, customer demographics, or product categories) immediately update to show just the data. relevant to that selection. This level of interactivity is invaluable for uncovering deeper insights and understanding the factors behind changes in data.
The importance of deeper analysis
Deeper analysis is crucial to making the most impactful decisions each month. It's what separates a standard report that simply shows whether numbers are increasing or decreasing month after month from a truly exceptional one, where you can propose proactive solutions, design innovative strategies and uncover untapped opportunities. By investing time in this level of analysis, you not only address immediate concerns but also position yourself as a key partner in your client's long-term growth.
3. Too much time and resources are spent creating visually appealing charts outside of Excel.
Because Excel visualizations tend to look a little clunky, one of the agencies outsourced the creation of polished, branded images to their designer each month. As with most design projects, this involved a lot of discussion about what these new images should look like.
4. Gaps in data skills led to inaccurate reporting
The reports were managed by someone without the experience necessary to fully understand the quirks of Excel, and understandably so, as it was not part of their primary function. As a result, both agencies unknowingly reported incorrect numbers. For example, although the Revenue column was set to “Currency”, entries such as “USD123” and “123” (with a space) were excluded from the total because Excel did not recognize them as valid currency values. While Excel offers a data validation feature to restrict input to decimals or whole numbers, it must be applied manually and many users are unaware of it. In my opinion, Excel should flag these discrepancies by default.
This example is only from one of the clients, since his case was more complete:
mailbox / Excel:
The agency's main Excel file, containing several tabs, was stored in Dropbox to allow global access to team members.
2. Python in deep note:
This is where I spent most of my time, using Python in a Deepnote notebook to thoroughly clean the data and then automate this process each month. Below is a snapshot of a Deepnote Python notebook. I described in the cells the steps I followed to extract, clean and send the data:
For both agencies, I made sure the cleaned data was stored in a database while also returning it to an Excel file in Dropbox for those who wanted to access the data in Excel format. Storing data in a database provides several key benefits, including:
to. Security: Advanced features such as user-based permissions, encryption, and audit trails ensure that sensitive data is protected and access is strictly controlled. Since Power BI does not allow hiding sensitive columns from certain users, I created relevant views within BigQuery to manage privacy, controlling what data is exposed at the dashboard level.
b. Speed: Queries run quickly, even when multiple users access data simultaneously through the dashboard.
do. Scalability: As data grows, the database will handle it seamlessly, avoiding the aforementioned issues both agencies experienced with Excel.
Great time savings
Your monthly and quarterly reports are now automatically updated in minutes, eliminating the time and effort previously spent collecting data manually. Even if someone is on annual leave, the process runs smoothly and without interruptions. The teams are no longer dependent on my contributions, making the entire system completely self-sufficient.
Very happy customers
Both agencies are delighted with the results and use phrases like “amazing” and “I'm obsessed” to describe their clients' new dashboards (sorry to brag, but sometimes it's necessary to brag). While I can't share the actual panels, here's a mockup that looks a lot like one of them:
Users have been trained to perform deeper level analysis.
Dashboards offer advanced, connected visualizations that enable deeper analysis. Fully shareable with the entire team, they allow for more detailed, sector- and team-specific information, allowing everyone to make more informed decisions.
The data is accurate.
Crucially, the numbers are now accurate, free of the quirks and limitations often associated with Excel.
No need to outsource a designer or rely on third-party tools
Stunning brand visualizations can now be created directly in PowerBI and easily integrated into PowerPoint, eliminating the need for designers or external visualization tools.
Agencies now have a better understanding of what is possible with data.
As with all of my clients, I took the time to educate them on the full potential of Excel, Power BI, and Python. Co-piloting with their teams, I helped close the data skills gap, highlighting the quirks of Excel while introducing the power of Python and notebooks to unlock even greater insights.
In conclusionExcel is a fantastic tool up to a point. Like a reliable car, it gets you where you need to go most of the time. But when the road becomes more challenging, sometimes you need a more powerful vehicle to keep moving forward.
As of August 2023, although Excel now integrates Python, it has some limitations, which you can read about here. In my opinion, working with Excel through a Python notebook is much more efficient for data analysis and manipulation.
Interested in learning how your business can benefit from similar automations and dashboards? Please feel free to contact:
https://www.datatatorsolutions.com/
