This article covers how to build a dynamic dashboard in Google Sheets, using formulas and data validation methods so that charts change dynamically based on user input.
This post is based on work I did in 2015 with The Write Life team to develop some behind-the-scenes sales data analysis during their 3-day digital bundle sale. The team wanted a simple way of gauging progress and seeing how the different sales channels fared as the sale took place.
Here’s the final dynamic dashboard:
Click here to access your copy of this template >>
Introduction to this dynamic dashboard
The Write Life (TWL) is an awesomely popular resource site for writers looking for hints, tips, recommendations and inspiration on a variety of topics. (Full disclosure: this website was founded by my wife, Alexis Grant). As part of their ongoing efforts to provide great quality content and products to their audience, The Write Life team put together a bundle of digital writing products, which they offered for sale at an unbelievable low price for 3 days in March of this year. It was a huge success for the team, making $34,000 over the 3 days.
I worked with the TWL team beforehand to set some sales targets that we thought were achievable, based on our knowledge of the different channels (i.e. where the sales would come from) and some calculations on conversion rates. (That’s a topic for another day!)
The sale began on a Monday morning and concluded at midnight on Wednesday, so the team wanted a visual way to gauge progress and focus their marketing efforts during this time.
My solution was to create a dashboard that was dynamic so the team could easily see progress and act accordingly.
Step 1: Remind me again, why are we doing this?
Before diving into the weeds, it’s worth thinking about what insights you’re after. This is a really important first step, as it’ll determine where you go with the analysis.There were several questions we wanted to answer during the sale and then display graphically:
– What is the progress towards the total sales target?
– What is the progress for each of our sales channels?
– What is the proportion of our sales from each channel?
– When are people buying the products?
These would be extremely useful pieces of knowledge for the team, so they could act upon them as the sale progressed, hopefully increasing sales. For example, the TWL team could compare how many sales were coming from their email marketing campaign compared to affiliates or paid advertising.
There are three key pieces to a dynamic dashboard:
1. The raw data
2. An intermediary table that updates based on some parameter, e.g. a user input
3. A chart or series of charts
Step 2: Preparing the raw data
The raw sales data was downloaded from ejunkie (a shopping cart and digital delivery service that TWL used) as a text file. This was easily copy-pasted into a Google spreadsheet.
As a quality control check, after each import of data I ran a quick pivot table report to get an instant summary of the new data. This is a useful step to: 1) check the data you have for any anomalies (such as gaps that suggest it’s incomplete), and 2) to get a “feel” for the data before introducing complexity (this will ensure you understand the data before doing anything too fancy).
The raw data was a big mishmash of people buying in different locations around the world and through different channels, so without some finessing, it wasn’t providing any real insight.
There were, in fact, six different sales channels, including the TWL community itself, affiliates and paid advertising, so it was important to segment the data along the lines of those channels.
I took the following steps to transform the raw data into something more useful:
1. The date and time stamp from ejunkie was split into its component parts and labeled, so I had a day-of-sale category (e.g. Day 1), a separate time stamp and finally, an hour-by-hour category (e.g. 8am to 9am, 9am to 10am, etc.).
2. A timezone based on the buyer’s country was added for each sale and then converted to an offset factor from GMT, so that all the times could then be normalized to the local time in that country and thus comparisons could be made. This was done so we could compare user behavior globally and determine, for example, whether users were buying in greater numbers at 9am local time, shortly after receiving a marketing email.
3. The Sales Channel category was standardized for all the data in the spreadsheet, so every sale was categorized into one of the six sales channels, Channel A through F.
4. The Buyer Country column was standardized into more general categories to facilitate the analysis. The major countries were kept (e.g. United States, Canada, United Kingdom) but the smaller countries with very few sales were grouped into “Rest of the World” to prevent a long tail skewing summary tables and charts.
Now that we had the raw data cleaned, checked (pivot tables) and categorized, it was ready to be summarized into a suitable table format for the dynamic dashboard.
Step 3: Setting up the intermediary tables
In the dashboard, there were two main charts showing sales by channel and sales by local time. So I needed to create dynamic tables for both of these. This was a two-step process ultimately requiring four intermediary tables. Two were summary tables from the raw data and two were the dynamic tables that would underpin the charts.
The sales channel summary table was created using a COUNTIFS formula to automatically pull in sales from the raw data table, using the following formula:
=countifs('Raw Data'!$D$3:$D$438,"<"&$B2,'Raw Data'!$P$3:$P$438,C$1)
The data table, with formulas showing, looked like this:
The second data table, for sales by local time, was set up in the same way.
With this summary set up, the next step was to create a method for capturing the user’s choice. For example, if the user wanted to see only data up to noon on day 2 of the sale, then the chart adjusted accordingly.
Step 4: Using the data validation method
One way to offer a choice to a user in a spreadsheet is by using the data validation method. This creates a nifty drop-down menu from which the user can select a parameter, in this case a sales channel or specific time, and then point the data to this choice so it will update automatically, without needing to write any lines of code. It’s a pretty simple technique but surprisingly powerful.
First, I created two unique lists of choices to present to the user: one for dates and times, the other for sales channels.
Using the Data > Validation feature on the highlighted list of values that I’d just created above, I could then create a user input menu, e.g. for sales channels:
The user is then presented with:
Another table was required to power the sales chart, which changed based on the user’s choice from the drop-down menu discussed above. This table used “index” and “match” formulas to show only the data from the sales table that corresponded to the user’s choice, as follows:
A similar table was created for the time sales data, running off the normalized time summary table.
Step 5: Creating the dynamic dashboard
Finally, the fun stuff! Props to you if you’ve made it this far. This is where the magic happens, where your charts come alive.
For the sales analysis, a stacked bar chart was most appropriate as it allowed the TWL team to see how many sales had been made so far versus how many were still required to hit the target, as follows:
The chart adjusts each time a user picks a new parameter from the drop-down menu, because this choice alters the underlying data.
To complete the dynamic dashboard, I added a sales pie chart (running off the same table as the one above) and a vertical bar chart for the normalized time data.
As final finishing touches, I changed the shading of the bars in the chart to green to match the TWL brand, removed the gridlines, added the TWL logo and presented the dashboard in full screen mode. I also added a running total in the header bar of the dashboard — this was a simple formula linked to the data table, so it updated as a user choose different times to review. The final dashboard looked like this:
While this only took a couple of hours to put together, it came in handy during the sale. We were able to see which channels were sending the most sales in real time, which spurred the team to take specific actions to continue that momentum.
The dynamic dashboard also served as a camaraderie-builder and motivator for the team to rally around — it was energizing to see our progress, particularly once we began to surpass expectations in certain categories. And once we bypassed our sales targets on day 3, it felt like a real win to see the bar on the chart depicting “sales-remaining-to-reach-target” disappear.
Click here to access your copy of this template >>