Skip to main content

Step 4 — Sales pipeline dashboard

A dashboard puts the pipeline on one page: open amount, won amount over the period, number of open deals, the pipeline by stage and the latest activities.

At a glance​

Sales pipelineThis month ▾↻OPEN PIPELINE173 500WON18 000▲ +12 %OPEN DEALS3Pipeline by stageQualifiedProposalNegotiationLatest activitiesBeta SARLCallACMEMeetingGamma SAE-mailPeriodPERIOD_FROM / PERIOD_TOKPIsum, count, compareSaved chartpipeline_by_stagePrevious periodchange shown under the figureTable widget10 latest rows

The queries​

In Liberty Next → Build → Connectors → crm → Queries, ＋ Add query for each:

-- open_deals_get — deals still in progress
SELECT ID, AMOUNT FROM deals WHERE STAGE NOT IN ('WON', 'LOST')

-- won_deals_get — deals won over the dashboard's period
SELECT ID, AMOUNT, CLOSE_DATE FROM deals
WHERE STAGE = 'WON' AND CLOSE_DATE BETWEEN :PERIOD_FROM AND :PERIOD_TO

-- pipeline_by_stage_get — amount per stage
SELECT s.LABEL AS STAGE, s.ORD, SUM(d.AMOUNT) AS AMOUNT
FROM deals d JOIN deal_stages s ON s.CODE = d.STAGE
WHERE d.STAGE NOT IN ('WON', 'LOST')
GROUP BY s.LABEL, s.ORD ORDER BY s.ORD

-- recent_activities_get — the latest activities
SELECT a.HAPPENED_AT, c.NAME AS CUSTOMER, d.NAME AS DEAL, a.KIND, a.RECORDED_BY
FROM activities a JOIN deals d ON d.ID = a.DEAL_ID JOIN customers c ON c.ID = d.CUSTOMER_ID
ORDER BY a.HAPPENED_AT DESC

:PERIOD_FROM and :PERIOD_TO receive the period chosen on the dashboard.

The chart​

Liberty Next → Build → Charts, scope crm, Add chart:

TabSetting
GeneralId pipeline_by_stage, label Pipeline by stage, query pipeline_by_stage_get.
ChartBar, X axis STAGE, one series AMOUNT, aggregate Sum.

See Charts.

The dashboard​

Liberty Next → Build → Dashboards, app crm, add a dashboard pipeline, title Sales pipeline:

  • Period: Add a period, opens on This month, date column CLOSE_DATE.
  • Widgets (Add widget), in this order:
WidgetSettingsWidth
KPI Open pipelineQuery open_deals_get, column AMOUNT, aggregation sum.4
KPI WonQuery won_deals_get, column AMOUNT, aggregation sum, Compare with the previous period.4
KPI Open dealsQuery open_deals_get, column ID, aggregation count.4
ChartThe saved chart pipeline_by_stage.8, height 2
Table Latest activitiesQuery recent_activities_get, row limit 10.4, height 2

Save. See Dashboards.

The menu entry​

Liberty Next → Build → Menus, app crm, at the top of the tree: ＋ Item, label Pipeline, type dashboard, target crm.pipeline, icon barchart. Move it above the Sales folder and set it as the connector's home (Liberty Next → Build → Connectors → crm) so that the app opens on it.

See it work​

Open Pipeline:

  • the three KPIs; Won shows its change against the previous period;
  • the bars of the pipeline by stage;
  • the latest activities, including the one recorded in Step 3.

Change the period to This year: the Won KPI follows. ↻ reloads every widget.

What you have now​

A dashboard that answers "where is the pipeline?" without opening a screen, on the same queries and permissions as the rest of the app.

→ Step 5 — Roles and sign-in: who sees what.