> would you mind sharing the structure of database i.e. how you split the information in your tables.
It's called normalizing the database. I would mind but, this is pretty simple, any other DB guy/gal could do it, and Adwords' structure and wording makes it easy, so here goes:
Campaigns table. Fields containing full name of campaign. I limit to 50 characters to make it simple. Not sure what Adwords actual limit is. I generate a unique ID since Google does not provide it but that's simple. I also have a field for the service (Google, Bing...), again a unique one-character code.
Adgroups table. Linked to above table through the campaign ID. I generate a unique ID here too since Google doesn't provide one. Length of groupname field is currently 50 but I need to increase that.
Ads table. Linked to adgroups table through adgroup ID. Here, Google is nice and provides their unique 10-character ID, so I use that as the primary. Also headline, adline1, adline2, dispurl and desturl fields, all memo fields (called variable length in other databases). Since I manage client accounts, I also have a field telling me if I created the ad or if the client did, just for statistical purposes.
Keywords table. Again, I must generate my own unique ID. Links to ads table. 80-character field of the keyword (this is Google's limit) and a matchtype field. Note that since this links to the ads table, there will be the same keyword repeated for the same ad group. That was my design decision which made sense: a keyword is linked to the ad, not the group. It's something to keep in mind when I do certain types of queries.
Results table. Links to keywords. I store each day's data for each keyword in here so I have the date (I call the field served), imps, clicks, adposition, cost, conversion and network (Google, Search Partner or Content network code).
Since I manage client accounts, I also have a clients table that links to the campaign table. If you have an MCC account, one field Google provides is their unique client ID, so I use that. But I generate my own should the client also use Bing, Yahoo or whatever else.
Next thing I want to implement is a landingpage table. The idea is to track versions of landing pages. This would contain the destination URL and a serialized version number with start and end dates. It would link to the ads table with a unique page ID (the desturl in that table would be replaced with it). The problem is tracking this. I like to know of even the smallest change on a page but clients don't tell me that. I could periodically read the page (say once a month) and compare the code. Not the perfect solution but better than nothing.
You may be wondering about QS. I could track this over time too. I used to on a daily basis (keyword ID, date and QS) but the table becomes huge. Plus, QS is for the keyword-ad, not just the keyword, so that's problematic. One solution to the table size problem would be tracking only when the QS changes (start/end dates). I finally decided it was not worth to do so. If I need QS for some calculation, best to just download the current QS and use that. If QS had better resolution such as one decimal point, I might reconsider as that would be more useful.
One thing I should do which I don't right now is a table of bid values. Again, start and end dates with the bid. This could be taken from the Changes report.
There's other data I could download and store too which I don't right now, the product extension data for one. Some which I occasionally do are the Search Query Report and the hourly report. These don't link to the main tables.
Does that answer your question? A pretty simple setup really.
