Google Search Console Total Export.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import httplib2\n",
    "from apiclient import errors\n",
    "from apiclient.discovery import build\n",
    "from oauth2client.client import OAuth2WebServerFlow\n",
    "import os\n",
    "from dotenv import load_dotenv\n",
    "project_folder = os.path.expanduser('/Users/oleksandrdagayev/code/jupyter_SEO/')  # adjust as appropriate\n",
    "load_dotenv(os.path.join(project_folder, '.env')) #create a text file called .env and paste  \n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import json\n",
    "from collections import defaultdict as dd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "__init__() takes at most 4 positional arguments (5 given)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Go to the following link in your browser: https://accounts.google.com/o/oauth2/v2/auth?client_id=1098023451168-irepb3tq7bbghm7aak97tcui6gpqjfen.apps.googleusercontent.com&redirect_uri=urn%3Aietf%3Awg%3Aoauth%3A2.0%3Aoob&scope=https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fwebmasters.readonly&access_type=offline&response_type=code\n"
     ]
    }
   ],
   "source": [
    "# Redirect URI for installed apps\n",
    "REDIRECT_URI = 'urn:ietf:wg:oauth:2.0:oob'\n",
    "\n",
    "CLIENT_ID = os.getenv('CLIENT_ID') #get this from setting up a Google App see here \n",
    "CLIENT_SECRET = os.getenv('CLIENT_SECRET')\n",
    "\n",
    "OAUTH_SCOPE = 'https://www.googleapis.com/auth/webmasters.readonly'\n",
    "\n",
    "# Run through the OAuth flow and retrieve credentials\n",
    "flow = OAuth2WebServerFlow(CLIENT_ID, CLIENT_SECRET, OAUTH_SCOPE, REDIRECT_URI)\n",
    "authorize_url = flow.step1_get_authorize_url()\n",
    "print('Go to the following link in your browser: ' + authorize_url) #this will generate a link - make sure to click the link"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#the above gets you to the credential setp."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "code = \"\" #this was my authcode used with my instance - you will need to use your own\n",
    "credentials = flow.step2_exchange(code)\n",
    "\n",
    "# Create an httplib2.Http object and authorize it with our credentials\n",
    "http = httplib2.Http()\n",
    "http = credentials.authorize(http)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "webmaster_search_analytics = build('webmasters', 'v3', http=http) #build the service"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "targetdomain = '' #specify your domain here. Make sure to include protocol and if domain is www. or not. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "request = {\n",
    "    'startDate': '2020-01-11', #all requests must include a start AND end date\n",
    "    'endDate': '2020-01-12',\n",
    "    'dimensions': ['date','query','page'], #select the dimensions of your response\n",
    "    #'dimensionsFilterGroups':[{'filters'}:[{'dimension':'device','expression':'mobile'}]] - this is sample filter\n",
    "    'rowLimit':25000, #this is the maximum row limit for GSC export data\n",
    "    'startRow':0\n",
    "      }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "test_response = webmaster_search_analytics.searchanalytics().query(siteUrl=targetdomain,body=request).execute()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "test_response"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "test_data = {'date':[],'searchQuery':[],'page':[], 'clicks':[],'impressions':[],'ctr':[], 'position':[]}\n",
    "    \n",
    "for i in test_response['rows']:\n",
    "    date = i['keys'][0] #assumes that date is first\n",
    "    test_data['date'].append(date)\n",
    "    searchQuery = i['keys'][1] #assumes that query is second\n",
    "    test_data['searchQuery'].append(searchQuery)\n",
    "    page = i['keys'][2] #assumes that query is second\n",
    "    test_data['page'].append(page)\n",
    "    clicks = i['clicks']\n",
    "    test_data['clicks'].append(clicks)\n",
    "    impressions = i['impressions']\n",
    "    test_data['impressions'].append(impressions)\n",
    "    ctr = i['ctr']\n",
    "    test_data['ctr'].append(ctr)\n",
    "    position = i['position']\n",
    "    test_data['position'].append(position)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.DataFrame(test_data) #this is meant to validate that everything you're doing is correct so far"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_all_response(domain,request): #this is a function that agreggates responses for API calls over 25000 rows\n",
    "    data = {'date':[],'searchQuery':[],'page':[], 'clicks':[],'impressions':[],'ctr':[], 'position':[]}\n",
    "    startRow = 0\n",
    "    request['startRow'] = startRow\n",
    "    initresponse = webmaster_search_analytics.searchanalytics().query(siteUrl=targetdomain,body=request).execute()\n",
    "    last_call_len = len(initresponse['rows'])\n",
    "    mapped_dictionary = map_response_to_dict(initresponse, data)\n",
    "    while last_call_len == 25000:\n",
    "        print('this is running')\n",
    "        startRow = startRow + 25000\n",
    "        request['startRow'] = startRow\n",
    "        response = webmaster_search_analytics.searchanalytics().query(siteUrl=targetdomain,body=request).execute()\n",
    "        last_call_len = len(response['rows'])\n",
    "        mapped_dictionary = map_response_to_dict(response, data)\n",
    "    return mapped_dictionary\n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def map_response_to_dict(response, data): # this turns the JSON response into an easy to manipulate dictionary for dataframes\n",
    "    for i in response['rows']:\n",
    "        date = i['keys'][0] #assumes that date is first\n",
    "        data['date'].append(date)\n",
    "        searchQuery = i['keys'][1] #assumes that query is second\n",
    "        data['searchQuery'].append(searchQuery)\n",
    "        page = i['keys'][2] #assumes that page is third\n",
    "        data['page'].append(page)\n",
    "        clicks = i['clicks']\n",
    "        data['clicks'].append(clicks)\n",
    "        impressions = i['impressions']\n",
    "        data['impressions'].append(impressions)\n",
    "        ctr = i['ctr']\n",
    "        data['ctr'].append(ctr)\n",
    "        position = i['position']\n",
    "        data['position'].append(position)\n",
    "    return data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "big_dict = get_all_response(targetdomain,request) #pushing all data to a dictionary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "seoDataFrame = pd.DataFrame(big_dict) #turning the dictionary into a PandaDataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "seoDataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "seoDataFrame.to_csv('/Users/oleksandrdagayev/code/jupyter_SEO/sample.csv') #set where the data is being written to"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}