How to find true waterfall on MEL, MCL for month

Level 5

How to find true waterfall on MEL, MCL for month

I am curious how everyone reports on MCL and MELS from Marketo to show all MCL, or MELS that hit the stage of flow (without using RCE). I find our numbers do not show a true waterfall and report this manually through excel sheets and pulling out data weekly.

Any best practices out there that pull in this information that you find accurate. 

Level 10

Re: How to find true waterfall on MEL, MCL for month

Hey Tara,

I'm more familiar with MAL (Marketing Accepted Leads) and MQL (Marketing Qualified Lead), so I'll talk in those terms, but you can obviously swap them out with the revenue stages that your org uses.

In SFDC, you can make a nice dashboard based on a report such as this:


That's my personal favorite type of graph, because it should show two things: (1) the total height of each bar graph growing higher month-over-month (or quarter-over-quarter) and (2) the color of previous months should change colors toward end-stages the further back you get. This helps point out things like "there's a bunch of persons in a transitional stage still, when a lead should only be an SAL (Sales Accepted Lead) for about a week before moving to SQL or MRL (Marketing Recycled Lead)." That's all demo data above there, so the look more at the format than the data itself.

Another way we've done this before, in a cost-conscious way, is through an Excel workbook with raw data tabs that are filled by reports. We have the smart lists show the important information, including the revenue stage and the other corresponding information. Put that into a lead performance report with custom columns for the smart lists, and subscribe to the report so it gets emailed once a month.

Here's some more demo data, but a subsection of the Excel workbook as a dashboard:



These end up being powered by the report numbers edited in a separate tab:


Of course, all the data relies on your having a really strong Lifecycle Processing program in place that stamps stages methodically and reliably. "Lifecycle Status" is an actual field that we're stamping those values with, and we're date/time stamping whenever they hit a new stage into separate fields. Re: Lifecycle Processing to guide and structure your marketing.


Edward Unthank | Etumos



In looking more at your request, let me add some more technical depth.

I think what you're needing is Lifecycle Processing and date/time stamping when a person hits a certain stage.


The example above is our Lifecycle Processing program. The important information from above is that we have a triggered smart campaign when a lead/contact hits a certain lifecycle milestone. For example, if someone hits a behavior score threshold of 100 or fills out a hand-raise form (e.g., Contact Sales or Demo Request), they trigger the MQL milestone. We then do a few things to them, the most important to this conversation being: (1) change their "Lifecycle Status" to "2 - MQL" and (2) change their "Stage - MQL Date" to "{{system.dateTime}}."

You can do the same for all of the stages you want to track.

The nuts and bolts of waterfall reporting is being able to report on: 

(1) what stage the person is currently in, aka the field value of "Lifecycle Status" (this makes up most of the graphs I shared above)

(2) what stage the person has been in before, aka which Stage - Date fields are not empty (important for telling if someone skipped straight into SQL or went through MAL to MQL to SAL to SQL)

(3) when the person entered each individual stage through the values in each Stage - Date field.

Those nuts and bolts make up the entirety of all of the graphs above, when adding in the Creation Date of the record itself. Those are the absolute foundations for all of the corresponding reports—all other reports just take those data pieces and intelligently interprets them into charts and graphs.