Recently, I collaborated with two agencies, both seeking a similar, straightforward solution:
To automate their monthly and quarterly reporting processes and present the data on visually appealing dashboards for their clients.
Both agencies were grappling with similar data challenges, which led me to think these issues are likely common across many agencies. This prompted me to write this blog, aiming to share useful insights and offer practical solutions.
1. The agencies were spending far too long compiling reports into Excel
Reporting sometimes took days to complete. One agency had to gather reports from multiple global markets, while the other relied on several staff members across the business to update and send the data from their systems. If someone was on annual leave, that data point was simply marked as ‘TBC’ in the reports.
2. Attempting any deeper-level analysis proved to be challenging
Issues with Excel’s stability & scalability:
Both agencies were handling large volumes of data, and, as many of us know all too well, Excel has a tendency to struggle and crash under these workloads. This frequent freezing and crashing in Excel, particularly during pivot creation, made deeper analysis very cumbersome. The teams often had to force Excel to restart, sometimes risking the loss of their work.
The limitations of Excel’s visualisations
Excel offers a relatively limited range of visualisations, making it harder to present data in diverse, insightful ways. While simple visuals are often the best choice for final presentations, the exploratory phase demands more advanced visuals to analyse data from multiple perspectives and uncover deeper insights.
Excel offers limited interactivity between visualisations compared to more advanced tools, which provide a more seamless and dynamic experience for data exploration. For instance, in a tool like Power BI, you can click on a region within one visual, and all related visuals (such as sales trends, customer demographics, or product categories) immediately update to display only the relevant data for that selection. This level of interactivity is invaluable for uncovering deeper insights and understanding the factors behind changes in the data.
The importance of Deeper analysis
Deeper analysis is crucial for making the most impactful decisions each month. It’s what separates a standard report that simply shows whether numbers are up or down month-over-month from a truly exceptional one, where you can propose proactive solutions, craft 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. Excessive time and resources spent on creating visually appealing graphs outside of Excel
Because Excel’s visualisations tend to look a bit, well, clunky, one of the agencies outsourced the creation of polished, branded visuals to their designer each month. As with most design projects, this involved a lot of back-and-forth discussions about how these new visuals should look.
4. Data skills gaps led to inaccurate reporting
The reporting was managed by someone without the necessary experience to fully understand Excel’s quirks , and understandably so, as it wasn’t part of their core role. As a result, both agencies unknowingly reported incorrect numbers. For example, even though the Revenue column was set to ‘Currency,’ entries like ‘USD123’ and ‘ 123’ (with a space) were excluded from the total because Excel didn’t recognize them as valid currency values. While Excel does offer a Data Validation feature to restrict entries to decimals or whole numbers, it must be applied manually, and many users aren’t aware of it. In my opinion, Excel should flag these discrepancies by default.
This example is from just one of the clients, as their case was more comprehensive:
Dropbox / Excel:
The agency’s primary Excel file, containing multiple tabs, was stored in Dropbox to allow global access for team members.
2. Python in Deepnote:
This is where I spent the majority of my time, using Python in a Deepnote notebook to thoroughly clean the data and then automate this process every month. Below is a snapshot of a Deepnote Python notebook. I’ve outlined in the cells the steps I took to pull, clean and push the data:
3. BigQuery
For both agencies, I ensured that the cleaned data was stored in a database while also pushing it back to an Excel file in Dropbox for those who would like to access the data in Excel format. Storing the data in a database provides several key advantages, including:
a. Security: Advanced features like user-based permissions, encryption, and audit trails ensure sensitive data is protected and access is tightly controlled. Since Power BI doesn’t allow for hiding sensitive columns from certain users, I created relevant views within BigQuery to manage privacy, controlling which data is exposed at the dashboard level.
b. Speed: Queries run quickly, even with multiple users accessing the data simultaneously via the dashboard.
c. Scalability: As the data grows, the database will handle it seamlessly, avoiding the aforementioned issues both agencies experienced with Excel.
Huge time savings
Their monthly and quarterly reports now refresh automatically in minutes, eliminating the time and effort once spent manually compiling data. Even if someone is on annual leave, the process runs smoothly without disruption. The teams are no longer dependent on my input, making the entire system fully self-sufficient🎉.
Very happy clients
Both agencies are thrilled with the results, using phrases like ‘amazing’ and ‘I’m obsessed’ to describe their clients’ new dashboards (sorry to toot my own horn, but sometimes you’ve just got to). While I can’t share the actual dashboards, here’s a mock-up that closely resembles one of them:
Users have been empowered to perform deeper-level analysis
The dashboards offer advanced, connected visualisations that enable deeper analysis. Fully shareable across the team, they allow for more detailed, sector- and team-specific insights, empowering everyone to make more informed decisions.
Data is accurate
Crucially, the numbers are now accurate, free from the quirks and limitations often associated with Excel.
No need to outsource a designer or rely on third-party tools
Stunning, branded visualisations can now be created directly in PowerBI and easily embedded into PowerPoint, eliminating the need for designers or external visualisation tools.
The agencies are now more savvy about what’s possible with data
As with all my clients, I took the time to educate them on the full potential of Excel, Power BI, and Python. By co-piloting with their teams, I helped close the data skills gap, highlighting Excel’s quirks while introducing the power of Python and notebooks to unlock even greater insights.
In conclusion, Excel 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 gets more challenging, sometimes you need a more powerful vehicle to keep moving forward.
As of August 2023, although Excel now integrates Python, it does come with some limitations, which you can read about here. In my opinion, working with Excel via a Python notebook is far more efficient for analysis and data wrangling.
Interested in learning how your business can benefit from similar automations and dashboarding? Feel free to reach out:
https://www.datagatorsolutions.com/