{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5c8d247d-86fa-44a4-96cb-6a7dfe2edc94",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import os\n",
    "import boto3\n",
    "import pandas as pd\n",
    "from IPython.display import Markdown as md\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import numpy as np\n",
    "import isoweek\n",
    "from users import load_users, User\n",
    "from datetime import timedelta, date, datetime, timezone\n",
    "from DataAPIRequester import DataAPIRequester\n",
    "\n",
    "# os.environ[\"S3_BUCKET\"] = \"pingcap-plg\" \n",
    "# os.environ[\"GOOGLE_APPLICATION_CREDENTIALS\"] = \"/home/ubuntu/gcpbigquery.json\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cff1d76b-f235-4f98-813f-6e223c39ab09",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "end = datetime.now(timezone.utc)\n",
    "begin = end - timedelta(days=90)\n",
    "week_zero = isoweek.Week(2023, 1)\n",
    "week_now = (end.date() - week_zero.day(0)).days // 7\n",
    "\n",
    "md(f'''\n",
    "report generate time: **{datetime.now().date()}**\n",
    "\n",
    "only include tenants signup after **{begin.date()}**\n",
    "''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c84f3731-a4f5-4fd9-8e31-1942bca6a9c9",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "users = load_users(begin)\n",
    "all_users = [u for u in users if not u.is_pingcap()]\n",
    "\n",
    "users = all_users\n",
    "cluster_df = pd.concat([u.clusters() for u in all_users])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "be8401dc-c0d5-4c9e-af18-27e236d08149",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "def send_chart_data(table: str, data: pd.DataFrame):\n",
    "    requester = DataAPIRequester(table)\n",
    "    \n",
    "    return requester.send_data(data)\n",
    "                                 "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "448466c1-18e0-4763-ba65-26243a19540f",
   "metadata": {},
   "source": [
    "## Signup Users"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "27105677-1f7c-4dd4-bcea-79f85c7ef218",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "signup = pd.DataFrame.from_records([{\n",
    "    'time': (u.signup_time().date() - week_zero.day(0)).days // 7,\n",
    "} for u in users if begin.date() <= u.signup_time().date() <= end.date()])\n",
    "signup = signup.groupby('time').size() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "73c1f20f-066e-4214-863e-10e9b1a6a9fa",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "signup_df = pd.DataFrame(columns=['signup_week', 'user_count', 'end_date_of_signup_week'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4d7723a6-c5cb-49ae-98bd-7227172f6d22",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "signup_df['signup_week'] = signup.index\n",
    "signup_df['user_count'] = signup.values\n",
    "signup_df['end_date_of_signup_week'] = signup_df.signup_week.apply(lambda x: (week_zero+x).sunday())\n",
    "signup_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dc67b7df-8f4a-4453-8a73-128ae73236b1",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "data = signup_df[(signup_df.signup_week == week_now) | (signup_df.signup_week == week_now-1)]\n",
    "data.end_date_of_signup_week = data.end_date_of_signup_week.apply(lambda x: x.strftime(\"%Y-%m-%d\"))\n",
    "send_chart_data('signup_update', data) "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "83637772-6142-4aaf-b9ff-251f2de220b2",
   "metadata": {},
   "source": [
    "## Engaged Users"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ce0cd647-afe5-494d-b9e4-cc8010a8f2f0",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "ru_df = users[0]._daily_billing_cost_df\n",
    "ru_df = ru_df[(ru_df.unit_category == 'Serverless Request Unit Cost') & (ru_df.raw_cost > 1_000_000)]\n",
    "ru_df['week'] = ru_df['billing_cost_date'].apply(lambda x: (x - week_zero.day(0)).days // 7)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a2ba4dd9-dbf3-4083-bbd0-d7d4a9778cb3",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df = pd.DataFrame({ 'ts': ru_df.week, 'id': ru_df.billing_tenant_id})\n",
    "df = df.groupby('id')['ts'].min()\n",
    "tf = users[0]._tenant_df[['id', 'created_at']]\n",
    "df = pd.merge(df, tf, on='id', how='inner')\n",
    "df = df[(df['created_at'].dt.date <= end.date()) & (df['created_at'].dt.date >= begin.date())]\n",
    "df.created_at = (df.created_at.dt.date - week_zero.day(0)).dt.days // 7\n",
    "\n",
    "week_range = 12\n",
    "week_mark = [ i for i in range(week_range)]\n",
    "\n",
    "engaged_count = pd.DataFrame(columns=['signup_week', 'signup_users'] + week_mark)\n",
    "\n",
    "gdf = df.groupby('created_at')\n",
    "for w, r in gdf:\n",
    "    cnt = [0 for i in range(week_range)]\n",
    "    for _, u in r.iterrows():\n",
    "        week_elapsed = u.ts - u.created_at\n",
    "        if week_elapsed < 12 and week_elapsed + w <= week_now:\n",
    "            cnt[u.ts - u.created_at] += 1\n",
    "    engaged_count.loc[len(engaged_count)] = [w, signup[w]] + cnt\n",
    "engaged_count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e9612d90-f615-468c-b164-b9faa935fb89",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "engaged_user_count = pd.DataFrame(columns=['signup_week', 'week_elapsed', 'user_count', 'end_date_of_signup_week'])\n",
    "\n",
    "for i, row in engaged_count.iterrows():\n",
    "    for we, uc in row[2:].iteritems():\n",
    "        if we + row['signup_week'] <= week_now:\n",
    "            engaged_user_count.loc[len(engaged_user_count)] = [row['signup_week'], we, uc, (week_zero+row['signup_week']).sunday()]\n",
    "\n",
    "engaged_user_count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6ac44224-5dfb-4289-ab3a-560a2c675784",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "data = engaged_user_count[(engaged_user_count.signup_week + engaged_user_count.week_elapsed == week_now) | (engaged_user_count.signup_week + engaged_user_count.week_elapsed == week_now-1)]\n",
    "data.end_date_of_signup_week = data.end_date_of_signup_week.apply(lambda x: x.strftime(\"%Y-%m-%d\"))\n",
    "send_chart_data('engaged_update', data) "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c11ca850-ceed-4a14-a51f-8f04c8d91254",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Active Users"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ebd20afc-781d-4dc9-9974-0c228a1fb54b",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "gw_access_df = users[0]._gw_access_df\n",
    "cluster_df.id = cluster_df.id.astype(str)\n",
    "gw_access_df.cluster_id = gw_access_df.cluster_id.astype(str)\n",
    "gw_access_df = gw_access_df[gw_access_df.duration > 1]\n",
    "gw_access_df = gw_access_df[~gw_access_df.gateway_instance.str.contains('-inner-')]\n",
    "\n",
    "af = pd.merge(cluster_df[['id', 'tenant_id']], gw_access_df, left_on='id', right_on='cluster_id')\n",
    "af = af.set_index(pd.to_datetime(af.ts, unit='s', utc=True)).sort_index()['tenant_id']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d5e66289-b8f5-4508-8209-bf2ec674ab0f",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df = pd.DataFrame({ 'ts': af.index, 'id': af.values})\n",
    "df = df.groupby('id')['ts'].min()\n",
    "tf = users[0]._tenant_df[['id', 'created_at']]\n",
    "df = pd.merge(df, tf, on='id', how='inner')\n",
    "df = df[(df['created_at'].dt.date <= end.date()) & (df['created_at'].dt.date >= begin.date())]\n",
    "df.created_at = (df.created_at.dt.date - week_zero.day(0)).dt.days // 7\n",
    "df.ts = (df.ts.dt.date - week_zero.day(0)).dt.days // 7\n",
    "\n",
    "week_range = 12\n",
    "week_mark = [ i for i in range(week_range)]\n",
    "\n",
    "active_count = pd.DataFrame(columns=['signup_week', 'signup_users'] + week_mark)\n",
    "\n",
    "gdf = df.groupby('created_at')\n",
    "for w, r in gdf:\n",
    "    cnt = [0 for i in range(week_range)]\n",
    "    for _, u in r.iterrows():\n",
    "        week_elapsed = u.ts - u.created_at\n",
    "        if week_elapsed < 12 and week_elapsed + w <= week_now:\n",
    "            i = int(u.ts - u.created_at)\n",
    "            cnt[i] += 1\n",
    "    active_count.loc[len(active_count)] = [w, signup[w]] + cnt\n",
    "\n",
    "active_count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "37c94494-4d96-41ea-8cec-339ff32948e3",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "active_user_count = pd.DataFrame(columns=['signup_week', 'week_elapsed', 'user_count', 'end_date_of_signup_week'])\n",
    "\n",
    "for i, row in active_count.iterrows():\n",
    "    for we, uc in row[2:].iteritems():\n",
    "        if we + row['signup_week'] <= week_now:\n",
    "            active_user_count.loc[len(active_user_count)] = [row['signup_week'], we, uc, (week_zero+row['signup_week']).sunday()]\n",
    "\n",
    "active_user_count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6c56307e-2aac-4cb2-94c2-f85dfa29ec0d",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "data = active_user_count[(active_user_count.signup_week + active_user_count.week_elapsed == week_now) | (active_user_count.signup_week + active_user_count.week_elapsed == week_now-1)]\n",
    "data.end_date_of_signup_week = data.end_date_of_signup_week.apply(lambda x: x.strftime(\"%Y-%m-%d\"))\n",
    "send_chart_data('active_update', data)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ff1c4243-c70b-436f-b2d3-d23d24016a86",
   "metadata": {},
   "source": [
    "## Paid Users"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "57f86ba3-b646-4f87-823b-c3791313cfc4",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "spend_limit_df = users[0]._spend_limit_df\n",
    "spend_limit_df = spend_limit_df[spend_limit_df.status == 'fit']\n",
    "spend_limit_df['invoice_limit'] = spend_limit_df['biz_data'].apply(lambda x: float(eval(x)[\"invoice_limit\"]))\n",
    "spend_limit_df = spend_limit_df[spend_limit_df.invoice_limit > 0]\n",
    "spend_limit_df['week'] = spend_limit_df.created_at.apply(lambda x: (x.date() - week_zero.day(0)).days // 7)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "44ce3a27-d85b-44a1-b740-f8e3e326e8b4",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df = pd.DataFrame({ 'ts': spend_limit_df.week, 'id': spend_limit_df.tenant_id})\n",
    "df = df.groupby('id')['ts'].min()\n",
    "tf = users[0]._tenant_df[['id', 'created_at']]\n",
    "df = pd.merge(df, tf, on='id', how='inner')\n",
    "df = df[(df['created_at'].dt.date <= end.date()) & (df['created_at'].dt.date >= begin.date())]\n",
    "df.created_at = (df.created_at.dt.date - week_zero.day(0)).dt.days // 7\n",
    "\n",
    "week_range = 12\n",
    "week_mark = [ i for i in range(week_range)]\n",
    "\n",
    "paid_count = pd.DataFrame(columns=['signup_week', 'signup_users'] + week_mark)\n",
    "\n",
    "gdf = df.groupby('created_at')\n",
    "for w, r in gdf:\n",
    "    if w < 0:\n",
    "        continue\n",
    "    cnt = [0 for i in range(week_range)]\n",
    "    for _, u in r.iterrows():\n",
    "        week_elapsed = u.ts - u.created_at\n",
    "        if week_elapsed < 12 and week_elapsed + w <= week_now:\n",
    "            cnt[u.ts - u.created_at] += 1\n",
    "    paid_count.loc[len(paid_count)] = [w, signup[w]] + cnt\n",
    "paid_count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bc5dce3c-8187-4939-8a3c-c2261aecfc0c",
   "metadata": {},
   "outputs": [],
   "source": [
    "paid_user_count = pd.DataFrame(columns=['signup_week', 'week_elapsed', 'user_count', 'end_date_of_signup_week'])\n",
    "\n",
    "for i, row in paid_count.iterrows():\n",
    "    for we, uc in row[2:].iteritems():\n",
    "        if we + row['signup_week'] <= week_now:\n",
    "            paid_user_count.loc[len(paid_user_count)] = [row['signup_week'], we, uc, (week_zero+row['signup_week']).sunday()]\n",
    "\n",
    "paid_user_count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "980d11fe-e027-4958-844d-e5462929a914",
   "metadata": {},
   "outputs": [],
   "source": [
    "data = paid_user_count[(paid_user_count.signup_week + paid_user_count.week_elapsed == week_now) | (paid_user_count.signup_week + paid_user_count.week_elapsed == week_now-1)]\n",
    "data.end_date_of_signup_week = data.end_date_of_signup_week.apply(lambda x: x.strftime(\"%Y-%m-%d\"))\n",
    "send_chart_data('paid_update', data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b772f68e-e364-4279-b08c-9db7d04ad828",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.16"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}