Ask A Manager Salary Survey 2021
Visualización de los resultados de la encuesta “Ask A Manager Salary Survey 2021” en la que se recolectan datos sobre los sueldos que tienen los profesionales en diferentes disciplinas (principalmente en Estados Unidos).
El formulario de puede consultar en: https://www.askamanager.org/2021/04/how-much-money-do-you-make-4.html y los datos quedan alojados en un Google Sheets público que se actualiza constantemente: https://docs.google.com/spreadsheets/d/1IPS5dBSGtwYVbjsfbaMCYIWnOuRmJcbequohNxCyGVw/edit?resourcekey#gid=1625408792
Cuadro de Control: Ask A Manager Salary Survey 2021
Adicionalmente se presenta la descripción de las variables de la encuesta y de los pasos llevados a cabo para la limpieza y modelado de los datos necesarios para la construcción y actualización del cuadro de control.
1 Variables en base de datos original
Variables contenidas en la fuente de datos de la encuesta.
Tabla: Variables originales de la fuente de datos
2 Variables luego de modeladas
Variables resultantes de la limpieza y modelado de datos.
Tabla: Variables del modelo - tabla “Data”
Tabla: Variables del modelo - tabla “JobUnpivot”
Tabla: Variables del modelo - tabla “Country”
Tabla: Variables del modelo - tabla “City”
Tabla: Variables del modelo - tabla “Currency”
3 Paso a paso para actualizar los datos y aplicar el modelado diseñado
Se asume que la estructura de la base de datos original no cambia en nuevas versiones.
Los siguientes pasos son para crear las tablas desde cero, pero también sirven de guía para actualizar los datos.
Descargar la base de datos de la encuesta ( https://docs.google.com/spreadsheets/d/1IPS5dBSGtwYVbjsfbaMCYIWnOuRmJcbequohNxCyGVw/edit?resourcekey#gid=1625408792 ) y actualizar la hoja “Form Responses 1” de la hoja de cálculo de modelado ( https://docs.google.com/spreadsheets/d/1Y-aKeclzRRlcizEadRUf-wAdJWv_dMxY3_81yM4gYmM/edit?usp=sharing ).
Crear la hoja “Data” como copia de la hoja “Form Responses 1”, agregar al comienzo la columna “ID” como consecutivo numérico. En caso de actualización sólo agregar las filas nuevas.
En la hoja “Data” en la columna “Annual salary”, eliminar las comas “,” de los datos, si el valor es inferior a 1000 puede ser porque el usuario lo escribió en miles entonces validar si es consistente con la información de las columnas “Additional context on income” y “Currency” y multiplicar por mil.
En la hoja “Data” en la columna “Other monetary comp”, eliminar las comas “,” de los datos, si el valor es inferior a 1000 puede ser porque el usuario lo escribió en miles entonces validar si es consistente con la información de las columnas “Additional context on income” y “Currency” y multiplicar por mil.
Crear la hoja “Job title”, copiar de la hoja “Data” las columnas “ID” y “Job title”, separar en columnas la columna “Job title” y seleccionar las 4 primeras columnas resultantes, colocarles como encabezado de columna “Job1” a “Job4” y eliminar las columnas restantes, ubicarse dos filas después de la última fila y tokenizar las columnas “Job” utilizando la fórmula “=ArrayFormula(SPLIT(FLATTEN(B1:E1&"|"&A2:A27658&"|"&B2:E27658),"|"))” garantizando que se están tomando todos los rangos de las columnas en cada actualización. Crear la hoja “JobUnpivot”, copiar de la hoja “Job title” las últimas dos columnas resultantes de la fórmula, colocarle como encabezado a esas columnas “ID” y “JobUnpivot” y eliminar las filas en las que la celda de “JobUnpivot” aparezca vacía.
Crear la hoja “Country”, copiar de la hoja “Data” la columna “Country”, ordenar y eliminar los valores repetidos, agregar la columna “CountryFilter” en la que se colocará para cada valor de “Country” el valor estandarizado del País relacionado y se irá ampliando con cada actualización, en caso de no identificar el país se dejará por defecto “United States”
En la hoja “Data” crear la columna “CountryFilter” y autocompletar las celdas desde la hoja “Country” con la fórmula “=BUSCARV(L2,Country!A:B,2,0)”
Crear la hoja “City”, copiar de la hoja “Data” las columnas “City” y los valores de “CountryFilter”, ordenar y eliminar los valores repetidos, agregar la columna “CityFilter” en la que se colocará para cada valor de “City” el valor estandarizado de la Ciudad y el País relacionado y se irá ampliando con cada actualización, en caso de no identificar la ciudad se dejará por defecto la capital del País.
En la hoja “Data” crear la columna “CityFilter” y autocompletar las celdas desde la hoja “City” con la fórmula “=BUSCARV(L2,Country!A:B,2,0)”
Crear la hoja “Currency”, copiar de la hoja “Data” la columna “Currency”, si en esta columna el valor es “Other” entonces copiar los datos de la columna “Currency - other”, eliminar los valores duplicados y ordenar, agregar la columna “CurrencyFilter” en la que se colocará para cada valor de “Currency” el código ISO relacionado que se irá ampliando con cada actualziación, agregar la columna “CurrencyCOP” en la que se calcula el valor equivalente en Pesos Colombianos a la fecha con la fórmula “=GOOGLEFINANCE(CONCAT(B2,"COP"))”
En la hoja “Data” crear la columna “Annual salary COP”, convertir el valor de la columna “Annual salary” a pesos Colombianos con la fórmula “=SI(I2="Other",G2*BUSCARV(J2,Currency!A:C,3,0),G2*BUSCARV(I2,Currency!A:C,3,0))”
En la hoja “Data” crear la columna “Other monetary comp COP”, convertir el valor de la columna “Other monetary comp” a pesos Colombianos con la fórmula “=SI(I2="Other",H2*BUSCARV(J2,Currency!A:C,3,0),H2*BUSCARV(I2,Currency!A:C,3,0))”
En Google DataStudio agregar o validar que ya existen como fuentes de datos las hojas “Data”, “JobUnpivot”
En la Fuentes de datos agregar o validar que ya existen los datos combinados (ver cómo hacerlo en https://support.google.com/datastudio/answer/9061420 )
Comentarios
Publicar un comentario