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.
1select clientID ,
2groupArray( lastTrafficSource ) as Sources
4select clientID, dateTime, lastTrafficSource
6order by clientID, dateTime
8group by clientID
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.
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.