Solved: BQ not showing correct data from GSC connection - Page 2

momtartin1 · 08-16-2023 09:08 AM

I've got 2 issues right now that I'm hoping I can get some support for, both relating to Big Query integration with GSC.

First of all, I tried to update the schema for the search site table and apparently screwed it up so it won't export anymore.

Edited Schema to try and add page

The reason I did that is because I'm trying to get a report showing all pages on my site and which queries were used to search for that page. However, when I run the report, I only get http domain data (http://www.domain.com) and nothing else. I then realized that it's not showing the https domain (https://www.domain.com)

How would I go about correcting it so that it shows all pages? Is it something I need to fix with the GSC connection?

ms4446

I'm glad to hear that you've resolved the schema issue and are now seeing HTTPS data. If you're only seeing the root domain (e.g., domain.com) and not individual pages (e.g., domain.com/aboutus), there are a few potential reasons and solutions:

Check Your GSC Data: Before diving into BigQuery, ensure that Google Search Console itself is showing data for individual pages. If GSC isn't showing this data, then it won't be in BigQuery either.
Query Modification: Ensure that your BigQuery SQL query is designed to retrieve individual page data. The column that typically holds this data is named something like page or url. Your query should select this column to see individual page data.

SELECT page, COUNT(*) as total_views
FROM `your_dataset.your_table`
GROUP BY page
ORDER BY total_views DESC;

Data Granularity: Ensure that the data you're exporting from GSC to BigQuery is at the right granularity. GSC can provide both site-level and page-level data. Ensure that you're exporting page-level data to BigQuery.
Filters in GSC: If you've set up any filters in GSC, ensure they aren't excluding individual page data. Filters can be set to include/exclude specific URLs or URL patterns.
Data Aggregation: If you're using any aggregation in your queries, ensure that you're not unintentionally aggregating page-level data into domain-level summaries.
Check Schema Again: Double-check the schema of your BigQuery table to ensure that there's a field/column that should contain individual page URLs. If this column is missing or not populated, it could be the reason you're not seeing individual pages.
Reconnect GSC: If you've recently made changes to your GSC settings or properties, consider reconnecting GSC to BigQuery to ensure that the latest configuration and data are being exported.

View solution in original post