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

How can I get google data studio to play nice with the monthly reports I keep in sheets?

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

I keep track of social media stats in sheets. Most social media pages have very clunky analytics and it is most convenient for me to keep track of monthly statistics as opposed to daily or weekly. I'm new to Data Studio and I would like to be able to show this data in charts and score cards. However, I have hit a couple of snags:

 

1. Data studio won't accept any of my data if I use the filter date picker feature (the dropdown calendar you can put on a report) This is the most annoying of my issues.

2. When using scorecards, it won't give me the average of a column (even with the source marked as "Avg")

3. When making a chart, it won't properly show the stats from each month. It's as if it is taking the sum of the column and displaying that as the result for every month.

How can I get google data studio to play nice with the monthly reports I keep in sheets?

Participant ✭ ✭ ☆
# 2
Participant ✭ ✭ ☆

Can you provide a screenshot of the edit dataset setup in Data Studio? 

 

For #1, I'd suggest creating essentially a fake date in Sheets. For instance, if your row is for April 2017, then write the date as 20170401, then May is 20170501. You can then use that fake date as the dataset dimension, but only pull the month name into the actual chart (in your data setup, create the month by doing month(date)). It's kind of a clunky workaround, but I've been using it for data that I want to keep monthly but still be able to map in DS. 

Re: How can I get google data studio to play nice with the monthly reports I keep in sheets?

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭

The score card is working now, but on the bar chart I'm still getting the full date instead of just the month. I tried changing it to different date settings in "manage sources" and I got an error every time.

Screen Shot 2017-07-18 at 1.30.50 PM.png

How can I get google data studio to play nice with the monthly reports I keep in sheets?

Participant ✭ ✭ ☆
# 4
Participant ✭ ✭ ☆

If you use "date" as your dimension, then you're going to get the full date as listed. In order to get the month, you need a separate month dimension. In your DS dataset setup, create a new dimension for month that equals month(date). Then pull that month dimension in instead of the date. 

 

Caveat is that this will lump the data from the same month of different years together. You could either do a line chart and pull in a second dimension of year to get year over year lines. Or, if you want a bar chart, you need a monthyear dimension instead of a month dimension, which I believe can also be built off the date. Another alternative is to just restrict your chart to a specific time range that includes one year's data.

How can I get google data studio to play nice with the monthly reports I keep in sheets?

[ Edited ]
Follower ✭ ✭ ✭
# 5
Follower ✭ ✭ ✭

Did you try to pick a Month as your Dimension? If it's just not there, make sure your Day field is of the right format, and add a new field to your Data set (click the pencil, and then '+') - name it Month, enter the formula MONTH(Day), where Day should be inserted as your field - you should see the green oval background under it. When you save, the Type of a new field should show as Month (MM)