Connecting Mitzu to ClickHouse
Mitzu is a no-code warehouse-native product analytics application. Just like Amplitude, Mixpanel or Posthog, Mitzu enables it's users to query the product usage data without any SQL or Python knowledge. Mitzu instead copy the company's product usage data, it generates native SQL queries over the company's data warehouse or lake.
Goal
In this guide we are going to cover:
- Warehouse-native product analytics
- How to integrate Mitzu to Clickhouse
If you do not have a data set to use for Mitzu, you can work with NYC Taxi Data. This dataset is available in Clickhouse Cloud.
1. Gather your connection details
To connect to ClickHouse with HTTP(S) you need this information:
The HOST and PORT: typically, the port is 8443 when using TLS or 8123 when not using TLS.
The DATABASE NAME: out of the box, there is a database named
default, use the name of the database that you want to connect to.The USERNAME and PASSWORD: out of the box, the username is
default. Use the username appropriate for your use case.
The details for your ClickHouse Cloud service are available in the ClickHouse Cloud console. Select the service that you will connect to and click Connect:
Choose HTTPS, and the details are available in an example curl command.
If you are using self-managed ClickHouse, the connection details are set by your ClickHouse administrator.
2. Sign in or sign up to Mitzu
As a first step head to https://app.mitzu.io for sign up.
3. Create your workspace
After creating an organization, you will be prompted to create your first workspace.
4. Connect Mitzu to ClickHouse
Once your workspace is created, you need to manually set the connection details.
In the guided onboarding Mitzu enables to integrate with a single table.
In order to run product analytics on your clickhouse setup, you need to > specify a few key columns from your table.
These are the following:
- User id - the column for the unique identifier for the users.
- Event time - the timestamp column of your events.
- Optional[Event name] - in case the table contains multiple event types, this column segments the events.
Adding more tables is possible once the initial guided setup is done. See below.
5. Create an event catalog
The final step of the onboarding is the Event catalog creation.
This step finds all events and its properties from the above defined table. This step may take up to a few minutes, depending on the size of your dataset.
If everything went well, you will ready to explore your events.
4. Run segmentation queries
User segmentation in Mitzu is just as easy as in Amplitude, Mixpanel or Posthog.
You can select the events on the left side of the explore page, while configuring the time horizont is on the top part.
Filtering is done like you would expect, pick a property (clickhouse column) and select the values from the dropdown that you want to filter. For breakdowns you choose any event or user property (see below how to integrate user properties).
5. Run funnel queries
Select up to 9 steps for a funnel. Choose the time window for within the funnel can be finished by your users. Get immediate conversion rate insights, without writing a single line of SQL code.
Pick Funnel trends to visualize funnels trends over time.
6. Run retention queries
Select up to 2 steps for a retention rate calculation. Choose the retention window for selecting the recurring window for Get immediate conversion rate insights, without writing a single line of SQL code.
Pick Weekly cohort retention to visualize how your retention rates are changing over time.
7. SQL native
Mitzu is SQL Native, this means it generates native SQL code from the configuration that you have chosen on the Explore page.
If you run into a limitation with Mitzu UI, simple copy the SQL code and continue your work in a BI tool.
8. Adding more event tables
If you store your product usage events in multiple tables, you can add those as well to your event catalog. Go to the workspace settings page (gear icon at the top of the page), select the event tables tab.
Add the remaining event tables from your Clickhouse warehouse.
Once you added all other event tables to your workspace. You need to configure those as well. Configure the user id, event time and optionally the event name columns.
Click the configure tables button and set these columns in a bulk. You can add up to 5000 tables to Mitzu.
Last but not least don't forget to Save and update event catalog.
Mitzu support
If you are lost feel free to contact us at [email protected]
Or you our Slack community here
Learn more
Find more information about Mitzu by at mitzu.io
Visit our documentations page at docs.mitzu.io