Quick ROI for SEM (Adwords) Defined

SEM or Internet paid advertisement or PPC or Adwords or what ever one may call, has been on high growth rate for many years now and has shown that it will continue to grow in the near future with the potential to grab market share from other advertisement channels.  This has implications on all companies that would like to have their products or services advertised.  By quickly responding to the market changes and being nimble, they can effectively spend their marketing dollars with positive ROI.

Though I use Google Adwords in the following explaination, you can replace the same logic for Yahoo and MSN. I assume that you are somewhat familiar with natural search, paid search, clicks, impressions and related; and what they mean.  

Related posts: Quick ROI forecasting technique

Google or other search engines provide you the service to bid for specific keywords/ phrases.  And at the same time they track those keywords performance including clicks, impressions, and many more.  The big advantage is the availability of all this information through APIs (Application Programming Interfaces) and  I suggest you use daily summary to help track them.  The APIs calls are simple to setup and you can get it going with mainly one report called “Placement/ Keyword Performance Report”.
Google takes less than 1/2 an hour to provide the 80K to 100K keyword data. On making the API call to get the report, make the application check every minutes or so and once ready pull the report.  If you are pulling the reports from more than one search engine, it is easy to have separate instances of application and then merge everything in database or data warehouse.  This way you can have better concurrency.
This is the work horse that pulls the data from search engines.  The application can be single threaded to handle small load or if it is a large load (say, more than 100K keywords), you can use multi-threaded programming techniques.   Google’s v13 or v2009 API calls provide all the important data elements that one need, but in case of Yahoo you need to make two separate calls if you need max bid and keyword type.  But for most business uses you can do without them thus simplifying the process. The complexity araises not because you need to make 2nd call, but Yahoo returns different matching data and you need to take care that you match them to the correct bidding units.  I will provide more information on this in other articles.
Psudeocode or logic:
   Authenticate with Adwords (with your token)
   Make a SOAP request (through Perl, Java, PHP, etc.)
   Dump all the data to a file (Google has more than 50 columns).  Also, do any changes or cleanup, if needed.

Data Warehouse:
Once the data is dumped into file, have another process or same process bulk load the data into staging area of the data warehouse.  Medium or large companies typically have their own internal process setup to track the conversion and revenue.  So, by combining the spend/cost data from Google with your revenue, you can measure the ROI very effectively to the lowest level of granularity – keyword and its type.  You can also slice and dice the data along time, accounts, campaigns, ad groups, geo-location, etc.
Psuedocode or logic:
     Validate staging data
     Join revenue data with cost/spend data
     Summarize with time, bid unit, location, search engines, sites and other dimensions
Once you have the metrics of interest to you (either positive ROI, rate of improvement in clicks/ impressions, etc.), you can create reports for only those keywords/ campaigns that of interest to you.  Now you are ready to increase or decrease the bid amount on the keyword.  Also, note in this step, you should optimize your pages to the keywords so that your quality score is improved.  Just increasing the bid without the well optimized page is throwing the money away.
Psuedocode or logic:
   If daily change to number of keywords is not high, you can do bid changes manually or through excel.
   Else build automated bid framework that helps you