Export your Google Logs to BigQuery for a Big Win

A school’s main goal is to empower students through the pursuit of knowledge. To do this, school administrators and teachers often leverage data to help them make decisions that will improve the learning environment. Here at Amplified at IT, we aim to assist schools and partners in developing actional road maps for Google for Education to achieve their goals. Our team understands how important data is in implementing action items. This is why we are introducing exporting Google logs to BigQuery, a data retention tool.

This is BIG

The Google Admin console, like many other tools with data retention, only maintains data logs for 30 to 180 days. Gmail, for example, only maintains them for 30 days. This gives school leadership limited time to analyze and use the data effectively and efficiently. BigQuery enables Google Workspace organizations to collect multiple data logs, and export them to the Google Cloud platform so they can be kept longer than the Admin console permits. It even allows users to export email logs, which can be larger with more complex queries and come at a higher cost.

This will really benefit those who use data visualization platforms such as Data Studio or Looker because the data is exported in a format that can be connected to these programs for real-time and historic insights. Also, now admins will be able to share reports with audiences, such as central office staff and principals, without having to give them access to the console.

Lasting data, more possibilities

Setting up Google Cloud Platform (GCP) billing, and a new BigQuery project are prerequisites to setting up BigQuery in the Admin console. Once that is complete, admins can begin exporting data by going to Reports > Audits and then select BigQuery Export. After exporting Google Workspace data to Google BigQuery, admins will see that the data is exported into two tables: Activity and Usage. The Activity table reports on user actions, such as login, Drive sharing, or Meet participation. The Usage table, on the other hand, contains aggregate data on things like Drive storage usage per user, teacher posting frequency in Classroom, or number of Meets organized by a user. This information can be invaluable in monitoring trends over time and in strategic planning to meet future goals since the data will not expire.

The cost of storage and queries

Storage and usage are coveted features of any cloud-based product. One valuable component of the BigQuery pricing model for schools is that the cost of storage and usage are manageable. Districts will receive 10GB’s of free storage in the GCP per month for their exported logs, whether queried or not. Many schools will stay within this free range for several months before needing to upgrade, if at all because they can choose to limit how long data is stored to control the cost. The same goes for the usage. Schools are allotted 1TB of free queries per month. Even for a medium sized school district, the leadership team will be able to query all data once per week to analyze and assess without incurring additional charges.

Consider this

Here are a couple of things to note when purchasing BigQuery:

  • As previously explained, the initial cost includes storage and computation up to a designated number of bytes.

  • Data exports are not retroactive. BigQuery will only start collecting data from the day it is turned on.

  • Districts can create a project to test it out without having to set up billing. Also, the first GCP project allows $300 if a credit card is provided.

BigQuery is going to change how schools export, keep and query data logs in a BIG way. Now that data can be stored longer past expiry, administrators and teachers will be able to make sound, data-based decisions for their student’s success.

To learn more about exporting Google logs to BigQuery, reach out to your Account Manager to schedule a Support Stack for guidance on establishing a BigQuery project for reporting logs, setting up the delivery of admin & Gmail logs, decompressing the Gmail into row-level entries, showcasing explorable usage/activity data with Data Studio templates, or going over data and dashboard templates.

  • Lalena Parkhurst
    Technical Support Analyst

  • About the Author:

    Lalena joined Amplified IT in 2021 after 15 years in the IT Department at a public school district near Austin, TX. She was the System Administrator as the district moved to Google for Education and purchased thousands of chromebooks. As a Technical Support Analyst at Amplified IT, she enjoys communicating with customers and researching new problems and working to ensure they are resolved. Lalena can be found bicycling around town and engaging in community civic and cultural events.