The hot topic of the moment on SEOmoz right now is “return on investment” – a term in the scope of this site specifically revolves around measuring the worth of your marketing efforts. ROI is an easy acronym to throw around and sound smart with your boss or clients. Frankly it may even be easy to promise better ROI than you competitors because everyone measures it differently and therefore it will be hard for you to be proven wrong. I am going to try and shed some more light on ROI with reports that will help you improve your ROI.

Luckily for us, ROI in internet marketing is much easier to capture on the web than in traditional marketing arenas like print, TV, radio, and billboard. Estimating cost per impression for an ad on a bus stop bench is hardly going to accurately tell you if you are getting great customer acquisition at a low cost.

As an in-house internet marketing specialist, it is my job to drive traffic to our site. Our site is the largest trafficked site in our corner of the internet, franchise opportunities portals. We make money by generating leads for franchisers. Our success as marketers in this example comes from quality traffic at a low cost, and more of it. The main way we measure this is by calculating cost per lead or CPL. Your site may be cost per acquisition, or cost per visitor, or cost per some action. This is important because your revenue minus your cost per action is going to equal the profits that will be used to pay for rent, employees, products/services, and so on.

To help us through this post, we are going to get organized with our CPL because this creates actionable events for improving ROI. First thing, you will need to make sure you have a system in place to help track your visitor information and their actions. Our database, with the help of Google Analytics, a simple cookie, and URL tracking parameters, tracks the source of our visitors and fires a pixel when they sign up for a lead. We know for each lead – its source, revenue, campaign/ad variation, landing page, and conversion page. Setting up a system like this will be most valuable in tracking your ROI and finding ways to improve it. It will help you to A/B test landing pages, conversion sources, ad copy, and track the performance of each source of traffic. Setting this portion up is outside the scope of this post and my programming experience (limited).

The information should also be exportable to Excel. We will be capturing visitor traffic data from our analytics program to help us determine the yield (percentage of visitors who turn into customers). Lastly we will be capturing cost information from our paid sources (we will just assume organic visitors as free for this project – you can include your SEO labor expenses and paid link building to get more accurate).

I am going to just show this for an example source, Google Adwords, however it can be applied to email marketing, SEO, banner advertising, cost per click, and cost per lead campaigns.

Setting Up Your Reporting

1. Open a new spreadsheet, we will be dumping lots of data into a raw data sheet and using pivot tables to help us look at our data set.

2. In the first row, label your columns with the data you are capturing. We use the term Tracking in column A to help us filter out different sources of data we are dumping into the spreadsheet (cost/clicks/impressions and lead information). In addition we have column headers such as: campaign ID (ex. Google Adwords), Date, Landing Page, Conversion Page, Adwords Campaign, Adwords Adgroup, Adwords Ad, Visits, Daily Forecasted Leads, # of Leads, Impressions, Clicks, Cost, Average Position, Average CPC, Year, Month, Day of Week, Day of the Month. You will likely find with experience what types of information you want to track.

3. Utilize Excel functions to help you automatically match up the date of the lead to its respective year, month, day of week and so on.

a. For year use fx=TEXT(G4889, "yyyy") where G4889 is the date (ex. 2/9/2010)

b. For month use fx=TEXT(G4889, "mmmm")

c. For day of the week use fx=TEXT(G4889, "dddd")

Updating the Report

1. First we will filter the Tracking column to only show us cost/clicks/impression. In a new row you will fill out the obvious information like site and campaign source (later on you will be able to just drag down these rows).

2. Open Adwords and go to the Reports tab. Create a report that shows “Account Performance”, your date range (we use last 7 days) viewed Daily, and make sure it shows impressions, clicks, cost, ave position, CTR. Save it as a template so you can rerun this in the future. You can also schedule the report to run every day automatically.

3. Open the created report and add the data for each day into its own row in Excel.

4. Change the filter to show just lead information. In these rows we will be formatting our lead information data to be posted into our raw data dump sheet. This means adding and deleting columns until your lead data is ready to be pasted into your new raw data sheet.

Pivot Tables Show You the Magic

Pivot tables allow you to look at data in easy to read tables and charts from your raw data sheet. You may want to look up some pivot table tutorials to learn more about them. Once you use them for a while, they become real intuitive and helpful for answering lots of questions about your data and business.

1. Insert a pivot table and make sure that it selects all the data columns in your raw data sheet. (Ex. Table/Range: 'RAW DATA NEW'!$A:$T is correct, Table/Range: 'RAW DATA NEW'!$A$1:$T$250 is wrong and wont capture future data you add to your raw data sheet.

2. In your first pivot table, drag the Month and Year fields into the Report Filter area. Drag Campaign ID and Date into the Row Labels area. Drag the Impressions, Clicks, CTR, Cost, CPC, Revenue into the Values area.

3. Click the PivotTable Tools button at the top of Excel and click the Formula button. Here you can make your additional fields yield, CPL, profit. The formula for yield will be “=Leads/Clicks”. CPL will be “=Costs/Leads”. Profit will be “=Revenue-Cost”. You can add these fields into the Values area.

4. Now you should be able to look at your CPL each day for your different visitor sources. As time goes on you will be able use your pivot table look at organized segments of your data such as just January or just Yahoo. You can also make pretty pivot charts to impress your boss/clients. Also play with Conditional Formatting to apply color scales to your charts to help you identify patterns and trends.

As your project gets larger, you may find it helpful to create different reports for different marketing channels such as email marketing and partnerships. You will also be able to create lots of more interesting pivot tables to look at such things as: yield by landing page, yield by conversion source, and leads per channel.

Conclusion

Setting up a reporting system like the one described below will help you answer important ROI questions: How much am I spending to acquire customers from each source? Is this improving and by how much? What sources should I spend more or less money on? What pages on my site need to be improved first? What parts of my site do the best job of converting customers?

There is no doubt different ways to set up your customer tracking. The key is to make sure that you have a system in place that can quantify the return on the marketing moneys you are spending as this is the first step to systematically improving your site’s value.