Prepare your workbook & data

• Open the provided workbook with:

  • MARKETING_CAMPAIGN_ACTIVITY 

  • Campaign Funnel (pre-aggregated funnel metrics)

• Add a second page for layout / “hidden controls” if you like to keep things tidy.

Build your Ragged Hierarchy

• In MARKETING_CAMPAIGN_ACTIVITY Base, create a new column called Path:

  • Path = RaggedHierarchy([Lead Source], [Lead Source Detail], [Parent Campaign], [Campaign])

• Hint: Confirm you can expand/collapse the hierarchy in a grouped table.

Create the Details table

• Create a new visualization from MARKETING_CAMPAIGN_ACTIVITY Base.

• Group rows by hierarchy path elements

• Add measures:

  • Leads = Sum([Leads])

  • MQLs = Sum([Mql])

  • add the remaining metrics you want to track

• Add rate columns:

  • MQL Rate = [MQLs] / NullIf([Leads], 0)

  • SQL Rate = [SQLs] / NullIf([MQLs], 0)

  • Add Opp Rate calc

Create your Control Elements

• Add a Hierarchy control bound to Path; name it CE-Path.

  • Target the Details table (and later the funnel chart).

• Add a Segmented control called CE-StageIndex with options:

  • Value = 1 (hint: name it “MQL Conversion”)

  • add the remaining conversion paths to this control

• Add a Number input called CE-MinThresholdPct:

  • Label: Minimum acceptable conversion (%)

  • Default value: 20

• (Optional) Add a Checkbox called CE-ShowUnhealthy:

  • Label: Show only below target

Add Below-Target Logic

On the Details table, add:

• Focus Rate = Choose([CE-StageIndex], [MQL Rate], [SQL Rate], [Opp Rate])

• Min Threshold = [CE-MinThresholdPct] / 100

• Is Unhealthy? = If(IsNull([Focus Rate]), Null, [Focus Rate] < [Min Threshold])

Then:

• Apply conditional formatting to highlight rows where Is Unhealthy? = true (or directly on Focus Rate).

• Add a KPI viz for Unhealthy Campaigns:

  • Unhealthy Campaigns = CountDistinctIf([Campaign], [Is Unhealthy?] = true)

• (Optional) Filter the Details table:

  • If CE-ShowUnhealthy = true, keep only Is Unhealthy? = true.

Build the Funnel chart

• From Campaign Funnel, create a new visualization.

• Set Chart Type = Funnel.

• Leave Stage empty.

• In Value, add (in order):

  • Sum of Leads

  • Sum of MQLs

  • add the remaining values

• Hint: aggregate the values

• Target this funnel with CE-Path so selecting any branch of the hierarchy filters the funnel.

Layout & polish

• Arrange page elements to your liking:

  • Top left: CE-Path (Hierarchy control)

  • Top right: Funnel chart

  • Middle: CE-StageIndex, CE-MinThresholdPct, optional CE-ShowUnhealthy

  • Bottom: Details table + Unhealthy Campaigns tile

• Add an active title to the funnel, e.g.

  • “Where does this branch leak at the selected step?”

• Hide helper columns (Min Threshold if desired) and format rates as percentages.