Crimson: WinRed Best Practices

Below are the recommended best practices for prepping and loading WinRed payments and attributions into Crimson. Note: Crimson offers a premium feature to automatically load the previous days WinRed gifts every morning. Please reach out to Crimson support or your account rep if you are interested.

Step 1: Formatting the WinRed Payout file

Step 2: Creating the WinRed entry in Crimson

Step 3: Import the WinRed memo records

Step 4: Format the Negative tab

Step 1: Formatting the WinRed Payout file

1. Open the WinRed payout file in excel.
2. Determine if you have a “Source Code” column.
   • If yes, sort by that column to confirm a value is in every cell and confirm they are the ones you want to use in Crimson.
   • Source codes need to already exist in Crimson before you attempt the load. If you do not have a source code column, create one and add a value to every cell.
3. Sort the spreadsheet by “Gross Amount”.
4. Delete any rows that have an amount of 0. These are chargeback fees and do not get loaded.
5. Create a new tab in the excel sheet and cut/paste any negative values to the new tab. Be sure to copy your header row. We will come back to this tab in Step 4.
   • Pro Tip: Make a WinRed support request to include “id” in your Payout exports if it is not included already. This will be useful later, again in Step 4.
6. Delete the “agency” columns at the far right of the spreadsheet. These tend to have duplicate column names and cause an error.
7. Confirm your spreadsheet does not have any other duplicate column names, like “Source Code”.
8. You should now have a file with a tab of positive WinRed gifts and a tab of negative chargebacks/refunds. Save this file as an .XLSX file type.

Step 2: Creating the WinRed entry in Crimson

1. Locate the WinRed record in Crimson using the people search or create one if needed.
2. From the WinRed record, add a new gift where the batch date is your transfer date, the amount is the total of the gross contributions in the positive WinRed tab, and the payment type is “WR”.
3. You will need to assign a batch number and source code as well.
4. Save the record.

Step 3: Import the WinRed memo records

1. Open the WinRed money record created in Step 2.
2. Click “WinRed Earmark Import” tab towards the top of the money record.
   
3. Choose/drag the saved formatted WinRed payout XLSX file. Select the correct tab from the worksheet list.
4. In the Import type step, enter the number of gifts and the total amount in the file.
   • Note: you can import up to 2500 records at a time. For files larger than 2500 records, you will need to split the file up into separate tabs and repeat this step.
5. Proceed to the mapping. If this is your first time importing from this file format, you will need to create a mapping. Map the process date as the adjustment date, source code, and all the name, address, and contact info fields. Note: If WinRed is not including phone/email in the payout file, request it be added.
6. VERY IMPORTANT: Map the “id” field in your WinRed file to the CC TRAN ID field in Crimson. This field will have a value that starts with rv_ch or rv_ccc. This is the field we use to match the refund file to. If it does not exist in your payout file, ask WinRed to add it.
7. After mapping everything needed, save your mapping to speed up this process for the next import.
8. Finalize the import in the validation/import step, correcting any errors that may prevent it from completing.

NOTE: Gifts loaded this way will link to the conduit WinRed record and automatically have the proper FEC memo text. They will also report at a 1:Many ratio, showing one WinRed record for its corresponding Earmarked gifts.

Step 4: Format the Negative tab

After all the positive memo records have been loaded, it is time to enter the negatives.

1. Open the negative tab in your excel spreadsheet. You only need three columns for this file
   • The “id” field, the same one you mapped to “CC TRAN ID” in Crimson
   • The amount
   • The date you want the adjustments to get in Crimson
     • Note: We recommend using the payout date for all adjustments. This makes it easier to tie the transactions back to the specific payout file.

2. The amount column needs to be formatted so it does not contain any special characters (commas, parenthesis, dollar signs, etc.) Note: The Mass Adjustment Tool will successfully match gifts if the amount you use is either negative or positive.

Step 5: Process the negative tab

1. Navigate to the Fundraising dashboard in Crimson and locate the Mass Adjustment tool from the dashboard navigation bar in the top right.
2. Choose or drag your file and select the negative tab in the worksheet.
3. In the next step you will map your columns. Map the “id” field to “Credit Card Transaction ID”, the date field to “Adjustment Date”, and the amount to the “Gift Amount” field. You do not need to map anything to MID since we are matching to CC Tran ID.
   
4. Continuing to the next step, Crimson will now attempt to match the CC Tran IDs in the file to the CC Tran IDs in the database.
   • Matched – The first tab will present you with any confirmed matches.
     1. You will be asked if you want these treated as Chargebacks or Refunds.
        • Chargebacks are reported as negatives on Schedule A
        • Refunds only mark these gifts as refunded in Crimson. You will still need to enter these as refunds on Schedule B to balance your cash on your FEC report.
     2. After you have confirmed which adjustment type you want, hit Process and the records will be adjusted.
   • Already Adjusted – You may also have records that are in the already adjusted. Already adjusted means they have already been processed for adjustments. You will want to review to confirm.
   • Not Matched – Not matched means either the CC TRAN ID does not exist in Crimson or the Amount does not match.
     • If the amount does not match, you will need to manually adjust the record in Crimson.
     • If the CC TRAN ID does not exist, it could be one of two things: the original transaction was imported without a CC Tran ID and will need to be manually adjusted or the original transaction has not been included in a WinRed payout file yet. This happens occasionally where the refund clears before the original transaction. You will need to put these records aside and attempt to match them again after you load the next WinRed payout file you receive.