Your Search Console Export Hides Two-Thirds of Your Queries: A pandas Festival Calendar for 2027

(I help out with FRQ.)

FRQ is a small hyperlocal marketplace: shoppers find clothing shops near them, and shop owners list for free. Like most small sites, it has no analytics team. What it does have is a Google Search Console export: one .xlsx file with the last 30 days to 27 October 2026.

This post is the notebook I used to turn that one file into a content calendar for the 2026-27 festival and wedding season. All numbers below come from that export. The code works on any site's export.

1. Load the export

Search Console → Performance → Export → Excel gives you one workbook with a sheet per tab.

import pandas as pd x = pd.ExcelFile("search_console_export.xlsx") print(x.sheet_names) # ['Chart', 'Queries', 'Pages', 'Countries', 'Devices', 'Search appearance', 'Filters']  q = x.parse("Queries"); q.columns = ["query", "clicks", "impr", "ctr", "pos"] p = x.parse("Pages"); p.columns = ["page", "clicks", "impr", "ctr", "pos"] chart = x.parse("Chart") 

2. Gotcha: the Queries sheet is not your traffic

The first thing I did was add up the Queries sheet. It did not match the total.

print(chart["Impressions"].sum(), chart["Clicks"].sum()) # 6886 86 print(q["impr"].sum(), q["clicks"].sum()) # 2340 21 

The 333 query rows cover 2,340 of 6,886 impressions (34%) and 21 of 86 clicks (24%). Google leaves out rare queries for privacy, and on a small, young site most queries are rare. If you plan content only from the Queries sheet, you plan from a third of the picture.

The Pages sheet goes the other way: it sums to 8,165 impressions, more than the total, because Search Console counts a page-level impression for every URL shown, and one search can show two of your pages. So:

• use Chart for totals,
• use Pages to judge pages,
• use Queries for the wording people type, not for volume.

3. Bucket queries by intent (Hinglish and Hindi included)

FRQ's audience types in English, Hinglish and Hindi script, so the patterns have to cover all three. A plain regex table was enough:

import re buckets = { "seller": r"business|online store|sell|dukan online|apni dukan", "when": r"\bkab\b|\bkb\b|date|muhurat|tithi|\btime\b|\btak\b|कब|मुहूर्त", "price": r"price|kitne ka|cost|\brate\b|sasta|budget", "what_to_buy": r"kya kharid|kya le|\blist\b|ideas|outfit|saman|सामान", "where": r"near me|kahan|nearby|पास", "how_to": r"how to|kaise|कैसे", } def bucket(query): for name, pattern in buckets.items(): if re.search(pattern, query, re.I): return name return "other" q["bucket"] = q["query"].apply(bucket) q.groupby("bucket").agg(queries=("query", "count"), impressions=("impr", "sum"), clicks=("clicks", "sum")).sort_values("impressions", ascending=False) 

Two things jumped out:

• "When" beats "where". 97 different queries asked when to shop ("karwa chauth ki shopping kab kare", "kb kre", "muhurat", the same question in Devanagari). Exactly one query had "near me" in it. For a "near me" product that is humbling, and useful: people plan festival shopping by the calendar first and pick the shop second.
• Spelling variety is the long tail. The same question showed up as karwa, karva, karwachauth and करवा चौथ. One page that answers the question well collects all of them; the regex table is how you see that they are one question.

4. Find pages Google shows but nobody clicks

zero = p[(p["impr"] >= 20) & (p["clicks"] == 0)].sort_values("impr", ascending=False) len(zero) # 27 

27 pages had 20 or more impressions and zero clicks. These are the cheapest wins on the site: Google already ranks them (several sit at average position 6 to 10), so a better title and a direct answer in the first two lines can turn impressions into visits without a new URL. On FRQ that list included a wedding-season guide (47 impressions, position 6.4), a men's kurta measurement guide (45 impressions) and a saree packing guide (68 impressions).

5. Check the device split before you design anything

x.parse("Devices") 

87% of impressions were on mobile, and mobile ranked about six places higher than desktop. Every page gets tested on a phone first.

6. Turn it into a calendar

The pattern in the data: festival guides with the year and a Hinglish question in the title win, and the impressions arrive before the festival. Karwa Chauth fell on 29 October 2026 (Drik Panchang); in the 14 days up to 27 October FRQ had 5,271 impressions, against 1,615 in the 18 days before that, and the Karwa Chauth 2026 guide alone collected 3,537 impressions and 28 clicks. So the calendar is a list of peaks, with a publish-by date about 10 days earlier:

from datetime import date, timedelta peaks = { "Diwali outfits and shopping list": date(2026, 11, 8), "Shaadi season opens (Dev Uthani Ekadashi)": date(2026, 11, 20), "Wedding guest outfits (Feb weddings)": date(2027, 2, 2), "Christmas and New Year party wear": date(2026, 12, 25), } cal = pd.DataFrame( [(topic, peak, peak - timedelta(days=10)) for topic, peak in peaks.items()], columns=["topic", "peak", "publish_by"], ).sort_values("publish_by") 

Festival dates come from a panchang, not from the data. Dev Uthani Ekadashi on 20 November 2026 and the first February 2027 wedding date are from Drik Panchang's calendars; Diwali on 8 November is from FRQ's own Diwali guide. Ten days is the lead time we chose to test, not something the export proves.

What we're doing with it

• Writing festival and wedding-season guides on frq.in's own blog first, in English and Hinglish, because the site's own pages are where the clicks are.
• Answering the "when" question in the first two lines of every guide.
• Rewriting the titles of the 27 zero-click pages before writing anything new.
• Re-running this notebook every month through the wedding season into 2027.

If you run a local shop and want to try it: listing on FRQ is free at shop.frq.in, and shoppers can browse local markets at market.frq.in.

(I help out with FRQ.)