Why You Should Build A Business Dashboard Using Google Spreadsheets

David Hehenberger July 21, 2014

A couple weeks ago I decided that it was time to set up a dashboard for my business. Why?
Dashboards are useful for spotting trends and grasping the big picture. I wanted a 40,000ft view of my businesses KPIs on a single screen, instead of having to spend a couple minutes logging into various apps and clicking through reports.

(This dashboard displays dummy data.)

(This dashboard displays dummy data.)

Step 1: Choosing your KPIs

Before building a dashboard you’ll have to decide which KPIs you want to report on. (For more, check out “Dashboard Metrics that Actually Work” from the excellent Segment.io blog.)

I had three goals

Goal 1: Reduce Email

In an effort to reduce the amount of emails I get, I wanted to be stop checking PayPal’s payment notification emails to find out how much money I made.

Metric chosen: “Revenue Yesterday”

Goal 2: Spot Trends

I wanted to be able to spot if my business is growing, stagnating or shrinkinging. I chose the following 3 metrics:

Goal 3: Measure Developer Productivity

The last thing I wanted to keep track off was how much my software developement team accomplished. I chose # of Git Commits Last 2 Weeks.

It’s difficult to measure developer productivity and I was unsure if this would be a good metric. A few weeks in and I’m pretty sure the answer is no, but I’ll leave it in my dashboard for now.

Step 2: Choosing your Reporting Timeframe

My business is small and the number of transactions varies quite a bit throughout the month, so 14 to 30 day reporting timeframes work best for me. (Shorter timeframes are better, but my metrics fluctuate too much to report in 7-day timeframes).

Step 3: Identifying your Data Sources

Your data sources will vary based on which tools and KPIs you use. Here are mine:

  1. Easy Digital Downloads API – I use my shopping cart’s API to report on Revenue Yesterday and Revenue Last 14 Days
  2. WordPress plugin repository API to report on Free Downloads Last 14 Days
  3. Google Analytics API to report on Ecommerce Conversion Rate Last 14 Days
  4. GitHub API to report on Git Commits (All Repos) Last 2 Week(s)

Again, here’s what the dashboard looks like:

(This dashboard displays dummy data.)

(This dashboard displays dummy data.)

Step 4: Choosing the right tools

The two biggest “dashboard as a service” apps I’ve found are Ducksboard and Geckoboard.

Both are great products and fairly affordable (about $20/mo). However, they both didn’t integrate with all of my data sources out of the box and didn’t support some of the metrics I wanted to track. Since I had to build custom integrations anyways I’ve decided to fully go down the DIY route. (The biggest remaining advantage of these services would have been a sexy user interface.)

I’ve previously built some dashboards using Google Spreadsheets and Google Apps Script for some SEO clients of mine. Choosing these Google products was a no-brainer:

Step 5: The Implementation (“The Magic”)

(Want to save time? You can find a Google Spreadsheets template you can use immediately at the bottom of this post.)

5.1: Set up the spreadsheet

Step 1

Create a new Google Spreadsheet, rename your current tab to “Dashboard” and create your dashboard’s interface.
fatcatdash-1

Step 2

Create a new tab (“Settings”) and add all the API information you need.

fatcatdash-2

5.2 Write some code

Confused? You can find a Google Spreadsheets template you can use immediately at the bottom of this post.

Go to Tools -> Script Editor. You’ll have to write some code that does the following:

Here is the code that I’m using. You or your developer can  modify it to fit your purpose.

code.gs

Functions:

[javascript]
// declare variables for this spreadsheet
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheets();
var Sheet1 = ss[0];
var Sheet2 = ss[1];

// declare a couple of variables to store data from each API
var EDD = {};
var WP = {};
var GIT = {};
var ANALYTIC = {};

/**
* EDD API
* Fetches yesterday earnings and
* put the result in Sheet1 ‘B3’ cell
*
*/
function getYesterdayRevenue(){
// Set dates
var startDate = “yesterday”;
var endDate = “yesterday”;

// Retrieve earning data
var totals = getEarnings_(startDate,endDate);

// Ouput data in the Dashboard sheet
Sheet1.getRange(“B3”).setValue(totals);
}

/**
* EDD API
* Fetches last X days earnings and
* compares with the previous period
*/
function getRevenue(days){
// Gets data from the last X days
var startDate = getEarlierDate_(days);
var endDate = “yesterday”;
var totals = getEarnings_(startDate,endDate);

// Gets data from previous period
var startDate = getEarlierDate_(days * 2);
var endDate = getEarlierDate_(days + 1);
var prevData = getEarnings_(startDate,endDate);

// Calculate % increase/decrease
var increase = (totals – prevData) / prevData;

// Ouput data in the Dashboard sheet
Sheet1.getRange(“B6”).setValue(“Revenue Last ” + days + ” Days”);
Sheet1.getRange(“B7”).setValue(totals);
Sheet1.getRange(“B8”).setValue(increase); // multiplied by 100 by cell format
}

/**
* WordPress.org API
* No date range feature, so we have to get all-range data
* and process accordingly.
*/
function getNumOfWPDownloads(days){
// Get 2X results for X days calculation with delta.
var numOfDays = days * 2;
var url = “http://api.wordpress.org/stats/plugin/1.0/downloads.php?slug=” + WP.SLUG +”&limit=” + Number(numOfDays+1);
try{
var response = UrlFetchApp.fetch(url);
var json = JSON.parse(response);
}
catch(e){
Logger.log(“getNumOfWPDownloads: ” + e.message);
}

// Load all 2X days downloads data.
var downloads = [];
for(var i in json) downloads.push( json[i] );

if(numOfDays == downloads.length){

// Load 0 to X days downloads data into prevData.
var prevData = Number(0);
for(var i = 0; i < days; i++) prevData += Number(downloads[i]);

// Load from X+1 to 2X days downloads data into currData.
var currData = Number(0);
for(var i = days; i < numOfDays; i++) currData += Number(downloads[i]);

var increase = (currData – prevData) / prevData;

} else {
currData = 0;
increase = 0;
}

// Ouput data in the Dashboard sheet
Sheet1.getRange(“D2”).setValue(“Free Downloads Last ” + days + ” Days”);
Sheet1.getRange(“D3”).setValue(currData);
Sheet1.getRange(“D4”).setValue(increase); // multiplied by 100 by cell format
}

/**
* Google Analytics API
* Fetches X days ecommerce conversion rate and
* compares with old period
*
*/
function getEcommConversionRate(days){
var tableId = “ga:” + ANALYTIC.PROFILE;
var metrics = ‘ga:transactionsPerSession’;

var startDate = days+’daysAgo’;
var endDate = ‘yesterday’;

// Get current periods data
try{
var response = Analytics.Data.Ga.get(tableId, startDate, endDate, metrics);
} catch(e){
Sheet1.getRange(“D6”).setValue(“Ecomm Conversion Rate Last ” + days + ” Days”);
Sheet1.getRange(“D7”).setValue(0);
Sheet1.getRange(“D8”).setValue(0);
return;
}

var json = JSON.parse(response);
var currData = json.totalsForAllResults[‘ga:transactionsPerSession’];

// Get previous periods data
startDate = (days*2)+’daysAgo’;
endDate = Number(days+1)+’daysAgo’;
response = Analytics.Data.Ga.get(tableId, startDate, endDate, metrics);
json = JSON.parse(response);
var prevData = json.totalsForAllResults[‘ga:transactionsPerSession’];

var increase = (currData – prevData) / prevData;

// Ouput data in the Dashboard sheet
Sheet1.getRange(“D6”).setValue(“Ecomm Conversion Rate Last ” + days + ” Days”);
Sheet1.getRange(“D7”).setValue(currData / 100);
Sheet1.getRange(“D8”).setValue(increase); // multiplied by 100 by cell format
}

/**
* GitHub API
* Fetches commits from all users and all repos
* compares with earlier week
* Note that the GitHub API returns data in “weeks” instead of “days”
*
* @param {Number} Number of days
*/
function getCommits(days){
// Convert days to number of weeks.
var weeks = Math.floor(days / 7);
var i = 52 – weeks * 2;

// Get the same number of previous weeks data
var prevData = 0;
for(var len = 52 – weeks; i < len; i++)
prevData += getGitCommits_(i);

// Get current weeks data
var currData = 0;
for(; i < 52; i++)
currData += getGitCommits_(i);

var increase = (currData – prevData) / prevData;

// Ouput data in the Dashboard sheet
Sheet1.getRange(“F6”).setValue(“Git Commits (All Repos) Last ” + weeks + ” Week(s)”);
Sheet1.getRange(“F7”).setValue(currData);
Sheet1.getRange(“F8”).setValue(increase); // multiplied by 100 by cell format
}

/**
* MAIN FUNCTION
* Reads the configuration from the ‘Settings’ sheet and then
* calls all of our data collecting functions.
*
*/
function getAllData(){
// Populate global objects/constants from Settings sheet.
var days = Sheet2.getRange(“B2”).getValue();
EDD.URL = Sheet2.getRange(“B5”).getValue() + “edd-api/stats/”;
EDD.KEY = Sheet2.getRange(“B6”).getValue();
EDD.TOKEN = Sheet2.getRange(“B7”).getValue();
WP.SLUG = Sheet2.getRange(“B10”).getValue();
ANALYTIC.PROFILE = Sheet2.getRange(“B13”).getValue();
GIT.USER = Sheet2.getRange(“B16”).getValue();
GIT.TOKEN = Sheet2.getRange(“B17”).getValue();

// Call all functions.
getYesterdayRevenue();
getRevenue(days);
getNumOfWPDownloads(days);
getEcommConversionRate(days);
getCommits(days);
}
[/javascript]

lib.gs

We’ve created another file – lib.gs – that contains some helper functions.

 

[javascript]
/**
* Helper function, called from getRevenue()
* Returns the earlier date
*
* @param {Number} days Number of earlier days
* @return {Number} Earlier date
*/
function getEarlierDate_(days){
var date = new Date();
var d = date.getDate() – days;
date.setDate(d);
return Utilities.formatDate(date, “UTC”, “yyyyMMdd”);
}

/**
* Helper function, called from getYesterdayRevenue() and getRevenue()
* Gets the total earnings from fatcatapps.com using the
* Easy Digital Downloads API https://easydigitaldownloads.com/docs/edd-api-reference/
*
* @param {Number} days Number of earlier days
* @return {Number} Total sales
*/
function getEarnings_(startdate,enddate){
var url = EDD.URL;
var params = {
method:”post”,
payload : {
key: EDD.KEY, // API key
token: EDD.TOKEN,
type: “earnings”,
date: “range”,
startdate: startdate,
enddate: enddate
}
}
try{
var response = UrlFetchApp.fetch(url, params);
var json = JSON.parse(response);
} catch(e){
return 0;
}
return json.totals;
}

/**
* Helper function, called from getGitCommits_
* Gets all the repos related to the user.
*
* @return {Array} repo names.
*/
function getGitRepos_(){
var url = “https://api.github.com/user/repos?access_token=” + GIT.TOKEN;

try{
var resp = UrlFetchApp.fetch(url);
} catch(e){
return [];
};
var respCode = resp.getResponseCode();
while(respCode == 202){
Utilities.sleep(5000);
resp = UrlFetchApp.fetch(url);
respCode = resp.getResponseCode();
}
var json = JSON.parse(resp);
var repos = [];
for (var i in json) repos.push( json[i].name );

return repos;
}

/**
* Gets weekly commit count of all repositories owned by the current githug user.
*
* @param {Number} Range from 0 to 51.
* @return {Number} Number commits for all users in all repos.
*/
function getGitCommits_(week){
var repos = getGitRepos_();
var commits = 0;
for (var i in repos ){
var url = “https://api.github.com/repos/” + GIT.USER + “/” + repos[i] + “/stats/participation?access_token=” + GIT.TOKEN;
try{
var resp = UrlFetchApp.fetch(url);
} catch (e){
return [0];
}
var respCode = resp.getResponseCode();
while(respCode == 202){
// Wait until Git api prepares and catches all results.
// This status indicated by return code 202.
Utilities.sleep(5000);
resp = UrlFetchApp.fetch(url);
respCode = resp.getResponseCode();
}
var json = JSON.parse(resp);
commits += json.all[week];
}
return commits;
}

[/javascript]

5.3: Set up a trigger

To test if everything worked, go to Run -> getAllData in the script editor. If this is the first time you run this script you’ll have to authorize this script to access your Google Analytics account and modify your spreadsheet.

Next you want to set up a trigger that automatically runs your script periodically. In the script editor, go to Resources -> Current Project’s Triggers.

I’ve set up a time-driven Trigger that runs getAllData() every two hours.

dashboard-trigger

Done!

We’re done. See how easy it is to build a custom business dashboard using Google Spreadsheets?

I realize that my use case (using Easy Digital Downloads, GitHub + WordPress.org) might be very specific. However, with a little bit of development you could display any kind of data you wanted, such as data from:

I want to re-iterate that screenshots used in this post don’t display our actual revenue. I’m not starting to post income reports. =)

The possibilities are endless.

Thoughts? I’d love to hear from you if you have any questions or have built dashboards in the past – simply leave a comment below.

You may be interested
in these articles