Job 1
Analyze the campaign have Multiplication Factor is less than 4 & Revenue/Spent Ratio (ROI) is less than 200. If the ROI is less than 118% then it is breakeven and should be inactive instantly.
Send this list of campaign to Smita & pause it after her approval.
Also send the list of paused campaigns to Vignesh.
Do it twice a week (Wednesday & Friday).
As per priyanka: Check the Marketing Report twice a week and pause all the campaigns where the multiplication is less than 4 and ROI is less than 200 ( Cross-check with Smita before pausing). Send mail to Vignesh, if you are pausing any campaign because of less multiplication ( Do check with Smita).
Optimizing the SEM campaigns as and when the CPN( Cost per need) is high.
Send this list of campaign to Smita & pause it after her approval.
Also send the list of paused campaigns to Vignesh.
Do it twice a week (Wednesday & Friday).
As per priyanka: Check the Marketing Report twice a week and pause all the campaigns where the multiplication is less than 4 and ROI is less than 200 ( Cross-check with Smita before pausing). Send mail to Vignesh, if you are pausing any campaign because of less multiplication ( Do check with Smita).
Optimizing the SEM campaigns as and when the CPN( Cost per need) is high.
Job 2
Update the status of campaign in P Column as Active/Inactive every week on Monday with the lastest Marketing Report Leads Report before 12 PM.
As per priyanka: Every Monday morning - Need to update the status of the campaigns as Active and Inactive in the latest Marketing Report Leads Report shared by Sunil or Krupa.
As per priyanka: Every Monday morning - Need to update the status of the campaigns as Active and Inactive in the latest Marketing Report Leads Report shared by Sunil or Krupa.
Job 3
Create new campaign as per Pending files shared by Vignesh if Active Clients are more than 4. Send this list of campaign to Smita & wait for her approval.
As per priyanka: Vignesh will be sending Pending Files weekly, you can check with Smita if you need to activate any campaign/s from the shared file.
As per priyanka: Vignesh will be sending Pending Files weekly, you can check with Smita if you need to activate any campaign/s from the shared file.
Job 4
Keep a close eye on CPLs and ROI and every monday send Vineet MTD spends.
Location we target in SEM campaign
- Bangalore
- Mumbai
- Delhi
- Chennai
- Hyderabad
- Thane
- Navi Mumbai
- Ghaziabad
- Faridabad
- Gurgaon
- Noida
- Pune
- Kolkata
Exception
- Goa - Extra location we target for Service Type "Travel Agents and Planners -> Car Rentals"
- Waterproofing - Only run for two cities - Bangalore & Hyderabad
Services we target in SEM campaign
- Appliance Repair -> Air Conditioner
- Packers & Movers
- Pest Control
- Home Help -> Home Nurse
- Software Development
- Lawyers & Legal Consultants
- Travel Agents and Planners -> Car Rentals
- Home Cleaning -> Full Home Deep Cleaning
- Architects & Civil Contractors
- Home Repair -> Waterproofing
- Tiffin & Catering Services -> Catering Services
Exception (we run it in facebook as CPN is high)
- Interior Designing & Home Improvements -> Painting
- Interior Designing & Home Improvements -> Interior Design
Analyze Pending Leads File
Open "Raw File" tab and replace below cities in "City-New" column
- MMR with Mumbai
- Delhi NCR with Delhi
Open "Raw File" tab and sort Column H i.e "SubCategory" & Column G i.e "SubCategory" by A->Z
Replace Values/Changes to be done in Column J i.e "Service Type-New"
- Air Conditioner - New Value
- Appliances All Services
- Architects & Civil Contractors - New Value
- Civil Contractor
- Full Home Deep Cleaning - New Value
- Home Cleaning All services
- Home Nurse - New Value
- Home Help All Services
- Lawyers & Legal Consultants - New Value
- Criminal Law
- Lawyers all services
- Divorce Lawyer
- Property/RERA Lawyer
- Software Development - New Value
- Mobile App Developer
- Standalone Software
- Website Developer
- Software all services
Refresh the Pivot Table.
Apply designs to Pivot table like this
- Design -> Subtotals -> Do Not Show Subtotals
- Design -> Grand Totals -> Off for Rows & Column
- Design -> Report Layout -> Show in Tabular Form
- Design -> Report Layout -> Repeat all Item Labels
Copy these column in new Tab
- SubCategory
- Service Type-New
- City-New
- Sum of Pending Leads
- Count of PackOrderID
- Sum of Pending Value
Add a new column between City-New & Sum of Pending Leads.
Now CONCATENATECONCATENATE(SubCategory,Service Type-New,City-New) in the new added column
Create Next Month Planning Sheet
Open "MTD Report" Tab & keep below column & delete everything
- Sub Cat
- City
- ServiceType
- Needs Captured
- Money Spent With Out Tax / Last Month Spends (Need that latest)
- Cost Per need
- Multiplication Factor
- Monetized Leads
- SEM Conversions
- Revenue
- Revenue/Spent Ratio
- Active/Inactive
Open "MTD Report" Tab & add below Columns between "ServiceType" & "Needs Captured"
- Pending Leads
- Active clients
- Revenue with GST
- Revenue without GST
- Daily Budget
- Needs required
- Spends required
Replace Values/Changes to be done in "MTD Report" Tab of Column A i.e " SubCat"
- Packers&Movers -> Packers & Movers (New Value)
- Catering Services -> Tiffin & Catering Services (New Value)
- Home Nurse -> Home Help (New Value)
- Car Rentals -> Travel Agents and Planners (New Value)
- Waterproofing -> Home Repair (New Value)
Add a new column between ServiceType & Leads. Now CONCATENATE(SubCategory,Service Type,City) in the new added column.
Do Vlookup based on Concat column and take values from Pending Leads File
- Pending Leads <-> Sum of Pending Lead (Pending Leads File)
- Active Clients <-> Count of PackOrderID (Pending Leads File)
- Revenue with GST <-> Sum of Pending Value (Pending Leads File)
Calculate below column values as follows:
- Revenue without GST: Apply Formula = Revenue with GST * 82%
- Daily Budget: Values to be taken from SEM Campaign
- Needs required: To be calulated as if "Active clients" is less than 6 then Apply Formula = Pending Leads / Active clients or if "Active clients" is more than or equal to 7 then Apply Formula = Pending Leads / 7
- Spends required: Apply Formula = Needs required * Cost Per Need
Now hide Column D or Concat column
Final Projection
Add 2 new columns:
- Spend (Y/N) - Where we want to spend in the planning month
- Last Month Spends (Use same column & its value i.e "Money Spent With Out Tax")
Apply filters in Pivot table like this.
Action to be taken after preparing this Next Month Planning Sheet (on every Monday/Tuesday)
- Ideal Active clients should be more than 7 & If Active clients is less then or equal to 5 for Active Campaign then pause it.
- Ideal Revenue/Spent Ratio should be more than 300% & if Revenue/Spent Ratio is less than 250% then pause it.
- Ideal Multiplication Factor should be more then or equal to 7.
- Pause the campaign if the Revenue without GST/Pending value of leads left is less then Rs. 40K then no point spending money, even if ROI is high.
- Ideal CPN should be less than Rs. 500.
- If Revenue without GST in Next Month Planning Sheet is more than 1 lakh then campaign need to be activated.
- Maintain your remarks in Next Month Planning Sheet when you have paused the campaign or send the email to Vignesh & Rahul Sanik with reason.
- Campaign like painting, AC repair etc. should be paused in off season.
- Compare historical data ( last 3-4months CPN to see if CPNs have shot up..) even though they maybe profitable & then take a call basis that.
- Compare historical data for all the active campaigns, do an excel with their last 6 months CPN comparison and also their currrent Revenue/Spent Ratio% & then take a call basis that.
