Skip to content

Instantly share code, notes, and snippets.

@nicobrx
nicobrx / adwords_report_scheduler.js
Created February 23, 2016 16:42
AdWords script - provides framework for scheduling reports in an MCC. Writes reports to a Google Sheet. Handles MCC limit of 50 accounts per script
/*
* written by Nico Brooks - www.twooctobers.com
* use at your own risk
*
* This script provides a generic framework for scheduling
* monthly AdWords reports and writing them to a Google sheet.
* To use, copy the script, set the global variables at the top of the file
* and customize the function getData. See
* https://developers.google.com/adwords/api/docs/guides/awql
* for reporting options
/**
* Writes a list of AdWords accounts in an MCC to a Google Sheet
* Useful in conjunction with scripts that run on batches of accounts
*
* The label-batching portion of this script was adapted from:
* http://magentosupport.help/knowledgebase/using-labels-to-executeinparallel-exceeding-the-50-account-limit/
*/
var TIMEZONE = 'MST';
var TODAY_STR = Utilities.formatDate(new Date(), TIMEZONE, 'yyyy-MM');
<script>
/*
* adapted from https://developers.google.com/analytics/devguides/collection/analyticsjs/cross-domain#iframes
* I include the GA object reference in my script, so that one version of
* the script works whether served in an iFrame or on a standalone page.
* I usually have to rely on the 3rd-party vendor to implement the code, so
* I like to keep implementation as simple as possible.
*/
(function(i,s,o,g,r,a,m){i['GoogleAnalyticsObject']=r;i[r]=i[r]||function(){
<script>
/*
* adapted from https://developers.google.com/analytics/devguides/collection/analyticsjs/cross-domain#iframes
* I wrapped the whole thing in a setTimeout, so I could observe the delayed execution time
* of the GA function in the JavaScript console, using the GA debugger Chrome plugin.
* alternatively, you could just wrap the frameWindow.postMessage in a setTimeout
*/
setTimeout(function(){
ga(function(tracker) {
<script>
if(!(window.ga && ga.create)) {
dataLayer.push({'event': 'gaNotLoaded'});
}
</script>
SELECT
event_date,
COUNT(DISTINCT(session_id)) AS sessions,
SUM(ga4_resource_page_views) AS ga4_resource_page_views
FROM (
SELECT
CONCAT(user_pseudo_id,(SELECT value.int_value FROM UNNEST(event_params) WHERE KEY = 'ga_session_id')) AS session_id,
COUNT((SELECT value.string_value FROM UNNEST(event_params) WHERE KEY = 'page_location'
AND value.string_value LIKE 'https://twooctobers.com/blog/ga4-resources/%')) AS ga4_resource_page_views,
PARSE_DATE('%Y%m%d',event_date) AS event_date
@nicobrx
nicobrx / ga4_average_engagement_time_per_page.sql
Created June 8, 2023 04:09
Query for getting average engagement time per page from GA4 BigQuery data.
SELECT
page_location,
SUM(engagement_time_seconds) AS engagement_time_seconds,
SUM(page_views) AS page_views,
SAFE_DIVIDE( SUM(engagement_time_seconds), SUM(page_views) ) AS average_engagement_time_per_page
FROM (
SELECT
(
SELECT
value.string_value
--Looker Studio BigQuery custom query to get sessions per user
with s as (select distinct
user_pseudo_id,
concat(user_pseudo_id,(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')) AS session_id,
timestamp_micros(event_timestamp) as timestamp
from `yourbqproject.analytics_1234567.events_*`
where _table_suffix between @DS_START_DATE and @DS_END_DATE
and event_name = 'session_start')
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_*`
@nicobrx
nicobrx / ga4_page_value.sql
Last active June 1, 2024 10:03
Gets a page_value metric from GA4 data in BigQuery that is similar to the Universal Analytics Page Value metric: https://support.google.com/analytics/answer/2695658 To use the query, replace the table references in lines 15 and 26 with your GA4 events table. Make sure to replace the table's date suffix with '*' so the query runs across date part…
-- set dates here so we don't have to update them in multiple places
with dates as (
select
'20230812' as start_date,
-- the next line gets yesterday
format_date('%Y%m%d',date_sub(current_date(), interval 1 day)) as end_date
),
-- get all events with event values
p as (