Time to play Adwords with the big kids. Hitch your pants up and lets get going. From start to finish, you’ll see how to setup a datafeed to generate content that gets triggered by Adwords clicks. Content rich advertisements that provide a plethora of opportunities for your visitors to find exactly what they are looking for. By way of example, you will see how to: – get a datafeed, setup a MySQL databse, create a script that cleans up the datafeed and uploads the content to the database – build a PHP search script for displaying results appropriate to your visitors interest – use a script that creates Adwords keyword groups from the title of the products stored in the MySQL database – setup an Adwords campaign that uses dynamic keyword insertion – define a htaccess file that creates search engine friendly URLs from the Adwords dynamic keyword insertion used in your advertisements – create a template that provides a list of related items and common content such as a header, navigation bar, contextual advertisements (like Google Adsense), and footer – another PHP script, a redirect script, that logs time, I.P. address and link clicked for tracking purposes There is a lot to cover in this Adwords article, lets get started.

Setting Up Your Datafeed

Like I said, this is going to be a working example, no theory here, of how to use Adwords with a datafeed. Trust me when I tell you it is not all pretty. There is some ugliness involved when adding a vendor’s datafeed to the mix – “goofy” characters being the biggest offender. You’ll know them when you see them. For this example, I am going to use a datafeed I have previously used, one of the most content rich datafeeds I have found on ShareASale – the Nature Hill’s datafeed. If you want to follow along, head over to ShareASale, login or signup if you aren’t a member already, then grab the NatureHill datafeed. Once in, find the Nature Hill’s program and click on the link that says “Download .csv”. You’ll need to download the file and extract it to your computer. Next, upload the extracted file (4742.txt) to your webserver in the directory of your choice and possibly change the permissions on this file (chmod).

Good coders create, great coders reuse

.. or something to that effect. I tell you this because I found a site where someone has done a great job setting up a database and importing the datafeed just like we want. Like I said, good coders create, great coders … But before we do that, we need to create a MySQL database, user, and password. You’ll also add the user to the MySQL database. When everything is said and done, your MySQL information should look like this if you are using CPanel: Back to the script. Of course, it won’t be right if I didn’t hack up the script a little bit, now would it. Remember how we have already uploaded the merchant’s datafeed file, in this case 4742.txt, to our webserver. Since we have already done that, we can chop off the ftp portion of this script and simply start by reading in the file. Plus, we need to add a little something extra to cleanup the title (Name). The final script looks like this: Note well the “REPLACE” comments – that is for you to specifically change, in particular your ShareASale id ($sas_id) which is a number provided by ShareASale, database user, database password, and database name. Typical information might look like this: Now that we have all of our “supplies”, it is time to start building a …

Search Script

… that is “smart” enough to display results that your visitor would be interested in. I already have in mind how my Adwords campaign will be setup so I know what this PHP search script needs to do. Now, for the purists out there, you’ll recoginize that what I am about to show is a “hack”. Not a pretty way of accomplishing what I want to do, but it works. If the database I am using was much larger, I would probably run into performance problems. The idea behind this is that I am looking for items whose Name matches ANY of the keywords provided by the user via an Adwords click. If someone is looking for “apple trees”, the search script will return results that have either “apple” or “tree” or both in the Name of the database field. And being the money grubbing mongrel that I am, results will be returned with the most expensive item displayed first As a fall back, if there are no matches in the Name (title) part of the database, the search script will make a second pass through the database looking in the Description part of the database for matching items. If that returns no results; well, I’ll figure something out by the time we get to the end of this article. We will start by creating a snippet of code that parses through the keyword or keywords and creating a sql query statement. This code assumes that the keyword(s) is passed from another script (which it will be using since we are using dynamic keyword insertion with Adwords). Line by line: 3) Grabs the keyword(s) passed from another script, chops off the whitespace at the beginning and end (if there is any), then strips out any tags (html type) to prevent any “junk” from being passed in. 4) Take the keyword(s) and break them up into individual words, shove them into an array. 5) Loop through each keyword. 6) Make sure the keyword is not empty. 7) Build a portion of the sql query needed and keep appending for each keyword. 10) Once finished with each keyword, chop off the extra “||” from the end. This is the conditional part of the yet-to-be-displayed sql query needed for searching the database of affiliate products.

Lets Query

If you look up at the original script shown (you don’t have to, trust me on this one), we named our table naturehills. Lets define a variable with that value. Now create a sql query that uses that variable and the $extra variable. Our search script now looks like this: Lets move along and show what the script looks like with the sql statement and looping through the results (along with our backup plan if we don’t get results the first time). Save this as “search.php” Notice I have added some MySQL stuff at the top to connect to your database. For where we are now, I am simply having the script print out the Name if it finds a match. If there are more than 30 results, the script stops returning results. The $count keeps tracks of how many results; if there are less than one result after the first sql query, the search script then changes the query from looking in the Name field to looking in the Description field ($extra = preg_replace(”/Name/”, “Description”, $extra);). To be honest, I don’t expect that the Description option will ever be triggered. Why? Because of the way I will setup my Adwords campaign that uses the datafeed Name as keywords. But just to be safe the script will include the backup query. Almost forgot my money mongering ways, add this to the sql statement to return results by most expensive to least expensive: That’s the basis of our search script, we will fill it out later to present the results in such a way that results are included in a template to display for our visitors. Moving right along.

Creating Adwords Keyword Groups

We are going to query the database that contains our datafeed information, grab all the names, and build a keyword group for EACH keyword that we can dump into the Google Adwords Editor. We could always group our keywords by another field in the datafeed, either Custom1 or Custom3, which shows which groups each Name fits into. Since I don’t know if I can count on these fields being populated at a later date, I’ll opt not to use these.

The result from running this script is a textarea box filled with ad groups that consists of one keyword per ad group with the campaign name “CAMPAIGN” that looks like the following.

I recommend you do a search and replace and give the campaign (”CAMPAIGN”) name something else. Then copy the results.

Google Adwords Editor

If you are not familiar with the Google Adwords Editor, download it here.

Next, go over to your Google Adwords Editor (I’m making the assumption that you have figured out how to set this up). Click on “Keywords”, then click on “Make Multiple Changes”, choose Add/Update Multiple Keywords.

Next, for the Destination option, choose the radio button that says “My keyword information below includes columns for campaign and ad group names. (These columns should be first, and in this order.)”.

Paste the results. Click on “Next”.

Some keywords might be ignored for duplicate reasons.

Complete the information required under the Ad Groups and Campaigns tabs before posting this new campaign to Adwords.

Under Campaigns, you will need to set a daily budget.

Under Ad Groups set a Max. CPC Bid (USD). Finally, click on the Ads tab, click on the Add Text Ad and add an ad for the newly created camgain (that was a mouthful).

NOTE WELL the liberal use of dynamic keywords  THIS IS IMPORTANT  and is the heart and soul of the whole operation – in particular the destination url.  If you are not familar with dynamic keyword insertion, check out the post I made about this.  http://www.davewooding.com/google-adwords-dynamic-keyword-insertion-php-script/

That’s it for the .htaccess file.

Template For Results

For this, I will leave up to you, dear reader, to come up with a decent looking template that displays results the way you want them to look like.

I’ll show results using a basis template that relies on subsitution for presenting unique content.

Create a file called “template.html” that contains something like this:

I know, I know, very boring. I’m sure you can come up with a much better looking template. You’ll probably want a Terms of Service page, a privacy page, an About page, a contact page, etc.

Notice the %%whatever%% things, those are “tokens” which will be replaced with a value that the search script uses … a value that is pulled out of the database based on the keyword sent over from Adwords.

A Better Search Script

Remember our php search script from earlier? We are going to modify it to log the keyword(s) along with the date/time, and I.P. address of the visitor along with printing out results that use the template.html page.

Lets start by creating another table in our MySQL database called “visitors” that includes date, I.P. address, search term. Here’s the SQL dump to create.

Tracking Keywords And Clicks

Ah yes, tracking. How could we possibly forget that. You may have noticed I sneaked in a page called go.php in the results that get printed out. That is a PHP script that (1) dumps the search search term into a database and logs the time, I.P. address of the visitor, the keyword that brought the visitor to the site, and the link that was clicked.

Now we just have to create it.

First, another MySQL table called “clicks” that logs the date, visitor’s I.P. address, keyword used to arrive and link clicked.

And our go.php script to log the click and redirect to the vendor:

http://www.davewooding.com/adwords/go.phps

Adwords driven, datafeed generated content

That’s all she wrote. Hope that helps you get your head wrapped around the idea of using a datafeed with Google Adwords.

{ Comments on this entry are closed }