- Posted by Garima Mathur
- On June 26, 2019
- 0 Comments
In today’s article, you will learn 3 tricks from the google data studio, which can make your life easier. Data Studio is Google’s powerful reporting and visualization solution for users who want to collate data from multiple sources into a single comprehensive, yet aesthetic visualization platform.
The 3 hacks from data studio in this article are:
- Blended Data
- Calculated metrics
- Rolling Date ranges
So, when will you need this?
Consider a situation where you have multiple data sources and you want to create a single data source containing specific information from all the relevant sources. In this case, either you can create an offline source or you can collate the data in google sheets. But, if you are using connectors within data studio then you cannot do this merging either in Google sheets or in offline excel.
In such situations, blending data sources within the Data Studio Interface will be the best solution and will also help a great deal in automating your data structure.
For example; If an organization wanted to find out how much do they spend on their marketing efforts with v/s without the retainer they pay to the marketing agency for Facebook. The first data source can be a Facebook connector within Data Studio, the second data source would be a google sheet containing the daily retainer cost. To add this extra information of the retainer into the marketing data coming from Facebook, they can create a new Blended data source, and create a joining link based on dates between the 2 diverse data sets.
Then they can get 2 different visualizations, one with the original data set and the other with the blended one.
Let’s look at how you can do that.
- Select the option to blend data
- A new table will appear in which there will be an option to add another source of data.
- On clicking that, a variety of different options would be provided for the other data source linked to your dashboard.
- Next, you need to provide a common key among all the data sources comprising as input for the blended data
- Add the dimensions and the metrics of both the data sources according to your requirements
Please note, your existing calculated metrics in individual data sources might not work as expected. So, please cross check on that.
2. Calculated Metrics
In Data studio often we need some extra calculations with the available data, for example, our data source can provide us cost and revenue, but to calculate ROI we need to divide revenue by cost which might not be available directly as a metric. To solve this we can take 2 ways, either create a separate data source in Google sheets with the calculated metric values and upload that, or a hack is through using the Create field option in Data Studio itself.
- Choose the option of create field as shown in the figure below:
- In that a new dialogue box will open, this is represented by the figure below:
- The metric can be calculated according to the requirements of your data. In the figure above, the example of Conversion rate is taken. The metric is named CVR and its calculation is based on 2 fields which already existed in the data.
Another trick :
- In contrast to the figure above, the data studio may not give the best result with the above formula, especially in cases where you are using multiple filters and dimensions. To solve this we can replace the above formula by this:
- Now we have used the SUM function. This will aggregate the data based on the combinations of dimensions and filters used in the dashboard.
There are various other functions also which can help to calculate figures from both the original source as well as blended data sources. Few of the popular ones are:
- Arithmetic : Addition, Subtraction, multiplication and Division
- Mathematical Formulas: MAX(), MIN(), SUM()
- Logical Comparisons : Allows data to be compared with by IF/ELSE or CASE/WHEN comments.
3. Rolling Date Ranges
Consider a situation in which you would like a visualization chart in data studio to only show a last week’s data every time you open the dashboard. However, it can be extremely cumbersome to update the date range of the data every week for accurate reporting. It can be cumbersome for some stakeholders at least.
For such problems, it is required to customize the date range to get updated automatically in a rolling manner. The rolling date range will enable you to represent the data according to the number of days/weeks/years with which you want refresh the data automatically.
So, how to enable a rolling date range?
- In the default date range, choose the custom option.
- In that there are many options to choose which category of date range is required. Options such as from last year to last month to meet your requirements, but there is also an advanced option.
- In this advanced option, you can add and subtract the number of days/years/months from the calendar and customize it accordingly. This will set the time period for which you want the data to be showcased in a rolling manner irrespective of the date on which you access the dashboard. In the example above, this option will only show the data since 16 days from today till day before yesterday, whatever the current date might be.
Therefore, these 3 hacks can really help you in google data studio, to create reports which are more clear, precise and visually appealing faster.