Excel dashboards have long been a favorite among those not ready for change. We experienced this working with a $400MM+ e-commerce client that was still running its business off two bloated Excel workbooks. These dashboards were only possible because of the feature enhancements that Excel has released over the years that allow it to handle larger datasets and produce better visualizations. I would argue this should not be possible. At the very least, you should not be doing it.
With Excel out, what should you be using? Before we answer that, let’s dive into the specific challenges our client faced as a result of Excel-based reporting.
The Problem
The client’s data existed in Adobe Analytics and was activated with Excel via Adobe’s Report Builder function. ETL was accomplished using PowerPivot, and visualizations were built off the resulting tables in Excel. There were two Excel workbooks with this setup used for weekly e-commerce reporting, each leveraging the same data presented in slightly different ways.
Legacy Excel-based reporting carried several problems, such as:
Formulas would break due to non-flexible cell references from additional data, both historical and new, being brought into the dataset.
Two workbooks for weekly reporting, each for a different audience, meant twice the work and twice the potential for something to go wrong. When one issue was resolved within one workbook, it was a common mistake to forget to update the other.
Excel was failing to scale adequately as the data size in the workbooks grew. This resulted in significantly increased load times and refresh length. (It’s best to take the day off when opening files much larger than 10MB as this often renders your computer useless until it’s finished.)
As the business was growing and evolving, new cuts and views of the data were required. The Excel workbooks proved fragile and difficult to maintain anytime a change was introduced.
So, how do we solve this problem? The answer lies in the cloud. The solution we proposed was to move the two large, cumbersome weekly reports out of Excel and into a new cloud-connected dashboard, built in Tableau powered by AWS.
The Solution
We recreated the weekly Excel reports using Adobe’s Reporting and Data Warehouse APIs, AWS services such as S3, SQS, Lambda, and Athena, to build a series of Tableau dashboards. This final product requires little to no human involvement because it is fully automated and fault-tolerant to handle errors that may occur while running.
An example flow of this is outlined in the visual.
When making the shift from Excel to Tableau, be mindful to not remain shackled to your Excel visualizations. These are different tools with different formatting options. Your time is better spent focusing on leveraging the capabilities of Tableau and evangelizing its benefits. For this client, we emphasized the interactive visualizations Tableau has made possible, instead of the differences in table formatting between Tableau and Excel.
The Outcome
Resilience
The key to improving pipeline resiliency is removing the human element to weekly refreshes. With the data pipeline complete, including our scheduler, there is no longer a need for manual weekly updates; the data extract refreshes automatically at 6 A.M. every day. No manual refreshes mean no opportunity for humans to make a mistake during the refresh.
Time savings
Excel took 1-4 hours to update every week, we have reduced that number to near-zero.
Capabilities
The client is now able to take advantage of the sophisticated capabilities Tableau offers. For example, with one click you can apply several statistical models for more advanced analysis.
With the shift to the cloud, one may be concerned about a cost increase. However, it’s probably not as much as you think. For this client our costs netted out to:
AWS Services: < $5.00/month
Tableau license: $75/user per month; however, the solution we’ve outlined here could be leveraged using other lower-cost visualization tools, such as PowerBI
If you are ready to transition your Legacy Excel reporting, Campfire can help you assess which tools are best for your business and how much investment is required. Reach out to us for more information at info@campfireanalytics.com