High Level Design
OverviewCustomer 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.
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.
- 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
|Summarized data for all CRM Accounts per Employee||# New Accounts||COUNT([Customer])
ProjectStatus IN (‘New Conslt’, ‘New OBP’, ‘New ReEng’)
|# On Hold||COUNT([Customer])
ProjectStatus = ‘On Hold’
|# Session Hours Today||IF(ISNULL(SUM([hourDuration])), 0, SUM([hourDuration]))
SessionDate = Today (in this use-case I chose 3/5/18)
|# Open Accounts||COUNT([Customer])
ProjectStatus NOT IN ( ‘Closed’, ‘Completed’, ‘Pending Closure, ‘Q&A’)
|# Unscheduled Sessions||COUNT([Customer])
NextMeeting = N/A
ProjectStatus NOT IN (‘Closed’, ‘Completed’, ‘Kickoff’, ‘New Conslt’, ‘New OBP’, ‘New ReEng’, ‘On Hold’, ‘Pending Closure’)
|# Approaching Timeframe||COUNT([Customer])
ApproachingDeadline = 1
|# Past Deadline||COUNT([Customer])
PastDeadline = 1
|# Completed Accounts||COUNT([Customer])
ProjectStatus IN (‘Completed’, ‘Pending Closure’, ‘Q&A’)
|Individual Customer data organized per Employee||Customer||[Customer]||Fact_ProjectInfo|
|Customer Manager||[Customer Manger]||Fact_ProjectInfo|
|Next Meeting||[Next Meeting]||Fact_ProjectInfo|
|Location | TZ||[Location | TZ]||Fact_ProjectInfo|
|Hours Remaining||[Hours Remaining]||Fact_ProjectInfo|
|Sort Int||[Sort Int]
**Hidden Field used for sorting the pivot
|Google URL||[Google URL]
**Hidden Field used to create hyper link on customer
|Session Is Today||[Session Is Today]
**Hidden Field used to highlight customer name in blue if session is today
|Past Deadline||[Hours Remaining]
**Hidden Field used to highlight row background color in red
|Approaching Deadline||[Hours Remaining]
**Hidden Field used to highlight row background color in orange
*Fact_ProjectInfo is already a mashup between Salesforce + Google Calendar data.
*Fact_CalendarInfo is an extract coming from Google Calendar.
Entities Relationship Diagram
Google Calendar Export Script
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:\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:
If you click on the # Session Hours Today, have a Jump To Dashboard (JTD) to see a calendar view:
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:
You can find all of the scripts in the implementation kit below.