Custom Field Filling in WhosOn Database

I had a requirement from a customer to collect the order numbers from their website, but at their current state they were unable to use the normal method of modifying the tracking code to set the order details in WhosOn. Fortunately, the details of the order were stored inside the URL for the customer, on the thank you page thankyou.aspx as the parameter OrderID.

I wanted this to show up as a form fill variable so it can easily be used for querying the order number based on chatted, invited etc.

The route I went down for this was modification of the AddPageView procedure. This procedure finds the page from the page header information (or inserts it if this page hasn't been seen before) and inserts a view record for the visitor.

The code below could be applied to the WhosOn Server Database in order to AddPageView

ALTER PROCEDURE [dbo].[AddPageView]

@VisitID int,

@VisitorID int,

@SiteKey smallint,

@PageName varchar(250),

@PageDate datetime,

@PageNumber smallint,

@ContentGroup varchar(50),

@HTTPStatus smallint,

@HTTPSubStatus smallint,

@CachedPageHeaderID int,

@PageHeaderID int OUTPUT,

@LastModifiedDate datetime OUTPUT,

@LastCheckedDate datetime OUTPUT,

@TitleTag varchar(250) OUTPUT

AS

BEGIN

SET NOCOUNT ON;

DECLARE @cgroup varchar(50)

DECLARE @orderref varchar(50)

IF CHARINDEX('thankyou.aspx',@PageName) > 0

BEGIN

DECLARE @ia INT;

DECLARE @iaa INT;

SET @ia = CHARINDEX('OrderID', @PageName);

SET @ia = CHARINDEX('=',@PageName, @ia);

SET @iaa = CHARINDEX('&',@PageName, @ia);

if @iaa = 0

BEGIN

SET @iaa = LEN(@PageName);

END

SET @orderref = SUBSTRING(@PageName,@ia+1, @iaa-@ia);

INSERT INTO dbo.VisitFormVars (VisitID, SiteKey, VarForm, VarName, VarData, VarTime)

VALUES (@VisitID, @SiteKey, 'Order', 'OrderNumber', @orderref, GETDATE())

SET @PageName = 'thankyou.aspx';

SET @CachedPageHeaderID = 0;

END

IF @CachedPageHeaderID = 0

BEGIN

SELECT @PageHeaderID = PageHeaderID,

@LastCheckedDate = LastChecked,

@LastModifiedDate = LastModified,

@TitleTag = TitleTag,

@cgroup = ContentGroup

FROM PageHeader

WHERE SiteKey = @SiteKey AND Page = @PageName

IF @PageHeaderID IS NULL

BEGIN

INSERT INTO PageHeader (

Page,

ContentGroup, LastModified,

LastChecked, SiteKey, TitleTag)

VALUES

(@PageName, @ContentGroup, '2000-01-01 00:00:00','2000-01-01 00:00:00', @SiteKey, '');

SET @LastCheckedDate = '2000-01-01 00:00:00';

SET @LastModifiedDate = '2000-01-01 00:00:00';

SET @TitleTag = '';

SET @PageHeaderID = SCOPE_IDENTITY()

END

ELSE

BEGIN

IF NOT @cgroup = @ContentGroup

UPDATE PageHeader SET ContentGroup = @ContentGroup

WHERE PageHeaderID = @PageHeaderID;

END

END

ELSE

SET @PageHeaderID = @CachedPageHeaderID

INSERT INTO PageViews

(VisitID, PageID, VisitorID, SiteKey, PageDate, PageNumber,

HTTPStatus, HTTPSubStatus)

VALUES

(@VisitID, @PageHeaderID, @VisitorID, @SiteKey,

@PageDate, @PageNumber, @HTTPStatus, @HTTPSubStatus);

END

Set Prospect Client Command

I've just completed a test project designed for adding more rules into how WhosOn Live Chat can decide on Prospect Detection. Prospect detection is used for setting up different rules for allocating the quality of visitors, and very commonly as something to send automatic invites based on. The new WhosOn V6 that will be released shortly allows for multiple types of prospect to be defined.

This uses the new Set Prospect command to insert the prospect into WhosOn and force the actions for that prospect to happen.

The system connects to a given site using the client protocol, and stores the visitors internally, subscribing to the WhosOn visitor event model (waiting for the @EV and @ER events, and using an internal array to hold the information).

The detection allows for second level triggering of time on site, time on page, time idle etc, and is easily plugable to allow for expansion of the types of filters that can be used.

It breaks down the rules into two types of rules - those that require timers, and those that don't.
If the rules don't require timers, then they wait for the events (or the initial visitor list population on connection) and check the rules at this stage.

For the rules that do require timers, these are executed based on the timer refresh length set in the program, and the number of seconds can be set here.

The rules are multi-threaded (along with each connection) so the program can deal with many connections for many different rule sets.

The rule set uses an XML structure for if a rule should be activated, containing the rule type and the login information.

Rules that I have created so far:

• Regular Expression for Current Page, Previous Page, Any Page this Session
• Form Variable Check
• Number of Seconds on Page
• Number of Seconds on Site
  

Possible Future updates:

• More Rules Types
• More intelligent timer - don't need to check every X seconds if there are no new visitors. This could be calculated by checking the current timer wait time, and minimum time for the rule, then setting up the internal timers to wait this long.
• More Actions (I.E. sending different messages through to the visitors in the invites, or sending messages to the operators about the visitors).
• Performance enhancements on the visitors arrays.
• Use reflection for loading the types, rather than just doing a case selection on the type name.

If you are interested in using this sort of rules engine, then please contact our professional services team, and we will endeavour to match up prospecting systems to deal with whatever your requirements for prospecting are. The link below shows information about our Configuration Professional Services for WhosOn Live Chat.

http://www.whoson.com/professionalconfiguration.aspx

WhosOn Database - Preventing Storage of Chat Transcripts

Sometimes it is necessary for security or privacy reasons to not store the chat transcript in the database. In WhosOn this can easily be achieved by adding a new database trigger to the database.

The following trigger drops the INSERT command if the LineNumber > 1, and for the first line creates an entry to the effect that the transcript is not stored.

The visitor's session information will still be stored, but this could be prevented with another trigger if required.

Just run the below code in SQL Management Studio, in a SQL Query window against the WhosOn database.



CREATE TRIGGER [dbo].[UserTranscriptStop]
ON [dbo].[UserTranscript]
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for trigger here
DECLARE @LineNum int
SELECT @LineNum = LineNumber FROM INSERTED

IF @LineNum = 1
BEGIN
INSERT INTO UserTranscript (SiteKey, ChatUID, LineNumber, LineTime, OperatorLine, LineText)
SELECT SiteKey, ChatUID, 1, LineTime, OperatorLine, 'Data not stored in database due to DB Settings'
FROM INSERTED
END
END