topic Intervalos entre fechas de pedido in Español

Intervalos entre fechas de pedido

Thu, 17 Jul 2014 07:58:48 GMT

Hola a todos, la cuestión es la siguiente:

Tengo una tabla con tres campos: ID_CLIENTE, ID_VENDEDOR, FECHA_PEDIDO.

Lo que necesito hacer es calcular, en script, para cada vendedor qué clientes han hecho un pedido habiendo pasado un año o mas de su anterior pedido.

Aún no he encontrado una manera de calcularlo eficientemente.

¿Me echais una manilla?

Muchas gracias

Re: Intervalos entre fechas de pedido

etendero — Thu, 17 Jul 2014 08:19:30 GMT

Hola Juan Antonio,

Pues se me ocurre lo siguiente:

Pedidos_aux:

LOAD ...

, ID_CLIENTE

, ID_VENDEDOR

, FECHA_PEDIDO

, DayNumberOfYear(FECHA_PEDIDO) - DayNumberOfYear(Previous(FECHA_PEDIDO)) as DIAS_DISTANCIA

Resident Pedidos

Order by ID_CLIENTE, ID_VENDEDOR, FECHA_PEDIDO;

DROP Table Pedidos;

Pedidos:

LOAD *

Resident Pedidos_aux

Where DIAS_DISTANCIA > 365

DROP Table Pedidos_aux;

Evidentemente, habrá que adaptarlo a tu modelo de datos.

Un saludo y espero haberte ayudado.

Re: Intervalos entre fechas de pedido

Thu, 17 Jul 2014 08:41:02 GMT

Hola Emilio.

Muy buena propuesta, además de resolver esta situación de una manera muy eficaz, me ha ayudado a mejorar otros cálculos. Con estos tres campos (id_cliente, fecha_pedido, id_vendedor) la idea es calcular una serie de indicadores como:

- Tasa de recuperación de cliente: % de clientes que han hecho un pedido y hacia mas de un año que no lo hacían

- Tasa de perdida de clientes: % de clientes que hace mas de un año que no hacen un pedido.

- Tasa de adquisición: % Clientes que han realizado un cliente por primera vez

¿Se te ocurre como calcular la tasa de adquisición? Yo había pensado en hacer un count(fecha_pedido) para tener el total pedidos realizados, y si al restar el numero de pedidos en el periodo de análisis (mes, trimestre, etc) y el total de pedidos realizados el resultado es 0, entonces lo consideraría cliente nuevo.

¿Cómo lo ves?

Un saludo

Re: Intervalos entre fechas de pedido

Thu, 17 Jul 2014 08:57:30 GMT

Emilio: Me estoy dando cuenta de que al tener un histórico de pedidos de 3 años, tendría que realizar algún apaño del tipo:

left join (Pedidos)
Load
FECHA_PEDIDO,
if(year(FECHA_PEDIDO)= year (today()),0 ,
    if(year(FECHA_PEDIDO)= year (today())-1,365,
       if(year(FECHA_PEDIDO)= year (today())-2,730 ) )) AS DECALAJE
resident Pedidos;

Pedidos_aux:

LOAD ...

, ID_CLIENTE

, ID_VENDEDOR

, FECHA_PEDIDO

, (DayNumberOfYear(FECHA_PEDIDO) + DECALAJE) - (DayNumberOfYear(Previous(FECHA_PEDIDO)+DECALAJE)) as DIAS_DISTANCIA

Resident Pedidos

Order by ID_CLIENTE, ID_VENDEDOR, FECHA_PEDIDO;

DROP Table Pedidos;

Pedidos:

LOAD *

Resident Pedidos_aux

Where DIAS_DISTANCIA > 365

DROP Table Pedidos_aux;

¿Como lo ves?

Re: Intervalos entre fechas de pedido

etendero — Thu, 17 Jul 2014 09:07:34 GMT

Hola, me alegro que te haya servido.

Se me ocurriría algo similar a lo que planteas, siempre que te haya entendido bien. Escrito rápidamente y sin probar, para sacar el porcentaje, sería algo como:

Count(DISTINCT if( Count(TOTAL Fecha_Pedido) = 1, ID_Cliente)

Count(DISTINCT ID_Cliente)

¿verdad?

Un saludo.

Re: Intervalos entre fechas de pedido

etendero — Thu, 17 Jul 2014 09:29:20 GMT

Tienes razón,

La solución que planteas es correcta, siempre y cuando sólo tengas un histórico de 3 años como dices y que la fecha del pedido anterior no caiga en otro año.

Una solución para que contemple estos casos también, podía ser la siguiente:

, (DayNumberOfYear(FECHA_PEDIDO) + (((Year(Today()) - Year(FECHA_PEDIDO)) * 365))

- (DayNumberOfYear(Previous(FECHA_PEDIDO)) + (((Year(Today()) - Year(Previous(FECHA_PEDIDO))) * 365))

as DIAS_DISTANCIA

Un saludo.

Re: Intervalos entre fechas de pedido

Thu, 17 Jul 2014 09:41:17 GMT

Pensaba que al calcular el campo DECALAJE si funcionaria, aunque me doy cuenta de que debería de calcularlo de la siguiente manera:

left join (Pedidos)
Load
FECHA_PEDIDO,
if(year(FECHA_PEDIDO)= year (today()), 730 ,
    if(year(FECHA_PEDIDO)= year (today())-1,365,
       if(year(FECHA_PEDIDO)= year (today())-2, 0 ) )) AS DECALAJE
resident Pedidos;

Asi un pedido del 6/12/2014 tendría como dia del año ( 6 ) + decalaje ( 730 ) = 736 y un pedido del 30/12/2013 tendría como dia ( 364 ) + decalaje ( 365 )= 729 , siendo asi la distancia entre pedidos de 7 días, por lo que aparentemente si funciona .

Re: Intervalos entre fechas de pedido

etendero — Thu, 17 Jul 2014 10:21:14 GMT

Pero el DECALAJE lo estás calculando sobre la FECHA_PEDIDO, entonces le vas a aplicar el mismo DECALAJE tanto a FECHA_PEDIDO como a Previous(FECHA_PEDIDO) y no uno distinto a cada uno. Tu ejemplo sería:

Asi un pedido del 6/12/2014 tendría como dia del año ( 6 ) + decalaje ( 730 ) = 736 y un pedido del 30/12/2013 tendría como dia ( 364 ) + decalaje ( 365 730 ) = 729 1094, siendo así la distancia entre pedidos de 7 358 días, por lo que aparentemente si NO funciona.

Un saludo.

Re: Intervalos entre fechas de pedido

Thu, 17 Jul 2014 10:25:14 GMT

Tienes Razón. No había caído en que el decalaje se aplica únicamente a la fecha del pedido. Voy a ejecutar con la solución que me indicabas. Y luego a probar el tema de la tasa de adquisición, que aun no me he metido con ello.

gracias de nuevo.

Re: Intervalos entre fechas de pedido

Thu, 17 Jul 2014 11:45:55 GMT

Analizando y validando resultados, he caído en la cuenta de que un mismo cliente puede ser trabajado por varios vendedores, por lo que habría que controlar que se calcule el intervalo entre pedidos de cada vendedor en el mismo cliente.

Se me ocurre hacer un bucle variando el vendedor en cada pasada, pero me temo que podría ser bastante ineficaz.

Re: Intervalos entre fechas de pedido

etendero — Thu, 17 Jul 2014 12:09:35 GMT

Entonces podrías quitar al ID_Vendedor del Group By para que no afecte el vendedor en los cálculos.

Re: Intervalos entre fechas de pedido

Thu, 17 Jul 2014 12:51:34 GMT

¿En que group by? para el calculo del intervalo entre pedidos no hay ninguno...

Re: Intervalos entre fechas de pedido

etendero — Thu, 17 Jul 2014 12:58:37 GMT

El Group By que te puse en la tabla Pedidos_aux de la respuesta Re: Intervalos entre fechas de pedido, a no ser que te haya entendido mal el último problema que me has comentado.

Re: Intervalos entre fechas de pedido

Thu, 17 Jul 2014 13:09:37 GMT

¿Te refieres al order by?

Pedidos_aux:

LOAD ...

, ID_CLIENTE

, ID_VENDEDOR

, FECHA_PEDIDO

, DayNumberOfYear(FECHA_PEDIDO) - DayNumberOfYear(Previous(FECHA_PEDIDO)) as DIAS_DISTANCIA

Resident Pedidos

Order by ID_CLIENTE, ID_VENDEDOR, FECHA_PEDIDO;

DROP Table Pedidos;

Pedidos:

LOAD *

Resident Pedidos_aux

Where DIAS_DISTANCIA > 365

DROP Table Pedidos_aux;

Re: Intervalos entre fechas de pedido

Thu, 17 Jul 2014 13:34:51 GMT

También he detectado que se me duplican las fechas, apareciendo una con la diferencia de días entre pedidos y la otra a 0:

ID_VENDEDOR - ID_CLIENTE	FECHA	DIAS DISTANCIA
000044-022709	24/06/14	-542
000044-022709	24/06/14	0
000044-022709	18/12/13	0
000044-022709	18/12/13	6
000044-022709	12/12/13	0
000044-022709	12/12/13	64
000044-022709	09/10/13	0
000044-022709	09/10/13	125
000044-022709	06/06/13	0
000044-022709	06/06/13	76
000044-022709	22/03/13	0
000044-022709	22/03/13	55
000044-022709	27/01/13	-634
000044-022709	27/01/13	0
000044-022709	22/10/12	0
000044-022709	22/10/12	48
000044-022709	04/09/12	0
000044-022709	04/09/12	76
000044-022709	20/06/12	0
000044-022709	20/06/12	47

Re: Intervalos entre fechas de pedido

etendero — Thu, 17 Jul 2014 23:31:03 GMT

Disculpa Juan Antonio, cuando te escribí el primer código, quise poner Group By en vez de Order By. Mi intención era la de quitar los repetidos y dejar las combinaciones de ID_CLIENTE, ID_VENDEDOR y FECHA_PEDIDO diferentes. Por lo tanto, me refería a que hicieras el Group By ID_CLIENTE, FECHA_PEDIDO porque al quitar el ID_VENDEDOR, te aparecen las fechas de pedido independientemente del vendedor.

Un saludo y disculpa una vez más por la confusión.

Re: Intervalos entre fechas de pedido

Fri, 18 Jul 2014 06:59:59 GMT

Hola Emilio.

Pero al no tener ninguna agregación, ¿funcionaria el group by?

Re: Intervalos entre fechas de pedido

etendero — Fri, 18 Jul 2014 07:46:55 GMT

Como lo estamos montando en el script, con un Group By no hace falta una función de agregación como tal, con utilizar algún tipo de formula u operación es suficiente. Mi propuesta quedaría de la siguiente forma:

PedidosClientes:

LOAD

, ID_CLIENTE

, FECHA_PEDIDO

, Autogenerate(ID_CLIENTE & '-' & FECHA_PEDIDO) as ID_PedidosClientes

, (DayNumberOfYear(FECHA_PEDIDO) + (((Year(Today()) - Year(FECHA_PEDIDO)) * 365))

- (DayNumberOfYear(Previous(FECHA_PEDIDO)) + (((Year(Today()) - Year(Previous(FECHA_PEDIDO))) * 365))

as DIAS_DISTANCIA

Resident Pedidos

Group by ID_CLIENTE, FECHA_PEDIDO;

Inner Join(Pedidos)

LOAD ID_PedidosClientes

, DIAS_DISTANCIA

Resident PedidosClientes

Where DIAS_DISTANCIA > 365

DROP Table PedidosClientes;

He modificado algo más la propuesta para adaptarlo un poco más, dentro de mi desconocimiento de tu modelo de datos. Ahora utilizo un Inner Join para reducir tu tabla de pedidos. He generado una clave única con ID_CLIENTE y FECHA_PEDIDO para que no te cree una tabla sintética. Tendrás que añadir esa clave única también en tu tabla de Pedidos para que funcione la relación.

Un saludo.

Re: Intervalos entre fechas de pedido

Fri, 18 Jul 2014 11:07:41 GMT

Hola Emilio.

Ya logre hacerlo funcionar. Finalmente el código ha quedado se esta manera:

DISTANCIA_ENTRE_PEDIDOS:
LOAD

ID_VENDEDOR,
ID_CLIENTE,
FECHA_PEDIDO,

SUM((DAYNUMBEROFYEAR(FECHA_PEDIDO) + ((YEAR(FECHA_PEDIDO)-(YEAR(TODAY()))) * 365))
-
(DAYNUMBEROFYEAR(PREVIOUS(FECHA_PEDIDO))+((YEAR(PREVIOUS(FECHA_PEDIDO))-YEAR(TODAY())) * 365))) AS DIAS_ENTRE_PEDIDOS,

NUM((DAYNUMBEROFYEAR(TODAY()))
-
(DAYNUMBEROFYEAR(FECHA_PEDIDO) + ((YEAR(FECHA_PEDIDO)-YEAR(TODAY() )) * 365))) AS DIAS_HASTA_HOY

RESIDENT PEDIDOS

GROUP BY ID_VENDEDOR, ID_CLIENTE, FECHA_PEDIDO;

He modificado la parte en la que calculábamos el decalaje del año, haciendo que el año del pedido menos el año del hoy.

Por otra parte he calculado el numero de días con respecto a hoy de cada pedido.

De esta manera podre ver el numero de días a cada pedido y la distancia entre ellos.

Muchisimas gracias por tu ayuda Emilio. Ha sido la típica cosa que sobre el papel no parecía complicada de calcular, pero al final ha llevado su tiempo.

Un saludo

Re: Intervalos entre fechas de pedido

etendero — Fri, 18 Jul 2014 11:20:47 GMT

Me alegro de que lo hayas conseguido.

Un saludo.