Create a blank spreadsheet and install the Google Analytics extension
Posted: Tue Jan 28, 2025 10:40 am
Determining channel value and budget reallocation
How to determine the value of each channel in terms of return on investment and marketing budget allocation?
Let's assume that the marketing budget is 1,000,000 rubles per month, and is distributed as follows:
Channel Budget, rubles
Yandex.Direct 300,000
Google AdWords 200,000
Target VKontakte 170,000
Facebook Target 150,000
MyTarget 180,000
Next, find the attribution model comparison tool in Analytics:
159-20.png
As we said above, in the example under consideration, the "Position-based" attribution model is completely suitable for us. But nothing prevents you from choosing your own.
We are interested in the "Conversion Value" column:
159-21.png
From this column we see that the income from each channel can be distributed as follows:
Channel Budget, rubles
Yandex.Direct 700,000
Google AdWords 450,000
Target VKontakte 270,000
Facebook Target 250,000
MyTarget 190,000
Next, we calculate the ROMI of each channel:
Yandex.Direct: 700,000 / 300,000 * 100
% = 233% Google AdWords: 300,000 / 200,000 * 100% = 150%
VKontakte Target: 270,000 / 170,000 * 100%
= 159% Facebook Target: 250,000 / 150,000 * 100% = 167%
MyTarget: 190,000 / 180,000 * 100% = 106%
Now we define new budgets for each channel. To do this, we calculate the channel shares in profit (% of total profit). Adding up all the income, we get: 1,710,000 rubles. Of which:
Yandex.Direct — 41%
Google AdWords — 18%
Target VKontakte — 16%
Target Facebook — 14%
MyTarget — 11%
Distribute the budget in the same shares:
Channel Old budget, rubles New budget, rubles
Yandex.Direct 300,000 410,000
Google AdWords 200,000 180,000
Target VKontakte 170,000 160,000
Facebook Target 150,000 140,000
MyTarget 180,000 110,000
Uploading data using API
You can automate this process by setting up data portugal consumer email list upload from the Google Analytics API to Google Spreadsheets and writing formulas.
.
Extension link
Create a blank report:
159-22.png
A template will appear:
159-23.png
Line 4 specifies the ID of the view. Data will be downloaded from it. The remaining parameters are described in the developer instructions .
Fill the template with parameters. Check the syntax using Query Explorer . In the menu, click Run reports . You will get the dump on a separate sheet.
After setting up the formulas, a budget distribution plan for channels for the nearest period will appear.
How to determine the value of each channel in terms of return on investment and marketing budget allocation?
Let's assume that the marketing budget is 1,000,000 rubles per month, and is distributed as follows:
Channel Budget, rubles
Yandex.Direct 300,000
Google AdWords 200,000
Target VKontakte 170,000
Facebook Target 150,000
MyTarget 180,000
Next, find the attribution model comparison tool in Analytics:
159-20.png
As we said above, in the example under consideration, the "Position-based" attribution model is completely suitable for us. But nothing prevents you from choosing your own.
We are interested in the "Conversion Value" column:
159-21.png
From this column we see that the income from each channel can be distributed as follows:
Channel Budget, rubles
Yandex.Direct 700,000
Google AdWords 450,000
Target VKontakte 270,000
Facebook Target 250,000
MyTarget 190,000
Next, we calculate the ROMI of each channel:
Yandex.Direct: 700,000 / 300,000 * 100
% = 233% Google AdWords: 300,000 / 200,000 * 100% = 150%
VKontakte Target: 270,000 / 170,000 * 100%
= 159% Facebook Target: 250,000 / 150,000 * 100% = 167%
MyTarget: 190,000 / 180,000 * 100% = 106%
Now we define new budgets for each channel. To do this, we calculate the channel shares in profit (% of total profit). Adding up all the income, we get: 1,710,000 rubles. Of which:
Yandex.Direct — 41%
Google AdWords — 18%
Target VKontakte — 16%
Target Facebook — 14%
MyTarget — 11%
Distribute the budget in the same shares:
Channel Old budget, rubles New budget, rubles
Yandex.Direct 300,000 410,000
Google AdWords 200,000 180,000
Target VKontakte 170,000 160,000
Facebook Target 150,000 140,000
MyTarget 180,000 110,000
Uploading data using API
You can automate this process by setting up data portugal consumer email list upload from the Google Analytics API to Google Spreadsheets and writing formulas.
.
Extension link
Create a blank report:
159-22.png
A template will appear:
159-23.png
Line 4 specifies the ID of the view. Data will be downloaded from it. The remaining parameters are described in the developer instructions .
Fill the template with parameters. Check the syntax using Query Explorer . In the menu, click Run reports . You will get the dump on a separate sheet.
After setting up the formulas, a budget distribution plan for channels for the nearest period will appear.