{
"cells": [
{
"cell_type": "code",
"execution_count": 112,
"metadata": {
"slideshow": {
"slide_type": "skip"
}
},
"outputs": [],
"source": [
"import pandas as pd \n",
"import numpy as np\n",
"import matplotlib.pyplot as plt\n",
"import seaborn as sns\n",
"import math\n",
"import datetime\n",
"import statsmodels.api as sm\n",
"import os\n",
"from forex_python.converter import CurrencyRates\n",
"\n",
"import swat\n",
"import warnings\n",
"\n",
"from sklearn.linear_model import LinearRegression\n",
"from sklearn.svm import SVR\n",
"\n",
"warnings.filterwarnings('ignore')\n",
"pd.set_option('display.max_columns', None)\n",
"sns.set_style('darkgrid')\n",
"pd.options.display.max_rows = 2000\n",
"%matplotlib inline"
]
},
{
"cell_type": "code",
"execution_count": 113,
"metadata": {
"slideshow": {
"slide_type": "skip"
}
},
"outputs": [],
"source": [
"path = '/Users/zhan009/Desktop/ad benchmark/all_data/'"
]
},
{
"cell_type": "code",
"execution_count": 114,
"metadata": {
"slideshow": {
"slide_type": "skip"
}
},
"outputs": [],
"source": [
"# data['Amount Spent (USD)'] = [i * 1.19 if not np.isnan(i) else i for index, i in enumerate(data['Amount Spent (EUR)'])]"
]
},
{
"cell_type": "code",
"execution_count": 115,
"metadata": {
"slideshow": {
"slide_type": "skip"
}
},
"outputs": [],
"source": [
"final_data = pd.DataFrame()\n",
"for filename in os.listdir(path):\n",
" if filename.endswith('.csv'):\n",
" label_data = pd.read_csv(path + filename)\n",
" final_data = pd.concat([final_data, label_data], axis = 0)\n",
" \n",
"final_data = final_data.reset_index()\n",
"final_data = final_data.drop('index', axis = 1)\n",
"original_country_data = final_data.copy()"
]
},
{
"cell_type": "code",
"execution_count": 116,
"metadata": {
"slideshow": {
"slide_type": "skip"
}
},
"outputs": [],
"source": [
"final_value = []\n",
"currency = ['Amount Spent (ARS)', 'Amount Spent (BRL)', 'Amount Spent (COP)', \n",
" 'Amount Spent (GBP)', 'Amount Spent (MXN)', 'Amount Spent (EUR)']\n",
"rate = [0.013, 0.19, 0.00027, 1.30, 0.046, 1.19]\n",
"currency_dict = dict(zip(currency, rate))\n",
"for index, i in enumerate(final_data['Amount Spent (USD)']):\n",
" if i == 0:\n",
" for cur in currency_dict:\n",
" if np.isnan(final_data[cur][index]) != 0:\n",
" final_value.append(currency_dict[cur] * final_data[cur][index])\n",
" break\n",
" else:\n",
" final_value.append(final_data[cur][index])\n",
" else:\n",
" final_value.append(i)\n",
"\n",
"final_data['Amount Spent (USD)'] = final_value\n",
"final_data = final_data.drop(currency, axis = 1)\n",
"final_data = final_data.dropna(subset = ['Country'])"
]
},
{
"cell_type": "code",
"execution_count": 117,
"metadata": {
"slideshow": {
"slide_type": "skip"
}
},
"outputs": [],
"source": [
"## How many valid data record for each country, \n",
"## Rank then and select data record > 1000\n",
"final_data_country = final_data.copy()\n",
"count_result = final_data.groupby('Country').count()['Amount Spent (USD)']\n",
"sort_result = count_result.sort_values(ascending=False)\n",
"sort_result = sort_result.reset_index()\n",
"sort_result = sort_result[sort_result['Amount Spent (USD)'] > 1000]\n",
"country_list = sort_result.Country\n",
"#filter out data records that is smaller than 1000\n",
"# sort_result = sort_result[sort_result['Amount Spent (USD)'] > 1000] \n",
"# country_list = list(sort_result.Country)\n",
"# final_data_country = final_data[final_data.Country.isin(country_list)]"
]
},
{
"cell_type": "code",
"execution_count": 118,
"metadata": {},
"outputs": [],
"source": [
"selected_country_data = final_data_country[final_data_country.Country.isin(country_list)]\n",
"selected_country_data = pd.concat([selected_country_data, pd.get_dummies(selected_country_data.Country)], axis=1)\n",
"# selected_country_data = selected_country_data.drop('Country', axis = 1)"
]
},
{
"cell_type": "markdown",
"metadata": {
"slideshow": {
"slide_type": "skip"
}
},
"source": [
"# Country Analysis In general "
]
},
{
"cell_type": "code",
"execution_count": 119,
"metadata": {
"scrolled": true,
"slideshow": {
"slide_type": "slide"
}
},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Objective | \n",
" Amount Spent (USD) | \n",
"
\n",
" \n",
" \n",
" \n",
" | 10 | \n",
" Traffic | \n",
" 84659 | \n",
"
\n",
" \n",
" | 11 | \n",
" Video Views | \n",
" 10884 | \n",
"
\n",
" \n",
" | 9 | \n",
" Reach | \n",
" 10315 | \n",
"
\n",
" \n",
" | 2 | \n",
" CONVERSIONS | \n",
" 6375 | \n",
"
\n",
" \n",
" | 1 | \n",
" Brand Awareness | \n",
" 1217 | \n",
"
\n",
" \n",
" | 4 | \n",
" Engagement | \n",
" 854 | \n",
"
\n",
" \n",
" | 8 | \n",
" Page Likes | \n",
" 365 | \n",
"
\n",
" \n",
" | 5 | \n",
" Event Responses | \n",
" 78 | \n",
"
\n",
" \n",
" | 3 | \n",
" Catalog Sales | \n",
" 28 | \n",
"
\n",
" \n",
" | 6 | \n",
" Lead Generation | \n",
" 10 | \n",
"
\n",
" \n",
" | 7 | \n",
" Messages | \n",
" 5 | \n",
"
\n",
" \n",
" | 0 | \n",
" App Installs | \n",
" 0 | \n",
"
\n",
" \n",
"
\n",
"