Funnel Chart Summary total to top of chart?

MLAstuto

Hey team, 

 

i'm hoping someone can help me here; i created a Parameter allow me to create a lead source funnel based on a filtered report. The filtered report gives me a total number of 861 leads (represented by the top left summary number).

 

The parameter created basically states "IF this THEN category";  which gives me 3 categories within the 861 total of Owned, Rejected, and converted. So far everything is working very well. However I would like to represent the 861 total from the summary number as the TOP most block on my funnel visual... can i do this by adding something to my funnel parameter?

 

My funnel parameter, see visual attached for result:

 

(CASE
WHEN `Converted` = 'true' THEN '3. Rep Converted'

WHEN `Status` = 'Rejected' AND
        `Lead Status Reason` = 'Bad Data' OR
        `Lead Status Reason` = 'Consumer' OR
        `Lead Status Reason` = 'Customer Service Request' THEN '2. Rep Hard Reject'
WHEN `Owner ID` <> 'Home Use' THEN '1. Rep Owned'
WHEN `Owner ID` <> 'Dead Data' THEN '1. Rep Owned'
ELSE 'Other'

END)

 

Summary 861 total to top of funnel help?

GrantSmith

Hi @MLAstuto 

You'd need to pre-aggregate / pre-calculate your data via a data flow since you're partitioning based on the  beast mode field. Because of how you're computing the grouping via the beast mode it no longer is part of the collective but that specific group.

 

You'd essentially need to have 2 records for each. One for your "All" or 0 group and the other for the assigned [1-3] grouping. This way it will allow you to graph the value twice in your funnel.

jaeW_at_Onyx

Instead of aggregating the data with a SUM() I would simply UNION the data on top of itself, and then assign a hard coded value of "Rep Owned" to the first copy of the data in the column that usually contains "Accept" or "Converted".  

 

THAT said, assuming you're working with salesforce or similar data that tracks opportunities, you probably need to go back and create a recursive data flow that captures a new row for the opportunity any time the status changes.

 

Right now you're only talking about 3 statuses, and we are assuming that everything has to be owned before can be accepted or converted.  but ideally, you want to capture a row when the opportunity was handed to a rep, and then a new row when the opportunity gets accepted, and then a new row when it gets converted or closed.  

 

If i had to guess, currently you're only capturing the current state of the opportunity (what' stage is it in) and therefore do not have an accurate picture of change over time.