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.

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;

Query JSON properties

Some event details are stored in JSON strings such as props and meta. Use ClickHouse JSON functions to extract them:

SELECT
  JSONExtractString(props, '$placement_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