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.
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.
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.
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.
Checking for Returns and Cancelled Orders
I will send you a spreadsheet with two tabs (returns and cancelled).
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.