CRM_analysis.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "import json\n",
    "import ast\n",
    "headers = {\n",
    "  'Content-Type': 'application/json',\n",
    "  'Authorization': 'Basic U0VBX############################=='#got the apikey using postman--->convert to python method\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Get activitype,campaign name and asset id to filter the required results, create the sync on bulk and return json file\n",
    "def api(actype,campname,aid): \n",
    "  url=\"https://secure.p03.eloqua.com/api/bulk/2.0/activities/exports\"\n",
    "  payload={\n",
    "  \"filter\": \"'{{Activity.Type}}'= '\"+str(actype)+\"' AND '{{Activity.Campaign.Field(CampaignName)}}'= '\"+str(campname)+\"' AND '{{Activity.Asset.Id}}'='\"+str(aid)+\"'\",\n",
    "  \"name\": \"api export\",\n",
    "  \"fields\": {#defining the required fields. Please refer the official documentation for additional fields\n",
    "    \"ActivityType\": \"{{Activity.Type}}\",\n",
    "    \"CampaignName\": \"{{Activity.Campaign.Field(CampaignName)}}\",\n",
    "    \"ContactId\": \"{{Activity.Contact.Id}}\",\n",
    "    \"AssetId\": \"{{Activity.Asset.Id}}\",\n",
    "    \"EmailAddress\": \"{{Activity.Field(EmailAddress)}}\"\n",
    "    }\n",
    "  }\n",
    "\n",
    "  response = requests.request(\"POST\" , url, headers=headers, data=json.dumps(payload))\n",
    "  dict1= ast.literal_eval(response.text)\n",
    "  sync1=dict1[\"uri\"] #we get sync number in the response and will be required in post req\n",
    "  url = \"https://secure.p03.eloqua.com/API/bulk/2.0/syncs\"\n",
    "  payload = json.dumps({\n",
    "  \"syncedInstanceUri\": sync1\n",
    "  })\n",
    "  response = requests.request(\"POST\", url, headers=headers, data=payload)\n",
    "  dict2= ast.literal_eval(response.text)\n",
    "  sync2=dict2[\"uri\"] #This is the final sync url which will be used to fetched the results\n",
    "  while ('\"status\":\"success\"'not in response.text): #waiting for the sync to complete then load the next step\n",
    "    url = f\"https://secure.p03.eloqua.com/API/bulk/2.0{sync2}\"\n",
    "    payload = {}\n",
    "    response = requests.request(\"GET\", url, headers=headers, data=payload)\n",
    "  url = f\"https://secure.p03.eloqua.com/API/bulk/2.0{sync2}/data?limit=50000\" #we can use offsets in case you want additional items\n",
    "  payload = {}\n",
    "  response = requests.request(\"GET\", url, headers=headers, data=payload)\n",
    "  #return(response.json()['totalResults'])\n",
    "  return(response.json())#returning the entire json file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Getting all email groups belonging to the campaign\n",
    "def assets(cname):\n",
    "    url=f\"https://secure.p03.eloqua.com/api/rest/2.0/assets/campaigns?depth=complete&search=name='{cname}'\"\n",
    "    payload={}\n",
    "    response = requests.request(\"GET\" , url, headers=headers, data=payload)\n",
    "    data=response.json()['elements'][0]['elements']\n",
    "    assets=[]\n",
    "    for d in data:\n",
    "        if d['type']=='CampaignEmail':\n",
    "            if d['emailId'] not in assets:\n",
    "                assets.append(d['emailId'])\n",
    "    return(assets)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Fetching the asset names from the provided asset id\n",
    "def name(id):\n",
    "    url=f\"https://secure.p03.eloqua.com/api/REST/2.0/assets/email/{id}\"\n",
    "    payload={}\n",
    "    response = requests.request(\"GET\" , url, headers=headers, data=payload)\n",
    "    return response.json()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "acttype=['EmailSend','EmailOpen','EmailClickthrough','Bounceback'] #define activity fields you want\n",
    "datajson=[] \n",
    "camp='<your campaign name>' #create an array in case of multiple campaigns and iterate entire logic below\n",
    "\n",
    "for i in assets(camp):\n",
    "    Send=api(acttype[0],camp,i)\n",
    "    Open= api(acttype[1],camp,i)\n",
    "    Click=api(acttype[2],camp,i)\n",
    "    Bounce=api(acttype[3],camp,i)\n",
    "    datajson.append([i,Send['totalResults'],Open['totalResults'],Click['totalResults'],Bounce['totalResults']])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "#creating the data in format we require to print in the output file\n",
    "data=[]\n",
    "for i in datajson:\n",
    "    a=name(i[0])\n",
    "    data.append([camp,a['name'],a['subject'],i[1],i[2],i[3],i[4]])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Feed the data into csv files\n",
    "import csv\n",
    "filename = 'output_file.csv'\n",
    "with open(filename, 'w', newline='') as file:\n",
    "    writer = csv.writer(file)\n",
    "    writer.writerow(['Campaign', 'EmailSegment', 'SubjectLine','Total EmailSends','Total EmailOpens','Total EmailClickthrough','Total Bounces'])\n",
    "    writer.writerows(data)"
   ]
  }
 ],
 "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.11.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}