Dernière mise à jour le : 6 Oct, 2020

Google Search Console to BigQuery avec Python

    Cet article est inspiré de celui-ci, que je vous invite à consulter : https://medium.com/@singularbean/revisiting-google-search-console-data-into-google-bigquery-708a19e2f746

    Ainsi que le Github associé : https://github.com/benpowis/search-console-bq

    Pourquoi vouloir charger la donnée Google Search Console dans BigQuery ?

    La donnée issue de Google Search console est précieuse.

    Il s’agit d’une des rares sources permettant de connaître les mots clé ayant généré des impressions et des clics dans Google vers votre site. Il est donc intéressant, dans le cadre d’un “Marketing Data warehouse”, d’y déverser cette donnée.

    Cela permet ensuite de la croiser avec d’autres sources, mais aussi de l’historiser, sachant que Google ne nous donne accès qu’à 16 mois glissant d’historique.

    Dans cet article, nous allons voir comment requêter l’API de Google Search Console, afin de stocker la donnée dans Google BigQuery. Ce code permet de le faire pour plusieurs propriétés en même temps, ce qui est un gain de temps important.

    Il s’agit aussi d’un prétexte pour apprendre à se connecter à une API, et à déverser la donnée dans BigQuery

    Pré-requis

    • Avoir les accès “propriétaire” à au moins une propriété Google Search Console.
    • Avoir un compte Google Cloud Platform (ou en créer un).
    • Connaissances de base dans Google BigQuery
    • Quelques connaissances de base en programmation Python

    Nous allons couvrir les sujets suivant :

    • Créer un “Service account user” dans Google Cloud platform, et activer les droits et accès
    • Préparer l’environnement Python
    • Adapter et faire tourner le code
    • Lire les résultats

    Etape 1 : créer l’environnement dans Big Query

    Créer un projet dans GCP et activer l’API Search Console

    Créez un projet dans Google Cloud Platform.

    Ensuite, activez les APIs Google Search Console. Pour cela, accéder à la librairie des APIs https://console.cloud.google.com/apis/library

    Chercher la “Search console API LEGACY”, at activez la si ce n’est pas déjà fait

    Screenshot 2020 08 28 at 16.13.58 | Google Search Console to BigQuery avec Python

    Créer un “service account” dans GCP

    Dans GCP, aller dans “IAM & Admin” → Service accounts (https://console.cloud.google.com/iam-admin/serviceaccounts)

    Cliquer sur “Create service account” :

    Screenshot 2020 08 28 at 16.18.48 | Google Search Console to BigQuery avec Python

    Créer le compte :

    Screenshot 2020 08 28 at 16.19.55 | Google Search Console to BigQuery avec Python

    Lui donner un rôle qui permette l’écriture et la création de tables dans BigQuery (Admin) :

    Screenshot 2020 08 28 at 16.20.44 | Google Search Console to BigQuery avec Python

    Créer une clé pour ce compte (JSON). Cette clé est téléchargée sur votre ordinateur. Nous en aurons besoin par la suite.

    Screenshot 2020 08 28 at 16.22.49 | Google Search Console to BigQuery avec Python

    Dans BigQuery, créer un dataset

    Pour notre exemple, nous le nommerons “search_console_demo”

    Screenshot 2020 08 28 at 16.57.33 | Google Search Console to BigQuery avec Python

    Donner accès à ce compte dans Google Search Console

    Dans Google Search console, donner un accès à ce compte pour chacune des propriétés que vous souhaitez exporter

    Screenshot 2020 08 28 at 16.25.57 | Google Search Console to BigQuery avec Python

    Etape 2 : le code Python

    Copier les fichiers nécessaires

    Copier les fichiers search_console_bq.py et requirements.txt sur votre ordinateur, ainsi que la clé json générée : https://github.com/Nicolas-Chollet/search-console-to-bigquery-python

    Analyse du code

    ########### IMPORT DES LIBRAIRIES ##################
    from google.oauth2 import service_account
    from googleapiclient.discovery import build
    import requests
    import json
    import pandas as pd
    from google.cloud import bigquery
    
    ########### SET YOUR PARAMETERS HERE ##################
    PROPERTIES = ["https://www.site1.com","https://site2.com"]
    BQ_DATASET_NAME = 'search_console'
    BQ_TABLE_NAME = 'your_table_name'
    SERVICE_ACCOUNT_FILE = 'your_key_file.json'
    start_date = '2020-08-01'
    end_date = '2020-08-20'
    ################ END OF PARAMETERS #########################
    
    SCOPES = ['https://www.googleapis.com/auth/webmasters']
    credentials = service_account.Credentials.from_service_account_file(
            SERVICE_ACCOUNT_FILE, scopes=SCOPES)
    
    service = build(
        'webmasters',
        'v3',
        credentials=credentials
    )
    
    def get_sc_df(site_url,start_date,end_date,start_row):
        """Grab Search Console data for the specific property and send it to BigQuery."""
    
        request = {
          'startDate': start_date,
          'endDate': end_date,
          'dimensions': ['query','device', 'page', 'date'], # uneditable to enforce a nice clean dataframe at the end!
          'rowLimit': 25000,
          'startRow': start_row
           }
    
        response = service.searchanalytics().query(siteUrl=site_url, body=request).execute()
    
        if len(response) > 1:
    
            x = response['rows']
    
            df = pd.DataFrame.from_dict(x)
            
            # split the keys list into columns
            df[['query','device', 'page', 'date']] = pd.DataFrame(df['keys'].values.tolist(), index= df.index)
            
            # Drop the key columns
            result = df.drop(['keys'],axis=1)
    
            # Add a website identifier
            result['website'] = site_url
    
            # establish a BigQuery client
            client = bigquery.Client.from_service_account_json(SERVICE_ACCOUNT_FILE)
            dataset_id = BQ_DATASET_NAME
            table_name = BQ_TABLE_NAME
            # create a job config
            job_config = bigquery.LoadJobConfig()
            # Set the destination table
            table_ref = client.dataset(dataset_id).table(table_name)
            job_config.destination = table_ref
            job_config.write_disposition = 'WRITE_APPEND'
    
            load_job = client.load_table_from_dataframe(result, table_ref, job_config=job_config)
            load_job.result()
    
            return result
        else:
            print("There are no more results to return.")
    
    # Loop through all defined properties, for up to 100,000 rows of data in each
    for p in PROPERTIES:
        for x in range(0,100000,25000):
            y = get_sc_df(p,start_date,end_date,x)
            if len(y) < 25000:
                break
            else:
                continue

    Modifier la config

    Dans le début du fichier search_console_bq.py :

    ########### SET YOUR PARAMETERS HERE ####################################
    PROPERTIES = ["https://www.site1.com","https://site2.com"]
    BQ_DATASET_NAME = 'search_console_demo'
    BQ_TABLE_NAME = 'your_table_name'
    SERVICE_ACCOUNT_FILE = 'your_key_file.json'
    start_date = '2020-08-01'
    end_date = '2020-08-20'
    ################ END OF PARAMETERS ######################################

    Installer les packages Python nécessaires

    Dans le terminal :

    • Créer un environnement virtuel :
    python3 -m venv env                   
    source env/bin/activate

    Installer les packages nécessaires :

    pip install -r requirements.txt

    Pour info, voici les packages en question :

    google-api-python-client==1.10.0
    google-auth-httplib2==0.0.4
    google-auth-oauthlib==0.4.1
    google-cloud==0.34.0
    google-cloud-bigquery==1.25.0
    pandas==1.0.5
    requests==2.22.0
    pyarrow==1.0.1

    Lancer le script

    python search_console_bq.py

    Et voilà 😉 Si tout va bien, les données sont requêtées et chargées dans BigQuery.

    Prochaines étapes

    Nous verrons par la suite comment :

    • Déployer le code dans Google Cloud App Engine
    • Lancer une tache journalière pour mettre à jour les données

    Nous verrons cela dans l’article suivant : Tutoriel : déployer une application Python avec Google App Engine

    Alternatives

    Parmi les alternatives, il y a bien entendu les ELTs, qui sont des solutions permettant justement d’automatiser ce type de connexions. Citons en particulier Fivetran, Supermetrics et Funnel.io, qui sont de très bonnes solutions.

    Donc si vous en avez la possibilité, choisir ces outils vous fera gagner du temps. Mais dans tous les cas, il est toujours intéressant de savoir faire les choses soi-même 😉

    Autres ressources

    Suivez l'actualité Data

    Newsletter UnNest

    Ne manquez aucun contenu : recevez chaque mois la liste des derniers articles publiés.