Working with Multiple Date Fields

gilbec01 · 2022-12-15T17:39:58+00:00

There was an error rendering this rich post.

I'm struggling to find a solution for creating things like conversion rates between metrics that are dependent on different date fields.

For Example:

looking for the ratio of leads created (created_date) and how many demos were scheduled (scheduled date) in the same month.

It's important to note that the scheduled count is not a subset of the leads created. There could be leads created in the previous month that were scheduled in the month I'm looking at and I want to include those still.

I have separate beast modes for Total Leads and Scheduled Demos that work on their own, but when I combine the expression for each of them into one beast mode I see incorrect numbers because they individually refer to different date fields.

Has anyone ran into this or have any ideas of how to make this work?

Thanks in advance!

Find more posts tagged with

Accepted answers

All comments

MichelleH

@gilbec01 You could append the leads created and demos scheduled in a dataflow and rename their respective date fields to be called "Date". In this scenario then you can create a constant column to each sub-table called "Activity Type" with a value of "Create" or "Schedule Demo" before appending to differentiate between activities. To calculate conversion rate using the new dataset, you can base your card off of the "Date" column and write a beast mode like the one below:

sum(case when `Activity Type` = 'Schedule Demo' then 1 else 0 end) 
/ 
sum(case when `Activity Type` = 'Create' then 1 else 0 end)