2

Our database was handled very roughly over last couple of years and tested features recklessly. Mixed events payments with contribution payments etc. Numerous price sets. Bot attack was also another problem until recaptcha implemented. We dont even know how many contact & contribution was populated by bots.

What I am trying is to export all contact data only with potential donors, contribution, and events data. Not bothered about activities. And import it onto a clean instance of Civicrm. Another concern we have is recurring paypal donations. After moving how can we keep those linked to contacts?

Any suggestion would be much appreciated. Thanks

2

Typically, bots contacts/contributions will follow some standard method of populating information - e.g. a recent bot attack on one of our clients created contacts with "%phone_short%" listed as their main phone number, so using search builder in the GUI allowed me to identify all associated spam contacts and delete them permanently.

If these spam contacts are associated with contributions, you'll need to delete the contributions before you are able to permanently delete the contacts - you can isolate those contributions by creating a group from the search builder results, then use Contributions > Find Contributions to search for and delete all contributions made by contacts in your spam group.

Depending on your payment processor, you may have contributions with a status of "Failed" - if so, you can search for all failed contributions (I would do this search using Search > Advanced Search with Display Results as "Contributions"). Review the list to see if you get any legitimately failed contributions associated with legitimate contacts - if you do, narrow the results further by entering a received date and time range since spam contributions are typically created in rapid succession. Once you've isolated the spam contributions, run the same same search, only this time with Display Results as "Contacts", then create a group of these contacts - this will allow you to isolate the spam contacts for permanent deletion after you have deleted the contributions.

With the above tips, you may be able to stick with the existing database and avoid the need to transfer recurring contributions, but if not, I would suggest creating new recurring contributions since migrating existing ones could prove to be very time consuming and complicated given the many different tables in which contribution information is stored, e.g.:

  1. Records in civicrm_contribution_recur are associated with a specific civicrm_contact.id
  2. Records in civicrm_contribution are associated with a specific civicrm_contribution_recur.id
  3. Records in civicrm_contribution are associated with several other tables, many of which are interconnected with IDs from yet other tables - e.g.:

    3.1. civicrm_financial_type > civicrm_financial_acount > civicrm_entity_financial_acount

    3.2. civicrm_contribution.id > civicrm_line_item.id

    3.3. civicrm_line_item.id > civicrm_financial_item.id

    3.4. civicrm_entity_financial_trxn > civicrm_contribution > civicrm_financial_item.id > civicrm_financial_trxn

    3.5. civicrm_contribution_id > civicrm_contribution_page.id

    3.6. civicrm_contribution_id > civicrm_event.id > civicrm_participant.id > civicrm_participant_payment.id

Keep in mind, the above may not be an exhaustive list, and you would most definitely want to do this directly in the database so that you can preserve all of the existing IDs.

Hope this helps, Tamar

  • Thanks for your reply! I will see if I can remove the bots first. otherwise seems quiet complicated – Tapash May 16 at 20:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.