{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "This notebook merges the results found from the search enginge based ad campaign matching methods and publishes the results to s3 as a csv. " ] }, { "cell_type": "code", "execution_count": 37, "metadata": {}, "outputs": [], "source": [ "import boto3\n", "import pandas as pd\n", "\n", "s3 = boto3.client('s3')\n", "\n", "events_streams_bucket = 'data-science-events-streams'\n", "ads_csv = 'ad_campaign_all_by_day.csv'\n", "\n", "obj = s3.get_object(Bucket=events_streams_bucket, Key=ads_csv)\n", "ads_df = pd.read_csv(\n", " obj['Body'], \n", " dtype={'Campaign Advertiser ID': str}, \n", " parse_dates=['Campaign Start Date', 'Campaign End Date', 'Day'])\n", "\n", "# Specify Column Names to keep from original DF\n", "new_cols = [\n", " 'Campaign Name',\n", " 'Campaign Key',\n", " 'Campaign Start Date',\n", " 'Campaign End Date',\n", " 'Day',\n", " 'Impact Score 2.0',\n", " 'Media Cost',\n", " 'Campaign Budget',\n", " 'Campaign Status',\n", " 'Campaign Category',\n", " 'Ad Recall Lift (People)'\n", "]\n", "\n", "# Drop all but chosen columns\n", "ads_df = ads_df[new_cols]\n", "\n", "\n", "\n", "def campaign_agg(x):\n", " \"\"\"Custom aggregation function for campaign Data\"\"\"\n", " names = {\n", " 'Campaign Key': x['Campaign Key'].iloc[0],\n", " 'Campaign Name': x['Campaign Name'].iloc[0],\n", " 'Campaign Start': x['Day'].min(),\n", " 'Campaign End': x['Day'].max()\n", " }\n", "\n", " return pd.Series(names, index=[\n", " 'Campaign Key', 'Campaign Name', 'Campaign Start', 'Campaign End'])\n", "\n", "# Aggregate ads data Campaign creating a summary object\n", "ads_df = ads_df.groupby('Campaign Key').apply(campaign_agg)\n", "ads_df = ads_df.sort_values(by=['Campaign Start'])" ] }, { "cell_type": "code", "execution_count": 38, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "count of unique campaigns: 6723\n" ] } ], "source": [ "ads_df.head(20)\n", "print('count of unique campaigns: ' + str(len(ads_df['Campaign Key'].unique())))" ] }, { "cell_type": "code", "execution_count": 46, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
artist_namecampaign_keyproduct_idrelease_daterelease_namesearch_termupc
7163Bob Dylan93406889811706781962-01-01Bob DylanBob Dylan5055122194729
10349ArmandoArmando Manzanero / Boleros Son / Age 30+ /Gen...17889921964-01-01Armando e Seu Solovox MaravilhosoArmando190374268752
10375ArmandoArmando Manzanero / Boleros Son / Age 30+ /Gen...17889921964-01-01Armando e Seu Solovox MaravilhosoArmando190374268752
10392ArmandoArmando Manzanero / Boleros Son / Age 30+ /Gen...17889921964-01-01Armando e Seu Solovox MaravilhosoArmando190374268752
10401ArmandoArmando Manzanero / Boleros Son / Age 30+ /Gen...17889921964-01-01Armando e Seu Solovox MaravilhosoArmando190374268752
\n", "
" ], "text/plain": [ " artist_name campaign_key \\\n", "7163 Bob Dylan 934068898 \n", "10349 Armando Armando Manzanero / Boleros Son / Age 30+ /Gen... \n", "10375 Armando Armando Manzanero / Boleros Son / Age 30+ /Gen... \n", "10392 Armando Armando Manzanero / Boleros Son / Age 30+ /Gen... \n", "10401 Armando Armando Manzanero / Boleros Son / Age 30+ /Gen... \n", "\n", " product_id release_date release_name search_term \\\n", "7163 1170678 1962-01-01 Bob Dylan Bob Dylan \n", "10349 1788992 1964-01-01 Armando e Seu Solovox Maravilhoso Armando \n", "10375 1788992 1964-01-01 Armando e Seu Solovox Maravilhoso Armando \n", "10392 1788992 1964-01-01 Armando e Seu Solovox Maravilhoso Armando \n", "10401 1788992 1964-01-01 Armando e Seu Solovox Maravilhoso Armando \n", "\n", " upc \n", "7163 5055122194729 \n", "10349 190374268752 \n", "10375 190374268752 \n", "10392 190374268752 \n", "10401 190374268752 " ] }, "execution_count": 46, "metadata": {}, "output_type": "execute_result" } ], "source": [ "matches_1 = pd.read_csv('./csv/matches_1_2000.csv', parse_dates=['release_date'])\n", "matches_2 = pd.read_csv('./csv/matches_2000_4000.csv', parse_dates=['release_date'])\n", "matches_3 = pd.read_csv('./csv/matches_4001_6723.csv', parse_dates=['release_date'])\n", "\n", "frames = [matches_1, matches_2, matches_3]\n", "\n", "matches_df = pd.concat(frames)\n", "matches_df = matches_df.drop(['Unnamed: 0'], axis=1)\n", "\n", "matches_df = matches_df.sort_values(by=['release_date'])\n", "matches_df.head()" ] }, { "cell_type": "code", "execution_count": 47, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "artist_name object\n", "campaign_key object\n", "product_id int64\n", "release_date datetime64[ns]\n", "release_name object\n", "search_term object\n", "upc int64\n", "dtype: object" ] }, "execution_count": 47, "metadata": {}, "output_type": "execute_result" } ], "source": [ "matches_df.dtypes" ] }, { "cell_type": "code", "execution_count": 48, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "release matches: 2437\n", "count of unique campaigns: 5724\n" ] } ], "source": [ "import datetime\n", "import re\n", "\n", "df_3 = pd.merge_asof(\n", " left=ads_df, right=matches_df, \n", " left_on='Campaign Start', right_on='release_date', \n", " left_by='Campaign Key', right_by='campaign_key')\n", "\n", "\n", "df_3['date_diff'] = df_3['Campaign Start'] - df_3['release_date']\n", "df_3['date_diff'] = df_3['date_diff'].abs()\n", "\n", "def release_match(row):\n", " if row['date_diff'] <= datetime.timedelta(days=14):\n", " return 1\n", " return 0\n", "\n", "\n", "def youtube_views(row):\n", " if re.search(r\"YT.*Video\", row['Campaign Name']):\n", " return 1\n", " return 0\n", " \n", "\n", "df_3['release_match'] = df_3.apply(release_match, axis=1)\n", "df_3['youtube_views'] = df_3.apply(youtube_views, axis=1)\n", "df_3['product_id'] = df['product_id'].astype(int)\n", "df_3['upc'] = df['upc'].astype(int)\n", "\n", "\n", "len(df_3[df_3.artist_name.notnull()])\n", "\n", "print('release matches: ' + str(df_3['release_match'].sum()))\n", "\n", "# count unique\n", "print('count of unique campaigns: ' + str(len(matches_df['campaign_key'].unique())))" ] }, { "cell_type": "code", "execution_count": 49, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Campaign Key object\n", "Campaign Name object\n", "Campaign Start datetime64[ns]\n", "Campaign End datetime64[ns]\n", "artist_name object\n", "campaign_key object\n", "product_id int64\n", "release_date datetime64[ns]\n", "release_name object\n", "search_term object\n", "upc int64\n", "date_diff timedelta64[ns]\n", "release_match int64\n", "youtube_views int64\n", "dtype: object" ] }, "execution_count": 49, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_3.dtypes" ] }, { "cell_type": "code", "execution_count": 50, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Campaign KeyCampaign NameCampaign StartCampaign Endartist_namecampaign_keyproduct_idrelease_daterelease_namesearch_termupcdate_diffrelease_matchyoutube_views
0Hatebreed - The Concrete Confessional PRE-ORDE...Hatebreed - The Concrete Confessional PRE-ORDE...2016-01-012016-05-12VIDEOHatebreed - The Concrete Confessional PRE-ORDE...585172013-07-08Blue JailVideo829410083782907 days00
1Kate Ceberano - Anthology PRE-ORDER Brand Awar...Kate Ceberano - Anthology PRE-ORDER Brand Awar...2016-01-012016-05-05VIDEOKate Ceberano - Anthology PRE-ORDER Brand Awar...8310262013-07-08Blue JailVideo3501910157329907 days00
2Kenny Garrett - Do Your Dance Video FB Out Now...Kenny Garrett - Do Your Dance Video FB Out Now...2016-01-012016-07-15Kenny GarrettKenny Garrett - Do Your Dance Video FB Out Now...19797562013-09-17Pushing the World AwayKenny Garrett191018648060836 days00
3Maggie Rose: The Variety Show Vol 1 - Out Now ...Maggie Rose: The Variety Show Vol 1 - Out Now ...2016-01-012016-05-02BrandMaggie Rose: The Variety Show Vol 1 - Out Now ...19805782006-09-05BrandBrand1910186548013405 days00
4Missing People Choir - \"I Hope\" PRE-ORDER Bran...Missing People Choir - \"I Hope\" PRE-ORDER Bran...2016-01-012016-05-10PeopleMissing People Choir - \"I Hope\" PRE-ORDER Bran...19800562012-08-28The Beautiful PeoplePeople1910186502161221 days00
\n", "
" ], "text/plain": [ " Campaign Key \\\n", "0 Hatebreed - The Concrete Confessional PRE-ORDE... \n", "1 Kate Ceberano - Anthology PRE-ORDER Brand Awar... \n", "2 Kenny Garrett - Do Your Dance Video FB Out Now... \n", "3 Maggie Rose: The Variety Show Vol 1 - Out Now ... \n", "4 Missing People Choir - \"I Hope\" PRE-ORDER Bran... \n", "\n", " Campaign Name Campaign Start \\\n", "0 Hatebreed - The Concrete Confessional PRE-ORDE... 2016-01-01 \n", "1 Kate Ceberano - Anthology PRE-ORDER Brand Awar... 2016-01-01 \n", "2 Kenny Garrett - Do Your Dance Video FB Out Now... 2016-01-01 \n", "3 Maggie Rose: The Variety Show Vol 1 - Out Now ... 2016-01-01 \n", "4 Missing People Choir - \"I Hope\" PRE-ORDER Bran... 2016-01-01 \n", "\n", " Campaign End artist_name \\\n", "0 2016-05-12 VIDEO \n", "1 2016-05-05 VIDEO \n", "2 2016-07-15 Kenny Garrett \n", "3 2016-05-02 Brand \n", "4 2016-05-10 People \n", "\n", " campaign_key product_id release_date \\\n", "0 Hatebreed - The Concrete Confessional PRE-ORDE... 58517 2013-07-08 \n", "1 Kate Ceberano - Anthology PRE-ORDER Brand Awar... 831026 2013-07-08 \n", "2 Kenny Garrett - Do Your Dance Video FB Out Now... 1979756 2013-09-17 \n", "3 Maggie Rose: The Variety Show Vol 1 - Out Now ... 1980578 2006-09-05 \n", "4 Missing People Choir - \"I Hope\" PRE-ORDER Bran... 1980056 2012-08-28 \n", "\n", " release_name search_term upc date_diff \\\n", "0 Blue Jail Video 829410083782 907 days \n", "1 Blue Jail Video 3501910157329 907 days \n", "2 Pushing the World Away Kenny Garrett 191018648060 836 days \n", "3 Brand Brand 191018654801 3405 days \n", "4 The Beautiful People People 191018650216 1221 days \n", "\n", " release_match youtube_views \n", "0 0 0 \n", "1 0 0 \n", "2 0 0 \n", "3 0 0 \n", "4 0 0 " ] }, "execution_count": 50, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_3.head()" ] }, { "cell_type": "code", "execution_count": 51, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "{'ETag': '\"6566950798ae73f7c9d125222d71a6b7\"',\n", " 'ResponseMetadata': {'HTTPHeaders': {'content-length': '0',\n", " 'date': 'Thu, 15 Nov 2018 23:10:29 GMT',\n", " 'etag': '\"6566950798ae73f7c9d125222d71a6b7\"',\n", " 'server': 'AmazonS3',\n", " 'x-amz-id-2': 'KSMOK5tIb0DdGNZP/S3vFMzUvoPyJ54wqw3whg/KMnpVx0uZZvHnudlkO6E/NPPSblU8C9MAPBE=',\n", " 'x-amz-request-id': '0FC54E46CCFBD503'},\n", " 'HTTPStatusCode': 200,\n", " 'HostId': 'KSMOK5tIb0DdGNZP/S3vFMzUvoPyJ54wqw3whg/KMnpVx0uZZvHnudlkO6E/NPPSblU8C9MAPBE=',\n", " 'RequestId': '0FC54E46CCFBD503',\n", " 'RetryAttempts': 0}}" ] }, "execution_count": 51, "metadata": {}, "output_type": "execute_result" } ], "source": [ "from io import StringIO\n", "\n", "csv_buffer = StringIO()\n", "df_3.to_csv(csv_buffer)\n", "s3_resource = boto3.resource('s3')\n", "s3_resource.Object('data-science-events-streams', 'ad_campaigns_with_matched_data.csv').put(Body=csv_buffer.getvalue())" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.6.4" } }, "nbformat": 4, "nbformat_minor": 2 }