SugarCRM Tips. How to Find Customers or Leads Afforded Insufficient Attention

SugarCRM Tips. How to Find Customers or Leads Afforded Insufficient Attention Author: Nikolay Bulava, CEO

We keep sharing the secrets on how SugarCRM can make your work faster and easier. In our previous materials, we explained how to quickly figure out what important events have happened. Today we are going to deal with another interesting issue “what important thing has NOT happened?”.

Chief Sales Officers want to know which deals are not pushed forward and which leads are untended. Chief Support Officers want to know which cases are pending. Account managers want to know which customer they have not called for a long time.

A simple list like this allows you to both save time and keep track of what is important.
SugarCRM Tips. How to Find Customers or Leads Afforded Insufficient Attention
With TimeLine Viewer add-on you will have no trouble dealing with report generation in Sugar Enterprise or Sugar Ultimate.

Below I will show you how to generate a report step by step using the following task as an example: “As the Head of Sales department I want to see all Leads which didn’t see any activities over the last week”. You can use it to create your own options adjusting data selection criteria to specific needs.

Step 1. Creation of a Custom Query

Note: You must be an administrator in order to create, edit and delete custom queries.

Let’s go to Advanced Reports and select Create Custom Query. Specify a clear Query Name – for example, Active Leads without events for the last 7 days. In Custom Query indicate the query which selects the required records, i.e. Leads in the Status neither Dead nor Converted, which don’t have any linked events in Timeline over the last 7 days. Let’s output in the report the name of Lead, its Status and its Assigned Manager, as well as the last activity/event on this Lead. Such a Query will look like this:

SELECT 
    leads.first_name,
    leads.last_name,
    leads.status,
    u.user_name,
    (
		SELECT 
        	max(tlv2.action_date) max_action_date
		FROM 
        	px_timeline tlv2
		WHERE 
        	tlv2.parent_id = leads.id 
            AND tlv2.parent_type = 'Leads'
            AND tlv2.deleted=0
	) last_action_date,
    (
    	SELECT
        	tlv3.name
        FROM
        	px_timeline tlv3
        WHERE
        	tlv3.parent_id = leads.id
            AND tlv3.parent_type = 'Leads'
            AND tlv3.deleted=0
        ORDER BY
        	tlv3.action_date DESC
        LIMIT 1
    ) last_action_name
FROM 
	leads
LEFT JOIN 
	users u 
    ON 
    (leads.assigned_user_id = u.id)
WHERE 
	leads.id NOT IN (
    	SELECT 
        	tlv.parent_id
		FROM 
        	px_timeline tlv
		WHERE 
        	tlv.parent_id IS NOT NULL
            AND
        	tlv.action_date >= DATE_SUB(CURRENT_DATE (), INTERVAL 7 DAY)
            AND 
            tlv.parent_type = 'Leads'
            AND 
            tlv.deleted=0
	)
    AND
    leads.deleted=0
    AND
    leads.status not in ('Dead', 'Converted', 'Recycled')

SELECT

leads.first_name,

leads.last_name,

leads.status,

u.user_name,

(

SELECT

max(tlv2.action_date) max_action_date

FROM

px_timeline tlv2

WHERE

tlv2.parent_id = leads.id

AND tlv2.parent_type = 'Leads'

AND tlv2.deleted=0

) last_action_date,

(

SELECT

tlv3.name

FROM

px_timeline tlv3

WHERE

tlv3.parent_id = leads.id

AND tlv3.parent_type = 'Leads'

AND tlv3.deleted=0

ORDER BY

tlv3.action_date DESC

LIMIT 1

) last_action_name

FROM

leads

LEFT JOIN

users u

(leads.assigned_user_id = u.id)

WHERE

leads.id NOT IN (

SELECT

tlv.parent_id

FROM

px_timeline tlv

WHERE

tlv.parent_id IS NOT NULL

AND

tlv.action_date >= DATE_SUB(CURRENT_DATE (), INTERVAL 7 DAY)

AND

tlv.parent_type = 'Leads'

AND

tlv.deleted=0

)

AND

leads.deleted=0

AND

leads.status not in ('Dead', 'Converted', 'Recycled')

In Sugar interface your record will look like this:
SugarCRM Tips. How to Find Customers or Leads Afforded Insufficient Attention
Let’s save the Custom Query record.

Step 2. Create Data Format

The next step is formatting the report so that its output is clear and user-friendly – change the names of columns (for example user.user_name is unlikely to be clear for a user, however Assigned Manager will be clear for everyone), font, background etc. To read more about data formatting you can go to Vendor’s website.

Let’s enter Data Format and select Query Name created in the previous step. Also tick Exportable and set Font Size to Default.
SugarCRM Tips. How to Find Customers or Leads Afforded Insufficient Attention
Let’s save the record. Once the record is saved, we see how the data is displayed in the report. You can leave it as is, but it would be better to give clear names and highlight the main data.

Here you can see how to format data displayed in the report.

Now our report is much clearer for an end-user.
SugarCRM Tips. How to Find Customers or Leads Afforded Insufficient Attention
Step 3: Create a report

The last step is to create a report. Enter the name of the report and, if necessary, indicate the description and Title. Let’s save the record.
SugarCRM Tips. How to Find Customers or Leads Afforded Insufficient Attention
Next open the report and Data Format and click Select to choose Data Format created in the previous stage:

That’s it. The report is ready.

Now the Chief Sales Officer just in 2 clicks gets the list of all Leads which didn’t see activities/events for the last 7 days – the task is done.

You can extend the current report, for example by adding one more Custom Query with data on Accounts which didn’t see activities/events for the last N days.

Want to try TimeLine Viewer? Leave us a request and we’ll be happy to give you access to the demo-environment.

WEBINAR: Productivity Enhancement for Every CRM User with Minimum Effort and Maximum Results

Find out how to be productive wherever you are with regular add-ons such as Customer Journey, Hint, iReports and TimeLine Viewer.

Watch the Webinar

WEBINAR:A Smart Way to Collect Key Business Events in SugarCRM for BI and AI

Find out how you can customize TimeLine Viewer add-on for your specific needs and be able to collect data for BI and AI easily.

Watch the Webinar

Other articles from “SugarCRM tips” series:

SugarCRM Tips. How to Find Customers or Leads Received Insufficient Attention

Leave a Reply Cancel reply