Dividing two integer columns that contain 0 values causing incorrect percentage value display (CTR)

DigitalAviator

Hi all,

I've run into an "issue" where I can't seem to get Average/AVG values to calculate correctly when using a custom beast mode. My issue sounds similar to this issue here, where some rows contain a "0" or Null value that messes with the aggregation. When I try to replicate that solution I get another confusing / unfamiliar value as well though. I've tried to fix through Magic ETL as well by replacing 0 values with Null and vice versa where applicable, applying CASE and IF statements to logic the clicks/impression calculation, etc, etc.

Overall, i'm trying to just create a very simple Click-Through-Rate (CTR) calculation using Beast Mode (i.e. total clicks divided by total impressions) but the output in the Multi-Value gauge is showing something completely different than what is shown in a table view breakdown by day. When I see it in a table view like below, its clear that the beast mode calculation is correct; it just has something to do with the Multi-Value Gauge, Date Ranges, etc.

In the image below, the "metrics_ctr" column is a default column through my data source (raw data) and the CTR-New column is my beast mode calculation (clicks/impressions) displayed as a percentage format.

My data source is aggregated/segmented by DAY with common identifiers like the above image and I use the Filter Views dashboard filtering to filter date ranges. All other metrics except for ones that require calculation/aggregation (CPC, CTR, CPCV, etc) are displaying correctly.

Any insight is appreciated! Thank you all ahead of time.

jaeW_at_Onyx

what's your beast mode?

jaeW_at_Onyx

remember zero and null are not the same. assuming your beast mode is sum(values) / count(values)

if you had 5 rows

0,0, null, 2 and 6, the average is 8/4 = 2

if you had 5 rows

null, null, null, 2 and 6 the average is 8 / 2 = 4.

if you are doing division within the row avg( colA / colB ) as opposed to sum(colA) / sum(colB) you'll get a different result too.

i

HeatherDomo

It seems like nulls are causing the issue (as Jae mentioned - zero and null are not the same).

Handling Nulls in the ETL may be the cleanest & easiest solution - referring to this response to handle Nulls in the ETL - https://dojo.domo.com/discussion/11843/convert-null-numeric-values-to-0-in-magic-etl (see most recent update)

DigitalAviator

@jaeW_at_Onyx @HeatherDomo

Very sorry for the delay in response! Thank you both for a quick response. I solved my own issue but may need to create a new topic on a new issue.

For transparency for any other future readers, my new Beast Mode fixed the issue like others suggested:

NEW: SUM(Cost-Metric) / SUM (Impression-Metric)

OLD: AVG( Cost-Metric / Impression-Metric) 

My data source holds "0" values and in the columns for days where those metrics were not captured/ads were not clicked.

New issue:

I want to see the Period-over-Period change for these new metrics, so I added a date segment to the grouping on the multi-value gauge (article here). It doesn't look like DOMO can automatically use the same dataset fields for both the gauge value AND for the comparison value. It looks like I may need to build something along these lines here in Beast Mode as well to accomplish this.

Ideally im looking to just see a very basic Period-over-Period percent change for the gauge value, but the values will be dynamic based on a global date filter on the dashboard page. Does that make sense?

Thank you all for your responses/help.