crooked-telephone-14504
01/02/2024, 11:35 AMcrooked-telephone-14504
01/02/2024, 11:43 AMCREATE TABLE sessions (
session_id INT PRIMARY KEY,
user_id INT,
timestamp TIMESTAMP,
page_name VARCHAR(255),
experiment_variant VARCHAR(50)
);
user_id
is an optional field only populated with logged in users, but we also support conversion for “guest” users in which case it would be null
conversions table create statement:
CREATE TABLE conversions (
conversion_id INT PRIMARY KEY,
user_id INT,
session_id INT,
timestamp TIMESTAMP
);
based on these 2 tables we have also setup 2 key metrics:
_*page_sessions*_ which uses the following query:
SELECT
session_id as user_id,
timestamp as timestamp
FROM
sessions
conversions which uses the following query:
SELECT
session_id as user_id,
timestamp as timestamp
FROM
conversions
fresh-football-47124
fresh-football-47124
fresh-football-47124
fresh-football-47124
fresh-football-47124
fresh-football-47124
crooked-telephone-14504
01/04/2024, 8:07 AMcrooked-telephone-14504
01/04/2024, 8:09 AMcrooked-telephone-14504
01/04/2024, 8:10 AMfresh-football-47124