Cómo lees las gráficas e indicadores de CRM para Outlook

Hola a todos

En el CRM for Outlook existe una sección dedicada a las estadísticas e indicadores de ventas. Hemos tenido algunas preguntas acerca de estos números de cómo se deben de interpretar y cómo son calculados. Este artículo lo explica:

Top 5 Sales:

Este muestra los 5 artículos incluidos en facturas en el año actual ordenados de acuerdo al total de las líneas de las facturas en moneda local. Se utiliza el siguiente SQL:

SELECT
TOP 5 T0.ItemCode, ISNULL((SELECT ISNULL(TS0.ItemName,'') FROM OITM TS0 WHERE TS0.ItemCode = T0.ItemCode), '') AS Description, SUM(T0.LineTotal) AS Total
FROM INV1 T0 JOIN OINV T1 ON T0.DocEntry = T1.DocEntry
WHERE T0.ItemCode IS NOT NULL AND T1.CardCode = @CardCode AND YEAR(T1.DocDate) = @ThisYear
GROUP BY T0.ItemCode ORDER BY SUM(T0.LineTotal) DESC

Nos han llegado comentarios de que se debe de sustraer las notas de crédito pero hemos decidido mostrar el nivel de interés (lo que ha pedido la mayoría) y no los números netos...

KPI Numbers:

Muestra las estadísticas de toda la historia del cliente más las compras/ventas del año y mes a la fecha.

Se utilizan los siguientes SQL para los diferentes números mostrados.

DECLARE @CardCode NVARCHAR(6);
SET @CardCode = N'1234';

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
(SELECT AVG(T0.DocTotal) FROM OQUT T0 WHERE T0.CardCode = @CardCode) AS averageQuotationSize,
(SELECT AVG(T0.DocTotal) FROM ORDR T0 WHERE T0.CardCode = @CardCode) AS averageOrderSize,
(SELECT AVG(T0.DocTotal) FROM OINV T0 WHERE T0.CardCode = @CardCode) AS averageInvoiceSize,
(SELECT AVG(T0.DocTotal) FROM ORIN T0 WHERE T0.CardCode = @CardCode) AS averageCreditNoteSize,
(SELECT COUNT(*) FROM OQUT T0 WHERE T0.CardCode = @CardCode) AS numberOfQuotations,
(SELECT COUNT(*) FROM OQUT T0 WHERE T0.CardCode = @CardCode AND (SELECT COUNT(*) FROM QUT1 TS0 WHERE TS0.DocEntry = T0.DocEntry AND TS0.TargetType <> -1) <> 0) AS numberOfCopyToQuotations,
(SELECT COUNT(*) FROM ORDR T0 WHERE T0.CardCode = @CardCode) AS numberOfOrders,
(SELECT COUNT(*) FROM OINV T0 WHERE T0.CardCode = @CardCode) AS numberOfInvoices,
(SELECT COUNT(*) FROM OINV T0 WHERE T0.CardCode = @CardCode AND (SELECT COUNT(*) FROM INV1 TS0 WHERE TS0.DocEntry = T0.DocEntry AND TS0.TargetType <> -1) <> 0) AS numberOfInvoicesWithCreditNotes,
(SELECT AVG(T0.CloPrcnt) FROM OOPR T0 WHERE T0.CardCode = @CardCode) AS averageSalesOpportunitiesClosePercentage,
(SELECT AVG(T0.MaxSumLoc) FROM OOPR T0 WHERE T0.CardCode = @CardCode) AS averageSalesOpportunitiesPotential,
(SELECT COUNT(*) FROM OOPR T0 WHERE T0.CardCode = @CardCode) AS numberOfSalesOpportunitiesCreated,
(SELECT COUNT(*) FROM OOPR T0 WHERE T0.CardCode = @CardCode AND T0.CloseDate IS NOT NULL) AS numberOfSalesOpportunitiesClosed,
(SELECT COUNT(*) FROM OOPR T0 WHERE T0.CardCode = @CardCode AND T0.Status = 'W') AS numberOfSalesOpportunitiesWon,
(SELECT COUNT(*) FROM OOPR T0 WHERE T0.CardCode = @CardCode AND T0.Status = 'L') AS numberOfSalesOpportunitiesLost,
(ISNULL((SELECT SUM(T0.DocTotal) FROM OINV T0 WHERE T0.CardCode = @CardCode AND T0.DocDate BETWEEN '20170101' AND '20170504'),0)-ISNULL((SELECT SUM(T0.DocTotal) FROM ORIN T0 WHERE T0.CardCode = @CardCode AND T0.DocDate BETWEEN '20170101' AND '20170504'),0)) AS YTD,
(ISNULL((SELECT SUM(T0.DocTotal) FROM OINV T0 WHERE T0.CardCode = @CardCode AND T0.DocDate BETWEEN '20160101' AND '20160504'),0)-ISNULL((SELECT SUM(T0.DocTotal) FROM ORIN T0 WHERE T0.CardCode = @CardCode AND T0.DocDate BETWEEN '20160101' AND '20160504'),0)) AS LASTYTD,
(ISNULL((SELECT SUM(T0.DocTotal) FROM OINV T0 WHERE T0.CardCode = @CardCode AND T0.DocDate BETWEEN '20170501' AND '20170504'),0)-ISNULL((SELECT SUM(T0.DocTotal) FROM ORIN T0 WHERE T0.CardCode = @CardCode AND T0.DocDate BETWEEN '20170501' AND '20170504'),0)) AS MTD,
(ISNULL((SELECT SUM(T0.DocTotal) FROM OINV T0 WHERE T0.CardCode = @CardCode AND T0.DocDate BETWEEN '20170401' AND '20170404'),0)-ISNULL((SELECT SUM(T0.DocTotal) FROM ORIN T0 WHERE T0.CardCode = @CardCode AND T0.DocDate BETWEEN '20170401' AND '20170404'),0)) AS LASTMTD

El siguiente SQL se utiliza para el cálculo de los diferentes números de proveedores.

DECLARE @CardCode NVARCHAR(6);
SET @CardCode = N'1234';

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
(SELECT AVG(T0.DocTotal) FROM OPQT T0 WHERE T0.CardCode = @CardCode) AS averageQuotationSize,
(SELECT AVG(T0.DocTotal) FROM OPOR T0 WHERE T0.CardCode = @CardCode) AS averageOrderSize,
(SELECT AVG(T0.DocTotal) FROM OPCH T0 WHERE T0.CardCode = @CardCode) AS averageInvoiceSize,
(SELECT AVG(T0.DocTotal) FROM ORPC T0 WHERE T0.CardCode = @CardCode) AS averageCreditNoteSize,
(SELECT COUNT(*) FROM OPQT T0 WHERE T0.CardCode = @CardCode) AS numberOfQuotations,
(SELECT COUNT(*) FROM OPQT T0 WHERE T0.CardCode = @CardCode AND (SELECT COUNT(*) FROM PQT1 TS0 WHERE TS0.DocEntry = T0.DocEntry AND TS0.TargetType <> -1) <> 0) AS numberOfCopyToQuotations,
(SELECT COUNT(*) FROM OPOR T0 WHERE T0.CardCode = @CardCode) AS numberOfOrders,
(SELECT COUNT(*) FROM OPCH T0 WHERE T0.CardCode = @CardCode) AS numberOfInvoices,
(SELECT COUNT(*) FROM OPCH T0 WHERE T0.CardCode = @CardCode AND (SELECT COUNT(*) FROM PCH1 TS0 WHERE TS0.DocEntry = T0.DocEntry AND TS0.TargetType <> -1) <> 0) AS numberOfInvoicesWithCreditNotes,
(ISNULL((SELECT SUM(T0.DocTotal) FROM OPCH T0 WHERE T0.CardCode = @CardCode AND T0.DocDate BETWEEN '20170101' AND '20170504'),0)-ISNULL((SELECT SUM(T0.DocTotal) FROM ORPC T0 WHERE T0.CardCode = @CardCode AND T0.DocDate BETWEEN '20170101' AND '20170504'),0)) AS YTD,
(ISNULL((SELECT SUM(T0.DocTotal) FROM OPCH T0 WHERE T0.CardCode = @CardCode AND T0.DocDate BETWEEN '20160101' AND '20160504'),0)-ISNULL((SELECT SUM(T0.DocTotal) FROM ORPC T0 WHERE T0.CardCode = @CardCode AND T0.DocDate BETWEEN '20160101' AND '20160504'),0)) AS LASTYTD,
(ISNULL((SELECT SUM(T0.DocTotal) FROM OPCH T0 WHERE T0.CardCode = @CardCode AND T0.DocDate BETWEEN '20170501' AND '20170504'),0)-ISNULL((SELECT SUM(T0.DocTotal) FROM ORPC T0 WHERE T0.CardCode = @CardCode AND T0.DocDate BETWEEN '20170501' AND '20170504'),0)) AS MTD,
(ISNULL((SELECT SUM(T0.DocTotal) FROM OPCH T0 WHERE T0.CardCode = @CardCode AND T0.DocDate BETWEEN '20170401' AND '20170404'),0)-ISNULL((SELECT SUM(T0.DocTotal) FROM ORPC T0 WHERE T0.CardCode = @CardCode AND T0.DocDate BETWEEN '20170401' AND '20170404'),0)) AS LASTMTD