How to Set Up sub id Encryption with a PHP redirect

March 26th, 2008 • Related • Filed Under

A common question I see on internet marketing forums is on how to set up affiliate sub id tracking. While there are paid tracking tools out there that will “cloak” your sub id, I find it just as good to set up your own tracking for free! Smaxor posted an excellent script that will do just this for you. His instructions are fine, but I know many people are having a hard time understanding the concept as well as having trouble setting everything up. On top of that, many people want to use a PHP redirect in conjunction with their affiliate links.

So here’s a somewhat more expansive tutorial on configuring the script, and how to use it in conjunction with your own PHP redirect. Let’s get started…

The reason for hiding things

What we’re about to do serves many purposes. For one, we are preventing the merchant / advertiser from seeing our keywords. If we sent all our keywords in plain text as sub ids, it would be a breeze for the merchant (or even affiliate network or your affiliate manager) to see what keywords are bringing in the most conversions and sales. Sharing this valuable information is something you don’t want to do. Keep your converting keywords to yourself!

Of course - doing this also lets us track which keywords convert and which don’t.

Secondly, any landing page on your site should cloak affiliate links to make it less obvious to the end user that your links are affiliate links and that you are getting paid for the sale. And yes - most surfers won’t know the difference anyways, but there is actually a small percentage of people who pay attention to the links they click. Or worse - some will copy and paste an affiliate link and cut off your affiliate id before they go to the URL just so you won’t get paid(!). In this case we are setting up good ol’ PHP redirect for our link.

But how does it all work?

So we are now putting these two methods together, and the figure below shows how this works. Click the diagram to enlarge it.

Let’s get started!

The first thing you wanna do is get Smaxor’s script and the SQL code needed to create the database. I have put these into a zip file that can be downloaded here. (Right click, then “Save As…”)

Step 1 - Create your sub id database and user

In order for the script to work, we need a database set up on the server under the same account as our landing page. I assume that you already have a shared hosting account for your domain that you can manage. Log in to your control panel (in my case, it’s CPanel) and look for the link to MySQL databases. Create a database and call it what you want. For this example we will call it subidbase.

If your account username is userdude, MySQL will then create a database with the name userdude_subidbase.

Now create a user for this database. We will call it subiduser, add a password, and MySQL will then create it as userdude_subiduser.

The user must be assigned to the database so the next step is to connect the two. My CPanel has a Add User To Database button to do this. Your control panel or hosting interface might be different but you should have a similar function somewhere.

Step 2 - Create the keyword table for our database

You should also have access to phpMyAdmin in your control panel. If you don’t have phpMyAdmin, you could either install it or perhaps run your SQL commands from your hosting account ssh shell. If you don’t know what I’m talking about here ask your webhost to help you out. Trust me, it’s less complicated than it may sound.

Anyway, go to phpMyAdmin and select your database by clicking on its name in the left pane. Now click on the SQL button and paste in the following code into the SQL query field and execute it:

CREATE TABLE keywords (
id int(11) NOT NULL auto_increment,
keyword varchar(200) NOT NULL default ‘ ‘,
count double NOT NULL default ‘1′,
PRIMARY KEY (id),
KEY keyword (keyword)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3;

Note: this code can also be found in the file create_table_for_subids.sql as part of the zip package you downloaded earlier. If you’re not using phpMyAdmin you can also run this file within a mysql shell to create the table.

Step 3 - Prepare your dbconnect.php file

Take a look at the dbconnect.php file. This file is needed by the script to communicate with the database. However, you need to open it in an editor and replace the dummy database name, username, and password with your own appropriate values. (Unless your hosting account username is actually userdude )

Step 4 - Insert Smaxor’s code into your landing page

Now we’re getting closer to being done. Open up Smaxor’s script - the file called Smaxor_php_subid_encryption.php. Copy the entire script and paste it into the top portion of your landing page.

Step 5 - Prepare your affiliate link redirect script

Time to take a look at our PHP redirect. Are you ready? Cool. Open up the file buy.php in an editor and replace the text YOUR AFFILIATE LINK with….your actual affiliate link! O’RLY? Yes, really.

Step 6 - Call your php redirect from your landing page

Now all we need to do is place a link to our php redirect (which now of course takes the visitor to your affiliate link WITH your encrypted sub id). An example of this can be seen in the file called what_goes_into_landing_page.html. It’s pretty self explanatory.

So there you have it. Post questions or comments to this post if anything is unclear. I can try to make things more clear if need be.

For some additional reading about sub ids, check out Wes Mahler’s site Subids.com.

Share this post with others: These icons link to social bookmarking sites where readers can share and discover new web pages.

There Are 31 Responses So Far. »

Pingback by What do AMs Need to know about you? - 5 Star Affiliate Marketing Forums on 28 March 2008:

[...] all your links, use their ppc spy tools, etc, etc. Any thoughts appreciated. Thanks. Here is an tutorial that i found interesting regarding your question and it’s something i will definitely be [...]
Comment by Brian on 31 March 2008:

Thanks for this post. I haven’t gone through it totally yet but it’s exactly the issue I was looking to fix (i.e. using Smaxor’s script along with a php redirect.)

I’ve been unable to get the zip file you provided with the script to work (it keeps saying it’s invalid or corrupted every time I try to open it.) Any idea what’s up there?

Thanks again!
Brian
Comment by ImagesAndWords on 31 March 2008:

Brian; That’s odd. The zip file uncompresses on my end but I will try to re-upload it and maybe upload a .rar file as well to be sure.
Comment by Brian on 31 March 2008:

I tried on my other PC as well and it looks like the zip file isn’t loading from the “download” folder on your site. I’ll look for your update and the .rar file too.

Looking forward to reading more posts in the future! Thanks!!
Comment by Brian on 31 March 2008:

Ahhh…found the problem. You have the link set up as being under a folder called “downloads” when it should be “download” (singular.) Hope that helps!
Comment by Brian on 31 March 2008:

Scratch the last post….I meant you have the file under a subfolder in the download folder. Feel free to dump my other useless posts.
Comment by ImagesAndWords on 31 March 2008:

Lol, I see what I did now. My apologies. The folder has been fixed.
Comment by Nick on 6 April 2008:

Thanks for this man! I really appreciate this, I plan on tweaking it a little but the walk through definitely helped.
Comment by maikunari on 6 April 2008:

Thanks for the tutorial!
Is there any way to get this to work without it printing this in the top of my landing page?

keyword grab:
SELECT * FROM keywords WHERE keyword = ”
id1: 3
sql2: UPDATE keywords SET count = count + 1 WHERE id = ‘3′

Cheers!
Comment by maikunari on 6 April 2008:

Also, how would I set this up for multiple pages within the same site?

Thanks
Comment by ImagesAndWords on 6 April 2008:

Hey maikunari,
Yeah, all you need to do is comment out the echo statements in the code. Smaxor just included those lines as an example so you can see that the script works.

Just remove or comment these lines like this:
// echo “keyword grab: $keyword
“;
// echo “$sql
“;
// echo “id1: $id
“;
// echo “sql2: $sql
“;
// echo “sql3: $sql
“;
// echo “id2: $id
“;
Comment by maikunari on 6 April 2008:

Perfect, thanks for the fast response!
Already subscribed to the blog, good luck with it.
Pingback by PHP Encrypted SubID Tracking - Affiliate Network - Firelead on 6 April 2008:

[...] How to Set Up sub id Encryption with a PHP redirect [...]
Comment by alfred wong on 22 June 2008:

I got up to step 3 and I don’t know what to do with the dbconnect.php file? After I make the changes where do I put the information?
Comment by alfred wong on 22 June 2008:

After Step 3 do I copy and paste the new information? If I do, where do I paste it at?
Comment by vaboski on 22 June 2008:

MY CPANEL FOR THE ‘LOCALHOST’ PORTION OF THIS CODE ON HOSTMONSTER ACTUALLY SAYS “SERVER:LOCALHOST”, DO I CHANGE THAT PART TO HOSTMONSTER OR LEAVE IT AS LOCALHOST??? SORRY SUPER NEWBIE HERE
Pingback by PHP Encrypted SubID Tracking | our-system.com on 26 September 2008:

[...] How to Set Up sub id Encryption with a PHP redirect [...]
Comment by robert on 17 January 2009:

I did everything as stated in your tutorial and everything seems to work, but then when I go to my shareasale account, under today’s stats the last page clicked shows my keyword in the link and not the number id. Any suggestions or did I do something wrong?

Thanks
Comment by ImagesAndWords on 19 January 2009:

@robert;
Take a look at what has been populated in your database in phpMyAdmin. Perhaps you’re sending the wrong values to the database through dbconnect(?)
Comment by Chris Donaldson on 9 March 2009:

I am going to try this out. Does anyone know if this script will block advertisers from seeing where their affiliate’s are promoting their offers?
Comment by Debbie White on 24 April 2009:

I have stumbled across your instrictions, which is exactly what i need, but the zip file I downloaded is empty. Possible because this post was so long ago? I’d love to get a good copy please. Let me know. Thanks, Debbie
Comment by ImagesAndWords on 24 April 2009:

@Debbie; Weird. The zip file looks fine on my end when I downloaded and tested it. Email me or direct message me on Twitter.com/ImagesAndWords, and I can try to send the file directly to you.

Anyone else having trouble with it?
Comment by Debbie White on 26 April 2009:

How do I email you? My website email is dwhite@onlinewealthcanada.com if you could possibly send me the file, it would be appreciated. Also, if you visit my ‘very newbie’ site, your comments would also be appreciated.
Thanks,
Debbie
Comment by Eric Itzkowitz on 3 July 2009:

I was wondering if your script will work with WordPress blogs? I am using PPC to drive traffic and all of the manual ways I have found to track keywords are rendered useless as soon as somebody clicks away from my landing page to another page/post on my blog.

Thanks!
Comment by ImagesAndWords on 4 July 2009:

@Eric;
Although I’ve never tried to use this with WP (since I don’t use WP landing pages), it will probably work if you integrate it with Exec-PHP. Go to Wordpress.org and search for the Exec-PHP plugin.
Comment by JAG on 10 October 2009:

Hi,

Thanks for the great tutorial. I have a few questions because I have never done this before.

1. If you want to direct link to an offer, do you just paste smaxor’s code into the buy.php page and then direct traffic to buy.php?

2. How does this fit in with your split testing with tracking 202 tutorial? Do you just send the ppc traffic to the rotate script that then redirects them to a direct linking buy.php?
Comment by Paul on 13 October 2009:

So what would our adwords destination url be if we want to dynamically track keywords?
Comment by Paul on 13 October 2009:

I am trying to use this with CJ and I can’t get the affiliate link to load with the buy.php

It switches to the affiliate link url but nothing will load up.

Is this because CJ uses “sid” instead of “subid”
Comment by ImagesAndWords on 14 October 2009:

@JAG;
1) Yes
2) I don’t mix smax0rs’ script and T202. Ever since I started using T202 more, I haven’t used his script that much.

@Paul;
To answer your questions; The destination URL would be like the one showed in step 1 of my figure in the post above.

Not sure what causes this on your end but yeah - make sure that all php parameters are the same throughout your campaign and your script setup.
Comment by Paul on 14 October 2009:

Can the subid be dynamic in the destination url like this:

http://www.myaffiliatelandingpage/index.php?k={keyword}

or does it have to be a predetermined SID adding to my destination URL already.

And in the Buy.php file do I remove both ” when I add my affiliate url:

( “Location: http://YOUR AFFILIATE LINK GOES HERE”.$subid)

like this or no?

( Location: http://www.affiliatelink.com.THEUH33444HH.$subid)
Comment by Eric Itzkowitz on 20 April 2010:

@ImagesAndWords I couldn’t find anything so I hired a developer at the end of 2009 to build a WordPress plug-in for me that tracks PPC keywords, campaign IDs, affiliate IDs and affiliate SUBIDS and the like. It’s available via my blog.