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