Mini_Pipeline_MAU_Growth_Accounting.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/theventurecity/data-toolkit/blob/master/Mini_Pipeline_MAU_Growth_Accounting.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "OpetXnsuOL_r"
      },
      "source": [
        "<a href=\"https://theventure.city\"><img src=\"https://github.com/theventurecity/data-toolkit/blob/master/img/tvc_horiz_junglegreen.png?raw=true\" alt='TheVentureCity' style=\"width: 400px;\"></a>\n",
        "\n",
        "# Mini-Pipeline: MAU Growth Accounting\n",
        "1. Extract raw event log data from a CSV\n",
        "2. Transform that data into growth accounting analysis dataframes\n",
        "3. Load the transformed data into Google Sheets\n",
        "4. Visualize insights in Google Data Studio\n",
        "\n",
        "## Before you begin\n",
        "\n",
        "- This notebook is shared in Google Colaboratory with read-only access. To run this notebook yourself, first click \"**Open in Playground**\" in the toolbar above. That will create a separate instance that you can run and/or save a copy of to your own Google Drive.\n",
        "\n",
        "- To run each cell, hit **Shift-Enter**, which will run the contents of the active cell and move to the next cell. This includes the markup cells (such as this one).\n",
        "\n",
        "- When you run the first block of Python code, you will get a message that says, \"**Warning: This notebook was not authored by Google.**\" Please be aware that we are **NOT** accessing your data shared with Google or reading data and credentials from other sessions. This notebook reads data from GitHub and writes to a Google Sheet that only you have access to and can control. We recommend you click the box to \"**Reset all runtimes before running**\" for extra information security."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "GHB2MNA2zwjo"
      },
      "source": [
        "## Import relevant Python libraries"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "9-n5RAG2N9-Z",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "fe78e8ba-33ae-47df-9b5a-32ef0b402c8e"
      },
      "source": [
        "### To run this with functions from TheVentureCity's GitHub repository,\n",
        "### clone the repository to the Google Colaboratory runtime environment.\n",
        "from importlib.machinery import SourceFileLoader\n",
        "!git clone https://github.com/theventurecity/data-toolkit.git /tmp/theventurecity\n",
        "\n",
        "### Installs all the necessary library versions for this environment\n",
        "!pip install -r /tmp/theventurecity/requirements.txt\n",
        "\n",
        "### Import the code. This code allows us to run pre-existing functions\n",
        "### rather than having to define them inline within the notebook\n",
        "### THIS IS ONLY APPLICABLE IF YOU WANT TO ACCESS THOSE FUNCTIONS ###\n",
        "!mv /tmp/theventurecity/python/tvc_load_colab.py tvc_load_colab.py\n",
        "!mv /tmp/theventurecity/python/tvc_transform.py tvc_transform.py\n",
        "!rm -r /tmp/theventurecity\n",
        "tvcl = SourceFileLoader('tvc_load_colab', 'tvc_load_colab.py').load_module()\n",
        "tvct = SourceFileLoader('tvc_transform', 'tvc_transform.py').load_module()\n",
        "\n",
        "### Pandas, Numpy, and date functions to read the data from its source\n",
        "### and manipulate it in memory\n",
        "import pandas as pd\n",
        "import numpy as np\n",
        "from datetime import timedelta\n",
        "from datetime import datetime\n",
        "import math\n",
        "\n",
        "### The IPython.display library allows us to embed an iFrame within this\n",
        "### notebook\n",
        "from IPython.display import IFrame"
      ],
      "execution_count": 25,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Cloning into '/tmp/theventurecity'...\n",
            "remote: Enumerating objects: 386, done.\u001b[K\n",
            "remote: Counting objects:  11% (1/9)\u001b[K\rremote: Counting objects:  22% (2/9)\u001b[K\rremote: Counting objects:  33% (3/9)\u001b[K\rremote: Counting objects:  44% (4/9)\u001b[K\rremote: Counting objects:  55% (5/9)\u001b[K\rremote: Counting objects:  66% (6/9)\u001b[K\rremote: Counting objects:  77% (7/9)\u001b[K\rremote: Counting objects:  88% (8/9)\u001b[K\rremote: Counting objects: 100% (9/9)\u001b[K\rremote: Counting objects: 100% (9/9), done.\u001b[K\n",
            "remote: Compressing objects: 100% (9/9), done.\u001b[K\n",
            "remote: Total 386 (delta 4), reused 0 (delta 0), pack-reused 377\u001b[K\n",
            "Receiving objects: 100% (386/386), 63.65 MiB | 21.04 MiB/s, done.\n",
            "Resolving deltas: 100% (238/238), done.\n",
            "Requirement already satisfied: pandas==2.0.3 in /usr/local/lib/python3.10/dist-packages (from -r /tmp/theventurecity/requirements.txt (line 1)) (2.0.3)\n",
            "Requirement already satisfied: numpy==1.25.2 in /usr/local/lib/python3.10/dist-packages (from -r /tmp/theventurecity/requirements.txt (line 2)) (1.25.2)\n",
            "Requirement already satisfied: gspread==6.1.0 in /usr/local/lib/python3.10/dist-packages (from -r /tmp/theventurecity/requirements.txt (line 3)) (6.1.0)\n",
            "Requirement already satisfied: gspread-dataframe==3.3.1 in /usr/local/lib/python3.10/dist-packages (from -r /tmp/theventurecity/requirements.txt (line 4)) (3.3.1)\n",
            "Requirement already satisfied: gspread-formatting==1.1.2 in /usr/local/lib/python3.10/dist-packages (from -r /tmp/theventurecity/requirements.txt (line 5)) (1.1.2)\n",
            "Requirement already satisfied: google-auth==2.27.0 in /usr/local/lib/python3.10/dist-packages (from -r /tmp/theventurecity/requirements.txt (line 6)) (2.27.0)\n",
            "Requirement already satisfied: google-auth-oauthlib==1.2.0 in /usr/local/lib/python3.10/dist-packages (from -r /tmp/theventurecity/requirements.txt (line 7)) (1.2.0)\n",
            "Requirement already satisfied: google-auth-httplib2==0.2.0 in /usr/local/lib/python3.10/dist-packages (from -r /tmp/theventurecity/requirements.txt (line 8)) (0.2.0)\n",
            "Requirement already satisfied: google-api-python-client==2.125.0 in /usr/local/lib/python3.10/dist-packages (from -r /tmp/theventurecity/requirements.txt (line 9)) (2.125.0)\n",
            "Requirement already satisfied: configparser in /usr/local/lib/python3.10/dist-packages (from -r /tmp/theventurecity/requirements.txt (line 10)) (7.0.0)\n",
            "Requirement already satisfied: python-dateutil>=2.8.2 in /usr/local/lib/python3.10/dist-packages (from pandas==2.0.3->-r /tmp/theventurecity/requirements.txt (line 1)) (2.8.2)\n",
            "Requirement already satisfied: pytz>=2020.1 in /usr/local/lib/python3.10/dist-packages (from pandas==2.0.3->-r /tmp/theventurecity/requirements.txt (line 1)) (2023.4)\n",
            "Requirement already satisfied: tzdata>=2022.1 in /usr/local/lib/python3.10/dist-packages (from pandas==2.0.3->-r /tmp/theventurecity/requirements.txt (line 1)) (2024.1)\n",
            "Requirement already satisfied: StrEnum==0.4.15 in /usr/local/lib/python3.10/dist-packages (from gspread==6.1.0->-r /tmp/theventurecity/requirements.txt (line 3)) (0.4.15)\n",
            "Requirement already satisfied: six>=1.12.0 in /usr/local/lib/python3.10/dist-packages (from gspread-dataframe==3.3.1->-r /tmp/theventurecity/requirements.txt (line 4)) (1.16.0)\n",
            "Requirement already satisfied: cachetools<6.0,>=2.0.0 in /usr/local/lib/python3.10/dist-packages (from google-auth==2.27.0->-r /tmp/theventurecity/requirements.txt (line 6)) (5.3.3)\n",
            "Requirement already satisfied: pyasn1-modules>=0.2.1 in /usr/local/lib/python3.10/dist-packages (from google-auth==2.27.0->-r /tmp/theventurecity/requirements.txt (line 6)) (0.4.0)\n",
            "Requirement already satisfied: rsa<5,>=3.1.4 in /usr/local/lib/python3.10/dist-packages (from google-auth==2.27.0->-r /tmp/theventurecity/requirements.txt (line 6)) (4.9)\n",
            "Requirement already satisfied: requests-oauthlib>=0.7.0 in /usr/local/lib/python3.10/dist-packages (from google-auth-oauthlib==1.2.0->-r /tmp/theventurecity/requirements.txt (line 7)) (1.3.1)\n",
            "Requirement already satisfied: httplib2>=0.19.0 in /usr/local/lib/python3.10/dist-packages (from google-auth-httplib2==0.2.0->-r /tmp/theventurecity/requirements.txt (line 8)) (0.22.0)\n",
            "Requirement already satisfied: google-api-core!=2.0.*,!=2.1.*,!=2.2.*,!=2.3.0,<3.0.0.dev0,>=1.31.5 in /usr/local/lib/python3.10/dist-packages (from google-api-python-client==2.125.0->-r /tmp/theventurecity/requirements.txt (line 9)) (2.11.1)\n",
            "Requirement already satisfied: uritemplate<5,>=3.0.1 in /usr/local/lib/python3.10/dist-packages (from google-api-python-client==2.125.0->-r /tmp/theventurecity/requirements.txt (line 9)) (4.1.1)\n",
            "Requirement already satisfied: googleapis-common-protos<2.0.dev0,>=1.56.2 in /usr/local/lib/python3.10/dist-packages (from google-api-core!=2.0.*,!=2.1.*,!=2.2.*,!=2.3.0,<3.0.0.dev0,>=1.31.5->google-api-python-client==2.125.0->-r /tmp/theventurecity/requirements.txt (line 9)) (1.63.0)\n",
            "Requirement already satisfied: protobuf!=3.20.0,!=3.20.1,!=4.21.0,!=4.21.1,!=4.21.2,!=4.21.3,!=4.21.4,!=4.21.5,<5.0.0.dev0,>=3.19.5 in /usr/local/lib/python3.10/dist-packages (from google-api-core!=2.0.*,!=2.1.*,!=2.2.*,!=2.3.0,<3.0.0.dev0,>=1.31.5->google-api-python-client==2.125.0->-r /tmp/theventurecity/requirements.txt (line 9)) (3.20.3)\n",
            "Requirement already satisfied: requests<3.0.0.dev0,>=2.18.0 in /usr/local/lib/python3.10/dist-packages (from google-api-core!=2.0.*,!=2.1.*,!=2.2.*,!=2.3.0,<3.0.0.dev0,>=1.31.5->google-api-python-client==2.125.0->-r /tmp/theventurecity/requirements.txt (line 9)) (2.31.0)\n",
            "Requirement already satisfied: pyparsing!=3.0.0,!=3.0.1,!=3.0.2,!=3.0.3,<4,>=2.4.2 in /usr/local/lib/python3.10/dist-packages (from httplib2>=0.19.0->google-auth-httplib2==0.2.0->-r /tmp/theventurecity/requirements.txt (line 8)) (3.1.2)\n",
            "Requirement already satisfied: pyasn1<0.7.0,>=0.4.6 in /usr/local/lib/python3.10/dist-packages (from pyasn1-modules>=0.2.1->google-auth==2.27.0->-r /tmp/theventurecity/requirements.txt (line 6)) (0.6.0)\n",
            "Requirement already satisfied: oauthlib>=3.0.0 in /usr/local/lib/python3.10/dist-packages (from requests-oauthlib>=0.7.0->google-auth-oauthlib==1.2.0->-r /tmp/theventurecity/requirements.txt (line 7)) (3.2.2)\n",
            "Requirement already satisfied: charset-normalizer<4,>=2 in /usr/local/lib/python3.10/dist-packages (from requests<3.0.0.dev0,>=2.18.0->google-api-core!=2.0.*,!=2.1.*,!=2.2.*,!=2.3.0,<3.0.0.dev0,>=1.31.5->google-api-python-client==2.125.0->-r /tmp/theventurecity/requirements.txt (line 9)) (3.3.2)\n",
            "Requirement already satisfied: idna<4,>=2.5 in /usr/local/lib/python3.10/dist-packages (from requests<3.0.0.dev0,>=2.18.0->google-api-core!=2.0.*,!=2.1.*,!=2.2.*,!=2.3.0,<3.0.0.dev0,>=1.31.5->google-api-python-client==2.125.0->-r /tmp/theventurecity/requirements.txt (line 9)) (3.6)\n",
            "Requirement already satisfied: urllib3<3,>=1.21.1 in /usr/local/lib/python3.10/dist-packages (from requests<3.0.0.dev0,>=2.18.0->google-api-core!=2.0.*,!=2.1.*,!=2.2.*,!=2.3.0,<3.0.0.dev0,>=1.31.5->google-api-python-client==2.125.0->-r /tmp/theventurecity/requirements.txt (line 9)) (2.0.7)\n",
            "Requirement already satisfied: certifi>=2017.4.17 in /usr/local/lib/python3.10/dist-packages (from requests<3.0.0.dev0,>=2.18.0->google-api-core!=2.0.*,!=2.1.*,!=2.2.*,!=2.3.0,<3.0.0.dev0,>=1.31.5->google-api-python-client==2.125.0->-r /tmp/theventurecity/requirements.txt (line 9)) (2024.2.2)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "DHPavVhwwSMQ"
      },
      "source": [
        "## 1. Extract raw event log data from a CSV"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "jgo6Umpxw5I1"
      },
      "source": [
        "This example uses a data file for a sample company from our GitHub repository called ServBiz. In this step we read the data file into memory as a Pandas dataframe we name \"t.\""
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "v3KNCE2Sb3-S",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 363
        },
        "outputId": "d583e0da-dab8-4ef4-ac0c-dcb8f5a967a4"
      },
      "source": [
        "filename = 'https://raw.githubusercontent.com/theventurecity/analytics/master/data/ServBiz_transactions.csv'\n",
        "t = pd.read_csv(filename)\n",
        "t.tail(10)"
      ],
      "execution_count": 26,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "       client_id        date  value_usd     segment\n",
              "420781    27902A  2024-04-30       8.75  Enterprise\n",
              "420782    34181A  2024-04-30      18.97         SMB\n",
              "420783    30168A  2024-04-30      17.73         SMB\n",
              "420784    30844A  2024-04-30      19.98         SMB\n",
              "420785    35815A  2024-04-30      17.98         SMB\n",
              "420786    16958A  2024-04-30      17.45         SMB\n",
              "420787    13090A  2024-04-30      13.48         SMB\n",
              "420788    19162A  2024-04-30      13.64  Enterprise\n",
              "420789    28409A  2024-04-30      14.72         SMB\n",
              "420790    12080A  2024-04-30      18.32         SMB"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-d06aa731-72f5-48f4-ab45-28a01ff38ffb\" class=\"colab-df-container\">\n",
              "    <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>client_id</th>\n",
              "      <th>date</th>\n",
              "      <th>value_usd</th>\n",
              "      <th>segment</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>420781</th>\n",
              "      <td>27902A</td>\n",
              "      <td>2024-04-30</td>\n",
              "      <td>8.75</td>\n",
              "      <td>Enterprise</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>420782</th>\n",
              "      <td>34181A</td>\n",
              "      <td>2024-04-30</td>\n",
              "      <td>18.97</td>\n",
              "      <td>SMB</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>420783</th>\n",
              "      <td>30168A</td>\n",
              "      <td>2024-04-30</td>\n",
              "      <td>17.73</td>\n",
              "      <td>SMB</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>420784</th>\n",
              "      <td>30844A</td>\n",
              "      <td>2024-04-30</td>\n",
              "      <td>19.98</td>\n",
              "      <td>SMB</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>420785</th>\n",
              "      <td>35815A</td>\n",
              "      <td>2024-04-30</td>\n",
              "      <td>17.98</td>\n",
              "      <td>SMB</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>420786</th>\n",
              "      <td>16958A</td>\n",
              "      <td>2024-04-30</td>\n",
              "      <td>17.45</td>\n",
              "      <td>SMB</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>420787</th>\n",
              "      <td>13090A</td>\n",
              "      <td>2024-04-30</td>\n",
              "      <td>13.48</td>\n",
              "      <td>SMB</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>420788</th>\n",
              "      <td>19162A</td>\n",
              "      <td>2024-04-30</td>\n",
              "      <td>13.64</td>\n",
              "      <td>Enterprise</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>420789</th>\n",
              "      <td>28409A</td>\n",
              "      <td>2024-04-30</td>\n",
              "      <td>14.72</td>\n",
              "      <td>SMB</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>420790</th>\n",
              "      <td>12080A</td>\n",
              "      <td>2024-04-30</td>\n",
              "      <td>18.32</td>\n",
              "      <td>SMB</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-d06aa731-72f5-48f4-ab45-28a01ff38ffb')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-d06aa731-72f5-48f4-ab45-28a01ff38ffb button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-d06aa731-72f5-48f4-ab45-28a01ff38ffb');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "<div id=\"df-b8def80f-f6f5-47f3-9b35-1caea9c3f9ca\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-b8def80f-f6f5-47f3-9b35-1caea9c3f9ca')\"\n",
              "            title=\"Suggest charts\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const quickchartButtonEl =\n",
              "        document.querySelector('#' + key + ' button');\n",
              "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "      try {\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      } catch (error) {\n",
              "        console.error('Error during call to suggestCharts:', error);\n",
              "      }\n",
              "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-b8def80f-f6f5-47f3-9b35-1caea9c3f9ca button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "\n",
              "    </div>\n",
              "  </div>\n"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "dataframe",
              "repr_error": "0"
            }
          },
          "metadata": {},
          "execution_count": 26
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "aTeIz9GPwYV1"
      },
      "source": [
        "## 2. Transform the raw data into growth accounting analysis dataframes\n",
        "**Note**: For a more detailed discussion about creating the DAU and DAU Decorated dataframes, complete with inline code, visit [Section 2 of Mini-Pipeline: Cohort Analysis](https://colab.research.google.com/drive/1oYy-wJl6VZFgOsv8uw7iGChQxUjrR5rf#scrollTo=aTeIz9GPwYV1)\n",
        "### 2.1 Create Daily Active Users (DAU) dataframe\n",
        "The **DAU** dataframe aggregates all activity by user and day."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "OvvX_fkKOmME",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 363
        },
        "outputId": "f8098b21-b6b5-4230-b40a-90c58f53ba67"
      },
      "source": [
        "# Run the create_dau_df function and show the first ten rows of the resulting dataframe\n",
        "dau = tvct.create_dau_df(t,\n",
        "                    user_id = 'client_id',\n",
        "                    activity_date = 'date',\n",
        "                    inc_amt = 'value_usd')\n",
        "dau.head(10)"
      ],
      "execution_count": 27,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "  user_id activity_date  inc_amt\n",
              "0  10000A    2020-12-14    11.75\n",
              "1  10001A    2020-12-14    13.75\n",
              "2  10001A    2021-01-02     7.50\n",
              "3  10001A    2021-01-22    18.00\n",
              "4  10001A    2022-06-05     6.25\n",
              "5  10001A    2023-02-08     8.75\n",
              "6  10002A    2020-12-14    11.75\n",
              "7  10002A    2020-12-26    12.25\n",
              "8  10002A    2021-01-16    12.25\n",
              "9  10002A    2021-01-23    12.25"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-0d742acc-17fb-4ae9-908b-1ecf8b0ed552\" class=\"colab-df-container\">\n",
              "    <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>user_id</th>\n",
              "      <th>activity_date</th>\n",
              "      <th>inc_amt</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>10000A</td>\n",
              "      <td>2020-12-14</td>\n",
              "      <td>11.75</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>10001A</td>\n",
              "      <td>2020-12-14</td>\n",
              "      <td>13.75</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>10001A</td>\n",
              "      <td>2021-01-02</td>\n",
              "      <td>7.50</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>10001A</td>\n",
              "      <td>2021-01-22</td>\n",
              "      <td>18.00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>10001A</td>\n",
              "      <td>2022-06-05</td>\n",
              "      <td>6.25</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>10001A</td>\n",
              "      <td>2023-02-08</td>\n",
              "      <td>8.75</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>10002A</td>\n",
              "      <td>2020-12-14</td>\n",
              "      <td>11.75</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>10002A</td>\n",
              "      <td>2020-12-26</td>\n",
              "      <td>12.25</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>10002A</td>\n",
              "      <td>2021-01-16</td>\n",
              "      <td>12.25</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>10002A</td>\n",
              "      <td>2021-01-23</td>\n",
              "      <td>12.25</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-0d742acc-17fb-4ae9-908b-1ecf8b0ed552')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-0d742acc-17fb-4ae9-908b-1ecf8b0ed552 button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-0d742acc-17fb-4ae9-908b-1ecf8b0ed552');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "<div id=\"df-34bbd1a0-4f74-4024-87d8-4ed9f277556a\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-34bbd1a0-4f74-4024-87d8-4ed9f277556a')\"\n",
              "            title=\"Suggest charts\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const quickchartButtonEl =\n",
              "        document.querySelector('#' + key + ' button');\n",
              "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "      try {\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      } catch (error) {\n",
              "        console.error('Error during call to suggestCharts:', error);\n",
              "      }\n",
              "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-34bbd1a0-4f74-4024-87d8-4ed9f277556a button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "\n",
              "    </div>\n",
              "  </div>\n"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "dataframe",
              "variable_name": "dau"
            }
          },
          "metadata": {},
          "execution_count": 27
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "tZvcQtB2Iqs5"
      },
      "source": [
        "### 2.2 - 2.3 Calculate First Date and DAU Decorated dataframes\n",
        "The create_dau_decorated_df function calls the create_first_dt_df if no first_dt dataframe is specified"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "n4bJEbKVGOtb",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 398
        },
        "outputId": "b598a720-f302-4810-ca52-32fbc9bcaa3e"
      },
      "source": [
        "# Run the create_dau_decorated_df function and show the first ten rows of the resulting dataframe\n",
        "dau_decorated = tvct.create_dau_decorated_df(dau)\n",
        "dau_decorated.head(10)"
      ],
      "execution_count": 28,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Creating DAU Decorated dataframe\n",
            "Creating first_dt dataframe\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "  user_id activity_date  inc_amt    first_dt             first_week  \\\n",
              "0  10000A    2020-12-14    11.75  2020-12-14  2020-12-14/2020-12-20   \n",
              "1  10001A    2020-12-14    13.75  2020-12-14  2020-12-14/2020-12-20   \n",
              "2  10001A    2021-01-02     7.50  2020-12-14  2020-12-14/2020-12-20   \n",
              "3  10001A    2021-01-22    18.00  2020-12-14  2020-12-14/2020-12-20   \n",
              "4  10001A    2022-06-05     6.25  2020-12-14  2020-12-14/2020-12-20   \n",
              "5  10001A    2023-02-08     8.75  2020-12-14  2020-12-14/2020-12-20   \n",
              "6  10002A    2020-12-14    11.75  2020-12-14  2020-12-14/2020-12-20   \n",
              "7  10002A    2020-12-26    12.25  2020-12-14  2020-12-14/2020-12-20   \n",
              "8  10002A    2021-01-16    12.25  2020-12-14  2020-12-14/2020-12-20   \n",
              "9  10002A    2021-01-23    12.25  2020-12-14  2020-12-14/2020-12-20   \n",
              "\n",
              "  first_month  \n",
              "0     2020-12  \n",
              "1     2020-12  \n",
              "2     2020-12  \n",
              "3     2020-12  \n",
              "4     2020-12  \n",
              "5     2020-12  \n",
              "6     2020-12  \n",
              "7     2020-12  \n",
              "8     2020-12  \n",
              "9     2020-12  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-620eeb08-01c6-4dbd-aa7b-c7d886a81126\" class=\"colab-df-container\">\n",
              "    <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>user_id</th>\n",
              "      <th>activity_date</th>\n",
              "      <th>inc_amt</th>\n",
              "      <th>first_dt</th>\n",
              "      <th>first_week</th>\n",
              "      <th>first_month</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>10000A</td>\n",
              "      <td>2020-12-14</td>\n",
              "      <td>11.75</td>\n",
              "      <td>2020-12-14</td>\n",
              "      <td>2020-12-14/2020-12-20</td>\n",
              "      <td>2020-12</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>10001A</td>\n",
              "      <td>2020-12-14</td>\n",
              "      <td>13.75</td>\n",
              "      <td>2020-12-14</td>\n",
              "      <td>2020-12-14/2020-12-20</td>\n",
              "      <td>2020-12</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>10001A</td>\n",
              "      <td>2021-01-02</td>\n",
              "      <td>7.50</td>\n",
              "      <td>2020-12-14</td>\n",
              "      <td>2020-12-14/2020-12-20</td>\n",
              "      <td>2020-12</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>10001A</td>\n",
              "      <td>2021-01-22</td>\n",
              "      <td>18.00</td>\n",
              "      <td>2020-12-14</td>\n",
              "      <td>2020-12-14/2020-12-20</td>\n",
              "      <td>2020-12</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>10001A</td>\n",
              "      <td>2022-06-05</td>\n",
              "      <td>6.25</td>\n",
              "      <td>2020-12-14</td>\n",
              "      <td>2020-12-14/2020-12-20</td>\n",
              "      <td>2020-12</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>10001A</td>\n",
              "      <td>2023-02-08</td>\n",
              "      <td>8.75</td>\n",
              "      <td>2020-12-14</td>\n",
              "      <td>2020-12-14/2020-12-20</td>\n",
              "      <td>2020-12</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>10002A</td>\n",
              "      <td>2020-12-14</td>\n",
              "      <td>11.75</td>\n",
              "      <td>2020-12-14</td>\n",
              "      <td>2020-12-14/2020-12-20</td>\n",
              "      <td>2020-12</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>10002A</td>\n",
              "      <td>2020-12-26</td>\n",
              "      <td>12.25</td>\n",
              "      <td>2020-12-14</td>\n",
              "      <td>2020-12-14/2020-12-20</td>\n",
              "      <td>2020-12</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>10002A</td>\n",
              "      <td>2021-01-16</td>\n",
              "      <td>12.25</td>\n",
              "      <td>2020-12-14</td>\n",
              "      <td>2020-12-14/2020-12-20</td>\n",
              "      <td>2020-12</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>10002A</td>\n",
              "      <td>2021-01-23</td>\n",
              "      <td>12.25</td>\n",
              "      <td>2020-12-14</td>\n",
              "      <td>2020-12-14/2020-12-20</td>\n",
              "      <td>2020-12</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-620eeb08-01c6-4dbd-aa7b-c7d886a81126')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-620eeb08-01c6-4dbd-aa7b-c7d886a81126 button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-620eeb08-01c6-4dbd-aa7b-c7d886a81126');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "<div id=\"df-5a44e1ac-9b1d-49e4-8035-8a039d2892cf\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-5a44e1ac-9b1d-49e4-8035-8a039d2892cf')\"\n",
              "            title=\"Suggest charts\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const quickchartButtonEl =\n",
              "        document.querySelector('#' + key + ' button');\n",
              "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "      try {\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      } catch (error) {\n",
              "        console.error('Error during call to suggestCharts:', error);\n",
              "      }\n",
              "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-5a44e1ac-9b1d-49e4-8035-8a039d2892cf button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "\n",
              "    </div>\n",
              "  </div>\n"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "dataframe",
              "variable_name": "dau_decorated"
            }
          },
          "metadata": {},
          "execution_count": 28
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "JyYfAz4RxU6O"
      },
      "source": [
        "Combining the basic DAU data with the first date, week, and month for each user, **the DAU Decorated dataframe is our basic building block for many different analyses**. It allows us to use user-level data to inspect engagement, retention, and growth accounting."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "j0m1X_OswHFX"
      },
      "source": [
        "### 2.4 Calculate MAU growth accounting metrics\n",
        "**Note**: The code in this section has been modified from what is in the tvc_transform.py file. This is for the sake of brevity and simplicity.\n",
        "\n",
        "Now that we have the \"DAU Decorated\" data frame, we can use it to calculate growth accounting metrics since they are super-important for an early-stage startup. As you can see by reviewing the code comments below, knowing a customer's first month helps us understand, in any given month, if we have ever seen that customer before. That information allows us to distinguish between \"new\" and \"resurrected\" users.\n",
        "#### First, aggregate DAU Decorated into MAU Decorated by calculating monthly totals for each user"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "DVal3IX-HK7t",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 380
        },
        "outputId": "ebe565fa-0b0c-4c30-8d90-73ccacadc2aa"
      },
      "source": [
        "### For discrete time period calculations, this helps set the variable names\n",
        "### in the different dataframes\n",
        "def get_time_period_dict(time_period):\n",
        "\n",
        "    time_fields_dict = {\n",
        "                        'day' : {'grouping_col' : 'activity_date',\n",
        "                                  'first_period_col' : 'first_dt',\n",
        "                                  'frequency' : 'Daily',\n",
        "                                  'unit' : 'Day',\n",
        "                                  'period_abbr' : 'D',\n",
        "                                  'python_period' : 'days',\n",
        "                                  'days' : 1\n",
        "                                  },\n",
        "                        'week' : {'grouping_col' : 'Week',\n",
        "                                  'first_period_col' : 'first_week',\n",
        "                                  'frequency' : 'Weekly',\n",
        "                                  'unit' : 'Week',\n",
        "                                  'period_abbr' : 'W',\n",
        "                                  'python_period' : 'weeks',\n",
        "                                  'days' : 7\n",
        "                                  },\n",
        "                        'month' : {'grouping_col' : 'Month_Year',\n",
        "                                   'first_period_col' : 'first_month',\n",
        "                                   'frequency' : 'Monthly',\n",
        "                                   'unit' : 'Month',\n",
        "                                   'period_abbr' : 'M',\n",
        "                                   'python_period' : 'months',\n",
        "                                   'days' : 28\n",
        "                                  }\n",
        "                        }\n",
        "\n",
        "    # if time_period passed in is a valid choice, then return the dictionary\n",
        "    # associated with that choice from the dictionary above\n",
        "    if time_period in time_fields_dict:\n",
        "        time_fields = time_fields_dict[time_period]\n",
        "    else:\n",
        "        time_fields = None\n",
        "\n",
        "    return time_fields\n",
        "\n",
        "\n",
        "\n",
        "### This is another helper function that allows us to determine the next week\n",
        "### or month for any given week of month. We need these because the Pandas\n",
        "### date math is not consistent. You can use timedelta to add weeks, but you\n",
        "### have to use DateOffset to add months.\n",
        "def increment_period(xau_grouping_col, time_period):\n",
        "\n",
        "    # Call the get_time_period_dict function above, passing in the time period\n",
        "    time_fields = get_time_period_dict(time_period)\n",
        "\n",
        "    # Set the one-letter period abbreviation to whatever that function returns\n",
        "    period_abbr = time_fields['period_abbr']\n",
        "\n",
        "    # Depending on the time period, increment the week or month by one after\n",
        "    # first converting it to the date time of the start of the period\n",
        "    if time_period == 'week':\n",
        "        start_of_next_period = pd.to_datetime(pd.PeriodIndex(xau_grouping_col).start_time + timedelta(weeks = 1))\n",
        "    elif time_period == 'month':\n",
        "        start_of_next_period = pd.to_datetime(pd.PeriodIndex(xau_grouping_col, freq = period_abbr).start_time) + pd.DateOffset(months = 1)\n",
        "    else:\n",
        "        start_of_next_period = None\n",
        "\n",
        "    # Convert the date time from the previous step back into the appropriate\n",
        "    # period (week or month)\n",
        "    if start_of_next_period is not None:\n",
        "        next_period = pd.Series(start_of_next_period).dt.to_period(period_abbr)\n",
        "    else:\n",
        "        next_period = None\n",
        "\n",
        "    # Return the next period\n",
        "    return next_period\n",
        "\n",
        "\n",
        "\n",
        "### create_xau_decorated_df is a generic function that allows us to find WAU\n",
        "### Decorated or MAU Decorated from a DAU Decorated based on the time period\n",
        "### that gets passed in\n",
        "def create_xau_decorated_df(dau_decorated_df, time_period):\n",
        "\n",
        "    # These are the parameters that are set from the get_time_period_dict\n",
        "    # function above\n",
        "    time_fields = get_time_period_dict(time_period)\n",
        "    grouping_col = time_fields['grouping_col']\n",
        "    frequency = time_fields['frequency']\n",
        "    first_period_col = time_fields['first_period_col']\n",
        "    period_abbr = time_fields['period_abbr']\n",
        "\n",
        "    # Print a notification message indicating that this function has been called\n",
        "    print('Creating ' + frequency + ' Active Users Decorated dataframe')\n",
        "\n",
        "    # We are grouping by the grouping_col (which is either \"Week\" or \"Month_Year\"),\n",
        "    # the user_id, and the first_period_col (either \"first_week\" or \"first_month\")\n",
        "    # For each user_id, there is one and only one first_period_col\n",
        "    groupby_cols = [grouping_col, 'user_id', first_period_col]\n",
        "\n",
        "    # Start by making a copy of the dataframe that gets passed in so as not to\n",
        "    # affect the original\n",
        "    dau_decorated = dau_decorated_df.copy()\n",
        "\n",
        "    # Convert the activity_date for each transaction in dau_decorated to a\n",
        "    # period the same timeframe as the period in question (either a week or a\n",
        "    # month)\n",
        "    dau_decorated[grouping_col] = pd.to_datetime(dau_decorated['activity_date']).dt.to_period(period_abbr)\n",
        "\n",
        "    # Group dau_decorated into the grouping_cols defined above and aggregate the\n",
        "    # sum of the inc_amt field\n",
        "    xau = (dau_decorated.groupby(groupby_cols, as_index = False)['inc_amt'].sum())\n",
        "\n",
        "    # Set a new column with the next time period by calling the increment_period\n",
        "    # function defined above\n",
        "    xau['Next_' + grouping_col] = increment_period(xau[grouping_col], time_period)\n",
        "\n",
        "    # Select a subset of the resultant columns from the groupby to output\n",
        "    output_cols = [grouping_col, 'user_id', 'inc_amt', first_period_col, 'Next_' + grouping_col]\n",
        "    xau = xau[output_cols]\n",
        "\n",
        "    # Return the resultant dataset\n",
        "    return xau\n",
        "\n",
        "\n",
        "\n",
        "# Run the create_xau_decorated_df function above (which calls the other two\n",
        "# functions in this code block) and show the first ten rows\n",
        "mau_decorated = create_xau_decorated_df(dau_decorated, 'month')\n",
        "mau_decorated.head(10)"
      ],
      "execution_count": 29,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Creating Monthly Active Users Decorated dataframe\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "  Month_Year user_id  inc_amt first_month Next_Month_Year\n",
              "0    2020-07   9030A   101.83     2020-07         2020-08\n",
              "1    2020-07   9033A     7.50     2020-07         2020-08\n",
              "2    2020-07   9036A    25.50     2020-07         2020-08\n",
              "3    2020-07   9045A    11.75     2020-07         2020-08\n",
              "4    2020-07   9046A    48.75     2020-07         2020-08\n",
              "5    2020-07   9051A    11.75     2020-07         2020-08\n",
              "6    2020-07   9056A    85.76     2020-07         2020-08\n",
              "7    2020-07   9057A    23.50     2020-07         2020-08\n",
              "8    2020-07   9062A    11.75     2020-07         2020-08\n",
              "9    2020-07   9064A    12.25     2020-07         2020-08"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-352f05b1-d093-4b46-b352-6d1b2a821b95\" class=\"colab-df-container\">\n",
              "    <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>user_id</th>\n",
              "      <th>inc_amt</th>\n",
              "      <th>first_month</th>\n",
              "      <th>Next_Month_Year</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>2020-07</td>\n",
              "      <td>9030A</td>\n",
              "      <td>101.83</td>\n",
              "      <td>2020-07</td>\n",
              "      <td>2020-08</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2020-07</td>\n",
              "      <td>9033A</td>\n",
              "      <td>7.50</td>\n",
              "      <td>2020-07</td>\n",
              "      <td>2020-08</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2020-07</td>\n",
              "      <td>9036A</td>\n",
              "      <td>25.50</td>\n",
              "      <td>2020-07</td>\n",
              "      <td>2020-08</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>2020-07</td>\n",
              "      <td>9045A</td>\n",
              "      <td>11.75</td>\n",
              "      <td>2020-07</td>\n",
              "      <td>2020-08</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>2020-07</td>\n",
              "      <td>9046A</td>\n",
              "      <td>48.75</td>\n",
              "      <td>2020-07</td>\n",
              "      <td>2020-08</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>2020-07</td>\n",
              "      <td>9051A</td>\n",
              "      <td>11.75</td>\n",
              "      <td>2020-07</td>\n",
              "      <td>2020-08</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>2020-07</td>\n",
              "      <td>9056A</td>\n",
              "      <td>85.76</td>\n",
              "      <td>2020-07</td>\n",
              "      <td>2020-08</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>2020-07</td>\n",
              "      <td>9057A</td>\n",
              "      <td>23.50</td>\n",
              "      <td>2020-07</td>\n",
              "      <td>2020-08</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>2020-07</td>\n",
              "      <td>9062A</td>\n",
              "      <td>11.75</td>\n",
              "      <td>2020-07</td>\n",
              "      <td>2020-08</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>2020-07</td>\n",
              "      <td>9064A</td>\n",
              "      <td>12.25</td>\n",
              "      <td>2020-07</td>\n",
              "      <td>2020-08</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-352f05b1-d093-4b46-b352-6d1b2a821b95')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-352f05b1-d093-4b46-b352-6d1b2a821b95 button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-352f05b1-d093-4b46-b352-6d1b2a821b95');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "<div id=\"df-814c0c75-0db2-4515-b9c0-86e4748bceb5\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-814c0c75-0db2-4515-b9c0-86e4748bceb5')\"\n",
              "            title=\"Suggest charts\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const quickchartButtonEl =\n",
              "        document.querySelector('#' + key + ' button');\n",
              "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "      try {\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      } catch (error) {\n",
              "        console.error('Error during call to suggestCharts:', error);\n",
              "      }\n",
              "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-814c0c75-0db2-4515-b9c0-86e4748bceb5 button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "\n",
              "    </div>\n",
              "  </div>\n"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "dataframe",
              "variable_name": "mau_decorated",
              "summary": "{\n  \"name\": \"mau_decorated\",\n  \"rows\": 93558,\n  \"fields\": [\n    {\n      \"column\": \"Month_Year\",\n      \"properties\": {\n        \"dtype\": \"period[M]\",\n        \"num_unique_values\": 46,\n        \"samples\": [\n          \"2023-10\",\n          \"2022-08\",\n          \"2022-09\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"user_id\",\n      \"properties\": {\n        \"dtype\": \"category\",\n        \"num_unique_values\": 15305,\n        \"samples\": [\n          \"27664A\",\n          \"25101A\",\n          \"14805A\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"inc_amt\",\n      \"properties\": {\n        \"dtype\": \"number\",\n        \"std\": 73.92911199138364,\n        \"min\": 0.48,\n        \"max\": 1947.91,\n        \"num_unique_values\": 10914,\n        \"samples\": [\n          84.36,\n          125.33999999999999,\n          26.589999999999996\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"first_month\",\n      \"properties\": {\n        \"dtype\": \"period[M]\",\n        \"num_unique_values\": 46,\n        \"samples\": [\n          \"2023-10\",\n          \"2022-08\",\n          \"2022-09\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"Next_Month_Year\",\n      \"properties\": {\n        \"dtype\": \"period[M]\",\n        \"num_unique_values\": 46,\n        \"samples\": [\n          \"2023-11\",\n          \"2022-09\",\n          \"2022-10\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    }\n  ]\n}"
            }
          },
          "metadata": {},
          "execution_count": 29
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "9pXteK_7J5PR"
      },
      "source": [
        "#### Next, calculate total users by category in each month\n",
        "Now that we have aggregated each user's activity in each month, we can start calculating the growth accounting for each month. In this step, we are calculating the following:\n",
        "\n",
        "* **Monthly Active Users (MAU)** used the service this month\n",
        "* **Retained** users used the service both this month and last month\n",
        "* **New** users have never used the service before this month\n",
        "* **Resurrected** users used the service before this month, but did not use it last month\n",
        "* **Churned** users used the service last month but not this month\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "7IJ8DrHlLnKV",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 415
        },
        "outputId": "3459292a-d83b-4ff1-f7cf-3f6c7b1db85c"
      },
      "source": [
        "### This takes a dataframe of transactions grouped by a particular date period\n",
        "### and returns active, retained, new, resurrected, and churned users for that\n",
        "### time period\n",
        "### Reminder: the .t suffix stands for \"this month\" and .l stands for \"last month\"\n",
        "def calc_user_ga(x, grouping_col, first_period_col):\n",
        "\n",
        "    # au = Active Users = the count of unique user_id's this period\n",
        "    au = x.loc[~x[grouping_col + '.t'].isnull(), 'user_id'].nunique()\n",
        "\n",
        "    # ret_users = retained_users = unique user_ids that transacted this period\n",
        "    # and last\n",
        "    ret_users = (x.loc[(x['inc_amt.t'] > 0) & (x['inc_amt.l'] > 0),\n",
        "                       'user_id']\n",
        "                 .nunique())\n",
        "\n",
        "    # new_users = unique user_id's for whom  this period's first_period_col is\n",
        "    # the same as this period's grouping_col\n",
        "    new_users = (x.loc[x[first_period_col + '.t'] == x[grouping_col + '.t'],\n",
        "                       'user_id']\n",
        "                 .nunique())\n",
        "\n",
        "    # res_users = resurrected users = transacted this period but not last, and\n",
        "    # this is not their first period to transact\n",
        "    res_users = (x.loc[(x[first_period_col + '.t'] != x[grouping_col + '.t']) &\n",
        "                       ~(x['inc_amt.l'] > 0),\n",
        "                       'user_id']\n",
        "                 .nunique())\n",
        "\n",
        "    # churned_users = transacted last period but not this one\n",
        "    churned_users = -1 * x.loc[~(x['inc_amt.t'] > 0), 'user_id'].nunique()\n",
        "\n",
        "    vals = [au, ret_users, new_users, res_users, churned_users]\n",
        "    return vals\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "### This takes one to many rows of growth accounting figures for a specific\n",
        "### date and calculates the user quick ratio\n",
        "### It is used when calculating standard or rolling quick ratio\n",
        "def calc_user_qr(row,\n",
        "                 new_col = 'new_users',\n",
        "                 res_col = 'resurrected_users',\n",
        "                 churned_col = 'churned_users'\n",
        "                ):\n",
        "\n",
        "    # These three lines make sure the column is found in the row. If not it sets it to 0\n",
        "    new_users = row[new_col] if hasattr(row, new_col) and pd.notnull(row[new_col]) else 0\n",
        "    res_users = row[res_col] if hasattr(row, res_col) and pd.notnull(row[res_col]) else 0\n",
        "    churned_users = row[churned_col] if hasattr(row, churned_col) and pd.notnull(row[churned_col]) else 0\n",
        "\n",
        "    # If churned users is < 0 then we calculate the quick ratio using the equation.\n",
        "    # Otherwise, we can't divide by zero, so it's set to NaN\n",
        "    if churned_users < 0:\n",
        "        user_qr = -1 * (new_users + res_users) / churned_users\n",
        "    else:\n",
        "        user_qr = math.nan\n",
        "\n",
        "    return user_qr\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "### Using the numbers in the \"final\" growth accounting dataframe, calculate\n",
        "### the number of users at the beginning of the period (BOP), the\n",
        "### period-over-period user retention ratio, the user quick ratio, and\n",
        "### compound growth rates\n",
        "def calc_user_ga_ratios(user_xga_df, time_period, growth_rate_periods=12):\n",
        "\n",
        "    time_fields = get_time_period_dict(time_period)\n",
        "    frequency = time_fields['frequency']\n",
        "    per = time_fields['period_abbr']\n",
        "\n",
        "    this_ratio_df = user_xga_df.copy()\n",
        "    this_ratio_df['segment'] = 'All'\n",
        "\n",
        "    # The users at the beginning of the period (BOP) are the active users from\n",
        "    # the previous period. The shift function in Pandas allows us to get the\n",
        "    # value from the previous row\n",
        "    this_ratio_df['Users BOP'] = this_ratio_df[frequency + ' Active Users'].shift(1)\n",
        "\n",
        "    # Period over Period User Retention is this period's Retained Users divided\n",
        "    # by the number of users at the beginning of the period (BOP, above)\n",
        "    this_ratio_df[per + 'o' + per + ' User Retention'] = this_ratio_df['Retained Users'] / this_ratio_df['Users BOP']\n",
        "\n",
        "    # Call the calc_user_qr function to calculate each period's quick ratio,\n",
        "    # passing in the correct column names\n",
        "    this_ratio_df['User Quick Ratio'] = this_ratio_df.apply(lambda x: calc_user_qr(x,\n",
        "                 new_col = 'New Users', res_col = 'Resurrected Users',\n",
        "                 churned_col = 'Churned Users'), axis = 1)\n",
        "\n",
        "    # Set the column name for the compound growth rate. For example, CMGR12 is\n",
        "    # the compound monthly growth rate calculated over a 12 month period.\n",
        "    cgr_col = 'User C%sGR%s' % (per, growth_rate_periods)\n",
        "\n",
        "    # To calculate the compound growth rate, divide the active users from\n",
        "    # the current period by the active users from growth_rate_periods ago,\n",
        "    # take it to the 1/growth_rate_periods power, and subtract one.\n",
        "    this_ratio_df[cgr_col] = (np.power((this_ratio_df[frequency + ' Active Users'] /\n",
        "                                        this_ratio_df[frequency + ' Active Users']\n",
        "                                        .shift(growth_rate_periods)),\n",
        "                                       1/growth_rate_periods)\n",
        "                              - 1)\n",
        "\n",
        "    # The Growth Threshold and growth rate target are constants for display\n",
        "    # purposes\n",
        "    this_ratio_df['Growth Threshold'] = 1.0\n",
        "    this_ratio_df[cgr_col + ' Target'] = 0.1\n",
        "\n",
        "    return this_ratio_df\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "### Produces the \"final\" growth accounting dataframe with both user and\n",
        "### revenue numbers for each time period in the \"decorated\" dataframe\n",
        "def create_user_growth_accounting_df(xau_decorated_df,\n",
        "                                     time_period,\n",
        "                                     keep_last_period = True,\n",
        "                                     date_limit = None,\n",
        "                                     growth_rate_periods = 12\n",
        "                                    ):\n",
        "\n",
        "    print('Creating User Growth Accounting dataframe')\n",
        "\n",
        "    # These are the parameters that are set from the get_time_period_dict\n",
        "    # function above\n",
        "    time_fields = get_time_period_dict(time_period)\n",
        "    grouping_col = time_fields['grouping_col']\n",
        "    first_period_col = time_fields['first_period_col']\n",
        "    frequency = time_fields['frequency']\n",
        "\n",
        "    # In this section, we create a copy of xau_decorated to join to itself.\n",
        "    # We turn the Next_<period> column into the column to join with the current\n",
        "    # period column. This results in user_id level data from this period,\n",
        "    # the fields with a .t suffix, and last period, with a .l suffix.\n",
        "    xau_decorated_df_last = xau_decorated_df.copy()\n",
        "    xau_decorated_df_last[grouping_col + '_join'] = xau_decorated_df_last['Next_' + grouping_col]\n",
        "    xau_decorated_df[grouping_col + '_join'] = xau_decorated_df[grouping_col]\n",
        "\n",
        "    interim_join_cols = ['user_id', grouping_col + '_join']\n",
        "\n",
        "    xga_interim = pd.merge(xau_decorated_df, xau_decorated_df_last,\n",
        "                           suffixes = ['.t', '.l'],\n",
        "                           how = 'outer',\n",
        "                           left_on = interim_join_cols,\n",
        "                           right_on = interim_join_cols)\n",
        "\n",
        "    # In this section we no longer group by user_id, and just group by the\n",
        "    # time period (week or month). For each period grouping, we use calc_user_ga\n",
        "    # to determine the numbers of active, retained, new, resurrected, and churned\n",
        "    # users.\n",
        "    groupby_cols = [grouping_col + '_join']\n",
        "\n",
        "    user_xga = (xga_interim.groupby(groupby_cols)\n",
        "                .apply(lambda x: pd.Series(calc_user_ga(x,\n",
        "                                                        grouping_col,\n",
        "                                                        first_period_col),\n",
        "                                           index = [frequency + ' Active Users',\n",
        "                                                    'Retained Users',\n",
        "                                                    'New Users',\n",
        "                                                    'Resurrected Users',\n",
        "                                                    'Churned Users'\n",
        "                                                    ]))\n",
        "                .reset_index()\n",
        "                .rename(columns = {grouping_col + '_join' : grouping_col}))\n",
        "\n",
        "    # Filter out any months that have 0 active users\n",
        "    user_xga = user_xga[user_xga[frequency + ' Active Users'] > 0]\n",
        "\n",
        "    # If we choose not to keep the last period of the resultant data set, we can\n",
        "    # get rid of it. This is particularly helpful if you are running this to\n",
        "    # determine monthly growth accounting with data through a partial last month\n",
        "    if not keep_last_period:\n",
        "        user_xga = user_xga[:-1]\n",
        "\n",
        "    # If we choose to eliminate more than one month of data from the end of the\n",
        "    # dataset we can use the date_limit parameter. This comes into play if a\n",
        "    # dataset contains data with future dates, perhaps from projections\n",
        "    if date_limit is not None:\n",
        "        user_xga = user_xga[user_xga[grouping_col] <= date_limit]\n",
        "\n",
        "    # This line calls the calc_user_ga_ratios function above to calculate\n",
        "    # the ratios from the basic user counts in each category\n",
        "    user_xga_with_ratios = calc_user_ga_ratios(user_xga, time_period,\n",
        "                                               growth_rate_periods)\n",
        "\n",
        "    return user_xga_with_ratios\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "# Call create_user_growth_accounting_df, passing in mau_decorated and 'month'\n",
        "# as the time period. We will use the default values for the other parameters.\n",
        "user_xga_with_ratios = create_user_growth_accounting_df(mau_decorated, 'month')\n",
        "user_xga_with_ratios.tail(10)\n"
      ],
      "execution_count": 30,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Creating User Growth Accounting dataframe\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "   Month_Year  Monthly Active Users  Retained Users  New Users  \\\n",
              "36    2023-07                  3248            2505        435   \n",
              "37    2023-08                  3215            2631        356   \n",
              "38    2023-09                  3355            2694        372   \n",
              "39    2023-10                  3521            2813        438   \n",
              "40    2023-11                  3609            2929        412   \n",
              "41    2023-12                  3946            3048        569   \n",
              "42    2024-01                  4130            3308        530   \n",
              "43    2024-02                  4447            3486        687   \n",
              "44    2024-03                  4686            3778        603   \n",
              "45    2024-04                  4739            3889        573   \n",
              "\n",
              "    Resurrected Users  Churned Users segment  Users BOP  MoM User Retention  \\\n",
              "36                308           -555     All     3060.0            0.818627   \n",
              "37                228           -617     All     3248.0            0.810037   \n",
              "38                289           -521     All     3215.0            0.837947   \n",
              "39                270           -542     All     3355.0            0.838450   \n",
              "40                268           -592     All     3521.0            0.831866   \n",
              "41                329           -561     All     3609.0            0.844555   \n",
              "42                292           -638     All     3946.0            0.838317   \n",
              "43                274           -644     All     4130.0            0.844068   \n",
              "44                305           -669     All     4447.0            0.849562   \n",
              "45                277           -797     All     4686.0            0.829919   \n",
              "\n",
              "    User Quick Ratio  User CMGR12  Growth Threshold  User CMGR12 Target  \n",
              "36          1.338739     0.031717               1.0                 0.1  \n",
              "37          0.946515     0.025557               1.0                 0.1  \n",
              "38          1.268714     0.033168               1.0                 0.1  \n",
              "39          1.306273     0.035674               1.0                 0.1  \n",
              "40          1.148649     0.036914               1.0                 0.1  \n",
              "41          1.600713     0.038767               1.0                 0.1  \n",
              "42          1.288401     0.036728               1.0                 0.1  \n",
              "43          1.492236     0.043886               1.0                 0.1  \n",
              "44          1.357250     0.040949               1.0                 0.1  \n",
              "45          1.066499     0.038290               1.0                 0.1  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-2cc47dc7-64e6-4b26-85d7-e0de77314c71\" class=\"colab-df-container\">\n",
              "    <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>Monthly Active Users</th>\n",
              "      <th>Retained Users</th>\n",
              "      <th>New Users</th>\n",
              "      <th>Resurrected Users</th>\n",
              "      <th>Churned Users</th>\n",
              "      <th>segment</th>\n",
              "      <th>Users BOP</th>\n",
              "      <th>MoM User Retention</th>\n",
              "      <th>User Quick Ratio</th>\n",
              "      <th>User CMGR12</th>\n",
              "      <th>Growth Threshold</th>\n",
              "      <th>User CMGR12 Target</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>36</th>\n",
              "      <td>2023-07</td>\n",
              "      <td>3248</td>\n",
              "      <td>2505</td>\n",
              "      <td>435</td>\n",
              "      <td>308</td>\n",
              "      <td>-555</td>\n",
              "      <td>All</td>\n",
              "      <td>3060.0</td>\n",
              "      <td>0.818627</td>\n",
              "      <td>1.338739</td>\n",
              "      <td>0.031717</td>\n",
              "      <td>1.0</td>\n",
              "      <td>0.1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>37</th>\n",
              "      <td>2023-08</td>\n",
              "      <td>3215</td>\n",
              "      <td>2631</td>\n",
              "      <td>356</td>\n",
              "      <td>228</td>\n",
              "      <td>-617</td>\n",
              "      <td>All</td>\n",
              "      <td>3248.0</td>\n",
              "      <td>0.810037</td>\n",
              "      <td>0.946515</td>\n",
              "      <td>0.025557</td>\n",
              "      <td>1.0</td>\n",
              "      <td>0.1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>38</th>\n",
              "      <td>2023-09</td>\n",
              "      <td>3355</td>\n",
              "      <td>2694</td>\n",
              "      <td>372</td>\n",
              "      <td>289</td>\n",
              "      <td>-521</td>\n",
              "      <td>All</td>\n",
              "      <td>3215.0</td>\n",
              "      <td>0.837947</td>\n",
              "      <td>1.268714</td>\n",
              "      <td>0.033168</td>\n",
              "      <td>1.0</td>\n",
              "      <td>0.1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>39</th>\n",
              "      <td>2023-10</td>\n",
              "      <td>3521</td>\n",
              "      <td>2813</td>\n",
              "      <td>438</td>\n",
              "      <td>270</td>\n",
              "      <td>-542</td>\n",
              "      <td>All</td>\n",
              "      <td>3355.0</td>\n",
              "      <td>0.838450</td>\n",
              "      <td>1.306273</td>\n",
              "      <td>0.035674</td>\n",
              "      <td>1.0</td>\n",
              "      <td>0.1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>40</th>\n",
              "      <td>2023-11</td>\n",
              "      <td>3609</td>\n",
              "      <td>2929</td>\n",
              "      <td>412</td>\n",
              "      <td>268</td>\n",
              "      <td>-592</td>\n",
              "      <td>All</td>\n",
              "      <td>3521.0</td>\n",
              "      <td>0.831866</td>\n",
              "      <td>1.148649</td>\n",
              "      <td>0.036914</td>\n",
              "      <td>1.0</td>\n",
              "      <td>0.1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>41</th>\n",
              "      <td>2023-12</td>\n",
              "      <td>3946</td>\n",
              "      <td>3048</td>\n",
              "      <td>569</td>\n",
              "      <td>329</td>\n",
              "      <td>-561</td>\n",
              "      <td>All</td>\n",
              "      <td>3609.0</td>\n",
              "      <td>0.844555</td>\n",
              "      <td>1.600713</td>\n",
              "      <td>0.038767</td>\n",
              "      <td>1.0</td>\n",
              "      <td>0.1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>42</th>\n",
              "      <td>2024-01</td>\n",
              "      <td>4130</td>\n",
              "      <td>3308</td>\n",
              "      <td>530</td>\n",
              "      <td>292</td>\n",
              "      <td>-638</td>\n",
              "      <td>All</td>\n",
              "      <td>3946.0</td>\n",
              "      <td>0.838317</td>\n",
              "      <td>1.288401</td>\n",
              "      <td>0.036728</td>\n",
              "      <td>1.0</td>\n",
              "      <td>0.1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>43</th>\n",
              "      <td>2024-02</td>\n",
              "      <td>4447</td>\n",
              "      <td>3486</td>\n",
              "      <td>687</td>\n",
              "      <td>274</td>\n",
              "      <td>-644</td>\n",
              "      <td>All</td>\n",
              "      <td>4130.0</td>\n",
              "      <td>0.844068</td>\n",
              "      <td>1.492236</td>\n",
              "      <td>0.043886</td>\n",
              "      <td>1.0</td>\n",
              "      <td>0.1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>44</th>\n",
              "      <td>2024-03</td>\n",
              "      <td>4686</td>\n",
              "      <td>3778</td>\n",
              "      <td>603</td>\n",
              "      <td>305</td>\n",
              "      <td>-669</td>\n",
              "      <td>All</td>\n",
              "      <td>4447.0</td>\n",
              "      <td>0.849562</td>\n",
              "      <td>1.357250</td>\n",
              "      <td>0.040949</td>\n",
              "      <td>1.0</td>\n",
              "      <td>0.1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>45</th>\n",
              "      <td>2024-04</td>\n",
              "      <td>4739</td>\n",
              "      <td>3889</td>\n",
              "      <td>573</td>\n",
              "      <td>277</td>\n",
              "      <td>-797</td>\n",
              "      <td>All</td>\n",
              "      <td>4686.0</td>\n",
              "      <td>0.829919</td>\n",
              "      <td>1.066499</td>\n",
              "      <td>0.038290</td>\n",
              "      <td>1.0</td>\n",
              "      <td>0.1</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-2cc47dc7-64e6-4b26-85d7-e0de77314c71')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-2cc47dc7-64e6-4b26-85d7-e0de77314c71 button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-2cc47dc7-64e6-4b26-85d7-e0de77314c71');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "<div id=\"df-5a18d5f1-b909-4414-9000-9d2bada4e0f4\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-5a18d5f1-b909-4414-9000-9d2bada4e0f4')\"\n",
              "            title=\"Suggest charts\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const quickchartButtonEl =\n",
              "        document.querySelector('#' + key + ' button');\n",
              "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "      try {\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      } catch (error) {\n",
              "        console.error('Error during call to suggestCharts:', error);\n",
              "      }\n",
              "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-5a18d5f1-b909-4414-9000-9d2bada4e0f4 button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "\n",
              "    </div>\n",
              "  </div>\n"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "dataframe",
              "summary": "{\n  \"name\": \"user_xga_with_ratios\",\n  \"rows\": 10,\n  \"fields\": [\n    {\n      \"column\": \"Month_Year\",\n      \"properties\": {\n        \"dtype\": \"period[M]\",\n        \"num_unique_values\": 10,\n        \"samples\": [\n          \"2024-03\",\n          \"2023-08\",\n          \"2023-12\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"Monthly Active Users\",\n      \"properties\": {\n        \"dtype\": \"number\",\n        \"std\": 586,\n        \"min\": 3215,\n        \"max\": 4739,\n        \"num_unique_values\": 10,\n        \"samples\": [\n          4686,\n          3215,\n          3946\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"Retained Users\",\n      \"properties\": {\n        \"dtype\": \"number\",\n        \"std\": 486,\n        \"min\": 2505,\n        \"max\": 3889,\n        \"num_unique_values\": 10,\n        \"samples\": [\n          3778,\n          2631,\n          3048\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"New Users\",\n      \"properties\": {\n        \"dtype\": \"number\",\n        \"std\": 110,\n        \"min\": 356,\n        \"max\": 687,\n        \"num_unique_values\": 10,\n        \"samples\": [\n          603,\n          356,\n          569\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"Resurrected Users\",\n      \"properties\": {\n        \"dtype\": \"number\",\n        \"std\": 27,\n        \"min\": 228,\n        \"max\": 329,\n        \"num_unique_values\": 10,\n        \"samples\": [\n          305,\n          228,\n          329\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"Churned Users\",\n      \"properties\": {\n        \"dtype\": \"number\",\n        \"std\": 80,\n        \"min\": -797,\n        \"max\": -521,\n        \"num_unique_values\": 10,\n        \"samples\": [\n          -669,\n          -617,\n          -561\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"segment\",\n      \"properties\": {\n        \"dtype\": \"category\",\n        \"num_unique_values\": 1,\n        \"samples\": [\n          \"All\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"Users BOP\",\n      \"properties\": {\n        \"dtype\": \"number\",\n        \"std\": 555.7165644463012,\n        \"min\": 3060.0,\n        \"max\": 4686.0,\n        \"num_unique_values\": 10,\n        \"samples\": [\n          4447.0\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"MoM User Retention\",\n      \"properties\": {\n        \"dtype\": \"number\",\n        \"std\": 0.012200128726759407,\n        \"min\": 0.8100369458128078,\n        \"max\": 0.8495615021362717,\n        \"num_unique_values\": 10,\n        \"samples\": [\n          0.8495615021362717\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"User Quick Ratio\",\n      \"properties\": {\n        \"dtype\": \"number\",\n        \"std\": 0.1921327797159591,\n        \"min\": 0.946515397082658,\n        \"max\": 1.6007130124777185,\n        \"num_unique_values\": 10,\n        \"samples\": [\n          1.3572496263079223\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"User CMGR12\",\n      \"properties\": {\n        \"dtype\": \"number\",\n        \"std\": 0.005123053323912357,\n        \"min\": 0.025556945702222222,\n        \"max\": 0.043886436932810025,\n        \"num_unique_values\": 10,\n        \"samples\": [\n          0.04094905760973244\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"Growth Threshold\",\n      \"properties\": {\n        \"dtype\": \"number\",\n        \"std\": 0.0,\n        \"min\": 1.0,\n        \"max\": 1.0,\n        \"num_unique_values\": 1,\n        \"samples\": [\n          1.0\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"User CMGR12 Target\",\n      \"properties\": {\n        \"dtype\": \"number\",\n        \"std\": 0.0,\n        \"min\": 0.1,\n        \"max\": 0.1,\n        \"num_unique_values\": 1,\n        \"samples\": [\n          0.1\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    }\n  ]\n}"
            }
          },
          "metadata": {},
          "execution_count": 30
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "hzG-fUwkyM9K"
      },
      "source": [
        "## 3. Load the transformed data into Google Sheets\n",
        "**Note**: For a more detailed discussion about loading data into Google Sheets, complete with inline code, visit [Section 3 of Mini-Pipeline: Cohort Analysis](https://colab.research.google.com/drive/1oYy-wJl6VZFgOsv8uw7iGChQxUjrR5rf#scrollTo=hzG-fUwkyM9K)\n",
        "### Establish connection to Google Sheets for writing output files\n",
        "The first time you run the cell below, or after some time of inactivity, you will be asked to click on a link. That link will take you to a new tab that will authorize this script to write to Google Sheets spreadsheets in your Google Account. To enable this feature, copy the code you get into the box below and hit Enter."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "6XdHduQeyNSF"
      },
      "source": [
        "gc = tvcl.google_authenticate()"
      ],
      "execution_count": 31,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "GwwRwkfizC7Y"
      },
      "source": [
        "### Be sure to set this value to refer to your Google Sheets workbook\n",
        "GOOGLE_SHEET_KEY = '1-XnO_eWkRwX-E1fiA2Jkbe3kJvoyoPFsdeW7vnF6zS0'"
      ],
      "execution_count": 32,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "6nn3Co-B9osp",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "acb980ca-a0cf-4f6a-a90f-fc453d3fd18e"
      },
      "source": [
        "print('https://docs.google.com/spreadsheets/d/' + GOOGLE_SHEET_KEY)"
      ],
      "execution_count": 33,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "https://docs.google.com/spreadsheets/d/1-XnO_eWkRwX-E1fiA2Jkbe3kJvoyoPFsdeW7vnF6zS0\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "_ae4mpWC0kga"
      },
      "source": [
        "### Execute this function to write the data in the dataframe to the google sheet\n",
        "### and tab name specified using the gc Google credentials\n",
        "tvcl.write_to_google_sheet(user_xga_with_ratios,\n",
        "                           GOOGLE_SHEET_KEY,\n",
        "                           'MAU Growth Accounting',\n",
        "                           gc)"
      ],
      "execution_count": 34,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "XgoZiaUa8Txs"
      },
      "source": [
        "## 4. Visualize insights in Google Data Studio\n",
        "A Google DataStudio dashboard preconfigured to read from the Google Sheet created above to visualize the data is [available at this link](https://datastudio.google.com/open/1xjS__Q6ZUXuUUARkgRvY4spYUw1ePksV) or by clicking on the Google DataStudio logo at the bottom of the chart embedded below. It is available in read-only mode for you to copy, link to your own Google Sheet tabs, and see your own data visualized.\n",
        "### 4.1 Show MAU\n",
        "The first chart shows MAU with the three main active categories broken out:\n",
        "\n",
        "- Retained Users are in blue\n",
        "- New Users are in light green\n",
        "- Resurrected Users are in darker green\n",
        "\n",
        "Together these three categories add up to the total MAU for each month. The line snaking through the chart is the compound monthly growth rate calculated over a 12-month period (CMGR12). We see that it starts off high when the growth starts from a low base. But over time, the CMGR12 stabilizes around 4%. The User GMGR12 Target line of 10% serves as an example of a healthy growth rate for a successful startup, though this can vary depending on the situation."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "DLaOgza7yxww",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 471
        },
        "outputId": "4dd5c7b7-f771-493a-af86-bd732ebecca2"
      },
      "source": [
        "IFrame('https://datastudio.google.com/embed/reporting/1xjS__Q6ZUXuUUARkgRvY4spYUw1ePksV/page/YVKk',\n",
        "       width=600,\n",
        "       height=450)"
      ],
      "execution_count": 35,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<IPython.lib.display.IFrame at 0x786ab0f9e710>"
            ],
            "text/html": [
              "\n",
              "        <iframe\n",
              "            width=\"600\"\n",
              "            height=\"450\"\n",
              "            src=\"https://datastudio.google.com/embed/reporting/1xjS__Q6ZUXuUUARkgRvY4spYUw1ePksV/page/YVKk\"\n",
              "            frameborder=\"0\"\n",
              "            allowfullscreen\n",
              "            \n",
              "        ></iframe>\n",
              "        "
            ]
          },
          "metadata": {},
          "execution_count": 35
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "tMbSWX__1u4R"
      },
      "source": [
        "### 4.2 Show MAU Growth Accounting\n",
        "The chart below is similar to the last one in that it still shows the same New and Resurrected user counts in light green and darker green, respectively. This chart removes the retained user count in favor of churned users in orange (expressed as a negative number). It also removes the CMGR12 line in favor of two new lines: the User Quick Ratio (in black) and MoM User Retention rate (in blue).\n",
        "\n",
        "- User Quick Ratio = -1 * (New Users + Resurrected Users) / Churned Users\n",
        "  - In other words, it is the ratio of the green bars at the top and the orange bar on the bottom\n",
        "  - The horizontal line where the Quick Ratio = 1 serves as the demarcation line between growth and shrinkage\n",
        "  - A Quick Ratio > 1 means that MAUs grew from last month to this month\n",
        "  - A Quick Ratio < 1 means that MAUs shrank from last month to this month\n",
        "  - For more about the Quick Ratio, visit our blog post [Quick Ratio as a Shortcut to Understand Product Growth](https://medium.com/theventurecity/quick-ratio-as-a-shortcut-to-understand-product-growth-ae60212bd371)\n",
        "- MoM Retention Rate = Retained Users / Users BOP\n",
        "  - Users BOP (\"beginning of period\") are the same as last month's users as calculated in the calc_user_ga_ratios function\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "0Ft-ADDcntHP",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 471
        },
        "outputId": "d2b259e4-519e-4162-9bab-3372480df931"
      },
      "source": [
        "IFrame('https://datastudio.google.com/embed/reporting/1xjS__Q6ZUXuUUARkgRvY4spYUw1ePksV/page/7eKk',\n",
        "       width=600,\n",
        "       height=450)"
      ],
      "execution_count": 36,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<IPython.lib.display.IFrame at 0x786ab0f9cb20>"
            ],
            "text/html": [
              "\n",
              "        <iframe\n",
              "            width=\"600\"\n",
              "            height=\"450\"\n",
              "            src=\"https://datastudio.google.com/embed/reporting/1xjS__Q6ZUXuUUARkgRvY4spYUw1ePksV/page/7eKk\"\n",
              "            frameborder=\"0\"\n",
              "            allowfullscreen\n",
              "            \n",
              "        ></iframe>\n",
              "        "
            ]
          },
          "metadata": {},
          "execution_count": 36
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "nmwV0-v1n3qy"
      },
      "source": [],
      "execution_count": 36,
      "outputs": []
    }
  ]
}