thankful-minister-34216
09/18/2023, 10:25 PMbrief-honey-45610
09/19/2023, 9:15 PMthankful-minister-34216
09/19/2023, 9:22 PMbrief-honey-45610
09/19/2023, 9:43 PMthankful-minister-34216
09/19/2023, 9:44 PMbrief-honey-45610
09/19/2023, 9:44 PMthankful-minister-34216
09/19/2023, 9:45 PMbrief-honey-45610
09/19/2023, 9:46 PMthankful-minister-34216
09/19/2023, 9:46 PMbrief-honey-45610
09/19/2023, 9:48 PMthankful-minister-34216
09/20/2023, 4:26 PMbrief-honey-45610
09/20/2023, 4:49 PMthankful-minister-34216
09/20/2023, 5:23 PMbrief-honey-45610
09/20/2023, 7:25 PMthankful-minister-34216
09/20/2023, 7:26 PMbrief-honey-45610
09/22/2023, 6:07 PMthankful-minister-34216
09/22/2023, 6:36 PMbrief-honey-45610
09/23/2023, 1:36 AM-- Site-Wide - Bounce Rate Metric
WITH __pageviews as (
SELECT
user_id as user_id,
user_id as anonymous_id,
received_at as timestamp,
path as url
FROM
pages
),
__users as (
-- Users visiting specific pages
SELECT
p.anonymous_id as user_id,
MIN(p.timestamp) as actual_start,
MIN(p.timestamp) + INTERVAL '72 hours' as conversion_end,
MIN(p.timestamp) - INTERVAL '30 minutes' as session_start
FROM
__pageviews p
WHERE
p.timestamp >= '2021-10-27 00:00:00'
AND p.timestamp <= '2022-01-25 00:00:00'
GROUP BY
p.anonymous_id
),
__metric as (
-- Metric (Bounce Rate)
SELECT
m.anonymous_id as user_id,
1 as value,
m.date_end as actual_start,
m.date_end + INTERVAL '72 hours' as conversion_end,
m.date_end - INTERVAL '30 minutes' as session_start
FROM
sessions m
WHERE
m.num_pages = '1'
AND m.date_end >= '2021-10-27 02:42:21'
AND m.date_end <= '2022-01-28 03:12:21'
),
__distinctUsers as (
SELECT
u.user_id,
MIN(u.conversion_end) as conversion_end,
MIN(u.session_start) as session_start,
MIN(u.actual_start) as actual_start
FROM
__users u
GROUP BY
u.user_id
),
__userMetric as (
-- Add in the aggregate metric value for each user
SELECT
1 as value
FROM
__distinctUsers d
JOIN __metric m ON (m.user_id = d.user_id)
WHERE
m.actual_start >= d.session_start
AND m.actual_start <= d.conversion_end
GROUP BY
d.user_id
),
__overallUsers as (
-- Number of users overall
SELECT
COUNT(*) as users
FROM
__distinctUsers
),
__overall as (
SELECT
COUNT(*) as count,
AVG(value:: float) as mean,
STDDEV(value) as stddev
from
__userMetric
),
__userMetricDates as (
-- Add in the aggregate metric value for each user
SELECT
date_trunc('day', d.actual_start) as date,
1 as value
FROM
__distinctUsers d
JOIN __metric m ON (m.user_id = d.user_id)
WHERE
m.actual_start >= d.session_start
AND m.actual_start <= d.conversion_end
GROUP BY
date_trunc('day', d.actual_start),
d.user_id
),
__usersByDate as (
-- Number of users by date
SELECT
date_trunc('day', actual_start) as date,
COUNT(*) as users
FROM
__distinctUsers
GROUP BY
date_trunc('day', actual_start)
),
__byDateOverall as (
SELECT
date,
COUNT(*) as count,
AVG(value:: float) as mean,
STDDEV(value) as stddev
FROM
__userMetricDates d
GROUP BY
date
)
SELECT
null as
date,
o.*,
u.users
FROM
__overall o
JOIN __overallUsers u ON (1 = 1)
UNION ALL
SELECT
o.*,
u.users
FROM
__byDateOverall o
JOIN __usersByDate u ON (o.date = u.date)
ORDER BY
date ASC
thankful-minister-34216
09/27/2023, 7:05 PMsessions
. Not sure if this was not clear but that is the table I'm trying to figure out how to create based on the GA Bigquery event data:SELECT
user_id,
user_pseudo_id AS anonymous_id,
MIN(TIMESTAMP_MICROS(event_timestamp)) as timestamp,
concat(user_pseudo_id,(select value.int_value FROM unnest(event_params) WHERE key = 'ga_session_id')) as session_id,
countif(event_name = 'page_view') as views,
FROM
*** your table ***
WHERE
((_TABLE_SUFFIX BETWEEN '{{date startDateISO "yyyyMMdd"}}' AND '{{date endDateISO "yyyyMMdd"}}')
OR (_TABLE_SUFFIX BETWEEN 'intraday_{{date startDateISO "yyyyMMdd"}}' AND 'intraday_{{date endDateISO "yyyyMMdd"}}'))
group by
user_id,
user_pseudo_id,
session_id
HAVING views = 1