Google Workspace (formerly G Suite) is a set of services provided by Google that includes Gmail, Google Drive, Calendar, Meet, Docs, Voice, and more. As a Google Workspace reseller, we are often asked for additional solutions on top of these offerings. Recently a client asked us to design a dashboard that displays high-level information about their employees' usage of select Workspace services.
Owning the Data
Google Workspace Organization administrators have the ability to enable the export of Workspace data to BigQuery. Turning the export on is a straightforward process that includes creating or using an existing Google Cloud Platform (GCP) project and setting it as a destination for the export. The export can be set up within the Reports page of the Google Admin console (detailed instructions). Once enabled, you should start seeing continuous export of the previous day's data in BigQuery.
Data Schema
The export flows into two date-partitioned tables in the selected dataset: a larger log-level activity table and an aggregated usage table.
We'll focus on the activity table which consists of attributes generic to all events and service-specific attributes that are grouped in records (voice, token, admin, login).
The attributes record type, event type and name provide a high-level description of the user's (identified by the email) activity. Record type will relate to the Workspace service, while event type and name will describe the user's action (such as creating a new calendar event).
Service-specific attributes will have a detailed description of the activity that occurred. Below are some dimensions associated with "calendar" events and an example of a query with the returned values.
Make sure to use the _PARTITIONTIME if possible, which will improve the cost and performance of the query.
[sourcecode lang="sql"] SELECT time_usec, domain_name, email, group_id, org_unit_name_path, ip_address, event_type, event_name, record_type, calendar FROM `project-id.workspace.activity` WHERE DATE(_PARTITIONTIME) = "2021-07-27" AND email = "my-email@adswerve.com" AND record_type = "calendar" [/sourcecode]
Note: Here's additional information about activity and usage tables.
While working on these solutions we noticed that not all the data you may expect is available in the exports. We often get requests to aggregate or filter workspace data by organization groups. You can find this and other data using Google's API services like Directory API (https://developers.google.com/admin-sdk/directory/v1/guides/manage-groups).
In our case, we set up an import of organization groups and members to BigQuery via a Cloud Function. To handle the authentication smoothly, we suggest adding Cloud Function's service account to the Workspace.
Visualizing
We connected our BigQuery export data to Data Studio to visualize incoming and outgoing calls and messages via Google Voice. This connection allowed for a better understanding of different metrics surrounding the call and message volume (by a user, time of day, group, etc.).
Since DataStudio reports can be shared without the users’ access to the underlying data, we are able to share only a portion of the actual data with the end-user.
Opportunities
The example above is a small fragment of everything possible with the Workspace export. You can use the export to answer a wider variety of other questions about user behavior and resource usage such as:
- What is the most popular meeting time? (Calendar)
- How many new documents do our users create per day? (Drive/doc)
- What is the ratio of internal vs external meetings? (Meet)
- What are the most frequently accessed dashboards? (Data Studio)
- Detecting anomalies around user password reset, change (Login)
To learn more about Google Workspace or for additional information please reach out to us using our contact us form or tag us on Twitter @lukaslo and @adswerveinc.