How To Establish Self-service Bi System Using Google Sheets and Datawrapper

Written by Islam Salahuddin on December 28, 2021. Posted in Articles, Operational performance

Image Source: Oleg Magni | Pexels

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?”

******

How-to time!

Part 1: Establishing the system

• If you are not using Google Drive for file storage at your organization, you can easily do so by creating an account.

GIF 1: Creating a Google Drive account | Source: Author

• 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.

GIF 2: Adding a folder on Google Drive | Source: Author

• In each folder, upload the relevant data files.

GIF 3: Adding a file on Google Drive | Source: Author

• Give suitable access permissions to your team members to relevant folders.

GIF 4: Sharing Google Drive folder with colleagues | Source: Author

Step 2: Prepare datasets for Google Drive

• 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.

GIF 5: Creating a Datawrapper account | Source: Author

• 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. 

GIF 6: Creating a chart on Datawrapper | Source: Author

• Connect the spreadsheet from your Google Sheets with the chart

GIF 7: Getting a shareable link from Google Drive file | Source: Author

GIF 8: Linking Datawrapper to Google Sheets| Source: Author

• 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.

GIF 9: Publishing a Datawrapper chart | Source: Author

• Create the previous steps to create one chart for each type of data as explained above.

Part 2: Using the system

• 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.

GIF 10: Duplicating a Datawrapper chart | Source: Author

Use 2: Editing or updating existing visualizations

• 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.

GIF 11: Updating a Datawrapper chart | Source: Author

• 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 visualizations

• 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.

GIF 12: Embedding an interactive Datawrapper chart | Source: Author

• 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.

GIF 13: Embedding a Datawrapper chart as an image | Source: Author

Additional tips

1. 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.
2. 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.
3. 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!).
4. 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.
5. 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.
6. 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.

Tags: Business Intelligence, Database, Google Sheets