Data Studio from Google is an awesome data visualization tool. With its ever growing list of features there may be a few that have flown under your radar. One of those features is creating your own formulas which is what I am going to expand on.
In this example there are two goals and we would like to see the number of goal conversions separately but also together. While you may think this is as quick as just adding them together, the process to make it automated can be a little more complicated.
The first goal we are using is Contact Us Form Completions, the second is Click to Call. Each month we want to see these numbers separate as well as the total number of “Contact Us Completions” (click to call & form completions).
Creating the scorecard to capture the number of completions individually is as easy as linking the scorecard to the appropriate goal. The tricky part is getting these goals to add up together and display the correct Total.
A simple addition formula would add the goal completions up but what happens if there are 0 Contact Us Form completions for the month and 2 Click to Call completions? With a simple addition formula it will return Null/No Data or 0, this is where Case and Else statements come into play. These formulas allow data studio to display the correct information regardless of the amount of completions one goal or another has had.
To create a formula in data studio and once you have selected the Scorecard chart you will go to the metric box and select “Create Field” which will open up the dialog box where you can name your metric and paste the formula once it is created. You can build your formula in this dialog box but I would recommend writing it out in notepad:
First, we are going to create the Case Statement for Goal 1: This formula shows that WHEN the Contact Us Form goal completions is NULL or 0 THEN we want Data Studio to show 0 completions. However, if there are Contact Us Form completions (ELSE) then ADD (+) them to Goal 2 completions. The formula that you will input into Data Studio will look like this: CASE WHEN Contact Us Form (Goal 1 Completions) IS NULL THEN 0 ELSE Contact Us Form (Goal 1 Completions) END +
You will then repeat the same formula as, above but with the 2nd goal, note that this will end the formula so we will not include the “+” after END: CASE WHEN Click to Call (Goal 2 Completions) IS NULL THEN 0 ELSE Click to Call (Goal 2 Completions) END
The highlighted areas above are the exact goal names along with its goal number. When entered correctly into the formula box, Data studio will automatically turn them into metrics. If you enter your formula and this does not happen then, there is an error in your formula which Data Studio will alert you to.
The complete formula looks like this: CASE WHEN Contact Us Form (Goal 1 Completions) IS NULL THEN 0 ELSE Contact Us Form (Goal 1 Completions) END +
CASE WHEN Click to Call (Goal 2 Completions) IS NULL THEN 0 ELSE Click to Call (Goal 2 Completions) END
Our End result is that we now have a formula that will automatically calculate our Total Goal Completions no matter what date we select in Data Studio. Case Else functions are just one of the many types of functions you can use in Data Studio. To learn about additional functions available in Data Studio check out the Function List here. Happy Analyzing!
