data-exploration-and-pipeline.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Exploration and Pipeline\n",
    "\n",
    "The database contains `Users`, `Orders`, and `Providers` tables. \n",
    "\n",
    "Partners are the companies who sell surplus items on the marketplace.\n",
    "A cohort consists of customers who made their first order within the same month (M0). \n",
    "M1 retention is the share of customers who have made at least one purchase one month after their first purchase month.\n",
    "\n",
    "Explore the data with Sql to investigate the following:\n",
    "\n",
    "- The top 10 partners by sales\n",
    "- Customers’ favourite partner segments (default offer types). \n",
    "- What is the M1 retention for any given customer cohort. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Connect to the database \n",
    "The connection to the sqlite datbase is achieved through the `jupysql` python library. This allows querying the database from jupyter notebook. Alternative tools are SQLMagic"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">displaylimit: Value None will be treated as 0 (no limit)</span>"
      ],
      "text/plain": [
       "displaylimit: Value None will be treated as 0 (no limit)"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Connecting to &#x27;sqlite:///data/mock_resq.db&#x27;</span>"
      ],
      "text/plain": [
       "Connecting to 'sqlite:///data/mock_resq.db'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import sqlite3\n",
    "con = sqlite3.connect(\"./data/mock_resq.db\") \n",
    "\n",
    "%load_ext sql\n",
    "%config SqlMagic.displaylimit = None\n",
    "\n",
    "%sql sqlite:///data/mock_resq.db"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How many user are in the database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;sqlite:///data/mock_resq.db&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'sqlite:///data/mock_resq.db'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>user_count</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>358366</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+------------+\n",
       "| user_count |\n",
       "+------------+\n",
       "|   358366   |\n",
       "+------------+"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT COUNT(*) AS user_count\n",
    "FROM users"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How many countries do the users come from"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;sqlite:///data/mock_resq.db&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'sqlite:///data/mock_resq.db'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>country_count</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>111</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+---------------+\n",
       "| country_count |\n",
       "+---------------+\n",
       "|      111      |\n",
       "+---------------+"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT COUNT(DISTINCT country) AS country_count\n",
    "FROM users"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Which top 10 countries having the most users"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;sqlite:///data/mock_resq.db&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'sqlite:///data/mock_resq.db'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>country</th>\n",
       "            <th>user_count</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>FI</td>\n",
       "            <td>339573</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>SE</td>\n",
       "            <td>8961</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>EE</td>\n",
       "            <td>6505</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>DE</td>\n",
       "            <td>512</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>AX</td>\n",
       "            <td>434</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>ES</td>\n",
       "            <td>209</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>FR</td>\n",
       "            <td>200</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>GB</td>\n",
       "            <td>190</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>AT</td>\n",
       "            <td>182</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>NL</td>\n",
       "            <td>152</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+---------+------------+\n",
       "| country | user_count |\n",
       "+---------+------------+\n",
       "|    FI   |   339573   |\n",
       "|    SE   |    8961    |\n",
       "|    EE   |    6505    |\n",
       "|    DE   |    512     |\n",
       "|    AX   |    434     |\n",
       "|    ES   |    209     |\n",
       "|    FR   |    200     |\n",
       "|    GB   |    190     |\n",
       "|    AT   |    182     |\n",
       "|    NL   |    152     |\n",
       "+---------+------------+"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "SELECT country, COUNT(*) AS user_count\n",
    "FROM users\n",
    "GROUP BY country\n",
    "ORDER BY user_count DESC\n",
    "LIMIT 10"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Inference\n",
    "\n",
    "- There are **358,366** users in the database from **111** different countries.\n",
    "- The top 10 countries where the users come from are Finland (FI), Sweden (SE), Estonia (EE), Germany (DE), Åland (AX), Spain (ES), France (FR), Great Britain (GB), Austria (AT), and the Netherlands (NL).\n",
    "- Ninety-four percent of users (94%, i.e., **339,573** users) are from Finland, followed by Sweden with **8,961** users."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How many Providers are in the database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;sqlite:///data/mock_resq.db&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'sqlite:///data/mock_resq.db'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>provider_count</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>4337</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+----------------+\n",
       "| provider_count |\n",
       "+----------------+\n",
       "|      4337      |\n",
       "+----------------+"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT COUNT(id) AS provider_count\n",
    "FROM providers"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Do providers have multiple offer types?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;sqlite:///data/mock_resq.db&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'sqlite:///data/mock_resq.db'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>provider</th>\n",
       "            <th>offer_type_count</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>9222930112446389796</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>9217379655006460479</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>9215371507696178188</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>9214584721622525154</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>9212615296993900753</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+---------------------+------------------+\n",
       "|       provider      | offer_type_count |\n",
       "+---------------------+------------------+\n",
       "| 9222930112446389796 |        1         |\n",
       "| 9217379655006460479 |        1         |\n",
       "| 9215371507696178188 |        1         |\n",
       "| 9214584721622525154 |        1         |\n",
       "| 9212615296993900753 |        1         |\n",
       "+---------------------+------------------+"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT id AS provider, COUNT(defaultoffertype) AS offer_type_count\n",
    "FROM providers\n",
    "GROUP BY id\n",
    "ORDER BY offer_type_count DESC\n",
    "LIMIT 5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How many providers per country"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;sqlite:///data/mock_resq.db&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'sqlite:///data/mock_resq.db'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>country</th>\n",
       "            <th>provider_count</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>fin</td>\n",
       "            <td>4095</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>est</td>\n",
       "            <td>154</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>swe</td>\n",
       "            <td>84</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>pol</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>deu</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+---------+----------------+\n",
       "| country | provider_count |\n",
       "+---------+----------------+\n",
       "|   fin   |      4095      |\n",
       "|   est   |      154       |\n",
       "|   swe   |       84       |\n",
       "|   pol   |       2        |\n",
       "|   deu   |       2        |\n",
       "+---------+----------------+"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "SELECT country, COUNT(*) AS provider_count\n",
    "FROM providers\n",
    "GROUP BY country\n",
    "ORDER BY provider_count DESC"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Inference\n",
    "\n",
    "- There are **4,337** providers in the database, with each provider having exactly one offer type (meal, snack, dessert, ingredients, flowers, etc.).\n",
    "- The providers are from Finland (FIN), Estonia (EST), Sweden (SWE), Poland (POL), and Germany (DEU).\n",
    "- Over **94% (4,095)** of the providers are from Finland, followed by Estonia with **154** providers.\n",
    "- Poland and Germany each have two (2) providers.\n",
    "\n",
    "`Questions:`\n",
    "\n",
    "1. Are **partners** the same as **providers**? If yes, why is there a difference in nomenclature between the database and the instructions?\n",
    "   \n",
    "2. The **Users** table uses two-letter [ISO-3166 country codes](https://en.wikipedia.org/wiki/List_of_ISO_3166_country_codes) (e.g., FI, SE, ES), while the **Providers** table uses three-letter country codes (e.g., FIN, SWE, POL). Is there a particular reason for this?\n",
    "\n",
    "3. All providers have exactly one offer type in the database, but the nomenclature includes terms like **defaultOfferType** and **partner segment**. Do providers have multiple offer types? Could the nomenclature be harmonized?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "## `Now answering the Analyst's questions`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Top 10 partners by sales"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;sqlite:///data/mock_resq.db&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'sqlite:///data/mock_resq.db'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>providerId</th>\n",
       "            <th>total_sales</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>7198110370745783236</td>\n",
       "            <td>10917800</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>8312310143652755348</td>\n",
       "            <td>7467750</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>8097235958083241788</td>\n",
       "            <td>2383700</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>3865474760205653333</td>\n",
       "            <td>2223400</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>8084884958338058541</td>\n",
       "            <td>1868140</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>4734853230275691017</td>\n",
       "            <td>1702100</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>5305286819167536850</td>\n",
       "            <td>1690500</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>1066258454353124935</td>\n",
       "            <td>1568100</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>7642201963087705313</td>\n",
       "            <td>1472000</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>4014236829817167297</td>\n",
       "            <td>1457000</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+---------------------+-------------+\n",
       "|      providerId     | total_sales |\n",
       "+---------------------+-------------+\n",
       "| 7198110370745783236 |   10917800  |\n",
       "| 8312310143652755348 |   7467750   |\n",
       "| 8097235958083241788 |   2383700   |\n",
       "| 3865474760205653333 |   2223400   |\n",
       "| 8084884958338058541 |   1868140   |\n",
       "| 4734853230275691017 |   1702100   |\n",
       "| 5305286819167536850 |   1690500   |\n",
       "| 1066258454353124935 |   1568100   |\n",
       "| 7642201963087705313 |   1472000   |\n",
       "| 4014236829817167297 |   1457000   |\n",
       "+---------------------+-------------+"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "SELECT providerid, SUM(sales) AS total_sales\n",
    "FROM orders\n",
    "GROUP BY providerid\n",
    "ORDER BY total_sales DESC\n",
    "LIMIT 10"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How many currencies are there in the database?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;sqlite:///data/mock_resq.db&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'sqlite:///data/mock_resq.db'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>currency</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>eur</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>sek</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+----------+\n",
       "| currency |\n",
       "+----------+\n",
       "|   eur    |\n",
       "|   sek    |\n",
       "+----------+"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "SELECT DISTINCT currency\n",
    "FROM orders"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are only **Euro (EUR)** and **Swedish Krone (SEK)** currencies in the database.\n",
    "\n",
    "`Question:`  \n",
    "Is the conversion rate to the base currency (most likely Euro) saved during the order payment? This will be useful for transforming sales figures into a common currency for consistent reporting."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How about **top 10 partners by sales in the respective sales currencies** ?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;sqlite:///data/mock_resq.db&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'sqlite:///data/mock_resq.db'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>providerId</th>\n",
       "            <th>currency</th>\n",
       "            <th>total_sales</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>7198110370745783236</td>\n",
       "            <td>sek</td>\n",
       "            <td>10917800</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>8312310143652755348</td>\n",
       "            <td>sek</td>\n",
       "            <td>7467750</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>8097235958083241788</td>\n",
       "            <td>sek</td>\n",
       "            <td>2383700</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>3865474760205653333</td>\n",
       "            <td>sek</td>\n",
       "            <td>2223400</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>8084884958338058541</td>\n",
       "            <td>eur</td>\n",
       "            <td>1868140</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>4734853230275691017</td>\n",
       "            <td>sek</td>\n",
       "            <td>1702100</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>5305286819167536850</td>\n",
       "            <td>sek</td>\n",
       "            <td>1690500</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>1066258454353124935</td>\n",
       "            <td>sek</td>\n",
       "            <td>1568100</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>7642201963087705313</td>\n",
       "            <td>sek</td>\n",
       "            <td>1472000</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>4014236829817167297</td>\n",
       "            <td>sek</td>\n",
       "            <td>1457000</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+---------------------+----------+-------------+\n",
       "|      providerId     | currency | total_sales |\n",
       "+---------------------+----------+-------------+\n",
       "| 7198110370745783236 |   sek    |   10917800  |\n",
       "| 8312310143652755348 |   sek    |   7467750   |\n",
       "| 8097235958083241788 |   sek    |   2383700   |\n",
       "| 3865474760205653333 |   sek    |   2223400   |\n",
       "| 8084884958338058541 |   eur    |   1868140   |\n",
       "| 4734853230275691017 |   sek    |   1702100   |\n",
       "| 5305286819167536850 |   sek    |   1690500   |\n",
       "| 1066258454353124935 |   sek    |   1568100   |\n",
       "| 7642201963087705313 |   sek    |   1472000   |\n",
       "| 4014236829817167297 |   sek    |   1457000   |\n",
       "+---------------------+----------+-------------+"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "SELECT providerid, currency, SUM(sales) AS total_sales\n",
    "FROM orders\n",
    "GROUP BY providerid, currency\n",
    "ORDER BY total_sales DESC\n",
    "LIMIT 10"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The same providers are in the top 10"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Converting SEK (Swedish Krone) to Euro with the assumption that a provider sells in only one currency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;sqlite:///data/mock_resq.db&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'sqlite:///data/mock_resq.db'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>providerId</th>\n",
       "            <th>total_sales_euro</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>8084884958338058541</td>\n",
       "            <td>1868140</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2329385876751836948</td>\n",
       "            <td>1113610</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>7781996256202689245</td>\n",
       "            <td>1089737</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>3314909292283785248</td>\n",
       "            <td>987080</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>7198110370745783236</td>\n",
       "            <td>960766.3999999999</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>864133707167331065</td>\n",
       "            <td>844570</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>644313756025543931</td>\n",
       "            <td>784250</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>8671992772042032524</td>\n",
       "            <td>724210</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>251523446593852416</td>\n",
       "            <td>720007</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>5617133624212883839</td>\n",
       "            <td>707950</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+---------------------+-------------------+\n",
       "|      providerId     |  total_sales_euro |\n",
       "+---------------------+-------------------+\n",
       "| 8084884958338058541 |      1868140      |\n",
       "| 2329385876751836948 |      1113610      |\n",
       "| 7781996256202689245 |      1089737      |\n",
       "| 3314909292283785248 |       987080      |\n",
       "| 7198110370745783236 | 960766.3999999999 |\n",
       "|  864133707167331065 |       844570      |\n",
       "|  644313756025543931 |       784250      |\n",
       "| 8671992772042032524 |       724210      |\n",
       "|  251523446593852416 |       720007      |\n",
       "| 5617133624212883839 |       707950      |\n",
       "+---------------------+-------------------+"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "-- with the assumption that a provider sells in only one currency\n",
    "SELECT providerid, \n",
    "       SUM(CASE \n",
    "               WHEN currency = 'sek' THEN sales * 0.088\n",
    "               ELSE sales \n",
    "           END) AS total_sales_euro\n",
    "FROM orders\n",
    "GROUP BY providerid\n",
    "ORDER BY total_sales_euro DESC\n",
    "LIMIT 10"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Converting SEK (Swedish Krone) to Euro with the assumption that a provider sells in multiple currencies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;sqlite:///data/mock_resq.db&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'sqlite:///data/mock_resq.db'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>providerid</th>\n",
       "            <th>total_sales_euro</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>8084884958338058541</td>\n",
       "            <td>1868140</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2329385876751836948</td>\n",
       "            <td>1113610</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>7781996256202689245</td>\n",
       "            <td>1089737</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>3314909292283785248</td>\n",
       "            <td>987080</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>7198110370745783236</td>\n",
       "            <td>960766.3999999999</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>864133707167331065</td>\n",
       "            <td>844570</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>644313756025543931</td>\n",
       "            <td>784250</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>8671992772042032524</td>\n",
       "            <td>724210</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>251523446593852416</td>\n",
       "            <td>720007</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>5617133624212883839</td>\n",
       "            <td>707950</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+---------------------+-------------------+\n",
       "|      providerid     |  total_sales_euro |\n",
       "+---------------------+-------------------+\n",
       "| 8084884958338058541 |      1868140      |\n",
       "| 2329385876751836948 |      1113610      |\n",
       "| 7781996256202689245 |      1089737      |\n",
       "| 3314909292283785248 |       987080      |\n",
       "| 7198110370745783236 | 960766.3999999999 |\n",
       "|  864133707167331065 |       844570      |\n",
       "|  644313756025543931 |       784250      |\n",
       "| 8671992772042032524 |       724210      |\n",
       "|  251523446593852416 |       720007      |\n",
       "| 5617133624212883839 |       707950      |\n",
       "+---------------------+-------------------+"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "-- With the assumption that a provider sells in multiple currencies\n",
    "\n",
    "WITH provider_sales_currency AS (\n",
    "    SELECT providerid, currency, SUM(sales) AS total_sales\n",
    "    FROM orders\n",
    "    GROUP BY providerid, currency\n",
    ")\n",
    "\n",
    "SELECT \n",
    "    providerid, \n",
    "    SUM(\n",
    "        CASE  \n",
    "            WHEN currency = 'sek' THEN total_sales * 0.088 -- assumed conversion rate\n",
    "            ELSE total_sales\n",
    "        END\n",
    "    ) AS total_sales_euro\n",
    "FROM provider_sales_currency\n",
    "GROUP BY providerid\n",
    "ORDER BY total_sales_euro DESC\n",
    "LIMIT 10"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Customers’ favourite partner segments (default offer types)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;sqlite:///data/mock_resq.db&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'sqlite:///data/mock_resq.db'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>partner_segment</th>\n",
       "            <th>sum_order_quantity</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>meal</td>\n",
       "            <td>305254</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+-----------------+--------------------+\n",
       "| partner_segment | sum_order_quantity |\n",
       "+-----------------+--------------------+\n",
       "|       meal      |       305254       |\n",
       "+-----------------+--------------------+"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "SELECT P.defaultoffertype AS partner_segment, \n",
    "       SUM(O.quantity) AS sum_order_quantity\n",
    "FROM orders O\n",
    "JOIN providers P ON O.providerid = P.id\n",
    "GROUP BY P.defaultoffertype\n",
    "ORDER BY sum_order_quantity DESC\n",
    "LIMIT 1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The Customers' favourite partner segment is **meal** with **305254** orders. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## What is the M1 retention for any given customer cohort. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Checking table definition and order table column types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;sqlite:///data/mock_resq.db&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'sqlite:///data/mock_resq.db'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>type</th>\n",
       "            <th>name</th>\n",
       "            <th>tbl_name</th>\n",
       "            <th>rootpage</th>\n",
       "            <th>sql</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>table</td>\n",
       "            <td>orders</td>\n",
       "            <td>orders</td>\n",
       "            <td>2</td>\n",
       "            <td>CREATE TABLE orders (id, createdAt, userId, quantity, refunded, currency, sales, providerId)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>table</td>\n",
       "            <td>providers</td>\n",
       "            <td>providers</td>\n",
       "            <td>4420</td>\n",
       "            <td>CREATE TABLE providers (id, defaultOfferType, country, registeredDate)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>table</td>\n",
       "            <td>users</td>\n",
       "            <td>users</td>\n",
       "            <td>4465</td>\n",
       "            <td>CREATE TABLE users (id, country, registeredDate)</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+-------+-----------+-----------+----------+----------------------------------------------------------------------------------------------+\n",
       "|  type |    name   |  tbl_name | rootpage |                                             sql                                              |\n",
       "+-------+-----------+-----------+----------+----------------------------------------------------------------------------------------------+\n",
       "| table |   orders  |   orders  |    2     | CREATE TABLE orders (id, createdAt, userId, quantity, refunded, currency, sales, providerId) |\n",
       "| table | providers | providers |   4420   |            CREATE TABLE providers (id, defaultOfferType, country, registeredDate)            |\n",
       "| table |   users   |   users   |   4465   |                       CREATE TABLE users (id, country, registeredDate)                       |\n",
       "+-------+-----------+-----------+----------+----------------------------------------------------------------------------------------------+"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "SELECT * \n",
    "FROM sqlite_master \n",
    "WHERE type = 'table'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;sqlite:///data/mock_resq.db&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'sqlite:///data/mock_resq.db'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>name</th>\n",
       "            <th>type</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>id</td>\n",
       "            <td></td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>createdAt</td>\n",
       "            <td></td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>userId</td>\n",
       "            <td></td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>quantity</td>\n",
       "            <td></td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>refunded</td>\n",
       "            <td></td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>currency</td>\n",
       "            <td></td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>sales</td>\n",
       "            <td></td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>providerId</td>\n",
       "            <td></td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+------------+------+\n",
       "|    name    | type |\n",
       "+------------+------+\n",
       "|     id     |      |\n",
       "| createdAt  |      |\n",
       "|   userId   |      |\n",
       "|  quantity  |      |\n",
       "|  refunded  |      |\n",
       "|  currency  |      |\n",
       "|   sales    |      |\n",
       "| providerId |      |\n",
       "+------------+------+"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "-- pragma table_info('orders')\n",
    "SELECT name, type \n",
    "FROM pragma_table_info('orders')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "No column types ? This is quit strange. The Date fields needs to be converted for date and aggregation functions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now creating cohorts and computing the M1 retention share\n",
    "A cohort consists of customers who made their first order within the same month (M0). \n",
    "M1 retention is the share of customers who have made at least one purchase one month after their first purchase month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;sqlite:///data/mock_resq.db&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'sqlite:///data/mock_resq.db'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>M1_SHARE_PERCENTAGE</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>14.587526783975067</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+---------------------+\n",
       "| M1_SHARE_PERCENTAGE |\n",
       "+---------------------+\n",
       "|  14.587526783975067 |\n",
       "+---------------------+"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "WITH FIRSTORDERDATES AS (\n",
    "        SELECT \n",
    "            USERID, \n",
    "            strftime('%Y-%m-01', MIN(DATE(CREATEDAT)))  AS COHORT_DATE\n",
    "        FROM ORDERS\n",
    "        GROUP BY USERID\n",
    "    ),\n",
    "    AT_LEAST_ONE_ORDER_PER_MONTH AS (\n",
    "        SELECT \n",
    "            USERID, \n",
    "            MAX(CREATEDAT) AS PURCHASE_DATE\n",
    "        FROM ORDERS\n",
    "        GROUP BY USERID, strftime('%Y-%m-01', CREATEDAT)\n",
    "    ),\n",
    "    RETENTION_TABLE AS (\n",
    "        SELECT \n",
    "            O.USERID, \n",
    "            FOD.COHORT_DATE,\n",
    "            O.PURCHASE_DATE, \n",
    "            ((strftime('%Y', O.PURCHASE_DATE) - strftime('%Y', FOD.COHORT_DATE)) * 12) + \n",
    "            (strftime('%m', O.PURCHASE_DATE) - strftime('%m', FOD.COHORT_DATE)) AS MONTHS_SINCE_FIRST_PURCHASE\n",
    "        FROM AT_LEAST_ONE_ORDER_PER_MONTH O\n",
    "        LEFT JOIN FIRSTORDERDATES FOD ON O.USERID = FOD.USERID\n",
    "        ORDER BY FOD.COHORT_DATE, O.USERID, O.PURCHASE_DATE\n",
    "    )\n",
    "\n",
    "    SELECT \n",
    "    (\n",
    "        CAST(\n",
    "        (\n",
    "            SELECT COUNT(*) \n",
    "            FROM RETENTION_TABLE \n",
    "            WHERE MONTHS_SINCE_FIRST_PURCHASE = 1\n",
    "        ) AS REAL) / \n",
    "        CAST(\n",
    "            (\n",
    "                SELECT COUNT(DISTINCT(USERID)) FROM RETENTION_TABLE\n",
    "            ) AS REAL)\n",
    "    ) * 100 AS M1_SHARE_PERCENTAGE"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Provider retention"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;sqlite:///data/mock_resq.db&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'sqlite:///data/mock_resq.db'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>M1_SHARE_PERCENTAGE</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>77.21661054994388</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+---------------------+\n",
       "| M1_SHARE_PERCENTAGE |\n",
       "+---------------------+\n",
       "|  77.21661054994388  |\n",
       "+---------------------+"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "WITH FIRSTORDERDATES AS (\n",
    "        SELECT \n",
    "            providerId, \n",
    "            strftime('%Y-%m-01', MIN(DATE(CREATEDAT)))  AS COHORT_DATE\n",
    "        FROM ORDERS\n",
    "        GROUP BY providerId\n",
    "    ),\n",
    "    AT_LEAST_ONE_ORDER_PER_MONTH AS (\n",
    "        SELECT \n",
    "            providerId, \n",
    "            MAX(CREATEDAT) AS PURCHASE_DATE\n",
    "        FROM ORDERS\n",
    "        GROUP BY providerId, strftime('%Y-%m-01', CREATEDAT)\n",
    "    ),\n",
    "    RETENTION_TABLE AS (\n",
    "        SELECT \n",
    "            O.providerId, \n",
    "            FOD.COHORT_DATE,\n",
    "            O.PURCHASE_DATE, \n",
    "            ((strftime('%Y', O.PURCHASE_DATE) - strftime('%Y', FOD.COHORT_DATE)) * 12) + \n",
    "            (strftime('%m', O.PURCHASE_DATE) - strftime('%m', FOD.COHORT_DATE)) AS MONTHS_SINCE_FIRST_PURCHASE\n",
    "        FROM AT_LEAST_ONE_ORDER_PER_MONTH O\n",
    "        LEFT JOIN FIRSTORDERDATES FOD ON O.providerId = FOD.providerId\n",
    "        ORDER BY FOD.COHORT_DATE, O.providerId, O.PURCHASE_DATE\n",
    "    )\n",
    "\n",
    "    SELECT \n",
    "    (\n",
    "        CAST(\n",
    "        (\n",
    "            SELECT COUNT(*) \n",
    "            FROM RETENTION_TABLE \n",
    "            WHERE MONTHS_SINCE_FIRST_PURCHASE = 1\n",
    "        ) AS REAL) / \n",
    "        CAST(\n",
    "            (\n",
    "                SELECT COUNT(DISTINCT(providerId)) FROM RETENTION_TABLE\n",
    "            ) AS REAL)\n",
    "    ) * 100 AS M1_SHARE_PERCENTAGE"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`Answer:` \n",
    "\n",
    "The M1 retention rate is **14.59%**. This means that **14.59%** of customers (**17,973** out of **123,203** customers) made at least one purchase one month after their first purchase month."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Building the data pipeline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First, install **dbt-core** and **dbt-sqlite** with:\n",
    "\n",
    "```bash\n",
    "pip install --quiet dbt-core dbt-sqlite\n",
    "```\n",
    "\n",
    "Make sure that the version of [dbt-sqlite](https://github.com/codeforkjeff/dbt-sqlite) aligns with that of dbt-core for the setup to work properly.\n",
    "\n",
    "If the project is set up with the exported virtual environment file **environment.yml**, there is no need to reinstall these dependencies. Check that they are installed with:\n",
    "\n",
    "```bash\n",
    "pip show dbt-core dbt-sqlite\n",
    "```\n",
    "\n",
    "The `dbt init` command was used to initialize the dbt project in the subdirectory **ResqDbPipeline**. Therefore, all dbt commands should be run in that subdirectory."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Connecting profile for Sqlite database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "\n",
    "dbt_project = \"./ResqDbPipeline\"\n",
    "os.makedirs(dbt_project, exist_ok=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Overwriting ./ResqDbPipeline/profiles.yml\n"
     ]
    }
   ],
   "source": [
    "%%writefile {dbt_project}/profiles.yml\n",
    "ResqDbPipeline:\n",
    "  target: dev\n",
    "  outputs:\n",
    "    dev:\n",
    "      type: sqlite\n",
    "      threads: 1\n",
    "      database: 'database'\n",
    "      schema: 'main'\n",
    "      schemas_and_paths:\n",
    "        main: '../data/mock_resq.db'\n",
    "      schema_directory: '../data'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Testing sqlite database connection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[0m10:06:41  Running with dbt=1.5.0\n",
      "\u001b[0m10:06:41  dbt version: 1.5.0\n",
      "\u001b[0m10:06:41  python version: 3.12.6\n",
      "\u001b[0m10:06:41  python path: /opt/anaconda3/envs/resq/bin/python\n",
      "\u001b[0m10:06:41  os info: macOS-14.6-arm64-arm-64bit\n",
      "\u001b[0m10:06:41  Using profiles.yml file at /Users/asare/source/ResqCLV/ResqDbPipeline/profiles.yml\n",
      "\u001b[0m10:06:41  Using dbt_project.yml file at /Users/asare/source/ResqCLV/ResqDbPipeline/dbt_project.yml\n",
      "\u001b[0m10:06:41  Configuration:\n",
      "\u001b[0m10:06:41    profiles.yml file [\u001b[32mOK found and valid\u001b[0m]\n",
      "\u001b[0m10:06:41    dbt_project.yml file [\u001b[32mOK found and valid\u001b[0m]\n",
      "\u001b[0m10:06:41  Required dependencies:\n",
      "\u001b[0m10:06:41   - git [\u001b[32mOK found\u001b[0m]\n",
      "\n",
      "\u001b[0m10:06:41  Connection:\n",
      "\u001b[0m10:06:41    database: database\n",
      "\u001b[0m10:06:41    schema: main\n",
      "\u001b[0m10:06:41    schemas_and_paths: {'main': '../data/mock_resq.db'}\n",
      "\u001b[0m10:06:41    schema_directory: ../data\n",
      "\u001b[0m10:06:41    Connection test: [\u001b[32mOK connection ok\u001b[0m]\n",
      "\n",
      "\u001b[0m10:06:41  \u001b[32mAll checks passed!\u001b[0m\n"
     ]
    }
   ],
   "source": [
    "!cd {dbt_project} && dbt debug"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[0m10:06:43  Running with dbt=1.5.0\n",
      "\u001b[0m10:06:43  Unable to do partial parsing because saved manifest not found. Starting full parse.\n",
      "\u001b[0m10:06:43  Encountered an error:\n",
      "Compilation Error\n",
      "  dbt found two macros named \"materialization_table_default\" in the project\n",
      "  \"dbt\".\n",
      "   To fix this error, rename or remove one of the following macros:\n",
      "      - macros/materializations/models/table/table.sql\n",
      "      - macros/materializations/models/table.sql\n"
     ]
    }
   ],
   "source": [
    "!cd {dbt_project} && dbt run --full-refresh"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This error seems strange and resembles bugs reported in the following threads:\n",
    "\n",
    "- [dbt compilation error: materialization table default](https://discourse.getdbt.com/t/dbt-compilation-error-materialization-table-default/16375)\n",
    "- [Materialization table default macro occurs](https://discourse.getdbt.com/t/materialization-table-default-macro-occurs/14973/2)\n",
    "\n",
    "### Next Action\n",
    "Proceeding with good old Python scripts since this is a **`simple pipeline`**. There is only one data source, which is already in the SQLite database, so there is no extraction step. However, there will be a step that transforms the data with queries and joins, and then creates Views."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Top partners view script"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Overwriting data/sql/top_partners.sql\n"
     ]
    }
   ],
   "source": [
    "%%writefile data/sql/top_partners.sql\n",
    "DROP VIEW IF EXISTS TOP_PARTNERS;\n",
    "\n",
    "CREATE VIEW TOP_PARTNERS \n",
    "AS \n",
    "    SELECT \n",
    "        PROVIDERID, \n",
    "        CURRENCY,\n",
    "        SUM(SALES) AS TOTAL_SALES\n",
    "    FROM ORDERS\n",
    "    GROUP BY PROVIDERID, CURRENCY\n",
    "    ORDER BY TOTAL_SALES DESC;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Parter segment order quantity view script"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Overwriting data/sql/partner_segment_order_quantity.sql\n"
     ]
    }
   ],
   "source": [
    "%%writefile data/sql/partner_segment_order_quantity.sql\n",
    "\n",
    "DROP VIEW IF EXISTS PARTNET_SEGMENT_ORDER_QUANTIY;\n",
    "\n",
    "CREATE VIEW PARTNET_SEGMENT_ORDER_QUANTIY\n",
    "AS \n",
    "    SELECT \n",
    "        P.DEFAULTOFFERTYPE AS PARTNER_SEGMENT, \n",
    "        SUM(O.QUANTITY) AS SUM_ORDER_QUANTITY\n",
    "    FROM ORDERS O\n",
    "    JOIN PROVIDERS P ON O.PROVIDERID = P.ID\n",
    "    GROUP BY P.DEFAULTOFFERTYPE\n",
    "    ORDER BY SUM_ORDER_QUANTITY DESC;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Customer cohort view script"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Overwriting data/sql/customer_cohort.sql\n"
     ]
    }
   ],
   "source": [
    "%%writefile data/sql/customer_cohort.sql\n",
    "DROP VIEW IF EXISTS CUSTOMER_COHORT;\n",
    "\n",
    "CREATE VIEW CUSTOMER_COHORT\n",
    "AS\n",
    "    WITH FIRSTORDERDATES AS (\n",
    "        SELECT \n",
    "            USERID, \n",
    "            strftime('%Y-%m-01', MIN(DATE(CREATEDAT)))  AS COHORT_DATE\n",
    "        FROM ORDERS\n",
    "        GROUP BY USERID\n",
    "    ),\n",
    "    AT_LEAST_ONE_ORDER_PER_MONTH AS (\n",
    "        SELECT \n",
    "            USERID, \n",
    "            MAX(CREATEDAT) AS PURCHASE_DATE\n",
    "        FROM ORDERS\n",
    "        GROUP BY USERID, strftime('%Y-%m-01', CREATEDAT)\n",
    "    )\n",
    "\n",
    "    SELECT \n",
    "        O.USERID, \n",
    "        FOD.COHORT_DATE,\n",
    "        O.PURCHASE_DATE, \n",
    "        ((strftime('%Y', O.PURCHASE_DATE) - strftime('%Y', FOD.COHORT_DATE)) * 12) + \n",
    "        (strftime('%m', O.PURCHASE_DATE) - strftime('%m', FOD.COHORT_DATE)) AS MONTHS_SINCE_FIRST_PURCHASE\n",
    "    FROM AT_LEAST_ONE_ORDER_PER_MONTH O\n",
    "    LEFT JOIN FIRSTORDERDATES FOD ON O.USERID = FOD.USERID\n",
    "    ORDER BY FOD.COHORT_DATE, O.USERID, O.PURCHASE_DATE;\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Provider cohort view script"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Overwriting data/sql/provider_cohort.sql\n"
     ]
    }
   ],
   "source": [
    "%%writefile data/sql/provider_cohort.sql\n",
    "DROP VIEW IF EXISTS PROVIDER_COHORT;\n",
    "\n",
    "CREATE VIEW PROVIDER_COHORT\n",
    "AS\n",
    "    WITH FIRSTORDERDATES AS (\n",
    "            SELECT \n",
    "                providerId, \n",
    "                strftime('%Y-%m-01', MIN(DATE(CREATEDAT)))  AS COHORT_DATE\n",
    "            FROM ORDERS\n",
    "            GROUP BY providerId\n",
    "        ),\n",
    "        AT_LEAST_ONE_ORDER_PER_MONTH AS (\n",
    "            SELECT \n",
    "                providerId, \n",
    "                MAX(CREATEDAT) AS PURCHASE_DATE\n",
    "            FROM ORDERS\n",
    "            GROUP BY providerId, strftime('%Y-%m-01', CREATEDAT)\n",
    "        )\n",
    "       \n",
    "        SELECT \n",
    "            O.providerId, \n",
    "            FOD.COHORT_DATE,\n",
    "            O.PURCHASE_DATE, \n",
    "            ((strftime('%Y', O.PURCHASE_DATE) - strftime('%Y', FOD.COHORT_DATE)) * 12) + \n",
    "            (strftime('%m', O.PURCHASE_DATE) - strftime('%m', FOD.COHORT_DATE)) AS MONTHS_SINCE_FIRST_PURCHASE\n",
    "        FROM AT_LEAST_ONE_ORDER_PER_MONTH O\n",
    "        LEFT JOIN FIRSTORDERDATES FOD ON O.providerId = FOD.providerId\n",
    "        ORDER BY FOD.COHORT_DATE, O.providerId, O.PURCHASE_DATE\n",
    "       \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Frequency, lifespan, average sales view script"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Overwriting data/sql/lifespan_frequency_sales_value.sql\n"
     ]
    }
   ],
   "source": [
    "%%writefile data/sql/lifespan_frequency_sales_value.sql\n",
    "DROP VIEW IF EXISTS LIFESPAN_FREQUENCY_SALES;\n",
    "\n",
    "CREATE VIEW LIFESPAN_FREQUENCY_SALES\n",
    "AS\n",
    "    WITH FIRSTORDERDATES AS (\n",
    "            SELECT \n",
    "                USERID, \n",
    "                strftime('%Y-%m-01', MIN(DATE(CREATEDAT)))  AS COHORT_DATE\n",
    "            FROM ORDERS\n",
    "            GROUP BY USERID\n",
    "        )\n",
    "\n",
    "    SELECT \n",
    "        O.USERID,\n",
    "        FOD.COHORT_DATE,\n",
    "        strftime('%Y-%m-01', MAX(DATE(CREATEDAT)))  AS purchase_month,\n",
    "        ((strftime('%Y', MAX(DATE(CREATEDAT))) - strftime('%Y', FOD.COHORT_DATE)) * 12) + (strftime('%m', MAX(DATE(CREATEDAT))) - strftime('%m', FOD.COHORT_DATE)) as lifespan,\n",
    "        COUNT(O.SALES) AS frequency,\n",
    "        O.currency,\n",
    "        AVG(O.SALES) AS average_sales\n",
    "    FROM ORDERS O\n",
    "    LEFT JOIN FIRSTORDERDATES FOD ON O.USERID = FOD.USERID\n",
    "    GROUP BY O.USERID, O.currency"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### The Data Pipeline script"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "#%%writefile resq_pipeline/data_pipeline.py\n",
    "import os\n",
    "import sqlite3\n",
    "import pandas as pd\n",
    "from datetime import datetime\n",
    "\n",
    "class DataPipeline(object):\n",
    "\n",
    "    def __init__(self, db_and_scripts_path=\"./data\"):\n",
    "        super().__init__()\n",
    "        self._log(\"Initializing pipeline\")\n",
    "\n",
    "        self.db_path = os.path.join(db_and_scripts_path, \"mock_resq.db\")\n",
    "        self._script_path = os.path.join(db_and_scripts_path, \"sql\")\n",
    "        os.makedirs(os.path.join(db_and_scripts_path, \"sql\"), exist_ok=True)\n",
    "\n",
    "        self._create_connection()\n",
    "        self._create_customer_cohorts_view()\n",
    "        self._create_provider_cohorts_view()\n",
    "        self._create_top_partners_by_sales_view()\n",
    "        self._create_partner_segment_order_quantity_view()\n",
    "        self._create_lifespan_frequency_sales_view()\n",
    "        self._close_connection()\n",
    "\n",
    "        self._log(\"Done\")\n",
    "\n",
    "    def top_n_partners(self, top_n=5):\n",
    "        \"\"\"\n",
    "        Return top n partners by sales\n",
    "        \"\"\"\n",
    "\n",
    "        if not isinstance(int(top_n), int):\n",
    "            raise ValueError(\"top n must be in int\")\n",
    "\n",
    "        sql = \"SELECT * FROM TOP_PARTNERS LIMIT ?\"\n",
    "        return self.execute_query(query=sql, param=(top_n,))\n",
    "\n",
    "    def customers_top_partner_segment(self, top_n=None):\n",
    "        \"\"\"\n",
    "        Returns the order quantity of the top n partner segment.\n",
    "        If top_n is not specified, it returns the favourite partner segment\n",
    "        \"\"\"\n",
    "\n",
    "        if (top_n is not None) and (not isinstance(top_n, int)):\n",
    "            raise ValueError(\"top n must be in int or None\")\n",
    "\n",
    "        top_n = 1 if top_n is None else top_n\n",
    "\n",
    "        sql = \"SELECT * FROM PARTNET_SEGMENT_ORDER_QUANTIY LIMIT ?\"\n",
    "\n",
    "        return self.execute_query(query=sql, param=(top_n,))\n",
    "\n",
    "    def m_customer_retention_rate(self, month=1):\n",
    "        \"\"\"\n",
    "        Returns M customer retention rate. Defauts to MI retention rate\n",
    "        \"\"\"\n",
    "\n",
    "        if not isinstance(month, int):\n",
    "            raise ValueError(\"month must be in int\")\n",
    "\n",
    "        sql = \"\"\"\n",
    "                SELECT \n",
    "                (\n",
    "                    CAST(\n",
    "                    (\n",
    "                        SELECT COUNT(*) \n",
    "                        FROM CUSTOMER_COHORT \n",
    "                        WHERE MONTHS_SINCE_FIRST_PURCHASE = ?\n",
    "                    ) AS REAL) / \n",
    "                    CAST(\n",
    "                        (\n",
    "                            SELECT COUNT(DISTINCT(USERID)) FROM CUSTOMER_COHORT\n",
    "                        ) AS REAL)\n",
    "                ) * 100 AS M_RETENTION\n",
    "              \"\"\"\n",
    "        return self.execute_query(query=sql, param=(month,))\n",
    "    \n",
    "    def m_provider_retention_rate(self, month=1):\n",
    "        \"\"\"\n",
    "        Returns M provider retention rate. Defauts to MI retention rate\n",
    "        \"\"\"\n",
    "\n",
    "        if not isinstance(month, int):\n",
    "            raise ValueError(\"month must be in int\")\n",
    "\n",
    "        sql = \"\"\"\n",
    "                SELECT \n",
    "                (\n",
    "                    CAST(\n",
    "                    (\n",
    "                        SELECT COUNT(*) \n",
    "                        FROM PROVIDER_COHORT \n",
    "                        WHERE MONTHS_SINCE_FIRST_PURCHASE = ?\n",
    "                    ) AS REAL) / \n",
    "                    CAST(\n",
    "                        (\n",
    "                            SELECT COUNT(DISTINCT(providerId)) FROM PROVIDER_COHORT\n",
    "                        ) AS REAL)\n",
    "                ) * 100 AS M_RETENTION\n",
    "              \"\"\"\n",
    "        return self.execute_query(query=sql, param=(month,))\n",
    "    \n",
    "    def m_customer_retention_rate_by_cohort(self, month=1, cohort=None):\n",
    "        \"\"\"\n",
    "        Returns M customer retention rate by cohort. Defauts to MI retention rate of entire customers\n",
    "\n",
    "        Keyword arguments:\n",
    "\n",
    "        :cohort: -- the cohort in the form yyyy-mm-01\n",
    "        \"\"\"\n",
    "\n",
    "        if not isinstance(month, int):\n",
    "            raise ValueError(\"month must be an int\")\n",
    "\n",
    "        if not cohort:\n",
    "            return self.m_customer_retention_rate(month=month)\n",
    "\n",
    "        error_message = \"cohort must be a date in the format yyyy-mm-01\"\n",
    "        try:\n",
    "            cohort_date = datetime.fromisoformat(cohort)\n",
    "\n",
    "            if cohort_date.day != 1:\n",
    "                raise ValueError(error_message)\n",
    "        except:\n",
    "            raise ValueError(error_message)\n",
    "\n",
    "        sql = \"\"\"\n",
    "                SELECT \n",
    "                (\n",
    "                    CAST(\n",
    "                    (\n",
    "                        SELECT COUNT(*) \n",
    "                        FROM CUSTOMER_COHORT \n",
    "                        WHERE MONTHS_SINCE_FIRST_PURCHASE = ? AND COHORT_DATE = ?\n",
    "                    ) AS REAL) / \n",
    "                    CAST(\n",
    "                        (\n",
    "                            SELECT COUNT(DISTINCT(USERID)) \n",
    "                            FROM CUSTOMER_COHORT\n",
    "                            WHERE COHORT_DATE = ?\n",
    "                        ) AS REAL)\n",
    "                ) * 100 AS M_RETENTION\n",
    "              \"\"\"\n",
    "        return self.execute_query(\n",
    "            query=sql,\n",
    "            param=(\n",
    "                month,\n",
    "                cohort,\n",
    "                cohort,\n",
    "            ),\n",
    "        )\n",
    "\n",
    "    def m_provider_retention_rate_by_cohort(self, month=1, cohort=None):\n",
    "        \"\"\"\n",
    "        Returns M provider retention rate by cohort. Defauts to MI retention rate of entire customers\n",
    "\n",
    "        Keyword arguments:\n",
    "\n",
    "        :cohort: -- the cohort in the form yyyy-mm-01\n",
    "        \"\"\"\n",
    "\n",
    "        if not isinstance(month, int):\n",
    "            raise ValueError(\"month must be an int\")\n",
    "\n",
    "        if not cohort:\n",
    "            return self.m_provider_retention_rate(month=month)\n",
    "\n",
    "        error_message = \"cohort must be a date in the format yyyy-mm-01\"\n",
    "        try:\n",
    "            cohort_date = datetime.fromisoformat(cohort)\n",
    "\n",
    "            if cohort_date.day != 1:\n",
    "                raise ValueError(error_message)\n",
    "        except:\n",
    "            raise ValueError(error_message)\n",
    "\n",
    "        sql = \"\"\"\n",
    "                SELECT \n",
    "                (\n",
    "                    CAST(\n",
    "                    (\n",
    "                        SELECT COUNT(*) \n",
    "                        FROM PROVIDER_COHORT \n",
    "                        WHERE MONTHS_SINCE_FIRST_PURCHASE = ? AND COHORT_DATE = ?\n",
    "                    ) AS REAL) / \n",
    "                    CAST(\n",
    "                        (\n",
    "                            SELECT COUNT(DISTINCT(providerId)) \n",
    "                            FROM PROVIDER_COHORT\n",
    "                            WHERE COHORT_DATE = ?\n",
    "                        ) AS REAL)\n",
    "                ) * 100 AS M_RETENTION\n",
    "              \"\"\"\n",
    "        return self.execute_query(\n",
    "            query=sql,\n",
    "            param=(\n",
    "                month,\n",
    "                cohort,\n",
    "                cohort,\n",
    "            ),\n",
    "        )\n",
    "    \n",
    "    def execute_query(self, query: str, param : tuple | None= None):\n",
    "        \"\"\"\n",
    "        Execute SQL query with parameters\n",
    "        \"\"\"\n",
    "\n",
    "        self._create_connection()\n",
    "        with self.conn as connection:\n",
    "            return pd.read_sql_query(sql=query, con=connection, params=param)\n",
    "\n",
    "    def _create_top_partners_by_sales_view(self):\n",
    "        \"\"\"\n",
    "        Creates top partners by sales view\n",
    "        \"\"\"\n",
    "\n",
    "        self._log(\"Creating top partners by sales view in database\")\n",
    "        script_path = os.path.join(self._script_path,\"top_partners.sql\")\n",
    "        self._execute_script(script_path)\n",
    "\n",
    "    def _create_partner_segment_order_quantity_view(self):\n",
    "        \"\"\"\n",
    "        Creates partner segments and order quantity view\n",
    "        \"\"\"\n",
    "\n",
    "        self._log(\"Creating partner segments and order quantity view in database\")\n",
    "        script_path = os.path.join(self._script_path,\"partner_segment_order_quantity.sql\")\n",
    "        self._execute_script(script_path)\n",
    "\n",
    "    def _create_customer_cohorts_view(self):\n",
    "        \"\"\"\n",
    "        Creates customer cohorts view\n",
    "        \"\"\"\n",
    "\n",
    "        self._log(\"Creating customer cohorts view in database\")\n",
    "        script_path = os.path.join(self._script_path,\"customer_cohort.sql\")\n",
    "        self._execute_script(script_path)\n",
    "    \n",
    "    def _create_provider_cohorts_view(self):\n",
    "        \"\"\"\n",
    "        Creates provider cohorts view\n",
    "        \"\"\"\n",
    "\n",
    "        self._log(\"Creating provider cohorts view in database\")\n",
    "        script_path = os.path.join(self._script_path,\"provider_cohort.sql\")\n",
    "        self._execute_script(script_path)\n",
    "\n",
    "    def _create_lifespan_frequency_sales_view(self):\n",
    "        \"\"\"\n",
    "        Creates Lifespan, Frequency, sales value view\n",
    "        \"\"\"\n",
    "\n",
    "        self._log(\"Creating Lifespan, Frequency, sales value view in database\")\n",
    "        script_path = os.path.join(self._script_path,\"lifespan_frequency_sales_value.sql\")\n",
    "        self._execute_script(script_path)\n",
    "\n",
    "    def _create_connection(self):\n",
    "        \"\"\"\n",
    "        Creates a database connection\n",
    "        \"\"\"\n",
    "\n",
    "        self.conn = sqlite3.connect(self.db_path)\n",
    "\n",
    "    def _close_connection(self):\n",
    "        \"\"\"\n",
    "        Closes existing database connection.\n",
    "        \"\"\"\n",
    "\n",
    "        if self.conn:\n",
    "            self.conn.commit()\n",
    "            self.conn.close()\n",
    "\n",
    "    def _execute_script(self, script_path):\n",
    "        \"\"\"\n",
    "        Execute SQL script from file\n",
    "        \"\"\"\n",
    "\n",
    "        with open(script_path, \"r\") as file:\n",
    "            sql_script = file.read()\n",
    "\n",
    "        cursor = self.conn.cursor()\n",
    "        cursor.executescript(sql_script)\n",
    "\n",
    "    def _log(self, text):\n",
    "        print(datetime.now().strftime(\"%H:%M:%S\"), text)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Executing the pipeline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13:06:44 Initializing pipeline\n",
      "13:06:44 Creating customer cohorts view in database\n",
      "13:06:44 Creating provider cohorts view in database\n",
      "13:06:44 Creating top partners by sales view in database\n",
      "13:06:44 Creating partner segments and order quantity view in database\n",
      "13:06:44 Creating Lifespan, Frequency, sales value view in database\n",
      "13:06:44 Done\n"
     ]
    }
   ],
   "source": [
    "pipeline = DataPipeline()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The Analyst can now use the pipeline to create presentation tables, as shown below\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Favourite partner segment:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>PARTNER_SEGMENT</th>\n",
       "      <th>SUM_ORDER_QUANTITY</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>meal</td>\n",
       "      <td>305254</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  PARTNER_SEGMENT  SUM_ORDER_QUANTITY\n",
       "0            meal              305254"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pipeline.customers_top_partner_segment()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Top 3 favourite partner segment:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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>PARTNER_SEGMENT</th>\n",
       "      <th>SUM_ORDER_QUANTITY</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>meal</td>\n",
       "      <td>305254</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>snack</td>\n",
       "      <td>63182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>grocery-bag</td>\n",
       "      <td>29884</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  PARTNER_SEGMENT  SUM_ORDER_QUANTITY\n",
       "0            meal              305254\n",
       "1           snack               63182\n",
       "2     grocery-bag               29884"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pipeline.customers_top_partner_segment(top_n = 3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Top N partners by sales:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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>providerId</th>\n",
       "      <th>currency</th>\n",
       "      <th>TOTAL_SALES</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7198110370745783236</td>\n",
       "      <td>sek</td>\n",
       "      <td>10917800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8312310143652755348</td>\n",
       "      <td>sek</td>\n",
       "      <td>7467750</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8097235958083241788</td>\n",
       "      <td>sek</td>\n",
       "      <td>2383700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3865474760205653333</td>\n",
       "      <td>sek</td>\n",
       "      <td>2223400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8084884958338058541</td>\n",
       "      <td>eur</td>\n",
       "      <td>1868140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>4734853230275691017</td>\n",
       "      <td>sek</td>\n",
       "      <td>1702100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>5305286819167536850</td>\n",
       "      <td>sek</td>\n",
       "      <td>1690500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1066258454353124935</td>\n",
       "      <td>sek</td>\n",
       "      <td>1568100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>7642201963087705313</td>\n",
       "      <td>sek</td>\n",
       "      <td>1472000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>4014236829817167297</td>\n",
       "      <td>sek</td>\n",
       "      <td>1457000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            providerId currency  TOTAL_SALES\n",
       "0  7198110370745783236      sek     10917800\n",
       "1  8312310143652755348      sek      7467750\n",
       "2  8097235958083241788      sek      2383700\n",
       "3  3865474760205653333      sek      2223400\n",
       "4  8084884958338058541      eur      1868140\n",
       "5  4734853230275691017      sek      1702100\n",
       "6  5305286819167536850      sek      1690500\n",
       "7  1066258454353124935      sek      1568100\n",
       "8  7642201963087705313      sek      1472000\n",
       "9  4014236829817167297      sek      1457000"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pipeline.top_n_partners(top_n=10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "M1 customer retention rate:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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>M_RETENTION</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>14.587527</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   M_RETENTION\n",
       "0    14.587527"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pipeline.m_customer_retention_rate()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "M2 customer retention rate:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "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>M_RETENTION</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>12.001656</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   M_RETENTION\n",
       "0    12.001656"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pipeline.m_customer_retention_rate(month=2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "M1 customer retention rate per cohort:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "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>M_RETENTION</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>29.511369</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   M_RETENTION\n",
       "0    29.511369"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pipeline.m_customer_retention_rate_by_cohort(month=1, cohort=\"2022-09-01\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "M1 provider retention"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "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>M_RETENTION</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>77.216611</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   M_RETENTION\n",
       "0    77.216611"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pipeline.m_provider_retention_rate(month=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "M2 provider retention rate:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "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>M_RETENTION</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>71.380471</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   M_RETENTION\n",
       "0    71.380471"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pipeline.m_provider_retention_rate(month=2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "M1 provider retention rate per cohort:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "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>M_RETENTION</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>93.338323</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   M_RETENTION\n",
       "0    93.338323"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pipeline.m_provider_retention_rate_by_cohort(month=1, cohort=\"2022-09-01\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "resq",
   "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.12.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}