How Affiliates Can Use Merchant Order Numbers to Their Advantage

My posts may include paid links for which I earn a commission.

As an affiliate you have so much data available to you that you are not likely using. While doing some research for Wine Club Group today, I was messaging Eric with a bunch of numbers and we were discussing the significance of those numbers. All of a sudden he said “that’s blog post content.” He was right. This is the kind of thing most affiliates would never even consider compiling and analyzing and yet it is right in front of you!

Who Does It Work For?

This data works best when you have fairly regular sales over a set period of time. The number of sales necessary depends on how much volume the merchant does. In our case, only a couple of sales a week per merchant during their “off season” was enough to give us some good metrics.

Compiling the Data

I used ShareASale for this, but you can use any network data where you are provided order numbers as part of your reports (which should be almost all of them).

I chose a time period that best represented the season. I choose the off season for wine clubs, but I could do the same analysis (and likely will) for the holiday season and then compare my results against each other. You could even do another report for the entire year and see if your data is significantly different comparing the time periods to the whole year. That’s another completely separate but important analysis!

For that time period, I ran reports for the 4 merchants we had the most sales for. I pulled a simple CSV Activity Details Report and and popped it into Excel. I then converted the text to columns (in ShareASale you need to use | as your delimiter). I stripped out everything except for 3 columns: Merchant, Transaction Date, and Comment (which held the order ID).

Next I sorted my columns by Merchant and then Transaction Date. I cleaned up the data a bit by removing bonuses so that only my sales would remain.

Analyzing the Data

The next big step is to use the order numbers to figure out how many sales each merchant had during the time period. You can either figure these manually or further use the Text to Columns feature to break out the order numbers and add formulas to subtract them out. It was faster for me just to subtract them out. So figured out how many sales each of the 4 merchants had in the 3 month period I was using.

For my data, there was a HUGE difference in the number of sales for each merchant. They ranged from 324 up to 18987. This tells me proportionately how big the market share is for each merchant in the niche, at least in terms of the NUMBER of sales.

Next, I counted up how many sales I had for each merchant and divided that out to figure out what portion of that merchant’s total sales I accounted for. Comparing those numbers, I was able to easily see that our sales were nothing but a drop in the bucket for a couple of merchants but a nice percentage of sales for the other merchants.

I made up some numbers so you can see it a bit more clearly. All you need to know comes from this small chart. You can see the big differences in a couple of the merchants and the HUGE difference in how I compare across the merchants.

Note: This doesn’t tell you where you rank in the affiliate program because you don’t know for sure how many sales are coming through the affiliate program. It may be that you are 19.75% of their overall sales and thus 50% of their total affiliate sales because their affiliate sales make up the majority of their overall sales. Conversely, you may only be .08% of their total sales but 20% of their affiliate sales because their other sales channels make up the majority of their sales.

Using the Data

So what do I do with this data?

First, as I mentioned before, you would want to compare different periods. I want to know if the market share shifts across the merchants during peak shopping and non-peak shopping. That might tell me where I want to better focus my sales during those times.

Second, I now know better how valuable I am to a merchant. If you figure out that you are sending a merchant 5-10% of their TOTAL sales in a year (or even a quarter), you are valuable to that merchant. You can now go into negotiations with their affiliate manager showing them these numbers and asking how you might be able to work more closely together.

If you are an affiliate manager and the merchant hasn’t shared with you what percentage of their sales come through the affiliate channel, you can use this same method and gauge how important your program is to their overall sales methods.

Third, I can see opportunities. If I know that I am only sending .01% of a merchant’s total sales, I have to wonder where I am missing the chance to send more.  Clearly the merchant is doing well, and I want to be a bigger part of that. I need to do more research and talk to their affiliate manager to find out what I can do to make that happen.

Those are just some of the things that you can do from running a simple report of merchants, dates, and order numbers. There are obviously a few caveats if a merchant has some kind of really funky order number system where they do not go sequentially. And for some types of merchants (like shoes), reversals might come into play. But it’s a good starting point when you want to compare merchants in the same niche.

Do you use data in this way as an affiliate or as an affiliate manager? Can you think of other ways that it would be valuable?