Skip to content

Instantly share code, notes, and snippets.

View jhnvdw's full-sized avatar

jhnvdw

7 followers · 0 following
  • Netherlands
View GitHub Profile
@jhnvdw
jhnvdw / bigquery_export.csv
Created June 1, 2019 07:32
Field Name Data Type Description
clientId STRING Unhashed version of the Client ID for a given user associated with any given visit/session.
fullVisitorId STRING The unique visitor ID (also known as client ID).
visitorId NULL This field is deprecated. Use "fullVisitorId" instead.
userId STRING Overridden User ID sent to Analytics.
visitNumber INTEGER The session number for this user. If this is the first session, then this is set to 1.
visitId INTEGER An identifier for this session. This is part of the value usually stored as the _utmb cookie. This is only unique to the user. For a completely unique ID, you should use a combination of fullVisitorId and visitId.
visitStartTime INTEGER The timestamp (expressed as POSIX time).
date STRING The date of the session in YYYYMMDD format.
totals RECORD This section contains aggregate values across the session.
@jhnvdw
jhnvdw / nested_example.csv
Last active December 17, 2020 18:54
We can make this file beautiful and searchable if this error is corrected: Unclosed quoted field in line 1.
"visitorId","visitNumber","visitId","visitStartTime","date","totals__visits","totals__hits","totals__pageviews","totals__timeOnSite","totals__bounces","totals__transactions","totals__transactionRevenue","totals__newVisits","totals__screenviews","totals__uniqueScreenviews","totals__timeOnScreen","totals__totalTransactionRevenue","totals__sessionQualityDim","trafficSource__referralPath","trafficSource__campaign","trafficSource__source","trafficSource__medium","trafficSource__keyword","trafficSource__adContent","trafficSource__adwordsClickInfo__campaignId","trafficSource__adwordsClickInfo__adGroupId","trafficSource__adwordsClickInfo__creativeId","trafficSource__adwordsClickInfo__criteriaId","trafficSource__adwordsClickInfo__page","trafficSource__adwordsClickInfo__slot","trafficSource__adwordsClickInfo__criteriaParameters","trafficSource__adwordsClickInfo__gclId","trafficSource__adwordsClickInfo__customerId","trafficSource__adwordsClickInfo__adNetworkType","trafficSource__adwordsClickInfo__targetingCriteria","tra
@jhnvdw
jhnvdw / user.csv
Last active May 31, 2022 00:26
User_Type Count_of_Sessions Users New_Users Percentage_New_Sessions Number_of_Sessions_per_User Hits
New Visitor 1 701623 701623 0.9979560777174068 1.0020481084571058 2814526
Returning Visitor 2 92243 0 0.0 1.0033064839608425 557066
Returning Visitor 3 35734 0 0.0 1.0030503162254436 251570
Returning Visitor 4 19105 0 0.0 1.0027218005757654 140295
Returning Visitor 5 11578 0 0.0 1.0031957160131284 85623
Returning Visitor 6 7653 0 0.0 1.0031360250882007 57300
Returning Visitor 7 5398 0 0.0 1.0027788069655428 40479
Returning Visitor 8 4023 0 0.0 1.001988565746955 29078
Returning Visitor 9 3075 0 0.0 1.0029268292682927 24962
@jhnvdw
jhnvdw / user.sql
Last active May 31, 2022 00:25
SELECT
-- User Type (dimension)
CASE
WHEN totals.newVisits = 1 THEN 'New Visitor'
ELSE
'Returning Visitor'
END
AS User_Type,
-- Count of Sessions (dimension)
visitNumber AS Count_of_Sessions,
@jhnvdw
jhnvdw / session.sql
Last active May 31, 2022 00:27
SELECT
-- Sessions (metric)
COUNT(DISTINCT CONCAT(fullVisitorId, CAST(visitStartTime AS STRING))) AS Sessions,
-- Bounces (metric)
COUNT(DISTINCT
CASE
WHEN totals.bounces = 1 THEN CONCAT(fullVisitorId, CAST(visitStartTime AS STRING))
ELSE
NULL
END
@jhnvdw
jhnvdw / time.sql
Last active May 31, 2022 00:27
SELECT
-- Date (dimension)
date AS Date,
-- Year (dimension)
FORMAT_DATE('%Y', PARSE_DATE("%Y%m%d",
date)) AS Year,
-- ISO Year (dimension)
FORMAT_DATE('%G', PARSE_DATE("%Y%m%d",
date)) AS ISO_Year,
-- Month of Year (dimension)
@jhnvdw
jhnvdw / time.csv
Last active May 31, 2022 00:27
We can make this file beautiful and searchable if this error is corrected: It looks like row 10 should actually have 16 columns, instead of 14. in line 9.
Date,Year,ISO_Year,Month_of_Year,Month_of_the_Year,Week_of_Year,Week_of_the_Year,ISO_Week_of_the_Year,ISO_Week_of_ISO_Year,Day_of_the_Month,Day_of_Week,Day_of_Week_Name,Hour_of_Day,Hour,Minute,Date_Hour_and_Minute
20170801,2017,2017,201708,08,201731,31,31,201731,01,2,Tuesday,2017080123,23,59,201708012359
20170801,2017,2017,201708,08,201731,31,31,201731,01,2,Tuesday,2017080123,23,58,201708012358
20170801,2017,2017,201708,08,201731,31,31,201731,01,2,Tuesday,2017080123,23,58,201708012358
20170801,2017,2017,201708,08,201731,31,31,201731,01,2,Tuesday,2017080123,23,57,201708012357
20170801,2017,2017,201708,08,201731,31,31,201731,01,2,Tuesday,2017080123,23,57,201708012357
20170801,2017,2017,201708,08,201731,31,31,201731,01,2,Tuesday,2017080123,23,57,201708012357
20170801,2017,2017,201708,08,201731,31,31,201731,01,2,Tuesday,2017080123,23,56,201708012356
20170801,2017,2017,201708,08,201731,31,31,201731,01,2,Tuesday,2017080123,23,55,201708012355
20170801,2017,2017,201708,08,201731,31,31,201731,01,2,Tuesday,2017080123,2
@jhnvdw
jhnvdw / traffic.sql
Last active May 31, 2022 00:28
SELECT
-- Referral Path (dimension)
trafficSource.referralPath AS Referral_Path,
-- Full Referrer (dimension)
CONCAT(trafficSource.source,trafficSource.referralPath) AS Full_Referrer,
-- Default Channel Grouping
channelGrouping AS Default_Channel_Grouping,
-- Campaign (dimension)
trafficSource.campaign AS Campaign,
-- Source (dimension)
@jhnvdw
jhnvdw / traffic.csv
Created July 16, 2019 21:04
We can make this file beautiful and searchable if this error is corrected: It looks like row 7 should actually have 12 columns, instead of 10. in line 6.
Referral_Path,Full_Referrer,Default_Channel_Grouping,Campaign,Source,Medium,Source_Medium,Keyword,Ad_Content,Social_Network,Social_Source,Campaign_Code
/YKEI_mrn/items/c10b14f9a69ff71b1b7a,qiita.com/YKEI_mrn/items/c10b14f9a69ff71b1b7a,Referral,(not set),qiita.com,referral,qiita.com / referral,,,(not set),No,
/yt/about/el/,youtube.com/yt/about/el/,Social,(not set),youtube.com,referral,youtube.com / referral,,,YouTube,Yes,
/presentation/d/1kGcCjdaigfoo5CMJ-gcBnf4_zdPfCBSk_iabtMZPN6s/pub,docs.google.com/presentation/d/1kGcCjdaigfoo5CMJ-gcBnf4_zdPfCBSk_iabtMZPN6s/pub,Referral,(not set),docs.google.com,referral,docs.google.com / referral,,,(not set),No,
,,Organic Search,(not set),google,organic,google / organic,www google,,(not set),No,
/mail/mu/mp/54/,mail.google.com/mail/mu/mp/54/,Referral,(not set),mail.google.com,referral,mail.google.com / referral,,,(not set),No,
/intl/pt-BR/yt/about/experiences/,youtube.com/intl/pt-BR/yt/about/experiences/,Social,(not set),youtube.com,referral,youtube.com / referral,,,YouTub
@jhnvdw
jhnvdw / geo.sql
Last active May 31, 2022 00:29
SELECT
-- Continent (dimension)
geoNetwork.continent AS Continent,
-- Sub Continent (dimension)
geoNetwork.subContinent AS Sub_Continent,
-- Country (dimension)
geoNetwork.country AS Country,
-- Region (dimension)
geoNetwork.region AS Region,
-- Metro (dimension)