Leveraging Tracked Content in RO to make Monthly Recruitment Metrics Easy, Part 3 of 3

In part one of this series, I mentioned how I go about putting together a recruitment report for my clients using RO Innovation (formerly ReferenceView) that is both easy to put together, and easy to understand. In part two of the series, I began walking you through the process on how to put it all together.

In this, the last in the series, we’ll finish up and provide you with some great insights into how your program is faring!

Under column D, filter on “Blanks.” Once you have them, highlight all rows and columns with data (A->J and 1->whatever you have) and hit Control+C. Then, hit Control+N to open a new excel sheet and hit enter, which should paste all of the data you copied. While it’s highlighted, we’re going to create a pivot table! For more information on Pivot Tables, please refer to this blog from our own Saravanan Sakthivel. Under the “Insert” tab in Excel, click on “PivotTable”. A small window will pop up with “new worksheet” selected. Click on the “existing worksheet” option and click on a cell outside of your data (Column M, Row 3 for example) and then hit “OK”.

This will now offer you numerous PivotTable Fields and several drag fields in which to place them. For this first quarter-less report, we’ll only need to use two. First, grab “Tracking Step” and drag it into “Rows”. Second, grab “Tracked Asset” and place it into “Values.” You’ll end up with a table that looks something like this:

Our clients like to play with the appearance a bit, and for the sake of the excel magic we perform in the next step, you’re going to want to copy / paste the above lines into the following order:

1. Top: Outreach with Account Own

2. Outreach with customer contact

3. Confirmed Referenceable

4. Declined

5. Dropped pursuit

Now that you’ve done that, set up your next table with the label “Status” above your first row, and Total above your first number and to the left of your bottom number, so it appears thusly:

I like to add a thick outsider border and color in the boxes, above, but it’s up to you on how to present, aesthetically.

Now to down to the Nitty Gritty! How are we tabulating our success rate[RQ1] ? Success rate is defined as the total number of recruited advocates divided by the total number of recruitment efforts that closed (both confirmed referenceable and confirmed not referenceable) - expressed as a percentage.

Now that we’ve done that, insert it two boxes below your final number, and you should receive a response like this one:

As was the case with the above table, I think outlining it in a thick border looks nice. But, like the above, it’s completely up to you on how to present your data!
Quick note: A 50% success rate extremely impressive. Don’t be discouraged if your number is not that high, 25% is average!

Moving on, let’s go back to our report and grab all the data that has dates associated with them.

Under column D, filter on everything except “Blanks.” Once you do, highlight all rows and columns with data (A->J and 1->whatever you have) and hit Control+C. Then, hit Control+N to open a new excel sheet and hit enter, which should paste all of the data you copied. While it’s highlighted, we’re going to create another pivot table! Under the “Insert” tab in Excel, click on “PivotTable”. A small window will pop up with “new worksheet” selected. Click on the “existing worksheet” option and click on a cell outside of your data (Column M, Row 3 for example) and then hit “OK”.

As with the above, numerous PivotTable Fields are available and several drag fields in which to place them. For this first quarter-oriented report, we’ll need to use three. First, grab “Tracking Step” and drag it into “Rows”. Second, grab “Tracked Asset” and place it into “Values.” Lastly, grab “Quarters” and place it in the “Columns” tab. You’ll end up with a table that looks something like this:

As we did above, it’s time to clean up your data orientation. For the sake of the excel magic we perform, you’re going to want to copy / paste the above lines into the following order:

1. Top: Outreach with Account Owner

2. Outreach with customer contact

3. Confirmed Referenceable

4. Declined

5. Dropped pursuit

However, in this example, you’re going to want to also present your quarterly data in linear fashion! Once you have that organized, perform the function “Autosum” down all of your rows. Once done, it should appear like this:

And once again to the Nitty Gritty! The Success Rate equation we used in the first excel sheet is the same that we’ll be using, here.

As above, paste it two boxes below your final number. Your data should look something like this:

And there you have it!

It’s important to begin taking the steps early on in your RO Innovation / Upland Software journey to ensure this data is organized in such a way that makes tables and reports like those presented here possible, but the end result and diligence are well worth it. Especially so for those, like me, who are focused on data, the best ways to report it, and maintaining the health of an advocacy program and reference database.

We specialize in this here at Referential and are happy to help guide you!

Please leave a comment below and let me know your thoughts and / or best practices!

---