Meetrics

Basic Google Calendar meeting data tracking within a Google organization. This is the codebase behind this blog post which describes how we analyzed meeting data at Storyblocks.

Requirements

• A Google Cloud Service Account
  • Needs the following scopes: calendar.readonly, admin.directory.user.readonly
  • Need a valid service_account_key.json file.
  • May require admin access to your organization's G Suite account.

Setup

Preparing Google Calendar

A "service account" is used to communicate with the Google Calendar API. Their documentation best describes how to do this. The basic steps I took were the following:

1. Create a project in the Google APIs dashboard to hold the service account.
2. Create the service account in the IAM & admin section of your project.
3. Delegate domain-wide authority to the service account.
4. Log into the G Suite Admin section (as an admin user) to authorize the service account for API scopes. For listing calendar information, you need the calendar.readonly scope: https://www.googleapis.com/auth/calendar.readonly. For listing users in the domain, you need the admin.directory.user.readonly scope: https://www.googleapis.com/auth/admin.directory.user.readonly.
5. Go to the credentials page in the Google APIs dashboard for your project, click Create credentials > Service account key, select your service account and JSON as the key type, and click Create. This will download your credentials JSON file containing your private key which will be used in Google API SDKs.

To connect, place your service_account_key.json file inside this application's config/ directory (it is git-ignored).

Lastly, you will need to choose a "subject" to query Google Calendar, which is the email address of a user in your organization who can see the calendars of your users (I used mine). This is set via the google.subject config variable.

Configuration

You MUST put a valid Google service_account_key.json file in the config/ directory.

The following configs are set via environment variables or config/production.yml file.

• GOOGLE_DOMAIN
  • (required) The domain of the Google account.
• GOOGLE_SUBJECT
  • (required) The email of the account to query Google's Calendar API.

Populating the Users Table

I found that a manually-curated whitelist of users was the best way to get accurate data on employees, since there are multiple aliases and miscellaneous emails in any organization. It was also the best way to attach a department to each user.

The setup.PopulateUsersFromCsv function reads in the user-departments.csv file and populates the users table. Edit this file to reflect your organization.

If you do not want to manually enter the user information, the setup.PopulateUsersFromApi method can be used instead, but this does not include department information and may include emails you do not wish to analyze.

Development

# Start mysql using docker-compose and initialize local database.
make init

Verify Configuration

You can verify that the code is able to hit the Google APIs by running the tests for the apis package.

go test ./pkg/apis

Docker

Build and push the docker image. Jenkins pulls the docker image each night and runs the script to populate the database with meeting information.

docker build -t chasdevs/meetrics .

You can run locally from the docker image:

docker run --rm -e DB_HOST="host.docker.internal" -v `pwd`:/app chasdevs/meetrics

Or against prod (must have a compiled image with a production config file):

docker run --rm -e ENV=production chasdevs/meetrics