Skip to content

Instantly share code, notes, and snippets.

@jarutis

jarutis/gist:b5d180d1273865732887

Created November 28, 2014 14:25
Show Gist options
  • Save jarutis/b5d180d1273865732887 to your computer and use it in GitHub Desktop.
Save jarutis/b5d180d1273865732887 to your computer and use it in GitHub Desktop.
Download ZIP
ipython test
Raw
gistfile1.txt
{
"metadata": {
"kernelspec": {
"codemirror_mode": {
"name": "python",
"singleOperators": {},
"version": 2
},
"display_name": "IPython (Python 2)",
"language": "python",
"name": "python2"
},
"name": "",
"signature": "sha256:00e6f6eafd092ab3a8e1fa1af7d6e9f29c546016e13540b7f6f39219b2feebd6"
},
"nbformat": 3,
"nbformat_minor": 0,
"worksheets": [
{
"cells": [
{
"cell_type": "heading",
"level": 1,
"metadata": {},
"source": [
"Prepare data"
]
},
{
"cell_type": "heading",
"level": 2,
"metadata": {},
"source": [
"Configuration"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"import datetime\n",
"import pandas as pd\n",
"import mysql.connector\n",
"from collections import OrderedDict"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 1
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"current_year = 2014\n",
"current_week = 45\n",
"\n",
"weeks = ['W' + str(current_week - 1), 'W' + str(current_week)]\n",
"start_week = current_week - 24\n",
"portals = OrderedDict([('DE','de'),\n",
" ('LT','lt'),\n",
" ('CZ','cz'),\n",
" ('LTB','lt_babies'),\n",
" ('DEB','de_babies'),\n",
" ('PL','pl'),\n",
" ('FR','fr'),\n",
" ('US','us'),\n",
" ('UK','uk'),\n",
" ('AT','at')])\n",
"portals_chart = OrderedDict([('DE','de'),\n",
" ('US','us'),\n",
" ('FR','fr')])\n",
"ret_portals = OrderedDict([('DE','de'),\n",
" ('US','us'),\n",
" ('UK','uk')])"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 2
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"def first_day_in_week(my_year, my_week):\n",
" ret = datetime.datetime.strptime('%04d-%02d-0' % (my_year, my_week), '%Y-%W-%w')\n",
" if datetime.date(my_year, 1, 4).isoweekday() > 4:\n",
" ret -= datetime.timedelta(days=7)\n",
" return ret"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 3
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"date = first_day_in_week(current_year, current_week)"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 4
},
{
"cell_type": "heading",
"level": 2,
"metadata": {},
"source": [
"Read data exported from analytics"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"def portal_to_end(name, portals):\n",
" parts = name.split(':')\n",
" portal = [x for x in parts if x in portals]\n",
" other = [x for x in parts if x not in portals]\n",
" return ':'.join(other + portal)"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 5
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"df = pd.read_csv('bi.csv', header=None, index_col=None, parse_dates=False)\n",
"df.T.to_csv('bi_t.csv', header=False, index=False)"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 6
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"analytics = pd.read_csv('bi_t.csv', parse_dates=['period'])\n",
"analytics.columns = [portal_to_end(x, portals.values()) for x in analytics.columns]"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 7
},
{
"cell_type": "heading",
"level": 2,
"metadata": {},
"source": [
"Read finance data from Karolina"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"finance_xls = pd.ExcelFile('finance.xlsx')"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 8
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"finance_uk = finance_xls.parse('UK', index_col=None, na_values=[''])\n",
"finance_uk = finance_uk.loc[:,[x for x in finance_uk.columns if x[0:7] != 'Unnamed']]\n",
"finance_uk.columns = [x.lower().replace(\" \", \"_\") + ':uk' for x in finance_uk.columns]\n",
"finance_uk = finance_uk.rename(columns = {'week:uk':'week', \n",
" 'ios_members:uk':'ios_paid_members:uk',\n",
" 'android_members:uk':'android_paid_members:uk',\n",
" 'total_members:uk':'total_paid_members:uk'})"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 9
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"finance_us = finance_xls.parse('US', index_col=None, na_values=[''])\n",
"finance_us = finance_us.loc[:,[x for x in finance_us.columns if x[0:7] != 'Unnamed']]\n",
"finance_us.columns = [x.lower().replace(\" \", \"_\") + ':us' for x in finance_us.columns]\n",
"finance_us = finance_us.rename(columns = {'week:us':'week', \n",
" 'ios_members:us':'ios_paid_members:us',\n",
" 'android_members:us':'android_paid_members:us',\n",
" 'total_members:us':'total_paid_members:us'})"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 10
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"finance = pd.merge(finance_uk, finance_us, on='week')\n",
"finance['period'] = [first_day_in_week(current_year, x) for x in finance.week]"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 11
},
{
"cell_type": "heading",
"level": 2,
"metadata": {},
"source": [
"Extract payment data from mysql"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Query from mindaugas."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"query = \"\"\"\n",
"SELECT \t\"Marketplace share\" AS fee_type, round(sum(bd.`marketplace_share`), 2) AS card_fee, \n",
"\t\tWEEK(created_at - INTERVAL {adjust} HOUR) AS WEEK\n",
"FROM buyer_debits bd\n",
"WHERE WEEK(created_at - INTERVAL {adjust} HOUR) BETWEEN {WEEK_from} AND {WEEK_to}\n",
"AND STATUS > 30\n",
"GROUP BY WEEK\n",
"UNION\n",
"SELECT \t\"marketplace share refunded\" AS fee_type, round(sum(bd.`marketplace_share` ), 2)*-1 AS card_fee, \n",
"\t\tWEEK(br.created_at - INTERVAL {adjust} HOUR) AS WEEK\n",
"FROM buyer_refunds br\n",
"JOIN buyer_debits bd\n",
"ON br.`buyer_debit_id` = bd.id AND bd.status IN (90, 100)\n",
"WHERE WEEK(br.created_at - INTERVAL {adjust} HOUR) BETWEEN {WEEK_from} AND {WEEK_to}\n",
"GROUP BY WEEK\n",
"UNION\n",
"SELECT \t\"Card processing\" AS fee_type, round(sum(pay_in_amount*{cc_rate} + {cc_fix_rate}), 2)*-1 AS card_fee, \n",
"\t\tWEEK(created_at - INTERVAL {adjust} HOUR) AS WEEK\n",
"FROM buyer_debits bd\n",
"WHERE WEEK(created_at - INTERVAL {adjust} HOUR) BETWEEN {WEEK_from} AND {WEEK_to}\n",
"AND STATUS > 30\n",
"GROUP BY WEEK\n",
"union\n",
"SELECT \t\"Card processing fee refunded\" AS fee_type, round(sum(br.amount*{cc_rate} ), 2) AS card_fee, \n",
"\t\tWEEK(br.created_at - INTERVAL {adjust} HOUR) AS WEEK\n",
"FROM buyer_refunds br\n",
"JOIN buyer_debits bd\n",
"ON br.`buyer_debit_id` = bd.id AND bd.status IN (90, 100)\n",
"WHERE WEEK(br.created_at - INTERVAL {adjust} HOUR) BETWEEN {WEEK_from} AND {WEEK_to}\n",
"GROUP BY WEEK\n",
"UNION\n",
"SELECT \t\"Payout\" AS fee_type, round(count(id)*{p_rate}, 2)*-1 AS card_fee, \n",
"\t\tWEEK(p.created_at - INTERVAL {adjust} HOUR) AS WEEK\n",
"FROM payouts p\n",
"WHERE WEEK(p.created_at - INTERVAL {adjust} HOUR) BETWEEN {WEEK_from} AND {WEEK_to} AND p.status IN (10,20,40)\n",
"GROUP BY WEEK\"\"\""
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 12
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"query_fees_us = query.format(WEEK_from=start_week,\n",
" WEEK_to=current_week,\n",
" adjust=7,\n",
" p_rate=0.25,\n",
" cc_rate=0.027,\n",
" cc_fix_rate=0.3)\n",
"\n",
"query_fees_uk = query.format(WEEK_from=start_week,\n",
" WEEK_to=current_week,\n",
" adjust=0,\n",
" p_rate=0.45,\n",
" cc_rate=0.022,\n",
" cc_fix_rate=0.25)"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 13
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"cnx = mysql.connector.connect(user='jjarutis', \n",
" password='nh627HjqPlz78Dh',\n",
" host='hx-db-analytics1.vinted.net',\n",
" port=3312,\n",
" database='vinted_us')\n",
"cursor = cnx.cursor()\n",
"cursor.execute(query_fees_us)\n",
"fees_us = pd.DataFrame(cursor.fetchall())\n",
"fees_us.columns = [i[0] for i in cursor.description]\n",
"cnx.close()"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 14
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"cnx = mysql.connector.connect(user='jjarutis', \n",
" password='nh627HjqPlz78Dh',\n",
" host='hx-db-analytics4.vinted.net',\n",
" port=3314,\n",
" database='vinted_uk')\n",
"cursor = cnx.cursor()\n",
"cursor.execute(query_fees_uk)\n",
"fees_uk = pd.DataFrame(cursor.fetchall())\n",
"fees_uk.columns = [i[0] for i in cursor.description]\n",
"cnx.close()"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 15
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"fees_us_final = fees_us.pivot(index='WEEK', columns='fee_type', values='card_fee')\n",
"fees_us_final.columns = ['fee_type:' + x.lower().replace(\" \", \"_\") + ':us' for x in fees_us_final.columns]\n",
"fees_us_final['week'] = fees_us_final.index"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 16
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"fees_uk_final = fees_uk.pivot(index='WEEK', columns='fee_type', values='card_fee')\n",
"fees_uk_final.columns = ['fee_type:' + x.lower().replace(\" \", \"_\") + ':uk' for x in fees_uk_final.columns]\n",
"fees_uk_final['week'] = fees_us_final.index"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 17
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"fees = pd.merge(fees_uk_final, fees_us_final, on='week', how='outer')"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 18
},
{
"cell_type": "heading",
"level": 2,
"metadata": {},
"source": [
"Combine everything together"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"df1 = pd.merge(finance, fees, on='week', how='outer')\n",
"df = pd.merge(analytics, df1, on='period', how='left')"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 19
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"df = df.fillna(0)"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 20
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"df.week = ['W%.0f' % x for x in df.week]\n",
"df = df.set_index('week')\n",
"df = df.drop('W0')"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 21
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"def percent_to_float(metric):\n",
" return float(str(metric).translate(None, '%')) / 100.0"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 22
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"def map_metrics(df, metric1, metric2, result_metric, f):\n",
" def get_metric(df, metric):\n",
" df = df[[x for x in df.columns if metric in x]]\n",
" df.columns = [x.replace(metric, '') for x in df.columns]\n",
" return df\n",
" df1 = get_metric(df, metric1)\n",
" df2 = get_metric(df, metric2)\n",
" keys = set(df1.columns).intersection(df2.columns)\n",
" for key in keys:\n",
" df[result_metric + key] = f(df1[key], df2[key])\n",
" return df"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 23
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"retention_cols = [x for x in df.columns if 'retention' in x]\n",
"df[retention_cols] = df[retention_cols].apply(lambda x: x.apply(percent_to_float).shift(1)) # shift retention by 1 week"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 24
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"df['total_spend'] = df['total_spend:us'] + df['total_spend:uk']\n",
"df['total_paid_members'] = df['total_paid_members:us'] + df['total_paid_members:uk']\n",
"df['organic_members'] = df['new_members'] - df['total_paid_members']\n",
"df['organic_members:us'] = df['new_members:us'] - df['total_paid_members:us']\n",
"df['organic_members:uk'] = df['new_members:uk'] - df['total_paid_members:uk']\n",
"df['cogs:uk'] = - df['fee_type:card_processing:uk'] - df['fee_type:card_processing_fee_refunded:uk']\n",
"\n",
"df['cogs:us'] = - df['fee_type:card_processing:us'].apply(float) - \\\n",
" df['fee_type:card_processing_fee_refunded:us'].apply(float) - \\\n",
" df['expenses:endicia_expenses:us'] - \\\n",
" df['counter_expenses:endicia_refunds:us']\n",
"\n",
"df['revenues:us'] = df['income:marketplace_fee_income_total:us'] + \\\n",
" df['counter_income:marketplace_fee_income_refunded:us'] + \\\n",
" df['income:shipping_extra_income_total:us'] + \\\n",
" df['counter_income:shipping_extra_income_refunded:us'] + \\\n",
" df['income:shipping_income_total:us'] + \\\n",
" df['counter_income:shipping_income_refunded:us']\n",
" \n",
"df['revenues:uk'] = df['income:marketplace_fee_income_total:uk'] + df['counter_income:marketplace_fee_income_refunded:uk']\n",
"df['net_revenues:us'] = df['revenues:us'] - df['cogs:us']"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 25
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"print query_fees_us"
],
"language": "python",
"metadata": {},
"outputs": [
{
"output_type": "stream",
"stream": "stdout",
"text": [
"\n",
"SELECT \t\"Marketplace share\" AS fee_type, round(sum(bd.`marketplace_share`), 2) AS card_fee, \n",
"\t\tWEEK(created_at - INTERVAL 7 HOUR) AS WEEK\n",
"FROM buyer_debits bd\n",
"WHERE WEEK(created_at - INTERVAL 7 HOUR) BETWEEN 21 AND 45\n",
"AND STATUS > 30\n",
"GROUP BY WEEK\n",
"UNION\n",
"SELECT \t\"marketplace share refunded\" AS fee_type, round(sum(bd.`marketplace_share` ), 2)*-1 AS card_fee, \n",
"\t\tWEEK(br.created_at - INTERVAL 7 HOUR) AS WEEK\n",
"FROM buyer_refunds br\n",
"JOIN buyer_debits bd\n",
"ON br.`buyer_debit_id` = bd.id AND bd.status IN (90, 100)\n",
"WHERE WEEK(br.created_at - INTERVAL 7 HOUR) BETWEEN 21 AND 45\n",
"GROUP BY WEEK\n",
"UNION\n",
"SELECT \t\"Card processing\" AS fee_type, round(sum(pay_in_amount*0.027 + 0.3), 2)*-1 AS card_fee, \n",
"\t\tWEEK(created_at - INTERVAL 7 HOUR) AS WEEK\n",
"FROM buyer_debits bd\n",
"WHERE WEEK(created_at - INTERVAL 7 HOUR) BETWEEN 21 AND 45\n",
"AND STATUS > 30\n",
"GROUP BY WEEK\n",
"union\n",
"SELECT \t\"Card processing fee refunded\" AS fee_type, round(sum(br.amount*0.027 ), 2) AS card_fee, \n",
"\t\tWEEK(br.created_at - INTERVAL 7 HOUR) AS WEEK\n",
"FROM buyer_refunds br\n",
"JOIN buyer_debits bd\n",
"ON br.`buyer_debit_id` = bd.id AND bd.status IN (90, 100)\n",
"WHERE WEEK(br.created_at - INTERVAL 7 HOUR) BETWEEN 21 AND 45\n",
"GROUP BY WEEK\n",
"UNION\n",
"SELECT \t\"Payout\" AS fee_type, round(count(id)*0.25, 2)*-1 AS card_fee, \n",
"\t\tWEEK(p.created_at - INTERVAL 7 HOUR) AS WEEK\n",
"FROM payouts p\n",
"WHERE WEEK(p.created_at - INTERVAL 7 HOUR) BETWEEN 21 AND 45 AND p.status IN (10,20,40)\n",
"GROUP BY WEEK\n"
]
}
],
"prompt_number": 26
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Fix transaction new report metrics per AU"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"df = map_metrics(df, 'tx_count', 'au_count', 'tx_per_au', lambda x, y: x.apply(float) / y)\n",
"df = map_metrics(df, 'gmv', 'au_count', 'gmv_per_au', lambda x, y: x.apply(float) / y)"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 27
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"df = df.loc[['W' + str(x) for x in range(start_week, current_week + 1)], :]"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 28
},
{
"cell_type": "heading",
"level": 2,
"metadata": {},
"source": [
"Function to format data"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"def get_metrics(df, metrics, weeks, portal=''):\n",
" add_portal = portal if portal == '' else ':' + portal\n",
" selected_cols = [x + add_portal for x in metrics.values()]\n",
" names = metrics.keys()\n",
" result = df.loc[weeks, selected_cols].T\n",
" result = result.set_index([names])\n",
" result['change %'] = result[weeks[1]].apply(float) / result[weeks[0]] - 1\n",
" return result\n",
"\n",
"def metric_by_portal(df, metric, weeks, portals):\n",
" selected_cols = [metric] + [metric + ':' + x for x in portals.values()]\n",
" names = ['total'] + portals.keys()\n",
" result = df.loc[weeks, selected_cols].T\n",
" result = result.set_index([names])\n",
" result['order'] = [1] + [0] * len(portals)\n",
" result = result.sort(['order', weeks[1]], ascending=[0, 0])\n",
" result = result.drop('order', 1)\n",
" result['change %'] = result[weeks[1]].apply(float) / result[weeks[0]] - 1\n",
" return result\n",
"\n",
"def metrics_for_chart1(df, metric, portals):\n",
" selected_cols = [metric] + [metric + ':' + x for x in portals.values()]\n",
" names = ['Global'] + portals.keys()\n",
" result = df.loc[:, selected_cols].T\n",
" result.columns = [x.strftime('%Y-%m-%d') for x in df.period]\n",
" result = result.set_index([names])\n",
" return result\n",
"\n",
"def metrics_for_chart_portal(df, metrics, portal):\n",
" add_portal = portal if portal == '' else ':' + portal\n",
" selected_cols = [x + add_portal for x in metrics.values()]\n",
" names = metrics.keys()\n",
" result = df.loc[:, selected_cols].T\n",
" result.columns = [x.strftime('%Y-%m-%d') for x in df.period]\n",
" result = result.set_index([names])\n",
" return result\n",
"\n",
"def metrics_for_chart2(df, metric, portals):\n",
" selected_cols = [metric + ':' + x for x in portals.values()]\n",
" names = portals.keys()\n",
" result = pd.DataFrame(df.loc['W' + str(current_week), selected_cols]).T\n",
" result.columns = names\n",
" return result\n",
"\n",
"def default_slide_data(writer, metric, portals, portals_chart, slide_nr):\n",
" slide_chart1 = metrics_for_chart1(df, metric, portals_chart)\n",
" slide_chart1.to_excel(writer, 'Slide %d' % slide_nr, startrow=1, startcol=1)\n",
" slide_table2 = metric_by_portal(df, metric, weeks, portals)\n",
" slide_table2.to_excel(writer, 'Slide %d' % slide_nr, startrow=10, startcol=1)\n",
" slide_chart3 = metrics_for_chart2(df, metric, portals)\n",
" slide_chart3.to_excel(writer, 'Slide %d' % slide_nr, startrow=30, startcol=1)"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 29
},
{
"cell_type": "heading",
"level": 1,
"metadata": {},
"source": [
"Generate data for slides"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"writer = pd.ExcelWriter('newsletter.xlsx', engine='xlsxwriter')"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 30
},
{
"cell_type": "heading",
"level": 2,
"metadata": {},
"source": [
"General Metrics "
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"metrics = OrderedDict([('WAU','au_count'),\n",
" ('New Organic Members','organic_members'),\n",
" ('New Paid Members','total_paid_members'),\n",
" ('Marketing Spend, EUR','total_spend'),\n",
" ('New Listings','new_listings'),\n",
" ('New Msg. Threads','msg_threads'),\n",
" ('New Transactions','tx_count'),\n",
" ('Tx per WAU','tx_per_au'),\n",
" ('GMV','gmv'),\n",
" ('GMV per WAU','gmv_per_au'),\n",
" ('Avg. Order Value','avg_tx_price')])\n",
"slide2_table1 = get_metrics(df, metrics, weeks)\n",
"slide2_table1.to_excel(writer, 'Slide 2', startrow=1, startcol=1)\n",
"slide2_table1"
],
"language": "python",
"metadata": {},
"outputs": [
{
"html": [
"<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>W44</th>\n",
" <th>W45</th>\n",
" <th>change %</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>WAU</th>\n",
" <td> 1522401.000000</td>\n",
" <td> 1448540.000000</td>\n",
" <td>-0.048516</td>\n",
" </tr>\n",
" <tr>\n",
" <th>New Organic Members</th>\n",
" <td> 86509.000000</td>\n",
" <td> 84494.000000</td>\n",
" <td>-0.023292</td>\n",
" </tr>\n",
" <tr>\n",
" <th>New Paid Members</th>\n",
" <td> 28440.000000</td>\n",
" <td> 16337.000000</td>\n",
" <td>-0.425563</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Marketing Spend, EUR</th>\n",
" <td> 106478.339370</td>\n",
" <td> 64985.000000</td>\n",
" <td>-0.389688</td>\n",
" </tr>\n",
" <tr>\n",
" <th>New Listings</th>\n",
" <td> 999286.000000</td>\n",
" <td> 967139.000000</td>\n",
" <td>-0.032170</td>\n",
" </tr>\n",
" <tr>\n",
" <th>New Msg. Threads</th>\n",
" <td> 1725220.000000</td>\n",
" <td> 1654137.000000</td>\n",
" <td>-0.041202</td>\n",
" </tr>\n",
" <tr>\n",
" <th>New Transactions</th>\n",
" <td> 176083.000000</td>\n",
" <td> 196531.000000</td>\n",
" <td> 0.116127</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Tx per WAU</th>\n",
" <td> 0.115661</td>\n",
" <td> 0.135675</td>\n",
" <td> 0.173038</td>\n",
" </tr>\n",
" <tr>\n",
" <th>GMV</th>\n",
" <td> 2227381.170000</td>\n",
" <td> 2560160.140000</td>\n",
" <td> 0.149404</td>\n",
" </tr>\n",
" <tr>\n",
" <th>GMV per WAU</th>\n",
" <td> 1.463071</td>\n",
" <td> 1.767407</td>\n",
" <td> 0.208012</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Avg. Order Value</th>\n",
" <td> 13.304788</td>\n",
" <td> 13.557729</td>\n",
" <td> 0.019011</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"metadata": {},
"output_type": "pyout",
"prompt_number": 31,
"text": [
" W44 W45 change %\n",
"WAU 1522401.000000 1448540.000000 -0.048516\n",
"New Organic Members 86509.000000 84494.000000 -0.023292\n",
"New Paid Members 28440.000000 16337.000000 -0.425563\n",
"Marketing Spend, EUR 106478.339370 64985.000000 -0.389688\n",
"New Listings 999286.000000 967139.000000 -0.032170\n",
"New Msg. Threads 1725220.000000 1654137.000000 -0.041202\n",
"New Transactions 176083.000000 196531.000000 0.116127\n",
"Tx per WAU 0.115661 0.135675 0.173038\n",
"GMV 2227381.170000 2560160.140000 0.149404\n",
"GMV per WAU 1.463071 1.767407 0.208012\n",
"Avg. Order Value 13.304788 13.557729 0.019011"
]
}
],
"prompt_number": 31
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"metrics = OrderedDict([('WAU','au_count'),\n",
" ('New Organic Members','organic_members'),\n",
" ('New Paid Members','total_paid_members'),\n",
" ('Marketing Spend, EUR','total_spend'),\n",
" ('New Listings','new_listings'),\n",
" ('New Transactions','tx_count'),\n",
" ('GMV','gmv'),\n",
" ('Revenues EUR','income'),\n",
" ('Net Revenues EUR','net_revenues'),\n",
" ('2nd week retention','2nd_week_member_retention:ios'),\n",
" ('5th week retention','5th_week_member_retention:ios')])\n",
"slide2_table2 = get_metrics(df, metrics, weeks, 'us')\n",
"slide2_table2.to_excel(writer, 'Slide 2', startrow=1, startcol=10)\n",
"slide2_table2"
],
"language": "python",
"metadata": {},
"outputs": [
{
"html": [
"<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>W44</th>\n",
" <th>W45</th>\n",
" <th>change %</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>WAU</th>\n",
" <td> 191006.00000</td>\n",
" <td> 182779.0000</td>\n",
" <td>-0.043072</td>\n",
" </tr>\n",
" <tr>\n",
" <th>New Organic Members</th>\n",
" <td> 20691.00000</td>\n",
" <td> 22728.0000</td>\n",
" <td> 0.098449</td>\n",
" </tr>\n",
" <tr>\n",
" <th>New Paid Members</th>\n",
" <td> 28423.00000</td>\n",
" <td> 13292.0000</td>\n",
" <td>-0.532351</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Marketing Spend, EUR</th>\n",
" <td> 106365.09937</td>\n",
" <td> 50422.0000</td>\n",
" <td>-0.525954</td>\n",
" </tr>\n",
" <tr>\n",
" <th>New Listings</th>\n",
" <td> 95599.00000</td>\n",
" <td> 105357.0000</td>\n",
" <td> 0.102072</td>\n",
" </tr>\n",
" <tr>\n",
" <th>New Transactions</th>\n",
" <td> 24396.00000</td>\n",
" <td> 27111.0000</td>\n",
" <td> 0.111289</td>\n",
" </tr>\n",
" <tr>\n",
" <th>GMV</th>\n",
" <td> 215702.56000</td>\n",
" <td> 244817.8800</td>\n",
" <td> 0.134979</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Revenues EUR</th>\n",
" <td> 115167.47000</td>\n",
" <td> 127739.7300</td>\n",
" <td> 0.109165</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Net Revenues EUR</th>\n",
" <td> 10118.36000</td>\n",
" <td> 19474.8400</td>\n",
" <td> 0.924703</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2nd week retention</th>\n",
" <td> 0.37020</td>\n",
" <td> 0.3681</td>\n",
" <td>-0.005673</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5th week retention</th>\n",
" <td> 0.19630</td>\n",
" <td> 0.2188</td>\n",
" <td> 0.114620</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"metadata": {},
"output_type": "pyout",
"prompt_number": 32,
"text": [
" W44 W45 change %\n",
"WAU 191006.00000 182779.0000 -0.043072\n",
"New Organic Members 20691.00000 22728.0000 0.098449\n",
"New Paid Members 28423.00000 13292.0000 -0.532351\n",
"Marketing Spend, EUR 106365.09937 50422.0000 -0.525954\n",
"New Listings 95599.00000 105357.0000 0.102072\n",
"New Transactions 24396.00000 27111.0000 0.111289\n",
"GMV 215702.56000 244817.8800 0.134979\n",
"Revenues EUR 115167.47000 127739.7300 0.109165\n",
"Net Revenues EUR 10118.36000 19474.8400 0.924703\n",
"2nd week retention 0.37020 0.3681 -0.005673\n",
"5th week retention 0.19630 0.2188 0.114620"
]
}
],
"prompt_number": 32
},
{
"cell_type": "heading",
"level": 2,
"metadata": {},
"source": [
"Default metric slides"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"default_slide_data(writer, 'au_count', portals, portals_chart, 4)\n",
"default_slide_data(writer, 'new_members', portals, portals_chart, 5)\n",
"default_slide_data(writer, 'new_listings', portals, portals_chart, 6)\n",
"default_slide_data(writer, 'tx_count', portals, portals_chart, 7)\n",
"default_slide_data(writer, 'tx_per_au', portals, portals_chart, 8)\n",
"default_slide_data(writer, 'gmv', portals, portals_chart, 9)\n",
"default_slide_data(writer, 'gmv_per_au', portals, portals_chart, 10)\n",
"default_slide_data(writer, 'avg_tx_price', portals, portals_chart, 11)"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 33
},
{
"cell_type": "heading",
"level": 2,
"metadata": {},
"source": [
"Payments system: refunds"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"slide12_chart1 = metrics_for_chart1(df, 'refunds_percentage', {'UK':'uk','US':'us'})\n",
"slide12_chart1 = slide12_chart1.drop('Global')\n",
"slide12_chart1 = slide12_chart1.iloc[:,0:(slide12_chart1.shape[1]-2)]\n",
"slide12_chart1.to_excel(writer, 'Slide 12', startrow=1, startcol=1)\n",
"slide12_chart1"
],
"language": "python",
"metadata": {},
"outputs": [
{
"html": [
"<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>2014-05-25</th>\n",
" <th>2014-06-01</th>\n",
" <th>2014-06-08</th>\n",
" <th>2014-06-15</th>\n",
" <th>2014-06-22</th>\n",
" <th>2014-06-29</th>\n",
" <th>2014-07-06</th>\n",
" <th>2014-07-13</th>\n",
" <th>2014-07-20</th>\n",
" <th>2014-07-27</th>\n",
" <th>...</th>\n",
" <th>2014-08-24</th>\n",
" <th>2014-08-31</th>\n",
" <th>2014-09-07</th>\n",
" <th>2014-09-14</th>\n",
" <th>2014-09-21</th>\n",
" <th>2014-09-28</th>\n",
" <th>2014-10-05</th>\n",
" <th>2014-10-12</th>\n",
" <th>2014-10-19</th>\n",
" <th>2014-10-26</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>US</th>\n",
" <td> 8.217705</td>\n",
" <td> 7.547607</td>\n",
" <td> 8.097103</td>\n",
" <td> 8.649102</td>\n",
" <td> 8.745575</td>\n",
" <td> 8.714142</td>\n",
" <td> 9.069731</td>\n",
" <td> 7.766458</td>\n",
" <td> 7.511184</td>\n",
" <td> 7.713530</td>\n",
" <td>...</td>\n",
" <td> 7.663918</td>\n",
" <td> 9.078218</td>\n",
" <td> 8.577770</td>\n",
" <td> 9.066857</td>\n",
" <td> 8.922651</td>\n",
" <td> 9.230018</td>\n",
" <td> 9.274312</td>\n",
" <td> 9.386323</td>\n",
" <td> 8.490489</td>\n",
" <td> 9.259765</td>\n",
" </tr>\n",
" <tr>\n",
" <th>UK</th>\n",
" <td> 7.597709</td>\n",
" <td> 6.705491</td>\n",
" <td> 6.851244</td>\n",
" <td> 6.665609</td>\n",
" <td> 7.069334</td>\n",
" <td> 7.558860</td>\n",
" <td> 8.425516</td>\n",
" <td> 8.751683</td>\n",
" <td> 9.208659</td>\n",
" <td> 9.382872</td>\n",
" <td>...</td>\n",
" <td> 9.082550</td>\n",
" <td> 8.927519</td>\n",
" <td> 8.157068</td>\n",
" <td> 8.534118</td>\n",
" <td> 9.451053</td>\n",
" <td> 9.280742</td>\n",
" <td> 9.765555</td>\n",
" <td> 8.643604</td>\n",
" <td> 9.263370</td>\n",
" <td> 8.441360</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>2 rows \u00d7 23 columns</p>\n",
"</div>"
],
"metadata": {},
"output_type": "pyout",
"prompt_number": 34,
"text": [
" 2014-05-25 2014-06-01 2014-06-08 2014-06-15 2014-06-22 2014-06-29 \\\n",
"US 8.217705 7.547607 8.097103 8.649102 8.745575 8.714142 \n",
"UK 7.597709 6.705491 6.851244 6.665609 7.069334 7.558860 \n",
"\n",
" 2014-07-06 2014-07-13 2014-07-20 2014-07-27 ... 2014-08-24 \\\n",
"US 9.069731 7.766458 7.511184 7.713530 ... 7.663918 \n",
"UK 8.425516 8.751683 9.208659 9.382872 ... 9.082550 \n",
"\n",
" 2014-08-31 2014-09-07 2014-09-14 2014-09-21 2014-09-28 2014-10-05 \\\n",
"US 9.078218 8.577770 9.066857 8.922651 9.230018 9.274312 \n",
"UK 8.927519 8.157068 8.534118 9.451053 9.280742 9.765555 \n",
"\n",
" 2014-10-12 2014-10-19 2014-10-26 \n",
"US 9.386323 8.490489 9.259765 \n",
"UK 8.643604 9.263370 8.441360 \n",
"\n",
"[2 rows x 23 columns]"
]
}
],
"prompt_number": 34
},
{
"cell_type": "heading",
"level": 2,
"metadata": {},
"source": [
"Retention"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"slide13_chart1 = metrics_for_chart1(df, '2nd_week_member_retention:ios', ret_portals)\n",
"slide13_chart1 = slide13_chart1.drop('Global')\n",
"slide13_chart1.to_excel(writer, 'Slide 13', startrow=1, startcol=1)\n",
"slide13_chart1"
],
"language": "python",
"metadata": {},
"outputs": [
{
"html": [
"<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>2014-05-25</th>\n",
" <th>2014-06-01</th>\n",
" <th>2014-06-08</th>\n",
" <th>2014-06-15</th>\n",
" <th>2014-06-22</th>\n",
" <th>2014-06-29</th>\n",
" <th>2014-07-06</th>\n",
" <th>2014-07-13</th>\n",
" <th>2014-07-20</th>\n",
" <th>2014-07-27</th>\n",
" <th>...</th>\n",
" <th>2014-09-07</th>\n",
" <th>2014-09-14</th>\n",
" <th>2014-09-21</th>\n",
" <th>2014-09-28</th>\n",
" <th>2014-10-05</th>\n",
" <th>2014-10-12</th>\n",
" <th>2014-10-19</th>\n",
" <th>2014-10-26</th>\n",
" <th>2014-11-02</th>\n",
" <th>2014-11-09</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>DE</th>\n",
" <td> 0.5674</td>\n",
" <td> 0.5627</td>\n",
" <td> 0.5502</td>\n",
" <td> 0.5409</td>\n",
" <td> 0.5484</td>\n",
" <td> 0.5387</td>\n",
" <td> 0.5442</td>\n",
" <td> 0.5317</td>\n",
" <td> 0.5333</td>\n",
" <td> 0.5191</td>\n",
" <td>...</td>\n",
" <td> 0.5749</td>\n",
" <td> 0.5776</td>\n",
" <td> 0.5492</td>\n",
" <td> 0.5446</td>\n",
" <td> 0.5184</td>\n",
" <td> 0.5201</td>\n",
" <td> 0.5503</td>\n",
" <td> 0.5386</td>\n",
" <td> 0.5830</td>\n",
" <td> 0.5420</td>\n",
" </tr>\n",
" <tr>\n",
" <th>US</th>\n",
" <td> 0.3699</td>\n",
" <td> 0.4153</td>\n",
" <td> 0.4004</td>\n",
" <td> 0.4234</td>\n",
" <td> 0.4290</td>\n",
" <td> 0.4247</td>\n",
" <td> 0.4380</td>\n",
" <td> 0.5044</td>\n",
" <td> 0.5661</td>\n",
" <td> 0.4618</td>\n",
" <td>...</td>\n",
" <td> 0.3459</td>\n",
" <td> 0.3344</td>\n",
" <td> 0.3166</td>\n",
" <td> 0.3674</td>\n",
" <td> 0.3620</td>\n",
" <td> 0.3692</td>\n",
" <td> 0.4496</td>\n",
" <td> 0.4247</td>\n",
" <td> 0.3702</td>\n",
" <td> 0.3681</td>\n",
" </tr>\n",
" <tr>\n",
" <th>UK</th>\n",
" <td> 0.4456</td>\n",
" <td> 0.4698</td>\n",
" <td> 0.4519</td>\n",
" <td> 0.4632</td>\n",
" <td> 0.4548</td>\n",
" <td> 0.4533</td>\n",
" <td> 0.4665</td>\n",
" <td> 0.4768</td>\n",
" <td> 0.4810</td>\n",
" <td> 0.4596</td>\n",
" <td>...</td>\n",
" <td> 0.4168</td>\n",
" <td> 0.4396</td>\n",
" <td> 0.4144</td>\n",
" <td> 0.4357</td>\n",
" <td> 0.4627</td>\n",
" <td> 0.4503</td>\n",
" <td> 0.4463</td>\n",
" <td> 0.4099</td>\n",
" <td> 0.3940</td>\n",
" <td> 0.4209</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>3 rows \u00d7 25 columns</p>\n",
"</div>"
],
"metadata": {},
"output_type": "pyout",
"prompt_number": 35,
"text": [
" 2014-05-25 2014-06-01 2014-06-08 2014-06-15 2014-06-22 2014-06-29 \\\n",
"DE 0.5674 0.5627 0.5502 0.5409 0.5484 0.5387 \n",
"US 0.3699 0.4153 0.4004 0.4234 0.4290 0.4247 \n",
"UK 0.4456 0.4698 0.4519 0.4632 0.4548 0.4533 \n",
"\n",
" 2014-07-06 2014-07-13 2014-07-20 2014-07-27 ... 2014-09-07 \\\n",
"DE 0.5442 0.5317 0.5333 0.5191 ... 0.5749 \n",
"US 0.4380 0.5044 0.5661 0.4618 ... 0.3459 \n",
"UK 0.4665 0.4768 0.4810 0.4596 ... 0.4168 \n",
"\n",
" 2014-09-14 2014-09-21 2014-09-28 2014-10-05 2014-10-12 2014-10-19 \\\n",
"DE 0.5776 0.5492 0.5446 0.5184 0.5201 0.5503 \n",
"US 0.3344 0.3166 0.3674 0.3620 0.3692 0.4496 \n",
"UK 0.4396 0.4144 0.4357 0.4627 0.4503 0.4463 \n",
"\n",
" 2014-10-26 2014-11-02 2014-11-09 \n",
"DE 0.5386 0.5830 0.5420 \n",
"US 0.4247 0.3702 0.3681 \n",
"UK 0.4099 0.3940 0.4209 \n",
"\n",
"[3 rows x 25 columns]"
]
}
],
"prompt_number": 35
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"slide13_chart2 = metrics_for_chart1(df, '5th_week_member_retention:ios', ret_portals)\n",
"slide13_chart2 = slide13_chart2.drop('Global')\n",
"slide13_chart2.to_excel(writer, 'Slide 13', startrow=10, startcol=1)\n",
"slide13_chart2"
],
"language": "python",
"metadata": {},
"outputs": [
{
"html": [
"<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>2014-05-25</th>\n",
" <th>2014-06-01</th>\n",
" <th>2014-06-08</th>\n",
" <th>2014-06-15</th>\n",
" <th>2014-06-22</th>\n",
" <th>2014-06-29</th>\n",
" <th>2014-07-06</th>\n",
" <th>2014-07-13</th>\n",
" <th>2014-07-20</th>\n",
" <th>2014-07-27</th>\n",
" <th>...</th>\n",
" <th>2014-09-07</th>\n",
" <th>2014-09-14</th>\n",
" <th>2014-09-21</th>\n",
" <th>2014-09-28</th>\n",
" <th>2014-10-05</th>\n",
" <th>2014-10-12</th>\n",
" <th>2014-10-19</th>\n",
" <th>2014-10-26</th>\n",
" <th>2014-11-02</th>\n",
" <th>2014-11-09</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>DE</th>\n",
" <td> 0.4199</td>\n",
" <td> 0.4252</td>\n",
" <td> 0.4000</td>\n",
" <td> 0.4150</td>\n",
" <td> 0.4118</td>\n",
" <td> 0.4009</td>\n",
" <td> 0.3913</td>\n",
" <td> 0.3756</td>\n",
" <td> 0.3698</td>\n",
" <td> 0.3790</td>\n",
" <td>...</td>\n",
" <td> 0.4320</td>\n",
" <td> 0.4389</td>\n",
" <td> 0.4321</td>\n",
" <td> 0.4082</td>\n",
" <td> 0.3997</td>\n",
" <td> 0.3894</td>\n",
" <td> 0.3950</td>\n",
" <td> 0.3897</td>\n",
" <td> 0.4363</td>\n",
" <td> 0.4247</td>\n",
" </tr>\n",
" <tr>\n",
" <th>US</th>\n",
" <td> 0.2085</td>\n",
" <td> 0.1979</td>\n",
" <td> 0.1795</td>\n",
" <td> 0.2088</td>\n",
" <td> 0.2398</td>\n",
" <td> 0.2287</td>\n",
" <td> 0.2395</td>\n",
" <td> 0.2430</td>\n",
" <td> 0.2380</td>\n",
" <td> 0.2491</td>\n",
" <td>...</td>\n",
" <td> 0.2215</td>\n",
" <td> 0.2153</td>\n",
" <td> 0.2259</td>\n",
" <td> 0.1551</td>\n",
" <td> 0.1564</td>\n",
" <td> 0.1664</td>\n",
" <td> 0.1958</td>\n",
" <td> 0.1923</td>\n",
" <td> 0.1963</td>\n",
" <td> 0.2188</td>\n",
" </tr>\n",
" <tr>\n",
" <th>UK</th>\n",
" <td> 0.2435</td>\n",
" <td> 0.2398</td>\n",
" <td> 0.2227</td>\n",
" <td> 0.2166</td>\n",
" <td> 0.2442</td>\n",
" <td> 0.2362</td>\n",
" <td> 0.2362</td>\n",
" <td> 0.2298</td>\n",
" <td> 0.2231</td>\n",
" <td> 0.2351</td>\n",
" <td>...</td>\n",
" <td> 0.2300</td>\n",
" <td> 0.2351</td>\n",
" <td> 0.2299</td>\n",
" <td> 0.2385</td>\n",
" <td> 0.2259</td>\n",
" <td> 0.2189</td>\n",
" <td> 0.2427</td>\n",
" <td> 0.2403</td>\n",
" <td> 0.2342</td>\n",
" <td> 0.2315</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>3 rows \u00d7 25 columns</p>\n",
"</div>"
],
"metadata": {},
"output_type": "pyout",
"prompt_number": 36,
"text": [
" 2014-05-25 2014-06-01 2014-06-08 2014-06-15 2014-06-22 2014-06-29 \\\n",
"DE 0.4199 0.4252 0.4000 0.4150 0.4118 0.4009 \n",
"US 0.2085 0.1979 0.1795 0.2088 0.2398 0.2287 \n",
"UK 0.2435 0.2398 0.2227 0.2166 0.2442 0.2362 \n",
"\n",
" 2014-07-06 2014-07-13 2014-07-20 2014-07-27 ... 2014-09-07 \\\n",
"DE 0.3913 0.3756 0.3698 0.3790 ... 0.4320 \n",
"US 0.2395 0.2430 0.2380 0.2491 ... 0.2215 \n",
"UK 0.2362 0.2298 0.2231 0.2351 ... 0.2300 \n",
"\n",
" 2014-09-14 2014-09-21 2014-09-28 2014-10-05 2014-10-12 2014-10-19 \\\n",
"DE 0.4389 0.4321 0.4082 0.3997 0.3894 0.3950 \n",
"US 0.2153 0.2259 0.1551 0.1564 0.1664 0.1958 \n",
"UK 0.2351 0.2299 0.2385 0.2259 0.2189 0.2427 \n",
"\n",
" 2014-10-26 2014-11-02 2014-11-09 \n",
"DE 0.3897 0.4363 0.4247 \n",
"US 0.1923 0.1963 0.2188 \n",
"UK 0.2403 0.2342 0.2315 \n",
"\n",
"[3 rows x 25 columns]"
]
}
],
"prompt_number": 36
},
{
"cell_type": "heading",
"level": 2,
"metadata": {},
"source": [
"UK Weekly Metrics"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"slide15_chart1 = metrics_for_chart_portal(df, {'WAU, since launch':'au_count'}, 'uk')\n",
"slide15_chart1.to_excel(writer, 'Slide 15', startrow=1, startcol=1)\n",
"slide15_chart1"
],
"language": "python",
"metadata": {},
"outputs": [
{
"html": [
"<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>2014-05-25</th>\n",
" <th>2014-06-01</th>\n",
" <th>2014-06-08</th>\n",
" <th>2014-06-15</th>\n",
" <th>2014-06-22</th>\n",
" <th>2014-06-29</th>\n",
" <th>2014-07-06</th>\n",
" <th>2014-07-13</th>\n",
" <th>2014-07-20</th>\n",
" <th>2014-07-27</th>\n",
" <th>...</th>\n",
" <th>2014-09-07</th>\n",
" <th>2014-09-14</th>\n",
" <th>2014-09-21</th>\n",
" <th>2014-09-28</th>\n",
" <th>2014-10-05</th>\n",
" <th>2014-10-12</th>\n",
" <th>2014-10-19</th>\n",
" <th>2014-10-26</th>\n",
" <th>2014-11-02</th>\n",
" <th>2014-11-09</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>WAU, since launch</th>\n",
" <td> 41088</td>\n",
" <td> 38004</td>\n",
" <td> 42691</td>\n",
" <td> 40962</td>\n",
" <td> 40101</td>\n",
" <td> 38780</td>\n",
" <td> 38140</td>\n",
" <td> 38489</td>\n",
" <td> 43519</td>\n",
" <td> 49053</td>\n",
" <td>...</td>\n",
" <td> 66444</td>\n",
" <td> 74763</td>\n",
" <td> 67339</td>\n",
" <td> 66222</td>\n",
" <td> 65971</td>\n",
" <td> 61852</td>\n",
" <td> 65935</td>\n",
" <td> 64276</td>\n",
" <td> 56095</td>\n",
" <td> 55713</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>1 rows \u00d7 25 columns</p>\n",
"</div>"
],
"metadata": {},
"output_type": "pyout",
"prompt_number": 37,
"text": [
" 2014-05-25 2014-06-01 2014-06-08 2014-06-15 2014-06-22 \\\n",
"WAU, since launch 41088 38004 42691 40962 40101 \n",
"\n",
" 2014-06-29 2014-07-06 2014-07-13 2014-07-20 2014-07-27 \\\n",
"WAU, since launch 38780 38140 38489 43519 49053 \n",
"\n",
" ... 2014-09-07 2014-09-14 2014-09-21 2014-09-28 \\\n",
"WAU, since launch ... 66444 74763 67339 66222 \n",
"\n",
" 2014-10-05 2014-10-12 2014-10-19 2014-10-26 2014-11-02 \\\n",
"WAU, since launch 65971 61852 65935 64276 56095 \n",
"\n",
" 2014-11-09 \n",
"WAU, since launch 55713 \n",
"\n",
"[1 rows x 25 columns]"
]
}
],
"prompt_number": 37
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"metrics = OrderedDict([('All','new_members'), \n",
" ('Paid','total_paid_members'), \n",
" ('Organic','organic_members')])\n",
"slide15_chart2 = metrics_for_chart_portal(df, metrics, 'uk')\n",
"slide15_chart2.to_excel(writer, 'Slide 15', startrow=10, startcol=1)\n",
"slide15_chart2"
],
"language": "python",
"metadata": {},
"outputs": [
{
"html": [
"<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>2014-05-25</th>\n",
" <th>2014-06-01</th>\n",
" <th>2014-06-08</th>\n",
" <th>2014-06-15</th>\n",
" <th>2014-06-22</th>\n",
" <th>2014-06-29</th>\n",
" <th>2014-07-06</th>\n",
" <th>2014-07-13</th>\n",
" <th>2014-07-20</th>\n",
" <th>2014-07-27</th>\n",
" <th>...</th>\n",
" <th>2014-09-07</th>\n",
" <th>2014-09-14</th>\n",
" <th>2014-09-21</th>\n",
" <th>2014-09-28</th>\n",
" <th>2014-10-05</th>\n",
" <th>2014-10-12</th>\n",
" <th>2014-10-19</th>\n",
" <th>2014-10-26</th>\n",
" <th>2014-11-02</th>\n",
" <th>2014-11-09</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>All</th>\n",
" <td> 5624</td>\n",
" <td> 5304</td>\n",
" <td> 12114</td>\n",
" <td> 8451</td>\n",
" <td> 7809.00</td>\n",
" <td> 7887.00</td>\n",
" <td> 5799.00</td>\n",
" <td> 7615</td>\n",
" <td> 12138</td>\n",
" <td> 15058</td>\n",
" <td>...</td>\n",
" <td> 16252</td>\n",
" <td> 21389</td>\n",
" <td> 11084.00</td>\n",
" <td> 9804</td>\n",
" <td> 9703.00</td>\n",
" <td> 9551</td>\n",
" <td> 12954</td>\n",
" <td> 10656</td>\n",
" <td> 3665</td>\n",
" <td> 7691</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Paid</th>\n",
" <td> 2720</td>\n",
" <td> 2786</td>\n",
" <td> 8247</td>\n",
" <td> 4830</td>\n",
" <td> 5050.56</td>\n",
" <td> 5139.16</td>\n",
" <td> 3332.78</td>\n",
" <td> 5419</td>\n",
" <td> 8621</td>\n",
" <td> 10651</td>\n",
" <td>...</td>\n",
" <td> 11234</td>\n",
" <td> 17183</td>\n",
" <td> 7386.47</td>\n",
" <td> 3468</td>\n",
" <td> 3620.45</td>\n",
" <td> 4580</td>\n",
" <td> 6909</td>\n",
" <td> 4297</td>\n",
" <td> 17</td>\n",
" <td> 3045</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Organic</th>\n",
" <td> 2904</td>\n",
" <td> 2518</td>\n",
" <td> 3867</td>\n",
" <td> 3621</td>\n",
" <td> 2758.44</td>\n",
" <td> 2747.84</td>\n",
" <td> 2466.22</td>\n",
" <td> 2196</td>\n",
" <td> 3517</td>\n",
" <td> 4407</td>\n",
" <td>...</td>\n",
" <td> 5018</td>\n",
" <td> 4206</td>\n",
" <td> 3697.53</td>\n",
" <td> 6336</td>\n",
" <td> 6082.55</td>\n",
" <td> 4971</td>\n",
" <td> 6045</td>\n",
" <td> 6359</td>\n",
" <td> 3648</td>\n",
" <td> 4646</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>3 rows \u00d7 25 columns</p>\n",
"</div>"
],
"metadata": {},
"output_type": "pyout",
"prompt_number": 38,
"text": [
" 2014-05-25 2014-06-01 2014-06-08 2014-06-15 2014-06-22 \\\n",
"All 5624 5304 12114 8451 7809.00 \n",
"Paid 2720 2786 8247 4830 5050.56 \n",
"Organic 2904 2518 3867 3621 2758.44 \n",
"\n",
" 2014-06-29 2014-07-06 2014-07-13 2014-07-20 2014-07-27 \\\n",
"All 7887.00 5799.00 7615 12138 15058 \n",
"Paid 5139.16 3332.78 5419 8621 10651 \n",
"Organic 2747.84 2466.22 2196 3517 4407 \n",
"\n",
" ... 2014-09-07 2014-09-14 2014-09-21 2014-09-28 \\\n",
"All ... 16252 21389 11084.00 9804 \n",
"Paid ... 11234 17183 7386.47 3468 \n",
"Organic ... 5018 4206 3697.53 6336 \n",
"\n",
" 2014-10-05 2014-10-12 2014-10-19 2014-10-26 2014-11-02 \\\n",
"All 9703.00 9551 12954 10656 3665 \n",
"Paid 3620.45 4580 6909 4297 17 \n",
"Organic 6082.55 4971 6045 6359 3648 \n",
"\n",
" 2014-11-09 \n",
"All 7691 \n",
"Paid 3045 \n",
"Organic 4646 \n",
"\n",
"[3 rows x 25 columns]"
]
}
],
"prompt_number": 38
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"metrics = OrderedDict([('All','tx_count'), \n",
" ('Without Voucher','tx_count:without_voucher'), \n",
" ('Voucher','tx_count:with_voucher')])\n",
"slide15_chart3 = metrics_for_chart_portal(df, metrics, 'uk')\n",
"slide15_chart3.to_excel(writer, 'Slide 15', startrow=20, startcol=1)\n",
"slide15_chart3"
],
"language": "python",
"metadata": {},
"outputs": [
{
"html": [
"<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>2014-05-25</th>\n",
" <th>2014-06-01</th>\n",
" <th>2014-06-08</th>\n",
" <th>2014-06-15</th>\n",
" <th>2014-06-22</th>\n",
" <th>2014-06-29</th>\n",
" <th>2014-07-06</th>\n",
" <th>2014-07-13</th>\n",
" <th>2014-07-20</th>\n",
" <th>2014-07-27</th>\n",
" <th>...</th>\n",
" <th>2014-09-07</th>\n",
" <th>2014-09-14</th>\n",
" <th>2014-09-21</th>\n",
" <th>2014-09-28</th>\n",
" <th>2014-10-05</th>\n",
" <th>2014-10-12</th>\n",
" <th>2014-10-19</th>\n",
" <th>2014-10-26</th>\n",
" <th>2014-11-02</th>\n",
" <th>2014-11-09</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>All</th>\n",
" <td> 5936</td>\n",
" <td> 6010</td>\n",
" <td> 6393</td>\n",
" <td> 6301</td>\n",
" <td> 5149</td>\n",
" <td> 5649</td>\n",
" <td> 5424</td>\n",
" <td> 5199</td>\n",
" <td> 5636</td>\n",
" <td> 6352</td>\n",
" <td>...</td>\n",
" <td> 9550</td>\n",
" <td> 10874</td>\n",
" <td> 9163</td>\n",
" <td> 9482</td>\n",
" <td> 11090</td>\n",
" <td> 9857</td>\n",
" <td> 9910</td>\n",
" <td> 9797</td>\n",
" <td> 8043</td>\n",
" <td> 8465</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Without Voucher</th>\n",
" <td> 5232</td>\n",
" <td> 5419</td>\n",
" <td> 5289</td>\n",
" <td> 5200</td>\n",
" <td> 4343</td>\n",
" <td> 4772</td>\n",
" <td> 4615</td>\n",
" <td> 4275</td>\n",
" <td> 4232</td>\n",
" <td> 4602</td>\n",
" <td>...</td>\n",
" <td> 7750</td>\n",
" <td> 8005</td>\n",
" <td> 7419</td>\n",
" <td> 7512</td>\n",
" <td> 8598</td>\n",
" <td> 8207</td>\n",
" <td> 8119</td>\n",
" <td> 7982</td>\n",
" <td> 7476</td>\n",
" <td> 7339</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Voucher</th>\n",
" <td> 704</td>\n",
" <td> 591</td>\n",
" <td> 1104</td>\n",
" <td> 1101</td>\n",
" <td> 806</td>\n",
" <td> 877</td>\n",
" <td> 809</td>\n",
" <td> 924</td>\n",
" <td> 1404</td>\n",
" <td> 1750</td>\n",
" <td>...</td>\n",
" <td> 1800</td>\n",
" <td> 2869</td>\n",
" <td> 1744</td>\n",
" <td> 1970</td>\n",
" <td> 2492</td>\n",
" <td> 1650</td>\n",
" <td> 1791</td>\n",
" <td> 1815</td>\n",
" <td> 567</td>\n",
" <td> 1126</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>3 rows \u00d7 25 columns</p>\n",
"</div>"
],
"metadata": {},
"output_type": "pyout",
"prompt_number": 39,
"text": [
" 2014-05-25 2014-06-01 2014-06-08 2014-06-15 2014-06-22 \\\n",
"All 5936 6010 6393 6301 5149 \n",
"Without Voucher 5232 5419 5289 5200 4343 \n",
"Voucher 704 591 1104 1101 806 \n",
"\n",
" 2014-06-29 2014-07-06 2014-07-13 2014-07-20 2014-07-27 \\\n",
"All 5649 5424 5199 5636 6352 \n",
"Without Voucher 4772 4615 4275 4232 4602 \n",
"Voucher 877 809 924 1404 1750 \n",
"\n",
" ... 2014-09-07 2014-09-14 2014-09-21 2014-09-28 \\\n",
"All ... 9550 10874 9163 9482 \n",
"Without Voucher ... 7750 8005 7419 7512 \n",
"Voucher ... 1800 2869 1744 1970 \n",
"\n",
" 2014-10-05 2014-10-12 2014-10-19 2014-10-26 2014-11-02 \\\n",
"All 11090 9857 9910 9797 8043 \n",
"Without Voucher 8598 8207 8119 7982 7476 \n",
"Voucher 2492 1650 1791 1815 567 \n",
"\n",
" 2014-11-09 \n",
"All 8465 \n",
"Without Voucher 7339 \n",
"Voucher 1126 \n",
"\n",
"[3 rows x 25 columns]"
]
}
],
"prompt_number": 39
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"slide15_chart4 = metrics_for_chart_portal(df, {'New weekly listings':'new_listings'}, 'uk')\n",
"slide15_chart4.to_excel(writer, 'Slide 15', startrow=30, startcol=1)\n",
"slide15_chart4"
],
"language": "python",
"metadata": {},
"outputs": [
{
"html": [
"<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>2014-05-25</th>\n",
" <th>2014-06-01</th>\n",
" <th>2014-06-08</th>\n",
" <th>2014-06-15</th>\n",
" <th>2014-06-22</th>\n",
" <th>2014-06-29</th>\n",
" <th>2014-07-06</th>\n",
" <th>2014-07-13</th>\n",
" <th>2014-07-20</th>\n",
" <th>2014-07-27</th>\n",
" <th>...</th>\n",
" <th>2014-09-07</th>\n",
" <th>2014-09-14</th>\n",
" <th>2014-09-21</th>\n",
" <th>2014-09-28</th>\n",
" <th>2014-10-05</th>\n",
" <th>2014-10-12</th>\n",
" <th>2014-10-19</th>\n",
" <th>2014-10-26</th>\n",
" <th>2014-11-02</th>\n",
" <th>2014-11-09</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>New weekly listings</th>\n",
" <td> 33337</td>\n",
" <td> 32053</td>\n",
" <td> 42336</td>\n",
" <td> 39148</td>\n",
" <td> 35653</td>\n",
" <td> 34650</td>\n",
" <td> 32815</td>\n",
" <td> 29847</td>\n",
" <td> 34076</td>\n",
" <td> 34664</td>\n",
" <td>...</td>\n",
" <td> 43588</td>\n",
" <td> 46263</td>\n",
" <td> 40685</td>\n",
" <td> 34650</td>\n",
" <td> 37144</td>\n",
" <td> 38431</td>\n",
" <td> 39523</td>\n",
" <td> 38408</td>\n",
" <td> 29463</td>\n",
" <td> 30611</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>1 rows \u00d7 25 columns</p>\n",
"</div>"
],
"metadata": {},
"output_type": "pyout",
"prompt_number": 40,
"text": [
" 2014-05-25 2014-06-01 2014-06-08 2014-06-15 \\\n",
"New weekly listings 33337 32053 42336 39148 \n",
"\n",
" 2014-06-22 2014-06-29 2014-07-06 2014-07-13 \\\n",
"New weekly listings 35653 34650 32815 29847 \n",
"\n",
" 2014-07-20 2014-07-27 ... 2014-09-07 \\\n",
"New weekly listings 34076 34664 ... 43588 \n",
"\n",
" 2014-09-14 2014-09-21 2014-09-28 2014-10-05 \\\n",
"New weekly listings 46263 40685 34650 37144 \n",
"\n",
" 2014-10-12 2014-10-19 2014-10-26 2014-11-02 \\\n",
"New weekly listings 38431 39523 38408 29463 \n",
"\n",
" 2014-11-09 \n",
"New weekly listings 30611 \n",
"\n",
"[1 rows x 25 columns]"
]
}
],
"prompt_number": 40
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"metrics = OrderedDict([('Revenue','income'), \n",
" ('Processig fees','cogs')])\n",
"slide16_chart = metrics_for_chart_portal(df, metrics, 'uk')\n",
"slide16_chart.to_excel(writer, 'Slide 16', startrow=1, startcol=1)\n",
"slide16_chart"
],
"language": "python",
"metadata": {},
"outputs": [
{
"html": [
"<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>2014-05-25</th>\n",
" <th>2014-06-01</th>\n",
" <th>2014-06-08</th>\n",
" <th>2014-06-15</th>\n",
" <th>2014-06-22</th>\n",
" <th>2014-06-29</th>\n",
" <th>2014-07-06</th>\n",
" <th>2014-07-13</th>\n",
" <th>2014-07-20</th>\n",
" <th>2014-07-27</th>\n",
" <th>...</th>\n",
" <th>2014-09-07</th>\n",
" <th>2014-09-14</th>\n",
" <th>2014-09-21</th>\n",
" <th>2014-09-28</th>\n",
" <th>2014-10-05</th>\n",
" <th>2014-10-12</th>\n",
" <th>2014-10-19</th>\n",
" <th>2014-10-26</th>\n",
" <th>2014-11-02</th>\n",
" <th>2014-11-09</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>Revenue</th>\n",
" <td> 25741.87</td>\n",
" <td> 26870.9</td>\n",
" <td> 28729.85</td>\n",
" <td> 26799.94</td>\n",
" <td> 23222.37</td>\n",
" <td> 26472.4</td>\n",
" <td> 25475.75</td>\n",
" <td> 23774.78</td>\n",
" <td> 26720.03</td>\n",
" <td> 31208.69</td>\n",
" <td>...</td>\n",
" <td> 49011.47</td>\n",
" <td> 56622.99</td>\n",
" <td> 48291.15</td>\n",
" <td> 51411.25</td>\n",
" <td> 59909.66</td>\n",
" <td> 54285.79</td>\n",
" <td> 55548.69</td>\n",
" <td> 53499.75</td>\n",
" <td> 42278.45</td>\n",
" <td> 44223.14</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Processig fees</th>\n",
" <td> 1879.94</td>\n",
" <td> 2011.62</td>\n",
" <td> 1926.50</td>\n",
" <td> 1621.24</td>\n",
" <td> 1875.98</td>\n",
" <td> 1818.78</td>\n",
" <td> 1657.51</td>\n",
" <td> 1816.51</td>\n",
" <td> 2115.12</td>\n",
" <td> 2988.78</td>\n",
" <td>...</td>\n",
" <td> 3838.69</td>\n",
" <td> 3435.02</td>\n",
" <td> 3413.44</td>\n",
" <td> 3994.92</td>\n",
" <td> 3764.21</td>\n",
" <td> 3673.27</td>\n",
" <td> 3696.87</td>\n",
" <td> 2894.86</td>\n",
" <td> 2980.04</td>\n",
" <td> 2496.56</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>2 rows \u00d7 25 columns</p>\n",
"</div>"
],
"metadata": {},
"output_type": "pyout",
"prompt_number": 41,
"text": [
" 2014-05-25 2014-06-01 2014-06-08 2014-06-15 2014-06-22 \\\n",
"Revenue 25741.87 26870.9 28729.85 26799.94 23222.37 \n",
"Processig fees 1879.94 2011.62 1926.50 1621.24 1875.98 \n",
"\n",
" 2014-06-29 2014-07-06 2014-07-13 2014-07-20 2014-07-27 \\\n",
"Revenue 26472.4 25475.75 23774.78 26720.03 31208.69 \n",
"Processig fees 1818.78 1657.51 1816.51 2115.12 2988.78 \n",
"\n",
" ... 2014-09-07 2014-09-14 2014-09-21 2014-09-28 \\\n",
"Revenue ... 49011.47 56622.99 48291.15 51411.25 \n",
"Processig fees ... 3838.69 3435.02 3413.44 3994.92 \n",
"\n",
" 2014-10-05 2014-10-12 2014-10-19 2014-10-26 2014-11-02 \\\n",
"Revenue 59909.66 54285.79 55548.69 53499.75 42278.45 \n",
"Processig fees 3764.21 3673.27 3696.87 2894.86 2980.04 \n",
"\n",
" 2014-11-09 \n",
"Revenue 44223.14 \n",
"Processig fees 2496.56 \n",
"\n",
"[2 rows x 25 columns]"
]
}
],
"prompt_number": 41
},
{
"cell_type": "heading",
"level": 2,
"metadata": {},
"source": [
"US Weekly Metrics"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"slide18_chart1 = metrics_for_chart_portal(df, {'WAU, since launch':'au_count'}, 'us')\n",
"slide18_chart1.to_excel(writer, 'Slide 18', startrow=1, startcol=1)\n",
"slide18_chart1"
],
"language": "python",
"metadata": {},
"outputs": [
{
"html": [
"<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>2014-05-25</th>\n",
" <th>2014-06-01</th>\n",
" <th>2014-06-08</th>\n",
" <th>2014-06-15</th>\n",
" <th>2014-06-22</th>\n",
" <th>2014-06-29</th>\n",
" <th>2014-07-06</th>\n",
" <th>2014-07-13</th>\n",
" <th>2014-07-20</th>\n",
" <th>2014-07-27</th>\n",
" <th>...</th>\n",
" <th>2014-09-07</th>\n",
" <th>2014-09-14</th>\n",
" <th>2014-09-21</th>\n",
" <th>2014-09-28</th>\n",
" <th>2014-10-05</th>\n",
" <th>2014-10-12</th>\n",
" <th>2014-10-19</th>\n",
" <th>2014-10-26</th>\n",
" <th>2014-11-02</th>\n",
" <th>2014-11-09</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>WAU, since launch</th>\n",
" <td> 103923</td>\n",
" <td> 105991</td>\n",
" <td> 102626</td>\n",
" <td> 104700</td>\n",
" <td> 114326</td>\n",
" <td> 121998</td>\n",
" <td> 116293</td>\n",
" <td> 109190</td>\n",
" <td> 105572</td>\n",
" <td> 115007</td>\n",
" <td>...</td>\n",
" <td> 143843</td>\n",
" <td> 153732</td>\n",
" <td> 148236</td>\n",
" <td> 162546</td>\n",
" <td> 173864</td>\n",
" <td> 162177</td>\n",
" <td> 155535</td>\n",
" <td> 190445</td>\n",
" <td> 191006</td>\n",
" <td> 182779</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>1 rows \u00d7 25 columns</p>\n",
"</div>"
],
"metadata": {},
"output_type": "pyout",
"prompt_number": 42,
"text": [
" 2014-05-25 2014-06-01 2014-06-08 2014-06-15 2014-06-22 \\\n",
"WAU, since launch 103923 105991 102626 104700 114326 \n",
"\n",
" 2014-06-29 2014-07-06 2014-07-13 2014-07-20 2014-07-27 \\\n",
"WAU, since launch 121998 116293 109190 105572 115007 \n",
"\n",
" ... 2014-09-07 2014-09-14 2014-09-21 2014-09-28 \\\n",
"WAU, since launch ... 143843 153732 148236 162546 \n",
"\n",
" 2014-10-05 2014-10-12 2014-10-19 2014-10-26 2014-11-02 \\\n",
"WAU, since launch 173864 162177 155535 190445 191006 \n",
"\n",
" 2014-11-09 \n",
"WAU, since launch 182779 \n",
"\n",
"[1 rows x 25 columns]"
]
}
],
"prompt_number": 42
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"metrics = OrderedDict([('All','new_members'), \n",
" ('Paid','total_paid_members'), \n",
" ('Organic','organic_members')])\n",
"slide18_chart2 = metrics_for_chart_portal(df, metrics, 'us')\n",
"slide18_chart2.to_excel(writer, 'Slide 18', startrow=10, startcol=1)\n",
"slide18_chart2"
],
"language": "python",
"metadata": {},
"outputs": [
{
"html": [
"<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>2014-05-25</th>\n",
" <th>2014-06-01</th>\n",
" <th>2014-06-08</th>\n",
" <th>2014-06-15</th>\n",
" <th>2014-06-22</th>\n",
" <th>2014-06-29</th>\n",
" <th>2014-07-06</th>\n",
" <th>2014-07-13</th>\n",
" <th>2014-07-20</th>\n",
" <th>2014-07-27</th>\n",
" <th>...</th>\n",
" <th>2014-09-07</th>\n",
" <th>2014-09-14</th>\n",
" <th>2014-09-21</th>\n",
" <th>2014-09-28</th>\n",
" <th>2014-10-05</th>\n",
" <th>2014-10-12</th>\n",
" <th>2014-10-19</th>\n",
" <th>2014-10-26</th>\n",
" <th>2014-11-02</th>\n",
" <th>2014-11-09</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>All</th>\n",
" <td> 11030</td>\n",
" <td> 8296</td>\n",
" <td> 19254</td>\n",
" <td> 19664</td>\n",
" <td> 25029.00</td>\n",
" <td> 28955.00</td>\n",
" <td> 19690.00</td>\n",
" <td> 9914</td>\n",
" <td> 12579</td>\n",
" <td> 20965</td>\n",
" <td>...</td>\n",
" <td> 36540</td>\n",
" <td> 41866.00000</td>\n",
" <td> 31522.0</td>\n",
" <td> 44374.00</td>\n",
" <td> 42410.000</td>\n",
" <td> 29100</td>\n",
" <td> 29220</td>\n",
" <td> 56142</td>\n",
" <td> 49114</td>\n",
" <td> 36020</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Paid</th>\n",
" <td> 4158</td>\n",
" <td> 2331</td>\n",
" <td> 11507</td>\n",
" <td> 10570</td>\n",
" <td> 14215.21</td>\n",
" <td> 16400.64</td>\n",
" <td> 8493.92</td>\n",
" <td> 0</td>\n",
" <td> 2777</td>\n",
" <td> 10502</td>\n",
" <td>...</td>\n",
" <td> 22315</td>\n",
" <td> 26230.79099</td>\n",
" <td> 15091.6</td>\n",
" <td> 24242.17</td>\n",
" <td> 21255.582</td>\n",
" <td> 11692</td>\n",
" <td> 15450</td>\n",
" <td> 36425</td>\n",
" <td> 28423</td>\n",
" <td> 13292</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Organic</th>\n",
" <td> 6872</td>\n",
" <td> 5965</td>\n",
" <td> 7747</td>\n",
" <td> 9094</td>\n",
" <td> 10813.79</td>\n",
" <td> 12554.36</td>\n",
" <td> 11196.08</td>\n",
" <td> 9914</td>\n",
" <td> 9802</td>\n",
" <td> 10463</td>\n",
" <td>...</td>\n",
" <td> 14225</td>\n",
" <td> 15635.20901</td>\n",
" <td> 16430.4</td>\n",
" <td> 20131.83</td>\n",
" <td> 21154.418</td>\n",
" <td> 17408</td>\n",
" <td> 13770</td>\n",
" <td> 19717</td>\n",
" <td> 20691</td>\n",
" <td> 22728</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>3 rows \u00d7 25 columns</p>\n",
"</div>"
],
"metadata": {},
"output_type": "pyout",
"prompt_number": 43,
"text": [
" 2014-05-25 2014-06-01 2014-06-08 2014-06-15 2014-06-22 \\\n",
"All 11030 8296 19254 19664 25029.00 \n",
"Paid 4158 2331 11507 10570 14215.21 \n",
"Organic 6872 5965 7747 9094 10813.79 \n",
"\n",
" 2014-06-29 2014-07-06 2014-07-13 2014-07-20 2014-07-27 \\\n",
"All 28955.00 19690.00 9914 12579 20965 \n",
"Paid 16400.64 8493.92 0 2777 10502 \n",
"Organic 12554.36 11196.08 9914 9802 10463 \n",
"\n",
" ... 2014-09-07 2014-09-14 2014-09-21 2014-09-28 \\\n",
"All ... 36540 41866.00000 31522.0 44374.00 \n",
"Paid ... 22315 26230.79099 15091.6 24242.17 \n",
"Organic ... 14225 15635.20901 16430.4 20131.83 \n",
"\n",
" 2014-10-05 2014-10-12 2014-10-19 2014-10-26 2014-11-02 \\\n",
"All 42410.000 29100 29220 56142 49114 \n",
"Paid 21255.582 11692 15450 36425 28423 \n",
"Organic 21154.418 17408 13770 19717 20691 \n",
"\n",
" 2014-11-09 \n",
"All 36020 \n",
"Paid 13292 \n",
"Organic 22728 \n",
"\n",
"[3 rows x 25 columns]"
]
}
],
"prompt_number": 43
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"metrics = OrderedDict([('All','tx_count'), \n",
" ('Without Voucher','tx_count:without_voucher'), \n",
" ('Voucher','tx_count:with_voucher')])\n",
"slide18_chart3 = metrics_for_chart_portal(df, metrics, 'us')\n",
"slide18_chart3.to_excel(writer, 'Slide 18', startrow=20, startcol=1)\n",
"slide18_chart3"
],
"language": "python",
"metadata": {},
"outputs": [
{
"html": [
"<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>2014-05-25</th>\n",
" <th>2014-06-01</th>\n",
" <th>2014-06-08</th>\n",
" <th>2014-06-15</th>\n",
" <th>2014-06-22</th>\n",
" <th>2014-06-29</th>\n",
" <th>2014-07-06</th>\n",
" <th>2014-07-13</th>\n",
" <th>2014-07-20</th>\n",
" <th>2014-07-27</th>\n",
" <th>...</th>\n",
" <th>2014-09-07</th>\n",
" <th>2014-09-14</th>\n",
" <th>2014-09-21</th>\n",
" <th>2014-09-28</th>\n",
" <th>2014-10-05</th>\n",
" <th>2014-10-12</th>\n",
" <th>2014-10-19</th>\n",
" <th>2014-10-26</th>\n",
" <th>2014-11-02</th>\n",
" <th>2014-11-09</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>All</th>\n",
" <td> 18594</td>\n",
" <td> 17277</td>\n",
" <td> 19155</td>\n",
" <td> 21482</td>\n",
" <td> 23166</td>\n",
" <td> 26807</td>\n",
" <td> 25756</td>\n",
" <td> 25520</td>\n",
" <td> 25482</td>\n",
" <td> 26097</td>\n",
" <td>...</td>\n",
" <td> 22978</td>\n",
" <td> 23812</td>\n",
" <td> 23827</td>\n",
" <td> 24572</td>\n",
" <td> 27312</td>\n",
" <td> 26219</td>\n",
" <td> 24392</td>\n",
" <td> 25627</td>\n",
" <td> 24396</td>\n",
" <td> 27111</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Without Voucher</th>\n",
" <td> 18233</td>\n",
" <td> 17048</td>\n",
" <td> 17010</td>\n",
" <td> 18016</td>\n",
" <td> 19461</td>\n",
" <td> 21657</td>\n",
" <td> 21567</td>\n",
" <td> 22497</td>\n",
" <td> 22909</td>\n",
" <td> 23571</td>\n",
" <td>...</td>\n",
" <td> 21545</td>\n",
" <td> 21679</td>\n",
" <td> 21329</td>\n",
" <td> 21417</td>\n",
" <td> 24384</td>\n",
" <td> 24145</td>\n",
" <td> 22615</td>\n",
" <td> 22947</td>\n",
" <td> 21397</td>\n",
" <td> 24143</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Voucher</th>\n",
" <td> 361</td>\n",
" <td> 229</td>\n",
" <td> 2145</td>\n",
" <td> 3466</td>\n",
" <td> 3705</td>\n",
" <td> 5150</td>\n",
" <td> 4189</td>\n",
" <td> 3023</td>\n",
" <td> 2573</td>\n",
" <td> 2526</td>\n",
" <td>...</td>\n",
" <td> 1433</td>\n",
" <td> 2133</td>\n",
" <td> 2498</td>\n",
" <td> 3155</td>\n",
" <td> 2928</td>\n",
" <td> 2074</td>\n",
" <td> 1777</td>\n",
" <td> 2680</td>\n",
" <td> 2999</td>\n",
" <td> 2968</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>3 rows \u00d7 25 columns</p>\n",
"</div>"
],
"metadata": {},
"output_type": "pyout",
"prompt_number": 44,
"text": [
" 2014-05-25 2014-06-01 2014-06-08 2014-06-15 2014-06-22 \\\n",
"All 18594 17277 19155 21482 23166 \n",
"Without Voucher 18233 17048 17010 18016 19461 \n",
"Voucher 361 229 2145 3466 3705 \n",
"\n",
" 2014-06-29 2014-07-06 2014-07-13 2014-07-20 2014-07-27 \\\n",
"All 26807 25756 25520 25482 26097 \n",
"Without Voucher 21657 21567 22497 22909 23571 \n",
"Voucher 5150 4189 3023 2573 2526 \n",
"\n",
" ... 2014-09-07 2014-09-14 2014-09-21 2014-09-28 \\\n",
"All ... 22978 23812 23827 24572 \n",
"Without Voucher ... 21545 21679 21329 21417 \n",
"Voucher ... 1433 2133 2498 3155 \n",
"\n",
" 2014-10-05 2014-10-12 2014-10-19 2014-10-26 2014-11-02 \\\n",
"All 27312 26219 24392 25627 24396 \n",
"Without Voucher 24384 24145 22615 22947 21397 \n",
"Voucher 2928 2074 1777 2680 2999 \n",
"\n",
" 2014-11-09 \n",
"All 27111 \n",
"Without Voucher 24143 \n",
"Voucher 2968 \n",
"\n",
"[3 rows x 25 columns]"
]
}
],
"prompt_number": 44
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"slide18_chart4 = metrics_for_chart_portal(df, {'New weekly listings':'new_listings'}, 'us')\n",
"slide18_chart4.to_excel(writer, 'Slide 18', startrow=30, startcol=1)\n",
"slide18_chart4"
],
"language": "python",
"metadata": {},
"outputs": [
{
"html": [
"<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>2014-05-25</th>\n",
" <th>2014-06-01</th>\n",
" <th>2014-06-08</th>\n",
" <th>2014-06-15</th>\n",
" <th>2014-06-22</th>\n",
" <th>2014-06-29</th>\n",
" <th>2014-07-06</th>\n",
" <th>2014-07-13</th>\n",
" <th>2014-07-20</th>\n",
" <th>2014-07-27</th>\n",
" <th>...</th>\n",
" <th>2014-09-07</th>\n",
" <th>2014-09-14</th>\n",
" <th>2014-09-21</th>\n",
" <th>2014-09-28</th>\n",
" <th>2014-10-05</th>\n",
" <th>2014-10-12</th>\n",
" <th>2014-10-19</th>\n",
" <th>2014-10-26</th>\n",
" <th>2014-11-02</th>\n",
" <th>2014-11-09</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>New weekly listings</th>\n",
" <td> 85309</td>\n",
" <td> 83175</td>\n",
" <td> 92262</td>\n",
" <td> 107321</td>\n",
" <td> 125456</td>\n",
" <td> 146247</td>\n",
" <td> 125300</td>\n",
" <td> 120063</td>\n",
" <td> 114817</td>\n",
" <td> 130306</td>\n",
" <td>...</td>\n",
" <td> 102632</td>\n",
" <td> 103563</td>\n",
" <td> 101763</td>\n",
" <td> 102839</td>\n",
" <td> 105374</td>\n",
" <td> 102248</td>\n",
" <td> 92209</td>\n",
" <td> 100530</td>\n",
" <td> 95599</td>\n",
" <td> 105357</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>1 rows \u00d7 25 columns</p>\n",
"</div>"
],
"metadata": {},
"output_type": "pyout",
"prompt_number": 45,
"text": [
" 2014-05-25 2014-06-01 2014-06-08 2014-06-15 \\\n",
"New weekly listings 85309 83175 92262 107321 \n",
"\n",
" 2014-06-22 2014-06-29 2014-07-06 2014-07-13 \\\n",
"New weekly listings 125456 146247 125300 120063 \n",
"\n",
" 2014-07-20 2014-07-27 ... 2014-09-07 \\\n",
"New weekly listings 114817 130306 ... 102632 \n",
"\n",
" 2014-09-14 2014-09-21 2014-09-28 2014-10-05 \\\n",
"New weekly listings 103563 101763 102839 105374 \n",
"\n",
" 2014-10-12 2014-10-19 2014-10-26 2014-11-02 \\\n",
"New weekly listings 102248 92209 100530 95599 \n",
"\n",
" 2014-11-09 \n",
"New weekly listings 105357 \n",
"\n",
"[1 rows x 25 columns]"
]
}
],
"prompt_number": 45
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"metrics = OrderedDict([('Revenue','income'), \n",
" ('COGS','cogs'), \n",
" ('Gross Profit','net_revenues')])\n",
"slide19_chart = metrics_for_chart_portal(df, metrics, 'us')\n",
"slide19_chart.to_excel(writer, 'Slide 19', startrow=1, startcol=1)\n",
"slide19_chart"
],
"language": "python",
"metadata": {},
"outputs": [
{
"html": [
"<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>2014-05-25</th>\n",
" <th>2014-06-01</th>\n",
" <th>2014-06-08</th>\n",
" <th>2014-06-15</th>\n",
" <th>2014-06-22</th>\n",
" <th>2014-06-29</th>\n",
" <th>2014-07-06</th>\n",
" <th>2014-07-13</th>\n",
" <th>2014-07-20</th>\n",
" <th>2014-07-27</th>\n",
" <th>...</th>\n",
" <th>2014-09-07</th>\n",
" <th>2014-09-14</th>\n",
" <th>2014-09-21</th>\n",
" <th>2014-09-28</th>\n",
" <th>2014-10-05</th>\n",
" <th>2014-10-12</th>\n",
" <th>2014-10-19</th>\n",
" <th>2014-10-26</th>\n",
" <th>2014-11-02</th>\n",
" <th>2014-11-09</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>Revenue</th>\n",
" <td> 69107.40</td>\n",
" <td> 61751.95</td>\n",
" <td> 70746.14</td>\n",
" <td> 83266.57</td>\n",
" <td> 89632.61</td>\n",
" <td> 105021.28</td>\n",
" <td> 102166.65</td>\n",
" <td> 97217.70</td>\n",
" <td> 98558.06</td>\n",
" <td> 100939.03</td>\n",
" <td>...</td>\n",
" <td> 97663.62</td>\n",
" <td> 103853.02</td>\n",
" <td> 104809.61</td>\n",
" <td> 111427.02</td>\n",
" <td> 126641.04</td>\n",
" <td> 123074.34</td>\n",
" <td> 111175.67</td>\n",
" <td> 120692.66</td>\n",
" <td> 115167.47</td>\n",
" <td> 127739.73</td>\n",
" </tr>\n",
" <tr>\n",
" <th>COGS</th>\n",
" <td> 47267.34</td>\n",
" <td> 46302.21</td>\n",
" <td> 53703.87</td>\n",
" <td> 61749.84</td>\n",
" <td> 69708.32</td>\n",
" <td> 79238.29</td>\n",
" <td> 76878.19</td>\n",
" <td> 76276.96</td>\n",
" <td> 77180.43</td>\n",
" <td> 79116.66</td>\n",
" <td>...</td>\n",
" <td> 73826.78</td>\n",
" <td> 81473.60</td>\n",
" <td> 82615.63</td>\n",
" <td> 85442.96</td>\n",
" <td> 101003.16</td>\n",
" <td> 96736.52</td>\n",
" <td> 91443.69</td>\n",
" <td> 90288.59</td>\n",
" <td> 91929.81</td>\n",
" <td> 95378.20</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Gross Profit</th>\n",
" <td> 14350.42</td>\n",
" <td> 9285.79</td>\n",
" <td> 11084.55</td>\n",
" <td> 14327.90</td>\n",
" <td> 13221.61</td>\n",
" <td> 16170.83</td>\n",
" <td> 17433.19</td>\n",
" <td> 9620.24</td>\n",
" <td> 11768.81</td>\n",
" <td> 13711.85</td>\n",
" <td>...</td>\n",
" <td> 13223.96</td>\n",
" <td> 12125.08</td>\n",
" <td> 12420.34</td>\n",
" <td> 13041.46</td>\n",
" <td> 13341.28</td>\n",
" <td> 12954.00</td>\n",
" <td> 5711.10</td>\n",
" <td> 17350.65</td>\n",
" <td> 10118.36</td>\n",
" <td> 19474.84</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>3 rows \u00d7 25 columns</p>\n",
"</div>"
],
"metadata": {},
"output_type": "pyout",
"prompt_number": 46,
"text": [
" 2014-05-25 2014-06-01 2014-06-08 2014-06-15 2014-06-22 \\\n",
"Revenue 69107.40 61751.95 70746.14 83266.57 89632.61 \n",
"COGS 47267.34 46302.21 53703.87 61749.84 69708.32 \n",
"Gross Profit 14350.42 9285.79 11084.55 14327.90 13221.61 \n",
"\n",
" 2014-06-29 2014-07-06 2014-07-13 2014-07-20 2014-07-27 \\\n",
"Revenue 105021.28 102166.65 97217.70 98558.06 100939.03 \n",
"COGS 79238.29 76878.19 76276.96 77180.43 79116.66 \n",
"Gross Profit 16170.83 17433.19 9620.24 11768.81 13711.85 \n",
"\n",
" ... 2014-09-07 2014-09-14 2014-09-21 2014-09-28 \\\n",
"Revenue ... 97663.62 103853.02 104809.61 111427.02 \n",
"COGS ... 73826.78 81473.60 82615.63 85442.96 \n",
"Gross Profit ... 13223.96 12125.08 12420.34 13041.46 \n",
"\n",
" 2014-10-05 2014-10-12 2014-10-19 2014-10-26 2014-11-02 \\\n",
"Revenue 126641.04 123074.34 111175.67 120692.66 115167.47 \n",
"COGS 101003.16 96736.52 91443.69 90288.59 91929.81 \n",
"Gross Profit 13341.28 12954.00 5711.10 17350.65 10118.36 \n",
"\n",
" 2014-11-09 \n",
"Revenue 127739.73 \n",
"COGS 95378.20 \n",
"Gross Profit 19474.84 \n",
"\n",
"[3 rows x 25 columns]"
]
}
],
"prompt_number": 46
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"metrics = OrderedDict([('Android','android_spend'), \n",
" ('iOS','ios_spend')])\n",
"slide21_chart1 = metrics_for_chart_portal(df, metrics, 'uk')\n",
"slide21_chart1.to_excel(writer, 'Slide 21', startrow=1, startcol=1)\n",
"slide21_chart1"
],
"language": "python",
"metadata": {},
"outputs": [
{
"html": [
"<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>2014-05-25</th>\n",
" <th>2014-06-01</th>\n",
" <th>2014-06-08</th>\n",
" <th>2014-06-15</th>\n",
" <th>2014-06-22</th>\n",
" <th>2014-06-29</th>\n",
" <th>2014-07-06</th>\n",
" <th>2014-07-13</th>\n",
" <th>2014-07-20</th>\n",
" <th>2014-07-27</th>\n",
" <th>...</th>\n",
" <th>2014-09-07</th>\n",
" <th>2014-09-14</th>\n",
" <th>2014-09-21</th>\n",
" <th>2014-09-28</th>\n",
" <th>2014-10-05</th>\n",
" <th>2014-10-12</th>\n",
" <th>2014-10-19</th>\n",
" <th>2014-10-26</th>\n",
" <th>2014-11-02</th>\n",
" <th>2014-11-09</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>Android</th>\n",
" <td> 8415.55</td>\n",
" <td> 5790.05</td>\n",
" <td> 11025.14</td>\n",
" <td> 7467.9</td>\n",
" <td> 9610.55</td>\n",
" <td> 9878.90</td>\n",
" <td> 3933.84</td>\n",
" <td> 14999.69</td>\n",
" <td> 21891.58</td>\n",
" <td> 28592.91</td>\n",
" <td>...</td>\n",
" <td> 18603.66</td>\n",
" <td> 13710.92</td>\n",
" <td> 9534.72</td>\n",
" <td> 3823.44</td>\n",
" <td> 3760.72</td>\n",
" <td> 10682.1248</td>\n",
" <td> 11666.119600</td>\n",
" <td> 6991.150000</td>\n",
" <td> 30.856</td>\n",
" <td> 4056</td>\n",
" </tr>\n",
" <tr>\n",
" <th>iOS</th>\n",
" <td> 11563.24</td>\n",
" <td> 6626.01</td>\n",
" <td> 33519.23</td>\n",
" <td> 25256.6</td>\n",
" <td> 24352.09</td>\n",
" <td> 26060.66</td>\n",
" <td> 19179.86</td>\n",
" <td> 28773.07</td>\n",
" <td> 61184.93</td>\n",
" <td> 43477.28</td>\n",
" <td>...</td>\n",
" <td> 29302.24</td>\n",
" <td> 34745.63</td>\n",
" <td> 25736.24</td>\n",
" <td> 27714.72</td>\n",
" <td> 30167.35</td>\n",
" <td> 35595.4212</td>\n",
" <td> 50179.456654</td>\n",
" <td> 23235.045963</td>\n",
" <td> 82.384</td>\n",
" <td> 10507</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>2 rows \u00d7 25 columns</p>\n",
"</div>"
],
"metadata": {},
"output_type": "pyout",
"prompt_number": 47,
"text": [
" 2014-05-25 2014-06-01 2014-06-08 2014-06-15 2014-06-22 \\\n",
"Android 8415.55 5790.05 11025.14 7467.9 9610.55 \n",
"iOS 11563.24 6626.01 33519.23 25256.6 24352.09 \n",
"\n",
" 2014-06-29 2014-07-06 2014-07-13 2014-07-20 2014-07-27 \\\n",
"Android 9878.90 3933.84 14999.69 21891.58 28592.91 \n",
"iOS 26060.66 19179.86 28773.07 61184.93 43477.28 \n",
"\n",
" ... 2014-09-07 2014-09-14 2014-09-21 2014-09-28 \\\n",
"Android ... 18603.66 13710.92 9534.72 3823.44 \n",
"iOS ... 29302.24 34745.63 25736.24 27714.72 \n",
"\n",
" 2014-10-05 2014-10-12 2014-10-19 2014-10-26 2014-11-02 \\\n",
"Android 3760.72 10682.1248 11666.119600 6991.150000 30.856 \n",
"iOS 30167.35 35595.4212 50179.456654 23235.045963 82.384 \n",
"\n",
" 2014-11-09 \n",
"Android 4056 \n",
"iOS 10507 \n",
"\n",
"[2 rows x 25 columns]"
]
}
],
"prompt_number": 47
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"metrics = OrderedDict([('Android','android_paid_members'), \n",
" ('iOS','ios_paid_members')])\n",
"slide21_chart2 = metrics_for_chart_portal(df, metrics, 'uk')\n",
"slide21_chart2.to_excel(writer, 'Slide 21', startrow=5, startcol=1)\n",
"slide21_chart2"
],
"language": "python",
"metadata": {},
"outputs": [
{
"html": [
"<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>2014-05-25</th>\n",
" <th>2014-06-01</th>\n",
" <th>2014-06-08</th>\n",
" <th>2014-06-15</th>\n",
" <th>2014-06-22</th>\n",
" <th>2014-06-29</th>\n",
" <th>2014-07-06</th>\n",
" <th>2014-07-13</th>\n",
" <th>2014-07-20</th>\n",
" <th>2014-07-27</th>\n",
" <th>...</th>\n",
" <th>2014-09-07</th>\n",
" <th>2014-09-14</th>\n",
" <th>2014-09-21</th>\n",
" <th>2014-09-28</th>\n",
" <th>2014-10-05</th>\n",
" <th>2014-10-12</th>\n",
" <th>2014-10-19</th>\n",
" <th>2014-10-26</th>\n",
" <th>2014-11-02</th>\n",
" <th>2014-11-09</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>Android</th>\n",
" <td> 1203</td>\n",
" <td> 1181</td>\n",
" <td> 2071</td>\n",
" <td> 1038</td>\n",
" <td> 1307.00</td>\n",
" <td> 1096.00</td>\n",
" <td> 473.00</td>\n",
" <td> 1638</td>\n",
" <td> 2192</td>\n",
" <td> 4453</td>\n",
" <td>...</td>\n",
" <td> 3494</td>\n",
" <td> 3285</td>\n",
" <td> 1413.90</td>\n",
" <td> 422</td>\n",
" <td> 386.45</td>\n",
" <td> 900</td>\n",
" <td> 1367</td>\n",
" <td> 1208</td>\n",
" <td> 12</td>\n",
" <td> 991</td>\n",
" </tr>\n",
" <tr>\n",
" <th>iOS</th>\n",
" <td> 1517</td>\n",
" <td> 1605</td>\n",
" <td> 6176</td>\n",
" <td> 3792</td>\n",
" <td> 3743.56</td>\n",
" <td> 4043.16</td>\n",
" <td> 2859.78</td>\n",
" <td> 3781</td>\n",
" <td> 6429</td>\n",
" <td> 6198</td>\n",
" <td>...</td>\n",
" <td> 7740</td>\n",
" <td> 13898</td>\n",
" <td> 5972.57</td>\n",
" <td> 3046</td>\n",
" <td> 3234.00</td>\n",
" <td> 3680</td>\n",
" <td> 5542</td>\n",
" <td> 3089</td>\n",
" <td> 5</td>\n",
" <td> 2054</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>2 rows \u00d7 25 columns</p>\n",
"</div>"
],
"metadata": {},
"output_type": "pyout",
"prompt_number": 48,
"text": [
" 2014-05-25 2014-06-01 2014-06-08 2014-06-15 2014-06-22 \\\n",
"Android 1203 1181 2071 1038 1307.00 \n",
"iOS 1517 1605 6176 3792 3743.56 \n",
"\n",
" 2014-06-29 2014-07-06 2014-07-13 2014-07-20 2014-07-27 \\\n",
"Android 1096.00 473.00 1638 2192 4453 \n",
"iOS 4043.16 2859.78 3781 6429 6198 \n",
"\n",
" ... 2014-09-07 2014-09-14 2014-09-21 2014-09-28 \\\n",
"Android ... 3494 3285 1413.90 422 \n",
"iOS ... 7740 13898 5972.57 3046 \n",
"\n",
" 2014-10-05 2014-10-12 2014-10-19 2014-10-26 2014-11-02 \\\n",
"Android 386.45 900 1367 1208 12 \n",
"iOS 3234.00 3680 5542 3089 5 \n",
"\n",
" 2014-11-09 \n",
"Android 991 \n",
"iOS 2054 \n",
"\n",
"[2 rows x 25 columns]"
]
}
],
"prompt_number": 48
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"metrics = OrderedDict([('Android','android_cpu'), \n",
" ('iOS','ios_cpu')])\n",
"slide21_chart3 = metrics_for_chart_portal(df, metrics, 'uk')\n",
"slide21_chart3.to_excel(writer, 'Slide 21', startrow=10, startcol=1)\n",
"slide21_chart3"
],
"language": "python",
"metadata": {},
"outputs": [
{
"html": [
"<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>2014-05-25</th>\n",
" <th>2014-06-01</th>\n",
" <th>2014-06-08</th>\n",
" <th>2014-06-15</th>\n",
" <th>2014-06-22</th>\n",
" <th>2014-06-29</th>\n",
" <th>2014-07-06</th>\n",
" <th>2014-07-13</th>\n",
" <th>2014-07-20</th>\n",
" <th>2014-07-27</th>\n",
" <th>...</th>\n",
" <th>2014-09-07</th>\n",
" <th>2014-09-14</th>\n",
" <th>2014-09-21</th>\n",
" <th>2014-09-28</th>\n",
" <th>2014-10-05</th>\n",
" <th>2014-10-12</th>\n",
" <th>2014-10-19</th>\n",
" <th>2014-10-26</th>\n",
" <th>2014-11-02</th>\n",
" <th>2014-11-09</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>Android</th>\n",
" <td> 6.995470</td>\n",
" <td> 4.902667</td>\n",
" <td> 5.323583</td>\n",
" <td> 7.194509</td>\n",
" <td> 7.353137</td>\n",
" <td> 9.013595</td>\n",
" <td> 8.316786</td>\n",
" <td> 9.15732</td>\n",
" <td> 9.987035</td>\n",
" <td> 6.421044</td>\n",
" <td>...</td>\n",
" <td> 5.324459</td>\n",
" <td> 4.173796</td>\n",
" <td> 6.743560</td>\n",
" <td> 9.060284</td>\n",
" <td> 9.731453</td>\n",
" <td> 11.869028</td>\n",
" <td> 8.534104</td>\n",
" <td> 5.787376</td>\n",
" <td> 2.571333</td>\n",
" <td> 4.092836</td>\n",
" </tr>\n",
" <tr>\n",
" <th>iOS</th>\n",
" <td> 7.622439</td>\n",
" <td> 4.128355</td>\n",
" <td> 5.427336</td>\n",
" <td> 6.660496</td>\n",
" <td> 6.505062</td>\n",
" <td> 6.445617</td>\n",
" <td> 6.706761</td>\n",
" <td> 7.60991</td>\n",
" <td> 9.517021</td>\n",
" <td> 7.014727</td>\n",
" <td>...</td>\n",
" <td> 3.785819</td>\n",
" <td> 2.500045</td>\n",
" <td> 4.309073</td>\n",
" <td> 9.098726</td>\n",
" <td> 9.328185</td>\n",
" <td> 9.672669</td>\n",
" <td> 9.054395</td>\n",
" <td> 7.521867</td>\n",
" <td> 16.476800</td>\n",
" <td> 5.115385</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>2 rows \u00d7 25 columns</p>\n",
"</div>"
],
"metadata": {},
"output_type": "pyout",
"prompt_number": 49,
"text": [
" 2014-05-25 2014-06-01 2014-06-08 2014-06-15 2014-06-22 \\\n",
"Android 6.995470 4.902667 5.323583 7.194509 7.353137 \n",
"iOS 7.622439 4.128355 5.427336 6.660496 6.505062 \n",
"\n",
" 2014-06-29 2014-07-06 2014-07-13 2014-07-20 2014-07-27 \\\n",
"Android 9.013595 8.316786 9.15732 9.987035 6.421044 \n",
"iOS 6.445617 6.706761 7.60991 9.517021 7.014727 \n",
"\n",
" ... 2014-09-07 2014-09-14 2014-09-21 2014-09-28 \\\n",
"Android ... 5.324459 4.173796 6.743560 9.060284 \n",
"iOS ... 3.785819 2.500045 4.309073 9.098726 \n",
"\n",
" 2014-10-05 2014-10-12 2014-10-19 2014-10-26 2014-11-02 \\\n",
"Android 9.731453 11.869028 8.534104 5.787376 2.571333 \n",
"iOS 9.328185 9.672669 9.054395 7.521867 16.476800 \n",
"\n",
" 2014-11-09 \n",
"Android 4.092836 \n",
"iOS 5.115385 \n",
"\n",
"[2 rows x 25 columns]"
]
}
],
"prompt_number": 49
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"metrics = OrderedDict([('Android','android_spend'), \n",
" ('iOS','ios_spend')])\n",
"slide22_chart1 = metrics_for_chart_portal(df, metrics, 'us')\n",
"slide22_chart1.to_excel(writer, 'Slide 22', startrow=1, startcol=1)\n",
"slide22_chart1"
],
"language": "python",
"metadata": {},
"outputs": [
{
"html": [
"<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>2014-05-25</th>\n",
" <th>2014-06-01</th>\n",
" <th>2014-06-08</th>\n",
" <th>2014-06-15</th>\n",
" <th>2014-06-22</th>\n",
" <th>2014-06-29</th>\n",
" <th>2014-07-06</th>\n",
" <th>2014-07-13</th>\n",
" <th>2014-07-20</th>\n",
" <th>2014-07-27</th>\n",
" <th>...</th>\n",
" <th>2014-09-07</th>\n",
" <th>2014-09-14</th>\n",
" <th>2014-09-21</th>\n",
" <th>2014-09-28</th>\n",
" <th>2014-10-05</th>\n",
" <th>2014-10-12</th>\n",
" <th>2014-10-19</th>\n",
" <th>2014-10-26</th>\n",
" <th>2014-11-02</th>\n",
" <th>2014-11-09</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>Android</th>\n",
" <td> 5586.3420</td>\n",
" <td> 5067.5888</td>\n",
" <td> 11325.7632</td>\n",
" <td> 11794.1056</td>\n",
" <td> 16165.6788</td>\n",
" <td> 20449.3732</td>\n",
" <td> 11965.3108</td>\n",
" <td> 0</td>\n",
" <td> 786.0604</td>\n",
" <td> 2216.2360</td>\n",
" <td>...</td>\n",
" <td> 24605.2508</td>\n",
" <td> 41811.316404</td>\n",
" <td> 25599.452400</td>\n",
" <td> 37466.464800</td>\n",
" <td> 28611.3324</td>\n",
" <td> 26885.532</td>\n",
" <td> 36468.8204</td>\n",
" <td> 28572.514876</td>\n",
" <td> 27798.09937</td>\n",
" <td> 15234</td>\n",
" </tr>\n",
" <tr>\n",
" <th>iOS</th>\n",
" <td> 6192.1228</td>\n",
" <td> 5001.9780</td>\n",
" <td> 41226.8840</td>\n",
" <td> 46021.7924</td>\n",
" <td> 49762.8772</td>\n",
" <td> 53825.8068</td>\n",
" <td> 23548.1592</td>\n",
" <td> 0</td>\n",
" <td> 1356.1212</td>\n",
" <td> 5885.0144</td>\n",
" <td>...</td>\n",
" <td> 31800.1404</td>\n",
" <td> 41784.853198</td>\n",
" <td> 38659.892804</td>\n",
" <td> 67104.002401</td>\n",
" <td> 59590.0002</td>\n",
" <td> 16367.322</td>\n",
" <td> 14913.5180</td>\n",
" <td> 74241.094000</td>\n",
" <td> 78567.00000</td>\n",
" <td> 35188</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>2 rows \u00d7 25 columns</p>\n",
"</div>"
],
"metadata": {},
"output_type": "pyout",
"prompt_number": 50,
"text": [
" 2014-05-25 2014-06-01 2014-06-08 2014-06-15 2014-06-22 \\\n",
"Android 5586.3420 5067.5888 11325.7632 11794.1056 16165.6788 \n",
"iOS 6192.1228 5001.9780 41226.8840 46021.7924 49762.8772 \n",
"\n",
" 2014-06-29 2014-07-06 2014-07-13 2014-07-20 2014-07-27 \\\n",
"Android 20449.3732 11965.3108 0 786.0604 2216.2360 \n",
"iOS 53825.8068 23548.1592 0 1356.1212 5885.0144 \n",
"\n",
" ... 2014-09-07 2014-09-14 2014-09-21 2014-09-28 \\\n",
"Android ... 24605.2508 41811.316404 25599.452400 37466.464800 \n",
"iOS ... 31800.1404 41784.853198 38659.892804 67104.002401 \n",
"\n",
" 2014-10-05 2014-10-12 2014-10-19 2014-10-26 2014-11-02 \\\n",
"Android 28611.3324 26885.532 36468.8204 28572.514876 27798.09937 \n",
"iOS 59590.0002 16367.322 14913.5180 74241.094000 78567.00000 \n",
"\n",
" 2014-11-09 \n",
"Android 15234 \n",
"iOS 35188 \n",
"\n",
"[2 rows x 25 columns]"
]
}
],
"prompt_number": 50
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"metrics = OrderedDict([('Android','android_paid_members'), \n",
" ('iOS','ios_paid_members')])\n",
"slide22_chart2 = metrics_for_chart_portal(df, metrics, 'us')\n",
"slide22_chart2.to_excel(writer, 'Slide 22', startrow=5, startcol=1)\n",
"slide22_chart2"
],
"language": "python",
"metadata": {},
"outputs": [
{
"html": [
"<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>2014-05-25</th>\n",
" <th>2014-06-01</th>\n",
" <th>2014-06-08</th>\n",
" <th>2014-06-15</th>\n",
" <th>2014-06-22</th>\n",
" <th>2014-06-29</th>\n",
" <th>2014-07-06</th>\n",
" <th>2014-07-13</th>\n",
" <th>2014-07-20</th>\n",
" <th>2014-07-27</th>\n",
" <th>...</th>\n",
" <th>2014-09-07</th>\n",
" <th>2014-09-14</th>\n",
" <th>2014-09-21</th>\n",
" <th>2014-09-28</th>\n",
" <th>2014-10-05</th>\n",
" <th>2014-10-12</th>\n",
" <th>2014-10-19</th>\n",
" <th>2014-10-26</th>\n",
" <th>2014-11-02</th>\n",
" <th>2014-11-09</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>Android</th>\n",
" <td> 1746</td>\n",
" <td> 1022</td>\n",
" <td> 2016</td>\n",
" <td> 1676</td>\n",
" <td> 3100.00</td>\n",
" <td> 4210.00</td>\n",
" <td> 2491.00</td>\n",
" <td> 0</td>\n",
" <td> 1019</td>\n",
" <td> 2873</td>\n",
" <td>...</td>\n",
" <td> 11934</td>\n",
" <td> 15315.79099</td>\n",
" <td> 6891.0</td>\n",
" <td> 9397.57</td>\n",
" <td> 6436.582</td>\n",
" <td> 6854</td>\n",
" <td> 9769</td>\n",
" <td> 11178</td>\n",
" <td> 9035</td>\n",
" <td> 5441</td>\n",
" </tr>\n",
" <tr>\n",
" <th>iOS</th>\n",
" <td> 2412</td>\n",
" <td> 1309</td>\n",
" <td> 9491</td>\n",
" <td> 8894</td>\n",
" <td> 11115.21</td>\n",
" <td> 12190.64</td>\n",
" <td> 6002.92</td>\n",
" <td> 0</td>\n",
" <td> 1758</td>\n",
" <td> 7629</td>\n",
" <td>...</td>\n",
" <td> 10381</td>\n",
" <td> 10915.00000</td>\n",
" <td> 8200.6</td>\n",
" <td> 14844.60</td>\n",
" <td> 14819.000</td>\n",
" <td> 4838</td>\n",
" <td> 5681</td>\n",
" <td> 25247</td>\n",
" <td> 19388</td>\n",
" <td> 7851</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>2 rows \u00d7 25 columns</p>\n",
"</div>"
],
"metadata": {},
"output_type": "pyout",
"prompt_number": 51,
"text": [
" 2014-05-25 2014-06-01 2014-06-08 2014-06-15 2014-06-22 \\\n",
"Android 1746 1022 2016 1676 3100.00 \n",
"iOS 2412 1309 9491 8894 11115.21 \n",
"\n",
" 2014-06-29 2014-07-06 2014-07-13 2014-07-20 2014-07-27 \\\n",
"Android 4210.00 2491.00 0 1019 2873 \n",
"iOS 12190.64 6002.92 0 1758 7629 \n",
"\n",
" ... 2014-09-07 2014-09-14 2014-09-21 2014-09-28 \\\n",
"Android ... 11934 15315.79099 6891.0 9397.57 \n",
"iOS ... 10381 10915.00000 8200.6 14844.60 \n",
"\n",
" 2014-10-05 2014-10-12 2014-10-19 2014-10-26 2014-11-02 \\\n",
"Android 6436.582 6854 9769 11178 9035 \n",
"iOS 14819.000 4838 5681 25247 19388 \n",
"\n",
" 2014-11-09 \n",
"Android 5441 \n",
"iOS 7851 \n",
"\n",
"[2 rows x 25 columns]"
]
}
],
"prompt_number": 51
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"metrics = OrderedDict([('Android','android_cpu'), \n",
" ('iOS','ios_cpu')])\n",
"slide22_chart3 = metrics_for_chart_portal(df, metrics, 'us')\n",
"slide22_chart3.to_excel(writer, 'Slide 22', startrow=10, startcol=1)\n",
"slide22_chart3"
],
"language": "python",
"metadata": {},
"outputs": [
{
"html": [
"<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>2014-05-25</th>\n",
" <th>2014-06-01</th>\n",
" <th>2014-06-08</th>\n",
" <th>2014-06-15</th>\n",
" <th>2014-06-22</th>\n",
" <th>2014-06-29</th>\n",
" <th>2014-07-06</th>\n",
" <th>2014-07-13</th>\n",
" <th>2014-07-20</th>\n",
" <th>2014-07-27</th>\n",
" <th>...</th>\n",
" <th>2014-09-07</th>\n",
" <th>2014-09-14</th>\n",
" <th>2014-09-21</th>\n",
" <th>2014-09-28</th>\n",
" <th>2014-10-05</th>\n",
" <th>2014-10-12</th>\n",
" <th>2014-10-19</th>\n",
" <th>2014-10-26</th>\n",
" <th>2014-11-02</th>\n",
" <th>2014-11-09</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>Android</th>\n",
" <td> 3.199509</td>\n",
" <td> 4.958502</td>\n",
" <td> 5.617938</td>\n",
" <td> 7.037056</td>\n",
" <td> 5.214735</td>\n",
" <td> 4.857333</td>\n",
" <td> 4.803417</td>\n",
" <td> 0</td>\n",
" <td> 0.771404</td>\n",
" <td> 0.771401</td>\n",
" <td>...</td>\n",
" <td> 2.061777</td>\n",
" <td> 2.729948</td>\n",
" <td> 3.714911</td>\n",
" <td> 3.986825</td>\n",
" <td> 4.445113</td>\n",
" <td> 3.922605</td>\n",
" <td> 3.733117</td>\n",
" <td> 2.556138</td>\n",
" <td> 3.076713</td>\n",
" <td> 2.799853</td>\n",
" </tr>\n",
" <tr>\n",
" <th>iOS</th>\n",
" <td> 2.567215</td>\n",
" <td> 3.821221</td>\n",
" <td> 4.343787</td>\n",
" <td> 5.174476</td>\n",
" <td> 4.477007</td>\n",
" <td> 4.415339</td>\n",
" <td> 3.922784</td>\n",
" <td> 0</td>\n",
" <td> 0.771400</td>\n",
" <td> 0.771400</td>\n",
" <td>...</td>\n",
" <td> 3.063302</td>\n",
" <td> 3.828205</td>\n",
" <td> 4.714276</td>\n",
" <td> 4.520432</td>\n",
" <td> 4.021189</td>\n",
" <td> 3.383076</td>\n",
" <td> 2.625157</td>\n",
" <td> 2.940591</td>\n",
" <td> 4.052352</td>\n",
" <td> 4.481977</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>2 rows \u00d7 25 columns</p>\n",
"</div>"
],
"metadata": {},
"output_type": "pyout",
"prompt_number": 52,
"text": [
" 2014-05-25 2014-06-01 2014-06-08 2014-06-15 2014-06-22 \\\n",
"Android 3.199509 4.958502 5.617938 7.037056 5.214735 \n",
"iOS 2.567215 3.821221 4.343787 5.174476 4.477007 \n",
"\n",
" 2014-06-29 2014-07-06 2014-07-13 2014-07-20 2014-07-27 \\\n",
"Android 4.857333 4.803417 0 0.771404 0.771401 \n",
"iOS 4.415339 3.922784 0 0.771400 0.771400 \n",
"\n",
" ... 2014-09-07 2014-09-14 2014-09-21 2014-09-28 \\\n",
"Android ... 2.061777 2.729948 3.714911 3.986825 \n",
"iOS ... 3.063302 3.828205 4.714276 4.520432 \n",
"\n",
" 2014-10-05 2014-10-12 2014-10-19 2014-10-26 2014-11-02 \\\n",
"Android 4.445113 3.922605 3.733117 2.556138 3.076713 \n",
"iOS 4.021189 3.383076 2.625157 2.940591 4.052352 \n",
"\n",
" 2014-11-09 \n",
"Android 2.799853 \n",
"iOS 4.481977 \n",
"\n",
"[2 rows x 25 columns]"
]
}
],
"prompt_number": 52
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"writer.save()"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 53
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"print('\\n'.join(df.columns))"
],
"language": "python",
"metadata": {},
"outputs": [
{
"output_type": "stream",
"stream": "stdout",
"text": [
"period\n",
"au_count\n",
"au_count:de\n",
"au_count:lt\n",
"au_count:cz\n",
"au_count:lt_babies\n",
"au_count:de_babies\n",
"au_count:pl\n",
"au_count:fr\n",
"au_count:us\n",
"au_count:uk\n",
"au_count:at\n",
"total_members\n",
"total_members:lt_babies\n",
"total_members:de_babies\n",
"total_members:at\n",
"total_members:uk\n",
"total_members:lt\n",
"total_members:pl\n",
"total_members:cz\n",
"total_members:fr\n",
"total_members:us\n",
"total_members:de\n",
"new_members\n",
"new_members:lt_babies\n",
"new_members:lt\n",
"new_members:de_babies\n",
"new_members:pl\n",
"new_members:at\n",
"new_members:cz\n",
"new_members:uk\n",
"new_members:fr\n",
"new_members:us\n",
"new_members:de\n",
"new_listings\n",
"new_listings:de\n",
"new_listings:lt\n",
"new_listings:cz\n",
"new_listings:lt_babies\n",
"new_listings:de_babies\n",
"new_listings:pl\n",
"new_listings:fr\n",
"new_listings:us\n",
"new_listings:uk\n",
"new_listings:at\n",
"2nd_week_member_retention\n",
"2nd_week_member_retention:desktop\n",
"2nd_week_member_retention:desktop:de\n",
"2nd_week_member_retention:desktop:lt\n",
"2nd_week_member_retention:desktop:cz\n",
"2nd_week_member_retention:desktop:lt_babies\n",
"2nd_week_member_retention:desktop:de_babies\n",
"2nd_week_member_retention:desktop:pl\n",
"2nd_week_member_retention:desktop:fr\n",
"2nd_week_member_retention:desktop:us\n",
"2nd_week_member_retention:desktop:uk\n",
"2nd_week_member_retention:desktop:at\n",
"2nd_week_member_retention:ios\n",
"2nd_week_member_retention:ios:de\n",
"2nd_week_member_retention:ios:lt\n",
"2nd_week_member_retention:ios:cz\n",
"2nd_week_member_retention:ios:lt_babies\n",
"2nd_week_member_retention:ios:de_babies\n",
"2nd_week_member_retention:ios:pl\n",
"2nd_week_member_retention:ios:fr\n",
"2nd_week_member_retention:ios:us\n",
"2nd_week_member_retention:ios:uk\n",
"2nd_week_member_retention:ios:at\n",
"2nd_week_member_retention:android\n",
"2nd_week_member_retention:android:de\n",
"2nd_week_member_retention:android:lt\n",
"2nd_week_member_retention:android:cz\n",
"2nd_week_member_retention:android:lt_babies\n",
"2nd_week_member_retention:android:de_babies\n",
"2nd_week_member_retention:android:pl\n",
"2nd_week_member_retention:android:fr\n",
"2nd_week_member_retention:android:us\n",
"2nd_week_member_retention:android:uk\n",
"2nd_week_member_retention:android:at\n",
"5th_week_member_retention\n",
"5th_week_member_retention:desktop\n",
"5th_week_member_retention:desktop:de\n",
"5th_week_member_retention:desktop:lt\n",
"5th_week_member_retention:desktop:cz\n",
"5th_week_member_retention:desktop:lt_babies\n",
"5th_week_member_retention:desktop:de_babies\n",
"5th_week_member_retention:desktop:pl\n",
"5th_week_member_retention:desktop:fr\n",
"5th_week_member_retention:desktop:us\n",
"5th_week_member_retention:desktop:uk\n",
"5th_week_member_retention:desktop:at\n",
"5th_week_member_retention:ios\n",
"5th_week_member_retention:ios:de\n",
"5th_week_member_retention:ios:lt\n",
"5th_week_member_retention:ios:cz\n",
"5th_week_member_retention:ios:lt_babies\n",
"5th_week_member_retention:ios:de_babies\n",
"5th_week_member_retention:ios:pl\n",
"5th_week_member_retention:ios:fr\n",
"5th_week_member_retention:ios:us\n",
"5th_week_member_retention:ios:uk\n",
"5th_week_member_retention:ios:at\n",
"5th_week_member_retention:android\n",
"5th_week_member_retention:android:de\n",
"5th_week_member_retention:android:lt\n",
"5th_week_member_retention:android:cz\n",
"5th_week_member_retention:android:lt_babies\n",
"5th_week_member_retention:android:de_babies\n",
"5th_week_member_retention:android:pl\n",
"5th_week_member_retention:android:fr\n",
"5th_week_member_retention:android:us\n",
"5th_week_member_retention:android:uk\n",
"5th_week_member_retention:android:at\n",
"tx_count\n",
"tx_count:de\n",
"tx_count:without_voucher:de\n",
"tx_count:lt\n",
"tx_count:without_voucher:lt\n",
"tx_count:cz\n",
"tx_count:without_voucher:cz\n",
"tx_count:lt_babies\n",
"tx_count:without_voucher:lt_babies\n",
"tx_count:de_babies\n",
"tx_count:without_voucher:de_babies\n",
"tx_count:pl\n",
"tx_count:without_voucher:pl\n",
"tx_count:fr\n",
"tx_count:without_voucher:fr\n",
"tx_count:with_voucher:fr\n",
"tx_count:us\n",
"tx_count:without_voucher:us\n",
"tx_count:with_voucher:us\n",
"tx_count:uk\n",
"tx_count:without_voucher:uk\n",
"tx_count:with_voucher:uk\n",
"tx_count:at\n",
"tx_count:without_voucher:at\n",
"gmv\n",
"gmv:de\n",
"gmv:without_voucher:de\n",
"gmv:lt\n",
"gmv:without_voucher:lt\n",
"gmv:cz\n",
"gmv:without_voucher:cz\n",
"gmv:lt_babies\n",
"gmv:without_voucher:lt_babies\n",
"gmv:de_babies\n",
"gmv:without_voucher:de_babies\n",
"gmv:pl\n",
"gmv:without_voucher:pl\n",
"gmv:fr\n",
"gmv:without_voucher:fr\n",
"gmv:with_voucher:fr\n",
"gmv:us\n",
"gmv:without_voucher:us\n",
"gmv:with_voucher:us\n",
"gmv:uk\n",
"gmv:without_voucher:uk\n",
"gmv:with_voucher:uk\n",
"gmv:at\n",
"gmv:without_voucher:at\n",
"avg_tx_price\n",
"avg_tx_price:de\n",
"avg_tx_price:without_voucher:de\n",
"avg_tx_price:lt\n",
"avg_tx_price:without_voucher:lt\n",
"avg_tx_price:cz\n",
"avg_tx_price:without_voucher:cz\n",
"avg_tx_price:lt_babies\n",
"avg_tx_price:without_voucher:lt_babies\n",
"avg_tx_price:de_babies\n",
"avg_tx_price:without_voucher:de_babies\n",
"avg_tx_price:pl\n",
"avg_tx_price:without_voucher:pl\n",
"avg_tx_price:fr\n",
"avg_tx_price:without_voucher:fr\n",
"avg_tx_price:with_voucher:fr\n",
"avg_tx_price:us\n",
"avg_tx_price:without_voucher:us\n",
"avg_tx_price:with_voucher:us\n",
"avg_tx_price:uk\n",
"avg_tx_price:without_voucher:uk\n",
"avg_tx_price:with_voucher:uk\n",
"avg_tx_price:at\n",
"avg_tx_price:without_voucher:at\n",
"refunds_percentage\n",
"refunds_percentage:de\n",
"refunds_percentage:without_voucher:de\n",
"refunds_percentage:lt\n",
"refunds_percentage:without_voucher:lt\n",
"refunds_percentage:cz\n",
"refunds_percentage:without_voucher:cz\n",
"refunds_percentage:lt_babies\n",
"refunds_percentage:without_voucher:lt_babies\n",
"refunds_percentage:de_babies\n",
"refunds_percentage:without_voucher:de_babies\n",
"refunds_percentage:pl\n",
"refunds_percentage:without_voucher:pl\n",
"refunds_percentage:fr\n",
"refunds_percentage:without_voucher:fr\n",
"refunds_percentage:with_voucher:fr\n",
"refunds_percentage:us\n",
"refunds_percentage:without_voucher:us\n",
"refunds_percentage:with_voucher:us\n",
"refunds_percentage:uk\n",
"refunds_percentage:without_voucher:uk\n",
"refunds_percentage:with_voucher:uk\n",
"refunds_percentage:at\n",
"refunds_percentage:without_voucher:at\n",
"tx_per_au\n",
"tx_per_au:de\n",
"tx_per_au:lt\n",
"tx_per_au:cz\n",
"tx_per_au:lt_babies\n",
"tx_per_au:de_babies\n",
"tx_per_au:pl\n",
"tx_per_au:fr\n",
"tx_per_au:us\n",
"tx_per_au:uk\n",
"tx_per_au:at\n",
"gmv_per_au\n",
"gmv_per_au:de\n",
"gmv_per_au:lt\n",
"gmv_per_au:cz\n",
"gmv_per_au:lt_babies\n",
"gmv_per_au:de_babies\n",
"gmv_per_au:pl\n",
"gmv_per_au:fr\n",
"gmv_per_au:us\n",
"gmv_per_au:uk\n",
"gmv_per_au:at\n",
"msg_threads\n",
"msg_threads:de\n",
"msg_threads:lt\n",
"msg_threads:cz\n",
"msg_threads:lt_babies\n",
"msg_threads:de_babies\n",
"msg_threads:pl\n",
"msg_threads:fr\n",
"msg_threads:us\n",
"msg_threads:uk\n",
"msg_threads:at\n",
"costs\n",
"costs:fr\n",
"costs:us\n",
"costs:uk\n",
"general_income\n",
"general_income:de\n",
"general_income:lt\n",
"general_income:pl\n",
"general_income:fr\n",
"general_income:us\n",
"general_income:at\n",
"income\n",
"income:fr\n",
"income:marketplace_fee_income_total:fr\n",
"income:shipping_extra_income_total:fr\n",
"income:shipping_income_total:fr\n",
"income:us\n",
"income:marketplace_fee_income_total:us\n",
"income:shipping_extra_income_total:us\n",
"income:shipping_income_total:us\n",
"income:uk\n",
"income:marketplace_fee_income_total:uk\n",
"income:shipping_extra_income_total:uk\n",
"income:shipping_income_total:uk\n",
"counter_income\n",
"counter_income:fr\n",
"counter_income:marketplace_fee_income_refunded:fr\n",
"counter_income:shipping_extra_income_refunded:fr\n",
"counter_income:shipping_income_refunded:fr\n",
"counter_income:us\n",
"counter_income:marketplace_fee_income_refunded:us\n",
"counter_income:shipping_extra_income_refunded:us\n",
"counter_income:shipping_income_refunded:us\n",
"counter_income:uk\n",
"counter_income:marketplace_fee_income_refunded:uk\n",
"counter_income:shipping_extra_income_refunded:uk\n",
"counter_income:shipping_income_refunded:uk\n",
"expenses\n",
"expenses:fr\n",
"expenses:voucher_expenses:fr\n",
"expenses:google_wallet_discount_expenses:fr\n",
"expenses:us\n",
"expenses:voucher_expenses:us\n",
"expenses:google_wallet_discount_expenses:us\n",
"expenses:endicia_expenses:us\n",
"expenses:uk\n",
"expenses:voucher_expenses:uk\n",
"expenses:google_wallet_discount_expenses:uk\n",
"counter_expenses\n",
"counter_expenses:fr\n",
"counter_expenses:vouchers_amount_refunded:fr\n",
"counter_expenses:google_wallet_discount_refunded:fr\n",
"counter_expenses:us\n",
"counter_expenses:vouchers_amount_refunded:us\n",
"counter_expenses:google_wallet_discount_refunded:us\n",
"counter_expenses:endicia_refunds:us\n",
"counter_expenses:uk\n",
"counter_expenses:vouchers_amount_refunded:uk\n",
"counter_expenses:google_wallet_discount_refunded:uk\n",
"ios_spend:uk\n",
"android_spend:uk\n",
"ios_paid_members:uk\n",
"android_paid_members:uk\n",
"ios_cpu:uk\n",
"android_cpu:uk\n",
"total_spend:uk\n",
"total_paid_members:uk\n",
"total_cpu:uk\n",
"ios_spend:us\n",
"android_spend:us\n",
"ios_paid_members:us\n",
"android_paid_members:us\n",
"ios_cpu:us\n",
"android_cpu:us\n",
"total_spend:us\n",
"total_paid_members:us\n",
"total_cpu:us\n",
"comments:us\n",
"fee_type:card_processing:uk\n",
"fee_type:card_processing_fee_refunded:uk\n",
"fee_type:marketplace_share:uk\n",
"fee_type:payout:uk\n",
"fee_type:marketplace_share_refunded:uk\n",
"fee_type:card_processing:us\n",
"fee_type:card_processing_fee_refunded:us\n",
"fee_type:marketplace_share:us\n",
"fee_type:payout:us\n",
"fee_type:marketplace_share_refunded:us\n",
"total_spend\n",
"total_paid_members\n",
"organic_members\n",
"organic_members:us\n",
"organic_members:uk\n",
"cogs:uk\n",
"cogs:us\n",
"revenues:us\n",
"revenues:uk\n",
"net_revenues:us\n"
]
}
],
"prompt_number": 54
},
{
"cell_type": "code",
"collapsed": false,
"input": [],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 54
}
],
"metadata": {}
}
]
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment