How to build a dashboard using data from a CSV file
CSV widgets are one of the more flexible ways of adding proprietary data to your dashboard. This guide will help you understand the key concepts of how to structure your data and push it to Geckoboard reliably using CSV widgets.
Geckoboard integrates with 60+ cloud services out of the box. However, sometimes you may wish to display data from sources Geckoboard doesn’t integrate with, such as your own internal systems or other platforms that don’t have an integration on Geckoboard yet.
In these scenarios our CSV integration sits alongside our Google Sheets and Excel integrations as an easy way to push data to your dashboard.
This guide will run you through some of the scenarios when you might want to consider using this, and will introduce you to how our CSV integration works and provide some example configurations for you to test out.
What is a CSV file?
CSV, standing for Comma-separated values, is a file format that allows tabular data to be stored in a series of plain-text numbers and letters.
Due to its wide adoption and ability to store a large amount of data in a relatively small file size, there is a huge range of uses for data in CSV format, including:
- Exporting large amounts of data from a 3rd party system
- Exporting a spreadsheet of sales data
- Storing large product inventories
- Creating spreadsheets and sharing them from open source software such as Open Office
Unlike Google Sheets or Excel files, CSV files can be updated programmatically relatively easily, making them ideal for pushing data from your own systems.
Getting started with Geckoboard’s CSV integration
Building widgets using a CSV file as a data source is a little different to using one of our other third-party integrations. Unlike other integrations, you will need to find a way to format and host your data in a way that your widgets will understand. Depending on how your data is generated, you may also need to figure out a way to make your CSV file update frequently so you are displaying up-to-data values on your dashboard.
Formatting your CSV file
To push data to a CSV widget on Geckoboard, you must first create a CSV file that acts as your data source. As a minimum, this file should contain at least one column and one row, and should contain at least one number.
In addition, the data in your CSV file must contain only plain letters and numbers - special characters such as currency symbols will not be processed in your CSV widget and may produce an error.
Note - in order to display some visualizations your CSV file may need to contain more than one numerical value. See Widget Examples later in this guide for more information.
There is no hard limit to the number of columns or rows your CSV file can contain, and to make configuring your widget easier, Geckoboard displays the contents of the first row of data as you set your widget up. This makes it possible to use a single CSV file to serve multiple data sets - if you do this, consider using the first row of your CSV file for adding titles to your columns as this will make identifying data easier during setup.
Values in your CSV file can be updated manually or programmatically. If needed, for example, you could update sales figures in your CSV file every 10 minutes with a script that pulls raw data from your internal systems and updates a value in your CSV file.
Bear in mind that however you update your CSV file, in order for your widgets to display correctly, you will need to format your data in columns.
Hosting your CSV file
Once your CSV file comes together, you will next need to host it at a URL Geckoboard can access. This URL will act as the data source you connect to your CSV widget, and depending on how you generate the data in your CSV file, one of the following approaches will work best:
Host on Dropbox (or similar cloud storage): Create a Dropbox account and upload your CSV file. Right-click on the file on your desktop machine or log in to Dropbox.com to generate a Public Link to the file.
This method requires no technical input, allows the file to be updated by other members of your organization to update data and is undoubtedly the quickest way to host a file. However, this approach lacks some of the security options available through other hosting techniques.
Host on web hosting: If you have your own web hosting, upload your CSV file via FTP to create a direct link to the file. If your data updates programmatically, make sure the resulting CSV file is available at a static URL.
Password protect your data: Geckoboard’s CSV widgets support basic auth, meaning that provided this is set up on your server, you can protect your CSV file via a username and password. These credentials are passed along in the URL you provide to Geckoboard to connect your data - along the lines of http://username:firstname.lastname@example.org/filename.csv - offering a basic level of protection to your data.
Pulling CSV data directly from a service: Some third-party platforms offer the ability to export data as a CSV file for use in other systems. In some cases this may work as a way for you to pull data into Geckoboard without having to go through the added step of hosting this data elsewhere, as long as the CSV file is available at a static URL. The success of this method very much depends on how the third-party service formats its data in the CSV file, so feel free to experiment!
Building your CSV widget
Once you have a valid CSV file that’s available at a static URL, the next step is to create a widget to visualize the data within it.
- Log in to Geckoboard as an admin, click ‘Add widget’ and locate ‘CSV’ in the list of integrations.
- Select a widget from the available options.
- Give your widget a label and define its size Paste the link to your CSV file in the field labelled ‘CSV URL’.
- Click anywhere on the page away from this field, and your widget will then attempt to pull data from the URL you provided.
- If your CSV is formatted correctly, your widget should load some additional option fields that will allow you to define the rows and columns data should be pulled from.
- Start by defining which rows and columns you would like your widget to plot by selecting them in the drop-downs that appear.
The widget will pull through the first value it finds for each row and column in your CSV file as a preview. If you have set aside the first row and column in your CSV file to be a descriptive title then this is what will be shown.
After selecting your rows and columns, some widgets will provide further options for configuring the data how you want it.
The options for selecting and visualizing your data that become available to you will vary depending on the widget you have chosen to set up. A simple number widget for example will require you to simply select a column and row from your CSV data, after which the value from that cell is displayed as a number in your widget. More advanced visualizations such as funnels and line charts will require you to select two columns in order for your widget to fetch data to plot the visualization you’ve chosen.
The following examples display some of what's possible with the CSV integration, and all use data from the same CSV file. Download the example CSV file here to try for yourself, or open the data in Google Sheets to see how it has been structured.
Ready to take your learning further? There’s a number of useful resources out there to help explain the hundreds of other ways to get the most out of Google Analytics.
As always, send an email to email@example.com if you have any questions.
Exporting CSV files from Excel: https://www.udemy.com/blog/convert-excel-to-csv-2/
Convert to/from CSV tool: http://www.convertcsv.com/