Store Filter Landlines results

[bchrobot]

Is your feature request related to a problem? Please describe.

1. It is impossible to tell whether a contact was removed from an uploaded list due to a) duplicate row, b) existing opt-out record, or c) landline filtration.
2. The carrier data used to perform landline filtration may be useful to clients and should be exposed.

Describe the solution you'd like

Landline filtration results should be stored in a new table in Spoke. This table should include all of the user-provided columns of the current campaign_contact table along with columns for landline status and detailed LRN results.

When possible, telco errors like Telnyx 40001 Not routable should also populate this new table.

Describe alternatives you've considered

Storing the information in the existing campaign_contact in new columns. Add soft deletes to handle the actual filtration.

Additional context

N/A

[sync-by-unito[bot]]

➤ Ben Packer commented:

Just noting that this would let us do this inside of Spoke as well, and that's significant! https://secure.helpscout.net/conversation/1686418875 ( https://secure.helpscout.net/conversation/1686418875 )

[sync-by-unito[bot]]

➤ Nina Baldwin commented:

Brian Goncalo also requested this feature in the following ticket: https://secure.helpscout.net/conversation/1798622034/5942?folderId=6069888 ( https://secure.helpscout.net/conversation/1798622034/5942?folderId=6069888 )

[sync-by-unito[bot]]

➤ Nina Baldwin commented:

Hiemanshu SharmaBenjamin Chrobot Can we add an export for clients?

[hiemanshu]

@politics-rewired/organizing Can you tell me what details you'd like in the export? And if you'd like an export that is a part of the standard campaign export or an export that's just an export of the filtered numbers? Or both?

@bchrobot There are two ways of looking at this:

1. We add a soft delete, with a reason of contact being deleted -- This is straight forward, is part of the exports by default, and we can get a specific export out easily. Postgres has a fairly large column limit, so we shouldn't run out of new columns. Only thing might be to think about partitioning in the future if the database gets slow when working with campaign_contact.

2. Create a new table that is filled with filtered contacts -- This is also fairly straight forward, but this adds a dependency on tracking changes in campaign contact if any in the future, this doesn't have the same effect on the growing size of campaign_contact.

Let me know what you think

[ajohn25]

Summary of changes which we would like for exports:

Export is a button you click that brings up a modal asking you to specify what you want (opt outs, filtered, or the 2 existing campaign exports). Each of these are separate .csv files, and you get sent only the ones you pick.

The first 14 columns (A-N) for the existing campaign contact export, landlines and opt outs exports should be the same.

If possible, the landlines report should include the type of phone (invalid, landline, VOIP etc.) I'm not sure if this is info we have available when we do the lookup, but if we do it would be nice to include.

The opt outs report is the same as the campaign contact export, just filtered for is_opted_out = true

[hiemanshu]

Thanks @ajohn25. What happens in case of numbers being filtered out because they were opted out in the organisation? What list do they show up in?

And where does the export button go? Part of the Export Campaign tool bar?

[ajohn25]

We think contacts filtered out as opt outs can go with landlines (so together it's like a "filtered contacts" report). And then the last column could be Reason which is Opt Out, Landline, VOIP etc. (or if we can't say the specific type for landline filtering, let's just say Landline for all those).

The change to the Export Data Button would be to have it behave more like the Export to VAN Button. So you click it and then a modal pops up to let you toggle on which reports you want exported.

[hiemanshu]

Thanks! Sounds good to me. Last question @ajohn25, is the export dialog going to include being able to export the data currently being sent? i.e. Campaign Export + Message Export. So the first 2 options are turned on by default, and the last 2 options being opt outs only, and filtered data only being off by default.

I'm also going to use the opportunity to update the export message with something a little less ugly looking.

[bchrobot]

@bchrobot There are two ways of looking at this:

1. We add a soft delete, with a reason of contact being deleted -- This is straight forward, is part of the exports by default, and we can get a specific export out easily. Postgres has a fairly large column limit, so we shouldn't run out of new columns. Only thing might be to think about partitioning in the future if the database gets slow when working with campaign_contact.
2. Create a new table that is filled with filtered contacts -- This is also fairly straight forward, but this adds a dependency on tracking changes in campaign contact if any in the future, this doesn't have the same effect on the growing size of campaign_contact.

Let me know what you think

I think option 2 is the way to go. campaign_contact is a heavily written table so everything we can do to prevent sizes from growing will help with performance (in a perfect world we would move "static" columns like custom_fields into their own table to decrease the amount of IO for each update of message_status, assigment_id, etc.

I think option 2 is also safer given the extremely sparse test coverage we have and how critical campaign_contact is. Adding a new table rather than adding a soft delete to the current one rules out missing updating an execution path and introducing a bug.

[hiemanshu]

Sounds good! Was my preference too.

[ajohn25]

Thanks! Sounds good to me. Last question @ajohn25, is the export dialog going to include being able to export the data currently being sent? i.e. Campaign Export + Message Export. So the first 2 options are turned on by default, and the last 2 options being opt outs only, and filtered data only being off by default.

I'm also going to use the opportunity to update the export message with something a little less ugly looking.

All sounds great!