Use your raw data in Excel to visualize your marketing funnel and associated metrics.
1. Start an Excel spreadsheet with your data that you use to define your funnel.
For this example, we will use the simple AIDA funnel: Attention, Interest, Desire, Action. Each lead has a field that indicates which stage it is currently in. Your data will be unique to you.
2. In a new Excel tab, create a table with the rows being the funnel stages and the columns being the metrics.
In this example, the metrics I am using are:
Number of People: Sum of the people who have ever been in the stage.
Stage Flow: The percent of people who have moved from the prior stage into this stage.
Overall Funnel Conversion: The percent of all leads who entered the funnel who have made it to that stage.
3. Define your formulas to calculate the funnel metrics.
You can use a SUMIF formula or COUNTIF formula to calculate data for each stage.
4. Insert a SmartArt graphic of an inverted pyramid.
Format and customize to match your marketing funnel.
5. Convert your SmartArt graphic to a SmartShape.
Click this button in the SmartArt Design menu.
This will allow you to use a formula as the text label for the shape in order to make the graphic dynamic with your data.
6. Click on the Text Box of the stage and go to the formula bar to enter the formula you want as the label.
In this example, I created a cell called Chart Label that concatenated the stage name, the # of People, and the Stage Full metrics.
If you want to add a carriage return in your label, you would enter Char(10) in the formula.
For example: the formula for the first stage of the example funnel
=B4 & CHAR(10) & C4 & "-" & TEXT(D4,"0.0%).