Consignes et exemples de requêtes

Bonnes pratiques pour les requêtes d'exportation groupée de données

Toujours utiliser les fonctions d'agrégation

Il n'est pas garanti que les données des tables soient regroupées par date, URL, site ou une combinaison de clés. Par conséquent, vous devez toujours agréger vos champs (SUM, COUNT, etc.) pour vous assurer que les valeurs de vos lignes sont consolidées. Par exemple, une table peut comporter plusieurs lignes avec la requête "fat cats" :

query     date       search_type   impressions ...
fat cats 10-10-2022      WEB          1000
fat cats 10-10-2022      WEB           200
fat cats 10-10-2022    IMAGE          5000

Si vous recherchez simplement la requête "fat cats", même pour une date et un type de recherche spécifiques, vous risquez d'obtenir plusieurs lignes. Vous devez donc agréger les valeurs pour obtenir la somme de vos impressions :

/* MAUVAISE manière d'afficher la requête la plus populaire : seule une ligne sera prise en compte par requête */
SELECT query, impressions FROM searchconsole.searchdata_site_impression
ORDER BY 2 DESC LIMIT 1;

 Résultat :
fat cats 5000

/* BONNE manière d'afficher la requête la plus populaire */
Requête SELECT, SUM(impressions) FROM searchconsole.searchdata_url_impression
WHERE query != ''  /* Filtre les requêtes anonymisées */
GROUP BY query ORDER BY 2 DESC LIMIT 1;

 Résultat :
fat cats 6200

Limiter les requêtes par date pour réduire les coûts de traitement

N'oubliez pas que l'exécution d'une requête sur BigQuery vous est facturée et que vos tables peuvent devenir très volumineuses. Les tables exportées sont partitionnées par date, ce qui permet de limiter efficacement l'analyse des entrées à seulement des jours d'intérêt. Pour les grands sites, cela peut se traduire par des économies réelles en termes de coût des requêtes. Un bon moyen de réduire les coûts des requêtes consiste à utiliser une clause WHERE pour limiter la plage de dates dans la table partitionnée par date.

Filtrer les chaînes de requête "null"

Les requêtes anonymisées constituent souvent le type de requête le plus courant pour un site. Une requête anonyme est signalée en tant que chaîne de longueur nulle dans la table. Si vous souhaitez afficher la requête la plus populaire pour des critères spécifiques, vous souhaitez probablement exclure les valeurs de longueur nulle de votre requête SQL.

Exemples de requêtes

Vous trouverez ci-dessous des exemples de requêtes pour vous aider à utiliser les données.

Résultats par site

Toutes les statistiques Web, par jour (deux dernières semaines)

SELECT
  data_date AS date,
  sum(impressions) AS impressions,
  sum(clicks) as clicks,
  sum(clicks) / sum(impressions) AS ctr,
  /* Ligne ajoutée ci-dessous, car la position est basée sur 0 */
  ((sum(sum_top_position) / sum(impressions)) + 1.0) AS avg_position

FROM searchconsole.searchdata_site_impression
WHERE search_type = 'WEB'
  AND data_date between DATE_SUB(CURRENT_DATE(), INTERVAL 14 day) and CURRENT_DATE()
GROUP BY data_date

ORDER BY date desc
  LIMIT 1000

Requêtes Web mobiles les plus fréquentes aux États-Unis par clics (deux dernières semaines)

SELECT
  query,
  device,
  sum(impressions) AS impressions,
  sum(clicks) AS clicks,
  sum(clicks) / sum(impressions) AS ctr,
  ((sum(sum_top_position) / sum(impressions)) + 1.0) AS avg_position

FROM searchconsole.searchdata_site_impression
WHERE search_type = 'WEB'
    AND country = 'usa'
    AND device = 'MOBILE'
    AND data_date between DATE_SUB(CURRENT_DATE(), INTERVAL 14 day) and CURRENT_DATE()
GROUP BY 1,2
ORDER BY clicks
LIMIT 1000

Résultats par URL

Principales URL Discover par clics (pour un jour spécifique)

SELECT
  url,
  sum(impressions),
  sum(clicks)
  FROM searchconsole.searchdata_url_impression
WHERE search_type = 'DISCOVER'
  AND data_date = DATE(2022, 02, 01)
GROUP BY url
ORDER BY 3
LIMIT 1000

Total des statistiques sur les requêtes et les URL pour les résultats enrichis de questions fréquentes (deux dernières semaines)

SELECT
  url,
  query,
  sum(impressions) AS impressions,
  sum(clicks) AS clicks,
  sum(clicks) / sum(impressions) AS ctr,
  ((sum(sum_position) / sum(impressions)) + 1.0) AS avg_position
FROM searchconsole.searchdata_url_impression
WHERE search_type = 'WEB'
  AND is_tpf_faq = true
  AND data_date between DATE_SUB(CURRENT_DATE(), INTERVAL 14 day) and CURRENT_DATE()
GROUP BY 1,2 
ORDER BY clicks
LIMIT 1000

Tout votre trafic par URL (deux dernières semaines)

SELECT
  url,
  search_type,
  sum(impressions) as impressions,
  sum(clicks) as clicks
FROM searchconsole.searchdata_url_impression
WHERE data_date between DATE_SUB(CURRENT_DATE(), INTERVAL 14 day) and CURRENT_DATE()
GROUP BY 1,2
ORDER BY 3 desc
LIMIT 1000

Impressions pour les requêtes contenant "your_brand" (votre_marque) (deux dernières semaines)

SELECT
  regexp_contains(query, 'your_brand') AS contains_brand,
  sum(impressions) AS impressions,
  sum(clicks) AS clicks
FROM searchconsole.searchdata_url_impression
WHERE search_type = 'WEB'
  AND data_date between DATE_SUB(CURRENT_DATE(), INTERVAL 14 day) and CURRENT_DATE()
GROUP BY 1
ORDER BY 2 desc
LIMIT 1000