Skip to content

Instantly share code, notes, and snippets.

View thomashandorf's full-sized avatar

Thomas Handorf thomashandorf

16 followers · 0 following
View GitHub Profile
@thomashandorf
thomashandorf / google_ads_gclid.sql
Created February 4, 2024 11:44
Get Google Ads hierarchy by gclid
with customer as (
select * except(rn) from (
select *,row_number() over (partition by customer_id order by _DATA_DATE desc) rn from `project`.`adwords`.`ads_Customer_1234567890`
)
where rn=1
),
campaigns as (
select * except(rn) from (
select *,row_number() over (partition by campaign_id order by _DATA_DATE desc) rn from `project`.`adwords`.`ads_Campaign_1234567890`
)
@thomashandorf
thomashandorf / coalesce_ifnull.sql
Created January 7, 2024 20:09
Joining incomplete data with COALESCE and IFNULL
SELECT
COALESCE(a.date, s.date) as date,
COALESCE(a.campaign, s.campaign) as campaign,
IFNULL(a.spent, 0) as spent,
IFNULL(a.impressions, 0) as impressions,
IFNULL(a.clicks, 0) as clicks,
IFNULL(s.sessions, 0) as sessions,
IFNULL(s.conversions, 0) as conversions
FROM
Ads a
@thomashandorf
thomashandorf / bqml_ga4.sql
Created December 18, 2023 08:34
BigQuery ML ARIMA Model on GA4 purchase data
CREATE OR REPLACE MODEL `your-project.arima_test.purchase_forecast`
OPTIONS(model_type='ARIMA_PLUS', time_series_timestamp_col='date', time_series_data_col='daily_purchases')
AS
SELECT
PARSE_DATE('%Y%m%d', event_date) as date,
COUNTIF(event_name = 'purchase') as daily_purchases
FROM
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
_TABLE_SUFFIX BETWEEN '20201101' AND '20211231'
@thomashandorf
thomashandorf / bigquery_billed_bytes_by_user.sql
Last active October 31, 2023 09:26
BigQuery billed byte by user
SELECT
user_email,
SUM(total_bytes_billed) AS bytes_billed_last_month
FROM
`region-europe-west1`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
EXTRACT(MONTH FROM creation_time) = EXTRACT(MONTH FROM CURRENT_DATE()) - 1
GROUP BY
user_email
ORDER BY
@thomashandorf
thomashandorf / google_sheets_dbt.sql
Last active March 21, 2024 05:03
BigQuery access Google Sheets for DBT
{{ config(materialized = 'table') }}
{%- call statement('create_ext_table', fetch_result=False) -%}
CREATE OR REPLACE EXTERNAL TABLE {{ this.database+"."+this.schema+"."+this.table+"_ref" }} (
Student_Name STRING,
Gender STRING,
Class_Level STRING,
Home_State STRING,
Major STRING,
@thomashandorf
thomashandorf / sidemenu_simple
Created August 4, 2018 21:19
<body lj-type="stage">
<div id="lcontent" lj-type="layer">
<div id="home" lj-type="frame">
... content ...
</div>
... optional: more content frames
</div>
<div id="lmenu" lj-type="layer" lj-default-frame="!none">
<div id="menu" lj-type="frame">
... menu items
@thomashandorf
thomashandorf / menu.html
Last active March 15, 2018 12:05
side menu demo
<body lj-type="stage">
<div lj-type="layer" id="content-layer" lj-fit-to="responsive-width" lj-transition="fade">
...
</div>
<div lj-type="layer" id="menu-layer" lj-native-scroll="false" lj-no-url="true">
<a lj-type="frame" id="hamburger" lj-transition="fade" lj-fit-to="fixed" lj-start-position="top-right" href="#main-menu">
M
</a>
<div lj-type="frame" id="main-menu" lj-transition="left" lj-fit-to="responsive-height" lj-start-position="top-right">
Menu Item<br>
@thomashandorf
thomashandorf / transition.html
Created March 15, 2018 10:51
transition to next frame
<body lj-type="stage">
<div lj-type="layer" id="content-layer" lj-fit-to="responsive-width" lj-transition="fade">
<div lj-type="frame" id="home">
Hello World
<br>
<a href="#more">Next</a>
</div>
<div lj-type="frame" id="more">
More content...
<br>
@thomashandorf
thomashandorf / layerJSdoc.html
Last active March 15, 2018 10:42
layerJS document structure
<html>
<head>
<script src="https://cdn.layerjs.org/libs/layerjs/layerjs-0.5.2.min.js" defer=""></script>
<link href="https://cdn.layerjs.org/libs/layerjs/layerjs-0.5.2.css" type="text/css" rel="stylesheet">
<link href="style.css" rel="stylesheet">
</head>
<body lj-type="stage">
<div lj-type="layer" id="content-layer" lj-fit-to="responsive-width">
<div lj-type="frame" id="home">
Hello World