Change Details

We currently pull down a bunch of mailing data from IBM, but not the details about what list we sent to or what criteria was used to build that list. We're going to do a lot more segmentation work this FY and IBM does not make this stuff easy to track. If we were to take our IBM query data that is associated with each mailing and import it into Civi, we could run mysql queries in our own databases and more easily track segments over time. You can view sample queries in the IBM UI by going to Data > Queries and clicking into one of the queries you see there. Example criteria for this query: **Query name: 20190614_GreatBritain(GB)_English(en)_optinNULL_2017donor-2018opener_Email2-PhotoPageTest - Segment 4** **Query criteria:** ( (country is equal to GB AND IsoLang is equal to en AND latest_donation_date year is equal to 2017 AND latest_optin_response is not equal to one of the following (YES | NO)) AND LastOpenDate is between Jan 1, 2018 and Jun 30, 2019 AND highest_usd_amount is less than 500) AND Segment is equal to 18 Ideally, we would save: 1) Contact Source name (usually a Query or Contact List) 2) All the query criteria--maybe just as a single text field, and just use a bunch of //LIKE %X%// statements? 3) Ideally, save the //Segment// value as its own field. This is a nice-to-have. 4) Maintain this at a per-mailing level, to go along with data in the civicrm_mailing_provider_data table Because this segmentation work is a priority this FY, I'd love to see if this is something we can work on in Q1-Q2. It only dawned on me recently that this was a possibility, but it would make our work so much cleaner. Let me know if there's any clarification I can provide, and thanks!

We currently pull down a bunch of mailing data from IBM, but not the details about what list we sent to or what criteria was used to build that list. We're going to do a lot more segmentation work this FY and IBM does not make this stuff easy to track. If we were to take our IBM query data that is associated with each mailing and import it into Civi, we could run mysql queries in our own databases and more easily track segments over time. You can view sample queries in the IBM UI by going to Data > Queries and clicking into one of the queries you see there. Example criteria for this query: **Query name: 20190614_GreatBritain(GB)_English(en)_optinNULL_2017donor-2018opener_Email2-PhotoPageTest - Segment 4** **Query criteria:** ( (country is equal to GB AND IsoLang is equal to en AND latest_donation_date year is equal to 2017 AND latest_optin_response is not equal to one of the following (YES | NO)) AND LastOpenDate is between Jan 1, 2018 and Jun 30, 2019 AND highest_usd_amount is less than 500) AND Segment is equal to 18 Ideally, we would save: 1) Contact Source name (usually a Query or Contact List) 2) All the query criteria--maybe just as a single text field, and just use a bunch of //LIKE %X%// statements when we need to query it? 3) Ideally, save the //Segment// value as its own field. This is a nice-to-have. 4) Maintain this at a per-mailing level, to go along with data in the civicrm_mailing_provider_data table Because this segmentation work is a priority this FY, I'd love to see if this is something we can work on in Q1-Q2. It only dawned on me recently that this was a possibility, but it would make our work so much cleaner. Let me know if there's any clarification I can provide, and thanks!

We currently pull down a bunch of mailing data from IBM, but not the details about what list we sent to or what criteria was used to build that list. We're going to do a lot more segmentation work this FY and IBM does not make this stuff easy to track. If we were to take our IBM query data that is associated with each mailing and import it into Civi, we could run mysql queries in our own databases and more easily track segments over time. You can view sample queries in the IBM UI by going to Data > Queries and clicking into one of the queries you see there. Example criteria for this query: **Query name: 20190614_GreatBritain(GB)_English(en)_optinNULL_2017donor-2018opener_Email2-PhotoPageTest - Segment 4** **Query criteria:** ( (country is equal to GB AND IsoLang is equal to en AND latest_donation_date year is equal to 2017 AND latest_optin_response is not equal to one of the following (YES | NO)) AND LastOpenDate is between Jan 1, 2018 and Jun 30, 2019 AND highest_usd_amount is less than 500) AND Segment is equal to 18 Ideally, we would save: 1) Contact Source name (usually a Query or Contact List) 2) All the query criteria--maybe just as a single text field, and just use a bunch of //LIKE %X%// statements when we need to query it? 3) Ideally, save the //Segment// value as its own field. This is a nice-to-have. 4) Maintain this at a per-mailing level, to go along with data in the civicrm_mailing_provider_data table Because this segmentation work is a priority this FY, I'd love to see if this is something we can work on in Q1-Q2. It only dawned on me recently that this was a possibility, but it would make our work so much cleaner. Let me know if there's any clarification I can provide, and thanks!