# 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 %}