CASO ELABORAR Y AUTOMATIZAR PRESUPUESTO DE VENTAS

ConeClick tiene dos formas de vincular archivos a sus proyectos: subiéndolos directamente a la aplicación y guardándolos en un bucket de Amazon S3 que le asigna la aplicación a cada usuario o bien vinculando archivos que se tengan actualmente en los Buckets de Amazon S3 que sean propiedad del usuario. Aquí en esta sesión veremos los dos casos.

Subir archivo desde la PC a la cuenta de ConeClick:

1. Bajar el siguiente archivo a su PC y guardarlo en la carpeta de su preferencia: sells_pricing.csv

2. En la aplicación dar click en Subir Archivo y seleccionar el archivo que acabamos de bajar y Abrir.

3. Click en Subir Archivo y Aceptar en la confirmación de que el archivo subió adecuadamente. Automáticamente el sistema iniciará a descubrir la MetaData del archivo (nombre de los campos y tipo de campos). Este proceso tarda unos cuantos minutos pero solo se debe realizar una sola vez por cada archivo nuevo.

4. En el panel izquierdo verás un signo mas (+). Dar click en el mas (+) y debes ver el nombre del archivo que acabamos de subir. Sobre el nombre del archivo damos click derecho y click en Seleccionar - usar - anexar archivo en el proyecto. Esto hará que el archivo se anexe al proyecto.

5. Dar click derecho sobre el nodo del archivo que anexamos al proyecto y dar click en Ver Archivo. Con esta opción se puede ver la información que contiene el archivo.

Notas: La misma aplicación descubre los tipos de cada campo (si son enteros, fechas, texto, etc...). Si se desean cambiar se puede dar click derecho sobre el archivo en el panel izquierdo y seleccionar Modifcar Metadata.

Vincular archivos desde otros Buckets de Amazon S3:

Para este ejercicio tenemos otros archivos que se encuentran en otro Bucket de AWS S3. Aquí los vamos a vincular para ser usados en este proyecto.

En el panel izquierdo hay un bucket (carpeta) llamado democone. En este se encuentran diferentes archivos que usamos para demostraciones y pruebas.

6. Al dar click sobre el signo mas (+) al lado izquierdo del nombre del bucket democone se pueden ver los diferentes archivos que contiene este bucket de AWS - S3.

Si quieres aprender como vincular buckets y archivos desde otras cuentas de AWS S3 puedes seguir el siguiente manual.

Explorar archivo:

7. En el bucket democone (debe estar desplegado.. sino darle click al mas(+) que hay al lado izquierdo del bucket) dar click derecho en el archivo que se llama sells_history y click en Seleccionar - usar - anexar archivo en el proyecto. Esto hará que el archivo se anexe al proyecto.

8. Dar click derecho sobre el nodo del archivo que anexamos al proyecto y dar click en Ver Archivo

Este archivo es muy extenso (tiene casi 5 millones de registros) por lo que no puede ser manipulado fácilmente en un procesador de datos como excel o access o ser procesado sin un servidor que le de capacidad de cómputo. Esta es una de las cosas que veremos como se trabaja de fácil en la aplicación con archivos muy grandes procesando millones de datos en segundos sin necesidad de instalar un servidor.

9. Los archivos se precargan para visualización. En este punto vamos a explorar el archivo.

9a. Con el scroll tanto vertical como horizontal podemos ver las primeras mil filas de la información del archivo.

9b. Click en Ir a Fin. Esto mostrará las últimas 1.000 filas del archivo.

9c. Click en Ir a Mitad. Esto situará el archivo en la mitad entre las filas 2.337.064 y la 2.338.062

9d. Bajamos el archivo usando el scroll vertical hasta el registro 2.338.062 y en la mitad de la pantalla aparece un botón que dice Ver mas registros. Damos click en este botón y se cargan otros mil registros del archivo.

9e. En la parte superior de la pantalla se puede apreciar el número de filas totales que tiene el archivo 4.675.126 y el número de filas que se está visualizando en esta pantalla. Si se desea ir a una fila en particular, en la parte superior de la pantalla hay un cuadro que dice "ir a la fila número". Ingresar 4.000.000 en este cuadro y dar click en el botón consultar. Esto nos situará en la fila 4.000.000 del archivo.

10. Ordenar registros: Dar Click en el boton Ordenar. Esto activará el menú ordenar. Seleccionar el campo year y month. Click en la primera flecha -> y en la columna Ordenar seleccionar descendente para los 2 campos (year,month) y por último Ejecutar.

11. Filtrar: Dar Click en el botón Filtrar. Esto activará el menú de Filtrar. Seleccionar el campo year y el campo month. En year seleccionar en la columna de operador "igual que" y en valor ingresar 2019. En month seleccionar en columna operador "comprendido en una lista" seleccionar el + que hay al lado de la columna valor (esto cargará los valores posibles) y seleccionar 1 y -> y 2 y -> y click en Aceptar. En la columna Cond seleccionar "And" y click en Ejecutar. Con esta instrucción se filtrará el archivo devolviendo los registros que cumplan la condición de año=2019 y los meses sean 1 o 2; osea los meses de Enero y Febrero del 2019.

12. Para terminar esta parte del ejercicio vamos a dar click derecho en cada uno de los nodos que insertamos y seleccionar Eliminar del proyecto. Esto eliminará los archivos del proyecto pero no del servidor por lo que se pueden volver a usar más adelante.

En este ejercicio calcularemos el presupuesto anual para una empresa de comercio electrónico.

Fecha importantes a tener en cuenta: Estamos a inicios del año 2019 y necesitamos construir el presupuesto de este año de acuerdo a las ventas de los años anteriores (y1=año 2018. y0=año 2017)

La información histórica de ventas se maneja en un archivo llamado sells_history. Este es un archivo transaccional en donde se registran entre otros datos el número de la orden de venta (order_id), las unidades vendidas en esa orden (units_sold), el tipo de producto vendido (item_type), el país en donde se realizó la venta(country), el año(year), mes (month) y día de la venta(day).

El presupuesto se maneja a nivel de producto (item_type) y de región (country) de tal forma que se incluyan las dinámicas regionales en cada uno de los productos de la compañía.

Para este fin partimos del archivo sells_history que tiene 4.675.126 filas correspondientes a las ventas históricas desde el 2010 y veremos como se procesa de rápido a traves de la aplicación.

Para esto seguiremos los siguientes pasos que además se puede revisar en el video tutorial.

a. Obtener un resumen de las ventas por cada mes.

b. Consolidar dicha información por año.

c. Calcular el crecimiento en ventas del año anterior (para este caso sería entre 2017 y 2018) por cada producto en cada país.

d. Aplicar algunas reglas al crecimiento. Por ejemplo, en este ejercicio cada producto en cada país debe crecer mínimo el 1% y máximo el 10%. Esto se hace para evitar crecimientos muy bajos o incluso negativos o crecimientos muy altos que sean dificiles de cumplir.

e. Calcular el presupuesto para el año (2019 en este caso). Para esto se toman las ventas por producto y por país del año anterior (2018) y se les aplica el porcentaje de crecimiento que se determinó en el punto anterior por producto y por país.

f. Al final se guarda el proyecto y se limpia el canvas para próximos proyectos.

En la parte 1 de este ejercicio se calculó el presupuesto para el año 2019 por producto y por región (país).

Uno de los supuestos que se tuvieron en cuenta fue crecer las ventas mínimo un 1% y máximo un 10%.

En esta parte del ejercicio queremos saber que tan adecuada es esta condición y que pasaría si la cambiáramos?

Para esto, vamos a calcular cual es el presupuesto total de ventas para el año 2019, calcularemos que tanto está creciendo con respecto al año actual (2018) y compararemos este crecimiento con el crecimiento de las ventas entre el año 2017 y 2018.

Con base en esta información calcularemos que pasaría con el presupuesto si cambiáramos la condición de que las ventas crezcan mínimo 1% y máximo 10% por que crezcan mínimo un 1% y máximo un 2%.

Para esto seguiremos los siguientes pasos que además se puede revisar en el video tutorial.

a. Se abre el proyecto que hemos venido trabajando en la parte 1.

b. Sobre la tabla en donde se tiene calculado todo el presupuesto (ventas_pto_year) se calculará cuanto fueron las ventas totales del año 2017 (sum_ventas_y0), las del año 2018 (sum_ventas_y1) y a cuanto asciende en total el presupuesto de ventas del año 2019(sum_pto_year)

c. Una vez tengamos estos valores se convertirá la variable sum_pto_year que se encuentra en notación científica a decimal y se calcularán las variaciones del presupuesto contra el año 2018(var_pto) y la variación del año 2017 vs 2018 (var_year).

d. La información la vamos a copiar y pegar en una hoja de excel para volverla a consultar.

e. Ahora cambiaremos el supuesto del crecimiento mínimo del 1% y máximo del 10% por un crecimiento mínimo del 1% y máximo del 2%. Esta condición se cambiará en el query q_var_regla.

f. Una vez se haya cambiado la condición, se ejecutarán los siguientes nodos (q_var_regla, q_pto_year,q_resumen_pto y q_var_resumen) y este resultado se copiará en la hoja de excel donde se tiene el otro resultado para compararlo. Esta es una de las ventajas de la automatización del proceso, ya que solo se tiene que construir el flujo una única vez y luego se puede ejecutar las veces que se desea obteniendo los resultados en segundos.

Con los datos que obtuvimos dejamos la regla de crecimiento de mínimo el 1% y máximo 2% ya que los resultados son más coherentes (el crecimiento del presupuesto contra el año 2018 es más acorde con el crecimiento del año 2018 vs 2017).

Debido a que el proyecto se autoguarda y ya se le dió un nombre en la parte1, procedemos a cerrarlo y a limpiar el canvas.

En la parte 1 de este ejercicio se calculó el presupuesto para el año 2019. Hasta el momento tenemos el presupuesto total para el año 2019 por producto (item_type) y por país (country).

Debido a que la empresa necesita hacer seguimiento mes a mes a sus ventas y determinar la ejecución presupuestal, se necesita tener el presupuesto para cada mes.

Para esto calcularemos la estacionalidad (que porcentaje de las ventas anuales se hacen en un mes en particular) por producto(item_type) y por país(country) utilizando la información histórica que tenemos desde el año 2010.

En esta parte seguiremos los siguientes pasos y además se puede revisar el video tutorial.

a. Se abre el proyecto que hemos venido trabajando desde la parte 2.

b. Del archivo ventas_mes calculamos el total de ventas que se han hecho en cada uno de los meses durante el periodo de análisis. Es como decir cuánto se vende en todos los Eneros, Febreros, etc... por item_type y por country. El resultado se guarda en el archivo ventas_total_month en la columna ventas_m.

c. Del archivo ventas_mes calculamos cuánto se vendió durante todo el periodo de análisis por country y por item_type. El resultado se guarda en el archivo ventas_total en la columna ventas_y.

d. Se cruzan los archivo ventas_total_month con el archivo ventas_total por country y por item_type. El resultado se guarda en el archivo ventas_month_total. En este archivo tendremos en la columna ventas_m cuanto se vende en cada país (country) por cada producto (item_type) en cada mes y en la columna ventas_y tenemos cuanto se vende en total en cada país y en cada producto.

e. Calculamos la estacionalidad: est=ventas_m*1.0/ventas_y y se guarda en el archivo ventas_estacionalidad. Esta variable indica que porcentaje de las ventas de un artículo en determinado país se hace en cada uno de los meses.

f. Cruzamos el archivo donde se encuentra la estacionalidad (ventas_estacionalidad) con el archivo donde se encuentra el presupuesto total del año por país y por producto (ventas_pto_year columna pto_year) y el resultado se guarda en el archivo ventas_pto_estacionalidad.

g. Cálculo del presupuesto mes a mes pto_month=est*pto_year y el resultado se guarda en el archivo ventas_pto_month.

h. Al final validamos que el presupuesto total anual que teníamos calculado por producto y por país en el archivo ventas_pto_year sea igual al presupuesto que acabamos de calcular por producto, país y mes en el archivo ventas_pto_month.

Debido a que el proyecto se autoguarda y ya se le dió un nombre en la parte1, procedemos a cerrarlo y a limpiar el canvas.

En los pasos anteriores diseñamos todo el flujo para el cálculo del presupuesto mensual del año 2019.

Ahora imaginémonos que ha pasado un año y necesitamos calcular el presupuesto para el año 2020. Si este flujo no estuviera documentado y automatizado tendríamos que volver a realiza el procedimiento, es decir, volver a cruzar los archivos, volver a poner los supuestos de crecimiento y decidir con cual supuesto de crecimiento realizar el presupuesto, volver a calcular la estacionalidad y por último obtener el presupuesto mes a mes para el año 2020.

Esta actividad se vuelve dispendiosa para muchas organizaciones que no tienen automatizado el proceso y que tienen que lidiar con múltiples archivos de gran tamaño en excel.

Es por esto que esta sección es tan importante porqué veremos como con ConeClick este proceso es super sencillo y super rápido debido a que ya tenemos todo el flujo diseñado. Veremos que es solo cambiar unos parámetros y volver a ejecutar el flujo para obtener el presupuesto del año 2020.

Para esto seguiremos los siguientes pasos y además se puede revisar el video tutorial.

Fecha importantes a tener en cuenta: Estamos a inicios del año 2020 y necesitamos construir el presupuesto de este año de acuerdo a las ventas de los años anteriores (y1=año 2019. y0=año 2018)

a. Abrir el proyecto que hemos venido trabajando en las anteriores secciones.

b. En el paso 1 de este tutorial dejamos unos comentarios para poder identificar los puntos en el flujo que deberíamos cambiar. Como se va a calcular el presupuesto del año 2020, el año base sería 2018 y el año de referencia para calcular los crecimientos sería el año 2019. Se edita entonces el query q_ventas_y0 (en el filtro del año cambiar 2017 por 2018) y en el query q_ventas_y1 (en el filtro cambiar 2018 por 2019).

c. Y listo. Volvemos a ejecutar todo el flujo y en segundos obtendremos el presupuesto del año 2020.

Hasta el momento tenemos calculado el presupuesto del año 2020 mes a mes y ahora vamos a suponer que ya tenemos la información de las ventas del año 2020 (hasta el mes 8) por lo que vamos a generar los diferentes informes que nos indiquen si vamos o no cumpliendo el presupuesto. De esta forma, queremos conocer cuánto hemos vendido, cuánto deberíamos haber vendido (ejecución presupuestal) y cuánto se han incrementado las ventas con respecto al año anterior (2019).

Para esto seguiremos los siguientes pasos y además se puede revisar el video tutorial.

Fechas importantes a tener en cuenta: Estamos a inicios de Septiembre del año 2020 y por lo tanto ya conocemos las ventas del año 2020 desde Enero hasta Agosto.

a. Abrir el proyecto que hemos venido trabajando en las anteriores secciones.

b. Volver a loguearse en el bucket democone. La contraseña se encuentra en esta misma página en la seccion vincular archivos desde otros Buckets de Amazon S3.

c. Anexar al proyecto el archivo sells_year. Este archivo es el archivo transaccional de ventas del año actual (desde Enero - mes 1 hasta Agosto - mes 8).

d. Seleccionamos el nodo unir - apilar - combinar archivos y el resultado se guardar en el archivo une_ventas. En este momento no anexaremos ningún archivo pero en la siguiente sección veremos como esta funcionalidad nos permite ir agregando al archivo une_ventas las ventas de los meses sucesivos.

e. Totalizamos las ventas del año actual (columna ventas_act) por mes(month), país(country) y por producto(item_type). Esta información se guarda en el archivo ventas_act.

f. Cruzar el archivo ventas_act con el archivo que construimos en la parte 3 y 4 de este caso (ventas_pto_mes que es el archivo donde tenemos el presupuesto mes a mes del año 2020). Este cruce se hace por mes, país y producto y se crea el archivo ventas_act_pto y en la columna ventas_pto tendremos el presupuesto para cada producto y país en cada uno de los meses.

g. Obtener la información del año anterior del archivo ventas_mes (que habíamos trabajado en la parte 1) filtrando la información por año (year=2019). El resultado se guardará en el archivo ventas_ant y en la columna ventas_ant tendremos la información de las ventas del año anterior por cada producto y país para cada uno de los meses.

h. Cruzamos el archivo donde se tiene la información del año anterior (ventas_ant) con el archivo que hemos venido construyendo (ventas_act_pto) donde tenemos la información de las ventas del año actual (ventas_act) y del presupuesto (ventas_pto). El resultado de este cruce se guarda en el archivo ventas_act_ant_pto.

i. Generar el informe de seguimiento total. Este informe indica en total hasta el mes 8 cuánto se ha vendido (act_venta_act), cuantó deberíamos haber vendido de acuerdo al presupuesto hasta el mes 8 (act_ventas_pto) y cuánto habíamos vendido hasta el mes 8 del año pasado(act_ventas_ant). Este primer informe se guarda en el archivo ventas_seg_total.

f. Generar informe de seguimiento presupuestal por región (country). Este informe indica cuánto se ha vendido por región hasta el mes 8 y compara con el presupuesto y con lo que se había vendido hasta el mes 8 el año pasado. Este informe se guarda en el archivo ventas_seg_country.

g. Generar informe de seguimiento presupuestal por producto (item type). Similar a los anteriores informes pero con una vista de producto. El resultado de este informe se guarda en el archivo ventas_seg_prod.

h. Generar informe de tendencia mensual. Indica para cada mes cuanto se vendió, el presupuesto de cada mes y las ventas de cada mes del año pasado. Este informe se guarda en el archivo ventas_seg_tend.

Debido a que el proyecto se autoguarda y ya se le dió un nombre en la parte1, procedemos a cerrarlo y a limpiar el canvas.

En la sección anterior trabajamos en el seguimiento presupuestal del año 2020 hasta el mes 8 (Agosto) y ejecutamos los diferentes informes para analizar la ejecución presupuestal.

Ahora vamos a suponer que ha pasado un mes y ya tenemos la información del mes de Septiembre (mes 9 del año 2020) y se necesita volver a generar los informes de seguimiento presupuestal pero con corte a Septiembre del 2020.

Al igual que con las anteriores secciones, veremos como este proceso es super sencillo usando la aplicación ConeClick. Solo se requiere actualizar un archivo y volver a ejecutar el flujo del proceso para obtener de forma automática el seguimiento presupuestal.

Para esto seguiremos los siguientes pasos y además se puede revisar el video tutorial.

Fechas importantes a tener en cuenta: Estamos a inicios de Octubre del año 2020 y por lo tanto ya conocemos las ventas del año 2020 desde Enero hasta Septiembre.

a. Abrir el proyecto que hemos venido trabajando en las anteriores secciones.

b. Volver a loguearse en el bucket democone. La contraseña se encuentra en esta misma página en la seccion vincular archivos desde otros Buckets de Amazon S3.

c. Anexar al proyecto el archivo sells_month. Este archivo es el archivo transaccional de ventas del mes de Septiembre 2020 - mes 9.

d. Editar el nodo q_une_ventas, anexar el archivo sells_month y en tipo de unión seleccionar UNION ALL. Recordemos que este nodo lo creamos en la parte 5 de este caso y tiene como objetivo ir apilando las ventas de los diferentes meses para unirlas en un solo archivo que se llama une_ventas. El tipo de unión que seleccionamos combina los archivos anexando todos los registros pero hay otros tipos de uniones como UNION (combina los archivos sin repetir los registros duplicados de ambos archivos), INTERCEPT (Combina dos o más archivos y el resultado será solo las filas o registros duplicados en ambos archivos) o EXCEPT(combina dos archivos y el resultado será los registros o filas que estén en el primer archivo pero no en el segundo).

e. Ejecutar de nuevo los nodos (q_une_ventas, q_ventas_act, q_ventas_act_pto, q_ventas_act_ant_pto) y ejecutar los nodos que tienen que ver con los informes (q_ventas_seg_total, q_ventas_seg_county, q_ventas_seg_prod, q_ventas_seg_tend).

Y listo. De esta forma obtenemos de manera rápida los informes de seguimiento presupuestal con corte a Septiembre 2020.

Debido a que el proyecto se autoguarda y ya se le dió un nombre en la parte1, procedemos a cerrarlo y a limpiar el canvas.

Para finalizar vamos a eliminar el proyecto con el que hemos venido trabajando junto con los archivos que se crearon. Este video muestra la forma en que se debe eliminar el proyecto y los diferentes archivos.