SEO Technique

Comment répondre à des questions complexes sur les données avec les données Oncrawl, en dehors d’Oncrawl ?

4 janvier 2022 - 27  min de lecture - par Tanguy Moal
Accueil > SEO Technique > Répondre à des questions complexes sur les données

L’un des avantages d’Oncrawl pour le SEO d’entreprise est d’avoir un accès complet à vos données brutes. Que vous connectiez vos données de SEO à une BI ou à un workflow de data science, que vous effectuiez vos propres analyses ou que vous travailliez dans le cadre des directives de sécurité des données de votre organisation, les données brutes de SEO et d’audit de site Web peuvent servir à de nombreux usages.

Aujourd’hui, nous allons voir comment utiliser les données Oncrawl pour répondre à des questions de données complexes.

Qu’est-ce qu’une question complexe sur les données ?

Les questions portant sur des données complexes sont des questions auxquelles il est impossible de répondre par une simple consultation de la base de données, mais qui nécessitent un traitement des données afin d’obtenir la réponse.

Voici quelques exemples courants de questions de données « complexes » auxquelles les SEO sont souvent confrontés :

  • Création d’une liste de tous les liens pointant vers des pages qui redirigent vers d’autres pages avec un statut 404.
  • Création d’une liste de tous les liens et de leur texte d’ancrage pointant vers des pages dans une segmentation basée sur des métriques non-URL.

Comment répondre à des questions complexes sur les données dans Oncrawl ?

La structure de données d’Oncrawl est conçue pour permettre à presque tous les sites de consulter des données en temps quasi réel. Cela implique de stocker différents types de données dans différents ensembles de données afin de s’assurer que les temps de consultation sont réduits au minimum dans l’interface. Par exemple, nous stockons toutes les données associées aux URL dans un ensemble de données : code de réponse, nombre de liens sortants, type de données structurées présentes, nombre de mots, nombre de visites organiques… Et nous stockons toutes les données relatives aux liens dans un ensemble de données distinct : cible du lien, origine du lien, texte d’ancrage…

La jonction de ces ensembles de données est complexe sur le plan informatique et n’est pas toujours prise en charge par l’interface de l’application Oncrawl. Lorsque vous souhaitez rechercher quelque chose qui nécessite de filtrer un ensemble de données afin de rechercher quelque chose dans un autre, nous vous recommandons de manipuler les données brutes par vous-même.

Comme toutes les données Oncrawl sont à votre disposition, il existe de nombreuses façons de joindre des ensembles de données et d’exprimer des requêtes complexes.

Dans cet article, nous allons nous pencher sur l’une d’entre elles, l’utilisation de Google Cloud et de BigQuery, qui convient aux très grands ensembles de données comme ceux que rencontrent beaucoup de nos clients lorsqu’ils examinent les données de sites comportant de gros volumes de pages.

Ce dont vous aurez besoin

Pour suivre la méthode dont nous allons parler dans cet article, vous devez avoir accès aux outils suivants :

Avant de commencer, vous devrez avoir accès à un rapport de crawl terminé dans Oncrawl.

Comment exploiter les données Oncrawl dans Google BigQuery ?

Le plan de l’article d’aujourd’hui est le suivant :

  1. Tout d’abord, nous allons nous assurer que Google Cloud Storage est configuré pour recevoir les données d’Oncrawl.
  2. Ensuite, nous utiliserons un script Python pour exécuter les exportations de Big Data d’Oncrawl afin d’exporter les données d’un crawl donné vers un bucket Google Cloud Storage. Nous allons exporter deux ensembles de données : les pages et les liens.
  3. Une fois cette opération terminée, nous créerons un ensemble de données dans Google BigQuery. Nous créerons ensuite une table à partir de chacun des deux exports dans l’ensemble de données BigQuery.
  4. Enfin, nous expérimenterons l’interrogation des ensembles de données individuels, puis des deux ensembles de données ensemble pour trouver la réponse à une question complexe.

Configuration dans Google Cloud pour recevoir les données Oncrawl

Pour exécuter ce guide dans un environnement dédié, nous vous recommandons de créer un nouveau projet Google Cloud pour l’isoler de vos projets en cours.

Commençons par la home de Google Cloud.

À partir de votre page d’accueil Google Cloud, vous avez accès à de nombreux éléments en plus du stockage en ligne. Nous nous intéressons aux buckets de stockage en cloud, qui sont disponibles dans le niveau de stockage en cloud de Google Cloud Platform :

Vous pouvez également accéder au navigateur de stockage en cloud directement à l’adresse https://console.cloud.google.com/storage/browser.

Vous devez ensuite créer un bucket de stockage en cloud et accorder les autorisations correctes pour que le compte de service d’Oncrawl soit autorisé à y écrire, sous le préfixe de votre choix.
Le godet Google Cloud Storage servira de stockage temporaire pour les exportations de Big Data d’Oncrawl avant de les charger dans Google BigQuery.

Dans ce bucket, j’ai également créé deux dossiers : « links » et « pages » :

Exportation de datasets depuis Oncrawl

Maintenant que nous avons configuré l’espace où nous voulons enregistrer les données, nous devons les exporter depuis Oncrawl. L’exportation vers un bucket Google Cloud Storage avec Oncrawl est particulièrement facile, car nous pouvons exporter les données dans le bon format et les enregistrer directement dans le bucket. Cela élimine toute étape supplémentaire.

Création d’une clé API

L’exportation de données depuis Oncrawl au format Parquet pour BigQuery nécessitera l’utilisation d’une clé API pour agir sur l’API de manière programmatique, au nom du propriétaire du compte Oncrawl. L’application Oncrawl permet aux utilisateurs de créer des clés API nommées afin que votre compte soit toujours bien organisé et propre. Les clés API sont également associées à différentes permissions (scopes) afin que vous puissiez gérer les clés et leurs objectifs.

Nommons notre nouvelle clé « Clé de session de connaissance ». La fonction d’exportation de Big Data nécessite des droits d’écriture sur le compte, car nous créons les exportations de données. Pour ce faire, nous devons avoir un accès en lecture sur le projet et un accès en lecture et en écriture sur le compte.

Nous avons maintenant une nouvelle clé API, que je vais copier dans mon presse-papiers.

Notez que, pour des raisons de sécurité, vous n’avez la possibilité de copier la clé qu’une seule fois. Si vous oubliez de copier la clé, vous devrez la supprimer et en créer une nouvelle.

Création de votre script Python

J’ai créé un carnet de notes Google Colab pour cela, mais je vais partager le code ci-dessous pour que vous puissiez créer vos propres outils ou votre propre carnet de notes.

1. Stockez votre clé API dans une variable globale
Tout d’abord, nous amorçons l’environnement et nous déclarons la clé API dans une variable globale nommée « Oncrawl Token ». Ensuite, nous nous préparons pour le reste de l’expérience :

#@title Access the Oncrawl API
#@markdown Provide your API token below to allow this notebook to access your Oncrawl data:
# YOUR TOKEN FOR ONCRAWL API
ONCRAWL_TOKEN = "" #@param {type:"string"}

!pip install prison

from IPython.display import clear_output
clear_output()

print('All loaded.')

 

2. Créez une liste déroulante pour choisir le projet Oncrawl avec lequel vous voulez travailler.
Ensuite, en utilisant cette clé, nous voulons pouvoir choisir le projet avec lequel nous voulons travailler en récupérant la liste des projets et en créant un widget de liste déroulante à partir de cette liste. En exécutant le deuxième bloc de code, effectuez les étapes suivantes :

  • Nous allons appeler l’API Oncrawl pour obtenir la liste des projets sur le compte en utilisant la clé API qui vient d’être soumise.
  • Une fois que nous avons la liste du projet à partir de la réponse API, nous la formatons comme une liste en utilisant le nom du projet ainsi que l’URL de départ du projet.
  • Nous stockons l’ID du projet qui a été fourni dans la réponse.
  • Nous construisons un menu déroulant et l’affichons sous le bloc de code.
#@title Select the website to analyze by choosing the corresponding Oncrawl project
import requests
import prison
import ipywidgets as widgets
import json

# Get list of projects
response = requests.get("https://app.oncrawl.com/api/v2/projects?limit={limit}&sort={sort}".format(
limit=1000,
sort='name:asc'
),
headers={ 'Authorization': 'Bearer '+ONCRAWL_TOKEN }
)

json_res = response.json()

#prepare dropdown to let user select a project
projects = []
for item in json_res['projects']:
projects.append(('{} - {}'.format(item['name'], item['start_url']), item['id']))

output = widgets.Output()

dropdown_purpose = widgets.Dropdown(options = projects, description="Project: ")

def dropdown_project_eventhandler(change):
output.clear_output()
with output:
display(projects)

dropdown_purpose.observe(dropdown_project_eventhandler, names='value')

display(dropdown_purpose)

 

À partir du menu déroulant ainsi créé, vous pouvez voir la liste complète des projets auxquels la clé API a accès.

Pour les besoins de la démonstration d’aujourd’hui, nous utilisons un projet de démonstration basé sur le site Web Oncrawl.

3. Créez une liste déroulante pour choisir le profil de crawl dans le projet avec lequel vous voulez travailler.
Ensuite, nous allons décider du profil de crawl à utiliser. Nous voulons choisir un profil de crawl dans ce projet. Le projet de démonstration a beaucoup de configurations de crawl différentes :

Dans ce cas, nous examinons un projet que les équipes d’Oncrawl utilisent souvent pour des expériences, je vais donc choisir le profil de crawl utilisé par l’équipe marketing pour surveiller les performances du site Web d’Oncrawl. Comme il est censé être le profil d’exploration le plus stable, c’est un bon choix pour l’expérience d’aujourd’hui.

Pour obtenir le profil de crawl, nous allons utiliser l’API Oncrawl, pour demander le dernier crawl de chaque profil de crawl du projet :

  • Nous nous préparons à interroger l’API Oncrawl pour le projet donné.
  • Nous allons demander tous les crawls retournés par ordre décroissant en fonction de leur date de création.
import requests
import json
import ipywidgets as widgets

project_id = dropdown_purpose.value

# Get projects details (include all the crawls in the project)
project = requests.get("https://app.oncrawl.com/api/v2/projects/{}".format(project_id),
params=dict(include_nested_resources=True, sort="created_at:desc"),
headers={ 'Authorization': 'Bearer '+ONCRAWL_TOKEN }).json()

# Group crawls by crawl profile (crawl name)
crawls_by_config = {}
try:
for crawl in project['crawls']:

if crawl['status'] in ["done"]:

if crawl['crawl_config']['name'] not in crawls_by_config.keys():
crawls_by_config[crawl['crawl_config']['name']] = {'crawl_ids' : [], 'is_crawl_archived' : False}

if len(crawls_by_config[crawl['crawl_config']['name']]['crawl_ids']) == 0:
crawls_by_config[crawl['crawl_config']['name']]['crawl_ids'].append(crawl['id'])

if crawl['status'] == "archived":
crawls_by_config[crawl['crawl_config']['name']]['is_crawl_archived'] = True

except Exception as e:
raise Exception("error {} , {}".format(e, project))

# Build the list for the dropdown select
list = [("{} ({})".format(k, len(v['crawl_ids'])), k) for k, v in crawls_by_config.items()]

dropdown_crawl_configs = widgets.Dropdown(options = list, description="Crawl configs: ")

def dropdown_cc_eventhandler(change):
output.clear_output()
with output:
display(crawls_by_config)

if len(crawls_by_config.values()) == 0:
print('No live crawl found in this project')
dropdown_crawl_configs.observe(dropdown_cc_eventhandler, names='value')

display(dropdown_crawl_configs)

 

Lorsque ce code est exécuté, l’API Oncrawl nous répondra avec la liste des crawls par ordre décroissant de la propriété « created at ».

Puis, puisque nous voulons seulement nous concentrer sur les crawls qui sont terminés, nous allons parcourir la liste des crawls. Pour chaque crawl dont le statut est « terminé », nous enregistrons le nom du profil de crawl et l’ID du crawl.

Nous garderons au maximum un crawl par profil de crawl afin de ne pas exposer trop de crawls.

Le résultat est ce nouveau menu déroulant créé à partir de la liste des profils de crawl du projet. Nous allons choisir celui que nous voulons. Il s’agit du dernier crawl effectué par l’équipe marketing :

4.Identifiez le dernier crawl avec le profil que nous voulons utiliser.
Nous avons déjà l’ID du crawl associé au dernier crawl dans le profil choisi. Il est caché dans le dictionnaire d’objets « crawl_by_config ».

Vous pouvez le vérifier facilement dans l’interface : Trouver le dernier crawl effectué dans l’analyse de ce profil.

Si nous cliquons pour afficher l’analyse, nous verrons que l’ID du crawl se termine par E617.

Prenons simplement note de l’ID de crawl pour les besoins de la démonstration d’aujourd’hui.

Bien sûr, si vous savez déjà ce que vous faites, vous pouvez sauter les étapes que nous venons de couvrir pour appeler l’API Oncrawl afin d’obtenir la liste des projets et la liste des crawls par profil de crawl : vous avez déjà l’ID de crawl de l’interface, et cet ID est tout ce dont vous avez besoin pour exécuter l’exportation.

Les étapes que nous avons parcourues jusqu’à présent visent simplement à faciliter le processus d’obtention du dernier crawl du profil de crawl donné du projet donné, étant donné ce à quoi la clé API a accès. Cela peut être utile si vous fournissez cette solution à d’autres utilisateurs, ou si vous cherchez à l’automatiser.

5.Exports des résultats de crawl
Maintenant, nous allons examiner la commande d’exportation :

#@title Trigger bigdata export
#@markdown Provide your GCS Bucket and prefix gs://some-bucket/pages

# YOUR GCS BUCKET
gcs_bucket = #@param {type:"string"}
gcs_prefix = #@param {type:"string"}

# Get last crawl ID from given project / crawl profile
list_crawl_ids = crawls_by_config[dropdown_crawl_configs.value]['crawl_ids']
last_crawl_id = list_crawl_ids[0]

# Template payload for data export query
payload = {
"data_export": {
"data_type": 'page',
"resource_id": last_crawl_id,
"output_format": 'parquet',
"target": 'gcs',
"target_parameters": {
"gcs_bucket": gcs_bucket,
"gcs_prefix": gcs_prefix
}
}
}

# Trigger export
export = requests.post("https://app.oncrawl.com/api/v2/account/data_exports", json=payload, headers={ 'Authorization': 'Bearer '+ONCRAWL_TOKEN }).json()

# Display API response
display(export)

# Store export ID for future usage
export_id = export['data_export']['id']

 

Nous voulons exporter dans le bucket de stockage en ligne que nous avons configuré précédemment.

A l’intérieur de celui-ci, nous allons exporter les pages pour le dernier ID de crawl :

  • Le dernier ID de crawl est obtenu à partir de la liste des ID de crawl, qui est stockée quelque part dans le dictionnaire « crawls_by_config », qui a été créé à l’étape 3.
  • Nous voulons choisir celui qui correspond au menu déroulant de l’étape 4, donc nous utilisons l’attribut value du menu déroulant.
  • Ensuite, nous extrayons l’attribut crawl_ID. Il s’agit d’une liste. Nous allons garder les 50 premiers éléments de la liste. Nous devons faire cela parce qu’à l’étape 2, comme vous vous en souvenez, lorsque nous avons créé le dictionnaire crawls_by_config, nous n’avons stocké qu’un seul crawl ID par nom de configuration.

J’ai configuré des champs de saisie pour qu’il soit facile de fournir le bucket et le préfixe, ou dossier, de Google Cloud Storage où nous voulons envoyer l’exportation.

Pour les besoins de la démonstration, aujourd’hui, nous allons écrire dans le dossier « mixed dataset », dans l’un des dossiers que j’ai déjà configurés. Lorsque nous avons configuré notre bucket dans Google Cloud Storage, vous vous souvenez que j’ai préparé des dossiers pour l’exportation des « liens » et pour l’exportation des « pages ».

Pour la première exportation, nous voulons exporter les pages dans le dossier « pages » pour le dernier ID de crawl en utilisant le format de fichier Parquet.

Dans les résultats ci-dessous, vous verrez la charge utile qui doit être envoyée au point de fin d’exportation de données, qui est le point de fin pour demander une exportation Big Data en utilisant une clé API :

# Modèle de données utiles pour la requête d’exportation de données

payload = {
"data_export" : {
"data_type" : 'page',
"resource_id" : last_crawl_id,
"output_format" : "parquet",
"target" : 'gcs',
"target_parameters" : {
"gcs_bucket" : gcs_bucket,
"gcs_prefix" : gcs_prefix
}
}
}

 

Ceci contient plusieurs éléments, dont le type d’ensemble de données que vous souhaitez exporter. Vous pouvez exporter le data set de la page, le dataset du lien, le dataset des clusters ou le dataset des données structurées. Si vous ne savez pas ce qu’il faut faire, vous pouvez saisir une erreur ici, et lorsque vous appellerez l’API, vous obtiendrez un message indiquant que le choix du type de données doit être soit page ou lien ou cluster ou données structurées. Le message ressemble à ceci :

{'fields': [{'message': 'Not a valid choice. Must be one of "page", "link", "cluster", "structured_data".',
'name': 'data_type',
'type': 'invalid_choice'}],
'type': 'invalid_request_parameters'}

 

 

Pour les besoins de l’expérience d’aujourd’hui, nous allons exporter l’ensemble de données de la page et l’ensemble de données du lien dans des exportations séparées.

Commençons par l’ensemble de données de la page. Lorsque j’exécute ce bloc de code, j’ai imprimé la sortie de l’appel API, qui ressemble à ceci :

{'data_export': {'data_type': 'page',
'export_failure_reason': None,
'id': 'XXXXXXXXXXXXXX',
'output_format': 'parquet',
'output_format_parameters': None,
'output_row_count': None,
'output_size_in_bytes: 1634460016000,
'resource_id': '60dd4c2b34d08a0f10a5e617',
'status': 'REQUESTED',
'target': 'gcs',
'target_parameters': {'gcs_bucket': 'data-cms',
'gcs_prefix': 'MIXDATASETS/pages/'}}}

 

Cela me permet de voir que l’exportation a été demandée.

Si nous voulons vérifier le statut de l’exportation, c’est très simple. En utilisant l’ID d’exportation que nous avons enregistré à la fin de ce bloc de code, nous pouvons demander l’état de l’exportation à tout moment avec l’appel API suivant :

# STATUS OF EXPORT

export_status = requests.get("https://app.oncrawl.com/api/v2/account/data_exports/{}".format(export_id), headers={ 'Authorization': 'Bearer '+ONCRAWL_TOKEN }).json()

display(export_status)

 

Ceci indiquera un état dans le cadre de l’objet JSON renvoyé :

{'data_export': {'data_type': 'page',
'export_failure_reason': None,
'id': 'XXXXXXXXXXXXXX',
'output_format': 'parquet',
'output_format_parameters': None,
'output_row_count': None,
'output_size_in_bytes': None,
'requested_at': 1638350549000,
'resource_id': '60dd4c2b34d08a0f10a5e617',
'status': 'EXPORTING',
'target': 'gcs',
'target_parameters': {'gcs_bucket': 'data-csm',
'gcs_prefix': 'MIXDATASETS/pages/'}}}

 

Lorsque l’exportation est terminée (‘status’ : ‘DONE’), nous pouvons retourner à Google Cloud Storage.

Si nous regardons dans notre bucket, et que nous allons dans le dossier « links », il n’y a rien ici pour le moment car nous avons exporté les pages.

Cependant, lorsque nous regardons dans le dossier « pages », nous pouvons voir que l’exportation a réussi. Nous avons un fichier Parquet :

À ce stade, le dataset des pages est prêt à être importé dans BigQuery, mais nous allons d’abord répéter les étapes ci-dessus pour obtenir le fichier Parquet pour les liens :

  • Assurez-vous de définir le préfixe des liens.
  • Choisissez le type de données « link ».
  • Exécutez à nouveau ce bloc de code pour demander la deuxième exportation.

Cela produira un fichier Parquet dans le dossier « links ».

Création de jeux de données BigQuery

Pendant que l’exportation est en cours, nous pouvons avancer et commencer à créer des ensembles de données dans BigQuery et importer les fichiers Parquet dans des tables séparées. Ensuite, nous joindrons les tables ensemble.

Ce que nous voulons faire maintenant, c’est jouer avec Google Big Query, qui est disponible dans le cadre de Google Cloud Platform. Vous pouvez utiliser la barre de recherche en haut de l’écran ou aller directement à https://console.cloud.google.com/bigquery.

Création d’un ensemble de données pour votre travail

Nous allons devoir créer un ensemble de données dans Google BigQuery :

Vous devrez donner un nom à l’ensemble de données et choisir l’emplacement où les données seront stockées. Ce choix est important car il conditionnera l’endroit où les données seront traitées et ne pourront pas être modifiées. Cela peut avoir un impact si vos données comprennent des informations couvertes par le GDPR ou d’autres lois sur la confidentialité.

Cet ensemble de données est initialement vide. Lorsque vous l’ouvrirez, vous pourrez créer un tableau, partager l’ensemble de données, copier, supprimer, etc.

Création de tableaux pour vos données

Nous allons créer un tableau dans cet ensemble de données.

Vous pouvez soit créer une table vide et ensuite fournir le schéma. Le schéma est la définition des colonnes de la table. Vous pouvez soit définir votre propre schéma, soit parcourir Google Cloud Storage pour choisir un schéma dans un fichier.

Nous allons utiliser cette dernière option. Nous allons naviguer vers notre bucket, puis vers le dossier « pages ». Choisissons le fichier « pages ». Il n’y a qu’un seul fichier, nous ne pouvons donc en sélectionner qu’un seul, mais si l’exportation avait généré plusieurs fichiers, nous aurions pu tous les choisir.


Lorsque nous sélectionnons le fichier, il détecte automatiquement qu’il est au format de fichier Parquet. Nous voulons créer une table nommée « pages », et le schéma sera défini par le fichier source.

Lorsque nous chargeons un fichier Parquet, il embarque un schéma. En d’autres termes, la définition des colonnes de la table que nous créons sera déduite du schéma qui existe déjà dans le fichier Parquet. C’est ici qu’une partie de la magie opère.

Allons de l’avant et créons simplement la table à partir du fichier Parquet.

Dans la barre latérale de gauche, nous pouvons voir qu’une table est apparue dans notre ensemble de données, ce qui est exactement ce que nous voulons :

Donc, nous avons maintenant le schéma de la table des pages avec tous les champs qui ont été automatiquement déduits du fichier Parquet. Nous avons le Inrank, la profondeur de la page, si la page est une redirection et ainsi de suite :

La plupart de ces champs sont les mêmes que ceux qui sont disponibles dans Data Studio via le connecteur Data Studio d’Oncrawl, et les mêmes que ceux que vous voyez dans le Data Explorer de l’interface d’Oncrawl.

Cependant, il y a quelques différences. Lorsque nous jouons avec l’exportation de big data brutes, vous avez toutes les données brutes.

  • Dans Data Studio, certains champs sont renommés, certains champs sont cachés et certains champs sont ajoutés, comme le statut.
  • Dans le Data Explorer, certains champs sont ce que nous appelons des « champs virtuels », ce qui signifie qu’ils peuvent être une sorte de raccourci vers un champ sous-jacent. Ces champs virtuels disponibles dans le Data Explorer ne seront pas répertoriés dans le schéma, mais ils peuvent être recréés en fonction de ce qui est disponible dans le fichier Parquet.

Fermons maintenant cette table et faisons de même pour les liens.

Pour la table des liens, le schéma est un peu plus petit.

Il ne contient que les champs suivants :

  • L’origine du lien,
  • La cible du lien,
  • La propriété follow,
  • La propriété interne,
  • Le statut de la cible,
  • L’étendue du statut de la cible,
  • Le texte d’ancrage, et
  • Le jus ou l’équité acheté par le lien.

Sur n’importe quelle table dans BigQuery, lorsque vous cliquez sur l’onglet d’aperçu, vous avez un aperçu de la table sans interroger la base de données :

Cela vous donne une vue rapide de ce qui est disponible dans la table. Dans l’aperçu du tableau des liens ci-dessus, vous avez un aperçu de chaque ligne et de toutes les colonnes.

Dans certains ensembles de données Oncrawl, vous pouvez voir des lignes qui couvrent plusieurs lignes. Je n’ai pas d’exemple pour vous, mais si c’est le cas, c’est parce que certains champs contiennent une liste de valeurs. Par exemple, dans la liste des titres h2 d’une page, une seule ligne s’étendra sur plusieurs lignes dans Big Query. Nous verrons cela plus tard si nous avons un exemple.

Création de votre requête

Si vous n’avez jamais créé de requête dans BigQuery, c’est le moment de jouer avec pour vous familiariser avec son fonctionnement. BigQuery utilise le langage SQL pour rechercher des données.

Comment fonctionnent les requêtes

À titre d’exemple, examinons toutes les URLs et leur Inrank…

SELECT url, inrank ...

à partir de l’ensemble de données des pages…

SELECT url, inrank FROM `datascience-oncrawl.example_bigdata_exports.pages` ...

où le code d’état de la page est 200…

SELECT url, inrank FROM `datascience-oncrawl.example_bigdata_exports.pages` WHERE status_code = 200

…et ne garder que les 10 premiers résultats :

SELECT url, inrank FROM `datascience-oncrawl.example_bigdata_exports.pages` WHERE status_code = 200 LIMIT 10

Lorsque nous exécutons cette requête, nous obtenons les 10 premières lignes de la liste des pages dont le code d’état est 200.

Chacune de ces propriétés peut être modifiée. Si je veux 1000 lignes au lieu de 10, je peux définir 1000 lignes :

SELECT url, inrank FROM `datascience-oncrawl.example_bigdata_exports.pages` WHERE status_code = 200 LIMIT 1000

Si je veux trier, je peux le faire avec « order-by » : cela me donnera toutes les lignes classées par ordre décroissant d’Inrank.

SELECT url, inrank FROM `datascience-oncrawl.example_bigdata_exports.links` ORDER BY inrank DESC LIMIT 1000

Ceci est ma première requête. Je peux la sauvegarder si je veux, ce qui me donnera la possibilité de réutiliser cette requête plus tard si je le souhaite :

Utiliser des requêtes pour répondre à des questions simples : Lister tous les liens internes vers les pages avec un statut 301

Maintenant que nous savons comment composer une requête, revenons à notre problème initial.

Nous voulions répondre à des questions de données, qu’elles soient simples ou complexes. Commençons par une question simple, telle que « quels sont tous les liens internes qui pointent vers des pages avec un statut 301 (redirigé), et où puis-je les trouver ? ».

Création d’une nouvelle requête

Nous allons commencer par explorer comment cela fonctionne.

Je vais vouloir des colonnes pour les éléments suivants de la base de données « links » :

  • Origine
  • Cible
  • Code d’état de la cible
SELECT origin, target, target_status FROM `datascience-oncrawl.example_bigdata_exports.links`

Je veux les limiter aux liens internes uniquement, mais imaginons que je ne me souvienne pas du nom de la colonne ou de la valeur qui indique si le lien est interne ou externe. Je peux aller dans le schéma pour le rechercher, et utiliser l’aperçu pour voir la valeur :

Cela m’indique que la colonne s’appelle « intern », et que la plage de valeurs possibles est « external » ou « internal ».

Dans ma requête, je veux spécifier « where intern is internal », et limiter les résultats aux 100 premiers pour le moment :

SELECT origin, target, target_status FROM `datascience-oncrawl.example_bigdata_exports.links` WHERE intern LIKE 'internal' LIMIT 100

 

Le résultat ci-dessus montre la liste des liens avec leur statut de cible. Nous avons seulement des liens internes, et nous en avons 100, comme spécifié dans la requête.

Si nous voulons avoir uniquement des liens internes qui pointent vers des pages redirigées, nous pourrions dire ‘where intern like internal and target status equals 301’ :

SELECT origin, target, target_status FROM `datascience-oncrawl.example_bigdata_exports.links` WHERE intern LIKE 'internal' AND target_status = 301

 


Si nous ne savons pas combien d’entre eux existent, nous pouvons exécuter cette nouvelle requête et nous verrons qu’il y a 3002 liens internes avec un statut cible de 301.

Joindre les tableaux : trouver les codes de statut final des liens pointant vers des pages redirigées

Sur un site Web, on trouve souvent des liens vers des pages qui sont redirigées. Nous voulons connaître le code de statut de la page vers laquelle ils sont redirigés (ou l’URL cible finale).

Dans un ensemble de données, vous avez les informations sur les liens : la page d’origine, la page cible et son code de statut (comme 301), mais pas l’URL vers laquelle une page redirigée pointe. Et dans l’autre, vous avez les informations sur les redirections et leurs cibles finales, mais pas la page d’origine où le lien vers celles-ci a été trouvé.

Décortiquons tout cela :

Premièrement, nous voulons des liens vers des redirections. Écrivons cela. Nous voulons :

  • L’origine.
  • La cible. La cible doit avoir un code de statut 301.
  • La cible finale de la redirection.

En d’autres termes, dans l’ensemble de données des liens, nous voulons :

  • L’origine du lien
  • La cible du lien

Dans l’ensemble de données des pages, nous voulons :

  • Toutes les cibles qui sont redirigées
  • La cible finale de la redirection

Cela nous donnera une requête comme :

SELECT url, final_redirect_location, final_redirect_status FROM `datascience-oncrawl.example_bigdata_exports.pages` AS pages WHERE status_code = 301 OR status_code = 302

Cela devrait me donner la première partie de l’équation.

Maintenant, j’ai besoin de tous les liens vers la page qui sont les résultats de la requête que je viens de créer, en utilisant des alias pour mes ensembles de données, et en les joignant sur l’URL cible du lien et l’URL de la page. Cela correspond à la zone de chevauchement des deux ensembles de données dans le diagramme au début de cette section.

SELECT
links.origin,
pages.url,
pages.final_redirect_location,
pages.final_redirect_status
FROM
`datascience-oncrawl.example_bigdata_exports.pages` AS pages
JOIN
`datascience-oncrawl.example_bigdata_exports.links` AS links
ON
links.target = pages.url
WHERE
pages.status_code = 301
OR pages.status_code = 302
ORDER BY
origin ASC

Dans les résultats de la requête, je peux renommer les colonnes pour rendre les choses plus claires, mais je peux déjà voir que j’ai un lien d’une page dans la première colonne, qui va vers la page de la deuxième colonne, qui est à son tour redirigée vers la page de la troisième colonne. Dans la quatrième colonne, j’ai le code de statut de la cible finale :

Maintenant je peux dire quels liens pointent vers des pages redirigées qui ne mènent pas à 200 pages. Ce sont peut-être des 404, par exemple, ce qui me donne une liste prioritaire de liens à corriger.

Nous avons vu précédemment comment enregistrer une requête. Nous pouvons également sauvegarder les résultats, jusqu’à 16000 lignes de résultats :

Nous pouvons ensuite utiliser ces résultats de nombreuses manières différentes. Voici quelques exemples :

  • Nous pouvons les enregistrer localement dans un fichier CSV ou JSON.
  • Nous pouvons l’enregistrer sous forme de feuille de calcul Google Sheets et la partager avec le reste de l’équipe.
  • Nous pouvons également l’exporter directement vers Data Studio.

Les données comme avantage stratégique

Avec toutes ces possibilités, il est facile d’utiliser stratégiquement les réponses à vos questions complexes. Vous avez peut-être déjà une expérience de la connexion des résultats de BigQuery à Data Studio ou à d’autres plateformes de visualisation de données, ou vous avez peut-être déjà mis en place un processus qui pousse les informations vers une équipe d’ingénieurs ou même dans un flux de travail de business intelligence ou d’analyse de données.

Si vous avez inclus les étapes de cet article dans un processus, n’oubliez pas que vous pouvez automatiser toutes les étapes dans BigQuery : toutes les actions que nous avons effectuées dans cet article sont également accessibles via l’API BigQuery. Cela signifie qu’elles peuvent être exécutées de manière programmatique dans le cadre d’un script ou d’un outil personnalisé.

Quelles que soient vos prochaines étapes, la première étape est toujours l’accès aux données brutes de SEO et de sites Web. Nous pensons que cet accès aux données est l’une des parties les plus importantes de l’analyse technique : avec Oncrawl, vous aurez toujours un accès complet à vos données brutes.

L’accès aux données signifie également que vous pouvez aller au-delà de ce qui est possible dans l’interface d’Oncrawl, et explorer toutes les relations entre vos données, quelle que soit la complexité des questions que vous posez.

Tanguy Moal Voir tous ses articles
Directeur technique et cofondateur d'Oncrawl. Sujets d'intérêt : moteurs de recherche, NLP statistique, Datamining, informatique distribuée et scalabilité. Je suis passionné par la vie en général et par les ordinateurs plus particulièrement. Je suis curieux des réseaux sociaux et des nouveaux modes de communication.
Sujets en lien :