Hi there,
We know that a user does not always make the desired action on a website during the first session. When taking a decision, a user can interact with different advertising channels.
Turns out, in Clickhouse you can easily prepare data for the multi-channel conversions report. As an example, you can download raw session data from Yandex.Metrika. In Renta, you can do it in a couple of clicks.
In the Sessions table there are plenty of data, including clientID, lastTrafficSource and dateTime:
Let us run the table query:
As a result, we will get aggregated advertising medium for each clientID.
Import the table in Power BI
The data model is already compiled in this example, therefore it is easiest to build bridges between tables or combine them using clientID.
That might have been the end of it, but the Source column contains characters that visually make the report heavier.
To get rid of them, you need to make some changes in the Power Query mode:
Replace double brackets with blanks.
Add a space after a comma.
After manipulations with data, we can set about reporting. All available measures in the Power BI projects are also available at the level of aggregated advertising medium.
Some useful links:
Documentation on the deployment of Clickhouse on the server. It takes less than 15 minutes. Everyone can do it on the first try even if someone never heard anything about the command line.
Documentation on Yandex.Metrika Logs API. Please consider this tool if you need to gather raw data.
If you have any questions left — ask them in the Comments.
