references/example-queries.md
Example queries for autocapture exploration
All queries filter by timestamp — adjust the interval to match your analysis window.
Contents
- Confirm autocapture exists
- Top clicked tag names
- Top clicked text values
- Top clicked hrefs
- Sample raw elements_chain for a page
- Find elements with data-attr attributes
- Find all data-* attribute keys in use
- Test selector uniqueness
- Sample matching events to inspect captures
- Refine with text filter
- Refine with URL filter
- Ad-hoc trends: count matching clicks over time
- Ad-hoc trends: breakdown by page
- Ad-hoc funnel: pageview to click
- Verify an action matches correctly
Confirm autocapture exists
SELECT count() as cnt
FROM events
WHERE event = '$autocapture'
AND timestamp > now() - INTERVAL 7 DAY
Top clicked tag names
SELECT
arrayJoin(elements_chain_elements) as tag,
count() as cnt
FROM events
WHERE event = '$autocapture'
AND timestamp > now() - INTERVAL 7 DAY
GROUP BY tag
ORDER BY cnt DESC
LIMIT 20
Top clicked text values
SELECT
arrayJoin(elements_chain_texts) as text,
count() as cnt
FROM events
WHERE event = '$autocapture'
AND timestamp > now() - INTERVAL 7 DAY
AND length(text) > 0
GROUP BY text
ORDER BY cnt DESC
LIMIT 30
Top clicked hrefs
SELECT
elements_chain_href as href,
count() as cnt
FROM events
WHERE event = '$autocapture'
AND timestamp > now() - INTERVAL 7 DAY
AND elements_chain_href != ''
GROUP BY href
ORDER BY cnt DESC
LIMIT 20
Sample raw elements_chain for a page
Replace the URL pattern to match the page of interest.
SELECT
elements_chain,
count() as cnt
FROM events
WHERE event = '$autocapture'
AND timestamp > now() - INTERVAL 7 DAY
AND properties.$current_url ILIKE '%/pricing%'
AND elements_chain != ''
GROUP BY elements_chain
ORDER BY cnt DESC
LIMIT 10
Find elements with data-attr attributes
SELECT
arrayJoin(extractAll(elements_chain, 'data-attr="([^"]*)"')) as data_attr_value,
count() as cnt
FROM events
WHERE event = '$autocapture'
AND timestamp > now() - INTERVAL 7 DAY
AND match(elements_chain, 'data-attr=')
GROUP BY data_attr_value
ORDER BY cnt DESC
LIMIT 20
Find all data-* attribute keys in use
Useful for discovering which data attributes the application sets on interactive elements.
SELECT
arrayJoin(extractAll(elements_chain, '(data-[a-zA-Z0-9_-]+)=')) as data_key,
count() as cnt
FROM events
WHERE event = '$autocapture'
AND timestamp > now() - INTERVAL 7 DAY
AND match(elements_chain, 'data-')
GROUP BY data_key
ORDER BY cnt DESC
LIMIT 20
Test selector uniqueness
Replace the regex pattern with one matching your candidate selector. See elements-chain-format.md for how selectors map to regex.
SELECT count() as matching_events
FROM events
WHERE event = '$autocapture'
AND timestamp > now() - INTERVAL 7 DAY
AND elements_chain =~ '(^|;)button.*?data-attr="checkout"'
Sample matching events to inspect captures
Verify that the selector matches only the intended interaction by inspecting what it captures.
SELECT
elements_chain,
properties.$current_url as url,
elements_chain_texts as texts,
timestamp
FROM events
WHERE event = '$autocapture'
AND timestamp > now() - INTERVAL 7 DAY
AND elements_chain =~ '(^|;)button.*?data-attr="checkout"'
ORDER BY timestamp DESC
LIMIT 10
Refine with text filter
SELECT count() as matching_events
FROM events
WHERE event = '$autocapture'
AND timestamp > now() - INTERVAL 7 DAY
AND elements_chain =~ '(^|;)button.*?data-attr="checkout"'
AND arrayExists(x -> x = 'Complete Purchase', elements_chain_texts)
Refine with URL filter
SELECT count() as matching_events
FROM events
WHERE event = '$autocapture'
AND timestamp > now() - INTERVAL 7 DAY
AND elements_chain =~ '(^|;)button.*?data-attr="checkout"'
AND properties.$current_url ILIKE '%/checkout%'
Ad-hoc trends: count matching clicks over time
SELECT
toStartOfDay(timestamp) as day,
count() as clicks
FROM events
WHERE event = '$autocapture'
AND timestamp > now() - INTERVAL 14 DAY
AND elements_chain =~ '(^|;)button.*?data-attr="checkout"'
GROUP BY day
ORDER BY day
Ad-hoc trends: breakdown by page
SELECT
properties.$current_url as url,
count() as clicks
FROM events
WHERE event = '$autocapture'
AND timestamp > now() - INTERVAL 7 DAY
AND elements_chain =~ '(^|;)button.*?data-attr="checkout"'
GROUP BY url
ORDER BY clicks DESC
LIMIT 20
Ad-hoc funnel: pageview to click
SELECT
person_id,
first_pageview,
first_click_after
FROM (
SELECT
p.person_id,
p.pageview_time as first_pageview,
min(c.click_time) as first_click_after
FROM (
SELECT person_id, min(timestamp) as pageview_time
FROM events
WHERE event = '$pageview'
AND timestamp > now() - INTERVAL 14 DAY
AND properties.$current_url ILIKE '%/pricing%'
GROUP BY person_id
) p
INNER JOIN (
SELECT person_id, timestamp as click_time
FROM events
WHERE event = '$autocapture'
AND timestamp > now() - INTERVAL 14 DAY
AND elements_chain =~ '(^|;)button.*?data-attr="signup"'
) c ON p.person_id = c.person_id AND c.click_time > p.pageview_time
GROUP BY p.person_id, p.pageview_time
)
Verify an action matches correctly
After creating an action, verify it captures the right events.
SELECT count() as matching_events
FROM events
WHERE matchesAction('Clicked checkout button')
AND timestamp > now() - INTERVAL 7 DAY