Query ClickHouse

The ClickHouse query API gives you direct SQL access to the same analytics data Superwall uses for charts and campaign results. Use it when you need flexible reporting, internal dashboards, or ad hoc analysis without maintaining a separate data warehouse.

Querying from the terminal or a coding agent? The Superwall CLI wraps this API: superwall query "SELECT count() FROM sw.events_rep" runs against your logged-in session, with no key handling needed.

Requests are scoped to your organization and require an organization API key with the data:read scope. Superwall provisions a read-only ClickHouse user for your organization on first use, then applies row-level policies so queries only return data for your organization's applications.

Endpoint

Use either POST or GET:

POST is recommended for most queries because SQL can be long and multiline.

Authentication

1. Open Settings > API Keys in the Superwall dashboard.
2. Create an organization API key.
3. Give the key the data:read scope.
4. Copy the token when Superwall shows it.

Pass the token as a bearer token:

Authorization: Bearer YOUR_SECRET_TOKEN

The token must belong to the organization in the path. A key from another organization cannot query this endpoint.

Send a query

Set your organization ID and API key:

export SUPERWALL_ORG_ID="123"
export SUPERWALL_API_KEY="sk_..."

Run a query with POST:

curl "https://api.superwall.com/v2/organizations/$SUPERWALL_ORG_ID/query" \
  --request POST \
  --header "Authorization: Bearer $SUPERWALL_API_KEY" \
  --data-binary "SELECT count() FROM sw.events_rep"

Run a query with GET:

curl --get "https://api.superwall.com/v2/organizations/$SUPERWALL_ORG_ID/query" \
  --header "Authorization: Bearer $SUPERWALL_API_KEY" \
  --data-urlencode "query=SELECT count() FROM sw.events_rep"

The response is the raw ClickHouse HTTP response. If you do not specify a format, ClickHouse returns its default text format. Add a FORMAT clause when you need JSON:

curl "https://api.superwall.com/v2/organizations/$SUPERWALL_ORG_ID/query" \
  --request POST \
  --header "Authorization: Bearer $SUPERWALL_API_KEY" \
  --data-binary "
    SELECT
      name,
      count() AS events
    FROM sw.events_rep
    WHERE ts >= now() - INTERVAL 7 DAY
    GROUP BY name
    ORDER BY events DESC
    LIMIT 20
    FORMAT JSONEachRow
  "

Use ClickHouse HTTP options

The endpoint proxies ClickHouse HTTP requests after Superwall authenticates your organization API key. You can pass standard ClickHouse URL parameters, such as query, database, or default_format, through the query string:

curl --get "https://api.superwall.com/v2/organizations/$SUPERWALL_ORG_ID/query" \
  --header "Authorization: Bearer $SUPERWALL_API_KEY" \
  --data-urlencode "query=SELECT name, count() FROM events_rep GROUP BY name LIMIT 20" \
  --data-urlencode "database=sw" \
  --data-urlencode "default_format=JSONEachRow"

Superwall does not expose the generated ClickHouse username and password. Authenticate to the Superwall endpoint with your bearer token instead of connecting directly to the ClickHouse cluster.

Available tables

Your read-only user can query the analytics tables Superwall exposes for customer reporting:

Use ClickHouse introspection queries to inspect columns before writing a production query:

SHOW TABLES FROM sw;
SHOW TABLES FROM open_revenue;
DESCRIBE TABLE sw.events_rep;
DESCRIBE TABLE open_revenue.attributed_events_by_ts_rep;

Find event names and property keys

DESCRIBE TABLE shows a table's columns, but not which event names, attribute keys, or event property keys one app actually sends. The data schema endpoints list them. To browse the same lists in the dashboard, see Data Schema. Start with the app's event names and attribute keys:

export SUPERWALL_APP_ID="456"

curl --get "https://api.superwall.com/v2/charts/data-schema" \
  --header "Authorization: Bearer $SUPERWALL_API_KEY" \
  --data-urlencode "application_id=$SUPERWALL_APP_ID"

With the Superwall CLI, run superwall get /v2/charts/data-schema -d application_id=<app-id>. With the Superwall MCP connected, an agent finds the endpoint with the api_docs tool and calls it with get.

The endpoint needs the charts:read scope and access to the app's project, not the data:read scope the query API uses. To use one key for both, give it both scopes.

A trimmed response:

{
  "object": "data_schema",
  "user_attributes": [
    { "key": "plan", "types": ["String"], "last_seen_at": "2026-10-29T18:04:12.000Z" },
    { "key": "streak_days", "types": ["Int64", "String"], "last_seen_at": "2026-10-29T17:58:40.000Z" }
  ],
  "device_attributes": [
    { "key": "appVersion", "types": ["String"], "last_seen_at": "2026-10-29T18:05:01.000Z" }
  ],
  "custom_events": [
    { "name": "onboarding_complete", "last_seen_at": "2026-10-29T18:03:55.000Z", "property_count": 3 }
  ],
  "standard_events": [
    { "name": "paywall_open", "last_seen_at": "2026-10-29T18:05:09.000Z", "property_count": 57 },
    { "name": "renewal", "last_seen_at": "2026-10-29T16:22:31.000Z", "property_count": 32 }
  ],
  "events_truncated": false
}

Each attribute has types, every JSON type the key has arrived as, most recently seen first: String, Int64, UInt64, Double, Bool, Array, Object, or Null. More than one type means the app has sent the key with different types. Attributes and events each have last_seen_at, the time they last arrived, in ISO 8601. Each event also has property_count, how many property keys it has carried, not counting the keys the SDK adds to every event (see below).

The data schema endpoints cover production data only, so a name that only ever arrived from sandbox is not listed, even though sw.events_rep has it. They list names and keys and can be up to 5 minutes old. For how often each event arrived, see Count each event's arrivals. The full reference is at api.superwall.com/docs.

List an event's property keys

Pass an event name, exactly as the data schema lists it, to see the keys that event has carried:

curl --get "https://api.superwall.com/v2/charts/data-schema/event-properties" \
  --header "Authorization: Bearer $SUPERWALL_API_KEY" \
  --data-urlencode "application_id=$SUPERWALL_APP_ID" \
  --data-urlencode "event=paywall_open"

With the CLI: superwall get /v2/charts/data-schema/event-properties -d application_id=<app-id> -d event=<event-name>.

{
  "object": "data_schema_event_properties",
  "event": "paywall_open",
  "properties": [
    { "key": "$paywall_identifier", "types": ["String"], "last_seen_at": "2026-10-29T18:05:09.000Z" },
    { "key": "$presented_by_event_name", "types": ["String"], "last_seen_at": "2026-10-29T18:05:09.000Z" }
  ],
  "properties_truncated": false
}

Each property has the same key, types, and last_seen_at as an attribute, and properties is sorted by key. It holds the 500 most recently seen keys; properties_truncated is true when the event has carried more. Four keys the SDK adds to every event are left out: $app_session_id, $event_name, $is_standard_event, and event_name. A name the app has never sent returns an empty properties list, not an error.

To search property keys across all events at once, GET /v2/charts/data-schema/property-index?application_id=<app-id> returns every event the data schema lists, each with its property keys and their types but without last_seen_at. Events with no properties are left out, each event holds at most 500 keys, and properties_truncated is true when at least one event was cut.

Count each event's arrivals

To see how often each event arrived, per day over the last 30 days, call the event volume endpoint:

curl --get "https://api.superwall.com/v2/charts/data-schema/event-volume" \
  --header "Authorization: Bearer $SUPERWALL_API_KEY" \
  --data-urlencode "application_id=$SUPERWALL_APP_ID"

With the CLI: superwall get /v2/charts/data-schema/event-volume -d application_id=<app-id>.

{
  "object": "data_schema_event_volume",
  "days": ["2026-10-01", "2026-10-02", /* … 27 more … */ "2026-10-30"],
  "events": [
    { "name": "session_start", "total": 184203, "daily": [6012, 5987, /* … */ 4120] },
    { "name": "paywall_open", "total": 52977, "daily": [1810, 1764, /* … */ 1203] }
  ]
}

days holds the window's 30 dates in UTC, oldest first, ending with today. Each event has its total for the window and daily, one count per date in days, with 0 for a day it didn't arrive. events is sorted by total, most arrivals first, and only holds events that arrived in the window: an event the data schema lists but that isn't here didn't arrive in the last 30 days.

The counts are approximate, within about 1.6%, and an event delivered twice counts once. Like the other data schema endpoints, they cover production data only. Today's count is still growing.

All four endpoints need the same charts:read scope and project access.

Use the names in SQL

Each name or key maps onto sw.events_rep:

• An event name is a value of the name column, as in WHERE name = 'renewal'.
• An event property key is a key in the props of that event's rows, so JSONExtractString(props, '$presented_by_event_name') with WHERE name = 'paywall_open' reads one.
• A user attribute key is a key in the props of user_attributes events, and a device attribute key is a key in the props of device_attributes events.
• types picks the JSON function: JSONExtractString for String, JSONExtractInt for Int64, JSONExtractUInt for UInt64, JSONExtractFloat for Double, JSONExtractBool for Bool, and JSONExtractRaw for Array and Object. A key with several types needs a query that handles each of them.

Users per plan value the app sent in the past 30 days:

SELECT
  JSONExtractString(props, 'plan') AS plan,
  uniq(appUserId) AS users
FROM sw.events_rep
WHERE applicationId = 456
  AND isSandbox = 0
  AND name = 'user_attributes'
  AND ts >= now() - INTERVAL 30 DAY
GROUP BY plan
ORDER BY users DESC

Count revenue like the dashboard

Many apps receive revenue from both their store and RevenueCat, so open_revenue.attributed_events_by_ts_rep can hold the same purchase twice. Each app's revenue provider decides which rows its dashboard figures count:

Rows with source = 'sdk' always count, and RevenueCat's copies of Stripe purchases never do. With the Superwall CLI, superwall query --app <app> passes the app's id and integrations as ClickHouse parameters, so one query works for any app:

SELECT sum(price) AS revenue
FROM open_revenue.attributed_events_by_ts_rep FINAL
WHERE applicationId = {app:UInt64}
  AND isSandbox = 0
  AND (integration IN {revenue_integrations:Array(String)} OR source = 'sdk')
  AND NOT (coalesce(integration, '') = 'revenue_cat' AND store = 'STRIPE')

Read deduplicated tables

sw.device_attributes_rep, sw.user_aliases_rep, sw.user_attributes_rep, and the _rep revenue tables are deduplicated in the background, so a plain query can return an older copy of a row next to its replacement. Add FINAL after the table name to read only the latest version of each row. On sw.device_attributes_rep and sw.user_aliases_rep, FINAL also drops deleted rows, so you don't need to filter on isDeleted.

Look up a user's device attributes:

SELECT key, value
FROM sw.device_attributes_rep FINAL
WHERE applicationId = 123
  AND appUserId = 'user_123'

Find every ID that belongs to the same user:

SELECT alias
FROM sw.user_aliases_rep FINAL
WHERE applicationId = 123
  AND id = 'user_123'

Aggregated SDK events

open_revenue.sdk_events_agg stores pre-aggregated unique users per app, SDK event name, hour, and breakdown, the same data behind dashboard conversion charts. Its user columns hold ClickHouse aggregate states, so read them with uniqIfMerge (or uniqIfMergeIf to pick an event) and a GROUP BY, not with SELECT *.

Each row has a tsType:

vendorIds counts every user. On install_time rows, vendorIdsD1, vendorIdsD3, vendorIdsD7, vendorIdsD14, vendorIdsD30, and vendorIdsD90 count only users who did the event within that many days of install.

Paywall conversion by placement over the last 30 days:

SELECT
  breakdown['placement'] AS placement,
  uniqIfMergeIf(vendorIds, name = 'paywall_open') AS paywall_users,
  uniqIfMergeIf(vendorIds, name = 'transaction_complete') AS converted_users
FROM open_revenue.sdk_events_agg
WHERE applicationId = 123
  AND isSandbox = 0
  AND tsType = 'event_time'
  AND ts >= now() - INTERVAL 30 DAY
GROUP BY placement
ORDER BY paywall_users DESC
LIMIT 20

New users per install day, and how many converted within 7 days:

SELECT
  toDate(ts) AS install_day,
  uniqIfMergeIf(vendorIds, name = 'first_seen') AS new_users,
  uniqIfMergeIf(vendorIdsD7, name = 'transaction_complete') AS converted_within_7_days
FROM open_revenue.sdk_events_agg
WHERE applicationId = 123
  AND isSandbox = 0
  AND tsType = 'install_time'
  AND ts >= now() - INTERVAL 30 DAY
GROUP BY install_day
ORDER BY install_day

Query JSON properties

Some event details are stored in JSON strings such as props and meta. Use ClickHouse JSON functions to extract them. To see which keys an app sends and their types, see Find event names and property keys.

SELECT
  JSONExtractString(props, '$presented_by_event_name') AS placement,
  count() AS opens
FROM sw.events_rep
WHERE name = 'paywall_open'
  AND ts >= now() - INTERVAL 30 DAY
GROUP BY placement
ORDER BY opens DESC
LIMIT 20
FORMAT JSONEachRow;

Limits

Queries run as a read-only organization user with ClickHouse settings applied:

If a query times out or uses too much memory, narrow the date range, add filters on applicationId, isSandbox, or event name, and avoid selecting large JSON columns unless you need them.

Troubleshooting

Related

• Access Controls
• Charts
• Superwall Skill