/
Campaign Manager Phase 2 - Technical Documentation

Campaign Manager Phase 2 - Technical Documentation

Owned by Pranay Mangulkar
Last updated: about 8 hours ago by Sabir Mohammedi
3 min read

Approach

The campaign analytics will be based on accountID and the corresponding day's search. The approach involves bringing data for analytics from the UploadedFileLog table in SQL and CDD, FDD, SDD, EDD, RCSDD and WDD collections in MongoDB. The downloaded file will be in PDF format. The data related to SQL will be fetched using kairos-reports, and the data related to MongoDB will be fetched using Python scripts.

  • Total records: Count will be from the UploadedFileLog table, column uploadedRecords.

  • Valid records: Count will be from the UploadedFileLog table, column validRecords.

  • Invalid records: Count will be from the UploadedFileLog table, column invalidRecords.

  • Total campaign uploads: A pie chart will compare the valid and invalid record counts from the database.

  • Negative records: Count will be from the NegativeBaseFileLogMaster table, column fileCountRecords.

  • Processed records: The number of FDDs created for the campaign.

  • Pending records: The number of FDDs with statusname as 'IN_PROGRESS'.

Hourly Campaign Performance Rate

  • A bar graph will present campaign performance based on the final status of detailed documents received on an hourly basis for SMS, RCS, Call, WhatsApp, and Email documents.

Common Details

  • Calls triggered: The number of CDDs created for the campaign.

  • SMS sent: The number of SDDs created for the campaign.

  • Emails sent: The number of EDDs created for the campaign.

  • WhatsApp sent: The number of WDDs created for the campaign.

  • RCS sent: The number of RCSDDs created for the campaign.

  • Retriggered count: The count from the retriggerCount column of the CampaignRetrigger table.

Call Metrics

  • Calls connected: The count of the connected flag as true from CDD.

  • Calls failed: The count of the connected flag as false from CDD.

  • Call performance rate: A pie chart will compare the number of calls connected to calls failed.

SMS Metrics

  • SMS delivered: The total count of Sms_Detail.delivery_status as ['DELIVERED'] in SDD for the campaign.

  • SMS failed: The total count of Sms_Detail.delivery_status as ['FAILED', 'EXPIRED', 'REJECTED', 'INVALID_NUMBER', 'OTHER', 'LANDLINE_NUMBER'] in SDD for the campaign.

  • SMS delivery rate: A pie chart will compare the number of SMS delivered to SMS failed.

Email Metrics

  • Emails delivered: The total count of Email_details.addresses.delivery_status as ['DELIVERED', 'OPENED', 'CLICKED', 'COMPLAINT'] in EDD for the campaign.

  • Emails failed: The total count of Email_details.addresses.delivery_status as ['FAILURE', 'EMPTY_EMAIL_LIST', 'INVALID_DATA', 'INVALID_FROM_EMAIL', 'INVALID_TO_EMAIL', 'BOUNCE', 'REJECTED'] in EDD for the campaign.

  • Email delivery rate: A pie chart will compare the number of emails delivered to emails failed.

WhatsApp Metrics

  • WhatsApp delivered: The total count of status as ['Sent', 'Delivered', 'Seen', 'Read', 'sent', 'delivered'] in WDD for the campaign.

  • WhatsApp failed: The total count of other statuses indicating failure in WDD for the campaign.

  • WhatsApp delivery rate: A pie chart will compare the number of WhatsApp messages delivered to WhatsApp messages failed.

RCS Metrics

  • RCS delivered: The total count of status as ['Sent', 'Delivered', 'Seen', 'Read', 'sent', 'delivered'] in RCSDD for the campaign.

  • RCS failed: The total count of other statuses indicating failure in RCSDD for the campaign.

  • RCS delivery rate: A pie chart will compare the number of RCS messages delivered to RCS messages failed.

 

NOTE :

There will be ~3 queries on SQL and there ~15 query on MongoDB for FDD, CDD, SDD, RCSDD, and WDD. In MongoDB there will be joins between these documents. This will be for Single user login. Currently there are multiple login in a single client account. This is without auto refresh.

Auto-refresh can be set for (1min-60min) range. This can have significant queries per minute if user set the minimum refresh time.

Multiple users with a single client can access the WEB-UI and hence multiple queries will be executed on the SQL and MongoDB if multiple user access campaign manager.

There are 2 client with Campaign manager access. This is not a significant number hence does not require an upgrade on our Databases. But if we increase the number of Client who have campaign manager access, that will lead to access load on SQL and Mongo. Which may require upgrade of the databases.

This document has been developed by Phonon.io for the sole and exclusive use of the customer / prospective customer with whom this document is being shared. Further, this document has been provided by Phonon.io to the recipient in good faith and based on request from the recipient for the same. This document is a confidential document and contains confidential product technology, workflow and commercial details that are for the sole usage of the intended recipients of this document. Recipients are advised not to share this document with any third party that is not the intended recipient of this document and neither to bring this document in full or parts into the public domain. Any unauthorized access may be brought to Phonon.io’s notice immediately. Phonon.io is free to take any legal action it deems necessary against any person or entity that violates this confidentiality agreement. Phonon.io is bound and governed by the rules of the state of Gujarat in India. In case you are not in agreement with the terms set in this clause or are not an intended recipient of this document, please destroy the document and intimate us of the same at info@phonon.io.