> ## Documentation Index > Fetch the complete documentation index at: https://learn.kubit.ai/llms.txt > Use this file to discover all available pages before exploring further. # Recipe: RudderStack events on ClickHouse > Connect, model and check RudderStack clickstream events loaded into your own ClickHouse, from grants to a first funnel and retention report. This recipe takes RudderStack events that RudderStack's ClickHouse destination loads into your cluster, and makes them ready for funnels, retention and paths in Kubit. At the end, every `track`, `page` and `screen` call counts as an Event, and each person counts as one User whether they were signed in or not. It takes about an hour. It combines three pages and adds the RudderStack-specific parts: [Connect ClickHouse](https://learn.kubit.ai/warehouses/clickhouse.md), [Connect RudderStack](https://learn.kubit.ai/product-analytics/data-sources/rudderstack.md) and [Product analytics in your warehouse](https://learn.kubit.ai/product-analytics/overview.md). ## Before you start * [ ] RudderStack's ClickHouse destination is enabled and has completed at least one sync * [ ] The ClickHouse database set on that destination (the **Database** field in RudderStack). This recipe calls it ``. * [ ] An admin ClickHouse user that can create users, roles, databases and views * [ ] The password for the `KUBIT` user, from the Kubit team * [ ] The names of 3 events in your signup or activation flow. This recipe uses `Signup Started`, `Account Created` and `Project Created`. ## Prompt for your coding agent Paste this into Claude Code, Cursor or Codex. It drafts the SQL and leaves running it to you: ```text theme={null} Read https://learn.kubit.ai/product-analytics/cookbook/rudderstack-clickhouse.md. Write all the ClickHouse SQL in it for the RudderStack database into kubit_rudderstack.sql. Don't connect to ClickHouse or run anything. ``` ## Steps ### 1. Check the RudderStack tables Run this as your admin user to confirm the destination has loaded data: ```sql theme={null} SELECT name, total_rows FROM system.tables WHERE database = '' ORDER BY total_rows DESC LIMIT 20; ``` You should see `tracks`, `identifies`, and `pages` or `screens`, plus one table per `track` event name. ### 2. Give Kubit read access Follow [Connect ClickHouse](https://learn.kubit.ai/warehouses/clickhouse.md#steps). It creates the `KUBIT` user, the `KUBIT_R` and `KUBIT_W` roles and the `KUBIT_DB` helper database, allows Kubit's IP ranges and adds the connection. In its **Grant permissions** step, grant read access on the whole RudderStack database instead of table by table: ```sql theme={null} GRANT SELECT ON .* TO KUBIT_R; ``` RudderStack creates a new table the first time it sees a new event name. A database grant covers those tables too. ### 3. Create one view for all events RudderStack writes `track`, `page` and `screen` calls to separate tables. This view puts them in one table with the same columns: ```sql theme={null} CREATE VIEW KUBIT_DB.rudder_events AS SELECT id, anonymous_id, user_id, event_text AS event_name, 'track' AS event_type, timestamp, received_at FROM .tracks UNION ALL SELECT id, anonymous_id, user_id, coalesce(name, 'Page Viewed') AS event_name, 'page' AS event_type, timestamp, received_at FROM .pages UNION ALL SELECT id, anonymous_id, user_id, coalesce(name, 'Screen Viewed') AS event_name, 'screen' AS event_type, timestamp, received_at FROM .screens; ``` Remove the `pages` or `screens` part if that table doesn't exist. A web-only source has no `screens` table. ### 4. Create the identity map This view maps each anonymous ID to the most recent user ID it was identified as: ```sql theme={null} CREATE VIEW KUBIT_DB.rudder_identity AS SELECT anonymous_id, argMax(user_id, timestamp) AS resolved_user_id FROM .identifies WHERE anonymous_id IS NOT NULL AND user_id IS NOT NULL GROUP BY anonymous_id; ``` Use `identifies`, not `users`. The ClickHouse destination skips the `users` table by default (the **Skip User Table** setting), and `users` has no `anonymous_id` column. ### 5. Model the events in Kubit In the Modeler: 1. Add `KUBIT_DB.rudder_events` as a fact table, with `timestamp` as the event time. RudderStack corrects `timestamp` for client clock skew, so it's closer to when the user acted than `received_at`. 2. Add `KUBIT_DB.rudder_identity` as a dimension table, joined on `rudder_events.anonymous_id = rudder_identity.anonymous_id` as a left join. Events from users who never signed in stay in the model. 3. Create the subjects below. | Subject | Definition | | - | - | | **Event** | `rudder_events.id` | | **User** | A custom column: `coalesce(rudder_events.user_id, rudder_identity.resolved_user_id, rudder_events.anonymous_id)` | The User column picks the signed-in ID first, then the ID the device later signed in as, then the anonymous ID. Events from before and after signup then count as one person. ### 6. Add event properties For each event you analyze, join its table on `id` and enable the property columns you need as fields, for example `project_created.template`. Enable `context_*` columns you filter or group by often, such as `context_os_name`, `context_app_version` and `context_page_path`. They exist on every RudderStack table. ## Verify Check the model with three reports. Each has a SQL query to run in ClickHouse for the same numbers. Compare a full day that ended at least 1 hour ago, so RudderStack's sync has finished. Totals should match within 1%. ### Daily active users Build a [Query](https://learn.kubit.ai/product-analytics/reports/query.md) report counting unique Users, all events, daily, for the last 14 days. Or ask your assistant: > "Show daily active users for the last 14 days" ```sql theme={null} SELECT toDate(e.timestamp) AS day, uniqExact(coalesce(e.user_id, i.resolved_user_id, e.anonymous_id)) AS dau FROM KUBIT_DB.rudder_events AS e LEFT JOIN KUBIT_DB.rudder_identity AS i ON e.anonymous_id = i.anonymous_id WHERE e.timestamp >= toStartOfDay(now() - INTERVAL 14 DAY) GROUP BY day ORDER BY day; ``` ### Signup funnel Build a [Funnel](https://learn.kubit.ai/product-analytics/reports/funnel.md) with 3 steps, `Signup Started`, `Account Created` and `Project Created`, with a 1-day conversion window over the last 30 days. Or ask: > "Build a funnel from Signup Started to Account Created to Project Created in the last 30 days, with a 1 day conversion window" Step 1 should be close to: ```sql theme={null} SELECT uniqExact(coalesce(e.user_id, i.resolved_user_id, e.anonymous_id)) AS users FROM KUBIT_DB.rudder_events AS e LEFT JOIN KUBIT_DB.rudder_identity AS i ON e.anonymous_id = i.anonymous_id WHERE e.event_name = 'Signup Started' AND e.timestamp >= toStartOfDay(now() - INTERVAL 30 DAY); ``` If step 2 is close to 0 while step 1 is large, the User subject is splitting people at signup. Users start anonymous and get a `user_id` at `Account Created`, so the steps only connect through the identity map. ### Weekly retention Build a [Retention](https://learn.kubit.ai/product-analytics/reports/retention.md) report: starting event `Account Created`, returning on any event, weekly, 8 weeks. Or ask: > "Weekly retention for 8 weeks for users who did Account Created, returning on any event" ## If it fails ### Errors while creating the views | Symptom | Cause | Fix | | - | - | - | | `Unknown expression identifier 'name'` | The `pages` or `screens` table has no `name` column because no call set a name | Replace `coalesce(name, 'Page Viewed')` with `'Page Viewed'` | | `There is no supertype for types` | A column has different types in two tables | Cast it in each `SELECT`, for example `toString(user_id)` | | `Table .screens does not exist` | The source sends no `screen` calls | Remove the `screens` part of the view | ### Counts are higher than expected Duplicate rows are the most common reason. RudderStack creates ClickHouse tables with the `ReplacingMergeTree` engine, ordered by `(received_at, id)`. When RudderStack retries a load, the same event can be stored twice until ClickHouse merges the table parts in the background, which can take minutes or hours. ```sql theme={null} SELECT count() AS row_count, uniqExact(id) AS unique_events FROM .tracks WHERE timestamp >= now() - INTERVAL 1 DAY; ``` If `row_count` is larger than `unique_events`, the table has unmerged duplicates. Event counts in Kubit can include them. Unique user counts don't change. ### Counts are lower than expected | Check | Query or action | What it means | | - | - | - | | The event's table exists | `SELECT name FROM system.tables WHERE database = '' AND name = ''` | No row: RudderStack hasn't synced that event yet | | Kubit can read it | `SHOW GRANTS FOR KUBIT_R` | No `SELECT ON .*`: run the grant in step 2 | | The latest sync finished | `SELECT max(received_at) FROM .tracks` | Older than the destination's **Sync Frequency**: check its sync status in RudderStack | | Events reach `tracks` | The destination's **Skip Tracks Table** setting | On: events are only in per-event tables, so the view misses them | RudderStack lowercases event names and converts them to snake case for table names. Search for `account_created`, not `Account Created`. The original name is in `event_text`. ### Daily numbers are shifted ClickHouse stores `DateTime` values as UTC instants and shows them in the server's time zone unless the column or query sets one. Check it with `SELECT timezone()`, and compare one day in UTC with `toDate(timestamp, 'UTC') = yesterday()`. ### Events land in the future RudderStack calculates `timestamp` as `received_at - (sent_at - original_timestamp)` to correct for client clock skew. A device with a badly wrong clock can still produce future dates: ```sql theme={null} SELECT count() AS future_events FROM .tracks WHERE timestamp > now() + INTERVAL 1 HOUR; ``` If the count is high, use `received_at` as the event time in the model. ### Booleans and nested properties RudderStack stores booleans as `UInt8` in ClickHouse, so filter on `= 1`, not `= true`. Nested properties are flattened with the parent key as prefix: `{ plan: { tier: "pro" } }` becomes `plan_tier`. If **JSON Columns** is set on the destination, the listed paths go into one native `JSON` column instead, which needs ClickHouse 25.3 or later. ## Next steps * [Reports overview](https://learn.kubit.ai/product-analytics/reports/overview.md): every report type and when to use it. * [Dashboards](https://learn.kubit.ai/product-analytics/dashboards.md): put the three check reports on one dashboard. This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.