Where Marketers Go to Grow

Being able to consistently hit your website's traffic goal and provide the right number of quality leads to your sales team are two of the most crucial responsibilities of a marketing team.

But without the right tools in place, you could have wild variations in the number of visits or leads you actually generate. At the end of a month or quarter, you could end up way ahead or (more likely) way behind your goal.

Using daily traffic and leads waterfall charts is a great way to ensure you never fall far behind the pace you need to stay afloat.

If you are a HubSpot customer, your marketing software has these reports built in. Just scroll down to the bottom of this post for more information on how to get them set up. If you're not a customer, don't worry. I'll show you how to set up each of these waterfall diagrams in Excel.

## How to Create a Traffic Waterfall Chart in Excel

The purpose of the traffic waterfall graph is to compare your actual traffic to your traffic goal on any given day and month. Here's how to set this up in Excel.

### Step 1: Set a traffic goal for the month.

I recommend basing it on past traffic numbers and your SMART marketing goals. Our traffic and leads calculator can also be helpful here.

### Step 2: Calculate a daily traffic goal.

A simple way to do this is to start with a total traffic goal and divide that by the number of days in the month you're measuring. Boom: Your daily leads goal.

If you want to set a more realistic daily goal (which I'd recommend), then you can cater this daily goal to whether each day falls on a weekday or a weekend.

For example, let's say there are 30 days in the month, and 22 of those are weekdays and 8 are weekend days. Let's also say that, in the past, about 85% of a given week's traffic comes during Monday through Friday. Finally, let's set your monthly traffic goal at 8,000 visits.

• Calculate the percentage growth you need to see on a weekday to hit your goal. Multiply your cumulative monthly traffic goal by the percentage of traffic comes on weekdays -- in this case, 85% or 0.85. Then, divide that number by the number of weekdays -- in this case, 22. So: (8000*0.85)/22 = 309 visits per weekday.
• Next, calculate the percentage growth you need to see on a weekend. Multiply your cumulative monthly traffic goal by the percentage of traffic that comes on weekends -- in this case, 15% or 0.15. Then, divide that number by the number of weekend days -- in this case, 8. So: (8000*0.15)/8 = 150 visits per weekend day.

### Step 3: Create the daily traffic waterfall.

Use this graph to plot your progress on a daily basis -- not just at the end of the month or quarter. In addition, share this with your marketing team so everyone can check on the team's progress and assess their own contributions.

If you're using Excel, create a table with the following columns:

• Date: Dedicate a cell to each day in the month.
• Weekday/Weekend: Label each day as a weekday or weekend day.
• Daily Actual Visits: Plug in the number of visits you actually get each day.
• Cumulative Actual Visits: Add that day's actual visit number to the number of visits you've gotten so far that month.
• Daily Goal Visits: At the beginning of the month, plug in your daily visits goal, depending on whether it's a weekday or weekend.
• Cumulative Goal Visits: At the beginning of the month, sum your daily visits goal day over day.

Here's what it might look like:

Each morning, plot your progress by plugging in the actual visit number from the previous day and adding it to the traffic you've earned over the month in the "Cumulative Actual Visits" column.

Next, create a graph from this spreadsheet to create your daily leads waterfall graph. First, hide the "Weekday/Weekend" column by right clicking on the column and choosing "Hide." Then, highlight the rest of your data, including the titles of your rows and columns but excluding the "Total" row that's at the bottom. Click the "Charts" tab at the top and choose "Line" and then "Marked Line."

You should see a graph that roughly resembles this one:

From there, you can add a title, label the lines, and change other visual elements. (And if you want more details on how to make a graph in Excel, this video tutorial is very helpful.)

Pro Tip: If you're finding that your "Cumulative Actual Visits" line is extending beyond the current date (i.e. that line drops to zero for all future dates), then set the proper date range by right-clicking on the white space in your graph and choosing "Select Data." Select "Cumulative Actual Visits" from the Series box, and edit the Y-values to only include the dates up until that day. Note that you will have to manually update this each day.

## How to Build a Daily Leads Waterfall Chart in Excel

This process is very similar to setting up a traffic waterfall. Here's what you need to do.

### Step 1: Decide on the definition of a "lead."

First thing's first: You'll need to work with your sales team to agree on the definition of a "lead," the qualifying factors for a lead to be handed off from marketing to sales, and what leads "count" toward the goal.

To help you get started with this, check out these three resources:

### Step 2: Set a monthly or quarterly goal for the total number of leads.

To learn how to calculate a monthly lead goal in more detail, read this blog post.

### Step 3: Set a daily leads goal.

Just like you did with the traffic waterfall, you should calculate how many leads you need to generate per day to hit your monthly or quarterly leads goals. Use the same process I outlined in the traffic section to find out how many leads you need each day.

### Step 4: Create the daily leads waterfall graph.

Like with the traffic waterfall, use this graph to plot your progress daily, and regularly share it with the team. This is a great graph to share with your sales reps so they have a clear and realistic idea of what to expect from marketing.

If you're using Excel, create a table with the following columns:

• Date: Dedicate a cell to each day in the month.
• Weekday/Weekend: Label each day as a weekday or weekend day.
• Daily Actual Leads: Plug in the number of leads you actually got each day.
• Daily Goal Leads: At the beginning of the month, plug in your daily lead goal, depending on whether it's a weekday or weekend.
• Cumulative Goal Leads: At the beginning of the month, sum your daily lead goal day over day.

Here's what it might look like:

Then, you can graph it in Excel in the same way you created your traffic waterfall graph. You should see a graph that roughly resembles this one:

From there, you can add a title, label the lines, and add other visual elements. (And here's that video tutorial on how to create Excel graphs again, in case you need a refresher.)

## How to Use the Daily Traffic & Leads Waterfall Graphs

Unfortunately, this means you're not on track to hit the traffic or leads goal, and you're at risk of letting the sales team down. What to do?

It’s time to invest in some extra work. Try to come up with additional marketing offers, create more content on your blog, step up your social media engagement, repurpose some content on new channels, and generally increase the promotions you're doing. To stay safe at all times, you may find it helpful to always have backup lead generation content you can publish at a moment's notice.

In addition to pumping up your inbound marketing activities, you can also keep a reserve of cash around to give a boost to some of your paid marketing campaigns or supplement your inbound efforts when you're falling behind the leads goal.

Congratulations: You're ahead of the game! This gives you an opportunity to start planning more for the future.

Maybe you can push some of your planned activity for this month off until next month. For example, maybe you hold off on publishing that blog article right away and instead schedule it to be published a few weeks in the future. In the future, you may not find yourself so ahead of the goal line, so take this time to prepare a backlog of content and promotional components.

## How to Set Up Traffic & Leads Waterfall Graphs in HubSpot

If you're a HubSpot customer, you're in luck: HubSpot has a built-in waterfall chart template that will generated these graphs for you.

To track traffic, you can find the waterfall report right on your portal's Dashboard in the Marketing Performance section. To create custom goals, simply select "Goals" from the graph's dropdown menu, and click "Set Goals" when it appears.

Your HubSpot-generated traffic waterfall chart will look something like this:

To track leads, you can set up an Attribution Report by going to "Reports" and then "Reports Home." Here are specific instructions for how to set up an attribution report in HubSpot. You can also set up automated emails that send this report out to specific contacts (like your sales and marketing teams).

Do you track your traffic and/or lead flow daily? What techniques do you use to make sure you support the sales team properly?

Editor's Note: This post was originally published in March 2012 and has been updated for freshness, accuracy, and comprehensiveness.

Topics:

Subscribe to HubSpot's Marketing Blog