2. Analyzing Website Performance - Queries.ipynb

{
    "metadata": {
        "kernelspec": {
            "name": "SQL",
            "display_name": "SQL",
            "language": "sql"
        },
        "language_info": {
            "name": "sql",
            "version": ""
        }
    },
    "nbformat_minor": 2,
    "nbformat": 4,
    "cells": [
        {
            "cell_type": "code",
            "source": [
                "use mavenfuzzyfactory"
            ],
            "metadata": {
                "azdata_cell_guid": "fef7cff1-6275-497e-80f1-8ba5771511a7",
                "language": "sql"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.014"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 1
        },
        {
            "cell_type": "code",
            "source": [
                "select * from website_pageviews limit 10"
            ],
            "metadata": {
                "azdata_cell_guid": "f2a641b8-4a17-40e9-a53e-914a505cb6c9",
                "language": "sql"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(10 row(s) affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:01.019"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 2,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "website_pageview_id"
                                    },
                                    {
                                        "name": "created_at"
                                    },
                                    {
                                        "name": "website_session_id"
                                    },
                                    {
                                        "name": "pageview_url"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "website_pageview_id": "1",
                                    "created_at": "2012-03-19 08:04:16",
                                    "website_session_id": "1",
                                    "pageview_url": "/home"
                                },
                                {
                                    "website_pageview_id": "2",
                                    "created_at": "2012-03-19 08:16:49",
                                    "website_session_id": "2",
                                    "pageview_url": "/home"
                                },
                                {
                                    "website_pageview_id": "3",
                                    "created_at": "2012-03-19 08:26:55",
                                    "website_session_id": "3",
                                    "pageview_url": "/home"
                                },
                                {
                                    "website_pageview_id": "4",
                                    "created_at": "2012-03-19 08:37:33",
                                    "website_session_id": "4",
                                    "pageview_url": "/home"
                                },
                                {
                                    "website_pageview_id": "5",
                                    "created_at": "2012-03-19 09:00:55",
                                    "website_session_id": "5",
                                    "pageview_url": "/home"
                                },
                                {
                                    "website_pageview_id": "6",
                                    "created_at": "2012-03-19 09:05:46",
                                    "website_session_id": "6",
                                    "pageview_url": "/home"
                                },
                                {
                                    "website_pageview_id": "7",
                                    "created_at": "2012-03-19 09:06:27",
                                    "website_session_id": "7",
                                    "pageview_url": "/home"
                                },
                                {
                                    "website_pageview_id": "8",
                                    "created_at": "2012-03-19 09:10:08",
                                    "website_session_id": "6",
                                    "pageview_url": "/products"
                                },
                                {
                                    "website_pageview_id": "9",
                                    "created_at": "2012-03-19 09:10:52",
                                    "website_session_id": "6",
                                    "pageview_url": "/the-original-mr-fuzzy"
                                },
                                {
                                    "website_pageview_id": "10",
                                    "created_at": "2012-03-19 09:14:02",
                                    "website_session_id": "6",
                                    "pageview_url": "/cart"
                                }
                            ]
                        },
                        "text/html": [
                            "<table>",
                            "<tr><th>website_pageview_id</th><th>created_at</th><th>website_session_id</th><th>pageview_url</th></tr>",
                            "<tr><td>1</td><td>2012-03-19 08:04:16</td><td>1</td><td>/home</td></tr>",
                            "<tr><td>2</td><td>2012-03-19 08:16:49</td><td>2</td><td>/home</td></tr>",
                            "<tr><td>3</td><td>2012-03-19 08:26:55</td><td>3</td><td>/home</td></tr>",
                            "<tr><td>4</td><td>2012-03-19 08:37:33</td><td>4</td><td>/home</td></tr>",
                            "<tr><td>5</td><td>2012-03-19 09:00:55</td><td>5</td><td>/home</td></tr>",
                            "<tr><td>6</td><td>2012-03-19 09:05:46</td><td>6</td><td>/home</td></tr>",
                            "<tr><td>7</td><td>2012-03-19 09:06:27</td><td>7</td><td>/home</td></tr>",
                            "<tr><td>8</td><td>2012-03-19 09:10:08</td><td>6</td><td>/products</td></tr>",
                            "<tr><td>9</td><td>2012-03-19 09:10:52</td><td>6</td><td>/the-original-mr-fuzzy</td></tr>",
                            "<tr><td>10</td><td>2012-03-19 09:14:02</td><td>6</td><td>/cart</td></tr>",
                            "</table>"
                        ]
                    }
                }
            ],
            "execution_count": 2
        },
        {
            "cell_type": "markdown",
            "source": [
                "**Subject: Top Website Pages:** Could you help me get my head around the site by pulling <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">the most-viewed website pages, ranked by session volume?</span>"
            ],
            "metadata": {
                "language": "sql",
                "azdata_cell_guid": "d5463f1a-89c5-4ab9-9997-bef5c7c2ba03"
            },
            "attachments": {}
        },
        {
            "cell_type": "code",
            "source": [
                "select pageview_url, count(DISTINCT website_session_id) as session\n",
                "from website_pageviews\n",
                "where created_at < '2012-06-09'\n",
                "GROUP BY pageview_url\n",
                "ORDER BY session desc\n",
                "limit 10"
            ],
            "metadata": {
                "azdata_cell_guid": "efba9e85-fe77-489d-acd3-1a3cd30156ae",
                "language": "sql"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(7 row(s) affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:01.501"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 3,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "pageview_url"
                                    },
                                    {
                                        "name": "session"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "pageview_url": "/home",
                                    "session": "10403"
                                },
                                {
                                    "pageview_url": "/products",
                                    "session": "4239"
                                },
                                {
                                    "pageview_url": "/the-original-mr-fuzzy",
                                    "session": "3037"
                                },
                                {
                                    "pageview_url": "/cart",
                                    "session": "1306"
                                },
                                {
                                    "pageview_url": "/shipping",
                                    "session": "869"
                                },
                                {
                                    "pageview_url": "/billing",
                                    "session": "716"
                                },
                                {
                                    "pageview_url": "/thank-you-for-your-order",
                                    "session": "306"
                                }
                            ]
                        },
                        "text/html": [
                            "<table>",
                            "<tr><th>pageview_url</th><th>session</th></tr>",
                            "<tr><td>/home</td><td>10403</td></tr>",
                            "<tr><td>/products</td><td>4239</td></tr>",
                            "<tr><td>/the-original-mr-fuzzy</td><td>3037</td></tr>",
                            "<tr><td>/cart</td><td>1306</td></tr>",
                            "<tr><td>/shipping</td><td>869</td></tr>",
                            "<tr><td>/billing</td><td>716</td></tr>",
                            "<tr><td>/thank-you-for-your-order</td><td>306</td></tr>",
                            "</table>"
                        ]
                    }
                }
            ],
            "execution_count": 3
        },
        {
            "cell_type": "markdown",
            "source": [
                "**Subject: Top Entry Pages -** Would you be able to pull a list of the top entry pages? I <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">want to confirm where our users are hitting the site.&nbsp;</span>  <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">If you could pull all entry pages and rank them on entry&nbsp;</span>  <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">volume, that would be great.</span>"
            ],
            "metadata": {
                "language": "sql",
                "azdata_cell_guid": "ad089ff1-123a-4eff-9177-6913ea0ea81c"
            },
            "attachments": {}
        },
        {
            "cell_type": "code",
            "source": [
                "-- creating a temporary table for first_entry_page later to be used.\n",
                "CREATE TEMPORARY TABLE first_pageview\n",
                "SELECT website_session_id, min(website_pageview_id) as first_entry_page\n",
                "FROM website_pageviews\n",
                "where created_at < '2012-06-12'\n",
                "GROUP by website_session_id"
            ],
            "metadata": {
                "azdata_cell_guid": "52393fc7-9366-4c30-841b-79f147d99c3f",
                "language": "sql"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:03.078"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 4
        },
        {
            "cell_type": "code",
            "source": [
                "-- drop TEMPORARY table first_pageview"
            ],
            "metadata": {
                "language": "sql",
                "azdata_cell_guid": "ad025df6-62c8-4db0-9a98-a7b670c953c3"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 5
        },
        {
            "cell_type": "code",
            "source": [
                "select * from first_pageview limit 10"
            ],
            "metadata": {
                "azdata_cell_guid": "cc2e6f9a-052c-413c-ab1d-954152b848bf",
                "language": "sql"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(10 row(s) affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:01.006"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 6,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "website_session_id"
                                    },
                                    {
                                        "name": "first_entry_page"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "website_session_id": "1",
                                    "first_entry_page": "1"
                                },
                                {
                                    "website_session_id": "2",
                                    "first_entry_page": "2"
                                },
                                {
                                    "website_session_id": "3",
                                    "first_entry_page": "3"
                                },
                                {
                                    "website_session_id": "4",
                                    "first_entry_page": "4"
                                },
                                {
                                    "website_session_id": "5",
                                    "first_entry_page": "5"
                                },
                                {
                                    "website_session_id": "6",
                                    "first_entry_page": "6"
                                },
                                {
                                    "website_session_id": "7",
                                    "first_entry_page": "7"
                                },
                                {
                                    "website_session_id": "8",
                                    "first_entry_page": "12"
                                },
                                {
                                    "website_session_id": "9",
                                    "first_entry_page": "14"
                                },
                                {
                                    "website_session_id": "10",
                                    "first_entry_page": "15"
                                }
                            ]
                        },
                        "text/html": [
                            "<table>",
                            "<tr><th>website_session_id</th><th>first_entry_page</th></tr>",
                            "<tr><td>1</td><td>1</td></tr>",
                            "<tr><td>2</td><td>2</td></tr>",
                            "<tr><td>3</td><td>3</td></tr>",
                            "<tr><td>4</td><td>4</td></tr>",
                            "<tr><td>5</td><td>5</td></tr>",
                            "<tr><td>6</td><td>6</td></tr>",
                            "<tr><td>7</td><td>7</td></tr>",
                            "<tr><td>8</td><td>12</td></tr>",
                            "<tr><td>9</td><td>14</td></tr>",
                            "<tr><td>10</td><td>15</td></tr>",
                            "</table>"
                        ]
                    }
                }
            ],
            "execution_count": 6
        },
        {
            "cell_type": "code",
            "source": [
                "-- IMPORTANT: \n",
                "-- LEFT TABLE will be the View we created it's logical to have info for these only\n",
                "-- We have to join on pageviews it will contain info about more pageview to count session\n",
                "select website_pageviews.pageview_url, count(DISTINCT first_pageview.website_session_id) as sessions_hitting_this_landing_page\n",
                "from first_pageview LEFT JOIN website_pageviews\n",
                "on website_pageviews.website_pageview_id = first_pageview.first_entry_page\n",
                "where created_at < '2012-06-12'\n",
                "GROUP BY pageview_url\n",
                "order by 2 desc"
            ],
            "metadata": {
                "azdata_cell_guid": "24d63d68-f1b5-4232-b1e9-add53b527d3a",
                "language": "sql"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(1 row(s) affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:01.059"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 7,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "pageview_url"
                                    },
                                    {
                                        "name": "sessions_hitting_this_landing_page"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "pageview_url": "/home",
                                    "sessions_hitting_this_landing_page": "10714"
                                }
                            ]
                        },
                        "text/html": [
                            "<table>",
                            "<tr><th>pageview_url</th><th>sessions_hitting_this_landing_page</th></tr>",
                            "<tr><td>/home</td><td>10714</td></tr>",
                            "</table>"
                        ]
                    }
                }
            ],
            "execution_count": 7
        },
        {
            "cell_type": "markdown",
            "source": [
                "**Subject: Bounce Rate Analysis:** The other day you showed us that all of our traffic is landing <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">on the homepage right now. We should check how that&nbsp;</span> <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">landing page is performing.</span>\n",
                "\n",
                "Can you pull bounce rates for traffic landing on the <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">homepage? I would like to see three numbers...Sessions,&nbsp;</span> <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">Bounced Sessions, and % of Sessions which Bounced&nbsp;</span> <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">(aka \"Bounce Rate\").</span>"
            ],
            "metadata": {
                "language": "sql",
                "azdata_cell_guid": "31cc828c-d72f-4a83-b854-794323b925e1"
            },
            "attachments": {}
        },
        {
            "cell_type": "markdown",
            "source": [
                "### **BOUNCE RATE: The Concept for Bounce Rate Calculation is -- If the session has only one pageview which means the user only landed on one page and then existed the website ( Hence Bounced).**"
            ],
            "metadata": {
                "language": "sql",
                "azdata_cell_guid": "304c3367-a7b8-4d09-bca4-1705d5c841d7"
            },
            "attachments": {}
        },
        {
            "cell_type": "code",
            "source": [
                "-- BUSINESS CONTEXT: we want to see landing page performance for a certain time period (bounce rate)\n",
                "-- STEP 1: find the first website_pageview_id for relevant sessions -- IMPORTANT for finding the first landing page.\n",
                "-- STEP 2: identify the landing page of each session\n",
                "-- STEP 3: counting pageviews for each session, to identify \"bounces\" -- IMPORTANT it's counting pageviews for each session later to identify bounce\n",
                "-- STEP 4: summarizing total sessions and bounced sessions, by LP"
            ],
            "metadata": {
                "azdata_cell_guid": "4db7af70-9140-434f-94cf-f9e579c6c920",
                "language": "sql",
                "tags": []
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 8
        },
        {
            "cell_type": "code",
            "source": [
                "-- finding the first or minimum website pageview id associated with each session and storing in views\n",
                "-- since we have to get the date from the specific table therefore the join on website session\n",
                "create TEMPORARY table first_pageviews_demo\n",
                "select website_sessions.website_session_id, \n",
                "min(website_pageviews.website_pageview_id) as first_pageview_id\n",
                "from website_pageviews \n",
                "join website_sessions \n",
                "on website_pageviews.website_session_id = website_sessions.website_session_id\n",
                "where website_sessions.created_at < '2012-06-14'\n",
                "group by website_sessions.website_session_id"
            ],
            "metadata": {
                "azdata_cell_guid": "efeb0808-ec1b-499c-b5c2-0a12ae17b88f",
                "language": "sql"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.391"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 9
        },
        {
            "cell_type": "code",
            "source": [
                "select * from first_pageviews_demo limit 10"
            ],
            "metadata": {
                "azdata_cell_guid": "1412ed1d-a721-4bf5-b2bc-e679f6a1b74a",
                "language": "sql"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(10 row(s) affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:01.006"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 10,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "website_session_id"
                                    },
                                    {
                                        "name": "first_pageview_id"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "website_session_id": "1",
                                    "first_pageview_id": "1"
                                },
                                {
                                    "website_session_id": "2",
                                    "first_pageview_id": "2"
                                },
                                {
                                    "website_session_id": "3",
                                    "first_pageview_id": "3"
                                },
                                {
                                    "website_session_id": "4",
                                    "first_pageview_id": "4"
                                },
                                {
                                    "website_session_id": "5",
                                    "first_pageview_id": "5"
                                },
                                {
                                    "website_session_id": "6",
                                    "first_pageview_id": "6"
                                },
                                {
                                    "website_session_id": "7",
                                    "first_pageview_id": "7"
                                },
                                {
                                    "website_session_id": "8",
                                    "first_pageview_id": "12"
                                },
                                {
                                    "website_session_id": "9",
                                    "first_pageview_id": "14"
                                },
                                {
                                    "website_session_id": "10",
                                    "first_pageview_id": "15"
                                }
                            ]
                        },
                        "text/html": [
                            "<table>",
                            "<tr><th>website_session_id</th><th>first_pageview_id</th></tr>",
                            "<tr><td>1</td><td>1</td></tr>",
                            "<tr><td>2</td><td>2</td></tr>",
                            "<tr><td>3</td><td>3</td></tr>",
                            "<tr><td>4</td><td>4</td></tr>",
                            "<tr><td>5</td><td>5</td></tr>",
                            "<tr><td>6</td><td>6</td></tr>",
                            "<tr><td>7</td><td>7</td></tr>",
                            "<tr><td>8</td><td>12</td></tr>",
                            "<tr><td>9</td><td>14</td></tr>",
                            "<tr><td>10</td><td>15</td></tr>",
                            "</table>"
                        ]
                    }
                }
            ],
            "execution_count": 10
        },
        {
            "cell_type": "code",
            "source": [
                "-- landing page to each session -- On clause is for mapping sessions (but )\n",
                "-- data on using session_id > first_pageview_id for joining -- because we have all the sessions but only selected pageview\n",
                "-- first_pageview_demo has repeated pageview but no repeated session check by using csv\n",
                "create TEMPORARY table sessions_w_landing_page_demo\n",
                "select first_pageviews_demo.website_session_id, website_pageviews.pageview_url \n",
                "from first_pageviews_demo \n",
                "LEFT JOIN website_pageviews \n",
                "ON first_pageviews_demo.first_pageview_id = website_pageviews.website_pageview_id -- website pageview is the landing page"
            ],
            "metadata": {
                "azdata_cell_guid": "ab1649ac-6ee8-4063-8cc9-5cf7de562e6c",
                "language": "sql"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.066"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 11
        },
        {
            "cell_type": "code",
            "source": [
                "select * from sessions_w_landing_page_demo limit 10"
            ],
            "metadata": {
                "azdata_cell_guid": "fef741d1-f962-4fb5-9f79-a7d81170a699",
                "language": "sql"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(10 row(s) affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:01.006"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 12,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "website_session_id"
                                    },
                                    {
                                        "name": "pageview_url"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "website_session_id": "1",
                                    "pageview_url": "/home"
                                },
                                {
                                    "website_session_id": "2",
                                    "pageview_url": "/home"
                                },
                                {
                                    "website_session_id": "3",
                                    "pageview_url": "/home"
                                },
                                {
                                    "website_session_id": "4",
                                    "pageview_url": "/home"
                                },
                                {
                                    "website_session_id": "5",
                                    "pageview_url": "/home"
                                },
                                {
                                    "website_session_id": "6",
                                    "pageview_url": "/home"
                                },
                                {
                                    "website_session_id": "7",
                                    "pageview_url": "/home"
                                },
                                {
                                    "website_session_id": "8",
                                    "pageview_url": "/home"
                                },
                                {
                                    "website_session_id": "9",
                                    "pageview_url": "/home"
                                },
                                {
                                    "website_session_id": "10",
                                    "pageview_url": "/home"
                                }
                            ]
                        },
                        "text/html": [
                            "<table>",
                            "<tr><th>website_session_id</th><th>pageview_url</th></tr>",
                            "<tr><td>1</td><td>/home</td></tr>",
                            "<tr><td>2</td><td>/home</td></tr>",
                            "<tr><td>3</td><td>/home</td></tr>",
                            "<tr><td>4</td><td>/home</td></tr>",
                            "<tr><td>5</td><td>/home</td></tr>",
                            "<tr><td>6</td><td>/home</td></tr>",
                            "<tr><td>7</td><td>/home</td></tr>",
                            "<tr><td>8</td><td>/home</td></tr>",
                            "<tr><td>9</td><td>/home</td></tr>",
                            "<tr><td>10</td><td>/home</td></tr>",
                            "</table>"
                        ]
                    }
                }
            ],
            "execution_count": 12
        },
        {
            "cell_type": "code",
            "source": [
                "-- table to include count of pageview per session\n",
                "create TEMPORARY table bounced_sessions_only\n",
                "select sessions_w_landing_page_demo.website_session_id, sessions_w_landing_page_demo.pageview_url, count(website_pageviews.website_pageview_id) as count_of_pages_viewed\n",
                "from sessions_w_landing_page_demo\n",
                "left join website_pageviews\n",
                "on sessions_w_landing_page_demo.website_session_id = website_pageviews.website_session_id\n",
                "GROUP BY 1, 2\n",
                "having count(website_pageviews.website_pageview_id) = 1"
            ],
            "metadata": {
                "azdata_cell_guid": "9781d3f3-1651-46d2-91db-a414d8982e18",
                "language": "sql"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.084"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 13
        },
        {
            "cell_type": "code",
            "source": [
                "select * from bounced_sessions_only limit 10"
            ],
            "metadata": {
                "language": "sql",
                "azdata_cell_guid": "cb810c3c-64e2-44a6-9202-027bbb1854d6"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(10 row(s) affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:01.006"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 14,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "website_session_id"
                                    },
                                    {
                                        "name": "pageview_url"
                                    },
                                    {
                                        "name": "count_of_pages_viewed"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "website_session_id": "1",
                                    "pageview_url": "/home",
                                    "count_of_pages_viewed": "1"
                                },
                                {
                                    "website_session_id": "2",
                                    "pageview_url": "/home",
                                    "count_of_pages_viewed": "1"
                                },
                                {
                                    "website_session_id": "3",
                                    "pageview_url": "/home",
                                    "count_of_pages_viewed": "1"
                                },
                                {
                                    "website_session_id": "4",
                                    "pageview_url": "/home",
                                    "count_of_pages_viewed": "1"
                                },
                                {
                                    "website_session_id": "5",
                                    "pageview_url": "/home",
                                    "count_of_pages_viewed": "1"
                                },
                                {
                                    "website_session_id": "7",
                                    "pageview_url": "/home",
                                    "count_of_pages_viewed": "1"
                                },
                                {
                                    "website_session_id": "8",
                                    "pageview_url": "/home",
                                    "count_of_pages_viewed": "1"
                                },
                                {
                                    "website_session_id": "9",
                                    "pageview_url": "/home",
                                    "count_of_pages_viewed": "1"
                                },
                                {
                                    "website_session_id": "10",
                                    "pageview_url": "/home",
                                    "count_of_pages_viewed": "1"
                                },
                                {
                                    "website_session_id": "11",
                                    "pageview_url": "/home",
                                    "count_of_pages_viewed": "1"
                                }
                            ]
                        },
                        "text/html": [
                            "<table>",
                            "<tr><th>website_session_id</th><th>pageview_url</th><th>count_of_pages_viewed</th></tr>",
                            "<tr><td>1</td><td>/home</td><td>1</td></tr>",
                            "<tr><td>2</td><td>/home</td><td>1</td></tr>",
                            "<tr><td>3</td><td>/home</td><td>1</td></tr>",
                            "<tr><td>4</td><td>/home</td><td>1</td></tr>",
                            "<tr><td>5</td><td>/home</td><td>1</td></tr>",
                            "<tr><td>7</td><td>/home</td><td>1</td></tr>",
                            "<tr><td>8</td><td>/home</td><td>1</td></tr>",
                            "<tr><td>9</td><td>/home</td><td>1</td></tr>",
                            "<tr><td>10</td><td>/home</td><td>1</td></tr>",
                            "<tr><td>11</td><td>/home</td><td>1</td></tr>",
                            "</table>"
                        ]
                    }
                }
            ],
            "execution_count": 14
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT\n",
                "sessions_w_landing_page_demo.pageview_url,\n",
                "count( DISTINCT sessions_w_landing_page_demo.website_session_id) as sessions,\n",
                "count( DISTINCT bounced_sessions_only.website_session_id) AS bounced,\n",
                "count( DISTINCT bounced_sessions_only.website_session_id)/count( DISTINCT sessions_w_landing_page_demo.website_session_id) as bounce_rate\n",
                "FROM sessions_w_landing_page_demo\n",
                "LEFT JOIN bounced_sessions_only\n",
                "ON sessions_w_landing_page_demo.website_session_id = bounced_sessions_only.website_session_id\n",
                "GROUP BY 1\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "5a3229b4-16a4-4749-9d23-832e5405c7a3",
                "language": "sql"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(1 row(s) affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:01.043"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 15,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "pageview_url"
                                    },
                                    {
                                        "name": "sessions"
                                    },
                                    {
                                        "name": "bounced"
                                    },
                                    {
                                        "name": "bounce_rate"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "pageview_url": "/home",
                                    "sessions": "11048",
                                    "bounced": "6538",
                                    "bounce_rate": "0.5918"
                                }
                            ]
                        },
                        "text/html": [
                            "<table>",
                            "<tr><th>pageview_url</th><th>sessions</th><th>bounced</th><th>bounce_rate</th></tr>",
                            "<tr><td>/home</td><td>11048</td><td>6538</td><td>0.5918</td></tr>",
                            "</table>"
                        ]
                    }
                }
            ],
            "execution_count": 15
        },
        {
            "cell_type": "markdown",
            "source": [
                "**Subject: Help Analyzing LP Test**\n",
                "\n",
                "Based on your bounce rate analysis, we ran a new custom <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">landing page (/lander-1) in a 50/50 test against the&nbsp;</span> <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">homepage (/home) for our gsearch nonbrand traffic.&nbsp;</span> <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">Can you pull bounce rates for the two groups so we can&nbsp;</span> <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">evaluate the new page? Make sure to just look at the time&nbsp;</span> <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">period where /lander-1 was getting traffic, so that it is a fair&nbsp;</span> <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">comparison.</span>"
            ],
            "metadata": {
                "language": "sql",
                "azdata_cell_guid": "e08fb849-5472-4d29-8970-39b3c2d25af4"
            },
            "attachments": {}
        },
        {
            "cell_type": "code",
            "source": [
                "-- STEP 0: find out when the new page /lander launched\n",
                "-- STEP 1: finding the first website_pageview_id for relevant sessions\n",
                "-- STEP 2: identifying the landing page of each session\n",
                "-- STEP 3: counting pageviews for each session, to identify \"bounces\"\n",
                "-- STEP 4: summarizing total sessions and bounced sessions, by LP"
            ],
            "metadata": {
                "language": "sql",
                "azdata_cell_guid": "3dc2a96d-d016-4c45-a4da-f5a13ee7f768"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 16
        },
        {
            "cell_type": "code",
            "source": [
                "select min(website_pageview_id) as first_pageview_id, min(created_at) as first_created_at from website_pageviews where pageview_url = '/lander-1'"
            ],
            "metadata": {
                "azdata_cell_guid": "ca0a77ee-0453-4403-b06c-33cd32c9d58d",
                "language": "sql"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(1 row(s) affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:01.447"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 17,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "first_pageview_id"
                                    },
                                    {
                                        "name": "first_created_at"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "first_pageview_id": "23504",
                                    "first_created_at": "2012-06-19 00:35:54"
                                }
                            ]
                        },
                        "text/html": [
                            "<table>",
                            "<tr><th>first_pageview_id</th><th>first_created_at</th></tr>",
                            "<tr><td>23504</td><td>2012-06-19 00:35:54</td></tr>",
                            "</table>"
                        ]
                    }
                }
            ],
            "execution_count": 17
        },
        {
            "cell_type": "code",
            "source": [
                "-- finding the first pageview for the each session with filters on date and others.\n",
                "-- will use the first_pageview_id to limit the home page session time also.\n",
                "create TEMPORARY table first_test_pageviews\n",
                "select website_pageviews.website_session_id,\n",
                "min(website_pageviews.website_pageview_id) as first_pageview\n",
                "from website_pageviews\n",
                "JOIN website_sessions\n",
                "On website_pageviews.website_session_id = website_sessions.website_session_id\n",
                "and website_sessions.created_at < '2012-07-28'\n",
                "and website_pageviews.website_pageview_id > 23504\n",
                "and utm_source = 'gsearch'\n",
                "and utm_campaign = 'nonbrand'\n",
                "GROUP by website_pageviews.website_session_id"
            ],
            "metadata": {
                "azdata_cell_guid": "4664cb2a-efee-4969-9bd7-872102f1a9bd",
                "language": "sql"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.406"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 18
        },
        {
            "cell_type": "code",
            "source": [
                "select * from first_test_pageviews limit 10;"
            ],
            "metadata": {
                "azdata_cell_guid": "70753090-21ab-4771-9122-96ab0ea467a3",
                "language": "sql"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(10 row(s) affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:01.006"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 19,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "website_session_id"
                                    },
                                    {
                                        "name": "first_pageview"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "website_session_id": "11684",
                                    "first_pageview": "23505"
                                },
                                {
                                    "website_session_id": "11685",
                                    "first_pageview": "23506"
                                },
                                {
                                    "website_session_id": "11686",
                                    "first_pageview": "23507"
                                },
                                {
                                    "website_session_id": "11687",
                                    "first_pageview": "23509"
                                },
                                {
                                    "website_session_id": "11688",
                                    "first_pageview": "23510"
                                },
                                {
                                    "website_session_id": "11689",
                                    "first_pageview": "23511"
                                },
                                {
                                    "website_session_id": "11690",
                                    "first_pageview": "23514"
                                },
                                {
                                    "website_session_id": "11691",
                                    "first_pageview": "23515"
                                },
                                {
                                    "website_session_id": "11692",
                                    "first_pageview": "23517"
                                },
                                {
                                    "website_session_id": "11693",
                                    "first_pageview": "23518"
                                }
                            ]
                        },
                        "text/html": [
                            "<table>",
                            "<tr><th>website_session_id</th><th>first_pageview</th></tr>",
                            "<tr><td>11684</td><td>23505</td></tr>",
                            "<tr><td>11685</td><td>23506</td></tr>",
                            "<tr><td>11686</td><td>23507</td></tr>",
                            "<tr><td>11687</td><td>23509</td></tr>",
                            "<tr><td>11688</td><td>23510</td></tr>",
                            "<tr><td>11689</td><td>23511</td></tr>",
                            "<tr><td>11690</td><td>23514</td></tr>",
                            "<tr><td>11691</td><td>23515</td></tr>",
                            "<tr><td>11692</td><td>23517</td></tr>",
                            "<tr><td>11693</td><td>23518</td></tr>",
                            "</table>"
                        ]
                    }
                }
            ],
            "execution_count": 19
        },
        {
            "cell_type": "code",
            "source": [
                "-- finding the first url for each pageview\n",
                "-- since we have to find the first landing page for every pageview therefore on clause will have pageview_id rather than session_id\n",
                "create TEMPORARY table nonbrand_test_sessions_w_landing_page\n",
                "select first_test_pageviews.website_session_id, \n",
                "website_pageviews.pageview_url as landing_page\n",
                "from first_test_pageviews\n",
                "left join website_pageviews\n",
                "on first_test_pageviews.first_pageview = website_pageviews.website_pageview_id\n",
                "where website_pageviews.pageview_url in ('/home','/lander-1')"
            ],
            "metadata": {
                "azdata_cell_guid": "8021ba98-da05-4eba-b983-5f7caec3d5a3",
                "language": "sql"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.041"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 20
        },
        {
            "cell_type": "code",
            "source": [
                "select * from nonbrand_test_sessions_w_landing_page limit 10"
            ],
            "metadata": {
                "azdata_cell_guid": "322336c4-c338-4313-a79b-e82eb338039f",
                "language": "sql"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(10 row(s) affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:01.006"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 21,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "website_session_id"
                                    },
                                    {
                                        "name": "landing_page"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "website_session_id": "11684",
                                    "landing_page": "/home"
                                },
                                {
                                    "website_session_id": "11685",
                                    "landing_page": "/lander-1"
                                },
                                {
                                    "website_session_id": "11686",
                                    "landing_page": "/lander-1"
                                },
                                {
                                    "website_session_id": "11687",
                                    "landing_page": "/home"
                                },
                                {
                                    "website_session_id": "11688",
                                    "landing_page": "/home"
                                },
                                {
                                    "website_session_id": "11689",
                                    "landing_page": "/lander-1"
                                },
                                {
                                    "website_session_id": "11690",
                                    "landing_page": "/home"
                                },
                                {
                                    "website_session_id": "11691",
                                    "landing_page": "/lander-1"
                                },
                                {
                                    "website_session_id": "11692",
                                    "landing_page": "/lander-1"
                                },
                                {
                                    "website_session_id": "11693",
                                    "landing_page": "/lander-1"
                                }
                            ]
                        },
                        "text/html": [
                            "<table>",
                            "<tr><th>website_session_id</th><th>landing_page</th></tr>",
                            "<tr><td>11684</td><td>/home</td></tr>",
                            "<tr><td>11685</td><td>/lander-1</td></tr>",
                            "<tr><td>11686</td><td>/lander-1</td></tr>",
                            "<tr><td>11687</td><td>/home</td></tr>",
                            "<tr><td>11688</td><td>/home</td></tr>",
                            "<tr><td>11689</td><td>/lander-1</td></tr>",
                            "<tr><td>11690</td><td>/home</td></tr>",
                            "<tr><td>11691</td><td>/lander-1</td></tr>",
                            "<tr><td>11692</td><td>/lander-1</td></tr>",
                            "<tr><td>11693</td><td>/lander-1</td></tr>",
                            "</table>"
                        ]
                    }
                }
            ],
            "execution_count": 21
        },
        {
            "cell_type": "code",
            "source": [
                "-- finding count of pageviews for each session and filtering to 1 to know which session where bounce\n",
                "create TEMPORARY table nonbrand_test_bounced_sessions\n",
                "select nonbrand_test_sessions_w_landing_page.website_session_id, \n",
                "nonbrand_test_sessions_w_landing_page.landing_page, \n",
                "count(website_pageviews.website_pageview_id) as count_of_pages_viewed\n",
                "from nonbrand_test_sessions_w_landing_page\n",
                "left join website_pageviews\n",
                "on nonbrand_test_sessions_w_landing_page.website_session_id = website_pageviews.website_session_id\n",
                "GROUP BY 1, 2\n",
                "having count(website_pageviews.website_pageview_id) = 1"
            ],
            "metadata": {
                "azdata_cell_guid": "17ce616f-e4ee-4a9c-8e39-012ce928a086",
                "language": "sql"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.078"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 22
        },
        {
            "cell_type": "code",
            "source": [
                "select * from nonbrand_test_bounced_sessions limit 10"
            ],
            "metadata": {
                "azdata_cell_guid": "b161734c-b1f9-494e-9605-f212f972ced1",
                "language": "sql"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(10 row(s) affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:01.001"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 23,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "website_session_id"
                                    },
                                    {
                                        "name": "landing_page"
                                    },
                                    {
                                        "name": "count_of_pages_viewed"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "website_session_id": "11684",
                                    "landing_page": "/home",
                                    "count_of_pages_viewed": "1"
                                },
                                {
                                    "website_session_id": "11685",
                                    "landing_page": "/lander-1",
                                    "count_of_pages_viewed": "1"
                                },
                                {
                                    "website_session_id": "11687",
                                    "landing_page": "/home",
                                    "count_of_pages_viewed": "1"
                                },
                                {
                                    "website_session_id": "11688",
                                    "landing_page": "/home",
                                    "count_of_pages_viewed": "1"
                                },
                                {
                                    "website_session_id": "11690",
                                    "landing_page": "/home",
                                    "count_of_pages_viewed": "1"
                                },
                                {
                                    "website_session_id": "11692",
                                    "landing_page": "/lander-1",
                                    "count_of_pages_viewed": "1"
                                },
                                {
                                    "website_session_id": "11696",
                                    "landing_page": "/home",
                                    "count_of_pages_viewed": "1"
                                },
                                {
                                    "website_session_id": "11697",
                                    "landing_page": "/lander-1",
                                    "count_of_pages_viewed": "1"
                                },
                                {
                                    "website_session_id": "11698",
                                    "landing_page": "/lander-1",
                                    "count_of_pages_viewed": "1"
                                },
                                {
                                    "website_session_id": "11701",
                                    "landing_page": "/lander-1",
                                    "count_of_pages_viewed": "1"
                                }
                            ]
                        },
                        "text/html": [
                            "<table>",
                            "<tr><th>website_session_id</th><th>landing_page</th><th>count_of_pages_viewed</th></tr>",
                            "<tr><td>11684</td><td>/home</td><td>1</td></tr>",
                            "<tr><td>11685</td><td>/lander-1</td><td>1</td></tr>",
                            "<tr><td>11687</td><td>/home</td><td>1</td></tr>",
                            "<tr><td>11688</td><td>/home</td><td>1</td></tr>",
                            "<tr><td>11690</td><td>/home</td><td>1</td></tr>",
                            "<tr><td>11692</td><td>/lander-1</td><td>1</td></tr>",
                            "<tr><td>11696</td><td>/home</td><td>1</td></tr>",
                            "<tr><td>11697</td><td>/lander-1</td><td>1</td></tr>",
                            "<tr><td>11698</td><td>/lander-1</td><td>1</td></tr>",
                            "<tr><td>11701</td><td>/lander-1</td><td>1</td></tr>",
                            "</table>"
                        ]
                    }
                }
            ],
            "execution_count": 23
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT\n",
                "nonbrand_test_sessions_w_landing_page.landing_page,\n",
                "count( DISTINCT nonbrand_test_sessions_w_landing_page.website_session_id) as sessions,\n",
                "count( DISTINCT nonbrand_test_bounced_sessions.website_session_id) AS bounced,\n",
                "count( DISTINCT nonbrand_test_bounced_sessions.website_session_id)/count( DISTINCT nonbrand_test_sessions_w_landing_page.website_session_id) as bounce_rate\n",
                "FROM nonbrand_test_sessions_w_landing_page\n",
                "LEFT JOIN nonbrand_test_bounced_sessions\n",
                "ON nonbrand_test_sessions_w_landing_page.website_session_id = nonbrand_test_bounced_sessions.website_session_id\n",
                "GROUP BY 1"
            ],
            "metadata": {
                "azdata_cell_guid": "5233428f-62d4-4e46-98db-974815c7c939",
                "language": "sql"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(2 row(s) affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:01.022"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 24,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "landing_page"
                                    },
                                    {
                                        "name": "sessions"
                                    },
                                    {
                                        "name": "bounced"
                                    },
                                    {
                                        "name": "bounce_rate"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "landing_page": "/home",
                                    "sessions": "2261",
                                    "bounced": "1319",
                                    "bounce_rate": "0.5834"
                                },
                                {
                                    "landing_page": "/lander-1",
                                    "sessions": "2315",
                                    "bounced": "1232",
                                    "bounce_rate": "0.5322"
                                }
                            ]
                        },
                        "text/html": [
                            "<table>",
                            "<tr><th>landing_page</th><th>sessions</th><th>bounced</th><th>bounce_rate</th></tr>",
                            "<tr><td>/home</td><td>2261</td><td>1319</td><td>0.5834</td></tr>",
                            "<tr><td>/lander-1</td><td>2315</td><td>1232</td><td>0.5322</td></tr>",
                            "</table>"
                        ]
                    }
                }
            ],
            "execution_count": 24
        },
        {
            "cell_type": "markdown",
            "source": [
                "**Subject: Landing Page Trend Analysis**\n",
                "\n",
                "<span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">Could you pull the volume of paid search nonbrand traffic&nbsp;</span> <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">landing on /home and /lander-1, trended weekly since June&nbsp;</span> <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">1st? I want to confirm the traffic is all routed correctly.&nbsp;</span> <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">Could you also pull our overall paid search bounce rate&nbsp;</span> <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">trended weekly? I want to make sure the lander change has&nbsp;</span> <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">improved the overall picture.</span>"
            ],
            "metadata": {
                "language": "sql",
                "azdata_cell_guid": "6fd48523-8a40-4e85-8879-38f43cbd471b"
            },
            "attachments": {}
        },
        {
            "cell_type": "code",
            "source": [
                "create TEMPORARY table sessions_w_first_pv_id_and_view_count\n",
                "select website_pageviews.website_session_id,\n",
                "min(website_pageviews.website_pageview_id) as first_pageview,\n",
                "count(website_pageviews.website_pageview_id) as count_pageviews\n",
                "from website_pageviews\n",
                "JOIN website_sessions\n",
                "On website_pageviews.website_session_id = website_sessions.website_session_id\n",
                "and website_sessions.created_at > '2012-06-01'\n",
                "and website_sessions.created_at < '2012-08-31'\n",
                "and utm_source = 'gsearch'\n",
                "and utm_campaign = 'nonbrand'\n",
                "GROUP by website_pageviews.website_session_id"
            ],
            "metadata": {
                "azdata_cell_guid": "e5ea5563-9731-4802-b452-27bc21c0155b",
                "language": "sql"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.410"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 25
        },
        {
            "cell_type": "code",
            "source": [
                "select * from sessions_w_first_pv_id_and_view_count limit 10"
            ],
            "metadata": {
                "azdata_cell_guid": "37246794-0f9a-4dea-95eb-561bba7e736e",
                "language": "sql"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(10 row(s) affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:01.002"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 26,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "website_session_id"
                                    },
                                    {
                                        "name": "first_pageview"
                                    },
                                    {
                                        "name": "count_pageviews"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "website_session_id": "9350",
                                    "first_pageview": "18598",
                                    "count_pageviews": "3"
                                },
                                {
                                    "website_session_id": "9351",
                                    "first_pageview": "18600",
                                    "count_pageviews": "3"
                                },
                                {
                                    "website_session_id": "9352",
                                    "first_pageview": "18601",
                                    "count_pageviews": "4"
                                },
                                {
                                    "website_session_id": "9354",
                                    "first_pageview": "18611",
                                    "count_pageviews": "1"
                                },
                                {
                                    "website_session_id": "9356",
                                    "first_pageview": "18616",
                                    "count_pageviews": "6"
                                },
                                {
                                    "website_session_id": "9357",
                                    "first_pageview": "18622",
                                    "count_pageviews": "1"
                                },
                                {
                                    "website_session_id": "9358",
                                    "first_pageview": "18623",
                                    "count_pageviews": "3"
                                },
                                {
                                    "website_session_id": "9359",
                                    "first_pageview": "18626",
                                    "count_pageviews": "1"
                                },
                                {
                                    "website_session_id": "9360",
                                    "first_pageview": "18627",
                                    "count_pageviews": "1"
                                },
                                {
                                    "website_session_id": "9361",
                                    "first_pageview": "18628",
                                    "count_pageviews": "1"
                                }
                            ]
                        },
                        "text/html": [
                            "<table>",
                            "<tr><th>website_session_id</th><th>first_pageview</th><th>count_pageviews</th></tr>",
                            "<tr><td>9350</td><td>18598</td><td>3</td></tr>",
                            "<tr><td>9351</td><td>18600</td><td>3</td></tr>",
                            "<tr><td>9352</td><td>18601</td><td>4</td></tr>",
                            "<tr><td>9354</td><td>18611</td><td>1</td></tr>",
                            "<tr><td>9356</td><td>18616</td><td>6</td></tr>",
                            "<tr><td>9357</td><td>18622</td><td>1</td></tr>",
                            "<tr><td>9358</td><td>18623</td><td>3</td></tr>",
                            "<tr><td>9359</td><td>18626</td><td>1</td></tr>",
                            "<tr><td>9360</td><td>18627</td><td>1</td></tr>",
                            "<tr><td>9361</td><td>18628</td><td>1</td></tr>",
                            "</table>"
                        ]
                    }
                }
            ],
            "execution_count": 26
        },
        {
            "cell_type": "code",
            "source": [
                "CREATE TEMPORARY TABLE sessions_w_counts_lander_and_created_at\n",
                "SELECT\n",
                "sessions_w_first_pv_id_and_view_count.website_session_id,\n",
                "sessions_w_first_pv_id_and_view_count.first_pageview,\n",
                "sessions_w_first_pv_id_and_view_count.count_pageviews,\n",
                "website_pageviews.pageview_url AS landing_page,\n",
                "website_pageviews.created_at AS session_created_at\n",
                "FROM sessions_w_first_pv_id_and_view_count\n",
                "LEFT JOIN website_pageviews\n",
                "ON sessions_w_first_pv_id_and_view_count.first_pageview = website_pageviews.website_pageview_id"
            ],
            "metadata": {
                "azdata_cell_guid": "e10d552a-babf-4079-af0e-031a8c00248c",
                "language": "sql"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.081"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 27
        },
        {
            "cell_type": "code",
            "source": [
                "select * from sessions_w_counts_lander_and_created_at limit 10"
            ],
            "metadata": {
                "azdata_cell_guid": "0f0d210d-049e-47c9-8239-1b48ebf4f2d8",
                "language": "sql"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(10 row(s) affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:01.006"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 28,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "website_session_id"
                                    },
                                    {
                                        "name": "first_pageview"
                                    },
                                    {
                                        "name": "count_pageviews"
                                    },
                                    {
                                        "name": "landing_page"
                                    },
                                    {
                                        "name": "session_created_at"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "website_session_id": "9350",
                                    "first_pageview": "18598",
                                    "count_pageviews": "3",
                                    "landing_page": "/home",
                                    "session_created_at": "2012-06-01 00:05:11"
                                },
                                {
                                    "website_session_id": "9351",
                                    "first_pageview": "18600",
                                    "count_pageviews": "3",
                                    "landing_page": "/home",
                                    "session_created_at": "2012-06-01 00:06:39"
                                },
                                {
                                    "website_session_id": "9352",
                                    "first_pageview": "18601",
                                    "count_pageviews": "4",
                                    "landing_page": "/home",
                                    "session_created_at": "2012-06-01 00:08:27"
                                },
                                {
                                    "website_session_id": "9354",
                                    "first_pageview": "18611",
                                    "count_pageviews": "1",
                                    "landing_page": "/home",
                                    "session_created_at": "2012-06-01 01:08:43"
                                },
                                {
                                    "website_session_id": "9356",
                                    "first_pageview": "18616",
                                    "count_pageviews": "6",
                                    "landing_page": "/home",
                                    "session_created_at": "2012-06-01 01:37:31"
                                },
                                {
                                    "website_session_id": "9357",
                                    "first_pageview": "18622",
                                    "count_pageviews": "1",
                                    "landing_page": "/home",
                                    "session_created_at": "2012-06-01 02:29:33"
                                },
                                {
                                    "website_session_id": "9358",
                                    "first_pageview": "18623",
                                    "count_pageviews": "3",
                                    "landing_page": "/home",
                                    "session_created_at": "2012-06-01 02:39:16"
                                },
                                {
                                    "website_session_id": "9359",
                                    "first_pageview": "18626",
                                    "count_pageviews": "1",
                                    "landing_page": "/home",
                                    "session_created_at": "2012-06-01 03:12:34"
                                },
                                {
                                    "website_session_id": "9360",
                                    "first_pageview": "18627",
                                    "count_pageviews": "1",
                                    "landing_page": "/home",
                                    "session_created_at": "2012-06-01 04:09:37"
                                },
                                {
                                    "website_session_id": "9361",
                                    "first_pageview": "18628",
                                    "count_pageviews": "1",
                                    "landing_page": "/home",
                                    "session_created_at": "2012-06-01 04:10:53"
                                }
                            ]
                        },
                        "text/html": [
                            "<table>",
                            "<tr><th>website_session_id</th><th>first_pageview</th><th>count_pageviews</th><th>landing_page</th><th>session_created_at</th></tr>",
                            "<tr><td>9350</td><td>18598</td><td>3</td><td>/home</td><td>2012-06-01 00:05:11</td></tr>",
                            "<tr><td>9351</td><td>18600</td><td>3</td><td>/home</td><td>2012-06-01 00:06:39</td></tr>",
                            "<tr><td>9352</td><td>18601</td><td>4</td><td>/home</td><td>2012-06-01 00:08:27</td></tr>",
                            "<tr><td>9354</td><td>18611</td><td>1</td><td>/home</td><td>2012-06-01 01:08:43</td></tr>",
                            "<tr><td>9356</td><td>18616</td><td>6</td><td>/home</td><td>2012-06-01 01:37:31</td></tr>",
                            "<tr><td>9357</td><td>18622</td><td>1</td><td>/home</td><td>2012-06-01 02:29:33</td></tr>",
                            "<tr><td>9358</td><td>18623</td><td>3</td><td>/home</td><td>2012-06-01 02:39:16</td></tr>",
                            "<tr><td>9359</td><td>18626</td><td>1</td><td>/home</td><td>2012-06-01 03:12:34</td></tr>",
                            "<tr><td>9360</td><td>18627</td><td>1</td><td>/home</td><td>2012-06-01 04:09:37</td></tr>",
                            "<tr><td>9361</td><td>18628</td><td>1</td><td>/home</td><td>2012-06-01 04:10:53</td></tr>",
                            "</table>"
                        ]
                    }
                }
            ],
            "execution_count": 28
        },
        {
            "cell_type": "code",
            "source": [
                "select \n",
                "min(DATE(session_created_at)),\n",
                "COUNT(DISTINCT CASE WHEN count_pageviews = 1 THEN website_session_id ELSE NULL END)/ \n",
                "COUNT(DISTINCT website_session_id) AS bounce_rate,\n",
                "count(DISTINCT case when landing_page='/home' then website_session_id else null end) as home_session,\n",
                "count(DISTINCT case when landing_page='/lander-1' then website_session_id else null end) as lander_session\n",
                "from sessions_w_counts_lander_and_created_at\n",
                "GROUP by YEARWEEK(session_created_at)"
            ],
            "metadata": {
                "azdata_cell_guid": "5e721764-8703-4570-bce8-fd0c4789c3f2",
                "language": "sql"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(14 row(s) affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:01.037"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 29,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "min(DATE(session_created_at))"
                                    },
                                    {
                                        "name": "bounce_rate"
                                    },
                                    {
                                        "name": "home_session"
                                    },
                                    {
                                        "name": "lander_session"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "min(DATE(session_created_at))": "2012-06-01",
                                    "bounce_rate": "0.6057",
                                    "home_session": "175",
                                    "lander_session": "0"
                                },
                                {
                                    "min(DATE(session_created_at))": "2012-06-03",
                                    "bounce_rate": "0.5871",
                                    "home_session": "792",
                                    "lander_session": "0"
                                },
                                {
                                    "min(DATE(session_created_at))": "2012-06-10",
                                    "bounce_rate": "0.6160",
                                    "home_session": "875",
                                    "lander_session": "0"
                                },
                                {
                                    "min(DATE(session_created_at))": "2012-06-17",
                                    "bounce_rate": "0.5582",
                                    "home_session": "492",
                                    "lander_session": "350"
                                },
                                {
                                    "min(DATE(session_created_at))": "2012-06-24",
                                    "bounce_rate": "0.5828",
                                    "home_session": "369",
                                    "lander_session": "386"
                                },
                                {
                                    "min(DATE(session_created_at))": "2012-07-01",
                                    "bounce_rate": "0.5821",
                                    "home_session": "392",
                                    "lander_session": "388"
                                },
                                {
                                    "min(DATE(session_created_at))": "2012-07-08",
                                    "bounce_rate": "0.5668",
                                    "home_session": "390",
                                    "lander_session": "411"
                                },
                                {
                                    "min(DATE(session_created_at))": "2012-07-15",
                                    "bounce_rate": "0.5424",
                                    "home_session": "429",
                                    "lander_session": "421"
                                },
                                {
                                    "min(DATE(session_created_at))": "2012-07-22",
                                    "bounce_rate": "0.5138",
                                    "home_session": "402",
                                    "lander_session": "394"
                                },
                                {
                                    "min(DATE(session_created_at))": "2012-07-29",
                                    "bounce_rate": "0.4971",
                                    "home_session": "33",
                                    "lander_session": "995"
                                },
                                {
                                    "min(DATE(session_created_at))": "2012-08-05",
                                    "bounce_rate": "0.5382",
                                    "home_session": "0",
                                    "lander_session": "1087"
                                },
                                {
                                    "min(DATE(session_created_at))": "2012-08-12",
                                    "bounce_rate": "0.5140",
                                    "home_session": "0",
                                    "lander_session": "998"
                                },
                                {
                                    "min(DATE(session_created_at))": "2012-08-19",
                                    "bounce_rate": "0.5010",
                                    "home_session": "0",
                                    "lander_session": "1012"
                                },
                                {
                                    "min(DATE(session_created_at))": "2012-08-26",
                                    "bounce_rate": "0.5378",
                                    "home_session": "0",
                                    "lander_session": "833"
                                }
                            ]
                        },
                        "text/html": [
                            "<table>",
                            "<tr><th>min(DATE(session_created_at))</th><th>bounce_rate</th><th>home_session</th><th>lander_session</th></tr>",
                            "<tr><td>2012-06-01</td><td>0.6057</td><td>175</td><td>0</td></tr>",
                            "<tr><td>2012-06-03</td><td>0.5871</td><td>792</td><td>0</td></tr>",
                            "<tr><td>2012-06-10</td><td>0.6160</td><td>875</td><td>0</td></tr>",
                            "<tr><td>2012-06-17</td><td>0.5582</td><td>492</td><td>350</td></tr>",
                            "<tr><td>2012-06-24</td><td>0.5828</td><td>369</td><td>386</td></tr>",
                            "<tr><td>2012-07-01</td><td>0.5821</td><td>392</td><td>388</td></tr>",
                            "<tr><td>2012-07-08</td><td>0.5668</td><td>390</td><td>411</td></tr>",
                            "<tr><td>2012-07-15</td><td>0.5424</td><td>429</td><td>421</td></tr>",
                            "<tr><td>2012-07-22</td><td>0.5138</td><td>402</td><td>394</td></tr>",
                            "<tr><td>2012-07-29</td><td>0.4971</td><td>33</td><td>995</td></tr>",
                            "<tr><td>2012-08-05</td><td>0.5382</td><td>0</td><td>1087</td></tr>",
                            "<tr><td>2012-08-12</td><td>0.5140</td><td>0</td><td>998</td></tr>",
                            "<tr><td>2012-08-19</td><td>0.5010</td><td>0</td><td>1012</td></tr>",
                            "<tr><td>2012-08-26</td><td>0.5378</td><td>0</td><td>833</td></tr>",
                            "</table>"
                        ]
                    }
                }
            ],
            "execution_count": 29
        },
        {
            "cell_type": "markdown",
            "source": [
                "### **CONVERSION FUNNEL ANALYSIS**"
            ],
            "metadata": {
                "language": "sql",
                "azdata_cell_guid": "987ced49-9c0d-48b3-a506-bbe3a7bd816c"
            },
            "attachments": {}
        },
        {
            "cell_type": "code",
            "source": [
                "-- Demo on Building Conversion Funnels\n",
                "-- BUSINESS CONTEXT\n",
                "-- we want to build a mini conversion funnel, from /lander-2 to /cart\n",
                "-- we want to know how many people reach each step, and also dropoff rates\n",
                "-- for simplicity of the demo, we're looking at /lander-2 traffic only\n",
                "-- for simplicity of the demo, we're lookign at customers who like Mr Fuzzy only\n",
                "\n",
                "-- STEP 1: select all pageviews for relevant session\n",
                "-- STEP 2: identify each relevant pageview as the specific funnel step\n",
                "-- STEP 3: create the session-level corversion funnel view\n",
                "-- STEP 4: aggregate the data to assess funnel performance\n",
                "\n",
                "-- first I will show you all of the pageviews we care about\n",
                "-- then, I will remove the comments from my flag columns one by one to show you what that"
            ],
            "metadata": {
                "azdata_cell_guid": "de36fd33-9ded-4cf1-af35-5f194f47e077",
                "language": "sql",
                "tags": []
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "**Subject: Help Analyzing Conversion Funnels**\n",
                "\n",
                "I'd like to understand where we lose our search visitors <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">between the new /lander-1 page and placing an order. Can&nbsp;</span> <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">you build us a full conversion funnel, analyzing how many&nbsp;</span> <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">customers make it to each step?&nbsp;</span> <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">Start with /lander-1 and build the funnel all the way to our&nbsp;</span> <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">thank you page. Please use data since August 5th.</span>"
            ],
            "metadata": {
                "language": "sql",
                "azdata_cell_guid": "36beb582-669f-46da-b45f-665f8ee90eee"
            },
            "attachments": {}
        },
        {
            "cell_type": "code",
            "source": [
                "select website_sessions.website_session_id,\n",
                "case when  pageview_url = '/lander-1' then 1 else 0 end as products_page,\n",
                "case when  pageview_url = '/the-original-mr-fuzzy' then 1 else 0 end as mrfuzzy_page,\n",
                "case when  pageview_url = '/cart' then 1 else 0 end as cart_page,\n",
                "case when  pageview_url = '/billing' then 1 else 0 end as billing_page,\n",
                "case when  pageview_url = '/thank-you-for-your-order' then 1 else 0 end as thankyou_page\n",
                "from website_pageviews \n",
                "join website_sessions \n",
                "on website_pageviews.website_session_id = website_sessions.website_session_id\n",
                "where website_sessions.created_at > '2012-08-05'\n",
                "and website_sessions.created_at < '2012-09-06'\n",
                "and utm_source = 'gsearch'\n",
                "and utm_campaign = 'nonbrand'\n",
                "order BY\n",
                "website_sessions.website_session_id\n",
                "limit 10"
            ],
            "metadata": {
                "azdata_cell_guid": "e294b5d6-68ef-46a7-b340-e456cac4fb3f",
                "language": "sql"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(10 row(s) affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:01.029"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 46,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "website_session_id"
                                    },
                                    {
                                        "name": "products_page"
                                    },
                                    {
                                        "name": "mrfuzzy_page"
                                    },
                                    {
                                        "name": "cart_page"
                                    },
                                    {
                                        "name": "billing_page"
                                    },
                                    {
                                        "name": "thankyou_page"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "website_session_id": "18243",
                                    "products_page": "1",
                                    "mrfuzzy_page": "0",
                                    "cart_page": "0",
                                    "billing_page": "0",
                                    "thankyou_page": "0"
                                },
                                {
                                    "website_session_id": "18244",
                                    "products_page": "1",
                                    "mrfuzzy_page": "0",
                                    "cart_page": "0",
                                    "billing_page": "0",
                                    "thankyou_page": "0"
                                },
                                {
                                    "website_session_id": "18244",
                                    "products_page": "0",
                                    "mrfuzzy_page": "0",
                                    "cart_page": "0",
                                    "billing_page": "0",
                                    "thankyou_page": "0"
                                },
                                {
                                    "website_session_id": "18244",
                                    "products_page": "0",
                                    "mrfuzzy_page": "1",
                                    "cart_page": "0",
                                    "billing_page": "0",
                                    "thankyou_page": "0"
                                },
                                {
                                    "website_session_id": "18244",
                                    "products_page": "0",
                                    "mrfuzzy_page": "0",
                                    "cart_page": "1",
                                    "billing_page": "0",
                                    "thankyou_page": "0"
                                },
                                {
                                    "website_session_id": "18244",
                                    "products_page": "0",
                                    "mrfuzzy_page": "0",
                                    "cart_page": "0",
                                    "billing_page": "0",
                                    "thankyou_page": "0"
                                },
                                {
                                    "website_session_id": "18244",
                                    "products_page": "0",
                                    "mrfuzzy_page": "0",
                                    "cart_page": "0",
                                    "billing_page": "1",
                                    "thankyou_page": "0"
                                },
                                {
                                    "website_session_id": "18245",
                                    "products_page": "1",
                                    "mrfuzzy_page": "0",
                                    "cart_page": "0",
                                    "billing_page": "0",
                                    "thankyou_page": "0"
                                },
                                {
                                    "website_session_id": "18246",
                                    "products_page": "1",
                                    "mrfuzzy_page": "0",
                                    "cart_page": "0",
                                    "billing_page": "0",
                                    "thankyou_page": "0"
                                },
                                {
                                    "website_session_id": "18246",
                                    "products_page": "0",
                                    "mrfuzzy_page": "0",
                                    "cart_page": "0",
                                    "billing_page": "0",
                                    "thankyou_page": "0"
                                }
                            ]
                        },
                        "text/html": [
                            "<table>",
                            "<tr><th>website_session_id</th><th>products_page</th><th>mrfuzzy_page</th><th>cart_page</th><th>billing_page</th><th>thankyou_page</th></tr>",
                            "<tr><td>18243</td><td>1</td><td>0</td><td>0</td><td>0</td><td>0</td></tr>",
                            "<tr><td>18244</td><td>1</td><td>0</td><td>0</td><td>0</td><td>0</td></tr>",
                            "<tr><td>18244</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td></tr>",
                            "<tr><td>18244</td><td>0</td><td>1</td><td>0</td><td>0</td><td>0</td></tr>",
                            "<tr><td>18244</td><td>0</td><td>0</td><td>1</td><td>0</td><td>0</td></tr>",
                            "<tr><td>18244</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td></tr>",
                            "<tr><td>18244</td><td>0</td><td>0</td><td>0</td><td>1</td><td>0</td></tr>",
                            "<tr><td>18245</td><td>1</td><td>0</td><td>0</td><td>0</td><td>0</td></tr>",
                            "<tr><td>18246</td><td>1</td><td>0</td><td>0</td><td>0</td><td>0</td></tr>",
                            "<tr><td>18246</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td></tr>",
                            "</table>"
                        ]
                    }
                }
            ],
            "execution_count": 46
        },
        {
            "cell_type": "code",
            "source": [
                "select count(website_session_id) as sessions,\n",
                "count(DISTINCT case when products_page = 1 then website_session_id else null end) as to_products,\n",
                "count(DISTINCT case when mrfuzzy_page = 1 then website_session_id else null end) as to_mrfuzzy,\n",
                "count(DISTINCT case when cart_page = 1 then website_session_id else null end) as to_cart,\n",
                "count(DISTINCT case when billing_page = 1 then website_session_id else null end) as to_shipping,\n",
                "count(DISTINCT case when thankyou_page = 1 then website_session_id else null end) as to_thankyou\n",
                "FROM\n",
                "(select website_sessions.website_session_id,\n",
                "case when  pageview_url = '/lander-1' then 1 else 0 end as products_page,\n",
                "case when  pageview_url = '/the-original-mr-fuzzy' then 1 else 0 end as mrfuzzy_page,\n",
                "case when  pageview_url = '/cart' then 1 else 0 end as cart_page,\n",
                "case when  pageview_url = '/billing' then 1 else 0 end as billing_page,\n",
                "case when  pageview_url = '/thank-you-for-your-order' then 1 else 0 end as thankyou_page\n",
                "from website_pageviews \n",
                "join website_sessions \n",
                "on website_pageviews.website_session_id = website_sessions.website_session_id\n",
                "where website_sessions.created_at > '2012-08-05'\n",
                "and website_sessions.created_at < '2012-09-05'\n",
                "and utm_source = 'gsearch'\n",
                "and utm_campaign = 'nonbrand'\n",
                "order BY\n",
                "website_sessions.website_session_id ) as a"
            ],
            "metadata": {
                "azdata_cell_guid": "e553c708-cf40-42c4-afee-37744a584c6d",
                "language": "sql"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(1 row(s) affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:01.325"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 47,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "sessions"
                                    },
                                    {
                                        "name": "to_products"
                                    },
                                    {
                                        "name": "to_mrfuzzy"
                                    },
                                    {
                                        "name": "to_cart"
                                    },
                                    {
                                        "name": "to_shipping"
                                    },
                                    {
                                        "name": "to_thankyou"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "sessions": "9832",
                                    "to_products": "4493",
                                    "to_mrfuzzy": "1567",
                                    "to_cart": "683",
                                    "to_shipping": "361",
                                    "to_thankyou": "158"
                                }
                            ]
                        },
                        "text/html": [
                            "<table>",
                            "<tr><th>sessions</th><th>to_products</th><th>to_mrfuzzy</th><th>to_cart</th><th>to_shipping</th><th>to_thankyou</th></tr>",
                            "<tr><td>9832</td><td>4493</td><td>1567</td><td>683</td><td>361</td><td>158</td></tr>",
                            "</table>"
                        ]
                    }
                }
            ],
            "execution_count": 47
        },
        {
            "cell_type": "markdown",
            "source": [
                "**Subject: Conversion Funnel Test Results**\n",
                "\n",
                "We tested an updated billing page based on your funnel <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">analysis. Can you take a look and see whether /billing-2 is&nbsp;</span> <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">doing any better than the original /billing page?&nbsp;</span> <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">We're wondering what % of sessions on those pages end up&nbsp;</span> <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">placing an order. FYI - we ran this test for all traffic, not just&nbsp;</span> <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">for our search visitors.</span>"
            ],
            "metadata": {
                "language": "sql",
                "azdata_cell_guid": "dc81579a-918f-489d-9460-2358364b802e"
            },
            "attachments": {}
        },
        {
            "cell_type": "code",
            "source": [
                "-- first time when billing was seen\n",
                "select pageview_url, website_pageview_id, created_at from website_pageviews\n",
                "where pageview_url= '/billing-2'\n",
                "order by 2\n",
                "limit 1"
            ],
            "metadata": {
                "azdata_cell_guid": "20e71617-0c53-4a3b-a704-9eca65b7eaf2",
                "language": "sql"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(1 row(s) affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:01.039"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 64,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "pageview_url"
                                    },
                                    {
                                        "name": "website_pageview_id"
                                    },
                                    {
                                        "name": "created_at"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "pageview_url": "/billing-2",
                                    "website_pageview_id": "53550",
                                    "created_at": "2012-09-10 00:13:05"
                                }
                            ]
                        },
                        "text/html": [
                            "<table>",
                            "<tr><th>pageview_url</th><th>website_pageview_id</th><th>created_at</th></tr>",
                            "<tr><td>/billing-2</td><td>53550</td><td>2012-09-10 00:13:05</td></tr>",
                            "</table>"
                        ]
                    }
                }
            ],
            "execution_count": 64
        },
        {
            "cell_type": "code",
            "source": [
                "select pageview_url,\n",
                "count(website_pageviews.website_session_id) as sessions,\n",
                "count(order_id) as orders,\n",
                "count(order_id)/count(website_pageviews.website_session_id) as billing_to_order_rt\n",
                "FROM\n",
                "website_pageviews\n",
                "left join orders\n",
                "on website_pageviews.website_session_id = orders.website_session_id\n",
                "where website_pageviews.website_pageview_id > 53550\n",
                "and website_pageviews.created_at < '2012-11-10'\n",
                "and pageview_url in ('/billing','/billing-2')\n",
                "group by 1"
            ],
            "metadata": {
                "azdata_cell_guid": "631e0cc5-4875-443a-b8e8-1a6f4bef1719",
                "language": "sql"
            },
            "outputs": [
{
    "output_type": "display_data",
    "data": {
        "text/html": "(2 row(s) affected)"
    },
    "metadata": {}
}, {
    "output_type": "display_data",
    "data": {
        "text/html": "Total execution time: 00:00:01.435"
    },
    "metadata": {}
}, {
    "output_type": "execute_result",
    "metadata": {},
    "execution_count": 77,
    "data": {
        "application/vnd.dataresource+json": {
            "schema": {
                "fields": [
                    {
                        "name": "pageview_url"
                    },
                    {
                        "name": "sessions"
                    },
                    {
                        "name": "orders"
                    },
                    {
                        "name": "billing_to_order_rt"
                    }
                ]
            },
            "data": [
                {
                    "pageview_url": "/billing",
                    "sessions": "657",
                    "orders": "300",
                    "billing_to_order_rt": "0.4566"
                },
                {
                    "pageview_url": "/billing-2",
                    "sessions": "653",
                    "orders": "409",
                    "billing_to_order_rt": "0.6263"
                }
            ]
        },
        "text/html": [
            "<table>",
            "<tr><th>pageview_url</th><th>sessions</th><th>orders</th><th>billing_to_order_rt</th></tr>",
            "<tr><td>/billing</td><td>657</td><td>300</td><td>0.4566</td></tr>",
            "<tr><td>/billing-2</td><td>653</td><td>409</td><td>0.6263</td></tr>",
            "</table>"
        ]
    }
}
],
            "execution_count": 77
        }
    ]
}