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.3K members online now
4.3K members online now
Ask questions about Data Studio
Guide Me
star_border
Reply

Daily average from monthly database

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

Hello everyone,

 

I have imported this Google Spreadsheet database into my project and I needed to create a daily average per month graph out of a metric. My original database goes something like this:

 

 

Month (AAAAMMDD) | Metric
2010/01/01 | 2346
2010/02/01 | 2776
2010/03/01 | 3232
2010/04/01 | 3507

 

It goes on basically up to today (and keeps being updated).

 

I would need the daily average for every month displayed on a graphic. I could simply divide the metric by 30, but it would inaccurate given that there are months with 28-31 days. 

 

Perhaps adding another row with simply the name of the date and a Case statement to divide accordingly --and Feb. depending on the year due to leaps. It seems a lot for a simple issue.

 

So, how's the best way can I do that using Data Studio?

Daily average from monthly database

Visitor ✭ ✭ ✭
# 2
Visitor ✭ ✭ ✭

Hi Raphael, I'm looking to do the same thing within a data studio project. Did you ever find a solution to this?

Daily average from monthly database

Follower ✭ ✭ ✭
# 3
Follower ✭ ✭ ✭

Hello,

 

You have to copy your date and set it to a YYYYMM format.

Then copy your metrics and set it to number with average treatment.

 

It will do the work!

Daily average from monthly database

Visitor ✭ ✭ ✭
# 4
Visitor ✭ ✭ ✭

Hmm tried that with (MM) format and it didn't work, will give your solution a shot if I get the chance. I ended up just doing the work in a separate sheet in Google Sheets. Thanks for your reply Smiley Happy

Daily average from monthly database

Follower ✭ ✭ ✭
# 5
Follower ✭ ✭ ✭

Yes you can't do it with a MM format, because if you set a YYYYMMDD into a MM format, Data studio will take the first YY as the MM... So it won't work.

Re: Daily average from monthly database

[ Edited ]
Visitor ✭ ✭ ✭
# 6
Visitor ✭ ✭ ✭

@Greg R

 

As a matter of fact, I kind of did.

 

First I created a metric containing the number of days each month had, like this:

 

CASE WHEN REGEXP_MATCH(Mês_no, '(1|3|5|7|8|10|12)') THEN 31 WHEN REGEXP_MATCH(Mês_no, '(4|6|9|11)') THEN 30 WHEN Ano  NOT IN ( '2012' , '2016' , '2020' , '2024' , '2028' , '2032' ) THEN 28 ELSE 29 END

It's in Portuguese, "Mês_no" is the number of the month. "Ano" means year.

 

Then I created a second metric which divided the number I wanted by this first metric.

 

I wanted to get the daily average of confirmed subscriptions per month. So it went something like this:

 

Confirmed subscriptions / number of days a month

It isn't pretty, but it actualy works. And it's good up to 2035 hahaha

 

 

@analytics a, I'm not sure I understand what you mean. Could you, please, elaborate more?

Daily average from monthly database

Visitor ✭ ✭ ✭
# 7
Visitor ✭ ✭ ✭

Thanks for your reply Raphael, that looks like a promising way to do this. Will need to keep this in mind for future. Thank you!