Como crear un panel de analisis de enlaces

Como crear un panel de analisis de enlaces

Analizar su propio perfil de enlace, así como el de sus competidores, es una tarea sumamente importante para los creadores de enlaces, auditores de enlaces o aquellos que diagnostican caídas de tráfico

Pero gran parte del trabajo implica estar limitado por la propia funcionalidad de interfaz de usuario del software de rastreo de enlaces, y no puede manipularse tan fácilmente como en una hoja de cálculo.

Por lo tanto, con mayor frecuencia, al analizar los perfiles de enlace, exportamos todos los datos y analizamos los enlaces en las hojas de cálculo con algunos filtros y tablas dinámicas, pero sabemos que debe haber una forma más eficiente.

Limitaciones y problemas de ejemplo con la típica hoja de cálculo o enfoque de software de enlace:

  • ¿Cómo aplica múltiples filtros a un perfil de enlace a la vez en una hoja de cálculo, de manera eficiente?
  • ¿Cómo se guarda la lógica en las reglas de filtrado para reutilizar una y otra vez?
  • ¿Cómo actualiza fácilmente los perfiles de enlaces mensualmente sin sobrescribir el trabajo anterior?
  • ¿Cómo puede apilar varios perfiles de enlace uno encima del otro para extraer patrones comunes?
  • Descubrí que aprender y usar la fórmula de consulta de Google para construir paneles MVP en las hojas de Google es una forma increíble de cerrar la brecha entre el análisis estándar y el software personalizado completo.

Conceptos básicos de la función de consulta

Si tiene experiencia de nivel intermedio con funciones de hoja de cálculo y se siente cómodo con vlookups y coincidencia de índice, encontrará que la función de consulta es una fórmula extremadamente poderosa que es más fácil de lo que parece después de unas horas de práctica.

Pero, ¿cuál es la función de consulta?

El experto en Google Sheets, Ben Collins, lo expresa perfectamente :

“Le permite usar un poderoso código de base de datos (un pseudo-SQL, lenguaje de consulta estructurado, el código utilizado para comunicarse con las bases de datos) para manipular sus datos en Google Sheets y es SÚPER poderoso .

Podría decirse que es la función más poderosa en Google Sheets ".

Al seguir esta guía, podrá crear el panel sin conocimientos previos de consulta, pero si desea algunas lecciones de introducción rápida, aquí están algunos de mis favoritos:

  • Función de consulta de hojas de cálculo de Google: la función más poderosa de las hojas de cálculo de Google
  • Función de consulta de Hojas de cálculo de Google: Domina el arma de la pereza masiva
  • Tutorial de la función QUERY de Google Sheets (video)
  • Referencia de la función QUERY de Google (úsela como referencia después de comprender cómo funciona)

Configura la hoja de Google

Sé que siempre me gusta saltar a la plantilla, pero le recomiendo que siga este proceso por su cuenta para que desarrolle ese músculo y aprenda los conceptos básicos de la consulta en menos de una hora.

Si debe tener la plantilla ahora, la he vinculado hacia el final, ¡pero trate de resistir!

Para comenzar, simplemente cree una nueva hoja de Google, con sheet.new escrita en su navegador.

Luego, cree una pestaña llamada 'Panel de control' y una pestaña para el nombre del primer sitio web cuyo perfil de enlace está analizando.

Pensé demasiado en qué sitio web usar como ejemplo, y quería un sitio web cuyo perfil de enlace sería algo relatable (también conocido como The New York Times o YouTube), así que elegí Newman's Own ya que donan el 100% de las ganancias para la caridad.

Entonces, en este escenario, podemos analizar los enlaces de Newman's Own como si trabajáramos para la compañía nosotros mismos o como una nueva compañía de alimentos caritativa que está ingresando al mercado.

Para la herramienta de búsqueda de enlaces, estoy usando Ahrefs, pero puedes usar cualquier software de rastreo de enlaces que prefieras, como Moz, Majestic, SEMrush, etc.

Tenga en cuenta que tendrá que ajustar sus fórmulas de filtrado según el software de enlace que esté utilizando, pero la lógica subyacente es la misma.

Exportar datos de enlace para el primer sitio web

Discutiremos el análisis de varios sitios más adelante, pero por ahora comencemos con el primer sitio web, ya sea el suyo o el de un competidor superior.

Aquí buscamos www.newmansown.com en Ahrefs Site Explorer y establecemos los filtros en Un enlace por dominio , Dofollow y Live .

La configuración de Un enlace por dominio es la única crítica aquí para mantener limpios los datos. Todavía puede funcionar con Group similar o All pero tiende a darle demasiados duplicados que terminan siendo mucho ruido.

Si prefiere analizar también los enlaces nofollow, puede dejar esa configuración desactivada. También puede establecer enlaces recientes o históricos si los prefiere.

Luego exportamos todas las filas. Si trabaja con sitios enormes, puede elegir limitar los enlaces a una cantidad más manejable, como los 10,000 principales.

Deberá ajustar las columnas de Excel para aquellas que sean demasiado estrechas, como First Seen y Last Check.

Luego vaya a la pestaña que creó, en este caso, 'newmans' e importe los datos en la hoja.

Pequeño consejo: puede copiar y pegar, pero me he encontrado con algunos errores en algunos perfiles, donde algunas de las filas de enlaces se mezclan en las celdas debido a un problema de formato que desordena el pegado.

Construir consulta inicial simple

Ahora podemos construir la primera fórmula de consulta para probar que todo está funcionando.

La función de consulta tiene el siguiente formato: CONSULTA (datos, consulta, [encabezados])

Lo que resultará en esto como una prueba inicial, extrayendo todos los datos

Si el sitio web con el que está trabajando tiene una tonelada de enlaces, puede recibir este error.

Si Google Sheet no agrega automáticamente filas para usted, continúe y agregue la cantidad de filas que necesita

Construir filtro inicial

Ahora que sabemos que la fórmula de consulta funciona y está obteniendo todos los datos, agreguemos un filtro para obtener solo las filas seleccionadas.

Escaneé el perfil del enlace y noté que algunos de los títulos de las páginas de referencia incluían 'ético', así que voy a probar con eso como filtro.

En la segunda parte de la fórmula, comenzamos a escribir "select * donde G contiene 'Ethical'" .

Esto significa que queremos seleccionar todos los datos donde la columna G contiene 'ético'.

Y ahora podemos ver que se muestran las 7 filas con ética en el título. ¡Muy genial!

Expandir con filtros adicionales * desplegables

Ahora que tenemos la fórmula de consulta funcionando, crearemos una lista de fórmulas y las nombraremos en una tabla de búsqueda simple. Esto nos permitirá crear un menú desplegable para seleccionar diferentes formas de ver rápidamente los datos.

Para comenzar, agregaremos nuestro nombre "ético", así como un nombre, y lo colocaremos en las columnas I y J con encabezados.

Lo que estamos haciendo es agregar solo la cláusula de la fórmula de consulta que cambiará cada vez.

Además de proporcionarnos un menú desplegable, esto nos permitirá agregar múltiples fórmulas y leer más claramente cómo se crean para la futura depuración.

Agreguemos algunos más con algunos parámetros más.

Crearemos uno para vincular dominios con una calificación de dominio (DR) superior a 70. Luego, crearemos un tercero que combine ambos

Luego crearemos un cuadro desplegable en A2 junto con el vlookup en B2 que buscará el nombre de la opción desplegable y lo buscará en nuestra tabla de referencia

Crearemos la fórmula para el vlookup: = vlookup (A2, I1: J5,2, falso)

Ahora que está funcionando, podemos ver la misma cláusula para la fórmula de consulta en B2

Nex, en A2, crearemos el menú desplegable. Vamos a Datos -> Validación de datos

Y luego seleccione I2: I4 para las opciones desplegables

Ahora, cuando volvemos a mirar a A2, podemos ver el menú desplegable con nuestras 3 opciones

Prueba con la opción DR> 70. Pero no cambia la tabla de datos a continuación porque todavía tenemos que actualizar la función de consulta original para extraer la información en B2.

Entonces volvemos a la fórmula de consulta, y la vamos a ajustar para hacer referencia a la información B2.

Eliminamos la sección codificada de la sintaxis de la consulta, comenzando con la sección where . Ahora vamos a concatenar agregando "& B2 &" "

¡Y parece estar funcionando bien!

Probar la tercera opción en el menú desplegable y eso también funciona

Fecha actualización el 2021-12-17. Fecha publicación el 2019-12-17. Categoría: semrush Autor: Oscar olg Mapa del sitio Fuente: searchenginejournal