Welcome to Part 2 of our favorite Data Studio tips! If you missed part 1, check it out here – Data Studio Tips Part 1: Beginner.
Custom Metrics & Dimensions
It’s likely that at some point in your report creation, you’ll need a calculated metric or dimension that isn’t already included in your data set. Common ones are cost per lead (CPL) and click-through-rate (CTR), but can be more advanced (campaign labeling rules, formulas, etc.)
Where Should I Create My New Field?
You have the option to create new fields directly within your table or graph, or you can add it to your data source. The important difference here is that adding a field to the data source allows you to use the field across different charts rather than just the one you add it to. For this reason, I typically recommend creating all of your custom fields one time at the data source level.
What Kinds of Formulas Are Useful?
For custom metrics, all of your basic mathematical operations are available (SUM(), AVG(), etc.).
The CASE statement is also a versatile tool that functions similarly to an IF statement that you’ll be familiar with from Google Sheets or Excel.
A note about CASE statements – you cannot use arithmetic functions within a CASE statement. It’s worth reading through the support page linked above before you get started with CASE statements. If you have an error, Data Studio isn’t the best about explaining what is incorrect.
Blending Data: Projections in Data Studio
Data Blending is a useful feature in Data Studio once you get familiar with it. In my case, this took a lot of trial and error. One of the most useful use cases for data blending (and custom fields!) is performance projections.
Here’s the overall method for calculating projections:
- Create custom fields that calculate the days in the current month and how many days are remaining
- Blend the data source. It will look like this:
- Source 1: Date range is month-to-yesterday.
- Source 2: Date range is the last 7 days
- Join keys: whatever dimension you need projections for (ex: campaign level, traffic source level, etc.)
- Metrics: Cost, any other metrics you need to project
- Create your table and create a custom field for the projected cost
- Projected cost = MTD Cost + ((NARY_MAX(L7D Cost,0)/7)*Days Left)
Helpful hint: the NARY_MAX function here allows you to account for items that have month-to-date spend, but none within the last 7 days. Without it, L7D Cost/7 will return “-” and not a zero.
Grouping and Filtering
You’re probably familiar with filter controls, and they aren’t fussy to set up. However, in some cases, you might want them to only filter certain graphs or tables on your page (by default they will filter the entire page). This is where grouping comes in handy!
To group items together:
- Select each item (click and drag or shift + click).
- Right-click on any of the selected items > group
Date Granularity & Advanced Date Options
Date formatting can be a tricky thing when it comes to reporting. Data Studio has some pretty nice date handling features, but they’re not super obvious to the new user.
Granularity
If you only have a standard date field (ex: MM/DD/YY) in your data set, but want to show your data by month or week in your graph, this is where you’ll adjust the granularity. Simply click next to the date field to get the menu shown below. This allows you some flexibility without needing to create new date fields (like week or month).
Advanced Date Options
Data Studio has a ton of pre-built date selections for you to choose from. If none of these are what you’re after, scroll down to the very bottom and select “Advanced”.
Think of these like a formula you’d use in Google Sheets, except smarter.
