Capture historical data for KPI dashboard

Brian Hacker

Having real-time data available for our dashboards and reports has improved our management capabilities.  I want to be able to share how we have been doing over time, as well.  I want to capture and report KPIs over time.  I have a report that can capture the information I need but have been trying to figure out how to capture this data on a recurring (e.g. Weekly) basis.  I can send a report as an attachment on a scheduled basis, but then I have to get the data back into the Smartsheet environment.  Anyone have ideas to share?

J. Craig Williams

Brian,

You'll need a third party tool (I prefer Zapier www.zapier.com for low sheet count systems) or the API. Set up a Date column to be used for a trigger (Sunday's usually work) and have it capture the data to another sheet.

I have successfully done this (using Zapier) for burn-down charts.

Craig

Brian Hacker

Thanks for the quick reply, Craig.  I was looking over Zapier and another similar service.  I

I'll follow your suggestion and give it a try.  

-Brian

Brian Hacker

Craig, 

  I've looked at Zapier and Smartsheet.  I didn't see the collection of actions and tests between both tools to get through your suggested recipe.  Can you help me understand which tool and which function you would use for each:   

Trigger on a day of the week?

Add a new row to a sheet?

I assume links (in Smartsheet) are used to fill in the columns.  

thanks, Brian

J. Craig Williams

Brian,

Short version (as I'm behind in other work):

1. Zapier Zap #1: 

A Zap to force updates daily

http://ronin-global.com/2017/03/15/forcing-today-refresh-in-smartsheet/

2. Smartsheet side:

new column for day of week 

=WEEKDAY(TODAY())

3. Smartsheet side

create new sheet for historical capture

4. Zapier Zap #2

trigger on changes to sheet, filter on the ones you want, and action copy row to new sheet (sometimes add row is better)

 

Some known issues:

Zapier (and other 3rd party tools) do not scale well. More sheets needs same functionality points to do-it yourself API work (though I have thoughts on that but no budget to implement)

Zapier may complain at first because so many rows are being (potentially) updated. This is resolved by contacting them with the finished Zap and explaining that the filters you have set up will prevent the Zaps from successfully finishing (the copy/add row action) except for a limited number.

An example, I had setup a recurring task list for a customer with 100 or so tasks, some daily, some weekly, some monthly, etc... 

Zapier was used to create a new instance of the recurring task. Each day, the 100 or so rows would be updated and next instances of a smaller set (less than 10) might get created (passed to a different sheet). But it only worked after confirming with Zapier that I knew what I was doing.

Craig

(that was longer than I expected)

 

 

smartsheet_pgh

You can also just use Zapier's copy row event and schedule it daily.