Jump to Content
Data Analytics

Top 25 Google Search terms, now in BigQuery

June 28, 2021
https://storage.googleapis.com/gweb-cloudblog-publish/images/trends.max-2600x2600.jpg
Maggy Hu

Product Manager

Antonio Lobato

Software Engineering Manager

Today we’re announcing preview availability of a new public dataset for Google Trends. For the first time we’re bringing Google-owned Search data into Google Cloud Datasets for convenient analysis in BigQuery, or through your favorite business intelligence tools. 

For over a decade, Google Trends has provided a sample of Google Search data. Continuing to lead with a privacy-first mindset, the data is anonymized, indexed, normalized, and aggregated prior to publication. Access to Google Trends data has been limited to the Google Trends website and requires manual processing to export the underlying dashboard data to derive additional analytical insights. Now, we’re streamlining access to this dataset by surfacing and allowing direct interaction with BigQuery in a safe, secure and private manner. 

This BigQuery dataset, also available soon in Analytics Hub, allows users to measure interest in a particular topic or search term across Google Search, from around the United States, down to the city-level. Whether you’re a marketer, an executive, or an operations manager, you can now easily ask what people are searching for to inform your analyses. Say you’re a merchandiser in retailing, and want to ensure your end-cap displays are relevant to your local audience, you can take signals from what people are looking for in your market area to inform what items to place. Or, imagine you’re in charge of your product’s R&D initiatives and want to understand what new features could be incorporated into an existing product based on what people are searching for. What’s popular and relevant might even inspire a new product line for your team. Terms that appear in these datasets could be an indicator of what you should be paying attention to.

https://storage.googleapis.com/gweb-cloudblog-publish/images/Announcing_Google_Trends_dataset.max-1000x1000.jpg

TOP 25 TRENDING GOOGLE SEARCH TERMS (Refreshed Daily, as of 12AM EST)

“Google Search Trends data has always been an important tool for our WPP agency data teams. At WPP we believe that data variety is a superpower which is why we are excited to use the new Trends dataset availability within BigQuery, plus the launch of Analytics Hub. The best creativity in the world is informed by data insights, and influenced by what people search for, so the operational efficiencies we’ll gain via the Analytics Hub and the insights we can drive with Trends data are just phenomenal.”—Di Mayze Global Head of Data and AI, WPP

How the Google Trends dataset works

To remain true to Google’s mission to “organize the world’s information and make it universally accessible and useful,” we are sharing some of this data in a safe, private, and secure manner. In this initial preview, we will provide popular trending terms through Google Cloud Datasets. The dataset surfaces the Top 25 stories and Top 25 Rising queries for the United States in a BigQuery dataset, available to access from the Google Cloud Marketplace.

https://storage.googleapis.com/gweb-cloudblog-publish/images/Trends_Dataset_in_GCP_Marketplace.max-1400x1400.jpg
Google Trends dataset now available in the Google Cloud Marketplace

Top stories are indicative of the most searched topics for the day throughout the United States, whereas top rising provides a view of what search terms have surged in popularity over the past day. Each term will be segmented by Nielsen’s Designated Market Area® (DMA) and by the week, enriched with a historical backfill over a rolling five year period.

As a new set of top terms and top rising queries is generated daily, data will be inserted into a new partition of their respective table. Each set of top stories and top rising queries will persist and remain static until its expiration date of 30 days is reached (e.g. each table partition has a time-to-live of 30 days). Learn more about the schema of each table in the dataset listing.   

Getting started with the Google Trends dataset

For top and rising terms stored in the Google Trends dataset, users can obtain access without charges of up to 1TB/month in queries and up to 10GB/month in storage through BigQuery’s free tier. SQL queries above these thresholds are subject to regular BigQuery pricing. Users can also leverage the BigQuery sandbox to access BigQuery without the need to create a Google Cloud account or provide credit card information, subject to the sandbox's limits and BigQuery’s free tier thresholds. 

To begin exploring these public dataset tables, simply query the top 25 and top 25 rising tables from the BigQuery SQL UI. To minimize the data scanned and processed, utilize the partition filter in your query:

SELECT * FROM `bigquery-public-data.google_trends.top_terms`
WHERE refresh_date = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)

To compare and overlay the most popular search trends with the top rising, join the tables on refresh_date and week (we can exclude DMA in the join here because the same top rising set exists for all DMAs for a given refresh_date):

WITH rising AS (
  SELECT
    week,
    refresh_date,
    ARRAY_AGG(DISTINCT term) AS terms
  FROM `bigquery-public-data.google_trends.top_rising_terms`
  WHERE refresh_date = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
  GROUP BY
    week,
    refresh_date)
 
SELECT
  top.refresh_date,
  top.week,
  top.dma_name,
  top.dma_id,
  top.term AS top_term,
  top.score AS top_score,
  top.rank AS top_rank,
  rising.terms AS rising_terms,
FROM `bigquery-public-data.google_trends.top_terms` top
JOIN rising
ON
  top.refresh_date = rising.refresh_date
  AND top.week = rising.week
ORDER BY
  week desc, refresh_date desc;

Outside of the BigQuery UI, you can also interact with this dataset from familiar BI tools like Looker, Data Studio or with solutions from our partner ecosystem. We've built an example Looker dashboard for you to see the current Top 25 Google Search terms, ranked by search volume (1 through 25) and with average search index score across the geographic areas (DMAs) in which it was searched. Have a look. What surprised you most? 

https://storage.googleapis.com/gweb-cloudblog-publish/images/looker.max-1700x1700.jpg

What’s next for Google Cloud Datasets?

This is just the beginning of our journey in making Google's first-party data more accessible to organizations to enhance their analytics initiatives. Stay tuned for a future blog post on reference patterns and use cases with Google Trends data, as well as for updates on product roadmap and other dataset solutions offered through Google Cloud Datasets. In the meantime, explore the new Google Trends dataset in your own project, or if you’re new to BigQuery spin up a project using the BigQuery sandbox.

Posted in