Nico Brooks nicobrx

Google Analytics Property Audit Instructions

You are a Google Analytics audit specialist. When a user provides a GA4 property ID and GTM account/container ID, follow these steps to conduct a comprehensive property audit. Using the uploaded Google Analytics Audit template, fill in only the <ai_fill> sections with output from the steps referenced in these instructions. Leave all other sections of the template unchanged and maintain the original formatting.

Prerequisites

User must provide a valid GA4 property ID (format: properties/XXXXXXXXX)
User must provide GTM Account ID and Container ID for comprehensive tag management review
User must provide the website URL associated with the GA4 property for technical validation
You have access to both Google Analytics MCP server and Google Tag Manager MCP server tools

	select
	-- day of week, starting with Sunday (dimension)
	format_date('%w',parse_date("%Y%m%d",event_date)) as day_of_week,
	-- hour (dimension)
	format("%02d",extract(hour from timestamp_micros(event_timestamp))) as hour,
	(select value.string_value from unnest(event_params) where key = 'page_location') as page_path,
	count(event_name) as sessions
	from
	-- change this to your google analytics 4 export location in bigquery
	`yourproject.analytics_1234567.events_*`

	-- This is intended to be run as a Looker Studio BigQuery custom query. To run it directly in BigQuery, change @DS_START_DATE
	-- and @DS_END_DATE to 'YYYYMMDD' string values. To use it in BigQuery, you will also need to enable date range parameters
	-- in the custom query. Be wary of using this query for high-volume GA4 properties or long date ranges, as it could incur
	-- BigQuery analysis costs. This github repo has a Google Dataform repository that is designed to update a table with path data
	-- on a daily basis, which is a much more efficient and cost-effective approach: https://github.com/nicobrx/ga4-path-analysis
	-- This query references an 'extract_path' UDF, the original version of which can be found here:
	-- https://github.com/GoogleCloudPlatform/bigquery-utils/blob/master/udfs/community/cw_url_extract_path.sqlx
	-- You will need to update the reference in this query to the location of the UDF you create in your BigQuery project
	-- You will also need to update the FROM references in the source_mediums

	/** Google Analytics Insights fetcher
	*
	* This script automates the collection of Google Analytics “new insight” emails and turns them into a structured log
	* in a shared spreadsheet. From there, you can build a dashboard in Looker Studio or another tool to monitor alerts for
	* one or more GA properties over time.
	* To use the script, set the configuration variables below, and add a trigger to run the script hourly or daily.
	* When it runs, it searches the Gmail inbox of the account the script is running under and looks
	* for any “new insight” notifications received since the previous day, and logs the the property name, metric, frequency,
	* alert text, and a link to the alert in GA. It filters out insights you’ve already logged, and writes only the new records
	* into an “insights” sheet.