Creating a custom event format

In this guide we will look at creating a custom event format using the example of a mail service's events.

By default, all additional event parameters in Rockstat are stored in the extra field. However, this is not always convenient and efficient if you need to run queries over these parameters.

For this case Rockstat provides a way to describe your own tables.

Describing the table#

For example, for a mail service we need to add the following parameters:

1. Event type (send, open, click, etc.).
2. User email.
3. Message ID.
4. Mailing list ID.
5. Task ID.
6. Link ID (for the click event).
7. Additional message (e.g., the response from an external mail service when sending).

At the same time we want to keep all the capabilities of the standard webhooks, so we will inherit our table from the standard webhooks one.

As a result we get the following table description in YML format:

    mail_statistics:
        event: String
        email: String
        message_id: String
        list_id: String
        task_id: String
        link_id: String
        detailed: String
        _options:
            extend: webhooks

Our table will be called mail_statistics. It will inherit from webhooks, which must be specified in the _options.extend parameter. All the other parameters are new columns. In our case they all have the String type, but you can use any ClickHouse type.

Describing the routes#

For certain events to start being written into this table, we need to define new routes.

In our case we will need 3 routes:

1. Webhook wh/mail.
2. Pixel pixel/mail.
3. Redirect redir/mail.

Webhook wh/mail#

This is exactly where we will send the delivery status from our mail-sending servers.

For example, we can do this with the following Python script:

import json
import requests

requests.post(
    'https://your_rockstat_domain/wh/mail', 
    headers={
        'Content-type': 'application/json'
    },
    data=json.dumps({
        {
          'event': 'sent',
          'email': email,
          'message_id': message_id,
          'task_id': task_id,
          'list_id': list_id,
          'detailed': detailed
        }
    }))

Pixel pixel/mail#

We will insert a link to this pixel as an image into the body of the email so that it loads when the recipient opens the email.

For example, this pixel can be inserted like this:

<img src="https://your_rockstat_domain/pixel/mail?event=open&task_id=QWERTY&message_id=QWERTY_ASDFGH&email=bob@example.com&list_id=12345"/>

Redirect redir/mail#

We will replace all links in the email with this redirect in order to track clicks. The to parameter must specify the target page the user will be sent to.

For example,

<a href="https://your_rockstat_domain/redir/mail?event=click&task_id=QWERTY&message_id=QWERTY_ASDFGH&email=bob@example.com&list_id=12345&link_id=1&to=https%3A%2F%2Fya.ru">Click me!</a>

For these routes to take effect, you need to build a structure like this:

    destinations:
        wh/mail: mail_statistics
        pixel/mail: mail_statistics
        redir/mail: mail_statistics

Putting it all together#

The description of the new table with its routes must be placed in a YML file in the /srv/platform/data/chwriter_custom_config directory.

In our case this will be the file /srv/platform/data/chwriter_custom_config/05_mail.yml. The full listing is below.

---
clickhouse:
    destinations:
        wh/mail: mail_statistics
        pixel/mail: mail_statistics
        redir/mail: mail_statistics
    mail_statistics:
        event: String
        email: String
        message_id: String
        list_id: String
        task_id: String
        link_id: String
        detailed: String
        _options:
            extend: webhooks

For Rockstat to apply the changes to the tables according to the file, you need to restart the chwriter container:

docker restart chwriter

Rockstat will automatically roll out the necessary changes for the table and configure the routes. We can safely send our events to the URLs described above. And for convenient statistics collection you can implement your own API services, but we'll cover that in another guide.