3. Analysis for Channel Portfolio Management - 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": "467d590f-1821-41f3-8633-2f7ddb19f953",
                "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.003"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 1
        },
        {
            "cell_type": "markdown",
            "source": [
                "**Subject: Expanded Channel Portfolio**\n",
                "\n",
                "<span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">With gsearch doing well and the site performing better, we&nbsp;</span> <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">launched a second paid search channel, bsearch, around&nbsp;</span> <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">August 22.</span>\n",
                "\n",
                "Can you pull weekly trended session volume since then and <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">compare to search nonbrand so I can get a sense for how&nbsp;</span> <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">important this will be for the business?</span>"
            ],
            "metadata": {
                "language": "sql",
                "azdata_cell_guid": "0ef02052-478b-4377-979b-e8f6e035d3aa"
            },
            "attachments": {}
        },
        {
            "cell_type": "code",
            "source": [
                "select min(date(website_sessions.created_at)) as week_start_date,\n",
                "count(DISTINCT case when utm_source = 'gsearch' then website_sessions.website_session_id else null end) as gsearch_sessions,\n",
                "count(DISTINCT case when utm_source = 'bsearch' then website_sessions.website_session_id else null end) as bsearch_sessions\n",
                "from website_sessions\n",
                "where website_sessions.created_at > '2012-08-22'\n",
                "and website_sessions.created_at < '2012-11-29'\n",
                "and utm_campaign = 'nonbrand'\n",
                "GROUP BY YEARWEEK(date(website_sessions.created_at))\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "422d8164-93bd-40ea-8103-d6c632721dd7",
                "language": "sql"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(15 row(s) affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:01.246"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 30,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "week_start_date"
                                    },
                                    {
                                        "name": "gsearch_sessions"
                                    },
                                    {
                                        "name": "bsearch_sessions"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "week_start_date": "2012-08-22",
                                    "gsearch_sessions": "590",
                                    "bsearch_sessions": "197"
                                },
                                {
                                    "week_start_date": "2012-08-26",
                                    "gsearch_sessions": "1056",
                                    "bsearch_sessions": "343"
                                },
                                {
                                    "week_start_date": "2012-09-02",
                                    "gsearch_sessions": "925",
                                    "bsearch_sessions": "290"
                                },
                                {
                                    "week_start_date": "2012-09-09",
                                    "gsearch_sessions": "951",
                                    "bsearch_sessions": "329"
                                },
                                {
                                    "week_start_date": "2012-09-16",
                                    "gsearch_sessions": "1151",
                                    "bsearch_sessions": "365"
                                },
                                {
                                    "week_start_date": "2012-09-23",
                                    "gsearch_sessions": "1050",
                                    "bsearch_sessions": "321"
                                },
                                {
                                    "week_start_date": "2012-09-30",
                                    "gsearch_sessions": "999",
                                    "bsearch_sessions": "316"
                                },
                                {
                                    "week_start_date": "2012-10-07",
                                    "gsearch_sessions": "1002",
                                    "bsearch_sessions": "330"
                                },
                                {
                                    "week_start_date": "2012-10-14",
                                    "gsearch_sessions": "1257",
                                    "bsearch_sessions": "420"
                                },
                                {
                                    "week_start_date": "2012-10-21",
                                    "gsearch_sessions": "1302",
                                    "bsearch_sessions": "431"
                                },
                                {
                                    "week_start_date": "2012-10-28",
                                    "gsearch_sessions": "1211",
                                    "bsearch_sessions": "384"
                                },
                                {
                                    "week_start_date": "2012-11-04",
                                    "gsearch_sessions": "1350",
                                    "bsearch_sessions": "429"
                                },
                                {
                                    "week_start_date": "2012-11-11",
                                    "gsearch_sessions": "1246",
                                    "bsearch_sessions": "438"
                                },
                                {
                                    "week_start_date": "2012-11-18",
                                    "gsearch_sessions": "3508",
                                    "bsearch_sessions": "1093"
                                },
                                {
                                    "week_start_date": "2012-11-25",
                                    "gsearch_sessions": "2286",
                                    "bsearch_sessions": "774"
                                }
                            ]
                        },
                        "text/html": [
                            "<table>",
                            "<tr><th>week_start_date</th><th>gsearch_sessions</th><th>bsearch_sessions</th></tr>",
                            "<tr><td>2012-08-22</td><td>590</td><td>197</td></tr>",
                            "<tr><td>2012-08-26</td><td>1056</td><td>343</td></tr>",
                            "<tr><td>2012-09-02</td><td>925</td><td>290</td></tr>",
                            "<tr><td>2012-09-09</td><td>951</td><td>329</td></tr>",
                            "<tr><td>2012-09-16</td><td>1151</td><td>365</td></tr>",
                            "<tr><td>2012-09-23</td><td>1050</td><td>321</td></tr>",
                            "<tr><td>2012-09-30</td><td>999</td><td>316</td></tr>",
                            "<tr><td>2012-10-07</td><td>1002</td><td>330</td></tr>",
                            "<tr><td>2012-10-14</td><td>1257</td><td>420</td></tr>",
                            "<tr><td>2012-10-21</td><td>1302</td><td>431</td></tr>",
                            "<tr><td>2012-10-28</td><td>1211</td><td>384</td></tr>",
                            "<tr><td>2012-11-04</td><td>1350</td><td>429</td></tr>",
                            "<tr><td>2012-11-11</td><td>1246</td><td>438</td></tr>",
                            "<tr><td>2012-11-18</td><td>3508</td><td>1093</td></tr>",
                            "<tr><td>2012-11-25</td><td>2286</td><td>774</td></tr>",
                            "</table>"
                        ]
                    }
                }
            ],
            "execution_count": 30
        },
        {
            "cell_type": "markdown",
            "source": [
                "**Subject: Comparing Our Channels**\n",
                "\n",
                "I'd like to learn more about the bsearch nonbrand campaign. <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">Could you please pull the percentage of traffic coming on&nbsp;</span> <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">Mobile, and compare that to search?&nbsp;</span> <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">Aggregate data since August 22nd is great, no&nbsp;</span> <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">need to show trending at this point.</span>"
            ],
            "metadata": {
                "language": "sql",
                "azdata_cell_guid": "4947cd85-0c1d-4fea-9b2f-4f9d67d0bb96"
            },
            "attachments": {}
        },
        {
            "cell_type": "code",
            "source": [
                "select * from website_sessions limit 5"
            ],
            "metadata": {
                "azdata_cell_guid": "e9624bdf-33c8-445d-83bf-b195d930e313",
                "language": "sql"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(5 row(s) affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:01.008"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 33,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "website_session_id"
                                    },
                                    {
                                        "name": "created_at"
                                    },
                                    {
                                        "name": "user_id"
                                    },
                                    {
                                        "name": "is_repeat_session"
                                    },
                                    {
                                        "name": "utm_source"
                                    },
                                    {
                                        "name": "utm_campaign"
                                    },
                                    {
                                        "name": "utm_content"
                                    },
                                    {
                                        "name": "device_type"
                                    },
                                    {
                                        "name": "http_referer"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "website_session_id": "1",
                                    "created_at": "2012-03-19 08:04:16",
                                    "user_id": "1",
                                    "is_repeat_session": "0",
                                    "utm_source": "gsearch",
                                    "utm_campaign": "nonbrand",
                                    "utm_content": "g_ad_1",
                                    "device_type": "mobile",
                                    "http_referer": "https://www.gsearch.com"
                                },
                                {
                                    "website_session_id": "2",
                                    "created_at": "2012-03-19 08:16:49",
                                    "user_id": "2",
                                    "is_repeat_session": "0",
                                    "utm_source": "gsearch",
                                    "utm_campaign": "nonbrand",
                                    "utm_content": "g_ad_1",
                                    "device_type": "desktop",
                                    "http_referer": "https://www.gsearch.com"
                                },
                                {
                                    "website_session_id": "3",
                                    "created_at": "2012-03-19 08:26:55",
                                    "user_id": "3",
                                    "is_repeat_session": "0",
                                    "utm_source": "gsearch",
                                    "utm_campaign": "nonbrand",
                                    "utm_content": "g_ad_1",
                                    "device_type": "desktop",
                                    "http_referer": "https://www.gsearch.com"
                                },
                                {
                                    "website_session_id": "4",
                                    "created_at": "2012-03-19 08:37:33",
                                    "user_id": "4",
                                    "is_repeat_session": "0",
                                    "utm_source": "gsearch",
                                    "utm_campaign": "nonbrand",
                                    "utm_content": "g_ad_1",
                                    "device_type": "desktop",
                                    "http_referer": "https://www.gsearch.com"
                                },
                                {
                                    "website_session_id": "5",
                                    "created_at": "2012-03-19 09:00:55",
                                    "user_id": "5",
                                    "is_repeat_session": "0",
                                    "utm_source": "gsearch",
                                    "utm_campaign": "nonbrand",
                                    "utm_content": "g_ad_1",
                                    "device_type": "mobile",
                                    "http_referer": "https://www.gsearch.com"
                                }
                            ]
                        },
                        "text/html": [
                            "<table>",
                            "<tr><th>website_session_id</th><th>created_at</th><th>user_id</th><th>is_repeat_session</th><th>utm_source</th><th>utm_campaign</th><th>utm_content</th><th>device_type</th><th>http_referer</th></tr>",
                            "<tr><td>1</td><td>2012-03-19 08:04:16</td><td>1</td><td>0</td><td>gsearch</td><td>nonbrand</td><td>g_ad_1</td><td>mobile</td><td>https://www.gsearch.com</td></tr>",
                            "<tr><td>2</td><td>2012-03-19 08:16:49</td><td>2</td><td>0</td><td>gsearch</td><td>nonbrand</td><td>g_ad_1</td><td>desktop</td><td>https://www.gsearch.com</td></tr>",
                            "<tr><td>3</td><td>2012-03-19 08:26:55</td><td>3</td><td>0</td><td>gsearch</td><td>nonbrand</td><td>g_ad_1</td><td>desktop</td><td>https://www.gsearch.com</td></tr>",
                            "<tr><td>4</td><td>2012-03-19 08:37:33</td><td>4</td><td>0</td><td>gsearch</td><td>nonbrand</td><td>g_ad_1</td><td>desktop</td><td>https://www.gsearch.com</td></tr>",
                            "<tr><td>5</td><td>2012-03-19 09:00:55</td><td>5</td><td>0</td><td>gsearch</td><td>nonbrand</td><td>g_ad_1</td><td>mobile</td><td>https://www.gsearch.com</td></tr>",
                            "</table>"
                        ]
                    }
                }
            ],
            "execution_count": 33
        },
        {
            "cell_type": "code",
            "source": [
                "select \n",
                "utm_source,\n",
                "count(DISTINCT website_session_id) as sessions,\n",
                "count(DISTINCT case when device_type = 'mobile' then website_session_id else null end) as mobile_sessions,\n",
                "count(DISTINCT case when device_type = 'mobile' then website_session_id else null end)/count(DISTINCT website_session_id) as pct_mobile\n",
                "from website_sessions\n",
                "where created_at > '2012-08-22'\n",
                "and created_at < '2012-11-29'\n",
                "and utm_campaign = 'nonbrand'\n",
                "GROUP BY utm_source\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "120dbe04-bbc0-43ba-a6e6-36612585433b",
                "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.271"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 34,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "utm_source"
                                    },
                                    {
                                        "name": "sessions"
                                    },
                                    {
                                        "name": "mobile_sessions"
                                    },
                                    {
                                        "name": "pct_mobile"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "utm_source": "bsearch",
                                    "sessions": "6460",
                                    "mobile_sessions": "558",
                                    "pct_mobile": "0.0864"
                                },
                                {
                                    "utm_source": "gsearch",
                                    "sessions": "19884",
                                    "mobile_sessions": "4867",
                                    "pct_mobile": "0.2448"
                                }
                            ]
                        },
                        "text/html": [
                            "<table>",
                            "<tr><th>utm_source</th><th>sessions</th><th>mobile_sessions</th><th>pct_mobile</th></tr>",
                            "<tr><td>bsearch</td><td>6460</td><td>558</td><td>0.0864</td></tr>",
                            "<tr><td>gsearch</td><td>19884</td><td>4867</td><td>0.2448</td></tr>",
                            "</table>"
                        ]
                    }
                }
            ],
            "execution_count": 34
        },
        {
            "cell_type": "markdown",
            "source": [
                "**Subject: Multi-Channel Bidding**\n",
                "\n",
                "<span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">Could you pull nonbrand conversion rates&nbsp;</span> <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">from session to order for gsearch and bsearch, and slice the&nbsp;</span> <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">data by device type?&nbsp;</span> <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">Please analyze data from August 22 to September 18; we&nbsp;</span> <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">ran a special pre-holiday campaign for gsearch starting on&nbsp;</span> <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">September 19th, so the data after that isn't fair game.</span>"
            ],
            "metadata": {
                "language": "sql",
                "azdata_cell_guid": "798b23f0-6956-401a-8bb7-88a0faa2bc63"
            },
            "attachments": {}
        },
        {
            "cell_type": "code",
            "source": [
                "select * from orders limit 5"
            ],
            "metadata": {
                "language": "sql",
                "azdata_cell_guid": "3d10988b-b5a1-4d27-be74-7bf9abd5b3b6"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(5 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": 36,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "order_id"
                                    },
                                    {
                                        "name": "created_at"
                                    },
                                    {
                                        "name": "website_session_id"
                                    },
                                    {
                                        "name": "user_id"
                                    },
                                    {
                                        "name": "primary_product_id"
                                    },
                                    {
                                        "name": "items_purchased"
                                    },
                                    {
                                        "name": "price_usd"
                                    },
                                    {
                                        "name": "cogs_usd"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "order_id": "1",
                                    "created_at": "2012-03-19 10:42:46",
                                    "website_session_id": "20",
                                    "user_id": "20",
                                    "primary_product_id": "1",
                                    "items_purchased": "1",
                                    "price_usd": "49.99",
                                    "cogs_usd": "19.49"
                                },
                                {
                                    "order_id": "2",
                                    "created_at": "2012-03-19 19:27:37",
                                    "website_session_id": "104",
                                    "user_id": "104",
                                    "primary_product_id": "1",
                                    "items_purchased": "1",
                                    "price_usd": "49.99",
                                    "cogs_usd": "19.49"
                                },
                                {
                                    "order_id": "3",
                                    "created_at": "2012-03-20 06:44:45",
                                    "website_session_id": "147",
                                    "user_id": "147",
                                    "primary_product_id": "1",
                                    "items_purchased": "1",
                                    "price_usd": "49.99",
                                    "cogs_usd": "19.49"
                                },
                                {
                                    "order_id": "4",
                                    "created_at": "2012-03-20 09:41:45",
                                    "website_session_id": "160",
                                    "user_id": "160",
                                    "primary_product_id": "1",
                                    "items_purchased": "1",
                                    "price_usd": "49.99",
                                    "cogs_usd": "19.49"
                                },
                                {
                                    "order_id": "5",
                                    "created_at": "2012-03-20 11:28:15",
                                    "website_session_id": "177",
                                    "user_id": "177",
                                    "primary_product_id": "1",
                                    "items_purchased": "1",
                                    "price_usd": "49.99",
                                    "cogs_usd": "19.49"
                                }
                            ]
                        },
                        "text/html": [
                            "<table>",
                            "<tr><th>order_id</th><th>created_at</th><th>website_session_id</th><th>user_id</th><th>primary_product_id</th><th>items_purchased</th><th>price_usd</th><th>cogs_usd</th></tr>",
                            "<tr><td>1</td><td>2012-03-19 10:42:46</td><td>20</td><td>20</td><td>1</td><td>1</td><td>49.99</td><td>19.49</td></tr>",
                            "<tr><td>2</td><td>2012-03-19 19:27:37</td><td>104</td><td>104</td><td>1</td><td>1</td><td>49.99</td><td>19.49</td></tr>",
                            "<tr><td>3</td><td>2012-03-20 06:44:45</td><td>147</td><td>147</td><td>1</td><td>1</td><td>49.99</td><td>19.49</td></tr>",
                            "<tr><td>4</td><td>2012-03-20 09:41:45</td><td>160</td><td>160</td><td>1</td><td>1</td><td>49.99</td><td>19.49</td></tr>",
                            "<tr><td>5</td><td>2012-03-20 11:28:15</td><td>177</td><td>177</td><td>1</td><td>1</td><td>49.99</td><td>19.49</td></tr>",
                            "</table>"
                        ]
                    }
                }
            ],
            "execution_count": 36
        },
        {
            "cell_type": "code",
            "source": [
                "select \n",
                "device_type,\n",
                "utm_source,\n",
                "count(DISTINCT website_sessions.website_session_id) as sessions,\n",
                "count(DISTINCT order_id) as orders,\n",
                "count(DISTINCT order_id)/count(DISTINCT website_sessions.website_session_id) as conv_rate\n",
                "from website_sessions\n",
                "left join orders\n",
                "on website_sessions.website_session_id = orders.website_session_id\n",
                "where website_sessions.created_at > '2012-08-22'\n",
                "and website_sessions.created_at < '2012-09-19'\n",
                "and utm_campaign = 'nonbrand'\n",
                "GROUP BY 1, 2"
            ],
            "metadata": {
                "azdata_cell_guid": "281e9a85-90aa-4888-b787-4a6637997222",
                "language": "sql"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(4 row(s) affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:01.250"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 39,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "device_type"
                                    },
                                    {
                                        "name": "utm_source"
                                    },
                                    {
                                        "name": "sessions"
                                    },
                                    {
                                        "name": "orders"
                                    },
                                    {
                                        "name": "conv_rate"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "device_type": "desktop",
                                    "utm_source": "bsearch",
                                    "sessions": "1162",
                                    "orders": "44",
                                    "conv_rate": "0.0379"
                                },
                                {
                                    "device_type": "desktop",
                                    "utm_source": "gsearch",
                                    "sessions": "3011",
                                    "orders": "136",
                                    "conv_rate": "0.0452"
                                },
                                {
                                    "device_type": "mobile",
                                    "utm_source": "bsearch",
                                    "sessions": "130",
                                    "orders": "1",
                                    "conv_rate": "0.0077"
                                },
                                {
                                    "device_type": "mobile",
                                    "utm_source": "gsearch",
                                    "sessions": "1015",
                                    "orders": "13",
                                    "conv_rate": "0.0128"
                                }
                            ]
                        },
                        "text/html": [
                            "<table>",
                            "<tr><th>device_type</th><th>utm_source</th><th>sessions</th><th>orders</th><th>conv_rate</th></tr>",
                            "<tr><td>desktop</td><td>bsearch</td><td>1162</td><td>44</td><td>0.0379</td></tr>",
                            "<tr><td>desktop</td><td>gsearch</td><td>3011</td><td>136</td><td>0.0452</td></tr>",
                            "<tr><td>mobile</td><td>bsearch</td><td>130</td><td>1</td><td>0.0077</td></tr>",
                            "<tr><td>mobile</td><td>gsearch</td><td>1015</td><td>13</td><td>0.0128</td></tr>",
                            "</table>"
                        ]
                    }
                }
            ],
            "execution_count": 39
        },
        {
            "cell_type": "markdown",
            "source": [
                "**Subject: Impact of Bid Changes**\n",
                "\n",
                "Based on your last analysis, we bid down bsearch nonbrand on <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">December 2nd.&nbsp;</span>  <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">Can you pull weekly session volume for search and search&nbsp;</span>  <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">nonbrand, broken down by device, since November 4th?&nbsp;</span>  <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">If you can include a comparison metric to show search as a&nbsp;</span>  <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">percent of gsearch for each device, that would be great too.</span>"
            ],
            "metadata": {
                "language": "sql",
                "azdata_cell_guid": "78d077c4-dba9-4337-82a4-4c725d459959"
            },
            "attachments": {}
        },
        {
            "cell_type": "code",
            "source": [
                "select * from website_sessions limit 5"
            ],
            "metadata": {
                "language": "sql",
                "azdata_cell_guid": "b4f3e391-b786-4c34-b415-99c17d4053aa"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(5 row(s) affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:01.008"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 42,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "website_session_id"
                                    },
                                    {
                                        "name": "created_at"
                                    },
                                    {
                                        "name": "user_id"
                                    },
                                    {
                                        "name": "is_repeat_session"
                                    },
                                    {
                                        "name": "utm_source"
                                    },
                                    {
                                        "name": "utm_campaign"
                                    },
                                    {
                                        "name": "utm_content"
                                    },
                                    {
                                        "name": "device_type"
                                    },
                                    {
                                        "name": "http_referer"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "website_session_id": "1",
                                    "created_at": "2012-03-19 08:04:16",
                                    "user_id": "1",
                                    "is_repeat_session": "0",
                                    "utm_source": "gsearch",
                                    "utm_campaign": "nonbrand",
                                    "utm_content": "g_ad_1",
                                    "device_type": "mobile",
                                    "http_referer": "https://www.gsearch.com"
                                },
                                {
                                    "website_session_id": "2",
                                    "created_at": "2012-03-19 08:16:49",
                                    "user_id": "2",
                                    "is_repeat_session": "0",
                                    "utm_source": "gsearch",
                                    "utm_campaign": "nonbrand",
                                    "utm_content": "g_ad_1",
                                    "device_type": "desktop",
                                    "http_referer": "https://www.gsearch.com"
                                },
                                {
                                    "website_session_id": "3",
                                    "created_at": "2012-03-19 08:26:55",
                                    "user_id": "3",
                                    "is_repeat_session": "0",
                                    "utm_source": "gsearch",
                                    "utm_campaign": "nonbrand",
                                    "utm_content": "g_ad_1",
                                    "device_type": "desktop",
                                    "http_referer": "https://www.gsearch.com"
                                },
                                {
                                    "website_session_id": "4",
                                    "created_at": "2012-03-19 08:37:33",
                                    "user_id": "4",
                                    "is_repeat_session": "0",
                                    "utm_source": "gsearch",
                                    "utm_campaign": "nonbrand",
                                    "utm_content": "g_ad_1",
                                    "device_type": "desktop",
                                    "http_referer": "https://www.gsearch.com"
                                },
                                {
                                    "website_session_id": "5",
                                    "created_at": "2012-03-19 09:00:55",
                                    "user_id": "5",
                                    "is_repeat_session": "0",
                                    "utm_source": "gsearch",
                                    "utm_campaign": "nonbrand",
                                    "utm_content": "g_ad_1",
                                    "device_type": "mobile",
                                    "http_referer": "https://www.gsearch.com"
                                }
                            ]
                        },
                        "text/html": [
                            "<table>",
                            "<tr><th>website_session_id</th><th>created_at</th><th>user_id</th><th>is_repeat_session</th><th>utm_source</th><th>utm_campaign</th><th>utm_content</th><th>device_type</th><th>http_referer</th></tr>",
                            "<tr><td>1</td><td>2012-03-19 08:04:16</td><td>1</td><td>0</td><td>gsearch</td><td>nonbrand</td><td>g_ad_1</td><td>mobile</td><td>https://www.gsearch.com</td></tr>",
                            "<tr><td>2</td><td>2012-03-19 08:16:49</td><td>2</td><td>0</td><td>gsearch</td><td>nonbrand</td><td>g_ad_1</td><td>desktop</td><td>https://www.gsearch.com</td></tr>",
                            "<tr><td>3</td><td>2012-03-19 08:26:55</td><td>3</td><td>0</td><td>gsearch</td><td>nonbrand</td><td>g_ad_1</td><td>desktop</td><td>https://www.gsearch.com</td></tr>",
                            "<tr><td>4</td><td>2012-03-19 08:37:33</td><td>4</td><td>0</td><td>gsearch</td><td>nonbrand</td><td>g_ad_1</td><td>desktop</td><td>https://www.gsearch.com</td></tr>",
                            "<tr><td>5</td><td>2012-03-19 09:00:55</td><td>5</td><td>0</td><td>gsearch</td><td>nonbrand</td><td>g_ad_1</td><td>mobile</td><td>https://www.gsearch.com</td></tr>",
                            "</table>"
                        ]
                    }
                }
            ],
            "execution_count": 42
        },
        {
            "cell_type": "code",
            "source": [
                "select \n",
                "min(date(created_at)),\n",
                "count(DISTINCT case when device_type = 'desktop' and utm_source = 'gsearch' then website_session_id else null end) as g_dtop_sessions,\n",
                "count(DISTINCT case when device_type = 'desktop' and utm_source = 'bsearch' then website_session_id else null end) as b_dtop_sessions,\n",
                "count(DISTINCT case when device_type = 'desktop' and utm_source = 'bsearch' then website_session_id else null end)/\n",
                "count(DISTINCT case when device_type = 'desktop' and utm_source = 'gsearch' then website_session_id else null end) as b_pct_of_d_dtop,\n",
                "count(DISTINCT case when device_type = 'mobile' and utm_source = 'gsearch' then website_session_id else null end) as g_mob_sessions,\n",
                "count(DISTINCT case when device_type = 'mobile' and utm_source = 'bsearch' then website_session_id else null end) as b_mob_sessions,\n",
                "count(DISTINCT case when device_type = 'mobile' and utm_source = 'bsearch' then website_session_id else null end)/\n",
                "count(DISTINCT case when device_type = 'mobile' and utm_source = 'gsearch' then website_session_id else null end) as b_pct_of_d_mob\n",
                "from website_sessions\n",
                "where created_at > '2012-11-04'\n",
                "and created_at < '2012-12-23'\n",
                "and utm_campaign = 'nonbrand'\n",
                "GROUP BY yearweek(date(created_at))"
            ],
            "metadata": {
                "language": "sql",
                "azdata_cell_guid": "a5a4a5f5-b741-4c17-878b-273badaa34ef"
            },
            "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.329"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 46,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "min(date(created_at))"
                                    },
                                    {
                                        "name": "g_dtop_sessions"
                                    },
                                    {
                                        "name": "b_dtop_sessions"
                                    },
                                    {
                                        "name": "b_pct_of_d_dtop"
                                    },
                                    {
                                        "name": "g_mob_sessions"
                                    },
                                    {
                                        "name": "b_mob_sessions"
                                    },
                                    {
                                        "name": "b_pct_of_d_mob"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "min(date(created_at))": "2012-11-04",
                                    "g_dtop_sessions": "1027",
                                    "b_dtop_sessions": "400",
                                    "b_pct_of_d_dtop": "0.3895",
                                    "g_mob_sessions": "323",
                                    "b_mob_sessions": "29",
                                    "b_pct_of_d_mob": "0.0898"
                                },
                                {
                                    "min(date(created_at))": "2012-11-11",
                                    "g_dtop_sessions": "956",
                                    "b_dtop_sessions": "401",
                                    "b_pct_of_d_dtop": "0.4195",
                                    "g_mob_sessions": "290",
                                    "b_mob_sessions": "37",
                                    "b_pct_of_d_mob": "0.1276"
                                },
                                {
                                    "min(date(created_at))": "2012-11-18",
                                    "g_dtop_sessions": "2655",
                                    "b_dtop_sessions": "1008",
                                    "b_pct_of_d_dtop": "0.3797",
                                    "g_mob_sessions": "853",
                                    "b_mob_sessions": "85",
                                    "b_pct_of_d_mob": "0.0996"
                                },
                                {
                                    "min(date(created_at))": "2012-11-25",
                                    "g_dtop_sessions": "2058",
                                    "b_dtop_sessions": "843",
                                    "b_pct_of_d_dtop": "0.4096",
                                    "g_mob_sessions": "692",
                                    "b_mob_sessions": "62",
                                    "b_pct_of_d_mob": "0.0896"
                                },
                                {
                                    "min(date(created_at))": "2012-12-02",
                                    "g_dtop_sessions": "1326",
                                    "b_dtop_sessions": "517",
                                    "b_pct_of_d_dtop": "0.3899",
                                    "g_mob_sessions": "396",
                                    "b_mob_sessions": "31",
                                    "b_pct_of_d_mob": "0.0783"
                                },
                                {
                                    "min(date(created_at))": "2012-12-09",
                                    "g_dtop_sessions": "1277",
                                    "b_dtop_sessions": "293",
                                    "b_pct_of_d_dtop": "0.2294",
                                    "g_mob_sessions": "424",
                                    "b_mob_sessions": "46",
                                    "b_pct_of_d_mob": "0.1085"
                                },
                                {
                                    "min(date(created_at))": "2012-12-16",
                                    "g_dtop_sessions": "1370",
                                    "b_dtop_sessions": "383",
                                    "b_pct_of_d_dtop": "0.2796",
                                    "g_mob_sessions": "412",
                                    "b_mob_sessions": "49",
                                    "b_pct_of_d_mob": "0.1189"
                                }
                            ]
                        },
                        "text/html": [
                            "<table>",
                            "<tr><th>min(date(created_at))</th><th>g_dtop_sessions</th><th>b_dtop_sessions</th><th>b_pct_of_d_dtop</th><th>g_mob_sessions</th><th>b_mob_sessions</th><th>b_pct_of_d_mob</th></tr>",
                            "<tr><td>2012-11-04</td><td>1027</td><td>400</td><td>0.3895</td><td>323</td><td>29</td><td>0.0898</td></tr>",
                            "<tr><td>2012-11-11</td><td>956</td><td>401</td><td>0.4195</td><td>290</td><td>37</td><td>0.1276</td></tr>",
                            "<tr><td>2012-11-18</td><td>2655</td><td>1008</td><td>0.3797</td><td>853</td><td>85</td><td>0.0996</td></tr>",
                            "<tr><td>2012-11-25</td><td>2058</td><td>843</td><td>0.4096</td><td>692</td><td>62</td><td>0.0896</td></tr>",
                            "<tr><td>2012-12-02</td><td>1326</td><td>517</td><td>0.3899</td><td>396</td><td>31</td><td>0.0783</td></tr>",
                            "<tr><td>2012-12-09</td><td>1277</td><td>293</td><td>0.2294</td><td>424</td><td>46</td><td>0.1085</td></tr>",
                            "<tr><td>2012-12-16</td><td>1370</td><td>383</td><td>0.2796</td><td>412</td><td>49</td><td>0.1189</td></tr>",
                            "</table>"
                        ]
                    }
                }
            ],
            "execution_count": 46
        },
        {
            "cell_type": "markdown",
            "source": [
                "**Subject: Site traffic breakdown**\n",
                "\n",
                "A potential investor is asking if we're building any <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">momentum with our brand or if we'll need to keep relying</span> <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">on paid traffic.</span>\n",
                "\n",
                "Could you pull organic search, direct type in, and paid <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">brand search sessions by month, and show those sessions&nbsp;</span>  <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">as a % of paid search nonbrand?</span>"
            ],
            "metadata": {
                "language": "sql",
                "azdata_cell_guid": "9da587f7-6419-43f4-84ef-687b61b88338"
            },
            "attachments": {}
        },
        {
            "cell_type": "code",
            "source": [
                "select http_referer from website_sessions GROUP by 1"
            ],
            "metadata": {
                "language": "sql",
                "azdata_cell_guid": "c5880cad-dbd3-474d-9224-b23fa50adf3b"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(4 row(s) affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:01.254"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 49,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "http_referer"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "http_referer": "https://www.gsearch.com"
                                },
                                {
                                    "http_referer": "NULL"
                                },
                                {
                                    "http_referer": "https://www.bsearch.com"
                                },
                                {
                                    "http_referer": "https://www.socialbook.com"
                                }
                            ]
                        },
                        "text/html": [
                            "<table>",
                            "<tr><th>http_referer</th></tr>",
                            "<tr><td>https://www.gsearch.com</td></tr>",
                            "<tr><td>NULL</td></tr>",
                            "<tr><td>https://www.bsearch.com</td></tr>",
                            "<tr><td>https://www.socialbook.com</td></tr>",
                            "</table>"
                        ]
                    }
                }
            ],
            "execution_count": 49
        },
        {
            "cell_type": "code",
            "source": [
                "select \n",
                "YEAR(date(created_at)) as yr,\n",
                "MONTH(date(created_at)) as mo,\n",
                "count(DISTINCT case when utm_campaign = 'nonbrand' then website_session_id else null end) as nonbrand,\n",
                "count(DISTINCT case when utm_campaign = 'brand' then website_session_id else null end) as brand,\n",
                "count(DISTINCT case when utm_campaign = 'brand' then website_session_id else null end)/\n",
                "count(DISTINCT case when utm_campaign = 'nonbrand' then website_session_id else null end) as brand_pct_nonbrand,\n",
                "count(DISTINCT case when channel_group = 'direct' then website_session_id else null end) as direct,\n",
                "count(DISTINCT case when channel_group = 'direct' then website_session_id else null end)/\n",
                "count(DISTINCT case when utm_campaign = 'nonbrand' then website_session_id else null end)\n",
                "as direct_pct_nonbrand,\n",
                "count(DISTINCT case when channel_group = 'organic' then website_session_id else null end) as organic,\n",
                "count(DISTINCT case when channel_group = 'organic' then website_session_id else null end)/\n",
                "count(DISTINCT case when utm_campaign = 'nonbrand' then website_session_id else null end)\n",
                "as organic_pct_nonbrand\n",
                "\n",
                "from(\n",
                "    select website_session_id,\n",
                "    created_at, utm_campaign,\n",
                "    case\n",
                "    when http_referer is null then 'direct'\n",
                "    when http_referer = 'https://www.gsearch.com' and utm_source is Null then 'organic'\n",
                "    when http_referer = 'https://www.bsearch.com' and utm_source is Null then 'organic'\n",
                "    else 'other'\n",
                "    END as channel_group\n",
                "    from website_sessions\n",
                "    where created_at < '2012-12-23'\n",
                ") as session_w_channel_grp\n",
                "\n",
                "GROUP by 1, 2"
            ],
            "metadata": {
                "azdata_cell_guid": "d6756df8-ac49-451c-bd35-f86b9295aa97",
                "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.450"
    },
    "metadata": {}
}, {
    "output_type": "execute_result",
    "metadata": {},
    "execution_count": 65,
    "data": {
        "application/vnd.dataresource+json": {
            "schema": {
                "fields": [
                    {
                        "name": "yr"
                    },
                    {
                        "name": "mo"
                    },
                    {
                        "name": "nonbrand"
                    },
                    {
                        "name": "brand"
                    },
                    {
                        "name": "brand_pct_nonbrand"
                    },
                    {
                        "name": "direct"
                    },
                    {
                        "name": "direct_pct_nonbrand"
                    },
                    {
                        "name": "organic"
                    },
                    {
                        "name": "organic_pct_nonbrand"
                    }
                ]
            },
            "data": [
                {
                    "yr": "2012",
                    "mo": "3",
                    "nonbrand": "1852",
                    "brand": "10",
                    "brand_pct_nonbrand": "0.0054",
                    "direct": "9",
                    "direct_pct_nonbrand": "0.0049",
                    "organic": "8",
                    "organic_pct_nonbrand": "0.0043"
                },
                {
                    "yr": "2012",
                    "mo": "4",
                    "nonbrand": "3509",
                    "brand": "76",
                    "brand_pct_nonbrand": "0.0217",
                    "direct": "71",
                    "direct_pct_nonbrand": "0.0202",
                    "organic": "78",
                    "organic_pct_nonbrand": "0.0222"
                },
                {
                    "yr": "2012",
                    "mo": "5",
                    "nonbrand": "3295",
                    "brand": "140",
                    "brand_pct_nonbrand": "0.0425",
                    "direct": "151",
                    "direct_pct_nonbrand": "0.0458",
                    "organic": "150",
                    "organic_pct_nonbrand": "0.0455"
                },
                {
                    "yr": "2012",
                    "mo": "6",
                    "nonbrand": "3439",
                    "brand": "164",
                    "brand_pct_nonbrand": "0.0477",
                    "direct": "170",
                    "direct_pct_nonbrand": "0.0494",
                    "organic": "190",
                    "organic_pct_nonbrand": "0.0552"
                },
                {
                    "yr": "2012",
                    "mo": "7",
                    "nonbrand": "3660",
                    "brand": "195",
                    "brand_pct_nonbrand": "0.0533",
                    "direct": "187",
                    "direct_pct_nonbrand": "0.0511",
                    "organic": "207",
                    "organic_pct_nonbrand": "0.0566"
                },
                {
                    "yr": "2012",
                    "mo": "8",
                    "nonbrand": "5318",
                    "brand": "264",
                    "brand_pct_nonbrand": "0.0496",
                    "direct": "250",
                    "direct_pct_nonbrand": "0.0470",
                    "organic": "265",
                    "organic_pct_nonbrand": "0.0498"
                },
                {
                    "yr": "2012",
                    "mo": "9",
                    "nonbrand": "5591",
                    "brand": "339",
                    "brand_pct_nonbrand": "0.0606",
                    "direct": "285",
                    "direct_pct_nonbrand": "0.0510",
                    "organic": "331",
                    "organic_pct_nonbrand": "0.0592"
                },
                {
                    "yr": "2012",
                    "mo": "10",
                    "nonbrand": "6883",
                    "brand": "432",
                    "brand_pct_nonbrand": "0.0628",
                    "direct": "440",
                    "direct_pct_nonbrand": "0.0639",
                    "organic": "428",
                    "organic_pct_nonbrand": "0.0622"
                },
                {
                    "yr": "2012",
                    "mo": "11",
                    "nonbrand": "12260",
                    "brand": "556",
                    "brand_pct_nonbrand": "0.0454",
                    "direct": "571",
                    "direct_pct_nonbrand": "0.0466",
                    "organic": "624",
                    "organic_pct_nonbrand": "0.0509"
                },
                {
                    "yr": "2012",
                    "mo": "12",
                    "nonbrand": "6643",
                    "brand": "464",
                    "brand_pct_nonbrand": "0.0698",
                    "direct": "482",
                    "direct_pct_nonbrand": "0.0726",
                    "organic": "492",
                    "organic_pct_nonbrand": "0.0741"
                }
            ]
        },
        "text/html": [
            "<table>",
            "<tr><th>yr</th><th>mo</th><th>nonbrand</th><th>brand</th><th>brand_pct_nonbrand</th><th>direct</th><th>direct_pct_nonbrand</th><th>organic</th><th>organic_pct_nonbrand</th></tr>",
            "<tr><td>2012</td><td>3</td><td>1852</td><td>10</td><td>0.0054</td><td>9</td><td>0.0049</td><td>8</td><td>0.0043</td></tr>",
            "<tr><td>2012</td><td>4</td><td>3509</td><td>76</td><td>0.0217</td><td>71</td><td>0.0202</td><td>78</td><td>0.0222</td></tr>",
            "<tr><td>2012</td><td>5</td><td>3295</td><td>140</td><td>0.0425</td><td>151</td><td>0.0458</td><td>150</td><td>0.0455</td></tr>",
            "<tr><td>2012</td><td>6</td><td>3439</td><td>164</td><td>0.0477</td><td>170</td><td>0.0494</td><td>190</td><td>0.0552</td></tr>",
            "<tr><td>2012</td><td>7</td><td>3660</td><td>195</td><td>0.0533</td><td>187</td><td>0.0511</td><td>207</td><td>0.0566</td></tr>",
            "<tr><td>2012</td><td>8</td><td>5318</td><td>264</td><td>0.0496</td><td>250</td><td>0.0470</td><td>265</td><td>0.0498</td></tr>",
            "<tr><td>2012</td><td>9</td><td>5591</td><td>339</td><td>0.0606</td><td>285</td><td>0.0510</td><td>331</td><td>0.0592</td></tr>",
            "<tr><td>2012</td><td>10</td><td>6883</td><td>432</td><td>0.0628</td><td>440</td><td>0.0639</td><td>428</td><td>0.0622</td></tr>",
            "<tr><td>2012</td><td>11</td><td>12260</td><td>556</td><td>0.0454</td><td>571</td><td>0.0466</td><td>624</td><td>0.0509</td></tr>",
            "<tr><td>2012</td><td>12</td><td>6643</td><td>464</td><td>0.0698</td><td>482</td><td>0.0726</td><td>492</td><td>0.0741</td></tr>",
            "</table>"
        ]
    }
}
],
            "execution_count": 65
        }
    ]
}