2x Productivity for Government Agency

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.

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.

 

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.

 

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:

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:

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:

• Reducing technical anxiety through basic Sheets training
• Rolling out a simple, core tool (the project tracker) first
• Reminding everyone to use it in redundant ways (calendar, personal email, newsletter, dashboard reporting)

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.