How to compare Yandex Direct and CRM data in DWH (ETL)?

Question

CRM

Good afternoon,

I can’t figure out how to compare CRM data and advertising costs in Yandex Direct.

We have:
- CRM, in which we can send UTM tags and other parameters to Leads (I don’t understand which ones are needed). From the leads, you can extract the amount of sales, and then the revenue from sales.
- More than 30 Yandex Direct, Metrika cabinets linked to sites.
- SQL Server with data, where all this can be collected, if necessary. I do a simple DWH in it, collect data from 1C, CRM, Google Sheets, and other databases.
- Power Bi, which I connect to this a la DWH to display indicators. There also I plan to give result on this task.
- SB and infobases, which prohibit uploading any information to external systems (that is, uploading data from CRM and importing it into GA or Yandex is not an option). You can download from outside.

Task:
I would like to collect data on the costs of Yandex Direct advertising campaigns in DWH. In parallel, collect sales revenues in CRM. After that, the two indicators are somehow compared in such a way as to calculate the effectiveness of spending on online advertising . But I don't understand how to do it. What keys, what tools.

What I tried:
In CRM, we added the UTM campaign, medium, source fields, which record the data of leads that arrived from websites. The same fields contain data from call trackings (there are also several of them).
In Google Sheets, through the Adveronix add-on, I can connect to Yandex Metrica, in which I can compare data using UTM tags, but there are no advertising costs. And I can connect to Yandex Direct, which has expenses, but there are no labels or signs by which I could compare data with CRM or Yandex Metrica to pull these UTMs. Those. I can not solve the problem with this add-on.
Perhaps UTM is not what I need at all, perhaps this connector does not show everything. I looked in the Yandex Api documentation, it seems that all the indicators are there.

Wishes:
In priority, of course, free connectors to services, we will look at paid tools last. I will collect data constantly, i.e. once a day for yesterday or once a week for last week, etc.

Answer 1

So it’s not easier to connect commerce in Yandex metrics and analytics so that there are anonymized sales data