Closing Rate % And Filtering

user06244
user06244 Member
in Magic ETL

So this is the first time I have not been able to find a solution on other peoples threads, so here is hoping! 

 

Our company gives cash offers for vehicles. 

They Submit a Form and Recieve an offer they can accept. 

I am trying to get the acceptance rate % for the current month. 

 

So I have it Counting The Submission Dates and Counting the Acceptance Dates to create the Acceptance Rate Percentage. 

The problem is I have to sort for the current month. If I sort for submission date this month, I lose the accepted ones that were submitted before the current month. If I sort for acceptance date, it filters it down to only those with an accepted date (Making it think it is 100%).

 

HOW do i tell it to sort for both dates?

 

See details attached. 

 

The first image is sorted by submission date - so the Accepted count is off by about 70 but the submission number is correct.

The second image is sorted by accepted date - so now it shows the correct number of accepted (282) but limits the submissions to the same number

 

This is really messing up the acceptance rate that we need to have! 

 

Please help! Is there a beast mode? 

 

Best Answers

  • Valiant
    Valiant Coach
    Answer ✓

    So we encountered a similar issue with multiple dates as well. What we desided to do was create a 'Unified Date' column as your date range. Basically allowing you to set a date range and all dates (submissions and acceptance in your case) would be returned.

     

    You start with a data transform, the basic code goes like this:

    SELECT *, 
                  `Submission Date` AS 'Unified Date',
                  'Submission' AS 'Date Type'
    FROM table
    WHERE `Submission Date` IS NOT NULL

    UNION ALL

    SELECT *, 
                  `Acceptance Date` AS 'Unified Date',
                  'Acceptance' AS 'Date Type'
    FROM table
    WHERE `Acceptance Date` IS NOT NULL

    Now you when you use this as your dataset, set the Unified Date as your Date Range option. Then you build individual beast modes for all the values you want to display.

    Example. If you were orignally doing a COUNT(`Submission Date`) it would now need to be:

    COUTN(CASE WHEN `Date Type` = 'Submission' THEN `Submission Date` END)

    Basically just building Case When checks on the Date Type field for each place you need those values.

     

    This will allow you to keep separate date counts for all dates (submission or acceptance) that fall within a specific date range. 

     

    Let me know if you have any other questions.

     

    Sincerely,
    ValiantSpur

     

     

  • Valiant
    Valiant Coach
    Answer ✓ 
    SUM(CASE WHEN `Date Type` = 'Acceptance'                     
               THEN 1
              END)
    /
    SUM(CASE WHEN `Date Type` = 'Submission'  THEN 1 END)

    What are you results with the above? 

Answers

  • Valiant
    Valiant Coach
    Answer ✓

    So we encountered a similar issue with multiple dates as well. What we desided to do was create a 'Unified Date' column as your date range. Basically allowing you to set a date range and all dates (submissions and acceptance in your case) would be returned.

     

    You start with a data transform, the basic code goes like this:

    SELECT *, 
                  `Submission Date` AS 'Unified Date',
                  'Submission' AS 'Date Type'
    FROM table
    WHERE `Submission Date` IS NOT NULL

    UNION ALL

    SELECT *, 
                  `Acceptance Date` AS 'Unified Date',
                  'Acceptance' AS 'Date Type'
    FROM table
    WHERE `Acceptance Date` IS NOT NULL

    Now you when you use this as your dataset, set the Unified Date as your Date Range option. Then you build individual beast modes for all the values you want to display.

    Example. If you were orignally doing a COUNT(`Submission Date`) it would now need to be:

    COUTN(CASE WHEN `Date Type` = 'Submission' THEN `Submission Date` END)

    Basically just building Case When checks on the Date Type field for each place you need those values.

     

    This will allow you to keep separate date counts for all dates (submission or acceptance) that fall within a specific date range. 

     

    Let me know if you have any other questions.

     

    Sincerely,
    ValiantSpur

     

     

  • user06244
    user06244 Member

    Thank you. 

    So to confirm, the first bit of code is not a beastmode correct? 

    Where would I put that code? 

  • Valiant
    Valiant Coach

    You would need to do a SQL transform in the Data Center. It should be next to the ETL option

  • user06244
    user06244 Member

    OK thanks. I had not done that option before. 

    I will try it now. 

  • user06244
    user06244 Member

    I am getting an error. 

     

    Will you please look? 

     

     

  • Valiant
    Valiant Coach

    Replace table with consumer_dealer_appraisaldata

  • user06244
    user06244 Member

    Oh I feel silly. Sorry - new to SQL in general. 

     

    Its working! Now to do phase two!

  • user06244
    user06244 Member

    One more silly question. 

     

    It wants me to add output data set in order to save

     

    But the SQL cant be blank in it.

    What do I need to put there

  • Valiant
    Valiant Coach

    Put this in the SQL

     

    SELECT * FROM transform_data_1

     

    And then give it an appropriate name. This will be the new dataset for your card

  • user06244
    user06244 Member

    At this point the new counts are correct for both submitted and accepted. 

    Now the problem is the acceptance rate calculation is not coming up properly.

     

    I have it as

     

    COUNT(CASE WHEN `Date Type` = 'Accepted' THEN `AcceptedDate` END)
    /
    COUNT(CASE WHEN `Date Type` = 'Submission' THEN `SubmissionDate` END)

     

    Its coming out to 0%

  • Valiant
    Valiant Coach

    Is it still 0 if you multiply by 100?

     

    COUNT(CASE WHEN `Date Type` = 'Accepted' THEN `AcceptedDate` END)
    /
    COUNT(CASE WHEN `Date Type` = 'Submission' THEN `SubmissionDate` END)

    * 100

  • user06244
    user06244 Member

    Yes. 

    And I formatted it to have like 8 decimals to see if there was any value - but there was none.

     

  • Valiant
    Valiant Coach

    Try this:

     

    SUM(CASE WHEN `Date Type` = 'Submission' 
                            AND  `AcceptedDate` IS NOT NULL 
               THEN 1
              END)
    /
    SUM(CASE WHEN `Date Type` = 'Submission'  THEN 1 END)
  • user06244
    user06244 Member

    That gave me a percentage - but an incorrect one. 

     

    The Current numbers are 292 Accepted and 1954 Submitted which is 14.9%

     

    The calculation pulled 11.8%

  • user06244
    user06244 Member

    Update: I removed the unified date filter and had it give me all time metrics - And the acceptance rate was correct.

     

    So it appears that I am back to square one, that when I filter for the current month, it is missing part of the data. 

     

    I am filtering on the new unified date

  • user06244
    user06244 Member

    Any suggestions? Please help! 

     

  • Valiant
    Valiant Coach
    Answer ✓ 
    SUM(CASE WHEN `Date Type` = 'Acceptance'                     
               THEN 1
              END)
    /
    SUM(CASE WHEN `Date Type` = 'Submission'  THEN 1 END)

    What are you results with the above? 

  • user06244
    user06244 Member

    YES! THANK YOU SO MUCH! 

     

    This worked!

     

    You are my hero today! 

  • Valiant
    Valiant Coach

    You're very welcome. Glad I could help

Categories