I am just starting out with GrowthBook and I am trying to use it to analyze a database I created with some mock data. Here are the columns in the database:
id
received_at
context_library_name
context_library_version
title
url
uuid_ts
context_adobe_analytics_marketing_cloud_visitor_id
context_page_title
original_timestamp
timestamp
user_id
anonymous_id
context_page_referrer
path
sent_at
context_user_agent
page_load_time
browser_number
The hypothesis is that page speed can be enhanced by using a different browser. This is the query I created to view the data in GrowthBook:
SELECT
anonymous_id,
received_at as timestamp,
"23" as experiment_id,
"46" as variation_id,
page_load_time,
(
CASE
WHEN context_user_agent LIKE '%Mobile%' THEN 'Mobile'
ELSE 'Tablet/Desktop' END
) as device,
(
CASE
WHEN context_user_agent LIKE '% Firefox%' THEN 'Firefox'
WHEN context_user_agent LIKE '% OPR%' THEN 'Opera'
WHEN context_user_agent LIKE '% Edg%' THEN ' Edge'
WHEN context_user_agent LIKE '% Chrome%' THEN 'Chrome'
WHEN context_user_agent LIKE '% Safari%' THEN 'Safari'
ELSE 'Other' END
) as browser
FROM
pages
WHERE
anonymous_id is not null
AND page_load_time is not null
When I attempt to view the results of the experiment it says "syntax error at or near ";". This is a partial view of the long SQL query that GrowthBook created. How did the ":" get in there and how can I remove it? (Also, can I use this database with GrowthBook? Am I on the right track? Now that I am thinking of this, perhaps the browser_number column should be a variation_id).
-- page_load_time (duration)
WITH __rawExperiment as (
SELECT
anonymous_id,
received_at as timestamp,
"23" as experiment_id,
"46" as variation_id,
page_load_time,
(
CASE
WHEN context_user_agent LIKE '%Mobile%' THEN 'Mobile'
ELSE 'Tablet/Desktop' END
) as device,
(
CASE
WHEN context_user_agent LIKE '% Firefox%' THEN 'Firefox'
WHEN context_user_agent LIKE '% OPR%' THEN 'Opera'
WHEN context_user_agent LIKE '% Edg%' THEN ' Edge'
WHEN context_user_agent LIKE '% Chrome%' THEN 'Chrome'
WHEN context_user_agent LIKE '% Safari%' THEN 'Safari'
ELSE 'Other' END
) as browser
FROM
pages
WHERE
anonymous_id is not null
AND page_load_time is not null
),
__experiment as (
-- Viewed Experiment
SELECT
e.anonymous_id as anonymous_id,
cast(e.variation_id as varchar) as variation,
e.timestamp as conversion_start,
e.timestamp + INTERVAL '72 hours' as conversion_end
FROM
__rawExperiment e
WHERE
e.experiment_id = 'pageloadtime'
AND e.timestamp >= '2022-08-18 175000'
),
__metric as (
-- Metric (page_load_time)
SELECT
anonymous_id as anonymous_id,
m.value as value,
m.timestamp as conversion_start,
m.timestamp as conversion_end
FROM
(
SELECT
user_id,
anonymous_id,
received_at as timestamp,
page_load_time as value
FROM
pages
WHERE
page_load_time IS NOT NULL;
) m
WHERE
m.timestamp >= '2022-08-18 175000'
),
__distinctUsers as (