Supermetrics Support Forum

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

  • Have you guys tried replacing null values with 0? Metrics with values seem to work so it might be worth a shot. Replacing nulls with 0 might even fix the scorecard errors too. Even if it's not the answer, it might still be useful to have the option like we do in sheets!
  • 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.


    image



     I've also provided screenshots of the case statements for each field. 


    image


    CASE

    WHEN Website conversions IS NULL THEN 0 

    ELSE Website conversions

    END


    image


    CASE

    WHEN Leads (form) IS NULL THEN 0 

    ELSE Leads (form)

    END


    image


    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.

Login to post a comment