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

DigitalAviator
DigitalAviator Member
in Magic ETL

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.


CTR-ss.png

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.

Tagged:

Comments

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    what's your beast mode?

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    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

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • HeatherDomo
    HeatherDomo Domo Employee

    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
    DigitalAviator Member

    @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.

Categories