This is the story of one of our favorite customers, a Marketing Pro at a leading hotel chain who totally revolutionized the way he does data. Six months ago our Marketing Pro was doing business as usual that many of you can relate to. Once a month he would pull data from Google Analytics, internal data tools, and other third party sources. He was all about linked tables and query tables in the veritable Microsoft Access. It was a great solution that worked for a while. But with all things data, it didn't take long to go from simple analytics to very complex. Soon, the workflow went from a couple of hours to a couple of days and eventually a couple of weeks. Eventually settling for dashboards in PowerPivot that were only updated monthly or worst, quarterly, our Marketing Pro became increasingly unhappy.
cause and effect
With each passing day, the effort to pull all of the required data together got increasingly tedious. Our Pro decided it was time to figure out what the heck was going on. Together with his team they analyzed all of their MS Access reports, data sources, and processes. They looked at alternative Visualization tools and considered getting more involvement from their internal data teams to automate more. Here is what they found:
MS access db too big for its BRITCHES
The shared Microsoft Access database was only about 4GB in file size which seemed okay on first glance. Additionally, like all Pros our guy had a powerful Windows laptop where he could run heavy data processes and visualizations quite comfortably. Things got interesting once they looked at all the fun loving linked tables. So easy to link a table, what could possibly go wrong?
Accounting for linked tables, the actual size of data churning through the MS Access database ranged from 15GB to 20GB. You have to give it to them for making it work for so long while operating at the ceiling for this database.
data prep complexity
Blending big data across multiple massive linked tables, complex transformation logic, and complex queries in general were pervasive in their MS Access queries. Even with all that logic in MS Access, our Marketing Pro and team were still spending countless hours in Excel manually massaging the source data.
They were manually extracting domains and parameters from URLs, splitting strings by delimiters, and correcting bad data. Manual and tedious to say the least and we've all been there.
Internal Data Teams
The team here was partially outsourced and partially internal. While there were some areas the internal team could help further automate, it was quickly clear the internal data team is already stretched too thin. It would be unrealistic to expect them to build all of the complex processes into a new system in any reasonable amount of time. In short, at this time this was not an option on the table really.
Alternative Visualization Tools
Let's get a new Visualization tool! Umm no.
Switching to a new Visualization tool like Tableau or QlikView would solve nothing here. Our Marketing Pro and his stakeholders were used to and happy with PowerPivot. A visualization tool is just that, built for delivering data in a beautiful format. Our problem so far is the tediousness and lack of automation in pulling relevant data together.
Here comes the boom
This is where our Marketing Pro meets Vero. I won't say it was love at first sight only because that would be weird, but yeah.
In addition to the Vero Designer, we offer Vero Data Cloud services (on request from customer).
First, we worked with our Marketing Pro to identify data sources and processes that can be automated. Great targets are big data sources like operational databases and other cloud sources. The Vero team built an automated process in a few weeks to automatically download and update data in a custom secure cloud data warehouse we set up for him. Because of our expertise and experience with Amazon RedShift, we chose it for this use case. Additionally, since we were using a scalable cloud platform, we ended up loading historical data that he previously was not able to query efficiently.
Second, working closely with our Pro we found that he consumes different sources of data on an irregular basis. There was no point in automating this since it would be different every time. Here we discovered a new opportunity to build into Vero. Direct data wrangling and uploads into Amazon RedShift. You can read more about it here. Basically, he can now pull down any data in CSV format drop it into Vero, wrangle it, and upload it to his cloud data warehouse managed by us.
Third, we helped him deal with Personally Identifying Information (PII) in a way that would not require a lengthy process with their legal and security teams. Vero is excellent at blending data as you can see from the many demos. In addition to importing data into Amazon RedShift, you can also load data into our Local DB. Here our Marketing Pro decided to only include non-PII data in the cloud data warehouse, while importing PII info into the Vero Local DB. Whenever he needs a report that includes the PII data he will do an on demand blend from the Local DB to the cloud. When Vero blends data it pushes data to your powerful databases, blends the data, then drops any temporary table. Now you can rest reassured that PII data is securely in your network.
Finally, we are starting some new work to repoint PowerPivot away from MS Access and instead point to summary reporting tables in RedShift. These summary tables are built from the Vero Designer itself. Now all of the data preparation, data wrangling, data importing is done with a combination of the Vero Designer and the Vero Data Cloud.
The beauty of the Vero Designer is that it's perhaps easier to use than MS Access but far more capable. Our Marketing Pro did not have to become an expert programmer overnight and he is building layers of reusable components making maintenance easier.
Needless to say, our Marketing Pro went on to live happily ever after. Do you want happiness in your data life? Wouldn't it be better to focus on Marketing rather than data engineering? Here are a few ways you can get started: