A while back now I made a post about how to report against targets in Google Data Studio. If you haven't read that you can find it here:
More than a year later I'm still pretty happy with that report, and it certainly serves a purpose.
However, anyone who has followed the steps there of setting a static target value may have come across some of it's limitations. For starters, that report assumes you always have the same target from month to month, but for many businesses this is not the case. Let's say you sell sunblock, you wouldn't expect to sell the same volume in the middle of Winter as you would in the Summer.
Similarly, if your target is static from month to month, poor February is going to catch a bum wrap when it doesn't make target due to having 3 fewer days to make target than January.
Finally, the static target does not account for partially completed months, so if you want to know how you're doing against your target 7 days into the month, you will want to be comparing with your target for 7 days rather than your target for the month.
With a few calculated fields in Google Data Studio we can set monthly targets, and set them to adjust based on the number of days being viewed in the report. That way you can plot performance against varying targets like this:
Or you can include a target comparison that is updated depending on the date range you select like so:
To do this we start by creating a calculated field* to specify our target for each month. In this example I'm using sessions, but you can adjust for goal completions, revenue, transactions or any of the metrics you report on. (Haven't made a calculated field before? I'll add some brief instructions on how to do that at the end of this post).
Setting your monthly targets
We want to write a case statement that says when the month = x the target = y. You might notice that if you use the "Month of the year" dimension in a chart it shows you the month name, however when using this dimension in a calculation you have to match on the months numerical value e.g. Jan = 01, Feb=02.
You could start using this as is, and it would be pretty useful. You could make the line/bar chart above and it would do the trick. Simply add our new "Monthly Target" Metric to a combo chart alongside the metric you are comparing too:
However, you might want to adjust this so that if you have a partially completed month displayed this is accounted for in the chart e.g. this chart below was showing data up to part way through March, as such the target value has been adjusted to only show the target for the month to date, not the entire month:
Adjusting the targets for partially completed months
The way we do this is to divide the monthly target by the number of days in that month and then multiply it by the number of days covered in the report.
e.g. if the January target was 100,000 and a user is looking at just the first 7 days in January we would want to divide that 100,000 by the number of days in the month (31) and times that by 7. So our 7 day target would be 22,581.
We will need to create another calculated field, this time to specify the number of days in a given month. The format for this is very similar to the target statement; when month = x days = y.
We can then incorporate this value into a calculated metric to work out what the daily target is for any given month.
To figure out how many days are being shown in the report we can use the COUNT function and count how many unique dates there are in the data set we are looking at. So if a user has selected Jan 1 to Jan 7, our function would count 7 different dates. You'd know the drill by now, create another calculated metric called 'Count of Date':
To make our adjusted target value we would use the following calculation, but wait for it, before the calculation below works you have to do some minor tweaking. See those little characters "SUM" and "AUT"? They indicate that these fields contain aggregated data.
You can change your metric aggregation here by clicking back to all fields, search for your newly created fields and select from the drop down options.
Now our target will increase/decrease depending on how many days the report user is looking at, and will take the length of a month into consideration when displaying target values!
*Creating a calculated field
To create a calculated field in Data Studio hover over the data source on any of your charts in the setting on the right hand side and click the pencil icon to edit the data source:
Once in the data set options screen, click the Add A Field button off to the right hand side:
That will bring up the custom field editing panel:
Once you've created a calculated field, the metric or dimension will be available to use in your metric/dimension pickers as per any of your standard fields in your data set: