> 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 by asking a question.

Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:

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

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.

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.
