BiomarkerKB_GA4_Automation.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# !pip install google-auth google-auth-oauthlib google-auth-httplib2 google-api-python-client"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: google-analytics-data in c:\\users\\swath\\anaconda3\\envs\\dm-sec11\\lib\\site-packages (0.18.13)\n",
      "Requirement already satisfied: google-api-core[grpc]!=2.0.*,!=2.1.*,!=2.10.*,!=2.2.*,!=2.3.*,!=2.4.*,!=2.5.*,!=2.6.*,!=2.7.*,!=2.8.*,!=2.9.*,<3.0.0dev,>=1.34.1 in c:\\users\\swath\\anaconda3\\envs\\dm-sec11\\lib\\site-packages (from google-analytics-data) (2.21.0)\n",
      "Requirement already satisfied: google-auth!=2.24.0,!=2.25.0,<3.0.0dev,>=2.14.1 in c:\\users\\swath\\anaconda3\\envs\\dm-sec11\\lib\\site-packages (from google-analytics-data) (2.35.0)\n",
      "Requirement already satisfied: proto-plus<2.0.0dev,>=1.22.3 in c:\\users\\swath\\anaconda3\\envs\\dm-sec11\\lib\\site-packages (from google-analytics-data) (1.25.0)\n",
      "Requirement already satisfied: protobuf!=4.21.0,!=4.21.1,!=4.21.2,!=4.21.3,!=4.21.4,!=4.21.5,<6.0.0dev,>=3.20.2 in c:\\users\\swath\\anaconda3\\envs\\dm-sec11\\lib\\site-packages (from google-analytics-data) (5.28.3)\n",
      "Requirement already satisfied: googleapis-common-protos<2.0.dev0,>=1.56.2 in c:\\users\\swath\\anaconda3\\envs\\dm-sec11\\lib\\site-packages (from google-api-core[grpc]!=2.0.*,!=2.1.*,!=2.10.*,!=2.2.*,!=2.3.*,!=2.4.*,!=2.5.*,!=2.6.*,!=2.7.*,!=2.8.*,!=2.9.*,<3.0.0dev,>=1.34.1->google-analytics-data) (1.65.0)\n",
      "Requirement already satisfied: requests<3.0.0.dev0,>=2.18.0 in c:\\users\\swath\\anaconda3\\envs\\dm-sec11\\lib\\site-packages (from google-api-core[grpc]!=2.0.*,!=2.1.*,!=2.10.*,!=2.2.*,!=2.3.*,!=2.4.*,!=2.5.*,!=2.6.*,!=2.7.*,!=2.8.*,!=2.9.*,<3.0.0dev,>=1.34.1->google-analytics-data) (2.31.0)\n",
      "Requirement already satisfied: grpcio<2.0dev,>=1.33.2 in c:\\users\\swath\\anaconda3\\envs\\dm-sec11\\lib\\site-packages (from google-api-core[grpc]!=2.0.*,!=2.1.*,!=2.10.*,!=2.2.*,!=2.3.*,!=2.4.*,!=2.5.*,!=2.6.*,!=2.7.*,!=2.8.*,!=2.9.*,<3.0.0dev,>=1.34.1->google-analytics-data) (1.67.0)\n",
      "Requirement already satisfied: grpcio-status<2.0.dev0,>=1.33.2 in c:\\users\\swath\\anaconda3\\envs\\dm-sec11\\lib\\site-packages (from google-api-core[grpc]!=2.0.*,!=2.1.*,!=2.10.*,!=2.2.*,!=2.3.*,!=2.4.*,!=2.5.*,!=2.6.*,!=2.7.*,!=2.8.*,!=2.9.*,<3.0.0dev,>=1.34.1->google-analytics-data) (1.67.0)\n",
      "Requirement already satisfied: cachetools<6.0,>=2.0.0 in c:\\users\\swath\\anaconda3\\envs\\dm-sec11\\lib\\site-packages (from google-auth!=2.24.0,!=2.25.0,<3.0.0dev,>=2.14.1->google-analytics-data) (5.5.0)\n",
      "Requirement already satisfied: pyasn1-modules>=0.2.1 in c:\\users\\swath\\anaconda3\\envs\\dm-sec11\\lib\\site-packages (from google-auth!=2.24.0,!=2.25.0,<3.0.0dev,>=2.14.1->google-analytics-data) (0.4.1)\n",
      "Requirement already satisfied: rsa<5,>=3.1.4 in c:\\users\\swath\\anaconda3\\envs\\dm-sec11\\lib\\site-packages (from google-auth!=2.24.0,!=2.25.0,<3.0.0dev,>=2.14.1->google-analytics-data) (4.9)\n",
      "Requirement already satisfied: pyasn1<0.7.0,>=0.4.6 in c:\\users\\swath\\anaconda3\\envs\\dm-sec11\\lib\\site-packages (from pyasn1-modules>=0.2.1->google-auth!=2.24.0,!=2.25.0,<3.0.0dev,>=2.14.1->google-analytics-data) (0.6.1)\n",
      "Requirement already satisfied: charset-normalizer<4,>=2 in c:\\users\\swath\\anaconda3\\envs\\dm-sec11\\lib\\site-packages (from requests<3.0.0.dev0,>=2.18.0->google-api-core[grpc]!=2.0.*,!=2.1.*,!=2.10.*,!=2.2.*,!=2.3.*,!=2.4.*,!=2.5.*,!=2.6.*,!=2.7.*,!=2.8.*,!=2.9.*,<3.0.0dev,>=1.34.1->google-analytics-data) (2.0.4)\n",
      "Requirement already satisfied: idna<4,>=2.5 in c:\\users\\swath\\anaconda3\\envs\\dm-sec11\\lib\\site-packages (from requests<3.0.0.dev0,>=2.18.0->google-api-core[grpc]!=2.0.*,!=2.1.*,!=2.10.*,!=2.2.*,!=2.3.*,!=2.4.*,!=2.5.*,!=2.6.*,!=2.7.*,!=2.8.*,!=2.9.*,<3.0.0dev,>=1.34.1->google-analytics-data) (3.4)\n",
      "Requirement already satisfied: urllib3<3,>=1.21.1 in c:\\users\\swath\\anaconda3\\envs\\dm-sec11\\lib\\site-packages (from requests<3.0.0.dev0,>=2.18.0->google-api-core[grpc]!=2.0.*,!=2.1.*,!=2.10.*,!=2.2.*,!=2.3.*,!=2.4.*,!=2.5.*,!=2.6.*,!=2.7.*,!=2.8.*,!=2.9.*,<3.0.0dev,>=1.34.1->google-analytics-data) (1.26.16)\n",
      "Requirement already satisfied: certifi>=2017.4.17 in c:\\users\\swath\\anaconda3\\envs\\dm-sec11\\lib\\site-packages (from requests<3.0.0.dev0,>=2.18.0->google-api-core[grpc]!=2.0.*,!=2.1.*,!=2.10.*,!=2.2.*,!=2.3.*,!=2.4.*,!=2.5.*,!=2.6.*,!=2.7.*,!=2.8.*,!=2.9.*,<3.0.0dev,>=1.34.1->google-analytics-data) (2023.11.17)\n"
     ]
    }
   ],
   "source": [
    "!pip install google-analytics-data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "from google.analytics.data_v1beta import BetaAnalyticsDataClient\n",
    "from google.analytics.data_v1beta.types import DateRange\n",
    "from google.analytics.data_v1beta.types import Dimension\n",
    "from google.analytics.data_v1beta.types import Metric\n",
    "from google.analytics.data_v1beta.types import RunReportRequest\n",
    "from google.analytics.data_v1beta.types import OrderBy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "## Set up global variables\n",
    "\n",
    "os.environ[\"GOOGLE_APPLICATION_CREDENTIALS\"] = 'GA4_API.json'\n",
    "property_id = '465698830'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Month-Year</th>\n",
       "      <th>Total Users</th>\n",
       "      <th>Users/Active Users</th>\n",
       "      <th>Returning Users</th>\n",
       "      <th>New Users</th>\n",
       "      <th>Pageviews</th>\n",
       "      <th>Hits/Events</th>\n",
       "      <th>Sessions</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01, 2025</td>\n",
       "      <td>89.0</td>\n",
       "      <td>89.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>83.0</td>\n",
       "      <td>781.0</td>\n",
       "      <td>1301.0</td>\n",
       "      <td>131.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>12, 2024</td>\n",
       "      <td>165.0</td>\n",
       "      <td>165.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>159.0</td>\n",
       "      <td>1068.0</td>\n",
       "      <td>1877.0</td>\n",
       "      <td>229.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11, 2024</td>\n",
       "      <td>71.0</td>\n",
       "      <td>71.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>69.0</td>\n",
       "      <td>600.0</td>\n",
       "      <td>1002.0</td>\n",
       "      <td>120.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Month-Year  Total Users  Users/Active Users  Returning Users  New Users  \\\n",
       "0   01, 2025         89.0                89.0              6.0       83.0   \n",
       "2   12, 2024        165.0               165.0              6.0      159.0   \n",
       "1   11, 2024         71.0                71.0              2.0       69.0   \n",
       "\n",
       "   Pageviews  Hits/Events  Sessions  \n",
       "0      781.0       1301.0     131.0  \n",
       "2     1068.0       1877.0     229.0  \n",
       "1      600.0       1002.0     120.0  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from google.analytics.data_v1beta import BetaAnalyticsDataClient\n",
    "from google.analytics.data_v1beta.types import DateRange, Dimension, Metric, RunReportRequest, OrderBy\n",
    "import pandas as pd\n",
    "\n",
    "# Initialize the GA4 client\n",
    "client = BetaAnalyticsDataClient()\n",
    "\n",
    "# Set up the query to fetch the required data (removing returningUsers)\n",
    "request = RunReportRequest(\n",
    "    property='properties/' + property_id,\n",
    "    dimensions=[Dimension(name=\"year\"), Dimension(name=\"month\")],\n",
    "    metrics=[\n",
    "        Metric(name=\"totalUsers\"),               # Total Users\n",
    "        Metric(name=\"activeUsers\"),              # Users/Active Users\n",
    "        Metric(name=\"newUsers\"),                 # New Users\n",
    "        Metric(name=\"screenPageViews\"),          # Pageviews\n",
    "        Metric(name=\"eventCount\"),               # Hits/Events\n",
    "        Metric(name=\"sessions\")                  # Sessions\n",
    "    ],\n",
    "    order_bys=[OrderBy(dimension={'dimension_name': 'month'})],\n",
    "    date_ranges=[DateRange(start_date=\"2020-01-01\", end_date=\"today\")]\n",
    ")\n",
    "\n",
    "# Send the request and get the response\n",
    "response = client.run_report(request)\n",
    "\n",
    "# Format the response into a DataFrame and sort it by year and month in descending order\n",
    "def format_report(response):\n",
    "    row_headers = [row.dimension_values for row in response.rows]\n",
    "    metric_values = [row.metric_values for row in response.rows]\n",
    "\n",
    "    data = []\n",
    "    \n",
    "    for i in range(len(row_headers)):\n",
    "        year = int(row_headers[i][0].value)  # Convert year to integer\n",
    "        month = int(row_headers[i][1].value)  # Convert month to integer\n",
    "        total_users = float(metric_values[i][0].value)\n",
    "        active_users = float(metric_values[i][1].value)\n",
    "        new_users = float(metric_values[i][2].value)\n",
    "        returning_users = total_users - new_users  # Calculate returning users\n",
    "        pageviews = float(metric_values[i][3].value)\n",
    "        hits_events = float(metric_values[i][4].value)\n",
    "        sessions = float(metric_values[i][5].value)\n",
    "\n",
    "        data.append([year, month, total_users, active_users, returning_users, new_users, pageviews, hits_events, sessions])\n",
    "\n",
    "    # Create a DataFrame with 'Year' and 'Month' for sorting\n",
    "    df = pd.DataFrame(data, columns=[\n",
    "        \"Year\", \"Month\", \"Total Users\", \"Users/Active Users\", \"Returning Users\", \"New Users\", \"Pageviews\", \"Hits/Events\", \"Sessions\"\n",
    "    ])\n",
    "\n",
    "    # Sort by Year and Month in descending order to have the most recent at the top\n",
    "    df = df.sort_values(by=[\"Year\", \"Month\"], ascending=[False, False])\n",
    "\n",
    "    # Format the Month-Year column for display\n",
    "    df['Month-Year'] = df['Month'].apply(lambda x: f'{x:02}') + ', ' + df['Year'].astype(str)\n",
    "\n",
    "    # Reorder columns to have 'Month-Year' as the first column\n",
    "    df = df[['Month-Year', 'Total Users', 'Users/Active Users', 'Returning Users', 'New Users', 'Pageviews', 'Hits/Events', 'Sessions']]\n",
    "\n",
    "    return df\n",
    "\n",
    "# Apply the formatting and sorting\n",
    "df = format_report(response)\n",
    "\n",
    "# Display the sorted DataFrame\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "32 cells updated.\n",
      "  Month-Year  Total Users  Users/Active Users  Returning Users  New Users  \\\n",
      "0   01, 2025         89.0                89.0              6.0       83.0   \n",
      "2   12, 2024        165.0               165.0              6.0      159.0   \n",
      "1   11, 2024         71.0                71.0              2.0       69.0   \n",
      "\n",
      "   Pageviews  Hits/Events  Sessions  \n",
      "0      781.0       1301.0     131.0  \n",
      "2     1068.0       1877.0     229.0  \n",
      "1      600.0       1002.0     120.0  \n"
     ]
    }
   ],
   "source": [
    "import os\n",
    "from google.analytics.data_v1beta import BetaAnalyticsDataClient\n",
    "from google.analytics.data_v1beta.types import DateRange, Dimension, Metric, RunReportRequest, OrderBy\n",
    "import pandas as pd\n",
    "from google.oauth2 import service_account\n",
    "from googleapiclient.discovery import build\n",
    "\n",
    "# Set the path to your service account key file\n",
    "os.environ['GOOGLE_APPLICATION_CREDENTIALS'] = 'GA4_API.json'\n",
    "\n",
    "# Initialize the GA4 client\n",
    "client = BetaAnalyticsDataClient()\n",
    "\n",
    "# Set your GA4 property ID\n",
    "property_id = \"465698830\"\n",
    "\n",
    "# Set up the query to fetch the required data\n",
    "request = RunReportRequest(\n",
    "    property='properties/' + property_id,\n",
    "    dimensions=[Dimension(name=\"year\"), Dimension(name=\"month\")],\n",
    "    metrics=[\n",
    "        Metric(name=\"totalUsers\"),\n",
    "        Metric(name=\"activeUsers\"),\n",
    "        Metric(name=\"newUsers\"),\n",
    "        Metric(name=\"screenPageViews\"),\n",
    "        Metric(name=\"eventCount\"),\n",
    "        Metric(name=\"sessions\")\n",
    "    ],\n",
    "    order_bys=[OrderBy(dimension={'dimension_name': 'month'})],\n",
    "    date_ranges=[DateRange(start_date=\"2020-01-01\", end_date=\"today\")]\n",
    ")\n",
    "\n",
    "# Send the request and get the response\n",
    "response = client.run_report(request)\n",
    "\n",
    "# Format the response into a DataFrame and sort it by year and month in descending order\n",
    "def format_report(response):\n",
    "    row_headers = [row.dimension_values for row in response.rows]\n",
    "    metric_values = [row.metric_values for row in response.rows]\n",
    "\n",
    "    data = []\n",
    "    \n",
    "    for i in range(len(row_headers)):\n",
    "        year = int(row_headers[i][0].value)\n",
    "        month = int(row_headers[i][1].value)\n",
    "        total_users = float(metric_values[i][0].value)\n",
    "        active_users = float(metric_values[i][1].value)\n",
    "        new_users = float(metric_values[i][2].value)\n",
    "        returning_users = total_users - new_users\n",
    "        pageviews = float(metric_values[i][3].value)\n",
    "        hits_events = float(metric_values[i][4].value)\n",
    "        sessions = float(metric_values[i][5].value)\n",
    "\n",
    "        data.append([year, month, total_users, active_users, returning_users, new_users, pageviews, hits_events, sessions])\n",
    "\n",
    "    df = pd.DataFrame(data, columns=[\n",
    "        \"Year\", \"Month\", \"Total Users\", \"Users/Active Users\", \"Returning Users\", \"New Users\", \"Pageviews\", \"Hits/Events\", \"Sessions\"\n",
    "    ])\n",
    "\n",
    "    df = df.sort_values(by=[\"Year\", \"Month\"], ascending=[False, False])\n",
    "    df['Month-Year'] = df['Month'].apply(lambda x: f'{x:02}') + ', ' + df['Year'].astype(str)\n",
    "    df = df[['Month-Year', 'Total Users', 'Users/Active Users', 'Returning Users', 'New Users', 'Pageviews', 'Hits/Events', 'Sessions']]\n",
    "\n",
    "    return df\n",
    "\n",
    "# Apply the formatting and sorting\n",
    "df = format_report(response)\n",
    "\n",
    "# Google Sheets API setup\n",
    "SCOPES = ['https://www.googleapis.com/auth/spreadsheets']\n",
    "SERVICE_ACCOUNT_FILE = 'google_sheets_api.json'\n",
    "\n",
    "creds = service_account.Credentials.from_service_account_file(\n",
    "    SERVICE_ACCOUNT_FILE, scopes=SCOPES)\n",
    "\n",
    "service = build('sheets', 'v4', credentials=creds)\n",
    "\n",
    "# Specify your Google Sheet ID\n",
    "SPREADSHEET_ID = '1ycFh97caeOTf7nB8ht_SBsB2sxRv0vfdpvfAUtUE9Nk'\n",
    "\n",
    "# Convert DataFrame to list of lists for Google Sheets\n",
    "values = [df.columns.tolist()] + df.values.tolist()\n",
    "\n",
    "# Specify the range where you want to update the data\n",
    "range_name = 'MainDomain!A1'  # This will start from A1 cell\n",
    "\n",
    "# Update the Google Sheet\n",
    "request = service.spreadsheets().values().update(\n",
    "    spreadsheetId=SPREADSHEET_ID,\n",
    "    range=range_name,\n",
    "    valueInputOption='RAW',\n",
    "    body={'values': values}\n",
    ")\n",
    "response = request.execute()\n",
    "\n",
    "print(f\"{response.get('updatedCells')} cells updated.\")\n",
    "\n",
    "# Optional: Print the first few rows of the DataFrame\n",
    "print(df.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "New Chart added successfully.\n"
     ]
    }
   ],
   "source": [
    "# Get the spreadsheet\n",
    "spreadsheet = service.spreadsheets().get(spreadsheetId=SPREADSHEET_ID).execute()\n",
    "sheet_id = spreadsheet['sheets'][0]['properties']['sheetId']\n",
    "\n",
    "# Calculate the y-axis range\n",
    "max_value = df[['Total Users', 'Users/Active Users', 'Returning Users', 'New Users']].max().max()\n",
    "min_value = df[['Total Users', 'Users/Active Users', 'Returning Users', 'New Users']].min().min()\n",
    "y_axis_max = max_value * 1.1  # Add 10% padding\n",
    "y_axis_min = max(0, min_value * 0.9)  # Ensure it doesn't go below 0\n",
    "\n",
    "# Define the chart with proper legend labels and improved y-axis\n",
    "chart = {\n",
    "    'spec': {\n",
    "        'title': 'User Metrics Over Time for All Domains (GA4 Data)',\n",
    "        'basicChart': {\n",
    "            'chartType': 'LINE',\n",
    "            'legendPosition': 'RIGHT_LEGEND',\n",
    "            'headerCount': 1,\n",
    "            'axis': [\n",
    "                {'position': 'BOTTOM_AXIS', 'title': 'Month-Year'},\n",
    "                {\n",
    "                    'position': 'LEFT_AXIS',\n",
    "                    'title': 'Count',\n",
    "                    'viewWindowOptions': {\n",
    "                        'viewWindowMin': y_axis_min,\n",
    "                        'viewWindowMax': y_axis_max\n",
    "                    }\n",
    "                }\n",
    "            ],\n",
    "            'domains': [{\n",
    "                'domain': {\n",
    "                    'sourceRange': {\n",
    "                        'sources': [{\n",
    "                            'sheetId': sheet_id,\n",
    "                            'startRowIndex': 0,\n",
    "                            'endRowIndex': df.shape[0] + 1,\n",
    "                            'startColumnIndex': 0,\n",
    "                            'endColumnIndex': 1\n",
    "                        }]\n",
    "                    }\n",
    "                },\n",
    "                'reversed': True  # This will reverse the x-axis order\n",
    "            }],\n",
    "            'series': [\n",
    "                {\n",
    "                    'series': {\n",
    "                        'sourceRange': {\n",
    "                            'sources': [{\n",
    "                                'sheetId': sheet_id,\n",
    "                                'startRowIndex': 0,\n",
    "                                'endRowIndex': df.shape[0] + 1,\n",
    "                                'startColumnIndex': 1,\n",
    "                                'endColumnIndex': 2\n",
    "                            }]\n",
    "                        }\n",
    "                    },\n",
    "                    'targetAxis': 'LEFT_AXIS',\n",
    "                    'color': {'red': 0.4, 'green': 0.4, 'blue': 1.0},\n",
    "                    'lineStyle': {'type': 'SOLID'}\n",
    "                },\n",
    "                {\n",
    "                    'series': {\n",
    "                        'sourceRange': {\n",
    "                            'sources': [{\n",
    "                                'sheetId': sheet_id,\n",
    "                                'startRowIndex': 0,\n",
    "                                'endRowIndex': df.shape[0] + 1,\n",
    "                                'startColumnIndex': 2,\n",
    "                                'endColumnIndex': 3\n",
    "                            }]\n",
    "                        }\n",
    "                    },\n",
    "                    'targetAxis': 'LEFT_AXIS',\n",
    "                    'color': {'red': 1.0, 'green': 0.4, 'blue': 0.4},\n",
    "                    'lineStyle': {'type': 'SOLID'}\n",
    "                },\n",
    "                {\n",
    "                    'series': {\n",
    "                        'sourceRange': {\n",
    "                            'sources': [{\n",
    "                                'sheetId': sheet_id,\n",
    "                                'startRowIndex': 0,\n",
    "                                'endRowIndex': df.shape[0] + 1,\n",
    "                                'startColumnIndex': 3,\n",
    "                                'endColumnIndex': 4\n",
    "                            }]\n",
    "                        }\n",
    "                    },\n",
    "                    'targetAxis': 'LEFT_AXIS',\n",
    "                    'color': {'red': 1.0, 'green': 0.8, 'blue': 0.2},\n",
    "                    'lineStyle': {'type': 'SOLID'}\n",
    "                },\n",
    "                {\n",
    "                    'series': {\n",
    "                        'sourceRange': {\n",
    "                            'sources': [{\n",
    "                                'sheetId': sheet_id,\n",
    "                                'startRowIndex': 0,\n",
    "                                'endRowIndex': df.shape[0] + 1,\n",
    "                                'startColumnIndex': 4,\n",
    "                                'endColumnIndex': 5\n",
    "                            }]\n",
    "                        }\n",
    "                    },\n",
    "                    'targetAxis': 'LEFT_AXIS',\n",
    "                    'color': {'red': 0.2, 'green': 0.8, 'blue': 0.2},\n",
    "                    'lineStyle': {'type': 'SOLID'}\n",
    "                }\n",
    "            ]\n",
    "        }\n",
    "    },\n",
    "    'position': {\n",
    "        'overlayPosition': {\n",
    "            'anchorCell': {'sheetId': sheet_id, 'rowIndex': 0, 'columnIndex': 9},\n",
    "            'widthPixels': 1200,\n",
    "            'heightPixels': 600\n",
    "        }\n",
    "    }\n",
    "}\n",
    "\n",
    "# Add the chart to the sheet\n",
    "chart_request = {\n",
    "    'requests': [{\n",
    "        'addChart': {\n",
    "            'chart': chart\n",
    "        }\n",
    "    }]\n",
    "}\n",
    "\n",
    "response = service.spreadsheets().batchUpdate(\n",
    "    spreadsheetId=SPREADSHEET_ID,\n",
    "    body=chart_request\n",
    ").execute()\n",
    "\n",
    "print(\"New Chart added successfully.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Set up the query to fetch the traffic source data\n",
    "traffic_source_request = RunReportRequest(\n",
    "    property='properties/' + property_id,\n",
    "    dimensions=[\n",
    "        Dimension(name=\"year\"),\n",
    "        Dimension(name=\"month\"),\n",
    "        Dimension(name=\"sessionSource\")\n",
    "    ],\n",
    "    metrics=[Metric(name=\"sessions\")],\n",
    "    order_bys=[\n",
    "        OrderBy(dimension={'dimension_name': 'year'}, desc=True),\n",
    "        OrderBy(dimension={'dimension_name': 'month'}, desc=True)\n",
    "    ],\n",
    "    date_ranges=[DateRange(start_date=\"2020-01-01\", end_date=\"2024-09-30\")]\n",
    ")\n",
    "\n",
    "# Send the request and get the response\n",
    "traffic_source_response = client.run_report(traffic_source_request)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "def format_traffic_source_report(response):\n",
    "    data = {}\n",
    "    for row in response.rows:\n",
    "        year = int(row.dimension_values[0].value)\n",
    "        month = int(row.dimension_values[1].value)\n",
    "        source = row.dimension_values[2].value\n",
    "        sessions = float(row.metric_values[0].value)\n",
    "        \n",
    "        key = f\"{month:02}, {year}\"\n",
    "        if key not in data:\n",
    "            data[key] = {\"Organic Search\": 0, \"Direct\": 0, \"Referral\": 0}\n",
    "        \n",
    "        if source.lower() == \"google\":\n",
    "            data[key][\"Organic Search\"] += sessions\n",
    "        elif source.lower() == \"(direct)\":\n",
    "            data[key][\"Direct\"] += sessions\n",
    "        elif source.lower() not in [\"google\", \"(direct)\"]:\n",
    "            data[key][\"Referral\"] += sessions\n",
    "\n",
    "    df = pd.DataFrame.from_dict(data, orient='index', columns=[\"Organic Search\", \"Direct\", \"Referral\"])\n",
    "    df.index.name = \"Month-Year\"\n",
    "    df = df.reset_index()\n",
    "    \n",
    "    # Create a datetime column for sorting\n",
    "    df['Year-Month'] = pd.to_datetime(df['Month-Year'].apply(lambda x: x.split(', ')[1] + '-' + x.split(', ')[0] + '-01'))\n",
    "    \n",
    "    # Sort by the datetime column in descending order\n",
    "    df = df.sort_values(by=['Year-Month'], ascending=False)\n",
    "    \n",
    "    # Remove the sorting column and return the DataFrame\n",
    "    return df[['Month-Year', 'Organic Search', 'Direct', 'Referral']]\n",
    "\n",
    "# Apply the formatting to the traffic source data\n",
    "traffic_source_df = format_traffic_source_report(traffic_source_response)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "4 cells updated for traffic source data.\n",
      "Empty DataFrame\n",
      "Columns: [Month-Year, Organic Search, Direct, Referral]\n",
      "Index: []\n"
     ]
    }
   ],
   "source": [
    "# Convert DataFrame to list of lists for Google Sheets\n",
    "traffic_source_values = [traffic_source_df.columns.tolist()] + traffic_source_df.values.tolist()\n",
    "\n",
    "# Calculate the starting row for the new table\n",
    "start_row = len(values) + 2  # Add 2 for an empty row between tables\n",
    "\n",
    "# Specify the range for the new table\n",
    "traffic_source_range = f'MainDomain!A{start_row}'\n",
    "\n",
    "# Update the Google Sheet with the traffic source data\n",
    "traffic_source_request = service.spreadsheets().values().update(\n",
    "    spreadsheetId=SPREADSHEET_ID,\n",
    "    range=traffic_source_range,\n",
    "    valueInputOption='RAW',\n",
    "    body={'values': traffic_source_values}\n",
    ")\n",
    "traffic_source_response = traffic_source_request.execute()\n",
    "\n",
    "print(f\"{traffic_source_response.get('updatedCells')} cells updated for traffic source data.\")\n",
    "\n",
    "# Optional: Print the first few rows of the traffic source DataFrame\n",
    "print(traffic_source_df.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Stacked bar chart with proper legend added to Google Sheets successfully.\n"
     ]
    }
   ],
   "source": [
    "# Assuming traffic_source_df is already created and contains the data\n",
    "\n",
    "# Get the current sheet properties\n",
    "sheet_metadata = service.spreadsheets().get(spreadsheetId=SPREADSHEET_ID).execute()\n",
    "properties = sheet_metadata.get('sheets', [])[0].get('properties')\n",
    "sheet_id = properties.get('sheetId')\n",
    "\n",
    "# Calculate the position for the new chart\n",
    "chart_start_row = start_row + len(traffic_source_values) + 2  # Add 2 for spacing\n",
    "\n",
    "# Calculate the actual start row for the data (including header)\n",
    "data_start_row = start_row - 1  # This will be the row with headers\n",
    "\n",
    "requests = [{\n",
    "    'addChart': {\n",
    "        'chart': {\n",
    "            'spec': {\n",
    "                'title': 'Medium Traffic Source Sessions Over Time',\n",
    "                'basicChart': {\n",
    "                    'chartType': 'COLUMN',\n",
    "                    'legendPosition': 'RIGHT_LEGEND',\n",
    "                    'headerCount': 1,  # Explicitly tell the chart to use the first row as headers\n",
    "                    'axis': [\n",
    "                        {'position': 'BOTTOM_AXIS', 'title': 'Month-Year'},\n",
    "                        {'position': 'LEFT_AXIS', 'title': 'Number of Sessions'}\n",
    "                    ],\n",
    "                    'domains': [{\n",
    "                        'domain': {\n",
    "                            'sourceRange': {\n",
    "                                'sources': [{\n",
    "                                    'sheetId': sheet_id,\n",
    "                                    'startRowIndex': data_start_row,\n",
    "                                    'endRowIndex': start_row + len(traffic_source_values) - 1,\n",
    "                                    'startColumnIndex': 0,\n",
    "                                    'endColumnIndex': 1\n",
    "                                }]\n",
    "                            }\n",
    "                        }\n",
    "                    }],\n",
    "                    'series': [\n",
    "                        {\n",
    "                            'series': {\n",
    "                                'sourceRange': {\n",
    "                                    'sources': [{\n",
    "                                        'sheetId': sheet_id,\n",
    "                                        'startRowIndex': data_start_row,\n",
    "                                        'endRowIndex': start_row + len(traffic_source_values) - 1,\n",
    "                                        'startColumnIndex': 1,\n",
    "                                        'endColumnIndex': 2\n",
    "                                    }]\n",
    "                                }\n",
    "                            },\n",
    "                            'targetAxis': 'LEFT_AXIS',\n",
    "                            'color': {'red': 0.4, 'green': 0.6, 'blue': 0.9}  # Blue\n",
    "                        },\n",
    "                        {\n",
    "                            'series': {\n",
    "                                'sourceRange': {\n",
    "                                    'sources': [{\n",
    "                                        'sheetId': sheet_id,\n",
    "                                        'startRowIndex': data_start_row,\n",
    "                                        'endRowIndex': start_row + len(traffic_source_values) - 1,\n",
    "                                        'startColumnIndex': 2,\n",
    "                                        'endColumnIndex': 3\n",
    "                                    }]\n",
    "                                }\n",
    "                            },\n",
    "                            'targetAxis': 'LEFT_AXIS',\n",
    "                            'color': {'red': 0.9, 'green': 0.3, 'blue': 0.3}  # Red\n",
    "                        },\n",
    "                        {\n",
    "                            'series': {\n",
    "                                'sourceRange': {\n",
    "                                    'sources': [{\n",
    "                                        'sheetId': sheet_id,\n",
    "                                        'startRowIndex': data_start_row,\n",
    "                                        'endRowIndex': start_row + len(traffic_source_values) - 1,\n",
    "                                        'startColumnIndex': 3,\n",
    "                                        'endColumnIndex': 4\n",
    "                                    }]\n",
    "                                }\n",
    "                            },\n",
    "                            'targetAxis': 'LEFT_AXIS',\n",
    "                            'color': {'red': 1.0, 'green': 0.8, 'blue': 0.2}  # Yellow\n",
    "                        }\n",
    "                    ],\n",
    "                    'stackedType': 'STACKED'\n",
    "                }\n",
    "            },\n",
    "            'position': {\n",
    "                'overlayPosition': {\n",
    "                    'anchorCell': {\n",
    "                        'sheetId': sheet_id,\n",
    "                        'rowIndex': chart_start_row,\n",
    "                        'columnIndex': 0\n",
    "                    },\n",
    "                    'widthPixels': 1000,\n",
    "                    'heightPixels': 400\n",
    "                }\n",
    "            }\n",
    "        }\n",
    "    }\n",
    "}]\n",
    "\n",
    "# Execute the request to add the chart to Google Sheets\n",
    "chart_response = service.spreadsheets().batchUpdate(\n",
    "    spreadsheetId=SPREADSHEET_ID,\n",
    "    body={'requests': requests}\n",
    ").execute()\n",
    "\n",
    "print(\"Stacked bar chart with proper legend added to Google Sheets successfully.\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Page Path</th>\n",
       "      <th>Pageviews</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [Page Path, Pageviews]\n",
       "Index: []"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Set up the query to fetch top 10 pages by pageviews\n",
    "top_pageviews_request = RunReportRequest(\n",
    "    property='properties/' + property_id,\n",
    "    dimensions=[Dimension(name=\"pagePath\")],  # URL path of the pages\n",
    "    metrics=[Metric(name=\"screenPageViews\")],  # Metric: Pageviews\n",
    "    order_bys=[OrderBy(metric={'metric_name': 'screenPageViews'}, desc=True)],  # Order by Pageviews (descending)\n",
    "    date_ranges=[DateRange(start_date=\"2023-01-01\", end_date=\"2024-09-30\")],\n",
    "    limit=10  # Limit to the top 10 results\n",
    ")\n",
    "\n",
    "# Send the request and get the response\n",
    "top_pageviews_response = client.run_report(top_pageviews_request)\n",
    "\n",
    "# Format the response into a DataFrame\n",
    "def format_top_pages_report(response):\n",
    "    row_headers = [row.dimension_values for row in response.rows]\n",
    "    metric_values = [row.metric_values for row in response.rows]\n",
    "\n",
    "    data = []\n",
    "    \n",
    "    for i in range(len(row_headers)):\n",
    "        page_path = row_headers[i][0].value  # Page path (URL)\n",
    "        pageviews = int(metric_values[i][0].value)  # Pageviews\n",
    "\n",
    "        data.append([page_path, pageviews])\n",
    "\n",
    "    # Create a DataFrame\n",
    "    df = pd.DataFrame(data, columns=[\"Page Path\", \"Pageviews\"])\n",
    "\n",
    "    return df\n",
    "\n",
    "# Format the top 10 pages report\n",
    "top_pages_df = format_top_pages_report(top_pageviews_response)\n",
    "top_pages_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Top pages data added to Google Sheets successfully.\n"
     ]
    }
   ],
   "source": [
    "# Convert DataFrame to list of lists for Google Sheets\n",
    "top_pages_values = [top_pages_df.columns.tolist()] + top_pages_df.values.tolist()\n",
    "\n",
    "# Calculate the starting row for the new table\n",
    "# It should be after the traffic source data and chart\n",
    "# Assuming chart takes about 25 rows of space\n",
    "chart_space = 25\n",
    "start_row_for_top_pages = start_row + len(traffic_source_values) + chart_space\n",
    "\n",
    "# Specify the range for the new table\n",
    "top_pages_range = f'MainDomain!A{start_row_for_top_pages}'\n",
    "\n",
    "# Add a title row before the table\n",
    "title_range = f'MainDomain!A{start_row_for_top_pages - 1}'\n",
    "title_request = service.spreadsheets().values().update(\n",
    "    spreadsheetId=SPREADSHEET_ID,\n",
    "    range=title_range,\n",
    "    valueInputOption='RAW',\n",
    "    body={'values': [['Top 10 Pages by Pageviews']]}\n",
    ")\n",
    "title_response = title_request.execute()\n",
    "\n",
    "# Update the Google Sheet with the top pages data\n",
    "top_pages_request = service.spreadsheets().values().update(\n",
    "    spreadsheetId=SPREADSHEET_ID,\n",
    "    range=top_pages_range,\n",
    "    valueInputOption='RAW',\n",
    "    body={'values': top_pages_values}\n",
    ")\n",
    "top_pages_response = top_pages_request.execute()\n",
    "\n",
    "# Format the title and table\n",
    "requests = [\n",
    "    {\n",
    "        'updateCells': {\n",
    "            'range': {\n",
    "                'sheetId': sheet_id,\n",
    "                'startRowIndex': start_row_for_top_pages - 1,\n",
    "                'endRowIndex': start_row_for_top_pages,\n",
    "                'startColumnIndex': 0,\n",
    "                'endColumnIndex': 2\n",
    "            },\n",
    "            'rows': [{\n",
    "                'values': [{\n",
    "                    'userEnteredFormat': {\n",
    "                        'textFormat': {'bold': True},\n",
    "                        'horizontalAlignment': 'LEFT',\n",
    "                        'backgroundColor': {'red': 0.9, 'green': 0.9, 'blue': 0.9}\n",
    "                    }\n",
    "                }]\n",
    "            }],\n",
    "            'fields': 'userEnteredFormat(textFormat,horizontalAlignment,backgroundColor)'\n",
    "        }\n",
    "    },\n",
    "    {\n",
    "        'updateCells': {\n",
    "            'range': {\n",
    "                'sheetId': sheet_id,\n",
    "                'startRowIndex': start_row_for_top_pages,\n",
    "                'endRowIndex': start_row_for_top_pages + 1,\n",
    "                'startColumnIndex': 0,\n",
    "                'endColumnIndex': 2\n",
    "            },\n",
    "            'rows': [{\n",
    "                'values': [{\n",
    "                    'userEnteredFormat': {\n",
    "                        'textFormat': {'bold': True},\n",
    "                        'backgroundColor': {'red': 0.95, 'green': 0.95, 'blue': 0.95}\n",
    "                    }\n",
    "                }]\n",
    "            }],\n",
    "            'fields': 'userEnteredFormat(textFormat,backgroundColor)'\n",
    "        }\n",
    "    }\n",
    "]\n",
    "\n",
    "# Execute the formatting requests\n",
    "format_response = service.spreadsheets().batchUpdate(\n",
    "    spreadsheetId=SPREADSHEET_ID,\n",
    "    body={'requests': requests}\n",
    ").execute()\n",
    "\n",
    "print(f\"Top pages data added to Google Sheets successfully.\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Top pages chart added successfully next to the table.\n"
     ]
    }
   ],
   "source": [
    "# Calculate the position for the new chart (next to the top pages table)\n",
    "chart_start_row_top_pages = start_row_for_top_pages\n",
    "# Calculate the actual start row for the data (including header)\n",
    "data_start_row = start_row_for_top_pages - 1  # This will be the row with headers\n",
    "requests = [{\n",
    "    'addChart': {\n",
    "        'chart': {\n",
    "            'spec': {\n",
    "                'title': 'Top 10 Pages by Pageviews',\n",
    "                'basicChart': {\n",
    "                    'chartType': 'BAR',  # Horizontal bar chart\n",
    "                    'legendPosition': 'NO_LEGEND',  # Correct enum value for no legend\n",
    "                    'axis': [\n",
    "                        {\n",
    "                            'position': 'BOTTOM_AXIS',\n",
    "                            'title': 'Pageviews'\n",
    "                        },\n",
    "                        {\n",
    "                            'position': 'LEFT_AXIS',\n",
    "                            'title': 'Page Path'\n",
    "                        }\n",
    "                    ],\n",
    "                    'domains': [{\n",
    "                        'domain': {\n",
    "                            'sourceRange': {\n",
    "                                'sources': [{\n",
    "                                    'sheetId': sheet_id,\n",
    "                                    'startRowIndex': data_start_row,\n",
    "                                    'endRowIndex': data_start_row + len(top_pages_values),\n",
    "                                    'startColumnIndex': 0,  # Page Path column\n",
    "                                    'endColumnIndex': 1\n",
    "                                }]\n",
    "                            }\n",
    "                        }\n",
    "                    }],\n",
    "                    'series': [{\n",
    "                        'series': {\n",
    "                            'sourceRange': {\n",
    "                                'sources': [{\n",
    "                                    'sheetId': sheet_id,\n",
    "                                    'startRowIndex': data_start_row,\n",
    "                                    'endRowIndex': data_start_row + len(top_pages_values),\n",
    "                                    'startColumnIndex': 1,  # Pageviews column\n",
    "                                    'endColumnIndex': 2\n",
    "                                }]\n",
    "                            }\n",
    "                        },\n",
    "                        'targetAxis': 'BOTTOM_AXIS',\n",
    "                        'color': {'red': 0.4, 'green': 0.6, 'blue': 0.9}  # Blue bars\n",
    "                    }],\n",
    "                    'headerCount': 1  # Use first row as headers\n",
    "                }\n",
    "            },\n",
    "            'position': {\n",
    "                'overlayPosition': {\n",
    "                    'anchorCell': {\n",
    "                        'sheetId': sheet_id,\n",
    "                        'rowIndex': chart_start_row_top_pages,\n",
    "                        'columnIndex': 3  # Position chart in column D\n",
    "                    },\n",
    "                    'widthPixels': 600,\n",
    "                    'heightPixels': 400\n",
    "                }\n",
    "            }\n",
    "        }\n",
    "    }\n",
    "}]\n",
    "\n",
    "# Execute the request to add the chart\n",
    "chart_response = service.spreadsheets().batchUpdate(\n",
    "    spreadsheetId=SPREADSHEET_ID,\n",
    "    body={'requests': requests}\n",
    ").execute()\n",
    "\n",
    "print(\"Top pages chart added successfully next to the table.\")\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "DM-sec11",
   "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.11.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}