Welcome to Part 2 of our Favorite Data Studio Tips! If you missed Part 1, read it here – Data Studio Tips Part 1: Beginners.
Custom metrics and dimensions
Chances are, at some point in the reporting process you will need a calculated metric or dimension that is not already in your data set. Cost-per-lead (CPL) and click-through rate (CTR) are common, but they can be refined (rules, formulas, etc. for labeling campaigns, etc.).
Where should I create my new field?
You have the option of creating new fields directly in your table or graphic or adding them to your data source. The important difference is that by adding a field to the data source, you can use the field on different charts, not just the one you are adding it to. For this reason, I usually recommend creating all of your custom fields once at the data source level.
What types of formulas are useful?
All basic math operations are available for custom metrics (SUM (), AVG (), etc.).
The CASE instruction is also a versatile tool that works similarly to an IF instruction that you are familiar with from Google Sheets or Excel.
A note on CASE instructions – you cannot use arithmetic functions in a CASE instruction. It is worth reading the support page linked above before starting any CASE instructions. When you have a bug, Data Studio is not best at explaining what is wrong.
Blending Data: Projections in Data Studio
Merging data is a useful feature in Data Studio once you are familiar with it. In my case, it took a lot of trial and error. One of the most useful use cases for mixing data (and custom fields!) Is with performance projections.
Here is the general method for calculating projections:
- Create custom fields that calculate the days in the current month and the number of days left
- Shuffle the data source. It will look like this:
- Source 1: The date range is from month to yesterday.
- Source 2: Date range is the last 7 days
- Linkage key: Whatever dimension you need projections for (e.g. campaign level, traffic source level, etc.)
- Metrics: cost, any other metrics you need to project
- Build your spreadsheet and create a custom field for the projected cost
- Projected costs = MTD costs + ((NARY_MAX (L7D costs, 0) / 7) * remaining days)
Helpful hint: You can use the NARY_MAX function to consider items that were issued monthly, but none within the last 7 days. Without this option, L7D Cost / 7 returns "-" and not a zero.
Grouping and filtering
You are likely familiar with filter controls and will not be cumbersome to set up. However, in some cases you may want only certain graphics or tables on your page to be filtered (by default, the entire page is filtered). The grouping is ideal here!
To group items:
- Select each item (click and drag or move + click).
- Right click one of the selected items> Group
Date granularity and advanced date options
Date formatting can be difficult when generating reports. Data Studio has some nice date handling features, but they are not particularly obvious to the new user.
If you only have a standard date field (e.g. MM / DD / YY) in your data set, but want to display your data by month or week on your chart, this is where you can adjust the granularity. Just click next to the date field to bring up the menu shown below. This gives you flexibility without having to create new date fields (like week or month).
Advanced date options
Data Studio offers a variety of pre-made date pickers to choose from. If you aren't looking for any of these, scroll all the way down and select "Advanced".
Think of this like a formula that you would use in Google Sheets, except that it's smarter.