> For the complete documentation index, see [llms.txt](https://docs.edgetag.io/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.edgetag.io/mcp/tools/edge-lake-query.md).

# 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.

{% hint style="warning" %}
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.
{% endhint %}

### 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"

```sql
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?"

```sql
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
```

{% hint style="info" %}
Edge Lake Querying must be enabled for your domain. Contact <support@blotout.io> if you get an "Action not permitted!" error.
{% endhint %}


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://docs.edgetag.io/mcp/tools/edge-lake-query.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.