lacerogers20

 
# checks for the ga4 table 
DECLARE   ga4_table_updated date;
#checks if the table has been updated and contains data within it
DECLARE   secondary_table  INT64;
 #selects the date the updates start from 
DECLARE   secondary_table_update date;  
# selects table name
DECLARE  dest_table_name string;
# selects table name

lacerogers20

 DECLARE
  SQLRUN STRING DEFAULT '';
CREATE TEMP FUNCTION
  ga4_firebase( key1 STRING,
    params ARRAY <STRUCT <key STRING,
    value STRUCT <string_value STRING,
    int_value INT64,
    float_value FLOAT64,
    double_value FLOAT64 >>>) AS ( (
    SELECT

lacerogers20

 
CREATE TEMP FUNCTION
  PROPER(str STRING) AS ((
    SELECT
      STRING_AGG(CONCAT(UPPER(SUBSTR(w,1,1)), LOWER(SUBSTR(w,2))), ' '
      ORDER BY
        pos)
    FROM
      UNNEST(SPLIT(str, ' ')) w
    WITH

lacerogers20

CREATE TABLE IF NOT EXISTS `testing.insert` AS
  (
 
  SELECT
    date,
    COUNT(DISTINCT(visitid)) AS unique_visits
  FROM
    `bigquery-public-data.google_analytics_sample.ga_sessions_*`
  WHERE
    _table_suffix = '20170801'

lacerogers20

CREATE TABLE
  `testing.insert` (date DATE OPTIONS( description='date' ),
    unique_visits INT64 OPTIONS( description='counts' ))
PARTITION BY
  date
CLUSTER BY
  unique_visits OPTIONS ( description="A table with a date partition",
    labels=[("testing",
      "data_is_great")],
      expiration_timestamp=TIMESTAMP "2023-01-01 00:00:00 UTC"

lacerogers20

SELECT 
date,
COUNT(DISTINCT(visitid)) as unique_visits
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*` 
WHERE _table_suffix = '20170801'   # use date_sub to programmatically select your table
GROUP BY date
ORDER BY date