Recalculer les volumes de recherches via Google Trends

• Établir une liste de keywords via des outils SEO quelconques (disons que c’est déjà fait ici)

• Récupération des volumes de recherche via API Google Ads

• Récupération de la base 100 Google Trends

• Recalcul de l’historique

• Nombre moyen de recherches mensuelles (12 derniers mois)

• Volume de recherche mensuel approximatif (par mois)

• Niveau de concurrence

• Indice de la concurrence

• 20e centile des enchères

• 80e centile des enchères

python
from serpapi import GoogleSearch

params = {
  "engine": "google_trends",
  "q": "data",
  "geo": "FR",
  "data_type": "TIMESERIES",
  "cat": "0",
  "date": "today 12-m",
  "api_key": "secret_api_key"
}

search = GoogleSearch(params)
results = search.get_dict()

sql
with trends as (
select * from `trends_data`
order by date desc), 

search_volumes as (
select * 
from `search_volume`
order by mois desc), 

trends_monthly as (
SELECT
    DATE_TRUNC('month', date) AS mois,
    AVG(base100) AS base100_moyenne
FROM trends
GROUP BY DATE_TRUNC('month', date)
order by mois desc), 

base_joint as (
select 
trends_monthly.mois, 
base100_moyenne, 
Volume_de_recherches_KP as search_volumes
from trends_monthly
left join search_volume using(mois)), 

baseline_trends_volume as (
Select base100_moyenne, search_volumes
FROM base_joint
where base100_moyenne = (select max(base100_moyenne) from base_joint where search_volumes is not null)), 

int_data as (select 
mois, 
base_joint.base100_moyenne, 
base_joint.search_volumes, 
baseline_trends_volume.base100_moyenne as baseline_trends, 
baseline_trends_volume.search_volumes as baseline_volumes
from base_joint
cross join baseline_trends_volume)

select 
mois, 
base100_moyenne, baseline_trends, 
(base100_moyenne/baseline_trends)*100 as recalculated_base100,
search_volumes, baseline_volumes,
case when search_volumes is not null then search_volumes 
else ((base100_moyenne/baseline_trends)) * baseline_volumes
end as recalculated_volumes

from int_data