A cette étape, vous avez normalement 2 tables :
- La table SEA avec : Terme de Recherche, Clics, Impressions, Conversion, Racine
- La table Site avec : URL, H1, Racine
Pour l’exemple, je vais mettre des screens des mots-clés SEA qui contiennent « assiette » et « grès » et travailler en SQL sur GBQ, mais la mécanique est tout à fait réalisable aussi sur R.
Extrait de la table SEA :
Extrait de la table Site :
On commence par supprimer les mots-clés en doublon qui travaillent la même racine en conservant à chaque fois la ligne avec le plus d’impressions.
WITH Best_Racine AS (SELECT Racine, max(Impr) AS Impr from Table_SEA group by Racine)
SELECT b2.SearchTerm,b2.Clics, b2.Impr, b2.Conv, b2.Racine from Best_Racine b1
left join Table_SEA b2
ON b1.Racine = b2.Racine and b1.Impr = b2.Impr
Il nous reste maintenant uniquement le meilleur SearchTerm par Racine.
On fait une jointure avec la table des H1 du site avec la colonne "Racine" comme clé de matching :
SELECT b1.*,b2.URL FROM Table_SEA b1
LEFT JOIN Table_Site b2
ON b1.Racine = b2.Racine
Et on finit par supprimer les mots-clés qui sont déjà travaillés sur une URL active.
SELECT b1.* FROM Table_SEA b1
LEFT JOIN Table_Site b2
ON b1.Racine = b2.Racine
WHERE URL is null
Et voilà, c’est terminé, on a les mots-clés intéressants qui auront le droit à une nouvelle landing page sur le site.