2x Productivity for Government Agency

building a bigquery dashboard report

David Krevitt

Lover of laziness, connoisseur of lean-back capitalism. Potentially the #1 user of Google Sheets in the world.

What’s the first word that comes to mind when you think about government?

Probably not innovation.  Probably not ‘Masters of Google Sheets.’

Prepare to have your mind blown.

Here at CIFL, we’ve seen every type of Google Sheets + Google Data Studio implementation under the sun.

But none of them hold a candle to what we with at the Department of Primary Industries, an Australian state agency based in Sydney, New South Wales.

They switched to Google Sheets for project management about a year ago, and have seen their performance as a team (measured in on-time tasks) double. That’s double with a D: from 40% to 80%.

For readers in the United States, the Department of Primary Industries (DPI) is like if the USDA and EPA had a baby.

They manage recreational (fishing + hunting) and agricultural (farming + livestock) projects throughout the state, and a part of their mission is providing advice on environmental assessments for major projects.

We recently spoke with Graeme White, the Manager of Assessments at NSW DPI, to learn how they pulled off this miracle of Google Sheets magic.

The problem

When Graeme took his post at DPI in 2016, he was confronted with a list of 700 assessment projects in a Google Sheet.

He wasn’t sure whether they were on-time or late on any given task.

What gets measured, gets managed – so Graeme knew that basic tracking of project statuses had to be their first priority.

None of the off-the-shelf software tools were suited to their workflow, so they decided to roll their own in Sheets.

The solution

Any useful project management tool does 3 jobs:

  1. Stores a database of tasks and current statuses
  2. Reminds people when their work is due
  3. Reports on team-wide performance

To keep things simple, Graeme and his team split each job into a separate Google Sheets tool.

Taking this microservices approach to rolling out Google Sheets is a *super smart* move – we’ve seen many teams get bogged down in overcomplicated Sheets.

I’d need to write a book to list out all the amazing features we built out, so here’s some of our favorites…

 

Easy usability with conditional formatting

The task tracker came alive with color – so one look at the Sheet would alert you to the problem tasks in the pipeline.

alt text

 

Problem area tabs

The Query function helped build mini reports within the task tracker.

So if you wake up in the mood to put out fires, you know exactly where to look for them.

alt text

 

Calendar invites

The second Sheet of the toolbelt did one simple job: sent Google Calendar invites to team members for upcoming projects.

When they checked out the calendar for a week, they’d know exactly which projects they have due, without having to log into the project management Sheet.

 

Email reminders

Using an Apps Script, summary emails could be scheduled be sent once a week which reminded each team member of the projects they had due in the next 14 days.

Who said redundancy was wasteful?

 

Dashboard reporting in Data Studio

Back in the dark ages, before Google Data Studio launched internationally, Graeme and co. relied on simple Google Sheets Queries to see how they were tracking week-over-week and month-over-month.

They built a handy dropdown report that would pop up reporting snapshots in Sheets:

alt text

But, as soon as Data Studio went live, we took advantage and moved their reporting over to it – making for a much more attractive dashboard:

alt text

That’s right: this government agency was among the earliest adopters of Data Studio.

 

Monthly newsletters

Not everyone on a team _needs_to be looking at reporting every day – but it’s still helpful to be in the loop occasionally.

The solution: a monthly newsletter (sent via Mailchimp), that transparently shows the team where they’re winning and losing.

The results

It’s impressive how all of these seemingly data-related problems were designed around the _people_ on the team.

Since Google Sheets (unlike Excel) is a collaborative tool, migrating to it is not about the tool itself.

It’s about molding the tool to your team’s preferred way of working together.

For Graeme’s team at DPI, they molded their switch around:

Their rollout of Sheets was designed up-front to be robust, and outlast any individual member of the team.

Because they know that 1 year, 5 years, 10 years from now, they will very likely be doing the same work.

The government does not go out of business.

And if you take a solid approach to your switch to Google Sheets, you likely won’t either.

Ready for BigQuery?

Get off the ground quickly, with quickstart Recipes for BigQuery.

GET COOKING