AdWords is now Google Ads. Our new name reflects the full range of advertising options we offer across Search, Display, YouTube, and more. Learn more

Data Studio
4.9K members online now
4.9K members online now
Ask questions about Data Studio
Guide Me
star_border
Reply

Problem - Copy reports with calculated fields and Google Sheets as the data source

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

I use Google Sheets as the data source with Supermetrics to pull Facebook, Twitter, Bing and Linkedin data to Data Studio. Since Data Studio doesn't offer weighted average for aggregation, mentioned in this thread, I have to use calculated fields for relative metrics such as CTR and CPM.

 

There is no option of copying a data source with all the calculated fields. Every time I want to duplicate the same report for other accounts, I have to reconnect to a new sheet and create the same calculated fields again, which is cumbersome.

 

Any tips/tricks for this?

Problem - Copy reports with calculated fields and Google Sheets as the data source

Explorer ✭ ✭ ✭
# 2
Explorer ✭ ✭ ✭

Hi @Zhao H

 

You could try using the Google Sheets IMPORTRANGE function to feed the data from one (Supermetrics powered) sheet to another, where you have can have your calculations run alongside the imported data?

 

Hope that helps,

Ben

Problem - Copy reports with calculated fields and Google Sheets as the data source

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭

Hi Ben P

 

Appreciate the answer!

 

Hmmm I don't see how IMPORTRANGE solves my problem. The root cause here is that you can't pre-calculate the metrics in Sheets but have to calculate them in Data Studio.

 

A simple example

 

Date Impressions Clicks CTR
2017-04-04 10 10 100.00%
2017-04-03 100 3 3.00%
2017-04-02 10 1 10.00%
total 120 14 11.67%

 

Everything but the last row is pulled by Supermetrics. In this case I have to use a calculated field CTR=clicks/impressions in Data Studio to calculate CTR. I can't pull CTR directly from Sheets since there is no weighted average as aggregation. It doesn't make sense to calculate the average of CTRs of different days.

 

Does this make my problem more clear? If so, do you still see IMPORTRANGE as a solution?

 

Best

Zhao

Problem - Copy reports with calculated fields and Google Sheets as the data source

Visitor ✭ ✭ ✭
# 4
Visitor ✭ ✭ ✭

I am facing same issue.

I have calculated fields in a report. When I try to make a copy of the report with new data source all the calculated fields are no more present in the copied report.

Any quick fix to it?

The issue is for example I made 10 calculated fields in x report. When i copy it to make y report and connect it to new data source which has exactly same fields it doesn't pick up calculated fields from previous report. So on every copy report I will always have to add 10 calculated fields to every report copy I do.

Re: Problem - Copy reports with calculated fields and Google Sheets as the data source

Visitor ✭ ✭ ✭
# 5
Visitor ✭ ✭ ✭

Face the same issue here.

 

Is there any update for this requirement?

 

Zhihong

Thanks

Problem - Copy reports with calculated fields and Google Sheets as the data source

Visitor ✭ ✭ ✭
# 6
Visitor ✭ ✭ ✭

Same issue. Is there any option I am missing here?

Problem - Copy reports with calculated fields and Google Sheets as the data source

Visitor ✭ ✭ ✭
# 7
Visitor ✭ ✭ ✭

Hi Harmeet,

 

Only one solution I have found as of now is:

Make calculated fields when one report is attached. Then copy that report with same source. If you do not change the source, the calculated fields will remain in tact.

 

Incase, if you want to change the content, don't import the changes just replace the current content of sheets without changing the header column, reconnect and it should work fine.

 

Let me know if you need further explanation.

Hope it helps.

Problem - Copy reports with calculated fields and Google Sheets as the data source

[ Edited ]
Visitor ✭ ✭ ✭
# 8
Visitor ✭ ✭ ✭

Hi, Harmeet,

 

If I understand you correctly, you copied the report with same datasource, and then change the content in the datasource to get data for the 2nd report? So basicly the 2 reports use the same datasource. 

 

I think this is a work round only for use case that the original report is a one time run report, and the 2nd report has the same structure and need a different access requirement. Otherwise, the 2 reports should be put in one report, just need add a filter control to get different content.  If both of the reports need daily/weekly/monthly update, then this is not the solution.