The Three Simple Spreadsheets I Use to Track My Affiliate Links, and the Few Numbers I Check Each Week

If you want to know how to track affiliate conversions, start with one rule: every link you publish gets a row in a sheet, and every week gets a row of numbers. Three spreadsheet tabs cover it: a link log, a weekly numbers sheet and a decision tab. None of them needs more than a free spreadsheet app and a few formulas.

The columns below are the ones that earn their place. Every extra column is one more thing to fill in on a tired Friday, and a sheet you skip is worth nothing.

Why a sheet beats memory

Memory keeps the sales you happened to notice and drops the rest. A post that earned a small commission every week for two months is easy to forget, while one big sale on a different post is easy to overrate.

A sheet gives you the same view every week. It shows which posts get read, which links get clicked, and which clicks end in a purchase. Those are three separate questions, and each one points to a different fix.

It also stops you from guessing what to write next. When you can see that comparison posts earn and listicles don’t, the next topic is a decision rather than a hunch.

Sheet one: the link log

The link log is a plain inventory with one row per link. Its job is to answer “what have I published, and where?” so you never have to open old posts to find out.

The columns worth keeping:

• Link ID: a short label you make up, such as P01-L1 for the first link in the first post. You will use it to match rows across sheets.
• Post title and post URL: where the link lives.
• Program and product: which affiliate program it belongs to and what it points at.
• Tracking ID or link address: the exact link you pasted. If the program lets you add a sub ID or campaign tag, give each post its own, so the program’s dashboard reports per post instead of in one lump.
• Placement: intro, middle, button, or end. This lets you compare positions later.
• Date added: so you know how long a link has had to perform.
• Last checked and status: live, broken or removed. Dead links earn nothing and are fixed in seconds once you know about them.

If your affiliate program cannot separate clicks by post, there is a workaround. Create a distinct link for each post wherever the program allows it, such as a different campaign tag. If it does not allow that, track the program as a whole and compare posts by the page views and outbound clicks in your site analytics.

Sheet two: the weekly numbers

The weekly sheet is where the data goes. Each week you add one row per post, so the sheet grows downward and keeps its history.

Columns:

• Week starting: the Monday date, so every row is dated the same way.
• Post: the title, or the Link ID if a post has several links.
• Page views: from your site analytics.
• Link clicks: from the affiliate dashboard, or from your analytics if you track outbound clicks.
• Sales: the number of purchases the program credits to that post.
• Commission: the amount credited, in your currency.
• Click rate: clicks divided by page views.
• Conversion rate: sales divided by clicks.
• Earnings per click: commission divided by clicks.

The last three columns are formulas, so you never type them. In most spreadsheet apps the conversion rate is =IF(D2=0,0,E2/D2), which returns zero instead of an error when there were no clicks. Adjust the letters to match where your clicks and sales columns sit.

Many programs report sales as pending before they are confirmed, and some reverse them later. Add one more column called Status with the values pending or confirmed, and update old rows when the program finalises them. Counting pending commission as income overstates what you earned.

Sheet three: the decision tab

The decision tab has one row per post, and it is where the weekly numbers become an action. It pulls totals from sheet two with SUMIF formulas: total views, total clicks, total sales and total commission for each post.

Then add four columns you fill in by hand:

• Weeks of data: how many weeks of rows the post has.
• Decision: keep, fix or drop.
• Reason: one sentence, such as “plenty of views, almost no clicks.”
• Next action and review date: what you will change and when you will look again.

The decision rules are short enough to write at the top of the tab:

1. Wait for enough data. Do not decide on a post until it has at least four weeks of rows and a number of views you consider meaningful for your site. Choose that threshold once and stick to it.
2. Keep a post that produces sales. Leave the content alone, and consider writing more on the same topic.
3. Fix a post when the numbers point to a specific stage. Plenty of views but few clicks means the link is hard to see or the pitch is weak. Plenty of clicks but no sales means the offer does not match what the reader expected, or the program’s page loses them.
4. Drop a post only after you have fixed it once and given the fix the same number of weeks. If it still earns nothing, stop spending time on it. Dropping means removing it from your weekly attention, not necessarily deleting the page.
5. Treat low views as a separate problem. A post nobody reads cannot be judged on its links. The fix there is traffic, not wording.

The mistakes audit covers what to look for when a post lands in the fix column. This post only covers the record that tells you which posts belong there.

What your first few weeks of numbers will look like

Here is an invented example, for illustration only, so you can see how the formulas behave. Suppose one post got 200 page views in a week, 30 link clicks and 1 sale worth a $12 commission.

• Click rate: 30 divided by 200 is 15 percent.
• Conversion rate: 1 divided by 30 is about 3.3 percent.
• Earnings per click: $12 divided by 30 is 40 cents.

Notice how fragile the conversion rate is at this size. One more sale would double it, and one fewer would drop it to zero. That is why the decision tab waits for several weeks of data. Early rows are for building the habit, and the pattern only shows up after a month or so.

Your own figures will differ, and the sheet is the only way to learn what normal looks like for your site. When you change something on a post, change one thing at a time, as in the split-test write-up. Otherwise the sheet shows a movement you cannot explain.

The 15-minute Friday review

Run the review at the same time every week so it becomes routine. The timings below add up to fifteen minutes.

1. Pull the numbers (3 minutes). Open the affiliate dashboard and your site analytics. Set both to the same Monday-to-Sunday range.
2. Enter them in sheet two (4 minutes). Add one row per post with the week date, views, clicks, sales and commission. The formula columns fill themselves.
3. Update the old rows (2 minutes). Check last week’s and the previous weeks’ pending sales. Change any that have been confirmed or reversed.
4. Check the link log (2 minutes). Click through the links on any post you edited this week, and mark any broken link in the status column.
5. Update the decision tab (3 minutes). Look at posts that have reached four weeks of data and apply the rules. Write the reason and the next action in a sentence.
6. Pick one thing for next week (1 minute). Choose either a post to fix or a topic to write, based on what the tab shows.

Notice that the review never asks you to analyse anything at length. If a post needs more thought, write “look closer” in the next action column and deal with it outside the fifteen minutes.

What the sheets can’t tell you

A tracking sheet records what happened and says nothing about why. A post with few clicks might have a buried link, a weak topic or a reader who was never the buyer. The sheet narrows the list of causes and you still have to test them.

It also misses some sales. A reader may click on their phone and buy later on a laptop, or return after the program’s tracking window closes, and the sale is credited to nobody. Treat your totals as a floor, not an exact count.

Some metrics are worth ignoring on purpose because they do not change a decision:

• Total page views on their own. A post with thousands of views and no clicks is a fix, not a success.
• Social likes and follower counts. They say little about whether a reader buys.
• Pending commission counted as income. Wait until it is confirmed.
• Daily numbers. A single day is too small a sample, and checking it every morning wastes time. The weekly row is enough.
• Conversion rates from a handful of clicks. Wait until there are enough clicks for one sale to stop swinging the figure.

Keep the sheet limited to the columns above. If you find yourself wanting a metric, ask what you would do differently if it were high or low. If the answer is nothing, leave it out.

How to set up your own version today

Start with one post, not your whole site. A single post is enough to get the structure right, and you can add the others one at a time.

1. Create a spreadsheet file with three tabs named Link Log, Weekly Numbers and Decisions.
2. In the Link Log, add one row for each link in your most-visited post. Give each a Link ID, and add a tracking ID wherever the program allows it.
3. In Weekly Numbers, add the column headings from sheet two and enter this week’s views, clicks, sales and commission for that post. Add the three formula columns.
4. In Decisions, add the post, the SUMIF totals and the empty decision columns. Type the five rules at the top of the tab so you do not have to remember them.
5. Put a recurring Friday reminder in your calendar for fifteen minutes.
6. Add your remaining posts to the three tabs over the next few weeks, newest and most-visited first.

By the fourth Friday, the Decisions tab will start showing which post to keep, which to fix and which to leave alone, and you can choose your next post from your own numbers.