Photo by Lukas Blazek on Unsplash

Google Data Studio for Employee Attendance Management & Other Metrics

Imagine this. You ask your HR Associate for an overview of an employee’s contribution . This could be in the form of working hours coupled with their daily reports to their team leader.

She comes back 2 hours later with the report.

You ask her, “What took you so long?”

She informs you that she was compiling the report from multiple sources.

But should it really take this much time?

Intro

This is a Business Intelligence project for HR. The goal of the project was to retrieve quick insights on:

  • each employee’s attendance,
  • their contribution towards reporting practices, and
  • their current standing according to the performance management system in place.

By the end of the project, HR reported that it had become convenient to visualize the productivity of each employee any time without any delay. It helped cut down the time taken for some processes by as much as 95%.

Objective

The employer felt that key insights into each employee’s contribution & productivity should be quick to retrieve.

But contribution and productivity were not exactly measured in this organization. Yet they were already collecting some form of data daily: it was all the attendance-related data as well as StandUps and their votes.

Furthermore, the management had already stated that they wanted to see a few basic pieces of info visualized for a chosen period, such as:

  • Number of hours worked each working day
  • Average Number of Hours worked
  • Number of Total Absences and Presences
  • Number of StandUps Participated In/Submitted and Missed
  • Performance Rating over time
  • Specific details of each StandUp submitted

The employer used this data whenever they needed to audit an employee’s performance. Yet compiling the data of each employee — spanning over a long period — usually took hours.

Thus, we decided to visualize it all — plus a few other metrics.

But What Exactly Are Standups?

In this particular organization, each employee is required to submit a concise report on Slack at the beginning of their day. In this report, they write about what they plan on working on for that day and which tasks they had completed during the last working day. This report is called a StandUp.

Then the StandUp, which is automatically posted on one of the company’s Slack channels, is reacted/voted on by each respective employee’s team leader. A reaction/vote could either be positive, negative, or neutral.

Understanding the Data

One of the company’s programmers had already developed a program on Google Apps Script for the attendance system. It collected all the data of each day’s Attendance and StandUps submitted.

The only data it didn’t store was Performance Rating scores. The HR maintained this data on one of the Trello Boards of the company. So we had to figure out:

  • how to extract each employee’s performance score daily,
  • save it an appropriate file, and
  • automate this process.

The Whole System

One of the Developers had created a Slack App specifically for the company’s Attendance System. Anyone in the company’s Slack workspace could send it a message and it would behave as programmed by the Developer.

For example, if you sent a message saying “in” it would respond with a button saying “Punch In” as shown below:

A chat showing that a user sent an “in” message to the Slack App and the App responded back with a clickable button which says “Punch In”

If you click on that button at 4:32 pm on 10th December 2020, the Attendance Sheet (a sheet in a spreadsheet on Google Drive) of 10th December will get updated with your Time In. In simpler words, it would get recorded in the respective Google Sheet that you started working at 4:32 pm.

This Attendance Sheet of 10th December would be a sheet in a spreadsheet called “Dec” — the abbreviated form of the month. The sheet would be named “10” — the date of the month, as shown below:

And this spreadsheet would be in a folder called “2020” — the year of the Attendance Sheet:

As an employee, you’d “Punch Out” or “Post StandUp” in a similar way. And when your Team Leader reacts/votes on your StandUp it also gets automatically recorded in the respective sheet. We will now refer to a day’s Attendance Sheet as a TimeSheet.

A new TimeSheet is only created whenever the first employee of the day Punches In. Each TimeSheet consists of a table with the following column names:

  • Employee Name
  • Slack ID
  • Time In
  • Time Out
  • Hours Worked
  • Remarks
  • Yesterday’s Tasks
  • Today’s Tasks
  • Manager Rating (Vote/Reaction)
  • Voter(s)

Google Apps Script is used to handle the payload received from the Slack App, read it, transform it, and then finally write it on the respective TimeSheet.

Proposed Solution

After an initial conversation with the management, it seemed they had already decided that they wanted to use Google Data Studio (GDS).

They were also considering Google Charts. However, Google Data Studio was much simpler since it involved dragging and dropping the visuals only. Whereas, Google Charts use HTML to create visuals, which seemed complicated for the simple solution they were looking for.

We decided to go along with their choice of tool but only after testing it appropriately.

Google Data Studio

When the management informed me that they wanted to use Google Data Studio, it was the first time I had heard of it. Some quick Googling helped me understand how Google Data Studio fits in the whole scenario. It was not a bad choice for a small-scale project like this.

But the more I googled the more questions I had. So instead of wasting time browsing forever, I got myself enrolled in Google Analytics Academy’s free course called Introduction to Google Studio. I completed the course in no time:

How Google Data Studio Fits In

To show interactive visuals, we can display them on Reports in Google Data Studio. A report can have one or more pages where each page can be treated like a Dashboard. Each report can be connected with many types of Data Sources such as Google Sheets or even YouTube Analytics data.

The dashboard gets automatically refreshed the moment it is opened. It can also be refreshed manually. Whereas, the data source can be set to automatically refresh every 15 minutes to 12 hours (as shown in the image).

The visuals looked decent and the filters seemed powerful enough for our use case. For example, check out this Sample Dashboard template of Firebase events:

Testing Google Studio

Before we invested ample time and effort on Google Data Studio, we needed to test it out with a subset of the actual data that will be used for the visuals. It’s always a good idea to test the whole or most of the pipeline in bits and pieces before indulging in it completely.

Compiling the Data

To accomplish this, I figured that the most effective way to connect the Data Source(s) would be to ensure that all the data is in one specific sheet of a Google Sheets spreadsheet. So I compiled all the attendance data (from 5th Nov 2020 till 7th Dec 2020) in one sheet and named it “All TimeSheets”. Then I named its parent spreadsheet “GDS Aggregated Data”. Finally, I connected the sheet to a blank report on Google Data Studio.

The only difference between any TimeSheet and this Aggregated sheet was that the latter had one extra column of “DateTime” on the left-side end.

It was also essential that this spreadsheet be updated automatically every single time our Attendance Sheets got edited — but we will come back to this later. For testing purposes, this was not necessary yet.

Pre-processing the Data

Compiling the data was not enough though — as always, the raw data needed to be pre-processed. All the required pre-processing of the data was performed in Google Data Studio’s window of editing Data Sources:

To transform the data into the desirable formats, I had to create many calculated fields. For example:

  • Google Data Studio was reading the “Time In” field as a Text type instead of as a Time or Date & Time type. This was one of the most challenging issues to tackle because Google Data Studio’s own documentation was of not much help in this regard. After quite a lot of Googling, I came across a formula on this page for parsing a Text type field into a Date & Time type. I concatenated the “Time In” field along with the “Date” field and parsed it to get it in a workable format. Here’s the formula:
  • If you convert a field of Date type into a Day of Week type, Google Data Studio will convert it into a number from 0 to 6 representing the days of the week from Sunday to Saturday. But I needed the day of the week in text format such as Sun, Mon, Tue, etc. Therefore, I created a calculated field using the CASE WHEN syntax as shown below:
  • There are times when some employees do not submit the required StandUp report. This needed to be reflected appropriately on the Dashboard. Consequently, I created a field called “StandUp Status” as shown below:

Creating relevant calculated fields was an ongoing process. By the end of the project, I had created and used a total of around 18 distinct calculated fields — some more complicated than others.

Visualizations

Finally, after a few basic fields had been created, I started playing around with the visual elements. With the right choice of fields and parameters, I was able to create my first useful visualization:

And a bit more playing around led to this iteration:

It was still not exactly what was required but it was close. And this concluded our testing phase where we were assured that Google Data Studio can indeed be used easily for this project.

Once it was clear how Google Data Studio worked, our objective solidified a bit more: Create only 1 page with all the relevant visuals displaying data of only 1 chosen employee, which shall be updated automatically regularly.

Cons (but Not Deal-Breakers)

Google Data Studio has its fair share of bugs and cons. However, when something so powerful is free to use, people generally tend to either work around the negatives or ignore them completely. And so did we.

Can Not Zoom In/Out

For a platform that is based around creating attractive visuals, you would expect it to have a feature for zooming in and out. Unfortunately, that is not the case here: there is no such feature in Google Data Studio and even if you try it using your Browser’s zooming feature, it wouldn’t budge.

Fortunately, I had a huge Screen to work with most of the time. Though when it was not available, creating visualizations with perfect boundaries was still difficult but not impossible. I either had to get really close to my laptop’s screen or use a Chrome Extension for magnifying, such as Magnifying Glass (Hover Zoom).

Misalignments

Since there’s no zoom-in feature, aligning objects automatically becomes difficult. Displayed in the image below, Figure A shows 2 objects that are aligned perfectly according to GDS.

To confirm if they were aligned, I decided to draw a vertically straight white dotted line connecting both objects by their edges. As shown in Figure B, we can see that the objects are, in fact, NOT aligned.

The only way to resolve this issue is either to zoom in (which is not yet a feature) or use a large screen to align them manually pixel by pixel.

Buggy Undo/Redo

Sometimes the Undo and Redo feature would not behave the way they usually do. For example, if you move a square object and a line object from one position to another simultaneously and then Undo it, GDS might only undo the position of the box but not the line.

This does not always happen but when it did I had to undo my actions manually.

System Error

This is probably the weirdest bug. Randomly while playing around with a visualization’s settings, I would get the following error:

To get rid of the error I would undo the settings I changed but the error still would not go away. Then I would just refresh the page 2 to 3 times and voila — the error disappears and the visualization returns to normal.

Employee Performance Rating

In the last draft displayed under the Visualizations sub-heading, you can see a Performance Rating line chart near the top right. When I compiled the data for testing, there was no data for it; I had to create a new column full of random numbers just so I could test the visualizations needed.

But we needed the actual data of Performance Ratings along with the Attendance Data. We had to figure out how to extract each employee’s performance rating/score daily from the company’s Trello Board.

Luckily, Trello’s API can help us do just that.

The Attendance System was already built on Google Apps Script using JavaScript. This program communicated with the company’s Slack App using Slack APIs. Thus, creating new scripts/functions for Trello APIs was fairly easy.

We created the following general-purpose function in Apps Script to call any Trello API:

Then we created a very specific function to extract an employee’s current performance rating. But before you see what that function looks like, it would be better to first learn how the company uses Trello to record each employee’s Performance Scores/Ratings:

  • The board is full of lists
  • Each list is named after each employee
  • The first card in each list is the “Balance” card
  • This Balance card states the employee’s current score/rating
  • The score is either updated by each employee’s Team Leader or by an HR executive
  • There are other cards in each list too but they’re not important for our objective
  • Each employee is only assigned one card in the whole Board — and it’s their respective Balance card

Keeping all this information in mind, the function shown below was created to extract any employee’s performance rating at any given time:

Once each employee’s performance score is retrieved, they’re recorded in the respective TimeSheet. A completely different set of functions were used to achieve this.

Automation

We had to implement 2 different types of Automations: one to update each employee’s performance score every hour and another to update our Aggregated Sheet every time a TimeSheet is updated.

Time-based Trigger

Google Apps Script allows us to install multiple types of triggers manually. A time-based trigger is one of them.

We had initially thought that it would have been enough if the performance scores updated daily at the end of the day. However, soon we realized that even if one small aspect went wrong the trigger wouldn’t run properly and, consequently, that specific day’s performance ratings would not get recorded. So we decided to go with an hourly trigger instead.

Event-based Trigger

We can even install event-based triggers using Google Apps Script’s newTrigger() method — but these can only be installed programmatically and not manually like time-based triggers.

Here’s a function we created to install event-based Triggers on Spreadsheets:

So how do we use this exactly?

We programmed the attendance system in a way that whenever a new Spreadsheet is created, an onEdit() trigger is installed programmatically on the Spreadsheet. Then that onEdit() trigger runs every time the Spreadsheet gets edited.

We created the trigger in such a way that whenever a TimeSheet is edited, its date gets recorded in another spreadsheet called the “ChangeLog”.

Every hour, the ChangeLog sheet is scanned once. If a date is mentioned in the ChangeLog sheet, then that date’s TimeSheet’s data instantly gets updated in the GDS Aggregated Datasheet.

Final Look

The dashboard’s outlook was finalized way before the triggers were installed or the ChangeLog sheet was introduced. But as we iterated it till the end of the project, it evolved into something much more insightful than the draft shown before.

Outcome

Time Saved

The company’s HR team reported that the time to get insights on an employee’s productivity came down from 2 to 3 hours to Zero — since it was all available and up-to-date online now.

Better Questions & Insights

Two of the visualizations in the Dashboard helped the company question if StandUp reports are indeed important.

As seen in the image above, the STANDUPS section shows 2 Donut charts:

  1. The left one shows how many StandUps were submitted and/or missed by an employee in a specific time period. It can be seen that this particular employee did not miss a single StandUp
  2. The one on the right shows the Vote/Reaction of the same StandUp reports. In this particular example, out of 20 submissions, the Team Leader only checked 3 reports: only 15%

This raises certain questions: what is the point of submitting StandUps if they are not being checked? Is the team leader not bound to check at least a certain number or percentage of StandUps every month? What could this imply about the Team Leader or this whole practice of submitting StandUps?

Another helpful visualization is the line chart right next to the Donut charts:

This graph displays the number of StandUp reports submitted each month by a particular employee (this employee is not the same as the one from the previous illustration).

This employee had been submitting StandUp reports in the beginning but gradually stopped doing so. It has come to a point where they did not submit a single report during March.

A quick survey about the employee reveals that they are actually one of the most prized employees and the Team Leader is quite happy with their performance. There have been talks of rewarding them with an Employee of the Quarter award for 2021 Q1.

Consequently, this raises a similar question: how is submitting StandUp reports related to productivity or performance if there seems to be no correlation?

StandUp reports do help the management though. However, now they also understand that not submitting StandUp reports is not a sign of a lack of productivity or performance.

An Unexpected Benefit

The HR has to create a Monthly StandUps Summary at the end of every month. They used to do this by copy-pasting the data of all the individual TimeSheets in an Excel Spreadsheet one by one.

The TimeSheets don’t have a date column. So they also had to manually add a date column on the left side of the TimeSheet’s data every time they pasted it on the target Excel spreadsheet.

According to the HR team, this process of copy-pasting and preparing the sheet easily took 2 to 3 hours every month. However, introducing the GDS Aggregated Data sheet completely erased the time it used to take for this process since the data was already prepared in the desired format.

Looking Ahead

After successful completion of the project, the company now intends to visualize more HR data — such as the distribution of skills amongst all employees.

I enjoyed working with the company and am looking forward to working on bigger projects soon.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store