Reconciliation Process

Reconciliations have to be completed by the 10th each month. The objective is to ensure commissions are not paid on orders that were cancelled. The majority of this work has to be completed in-house. I would however like to train you to extended orders in commission junction, complete v-lookups in spreadsheets to find cancelled and returned orders, and adjust the orders in commission junction.


Extending Orders in Commission Junction

Click on Reports, Transaction, Commission Detail, Event Date and Last Month.


extend orders 1

In the Commission Details section change the drop down to view 200 orders at a time, take note of how many pages result (8 in this example), check the box on the first row and first column to check each order. After there is a check next to each of the 200 orders listed, click extend. Complete this action for every page of orders. In this example we just extended the orders for page 1 of 8.

extend orders 2



Export Orders from Commission Junction

After you’ve extended the orders for the previous month. We need to export the orders for the month before that. E.G. If you just extended February orders, you will export January orders. It is currently, November 10th, so I am exporting September orders. The process is the same as extending orders except you are selecting a different month and instead of extend you hit the download button.


exporting orders


After you have the spreadsheet, please run a sort and remove any affiliates on a CPC model. That action locks right away and can be removed for this task. Sort the Order ID column to put all the rows starting with PPC Count together. Delete those rows.


reconciliation 6


reconciliation 7


reconciliation 8


Checking for Returns and Cancelled Orders

I will send you a spreadsheet with two tabs (returns and cancelled).


reconciliation 5

Paste the CJ order numbers in the 2nd column in both these tabs. Enter the vlookup formula in the third column. It is =vlookup(select order id from the second column, highlight the entire 1 column of orders for table array, put $ signs in front of the row numbers (2 & 2528 in this example, select column 1, and false. Here is what it looks like for this months spreadsheet. The ones where an order id appeared need to be investigated.


vlookup 3

Methods to Search for Affiliates

One of the methods we use to search for new affiliates is to use the Google keyword tool. It will provide you with an idea of how much search volume there is for a term or phrase and related ways people search.

Steps to find the keyword tool

1. Go to

2. Login – it is free to create an account if you don’t have one

3. Hover over tools

4. Click on keyword planner


keyword planner 1

The first box (Search for new keyword and ad group ideas) will provide you with related search keywords and phrases. Use the second box (Get search volume for a list of keywords or group them into ad groups) to find an estimate of the search volume for a specific keyword or phrase.


keyword planner 2We will mostly use the first box to find related searches. In this example, I searched for “wine refrigerators” and sorted by keyword ideas. I usually leave the list sorted by relevance. If you sort by volume, the searches become less related. Use these searches in Google to find people that would be good affiliates. Searches using best, top, reviews are normally our prime targets. Searches related to products or categories are more likely to result in competitors who will not become affiliates.


keyword planner 3

When you search Google, make sure you are not logged in and change your location to United States.


keyword planner 4

We normally only go two pages deep in the search results. – never a good target. – competitor – we have the top spot – he briefly worked with us we need to target again. this is what an affiliate site often looks like. he is a prime target. – our own site – our own site – our own site – competitor -interesting. note the author and enter in buzzstream – competitor – different marketing channel – good target. authors can work with us. we need better placement. – perfect target. we need placement can see from the buy call to actions that they work with the amazon affiliate program. – competitor – competitorĀ  competitor – interesting note the author – they are part of a bigger company. there is a chance but it is slim. – he is a target. he currently uses just the amazong program. – competitor


I highlighted the links that have some potential. Cross reference these sites against buzzstream. Remove the ones that already have an affiliate tag. Add the others as affiliate leads. Try your best to find contact emails. Repeat the process with the next search phrase provided by the keyword tool. Send me a spreadsheet at the end of the day with the targets you like and their contact information.

keyword planner 5