Query Workspace Usage Data with BigQuery

Export Google Workspace activity logs to BigQuery and build SQL views.

Introduction

30 Second Summary

Every IT admin wants to know how their team actually uses Google Workspace. Are people logging in regularly? Is Drive storage growing out of control? Without data, you are guessing.

In this project, you will enable the native Google Workspace BigQuery Export and build SQL views that transform raw event logs into a per-user daily metrics summary. The result is a self-updating dataset that any dashboard or AI agent can query directly.

What You'll Build

A fully operational Workspace-to-BigQuery data pipeline with dashboard-ready SQL views on top that run automatically with zero ongoing maintenance.

By the end of this project, you'll have:

  • BigQuery Export enabled and validated in the Admin Console, with Workspace activity and usage data flowing automatically into a BigQuery dataset.
  • A schema exploration workflow you can rerun to discover new metrics as Google adds them to the export.
  • Two dashboard-ready SQL views (v_daily_user_activity and v_user_dashboard) producing per-user daily event counts and storage metrics queryable by any downstream tool.
  • Secret Mission: A scheduled query that materializes your view into a partitioned table for sub-second dashboard reads.

Are there any prerequisites?

You need a Google Workspace account with a supported edition (Enterprise Standard/Plus, Education Standard/Plus, Frontline Standard/Plus, or Enterprise Essentials Plus). You also need a Google Cloud project with billing enabled and the Reports administrator privilege in the Admin Console.

Enable BigQuery Export in the Admin Console

Google Workspace generates a constant stream of activity data. Every login, Drive edit, Calendar update, and Gmail event is recorded. The native BigQuery Export pipes all of this directly into your data warehouse with zero custom code or scheduled jobs.

In this step, you'll enable the export in the Admin Console and confirm that event data starts flowing into a new BigQuery dataset.

In this step, get ready to:
  • Verify your prerequisites are in place.
  • Enable the BigQuery Export in the Admin Console.
  • Confirm activity data starts appearing in BigQuery.
Verify your prerequisites

Before enabling the export, confirm you have the three requirements from the previous step ready to go.

  • Your GCP project has billing enabled (not sandbox mode). Activity log export uses the insertAll API, which is disabled in sandbox mode.
  • The account you're using has the Reports administrator privilege in the Google Workspace Admin Console.
  • You've decided on a dataset name (e.g., workspace_logs) and an optional geographic location.

Why does billing need to be enabled?

Log data is exported through the BigQuery insertAll API. Without billing, your project runs in sandbox mode and activity log data is not exported. Usage report exports still work in sandbox mode, but activity events (the core of this project) require billing.

Enable the export

Now you'll configure the export in the Google Admin Console. This tells Workspace to start streaming activity logs and usage reports into your BigQuery dataset automatically.

  • Open your browser and navigate to admin.google.com.
  • Sign in with the account that has the Reports administrator privilege.
  • Click the Menu icon (hamburger icon in the top left).
  • Click Reporting.
  • Click Data integrations.
  • Point to the BigQuery Export card and click Edit.
  • Check the box labelled Enable Google Workspace data export to Google BigQuery.
  • Under BigQuery project ID, select your GCP project.
  • Under New dataset within project, enter your dataset name (e.g., workspace_logs).
  • (Optional) Check Restrict the dataset to a specific geographic location and select your preferred location.
  • Click Save.

What happens when you click Save?

The Admin Console triggers dataset creation in your GCP project. The service account gapps-reports@system.gserviceaccount.com is automatically added as an editor on the dataset. This service account writes log data and updates the schema on your behalf.

Activity log events start appearing in tables named activity_YYYYMMDD within approximately 10 minutes. Usage data appears the following day in tables named usage_YYYYMMDD.

Can't save the project?

If you get an error when clicking Save, go to the Google Cloud Console, delete the new dataset if it was partially created, then try saving again in the Admin Console.

Also confirm your account has the correct IAM permissions on the GCP project. Help me troubleshoot the BigQuery Export save error in the Admin Console.

Verify export activation

Activity tables typically appear within 10-15 minutes of enabling the export. Let's confirm data is flowing.

  • Open the Google Cloud Console at console.cloud.google.com.
  • In the navigation menu, click BigQuery.
  • In the Explorer panel on the left, expand your project name.
  • Expand the workspace_logs dataset (or whatever name you chose).
  • Look for at least one table starting with activity_.

What should I see?

You should see one or more tables named activity_YYYYMMDD (where YYYYMMDD is today's date or a recent date). These contain the raw event logs from your Workspace domain.

You will not see usage_ tables yet. Usage data appears the following day after enabling the export.

Don't see any tables yet?

If 15 minutes have passed and no activity_ table appears, check the following:

  • Confirm billing is enabled on your GCP project. Without billing, only usage exports work (not activity logs).
  • Make sure you expanded the correct dataset name in the Explorer panel.
  • Try refreshing the BigQuery Explorer panel by clicking the refresh icon next to your project name.

Help me figure out why my BigQuery Export activity tables are not appearing.

Cost awareness

BigQuery charges for storage and queries. The free tier covers the first 1 TiB of queries per month and 10 GB of storage. For a typical Workspace domain running through this project, you will stay well within those limits.

Your data pipeline is live. Activity events are streaming into BigQuery with zero ongoing maintenance. Next up, you'll explore the table schemas to discover what metrics are available for your views.

Explore the Schema

Your BigQuery dataset is live and receiving data from Google Workspace. Before building any views, you need to understand what fields are actually available.

The export populates two table families: activity_ tables (event-level logs) and usage_ tables (per-user daily metrics). In this step, you will run discovery queries against both to see exactly which services, events, and fields are flowing into your dataset.

In this step, get ready to:
  • Discover what event types flow in the activity table.
  • Explore per-user storage metrics in the usage table.
  • Inspect the full schema for future reference.
Discover activity table fields

The activity table contains one row per event. Each row has a record_type (which service generated it), an event_type, and an event_name. Your first query groups these to reveal which services have data flowing.

  • In the Google Cloud Console, navigate to BigQuery using the left navigation menu or the search bar at the top.
  • Click the query editor tab (labeled Untitled query). If you do not see it, click Compose new query to open a new editor tab.
  • Paste the following query into the editor. Replace YOUR_PROJECT with your actual GCP project ID:
-- Discover what record types and events are flowing in activity
SELECT
  record_type,
  event_type,
  event_name,
  COUNT(*) AS event_count
FROM `YOUR_PROJECT.workspace_logs.activity`
WHERE _PARTITIONTIME >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
GROUP BY 1, 2, 3
ORDER BY 4 DESC
LIMIT 50;
  • Click Run in the toolbar above the editor.

What does this query do?

It scans all activity events from the last day and groups them by service (record_type), event classification (event_type), and specific event (event_name).

The _PARTITIONTIME filter restricts the scan to a single day's partition, keeping the query fast and within the free tier.

You should see rows in the Query results pane with values like login, drive, calendar, and gmail in the record_type column.

Seeing zero rows returned?

If you enabled the export less than 10 minutes ago, data may not have arrived yet. Wait a few more minutes and click Run again.

If you still see no results, confirm that you replaced YOUR_PROJECT with your actual GCP project ID. The dataset reference must match exactly: your-project-id.workspace_logs.activity.

Need more help? Why is my activity query returning no rows?

Discover usage table fields

When does usage data appear?

Usage data takes approximately 24 hours to appear after enabling the export. If no usage_ table exists yet in your dataset, skip this substep and revisit it the next day.

The usage table contains per-user rows with storage metrics like Drive and Gmail quota consumption. This query confirms that per-user data is flowing.

  • Clear the query editor, then paste the following query. Replace YOUR_PROJECT with your GCP project ID:
-- Discover per-user usage data
SELECT
  date,
  user_email,
  accounts.drive_used_quota_in_mb,
  accounts.gmail_used_quota_in_mb,
  accounts.is_super_admin
FROM `YOUR_PROJECT.workspace_logs.usage`
WHERE user_email IS NOT NULL AND user_email != ''
ORDER BY date DESC
LIMIT 20;
  • Click Run.

What does this query do?

It pulls the most recent 20 per-user rows, showing each user's Drive storage, Gmail storage, and whether they are a super admin.

The WHERE clause filters out domain-level aggregate rows (which have a blank user_email) and keeps only per-user data.

You should see rows with real email addresses, storage values in MB, and a boolean is_super_admin column.

Getting a 'not found' error for the usage table?

This means it has been less than 24 hours since you enabled the export. The usage_ tables are generated the following day. Note this and come back tomorrow to run this query.

Still stuck? Help me troubleshoot my missing usage table.

Inspect the full schema programmatically

Google can add new fields to the export schema at any time. The INFORMATION_SCHEMA system view gives you a living reference of every column available.

  • Clear the query editor and paste this query. Replace YOUR_PROJECT with your GCP project ID:
-- Full schema inspection
SELECT column_name, data_type, is_nullable
FROM `YOUR_PROJECT.workspace_logs.INFORMATION_SCHEMA.COLUMNS`
WHERE table_name = 'usage'
ORDER BY ordinal_position;
  • Click Run.

What does this query do?

It queries BigQuery's metadata catalog to list every column in the usage table, along with its data type and nullability.

Save this output as a reference. Re-run this query periodically to discover new metrics as Google adds them to the export.

You should see a list of column names including nested fields like accounts.drive_used_quota_in_mb and accounts.gmail_used_quota_in_mb.

Save these queries for later

Keep all three queries in a local file named 01_explore_schema.sql. You can copy each query from the BigQuery editor into this file. These become your go-to reference whenever you want to check what data is available.

Schema query returning no results?

If the usage_ table does not yet exist (less than 24 hours since enabling the export), this INFORMATION_SCHEMA query will also fail. You can change table_name = 'usage' to table_name = 'activity' to inspect the activity table schema instead.

Need help? Help me query INFORMATION_SCHEMA for my workspace_logs dataset.

✔️ Awesome, I've got everything!

Great. Make sure you have saved all three queries to 01_explore_schema.sql locally for future reference.

ⓧ I'd like to double check the full code

Here is the complete 01_explore_schema.sql file with all three discovery queries:

-- Discover what record types and events are flowing in activity
SELECT
  record_type,
  event_type,
  event_name,
  COUNT(*) AS event_count
FROM `YOUR_PROJECT.workspace_logs.activity`
WHERE _PARTITIONTIME >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
GROUP BY 1, 2, 3
ORDER BY 4 DESC
LIMIT 50;

-- Discover per-user usage data
SELECT
  date,
  user_email,
  accounts.drive_used_quota_in_mb,
  accounts.gmail_used_quota_in_mb,
  accounts.is_super_admin
FROM `YOUR_PROJECT.workspace_logs.usage`
WHERE user_email IS NOT NULL AND user_email != ''
ORDER BY date DESC
LIMIT 20;

-- Full schema inspection
SELECT column_name, data_type, is_nullable
FROM `YOUR_PROJECT.workspace_logs.INFORMATION_SCHEMA.COLUMNS`
WHERE table_name = 'usage'
ORDER BY ordinal_position;

You now have a clear picture of what data is flowing into your dataset. Next up, you will build a view that aggregates these raw activity events into a clean per-user daily summary.

Build the Per-User Daily Activity View

In the previous step, you explored the raw schema and confirmed what record types and fields are flowing into your dataset. You know the shape of the data. Now it's time to transform those raw event rows into something useful.

In this step, you'll create a BigQuery view that aggregates activity events into one row per user per day. This gives any downstream dashboard or agent a clean, queryable summary without deploying any additional infrastructure.

In this step, get ready to:
  • Create a SQL view that counts daily events per user across logins, Drive, Calendar, and Gmail.
  • Test the view to confirm non-zero event counts for active users.
Create the activity aggregation view

Your activity table contains one row per event. A single user can generate dozens of events per day across different services. A view lets you define a reusable query that BigQuery executes on the fly whenever you (or a dashboard) reads from it.

  • In the BigQuery query editor, run the following SQL to create the aggregation view:
CREATE OR REPLACE VIEW `YOUR_PROJECT.workspace_logs.v_daily_user_activity` AS
SELECT
  DATE(TIMESTAMP_MICROS(time_usec), 'America/Los_Angeles') AS report_date,
  email AS user_email,
  COUNTIF(event_type = 'login') AS login_events,
  COUNTIF(drive.doc_id IS NOT NULL) AS drive_events,
  COUNTIF(calendar.calendar_id IS NOT NULL) AS calendar_events,
  COUNTIF(gmail.event_info.timestamp_usec IS NOT NULL) AS gmail_events,
  COUNT(*) AS total_events
FROM `YOUR_PROJECT.workspace_logs.activity`
WHERE email IS NOT NULL AND email != ''
GROUP BY 1, 2;

What does this view do?

This view reads every row from the activity table and rolls them up into one summary row per user per day.

  • DATE(TIMESTAMP_MICROS(time_usec), 'America/Los_Angeles') converts the raw microsecond timestamp into a date aligned with Pacific Time. Workspace partitions are PT-aligned, not UTC, so this keeps your dates consistent with the source.
  • COUNTIF conditionally counts events by checking service-specific nested fields. For example, if drive.doc_id is not null, that row represents a Drive event.
  • The WHERE email IS NOT NULL clause filters out system-level events that have no associated user.

Why Pacific Time instead of UTC?

Google Workspace overrides BigQuery's default UTC partition boundaries to align with Pacific Time (PT). If you used UTC for date extraction, your daily counts would not line up with the underlying table partitions. Using 'America/Los_Angeles' keeps everything consistent.

  • Replace YOUR_PROJECT with your actual GCP project ID in both the view name and the FROM clause.
  • Click Run.

You should see a message confirming the view was created successfully. The view now appears in your workspace_logs dataset in the Explorer panel on the left.

Seeing a "not found" error?

  • Double-check that your project ID is correct in both the CREATE VIEW line and the FROM clause. They must match exactly.
  • Confirm the dataset name is workspace_logs (or whatever name you chose when enabling the export). Check the Explorer panel to verify.
  • Make sure activity tables are present in your dataset. If you just enabled the export, wait 10-15 minutes for the first tables to appear.

Why is my view creation failing?

Test the view

Creating a view does not execute any query against your data. You need to query the view to confirm it actually returns results.

  • Run this query to pull yesterday's activity from your new view:
SELECT *
FROM `YOUR_PROJECT.workspace_logs.v_daily_user_activity`
WHERE report_date = DATE_SUB(CURRENT_DATE('America/Los_Angeles'), INTERVAL 1 DAY)
ORDER BY total_events DESC
LIMIT 20;

What does this query do?

This selects all columns from your view but filters to only yesterday's data. The ORDER BY total_events DESC puts the most active users at the top so you can quickly verify non-zero counts.

  • Replace YOUR_PROJECT with your GCP project ID.
  • Click Run.

You should see a results table with one row per user. Active users will show non-zero values in columns like login_events, drive_events, or gmail_events.

Seeing zero rows or all zeros?

  • If you get zero rows, try changing INTERVAL 1 DAY to INTERVAL 3 DAY in case yesterday had no recorded events yet. Data can take time to flow depending on when you enabled the export.
  • If you see rows but all event counts are zero, that means the activity table has events for those users but none matched the specific nested-field checks. Run the schema exploration query from Step 2 again to see which record_type values are actually present.

Help me debug why my view returns no data.

✔️ Awesome, I've got everything!

Great. Make sure you've saved your SQL statements for reference.

ⓧ I'd like to double check the full code

Here is the complete 02_v_daily_user_activity.sql file:

CREATE OR REPLACE VIEW `YOUR_PROJECT.workspace_logs.v_daily_user_activity` AS
SELECT
  DATE(TIMESTAMP_MICROS(time_usec), 'America/Los_Angeles') AS report_date,
  email AS user_email,
  COUNTIF(event_type = 'login') AS login_events,
  COUNTIF(drive.doc_id IS NOT NULL) AS drive_events,
  COUNTIF(calendar.calendar_id IS NOT NULL) AS calendar_events,
  COUNTIF(gmail.event_info.timestamp_usec IS NOT NULL) AS gmail_events,
  COUNT(*) AS total_events
FROM `YOUR_PROJECT.workspace_logs.activity`
WHERE email IS NOT NULL AND email != ''
GROUP BY 1, 2;

Your activity view is live. Any tool that connects to BigQuery can now query v_daily_user_activity for per-user daily event counts. Next up, you'll combine this with storage and account metrics from the usage table to build a complete dashboard-ready view.

Build the Combined Dashboard View

Your v_daily_user_activity view gives you per-user event counts from the activity table. But activity counts alone only tell half the story.

Storage consumption, admin status, and account metadata live in a separate table: usage. In this step, you will create a single combined view that joins activity counts with storage metrics. This gives any downstream tool one clean endpoint to query.

In this step, get ready to:
  • Create a combined view joining activity and usage data.
  • Test the view to confirm per-user rows include both event counts and storage metrics.
  • Validate that the view is ready for dashboard consumption.
Create the combined view joining activity and usage data

The usage table stores its date as a string, while your activity view uses a proper DATE type. The join needs to parse the usage date string into a matching format so the two tables can align on the same user and day.

  • Create the combined dashboard view by running this query in the BigQuery query editor:
CREATE OR REPLACE VIEW `YOUR_PROJECT.workspace_logs.v_user_dashboard` AS
SELECT
  a.report_date,
  a.user_email,
  a.login_events,
  a.drive_events,
  a.calendar_events,
  a.gmail_events,
  a.total_events,
  u.drive_used_quota_in_mb,
  u.gmail_used_quota_in_mb,
  u.is_super_admin
FROM `YOUR_PROJECT.workspace_logs.v_daily_user_activity` a
LEFT JOIN (
  -- Subquery parses the string date and pulls per-user storage metrics
  SELECT
    SAFE.PARSE_DATE('%Y-%m-%d', date) AS usage_date,
    user_email,
    accounts.drive_used_quota_in_mb,
    accounts.gmail_used_quota_in_mb,
    accounts.is_super_admin
  FROM `YOUR_PROJECT.workspace_logs.usage`
  WHERE user_email IS NOT NULL AND user_email != ''
) u
  ON a.user_email = u.user_email
  AND a.report_date = u.usage_date;

What does this query do?

This view pulls all columns from your existing v_daily_user_activity view (the activity counts) and joins them with storage and account data from the usage table.

  • LEFT JOIN ensures every activity row is preserved even if no matching usage row exists yet (usage data can lag by a day).
  • SAFE.PARSE_DATE('%Y-%m-%d', date) converts the usage table's string-type date into a proper DATE so it can match report_date from the activity view.
  • The join matches on both user_email and date, so each row represents one user on one day with all their metrics in a single place.
  • Click Run in the query editor.
  • You should see a message confirming the view was created. In the Explorer panel on the left, expand your workspace_logs dataset and confirm v_user_dashboard appears under Views.

Seeing a "Not found" error?

  • Make sure you replaced YOUR_PROJECT with your actual GCP project ID in all three places it appears (the view name, the activity view reference, and the usage table reference).
  • Confirm that v_daily_user_activity already exists in your dataset. You created it in the previous step.
  • If the usage table doesn't exist yet, remember that usage data takes up to 24 hours to appear after enabling the export.

Help me debug my v_user_dashboard view creation error.

Query the dashboard view
  • Test the combined view by querying the last 7 days of data:
-- Pull the last 7 days of combined user metrics
SELECT *
FROM `YOUR_PROJECT.workspace_logs.v_user_dashboard`
WHERE report_date >= DATE_SUB(CURRENT_DATE('America/Los_Angeles'), INTERVAL 7 DAY)
ORDER BY report_date DESC, total_events DESC
LIMIT 50;

What should I see?

Each row represents one user on one day. You should see columns for activity counts (login_events, drive_events, etc.) alongside storage metrics (drive_used_quota_in_mb, gmail_used_quota_in_mb).

Some storage columns may show null for days where usage data hasn't arrived yet. This is expected because the LEFT JOIN preserves activity rows even without a matching usage row.

  • Click Run and confirm the results show per-user rows with both event counts and storage metrics.

All storage columns are null?

  • Usage data takes approximately 24 hours to appear after enabling the export. If you enabled it today, check again tomorrow.
  • The view still works correctly. The LEFT JOIN ensures activity data is always returned. Storage columns will populate once usage data flows in.

Why are my storage columns showing null in v_user_dashboard?

Validate for dashboard consumption

This view is now the single endpoint your dashboard or agent queries. Any downstream tool that can connect to BigQuery queries v_user_dashboard with date filters.

Who can query this view?

Any tool with a BigQuery connection can use this view directly. This includes Looker Studio, Retool, Streamlit, a Python script, or an AI agent.

Since this is a view (not a materialized table), query cost scales with the underlying data scanned each time. For small-to-medium orgs, this is negligible within the free tier.

✔️ Awesome, I've got everything!

Great. Double check you've saved your query as 03_v_user_dashboard.sql for future reference.

ⓧ I'd like to double check the full code

Here is the complete 03_v_user_dashboard.sql file:

CREATE OR REPLACE VIEW `YOUR_PROJECT.workspace_logs.v_user_dashboard` AS
SELECT
  a.report_date,
  a.user_email,
  a.login_events,
  a.drive_events,
  a.calendar_events,
  a.gmail_events,
  a.total_events,
  u.drive_used_quota_in_mb,
  u.gmail_used_quota_in_mb,
  u.is_super_admin
FROM `YOUR_PROJECT.workspace_logs.v_daily_user_activity` a
LEFT JOIN (
  SELECT
    SAFE.PARSE_DATE('%Y-%m-%d', date) AS usage_date,
    user_email,
    accounts.drive_used_quota_in_mb,
    accounts.gmail_used_quota_in_mb,
    accounts.is_super_admin
  FROM `YOUR_PROJECT.workspace_logs.usage`
  WHERE user_email IS NOT NULL AND user_email != ''
) u
  ON a.user_email = u.user_email
  AND a.report_date = u.usage_date;

Your Workspace data pipeline is fully operational. You have raw event data flowing in automatically, a per-user activity view for granular counts, and now a combined dashboard view that any tool can query. Next, take on the Secret Mission to materialize this view into a partitioned table for sub-second dashboard reads.

Secret mission

Schedule Daily Materialization

Your views work great for ad-hoc queries. But every time a dashboard loads, BigQuery scans the full underlying tables. Can you schedule a daily MERGE that materializes the view into a partitioned table for sub-second reads?

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. The BigQuery free tier covers 10 GB of storage and 1 TiB of queries per month. For small-to-medium orgs, the data flowing from the Workspace export will stay well within these limits.

Cost warning

The BigQuery Export continuously populates daily tables in your dataset. Storage costs apply once you exceed 10 GB. For larger organizations with many active users, this can happen within a few months. If you no longer need the data pipeline, disable the export and delete the dataset to stop accumulation.

Resources you used:

  • BigQuery dataset workspace_logs (contains views, tables, and auto-populated daily shards).
  • BigQuery scheduled query (daily MERGE into daily_user_metrics).
  • BigQuery Export setting in the Google Admin Console.
  • Local SQL files (01_explore_schema.sql, 02_v_daily_user_activity.sql, 03_v_user_dashboard.sql, 04_scheduled_materialization.sql).

Keep everything running

No action needed. Choose this if you plan to connect a dashboard or agent to v_user_dashboard or daily_user_metrics. The export continues flowing data automatically. The scheduled query runs daily and materializes fresh rows.

  • Your dataset will grow over time as new daily tables are created. Monitor storage in the BigQuery Console under your project's billing details.

Pause - I'll come back to this later

Stop new data from flowing while keeping your existing views and tables intact for later use.

  • Disable the BigQuery Export: in the Google Admin Console, navigate to Menu > Reporting > Data integrations.
  • Click Edit on the BigQuery Export card.
  • Uncheck Enable Google Workspace data export to Google BigQuery and click Save.
  • Delete the scheduled query to stop it from running while paused: in the Google Cloud Console, open BigQuery and click Scheduled queries in the navigation menu.
  • Click the name of your MERGE query, then click Delete on the Scheduled query details page.
  • Your dataset, views, and daily_user_metrics table remain in place. You can re-enable the export and recreate the scheduled query when you return.

Delete - I don't want to use this again

Remove all project resources completely.

  • Delete the scheduled query first: in the Google Cloud Console, open BigQuery and click Scheduled queries in the navigation menu.
  • Click the name of your MERGE query, then click Delete on the Scheduled query details page.
  • Delete the BigQuery dataset: in the BigQuery Explorer pane, expand your project and click workspace_logs.
  • In the details pane, click Delete. Type delete in the confirmation dialog and click Delete. This removes all tables and views in the dataset.
  • Disable the BigQuery Export: in the Google Admin Console, navigate to Menu > Reporting > Data integrations.
  • Click Edit on the BigQuery Export card.
  • Uncheck Enable Google Workspace data export to Google BigQuery and click Save.
  • Delete local SQL files by running:
rm 01_explore_schema.sql 02_v_daily_user_activity.sql 03_v_user_dashboard.sql 04_scheduled_materialization.sql
  • Confirm the files are gone by running:
ls *.sql

You should see no matching files listed.

Nice Work!

Nice Work!

Nice work! You have built a fully operational Workspace-to-BigQuery data pipeline that runs automatically with zero ongoing maintenance.

You've learned how to:

  • Enable the native Google Workspace BigQuery Export with zero custom infrastructure. No service accounts, delegation, or Cloud Run required.
  • Build v_daily_user_activity: a BigQuery view producing per-user daily event counts across logins, Drive, Calendar, and Gmail.
  • Build v_user_dashboard: a combined view joining activity counts with storage and account metrics from the usage table into a single queryable endpoint.
  • Secret Mission: Scheduled a daily MERGE query that materializes the view into a partitioned table for sub-second dashboard reads.

Ready to quiz yourself?