• Hristo Piyankov

Dashboard gallery (Google Sheets): Simple sales funnel

Updated: Nov 23, 2020

In the “Dashboard gallery” posts, I will be showing you various dashboards (from simple to more complex). Those sections will be mainly focused on showcasing various layouts and functionalities, rather than instructions on how to build the dashboards. In case you are looking for series focusing on step by step building of the dashboards, you can check out Interactive Dashboards – why not do it on your own?

The dashboard below is INTERACTIVE (supposed to be, read “The (very) bad” section for details). If there are display problems, please refresh the page. The dashboard is using CRM sales cycle data.

This dashboard is similar to the one here prepared with Google Data Studio over the same data set. The goal is to highlight the difference between the two approaches.

Sales funnel data in Google Sheets

The source data for the dashboard shown below is as simple as it gets, just 5 columns:

  1. Customer id – a unique identifier for each customer.

  2. Campaign – the date when the customer was added to a campaign.

  3. Call – the date when (if) the customer was called.

  4. Lead – the date when (if) the customer became a lead.

  5. Sale – the date when (if) the bought our product.

Even though the data is quite limited, we can still draw valuable insights from it. It can help us identify our sales funnel and give early warnings in case not everything is going as planned. By design, you can select a focus month, to display only this month’s data. However, the snapshot below is not interactive. The reasons for it are further down.

The (very) bad

As much as I love Google Sheets for all its functionality, it has one major issue when it comes to dashboards. Even though the whole product all about “online” and “collaboration”, there is absolutely no way for me to lock the charts in place. This means that in case I provide access to the file for editing (so you can select the month from the drop-down menu) anyone can move/resize and edit the charts into oblivion (even if I lock all the sheets for editing). And in a tool where there is no easy way to accurately resize and position the charts this is a big issue (I still cannot wrap my head around how easy it is to do so in Google Data Studio and how hard it is here).

Publishing is also a bit of a pain. Google Data Studio / Tableau try to adequately resize/fit the dashboards so they are easy for publishing like here on a web page. It was a cruel and unusual punishment until I managed to size the dashboard above to be somewhat presentable as an embedded document. The code for embedding also lacks some options (height/width/enable editing) which takes some searching to be made available.

Last but definitely not least – no slicers. The fundamental functionality which makes Excel/Tableau/Power BI/Google Data Studio interactive dashboards great is just not present here. Sure there are some workarounds for single filters (like in the dashboard above… which you cannot test because of the security issue). And there are some even more complicated workarounds where I can set up a multi-select functionality via formulas, but it’s just miles away from the real deal.

The good

On the flip side, Google Sheets has a lot more customisation options and charts than Google Data Studio (but still less than Excel/Tableau). Data cleaning/processing is also a lot easier and the available functions are a lot more.

The great

Google Sheets has some very bad features, a few good ones and quite a few great ones. It still has the best collection of formulas for data processing (even compared to Excel). Things like FILTER, QUERY, IMPORTXML, IMPORTHTML give you great flexibility for getting web data and processing it efficiently. Let’s not forget that those formulas are working even when your Spreadsheet is not open thus constantly updating. Add to this the possibility to execute triggered scripts even when your computer is turned off (and thus possibly collecting web data 24/7). Its power to collect and process data is unparalleled.

In conclusion: if you want to build quite flexible/scheduled dashboards for personal use – Google Sheets is a great solution. If you need other people to have access to your dashboards, you might need a solution which offers better abstraction to the data like Tableau or Google Data Studio.

#Automation #Dashboards #Reporting