Mini_Pipeline_Cohort_Analysis.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_Cohort_Analysis.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: Cohort Analysis\n",
        "1. Extract raw event log data from a CSV\n",
        "2. Transform that data into a cohort analysis dataframe\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": "92fcb7c4-b063-40af-992f-abcd1df40d4e"
      },
      "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",
        "from dateutil.relativedelta import relativedelta\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\n",
        "\n",
        "### Google account authentication to give the code access to your Google account\n",
        "### and gspread to enable writing to Google Sheets\n",
        "from google.colab import auth\n",
        "from google.auth import default\n",
        "import gspread\n",
        "from gspread_dataframe import set_with_dataframe"
      ],
      "execution_count": 27,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Cloning into '/tmp/theventurecity'...\n",
            "remote: Enumerating objects: 392, done.\u001b[K\n",
            "remote: Counting objects: 100% (15/15), done.\u001b[K\n",
            "remote: Compressing objects: 100% (15/15), done.\u001b[K\n",
            "remote: Total 392 (delta 8), reused 0 (delta 0), pack-reused 377\u001b[K\n",
            "Receiving objects: 100% (392/392), 63.66 MiB | 19.33 MiB/s, done.\n",
            "Resolving deltas: 100% (242/242), done.\n",
            "Updating files: 100% (28/28), 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. 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": "87ac0258-0119-4d2b-a366-94f640a0ab20"
      },
      "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": 28,
      "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-c5c08390-1cb3-4cb4-9109-558683f361ae\" 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-c5c08390-1cb3-4cb4-9109-558683f361ae')\"\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-c5c08390-1cb3-4cb4-9109-558683f361ae 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-c5c08390-1cb3-4cb4-9109-558683f361ae');\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-62208e8e-a633-47a9-8672-df8a372c3fe5\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-62208e8e-a633-47a9-8672-df8a372c3fe5')\"\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-62208e8e-a633-47a9-8672-df8a372c3fe5 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": 28
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "aTeIz9GPwYV1"
      },
      "source": [
        "## 2. Transform the raw data into engagement analysis dataframes\n",
        "**Note**: For a more detailed discussion about creating the DAU and DAU Decorated dataframes, complete with inline code, visit [Create the DAU Decorated Data Set](https://colab.research.google.com/drive/12uehG2EcIqxcTazKs-pNQRTQSckllOmE)\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": "e58b1882-79c7-41a3-d4d9-32a66dc18519"
      },
      "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",
        "                         segment_col = 'segment'\n",
        "                        )\n",
        "dau.head(10)"
      ],
      "execution_count": 29,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "  user_id activity_date     segment  inc_amt\n",
              "0  10000A    2020-12-14         SMB    11.75\n",
              "1  10001A    2020-12-14         SMB    13.75\n",
              "2  10001A    2021-01-02         SMB     7.50\n",
              "3  10001A    2021-01-22         SMB    18.00\n",
              "4  10001A    2022-06-05         SMB     6.25\n",
              "5  10001A    2023-02-08         SMB     8.75\n",
              "6  10002A    2020-12-14  Enterprise    11.75\n",
              "7  10002A    2020-12-26  Enterprise    12.25\n",
              "8  10002A    2021-01-16  Enterprise    12.25\n",
              "9  10002A    2021-01-23  Enterprise    12.25"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-07993bb0-97fd-4d7d-9eeb-d0e1ad286cee\" 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>segment</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>SMB</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>SMB</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>SMB</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>SMB</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>SMB</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>SMB</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>Enterprise</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>Enterprise</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>Enterprise</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>Enterprise</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-07993bb0-97fd-4d7d-9eeb-d0e1ad286cee')\"\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-07993bb0-97fd-4d7d-9eeb-d0e1ad286cee 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-07993bb0-97fd-4d7d-9eeb-d0e1ad286cee');\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-e86a705b-2388-4677-b9b2-f0974799ed56\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-e86a705b-2388-4677-b9b2-f0974799ed56')\"\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-e86a705b-2388-4677-b9b2-f0974799ed56 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": 29
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "CGXjLYIjxgo5"
      },
      "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": "0Rtr2KqNx5Kh",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 398
        },
        "outputId": "60a43820-2c5d-4334-e410-7f691558fcec"
      },
      "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": 30,
      "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     segment  inc_amt    first_dt  \\\n",
              "0  10000A    2020-12-14         SMB    11.75  2020-12-14   \n",
              "1  10001A    2020-12-14         SMB    13.75  2020-12-14   \n",
              "2  10001A    2021-01-02         SMB     7.50  2020-12-14   \n",
              "3  10001A    2021-01-22         SMB    18.00  2020-12-14   \n",
              "4  10001A    2022-06-05         SMB     6.25  2020-12-14   \n",
              "5  10001A    2023-02-08         SMB     8.75  2020-12-14   \n",
              "6  10002A    2020-12-14  Enterprise    11.75  2020-12-14   \n",
              "7  10002A    2020-12-26  Enterprise    12.25  2020-12-14   \n",
              "8  10002A    2021-01-16  Enterprise    12.25  2020-12-14   \n",
              "9  10002A    2021-01-23  Enterprise    12.25  2020-12-14   \n",
              "\n",
              "              first_week first_month  \n",
              "0  2020-12-14/2020-12-20     2020-12  \n",
              "1  2020-12-14/2020-12-20     2020-12  \n",
              "2  2020-12-14/2020-12-20     2020-12  \n",
              "3  2020-12-14/2020-12-20     2020-12  \n",
              "4  2020-12-14/2020-12-20     2020-12  \n",
              "5  2020-12-14/2020-12-20     2020-12  \n",
              "6  2020-12-14/2020-12-20     2020-12  \n",
              "7  2020-12-14/2020-12-20     2020-12  \n",
              "8  2020-12-14/2020-12-20     2020-12  \n",
              "9  2020-12-14/2020-12-20     2020-12  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-fdeebf0c-7108-4eae-ab78-7c204809a5e9\" 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>segment</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>SMB</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>SMB</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>SMB</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>SMB</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>SMB</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>SMB</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>Enterprise</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>Enterprise</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>Enterprise</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>Enterprise</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-fdeebf0c-7108-4eae-ab78-7c204809a5e9')\"\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-fdeebf0c-7108-4eae-ab78-7c204809a5e9 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-fdeebf0c-7108-4eae-ab78-7c204809a5e9');\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-f0ea1255-87f6-40fb-ac68-fc7fba0053c3\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-f0ea1255-87f6-40fb-ac68-fc7fba0053c3')\"\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-f0ea1255-87f6-40fb-ac68-fc7fba0053c3 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": 30
        }
      ]
    },
    {
      "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": "E9sHP5yDwAwH"
      },
      "source": [
        "### 2.4 Calculate cohort analysis metrics\n",
        "Now that we have the \"DAU Decorated\" data frame, we can use it to calculate cohort retention and LTV metrics since they are super-important for an early-stage startup. The **create_xau_decorated_df** function below creates a monthly roll-up of the DAU Decorated data we call MAU Decorated."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "lZ2gzkY70psc",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 380
        },
        "outputId": "7aba7ee8-601f-4a2b-cd2a-4cb8bfa4b572"
      },
      "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",
        "### 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, use_segment):\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",
        "\n",
        "    groupby_cols = [grouping_col, 'user_id', first_period_col]\n",
        "\n",
        "    # If we are using the segment column, we include that in the groupby_cols\n",
        "    # list as well\n",
        "    if use_segment:\n",
        "        groupby_cols = groupby_cols + ['segment']\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",
        "    if use_segment:\n",
        "        output_cols = output_cols + ['segment']\n",
        "    xau = xau[output_cols]\n",
        "\n",
        "    # Return the resultant dataset\n",
        "    return xau\n",
        "\n",
        "\n",
        "# Run create_xau_decorated_df for MAU's (using 'month'), unsegmented\n",
        "mau_decorated = create_xau_decorated_df(dau_decorated, 'month', use_segment=False)\n",
        "mau_decorated.tail(10)"
      ],
      "execution_count": 31,
      "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",
              "93545    2024-04   9794A    24.72     2020-11         2024-05\n",
              "93546    2024-04   9808A    85.12     2020-11         2024-05\n",
              "93547    2024-04   9833A    17.98     2020-11         2024-05\n",
              "93548    2024-04   9868A    65.75     2020-12         2024-05\n",
              "93549    2024-04   9876A    19.48     2020-12         2024-05\n",
              "93550    2024-04   9902A    39.76     2020-12         2024-05\n",
              "93551    2024-04   9952A    64.20     2020-12         2024-05\n",
              "93552    2024-04   9986A   131.22     2020-12         2024-05\n",
              "93553    2024-04   9989A    21.28     2020-12         2024-05\n",
              "93554    2024-04   9995A   106.40     2020-12         2024-05"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-368d1a78-d7bc-4d0b-b420-36e04527bc0d\" 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>93545</th>\n",
              "      <td>2024-04</td>\n",
              "      <td>9794A</td>\n",
              "      <td>24.72</td>\n",
              "      <td>2020-11</td>\n",
              "      <td>2024-05</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>93546</th>\n",
              "      <td>2024-04</td>\n",
              "      <td>9808A</td>\n",
              "      <td>85.12</td>\n",
              "      <td>2020-11</td>\n",
              "      <td>2024-05</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>93547</th>\n",
              "      <td>2024-04</td>\n",
              "      <td>9833A</td>\n",
              "      <td>17.98</td>\n",
              "      <td>2020-11</td>\n",
              "      <td>2024-05</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>93548</th>\n",
              "      <td>2024-04</td>\n",
              "      <td>9868A</td>\n",
              "      <td>65.75</td>\n",
              "      <td>2020-12</td>\n",
              "      <td>2024-05</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>93549</th>\n",
              "      <td>2024-04</td>\n",
              "      <td>9876A</td>\n",
              "      <td>19.48</td>\n",
              "      <td>2020-12</td>\n",
              "      <td>2024-05</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>93550</th>\n",
              "      <td>2024-04</td>\n",
              "      <td>9902A</td>\n",
              "      <td>39.76</td>\n",
              "      <td>2020-12</td>\n",
              "      <td>2024-05</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>93551</th>\n",
              "      <td>2024-04</td>\n",
              "      <td>9952A</td>\n",
              "      <td>64.20</td>\n",
              "      <td>2020-12</td>\n",
              "      <td>2024-05</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>93552</th>\n",
              "      <td>2024-04</td>\n",
              "      <td>9986A</td>\n",
              "      <td>131.22</td>\n",
              "      <td>2020-12</td>\n",
              "      <td>2024-05</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>93553</th>\n",
              "      <td>2024-04</td>\n",
              "      <td>9989A</td>\n",
              "      <td>21.28</td>\n",
              "      <td>2020-12</td>\n",
              "      <td>2024-05</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>93554</th>\n",
              "      <td>2024-04</td>\n",
              "      <td>9995A</td>\n",
              "      <td>106.40</td>\n",
              "      <td>2020-12</td>\n",
              "      <td>2024-05</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-368d1a78-d7bc-4d0b-b420-36e04527bc0d')\"\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-368d1a78-d7bc-4d0b-b420-36e04527bc0d 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-368d1a78-d7bc-4d0b-b420-36e04527bc0d');\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-60e91bc0-6059-4bd6-9800-4102540ffea1\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-60e91bc0-6059-4bd6-9800-4102540ffea1')\"\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-60e91bc0-6059-4bd6-9800-4102540ffea1 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\": \"mau_decorated\",\n  \"rows\": 10,\n  \"fields\": [\n    {\n      \"column\": \"Month_Year\",\n      \"properties\": {\n        \"dtype\": \"period[M]\",\n        \"num_unique_values\": 1,\n        \"samples\": [\n          \"2024-04\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"user_id\",\n      \"properties\": {\n        \"dtype\": \"string\",\n        \"num_unique_values\": 10,\n        \"samples\": [\n          \"9989A\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"inc_amt\",\n      \"properties\": {\n        \"dtype\": \"number\",\n        \"std\": 40.01396657557348,\n        \"min\": 17.98,\n        \"max\": 131.22,\n        \"num_unique_values\": 10,\n        \"samples\": [\n          21.28\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"first_month\",\n      \"properties\": {\n        \"dtype\": \"period[M]\",\n        \"num_unique_values\": 2,\n        \"samples\": [\n          \"2020-12\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"Next_Month_Year\",\n      \"properties\": {\n        \"dtype\": \"period[M]\",\n        \"num_unique_values\": 1,\n        \"samples\": [\n          \"2024-05\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    }\n  ]\n}"
            }
          },
          "metadata": {},
          "execution_count": 31
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "q-hrJq9G27GZ"
      },
      "source": [
        "The **create_xau_cohort_df** function below helps us calculate what happens to the cohort of users that comes in as a new customer each month. It looks at how many of them continue to use the product (expressed as a number and as a percentage), how much income they generate each month, the income per customer, and the cumulative income per customer. This information leads to several insightful visuals as we will see below."
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "### Helper function to calculate date differences\n",
        "\n",
        "def date_difference(start, end, timeframe):\n",
        "    \"\"\"Ensure both start and end are converted to Timestamp if they are Periods.\"\"\"\n",
        "    start = pd.to_datetime(start) if isinstance(start, pd.Period) else start\n",
        "    end = pd.to_datetime(end) if isinstance(end, pd.Period) else end\n",
        "\n",
        "    delta = relativedelta(end, start)\n",
        "    if timeframe == 'day':\n",
        "        return (end - start).days\n",
        "    elif timeframe == 'week':\n",
        "        return (end - start).days // 7\n",
        "    elif timeframe == 'month':\n",
        "        return delta.years * 12 + delta.months\n",
        "    elif timeframe == 'year':\n",
        "        return delta.years\n",
        "    else:\n",
        "        raise ValueError(\"Invalid timeframe specified. Use 'day', 'week', 'month', or 'year'.\")\n",
        "\n"
      ],
      "metadata": {
        "id": "dj3-mNqLVuj3"
      },
      "execution_count": 32,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ewa5yayk3Cg4",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 363
        },
        "outputId": "7412d293-973e-425c-9da7-9ebc0240996a"
      },
      "source": [
        "### Calculate the user retention and empirical CLTV using inc_amtby cohort\n",
        "### defined by any weekly or monthly time period\n",
        "def create_xau_cohort_df(xau_decorated_df,\n",
        "                         time_period,\n",
        "                         use_segment = False,\n",
        "                         recent_periods_back_to_exclude = 1,\n",
        "                         date_limit = None,\n",
        "                         create_period_n_inc_cols = False,\n",
        "                         add_hours = False,\n",
        "                         use_standard_col_names = False):\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",
        "    unit = time_fields['unit']\n",
        "    period_abbr = time_fields['period_abbr']\n",
        "\n",
        "    # If a date_limit is set, set that date as the max date in the\n",
        "    # dau_decorated_df dataframe passed into the function.\n",
        "    # Otherwise, make a copy of the entire input dau_decorated_df.\n",
        "    if date_limit is not None:\n",
        "        xau_d = (xau_decorated_df[pd.PeriodIndex(xau_decorated_df[grouping_col],\n",
        "                                                 freq = period_abbr)\n",
        "                                  .start_time <= date_limit]\n",
        "                 .copy()\n",
        "                )\n",
        "    else:\n",
        "        xau_d = xau_decorated_df.copy()\n",
        "\n",
        "    # Set the since_col variable to say \"Months Since First\" or \"Weeks Since First\"\n",
        "    since_col = '%ss Since First' % unit\n",
        "\n",
        "    # Convert periods to datetime (start of the period) if not already done\n",
        "    xau_d[grouping_col] = pd.to_datetime(xau_d[grouping_col].dt.start_time)\n",
        "    xau_d[first_period_col] = pd.to_datetime(xau_d[first_period_col].dt.start_time)\n",
        "\n",
        "    # Calculate the value in the since_col to be the number of periods between\n",
        "    # the current period and the user's first period\n",
        "    xau_d[since_col] = [date_difference(g, f, time_period) for g, f in zip(xau_d[first_period_col], xau_d[grouping_col])]\n",
        "\n",
        "    # Since we are aggregating it all by the cohort of users that started in a\n",
        "    # particular period, we set the group by columns for the first aggregation\n",
        "    # as the first_period_col, grouping_col, and since_col\n",
        "    # first_period_col = {'first_month' | 'first_week' | 'first_day'}\n",
        "    # grouping_col = {'Month_Year' | 'Week' | 'activity_date'}\n",
        "    # since_col = {'Months Since First' | 'Weeks Since First' | 'Days Since First'}\n",
        "    first_groupby_cols = [first_period_col, grouping_col, since_col]\n",
        "\n",
        "    # If we are including the segment in the calculations, include it in the\n",
        "    # groupby columns\n",
        "    if use_segment:\n",
        "        first_groupby_cols = first_groupby_cols + ['segment']\n",
        "\n",
        "    # Group xau_d by the first_groupby_cols to find the sum of inc_amt and\n",
        "    # the number of unique user_ids in each grouping\n",
        "    xau_d = xau_d.groupby(first_groupby_cols)\\\n",
        "                    .agg({'inc_amt' : 'sum',\n",
        "                          'user_id' : 'nunique'})\\\n",
        "                    .rename(columns = { 'user_id' : 'cust_ct' })\n",
        "\n",
        "    # For the second groupby, we reduce the columns down to the first_period_col\n",
        "    # and the segment (if applicable)\n",
        "    second_groupby_cols = [first_period_col]\n",
        "    if use_segment:\n",
        "        second_groupby_cols = second_groupby_cols + ['segment']\n",
        "\n",
        "    # The second groupby is used to make calculations about the cohort as a whole\n",
        "    # rather than at the individual period level\n",
        "    # The first of such calculations is to take the first value for cust_ct\n",
        "    # (customer count) as being the number of customers in the cohort\n",
        "    xau_d['cohort_cust_ct'] = xau_d.groupby(second_groupby_cols)['cust_ct'].transform('first')\n",
        "\n",
        "    # The second calculation at the cohort level is to get the cumulative sum\n",
        "    # of the inc_amt for each period's cohort\n",
        "    xau_d['cum_inc_amt'] = xau_d.groupby(second_groupby_cols)['inc_amt'].cumsum()\n",
        "\n",
        "    # These ratios are calculated per period using the per-cohort numbers calculated\n",
        "    # using the second groupby\n",
        "    # First we calculate cum_inc_per_cohort_cust (cumulative income per cohort\n",
        "    # customer) as that period's cumulative inc_amt divided by the total number\n",
        "    # of customers in the first period of the cohort\n",
        "    xau_d['cum_inc_per_cohort_cust'] = xau_d['cum_inc_amt'] / xau_d['cohort_cust_ct']\n",
        "\n",
        "    # We also calculate how many users from the original count of cohort customers\n",
        "    # is still active in the current period. This is the cust_ret_pct (customer\n",
        "    # retention percentage)\n",
        "    xau_d['cust_ret_pct'] = xau_d['cust_ct'] / xau_d['cohort_cust_ct']\n",
        "\n",
        "    # Reset the index on the Pandas dataframe\n",
        "    xau_d = xau_d.reset_index()\n",
        "\n",
        "    # The code below removes rows, adds columns, or tweaks some of the time\n",
        "    # columns to allow for presentation in certain cases.\n",
        "\n",
        "    # If the time period in question is 'day', then the logic works somewhat\n",
        "    # differently than it does if it is a 'week' or 'month'. Week/Month are in\n",
        "    # the top \"if\" clause, while Day is in the \"else\" clause.\n",
        "    if time_period != 'day':\n",
        "\n",
        "        # If the time period is a month, and we have recent periods back to\n",
        "        # exclude (an input parameter), we use this code to exclude those\n",
        "        # periods from xau_d. The periods back is measured from TODAY's date.\n",
        "        if time_period == 'month':\n",
        "            td = pd.DateOffset(months=recent_periods_back_to_exclude)\n",
        "        elif time_period == 'week':\n",
        "            td = pd.DateOffset(weeks=recent_periods_back_to_exclude)\n",
        "\n",
        "        last_period = pd.to_datetime(datetime.today() - td).to_period(period_abbr).start_time\n",
        "        xau_d = xau_d.loc[xau_d[grouping_col] <= last_period]\n",
        "\n",
        "        # If we want to add seven hours to the first_period_col and grouping_col\n",
        "        # we do so here if the add_hours input parameter is True. It defaults\n",
        "        # to False\n",
        "        if add_hours:\n",
        "            xau_d[first_period_col] = (pd.PeriodIndex(xau_d[first_period_col],\n",
        "                                                      freq = period_abbr)\n",
        "                                       .start_time + timedelta(hours = 7))\n",
        "            xau_d[grouping_col] = (pd.PeriodIndex(xau_d[grouping_col],\n",
        "                                                  freq = period_abbr)\n",
        "                                   .start_time + timedelta(hours = 7))\n",
        "\n",
        "        # Using the segment column requires us to specify which segment each\n",
        "        # first_period_col goes with.\n",
        "        # Creator's note: needs string to handle the weekly case as well as the monthly\n",
        "        if use_segment:\n",
        "            xau_d['segment_first_' + time_period] = (xau_d[first_period_col]\n",
        "                                                     .dt\n",
        "                                                     .strftime('%Y-%m') + '-' + xau_d['segment'])\n",
        "\n",
        "        # If we want to add new columns for weekly trend analysis, we would do\n",
        "        # so by setting the create_period_n_inc_cols equal to True, which would\n",
        "        # trigger the add_period_n_cum_inc_per_cohort_cust_columns function\n",
        "        # to run. The parameter defaults to False.\n",
        "        if create_period_n_inc_cols:\n",
        "            xau_d = add_period_n_cum_inc_per_cohort_cust_columns(xau_d, since_col, unit)\n",
        "\n",
        "    else:\n",
        "\n",
        "        # In the case of days, excluding the last period involves using\n",
        "        # timedelta to subtract days from today's date\n",
        "        last_period = (datetime.today() - timedelta(days = recent_periods_back_to_exclude)).date()\n",
        "        xau_d = xau_d.loc[xau_d[grouping_col] <= last_period]\n",
        "\n",
        "        # The add_hours piece uses timedelta(hours = 7)\n",
        "        if add_hours:\n",
        "            xau_d[first_period_col] = xau_d[first_period_col] + timedelta(hours = 7)\n",
        "            xau_d[grouping_col] = xau_d[grouping_col] + timedelta(hours = 7)\n",
        "\n",
        "        # Change the data type on the since_col to be a number and not a timedelta\n",
        "        xau_d[since_col] = xau_d[since_col].astype(timedelta).map(lambda x: np.nan if pd.isnull(x) else x.days)\n",
        "\n",
        "        # Using the segment column requires us to specify which segment each\n",
        "        # first_period_col goes with.\n",
        "        if use_segment:\n",
        "            xau_d['segment_first_' + time_period] = (xau_d[first_period_col]\n",
        "                                                     .dt\n",
        "                                                     .strftime('%Y-%m-%d') + '-' + xau_d['segment'])\n",
        "\n",
        "        # Similar to above for adding weekly trend analysis, we would do\n",
        "        # so by setting the create_period_n_inc_cols equal to True, which would\n",
        "        # trigger the add_period_n_cum_inc_per_cohort_cust_columns function\n",
        "        # to run. The parameter defaults to False.\n",
        "        if create_period_n_inc_cols:\n",
        "            xau_d = add_period_n_cum_inc_per_cohort_cust_columns(xau_d, since_col, unit)\n",
        "\n",
        "    # This last bit of code changes column names if the input parameter\n",
        "    # use_standard_col_names is True. It defaults to False\n",
        "    if use_standard_col_names:\n",
        "        mapping = {'month_year' : 'month',\n",
        "                   'cust_ret_pct' : 'retained_pctg',\n",
        "                   since_col : time_period + 's_since_first'\n",
        "                   }\n",
        "\n",
        "        cols = xau_d.columns\n",
        "        new_xau_cols = []\n",
        "        for c in cols:\n",
        "            new_c = '_'.join(c.lower().split(' '))\n",
        "            if new_c in mapping:\n",
        "                new_c = mapping[new_c]\n",
        "            new_xau_cols.append(new_c)\n",
        "\n",
        "        xau_d.columns = new_xau_cols\n",
        "\n",
        "\n",
        "    return xau_d\n",
        "\n",
        "\n",
        "\n",
        "mau_retention_by_cohort = create_xau_cohort_df(mau_decorated, 'month')\n",
        "mau_retention_by_cohort.tail(10)"
      ],
      "execution_count": 33,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "     first_month Month_Year  Months Since First   inc_amt  cust_ct  \\\n",
              "1066  2023-12-01 2023-12-01                   0  23029.76      569   \n",
              "1067  2023-12-01 2024-01-01                   1  25563.56      381   \n",
              "1068  2023-12-01 2024-02-01                   2  22518.13      321   \n",
              "1069  2023-12-01 2024-03-01                   3  23657.35      298   \n",
              "1071  2024-01-01 2024-01-01                   0  19143.70      530   \n",
              "1072  2024-01-01 2024-02-01                   1  19601.45      362   \n",
              "1073  2024-01-01 2024-03-01                   2  18878.39      312   \n",
              "1075  2024-02-01 2024-02-01                   0  22804.65      687   \n",
              "1076  2024-02-01 2024-03-01                   1  28401.44      469   \n",
              "1078  2024-03-01 2024-03-01                   0  21823.96      603   \n",
              "\n",
              "      cohort_cust_ct  cum_inc_amt  cum_inc_per_cohort_cust  cust_ret_pct  \n",
              "1066             569     23029.76                40.474095      1.000000  \n",
              "1067             569     48593.32                85.401265      0.669596  \n",
              "1068             569     71111.45               124.976186      0.564148  \n",
              "1069             569     94768.80               166.553251      0.523726  \n",
              "1071             530     19143.70                36.120189      1.000000  \n",
              "1072             530     38745.15                73.104057      0.683019  \n",
              "1073             530     57623.54               108.723660      0.588679  \n",
              "1075             687     22804.65                33.194541      1.000000  \n",
              "1076             687     51206.09                74.535793      0.682678  \n",
              "1078             603     21823.96                36.192305      1.000000  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-60a41a39-db81-4079-a680-25336588d87d\" 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>first_month</th>\n",
              "      <th>Month_Year</th>\n",
              "      <th>Months Since First</th>\n",
              "      <th>inc_amt</th>\n",
              "      <th>cust_ct</th>\n",
              "      <th>cohort_cust_ct</th>\n",
              "      <th>cum_inc_amt</th>\n",
              "      <th>cum_inc_per_cohort_cust</th>\n",
              "      <th>cust_ret_pct</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>1066</th>\n",
              "      <td>2023-12-01</td>\n",
              "      <td>2023-12-01</td>\n",
              "      <td>0</td>\n",
              "      <td>23029.76</td>\n",
              "      <td>569</td>\n",
              "      <td>569</td>\n",
              "      <td>23029.76</td>\n",
              "      <td>40.474095</td>\n",
              "      <td>1.000000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1067</th>\n",
              "      <td>2023-12-01</td>\n",
              "      <td>2024-01-01</td>\n",
              "      <td>1</td>\n",
              "      <td>25563.56</td>\n",
              "      <td>381</td>\n",
              "      <td>569</td>\n",
              "      <td>48593.32</td>\n",
              "      <td>85.401265</td>\n",
              "      <td>0.669596</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1068</th>\n",
              "      <td>2023-12-01</td>\n",
              "      <td>2024-02-01</td>\n",
              "      <td>2</td>\n",
              "      <td>22518.13</td>\n",
              "      <td>321</td>\n",
              "      <td>569</td>\n",
              "      <td>71111.45</td>\n",
              "      <td>124.976186</td>\n",
              "      <td>0.564148</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1069</th>\n",
              "      <td>2023-12-01</td>\n",
              "      <td>2024-03-01</td>\n",
              "      <td>3</td>\n",
              "      <td>23657.35</td>\n",
              "      <td>298</td>\n",
              "      <td>569</td>\n",
              "      <td>94768.80</td>\n",
              "      <td>166.553251</td>\n",
              "      <td>0.523726</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1071</th>\n",
              "      <td>2024-01-01</td>\n",
              "      <td>2024-01-01</td>\n",
              "      <td>0</td>\n",
              "      <td>19143.70</td>\n",
              "      <td>530</td>\n",
              "      <td>530</td>\n",
              "      <td>19143.70</td>\n",
              "      <td>36.120189</td>\n",
              "      <td>1.000000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1072</th>\n",
              "      <td>2024-01-01</td>\n",
              "      <td>2024-02-01</td>\n",
              "      <td>1</td>\n",
              "      <td>19601.45</td>\n",
              "      <td>362</td>\n",
              "      <td>530</td>\n",
              "      <td>38745.15</td>\n",
              "      <td>73.104057</td>\n",
              "      <td>0.683019</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1073</th>\n",
              "      <td>2024-01-01</td>\n",
              "      <td>2024-03-01</td>\n",
              "      <td>2</td>\n",
              "      <td>18878.39</td>\n",
              "      <td>312</td>\n",
              "      <td>530</td>\n",
              "      <td>57623.54</td>\n",
              "      <td>108.723660</td>\n",
              "      <td>0.588679</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1075</th>\n",
              "      <td>2024-02-01</td>\n",
              "      <td>2024-02-01</td>\n",
              "      <td>0</td>\n",
              "      <td>22804.65</td>\n",
              "      <td>687</td>\n",
              "      <td>687</td>\n",
              "      <td>22804.65</td>\n",
              "      <td>33.194541</td>\n",
              "      <td>1.000000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1076</th>\n",
              "      <td>2024-02-01</td>\n",
              "      <td>2024-03-01</td>\n",
              "      <td>1</td>\n",
              "      <td>28401.44</td>\n",
              "      <td>469</td>\n",
              "      <td>687</td>\n",
              "      <td>51206.09</td>\n",
              "      <td>74.535793</td>\n",
              "      <td>0.682678</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1078</th>\n",
              "      <td>2024-03-01</td>\n",
              "      <td>2024-03-01</td>\n",
              "      <td>0</td>\n",
              "      <td>21823.96</td>\n",
              "      <td>603</td>\n",
              "      <td>603</td>\n",
              "      <td>21823.96</td>\n",
              "      <td>36.192305</td>\n",
              "      <td>1.000000</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-60a41a39-db81-4079-a680-25336588d87d')\"\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-60a41a39-db81-4079-a680-25336588d87d 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-60a41a39-db81-4079-a680-25336588d87d');\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-2a209c89-116c-4e7f-acdd-5bf9aa9852fb\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-2a209c89-116c-4e7f-acdd-5bf9aa9852fb')\"\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-2a209c89-116c-4e7f-acdd-5bf9aa9852fb 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\": \"mau_retention_by_cohort\",\n  \"rows\": 10,\n  \"fields\": [\n    {\n      \"column\": \"first_month\",\n      \"properties\": {\n        \"dtype\": \"date\",\n        \"min\": \"2023-12-01 00:00:00\",\n        \"max\": \"2024-03-01 00:00:00\",\n        \"num_unique_values\": 4,\n        \"samples\": [\n          \"2024-01-01 00:00:00\",\n          \"2024-03-01 00:00:00\",\n          \"2023-12-01 00:00:00\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"Month_Year\",\n      \"properties\": {\n        \"dtype\": \"date\",\n        \"min\": \"2023-12-01 00:00:00\",\n        \"max\": \"2024-03-01 00:00:00\",\n        \"num_unique_values\": 4,\n        \"samples\": [\n          \"2024-01-01 00:00:00\",\n          \"2024-03-01 00:00:00\",\n          \"2023-12-01 00:00:00\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"Months Since First\",\n      \"properties\": {\n        \"dtype\": \"number\",\n        \"std\": 1,\n        \"min\": 0,\n        \"max\": 3,\n        \"num_unique_values\": 4,\n        \"samples\": [\n          1,\n          3,\n          0\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"inc_amt\",\n      \"properties\": {\n        \"dtype\": \"number\",\n        \"std\": 2965.4909370904443,\n        \"min\": 18878.39,\n        \"max\": 28401.440000000002,\n        \"num_unique_values\": 10,\n        \"samples\": [\n          28401.440000000002,\n          25563.56,\n          19601.45\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"cust_ct\",\n      \"properties\": {\n        \"dtype\": \"number\",\n        \"std\": 138,\n        \"min\": 298,\n        \"max\": 687,\n        \"num_unique_values\": 10,\n        \"samples\": [\n          469,\n          381,\n          362\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"cohort_cust_ct\",\n      \"properties\": {\n        \"dtype\": \"number\",\n        \"std\": 58,\n        \"min\": 530,\n        \"max\": 687,\n        \"num_unique_values\": 4,\n        \"samples\": [\n          530,\n          603,\n          569\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"cum_inc_amt\",\n      \"properties\": {\n        \"dtype\": \"number\",\n        \"std\": 24921.021069997118,\n        \"min\": 19143.7,\n        \"max\": 94768.8,\n        \"num_unique_values\": 10,\n        \"samples\": [\n          51206.090000000004,\n          48593.32000000001,\n          38745.15\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"cum_inc_per_cohort_cust\",\n      \"properties\": {\n        \"dtype\": \"number\",\n        \"std\": 44.722111760092574,\n        \"min\": 33.19454148471616,\n        \"max\": 166.55325131810193,\n        \"num_unique_values\": 10,\n        \"samples\": [\n          74.53579330422126,\n          85.40126537785589,\n          73.10405660377359\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"cust_ret_pct\",\n      \"properties\": {\n        \"dtype\": \"number\",\n        \"std\": 0.20351389508802667,\n        \"min\": 0.523725834797891,\n        \"max\": 1.0,\n        \"num_unique_values\": 7,\n        \"samples\": [\n          1.0,\n          0.6695957820738138,\n          0.5886792452830188\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    }\n  ]\n}"
            }
          },
          "metadata": {},
          "execution_count": 33
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "hzG-fUwkyM9K"
      },
      "source": [
        "## 3. Load the transformed data into Google Sheets\n",
        "### Establish connection to Google Sheets for writing output files\n",
        "The first time you run this cell, 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": [
        "auth.authenticate_user()\n",
        "creds, _ = default()\n",
        "gc = gspread.authorize(creds)"
      ],
      "execution_count": 34,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "hKW2TNyWyf-1"
      },
      "source": [
        "### Function to facilitate writing from Pandas to Google Sheets\n",
        "For use further down in this notebook."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "cKXbV7w0ygK4"
      },
      "source": [
        "def write_to_google_sheet(dataframe, spreadsheet_key, worksheet_name, goog_creds = gc):\n",
        "\n",
        "  sh = goog_creds.open_by_key(spreadsheet_key)\n",
        "\n",
        "  ws = None\n",
        "  worksheet_list = sh.worksheets()\n",
        "  for worksheet in worksheet_list:\n",
        "    if worksheet.title == worksheet_name:\n",
        "      ws = worksheet\n",
        "\n",
        "  if ws is None:\n",
        "    ws = sh.add_worksheet(title = worksheet_name, rows=\"1\", cols = \"1\")\n",
        "\n",
        "  set_with_dataframe(ws, dataframe, row=1, col=1, include_index=False,\n",
        "                     include_column_header=True, resize=True, allow_formulas=True)"
      ],
      "execution_count": 35,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "by-pAUIe3St7"
      },
      "source": [
        "### Create Google Sheet for writing output files if one is not already established\n",
        "\n",
        "***Warning: you must choose from Options A and B below***.\n",
        "\n",
        "**Option A**: If you already have a Google Sheet where you store transformed data for analytics, go to that sheet, copy the long ID string from the sheet's URL in a browser, and paste it between the quotes below. Then uncomment the code and run the cell. Do NOT run the code for Option B without commenting it out.\n",
        "\n",
        "This option is especially important if you have previously linked a reporting dashboard to this Google Sheet and would like to use this ETL process to update that dashboard."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "GwwRwkfizC7Y"
      },
      "source": [
        "### Option A Code\n",
        "GOOGLE_SHEET_KEY = '1-XnO_eWkRwX-E1fiA2Jkbe3kJvoyoPFsdeW7vnF6zS0'"
      ],
      "execution_count": 36,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "jNR7YkOY6jQX"
      },
      "source": [
        "**Option B**: If you are running this for the first time and do not already have a Google Sheet where you store transformed data for analytics, uncomment and run the cell below to set the GOOGLE_SHEET_KEY variable, which will be used later in the process. Be sure to replace the \"Sample Company Analytics\" filename with one of your own."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "zSzoG5Bn3h8N"
      },
      "source": [
        "### Option B Code\n",
        "# GOOGLE_SHEET_KEY = gc.create('Sample Company Analytics').id"
      ],
      "execution_count": 37,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "CUX6Br7s7ibA"
      },
      "source": [
        "After executing the cell above, a new spreadsheet with the name you supply will be shown in your sheets list on [sheets.google.com](http://sheets.google.com/). To go directly to the newly created Google Sheet, run the code block below and visit the link that it outputs. You can keep this Sheet open in a separate tab to see the data get updated whenever **write_to_google_sheet** (defined above) is called."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "6nn3Co-B9osp",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "d0b4a4e3-f917-4629-ca86-db94777e3321"
      },
      "source": [
        "print('https://docs.google.com/spreadsheets/d/' + GOOGLE_SHEET_KEY)"
      ],
      "execution_count": 38,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "https://docs.google.com/spreadsheets/d/1-XnO_eWkRwX-E1fiA2Jkbe3kJvoyoPFsdeW7vnF6zS0\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "YTuA7GJxi8P5"
      },
      "source": [
        "write_to_google_sheet(mau_retention_by_cohort, GOOGLE_SHEET_KEY, 'MAU Retention by Cohort')"
      ],
      "execution_count": 39,
      "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",
        "\n",
        "Note: sometimes there is an issue embedding the Google DataStudio dashboard in this notebook, particularly when it is rendered in the GitHub notebook reader. In that case, click on the \"Open in Colab\" button at the very top of the notebook to see a notebook that can embed the dashboard properly.\n",
        "### 4.1 Monthly Cohort User Retention\n",
        "One way to think about cohort user retention is based on the fact that, typically, not all users are retained from Month 0 to Month 1, and then to Month 2, 3, 4, etc. The chart below depicts this in the downward curve approaching. If you mouseover the bars, we can see that the January 2018 cohort at the farthest right is at 23.7% user retention in Month 13. Compare that to the 60.94% figure for that same cohort in Month 1.\n",
        "\n",
        "Sometimes this data is depicted as downward curving lines, one for each cohort. We prefer this visual because (a) the lines often end up looking jumbled and spaghetti-like; and (b) it allows us to see the trend in each Months Since First's retention. For example, the most recent Month 1 retention of 66.55% for the January 2019 cohort is down from the Month 1 peak of 72.8% percent for the July 2018 cohort. In this way, we can see the general downward slope that the spaghetti lines would give us, but also see the trends within each Months Since First, for a superior visual."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Jtw28m7Dlwo-",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 471
        },
        "outputId": "474283c5-b89d-4297-9c02-815123956db2"
      },
      "source": [
        "IFrame('https://datastudio.google.com/embed/reporting/1xjS__Q6ZUXuUUARkgRvY4spYUw1ePksV/page/EOyj',\n",
        "       width=600,\n",
        "       height=450)"
      ],
      "execution_count": 40,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<IPython.lib.display.IFrame at 0x78a2ab0ec8e0>"
            ],
            "text/html": [
              "\n",
              "        <iframe\n",
              "            width=\"600\"\n",
              "            height=\"450\"\n",
              "            src=\"https://datastudio.google.com/embed/reporting/1xjS__Q6ZUXuUUARkgRvY4spYUw1ePksV/page/EOyj\"\n",
              "            frameborder=\"0\"\n",
              "            allowfullscreen\n",
              "            \n",
              "        ></iframe>\n",
              "        "
            ]
          },
          "metadata": {},
          "execution_count": 40
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "xookNSymJYcR"
      },
      "source": [
        "### 4.2 Cumulative Customer Revenue LTV by Monthly Cohort\n",
        "The visual below shows the cumulative inc_amt *per original cohort customer* that each cohort has generated in the time it has been active. These lines give a look at how well a group of ServBiz's users keeps generating revenue after they are initially acquired. This gives us an empirical way to look at customer long-term value (LTV) over a range of possible scenarios. For example, if you mouseover 16 Months Since First in the chart below, the four cohorts that have been around that long range from `$`440.47 to `$`588.87 per cohort customer. We can use that as a 16-month LTV range for projection or for calculating LTV-to-CAC ratio.\n",
        "\n",
        "Another feature of the curves below is that, while they are slighly bending downward, the rate of change in the slope is very gradual. This implies that ServBiz's customers are active for a long period of time, meaning that the time horizon for calculating LTV may be extended and that these customers are quite value in the long-term."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "MEczC7003zvl",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 471
        },
        "outputId": "55b7e6ba-a998-4326-a3ad-a53ffc68ea84"
      },
      "source": [
        "IFrame('https://datastudio.google.com/embed/reporting/1xjS__Q6ZUXuUUARkgRvY4spYUw1ePksV/page/pVBk',\n",
        "       width=600,\n",
        "       height=450)"
      ],
      "execution_count": 41,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<IPython.lib.display.IFrame at 0x78a2ab0ec6a0>"
            ],
            "text/html": [
              "\n",
              "        <iframe\n",
              "            width=\"600\"\n",
              "            height=\"450\"\n",
              "            src=\"https://datastudio.google.com/embed/reporting/1xjS__Q6ZUXuUUARkgRvY4spYUw1ePksV/page/pVBk\"\n",
              "            frameborder=\"0\"\n",
              "            allowfullscreen\n",
              "            \n",
              "        ></iframe>\n",
              "        "
            ]
          },
          "metadata": {},
          "execution_count": 41
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "azSCjmD5Rrsb"
      },
      "source": [],
      "execution_count": 41,
      "outputs": []
    }
  ]
}