We know that a user not always makes a desired action on a website during the first session. When taking a decision, a user can interact with different advertising medium.
Turns out, in Clickhouse you can easily prepare data for the multi-channel conversions report.
As an example, you can download raw sessions 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:
[crayon-5da7524d6552f903519010 inline="true" ]select clientID ,
groupArray( lastTrafficSource ) as Sources
select clientID, dateTime, lastTrafficSource
order by clientID, dateTime
group 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.
That might have been the end of it, but the Source column contains characters which visually make the report heavier.
To get rid of them, you need to make some changes in the Power Query mode:
- Replace double brackets by 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 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.