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

Google Sheets - Percentage by Month - No Data error

[ Edited ]
Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

Hello, I've set up a Google Sheet I'm want to connect to Data Studio. The columns are date (month) and CTR (percentage).

 

I want to create a scorecard with the percentage for that month. If you change the date range to the previous month(s), I want to be able to see the percentage for that month.

 

I've tried this several ways but get Configuration errors or "No Data". 

 

I'm not sure how to set up the aggregation for the CTR field in data studio to get the results I need, or if it is an issue with the Google Sheet data itself.


Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
Marked as Best Answer.
Solution
Accepted by topic author Monica H
May 2017

Google Sheets - Percentage by Month - No Data error

Explorer ✭ ☆ ☆
# 6
Explorer ✭ ☆ ☆

@Monica H, I got this to work. My date was in YYYYMMDD format, so for the month of May, I used 20170501. Then, as @Litmon said, make sure your CTR is set to Percentage (not Number) and Average (not Sum).

 

 

View solution in original post

Google Sheets - Percentage by Month - No Data error

Participant ✭ ✭ ☆
# 2
Participant ✭ ✭ ☆

I'm wondering if your problem might be the date? Try putting the date in your sheet in the format YYYYMM and then edit your dataset to specifically tell the data that the date is in YYYYMM format. I've found that DS can be finicky with dates. 

 

Also, check in your dataset that the CTR is set up as a percentage.

Google Sheets - Percentage by Month - No Data error

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭

I have CTR as a percentage, and I think I may have the date range correct; it's MMDDYYYY in the google sheet but YYYYMMDD in date studio. Is it possible to set it up as month only in both? I don't need the data by day, just month.

 

Thank you!

Google Sheets - Percentage by Month - No Data error

Participant ✭ ✭ ☆
# 4
Participant ✭ ✭ ☆

In DS, click the pencil next to the dataset. Once there, click the plus sign to add a new dimension. Your formula would be month(whatever your YYYYMMDD dimension is called). Once you've done that, it should automatically classify that as a MM dimension and then you can use it in your filters and charts.

Google Sheets - Percentage by Month - No Data error

Explorer ✭ ☆ ☆
# 5
Explorer ✭ ☆ ☆

I've never tried to only enter a month at a time. This is likely your issue since DS tries calculating things with date ranges and averages/sums data for each day in the selected date range. If you are unable to collect CTR data for each individual day, you may want to create a document that has the monthly CTR average for each actual day of the month. This way when you select that time frame, DS will average out the CTR for the days selected - giving you your score card.

Marked as Best Answer.
Solution
Accepted by topic author Monica H
May 2017

Google Sheets - Percentage by Month - No Data error

Explorer ✭ ☆ ☆
# 6
Explorer ✭ ☆ ☆

@Monica H, I got this to work. My date was in YYYYMMDD format, so for the month of May, I used 20170501. Then, as @Litmon said, make sure your CTR is set to Percentage (not Number) and Average (not Sum).

 

 

Google Sheets - Percentage by Month - No Data error

Visitor ✭ ✭ ✭
# 7
Visitor ✭ ✭ ✭

OK, let me try that. I'm continuing to run into issues with this one. We need a CTR (%) for each month of the year. I have this set up in a Google Sheet. I'll change the date format and try average. Thanks!

Google Sheets - Percentage by Month - No Data error

Explorer ✭ ☆ ☆
# 8
Explorer ✭ ☆ ☆

@Monica H, When I went back and looked at my test report, I found that my table was reporting the monthly CTRs correctly but my scorecard was only reporting the average - despite selecting different date ranges.

 

I noticed my "Date Range Dimension" was blank/undefined. Once I selected "Month" it now reports perfectly.

 

Google Sheets - Percentage by Month - No Data error

Explorer ✭ ☆ ☆
# 9
Explorer ✭ ☆ ☆

Any luck, @Monica H?

Google Sheets - Percentage by Month - No Data error

Visitor ✭ ✭ ✭
# 10
Visitor ✭ ✭ ✭

Yes, this is working for me now after changing the date format. Thank you!