This article will cover:
- What Is The CSV Reconciliation Tool?
- Accessing The CSV Reconciliation Tool
- Step 1: Choose Your File Template
- Step 2: Choose To Exclude (Billed) Conversions
- Step 3: Choose An Effective Date
- Step 4: Choose A Disposition
- Step 5: Change Payouts/Revenue
- Step 6: Choose Unpaid Disposition Settings
- Step 7: Uploading The CSV File
- Step 8: Review Any Error Messages
What Is The CSV Reconciliation Tool?
The CSV Reconciliation Tool is used to process conversion price and/or disposition updates en masse. While it is possible to update individual conversions or update multiple conversions from a single campaign via the Mass Adjustment Tool, this tool allows for a group of handpicked conversions across any campaign to be updated by importing a CSV file.
Note:
In order to access this tool, it must first be enabled in your instance. Please reach out to your dedicated CAKE Client Success Manager or CAKE Support ([email protected]) for more information.
Accessing The CSV Reconciliation Tool
Once enabled, the CSV Conversion Reconciliation Tool will be located under the Tools main tab.
Note:
If the tool has been enabled but you do not see the Tools main tab in your view, be sure to check your permission settings.
Step 1: Choose Your File Template
The CSV Reconciliation Tool allows you to make two types of adjustments: Payout/Revenue (and optionally Disposition changes) or Disposition changes only. Each template requires a different set of headers. The following is a breakdown of the template headers and their expected values:
- Conversion ID- CAKE's numeric ID of the conversion record.
- Option 1: leave column blank to have the tool search by Transaction ID inputs
- Transaction ID- The value passed in the t= parameter on the conversion postback/pixel.
- Option 2: leave column blank to have the tool search by Conversion ID inputs
- Disposition ID - Dropdown list of IDs is presented in the tool. See Step 4 for more details.
- Offer ID - CAKE's numeric ID attributed to the conversion record.
- Payout - New Affiliate payout for the conversion record.
- Add to Existing Payout - Pass "true" to add the amount above to the original payout amount or pass "false" to replace the original payout amount with the amount passed above.
- Update Revshare Payout - If you want the payout amounts on adjusted RevShare conversions to dynamically recalculate based on new received amounts, pass "true." Otherwise pass "false" to not change the original payout amount on RevShare conversions.
- Received - New received amount or percentage for the conversion record. See Step 5 for more details.
- Unpaid Dispostion ID - Dropdown list of IDs is presented in the tool. See Step 6 for more details.
- Custom Date - Pass dd/mm/yyyy hh/mm/ss format. See Step 3 for more details.
Template for Disposition Changes (template file attached at bottom of article):
- Conversion ID - CAKE's numeric ID of the conversion record.
- Transaction ID - The value passed in the t= parameter on the conversion postback/pixel.
- Disposition ID - Dropdown list of IDs is presented in the tool.
- Offer ID - CAKE's numeric ID attributed to the conversion record.
- Custom Date - Pass dd/mm/yyyy hh/mm/ss format.
Note:
Once you've chosen your file and filled it out, save it to your computer for Step 7.
Step 2: Choose To Exclude (Billed) Conversions
When adjusting conversions, you may want to take into consideration whether the conversions have already been billed (aka marked as paid in the Accounting section). The top three options force the tool to skip over/not adjust any conversions that meet the criteria. The last option will force the tool to make the adjustment, regardless of the conversion's billing statuses.
Step 3: Choose An Effective Date
When adjusting conversions, you will need to choose an Effective Date for when the adjustment should be reflected. The three options are Today, Conversion Date, or Custom.
- Today - This should be used if you wish to apply the payout/disposition changes immediately/to your current billing cycle.
- Conversion Date - This should be used if you wish to apply the payout/disposition changes to the same date that each conversion occurred. This could include previous billing cycles.
- Custom Date - This should be used if you wish will apply the payout/disposition changes to any date between the original conversion date and today's date. A custom date cannot be a date before the original conversion date or in the future. If chosen, the custom date will be taken from the CSV file.
Note:
Be mindful that the date you choose impacts billing. For instance, if "today" is not in the same billing period as the original conversion date, any Revenue or Cost being adjusted will affect the current billing cycle.
Step 4: Choose A Disposition
This tool requires you to choose a disposition that will be applied during the mass adjustment. You can update the conversion’s disposition with any of the last four options or force the tool to skip over/leave the existing disposition in place by choosing No Change.
Note:
By default, every successfully tracked conversion in CAKE is automatically assigned the disposition of Approved. This default can be changed by leveraging our Pending Conversions feature.
Step 5: Change Payouts/Revenue
If you are only making Disposition updates, leave the Change Payouts/Revenue box unchecked and move on to Step 7. However, if you are adjusting payouts and/or revenue, start by checking this box. Payouts will be adjusted via the CSV file using the Payout field and Add to Existing field. To adjust revenue, choose one of the following adjustment options:
- Total Sale Amount - This should be used when adjusting RevShare conversions. The expected parameter is a percentage and will be multiplied against the original received amount. For example, if the price received was $5.00 and the value passed is 80, the new received amount for that conversion is $4.00.
- Additional Sale Amount - This should be used when adjusting RevShare conversions. The expected parameter is a percentage and will be multiplied then added to the original received amount. For example, if the price received was $50.00 and the value passed is 10, the new received amount for that conversion is $55.00.
- Total Revenue - This should be used when adjusting CPA, CPC, CPM, and Fixed conversions. The expected parameter is a dollar amount and will replace the original received amount. For example, if the price received was $10.00 and the value passed is $12.50, the new received amount for that conversion is $12.50.
- Additional Revenue - This should be used when adjusting CPA, CPC, CPM, and Fixed conversions. The expected parameter is a dollar amount and will be added to the original received amount. For example, if the price received was $10.00 and the value passed is $2.50, the new received amount for that conversion is $12.50.
- No Change - This should be used when you do not want to make any changes to the received amounts of the conversions.
Step 6: Choose Unpaid Disposition Settings
Unpaid Dispositions control the visibility of $0 conversions in the Affiliate Portal. As the helper text in the tool states, any conversions that are adjusted to $0 that have an Unpaid Disposition other than 0 will not be shown in the Affiliate Portal. Conversely, any conversions adjusted to $0 with an Unpaid Disposition of 0 will be displayed in the Affiliate portal.
The list of available Unpaid Disposition IDs can be populated into the tool by clicking Get Unpaid Disposition IDs.
Step 7: Uploading The CSV File
You may either upload a CSV file, or you can manually enter each line in the tool for each conversion you would like to change. To upload a file saved locally on your computer, follow these steps:
- Click Choose File
- Browse your computer and locate the name of your saved .csv file
- Click Open to upload it
- Validate the data from the file uploaded into the Data field
- Optionally, add a note to append with each adjusted conversion
- Click Save to submit
- Scroll down to review the Results Log
- Optionally, click Export to CSV if you would like to review the entire Results Log in a .csv file
Step 8: Review Any Error Messages
This tool can return numerous types of error messages. Some of the more common errors are:
- 500 - Internal Server Error - This will typically occur if the file is improperly formatted. Please be sure to review your file has all the required headers as well as valid IDs.
- Conversion Not Found - The numeric ID passed does not exist as a valid CAKE conversion ID or valid CAKE Offer ID. Or, the Offer ID is valid but not the ID attributed to this conversion.
- Duplicate Transaction IDs - The Transaction ID passed is associated with multiple conversions and therefore the tool does not know which one to adjust. Instead of using Transaction ID, pass the specific CAKE Conversion ID for the record you want adjusted or adjust manually.
FAQ
Can I add conversions with this tool?
No, this tool is used to adjust existing conversions.