How & Why To Prepare Google Sheet For BigQuery Integration ?
- Last updated :
Table of Contents
Why Do You Need This Google Sheet (Custom Input)?
Looker Report is a visualization tool, but it needs your specific input to make the data relevant to your business or SEO goal. As the site owner, you know your website better than any AI.
To track your content accurately, you must define your own Segments and User Intent. This is why filling in the Key Event, URL, Segment, and Intent columns is essential.
The custom data input needs to be specified as follows:
Column 1: KeyEvent – You must fill in only 1 GA4 Key Event to track.
Column 2: URL – Enter all the webpage URLs you wish to monitor.
Crucial: Ensure every URL ends with a trailing slash (e.g., https://www.abc.com/). Only include pages from a single website in this column; do not mix multiple domains.
Column 3: Segment – This column is essential for the Segment filters in the Looker report. This is your first layer of filtering. You must manually group your URLs by similar content.
For example:
| url | segment |
| https://www.abc.com/luxury-hotels-nearby/ | Accommodation |
| https://www.abc.com/budget -stays-comparison/ | Accommodation |
| https://www.abc.com/top-10-family-activities/ | Attractions |
| https://www.abc.com/history-of-landmark/ | Attractions |
| https://www.abc.com/how-to-skip-the-lines/ | Tips & Hacks |
| https://www.abc.com/essential-packing-list/ | Tips & Hacks |
Column 4: Intent – This column is also essential for the Intent filters in the Looker report. This is your second layer of filtering. You need to manually group your content based on specific user intentions.
For example:
| url | segment | intent |
| https://www.abc.com/luxury-hotels-nearby/ | Accommodation | Compare |
| https://www.abc.com/budget -stays-comparison/ | Accommodation | Compare |
| https://www.abc.com/top-10-family-activities/ | Attractions | Explore |
| https://www.abc.com/history-of-landmark/ | Attractions | Explore |
| https://www.abc.com/how-to-skip-the-lines/ | Tips & Hacks | Prepare |
| https://www.abc.com/essential-packing-list/ | Tips & Hacks | Prepare |
Important Note:
Avoid using generic intents like ‘Informational’ or ‘Transactional.’ Instead, use Action-Based Intent to describe specific human behaviors. This makes it much easier to identify exactly where a user is in their journey.
| Avoid (Generic) | Use Instead (Action-Based) |
| Informational | How-to Guide, Setup Guide, Research |
| Commercial | Product Review, Comparison, Pricing |
| Transactional | Download, Book Now, Subscription |
How to Identify True Intent:
- Analyze Keywords: Look for “how/what” vs. “buy/price” modifiers.
- Study SERPs: See if Google ranks blog posts or product pages for the query.
- Monitor Behavior: Review which specific pages users visit and their search paths.
The four column titles from your Google Sheet will be used to define the schema when creating the table in BigQuery.
1. Balancing Volume & Intent: Choosing The Right Key Events
By filling out this Google Sheet, your Looker Studio Report can track your GA4 Events.
However, not just any event will do. Default events like page_view, session_start, or scroll are “general engagement data”—they tell us people are on the site, but they don’t track actual business results.
To prove the success of your content strategy, you must select a specific Key Event (Conversion). These provide the only objective proof of traffic quality. While standard traffic shows brand awareness, Key Events measure business growth by linking profitable user actions directly to our SEO efforts.
Critical Strategy: Prioritize Volume Over Final Purchase
When selecting events for SEO reporting, you must balance the “Meaning” of the action with the “Volume” of the data. While a purchase is the most meaningful event, the actual count is often too low (e.g., 50–300 events) to give the SEO team enough data to analyze which specific articles are performing.
To truly track the performance of your content, we must focus on Intention-based Events. These provide a significantly higher “signal volume,” allowing you to see exactly where your content is influencing the user journey:
- Lead Gen: You might only get 50 closed deals, but your content could drive 500 form_submit or 800 whatsapp_click events. Tracking the form submission proves the content is successfully capturing leads.
- E-commerce: A specific product guide might result in 30 sales, but it could drive 600 add_to_cart or 400 begin_checkout actions. These mid-funnel events provide 10x more data to prove that your SEO writing is putting items into baskets.
- Service-Based: While final bookings might be rare, seeing 1,000 click_to_call events vs. 50 actual bookings gives the team a much clearer picture of which landing pages are generating the most “warm” inquiries.
By tracking intent rather than just the final step, the content team can see which topics are effectively moving users down the funnel, even if the final transaction happens via a different channel or at a later date.
Use the table below as a guide:
| SEO Content Goal | Recommended KeyEvent |
| Lead Generation | form_submit / whatsapp_click |
| Sales (E-commerce) | purchase / add_to_cart |
| Awareness (Blog) | file_download / newsletter_signup |
| Local Business | Click_to_call / get_directions |
Rule of Thumb: Choose events that identify significant user actions and meaningful intent.
2. Where To Find Your KeyEvent (KE) In GA4
Here Is Step-by-Step Navigation
- Login: Go to analytics.google.com.
- Reports: Click the Reports icon in the left-hand sidebar.
- Engagement: Expand the Engagement collection.
- Events: Click on the Events report.
3. Which URLs Should You Include?
You should input your SEO Content URLs. You can easily find these in your sitemap. Crucial: If a URL is not part of your SEO strategy (e.g., a “Privacy Policy” or “Thank You” page), do not include it in the Google Sheet. Only include pages where you want to measure SEO performance.4. Visualizing The Integration: From Google Sheet To Looker Report
The Google Sheet acts as your Input, while the Looker Report serves as your Output.. A. Key Event Mapping When you fill in the Key Event in Column 1 of the Google Sheet (image below left), the Looker Report (image below right) transforms to visualize your KE First Visit Conversion Rate and KE First Visit User metrics.
B. URL Mapping
When you list your URLs in Column 2 of the Google Sheet (image below left), the Looker Report (image below right) filters the data to display performance specifically for those pages.
C. Segment Mapping
When you fill in the Segment in Column 3 of the Google Sheet (image below left), the Looker Report (image below right) generates the Segment Insight Table to show which specific segments drive the most traffic and conversions.
D. Intent Mapping
When you fill in the Intent in Column 4 of the Google Sheet (image below left), the Looker Report (image below right) visualizes the traffic quality and mindset of your visitors across your different content types.
How To Structure Your Google Sheet For BigQuery Integration?
Before generating a one-page GSC + GA4 Looker Report, you MUST first structure a Google Sheet as follows:
https://docs.google.com/spreadsheets/d/1DiqcqovaLXi10u3ltuO_p6ZMz_AwDhfOe1oicEgBBJc/edit?gid=0#gid=0
Below are the steps:
Step 1: Create a new Google Sheet (name it “gsc_ga4_sitemap” or your preferred title).
Step 2: Copy the headers from Row 1 (KeyEvent, url, segment, and intent) from the link above.
Note: Ensure your Google Sheet is saved under the same account as your Google Cloud project. Both must use the exact same email address.
Step 3: Paste the headers into your new google sheet.
