Blue Pumpkin Data – What to use it for?

In the previous post I showed you were all the information is held in the BP database to allow you to start creating reports or using the data to build applications.

The first report I wanted to build was adherence and the easiest way to do this was to create two arrays with 86400 elements in each, one for every second of the day. The first is the scheduled data and the second is the actual data.

Scheduled Data
I set up a loop going through each advisor in the structure and within this loop another loop which stepped through the scheduled information for this advisor, this loop populated the scheduled array with an activity code which I set up.

Actual Data
Then after this loop had finished but still inside the first loop I collected the actual data in the same way, filling each second in the array with the actual activity code I set up.

Matching Up
Using another loop I stepped through each second in the arrays and cross checked each second against the scheduled and the actual. I used two variables adherent and non-adherent, so if the codes matched then this was adherent else it was not. This then gave me a total time adherent and non adherent. From this I could make a simple calculation to give the adherence.

This method, although the most accurate, does not allow for any deviation, either positive or negative in the schedule.

Also within the above loops I set up other variables to capture key information which can be added to a report to help manage advisors on adherence or other scheduling issues. Also Key call stats can be extracted from this data to give some insight into advisor behaviour.

I set up variables to capture scheduled data, how much time should be spent taking calls, how much time should be spent on breaks, lunches, meetings etc. I also collect the actual time spent in those states and then in the report compare this. This allows team leaders to quickly see where the adherence is being lost and manage this effectively.

I have then set up templates to output the information so this can be imported into other reports so with a simple text file output I can save the data to be used a at a later date.

Due to the fact that our blue pumpkin data does not update until over night when I run the extractor I usually run this for 5- 10 days at a time to ensure any changed schedules are picked up in future reports. The actual data does update live throughout the day and I will show another time how I use this to manage advisors real time in a simple dashboard.

Blue Pumpkin Data

Over the last year and a bit I have been working on getting data from the blue pumpkin data base. I have access to the main data base and I have gone through not all but quite a few of the tables extracting information I require and sorting these into useable tables and exporting these into my usual text file format.

I am writing this post because I have looked online and nobody seems to be sharing what tables are available or where the information you most want is held. Since there is many tables some with very little information and some with masses of information in them it takes a bit of time to find just the basics which you may use for adherence reports etc… you may say but there are reports in Impact 360 which can give you all this information and I agree but I like to use raw data when creating reports so as not to over round the data which can skew final results. Also I like to automate the data collection and report creation to free me up to create new reports or further improve the process.

The data in the blue pumpkin data base is all split up into several tables so you will find to build a timeline of information you will need to map the tables together using the key ID columns in the tables. I am using SQL to pull the information into excel and then stitch them together to produce my useable data.

The tables I am using to pull structures and actual data are as follows.

Table

V_TIMEENTRYEVENT – I use this table to pull in the actual activity.

Columns

ACTIVITYID – this is the ID code for the event

EMPLOYEEID – this is the employee id for the event

STARTTIME – this is the start time of the event

ENDTIME – this is the end time of the event

This table holds all the event data in the blue pumpkin database, so you will need to specify a start and end date or the SQL query will bring every event through and depending on how long you have been running blue pumpkin and the volume of employees this could be huge.

Table

V_RPT_EMPLOYEEORG

Columns

EMPLOYEEID – you will use this to match a name to the events

FIRSTNAME – This is the forename

LASTNAME – This is the surname

This table holds all your employee names along with the employee ID which you will use to put peoples names to events from the actual table and the scheduled table.

Table

V_RPT_PLANNEDACTIVITIES

Columns

EMPLOYEEID – The employee for each activity

ACTIVITYID – This is the event ID scheduled

activityName – this is the actual event name

startTimeGMT – this is the start time of the event

endTimeGMT – this is the end time of the event

This table holds all the schedule information held in blue pumpkin so again you will need a start and end date on the query to narrow down the results.

Table

ACTIVITY

Columns

ID – This is the event ID used to match up the event to its name

NAME  - This is the event name

I use this table to match up the event ID to its name to make the table more readable.

Table

ADHERENCEEXCEPTION

Columns

EMPLOYEEID – again this is the employee ID for the exception

STARTTIME – This is the start time of the exception

ENDTIME – This is the end time of the exception

PLANNEDACTIVITYID – This is the planned activity ID which is matched in the ACTIVITY table

This table is any exceptions added to override the scheduled event in blue pumpkin.

So with these tables pulled into excel via SQL you can then start to build up your reports. I have many reports and dashboards which use the data files I create or pull the data live from the database.

One warning I do not know if this is set up the same on all installations but if we make a change to a schedule this does not update on the scheduled table until the next day, I guess this gets updated overnight so when creating reports you will need to advise people any changes made in the last 24 hours wont be reflected in the reports.