In campaign analysis in a B2B set up I want to see how many days an organisation takes to convert from lead to customer after seeing a campaign in the form of an histogram.
Below is the sample data set where there are multiple lead underneath an organisation.
--For e.g organisation abc has three leads- Bill, John and Sam. Sam
is the last one to see the campaign amongst all three i.e. on 14/9/2020 on campaign date column and converted on same day. So for organisation abc it took 0 days to convert. Here we are considering the last campaign date for a given organisation to create time to conversion view in the form of histogram
--Organisation efg has two leads - Don and Harry. Harry
is the last one to see the campaign on 18/9/2020 and converted on 19/9/2020. so organisation efg took 1 day to convert.
-- Similarly organisation pqr took 0 days to convert.
In filter I want to have converted date and region column so when sept is selected in converted date and US on region filter then a histogram view should come up couting 0 as 2 and 1 as 1.
I created a calculated field which capture the max of campaign date for a given organisation ID
if [campaign date] = {fixed[organization id]: MAX([campaign date])} then 1 else 0 END
But not able to create the view in the form of histogram.
You have nearly reached the solution..
Step-1 create a T/F condition so that your criteria is met
[campaign date]={FIXED [org id]: max([campaign date])}
Step-2 convert your field days to convert
to both discreet and dimension.
Step-3 you have a large number of vraiables under this column, you can also create bins of appropriate size (OPTIONAL but this will give a proper histogram look to your chart)
Step-3 (I have not created bins so repeating step-3) add COUNTD(org id) to view, region filter to context, and cond
filter to TRUE
you'll get a view like this
if you'll proceed without creating bins, values of days to convert where no organisation meets the condition, will not show up, while if you have large distinct values in 'days to convert' creating bins will line them up neatly.