2024_ISE Qual Script Template.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "ee448758",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\daniel.sezari\\AppData\\Local\\Temp\\ipykernel_23044\\301074297.py:169: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df[\"Comments\"][z] = str(df[\"Comments\"][z]).strip('\\n')\n"
     ]
    }
   ],
   "source": [
    "# How to use this script:\n",
    "# Step 1: Export dataset from here: https://lrback.firabarcelona.com/leads\n",
    "# Step 2: Save the export in the \"Raw\" folder in directory\n",
    "# Step 3: Rename the file to YYYY_Show_Day#_RAW.xlsx (e.g. 2023_ISE_RAW.xlsx)\n",
    "# Step 4: Update the name of the file in the chunk \"Import Dataset\"\n",
    "# Step 5: Change the export name at the end of the script (same name without suffix \"_RAW\")\n",
    "# Step 6: Update the chunk \"Staffing of the Day\" if needed\n",
    "# Step 7: Insert the timestamp of the last imported scan (from the day before) in the chunk \"Truncuate list from last update\"\n",
    "# Step 8: Run the Script\n",
    "# Step 9: Import both csv files into Hubspot\n",
    "\n",
    "\n",
    "# Import packages\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from datetime import date\n",
    "\n",
    "\n",
    "# Import Dataset\n",
    "df = pd.read_excel(\"C:\\\\Python\\\\ISE_Datasets\\\\Raw\\\\2024_ISE_Day4_Raw.xlsx\") # Step 4: UPDATE HERE\n",
    "\n",
    "\n",
    "#--------------------------------------------------------PREPROCESSING--------------------------------------------------------\n",
    "\n",
    "# Convert Visit Date string into datetime format\n",
    "df[\"Visit date\"] = pd.to_datetime(df['Visit date'])\n",
    "df[\"ISE 2024 Visit Day\"] = df[\"Visit date\"].dt.date\n",
    "\n",
    "\n",
    "# Truncuate list from last update\n",
    "last_import = pd.to_datetime('2023-02-01 23:59:59') # Step 7: UPDATE HERE\n",
    "\n",
    "df = df.loc[(df[\"Visit date\"] > last_import)]\n",
    "\n",
    "\n",
    "# Set new index\n",
    "df = df.set_index(np.arange(0,len(df)))\n",
    "\n",
    "\n",
    "# Delete all HOLOPLOT staff\n",
    "y = 0\n",
    "del_list = []\n",
    "for i in df[\"Email\"]:\n",
    "    string = str(df[\"Email\"][y]).split(\"@\")\n",
    "    if \"holoplot.com\" in string:\n",
    "        del_list.append(y)\n",
    "    y += 1\n",
    "del_list = sorted(del_list,reverse=True)\n",
    "df = df.drop(index = del_list)\n",
    "\n",
    "\n",
    "# Set new index\n",
    "df = df.set_index(np.arange(0,len(df)))\n",
    "\n",
    "\n",
    "# Staffing of the day\n",
    "df['Scanner name'] = df['Scanner name'].replace(\"François Villaret\", \"Francois Villaret\")\n",
    "staff_demo = (\"Natalia Szczepanczyk\", \"ANGAD\", \"Giulia Nicosia\", \"Ewa Cabaj\") # Step 6: UPDATE HERE\n",
    "sales_staff = (\"Ryan Penny\", \"Francois Villaret\", \"Alessandro Rinaldi\", \"Ian Holden\", \"Reese Kirsh\", \"Natalia Szczepanczyk\", \"François Villaret\")\n",
    "\n",
    "\n",
    "# Create df2 for Deals\n",
    "df2 = df[[ \"Email\", \"Scanner name\", \"[Project Survey] Project Name\",\"[Project Survey] Product Application - Speech\", \n",
    "          \"[Project Survey] Product Application - Live\", \"[Project Survey] Product Application - Experience\",\n",
    "          \"[Project Survey] Project Timeline - 6 months\", \"[Project Survey] Project Timeline - 6-12 months\", \n",
    "          \"[Project Survey] Project Timeline - 12-24 months\", \"[Project Survey] Project Timeline - 24+ months\", \n",
    "          \"[Project Survey] Project Size - S\", \"[Project Survey] Project Size - M\", \"[Project Survey] Project Size - L\", \n",
    "          \"[Project Survey] Project Size - XL\", \"[Project Survey] Project Budget - Confirmed / secured by client\", \n",
    "          \"[Project Survey] Project Budget - Not confirmed / secured by client\", \"[Project Survey] Project Budget - Unsure\", \n",
    "          \"[Project Survey] Additional Note\"]]\n",
    "\n",
    "\n",
    "# ---------------------------------------------------CONTACTS-----------------------------------------------------------------\n",
    "\n",
    "## Demo Tag\n",
    "def demo(scanner_name):\n",
    "    if scanner_name in staff_demo:\n",
    "        return \"Demo\"\n",
    "    else:\n",
    "        return \"Booth\"\n",
    "    \n",
    "## Contact Owner\n",
    "def contact_owner(scanner_name):\n",
    "    if scanner_name in sales_staff:\n",
    "        return scanner_name\n",
    "    else:\n",
    "        return \"\"\n",
    "    \n",
    "## Demo date\n",
    "def demo_date(visit_day,demo):\n",
    "    if demo == \"Demo\":\n",
    "        return visit_day\n",
    "    else:\n",
    "        return \"\"\n",
    "    \n",
    "## Lifecycle Stage\n",
    "#sql_tag = (\"X1 Opportunity\", \"X2 Opportuinty\", \"Distribution\")\n",
    "x1_buy_comp_act = (\"Entertainment (includes cinemas, theatres, museums, theme parks)\", \n",
    "                   \"Sports/ Venues (includes arenas, convention centres)\" )\n",
    "x2_buy_comp_act = (\"Education\", \"Retail\", \"Transportation (land, sea, air)\", \n",
    "                   \"Hospitality (hotels, restaurants, bars, casinos, cruise ships)\", \n",
    "                   \"Government/ Military\", \"Religious Organisation\")\n",
    "rec_comp_act = (\"AV Integration/ Installation\", \"Distribution/Reselling\",\n",
    "                \"Rental/ Staging/ Live Events/ Meeting Planning\")\n",
    "decision_maker = (\"CEO Chief Executive Officer\", \"CFO Chief Financial Officer\", \"CMO Chief Marketing Officer\",\n",
    "                  \"COO Chief Operating Officer\", \"CTO/CIO\", \"Director\", \"President\", \"Specialist\", \"Vice President\")\n",
    "\n",
    "def lifecycle_qual(survey, tag, rating, comp_act, seniority, job_function, area_resp):\n",
    "    # sql\n",
    "    if pd.notnull(survey) == True:\n",
    "        return \"Qualified Revenue Bringer\"\n",
    "    elif tag in (\"X1 Opportunity\", \"X2 Opportunity\", \"X1 Opportunity, X2 Opportunity\", \"X1 Opportunity*\", \"X2 Opportunity*\"):\n",
    "        return \"Qualified Revenue Bringer\"\n",
    "    elif rating == \"HOT\":\n",
    "        return \"Qualified Revenue Bringer\"\n",
    "    elif tag in (\"Distribution\", \"integrator\", \"*Distribution\", \"*integrator\", \"Distribution*\", \"integrator*\", \"Distribution, integrator\"):\n",
    "        return \"Qualified Distributor\"\n",
    "    # mql\n",
    "    elif comp_act in x1_buy_comp_act and seniority in decision_maker:\n",
    "        return \"Potential X1 End-Customer\"\n",
    "    elif comp_act in x2_buy_comp_act and seniority in decision_maker:\n",
    "        return \"Potential X2 End-Customer\"\n",
    "    elif comp_act in rec_comp_act and seniority in decision_maker and (job_function in (\"Audio (mixing, engineering)\", \"System design\") or area_resp == \"AV\"):\n",
    "        return \"Potential Distributor / Integrator / Consultant\"\n",
    "    # lead\n",
    "    else:\n",
    "        return \"\"\n",
    "\n",
    "def lifecycle_stage (qual):\n",
    "    if qual in (\"Qualified Revenue Bringer\", \"Qualified Distributor\"):\n",
    "        return \"SQL\"\n",
    "    elif qual in (\"Potential X1 End-Customer\", \"Potential X2 End-Customer\", \"Potential Distributor / Integrator / Consultant\"):\n",
    "        return \"MQL\"\n",
    "    else:\n",
    "        return \"Lead\"\n",
    "\n",
    "    \n",
    "# Add columns\n",
    "df[\"ISE 2024 Demo vs Booth\"] = [demo(scanner_name) for scanner_name in df[\"Scanner name\"]]\n",
    "\n",
    "df[\"Contact Owner\"] = [contact_owner(scanner_name) for scanner_name in df[\"Scanner name\"]]\n",
    "\n",
    "df[\"ISE 2024 Demo Date\"] = df.apply(lambda x: demo_date(x[\"ISE 2024 Visit Day\"], x[\"ISE 2024 Demo vs Booth\"]), axis=1)\n",
    "\n",
    "df[\"Trade Show Name\"] = \"2024 ISE\"\n",
    "\n",
    "df['Sales Stream'] = \"Inbound\"\n",
    "\n",
    "df['Sales Channels'] = \"Tradeshows\"\n",
    "\n",
    "df['Sales Channels Drill-down'] = \"2024 ISE\"\n",
    "\n",
    "df['ISE 2024 Lifecycle Qualification Persona'] = df.apply(lambda x: lifecycle_qual(x[\"[Project Survey] Project Name\"], \n",
    "                                                                                   x[\"Tags\"], x[\"Rating\"], \n",
    "                                                                                   x[\"Company activity\"], x[\"Job title\"], \n",
    "                                                                                   x[\"Job function\"], x[\"Area of responsibility\"]), axis=1)\n",
    "\n",
    "df['ISE 2024 Lifecycle Stage'] = [lifecycle_stage(qual) for qual in df['ISE 2024 Lifecycle Qualification Persona']]\n",
    "\n",
    "\n",
    "# Set new index\n",
    "df = df.set_index(np.arange(0,len(df)))\n",
    "\n",
    "\n",
    "# Repair Comments-Column\n",
    "z = 0\n",
    "for i in df[\"Comments\"]:\n",
    "    if isinstance(df[\"Comments\"][z], float) == False:\n",
    "        df[\"Comments\"][z] = str(df[\"Comments\"][z]).strip('\\n')\n",
    "    z += 1\n",
    "    \n",
    "\n",
    "# Set new index\n",
    "df = df.set_index(np.arange(0,len(df)))\n",
    "        \n",
    "\n",
    "# Rename columns\n",
    "df = df.rename(columns = {\"Scanner name\":\"ISE 2024 Scanner Name\",\"Job title\": \"ISE 2024 Seniority\", \"Company\":\"Company Name\",\n",
    "                          \"Company activity\":\"ISE 2024 Company activity\", \n",
    "                          \"Area of responsibility\":\"ISE 2024 Area of responsibility\", \"Job function\": \"ISE 2024 Job function\",\n",
    "                          \"Comments\":\"ISE 2024 Note\", \"Rating\":\"ISE 2024 Rating\", \"GDPR Check 1 - Yes\":\"ISE 2024 MKT Opt-in\", \n",
    "                          \"Tags\": \"ISE 2024 Tags\", \"Country\": \"Country/Region\"})\n",
    "\n",
    "    \n",
    "# Delete unnecessary columns\n",
    "df_col_del = (\"Id\",\"Scanner device identifier\",\"Visit date\",\"Meeting name\",\"Scan type\",\"Pass Type\",\n",
    "              \"Title\",\"Company size\",\"Postal code\",\"Address\",\"Admin private comments\",\"Images\",\n",
    "              \"GDPR check 2 - No\", \"[Project Survey] Project Name\",\"[Project Survey] Product Application - Speech\",\n",
    "              \"[Project Survey] Product Application - Live\", \"[Project Survey] Product Application - Experience\", \n",
    "              \"[Project Survey] Project Timeline - 6 months\", \"[Project Survey] Project Timeline - 6-12 months\",\n",
    "              \"[Project Survey] Project Timeline - 12-24 months\", \"[Project Survey] Project Timeline - 24+ months\",\n",
    "              \"[Project Survey] Project Size - S\", \"[Project Survey] Project Size - M\", \"[Project Survey] Project Size - L\",\n",
    "              \"[Project Survey] Project Size - XL\", \"[Project Survey] Project Budget - Confirmed / secured by client\",\n",
    "              \"[Project Survey] Project Budget - Not confirmed / secured by client\", \"[Project Survey] Project Budget - Unsure\",\n",
    "              \"[Project Survey] Additional Note\", \"ISE 2024 Demo vs Booth\")\n",
    "for item in df_col_del:\n",
    "    df.pop(item)\n",
    "    \n",
    "    \n",
    "# -------------------------------------------------------DEALS----------------------------------------------------------------    \n",
    "\n",
    "# Drop rows with no Opportunities\n",
    "df2 = df2.dropna(subset=['[Project Survey] Project Name'])\n",
    "\n",
    "\n",
    "# Set new index\n",
    "df2 = df2.set_index(np.arange(0,len(df2)))\n",
    "\n",
    "\n",
    "# Prepare functions\n",
    "\n",
    "## Product Application\n",
    "def prod_app (speech, live, experience):\n",
    "    if speech == \"X\":\n",
    "        return \"Speech\"\n",
    "    elif live == \"X\":\n",
    "        return \"Live\"\n",
    "    elif experience == \"X\":\n",
    "        return \"Experience\"\n",
    "    else:\n",
    "        return \"\"\n",
    "\n",
    "\n",
    "## Project Timeline    \n",
    "def timeline (six_m, twelve_m, twentyfour_m, twentyfour_plus):\n",
    "    if six_m == \"X\":\n",
    "        return date(year=2024, month=7, day=31)\n",
    "    elif twelve_m == \"X\":\n",
    "        return date(year=2024, month=12, day=31)\n",
    "    elif twentyfour_m == \"X\":\n",
    "        return date(year=2025, month=12, day=31)\n",
    "    elif twentyfour_plus == \"X\":\n",
    "        return date(year=2026, month=12, day=31)\n",
    "    else:\n",
    "        return \"\"\n",
    "    \n",
    "\n",
    "\n",
    "## Project Size    \n",
    "def size (small, medium, large, xlarge):\n",
    "    if small == \"X\":\n",
    "        return \"S\"\n",
    "    elif medium == \"X\":\n",
    "        return \"M\"\n",
    "    elif large == \"X\":\n",
    "        return \"L\"\n",
    "    elif xlarge == \"X\":\n",
    "        return \"XL\"\n",
    "    else:\n",
    "        return \"\"\n",
    "\n",
    "\n",
    "\n",
    "## Project Budget\n",
    "def budget (secured, not_secured, unsure):\n",
    "    if secured == \"X\":\n",
    "        return \"Confirmed / secured by client\"\n",
    "    elif not_secured == \"X\":\n",
    "        return \"Not confirmed / secured by clien\"\n",
    "    elif unsure == \"X\":\n",
    "        return \"Unsure\"\n",
    "    else:\n",
    "        return \"\"\n",
    "\n",
    "\n",
    "## Contact Owner    \n",
    "def contact_owner (scanner_name):\n",
    "    if scanner_name in sales_staff:\n",
    "        return scanner_name\n",
    "    else:\n",
    "        return \"Alessandro Rinaldi\"\n",
    "\n",
    "    \n",
    "# Create Columns\n",
    "df2[\"Product Application\"] = df2.apply(lambda x: prod_app(x[\"[Project Survey] Product Application - Speech\"],\n",
    "                                                          x[\"[Project Survey] Product Application - Live\"], \n",
    "                                                           x[\"[Project Survey] Product Application - Experience\"]), axis=1)\n",
    "\n",
    "df2[\"Close Date\"] = df2.apply(lambda x: timeline(x[\"[Project Survey] Project Timeline - 6 months\"],\n",
    "                                                          x[\"[Project Survey] Project Timeline - 6-12 months\"], \n",
    "                                                           x[\"[Project Survey] Project Timeline - 12-24 months\"],\n",
    "                                                            x[\"[Project Survey] Project Timeline - 24+ months\"]), axis=1)\n",
    "\n",
    "df2[\"Estimated Project Size\"] = df2.apply(lambda x: size(x[\"[Project Survey] Project Size - S\"],\n",
    "                                                          x[\"[Project Survey] Project Size - M\"], \n",
    "                                                           x[\"[Project Survey] Project Size - L\"],\n",
    "                                                            x[\"[Project Survey] Project Size - XL\"]), axis=1)\n",
    "\n",
    "df2[\"Project Budget Confirmation\"] = df2.apply(lambda x: budget(x[\"[Project Survey] Project Budget - Confirmed / secured by client\"],\n",
    "                                                          x[\"[Project Survey] Project Budget - Not confirmed / secured by client\"], \n",
    "                                                           x[\"[Project Survey] Project Budget - Unsure\"]), axis=1)\n",
    "\n",
    "df2[\"Deal Owner\"] = [contact_owner(scanner_name) for scanner_name in df2[\"Scanner name\"]]\n",
    "\n",
    "df2['[Project Survey] Additional Note'] = df2.apply(lambda row: f\"{row['[Project Survey] Additional Note']} (scanned on ISE 24 by {row['Scanner name']})\", axis=1)      \n",
    "\n",
    "df2['Pipeline'] = \"Sales Pipeline\"\n",
    "\n",
    "df2['Deal stage'] = \"Opportunity\"\n",
    "\n",
    "df2['Sales Stream'] = \"Inbound\"\n",
    "\n",
    "df2['Sales Channel'] = \"Tradeshows\"\n",
    "\n",
    "df2['Sales Channel Trade Show Drilldown'] = \"2024 ISE\"\n",
    "\n",
    "\n",
    "# Rename Columns\n",
    "df2 = df2.rename(columns = {\"Email\": \"Associated Contact\", \"[Project Survey] Project Name\":\"Deal Name\",\n",
    "                            \"[Project Survey] Additional Note\": \"Deal Description\"})\n",
    "\n",
    "\n",
    "# Delete unnecessary columns\n",
    "df2_col_del = (\"[Project Survey] Product Application - Speech\", \n",
    "          \"[Project Survey] Product Application - Live\", \"[Project Survey] Product Application - Experience\",\n",
    "          \"[Project Survey] Project Timeline - 6 months\", \"[Project Survey] Project Timeline - 6-12 months\", \n",
    "          \"[Project Survey] Project Timeline - 12-24 months\", \"[Project Survey] Project Timeline - 24+ months\", \n",
    "          \"[Project Survey] Project Size - S\", \"[Project Survey] Project Size - M\", \"[Project Survey] Project Size - L\", \n",
    "          \"[Project Survey] Project Size - XL\", \"[Project Survey] Project Budget - Confirmed / secured by client\", \n",
    "          \"[Project Survey] Project Budget - Not confirmed / secured by client\", \"[Project Survey] Project Budget - Unsure\",\n",
    "            \"Scanner name\" )\n",
    "\n",
    "for item in df2_col_del:\n",
    "    df2.pop(item)\n",
    "    \n",
    "\n",
    "#-------------------------------------------------------SAVING CSV-----------------------------------------------------------    \n",
    "\n",
    "\n",
    "#Export as .csv\n",
    "df2.to_csv('2024_ISE_IMPORT_Deals_Day4.csv') # Step 5: UPDATE HERE\n",
    "df.to_csv('2024_ISE_IMPORT_Contacts_Day4.csv') # Step 5: UPDATE HERE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "b20a9020",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9a168b90",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.10.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}