How to Automate Tracking Sales by Subid?

[blacktastic]

I have a campaign where I am I'm directly linking to the sales page of the merchant and can't get conversion code on there. Thus, I'm tracking sales by subid so I can weed out the underperforming keywords.

I found some hidden gold and want to start automating this as much as possible but am having trouble finding a quick way to track sales.

Currently I download my adwords report (just using G to start), sort the list by clicks, find the subid in the custom url for each sale in my affiliate report, and add my commission amount in a new column to the right. Easy enough...

What happens the next time I run a report? I could just copy the whole column of sales info over, but if I ever add or remove a keyword from my adwords campaign, the numbers won't match up.

It wouldn't matter much to copy the info over by hand at the beginning, but this is building pretty fast and I want to automate as much as possible.

Any ideas? If I'm making this way too hard, plz let me know of an easier method to track sales over time by subid using a direct link to the advertiser page (no conversion codes allowed)

 

[on-on]

I wrote an article that roughly describes the basic theory behind this kind of accounting, though you probably already get it. Basically, you need to impose yourself inbetween the two actors in the conversion and insert a unique tracking identifier. In your case, the metric you're trying to track is somewhat different - you have a keyword and you want to rate its performance against other keywords and potentially index this by product, so you will want to record the AdWords information in your internal database and pass along a unique transaction identifier to your affiliate network. Once they complete the reporting, download the report with the subids and true up the transactions. Why not just use the affiliate network's reporting, you ask? Because A) you're tied to someone else for your reporting and B) what happens when you want to add a second affiliate network and a third and a fourth and so on? Better to start collecting data now.

Article's Here:
Basic Rube Goldberg Affiliate Marketing Accounting

(and no I'm not selling any stupid eBook or magic beans or anything, it's just a long blog article on the topic)

 

[micker]

I'm not sure what you're trying to do, but I usually take a text file of my keywords and do something like this (you'll need to do this on a mac or a linux/unix system);

cat keywords.txt | sed 's/ /-/g' | awk '{print"http://path.to.affiliate.site.com/page.php?affil=fd7d0s&subid="$0}' >urls.txt

then I paste that into the spreadsheet I send to the ad network I'm using and go from there...

 

[blacktastic]

I'm tracking the data just fine and getting reports, I'm trying to figure out a way to combine Google's adwords data with my affiliate's data in a manner that will allow me to spend less time crunching numbers and more time analyzing.

currently I can add my commission amounts from my affiliate reports directly into a new column in the adwords report that I download, so I can see what keywords are making sales.

BUT... the next time I want to run a report. I would have to manually copy all the sales information from the previous report over into the new adwords report.

Now, if the keywords stayed exactly the same between reports, I'd be fine - I would just copy the column from one spreadsheet and plug it into the new one. However, I'm going to be adding and removing keywords over time, so the two spreadsheets wouldn't match up.

Hope this makes more sense - thanks,

 

[micker]

so create some kind of order for your report, organize it by something and then sort the affiliate report in the same manner before pasting it in.

 

[on-on]

Oh, I overshot the target there, heh. I didn't key in on the guerilla spreadsheet and bondo approach. Good luck! If you ever want to build some tools to handle the work for you, let me know.

 

[blacktastic]

so create some kind of order for your report, organize it by something and then sort the affiliate report in the same manner before pasting it in.

That's the problem, the new and old reports wouldn't have the same keywords, so whatever order I use would not align correctly.

Oh, I overshot the target there, heh. I didn't key in on the guerilla spreadsheet and bondo approach. Good luck! If you ever want to build some tools to handle the work for you, let me know.

Yeah, guess I'll have to splash out some cash... I've shot myself in the foot before by being "thrifty" - losing profits in the meantime.

 

[beejeebers]

There are programs written that do this, but they cost a ridiculous amount of money each month and you don't get to keep the script on your server. You could go to rent a coder and ask someone to write a script to do exactly this.

Plus, have you ever actually asked a merchant if they would put your conversion code on their thank you page? You'd be surprised how many will do this for you. (some may require some good sales first, but it is possible).