Basic Rube Goldberg Affiliate Marketing Accounting

The point of the Rube Goldberg series of articles is to give some advice to the uninitiated on how to spend the least wasted effort to structure their online marketing so that they can make the most back out of it. All non-technical marketers start off their online marketing lives with small efforts like one-off pages linked to one-off offers and inevitably end up asking questions. How can they find out who is buying what? How can they link that data back to the purchaser? How can they handle abrupt changes in the status of affiliate offers without having to manually change 100s of links?

There are a million questions that those of us who work inside the industry have been forced to answer already, but that remain mysterious to the neophytes who haven’t been bloodied over the years as the industry grew up around them. The solutions I offer and the advice I give are technology non-specific where possible, deal only with light theory for the most part and are not geared for the experienced operator. After reading, the smaller online marketer without much tech experience should have a grounding in the basic theory of how their systems can begin to work together to more efficiently support their marketing efforts. This will enrich their reporting, their accounting and increase their flexibility so that they can make better marketing decisions with less footwork. It’s not about specific technology, it’s about concepts.

If you’re already at the peak of the game then please drop in and correct my errors or offer your own point of view - all help is appreciated. Being an internet tough guy, however, is not.

Every minute of every day at online marketing forums across the net I see people asking how to do this, that or the other thing with regard to affiliate marketing - “How do I get into it?”, “How do I find the right program?”, “Who shot JFK?” These are just the basics, but these questions lead to more complex problems. Sure, one moment you’re a golden boy with your first eDollars in hand. Bing, bang, boom, you’ve become an affiliate marketer. You run home and tell mom about your new career and she’s so proud of you. “My boy is in MARKETING!” she boasts to the neighbors. “MARKETING,” she reiterates smugly. Good for you. Count to five after this moment and the new questions start, revealing deeper issues that you will want to tackle. Hopefully we can work through some of these in an interesting and entertaining (for me, anyway) manner.

Setting Up The Scenario
Your name is Gomer and you run a music site called YourSite. Your current affiliate network is LinkShare and all your sales at the moment come from iTunes (just to make things easy and because only The Beatles hate(d) iTunes). Also you’re a pimply dork who lives in mom’s basement. Why? Because it’s fun to harass your readers. It doesn’t matter for now how YourSite gets its traffic, just that YourSite is the final handoff point between your members and LinkShare. YourSite recently started making money using these iTunes offers and you’re wondering who and what is driving these new profits (if you don’t wonder, fake it or skedaddle). You want to target your members with more and better marketing messages and hopefully make enough money to move out of mom’s basement, but you’re not sure how. Kudos.
After a long night of littering up people’s MySpace accounts with your friend requests and submitting your album reviews to digg, you awaken and roll over to your Cool Ranch Dorito-crumb-covered laptop and prepare to check on the fruits of your labor. Giddy as a schoolboy, you log in to LinkShare to check how much money you’ve made on iTunes, click the report button and the whirring and clicking commences while the orange boxes of the report screen flash methodically. With an anticlimactic “click click,” your report comes back and tells you that you made 183 sales. Woohoo, you earned $9.05! “I’m rich, Bick! I’m a rich one! I’m a rich boy.” you exclaim to no one in particular. In your feverish thirst for more information, you drill down to the individual item report level and see this:

Sunday 11-FEB-2007 14:55 Apple iTunes Q0319 ITUNES MUSIC STORE $0.99 1 5389185641

Introducing Our Hero’s Dilemma
Wait a second - what the Hell is that? QO319 is the product SKU which doesn’t correspond to a specific product on the iTunes site, just a type of product. “Crikey,” you say, “how will I know what people bought?” Even if you did know what got bought, you wouldn’t know who bought it, so you’d still be shooting flies with a shotgun. Another complicating factor is that iTunes rewards your members for all purchases made within 24 hours, so a member may have clicked on and bought a song advertised on your site and then bought several other songs that you didn’t advertise. The revenue is welcome, of course, but you’re not sure how many of these purchases are for what item, from which member and tied to what category (R&B, Country, Pop, Rock, etc).
We can’t solve all these problems because there’s an inefficiency of data in the reporting chain, but if you knew what item the member clicked on your site to get them to iTunes in the first place then you would be much better off because you would have two valuable pieces of information:

• The purchasing patterns of that particular member
• The effectiveness of that particular item in driving members to iTunes

Unfortunately, you’re currently stuck in this situation:

Bloviating About the Dilemma and the Necessary Elements of a Solution
The bottleneck is your dependency on your affiliate network, in this example LinkShare, for all your reporting data. LinkShare doesn’t know anything about your site or your members, only you do. LinkShare just knows what data you pass to them, what data they pass to iTunes and what data iTunes passes back to them (along with ancillary meta-data about your member that is logged during the click through for tracking purposes). You see, you know who your member or user is (we’ll call him or her a member for this example) because your member is registered at YourSite and has a database-generated member id (a number - like 12345678) that was assigned when their record was inserted into your member database. This member id can follow the member whenever he or she performs an activity on your site or whenever he or she clicks a link that you generate specifically for the member to click (in, say, an email).
The first crucial element you will need to start implementing a more complete system are some kind of database-driven tracking links (these are often called cloaking links by people who haven’t figured out that they provide valuable tracking data). Why database-driven? Well, mod_rewrite tracking links are popular in the online marketing community because they’re simple to implement and the marketers are impatient people, but using mod_rewrite alone for redirects is a kludge (it has better uses). In fact, it’s the comb-over of online marketing; You have only one choice for your hairstyle and you can’t go out in the wind (that analogy makes no sense, but I like it). Basically, don’t do it. Invest enough time to use database-driven tracking links. To give you an example of what I mean, using database-driven tracking links you might, for instance, go to LinkShare to get a link for the new Pink song at iTunes and receive a LinkShare link that looks like this:

click.linksynergy.com/?what=123

When you insert it into your tracking system, it spits out a link that looks like this in return:

yoursite.com/l.php?linkid=987

In this scenario, all links on your site that are outbound links to affiliate offers look like the yoursite.com link above. When a member clicks the yoursite.com link, your system looks up link id 987 in the database and sees that the correct redirect URL for that link id is click.linksynergy.com/?what=123, so it forwards the member to that outbound link (hereafter: tracking link). This is a very basic approach to cloaking or tracking, but it means that your system has part of the key the functionality we need to continue on. The other crucial functionality, which is the ability to track member identity through registration, has been stipulated as well, so we’re almost back to square one.
Going forward we’ll assume that you are now familiar with these concepts and with the inner workings of whatever software you use to accomplish what I’m describing here (unless otherwise noted) or, if you’re not, that you employ someone who is. People who use your site have a member id (hereafter: memberid) that they get when they register and all outbound links have a link id (hereafter: linkid) that is supplied by your system. Of course you’ll need to differentiate between links, so we’ll give them an arbitrary name called the link name (hereafter: linkname). Using this, you can always go back and figure out that link 987 pointed to “John Legend album.” It doesn’t matter what you call these pieces of data in reality, just that you understand the concept of what they are - unique identifiers for granular pieces of data that are crucial to the overall reporting chain. We’ll also assume, just for shits and giggles, that you’re capable of keeping your mouth closed while breathing and that you don’t move your lips when you read to yourself.
The Exciting World of Transactions and Subids
What is a transaction? Like anything in the data world, you get to decide that. With respect to affiliate marketing, a transaction can be any of several things - the exchange of a user from site to network, the purchase of goods at a merchant site or any other point of exchange that is worth tracking. In today’s example, we’re concerned with one specific kind of transaction: the transaction in which you hand a member from YourSite to LinkShare. This transaction marks the end of your direct relationship with that member and the beginning of the affiliate network’s and, eventually, the merchant’s direct relationship with that member. It’s the last chance you have to pee on the member, thereby marking your territory, before the rest of the affiliate chain does its job.
The point at which this transaction takes place is a valuable piece of information to keep track of because it’s the product of successful marketing. In other words, when you do a good job promoting an affiliate offer the reward is that the member goes to the affiliate and carries out revenue-producing activities. You have no control over what activities the member performs, but you do have a way of keeping track of the resulting value (we’ll get to that in a moment). At this point, having defined the transaction as a crucial piece of information in the affiliate marketing accounting data chain (that’s a mouthful) the only thing left to do is to capture it with a representative piece of data in your system. This transaction record will be tied to a unique identifier called a transaction id (hereafter: transactionid) that allows you to look up, in your system, who carried out the transaction, what the transaction consisted of and when it was carried out. What you attach to this record really depends on your existing data and how you handle it, but for today’s example we’ll assume that the transaction record captures the memberid, the linkid and the timestamp of the event. These pieces of data for a given transaction can thereafter be referenced by the event’s transactionid.

So, to recap where we’re at, each product on your site has a unique linkid. John Legend’s new album is linkid 987. Each member of your site has a memberid. SexyHeels6969’s memberid is 12345. In plain English, when SexyHeels6969 clicks a link on your site to go buy the new John Legend album, a transaction is logged in your system saying that memberid 12345 clicked link 987 at such and such time. Now you not only have a log of all these transactions, but you can query them in groups to find out interesting things. What product sells the best? What user clicks the most products? Unfortunately, this is only half the picture. You know who is clicking what and you know in aggregate how much money you’re making, but you don’t know how much money is being driven by each click and who is making you the most money. In marketing, knowing where this sweet spot lies is crucial since the proverbial 20 percent of your members will drive 80 percent of your profits.
This is where that other funny word, “subid,” comes in. The subid, a diminutive for sub-affiliate identifier, is a utility field supplied by most (all?) major affiliate networks for use by their publishers. Some even provide more than one subid field. This field is generally offered in the form of a querystring variable, but the details of what can legally constitute that variable depend on which network you use. Since we’re using LinkShare in our example, for now we’ll stick with explaining only LinkShare’s subid field (which they refer to as a Member ID). The rules set by LinkShare say that the subid field can be no more than 72 alphanumeric characters (0-9, a-z, A-Z) and that it must be referred to in the querystring by the label u1. So, what would this look like? Appending to our earlier example:

click.linksynergy.com/?what=123&u1=EepOppOrkAhAh123

In the example, u1=EepOppOrkAhAh123 is the subid field. The variable name “u1″ tells LinkShare that this is the subid and the content following the equal sign “EepOppOrkAhAh123″ is the value of that variable. How this helps your marketing is where things get interesting. LinkShare takes this subid and stores it in their database when they accept the member from your outbound tracking link. When LinkShare hands the member off to iTunes to go shopping, the member is uniquely identified to LinkShare by another process so that any purchase the member makes at iTunes is reported back to LinkShare in a way that allows them to tie the member’s purchases to that specific use of your affiliate offer link, which, in turn, is tied to your affiliate account.
Kinda confusing, but it makes sense if you think about it for a second. It’s the other side of your transaction process. To you, the handing off of the member to LinkShare is the key transaction, but to LinkShare the accepting of the member is the first of three key transactions. The second important transaction for LinkShare occurs at the point when they hand the member to iTunes to shop and the third important transaction comes in reply to the second, when iTunes hands back the purchasing data tied to the initial transaction through which they received the member. By tying one key transaction to another in their database, they are able to know that it was your member who made purchases reported by iTunes and, by keeping track of a specific instance of accepting a member from you, they can know the specific details of that transaction, such as what subid you passed them at that time. Get it? If you don’t, keep reading and see if it starts to make sense.
Transaction Tracking in Action and Recapping the Process to This Point
We already have a potential revenue-driving event recorded in your system’s database identified by a transactionid. Now we can hand that transactionid off to LinkShare in the form of the u1 field so that later we can find out what happened on the far side of the transaction when the member went to iTunes to spend money.

To illustrate using the previous example, SexyHeels6969 clicks a link (linkid 987) on your site to buy the new John Legend album at iTunes. That link looks like this:

yoursite.com/l.php?linkid=987

When your site receives this click, it already knows SexyHeels6969’s memberid is 12345 based on her session or her cookie and it then looks up the outbound link for linkid 987, which is the LinkShare link click.linksynergy.com/?what=123 . Once your system has this much figured out, it creates a new transaction in the database for this event. This transaction, transactionid 321, now holds the information that memberid 12345 clicked linkid 987 at [timestamp]. This much we knew already, but now we have a new element: the subid. When your site constructs the outbound link based on the linkid, it appends the transactionid to the outbound affiliate link in the LinkShare-supplied subid field. The resulting outbound link looks like this:

click.linksynergy.com/?what=123&u1=321

LinkShare then logs this subid in connection with their record of the transaction and you cross over to the dark side of the moon until reporting comes back. What does that mean exactly? Well, until iTunes reports back to LinkShare about SexyHeels6969’s purchases, nothing else happens for you. You basically sit and wait, refreshing the LinkShare reporting page repeatedly and annoying their systems team who have to build in extra capacity in order to deal with marketers like you who can’t resist mashing the refresh key in an irrational fit of hyperoptimistic exuberance.
What Happens on the Far End of the Transaction
The exact details of how all this is handled are not part of my particular expertise, though I understand the process well enough and have read enough material about it to have a good idea. Essentially, LinkShare hands off a unique identifier to iTunes that iTunes then appends to the member’s session and activities at the iTunes site. They also do some whiz bang cookie stuff to try to track conversions and other things, but the sales data all comes from merchant reporting that is based on the unique identifier previously handed off.
At some point after the purchase, iTunes poops out reporting data for LinkShare that has this identifier and the details of the purchases made at the iTunes site. LinkShare then accepts this reporting data and pulls it back into their system, tying the identifier in the purchase reports with the identifier they assigned when they accepted the member from you in the first place. Contained in the records for your initial handoff to LinkShare is the subid field (the u1 querystring variable) with your transactionid, 321, logged by LinkShare for all eternity (or possibly a shorter time period than for-eh-ver). How this relationship takes place is not the subject of this article since it involves all kinds of complex topics and unknown (to me, anyway) practices and variables, but the fact that it takes place is what’s important.
At this point, LinkShare has reporting data ready for you which is made available through a number of reports, including some that show each revenue-inducing transaction tied to the subid that you passed in the first place. Voila, there’s the magic. Several days ago you bundled SexyHeels6969 up, handed her a sack lunch containing a transactionid and sent her off to iTunes to buy John Legend albums with a kiss on the forehead. Your eyes got misty and you covered your mouth as you thought of all the great purchases she might make in your name. For days, you checked for any information, but no one had heard anything and you were becoming worried. Now, out of thin air, a record of what SexyHeels6969 spent shows up for you to see.
How do you use this data? Do I expect you to look through LinkShare’s reports line by line and figure this all out with an abacus and a copy of VisiCalc? No, THIS IS WEB 2.0 AS YOU MAY HAVE HEARD. Actually the answer, the process for what comes next, has been fairly routine for a number of years now and it’s the last piece in this basic accounting core of a Rube Goldberg affiliate marketing machine.
Retrieving and Truing Up Your Transaction Data
In the example we’ve been discussing, YourSite’s database writes a transaction record that includes the memberid, linkid and timestamp, all of which are referenced by the particular transaction’s unique transactionid that is automatically created along with the record. This transactionid is handed to LinkShare and subsequently linked back to purchasing data from iTunes and then this unified data is made available to you by LinkShare’s reporting services.
However, before getting into the retrieving and truing up process we’re going to need to add another kind of record to your database, the affiliate transaction record. The affiliate transaction record will hold the purchase-related data that you get back from the affiliate network so that you can link it, in your system, with the data you’ve already recorded in your transaction records. For simplicity’s sake, we’ll say that this record should hold three new pieces of data: a sale field (hereafter: saleamount) that contains the full monetary value of each sale, a commission field (hereafter: commission) that contains the full monetary value of each commission and an order id field (hereafter: afforderid) that relates back to the order id that iTunes sent to LinkShare along with the purchase data. This new record will also contain the transactionid, just like the transaction record, and you’ll see why in a moment.

Having summed up the data involved, let’s talk a little about how it all relates. The memberid and the linkid become part of a transaction record that has a transactionid. That transactionid is sent to LinkShare. LinkShare records it and eventually ties it to the purchase data it gets back from iTunes. This data flow occurs roughly as seen in the figure below.

We’ve gone over this basic flow several times in separate pieces and in total and we’ve done that for a specific reason. If you’re not familiar with how relational data works or with how affiliate networks and membership-based web sites work, then this will be confusing. If you already knew most of this, you might, at this point, start becoming irritated at the repetitiveness. I feel this repetitiveness is necessary to make sure that everyone gets the message several different ways, but it doesn’t matter because it’s over now! Time to cover the truing up process and discuss how we get your precious purchase data back into your system so that you can see how much SexyHeels6969 earned and what she clicked on to get there.
At this point you should know the data flow from your member to LinkShare and iTunes by heart. What you may not yet know is how that data gives you a reacharound. It’s pretty simple, really. LinkShare makes a report available by web or by secure, authenticated http download. This report looks (surprise!) roughly like the original record I supplied. Below I have replicated a sample of what the report might look like for several iTunes purchases. For this example, the only columns that matter are the noted columns. The other columns are important and we will discuss them in subsequent articles, but for now if you want to know more you will have to read the LinkShare Signature Reporting Overview PDF linked at the bottom of this article. The title on top is what LinkShare calls these values and the title in parentheses is what they’re called in your system.

As you can see, there are multiple records for each transactionid. Is this a mistake? Not really, it’s just the way that LinkShare chooses to report the data. For every purchase that your member makes, a separate purchase record is handed back to LinkShare tied to the transactionid of the link that last sent that member to LinkShare and, eventually, to iTunes. So what does this record tell you exactly from a business point of view? Well, it seems to say that you made money from two separate click throughs to LinkShare. One click through, transactionid 321, resulted in three sales for $.99 each while another, transactionid 444, resulted in one sale for $.99. For this example we’ll ignore transactionid 444 and focus on transactionid 321, which we’ve been using throughout the example to describe SexyHeels6969’s shopping spree at iTunes.
SexyHeels6969 seems to have made three purchases at iTunes for $.99 each, resulting in a commission of $.0495 each, for a grand total of circa $.1485. You just earned fourteen cents and a fractional sum that will be shaved off into a disgruntled IT person’s account like in Office Space - congrats! The next step is to grab this data in a machine-readable format so that you can import it into your system. We won’t go into details on this, but it is possible to automate this process by setting a “scheduled task” or “cron job” that downloads this report using a secure, authenticated URL. You can then automate the database processes that follow. We won’t be describing how to do that either, though, so if you want to know more, consult the LinkShare Signature Reporting Overview PDF linked at the end of the article.
Your system now needs one more capability that we won’t describe in detail: the ability to intelligently import the file that you download from LinkShare. Your system must be able to select just the columns that you need and ignore the others in this import. You also have to stagger your downloads from your transactions by several days for complicated reasons. LinkShare provides the data based on the “process date” which is the date on which LinkShare processed the return transaction from the merchant and added it to your available report. LinkShare may not process transactions from a given merchant for up to seven days. So you should always wait several days to get the fullest possible transaction record for a given day. If you want transactions from Monday, wait until a later date - maybe the following Monday. Additionally, there is no LinkShare internal id available to you to begin counting from (this is a poor choice, in my opinion), so you have to download from start date to end date. For this reason, you should always download reports from a process date that has already ended in LinkShare’s time zone. Waiting an extra full day between the process date and the current date is the safest option, so if you’re trying to get data for Monday’s processed transactions, download them on Wednesday by setting the begin date to Monday and the end date to Monday.
What this means is that the absolute safest way to get these transactions is to wait nine days from the original transaction date. In other words, download the report for last Monday on this Wednesday. You don’t have to wait this long and often you can get away with a shorter time from of 3-4 days (with other affiliate networks using updated technology, even sooner), but the safe option is nine days because nine days virtually guarantees that all potential data will be available. Now that we’ve cleared up the hows and whys of getting the report, let’s talk about what to do once you’ve got it.
Your system imports the relevant columns from the LinkShare report, Member ID, Order ID, Sales ($) and Commissions ($) and creates affiliate transaction records for them, storing the Member ID as the transactionid, the Order ID as the afforderid, the Sales ($) as the saleamount and the Commissions ($) as the commission. So, in your database you now have three affiliate transaction records for transactionid 321. All the data you need for proper accounting is in place and we can take a look at how it relates together to give you better accounting and better marketing intelligence.

That is the worst looking graphic I’ve made in a long time, but I think it should explain the data relationship even to the technologically challenged. Small teams of monkeys were able to trace the relationships with their banana-smeared fingers in lab experiments, so I’m confident of the results.
As you can see, the key to all your accounting data now is the transactionid. One transactionid allows you to use your system to find out that that SexyHeels6969 clicked on a link for the John Legend album at 8:55am on 01/30/2007, spent $2.97 at iTunes and earned you $.1485. Imagine the aggregate power of this small transaction set multiplied across thousands of users, hundreds of products and millions of transactions. It’s a quantum leap in reporting for the small operation and it allows you to do all kinds of new things. Building on this basic functionality, you can:

• Gather data on who your most valuable members are
• Gather data on what your best selling products/services are
• Reward individual members for their purchasing activity
• Perform A/B splits using differing links to the same product on multiple pages and judging by bottom line results
• Find what month, day and hour of the day drive the most revenue
• Stop guessing about all these things

The system described in this entry is for illustrative purposes only and is actually too simple to work well on its own, but the concepts described are the key concepts. From here you may want to support other merchants or other affiliate networks. Maybe you will want a link to correspond to a merchant and a merchant to correspond to an affiliate network so that you can run an online affiliate mall. Whatever you want to do, if you can understand the above relationships and explanations then you can understand the basics of how affiliate accounting works. Next time we’ll tackle the issues of data granularity and expanding your system to address new problems presented by this configuration.
Important Caveats to Those Trusting Too Closely in The Example

• We described no method for accounting for chargebacks, which do occur, and this varies by affiliate network. To handle this, LinkShare likes you to match up their Member ID (transactionid) with their Order ID (afforderid) when you get a second record back with a negative amount. Essentially, if a user buys a toaster and later returns it, LinkShare will issue a negative transaction for the original Order ID and when you do a sum on the member’s earnings, this will be reflected in your records.
• We only described one affiliate network here and this affiliate network handles everything simply, through querystrings and downloaded reports. It also has its own variables for referring to values. Other networks may use more (or less) and differing variables for the same and other information. Because of this, creating a truly comprehensive affiliate accounting system means writing a lot of conditional logic to massage data from multiple networks until it fits neatly into your internal accounting system.
• Other networks use web services that allow you greater flexibility, but these are much more complicated to describe. With greater freedom comes greater responsibility, so understanding the particulars of each network’s system and how not to fuck yourself using it requires careful consideration. That kind of discussion was outside the purview of this article, which is why it was not included.
• This example assumes that a system always knows who the member clicking a link is, but this may not be true depending on the architecture of your site.
• This system has the link record as the most granular level of product or service data, but this is bad form. In reality, you would want to group products under merchants and merchants under affiliate networks as well as creating meta-relationships involving categories (market verticals) and other attributes common across your target marketing (artist for music, director for movies, etc). The more information you put in, the more information you can get out.
• 42.