## 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} 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.
### 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
# Change Donation Bounded Context
# Store tracking data in new tables {T336848}
# Create a database migration to create the tables and copy the data {T336846}
# Adapt Fundraising Application to changes & migrate database {T390996}
# Change Fundraising Operation Center (items on the same level can be done in parallel)
# Adapt donation fixtures for tests
# Change Donation export to use new tracking data table {T338517}
# Refactor `Analysis\DataAccess\DatabaseDonationReader` {T338518}
# Create DonationReportFetcher {T338511}
# Change Reports
- Rewrite "Donation Sum Per Day" Report {T338504}
- Refactor CampaignMonitorPerDay Report {T338505}
- Refactor CampaignMonitorPerHour Report {T338506}
- Refactor DonationsPerCampaign Report {T338507}
- Refactor "Anonymous Donations" Report {T338512}
- Refactor Raw Data Export {T338513}
- Refactor "Donations Per Day" Report {T338515}