Closing Rate % And Filtering

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?

screenshot-rumbleon.domo.com-2018.04.10-17-07-49.png

screenshot-rumbleon.domo.com-2018.04.10-17-08-03.png

Find more posts tagged with

Accepted answers

BlueRooster

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

BlueRooster

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?

All comments

BlueRooster

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

Thank you.

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

Where would I put that code?

BlueRooster

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

user06244

OK thanks. I had not done that option before.

I will try it now.

user06244

I am getting an error.

Will you please look?

screenshot-rumbleon.domo.com-2018.04.11-09-24-26.png

BlueRooster

Replace table with consumer_dealer_appraisaldata

user06244

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

Its working! Now to do phase two!

user06244

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

BlueRooster

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

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%

BlueRooster

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

Yes.

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

screenshot-rumbleon.domo.com-2018.04.11-09-58-26.png

BlueRooster

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

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

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

Any suggestions? Please help!

BlueRooster

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

YES! THANK YOU SO MUCH!

This worked!

You are my hero today!

BlueRooster

You're very welcome. Glad I could help