~/ endpoints /get-grouped-stats-redirects
id: 35e0c5fa-cac4-4cd4-b353-734a709acd4a
BigQuery SQL
SELECT
DATE_TRUNC('day', timestamp) AS group_time,
m.event_name,
m.country,
m.device,
m.language,
m.referrer,
m.utm_params,
COUNT(*) AS total_hits
FROM
"stats.events.logs"
CROSS JOIN UNNEST(metadata) AS m
WHERE
m.event_type = 'url_redirect'
AND (@event_name IS NULL OR m.event_name = @event_name)
AND (@country IS NULL OR m.country = @country)
AND (@device IS NULL OR m.device = @device)
AND (@language IS NULL OR m.language = @language)
AND (@referrer IS NULL OR m.referrer = @referrer)
AND (@utm_key IS NULL OR @utm_value IS NULL OR (m.utm_params IS NOT NULL AND (m.utm_params)::jsonb ->> @utm_key = @utm_value))
AND (@start_time IS NULL OR timestamp >= CAST(@start_time AS timestamp))
AND (@end_time IS NULL OR timestamp <= CAST(@end_time AS timestamp))
GROUP BY 1, 2, 3, 4, 5, 6, 7
ORDER BY 1 ASC, 2 ASC
- max rows: 1000
- caching: 300 seconds
- cache warming: 1800 seconds
- query sandboxing: disabled
SELECT DATE_TRUNC('day', timestamp) AS group_time, m.event_name, m.country, m.device, m.language, m.referrer, m.utm_params, COUNT(*) AS total_hits FROM "stats.events.logs" CROSS JOIN UNNEST(metadata) AS m WHERE m.event_type = 'url_redirect' AND (@event_name IS NULL OR m.event_name = @event_name) AND (@country IS NULL OR m.country = @country) AND (@device IS NULL OR m.device = @device) AND (@language IS NULL OR m.language = @language) AND (@referrer IS NULL OR m.referrer = @referrer) AND (@utm_key IS NULL OR @utm_value IS NULL OR (m.utm_params IS NOT NULL AND CAST((m.utm_params) AS jsonb) ->> @utm_key = @utm_value)) AND (@start_time IS NULL OR timestamp >= CAST(@start_time AS TIMESTAMP)) AND (@end_time IS NULL OR timestamp <= CAST(@end_time AS TIMESTAMP)) GROUP BY 1, 2, 3, 4, 5, 6, 7 ORDER BY 1 ASC, 2 ASC
Call your endpoint
# By UUID
curl "https://api.logflare.app/api/endpoints/query/35e0c5fa-cac4-4cd4-b353-734a709acd4a" \
-H 'X-API-KEY: YOUR-ACCESS-TOKEN' \
-H 'Content-Type: application/json; charset=utf-8' \
-G -d "end_time=VALUE" -d "start_time=VALUE" -d "utm_value=VALUE" -d "utm_key=VALUE" -d "referrer=VALUE" -d "language=VALUE" -d "device=VALUE" -d "country=VALUE" -d "event_name=VALUE"
# By name
curl "https://api.logflare.app/api/endpoints/query/get-grouped-stats-redirects" \
-H 'X-API-KEY: YOUR-ACCESS-TOKEN' \
-H 'Content-Type: application/json; charset=utf-8' \
-G -d "end_time=VALUE" -d "start_time=VALUE" -d "utm_value=VALUE" -d "utm_key=VALUE" -d "referrer=VALUE" -d "language=VALUE" -d "device=VALUE" -d "country=VALUE" -d "event_name=VALUE"