{ "cells": [ { "cell_type": "markdown", "id": "d9e67c9f", "metadata": {}, "source": [ "." ] }, { "cell_type": "markdown", "id": "b63fc669", "metadata": {}, "source": [ "
Conversion Rate Optimization For E-COMMERCE
\n", "\tIntroducción
" ] }, { "cell_type": "markdown", "id": "610048f8", "metadata": {}, "source": [ "\n", "En este proyecto se aborda el caso de un comercio tipo ecommerce que presenta estancamiento en sus ventas. Se aplicaran técnicas analíticas a los registros transaccionales con el fin de obtener lineas de accion para el diseño de una estrategia basada en datos. Al final del preoyecto, se entregan resultados de diagnóstico en las áreas analizadas, seguido de las acciones estratégicas de resolución para incrementar el CRO.\n", "\n", "" ] }, { "cell_type": "markdown", "id": "02228b30", "metadata": {}, "source": [ "# **Objetivos del Proyecto**" ] }, { "cell_type": "markdown", "id": "e9322701", "metadata": {}, "source": [ "\n", " \n", "Impulsar el crecimieno en ventas del E-Commerce por medio de: \n", " \n", " * Analizar situación actual en base a tres áreas de enfoque: Ventas, Clientes y Productos.(Establecer el baseline).\n", "\n", " * Determinar las mejores estrategias de CRO (Conversion Rate Optimization) adecuadas en base a los datos obtenidos.\n", " \n", "" ] }, { "cell_type": "markdown", "id": "b93ee5b9", "metadata": {}, "source": [ "# Procesos ETL." ] }, { "cell_type": "markdown", "id": "29192a3b", "metadata": {}, "source": [ "\n", " \n", "**Herramientas:** \n", "El proyecto se aborda implementando Python por medio de Jupyter notebook, con los siguientes paquetes: Pandas, Numpy, Matplotlib, Scipy, Plotly, Seaborn y SqlAlchemy.
\n", "\n", "**Data Set:**\n", "La empresa cuenta con una base de datos SQL Server desde donde administra toda su información transaccional.
\n", "\n", "" ] }, { "cell_type": "markdown", "id": "1b5de814", "metadata": {}, "source": [ "## Importación de librerias." ] }, { "cell_type": "code", "execution_count": 1, "id": "844ddee9", "metadata": {}, "outputs": [], "source": [ "# Se importan los pquetes requeridos\n", "import numpy as np\n", "import pandas as pd\n", "import matplotlib.pyplot as plt\n", "%matplotlib inline\n", "import seaborn as sns\n", "import sqlalchemy as sa\n", "\n", "#Automcompletar rápido\n", "%config IPCompleter.greedy=True\n", "\n", "#Formato de display\n", "pd.options.display.float_format = '{:15.2f}'.format\n", "\n", "#Estilo de sns\n", "sns.set_theme(style=\"whitegrid\")" ] }, { "cell_type": "markdown", "id": "a6c4ac69", "metadata": {}, "source": [ "## Conexión a base de datos." ] }, { "cell_type": "code", "execution_count": 2, "id": "8906e429", "metadata": {}, "outputs": [], "source": [ "# Conexión a la base de datos y carga de los datos iniciales.\n", "\n", "con = sa.create_engine('sqlite:///../Datos/ecommerce.db')" ] }, { "cell_type": "code", "execution_count": 3, "id": "b75193b8", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "['2019-Dec', '2019-Nov', '2019-Oct', '2020-Feb', '2020-Jan']" ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Inspeccion de tablas existentes.\n", "\n", "from sqlalchemy import inspect\n", "insp = inspect(con)\n", "tablas = insp.get_table_names()\n", "tablas" ] }, { "cell_type": "code", "execution_count": 4, "id": "5aa381a5", "metadata": {}, "outputs": [], "source": [ "# Creando df a partir de las tablas.\n", "\n", "oct = pd.read_sql('2019-Oct', con)\n", "nov = pd.read_sql('2019-Nov', con)\n", "dic = pd.read_sql('2019-Dec', con)\n", "ene = pd.read_sql('2020-Jan', con)\n", "feb = pd.read_sql('2020-Feb', con)" ] }, { "cell_type": "code", "execution_count": 5, "id": "5f056356", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " | index | \n", "event_time | \n", "event_type | \n", "product_id | \n", "category_id | \n", "category_code | \n", "brand | \n", "price | \n", "user_id | \n", "user_session | \n", "
---|---|---|---|---|---|---|---|---|---|---|
0 | \n", "68 | \n", "2019-10-01 00:01:46 UTC | \n", "view | \n", "5843665 | \n", "1487580005092295511 | \n", "None | \n", "f.o.x | \n", "9.44 | \n", "462033176 | \n", "a18e0999-61a1-4218-8f8f-61ec1d375361 | \n", "
1 | \n", "72 | \n", "2019-10-01 00:01:55 UTC | \n", "cart | \n", "5868461 | \n", "1487580013069861041 | \n", "None | \n", "italwax | \n", "3.57 | \n", "514753614 | \n", "e2fecb2d-22d0-df2c-c661-15da44b3ccf1 | \n", "
2 | \n", "95 | \n", "2019-10-01 00:02:50 UTC | \n", "view | \n", "5877456 | \n", "1487580006300255120 | \n", "None | \n", "jessnail | \n", "122.22 | \n", "527418424 | \n", "86e77869-afbc-4dff-9aa2-6b7dd8c90770 | \n", "
3 | \n", "122 | \n", "2019-10-01 00:03:41 UTC | \n", "view | \n", "5649270 | \n", "1487580013749338323 | \n", "None | \n", "concept | \n", "6.19 | \n", "555448072 | \n", "b5f72ceb-0730-44de-a932-d16db62390df | \n", "
4 | \n", "124 | \n", "2019-10-01 00:03:44 UTC | \n", "view | \n", "18082 | \n", "1487580005411062629 | \n", "None | \n", "cnd | \n", "16.03 | \n", "552006247 | \n", "2d8f304b-de45-4e59-8f40-50c603843fe5 | \n", "
... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "
429785 | \n", "4156660 | \n", "2020-02-29 23:58:49 UTC | \n", "cart | \n", "5815662 | \n", "1487580006317032337 | \n", "None | \n", "None | \n", "0.92 | \n", "147995998 | \n", "5ff96629-3627-493e-a25b-5a871ec78c90 | \n", "
429786 | \n", "4156663 | \n", "2020-02-29 23:58:57 UTC | \n", "view | \n", "5815665 | \n", "1487580006317032337 | \n", "None | \n", "None | \n", "0.59 | \n", "147995998 | \n", "5ff96629-3627-493e-a25b-5a871ec78c90 | \n", "
429787 | \n", "4156668 | \n", "2020-02-29 23:59:05 UTC | \n", "cart | \n", "5815665 | \n", "1487580006317032337 | \n", "None | \n", "None | \n", "0.59 | \n", "147995998 | \n", "5ff96629-3627-493e-a25b-5a871ec78c90 | \n", "
429788 | \n", "4156675 | \n", "2020-02-29 23:59:28 UTC | \n", "view | \n", "5817692 | \n", "1487580010872045658 | \n", "None | \n", "None | \n", "0.79 | \n", "619841242 | \n", "18af673b-7fb9-4202-a66d-5c855bc0fd2d | \n", "
429789 | \n", "4156680 | \n", "2020-02-29 23:59:54 UTC | \n", "view | \n", "5716351 | \n", "1487580010872045658 | \n", "None | \n", "irisk | \n", "0.79 | \n", "619841242 | \n", "18af673b-7fb9-4202-a66d-5c855bc0fd2d | \n", "
2095076 rows × 10 columns
\n", "\n", " | fecha | \n", "evento | \n", "producto | \n", "categoria | \n", "precio | \n", "usuario | \n", "sesion | \n", "
---|---|---|---|---|---|---|---|
0 | \n", "2019-10-01 00:01:46 | \n", "view | \n", "5843665 | \n", "1487580005092295511 | \n", "9.44 | \n", "462033176 | \n", "a18e0999-61a1-4218-8f8f-61ec1d375361 | \n", "
1 | \n", "2019-10-01 00:01:55 | \n", "cart | \n", "5868461 | \n", "1487580013069861041 | \n", "3.57 | \n", "514753614 | \n", "e2fecb2d-22d0-df2c-c661-15da44b3ccf1 | \n", "
2 | \n", "2019-10-01 00:02:50 | \n", "view | \n", "5877456 | \n", "1487580006300255120 | \n", "122.22 | \n", "527418424 | \n", "86e77869-afbc-4dff-9aa2-6b7dd8c90770 | \n", "
3 | \n", "2019-10-01 00:03:41 | \n", "view | \n", "5649270 | \n", "1487580013749338323 | \n", "6.19 | \n", "555448072 | \n", "b5f72ceb-0730-44de-a932-d16db62390df | \n", "
4 | \n", "2019-10-01 00:03:44 | \n", "view | \n", "18082 | \n", "1487580005411062629 | \n", "16.03 | \n", "552006247 | \n", "2d8f304b-de45-4e59-8f40-50c603843fe5 | \n", "
... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "
429785 | \n", "2020-02-29 23:58:49 | \n", "cart | \n", "5815662 | \n", "1487580006317032337 | \n", "0.92 | \n", "147995998 | \n", "5ff96629-3627-493e-a25b-5a871ec78c90 | \n", "
429786 | \n", "2020-02-29 23:58:57 | \n", "view | \n", "5815665 | \n", "1487580006317032337 | \n", "0.59 | \n", "147995998 | \n", "5ff96629-3627-493e-a25b-5a871ec78c90 | \n", "
429787 | \n", "2020-02-29 23:59:05 | \n", "cart | \n", "5815665 | \n", "1487580006317032337 | \n", "0.59 | \n", "147995998 | \n", "5ff96629-3627-493e-a25b-5a871ec78c90 | \n", "
429788 | \n", "2020-02-29 23:59:28 | \n", "view | \n", "5817692 | \n", "1487580010872045658 | \n", "0.79 | \n", "619841242 | \n", "18af673b-7fb9-4202-a66d-5c855bc0fd2d | \n", "
429789 | \n", "2020-02-29 23:59:54 | \n", "view | \n", "5716351 | \n", "1487580010872045658 | \n", "0.79 | \n", "619841242 | \n", "18af673b-7fb9-4202-a66d-5c855bc0fd2d | \n", "
2074026 rows × 7 columns
\n", "\n", " | evento | \n", "producto | \n", "categoria | \n", "precio | \n", "usuario | \n", "sesion | \n", "date | \n", "año | \n", "mes | \n", "dia | \n", "hora | \n", "minuto | \n", "segundo | \n", "
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
fecha | \n", "\n", " | \n", " | \n", " | \n", " | \n", " | \n", " | \n", " | \n", " | \n", " | \n", " | \n", " | \n", " | \n", " |
2019-10-01 00:01:46 | \n", "view | \n", "5843665 | \n", "1487580005092295511 | \n", "9.44 | \n", "462033176 | \n", "a18e0999-61a1-4218-8f8f-61ec1d375361 | \n", "2019-10-01 | \n", "2019 | \n", "10 | \n", "1 | \n", "0 | \n", "1 | \n", "46 | \n", "
2019-10-01 00:01:55 | \n", "cart | \n", "5868461 | \n", "1487580013069861041 | \n", "3.57 | \n", "514753614 | \n", "e2fecb2d-22d0-df2c-c661-15da44b3ccf1 | \n", "2019-10-01 | \n", "2019 | \n", "10 | \n", "1 | \n", "0 | \n", "1 | \n", "55 | \n", "
2019-10-01 00:02:50 | \n", "view | \n", "5877456 | \n", "1487580006300255120 | \n", "122.22 | \n", "527418424 | \n", "86e77869-afbc-4dff-9aa2-6b7dd8c90770 | \n", "2019-10-01 | \n", "2019 | \n", "10 | \n", "1 | \n", "0 | \n", "2 | \n", "50 | \n", "
2019-10-01 00:03:41 | \n", "view | \n", "5649270 | \n", "1487580013749338323 | \n", "6.19 | \n", "555448072 | \n", "b5f72ceb-0730-44de-a932-d16db62390df | \n", "2019-10-01 | \n", "2019 | \n", "10 | \n", "1 | \n", "0 | \n", "3 | \n", "41 | \n", "
2019-10-01 00:03:44 | \n", "view | \n", "18082 | \n", "1487580005411062629 | \n", "16.03 | \n", "552006247 | \n", "2d8f304b-de45-4e59-8f40-50c603843fe5 | \n", "2019-10-01 | \n", "2019 | \n", "10 | \n", "1 | \n", "0 | \n", "3 | \n", "44 | \n", "
... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "
2020-02-29 23:58:49 | \n", "cart | \n", "5815662 | \n", "1487580006317032337 | \n", "0.92 | \n", "147995998 | \n", "5ff96629-3627-493e-a25b-5a871ec78c90 | \n", "2020-02-29 | \n", "2020 | \n", "2 | \n", "29 | \n", "23 | \n", "58 | \n", "49 | \n", "
2020-02-29 23:58:57 | \n", "view | \n", "5815665 | \n", "1487580006317032337 | \n", "0.59 | \n", "147995998 | \n", "5ff96629-3627-493e-a25b-5a871ec78c90 | \n", "2020-02-29 | \n", "2020 | \n", "2 | \n", "29 | \n", "23 | \n", "58 | \n", "57 | \n", "
2020-02-29 23:59:05 | \n", "cart | \n", "5815665 | \n", "1487580006317032337 | \n", "0.59 | \n", "147995998 | \n", "5ff96629-3627-493e-a25b-5a871ec78c90 | \n", "2020-02-29 | \n", "2020 | \n", "2 | \n", "29 | \n", "23 | \n", "59 | \n", "5 | \n", "
2020-02-29 23:59:28 | \n", "view | \n", "5817692 | \n", "1487580010872045658 | \n", "0.79 | \n", "619841242 | \n", "18af673b-7fb9-4202-a66d-5c855bc0fd2d | \n", "2020-02-29 | \n", "2020 | \n", "2 | \n", "29 | \n", "23 | \n", "59 | \n", "28 | \n", "
2020-02-29 23:59:54 | \n", "view | \n", "5716351 | \n", "1487580010872045658 | \n", "0.79 | \n", "619841242 | \n", "18af673b-7fb9-4202-a66d-5c855bc0fd2d | \n", "2020-02-29 | \n", "2020 | \n", "2 | \n", "29 | \n", "23 | \n", "59 | \n", "54 | \n", "
2074026 rows × 13 columns
\n", "\n", " | evento | \n", "producto | \n", "categoria | \n", "precio | \n", "usuario | \n", "sesion | \n", "date | \n", "año | \n", "mes | \n", "dia | \n", "hora | \n", "minuto | \n", "segundo | \n", "black_friday | \n", "san_valentin | \n", "
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
fecha | \n", "\n", " | \n", " | \n", " | \n", " | \n", " | \n", " | \n", " | \n", " | \n", " | \n", " | \n", " | \n", " | \n", " | \n", " | \n", " |
2019-10-01 00:01:46 | \n", "view | \n", "5843665 | \n", "1487580005092295511 | \n", "9.44 | \n", "462033176 | \n", "a18e0999-61a1-4218-8f8f-61ec1d375361 | \n", "2019-10-01 | \n", "2019 | \n", "10 | \n", "1 | \n", "0 | \n", "1 | \n", "46 | \n", "0 | \n", "0 | \n", "
2019-10-01 00:01:55 | \n", "cart | \n", "5868461 | \n", "1487580013069861041 | \n", "3.57 | \n", "514753614 | \n", "e2fecb2d-22d0-df2c-c661-15da44b3ccf1 | \n", "2019-10-01 | \n", "2019 | \n", "10 | \n", "1 | \n", "0 | \n", "1 | \n", "55 | \n", "0 | \n", "0 | \n", "
2019-10-01 00:02:50 | \n", "view | \n", "5877456 | \n", "1487580006300255120 | \n", "122.22 | \n", "527418424 | \n", "86e77869-afbc-4dff-9aa2-6b7dd8c90770 | \n", "2019-10-01 | \n", "2019 | \n", "10 | \n", "1 | \n", "0 | \n", "2 | \n", "50 | \n", "0 | \n", "0 | \n", "
2019-10-01 00:03:41 | \n", "view | \n", "5649270 | \n", "1487580013749338323 | \n", "6.19 | \n", "555448072 | \n", "b5f72ceb-0730-44de-a932-d16db62390df | \n", "2019-10-01 | \n", "2019 | \n", "10 | \n", "1 | \n", "0 | \n", "3 | \n", "41 | \n", "0 | \n", "0 | \n", "
2019-10-01 00:03:44 | \n", "view | \n", "18082 | \n", "1487580005411062629 | \n", "16.03 | \n", "552006247 | \n", "2d8f304b-de45-4e59-8f40-50c603843fe5 | \n", "2019-10-01 | \n", "2019 | \n", "10 | \n", "1 | \n", "0 | \n", "3 | \n", "44 | \n", "0 | \n", "0 | \n", "
\n", " | kpi | \n", "valor | \n", "
---|---|---|
0 | \n", "visitas | \n", "100.00 | \n", "
1 | \n", "carrito | \n", "59.75 | \n", "
2 | \n", "compra | \n", "22.20 | \n", "
\n", " | productos_tot_num | \n", "compras_tot_num | \n", "precio_medio_prod | \n", "ult_compra | \n", "gasto_tot | \n", "productos_por_compra | \n", "
---|---|---|---|---|---|---|
usuario | \n", "\n", " | \n", " | \n", " | \n", " | \n", " | \n", " |
25392526 | \n", "3 | \n", "1 | \n", "7.38 | \n", "2019-12-18 | \n", "22.14 | \n", "3.00 | \n", "
27756757 | \n", "1 | \n", "1 | \n", "20.63 | \n", "2020-01-27 | \n", "20.63 | \n", "1.00 | \n", "
50748978 | \n", "9 | \n", "1 | \n", "1.11 | \n", "2019-12-14 | \n", "10.01 | \n", "9.00 | \n", "
52747911 | \n", "3 | \n", "1 | \n", "7.67 | \n", "2019-10-10 | \n", "23.02 | \n", "3.00 | \n", "
65241811 | \n", "5 | \n", "1 | \n", "8.36 | \n", "2019-11-11 | \n", "41.79 | \n", "5.00 | \n", "
... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "
621995551 | \n", "5 | \n", "1 | \n", "2.09 | \n", "2020-02-29 | \n", "10.46 | \n", "5.00 | \n", "
622021687 | \n", "1 | \n", "1 | \n", "13.33 | \n", "2020-02-29 | \n", "13.33 | \n", "1.00 | \n", "
622041514 | \n", "3 | \n", "1 | \n", "0.63 | \n", "2020-02-29 | \n", "1.90 | \n", "3.00 | \n", "
622042698 | \n", "3 | \n", "1 | \n", "28.04 | \n", "2020-02-29 | \n", "84.13 | \n", "3.00 | \n", "
622065819 | \n", "4 | \n", "1 | \n", "5.12 | \n", "2020-02-29 | \n", "20.48 | \n", "4.00 | \n", "
11040 rows × 6 columns
\n", "\n", " | producto | \n", "view | \n", "cart | \n", "remove_from_cart | \n", "purchase | \n", "precio | \n", "
---|---|---|---|---|---|---|
0 | \n", "3752 | \n", "10.00 | \n", "0.00 | \n", "0.00 | \n", "0.00 | \n", "15.71 | \n", "
1 | \n", "3762 | \n", "258.00 | \n", "127.00 | \n", "59.00 | \n", "28.00 | \n", "19.29 | \n", "
2 | \n", "3763 | \n", "51.00 | \n", "10.00 | \n", "2.00 | \n", "2.00 | \n", "16.00 | \n", "
3 | \n", "3771 | \n", "9.00 | \n", "0.00 | \n", "0.00 | \n", "0.00 | \n", "15.08 | \n", "
4 | \n", "3774 | \n", "76.00 | \n", "26.00 | \n", "13.00 | \n", "7.00 | \n", "15.92 | \n", "
... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "
45322 | \n", "5932537 | \n", "1.00 | \n", "1.00 | \n", "0.00 | \n", "0.00 | \n", "1.43 | \n", "
45323 | \n", "5932538 | \n", "1.00 | \n", "0.00 | \n", "0.00 | \n", "0.00 | \n", "1.43 | \n", "
45324 | \n", "5932540 | \n", "2.00 | \n", "1.00 | \n", "0.00 | \n", "0.00 | \n", "1.43 | \n", "
45325 | \n", "5932578 | \n", "1.00 | \n", "0.00 | \n", "0.00 | \n", "0.00 | \n", "6.02 | \n", "
45326 | \n", "5932585 | \n", "2.00 | \n", "0.00 | \n", "0.00 | \n", "0.00 | \n", "6.33 | \n", "
45327 rows × 6 columns
\n", "\n", " | producto | \n", "view | \n", "cart | \n", "remove_from_cart | \n", "purchase | \n", "precio | \n", "
---|---|---|---|---|---|---|
0 | \n", "3752 | \n", "10.00 | \n", "0.00 | \n", "0.00 | \n", "0.00 | \n", "15.71 | \n", "
3 | \n", "3771 | \n", "9.00 | \n", "0.00 | \n", "0.00 | \n", "0.00 | \n", "15.08 | \n", "
6 | \n", "3790 | \n", "10.00 | \n", "0.00 | \n", "0.00 | \n", "0.00 | \n", "7.92 | \n", "
8 | \n", "3809 | \n", "2.00 | \n", "0.00 | \n", "0.00 | \n", "0.00 | \n", "12.54 | \n", "
9 | \n", "3812 | \n", "1.00 | \n", "0.00 | \n", "0.00 | \n", "0.00 | \n", "12.54 | \n", "
... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "
45322 | \n", "5932537 | \n", "1.00 | \n", "1.00 | \n", "0.00 | \n", "0.00 | \n", "1.43 | \n", "
45323 | \n", "5932538 | \n", "1.00 | \n", "0.00 | \n", "0.00 | \n", "0.00 | \n", "1.43 | \n", "
45324 | \n", "5932540 | \n", "2.00 | \n", "1.00 | \n", "0.00 | \n", "0.00 | \n", "1.43 | \n", "
45325 | \n", "5932578 | \n", "1.00 | \n", "0.00 | \n", "0.00 | \n", "0.00 | \n", "6.02 | \n", "
45326 | \n", "5932585 | \n", "2.00 | \n", "0.00 | \n", "0.00 | \n", "0.00 | \n", "6.33 | \n", "
21850 rows × 6 columns
\n", "