Intro to Parameters. Part 2
We’ve already created a table with cost, budget percent of budget used and the rest of the budget. We have added manually some Google Data Studio parameters so that we can change the budget in a View mode. When we use parameters, Total Row may not be 100% accurate, so in this article I am going to tell you how to fix this issue.
Here is a video tutorial relating to this video:
Problem 1
Our daily cost was around 400 euros but our total cost was around 50 000. It means that during last month we had about 100 days, so it doesn’t make any sense. To see the reason for this problem, I created an additional table with Ads platforms, campaigns and dates. So, in one day we could have several campaigns and when we calculate the average of total cost, we receive the wrong number, because we have different campaigns. To solve it I suppose we need to count the total Google Ads sum and divide it per number of days when the ads were shown.
Firstly, to count the number of days, let’s use a formula:
DATE_DIFF(MAX(Date)),MIN(Date))
The next step we take is counting the number of the days when the ads were running. This formula actually counts the daily budget but it also contains a formula that extracts days from dates.
sum(Cost)/COUNT_DISTINCT( EXTRACT(DAY FROM Date) )
My advice: when you have a complex formula that contains other formulas inside, you should better go from the center of the formula to the sides. That’s what I am going to explain right now.
COUNT_DISTINCT formula counts only unique values. We can check that our ads were running for 31 days. So, to count the exact number of days that corresponds to the date, we use the formula:
COUNT_DISTINCT( EXTRACT(DAY FROM Date)
Then we remove all the dimensions and add only Ads Platform and as a metric we leave # Days. Thus we can see that our ads were running for 31 days.
The next step we take is to count the summary of cost divided by the number of days. That is how we get the daily average of the cost.
Problem 2
The tricky thing is that when we have calculated fields and parameters, summary rows counted automatically are usually wrong. So, how can we solve it?
So, we have an average daily budget for Google and for Bing. And in summary what we do is actually summarize them and, as consequence, the formulas will look like this:
SUM (CASE WHEN Ads Platform
= “Google Ads” then Cost
End)
/
COUNT_DISTINCT (CASE WHEN
Ads Platform = “Google Ads”
THEN
CAST(EXTRACT(DAY FROM Date) as Number)
END)
Using exactly the same formula I calculated the average daily budget for Bing.
SUM (CASE WHEN Ads Platform
= “Bing Ads” then Cost
End)
/
COUNT_DISTINCT (CASE WHEN
Ads Platform = “Bing Ads”
THEN
CAST(EXTRACT(DAY FROM Date) as Number)
END)
As a total daily cost, I just summarized these two formulas. So we’ve got:
SUM (CASE WHEN Ads Platform
= “Google Ads” then Cost
End)
/
COUNT_DISTINCT (CASE WHEN
Ads Platform = “Google Ads”
THEN
CAST(EXTRACT(DAY FROM Date) as Number)
END)
+
SUM (CASE WHEN Ads Platform
= “Bing Ads” then Cost
End)
/
COUNT_DISTINCT (CASE WHEN
Ads Platform = “Bing Ads”
THEN
CAST(EXTRACT(DAY FROM Date) as Number)
END)
In fact, these calculations and the logic aren’t easy at all. When we face troubles like this, we don’t have to be afraid and we just need to create some custom formulas. Do check the number you get in the tables, ask yourself whether they make sense or not.
I hope this article about Google Data Studio parameters was educational and interesting for you. If you have any questions, do not hesitate to ask in the comments section!