How to pull data from SaaS software, enterprise apps behind a firewall and SQL databases onto every device you own.
Integrations | Feb 3
Vlada Petrović on August 19, 2016 • 4 minute read
Every organization, no matter the size, uses some type of tabular data to keep track of their key metrics. This is the simplest yet most powerful way of condensing a lot of information in one place.
But let’s be honest — when it comes to extracting useful information in the blink of an eye, tabular data is way too complex.
That’s why we use different tools on top of Excel-like spreadsheets to visualize important data hidden inside tables.
In this example, we’ll show you how to use Databox to transform a Google Sheet into an amazing, easy-to-consume dashboard with access to essential data and insights — all right in the palm of your hand. And what’s even better, it’ll automatically update whenever your data in the Google Sheet changes.
For the sake of the example, let’s assume that we run a business selling books, and all the transactions are manually recorded in a Google Sheet just like the one below:
To successfully run and improve their business, any entrepreneur needs to keep a constant eye on certain KPIs, like revenue and whether it’s going up or down. In this example, the bookseller also should know the top traffic source / referrer (for advertising decisions), the bestselling book and their most profitable customers.
The first step is to extract this data from Google Sheets and push it to Databox.
We’ll use Zapier for that.
Zapier is a tool for primarily non-technical users to connect their web apps and services. An integration between two apps is called a Zap. A Zap is made up of a trigger and actions. Whenever the trigger happens in one app, Zapier will automatically perform the actions in another app, in order. In our example, we’ll use Google Sheets as trigger and Databox as an action. Whenever data in the Google Sheet changes, Zapier will make sure these changes are reflected in Databox as well.
Go to Zapier and create a new Zap. You’ll find yourself on the Choose a Trigger App page. Find “Google Sheets” and select it. As the trigger, select “New Spreadsheet Row”. If you’re new to this, here is a good article explaining how to get started with Google Sheets on Zapier.
It is important to remember that if you’re not starting your Google Sheet data from scratch, connecting Zapier to Google Sheet with data will do absolutely nothing, because the action is triggered only when adding a new row.
The best course of action is to create a new worksheet without any data (copy only the header, and add one dummy data row).
Don’t worry, after setting up the Zaps you’ll be able to copy your existing data over and it’ll be pushed to Databox without any problems. You’ll only have to do it once.
Finish up setting up the Trigger part for this Zap and move on to the Action part.
Pick “Databox” as the Action App for your Zap. Choose “Push Custom Data”.
For this step, you’ll need to create a Zapier data connection within Databox. Here is short guide if you don’t know how to create it.
After connecting, you’ll find yourself on the most important step, where we’ll select which columns from our Google Sheet should be forwarded to Databox.
Note to the “Title” field: if we want to push multiple textual columns (attributes) with one Zap, we need to use a special character — the pipe “|” — to separate these values, like shown in the example screenshot.
If we also want Total Revenue that’s unaffected by the other attributes, we’ll need to set up another Zap with identical trigger, and slightly different settings: empty “Title” field, and “Sum Values” set to “yes”. Don’t forget to also change the name of the metric, so it won’t overwrite the previous Zap.
Almost done with this part. The last thing we need to do is to trigger some changes in Google Sheets. Now’s the time to copy all the rows from original worksheet to our empty worksheet that we connected to Zapier. When that’s done, all the data should be already waiting for you in Databox.
Now’s the time to play around! Go to Databox Designer and set up your mobile (Datacard) or big screen dashboard (Datawall) just the way you want. Databox offers more than a dozen visualization options and multiple time intervals for the data, and the dashboards are highly customizable to allow custom titles, colors, branding and many other variations.
But let’s not complicate it — setting up a basic Mobile Datacard is very simple, and here’s a video showing how we did it:
If you have data in Google Sheets as well, I’m sure you’re itching to give it a shot already.
Integrations | Feb 3
Integrations | Jan 19