Updating Campaign Tables
From V10.6.811 - now supports updating records on running campaigns
Updating records on running campaigns
From V10.6.811
Some customers would like to update the campaign database in real-time while campaigns are running.
This presents a challenge because, in order to achieve high throughput, Softdial Campaign Manager™ caches records, and uses snapshot-type cursors.
The existing mechanism to mitigate this involves purging the Softdial Campaign Manager™ cache and closing the main cursor for the campaign.
This is not ideal as it leads to short term number starvation for Softdial CallGem™.
The change that has been implemented involves Softdial Campaign Manager™ exposing a SDMP message API that allows individual customer records to be updated by proxying through Softdial Campaign Manager™, so that Softdial Campaign Manager™ can deal with cache checking and mitigation of snapshots.
Updating records
The API to support record update is the following pair of SDMP messages:
- Update Record [UR] - sent by an application to update a record against a running campaign
- Record Updated [RU] - sent in response, indicating whether the record is updated
API behaviour
On receipt of a valid Update Record [UR] message, Softdial Campaign Manager™ will first check its cache to see if the record is already dialed.
If the record is
- already dialed, the appropriate Record Updated [RU] is returned and no update action is taken.
- in cache, but not yet dialed, the record is taken out of cache and the record updated as per the parameters.
- not in cache, the record is updated as per the parameters. In addition, the update request is cached in Softdial Campaign Manager™.
On reading in new rows, a cache check is made in case the snapshot delivers an earlier copy of the record without updates. In this case the record will be re-updated and the record read discarded. This measure prevents database server caching from causing data integrity issues.
Softdial Campaign Manager™ acts as a database proxy, caching rows of customer/ lead data to be processed and managing updates of this data through a single point of access to the database.
Softdial Campaign Manager™ is designed to provide maximum data throughput. To achieve and maintain this, a strict protocol must be observed when third-party applications require to modify campaign table data during live operations.
In general, a user should not update a table that has an open cursor. Whilst doing this may appear to 'work' it will inevitably lead to unreliable operation caused by deadlocks and resulting in data corruption.
Recognising that there are valid business reasons for accessing and updating the campaign database table while a campaign is running, SCC provides a mechanism for doing this in a safe and reliable manner.
Softdial Campaign Manager™ Caching Behaviour
In order to keep Softdial CallGem™ fed with numbers, Softdial Campaign Manager™ operates a two-stage cache mechanism.
- Softdial Campaign Manager™ keeps a certain number of database rows in memory ready to supply to Softdial CallGem™ immediately upon request. In addition to this, Softdial Campaign Manager™ maintains an open cursor on the primary table/ view so that a bulk fetch of anything between 50 and 500 rows can be made without having to cache the index to the database view (which may take time).
- Softdial Campaign Manager™ also periodically opens a cursor to pick up retries and rescheduled calls every minute. This cursor is opened, scanned and then closed. In order to keep data integrity Softdial Campaign Manager™ updates the primary table to mark a row as 'in cache' by setting the Switch Result column to a negative number value. There are different negative number values for different cache states.
Applications designed to modify data in the campaign tables while the campaign is running must take into account the fact that Softdial Campaign Manager™ maintains an open cursor and has database data cached.
Reasons for Adding Records to or Updating the Campaign Table Data
There are good business reasons why a user may need wish to add or update numbers in a list, for example:
- An insurance aggregator feed providing new lead data in real-time needs to add records to the campaign table and/ or reprioritise existing records. See also the FAQ - 15) How do I add records from a web site feed and prioritise them to be dialed in order of most recently added?
- An immediate business need to re-prioritise the processing order of the campaign table data.
- A need to delete records that are no longer 'live'.
- Existing third-party application implementation detail.
In order to facilitate this, SCC provides a function in Softdial Campaign Manager™, available in versions 10.5 and 10.6, to temporarily close the main cursor.
Constraints and Workarounds
Any third-party use of the temporary close mechanism, and any third-party updates to tables used by Softdial Campaign Manager™ during live operation must conform to the following rules:
Updating tables
If an update to any of the input tables to the campaign is required and that update is not a single column update, the third party must follow the procedure below to perform the update:
- Issue the Close Cursor [CU]) command for the campaign.
- Wait for the Now Unloaded [NU] acknowledgement from Softdial Campaign Manager™ Server.
- Issue the update query (see Updating Records, above).
- Upon completion of the update query, issue Reload Campaign [RL] to force reload of the primary cursor.
Updating a single row
An update to a single row in the campaign table/ view may be made without first closing the primary cursor, as follows:
- The update being made must use the same unique identity key as Softdial Campaign Manager™ uses to identify rows.
- Any and all third-party updates to any input table to the campaign exclude rows in cache. This means any SQL query making 3rd-party updates to the campaign tables must contain a predicate as follows:
WHERE (<switch_result_col> IS NULL or < switch_result_col> >= 0)
- This rule holds true even if calls have been purged from the Softdial CallGem™ queue.
- In order to allow third parties to update data that is otherwise held in cache, a user may issue a Purge Queue [PQ] message in order to flush Softdial CallGem™'s cache of numbers not yet dialed. This practice is not recommended as it leads to dialer starvation and has limited other benefit. If a third party requires this behaviour the Purge Queue [PQ] message must be sent prior to the sequence of events in Updating Tables above, so that any database updates queued are processed before the cursor is closed.
Adding rows
Adding new records / inserting new rows in the campaign table (or table view) may take place without closing the primary cursor.
- If the new rows are for imminent dial (as is often the case) the rows should be inserted as if the call is scheduled for immediate retry; this means setting the switch_result column value to -4 and the retry_timestamp column to Now (UTC).
- If the rows are not scheduled for dial, in order to guarantee inclusion in the campaign run the primary cursor must be reloaded using Reload Campaign [RL].
From V10.6.311 - a web-based interface for safely adding records from a web feed is now available. See the Insert Record [IR] message. This can be used, for example, to insert a record from a web site using the HTTP API.