How To Establish Self-service Bi System Using Google Sheets and Datawrapper
To learn more about implementing a self-service business intelligence system, read the author’s previous article: “Embracing Data Visualization: What Is a Self-service BI System?”
******Google Sheets needs no further introduction, but let me make a reminder that it builds on arguably one of the simplest cloud-based storage solutions, Google Drive. That will help us build a basic data warehouse that will be connected to the visualizations, as explained later in the steps. Datawrapper is also a famous online-based data visualization tool that is commonly used by news organizations, but also can be used by any business, as we will see in the how-to section of the article.
How-to time!The how-to section will be divided into two parts; the first on how to establish the system, and the second on how to use it afterwards. These two will be followed by a final part that includes additional tips to keep the system sustainable and in best shape. So, let’s get started!
Part 1: Establishing the systemStep 1: Create your Google Drive data warehouse
- If you are not using Google Drive for file storage at your organization, you can easily do so by creating an account.
- Create folders to categorize and build a hierarchy for the data files that you’ll add later. For instance, you can create a folder for each division in your company and then subfolders for each team within the divisions.
- In each folder, upload the relevant data files.
- Give suitable access permissions to your team members to relevant folders.
Step 2: Prepare datasets for Google DriveAs we will be connecting the datasets you’ve just uploaded to Datawrapper, we need to structure them in the way that the visualization tool can best read and interpret them. Here are a few things to make sure of:
- There must be only one header row.
- Do not merge cells.
- Eliminate thousand separators as they will be automatically inserted on Datawrapper.
- In the columns of values, do not mix letters with digits (You can add prefixes and suffixes later on Datawrapper, if you need to).
- Inside the tables, do not leave any blank cells:
- If the value is unknown/non applicable, put down a dash “-”.
- If the value is zero, write a zero digit “0”.
- Do not write any notes, source… etc. below the table in the spreadsheet. That can be added later on Datawrapper.
Step 3: Initiate your visualizations catalogue
- Create a Datawrapper account.
- Start creating one type of visualization for each type of data you have. The visualizations you’ll create will serve as templates for your colleagues when they start using the system.
- Connect the spreadsheet from your Google Sheets with the chart
- Proceed with Check & Describe and then Visualize your chart. For the purpose of this article, we cannot delve into all the options in Datawrapper, but it is encouraged that you play around, as the interface is super intuitive, and have a look at this tutorial from Datawrapper Academy. Also see this tutorial on how to choose colors.
- For timeseries data with one or two values, choose a line graph.
- For timeseries data with more than two values, choose an area graph.
- For a simple comparison, like comparing values over a few months, choose grouped column chart.
- For a little bit more complex comparison, choose a single column chart with a filter above.
- Publish your chart.
- Create the previous steps to create one chart for each type of data as explained above.
Part 2: Using the systemUse 1: Creating new visualizations Users in your team will be able to create new visualizations by following the same previous steps that you followed in Part 1, except:
- Instead of creating a new visualization from scratch, they can duplicate an already formatted existing one, and just change the data source.
- Republish the chart to get the changes in the new chart reflected.
Use 2: Editing or updating existing visualizationsUsers can also edit or update an existing visualization
- If editing or updating the data itself is what is needed, you’ll need to edit or update the source data spreadsheet, and then go to the connected chart on Datawrapper to republish it, so it reflects the new changes in the data source.
- If editing or updating the chart itself is what is needed, you can head directly to the chart, make your changes and republish as in the previous steps.
Use 3: Exporting and embedding visualizationsNow, once the visualization is created, it can be exported in both interactive and static forms, to be embedded in web pages, pdf reports… etc.
- To get an interactive version of your chart, you’ll need to get its HTML code and paste it into the editor of your web page.
- To get a static version of your chart, you’ll just need to export it as an image, and then you can use it as any other image.
Additional tipsFor this system to keep working efficiently and with as little flaws as possible, here are a few tips of good practice:
- Make your data folders and files naming systematic and understandable. Then, name the connected chart with the same name of the spreadsheet. That will enable easier reaching for users in the future, using the search function on both tools.
- You can let your team use the same account you created for Datawrapper, but that of course is not advisable. Instead, you can create teams within your account, and invite relevant members through email, assigning specific access authorities (in the same fashion of giving access on Google Drive). For more on this, you can see Datawrapper Academy articles on how to create a team and how to invite others.
- Create a shared guide document with your team containing detailed steps, color codes and all standards for all the uses above (You are also welcome to embed a link to this article as well!).
- As an appendix to that guide, add a part for the technical issues that faced your team when working with the system, and document how you solved them. Keep that part updated with every new issue coming up.
- Assign yourself or one of your team members the responsibility of doing regular checkups on datasheets and newly created or updated charts, to make sure you are avoiding human errors and random technical ones as well.
- Depending on your needs, you might consider paid plans of Google Drive and Datawrapper. Click here and here respectively to see what they have to offer.