retail/pricing-simulations-around-product-classes/main.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pricing simulations around product classes\n",
    "\n",
    "For more information on the classification algorithm and a high-level overview of this notebook, [check out our article on atoti.io](https://www.atoti.io/data-dive-how-to-improve-pricing-strategies-with-a-set-of-checkout-receipts/).\n",
    "\n",
    "## Introduction\n",
    "\n",
    "In this notebook, we will showcase a simplified example of how a retailer can achieve a better price perception from its customers, while keeping or even slightly increasing its margins.\n",
    "\n",
    "## Classification algorithm\n",
    "\n",
    "A classification algorithm has already been run on three months of receipts to classify the products into three categories:\n",
    "- Driver products: Those are products that make people come to the store, their price is often compared to the competition. A typical example would be a drill. \n",
    "- Complementary products: Those are products that people do not intend to buy before coming to the store, they generate so called \"opportunity\" sales. Their price is most of the time not known. Examples are drill batteries or chewing gums near the checkout area.\n",
    "- Independent products: This third group of products contains products that are often sold alone. They tend to be large and more expensive and thus also have a tendency to be compared."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Importing atoti, having a first look at the data\n",
    "\n",
    "First we will start atoti, load the products referential and have a look at the classification algorithm result on our dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Welcome to atoti 0.4.1!\n",
      "\n",
      "By using this community edition, you agree with the license available at https://www.atoti.io/eula.\n",
      "Browse the official documentation at https://docs.atoti.io.\n",
      "Join the community at https://www.atoti.io/register.\n",
      "\n",
      "You can hide this message by setting the ATOTI_HIDE_EULA_MESSAGE environment variable to True.\n"
     ]
    }
   ],
   "source": [
    "import atoti\n",
    "from atoti.config import create_config\n",
    "\n",
    "# Tell atoti to load the database containing the UI dashboards\n",
    "config = create_config(metadata_db=\"./metadata.db\")\n",
    "\n",
    "session = atoti.create_session(config=config)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Loading the products referential with all the products information"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "The store has been sampled because there are more than 10000 lines in the files to load. Call Session.load_all_data() to trigger the full load of the data.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<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>Product Name</th>\n",
       "      <th>Brand</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ProductId</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Product 1</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Product 2</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Product 3</td>\n",
       "      <td>Brand 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Product 4</td>\n",
       "      <td>Brand 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Product 5</td>\n",
       "      <td>Brand 1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Product Name    Brand\n",
       "ProductId                      \n",
       "1            Product 1     None\n",
       "2            Product 2     None\n",
       "3            Product 3  Brand 1\n",
       "4            Product 4  Brand 1\n",
       "5            Product 5  Brand 1"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "products_store = session.read_csv(\n",
    "    \"s3://data.atoti.io/notebooks/products-classification/products_referential.csv\",\n",
    "    keys=[\"ProductId\"],\n",
    "    store_name=\"Products_store\",\n",
    "    sep=\";\",\n",
    ")\n",
    "products_store.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Then loading the result of the classification algorithm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>Class</th>\n",
       "      <th>edge_purchases</th>\n",
       "      <th>n_purchases</th>\n",
       "      <th>solo_purchases</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ProductId</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>69253</th>\n",
       "      <td>Complementary</td>\n",
       "      <td>2052</td>\n",
       "      <td>2628</td>\n",
       "      <td>576</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23395</th>\n",
       "      <td>Complementary</td>\n",
       "      <td>728</td>\n",
       "      <td>959</td>\n",
       "      <td>231</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53048</th>\n",
       "      <td>Driver</td>\n",
       "      <td>447</td>\n",
       "      <td>724</td>\n",
       "      <td>277</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94770</th>\n",
       "      <td>Complementary</td>\n",
       "      <td>4479</td>\n",
       "      <td>5464</td>\n",
       "      <td>985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105666</th>\n",
       "      <td>Driver</td>\n",
       "      <td>1238</td>\n",
       "      <td>1764</td>\n",
       "      <td>526</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Class  edge_purchases  n_purchases  solo_purchases\n",
       "ProductId                                                            \n",
       "69253      Complementary            2052         2628             576\n",
       "23395      Complementary             728          959             231\n",
       "53048             Driver             447          724             277\n",
       "94770      Complementary            4479         5464             985\n",
       "105666            Driver            1238         1764             526"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "classified_products_store = session.read_csv(\n",
    "    \"s3://data.atoti.io/notebooks/products-classification/classified_products.csv\",\n",
    "    keys=[\"ProductId\"],\n",
    "    store_name=\"classified_products\",\n",
    ")\n",
    "classified_products_store.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And join them together"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "classified_products_store.join(products_store, mapping={\"ProductId\": \"ProductId\"})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can then create a cube"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "cube = session.create_cube(classified_products_store, \"products_cube\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/svg+xml": [
       "<svg height=\"238px\" viewBox=\"0.00 0.00 499.00 238.00\" width=\"499px\" xmlns=\"http://www.w3.org/2000/svg\" xmlns:xlink=\"http://www.w3.org/1999/xlink\">\n",
       "<g class=\"graph\" id=\"graph0\" transform=\"scale(1.0 1.0) rotate(0) translate(4 234)\">\n",
       "<title>products_cube schema</title>\n",
       "<polygon fill=\"white\" points=\"-4,4 -4,-234 495,-234 495,4 -4,4\" stroke=\"transparent\"/>\n",
       "<!-- classified_products -->\n",
       "<g class=\"node\" id=\"node1\">\n",
       "<title>classified_products</title>\n",
       "<polygon fill=\"none\" points=\"8,-189 8,-226 146,-226 146,-189 8,-189\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-weight=\"bold\" text-anchor=\"start\" x=\"19\" y=\"-204.8\">classified_products</text>\n",
       "<polygon fill=\"none\" points=\"8,-152 8,-189 146,-189 146,-152 8,-152\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-weight=\"bold\" text-anchor=\"start\" x=\"19\" y=\"-167.8\">Key</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"44\" y=\"-167.8\">  ProductId </text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-style=\"italic\" text-anchor=\"start\" x=\"113\" y=\"-167.8\">int</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"129\" y=\"-167.8\"> </text>\n",
       "<polygon fill=\"none\" points=\"8,-115 8,-152 146,-152 146,-115 8,-115\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"19\" y=\"-130.8\">Class </text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-style=\"italic\" text-anchor=\"start\" x=\"53\" y=\"-130.8\">String</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"89\" y=\"-130.8\"> </text>\n",
       "<polygon fill=\"none\" points=\"8,-78 8,-115 146,-115 146,-78 8,-78\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"19\" y=\"-93.8\">edge_purchases </text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-style=\"italic\" text-anchor=\"start\" x=\"113\" y=\"-93.8\">int</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"129\" y=\"-93.8\"> </text>\n",
       "<polygon fill=\"none\" points=\"8,-41 8,-78 146,-78 146,-41 8,-41\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"19\" y=\"-56.8\">n_purchases </text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-style=\"italic\" text-anchor=\"start\" x=\"94\" y=\"-56.8\">int</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"110\" y=\"-56.8\"> </text>\n",
       "<polygon fill=\"none\" points=\"8,-4 8,-41 146,-41 146,-4 8,-4\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"19\" y=\"-19.8\">solo_purchases </text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-style=\"italic\" text-anchor=\"start\" x=\"110\" y=\"-19.8\">int</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"126\" y=\"-19.8\"> </text>\n",
       "</g>\n",
       "<!-- Products_store -->\n",
       "<g class=\"node\" id=\"node2\">\n",
       "<title>Products_store</title>\n",
       "<polygon fill=\"none\" points=\"334.5,-152 334.5,-189 483.5,-189 483.5,-152 334.5,-152\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-weight=\"bold\" text-anchor=\"start\" x=\"363\" y=\"-167.8\">Products_store</text>\n",
       "<polygon fill=\"none\" points=\"334.5,-115 334.5,-152 483.5,-152 483.5,-115 334.5,-115\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-weight=\"bold\" text-anchor=\"start\" x=\"345.5\" y=\"-130.8\">Key</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"370.5\" y=\"-130.8\">  ProductId </text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-style=\"italic\" text-anchor=\"start\" x=\"439.5\" y=\"-130.8\">int</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"455.5\" y=\"-130.8\"> </text>\n",
       "<polygon fill=\"none\" points=\"334.5,-78 334.5,-115 483.5,-115 483.5,-78 334.5,-78\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"345.5\" y=\"-93.8\">Product Name </text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-style=\"italic\" text-anchor=\"start\" x=\"432.5\" y=\"-93.8\">String</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"468.5\" y=\"-93.8\"> </text>\n",
       "<polygon fill=\"none\" points=\"334.5,-41 334.5,-78 483.5,-78 483.5,-41 334.5,-41\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"345.5\" y=\"-56.8\">Brand </text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-style=\"italic\" text-anchor=\"start\" x=\"385.5\" y=\"-56.8\">String</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"421.5\" y=\"-56.8\"> </text>\n",
       "</g>\n",
       "<!-- classified_products&#45;&gt;Products_store -->\n",
       "<g class=\"edge\" id=\"edge1\">\n",
       "<title>classified_products-&gt;Products_store</title>\n",
       "<path d=\"M154.01,-115C202.21,-115 264.85,-115 315.8,-115\" fill=\"none\" stroke=\"black\"/>\n",
       "<polygon fill=\"black\" points=\"315.82,-118.5 325.82,-115 315.82,-111.5 315.82,-118.5\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"middle\" x=\"240\" y=\"-118.8\">ProductId → ProductId</text>\n",
       "</g>\n",
       "</g>\n",
       "</svg>"
      ],
      "text/plain": [
       "<IPython.core.display.SVG object>"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cube.schema"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# We can now load all the data so that visualizations operate on the entire dataset.\n",
    "# NB: as a best practice, to optimize speed while exploring your data, we recommend keeping the default sampling mode enabled.\n",
    "#     Once the model is ready, as it is the case in this notebook, you may call session.load_all_data() after creating the cube.\n",
    "session.load_all_data()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And have a first look at the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "atoti": {
     "state": {
      "name": "Percentage of products per class pie chart",
      "type": "container",
      "value": {
       "body": {
        "configuration": {
         "mapping": {
          "horizontal": [],
          "splitBy": [
           "[Hierarchies].[Class].[Class]"
          ],
          "values": [
           "[Measures].[contributors.COUNT]"
          ],
          "vertical": [],
          "xAxis": []
         },
         "switchedTo": "plotly-donut-chart",
         "type": "plotly-line-chart"
        },
        "query": {
         "mdx": "SELECT NON EMPTY [Measures].[contributors.COUNT] ON COLUMNS, NON EMPTY [Hierarchies].[Class].[Class].Members ON ROWS FROM [products_cube] CELL PROPERTIES VALUE, FORMATTED_VALUE, BACK_COLOR, FORE_COLOR, FONT_FLAGS",
         "serverUrl": "",
         "updateMode": "once"
        }
       },
       "containerKey": "chart",
       "showTitleBar": false,
       "style": {}
      }
     }
    }
   },
   "outputs": [
    {
     "data": {
      "application/vnd.atoti.v0+json": {
       "cube": "products_cube",
       "name": "Percentage of products per class pie chart",
       "session": "Unnamed"
      },
      "text/plain": [
       "Install and enable the atoti JupyterLab extension to see this widget."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "cube.visualize(\"Percentage of products per class pie chart\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Computing the margin\n",
    "\n",
    "To perform that calculation we will load the purchase and selling prices tables and join them to the current data structure."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>PurchasePrice</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ProductId</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>76939.0</th>\n",
       "      <td>9.91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82520.0</th>\n",
       "      <td>1.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82521.0</th>\n",
       "      <td>1.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82522.0</th>\n",
       "      <td>1.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82523.0</th>\n",
       "      <td>1.80</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           PurchasePrice\n",
       "ProductId               \n",
       "76939.0             9.91\n",
       "82520.0             1.80\n",
       "82521.0             1.80\n",
       "82522.0             1.80\n",
       "82523.0             1.80"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "purchase_prices_store = session.read_csv(\n",
    "    \"s3://data.atoti.io/notebooks/products-classification/purchase_prices.csv\",\n",
    "    keys=[\"ProductId\"],\n",
    "    store_name=\"purchase_prices\",\n",
    ")\n",
    "purchase_prices_store.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>SellingPrice</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ProductId</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6707.0</th>\n",
       "      <td>4.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26332.0</th>\n",
       "      <td>26.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27200.0</th>\n",
       "      <td>7.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27480.0</th>\n",
       "      <td>178.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46379.0</th>\n",
       "      <td>260.15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           SellingPrice\n",
       "ProductId              \n",
       "6707.0             4.20\n",
       "26332.0           26.35\n",
       "27200.0            7.65\n",
       "27480.0          178.20\n",
       "46379.0          260.15"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "selling_prices_store = session.read_csv(\n",
    "    \"s3://data.atoti.io/notebooks/products-classification/selling_prices.csv\",\n",
    "    keys=[\"ProductId\"],\n",
    "    store_name=\"selling_prices\",\n",
    ")\n",
    "selling_prices_store.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "classified_products_store.join(\n",
    "    purchase_prices_store, mapping={\"ProductId\": \"ProductId\"}\n",
    ")\n",
    "classified_products_store.join(selling_prices_store, mapping={\"ProductId\": \"ProductId\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/svg+xml": [
       "<svg height=\"439px\" viewBox=\"0.00 0.00 500.00 438.50\" width=\"500px\" xmlns=\"http://www.w3.org/2000/svg\" xmlns:xlink=\"http://www.w3.org/1999/xlink\">\n",
       "<g class=\"graph\" id=\"graph0\" transform=\"scale(1.0 1.0) rotate(0) translate(4 434.5)\">\n",
       "<title>products_cube schema</title>\n",
       "<polygon fill=\"white\" points=\"-4,4 -4,-434.5 496,-434.5 496,4 -4,4\" stroke=\"transparent\"/>\n",
       "<!-- classified_products -->\n",
       "<g class=\"node\" id=\"node1\">\n",
       "<title>classified_products</title>\n",
       "<polygon fill=\"none\" points=\"8,-270.5 8,-307.5 146,-307.5 146,-270.5 8,-270.5\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-weight=\"bold\" text-anchor=\"start\" x=\"19\" y=\"-286.3\">classified_products</text>\n",
       "<polygon fill=\"none\" points=\"8,-233.5 8,-270.5 146,-270.5 146,-233.5 8,-233.5\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-weight=\"bold\" text-anchor=\"start\" x=\"19\" y=\"-249.3\">Key</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"44\" y=\"-249.3\">  ProductId </text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-style=\"italic\" text-anchor=\"start\" x=\"113\" y=\"-249.3\">int</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"129\" y=\"-249.3\"> </text>\n",
       "<polygon fill=\"none\" points=\"8,-196.5 8,-233.5 146,-233.5 146,-196.5 8,-196.5\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"19\" y=\"-212.3\">Class </text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-style=\"italic\" text-anchor=\"start\" x=\"53\" y=\"-212.3\">String</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"89\" y=\"-212.3\"> </text>\n",
       "<polygon fill=\"none\" points=\"8,-159.5 8,-196.5 146,-196.5 146,-159.5 8,-159.5\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"19\" y=\"-175.3\">edge_purchases </text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-style=\"italic\" text-anchor=\"start\" x=\"113\" y=\"-175.3\">int</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"129\" y=\"-175.3\"> </text>\n",
       "<polygon fill=\"none\" points=\"8,-122.5 8,-159.5 146,-159.5 146,-122.5 8,-122.5\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"19\" y=\"-138.3\">n_purchases </text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-style=\"italic\" text-anchor=\"start\" x=\"94\" y=\"-138.3\">int</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"110\" y=\"-138.3\"> </text>\n",
       "<polygon fill=\"none\" points=\"8,-85.5 8,-122.5 146,-122.5 146,-85.5 8,-85.5\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"19\" y=\"-101.3\">solo_purchases </text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-style=\"italic\" text-anchor=\"start\" x=\"110\" y=\"-101.3\">int</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"126\" y=\"-101.3\"> </text>\n",
       "</g>\n",
       "<!-- Products_store -->\n",
       "<g class=\"node\" id=\"node2\">\n",
       "<title>Products_store</title>\n",
       "<polygon fill=\"none\" points=\"335,-389.5 335,-426.5 484,-426.5 484,-389.5 335,-389.5\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-weight=\"bold\" text-anchor=\"start\" x=\"363.5\" y=\"-405.3\">Products_store</text>\n",
       "<polygon fill=\"none\" points=\"335,-352.5 335,-389.5 484,-389.5 484,-352.5 335,-352.5\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-weight=\"bold\" text-anchor=\"start\" x=\"346\" y=\"-368.3\">Key</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"371\" y=\"-368.3\">  ProductId </text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-style=\"italic\" text-anchor=\"start\" x=\"440\" y=\"-368.3\">int</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"456\" y=\"-368.3\"> </text>\n",
       "<polygon fill=\"none\" points=\"335,-315.5 335,-352.5 484,-352.5 484,-315.5 335,-315.5\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"346\" y=\"-331.3\">Product Name </text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-style=\"italic\" text-anchor=\"start\" x=\"433\" y=\"-331.3\">String</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"469\" y=\"-331.3\"> </text>\n",
       "<polygon fill=\"none\" points=\"335,-278.5 335,-315.5 484,-315.5 484,-278.5 335,-278.5\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"346\" y=\"-294.3\">Brand </text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-style=\"italic\" text-anchor=\"start\" x=\"386\" y=\"-294.3\">String</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"422\" y=\"-294.3\"> </text>\n",
       "</g>\n",
       "<!-- classified_products&#45;&gt;Products_store -->\n",
       "<g class=\"edge\" id=\"edge3\">\n",
       "<title>classified_products-&gt;Products_store</title>\n",
       "<path d=\"M154.12,-232.49C202.78,-255.49 266.15,-285.44 317.4,-309.67\" fill=\"none\" stroke=\"black\"/>\n",
       "<polygon fill=\"black\" points=\"315.96,-312.86 326.49,-313.97 318.95,-306.53 315.96,-312.86\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"middle\" x=\"240\" y=\"-305.3\">ProductId → ProductId</text>\n",
       "</g>\n",
       "<!-- purchase_prices -->\n",
       "<g class=\"node\" id=\"node3\">\n",
       "<title>purchase_prices</title>\n",
       "<polygon fill=\"none\" points=\"334,-214.5 334,-251.5 484,-251.5 484,-214.5 334,-214.5\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-weight=\"bold\" text-anchor=\"start\" x=\"359.5\" y=\"-230.3\">purchase_prices</text>\n",
       "<polygon fill=\"none\" points=\"334,-177.5 334,-214.5 484,-214.5 484,-177.5 334,-177.5\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-weight=\"bold\" text-anchor=\"start\" x=\"345\" y=\"-193.3\">Key</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"370\" y=\"-193.3\">  ProductId </text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-style=\"italic\" text-anchor=\"start\" x=\"439\" y=\"-193.3\">int</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"455\" y=\"-193.3\"> </text>\n",
       "<polygon fill=\"none\" points=\"334,-140.5 334,-177.5 484,-177.5 484,-140.5 334,-140.5\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"345\" y=\"-156.3\">PurchasePrice </text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-style=\"italic\" text-anchor=\"start\" x=\"429\" y=\"-156.3\">double</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"469\" y=\"-156.3\"> </text>\n",
       "</g>\n",
       "<!-- classified_products&#45;&gt;purchase_prices -->\n",
       "<g class=\"edge\" id=\"edge2\">\n",
       "<title>classified_products-&gt;purchase_prices</title>\n",
       "<path d=\"M154.12,-196.5C202.3,-196.5 264.9,-196.5 315.88,-196.5\" fill=\"none\" stroke=\"black\"/>\n",
       "<polygon fill=\"black\" points=\"315.91,-200 325.91,-196.5 315.91,-193 315.91,-200\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"middle\" x=\"240\" y=\"-200.3\">ProductId → ProductId</text>\n",
       "</g>\n",
       "<!-- selling_prices -->\n",
       "<g class=\"node\" id=\"node4\">\n",
       "<title>selling_prices</title>\n",
       "<polygon fill=\"none\" points=\"340,-77.5 340,-114.5 479,-114.5 479,-77.5 340,-77.5\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-weight=\"bold\" text-anchor=\"start\" x=\"368.5\" y=\"-93.3\">selling_prices</text>\n",
       "<polygon fill=\"none\" points=\"340,-40.5 340,-77.5 479,-77.5 479,-40.5 340,-40.5\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-weight=\"bold\" text-anchor=\"start\" x=\"351\" y=\"-56.3\">Key</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"376\" y=\"-56.3\">  ProductId </text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-style=\"italic\" text-anchor=\"start\" x=\"445\" y=\"-56.3\">int</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"461\" y=\"-56.3\"> </text>\n",
       "<polygon fill=\"none\" points=\"340,-3.5 340,-40.5 479,-40.5 479,-3.5 340,-3.5\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"351\" y=\"-19.3\">SellingPrice </text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-style=\"italic\" text-anchor=\"start\" x=\"424\" y=\"-19.3\">double</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"464\" y=\"-19.3\"> </text>\n",
       "</g>\n",
       "<!-- classified_products&#45;&gt;selling_prices -->\n",
       "<g class=\"edge\" id=\"edge1\">\n",
       "<title>classified_products-&gt;selling_prices</title>\n",
       "<path d=\"M154.12,-164.9C204.21,-144.1 269.86,-116.85 321.86,-95.26\" fill=\"none\" stroke=\"black\"/>\n",
       "<polygon fill=\"black\" points=\"323.51,-98.37 331.4,-91.3 320.83,-91.9 323.51,-98.37\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"middle\" x=\"240\" y=\"-159.3\">ProductId → ProductId</text>\n",
       "</g>\n",
       "</g>\n",
       "</svg>"
      ],
      "text/plain": [
       "<IPython.core.display.SVG object>"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cube.schema"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's now add the margin measure"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "m = cube.measures\n",
    "h = cube.hierarchies\n",
    "lvl = cube.levels"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Because selling and purchase prices are different per products, we have to compute the margin at ProductId level using parameter \"scope\", and then aggregate it as a sum above this level:\n",
    "\n",
    "$\\sum_{p \\in Products}{(Selling Price - Purchase Price) \\times Quantity Sold}$"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "m[\"Margin.SUM\"] = atoti.agg.sum(\n",
    "    (m[\"SellingPrice.VALUE\"] - m[\"PurchasePrice.VALUE\"])\n",
    "    * classified_products_store[\"n_purchases\"],\n",
    "    scope=atoti.scope.origin(lvl[\"ProductId\"]),\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "atoti": {
     "state": {
      "name": "Margin per product class",
      "type": "container",
      "value": {
       "body": {
        "configuration": {
         "mapping": {
          "horizontal": [],
          "splitBy": [],
          "values": [
           "[Measures].[Margin.SUM]"
          ],
          "vertical": [],
          "xAxis": [
           "[Hierarchies].[Class].[Class]"
          ]
         },
         "switchedTo": "plotly-clustered-column-chart",
         "type": "plotly-line-chart"
        },
        "query": {
         "mdx": "SELECT NON EMPTY [Hierarchies].[Class].[Class].Members ON ROWS, NON EMPTY [Measures].[Margin.SUM] ON COLUMNS FROM [products_cube] CELL PROPERTIES VALUE, FORMATTED_VALUE, BACK_COLOR, FORE_COLOR, FONT_FLAGS",
         "serverUrl": "",
         "updateMode": "once"
        }
       },
       "containerKey": "chart",
       "showTitleBar": false,
       "style": {}
      }
     }
    }
   },
   "outputs": [
    {
     "data": {
      "application/vnd.atoti.v0+json": {
       "cube": "products_cube",
       "name": "Margin per product class",
       "session": "Unnamed"
      },
      "text/plain": [
       "Install and enable the atoti JupyterLab extension to see this widget."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "cube.visualize(\"Margin per product class\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "atoti visualizations are available both in the notebook, but also from the dedicated application.  \n",
    "Run the two cells below to get a link to a summary dashboard of all what was done so far."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "m[\"SellingPrice.MEAN\"] = atoti.agg.mean(\n",
    "    m[\"SellingPrice.VALUE\"], scope=atoti.scope.origin(lvl[\"ProductId\"])\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'http://localhost:36413/#/dashboard/326'"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "session.url + \"/#/dashboard/326\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you wish to play with the UI and explore the data by yourself, [you can have a look at our UI documentation here](https://www.activeviam.com/activeui/documentation/index.html).\n",
    "\n",
    "## Custom hierarchy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "h[\"Products\"] = {\n",
    "    \"Product Brand\": lvl[\"Brand\"],\n",
    "    \"Product Label\": lvl[\"Product Name\"],\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "atoti": {
     "state": {
      "name": "",
      "type": "container",
      "value": {
       "body": {
        "configuration": {
         "tabular": {
          "addButtonFilter": "numeric",
          "columns": [
           {
            "key": "c-treeCells-member",
            "width": 194
           },
           {
            "key": "[Measures].[SellingPrice.MEAN]",
            "width": 143
           }
          ],
          "columnsGroups": [
           {
            "captionProducer": "firstColumn",
            "cellFactory": "kpi-status",
            "selector": "kpi-status"
           },
           {
            "captionProducer": "firstColumn",
            "cellFactory": "lookup",
            "selector": "lookup"
           },
           {
            "captionProducer": "expiry",
            "cellFactory": "expiry",
            "selector": "kpi-expiry"
           },
           {
            "captionProducer": "columnMerge",
            "cellFactory": {
             "args": {},
             "key": "treeCells"
            },
            "selector": "member"
           }
          ],
          "defaultOptions": {},
          "expansion": {
           "automaticExpansion": true
          },
          "hideAddButton": true,
          "pinnedHeaderSelector": "member",
          "sortingMode": "non-breaking",
          "statisticsShown": true
         }
        },
        "contextValues": {},
        "mdx": "SELECT NON EMPTY {[Measures].[SellingPrice.MEAN], [Measures].[Margin.SUM]} ON COLUMNS, NON EMPTY Hierarchize(Union(DrilldownLevel([Hierarchies].[Products].[ALL].[AllMember]), Descendants({[Hierarchies].[Products].[ALL].[AllMember].[Brand 10]}, [Hierarchies].[Products].[Product Label]))) ON ROWS FROM [products_cube] CELL PROPERTIES VALUE, FORMATTED_VALUE, BACK_COLOR, FORE_COLOR, FONT_FLAGS",
        "ranges": {
         "column": {
          "chunkSize": 50,
          "thresholdPercentage": 0.2
         },
         "row": {
          "chunkSize": 2000,
          "thresholdPercentage": 0.1
         }
        },
        "serverUrl": "",
        "updateMode": "once"
       },
       "containerKey": "pivot-table",
       "showTitleBar": false,
       "style": {}
      }
     }
    }
   },
   "outputs": [
    {
     "data": {
      "application/vnd.atoti.v0+json": {
       "cube": "products_cube",
       "name": null,
       "session": "Unnamed"
      },
      "text/plain": [
       "Install and enable the atoti JupyterLab extension to see this widget."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "cube.visualize()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Computing the price index\n",
    "\n",
    "There are different existing formulas for the price index but the idea behind is always the same: the price index is a measurement of where a retailer is positioned compared to one or multiple of its competitors.\n",
    "\n",
    "The formula we will use in this example compares a product price to the average price found among the competitors, measuring at which percentage of this average competitors price the product is.  \n",
    "\n",
    "We will weight the price index indicator by the margin when aggregating above product level so that we later optimize the price index for products that contribute the most to our margin.  \n",
    "Other commonly used formulas weight by sales quantity or revenue."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Price Index Formula"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# $100 \\times \\frac{\\sum_{p \\in Products}\\frac{Selling Price(p)}{Average Competitor Price(p)} \\times Margin(p)}{\\sum_{p \\in Products}Margin(p)}$"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Loading Competitor prices data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "competitor_prices_store = session.read_csv(\n",
    "    \"s3://data.atoti.io/notebooks/products-classification/competitor_prices.csv\",\n",
    "    keys=[\"ProductId\", \"CompetitorName\"],\n",
    "    store_name=\"Competitor_prices_store\",\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "classified_products_store.join(\n",
    "    competitor_prices_store, mapping={\"ProductId\": \"ProductId\"}\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/svg+xml": [
       "<svg height=\"613px\" viewBox=\"0.00 0.00 548.00 613.00\" width=\"548px\" xmlns=\"http://www.w3.org/2000/svg\" xmlns:xlink=\"http://www.w3.org/1999/xlink\">\n",
       "<g class=\"graph\" id=\"graph0\" transform=\"scale(1.0 1.0) rotate(0) translate(4 609)\">\n",
       "<title>products_cube schema</title>\n",
       "<polygon fill=\"white\" points=\"-4,4 -4,-609 544,-609 544,4 -4,4\" stroke=\"transparent\"/>\n",
       "<!-- classified_products -->\n",
       "<g class=\"node\" id=\"node1\">\n",
       "<title>classified_products</title>\n",
       "<polygon fill=\"none\" points=\"8,-376 8,-413 146,-413 146,-376 8,-376\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-weight=\"bold\" text-anchor=\"start\" x=\"19\" y=\"-391.8\">classified_products</text>\n",
       "<polygon fill=\"none\" points=\"8,-339 8,-376 146,-376 146,-339 8,-339\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-weight=\"bold\" text-anchor=\"start\" x=\"19\" y=\"-354.8\">Key</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"44\" y=\"-354.8\">  ProductId </text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-style=\"italic\" text-anchor=\"start\" x=\"113\" y=\"-354.8\">int</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"129\" y=\"-354.8\"> </text>\n",
       "<polygon fill=\"none\" points=\"8,-302 8,-339 146,-339 146,-302 8,-302\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"19\" y=\"-317.8\">Class </text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-style=\"italic\" text-anchor=\"start\" x=\"53\" y=\"-317.8\">String</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"89\" y=\"-317.8\"> </text>\n",
       "<polygon fill=\"none\" points=\"8,-265 8,-302 146,-302 146,-265 8,-265\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"19\" y=\"-280.8\">edge_purchases </text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-style=\"italic\" text-anchor=\"start\" x=\"113\" y=\"-280.8\">int</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"129\" y=\"-280.8\"> </text>\n",
       "<polygon fill=\"none\" points=\"8,-228 8,-265 146,-265 146,-228 8,-228\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"19\" y=\"-243.8\">n_purchases </text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-style=\"italic\" text-anchor=\"start\" x=\"94\" y=\"-243.8\">int</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"110\" y=\"-243.8\"> </text>\n",
       "<polygon fill=\"none\" points=\"8,-191 8,-228 146,-228 146,-191 8,-191\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"19\" y=\"-206.8\">solo_purchases </text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-style=\"italic\" text-anchor=\"start\" x=\"110\" y=\"-206.8\">int</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"126\" y=\"-206.8\"> </text>\n",
       "</g>\n",
       "<!-- Products_store -->\n",
       "<g class=\"node\" id=\"node2\">\n",
       "<title>Products_store</title>\n",
       "<polygon fill=\"none\" points=\"359,-564 359,-601 508,-601 508,-564 359,-564\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-weight=\"bold\" text-anchor=\"start\" x=\"387.5\" y=\"-579.8\">Products_store</text>\n",
       "<polygon fill=\"none\" points=\"359,-527 359,-564 508,-564 508,-527 359,-527\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-weight=\"bold\" text-anchor=\"start\" x=\"370\" y=\"-542.8\">Key</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"395\" y=\"-542.8\">  ProductId </text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-style=\"italic\" text-anchor=\"start\" x=\"464\" y=\"-542.8\">int</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"480\" y=\"-542.8\"> </text>\n",
       "<polygon fill=\"none\" points=\"359,-490 359,-527 508,-527 508,-490 359,-490\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"370\" y=\"-505.8\">Product Name </text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-style=\"italic\" text-anchor=\"start\" x=\"457\" y=\"-505.8\">String</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"493\" y=\"-505.8\"> </text>\n",
       "<polygon fill=\"none\" points=\"359,-453 359,-490 508,-490 508,-453 359,-453\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"370\" y=\"-468.8\">Brand </text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-style=\"italic\" text-anchor=\"start\" x=\"410\" y=\"-468.8\">String</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"446\" y=\"-468.8\"> </text>\n",
       "</g>\n",
       "<!-- classified_products&#45;&gt;Products_store -->\n",
       "<g class=\"edge\" id=\"edge4\">\n",
       "<title>classified_products-&gt;Products_store</title>\n",
       "<path d=\"M154.04,-353.28C160.1,-357.27 166.14,-361.21 172,-365 228.05,-401.23 291.63,-440.87 341.88,-471.87\" fill=\"none\" stroke=\"black\"/>\n",
       "<polygon fill=\"black\" points=\"340.11,-474.89 350.46,-477.16 343.79,-468.93 340.11,-474.89\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"middle\" x=\"240\" y=\"-452.8\">ProductId → ProductId</text>\n",
       "</g>\n",
       "<!-- purchase_prices -->\n",
       "<g class=\"node\" id=\"node3\">\n",
       "<title>purchase_prices</title>\n",
       "<polygon fill=\"none\" points=\"358,-389 358,-426 508,-426 508,-389 358,-389\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-weight=\"bold\" text-anchor=\"start\" x=\"383.5\" y=\"-404.8\">purchase_prices</text>\n",
       "<polygon fill=\"none\" points=\"358,-352 358,-389 508,-389 508,-352 358,-352\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-weight=\"bold\" text-anchor=\"start\" x=\"369\" y=\"-367.8\">Key</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"394\" y=\"-367.8\">  ProductId </text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-style=\"italic\" text-anchor=\"start\" x=\"463\" y=\"-367.8\">int</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"479\" y=\"-367.8\"> </text>\n",
       "<polygon fill=\"none\" points=\"358,-315 358,-352 508,-352 508,-315 358,-315\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"369\" y=\"-330.8\">PurchasePrice </text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-style=\"italic\" text-anchor=\"start\" x=\"453\" y=\"-330.8\">double</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"493\" y=\"-330.8\"> </text>\n",
       "</g>\n",
       "<!-- classified_products&#45;&gt;purchase_prices -->\n",
       "<g class=\"edge\" id=\"edge3\">\n",
       "<title>classified_products-&gt;purchase_prices</title>\n",
       "<path d=\"M154.38,-316.89C208.85,-327.5 282.36,-341.83 340.05,-353.08\" fill=\"none\" stroke=\"black\"/>\n",
       "<polygon fill=\"black\" points=\"339.42,-356.52 349.9,-355 340.75,-349.65 339.42,-356.52\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"middle\" x=\"240\" y=\"-349.8\">ProductId → ProductId</text>\n",
       "</g>\n",
       "<!-- selling_prices -->\n",
       "<g class=\"node\" id=\"node4\">\n",
       "<title>selling_prices</title>\n",
       "<polygon fill=\"none\" points=\"364,-252 364,-289 503,-289 503,-252 364,-252\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-weight=\"bold\" text-anchor=\"start\" x=\"392.5\" y=\"-267.8\">selling_prices</text>\n",
       "<polygon fill=\"none\" points=\"364,-215 364,-252 503,-252 503,-215 364,-215\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-weight=\"bold\" text-anchor=\"start\" x=\"375\" y=\"-230.8\">Key</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"400\" y=\"-230.8\">  ProductId </text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-style=\"italic\" text-anchor=\"start\" x=\"469\" y=\"-230.8\">int</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"485\" y=\"-230.8\"> </text>\n",
       "<polygon fill=\"none\" points=\"364,-178 364,-215 503,-215 503,-178 364,-178\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"375\" y=\"-193.8\">SellingPrice </text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-style=\"italic\" text-anchor=\"start\" x=\"448\" y=\"-193.8\">double</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"488\" y=\"-193.8\"> </text>\n",
       "</g>\n",
       "<!-- classified_products&#45;&gt;selling_prices -->\n",
       "<g class=\"edge\" id=\"edge2\">\n",
       "<title>classified_products-&gt;selling_prices</title>\n",
       "<path d=\"M154.38,-287.33C210.58,-276.53 287.05,-261.84 345.5,-250.62\" fill=\"none\" stroke=\"black\"/>\n",
       "<polygon fill=\"black\" points=\"346.29,-254.03 355.46,-248.7 344.97,-247.15 346.29,-254.03\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"middle\" x=\"240\" y=\"-286.8\">ProductId → ProductId</text>\n",
       "</g>\n",
       "<!-- Competitor_prices_store -->\n",
       "<g class=\"node\" id=\"node5\">\n",
       "<title>Competitor_prices_store</title>\n",
       "<polygon fill=\"none\" points=\"334,-115 334,-152 532,-152 532,-115 334,-115\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-weight=\"bold\" text-anchor=\"start\" x=\"357.5\" y=\"-130.8\">Competitor_prices_store</text>\n",
       "<polygon fill=\"none\" points=\"334,-78 334,-115 532,-115 532,-78 334,-78\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-weight=\"bold\" text-anchor=\"start\" x=\"345\" y=\"-93.8\">Key</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"370\" y=\"-93.8\">  ProductId </text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-style=\"italic\" text-anchor=\"start\" x=\"439\" y=\"-93.8\">int</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"455\" y=\"-93.8\"> </text>\n",
       "<polygon fill=\"none\" points=\"334,-41 334,-78 532,-78 532,-41 334,-41\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-weight=\"bold\" text-anchor=\"start\" x=\"345\" y=\"-56.8\">Key</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"370\" y=\"-56.8\">  CompetitorName </text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-style=\"italic\" text-anchor=\"start\" x=\"481\" y=\"-56.8\">String</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"517\" y=\"-56.8\"> </text>\n",
       "<polygon fill=\"none\" points=\"334,-4 334,-41 532,-41 532,-4 334,-4\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"345\" y=\"-19.8\">CompetitorPrice </text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" font-style=\"italic\" text-anchor=\"start\" x=\"443\" y=\"-19.8\">double</text>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"start\" x=\"483\" y=\"-19.8\"> </text>\n",
       "</g>\n",
       "<!-- classified_products&#45;&gt;Competitor_prices_store -->\n",
       "<g class=\"edge\" id=\"edge1\">\n",
       "<title>classified_products-&gt;Competitor_prices_store</title>\n",
       "<path d=\"M154.24,-249.66C160.23,-245.68 166.2,-241.76 172,-238 219.12,-207.48 271.76,-174.83 317.28,-147.03\" fill=\"none\" stroke=\"black\" stroke-dasharray=\"5,2\"/>\n",
       "<polygon fill=\"black\" points=\"319.28,-149.91 325.99,-141.71 315.63,-143.93 319.28,-149.91\" stroke=\"black\"/>\n",
       "<text font-family=\"Times,serif\" font-size=\"14.00\" text-anchor=\"middle\" x=\"240\" y=\"-241.8\">ProductId → ProductId</text>\n",
       "</g>\n",
       "</g>\n",
       "</svg>"
      ],
      "text/plain": [
       "<IPython.core.display.SVG object>"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cube.schema"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Creating the price index measure"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The \"scope\" parameters enables you to have a different aggregation strategy below the \"on\" level.  \n",
    "In the following cell, everything above Product (ProductId) level is aggregated as a sum, while below the product level we find the previous formula, that does an average on the competitor prices just for one particular product."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "m[\"CompetitorPrice.MEAN\"] = atoti.agg.mean(m[\"CompetitorPrice.VALUE\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "price_index_numerator = atoti.agg.sum(\n",
    "    (m[\"SellingPrice.MEAN\"] * m[\"Margin.SUM\"]) / m[\"CompetitorPrice.MEAN\"],\n",
    "    scope=atoti.scope.origin(lvl[\"ProductId\"]),\n",
    ")\n",
    "\n",
    "# Some products have no competitor price or selling price.\n",
    "# We exclude them from the denominator for consistency with the numerator\n",
    "price_index_denominator = atoti.agg.sum(\n",
    "    atoti.where(\n",
    "        (m[\"CompetitorPrice.MEAN\"] != None) & (m[\"SellingPrice.MEAN\"] != None),\n",
    "        m[\"Margin.SUM\"],\n",
    "    ),\n",
    "    scope=atoti.scope.origin(lvl[\"ProductId\"]),\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "m[\"Price Index\"] = 100 * price_index_numerator / price_index_denominator"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The price index measure is now created accordingly to the formula.\n",
    "\n",
    "We can see below that our driver products are in fact a bit more expensive than the average market price while we are quite competitive on the complementary products, which is the opposite of our goal."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "atoti": {
     "state": {
      "name": "Price index per competitor",
      "type": "container",
      "value": {
       "body": {
        "configuration": {
         "tabular": {
          "addButtonFilter": "numeric",
          "columns": [
           {
            "key": "c-treeCells-member",
            "width": 151
           }
          ],
          "columnsGroups": [
           {
            "captionProducer": "firstColumn",
            "cellFactory": "kpi-status",
            "selector": "kpi-status"
           },
           {
            "captionProducer": "firstColumn",
            "cellFactory": "lookup",
            "selector": "lookup"
           },
           {
            "captionProducer": "expiry",
            "cellFactory": "expiry",
            "selector": "kpi-expiry"
           },
           {
            "captionProducer": "columnMerge",
            "cellFactory": {
             "args": {},
             "key": "treeCells"
            },
            "selector": "member"
           }
          ],
          "defaultOptions": {},
          "expansion": {
           "automaticExpansion": true
          },
          "hideAddButton": true,
          "pinnedHeaderSelector": "member",
          "sortingMode": "non-breaking",
          "statisticsShown": true
         }
        },
        "contextValues": {},
        "mdx": "SELECT NON EMPTY Order(DrilldownLevel([Hierarchies].[Class].[ALL].[AllMember]), ([Measures].[Price Index], [Hierarchies].[CompetitorName].[ALL].[AllMember]), DESC) ON ROWS, NON EMPTY Crossjoin({[Measures].[Price Index]}, Hierarchize(DrilldownLevel([Hierarchies].[CompetitorName].[ALL].[AllMember]))) ON COLUMNS FROM [products_cube] CELL PROPERTIES VALUE, FORMATTED_VALUE, BACK_COLOR, FORE_COLOR, FONT_FLAGS",
        "ranges": {
         "column": {
          "chunkSize": 50,
          "thresholdPercentage": 0.2
         },
         "row": {
          "chunkSize": 2000,
          "thresholdPercentage": 0.1
         }
        },
        "serverUrl": "",
        "updateMode": "once"
       },
       "containerKey": "pivot-table",
       "showTitleBar": false,
       "style": {}
      }
     }
    }
   },
   "outputs": [
    {
     "data": {
      "application/vnd.atoti.v0+json": {
       "cube": "products_cube",
       "name": "Price index per competitor",
       "session": "Unnamed"
      },
      "text/plain": [
       "Install and enable the atoti JupyterLab extension to see this widget."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "cube.visualize(\"Price index per competitor\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Simulate Price Strategies"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Complementary products are usually less expensive than those of the other two categories and people tend to ignore their price. This means that an increase in price does not necessarily mean a drop in sales.  \n",
    "In a first simulation we will try to assess how much margin we can make if we increase their price by 10%."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To do this we create a simulation against Class and ProductId levels and specify that we need to multiply by 1.1 each selling price. All KPIs defined previously such as the price index will be re-computed on the fly which will enable us to compare scenarios and their benefits."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "price_strategies = cube.setup_simulation(\n",
    "    \"Price Strategies\",\n",
    "    levels=[lvl[\"Class\"], lvl[\"ProductId\"]],\n",
    "    multiply=[m[\"SellingPrice.VALUE\"]],\n",
    ")\n",
    "\n",
    "complementary_increase = price_strategies.scenarios[\"10% complementary increase\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "complementary_increase += (\"Complementary\", None, 1.1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see below that this represents a gain of 12 million per year, while increasing the index dramatically from 98.52 to 108.37"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "atoti": {
     "state": {
      "name": "Comparison of the two scenarios per class",
      "type": "container",
      "value": {
       "body": {
        "configuration": {
         "tabular": {
          "addButtonFilter": "numeric",
          "columns": [
           {
            "key": "c-treeCells-member",
            "width": 132
           }
          ],
          "columnsGroups": [
           {
            "captionProducer": "firstColumn",
            "cellFactory": "kpi-status",
            "selector": "kpi-status"
           },
           {
            "captionProducer": "firstColumn",
            "cellFactory": "lookup",
            "selector": "lookup"
           },
           {
            "captionProducer": "expiry",
            "cellFactory": "expiry",
            "selector": "kpi-expiry"
           },
           {
            "captionProducer": "columnMerge",
            "cellFactory": {
             "args": {},
             "key": "treeCells"
            },
            "selector": "member"
           }
          ],
          "defaultOptions": {},
          "expansion": {
           "automaticExpansion": true
          },
          "hideAddButton": true,
          "pinnedHeaderSelector": "member",
          "sortingMode": "non-breaking",
          "statisticsShown": true
         }
        },
        "contextValues": {},
        "mdx": "WITH  Member [Measures].[Margin Delta] AS [Measures].[Margin.SUM] - ([Measures].[Margin.SUM], [Measure Simulations].[Price Strategies].[Price Strategies].[Base]), FORE_COLOR = CASE WHEN [Measures].[Margin Delta] >= 0 THEN rgb(44, 160, 44) WHEN [Measures].[Margin Delta] < 0 THEN rgb(224, 102, 102) END, FONT_FLAGS = CASE WHEN [Measures].[Margin Delta] >= 0 THEN 1 WHEN [Measures].[Margin Delta] < 0 THEN 1 END, FORMAT_STRING = \"#,###.##\"    Member [Measures].[Price Index Delta] AS [Measures].[Price Index] - ([Measures].[Price Index], [Measure Simulations].[Price Strategies].[Price Strategies].[Base]), FORE_COLOR = CASE WHEN [Measures].[Price Index Delta] <= 0 THEN rgb(44, 160, 44) WHEN [Measures].[Price Index Delta] > 0 THEN rgb(224, 102, 102) END, FONT_FLAGS = CASE WHEN [Measures].[Price Index Delta] <= 0 THEN 1 WHEN [Measures].[Price Index Delta] > 0 THEN 1 END, FORMAT_STRING = \"#,###.##\"  SELECT NON EMPTY Hierarchize(DrilldownLevel([Hierarchies].[Class].[ALL].[AllMember])) ON ROWS, NON EMPTY Crossjoin({[Measures].[Price Index], [Measures].[Margin.SUM], [Measures].[Margin Delta], [Measures].[Price Index Delta]}, [Measure Simulations].[Price Strategies].[Price Strategies].Members) ON COLUMNS FROM [products_cube] CELL PROPERTIES BACK_COLOR, FONT_FLAGS, FORE_COLOR, FORMATTED_VALUE, VALUE",
        "ranges": {
         "column": {
          "chunkSize": 50,
          "thresholdPercentage": 0.2
         },
         "row": {
          "chunkSize": 2000,
          "thresholdPercentage": 0.1
         }
        },
        "serverUrl": "",
        "updateMode": "once"
       },
       "containerKey": "pivot-table",
       "showTitleBar": false,
       "style": {}
      }
     }
    }
   },
   "outputs": [
    {
     "data": {
      "application/vnd.atoti.v0+json": {
       "cube": "products_cube",
       "name": "Comparison of the two scenarios per class",
       "session": "Unnamed"
      },
      "text/plain": [
       "Install and enable the atoti JupyterLab extension to see this widget."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "cube.visualize(\"Comparison of the two scenarios per class\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As seen before a better strategy is to improve the price image by being more competitive on the driver and independent products, hence lowering the prices of those products, while compensating the losses in margin by increasing the prices of the complementary products.\n",
    "\n",
    "We will first import our pricing methods: in this example a very simple pricing functions from our `pricer.py` file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "from price_engine import pricer"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Then load the initial price list:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "selling_prices = pd.read_csv(\n",
    "    \"http://data.atoti.io/notebooks/products-classification/selling_prices.csv\", sep=\";\"\n",
    ")\n",
    "classified_products = pd.read_csv(\n",
    "    \"http://data.atoti.io/notebooks/products-classification/classified_products.csv\",\n",
    "    sep=\";\",\n",
    ")\n",
    "initial_price_list_with_classes = selling_prices.merge(classified_products)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will then give our initial price list to a price optimizer that will optimize prices and load the resulting price list into a third scenario named \"Optimized price index\" that we will be able to compare to the other scenarios."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "selling_prices_for_optimized_price_index = pricer.optimize_price_index(\n",
    "    initial_price_list_with_classes\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "selling_prices_store.scenarios[\"Optimized price index\"].load_pandas(\n",
    "    selling_prices_for_optimized_price_index\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The result as we can see below is much better since we have been able to very slightly increase our global margin, while being much more competitive on the Driver products"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "atoti": {
     "state": {
      "name": "Results summary",
      "type": "container",
      "value": {
       "body": {
        "configuration": {
         "tabular": {
          "addButtonFilter": "numeric",
          "columnsGroups": [
           {
            "captionProducer": "firstColumn",
            "cellFactory": "kpi-status",
            "selector": "kpi-status"
           },
           {
            "captionProducer": "firstColumn",
            "cellFactory": "lookup",
            "selector": "lookup"
           },
           {
            "captionProducer": "expiry",
            "cellFactory": "expiry",
            "selector": "kpi-expiry"
           },
           {
            "captionProducer": "columnMerge",
            "cellFactory": {
             "args": {},
             "key": "treeCells"
            },
            "selector": "member"
           }
          ],
          "defaultOptions": {},
          "expansion": {
           "automaticExpansion": true
          },
          "hideAddButton": true,
          "pinnedHeaderSelector": "member",
          "sortingMode": "non-breaking",
          "statisticsShown": true
         }
        },
        "contextValues": {},
        "mdx": "WITH  Member [Measures].[Margin Delta] AS [Measures].[Margin.SUM] - ([Measures].[Margin.SUM], [Epoch].[Epoch].[Base]), FORE_COLOR = CASE WHEN [Measures].[Margin Delta] >= 0 THEN rgb(44, 160, 44) WHEN [Measures].[Margin Delta] < 0 THEN rgb(224, 102, 102) END, FONT_FLAGS = CASE WHEN [Measures].[Margin Delta] >= 0 THEN 1 WHEN [Measures].[Margin Delta] < 0 THEN 1 END, FORMAT_STRING = \"#,###.\n##\"    Member [Measures].[Price Index Delta] AS [Measures].[Price Index] - ([Measures].[Price Index], [Epoch].[Epoch].[Base]), FORE_COLOR = CASE WHEN [Measures].[Price Index Delta] <= 0 THEN rgb(44, 160, 44) WHEN [Measures].[Price Index Delta] > 0 THEN rgb(224, 102, 102) END, FONT_FLAGS = CASE WHEN [Measures].[Price Index Delta] <= 0 THEN 1 WHEN [Measures].[Price Index Delta] > 0 THEN 1 END, FORMAT_STRING = \"#,###.##\"  SELECT NON EMPTY Crossjoin(Hierarchize([Epoch].[Epoch].[Branch].Members), Hierarchize(DrilldownLevel([Hierarchies].[Class].[ALL].[AllMember]))) ON ROWS, NON EMPTY {[Measures].[Price Index], [Measures].[Margin.SUM], [Measures].[Margin Delta], [Measures].[Price Index Delta]} ON COLUMNS FROM [products_cube] CELL PROPERTIES BACK_COLOR, FONT_FLAGS, FORE_COLOR, FORMATTED_VALUE, VALUE",
        "ranges": {
         "column": {
          "chunkSize": 50,
          "thresholdPercentage": 0.2
         },
         "row": {
          "chunkSize": 2000,
          "thresholdPercentage": 0.1
         }
        },
        "serverUrl": "",
        "updateMode": "once"
       },
       "containerKey": "pivot-table",
       "showTitleBar": false,
       "style": {}
      }
     }
    }
   },
   "outputs": [
    {
     "data": {
      "application/vnd.atoti.v0+json": {
       "cube": "products_cube",
       "name": "Results summary",
       "session": "Unnamed"
      },
      "text/plain": [
       "Install and enable the atoti JupyterLab extension to see this widget."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "cube.visualize(\"Results summary\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Combining Scenarios"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "atoti": {
     "state": {
      "name": "Price index comparison across scenarios (per class)",
      "type": "container",
      "value": {
       "body": {
        "configuration": {
         "tabular": {
          "addButtonFilter": "numeric",
          "columns": [
           {
            "key": "([Measures].[Price Index],[Epoch].[Epoch].[Branch].[Optimized price index])",
            "width": 162
           },
           {
            "key": "([Measures].[Price Index],[Epoch].[Epoch].[Branch].[Increased price on complementary products])",
            "width": 289
           },
           {
            "key": "c-treeCells-member",
            "width": 205
           }
          ],
          "columnsGroups": [
           {
            "captionProducer": "firstColumn",
            "cellFactory": "kpi-status",
            "selector": "kpi-status"
           },
           {
            "captionProducer": "firstColumn",
            "cellFactory": "lookup",
            "selector": "lookup"
           },
           {
            "captionProducer": "expiry",
            "cellFactory": "expiry",
            "selector": "kpi-expiry"
           },
           {
            "captionProducer": "columnMerge",
            "cellFactory": {
             "args": {},
             "key": "treeCells"
            },
            "selector": "member"
           }
          ],
          "defaultOptions": {},
          "expansion": {
           "automaticExpansion": true
          },
          "hideAddButton": true,
          "pinnedHeaderSelector": "member",
          "sortingMode": "non-breaking",
          "statisticsShown": true
         }
        },
        "contextValues": {},
        "mdx": "SELECT NON EMPTY Crossjoin([Measures].[Price Index], [Epoch].[Epoch].[Branch].Members) ON COLUMNS, NON EMPTY Crossjoin([Measure Simulations].[Price Strategies].[Price Strategies].Members, Hierarchize(DrilldownLevel([Hierarchies].[Class].[ALL].[AllMember]))) ON ROWS FROM [products_cube] CELL PROPERTIES VALUE, FORMATTED_VALUE, BACK_COLOR, FORE_COLOR, FONT_FLAGS",
        "ranges": {
         "column": {
          "chunkSize": 50,
          "thresholdPercentage": 0.2
         },
         "row": {
          "chunkSize": 2000,
          "thresholdPercentage": 0.1
         }
        },
        "serverUrl": "",
        "updateMode": "once"
       },
       "containerKey": "pivot-table",
       "showTitleBar": false,
       "style": {}
      }
     }
    }
   },
   "outputs": [
    {
     "data": {
      "application/vnd.atoti.v0+json": {
       "cube": "products_cube",
       "name": "Price index comparison across scenarios (per class)",
       "session": "Unnamed"
      },
      "text/plain": [
       "Install and enable the atoti JupyterLab extension to see this widget."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "cube.visualize(\"Price index comparison across scenarios (per class)\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can also have a look at our second summary dashboard of the simulation part (run the next cell to get the link)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'http://localhost:36413/#/dashboard/a17'"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "session.url + \"/#/dashboard/a17\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}