High Level Design

Overview

Customer relationship management (CRM) is a technology for managing all your company's relationships and interactions with customers and potential customers. In this CRM dashboard use-case, we will see how Sisense can organize/track/maintain CRM related data to have a hub for managing all customer related information helping employees prioritize workload. It is important to organize customer data based on relevance and to flag customers throughout various stages. In this use-case, we will mash-up data coming from employee google calendars, with data from a CRM system (Salesforce), to organize each customer based on when the employee will meet with them next.

Dashboard Example (sample data)

Click on the image to open and interact with the dashboard:

Goals

To organize all CRM data based on when the employee is meeting with the customer next.
To help track all hours, including hours from the CRM system as well as each employee’s google calendars. To create a hub that can link to the CRM system to help manage customer data.

Objectives

  • To better manage/track customers that you work with
  • To prioritize work load based on when you need to meet with your customers next
  • To identify the stage for each customer (“New”, “Mid-Project”, “On-Hold”, “Q&A”, “Completed/Closed”), and color code each.
  • To identify customers that are missing a session in your calendar
  • To better manage customer hours as well as project timeframes

KPI Architecture

Objectives KPIs Measures Data Source
Summarized data for all CRM Accounts per Employee # New Accounts COUNT([Customer])

ProjectStatus IN (‘New Conslt’, ‘New OBP’, ‘New ReEng’)

Fact_ProjectInfo
# On Hold COUNT([Customer])

ProjectStatus = ‘On Hold’

Fact_ProjectInfo
# Session Hours Today IF(ISNULL(SUM([hourDuration])), 0, SUM([hourDuration]))

SessionDate = Today (in this use-case I chose 3/5/18)

Fact_CalendarInfo
# Open Accounts COUNT([Customer])

ProjectStatus NOT IN ( ‘Closed’, ‘Completed’, ‘Pending Closure, ‘Q&A’)

Fact_ProjectInfo
# Unscheduled Sessions COUNT([Customer])

NextMeeting = N/A

ProjectStatus NOT IN (‘Closed’, ‘Completed’, ‘Kickoff’, ‘New Conslt’, ‘New OBP’, ‘New ReEng’, ‘On Hold’, ‘Pending Closure’)

Fact_ProjectInfo
# Approaching Timeframe COUNT([Customer])

ApproachingDeadline = 1

Fact_ProjectInfo
# Past Deadline COUNT([Customer])

PastDeadline = 1

Fact_ProjectInfo
# Completed Accounts COUNT([Customer])

ProjectStatus IN (‘Completed’, ‘Pending Closure’, ‘Q&A’)

Fact_ProjectInfo
Individual Customer data organized per Employee Customer [Customer] Fact_ProjectInfo
Customer Manager [Customer Manger] Fact_ProjectInfo
Next Meeting [Next Meeting] Fact_ProjectInfo
Status [Status] Fact_ProjectInfo
Timeframe [Timeframe] Fact_ProjectInfo
Location | TZ [Location | TZ] Fact_ProjectInfo
Champion [Champion] Fact_ProjectInfo
Hours Remaining [Hours Remaining] Fact_ProjectInfo
Sort Int [Sort Int]

**Hidden Field used for sorting the pivot

Fact_ProjectInfo
Google URL [Google URL]

**Hidden Field used to create hyper link on customer

Fact_ProjectInfo
Session Is Today [Session Is Today]

**Hidden Field used to highlight customer name in blue if session is today

Fact_ProjectInfo
Past Deadline [Hours Remaining]

**Hidden Field used to highlight row background color in red

Fact_ProjectInfo
Approaching Deadline [Hours Remaining]

**Hidden Field used to highlight row background color in orange

Fact_ProjectInfo

*Fact_ProjectInfo is already a mashup between Salesforce + Google Calendar data.
*Fact_CalendarInfo is an extract coming from Google Calendar.

Entities Relationship Diagram

Suggested data model for SEM analysis

Google Calendar Export Script

Here is a Sisense Documentation Post on the Script.
Here is the google calendar API quickstart documentation.

Using the quickstart documentation and python script, you can connect to your own calendar. If your organization has shared calendars, than you can connect to colleague calendars through the single authentication. Just as a reference, the API has a limitation of 2500 events per request. The main link from google calendar to salesforce was based on the unique domains in the google calendar invite against the user domains stored in salesforce. You can find the full CRM dashboard script in the implementation kit below.

Batch Script Automation

Once the google calendar export is working you can create an elasticube that mashes the data up between salesforce + google calendar. Once this elasticube is created you can automate the entire process by first kicking off the google calendar export python script and then kicking of an elasticube build. Both of these steps can be kicked off through command line, so you can create a batch script that kicks off the python program, and then kicks of the build through the PSM.

Here is the batch script:
cd “C:\Python34\Sisense\GoogleCalendarExportScript\”
C:\Python34\python.exe “BICTeamCalendarExport.py”
cd “C:\Program Files\Sisense\Prism\”
psm ecube build name=”[Replace with Cube Name]” mode=restart serverAddress=localhost

Then you can kickoff this batch script from the task scheduler as frequently as you want:

CRM Dashboard

If you click on the # Session Hours Today, have a Jump To Dashboard (JTD) to see a calendar view:

CRM dashboard example

If you right click on a row in the pivot table, then you can also Jump To Dashboard to see the same calendar view but filtered for that specific customer:

CRM dashboard

You can find all of the scripts in the implementation kit below.