uplift_report.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "uplift_report.ipynb",
      "version": "0.3.2",
      "provenance": [],
      "collapsed_sections": [
        "eSixTLyiy0_A"
      ],
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python2",
      "language": "python",
      "display_name": "Python 2"
    },
    "pycharm": {
      "stem_cell": {
        "cell_type": "raw",
        "source": [],
        "metadata": {
          "collapsed": false
        }
      }
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/VOvchinnikov/uplift-report/blob/master/uplift_report.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "colab_type": "text",
        "id": "5mOAgeTNy0-o"
      },
      "source": [
        "# remerge uplift report\n",
        "\n",
        "This notebook allows you to validate remerge provided uplift reporting numbers. To do so it downloads and analyses exported campaign and event data from S3. The campaign data contains all users that remerge marked to be part of an uplift test, the A/B group assignment, the timestamp of marking, conversion events (click, app open or similar) and their cost. The event data reflects the app event stream and includes events, their timestamp and revenue (if any). We calculate the incremental revenue and the iROAS in line with the [remerge whitepaper](https://drive.google.com/file/d/1PTJ93Cpjw1BeiVns8dTcs2zDDWmmjpdc/view). \n",
        "\n",
        "**Hint**: This notebook can be run in any Jupyter instance with enough space/memory, as a [Google Colab notebook](#Google-Colab-version) or as a standalone Python script. If you are using a copy of this notebook running on Colab or locally you can find the original template on [GitHub: remerge/uplift-report](https://github.com/remerge/uplift-report/blob/master/uplift_report_per_campaign.ipynb)\n",
        "\n",
        "### Notebook configuration\n",
        "\n",
        "For this notebook to work properly several variables in the [Configuration](#Configuration) section need to be be set: `customer`, `audience`, `\n",
        "revenue_event`, `dates` and the AWS credentials. All of these will be provided by your remerge account manager. \n",
        "\n",
        "\n",
        "### Verification\n",
        "\n",
        "To verify that the group split is random and has no bias, user events / attributes before the campaign start can be compared and checked for an equal distribution in test and control group. For example the user age distribution, the user activity distribution or the average spend per user  should be the same in both groups pre campaign.\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "OYbsRBPTaCZW",
        "colab_type": "text"
      },
      "source": [
        "## Google Colab support\n",
        "\n",
        "This notebook can be run inside Google Colab. Due to size limitations it cointains several optimizations like removing unused fields from the input files and caching files. Furthermore it installs missing dependencies and restarts the kernel. **If pandas was upgraded the kernel needs to be restarted once per fresh instance. Just run the cell again after restart** "
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "QoJAOpL0aEIT",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "try:\n",
        "    import google.colab\n",
        "\n",
        "    IN_COLAB = True\n",
        "except:\n",
        "    IN_COLAB = False\n",
        "\n",
        "if IN_COLAB:\n",
        "    !pip install pyarrow\n",
        "    \n",
        "    import pandas as pdt\n",
        "    if pdt.__version__ < '0.23.4':\n",
        "        # upgrading pandas requires a restart of the kernel\n",
        "        # (we need an up to date pandas because we write to S3 for caching)\n",
        "        # we kill it and let it auto restart (only needed once per fresh instance)\n",
        "        !pip install pandas==0.23.4\n",
        "        \n",
        "        import os\n",
        "        os.kill(os.getpid(), 9)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "colab_type": "text",
        "id": "kyYz6TCny0-q"
      },
      "source": [
        "## Import needed packages\n",
        "\n",
        "This notebook/script needs pandas and scipy for analysis and boto to access data store on S3.\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab_type": "code",
        "id": "GWizAQT3y0-r",
        "colab": {}
      },
      "source": [
        "from datetime import datetime\n",
        "import pandas as pd\n",
        "import re\n",
        "import os\n",
        "import gzip\n",
        "import scipy\n",
        "import scipy.stats\n",
        "import s3fs\n",
        "from IPython.display import display  # so we can run this as script as well\n",
        "import gc"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "colab_type": "text",
        "id": "8u6Q76fCy0-u"
      },
      "source": [
        "## Configuration\n",
        "\n",
        "Set the customer name, audience and access credentials for the S3 bucket and path. Furthermore the event for which we want to evaluate the uplift needs to be set `revenue_event`."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "pycharm": {
          "name": "#%%\n"
        },
        "id": "43HG7UvQGADK",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "# configure path and revenue event \n",
        "customer = ''\n",
        "audiences = ['']\n",
        "revenue_event = 'purchase'\n",
        "\n",
        "# date range for the report\n",
        "dates = pd.date_range(start='2019-01-01',end='2019-01-01')\n",
        "\n",
        "# AWS credentials\n",
        "os.environ[\"AWS_ACCESS_KEY_ID\"] = ''\n",
        "os.environ[\"AWS_SECRET_ACCESS_KEY\"] = ''\n",
        "\n",
        "# Configure the reporting output: \n",
        "\n",
        "# named groups that aggregate several campaigns\n",
        "groups = {}\n",
        "\n",
        "# show uplift results per campaign:\n",
        "per_campaign_results = False\n",
        "\n",
        "# base statistical calculations on unique converters instead of conversions\n",
        "use_converters_for_significance = False\n",
        "\n",
        "# enable deduplication heuristic for appsflyer\n",
        "use_deduplication = False"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "UOJgQCwhRor6",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "# Load and import the libraries\n",
        "!git clone --branch master https://github.com/VOvchinnikov/uplift-report.git uplift_report\n",
        "\n",
        "from uplift_report.lib.helpers import read_csv"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "PjWaWZS-y0_B",
        "colab_type": "text"
      },
      "source": [
        "## Load CSV data from S3\n",
        "\n",
        "Load mark, spend and event data from S3. \n",
        "\n",
        "### IMPORTANT\n",
        "\n",
        "**The event data is usually quite large (several GB) so this operation might take several minutes or hours to complete, depending on the size and connection.**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "kFg_-_EW5TR1",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "bids_df = pd.concat([read_csv(customer, audience, revenue_event, 'marks_and_spend', date) for audience in audiences for date in dates],\n",
        "                    ignore_index=True, verify_integrity=True)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "pycharm": {
          "name": "#%%\n"
        },
        "id": "ASKrbjXoGADW",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "attributions_df = pd.concat(\n",
        "    [read_csv(customer, audience, revenue_event, 'attributions', date, extract_revenue_events) for audience in audiences for date in dates],\n",
        "    ignore_index=True, verify_integrity=True)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "collapsed": false,
        "pycharm": {
          "name": "#%% md\n"
        },
        "id": "GDEbZrBLGADa",
        "colab_type": "text"
      },
      "source": [
        "Print some statistics of the loaded data sets."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "pycharm": {
          "name": "#%%\n"
        },
        "id": "KlR3Kxb4GADb",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "bids_df.info()"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "pycharm": {
          "name": "#%%\n"
        },
        "id": "7MVma_UyGADf",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "attributions_df.info()"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "hA6vaITL5yZ4",
        "colab_type": "code",
        "pycharm": {
          "name": "#%% md\n"
        },
        "colab": {}
      },
      "source": [
        "## Remove duplicated events coming from AppsFlyer\n",
        "\n",
        "AppsFlyer is sending us two revenue events if they attribute the event to us. One of the events they send us does not contain attribution information and the other one does. Sadly, it is not possible for us to distingish correctly if an event is a duplicate or if the user actually triggered two events with nearly the same information. Therefore we rely on a heuristic. We consider an event a duplicate if the user and revenue are equal and the events are less than a minute apart."
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "colab_type": "text",
        "id": "4XVQNpr9y0_7"
      },
      "source": [
        "## Define functions to prepare data frames\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "colab_type": "text",
        "id": "IPPvDwWIy1AC"
      },
      "source": [
        "Calculate the cost of advertising give a dataframe. Remerge tracks monetary values in micro currency units. "
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab_type": "code",
        "id": "ZEISdQDny1AC",
        "colab": {}
      },
      "source": [
        "def ad_spend(df):\n",
        "    ad_spend_micros = df[df.event_type == 'buying_conversion']['cost_eur'].sum()\n",
        "    \n",
        "    return ad_spend_micros / 10 ** 6"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "XgJuPnbAJu3w",
        "colab_type": "text"
      },
      "source": [
        "The dataframe created by `marked`  will contain all mark events. Remerge marks users per campaign.  If a user was marked once for an audience he will have the same group allocation for consecutive marks (different campaigns) unless manually reset on audience level.  "
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab_type": "code",
        "id": "a6XtI0Iqy0_8",
        "colab": {}
      },
      "source": [
        "def marked(df):\n",
        "    mark_df = df[df.event_type == 'mark']\n",
        "       \n",
        "    sorted_mark_df = mark_df.sort_values('ts')\n",
        "    \n",
        "    depuplicated_mark_df = sorted_mark_df.drop_duplicates(['user_id'])\n",
        "    \n",
        "    return depuplicated_mark_df"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "colab_type": "text",
        "id": "EM9SbRf2y1AG"
      },
      "source": [
        "`revenue` creates a dataframe that contains all relevant revenue events."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab_type": "code",
        "id": "ONjCFbzIy1AH",
        "colab": {}
      },
      "source": [
        "def revenue(df):\n",
        "    # for now just return the df as is, because we filter already\n",
        "    # while reading the csv file\n",
        "    # revenue_df = df[pd.notnull(df['revenue_eur'])]\n",
        "    # return revenue_df[revenue_df.partner_event == revenue_event]\n",
        "    return df\n",
        "    \n",
        "    "
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "colab_type": "text",
        "id": "ph6eaF4Ny1Ad"
      },
      "source": [
        "`merge` joins the marked users with the revenue events and excludes any revenue event that happend before the user was marked."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab_type": "code",
        "id": "3mReofc4y1Ad",
        "colab": {}
      },
      "source": [
        "def merge(mark_df, revenue_df):\n",
        "    merged_df = pd.merge(revenue_df, mark_df, on='user_id')\n",
        "    \n",
        "    return merged_df[merged_df.ts_x > merged_df.ts_y]"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "colab_type": "text",
        "id": "armi-3kmy1Ag"
      },
      "source": [
        "## Calculate uplift kpis\n",
        "\n",
        "We calculate the incremental revenue and the iROAS in line with the [remerge whitepaper](https://drive.google.com/file/d/1PTJ93Cpjw1BeiVns8dTcs2zDDWmmjpdc/view). Afterwards we run a [chi squared test](https://en.wikipedia.org/wiki/Chi-squared_test) on the results to test for significance of the results, comparing conversion to per group uniques."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab_type": "code",
        "id": "V1vKf_u5y1Ag",
        "colab": {}
      },
      "source": [
        "def uplift(ad_spend, mark_df, revenue_df, index_name, m_hypothesis=1):\n",
        "    # join marks and revenue events\n",
        "    merged_df = merge(mark_df, revenue_df)\n",
        "    \n",
        "    # group marked users by their ab_test_group\n",
        "    grouped = mark_df.groupby(by='ab_test_group')\n",
        "    test_df = None\n",
        "    control_df = None\n",
        "    \n",
        "    # we might not have any marks for a certain group\n",
        "    if  'test' in grouped.groups:\n",
        "        test_df = grouped.get_group('test')\n",
        "    else:\n",
        "        print(\"WARNING: No users marked as test for \", index_name, 'skipping.. ')\n",
        "        return None\n",
        "\n",
        "    if  'control' in grouped.groups:\n",
        "        control_df = grouped.get_group('control')\n",
        "    else:\n",
        "        print(\"WARNING: No users marked as control for \", index_name, 'skipping.. ')\n",
        "        return None\n",
        "\n",
        "\n",
        "    grouped_revenue = merged_df.groupby(by='ab_test_group_y')\n",
        "\n",
        "    # init all KPIs with 0s first:\n",
        "    test_revenue_micros = 0\n",
        "    test_conversions = 0\n",
        "    test_converters = 0\n",
        "\n",
        "    control_revenue_micros = 0\n",
        "    control_conversions = 0\n",
        "    control_converters = 0\n",
        "\n",
        "    # we might not have any events for a certain group in the time-period,\n",
        "    if 'test' in grouped_revenue.groups:\n",
        "        test_revenue_df = grouped_revenue.get_group('test')\n",
        "        test_revenue_micros = test_revenue_df['revenue_eur'].sum()\n",
        "        test_conversions = test_revenue_df['partner_event'].count()\n",
        "        test_converters = test_revenue_df['user_id'].nunique()\n",
        "\n",
        "    if 'control' in grouped_revenue.groups:\n",
        "        control_revenue_df = grouped_revenue.get_group('control')\n",
        "        control_revenue_micros = control_revenue_df['revenue_eur'].sum()\n",
        "        control_conversions = control_revenue_df['partner_event'].count()\n",
        "        control_converters = control_revenue_df['user_id'].nunique()\n",
        "\n",
        "    # calculate KPIs\n",
        "    test_group_size = test_df['user_id'].nunique()\n",
        "    test_revenue = test_revenue_micros / 10 ** 6\n",
        "    control_group_size = control_df['user_id'].nunique()\n",
        "\n",
        "    control_revenue = control_revenue_micros / 10 ** 6\n",
        "\n",
        "    ratio = float(test_group_size) / float(control_group_size)\n",
        "    scaled_control_conversions = float(control_conversions) * ratio\n",
        "    scaled_control_revenue_micros = float(control_revenue_micros) * ratio\n",
        "    incremental_conversions = test_conversions - scaled_control_conversions\n",
        "    incremental_revenue_micros = test_revenue_micros - scaled_control_revenue_micros\n",
        "    incremental_revenue = incremental_revenue_micros / 10 ** 6\n",
        "    incremental_converters = test_converters - control_converters * ratio\n",
        "    \n",
        "    iroas = incremental_revenue / ad_spend\n",
        "    icpa = ad_spend / incremental_conversions\n",
        "    cost_per_incremental_converter = ad_spend / incremental_converters\n",
        "    \n",
        "    rev_per_conversion_test = 0\n",
        "    rev_per_conversion_control = 0\n",
        "    if test_conversions > 0:\n",
        "        rev_per_conversion_test = test_revenue / test_conversions\n",
        "    if control_conversions > 0:\n",
        "        rev_per_conversion_control = control_revenue / control_conversions\n",
        "\n",
        "    test_cvr = test_conversions / test_group_size\n",
        "    control_cvr = control_conversions / control_group_size\n",
        "\n",
        "    uplift = 0\n",
        "    if control_cvr > 0:\n",
        "        uplift = test_cvr / control_cvr - 1\n",
        "\n",
        "    control_successes, test_successes = control_conversions, test_conversions\n",
        "    if use_converters_for_significance or max(test_cvr, control_cvr) > 1.0:\n",
        "        control_successes, test_successes = control_converters, test_converters\n",
        "    chi_df = pd.DataFrame({\n",
        "        \"conversions\": [control_successes, test_successes],\n",
        "        \"total\": [control_group_size, test_group_size]\n",
        "    }, index=['control', 'test'])\n",
        "    # CHI square calculation will fail with insufficient data\n",
        "    # Fallback to no significance\n",
        "    try:\n",
        "        chi, p, *_ = scipy.stats.chi2_contingency(\n",
        "            pd.concat([chi_df.total - chi_df.conversions, chi_df.conversions], axis=1), correction=False)\n",
        "    except:\n",
        "        chi, p = 0, 1.0\n",
        "        \n",
        "    # bonferroni correction with equal weights - if we have multiple hypothesis:\n",
        "    # https://en.wikipedia.org/wiki/Bonferroni_correction\n",
        "    significant = p < 0.05/m_hypothesis\n",
        "\n",
        "    dataframe_dict = {\n",
        "        \"ad spend\": ad_spend,\n",
        "        \"total revenue\": test_revenue + control_revenue,\n",
        "        \"test group size\": test_group_size,\n",
        "        \"test conversions\": test_conversions,\n",
        "        \"test converters\": test_converters,\n",
        "        \"test revenue\": test_revenue,\n",
        "        \"control group size\": control_group_size,\n",
        "        \"control conversions\": control_conversions,\n",
        "        \"control_converters\": control_converters,\n",
        "        \"control revenue\": control_revenue,\n",
        "        \"ratio test/control\": ratio,\n",
        "        \"control conversions (scaled)\": scaled_control_conversions,\n",
        "        \"control revenue (scaled)\": scaled_control_revenue_micros / 10 ** 6,\n",
        "        \"incremental conversions\": incremental_conversions,\n",
        "        \"incremental converters\": incremental_converters,\n",
        "        \"incremental revenue\": incremental_revenue,\n",
        "        \"rev/conversions test\": rev_per_conversion_test,\n",
        "        \"rev/conversions control\": rev_per_conversion_control,\n",
        "        \"test CVR\": test_cvr,\n",
        "        \"control CVR\": control_cvr,\n",
        "        \"CVR Uplift\": uplift,\n",
        "        \"iROAS\": iroas,\n",
        "        \"cost per incr. converter\": cost_per_incremental_converter,\n",
        "        \"iCPA\": icpa,\n",
        "        \"chi^2\": chi,\n",
        "        \"p-value\": p,\n",
        "        \"significant\": significant\n",
        "    }\n",
        "\n",
        "    # show results as a dataframe\n",
        "    return pd.DataFrame(\n",
        "        dataframe_dict,\n",
        "        index=[index_name],\n",
        "    ).transpose()"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "tKdBRvkxL8Aa",
        "colab_type": "text"
      },
      "source": [
        "### Calculate and display uplift report for the data set as a whole\n",
        "\n",
        "This takes the whole data set and calculates uplift KPIs."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab_type": "code",
        "id": "2y91jjPVy1Ai",
        "colab": {}
      },
      "source": [
        "if use_deduplication:\n",
        "  attributions_df = drop_duplicates_in_attributions(attributions_df, pd.Timedelta('1 minute'))\n",
        "\n",
        "# calculate the total result:\n",
        "revenue_df = revenue(attributions_df)\n",
        "\n",
        "mark_df = marked(bids_df)\n",
        "\n",
        "results_df = uplift(ad_spend(bids_df), mark_df, revenue_df, \"total\")"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Lcw0S2p2MHwu",
        "colab_type": "text"
      },
      "source": [
        "### Calculate uplift report per group (if configured)\n",
        "\n",
        "Sometimes it makes sense to look at groups of similar campaigns. If the `groups`  dictionary contains group names as keys and a list of campaign ids as values per key, this function will compile a per group report. "
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "w-OaxnWLGXTZ",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "# if there are groups filter the events against the per campaign groups and generate report\n",
        "if len(groups) > 0:\n",
        "    per_group_df = None\n",
        "    for name, campaigns in groups.items():\n",
        "        group_marks_df = bids_df[bids_df.campaign_id.isin(campaigns)]\n",
        "        results_df[name] = uplift(ad_spend(group_marks_df), marked(group_marks_df), revenue_df, name, len(groups))"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "cbZZDsqcMPrI",
        "colab_type": "text"
      },
      "source": [
        "### Calculate uplift report per campaign\n",
        "\n",
        "Sometimes it makes sense to look at the uplift report per campaign. Each campaign usually reflects one segement of users. To do that we iterate over all campaigns in the current dataset."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "bOfbElnCEHhP",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "if per_campaign_results:\n",
        "    campaigns = bids_df['campaign_name'].unique()\n",
        "    for campaign in campaigns:\n",
        "        name = \"c_{0}\".format(campaign)\n",
        "        df = bids_df[bids_df.campaign_name == campaign]\n",
        "        results_df[name] = uplift(ad_spend(df), marked(df), revenue_df, name, len(campaigns))"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "SdFSmL3u8Pe4",
        "colab_type": "text"
      },
      "source": [
        "# Uplift Results\n",
        "\n",
        "You can configure the ouput by using variables in the 'Configuration' section"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "GWQXKYXB8YO2",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "# set formatting options\n",
        "pd.set_option('display.float_format', '{:.5f}'.format)\n",
        "\n",
        "results_df"
      ],
      "execution_count": 0,
      "outputs": []
    }
  ]
}