edgeLakeQuery

Run SQL queries against the Edge Lake event data lake. Edge Lake stores all website and shop events (page views, purchases, add to carts, etc.) in Cloudflare R2 and makes them queryable via SQL.

Use dedicated analytics tools first (domainAnalytics, domainErrors, edgeLakeTrafficAnalysis) before falling back to Edge Lake queries. Edge Lake is best for custom queries that dedicated tools can't answer.

Parameters

Parameter	Type	Required	Description
channelId	string (UUID)	Yes	The Edge Lake channel ID. Must be a channel with providerId: "edgeLake". Get this from the domains tool.
teamId	string (UUID)	Yes	The team ID the domain belongs to.
sql	string	Yes	SQL SELECT query against lake.events. Always include event_timestamp range filters for performance.

SQL limitations

Edge Lake uses R2 SQL which has specific constraints:

Supported:

• WHERE with =, !=, <, >, BETWEEN, LIKE (prefix only)
• GROUP BY, HAVING, ORDER BY
• LIMIT (max 10,000 rows)

Not supported:

• JOINs, subqueries, CTEs, UNION
• Window functions
• IN lists
• Functions in WHERE clauses
• Arithmetic in queries
• Aliases

Event schema

The lake.events table contains the following key columns:

Event columns

Column	Description
event_sessionId	Session identifier
event_timestamp	Event timestamp
event_id	Unique event ID
event_name	Event name (PageView, Purchase, AddToCart, etc.)
event_pageUrl	Page URL where event fired
event_referrer	Referrer URL
event_origin	Event origin

Payload columns

Column	Description
payload_value	Transaction value
payload_currency	Currency code
payload_orderId	Order identifier
payload_search	Search query

User columns

Column	Description
user_id	User identifier
user_email	User email
user_firstName	First name
user_lastName	Last name
user_phone	Phone number
user_country	Country
user_city	City
user_state	State
user_zip	Postal code

Geo columns

Column	Description
geo_country	Country (from IP geolocation)
geo_city	City
geo_region	Region
geo_timezone	Timezone
geo_continent	Continent
geo_isEUCountry	Whether the user is in an EU country

Attribution columns

Column	Description
attribution_isNewCustomer	Whether this is a new customer
attribution_session_fc_utm_source	First-click UTM source
attribution_session_fc_utm_medium	First-click UTM medium
attribution_session_fc_utm_campaign	First-click UTM campaign
attribution_session_lc_utm_source	Last-click UTM source
attribution_session_lc_utm_medium	Last-click UTM medium
attribution_session_lc_utm_campaign	Last-click UTM campaign

Examples

"Show me all purchases from today with their values"

SELECT event_timestamp, payload_value, payload_currency, payload_orderId, user_email
FROM lake.events
WHERE event_name = 'Purchase'
  AND event_timestamp BETWEEN '2024-01-01T00:00:00Z' AND '2024-01-01T23:59:59Z'
ORDER BY event_timestamp DESC
LIMIT 100

"What are the top pages by page views this week?"

SELECT event_pageUrl, COUNT(*) as views
FROM lake.events
WHERE event_name = 'PageView'
  AND event_timestamp BETWEEN '2024-01-01T00:00:00Z' AND '2024-01-07T23:59:59Z'
GROUP BY event_pageUrl
ORDER BY views DESC
LIMIT 20

Edge Lake Querying must be enabled for your domain. Contact [e-mail] if you get an "Action not permitted!" error.