How to use SQL with the Google Trend dataset?

As data becomes more and more important to businesses and organizations, there's a growing need for tools that can help us make sense of it all. One such tool is Google's BigQuery, a cloud-based platform for analyzing large datasets.

In this article, I'll take a look at how to use BigQuery to work with one of Google's own datasets: Google Trends.

Google Trends is a free tool that provides insights into the popularity of search terms on Google. With Google Trends, you can see how often particular search terms are being used, where they are being used, and how their popularity has changed over time.

Let's get started!!!

Step 1: Accessing BigQuery

Go to the BigQuery Console:

Choose your country and click "Agree and Continue"

Click "Select a Project"

Click "New Project"

Choose a Project Name, I used the name "Data Hype", then click "Create".

Congratulation !!! Now you can use BigQuery to retrieve data from public dataset

Explore the Google Trend dataset

Click "Open this query" or search "Google Trend" in the search box

According to the definition of BigQuery: "The Google Trends dataset will provide critical signals that individual users and businesses alike can leverage to make better data-driven decisions. This dataset simplifies the manual interaction with the existing Google Trends UI by automating and exposing anonymized, aggregated, and indexed search data in BigQuery".

In other words, the t contains the daily top Google Search terms, with their score, ranking, time, and designated market area. I will take Germany as an example.

Here is the code (BUT skip it if it looks too complicated for you !!!)

-- This query shows a list of the daily top Google Search terms in Germany.
SELECT
? refresh_date AS Day,
? term AS Top_Term,
? country_name,
? rank,
FROM
? `bigquery-public-data.google_trends.international_top_terms`
WHERE
? rank = 1 -- Choose only the top term each day.
? AND refresh_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 2 WEEK) -- Filter to the last 2 weeks.
? AND country_name = 'Germany' --Filter country
GROUP BY
? Day,
? Top_Term,
? country_name,
? rank
ORDER BY
? Day DESC -- Show the days in reverse chronological order.        

Here is the result of the top 1 search term in Germany from 16 to 29 April 2023.

We can not deny that German interests in football (BVB, Bayern, etc.)

How about Vietnamese? What are they searching for?

I will change one filter AND country_name = 'Vietnam' and the result is:

It is not surprising that Vietnamese are football lovers too!!! Terms like "Bayern, Man City, and Arsenal" appear among the top 10. But wait a second, Vietnamese may only pay attention to Premier League.

Overall, BigQuery is a powerful tool for working with large datasets like Google Trends. Whether you're an analyst looking to uncover insights or a developer building data-driven applications, BigQuery can help you make the most of your data. So why not give it a try and see what you can discover?

I also wrote 2 other articles (SR only in Vietnamese):

1) Mình tr? thành Data Analyst nh? th? nào? Link

2) Mình h?c gì ?? tr? thành Data Analyst? Link

Thanks for spending your time !!! Happy coding