GSC Performance Between Two Dates.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "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": null,
   "metadata": {},
   "outputs": [],
   "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": {
    "scrolled": true
   },
   "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": {},
   "outputs": [],
   "source": [
    "request1 = {\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": [
    "request2 = {\n",
    "    'startDate': '2019-01-11', #all requests must include a start AND end date\n",
    "    'endDate': '2019-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": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "big_dict1 = get_all_response(targetdomain,request1) #pushing all data to a dictionary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "seoDataFrame1 = pd.DataFrame(big_dict1) #turning the dictionary into a PandaDataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "seoDataFrame1 #this is our first set of data for the first date range"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "big_dict2 = get_all_response(targetdomain,request2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "seoDataFrame2 = pd.DataFrame(big_dict2) #this is our second set of data for the first date range"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "seoDataFrame2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "summedSearchQueryDateRange1 = seoDataFrame1.groupby(\n",
    "    ['searchQuery'] # this is set to be aggregated only on searchQuery right now. We can change this to be ['searchQuery','page'] to be on a searchQuery & page level\n",
    ").agg({\n",
    "    'clicks':\"sum\",\n",
    "    'impressions':\"sum\",\n",
    "    'ctr':\"mean\",\n",
    "    'position':\"mean\"\n",
    "}).sort_values('clicks',ascending=False) #we're defining how we're aggregating the information here"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "summedSearchQueryDateRange1 #taking a look at our aggregated data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "summedSearchQueryDateRange2 = seoDataFrame2.groupby(\n",
    "    ['searchQuery']\n",
    ").agg({\n",
    "    'clicks':\"sum\",\n",
    "    'impressions':\"sum\",\n",
    "    'ctr':\"mean\",\n",
    "    'position':\"mean\"\n",
    "}).sort_values('clicks',ascending=False) \n",
    "#this is the same code as for the above except now we're aggregating the 2nd data frame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "summedSearchQueryDateRange2 #checking out our data frame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mergedSummedSEODataFrames = pd.merge(summedSearchQueryDateRange2, summedSearchQueryDateRange1, on=['searchQuery'], how='inner')\n",
    "\n",
    "#this is the merged version of the two data frames\n",
    "#please note that this implementation uses an inner join which means that comparison is only done on search queries that are present in both data frames. \n",
    "#To change this try using \"left\" or \"right\" as the parameter on how, depending on which dataset you want to be using"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mergedSummedSEODataFrames"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#below is where the difference is calculated for two date ranges"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mergedSummedSEODataFrames['clicks_diff'] = mergedSummedSEODataFrames['clicks_x'] - mergedSummedSEODataFrames['clicks_y']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mergedSummedSEODataFrames['impressions_diff'] = mergedSummedSEODataFrames['impressions_x'] - mergedSummedSEODataFrames['impressions_y']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mergedSummedSEODataFrames['ctr_diff'] = mergedSummedSEODataFrames['ctr_x'] - mergedSummedSEODataFrames['ctr_y']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mergedSummedSEODataFrames['position_diff'] = mergedSummedSEODataFrames['position_y'] - mergedSummedSEODataFrames['position_x']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mergedSummedSEODataFrames"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#the below exports everything to a .csv file. Feel free to define your own export path here"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mergedSummedSEODataFrames.to_csv('/Users/oleksandrdagayev/code/jupyter_SEO/sample.csv')"
   ]
  },
  {
   "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
}