How To Use Microsoft Excel To Analyze & Improve Your Marketing Campaigns…

If you’re intimidated by crunching and analyzing the numbers on a Microsoft Excel spreadsheet, don’t be. It’s easier than you think…

By just knowing how to input basic math into each excel cell, and understanding the information you need to put in each column, you’ll have a clear picture on how well your marketing is doing, and know what it will take to increase your profits efficiently.

Now the following sample could, with a couple a changes, be applied to telemarketer campaigns, email marketing campaigns, and testing web content pages for conversions.

So let’s take a look at an excel spreadsheet for a completed direct mail campaign. The direct mail campaign is sent to two targeted locations, with it’s tried and true sales letter and two test letters.

Take a look over the spreadsheet below (click on it, then click on it again on the new screen to enlarge). I’ll explain the spreadsheet columns and analysis below.

What does each column mean?

Campaign ID Set – It’s the set of ID numbers you use to track each sales letter you send out. When someone sees your ad they give you the ad’s unique campaign ID number when the contact you. The A, B, and C refer to the type of sales letter used. A is the tried and true sales letter, and B and C are the test sales letters. The first number right after the letter refer to locations 1 and 2.

The four zeros are the digit placements you need to cover the number of sales letters sent out in that ID set, so 4 digits will do since the largest campaign ID set has 8000 letters (so each letter would have a unique ID from A10001 to A18000, and so on for the other ID Sets) You can add more numbers to your ID set categorize it in all sorts of different ways to include each campaigns  in home date, the data house used to get the mailing list, the color of the sales letter, other demographic details, etc (and you can describe each one in different columns on the spreadsheet to make your analysis easier)

Campaign Cost – The total cost of mailing out, setting up, and the whole marketing process up to the sale. The lower the better. Includes all data and labor costs too. You should be able to calculate this for Campaign ID split you do (even if it’s all mailed out as one campaign). In our case, it costs $9000 for the entire 24000 letter campaign, so 2000 letters is 1/12 size of the campaign, so 1/12×9000 (or 9000/12) = $750.

Mailers – The total number of sales letters sent out in each campaign ID set. None of them have to be the same amount, as long as you have a large enough sample size (over 100 is usually enough)

Responses – The total number of responses you received from each campaign ID set. This includes all responses, whether they become sales or not. Generally (but not always), the higher the better.

All Sales – All types of sales you made from the responses of each campaign ID set. The higher the better.  It it includes sales from all types of products/services you sold in this campaign.

Product 1 Sales & Product 2 Sales – If you have more than one product/service you’re selling (or cross-selling) in these campaigns. Make one for each type of product/service to track how many sales came from each campaign ID set. If you have just one product then you don’t need this column. It would just go under your total sales.

Response Rate – You get this by dividing the response column by the number of mailers column. You do so by putting the formula in the first box you want to calculate, then drag it down the rest of the column (learn more about this here)  Then format the column to turn it into a percentage.

All Sales Rate – Similar to response rate, but now you divide the total sales column by the number of mailers column instead

Product 1 & Product 2 Rate – Just divide the product 1 and product 2 sales rate by the number mailers respectively, like before.

Total Revenue – How much money you made off the sales of each campaign ID set. The higher the better. You get this by multiplying the sale price of each product by their respective number of sales for each campaign ID set, and then adding them together. In this case product 1 sells for $80 while product 2 sells for $120.  So if you sell 5 of product 1 and 4 of product 2, then your total revenue would be 5x$80 + 4x$120 = $880.00

Cost/Dollar/Sale – AKA the cost per dollar per sale. It calculates how much it actually costs you to get each one sale from that campaign. The lower the better, and one of the most important measures of success in your campaign. You get this by dividing the campaign cost column by the All sales column.

Data Analysis

So what do the numbers in our example mean? Which campaign did best?

Well it seems like test letter B at location 2 did the best.

Why? Well the response rate is the highest, the sales rate is one of the top 2, its rate for product 2 sales is the highest, which is more profitable for you because it’s $40 more revenue for you for each sale than product 1(assume it costs the same to produce product 1 or 2). The total revenue is the highest at it’s sample size, and predicted to stay at that rate if you place 8000 letters instead of 2000.

But most importantly, the cost per dollar per sale is the lowest across the board. It means that it will cost you only $12.10 to get one more sale from that location and using letter B.

And as you can figure out using the same analysis as above, test letter B is better in sales and profit than the other 2, and it seems that location 2 is somewhat better for you than location 1.

You can start drawing all sorts of trends and conclusions from the data above to improve your campaigns, but with the simple analysis I showed above, you figured out test letter B and location 2 is more likely to pull more sales in the more profitable manner. So you can focus more mailers with that criterion to boost sales.

Follow this format and you’ll be on a great start to improving your marketing campaigns significantly, with tons of profit along the way.

You might even Excel at it 🙂

Advertisements