Hello there! 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] As a result, we will get aggregated advertising medium for each clientID.
[crayon-5e24827845938934039699 inline="true" ]select clientID ,
groupArray( lastTrafficSource ) as Sources
select clientID, dateTime, lastTrafficSource
order by clientID, dateTime
group by clientID
Import the table in Power BIThe 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.
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.