All content marketers know that to create content that converts, we have to know what has worked in the past and what hasn’t.

But the way most content marketers use that knowledge to plan is still pretty broad-stroke. We scan a few data sets and think, “That post did really well. Let’s do another one like it and cross our fingers.”

What if we could predict the performance of our future content more precisely? What if we could forecast if articles from last Wednesday’s brainstorm are worth writing – and how well they’ll perform?

I think we can.

I propose that with a few key metrics and some simple (enough) math in Microsoft Excel, we can predict the success of a given content idea.

Intrigued? Let’s get started.

Part 1 – Gather the Data

Step 1 – Identify relevant data

There are about a million ways to slice and dice historical data, so it can be hard to identify which stats are relevant and which stats aren’t. There are two main types of data I recommend gathering for ROI Math: content dimensions and success metrics.

Content Dimensions

To be able to observe useful trends over time, you can’t look at just one piece of content. You have to group your content into meaningful categories.

The best way to group your past content is by the variables that you experiment with during the content planning stage. Think about the decisions you usually make when planning content – what the content should be about, how long it should be, who should write it, etc. I call these variables “content dimensions.”

For your first attempt at ROI Math, I recommend sticking to these four content dimensions:

Word Count Range (i.e. Length)

Word counts vary wildly per piece of content, so you’ll need to translate these into ranges to gauge performance more easily and give your writers more practical targets to hit.

Author

If you have multiple in-house writers, manage a large pool of freelancers, or frequently publish work by guest writers, author performance will be an important metric to track.

Content Format

Common format types include reviews, infographics, videos, slideshows, long-form articles, and numbered lists.

Topic

Common topic types include the vertical, the category, or the service being discussed.

Success Metrics

To measure success, you have to define and quantify it. What do you want above every other goal? When you get what you want, how will you know?

The answer to the first question is up to you. The answer to the second question is success metrics, which you and I are about to define.

For your first time doing ROI Math, I recommend defining success using three main types of measurements: traffic metrics, e-commerce metrics, and any proprietary metrics that are unique to your business.

Traffic Metrics

Traffic metrics are an excellent indicator of success because they represent the customers who are already engaging in your conversion process. Here are a few examples of traffic metrics:

Unique Page Views (UPVs)
Clicks per Page
Events per Session
Goal Completions

I recommend analyzing just the UPVs for your first attempt at predicting ROI, since it represents your site’s overall success rather than the success of specific campaigns.

If you do select multiple traffic metrics, make sure they’re consistent with each other or your predictions will be muddled.

E-commerce Metrics

I recommend looking at net revenue if possible for your first run at ROI Math since revenue is a very broad indicator and like UPVs, will give you a good idea of your content’s overall success. As you gain more experience, you can add more e-commerce metrics like these:

Average Order Value
Number of Items Sold
Lifetime Value

Proprietary Metrics

To get a more customized view of how your content is doing, think about your main goal again and the metrics you already monitor to gauge your progress toward that goal. Then, single out the one or two metrics that best determine success.

At CLEARLINK, we use answered phone calls as one of our core proprietary success metrics because it is the strongest indicator of a marketing campaign’s success and correlates heavily with revenue.

If your main goal is to become a national brand, you may measure success by how many cities you’re shipping to. If your main goal is to serve more homeless in the community, your key proprietary metric may be the number of clients served. Even if your goal is only to drive your net worth as high as possible, you’ll need a metric more specific than cold, hard cash to be strategic in your content planning.

Time Frames

When you collect these metrics from your reports you’ll want to choose a period that will represent your content well, but doesn’t provide so much data that it overwhelms you. I recommend one of the following time frames: year-to-date, the most recent 6–12 months, or a customized period that you’re particularly curious about (e.g., last holiday season, first 90 days after new hires, etc.).

Step 2 – Set up your spreadsheet

Now it’s time to set up your Excel sheet, where you’ll gather your historical data.

The fastest, easiest way to start your spreadsheet is by using a program like Xenu or Screaming Frog to run a crawl of your site. Once the crawl is complete, export the results and save them as an XLSX file.

The site crawl automatically gathers every single web address on your domain, which you need for Excel to be able to properly pull data from your analytics reports.

However, the site crawl also gathers data you don’t need, so you’ll have to clean up the sheet before pulling in any analytics.

Here’s how:

Delete Extraneous Information

Delete any rows containing URLs you don’t want to analyze for this audit. For example, the unique URLs of generic stock photos found throughout your site may not be worth analyzing.
Delete all columns but two: the URI and the word count. If you really want to, you can reevaluate the usefulness of the other columns once you’ve got the hang of ROI Math. For now, you’ll have more than enough relevant info without them.

Add New Columns For Relevant Information

Create a new column for URLs. The easiest way to do this is to duplicate the URL column and then use Excel’s Find and Replace feature to delete the domain name from each URL (e.g., http://ift.tt/1MIDFvB would become /blog).
Then create a new column for each of the four basic content dimensions:

Word Count Range
Author
Format
Topic

And finally, create a new column for each metric you selected. To review, these are the metrics I recommend adding:

UPVs
Revenue
Proprietary metrics (e.g., answered phone calls)

Once you’re done creating these new columns, your spreadsheet will look something like this:

Step 3 – Fill in the data

Now that you’ve set up your spreadsheet, you’re going to use a combination of brain power, Google Analytics, and Excel’s VLOOKUP function to fill in the data.

Fill in the content dimensions by hand

Fill in the author, topic, format, and word count for each URL. Unless you know your content in your sleep, this step will likely require that you visit each page.

Get your music on – this is probably the most monotonous step in content marketing ROI Math. But I really encourage you to stick it out. The sheer volume of data that Excel interprets automatically, not to mention the precision of your results, will make this task worth the extra time and effort. I promise.

Fill in the word count ranges first. Use whatever word count ranges you normally use to assign content to your writers (e.g., 500–750 words, 750–1,000 words, etc.).
Fill in the author for each piece of content.
Fill in the format of each piece of content. I recommend selecting from as few formats as possible to keep the results manageable.
Fill in the topic for each piece of content. Again, I recommend sticking to as few as possible.

Fill in success metrics using analytics data and Excel’s VLOOKUP function

Fill the remaining cells in your spreadsheet by pulling data sets from your reporting accounts. I recommend starting with Google Analytics.

Open Google Analytics and navigate to the All Pages report under Behavior/Site Content.
Choose the time period(s) you’d like to analyze and filter out all content from before and after.
Display all columns to make sure you don’t miss any key metrics, and then download the data into an XLSX file. (This will not be the same file you are using to calculate ROI Math. You will have these documents open side-by-side.)
Return to your original Excel file. For each success metric column, use the VLOOKUP function to populate the cells with the corresponding data from your exported Google Analytics spreadsheet.
Repeat steps two through five for any other reporting accounts. As long as your reports include a URL or URI in the export, you’ll be able to integrate the data into your audit.

If you don’t dive into analytics on a regular basis or aren’t familiar with Excel, I recommend tutoring yourself in the VLOOKUP function until you get the hang of it. It’s a fast and extremely reliable tool for cross-referencing.

Part 2 – Do the Math

At this point, you’ve gathered all the data you need. Now you’re ready to crunch the numbers.

Step 4 – Create a PivotTable

To predict future conversion, you’ll need to compare every single content dimension against every success metric and quantify how well each content type has performed in the past. Excel’s sophisticated PivotTable feature can do these comparisons quickly and easily.

Here’s how to create your PivotTable:

Select all the data on your spreadsheet by typing Ctrl + A or clicking the triangle in the top left corner of your spreadsheet.
Head over to Excel’s Insert tab and click on PivotTable. It should be one of the very first icons on this tab.
When the PivotTable dialog appears, select the option to create the PivotTable on a new sheet. It will appear as Sheet2 in your Excel doc.

The PivotTable is another Excel feature that takes practice to understand. If you’ve never used it before, either tinker patiently until you get the hang of it or study some tutorials first.

Step 5 – Use the PivotTable to find averages and variances

Your goal with these Pivots is to calculate two new metrics – the average performance and variance to average of all content dimensions. These two meta-metrics hold the key to predicting how well similar content will perform in the future.

To systematically calculate the averages and variances, complete the following steps for each content dimension and success metric.

Here I’ve used author and UPV as examples:

Drag and drop the author field into the ROWS field of the PivotTable.
Drag and drop UPV into the VALUES field of the PivotTable. Use the dropdown menu to the right to specify that you want the PivotTable to return the Average (not the Sum, Count, or anything else) of the UPVs for each author.
Drag and drop UPV into the VALUES field a second time. Use the dropdown menu to specify that you want the PivotTable to return the average variance of the UPVs for each author.

Each Pivot should look like the below screenshot, with the content dimensions appearing in the ROWS field and the averages and variances of each success metric appearing in the VALUES field.

As you complete each pivot, copy and paste the data to a third sheet formatted with the columns below. Complete all the pivots until you have recorded the averages and variances of each success metric.

Dimension Type – Shows which dimension type is being analyzed (e.g., format).

Dimension – Shows which specific dimension is being analyzed (e.g., long-form articles).

Average UPV – Shows the average number of UPVs within the time period you chose for that specific dimension (e.g., long-form articles have an average UPV of 12,000).

UPV Variance – Shows how much that dimension differs from the average across your site.

A column for the averages and variances of each remaining metric

This tab should look similar to the below screenshot:

Part 3 – Plan Winning Content

If you’ve gotten this far in the process, congratulations! You have completed an impressive feat of auditing, pivoting, and formula-writing – and now comes the fun part.

Step 6 – Set the stage for the brainstorm

Everything to this point has been about analyzing past performance. Now it’s time to look forward.

Start a fourth tab to house your ideas for future content. You’ll want to start off with a column for content ideas, followed by columns for author, topic, format, word count range, and average variance of each of these dimensions.

Populate each variance column by using a VLOOKUP function to pull the corresponding values from your PivotTable results. To check if the function is working correctly, type one of your authors into the appropriate column and check your third sheet to verify that the VLOOKUP has returned the same average variance you see in your pivot results.

Step 7 – Brainstorm

As you brainstorm new content ideas, add them to your fourth sheet and propose an author, word count range, topic, and format for each one. You can either do this yourself or have the individual idea contributors add them in.

As you add these details to each row, the VLOOKUP will automatically populate the variance columns and you’ll slowly see trends emerging. It’s like magic, except nerdier.

Now get ready for your hard work to really pay off.

Step 8 – Calculate the likelihood of conversion

Create one last column and label it “Result.” This column is your golden number. This column is the reason ROI Math exists.

To find the values for this column, use the =AVERAGE formula to average all the variances across each row and then subtract 1 from that average.

Here’s what the golden number should tell you:

Based on the historical performance of the proposed author, format type, topic, and word count, the content idea “ROI Math” should convert 8.75 percent better than the average piece of content on JeffBullas.com.*

*Disclaimer: This example is purely aspirational. I have no historical data to back it up.

There you have it. From now on, you can confidently predict the likelihood of conversion for every content idea you have.

Step 9 – Shape your content strategy

ROI calculations are very flexible. If at any point while brainstorming you get a low or negative percentage, you can immediately tweak the content dimensions (e.g., increase the word count, change the format from slideshow to long-form, etc.) to try and improve the chances of success.

Once you’re comfortable with ROI Math and you’re feeling adventurous, you can even go back to the beginning and add additional success metrics (e.g., promotional metrics like links, shares, etc.) and content dimensions (e.g., time of publication, day of the week, etc.). You can even compound the dimensions if you like – for example, you can find the word count range that works best by format vs. the word count range that works best across the board.

The more layers you add to your calculations, the more precise the predictions become. Keep at it until you get a model that works for you and your site.

ROI Math as a jumping-off point

Content marketing ROI Math has helped me sharpen my content planning process dramatically:

I can now hand-pick strategic publication dates based on the probability of performance.
I can now assign content pieces to the authors that perform best in each category.
My brainstorming is more efficient because I already know which formats and topics perform well.
I manage risks better because I can estimate how badly something might flop and how much it will cost me.

But let’s get real for a moment.

Ideas are surprisingly fickle. Some of our best ideas (according to us) end up producing mediocre results, while run-of-the-mill ideas can be off-the-charts successful. Some of the most successful pieces we’ve published at CLEARLINK started out as high-risk ideas.

I encourage you to use ROI Math for inspiration. Use it as a sanity check, but when decision time comes around, go with your gut, your common sense, or some combination of both. No spreadsheet can replace human intuition.

Treat ROI Math like the living, breathing process that it is. Take chances, make mistakes, get messy, and then learn from it.

Let’s improve ROI Math together. If you have any ideas on alternate use cases or on how ROI Math can be improved, please hit me up on Twitter @daveydemille.

Guest Author: With over 6 years of digital marketing experience, David DeMille has brought a real passion for marketing to his current position as marketing director at CLEARLINK. He focuses on creating data driven strategy, consumer driven content, and a community worth engaging in.

The post A New Formula For Content Marketing That Converts [Step-by-Step] appeared first on Jeffbullas's Blog.