12345otel_traces                              otel_logs
+-- span: pageview "/"                  +-- log: "button_click"
|   +-- span: fetch GET /api/user        +-- log: "form_submit"
+-- span: pageview "/pricing"           +-- log: "purchase"
+-- span: pageview "/dashboard"

123456789101112131415161718Browser (OTel SDK)                     Collector (Cloudflare Worker)
+-------------------------------+      +----------------------------------+
| Resource attributes:          |      |                                  |
|   browser.platform            |      |  Reads from request headers:     |
|   browser.mobile              | ---> |    CF-IPCountry -> geo.country   |
|   browser.language            |      |    User-Agent   -> user_agent    |
|   browser.brands              |      |                                  |
|   user_agent.original         |      |  Injects into span_attributes    |
|                               |      |  of every row                    |
| Span attributes:              |      |                                  |
|   session.id                  |      |  Writes to otel_traces           |
|   visitor.id                  |      |         |                        |
|   url.path                    |      |         |                        |
|   url.query                   |      |         v                        |
|   url.full                    |      |  MV fires automatically          |
|   http.request.header.referer |      |    otel_analytics_pages          |
|   user.id                     |      |    otel_analytics_sessions       |
+-------------------------------+      +----------------------------------+

1234567const sessionId =
  sessionStorage.getItem('strada.session_id') ??
  (() => {
    const id = crypto.randomUUID()
    sessionStorage.setItem('strada.session_id', id)
    return id
  })()

12345678910{
  "service.name": "my-app",
  "service.version": "1.4.2",
  "deployment.environment.name": "production",
  "browser.platform": "macOS",
  "browser.mobile": false,
  "browser.language": "en-US",
  "browser.brands": "Google Chrome 147, Chromium 147",
  "user_agent.original": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) ..."
}

123456789101112131415161718192021222324{
  "traceId": "4bf92f3577b34da6a3ce929d0e0e4736",
  "spanId": "00f067aa0ba902b7",
  "parentSpanId": null,
  "name": "pageview",
  "kind": "INTERNAL",
  "startTimeUnixNano": "1711541826000000000",
  "endTimeUnixNano": "1711541871000000000",
  "attributes": {
    "session.id": "f47ac10b-58cc-4372-a567-0e02b2c3d479",
    "visitor.id": "c0a80123-58cc-4372-a567-0e02b2c3d479",
    "url.full": "https://app.acme.com/pricing",
    "url.path": "/pricing",
    "url.query": "?plan=pro",
    "http.request.header.referer": "https://google.com",
    "user.id": "user_123",
    "geo.country": "IT"
  },
  "resource": {
    "service.name": "my-app",
    "service.version": "1.4.2",
    "browser.platform": "macOS"
  }
}

123456789101112131415161718192021222324{
  "timeUnixNano": "1711541840000000000",
  "severityNumber": 9,
  "severityText": "INFO",
  "body": "button_click",
  "traceId": "4bf92f3577b34da6a3ce929d0e0e4736",
  "spanId": "00f067aa0ba902b7",
  "attributes": {
    "event.name": "button_click",
    "session.id": "f47ac10b-58cc-4372-a567-0e02b2c3d479",
    "visitor.id": "c0a80123-58cc-4372-a567-0e02b2c3d479",
    "url.path": "/pricing",
    "url.full": "https://app.acme.com/pricing",
    "user.id": "user_123",
    "custom.element": "cta-hero",
    "custom.text": "Start free trial",
    "custom.plan": "pro"
  },
  "resource": {
    "service.name": "my-app",
    "service.version": "1.4.2",
    "browser.platform": "macOS"
  }
}

12345678910111213141516{
  "traceId": "4bf92f3577b34da6a3ce929d0e0e4736",
  "spanId": "b3d7c2e1f8a90b4c",
  "parentSpanId": "00f067aa0ba902b7",
  "name": "GET /api/plans",
  "kind": "CLIENT",
  "startTimeUnixNano": "1711541828000000000",
  "endTimeUnixNano": "1711541829450000000",
  "attributes": {
    "http.request.method": "GET",
    "url.full": "https://api.acme.com/api/plans",
    "http.response.status_code": 200,
    "session.id": "f47ac10b-58cc-4372-a567-0e02b2c3d479",
    "visitor.id": "c0a80123-58cc-4372-a567-0e02b2c3d479"
  }
}

1234span: documentLoad "/"             [0ms -> 1240ms]
+-- span: resourceFetch "main.js"  [45ms -> 320ms]
+-- span: resourceFetch "app.css"  [45ms -> 180ms]
+-- span: fetchRequest "GET /api"  [800ms -> 1100ms]

1navigation.addEventListener('navigate', ...) // fires on every SPA route change

12345import { startPageSpan, endCurrentPageSpan } from '@strada.sh/sdk'

// on route change
endCurrentPageSpan()
startPageSpan(newPath)

12345678910111213141516171819202122import { initStrada, track, identifyUser } from '@strada.sh/sdk'

initStrada({
  projectId: '01JTHG5M7XPQR8KNCZ0W4D',
  service: 'frontend',
})

// Simple event
track('button_click')

// Event with properties
track('form_submit', {
  form: 'signup',
  plan: 'pro',
  variant: 'hero-cta',
})

identifyUser({ id: 'user_123' })

track('billing_opened', {
  plan: 'pro',
})

12345678910111213logger.emit({
  severityNumber: 9,  // INFO
  body: 'button_click',
  attributes: {
    'event.name': 'form_submit',
    'session.id': currentSessionId,
    'url.path': window.location.pathname,
    'url.full': window.location.href,
    'user.id': currentUserId,
    'custom.form': 'signup',
    'custom.plan': 'pro',
  },
})

12345678910111213141516171819// Node.js backend
import { logs } from '@opentelemetry/api-logs'

const logger = logs.getLogger('my-api')

app.post('/api/checkout', async (req, res) => {
  // The active span is auto-created by the OTel HTTP instrumentation.
  // This log automatically inherits the span's TraceId + SpanId.
  logger.emit({
    body: 'purchase',
    attributes: {
      'event.name': 'purchase',
      'user.id': req.user.id,
      'custom.plan': req.body.plan,
      'custom.amount': String(req.body.amount),
    },
  })
  // ...handle checkout
})

1234567Browser request trace (TraceId = abc123)
+-- fetch POST /api/checkout  (traceparent: abc123)
        |
        v
Backend (TraceId = abc123)
+-- span: POST /api/checkout
    +-- log: "purchase" event  (TraceId = abc123, user.id = "user_123")

1234567891011SELECT
  Timestamp,
  ServiceName,                          -- 'my-app' (browser) or 'my-api' (backend)
  LogAttributes['event.name'] AS event,
  LogAttributes['url.path'] AS path,
  TraceId,
  LogAttributes
FROM otel_logs
WHERE LogAttributes['user.id'] = {user_id:String}
  AND LogAttributes['event.name'] != ''
ORDER BY Timestamp ASC

12345678import { initStrada } from '@strada.sh/sdk'

initStrada({
  projectId: '01JTHG5M7XPQR8KNCZ0W4D',  // get one with `strada projects create`
  service: 'my-app',
  version: '1.4.2',
  environment: 'production',
})

1234567891011121314-- Schema (AggregatingMergeTree)
ProjectId       LowCardinality(String)
Date            Date
ServiceName     LowCardinality(String)
Domain          String                     -- extracted from url.full with domainWithoutWWW()
Pathname        String
Referrer        String                     -- domainWithoutWWW(http.request.header.referer)
Device          LowCardinality(String)     -- 'desktop', 'mobile-android', 'mobile-ios', 'bot'
Browser         LowCardinality(String)     -- 'chrome', 'firefox', 'safari', 'opera', 'ie', 'Unknown'
Country         LowCardinality(String)     -- ISO 3166-1 alpha-2 from geo.country
Language        LowCardinality(String)     -- from browser.language
Visits          AggregateFunction(uniq, String)    -- uniqState(visitor.id), session.id fallback
FirstVisits     AggregateFunction(uniq, String)    -- uniqStateIf(visitor.id, first_visit)
Hits            AggregateFunction(count, UInt64)   -- countState()

1234567891011121314CASE
  WHEN match(ua, 'wget|ahrefsbot|curl|urllib|bitdiscovery|googlebot') THEN 'bot'
  WHEN match(ua, 'android') THEN 'mobile-android'
  WHEN match(ua, 'ipad|iphone|ipod') THEN 'mobile-ios'
  ELSE 'desktop'
END AS Device,
CASE
  WHEN match(ua, 'firefox') THEN 'firefox'
  WHEN match(ua, 'chrome|crios') THEN 'chrome'
  WHEN match(ua, 'opera') THEN 'opera'
  WHEN match(ua, 'msie|trident') THEN 'ie'
  WHEN match(ua, 'iphone|ipad|safari') THEN 'safari'
  ELSE 'Unknown'
END AS Browser

123456789101112-- Schema (AggregatingMergeTree)
ProjectId       LowCardinality(String)
Date            Date
ServiceName     LowCardinality(String)
Domain          String
SessionId       String
Device          SimpleAggregateFunction(any, LowCardinality(String))
Browser         SimpleAggregateFunction(any, LowCardinality(String))
Country         SimpleAggregateFunction(any, LowCardinality(String))
FirstHit        SimpleAggregateFunction(min, DateTime)
LatestHit       SimpleAggregateFunction(max, DateTime)
Hits            AggregateFunction(count, UInt64)

1234-- Analytics MVs: 90-day retention
ENGINE_TTL Date + INTERVAL 90 DAY

-- Raw traces: 7-day retention (or whatever the user configures)

12345678910111213141516-- Current period
WITH
  toDate({date_from:String}) AS current_start,
  toDate({date_to:String}) AS current_end
SELECT
  Date AS day,
  countMerge(Hits) AS pageviews,
  uniqMerge(Visits) AS unique_visitors
FROM otel_analytics_pages
WHERE
  ServiceName = {service:String}
  AND Domain = {domain:String}
  AND Date >= current_start
  AND Date <= current_end
GROUP BY day
ORDER BY day

1234567891011121314151617-- Previous period (same duration, shifted back)
WITH
  toDate({date_from:String}) AS current_start,
  toDate({date_to:String}) AS current_end,
  dateDiff('day', current_start, current_end) + 1 AS period_days
SELECT
  Date + period_days AS day,   -- align to current period for comparison
  countMerge(Hits) AS pageviews,
  uniqMerge(Visits) AS unique_visitors
FROM otel_analytics_pages
WHERE
  ServiceName = {service:String}
  AND Domain = {domain:String}
  AND Date >= current_start - period_days
  AND Date < current_start
GROUP BY day
ORDER BY day

123456789101112131415161718192021222324WITH
  toDate({date_from:String}) AS current_start,
  toDate({date_to:String}) AS current_end
SELECT
  day,
  count() AS sessions,
  sumIf(1, latest_ms = first_ms) / greatest(count(), 1) AS bounce_rate,
  avg(latest_ms - first_ms) / 1000 AS avg_session_duration_sec
FROM (
  SELECT
    Date AS day,
    SessionId,
    toUnixTimestamp64Milli(max(LatestHit)) AS latest_ms,
    toUnixTimestamp64Milli(min(FirstHit)) AS first_ms
  FROM otel_analytics_sessions
  WHERE
    ServiceName = {service:String}
    AND Domain = {domain:String}
    AND Date >= current_start
    AND Date <= current_end
  GROUP BY day, SessionId
)
GROUP BY day
ORDER BY day

123456789101112WITH
  toDate({date_from:String}) AS current_start,
  toDate({date_to:String}) AS current_end
SELECT
  uniqMerge(Visits) AS unique_visitors,
  countMerge(Hits) AS total_pageviews
FROM otel_analytics_pages
WHERE
  ServiceName = {service:String}
  AND Domain = {domain:String}
  AND Date >= current_start
  AND Date <= current_end

12345678910111213141516171819202122-- Session-based KPIs (bounce rate, avg duration)
-- Must convert SimpleAggregateFunction columns to millis before subtraction.
WITH
  toDate({date_from:String}) AS current_start,
  toDate({date_to:String}) AS current_end
SELECT
  count() AS total_sessions,
  sumIf(1, latest_ms = first_ms) / greatest(count(), 1) AS bounce_rate,
  avg(latest_ms - first_ms) / 1000 AS avg_session_duration_sec
FROM (
  SELECT
    SessionId,
    toUnixTimestamp64Milli(max(LatestHit)) AS latest_ms,
    toUnixTimestamp64Milli(min(FirstHit)) AS first_ms
  FROM otel_analytics_sessions
  WHERE
    ServiceName = {service:String}
    AND Domain = {domain:String}
    AND Date >= current_start
    AND Date <= current_end
  GROUP BY SessionId
)

1234567891011121314151617WITH
  toDate({date_from:String}) AS current_start,
  toDate({date_to:String}) AS current_end
SELECT
  Pathname,
  countMerge(Hits) AS pageviews,
  uniqMerge(Visits) AS unique_visitors
FROM otel_analytics_pages
WHERE
  ServiceName = {service:String}
  AND Domain = {domain:String}
  AND Date >= current_start
  AND Date <= current_end
GROUP BY Pathname
ORDER BY pageviews DESC
LIMIT {limit:UInt32}
OFFSET {offset:UInt32}

1234567891011121314151617WITH
  toDate({date_from:String}) AS current_start,
  toDate({date_to:String}) AS current_end
SELECT
  Country,
  uniqMerge(Visits) AS unique_visitors,
  countMerge(Hits) AS pageviews
FROM otel_analytics_pages
WHERE
  ServiceName = {service:String}
  AND Domain = {domain:String}
  AND Date >= current_start
  AND Date <= current_end
GROUP BY Country
ORDER BY unique_visitors DESC
LIMIT {limit:UInt32}
OFFSET {offset:UInt32}

12345678910111213141516WITH
  toDate({date_from:String}) AS current_start,
  toDate({date_to:String}) AS current_end
SELECT
  Browser,
  uniqMerge(Visits) AS unique_visitors,
  countMerge(Hits) AS pageviews
FROM otel_analytics_pages
WHERE
  ServiceName = {service:String}
  AND Domain = {domain:String}
  AND Date >= current_start
  AND Date <= current_end
GROUP BY Browser
ORDER BY unique_visitors DESC
LIMIT {limit:UInt32}

12345678910111213141516WITH
  toDate({date_from:String}) AS current_start,
  toDate({date_to:String}) AS current_end
SELECT
  Device,
  uniqMerge(Visits) AS unique_visitors,
  countMerge(Hits) AS pageviews
FROM otel_analytics_pages
WHERE
  ServiceName = {service:String}
  AND Domain = {domain:String}
  AND Date >= current_start
  AND Date <= current_end
GROUP BY Device
ORDER BY unique_visitors DESC
LIMIT {limit:UInt32}

123456789101112131415161718WITH
  toDate({date_from:String}) AS current_start,
  toDate({date_to:String}) AS current_end
SELECT
  Referrer,
  uniqMerge(Visits) AS unique_visitors,
  countMerge(Hits) AS pageviews
FROM otel_analytics_pages
WHERE
  ServiceName = {service:String}
  AND Domain = {domain:String}
  AND Date >= current_start
  AND Date <= current_end
  AND Referrer != ''
  AND Referrer != {domain:String}   -- exclude self-referrals
GROUP BY Referrer
ORDER BY unique_visitors DESC
LIMIT {limit:UInt32}

12345678910111213141516WITH
  toDate({date_from:String}) AS current_start,
  toDate({date_to:String}) AS current_end
SELECT
  Language,
  uniqMerge(Visits) AS unique_visitors,
  countMerge(Hits) AS pageviews
FROM otel_analytics_pages
WHERE
  ServiceName = {service:String}
  AND Domain = {domain:String}
  AND Date >= current_start
  AND Date <= current_end
GROUP BY Language
ORDER BY unique_visitors DESC
LIMIT {limit:UInt32}

123456SELECT uniq(SpanAttributes['session.id']) AS active_visitors
FROM otel_traces
WHERE
  ServiceName = {service:String}
  AND SpanName = 'pageview'
  AND Timestamp >= now() - INTERVAL 5 MINUTE

123456789101112WITH
  (now() - INTERVAL 30 MINUTE) AS window_start
SELECT
  toStartOfMinute(Timestamp) AS minute,
  uniq(SpanAttributes['session.id']) AS visitors
FROM otel_traces
WHERE
  ServiceName = {service:String}
  AND SpanName = 'pageview'
  AND Timestamp >= window_start
GROUP BY minute
ORDER BY minute

12345678910111213SELECT
  Timestamp,
  SpanAttributes['url.path'] AS path,
  SpanAttributes['url.full'] AS full_url,
  Duration / 1e9 AS time_on_page_sec,
  SpanAttributes['session.id'] AS session_id,
  SpanAttributes['http.request.header.referer'] AS referrer
FROM otel_traces
WHERE
  ServiceName = {service:String}
  AND SpanName = 'pageview'
   AND SpanAttributes['user.id'] = {user_id:String}
ORDER BY Timestamp ASC

12345678910SELECT
  Timestamp,
  SpanAttributes['url.path'] AS path,
  Duration / 1e9 AS time_on_page_sec
FROM otel_traces
WHERE
  ServiceName = {service:String}
  AND SpanName = 'pageview'
  AND SpanAttributes['session.id'] = {session_id:String}
ORDER BY Timestamp ASC

12345678910SELECT
  Timestamp,
  SpanName,
  SpanKind,
  SpanAttributes['url.path'] AS path,
  Duration / 1e6 AS duration_ms,
  SpanAttributes
FROM otel_traces
WHERE SpanAttributes['session.id'] = {session_id:String}
ORDER BY Timestamp ASC

12345678910111213141516171819WITH sessions AS (
  SELECT
    SpanAttributes['session.id'] AS session_id,
    groupArray(SpanAttributes['url.path']) AS pages
  FROM otel_traces
  WHERE
    ServiceName = {service:String}
    AND SpanName = 'pageview'
    AND Timestamp >= toDate({date_from:String})
    AND Timestamp <= toDate({date_to:String}) + 1
  GROUP BY session_id
)
SELECT
  count()                                                                    AS total_sessions,
  countIf(has(pages, '/pricing'))                                            AS step_1_pricing,
  countIf(has(pages, '/pricing') AND has(pages, '/checkout'))                AS step_2_checkout,
  countIf(has(pages, '/pricing') AND has(pages, '/checkout')
          AND has(pages, '/success'))                                         AS step_3_success
FROM sessions

123456789SELECT
  Domain,
  countMerge(Hits) AS total_pageviews,
  min(Date) AS first_seen,
  max(Date) AS last_seen
FROM otel_analytics_pages
WHERE ServiceName = {service:String}
GROUP BY Domain
ORDER BY total_pageviews DESC

12345678910111213SELECT
  LogAttributes['event.name'] AS event,
  count() AS occurrences,
  uniqExact(LogAttributes['session.id']) AS unique_sessions
FROM otel_logs
WHERE
  ServiceName = {service:String}
  AND LogAttributes['event.name'] != ''
  AND Timestamp >= toDate({date_from:String})
  AND Timestamp <= toDate({date_to:String}) + 1
GROUP BY event
ORDER BY occurrences DESC
LIMIT {limit:UInt32}

123456789101112SELECT
  toDate(Timestamp) AS day,
  LogAttributes['event.name'] AS event,
  count() AS occurrences
FROM otel_logs
WHERE
  ServiceName = {service:String}
  AND LogAttributes['event.name'] != ''
  AND Timestamp >= toDate({date_from:String})
  AND Timestamp <= toDate({date_to:String}) + 1
GROUP BY day, event
ORDER BY day, occurrences DESC

12345678910111213SELECT
  Timestamp,
  LogAttributes['event.name'] AS event,
  LogAttributes['session.id'] AS session_id,
  LogAttributes['url.path'] AS path,
  LogAttributes['user.id'] AS user_id,
  LogAttributes
FROM otel_logs
WHERE
  ServiceName = {service:String}
  AND LogAttributes['user.id'] = {user_id:String}
  AND LogAttributes['event.name'] != ''
ORDER BY Timestamp ASC

1234567891011SELECT
  Timestamp,
  LogAttributes['event.name'] AS event,
  LogAttributes['url.path'] AS path,
  LogAttributes
FROM otel_logs
WHERE
  ServiceName = {service:String}
  AND LogAttributes['session.id'] = {session_id:String}
  AND LogAttributes['event.name'] != ''
ORDER BY Timestamp ASC

123456789101112SELECT
  LogAttributes['custom.plan'] AS plan,
  count() AS occurrences,
  uniqExact(LogAttributes['user.id']) AS unique_users
FROM otel_logs
WHERE
  ServiceName = {service:String}
  AND LogAttributes['event.name'] = 'form_submit'
  AND Timestamp >= toDate({date_from:String})
  AND Timestamp <= toDate({date_to:String}) + 1
GROUP BY plan
ORDER BY occurrences DESC

123456789101112131415161718192021222324252627282930SELECT * FROM (
  SELECT
    Timestamp,
    'pageview' AS type,
    SpanAttributes['url.path'] AS name,
    SpanAttributes['session.id'] AS session_id,
    Duration / 1e9 AS duration_sec,
    SpanAttributes AS properties
  FROM otel_traces
  WHERE
    ServiceName = {service:String}
    AND SpanName = 'pageview'
    AND SpanAttributes['user.id'] = {user_id:String}

  UNION ALL

  SELECT
    Timestamp,
    'event' AS type,
    LogAttributes['event.name'] AS name,
    LogAttributes['session.id'] AS session_id,
    0 AS duration_sec,
    LogAttributes AS properties
  FROM otel_logs
  WHERE
    ServiceName = {service:String}
    AND LogAttributes['user.id'] = {user_id:String}
    AND LogAttributes['event.name'] != ''
)
ORDER BY Timestamp ASC

12345678910111213141516171819202122232425262728293031WITH events AS (
  SELECT
    SpanAttributes['session.id'] AS session_id,
    SpanAttributes['url.path'] AS event_name
  FROM otel_traces
  WHERE ServiceName = {service:String} AND SpanName = 'pageview'
    AND Timestamp >= toDate({date_from:String})
    AND Timestamp <= toDate({date_to:String}) + 1

  UNION ALL

  SELECT
    LogAttributes['session.id'] AS session_id,
    LogAttributes['event.name'] AS event_name
  FROM otel_logs
  WHERE ServiceName = {service:String} AND LogAttributes['event.name'] != ''
    AND Timestamp >= toDate({date_from:String})
    AND Timestamp <= toDate({date_to:String}) + 1
),
sessions AS (
  SELECT session_id, groupArray(event_name) AS events
  FROM events
  GROUP BY session_id
)
SELECT
  count() AS total_sessions,
  countIf(has(events, '/pricing'))                                            AS step_1_pricing,
  countIf(has(events, '/pricing') AND has(events, 'start_trial'))             AS step_2_start_trial,
  countIf(has(events, '/pricing') AND has(events, 'start_trial')
          AND has(events, 'signup_form'))                                      AS step_3_signup
FROM sessions