SQL (beta)

When the reports do not answer a question, you can ask it in SQL. Open your website in Quomerce and choose SQL (beta) in the menu. You only see this website’s data, and you can only read it.

An AI assistant connected to Quomerce (see Account settings → AI assistant access) can run the same queries for you. You can ask it in plain words, such as “which pages did visitors who left at checkout see last?”

Tables

Table	One row per
sessions	visit
events	thing a visitor did: a page view, a click, adding to cart, paying
errors	problem a visitor met: a script error, a failed request, a click that did nothing
orders	order
screen_text	text a page showed in a recorded visit

These follow the same rules as the reports: bots and visits without cookie consent are left out. Orders from visits without consent are still in orders, with an empty session_id. The page lists every column next to the editor.

screen_text is read out of session recordings, for visits that reached a product page or ordered. after_load = 1 means the text appeared after the page loaded, such as a message or a pop-up. Checkout, thank-you and account pages are hidden in recordings, so they have no text here. The text was on the page, but not always on the screen: it may have been under a banner or further down.

Examples

Visits per day, last two weeks:

SELECT toDate(started_at, 'Europe/Warsaw') AS day, count() AS visits
FROM sessions
WHERE session_date >= today() - 14
GROUP BY day
ORDER BY day

Orders and revenue by traffic source, last 30 days:

SELECT s.source, count() AS orders, sum(o.value) AS revenue
FROM orders AS o
INNER JOIN sessions AS s ON s.session_id = o.session_id
WHERE o.placed_at >= now() - INTERVAL 30 DAY
GROUP BY s.source
ORDER BY orders DESC

Most viewed pages, last 7 days:

SELECT page_path, count() AS views
FROM events
WHERE type = 'page_view' AND ts >= now() - INTERVAL 7 DAY
GROUP BY page_path
ORDER BY views DESC
LIMIT 20

Visits that saw “sold out” in the cart, last 7 days:

SELECT count(DISTINCT t.session_id) AS visits
FROM screen_text AS t
WHERE t.ts >= now() - INTERVAL 7 DAY
  AND positionCaseInsensitiveUTF8(t.text, 'sold out') > 0
  AND t.page_load_id IN (
    SELECT page_load_id FROM events
    WHERE type = 'page_view' AND page_type = 'cart' AND ts >= now() - INTERVAL 7 DAY)

Good to know

• Times are in UTC. Pass your time zone to get your own days, as in the first example.
• Money is in normal units (12.50), with the currency next to it.
• Filter on time first (ts, placed_at, session_date). Queries over a short period are faster.
• Limits. A query may run for 30 seconds and return up to 10,000 rows, and each website has an hourly limit. Without a LIMIT, you get the first 100 rows. Explain shows how much a query would read before you run it.
• The language is ClickHouse SQL.