Change Details

Currently we are storing the tracking information of donations in the serialized `data` property. 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. To prepare for improvements in the FOC we need to extract the tracking information. To keep the existing scripts running, we need to keep storing the tracking data in the `data` blob until the FOC is adapted. **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. - The deprecated fields `source`, `color` and `layout` are removed from the `TrackingInfo` domain class. They are superseded by other software and are not exported since 2019. **Implementation details** - Store the `TrackingInfo` domain class as an entity in the donation bounded context. Backing table will probably have 3 columns: `id`, `campaign`,`keyword` (with a 2-column unique index on campaign and keyword). Use `donation_tracking` as the table name, because we will have tracking for memberships and subscriptions as well. - Store the tracking ID in donation (using the legacy converter classes), - Add a 1:n column relationship between Donation Doctrine entity and the Tracking entity - Create a migration that adds the new entity table and adds the `tracking_id` property to the donation (`spenden`) table - Create a script that reads existing donations and their tracking information, creating new entries in the tracking table as needed (each campaign/keyword combination should be unique) and updating the donation. Don't do this in the migration because you need to query and modify the 7Mio+ donations in batches. Test the script in the test environment with the full production data set. - Three implementations of an interface that stores donation tracking data: - one that stores it in the table. - one that stores it in the blob. - a proxy that uses the two implementations above. - When the Fundraising Operation Center is ready to use the new tables, we can drop the latter two.

## 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 **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 makring 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).

## Product perspective ### Context Currently we are storing the tracking information of(the Matomo campaign name and keyword from the URL at the point when a donationsn 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. To prepare for improvements in the FOC we need to extract the tracking information. To keep the existing scripts running, we need to keep storing the tracking data in the `data` blob until the FOC is adapted.We can avoid those drawbacks by storing the tracking information **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. - The deprecated fields `source`, `color` and `layout` are removed from the `TrackingInfo` domain class. They are superseded by other software and are not exported since 2019. **Implementation details** - Store the `TrackingInfo` domain class as an entity in the donation bounded context. Backing table will probably have 3 columns: `id`, `campaign`,`keyword` (with a 2-column unique index on campaign and keyword)--- ## Technical perspective ### Context This change is part of a larger strategy of makring the donation table more normalized and moving information out of the "data blob". Use `donation_tracking` as the table name, because we will have tracking for memberships and subscriptions as well. - Store the tracking ID in donation (using the legacy converter classes), - Add a 1:n column relationship between Donation Doctrine entity and the Tracking entity - Create a migration that adds the new entity table and adds the `tracking_id` property to the donation (`spenden`) tableSee {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. - Create a script that reads existing donations and their tracking information, creating new entries in the tracking table as needed (each campaign/keyword combination should be unique) and updating the donation. Don't do this in the migration because you need to query and modify the 7Mio+ donations in batches. Test the script in the test environment with the full production data set. ### Affected repositories - Three implementations of an interface that stores- donation bounded context: adding tables and changing how donation tracking data:s are stored - one that stores it in the table.- Fundraising Application: Integration of new releases of the bounded context, no other changes should be needed - one that stores it in the blob- 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). - a proxy that uses the two implementations above.- We need structural migrations (adding tables) - When the Fundraising Ope- We need data migration Center is ready to use the new tables, we can dropscripts (copying existing tracking data from the blob into the latter twonew tables).