These SQL queries are useful for customers who integrate Datagran's Pixel data into their workspace/project. The text in BOLD shows the table names that need to be changed according to the users’ data schema.

Sessions in the last 30 days.

SELECT count(distinct(session_id)) as Sessions 
FROM **example_pixel_raw_logs** where hit_type='pv' 
	and CAST(CAST(hit_time_utc as DATETIME) as DATE) >= DATE_ADD(CURRENT_DATE(), INTERVAL -30 DAY);

Sessions per month

SELECT
    FORMAT_DATE('%Y-%m', CAST(CAST(hit_time_utc as DATETIME) as DATE)) as Month,
    count(distinct session_id) as Sessions
FROM
    `example_pixel`
WHERE
    hit_type = 'pv'
GROUP BY
    Month
ORDER BY
    Month;

Custom events in the last 30 days

SELECT JSON_EXTRACT_SCALAR(hit_payload, '$.en'), count(*) as Clicks 
FROM **example_pixel_raw_logs** where hit_type='ce' 
	and CAST(CAST(hit_time_utc as DATETIME) as DATE) >= DATE_ADD(CURRENT_DATE(), INTERVAL -30 DAY) group by 1 order by 2 desc

Visits from SQL Chart

SELECT count(session_id) as sessions, f_source FROM `**example_pixel_raw_logs**` 
where page_url = '**<https://www.example.com/**>' 
group by f_source
order by sessions desc

Homepage visits by country

SELECT country, count(session_id) as sessions  FROM `example_pixel_raw_logs` 
where page_url = '<https://www.example.com/>'
group by country
order by sessions desc

Homepage Daily visits

SELECT date(hit_time_utc) as fecha, count(session_id) as sessions FROM `example_pixel_raw_logs` 
where page_url = '<https://www.example.com/>'
group by fecha
order by fecha asc