Background

The Folk Project was an early adopter of PayPal, which was championed by then webmaster George Otto many moons ago. In fact, George's name is still on the PayPal account as the "owner." Changing ownership of a PayPal account is a non-trivial undertaking that involves photo IDs, notarized paperwork and so forth. It's been easier to let the ownership question slide, since we have multiple logins with an equivalent level of access. George is no longer involved with PayPal management, but as of this writing (May 2018) is the Project's newsletter editor.

Initially, we used PayPal to make it easier for people to renew their memberships without putting a check in the mail. It was then expanded to support Festival (now Getaway) and collect admissions revenue for Special Concerts. It has never been used for Merchandising, although it could be. If a Merchandising chair wanted to set up a virtual boutique at folkproject.org, PayPal could support it.

The Basics

PayPal is available 24 x 7, humming along in the background. With a combination of email communications and on-site links, Folk Project customers are directed to forms that support individual activities. Many of these forms have been built in Constant Contact, which integrates with PayPal (at least to a degree). Some of the older forms were hand-coded.

Generally on the first of each month, the Treasurer logs into PayPal to review the previous month's activity. By analyzing PayPal transactions, the Treasurer can determine the revenue and PayPal fees that should accrue to each venue. This is called a "sweep." The Treasurer then creates invoice transactions in QuickBooks for each venue that is showing PayPal activity in the given period (posting the total revenue earned and deducting the PayPal fee). The total of these invoices is then transferred from the PayPal account to the Folk Project's checking account, a process that takes one business day.

Pros and Cons

PayPal is a well-respected brand and gets the job done. People can pay with the credit card of their choice, and refunds can be issued directly through PayPal up to 180 days after the date of the original transaction. For our purposes, though, it is surprisingly primitive in terms of reporting. That means an additional step is necessary to go from raw PayPal transaction logs to final QuickBooks invoices.

Each PayPal transaction can be considered in two parts. An initial set of data is captured when the transaction is created in a user's Shopping Cart, and a second transaction is generated when it's finalized. It's in this second transaction that PayPal fees are applied, so that's the one we need to capture. However, some of the Shopping Cart details are dropped from the final transaction, just because they are.

Each transaction has a "notify" email, and that's been the only workable way to sort transactions by department. In some cases, it's easy. The email membership@folkproject.org is always Dues income, for example. Because the Getaways are six months apart, revenue for FestReg@folkproject.org can be pretty easily associated with the appropriate weekend. The most complex situation occurs with sctickets@folkproject.org, which is used by Special Concerts. That's because multiple concerts may be in play in any given reporting period. The concert name isn't carried through to the final transaction record, and therein lies the rub.

Step by Step

The PayPal sweep procedure can only be considered a workaround, but it's the best we've been able to develop. Here it is:

1. Log in to PayPal and click "More" in the upper right corner of the Landing Page. That leads to the Activity Summary screen. In the upper right, click on "Download."

2. Define the download report appropriately (first day of the previous month to the last day, tab delimited format, all transactions) and click "Create Report." It sometimes takes a few minutes before the report is ready for downloading.

3. Open the latest version of the PayPal Sweeps sheet (sample attached below) and add a new tab. Copy the data from a previous month and paste to the new tab. Change the tab label at the bottom.

4. Still in Excel, open the downloaded text file (sample attached below), copy all the transaction details and paste them into the new tab, overwriting the old data but preserving the headers. Let Excel convert the tab-delimited format into Excel rows and columns.

5. Delete cells from the downloaded data until you bring the basic columns of information into line with the column headers. There is detail you may need on the far right, so don't delete that area yet.

6. Sort the data on the PayPal Net column, largest first. This will bring all the final transactions (the ones that have associated PayPal fees) into the top of the sheet.

7. Sort this final transaction range by the notify email address. That will establish the first level of macro-aggregation, and in some months, you can stop here. If there's no Special Concert or Getaway activity, that's that.

8. If you do need to sort out money for multiple concerts, you may need to look at the Shopping Cart transactions at the bottom of the sheet. Sorting this second range by user name makes that a little easier. Look at each person who purchased tickets in the sctickets@folkproject.org section in the final transaction range, and then find their name in the shopping cart range. Review that record and annotate the final transaction appropriately. One approach is to replace the notify email with something like "Dues" or "Concerts\Doolin" so the final sweep file doesn't show email addresses. However, note that the data to the far right of the sheet may also contain identifying information. Or not. It depends on how the transaction capture forms were coded, and since more than one person is involved, you can't expect too much consistency.

9. Once the final transaction range is fully categorized, sort again on the category field. Subtotal, adding sums for Gross, Fee and Net. Save.

10. Go to QuickBooks and create the appropriate transactions. These are department level "invoices" by which we charge PayPal for the money we've collected. They should be dated for the last day of the previous month, and assigned to a department (Concerts) and possibly a class (Doolin).

The grand total for the month shown in the Sweeps sheet is the amount of money that should be transferred to the checking account. You do that from the PayPal home page, under Transfer funds at the upper left. The checking account information is already loaded as part of The Folk Project profile.

Before you initiate the transfer, be sure to check that the total of the QuickBooks invoices you've created is the same as the total of the funds you're about to transfer.