Just lately, I collaborated with two companies, each looking for the same, simple resolution:
To automate their month-to-month and quarterly reporting processes and current the info on visually interesting dashboards for his or her shoppers.
Each companies had been grappling with related knowledge challenges, which led me to suppose these points are probably frequent throughout many companies. This prompted me to put in writing this weblog, aiming to share helpful insights and supply sensible options.
1. The companies had been spending far too lengthy compiling studies into Excel
Reporting typically took days to finish. One company needed to collect studies from a number of world markets, whereas the opposite relied on a number of employees members throughout the enterprise to replace and ship the info from their techniques. If somebody was on annual depart, that knowledge level was merely marked as ‘TBC’ within the studies.
2. Making an attempt any deeper-level evaluation proved to be difficult
Points with Excel’s stability & scalability:
Each companies had been dealing with massive volumes of information, and, as many people know all too nicely, Excel tends to wrestle and crash underneath these workloads. This frequent freezing and crashing in Excel, notably throughout pivot creation, made deeper evaluation very cumbersome. The groups usually needed to power Excel to restart, typically risking the lack of their work.
The constraints of Excel’s visualisations
Excel gives a comparatively restricted vary of visualisations, making it tougher to current knowledge in numerous, insightful methods. Whereas easy visuals are sometimes your best option for closing displays, the exploratory section calls for extra superior visuals to analyse knowledge from a number of views and uncover deeper insights.
Excel gives restricted interactivity between visualisations in comparison with extra superior instruments, which give a extra seamless and dynamic expertise for knowledge exploration. As an illustration, in a instrument like Energy BI, you may click on on a area inside one visible, and all associated visuals (similar to gross sales developments, buyer demographics, or product classes) instantly replace to show solely the related knowledge for that choice. This stage of interactivity is invaluable for uncovering deeper insights and understanding the elements behind modifications within the knowledge.
The significance of Deeper evaluation
Deeper evaluation is essential for making probably the most impactful choices every month. It’s what separates a regular report that merely reveals whether or not numbers are up or down month-over-month from a really distinctive one, the place you may suggest proactive options, craft revolutionary methods, and uncover untapped alternatives. By investing time on this stage of study, you not solely deal with instant considerations but additionally place your self as a key associate in your shopper’s long-term progress.
3. Extreme time and sources spent on creating visually interesting graphs outdoors of Excel
As a result of Excel’s visualisations are inclined to look a bit, nicely, clunky, one of many companies outsourced the creation of polished, branded visuals to their designer every month. As with most design initiatives, this concerned quite a lot of back-and-forth discussions about how these new visuals ought to look.
4. Information abilities gaps led to inaccurate reporting
The reporting was managed by somebody with out the required expertise to totally perceive Excel’s quirks , and understandably so, because it wasn’t a part of their core position. Consequently, each companies unknowingly reported incorrect numbers. For instance, though the Income column was set to ‘Foreign money,’ entries like ‘USD123’ and ‘ 123’ (with an area) had been excluded from the overall as a result of Excel didn’t acknowledge them as legitimate forex values. Whereas Excel does supply a Information Validation characteristic to limit entries to decimals or entire numbers, it should be utilized manually, and plenty of customers aren’t conscious of it. For my part, Excel ought to flag these discrepancies by default.
This instance is from simply one of many shoppers, as their case was extra complete:
Dropbox / Excel:
The company’s major Excel file, containing a number of tabs, was saved in Dropbox to permit world entry for group members.
2. Python in Deepnote:
That is the place I spent nearly all of my time, utilizing Python in a Deepnote pocket book to completely clear the info after which automate this course of each month. Under is a snapshot of a Deepnote Python pocket book. I’ve outlined within the cells the steps I took to drag, clear and push the info:
3. BigQuery
For each companies, I ensured that the cleaned knowledge was saved in a database whereas additionally pushing it again to an Excel file in Dropbox for many who wish to entry the info in Excel format. Storing the info in a database offers a number of key benefits, together with:
a. Safety: Superior options like user-based permissions, encryption, and audit trails guarantee delicate knowledge is protected and entry is tightly managed. Since Energy BI doesn’t permit for hiding delicate columns from sure customers, I created related views inside BigQuery to handle privateness, controlling which knowledge is uncovered on the dashboard stage.
b. Velocity: Queries run shortly, even with a number of customers accessing the info concurrently through the dashboard.
c. Scalability: As the info grows, the database will deal with it seamlessly, avoiding the aforementioned points each companies skilled with Excel.
Big time financial savings
Their month-to-month and quarterly studies now refresh routinely in minutes, eliminating the effort and time as soon as spent manually compiling knowledge. Even when somebody is on annual depart, the method runs easily with out disruption. The groups are not depending on my enter, making your entire system totally self-sufficient🎉.
Very pleased shoppers
Each companies are thrilled with the outcomes, utilizing phrases like ‘superb’ and ‘I’m obsessed’ to explain their shoppers’ new dashboards (sorry to toot my very own horn, however typically you’ve simply bought to). Whereas I can’t share the precise dashboards, right here’s a mock-up that intently resembles one in all them:
Customers have been empowered to carry out deeper-level evaluation
The dashboards supply superior, linked visualisations that allow deeper evaluation. Absolutely shareable throughout the group, they permit for extra detailed, sector- and team-specific insights, empowering everybody to make extra knowledgeable choices.
Information is correct
Crucially, the numbers are actually correct, free from the quirks and limitations usually related to Excel.
No must outsource a designer or depend on third-party instruments
Beautiful, branded visualisations can now be created instantly in PowerBI and simply embedded into PowerPoint, eliminating the necessity for designers or exterior visualisation instruments.
The companies are actually extra savvy about what’s doable with knowledge
As with all my shoppers, I took the time to teach them on the total potential of Excel, Energy BI, and Python. By co-piloting with their groups, I helped shut the info abilities hole, highlighting Excel’s quirks whereas introducing the facility of Python and notebooks to unlock even better insights.
In conclusion, Excel is a unbelievable instrument up to some extent. Like a dependable automotive, it will get you the place it’s essential to go more often than not. However when the street will get tougher, typically you want a extra highly effective car to maintain transferring ahead.
As of August 2023, though Excel now integrates Python, it does include some limitations, which you’ll be able to examine right here. For my part, working with Excel through a Python pocket book is way extra environment friendly for evaluation and knowledge wrangling.
Involved in studying how your online business can profit from related automations and dashboarding? Be happy to achieve out:
https://www.datagatorsolutions.com/