BigQuery Example for getting AB test data

AB_Test_Query.ipynb

{
  "cells": [
    {
      "metadata": {
        "trusted": true
      },
      "cell_type": "code",
      "source": "# Change the table name accordingly \nQUERY = \"\"\"\n    WITH\n      dataset AS(\n      SELECT\n        fullvisitorId AS clientId,\n        visitId AS sessionId,\n        test.experimentVariant AS variants,\n        device.deviceCategory AS device,\n        MAX (IF (h.eCommerceAction.action_type = '3',\n            1,\n            0)\n            ) AS add_to_cart,\n        IF (MAX(totals.transactions)>0,\n          1,\n          0) AS make_a_trans,\n        IF (MAX(totals.transactionRevenue)>0,\n          MAX(totals.transactionRevenue/1000000),\n          0) AS trans_rev\n      FROM\n        `xxxxxx_your_BigQuery_Table_xxxxxx`,\n        UNNEST (hits) AS h,\n        UNNEST(h.experiment) AS test\n      WHERE\n        _TABLE_SUFFIX BETWEEN @START_DATE\n        AND @END_DATE\n        AND test.experimentId = @EXPERIMENT_ID\n      GROUP BY\n        fullvisitorId,\n        visitId,\n        variants,\n        device)\n    SELECT\n      clientId,\n      variants,\n      device,\n      SUM(add_to_cart) AS num_atc,\n      COUNT(sessionId) AS num_sess,\n      SUM(make_a_trans) AS num_trans,\n      SUM(trans_rev) AS rev\n    FROM\n      dataset\n    GROUP BY\n      clientId,\n      variants,\n      device\n    \"\"\"",
      "execution_count": 1,
      "outputs": []
    }
  ],
  "metadata": {
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3",
      "language": "python"
    },
    "latex_envs": {
      "eqNumInitial": 1,
      "eqLabelWithNumbers": true,
      "current_citInitial": 1,
      "cite_by": "apalike",
      "bibliofile": "biblio.bib",
      "LaTeX_envs_menu_present": true,
      "labels_anchors": false,
      "latex_user_defs": false,
      "user_envs_cfg": false,
      "report_style_numbering": false,
      "autoclose": false,
      "autocomplete": true,
      "hotkeys": {
        "equation": "Ctrl-E",
        "itemize": "Ctrl-I"
      }
    },
    "language_info": {
      "name": "python",
      "version": "3.6.4",
      "mimetype": "text/x-python",
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "pygments_lexer": "ipython3",
      "nbconvert_exporter": "python",
      "file_extension": ".py"
    },
    "gist": {
      "id": "cb055ce6bbc87827c2a5f7d224f735e6",
      "data": {
        "description": "BigQuery Example for getting AB test data",
        "public": true
      }
    },
    "_draft": {
      "nbviewer_url": "https://gist.github.com/cb055ce6bbc87827c2a5f7d224f735e6"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 2
}