Exclude data (COUNT) that doesn't meet a predetermined min/max # value

John-Peddle
John-Peddle Contributor
in Magic ETL

We're trying to determine the number of customers that have to call more than a certain number of times in order to have their concern/issue resolved, i.e. those who are not able to receive first call resolution.

 

Domo obviously can determine the number of times a certain phone# has called in, and we've generated a card using the funnel graph but we're having trouble finding a way to filter / exclude customer phone# that have called less than a certain number of times (generally 2x or less), and on the flip side isolate those who have had to call in excessively, i.e. OVER a certain predetermined number of times to exclude partner lines we don't need to review.

 

Is there a beast mode calculation that can help us with this, or is this something we should be able to set in standard filters without a beast mode? Image below shows what we have with no additional calculations, so it's working to provide the number of times a specific customer calls, just want to be able to set parameters.

 

We had several attempts for a Beast Mode that wouldn't validate but were finally able to generate one that does though it's not achieving the expected esults when replacing the CustomerPhoneNumber dimension, which shows unique phone#'s (counted) in the Funnel Section Name; the first screenshot is what happens when replacing the current dimension that actually separates unique phone#'s but does NOT exclude those that don't fall within the criteria set in the Beast Mode below:

 

(
CASE
WHEN COUNT(DISTINCT `CustomerPhoneNumber`) <'3' AND COUNT(DISTINCT `CustomerPhoneNumber`) >'50' then '0' ELSE COUNT(DISTINCT `CustomerPhoneNumber`)
END
)

 

 

Screen Shot 2017-12-26 at 11.07.26 AM.png

 

 

Screen Shot 2017-12-20 at 5.08.51 PM.pngScreen Shot 2017-12-20 at 4.49.30 PM.png

Best Answer

  • ST_-Superman-_
    ST_-Superman-_ Coach
    Answer ✓

    Thanks.  I'm assuming that Callsid is a unique field for each call made.

     

    My thinking is that you would want to add a field 

     

    COUNT(DISTINCT `Callsid`) OVER (PARTITION BY `CustomerPhoneNumber` ORDER BY `CustomerPhoneNumber`) as `NumberOfCalls`

     

    You would need to use the "redshift" option in a SQL dataflow to do this.  This would create a new field with the total number of `Callsid` from a particular phone number in it.  You could then filter your card to only include numbers where this value is between 3 and 50.  You would need to be careful when using this field though, since it is created on the data set, it will not get adjusted if you change the timeframe you are using on the card.  Are you always looking at the same time frame?  Or do you need the ability to adjust on the card whether you are looking at this week, or the last 12 months, or the past 30 days?

     

    It might be worthwhile to evaluate what the purpose is for creating the card rather than just what you want on it.  Sometimes there can be a simpler way to get at the intended direction rather than just creating what was asked for.  


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman

Answers

  • John-Peddle
    John-Peddle Contributor

    Any ideas at all? 

  • John-Peddle
    John-Peddle Contributor

    Since we haven't had any suggestions or feeedback to this point we're going to do a table instead even though it's not what we really wanted for this metric; however, if it can provide the data we need organized in a manner that we can benefit from then we'll try. 

  • bwardell
    bwardell Domo Employee

    Hey there, 

    I believe that you will only need to make a small change to the beast mode that you have, if I understand you right.

    So if I understand you right, you would like all the numbers to list that have called more than 3 times, but less than 50. Is that right?

    If so, this is the change that I would make:

    (
    CASE
    WHEN COUNT(DISTINCT `CustomerPhoneNumber`) <'3' AND COUNT(DISTINCT `CustomerPhoneNumber`) >'50' then '0' ELSE `CustomerPhoneNumber`
    END
    )

    Then you can use this beast mode in the X-Axis, and the Y-Axis. In the Y-Axis, you can then aggregate it using COUNT.

    This should display a funnel with multiple numbers, and counts for each distinct number. 

    If that doesn't work, would you mind including a link to the card?

    Thank you!

    Brandon Wardell
    Associate Technical Advisor

  • John-Peddle
    John-Peddle Contributor

    Hey Brandon, thanks for the reply. Tried the beast mode provided but unable to get it to work (it does validate). Screenshots embeded below showing results prior to and after using updated calculation in FUNNEL SECTION NAME and FUNNEL SECTION VALUE, as well as links to both the Funnel Graph and Table cards. Appreciate your assistance and any help you can provide, if what we're looking to do is even possible.

     

    ** To make it easier to troubleshoot, generated a new the beast mode with the calculation you provided in TEST Phone# Count

     

    Duplicate Inbound Calls: https://peddle.domo.com/kpis/details/158856328

    Copy of Duplicate Inbound Calls: https://peddle.domo.com/kpis/details/166803410

     

    Screen Shot 2017-12-28 at 10.34.28 AM.pngScreen Shot 2017-12-28 at 10.26.45 AM.pngScreen Shot 2017-12-28 at 10.26.10 AM.png

     

  • Darius
    Darius Domo Employee

    Hello John-Peddle,

     

    It looks like your original logic was that to be marked '0', a number would have to call less than three times, and more than 50 times. It is not possible for a number to call less than three times and more than 50 times at the same time, so your else statement was being used in all cases. I believe you can accomplish your goal by changing the logic from AND to OR. That would look like this:

     

    (
    CASE
    WHEN COUNT(DISTINCT `CustomerPhoneNumber`) < 3 OR COUNT(DISTINCT `CustomerPhoneNumber`) > 50 then '0' ELSE COUNT(DISTINCT `CustomerPhoneNumber`)
    END
    )

     

    Using OR logic, if a number calls less than 3 times or more than 50 times it would return 0, but anything between and including 3 and 50 would be counted. Please let us know if this gives you the result you are looking for. Thank you!


    Darius Rose
    **Say “Thanks” by clicking the “heart” in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • John-Peddle
    John-Peddle Contributor

    Good morning, Darius, sorry for the late response; unfortunately, that didn't seem to work either (result in screenshot, similar to what we've seen previously).

     

    From your experience and the goal communicated in the thread, 1) is what we're hoping to accomplish even possible and 2) can we do so using a funnel chart or would we need to consider changing the chart type?

     

    Thanks to you & Brandon for your assistance, it's greatly appreciated!

     

     Screen Shot 2018-01-03 at 9.14.49 AM.png

  • ST_-Superman-_
    ST_-Superman-_ Coach

    I think that the issue here may be more related to the limit of beast mode calculations.  Could you provide a sample of your data set?  I think you could accomplish this by engineering some fields with a MySQL data flow, or possibly a Magic ETL but I would like to see a sample data set before going too far down this road.


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • John-Peddle
    John-Peddle Contributor

    Thanks for the response, @ST_-Superman-_. See the attached Excel sheet, has all columns we're using along with a single row of data (the count of unique customer phone#'s is using CustomerPhoneNumber) - hope this helps, and appreciate any insight you can provide!

  • ST_-Superman-_
    ST_-Superman-_ Coach
    Answer ✓

    Thanks.  I'm assuming that Callsid is a unique field for each call made.

     

    My thinking is that you would want to add a field 

     

    COUNT(DISTINCT `Callsid`) OVER (PARTITION BY `CustomerPhoneNumber` ORDER BY `CustomerPhoneNumber`) as `NumberOfCalls`

     

    You would need to use the "redshift" option in a SQL dataflow to do this.  This would create a new field with the total number of `Callsid` from a particular phone number in it.  You could then filter your card to only include numbers where this value is between 3 and 50.  You would need to be careful when using this field though, since it is created on the data set, it will not get adjusted if you change the timeframe you are using on the card.  Are you always looking at the same time frame?  Or do you need the ability to adjust on the card whether you are looking at this week, or the last 12 months, or the past 30 days?

     

    It might be worthwhile to evaluate what the purpose is for creating the card rather than just what you want on it.  Sometimes there can be a simpler way to get at the intended direction rather than just creating what was asked for.  


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • John-Peddle
    John-Peddle Contributor

    @ST_-Superman-_, thanks for the response and suggestion. We're going to request our Engineering team review and look into applying the solution you mentioned to see if it's feasible as I'm unable to access existing dataflows, SQL, etc. that are marked private as this one is. We appreciate your assistance along with @bwardell and @Darius, and will go ahead and mark this as resolved! Have a great day...

  • Darius
    Darius Domo Employee

    Hello John-Peddle,

     

    We are happy we could review and discuss your question with you. We wish you the best of luck in applying Scott-Thompson's approach through your engineering team. Have a great day and new year as well!


    Darius Rose
    **Say “Thanks” by clicking the “heart” in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Categories