🟣 Scripts sympas pour travailler avec l'API Google Search Console

from googleapiclient.discovery import build
from google_auth_oauthlib.flow import InstalledAppFlow
from google.auth.transport.requests import Request
from dateutil.relativedelta import relativedelta
from dateutil.rrule import rrule, DAILY
from dateutil.parser import parse

import pandas as pd
import os
import pickle
from datetime import datetime, timedelta

# Scopes
SCOPES = ['https://www.googleapis.com/auth/webmasters.readonly']

def get_credentials():
    creds = None
    if os.path.exists('token.pickle'):
        with open('token.pickle', 'rb') as token:
            creds = pickle.load(token)

    if not creds or not creds.valid:
        if creds and creds.expired and creds.refresh_token:
            creds.refresh(Request())
        else:
            flow = InstalledAppFlow.from_client_secrets_file('client_secret.json', SCOPES)
            creds = flow.run_console()

        with open('token.pickle', 'wb') as token:
            pickle.dump(creds, token)

    return creds

def fetch_data(webmasters_service, start_date, end_date, data_file='get_all_gsc_data_bypage.csv', tmp_file='temp_dates.csv'):
    if os.path.exists(tmp_file):
        fetched_dates = pd.read_csv(tmp_file)['date'].tolist()
    else:
        fetched_dates = []

    date_range = [dt.strftime('%Y-%m-%d') for dt in rrule(DAILY, dtstart=parse(start_date), until=parse(end_date))]
    missing_dates = list(set(date_range) - set(fetched_dates))

    if not missing_dates:
        print(f"All data for dates between {start_date} to {end_date} already fetched")
        return []

    for date in missing_dates:
        request = {
            'startDate': date,
            'endDate': date,
            'dimensions': ['date', 'page', 'query'],
            'searchType': 'web',
            'aggregationType': 'auto',
            'rowLimit': 25000
        }
        response = webmasters_service.searchanalytics().query(siteUrl='https://*****', body=request).execute()
        if 'rows' not in response:
            print(f"No data for {date}")
        else:
            data = []
            for row in response['rows']:
                data.append({
                    'date': row['keys'][0],
                    'page': row['keys'][1],
                    'query': row['keys'][2],
                    'position': row['position'],
                    'clicks': row['clicks'],
                    'impressions': row['impressions'],
                    'ctr': row['ctr']
                })

            df = pd.DataFrame(data)
            if os.path.exists(data_file):
                df.to_csv(data_file, mode='a', header=False, index=False)
            else:
                df.to_csv(data_file, mode='w', header=True, index=False)

            fetched_dates.append(date)
            pd.DataFrame({'date': fetched_dates}).to_csv(tmp_file, index=False)

    return data

creds = get_credentials()

# Appel Search Console API
webmasters_service = build('webmasters', 'v3', credentials=creds)

# Definition du delta dates
end_date = datetime.now()  # Fetch data up to today
start_date = (end_date - relativedelta(months=16))  # Set your desired start date to be 16 months prior to today
start_date_str = start_date.strftime("%Y-%m-%d")
end_date = end_date.strftime('%Y-%m-%d')

print(start_date_str, end_date)

fetch_data(webmasters_service, start_date_str, end_date)
 

import pandas as pd
import re
import os
from urllib.parse import urlparse

def clean_filename(filename):
    """
    Nettoyage filename
    """
    filename = re.sub(r'\W+', '_', filename)  # Remplace non alphanumerique par underscore
    filename = filename.strip('_')  # Retire underscores
    return filename

# Chargement du CSV
df = pd.read_csv('keywords2.csv')

# Création 'base_url' qui contient l'url avant '#'
df['base_url'] = df['url'].apply(lambda x: urlparse(x).scheme + "://" + urlparse(x).netloc + urlparse(x).path)

# Regroupement par base_url
grouped_by_url = df.groupby('base_url')

# Creation dossier pour les csv
os.makedirs('url_csvs', exist_ok=True)

# Creation fichiers csv
for url, group_df in grouped_by_url:
    group_df = group_df.drop(columns=['url', 'base_url'])
    filename = clean_filename(url) + '.csv'
    file_path = os.path.join('url_csvs', filename)
    group_df.to_csv(file_path, index=False)

import os
import pandas as pd
import jinja2

template_string = """
<!DOCTYPE html>
<html>
<head>
  <title>Heatmap Visualization</title>
  <script src="https://code.highcharts.com/highcharts.js"></script>
  <script src="https://code.highcharts.com/modules/heatmap.js"></script>
  <script src="https://code.highcharts.com/modules/exporting.js"></script>
  <script src="https://code.highcharts.com/modules/data.js"></script>
  <script src="https://code.highcharts.com/modules/boost-canvas.js"></script>
  <script src="https://code.highcharts.com/modules/boost.js"></script>
  <script src="https://code.highcharts.com/modules/accessibility.js"></script>

  <style>
    .highcharts-figure, .highcharts-data-table table {
    min-width: 360px;
    max-width: 1000px;
    margin: 1em auto;
}

.highcharts-data-table table {
  font-family: Verdana, sans-serif;
  border-collapse: collapse;
  border: 1px solid #EBEBEB;
  margin: 10px auto;
  text-align: center;
  width: 100%;
  max-width: 500px;
}
.highcharts-data-table caption {
    padding: 1em 0;
    font-size: 1.2em;
    color: #555;
}
.highcharts-data-table th {
  font-weight: 600;
    padding: 0.5em;
}
.highcharts-data-table td, .highcharts-data-table th, .highcharts-data-table caption {
    padding: 0.5em;
}
.highcharts-data-table thead tr, .highcharts-data-table tr:nth-child(even) {
    background: #f8f8f8;
}
.highcharts-data-table tr:hover {
    background: #f1f7ff;
}
  </style>

</head>
<body>
  <figure class="highcharts-figure">
      <div id="container"></div>
      <p class="highcharts-description">
          Ton texte
      </p>
  </figure>

  <pre id="csv" style="display: none">{{ csv_data }}</pre>

  <script>
    Highcharts.chart('container', {

    data: {
        csv: document.getElementById('csv').innerHTML
    },

    chart: {
        type: 'heatmap'
    },

    boost: {
        useGPUTranslations: true
    },

    title: {
        text: 'Highcharts heat map',
        align: 'left',
        x: 40
    },

    subtitle: {
        text: 'Rankings',
        align: 'left',
        x: 40
    },

    xAxis: {
        type: 'datetime',
        min: Date.UTC(2022, 2, 10),
        max: Date.UTC(2023, 6, 02, 23, 59, 59),
        labels: {
            align: 'left',
            x: 5,
            y: 10,
            format: '{value:%d-%m-%y}' // long month
        },
        showLastLabel: false,
        tickLength: 16
    },

    yAxis: {
        title: {
            text: 'position'
        },
        labels: {
            format: '{value}'
        },
        minPadding: 0,
        maxPadding: 0,
        startOnTick: false,
        endOnTick: false,
        tickPositions: [1, 5, 10, 15, 20, 30, 40, 50, 70, 100],
        tickWidth: 1,
        min: 1,
        max: 100,
        reversed: true
    },

    colorAxis: {
        stops: [
            [0, '#ffffff'],
            [0.01, '#98c0e3'],
            [0.1, '#3892e0'],
            [0.2, '#f7a840'],
            [0.5, '#f7c040'],
            [1, '#f24f0f']
        ],
        min: 1,
        max: 350,
        startOnTick: true,
        endOnTick: false,
        labels: {
            format: '{value}'
        }
    },

    series: [{
        boostThreshold: 100,
        borderWidth: 0,
        nullColor: '#EFEFEF',
        colsize: 24 * 36e5, // one day
        tooltip: {
            headerFormat: 'kw<br/>',
            pointFormat: '{point.x:%e %b, %Y} {point.y}: <b>{point.value} mots clés</b>'
        },
        turboThreshold: Number.MAX_VALUE // #3404, remove after 4.0.5 release
    }]

});
  </script>
</body>
</html>
"""

output_dir = "/url_csvs"

# Fetch all CSV files from the folder.
csv_files = [f for f in os.listdir(output_dir) if f.endswith('.csv')]

# Load the Jinja2 template.
template = jinja2.Template(template_string)

# Loop over each CSV file and create an HTML file for it.
for csv_file in csv_files:
    df = pd.read_csv(os.path.join(output_dir, csv_file))

    # Prepare the CSV data for the HTML template.
    csv_data = df.to_csv(index=False)

    # Render the HTML with the CSV data.
    html = template.render(csv_data=csv_data)

    # Write the HTML to a new .html file.
    with open(os.path.join(output_dir, f"/{csv_file}.html"), 'w') as f:
        f.write(html)