Sanity check for adding customer (sales) data to database with donations data

Question

I am importing a large amount of customer data for a charity's shop into CiviCRM. Of course we would like to be able to use this data alongside existing data from donors and contributors.

I have made a new Financial Type and a new Financial Account to hold this data and keep it separate from the donation data. Is the best thing to do now to make a new Price Set which includes all of the items in the store? Is there anything I should know before I do this to avoid polluting the dataset? Should I process each Sale in the same way as a Contribution? I am worried this will pollute overviews of the dataset by inflating the amount of money the charity appears to have at any one time, given that the actual assets for the sales transaction is equal to (sale value minus item cost).

I should clarify that I am a software engineer not an accountant and I have no idea if this is a reasonable concern or not.

Edit: The shop is based in BigCartel. I am building a script which imports historic sales data and will also function as a webhook (e.g. IPN) for future sales.

They have 60 products in 14 categories. Each product has different types, e.g. 'small t-shirt', 'book with £5 donation', etc. Fairly standard charity gift shop fair -- t-shirts, books, stickers, etc. My understanding is this system should accurately reflect financial data but isn't being used to keep inventory. The purpose of the integration is mainly to increase demographics and sales monitoring to tie the shop data into CiviCRM with data from other sources (e.g. newsletter subscriptions, social networking, etc.).

I'm not sure if I should then make a new price set for each category and map the data that way. One thing I would like to do when I make a new Price Set is to say that the price set is used for "Product Sales", not "Contributions", "Events", "Memberships", etc. Though the business logic is probably similar, it seems that a product sale is inherently a different kind of event to a donation. I can't seem to figure out how I'd do that without making a new module though...

Answer 1

@DanielJames

Thanks for your clarification. Since you will be importing historical data and using an IPN to capture future sales information, I don't think you need to use price sets at all.

FINANCIAL TYPES:

Again, you should touch base with the powers that be to understand what level of detail they wish to preserve in Civi, but my guess is that at the very least you will want to create financial types/accounts for each of the 14 categories (my OCD would prompt me to create a financial type/account for each type of product - e.g. financial type = T-shirts, different sizes should be handled as custom data if the client is interested in preserving that detail).

You can build your import script/hook to create both financial types/accounts if they do not already exist, and since people are receiving goods on behalf of their purchases, those financial types should be considered non-deductible (the exception would be the additional donation amounts that donors can contribute with their item purchase - that, of course, should be considered deductible).

I am assuming that your hook would include a verification to check for existing contacts and if no matching contact is found, create a new one - you would do the same for categories/products that may be added directly via BigCartel.

To facilitate reporting and differentiate between contributions/donations originating from BigCartel from other contributions/donations recorded directly in Civi, you can do so in several ways - the first two that come to mind:

1. Include a custom data field (e.g. a Y/N flag for "BigCartel?")
2. Include a convention on the financial types that can then be used for multiple filtration purposes (e.g. all BigCartel financial types should begin with BC---, a custom extension would prevent them from being available in the drop down when recording contributions directly in Civi, add filters in reports/searches to group all financial types by whatever precedes the three dashes, etc.)

How much effort you put into the differentiation will depend on what your clients' processes will be for recording the information from BigCartel moving forward - i.e. will they be transferring all financial information from Civi to their G/L system, or will they continue to take contribution information from both Civi and BigCartel (which is what I assume they are currently doing)? If it is the former, then you will definitely need to sit down with someone to understand how the data needs to be structured so that they are able to extract that information in a relatively easy manner.

HISTORICAL & LINE ITEM DATA:

I am assuming you are writing an import script for historical data instead of using the built-in contribution import wizard because you want to preserve the line item detail of each contribution (e.g. if more than one type of item was purchased) - the function would be the same in your hook and I believe you can make use of APIs or hooks that already exist, but here you would be more well versed than I and you probably would know where to look for the information at the following link: https://wiki.civicrm.org/confluence/display/CRMDOC/Develop

Hope this helps! Tamar

Answer 2

Thought of a few more important things:

1. I would map the BigCartel transaction ID to invoice ID and the payment processor transaction ID to the transaction ID field in Civi - this will preserve consistency of data and facilitate reconciliation
2. Sometimes communications get disrupted and your hook might miss a few transactions unless you add some kind of a loop based on BigCartel transaction ID and amount, e.g.: every time the hook is triggered, check that for every BigCartel transaction ID and amount there is a corresponding Contribution ID - to limit the amount of data that this query reviews, you can limit the verification to something like the past 30 days
3. You also need to determine how you will handle refunded/cancelled transactions, e.g: create a separate negative contribution that should have its own unique payment processor transaction ID (probably the easiest option), or update existing contribution ID to a status of refunded of the refund amount is for the full amount of the contribution (in which case you need to be sure the script will not get confused during step #2)
4. If a transaction is missed and it is older than 30 days or if a contribution needs to be edited for some reason (e.g. If pending pay later is offered as an option for payment), then you will need to make the financial types available for staff to record the contribution manually via the back office, otherwise, upon clicking edit, the financial type might be wiped and upon clicking save the system will require that they select a financial type

Ok, I think that is really it for now - good luck!