Google Trends offers a look into the interest of people worldwide. By understanding trending topics and how interests shift, businesses, content creators, and marketers can gain invaluable insights. With Python, we can automate data collection from Google Trends, store it in a local SQLite database, and use Bootstrap and JS Charts to visualize and drill down into the data.
This article covers setting up a virtual environment, collecting daily trend data, storing it locally, and creating an interactive, browser-based interface.
The final output will look like this:
Setting up Your Python Environment
Before diving into the code, let’s set up a virtual environment to manage the packages. This process ensures your project has a clean environment without interfering with system-wide Python packages and/or versions.
For Windows:
python -m venv google_trends_env
google_trends_env\Scripts\activateFor Linux/Mac:
python3 -m venv google_trends_env
source google_trends_env/bin/activateWith the environment activated, install the required packages:
pip install pytrends flask pandapytrends: A library to access Google Trends data.flask: A lightweight web framework to create a local server for displaying data.pandas: For data manipulation.sqlite3: built-in Python library for working with SQLite databases. (There is no need to pip install sqlite3)
Create Files
We’ll create three main files:
createdb.py: Sets up the SQLite database to store Google Trends data.app.py: Fetches Google Trends data and inserts it into the database.flaskapp.py: A Flask application that displays the trends data in a web interface with interactive charts.
When finished your file structure will look like this:
google_trends_app/
├── google_trends_env/ # Virtual environment for dependencies
├── app.py # Fetches Google Trends data and stores it in the database
├── createdb.py # Initializes the SQLite database and table for storing trends data
├── flaskapp.py # Flask application that serves the web interface and manages data interactions
├── trends.db File 1: createdb.py
This script initializes a SQLite database with a table to store the daily trends data.
import sqlite3
from datetime import datetime
# Connect to SQLite database
conn = sqlite3.connect("trends.db")
cursor = conn.cursor()
# Create a table for daily trends
cursor.execute('''CREATE TABLE IF NOT EXISTS daily_trends (
id INTEGER PRIMARY KEY,
date DATE,
trend_name TEXT,
searches INTEGER,
source TEXT,
interest_over_time TEXT
)''')
conn.commit()
conn.close()- Explanation: This script creates a database file named
trends.dbwith a table calleddaily_trends. The table stores information about each trend, including the date, trend name, average search interest, source (e.g., “Google Trends”), and a JSON string of interest values over time.
To run this script and create the database:
python createdb.pyFile 2: app.py
This script fetches daily trending searches from Google Trends and stores them in the database.
# app.py
from pytrends.request import TrendReq
import pandas as pd
from datetime import datetime
import sqlite3
import json
def fetch_daily_trends():
pytrends = TrendReq()
today = datetime.today().date()
conn = sqlite3.connect("trends.db")
cursor = conn.cursor()
# Check if today’s trends are already in the database
cursor.execute("SELECT * FROM daily_trends WHERE date = ?", (today,))
if cursor.fetchone():
conn.close()
return False # Return False if data for today already exists
# Fetch and process trending data (as in the original code)
trending_data = pytrends.trending_searches(pn='united_states')
trending_data.columns = ["trend_name"]
for index, row in trending_data.iterrows():
trend_name = row["trend_name"]
pytrends.build_payload([trend_name])
interest_over_time_df = pytrends.interest_over_time()
if not interest_over_time_df.empty:
interest_values = interest_over_time_df[trend_name].tolist()
interest_json = json.dumps(interest_values)
avg_search_interest = sum(interest_values) / len(interest_values)
cursor.execute(
'''INSERT INTO daily_trends (date, trend_name, searches, source, interest_over_time)
VALUES (?, ?, ?, ?, ?)''',
(today, trend_name, avg_search_interest, "Google Trends", interest_json)
)
conn.commit()
conn.close()
return True # Return True if data was fetched and inserted- Explanation: This function uses
pytrendsto fetch the top daily trends in the United States, gathers interest over time for each trend, and stores it in thetrends.dbdatabase. It avoids duplicating data by checking if today’s trends are already saved.
File 3: flaskapp.py
This file creates a Flask application that provides an interactive web interface to visualize the trends data.
from flask import Flask, render_template_string, jsonify
import sqlite3
from app import fetch_daily_trends
import warnings
from datetime import datetime, timedelta
warnings.filterwarnings("ignore", category=FutureWarning)
app = Flask(__name__)
@app.route("/")
def index():
conn = sqlite3.connect("trends.db")
cursor = conn.cursor()
cursor.execute("SELECT id, date, trend_name, searches, interest_over_time FROM daily_trends ORDER BY date DESC LIMIT 10")
trends = cursor.fetchall()
conn.close()
html = """
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Google Trends</title>
<link href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css" rel="stylesheet">
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
</head>
<body>
<div class="container mt-5">
<h2 class="text-center mt-5">Daily Google Trends</h2>
<button id="getTrends" class="btn btn-primary mb-4">Get Daily Trends</button>
<div class="alert alert-success d-none" id="successMessage">Data imported successfully!</div>
<div class="alert alert-info d-none" id="alreadyFetchedMessage">Today's trends have already been fetched.</div>
<div class="alert alert-info d-none" id="loadingMessage">Fetching data, please wait...</div>
<div class="progress d-none" id="progressBar">
<div class="progress-bar progress-bar-striped progress-bar-animated" role="progressbar" style="width: 100%;" id="progress" aria-valuenow="100" aria-valuemin="0" aria-valuemax="100"></div>
</div>
<table class="table">
<thead>
<tr>
<th>Date</th>
<th>Trend</th>
<th>Avg Interest</th>
<th>Interest Over Time</th>
</tr>
</thead>
<tbody>
{% for trend in trends %}
<tr>
<td>{{ trend[1] }}</td> <!-- Date -->
<td>{{ trend[2] }}</td> <!-- Trend Name -->
<td>{{ trend[3] }}</td> <!-- Average Search Interest -->
<td>
<select id="rangeSelect{{ trend[0] }}" onchange="updateChart{{ trend[0] }}()">
<option value="7">Last 7 days</option>
<option value="30">Last 30 days</option>
<option value="all">All data points</option>
</select>
<canvas id="chart{{ trend[0] }}" style="width: 100%; height: 150px;"></canvas>
</td>
</tr>
{% endfor %}
</tbody>
</table>
</div>
<script>
const trends = {{ trends | tojson }};
console.log("Collected Trends Data:", trends);
{% for trend in trends %}
let interestData{{ trend[0] }} = JSON.parse({{ trend[4] | tojson | safe }});
let ctx{{ trend[0] }} = document.getElementById("chart{{ trend[0] }}").getContext('2d');
// Create initial chart with last 7 days data
let chart{{ trend[0] }} = new Chart(ctx{{ trend[0] }}, {
type: 'line',
data: {
labels: generateLabels(7),
datasets: [{
label: "{{ trend[2] }} Interest Over Time (Last 7 Days)",
data: interestData{{ trend[0] }}.slice(-7),
fill: false,
borderColor: 'green',
borderWidth: 1
}]
},
options: {
scales: {
x: { title: { display: true, text: "Date" } },
y: {
title: { display: true, text: "Interest Level" },
beginAtZero: true
}
}
}
});
function updateChart{{ trend[0] }}() {
let selectedRange = document.getElementById("rangeSelect{{ trend[0] }}").value;
let newData, newLabels;
if (selectedRange === "7") {
newData = interestData{{ trend[0] }}.slice(-7);
newLabels = generateLabels(7);
} else if (selectedRange === "30") {
newData = interestData{{ trend[0] }}.slice(-30);
newLabels = generateLabels(30);
} else if (selectedRange === "all") {
newData = getWeeklyTotals(interestData{{ trend[0] }});
newLabels = Array.from({length: newData.length}, (_, i) => `Week ${i + 1}`);
}
chart{{ trend[0] }}.data.labels = newLabels;
chart{{ trend[0] }}.data.datasets[0].data = newData;
chart{{ trend[0] }}.data.datasets[0].label = "{{ trend[2] }} Interest Over Time (" + (selectedRange === "all" ? "All Data (Weekly)" : `Last ${selectedRange} Days`) + ")";
chart{{ trend[0] }}.update();
}
{% endfor %}
document.getElementById("getTrends").onclick = function() {
document.getElementById("loadingMessage").classList.remove("d-none"); // Show loading message
document.getElementById("progressBar").classList.remove("d-none"); // Show progress bar
document.getElementById("progress").style.width = "0%"; // Reset progress bar
fetch("/fetch_trends").then(response => response.json()).then(data => {
// Hide loading message after fetch completes
document.getElementById("loadingMessage").classList.add("d-none");
if (data.already_fetched) {
document.getElementById("alreadyFetchedMessage").classList.remove("d-none");
document.getElementById("successMessage").classList.add("d-none");
} else {
document.getElementById("successMessage").classList.remove("d-none");
document.getElementById("alreadyFetchedMessage").classList.add("d-none");
}
// Simulate progress update
let progress = 0;
const interval = setInterval(() => {
progress += 10; // Increase progress
document.getElementById("progress").style.width = progress + "%"; // Update the progress bar
if (progress >= 100) {
clearInterval(interval); // Stop the interval when complete
document.getElementById("progressBar").classList.add("d-none"); // Hide progress bar after completion
location.reload(); // Reload to display new data
}
}, 500); // Update every 500ms
});
};
// Function to generate labels for date
function generateLabels(dataLength) {
let labels = [];
for (let i = 0; i < dataLength; i++) {
let date = new Date();
date.setDate(date.getDate() - (dataLength - 1 - i));
labels.push(date.toISOString().slice(0, 10)); // Format as YYYY-MM-DD
}
return labels;
}
// Function to sum each 7-day period into a weekly total for "All data points" option
function getWeeklyTotals(data) {
let weeklyTotals = [];
for (let i = 0; i < data.length; i += 7) {
let weekTotal = data.slice(i, i + 7).reduce((acc, val) => acc + val, 0);
weeklyTotals.push(weekTotal);
}
return weeklyTotals;
}
</script>
</body>
</html>
"""
return render_template_string(html, trends=trends)
@app.route("/fetch_trends")
def fetch_trends():
data_fetched = fetch_daily_trends()
return jsonify(success=True, already_fetched=not data_fetched)
if __name__ == "__main__":
app.run(debug=True)Explanation
index(): Fetches the latest trends data and renders it with an embedded HTML template that usesChart.jsto display interest over time.- Fetch Trends Button: Clicking this button will trigger data fetching.
Dropdown Selection for Different Time Ranges
For each trend, there’s a dropdown menu with three options:
- Last 7 days (default) — This option pulls the last 7 data points from the
interest_over_timelist. - Last 30 days — This option pulls the last 30 data points from the
interest_over_timelist. - All data points (weekly aggregation) — This option aggregates the entire dataset by calculating a weekly total, providing an overall view of the trend’s popularity over time.
Each time the dropdown selection changes, it triggers a function (updateChart) specific to that chart. This function:
- Determines the selected time range.
- Updates the data points displayed in the chart based on the selection.
How Data Aggregation Works for “All Data Points”
When the “All data points” option is selected, the data for each trend is grouped into weekly totals. Here’s how this aggregation works:
- Retrieve All Data Points: All data points from
interest_over_timeare accessed. - Group by Week: Every 7 days’ worth of data points are summed to create one aggregated data point.
- Display Weekly Totals: Each aggregated point represents the cumulative interest for a 7-day period, giving a smoother, summarized trend over time.
By grouping data weekly, this view reduces noise in daily fluctuations, offering a broader perspective on the trend’s overall movement. Each aggregated week is displayed as a single data point on the chart, labeled as “Week 1,” “Week 2,” etc., to make it easy for users to understand the trend’s behavior over longer periods.
Progress Bar
The progress bar in the application provides visual feedback to users during the data fetching process. When the “Get Daily Trends” button is clicked, the progress bar and a loading message are displayed, indicating that the application is actively retrieving the latest Google Trends data.
Once the data fetching is complete, the progress bar is hidden, and the user is notified of the successful data import or relevant status updates.
Run the Flask Application
To view the application in your browser, run the following command:
python flaskapp.pyOpen a web browser and go to http://127.0.0.1:5000 to see your historical Google Trends data visualized! Each chart dynamically updates based on the dropdown selection for a more customized analysis experience.
Thank you for reading this article. We hope you found it helpful and informative. If you have any questions, or if you would like to suggest new Python code examples or topics for future tutorials/articles, please feel free to join and comment. Your feedback and suggestions are always welcome!
You can find the same tutorial on Medium.com.
