Google Spreadsheets isn’t as simple as you think it is, it’s actually a serious Business Intelligence asset. For those of you that may be scoffing, I’m not kidding. According to Google, more than 5 million businesses are already using the recently rebranded Apps for Work.
Google Spreadsheets isn’t as simple as you think it is, it’s actually a serious Business Intelligence asset. For those of you that may be scoffing, I’m not kidding. According to Google, more than 5 million businesses are already using the recently rebranded Apps for Work. Amit Singh, president of Google’s For Work division says that 60% of Fortune 500 companies are “paying, active clients.” Yet Spreadsheets, in comparison to Excel for example, isn’t really seen as more than a tool for sharing and collaboration.
While these features are great, and probably will remain the biggest use cases for the program, it can be used for so much more. Basically, there’s a lot of data collected by programs and software that you can’t analyze because it can’t be read by BI tools. You can take this unstructured data and put it into a Google Spreadsheet to “translate” into a form that’s readable and you can input it into your BI platform. Here’s how.
Making Sense of Unstructured Data
Let’s start with the more manual example of how to use Spreadsheets. Again, it’s important to note that these are advantages Spreadsheets can give you only if you solid business intelligence solution already in place at your organization. What I’ll show you how to do is essentially turn Spreadsheets into a database that holds previously unstructured data – making it readable for your BI software.
A key advantage of using Spreadsheets as a database is its automatic refresh option. This is actually kind of trivial for a program that’s built for multiple users. But it’s a huge advantage for using it as a data source for a BI tool. With various refresh options – from one minute to one hour, you can get near real-time info. Basically you’re creating an accumulative build that refreshes automatically and every update also updates the information in your BI software dashboard.
Webinars
For example our marketing team hosts regular webinars, usually once a week, that leave us with quite a simple report that covers very interesting data: from drop rates, to how long users were listening, to what questions were asked. At the end of each webinar, the diligent Marketing Director manually inputs the information into a spreadsheet. In essence the Google Spreadsheet becomes another database. This can also be done with Excel, but if you want to share or collaborate on document’s, you’re usually left with a problem of multiple copies for multiple users, and a jumble of sometimes contradicting information.
We then connect our sheet to our BI software, which links it to all other webinars we’ve hosted, and suddenly it’s possible to see long-term trends: who gives the most engaging webinars, is there a “time limit” to how long users will stay connected, etc. Instead of standing on its own, we can also connect all the information from each webinar to data stored in our CRM software (currently Salesforce). Suddenly you can do things like connect contact information to people who asked questions, to be able to give them more relevant information.
The input process may also be made simpler for some of you using the GoToWebinar REST API.
ImportJSON
Which brings us to the next set of examples: using API keys of programs or software you use and inputting the data they collect into a Spreadsheet. JSON being one of the most common languages for unstructured data, I’ll show how we use it to automatically input data that can then be connected to our BI tool. You can find a detailed guide on how to do this using ImportJSON at Fast Fedora.
Below is an example of how we use ImportJSON to get information from a meetup group that we run:
A number of our clients that use the mobile ad platform HasOffers also use this ImportJSON trick to get the information straight from the program and usually merge it with other sources to gain insights. This can be done using the HasOffers API.
This technique can also work for platforms that have analytical tools, but ones that aren’t robust enough to give real insights. For example Flurry is a very commonly used mobile analytics program. Problem is, that it doesn’t have ad hoc analytics options, is not connected to other data collecting systems, and only keeps data for rather short time frames. By creating an external Spreadsheets database of all the information collected in Flurry, we are able to get much more in-depth analysis, as well as to merge the data with other relevant sources.
A Little Extra
There’s two more quick ways to make the most of your Spreadsheets that I’d like to introduce to you. The first is for listing and keeping track of online targets. For us this comes into play for the Sales department which creates intricate targets for employees, that are too complicated to keep in Salesforce. For example, weekly targets aren’t possible to track, as well as extenuating circumstances such as time off or sick leave that bear on how a person will perform. In addition, keeping these targets in Spreadsheets makes it much easier to collaborate and share with the whole team.
Another great Spreadsheets feature is simple data enrichment tools such as the mapping options. If you have a list of addresses, you can automatically obtain each address’ longitude and latitude again through the Spreadsheets API. This way you can easily plot all the addresses on a map, without having to manually add each one.
There are loads of other tricks you can use to make Google Spreadsheets work to your advantage, this is just a small example. It’s obvious though that there’s more to Spreadsheets than meets the eye. What are some more creative uses for Spreadsheets that you have? Tell us in the comments below!