Why You Should Build A Business Dashboard Using Google Spreadsheets

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”

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

  • Revenue Last 14 Days: How much revenue did all of my products generate?
  • Free Downloads Last 14 Days: How many downloads did all of my free WordPress plugins generate?
  • Conversion Rate Last 14 Days: My site’s Ecommerce conversion rate (from Google Analytics).

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:

  • It allowed me to host my dashboard for free using Google Drive
  • Using Google Apps Script for the logic and Google Spreadsheets for the UI drastically sped up development.

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:

  • Read data from the Settings-tab of our spreadsheet.
  • Get all data from our APIs.
  • Write data to the Dashboard-tab of our spreadsheet.

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

code.gs

Functions:

  • getYesterdayRevenue() – Gets yesterday’s revenue from the Easy Digital Downloads API.
  • getRevenue(days) – Gets last X days of revenue from the Easy Digital Downloads API.
  • getNumOfWPDownloads(days) – Gets number of downloads over the last X days from the WordPress.org API.
  • getEcommConversionRate(days) – Gets the Google Analytics Ecommerce Conversion Rate over the last X days.
  • getCommits(days) – Gets the number of git commits from the GitHub API over the last X days.
  • getAllData() – Reads the configuration from the ‘Settings’ sheet and then calls all of our data collecting 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.

  • getEarlierDate_(days) – Helps with our date calculations. Returns the date X days ago.
  • getEarnings_(startdate,enddate) – Gets the total earnings from the Easy Digital Downloads API for the specified period.
  • getGitRepos_() – Helper function, called from getGitCommits_.Gets all the repos related to the user.
  • getGitCommits_(week) – Gets weekly commit count of all repositories owned by the current githug user.

 

[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:

  • Mailchimp / Campaign Monitor
  • Infusionsoft / Ontraport / Drip
  • Zendesk / SupportBee
  • WooCommerce
  • Stripe / PayPal
  • Kissmetrics / Mixpanel

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.

21 Comments

Julian says:

good post David! I have used pre-build scripts for GA. They moved a lot of that stuff to Google Spreadsheet Addons.

davidhme says:

Thanks for your comment, Julian! I’ll have to check out the Google Spreadsheet Addon at some point.

An idea for tracking developer productivity: instead of commits, track lines of code changed? Obviously not the best metric either but perhaps a bit more insightful than # commits.

davidhme says:

Hey Tom,
Thanks for your comment!

Not sure about the # lines of code changed. This could potentially make a great developer writing elegant code bad while making a developer that writes bloated code good. But it might be more insightful than the # of commits.

I guess another approach could be # of tasks completed (maybe track it using the Trello API?), but then you’d have to make sure that all tasks are of roughly the same size.

-David

You also arent measuring code quality though, just productivity. The % weekly changes would be a good indicator of weekly productivity. Commits are worse than lines of code because you can have a massive update in 1 commit and 3 small tweaks in 3 commits.

If your devs are tracking hours well, that could be a good indicator, though its a very slippery slope.

You could do tasks with pivotal tracker, it allows you to estimate the size of the tasks.

davidhme says:

Great points! I’ll check out pivotal tracker.

richardpatey says:

Solid post David 🙂

I love the idea of using Google Apps for a dashboard, but as a non-programer this seems far from easy. How about a service that helps you set it up?

davidhme says:

Hey Joe,

Good idea! You aren’t the first person suggesting this. Right now I have other priorities (WP plugins), but I’d be happy to put you in touch with someone who could get this done for you.

Cheers,

David

Kristof says:

Was thinking the same thing. Definitely a viable business idea there!

Been doing these KPIs weekly in google spreadsheets – definitely going to work on this – 🙂

davidhme says:

Nice! Supermetrics looks interesting. Did you end up trying it?

connected a few apps with it – but it isn’t showing the numbers the way I want to… need to mess around more

gotta get back to it but worked on this week’s podcast and its answering your questions on HK audits 😉

davidhme says:

Sweet! I was already wondering if speak pipe failed =)

Let me know if you make some more progress on the dashboard.

did you send me a speak pipe?? never got it – nor any others from people… hmmm

davidhme says:

Really? I left one like 2 months ago. Maybe there’s something wrong with your setup?

yup…. sigh…. I can’t even login – maybe I never verified my email…?!?! I have the API key – but I can’t login to a speakpipe account and it says there isn’t an account with this email. Just emailed support…

to followup – got into speakpipe account (email wasn’t verified) and a few other voicemails too! doh! Yours only the first 4 seconds I can hear/download? not sure if its freemium trick… investigating more

Leave a Reply

Your email address will not be published. Required fields are marked *