Learn how to make my favorite chart. It tells many stories from one graph and saves you time because you won’t be asked to create as many different reports or graphs with the right crawl charts on your dashboards.

A crawl chart shows some number as it eeks it’s way through the year, month by month. The line grows steadily at some year and you have previous years as a reference point. It tells such a story, whether you are looking at money earned, money spent, contacts added, cases closed, or whatever.

These are my favorite graphs. They answer so many questions about a data set… questions you didn’t even know you had. One graph like this one can serve in place of many reports and graphs. It’s very intuitive. But there are two really big tricks to creating them.

Trick 1: Formula Field Copy of Date

You need two copies of the date in your report. The simplest way to do this is to create a custom formula field on your object that is always equal to the date. So for opportunities, just add Close Date 2 that is always equal to Closed Date. #PrepareObject

Note: Salesforce knows you may want two copies of Close Date in a report so they provide it automatically. It’s called Close Date (2). For most other objects and dates, you’ll have to create the duplicate using the formula field. Here is an example to create a copy of the Created Date on a contact (so you can see a crawl chart of how many contacts get added into Salesforce).

Trick 2: Grouping by Date TWICE

You want a summary report. And you want to group by both of the dates. So to create a crawl chart of opportunities, you would filter for closed won opportunities, add the amount field, and then group by the Close Date. Then go ahead and add the copy, Close Date 2, as a second grouping. Summarize your Amount field with a total.

Now customize the first grouping using the little triangular drop-down menu: set it to group by Calendar Month in Year. This will give you Groups like Jan, Feb, Mar, Apr… Dec. Then customize the second group: set it to group by Calendar Year. And also sort in descending order. This gives you 2016, 2014, 2013, etc. Now you can hide details. #SetupReport

Graph Setup

Now add a Chart. Choose a line graph. Show the Sum of Amount on the Y axis and set up units as you like. Show the Close Date (i.e., Jan, Feb, Mar… Dec) on the X axis. Then add a grouping by the Close Date 2 (i.e., 2016, 2015, …) to show multiple lines. Click the cumulative checkbox so the line shows year to date rather just show the amount per month.

Examples

Closed Opportunity Crawl Chart

Here is a crawl chart example of closed opportunities through mid May of 2016. The current year usually flat-lines since there isn’t yet any closed opportunities for the rest of the year (not always true but usually).

You can see at a glance that we’re way ahead this year. Typically you want to see this year on top of all the other lines. You can see at a glance why — both January and April were fantastic. You can see that sometimes we have a big jump in August. Maybe we should figure out why and see if we can make it happen more frequently! You can see that we have historically ended up in December better each year than the previous year–and that’s what you probably want to see. But you can see how the lines tangle up that it’s not always the case mid-year. You can see that early- and mid-2014 and even 2015 we were running behind by a couple of months. The graph told us that easily and clearly. So we focused early and reacted. The details don’t matter but it’s important that you learn to read the story revealed by the line.

Since we sorted the years in descending order, the most recent year will be on top of the legend. If you don’t want so many years of history, you can filter your report by close date > xxx.

Note that if you don’t have closed opportunities for each month, the month will be skipped. It doesn’t hurt anything but it might be unexpected. Same goes for opportunities in a year. If you didn’t operate for 2013, then it will be skipped.

This is closed opportunities, but you could also have a crawl chart of payments if you were more concerned with that detail, assuming you have multi-payment commitments or grants.

Newly Created Reachable Contacts

Here is a simple way to measure whether you’re doing a good job with Salesforce and networking. It simply measures the number of new people you are adding into Salesforce that are somehow reachable.

It’s a graph of record count of contacts who match the criteria by Created Date. It shows we’ve slowed down in the number of new people are are meeting and capturing into Salesforce. We’re slower than last year and much slower than last year. Dangerous, huh!

New Donation Power Acquisition

Here is a chart that shows new donor power (i.e., what a donor is giving over a year) vs when they started giving. It’s an awesome measure of your new donor cultivation results as they turn to actual gifts. It’s based on using my automatic donor statistics described in another blog.

If you use my donor statistics, you have annual giving power on each account. So you can graph that vs first giving date. This gives you a nifty chart like this one which tells you how well you are capturing new giving power. You could also modify the graph to show record count to see how many donors, instead.

You can quickly see that 2015 was a slow climb. We had to work hard for all those gifts.

Variations

You can create a crawl chart for months by the day number. The day of the month would show across the bottom and the lines would represent different months. This is just a matter of customizing the grouping settings for the date.

You can limit the history shows by filtering on the date to limit the amount of history.

This works on anything that has a date. You can total things or count them.