Solved
Custom Metrics in Data Studio Connectors not working properly
I would like to add values across multiple columns when using various Data Studio Connectors, like Facebook ads, Adwords, and Analytics, but the custom metrics do not calculate properly if any of the columns contain null values.
For instance, suppose I want to add multiple actions within the Facebook Ads Connector, such as Leads (form) + website leads, the results will look like this:
| Campaign | Leads (form) | Website Leads | Total Form Fills (Custom Metric) |
| Campaign 1 | 100 | 20 | 120 |
| Campaign 2 | null | 300 | 0 |
| Campaign 3 | 200 | null | 0 |
Is there a way to convert null values to 0 or some other way to get columns to add up properly?
Hi!
Unfortunately this is something that we cannot influence. Many people have been discussing about the same Data Studio issue at https://www.en.advertisercommunity.com/t5/Data-Studio/Replacing-No-Data-with-0/td-p/999599#
Best regards,
-Supermetrics Team
I've found a solution--hoping it is helpful for everyone else!
The first two columns are the original metrics from the connector (producing null values): Website conversions and Leads (form). As you can see, the third column (adding the first two fields together) resulted in errors, because it could not add together null values.
I had to create three new fields: 2 case when statements converting null values for each field into 0, and 1 field adding the two new case when statement fields together. These result is shown in the last three columns above.
I've also provided screenshots of the case statements for each field.
CASE
WHEN Website conversions IS NULL THEN 0
ELSE Website conversions
END
CASEWHEN Leads (form) IS NULL THEN 0
ELSE Leads (form)
END
calcWebsiteConversions+calcLeadsForm
Good luck!
Thank you for your help, this worked for me. Lazy design on the part of Supermetrics Team! This should be really simple to fix.