Product perspective

Context

Currently we are storing the tracking information (the Matomo campaign name and keyword from the URL at the point when a donation happened) in the serialized data property of the spenden (donations) table. This has the following drawbacks:

• It's very hard to create analysis and reports on donations in the Fundraising Operation Center, leading to a lot of code that filters donations in PHP, which is a challenge for developer understanding and has a negative impact on performance of the FOC.
• Each donation takes an additional 20-30 bytes to store.
• We can't do ad-hoc queries for donations of specific campaigns.

We can avoid those drawbacks by storing the tracking information in a dedicated database table.

When we re-wrote the Fundraising Application in 2016, we did not consider the tracking information an essential part of the donation domain, because we thought more in terms of donors and payments. Now, in 2025, we are convinced that the origin of a donation matters as much as donor and payment data and does belong in the donation domain (and bounded context).

Acceptance criteria

• All new donations store tracking data in a way that can be queried from SQL, targeting campaigns and keywords separately. This is in addition to the tracking string in the data blob (for backwards compatibility).
• The database migration copies the existing tracking data of donations to the new tracking table.

Technical perspective

Context

This change is part of a larger strategy of making the donation table more normalized and moving information out of the "data blob". See T203679: Refactor fundraising database schema for the overall database change strategy

Constraints

• To avoid a "big bang" change and keep the existing scripts running, we need store the data in two places - in the data blob until all places where the FOC uses this data are adapted and in the new separate table. When the FOC is fully adapted we can drop the storage in the data blob.

• 1. Affected repositories
• donation bounded context: adding tables and changing how donations are stored
• Fundraising Application: Integration of new releases of the bounded context, no other changes should be needed
• Fundraising Operation Center: Refactoring of all places where we iterate over donation ranges and check their tracking data.

Integration details

• We need at least two major releases, each of them changing the database in fundamental ways (adding tables, possibly dropping columns).
• We need structural migrations (adding tables)
• We need data migration scripts (copying existing tracking data from the blob into the new tables).

Subtickets / Order of implementation

1. Change Donation Bounded Context
   1. Store tracking data in new tables T336848: Change how the donation bounded context stores tracking data
   2. Create a database migration to create the tables and copy the data T336846: Migrate donation tracking data to new table
2. Adapt Fundraising Application to changes & migrate database T390996: Integrate Donation tracking changes in Fundraising Application
3. Change Fundraising Operation Center
   1. Adapt donation fixtures for tests T338509: Adapt donation fixtures for tests
   2. Change Donation export to use new tracking data table T338517: Change Donation export to use new tracking data table
   3. Refactor Analysis\DataAccess\DatabaseDonationReader T338518: Refactor Analysis\DataAccess\DatabaseDonationReader
   4. Create DonationReportFetcher T338511: Create DonationReportFetcher
   5. Change Reports (items can be done in parallel)
      • Rewrite "Donation Sum Per Day" Report T338504: Refactor getDonationSumPerDay Report
      • Refactor CampaignMonitorPerDay Report T338505: Refactor CampaignMonitorPerDay Report
      • Refactor CampaignMonitorPerHour Report T338506: Refactor CampaignMonitorPerHour Report
      • Refactor DonationsPerCampaign Report T338507: Refactor DonationsPerCampaign Report
      • Refactor "Anonymous Donations" Report T338512: Refactor AnonymousDonationsReportUseCase
      • Refactor Raw Data Export T338513: Refactor RawDataExportReportUseCase
      • Refactor "Donations Per Day" Report T338515: Refactor DonationsPerDayReportUseCase
4. Remove tracking data from data blob (2nd "data migration")