Skip to main content

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
Example datasets

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:

ClickHouse Cloud service connect button

Choose HTTPS, and the details are available in an example curl command.

ClickHouse Cloud HTTPS connection details

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.

Sign in

3. Create your workspace

After creating an organization, you will be prompted to create your first workspace.

Create workspace

4. Connect Mitzu to ClickHouse

Once your workspace is created, you need to manually set the connection details.

Setup 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.
Setup table connection
Adding more tables

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.

Create event catalog

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.

Explore

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.

Segmentation
Filters and Breakdown

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.

Funnel
Visualize trends

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.

Retention
Cohort retention

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.

SQL Native
Continue your work in a BI tool

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.

Extra tables

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.

Configure tables

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 support@mitzu.io

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