Showing posts with label internet marketing. Show all posts

Friday, January 14, 2011

Using Excel Solver To Optimize Your Marketing Budget

Using the Excel Solver To

Optimize Your Marketing

Budget

The Excel Solver allows you to calculate how to allocate your resources to get an optimal result. It is such a useful and versatile tool that almost any manager can put it use effectively but not many are familiar with how to use it.

This article will provide a simple example of how an Internet marketing manager can use the Excel Solver to optimize the allocation of marketing budget funds among 4 pay-per-click networks to achieve the highest number of conversions (sales).

Solver Problem Summary

In summary, here’s what we are trying to do:

1) Maximize the total number of conversions from all 4 pay-per-click vendors.

2) Not spend more than $500 on each individual pay-per-click provider

3) Not spend more than $1,500 total

We are also provided with the average cost per click and average conversion rate (percentage of clicks that convert to sales) for each network. The information is placed on a spreadsheet as follows:

Click On the Image To See a Larger Version

Our Solver Optimization Task

We have a total monthly paid search budget of $1,500 that must be allocated between the 4 following paid search providers: Google AdWords Search Network, Google AdWords Content, Facebook paid search, and LinkedIn paid search. We want to keep our pay-per-click efforts diversified; we will spend no more than $500 per month any single pay-per-click providers, but no more than $1500 total.

We know our average cost-per-click and conversion rate for each of the 4 pay-per-click vendors. Conversion rate is the percentage of clicks that convert (buy and become customers).

Running the Problem on the Excel Solver

Once the Solver has been installed as an add-in (To add-in Solver: File / Options / Add-Ins / Manage / Excel Add-Ins / Go / Solver Add-In), you can access the Solver in Excel 2010 by: Data / Solver.

The following blank Solver dialogue box appears:

Click on Image To See Larger Version

The Solver dialogue box has the following 5 parameters that need to be set:

1) The Objective Cell – The is the target cell that we are either trying to maximize, minimize, or achieve a certain value.

2) Whether we want to minimize or maximize the target, or attempt to achieve a certain value in the Objective cell.

3) Decision Variables – A set of variables that will be changed by the Excel Solver in order to optimize the target cell.

4) Constraints – These are the limitations that the problem subjects the Solver to during its calculations

5) Solving Method to be used.
Here once again is the Excel spreadsheet input data that will be used:

Click On Image To See Larger Version

Objective:

Maximize the total number of conversions from all 4 pay-per-click vendors (green cell H10)

Decision Variables:

By varying the budget amounts allocated to each of the 4 pay-per-click networks (tan cells – C6 to F6)

Objective and Decision Variables Selected in Solver

Click on Image To See Larger Version

Choice of Solver Method

Click on Image To See Larger Version

a1x1 + a2x2 + … + anxn = objective

We can tell that we are dealing entirely with linear equations in this example because all equations which used to determine the objective (the final number of conversions) are first-order. These basic equations are as follows:

Number of Clicks = (Average Cost Per Click) * (Amount Spent)

Number of Conversions = (Average Conversion Rate) * (Number of Clicks)

The GRG Nonlinear method is used when the equation producing the objective is not linear but is smooth (continuous). Examples of smooth nonlinear functions are:

=1/C1, =Log(C1), and =C1^2

These functions have graphs that are curved (nonlinear), but have no breaks (smooth)

The Evolutionary method is used for non-smooth functions. These are functions whose graph is discontinuous at any point.

The use of the GRG Nonlinear and Evolutionary methods would require much more discussion than time permits in this article. We will therefore work exclusively with the Simplex LP method here.

Constraints

Constraints must be added separately. To add a new constraint, just click the Add button and select the cell that will have the constraints applied to it. You then select the type of constraint, for example, the constrained cell must be less than a certain number. Finally you specify the constraining value, for example, the number that the constrained variable must be less than.

When setting constraints, it is important to remember which variables cannot take a negative number and then specify this as a constraint. In this case, the Amount Spent for any of the pay-per-click networks cannot take a negative value.

The constraint highlighted in blue ( $C$4:$F$4 Greater Than or Equal To 0) illustrates this type of constraint in this example:

Click On Image To See Larger Version

Close-Up of Constraints Input Into Solver

Click On Image To See Larger Version

Solve

You are now ready to hit the Solve button and get the final result. Solver changes the Decision Variables to optimize the Objective Cell. When Solver changes the Decision Variables, all other variables which depend upon these Decision Variables will be recalculated. By definition, the Objective Cell must be a variable that is derived from the Decision Variables. The end result of running the Solver are as follows:

Click On Image To See Larger Version

Final, Optimized Result

We can see that the Excel Solver calculated the maximum possible number of conversions to be 392. Solver did this by setting the Decision Variables as follows:

Amount Spent – Google AdWords Search: $500

Amount Spent – Google AdWords Content: $500

Amount Spent – Facebook Paid Ads: $300

Amount Spent – LinkedIn Paid Ads: $500

Constraints Satisfied

The constraints on the upper limits of each Amount Spent, Total Amount Spent, and non-negative constraint for each Amount Spent have all been satisfied.

Available Reports

There are 3 available reports that can be attached to the Solver’s result. These 3 reports are Answer, Sensitivity, and Limits as seen in the Solver Completion dialogue box below:

Click On Image To See Larger Version

We will briefly evaluate only a part of 1 of those reports in this article: the Sensitivity Report.

The Sensitivity Report

Part of the Sensitivity Report is shown below:

Click On Image To See Larger Version

The Objective Function column shows the coefficients for each Amount Spent to produce the Total Number of Conversions. Since this is a first-order equation, the result looks like this:

0.2 * (Amount Spent on Google AdWords Search) +

0.2857143 * (Amount Spent on Google AdWords Content) +

0.1066667 * (Amount Spent on Facebook Paid Ads) +

0.2333333 * (Amount Spent on LinkedIn Paid Ads) =

= Total Number of Conversions

We can see from the Objective Coefficients that an amount spent on the Google AdWords Content Network would have the greatest effect on the Total Number of Conversions.

Summary

The Excel Solver at its basic level is a simple tool to use to determine the optimal allocation of your available resources.

If you would like to create a link to this blog article, here is the link to copy for your convenience:
Using Solver To Optimize Your Marketing Budget

Excel Model Building - Experts vs. Non-Experts

Please post any comments you have on this article. Your opinion is highly valued!

If You Like This, Then Share It...

Excel Master Series Blog Directory

Statistical Topics and Articles In Each Topic

Thursday, June 17, 2010

How To Analyze Your Twitter Follower Program With the Excel Histogram

How To Twitter Better

With a Histogram in Excel

How’s your Twitter Follower acquisition program going? If you use Twitter as a prospecting tool, you need a convenient system in place to monitor how well your Twitter account(s) are picking up followers.

The Histogram in Excel is a tool custom-made for that job. All you have to do is record the total number of new followers that you pick up every day in Excel and then run a Histogram on that data once per month. You can visually compare Histograms from month to month to know immediately which direction your Twitter Follower Acquisition program is going.

What Is a Histogram?

A Histogram divides a large group of dissimilar objects into many smaller groups of similar objects. Excel allows you to arrange the smaller groups in these two ways:

1) By Size of Objects in Group - You can also arrange the groups by the size of the objects in each group. The group on the left will contain objects of the smallest size. Groups to the right contain objects of progressively larger size. This is the type of group arrangement that will be used to monitor your Twitter Follower Acquisition program:

histograms, histogram in excel, pareto chart, twitter, follow twitter, twitter users, twitter seo, social oomph, tweet adder

Click On Image To See Enlarged View

2) By Number of Objects in Group - You can arrange the groups into a Pareto chart. In this case, the groups are arranged by the number of objects in each group. The group with the largest number of objects appears on the left side of the Pareto chart. Groups to the right get progressively smaller:

The embedded video below will provide step-by-step instructions on how to use the Histogram in Excel to monitor your Twitter Follower Acquisition program. Here is the video:

Here is a Step-By-Step Video Showing How to Determine If Your Twitter Follower Acquisition Program Is Getting Better Or Worse By Using An Excel Histogram:

(Is Your Sound Turned On?)

you pick on all of your Twitter accounts every day. It is most convenient to record these daily figures on an Excel spreadsheet as follows:

. The Histogram will divide the overall group of daily follower pickup numbers into many smaller groups that have similar numbers of daily followers picked. The Excel Histogram will produce a number of smaller groups with containing objects (days) of similar size (number of followers picked up in a day). The groups are then arranged on a chart. The groups can be arranged by the size of the objects in the groups or by the number of objects in the groups (a Pareto chart). In this case, the groups will be arranged by the size of objects in the groups. Here is an example of such a Histogram:

each month. Here is an example of a monthly comparison:

You can clearly see that April’s Twitter Follower Acquisition program did significantly better than May’s. Something must have happened between April and May which hurt the Follower Acquisition program. The Twitter account owner needs to figure out what happened that caused the decline. The Excel Histogram makes this comparison quick, easy, and accurate.

Some Tips About Using Twitter for Customer Acquisition

I use Twitter quite a lot for prospecting. I currently have 9 Twitter accounts and, at the time of this writing, acquire about 300 new followers total per day. That’s not so many but I keep it conservative to avoid attracting Twitter’s attention. Here’s what has worked for me so far:

. After a Twitter account has acquired 2,000 followers, the 10% Rule applies. The number of people you follow can’t exceed the number of people who follow you by 10%. In other words, if one of your Twitter accounts has reached 2,000 followers, don’t allow the Follows sent out plus the FollowBacks to exceed 200, until more people begin to follow you or you unfollow a few people. It is best to try to keep your tff (Twitter Following / Follower) ratio to be just a tiny bit under 1.10. I use TweetAdder to automatically keep my tff ratio 1.09 or lower for my Twitter accounts that have more than 2,000 followers.

Don’t unfollow anyone for at least one whole day after you begin following them. Twitter views this as churn. That’s a label you don’t want from Twitter. You’d be better off if you never unfollow anyone for at least 2 days, preferably 3. TweetAdder handles this for me pretty easily. The attached TweetAdder video shows how this is done.

If you need to set up more than 10 Twitter accounts, Google “proxy IP address” or “proxy IP server” to find out how. This limit is why I have only 9 Twitter accounts. I’ve heard of people with many more accounts, but it’s risky unless you really know what you’re doing.

. If you have set up multiple Twitter accounts based on derivatives of the same gmail account (joejohnson@gmail.com, joe.johnson@gmail.com, and j.oejohnson@gmail all forward to the same email account but can be used to set up separate Twitter accounts), be aware that if any of those accounts are suspended, all of the accounts will be suspended.

If you are using a tweet automation tool, it is best to tweet no more often than hourly and limit to 10 per day total per account. I use SocialOomph to automate my Tweets at regular hourly intervals during certain times of the day.

If you have multiple Twitter accounts and use an automation tool to Tweet at regular intervals, makes sure that no accounts are tweeting the same thing at the same time. In the past, I’ve had a few Twitter accounts suspended for this reason. I’m much more careful now when I’m setting up the timing, intervals, tweet content, and tweet rotation on SocialOomph. All accounts tweet at different times and with different tweets. The attached SocialOomph video shows how this works.

If you are using Twitter for prospecting, consider automating at least some parts of your Twitter interaction. If you are trying to achieve large numbers of Twitter followers, automation is almost mandatory. I’ve had good luck with the following 3 tools: TweetAdder, SocialOomph, and bit.ly. Here is a little description of each and how I use it, along with a video so you can take a look at how I use these tools.

TweetAdder – TweetAdder automates the following tasks for me:

1) Automates Follows sent and Follow-Backs for each account.

2) Automatically keeps my tff ratio at 1.09 for all of my accounts with more than 2,000 follows.

3) Automatically unfollows anyone who doesn’t follow me back within a time limit that I specify. I usually keep that time limit at 3 days for all accounts.

4) Provides a “safelist” of accounts I’m following that won’t be unfollowed.

5) Creates a “who to follow” list for each account based on any of the following criteria that I specify: Tweet keywords, Profile data, Location, Followers of another user, and Users followed by another user.

6) Allows for completely different settings on each of my Twitter accounts.

7) Can be used to automate tweets but I find that SocialOomph is the best tool for that.

8) Can be used to automatically send replies and direct messages, but I avoid doing this because can come across as “spammy” to recipients.

9) It’s quite easy to use and very intuitive. I probably spend 15 minutes total daily on all 9 of my Twitter account making little adjustments here and there, and then, with the push of 2 buttons, all automated tasks of all accounts run all the way to completion.

Here’s a video to show you how I use TweetAdder to manage Follows and Unfollows for all of my accounts:

Here is a Step-By-Step Video Showing How to Use TweetAdder To Add 300 New Followers to Your Twitter Program Every Day:

(Is Your Sound Turned On?)

If you are interested in TweetAdder, you can click here to check it out. It has a free trial. If you find it useful, there are 4 pay versions that can be purchased with a one-time fee as follows at the time of this writing: Managing 1 Twitter profile - $55, managing 5 Twitter profiles - $74, managing 10 profiles $110, and managing unlimited profiles - $187. I’ve purchased the unlimited version, but I’m only using it to manage 9 Twitter profiles at the moment.

I’ve also heard that another similar tool called Hummingbird does a great job at managing Follows and Unfollows, but I don’t have experience with it so I can’t provide any insight into it. I can say that TweetAdder has done a very fine job for me so far and I’m now averaging about 300 new followers a day with not much effort on my part.

– SocialOomph works great to automate Tweets for all of my accounts. SocialOomph has so many uses that I probably haven’t tapped into even 30% of its functionality, but here’s what it does for me:

1) Schedules tweets for any Twitter account that will continuously repeat themselves at any interval 24 hours or greater that I specify. This makes SocialOomph a real Set-And-Forget tool, if you want that.

2) Creates a series of Tweets that I specify which will be sent out on a rotating basis at the same time every day (or at any interval 24 hours or greater). I use this function to prevent any of my Twitter accounts from sending out the same Tweet at the same time every day.

3) Auto-follows anyone who follows me.

4) Has a very good URL shortening service, but I’ve using bit.ly for a long time and have had good results with bit.ly so I’ll stick with bit.ly.

Here is a short video showing how I use SocialOomph set up a rotating series of Tweets that will be sent out by one of my Twitter accounts at the same time everyday:

Here is a Step-By-Step Video Showing How to Use SocialOomph To Automate Your Twitter Tweets and Get More Followers:

(Is Your Sound Turned On?)

If you are interested in using SocialOomph, click here to try a free version. The free version, however, does not provide the service of recurring tweets, which is the main reason that I like and use SocialOomph. Maybe I’m just lazy but I just love “Set and Forget.” You can easily upgrade from the free version to the pay version.

The pay version of SociaOomph costs $29.97 per month at the time of this writing. I’ve been using the pay version of SocialOomph exclusively to automate recurring and rotating Tweets for all of my Twitter accounts and it’s worked very well so far. Here is a link to SocialOomph if you are interested.

bit.ly – bit.ly is not only great for creating very short URLs but it also provides excellent analytics. I use Google Analytics tracking data to monitor anything happening on my site, but offsite, I use bit.ly to track clicks. I exclusively use bit.ly to track offsite clicks so all of the click-through information is maintained in one place.

To Sum It All Up:

The Excel Histogram is a fast and accurate analysis tool that will immediately tell you whether your Twitter Follower Acquisition Program is improving or not.

A great combination to build a larger Twitter following includes TweetAdder, SocialOomph, and bit.ly.

SocialOomph

7) Don't let different accounts Tweet the same thing at the same time.

6) Tweet no more often then hourly and 10X per day.

5) Take care if you use derivatives of the same gmail account

4) Don’t set up more than 10 Twitter accounts from 1 IP address.

3) Wait at least 2 to 3 days to unfollow anyone.

2) Keep your Following / Follower Ratio to 1.10

1) Limit follows to 350 per account per day.

The maximum allowable number of new Follows per day per account is 1,000. This includes Follows sent out and Follow-Backs. Your best bet is to stay way below this limit to avoid triggering Twitter’s attention. If you limit the daily Follows sent out by any single account to 350 or less, you should have no problem. I use TweetAdder to automatically keep Follows plus Follow-Backs within this limit.

First and foremost, play it safe.

Try to stay under Twitter’s radar by not being too aggressive in acquiring new followers. Twitter will suspend accounts if any of its limits are breached. It is now becoming harder and harder to get a suspended account reinstated. The larger of a Twitter following you have, the more you have to lose if Twitter suspends one or more of your accounts. It is probably best to stay well within the limits of the following rules:

3) Record each monthly Histogram on an Excel spreadsheet and then compare them

2) Run a monthly Histogram on this data

1) Record the total combined number of new followers

Steps To Using the Histogram to Monitor Your Twitter Follower Acquisition program:

Excel Master Series Blog Directory

Statistical Topics and Articles In Each Topic

Subscribe to: Posts (Atom)

Become an Excel Statistical Master

Excel Master Series - MBA-level statistics - Over 1,100+ Pages of Easy-To-Follow Instructions in Excel

It's a Full
Easy-To-Follow
MBA Course in Business Statistics

ALL IN EXCEL

&

MUCH Clearer

Than Your Text

Book

Download the
1,100+ Page Excel Statistical Master now

More Easy-To-

Follow eManuals

That You Will

Master Quickly

Step-By-Step Optimization With Excel Solver

What's In It?

For anyone who wants to be operating at a high level with the Excel Solver quickly, this is the book for you. Step-By-Step Optimization With Excel Solver is a 200+ page .pdf e-manual of simple yet thorough explanations on how to use the Excel Solver to solve today’s most widely known optimization problems. Loaded with screen shots that are coupled with easy-to-follow instructions, this book will simplify many difficult optimization problems and make you a master of the Excel Solver almost immediately.

Here are just some of the Solver optimization problems that are solved completely with simple-to-understand instructions and screen shots in this e-manual:

• The famous “Traveling Salesman” problem using Solver’s Alldifferent constraint and the Solver’s Evolutionary method to find the shortest path to reach all customers. This also provides an advanced use of the Excel INDEX function.

• The well-known “Knapsack Problem” which shows how optimize the use of limited space while satisfying numerous other criteria.

• How to perform nonlinear regression and curve-fitting on the Solver using the Solver’s GRG Nonlinear solving method.

• How to solve the “Cutting Stock Problem” faced by many manufacturing companies who are trying to determine the optimal way to cut sheets of material to minimize waste while satisfying customer orders.

• Portfolio optimization to maximize return or minimize risk.

• Venture capital investment selection using the Solver’s Binary constraint to maximize Net Present Value of selected cash flows at year 0. Clever use of the If-Then-Else statements makes this a simple problem.

• How use Solver to minimize the total cost of purchasing and shipping goods from multiple suppliers to multiple locations.

• How to optimize the selection of different production machine to minimize cost while fulfilling an order.

• How to optimally allocate a marketing budget to generate the greatest reach and frequency or number of inbound leads at the lowest cost.

Step-By-Step Optimization With Excel Solver has complete instructions and numerous tips on every aspect of operating the Excel Solver. You’ll fully understand the reports and know exactly how to tweek all of the Solver’s settings for total custom use. This e-manual also provides lots of inside advice and guidance on setting up the model in Excel so that it will be as simple and intuitive as possible to work with.

All of the optimization problems in this book are solved step-by-step using a 6-step process that works every time. In addition to detailed screen shots and easy-to-follow explanations on how to solve every optimization problem in the book, a link is provided to download an Excel workbook that has all problems completed exactly as they are in this e-manual.

Step-By-Step Optimization With Excel Solver is exactly the e-manual you need if you want to be optimizing at an advanced level with the Excel Solver quickly.

*******************

Become an Excel Statistical Master

It's a Full
Easy-To-Follow
MBA Course in Business Statistics

ALL IN EXCEL

&

MUCH Clearer

Than Your Text

Book

Download the
1,100+ Page Excel Statistical Master now

More Easy-To-

Follow eManuals

That You Will

Master Quickly

*******************

Become an Excel Statistical Master

It's a Full
Easy-To-Follow
MBA Course in Business Statistics

ALL IN EXCEL

&

MUCH Clearer

Than Your Text

Book

Download the
1,100+ Page Excel Statistical Master now

More Easy-To-

Follow eManuals

That You Will

Master Quickly

*******************

Become an Excel Statistical Master

It's a Full
Easy-To-Follow
MBA Course in Business Statistics

ALL IN EXCEL

&

MUCH Clearer

Than Your Text

Book

Download the
1,100+ Page Excel Statistical Master now

Immediate, Absolute, No-Questions-Asked, Money-Back Guarantee If Not TOTALLY, 100% Satisfied. In Other Words, If Any Excel Master Series eManual That You've Purchased Here Does Not Provide Instructions That Are CRYSTAL CLEAR and EASY TO UNDERSTAND, You Get All Of Your Money Back Immediately and Keep the eManual. Guaranteed!

Meet The Author