Automate or Stagnate: Master Salesforce Task Automation with Python and Google Cloud Platform
Business Context & Introduction
We all know the frustration of managing endless Excel sheets, but manually combing through each row to create tasks in Salesforce adds another layer of tedium.
We deployed a machine learning (ML) model to proactively address churn and retain our restaurant partners (RPs) by predicting the likelihood of partners leaving our e-commerce platform. In October 2022, we launched a pilot project with our Account Management team to validate and refine this predictive model, testing its efficacy.
Since then, the Local Country DE Analytics team has been providing the Account Management team with a Google Sheet listing approximately 50 restaurant partners every month, identified by the ML model as high-risk for churn. Before automation, the Account Management team had to manually create the Churn-Prevention tasks in Salesforce based on this list so that the respective Account owners could contact the high-risk churn partners. This manual process was time-consuming and prone to delays, potentially impacting our ability to provide timely support to these at-risk partners.
We recognized the need for automation to streamline operations, reduce manual effort, and ensure that each at-risk partner receives timely attention and support. By integrating Salesforce task automation with Python and Google Cloud Platform, we took a pivotal step towards optimizing our Churn-Prevention efforts. This automation saves time and allows the team to focus more on strategic initiatives that drive partner satisfaction and long-term business success.
Why Automate Salesforce Tasks
Imagine a world where your team never misses a task or deadline because every task is created and assigned automatically. With a few Python scripts and the power of Google Cloud, this isn’t a dream — it’s your new reality.
- Save Time and Resources: Automating repetitive tasks allows employees to focus on more strategic initiatives rather than manual data entry.
- Ensure Consistency: Automation ensures that tasks are created and assigned in a standardized manner, reducing variability and confusion.
- Improve Accuracy: Eliminate human error associated with manual data input, ensuring tasks are assigned correctly and deadlines are met.
Architectural Overview
- Data Extraction: Python scripts extract relevant data from BigQuery, ensuring all necessary information is available for task creation.
- Data Transformation: The extracted data is processed and transformed into the required format for Salesforce task creation. This step involves cleaning, filtering, and preparing data to match Salesforce’s requirements.
- Task Creation: Interact with Salesforce’s API using Python to create tasks based on the transformed data. OAuth 2.0 authentication secures access to Salesforce.
- Google Cloud Functions: Host Python scripts as serverless functions on Google Cloud, providing scalability and reliability.
- Google Cloud Scheduler: Schedule Python scripts to run at specified intervals (e.g. monthly), ensuring tasks are created promptly.
Step-by-Step Implementation
Extracting & Transforming Bigquery Prediction Data
The first step is to gather the necessary data. The data extraction script connects to BigQuery, runs a query, and processes the results into a DataFrame. This data frame contains essential details like account IDs and owner IDs, which are being used to create Salesforce tasks.
dataExtraction.py
import pandas_gbq
import os
from google.oauth2 import service_account
def extract_from_big_query():
prj_id = 'just-data-coun-dev'
# Set the environment variable for the quota project
os.environ["CLOUDSDK_CORE_QUOTA_PROJECT"] = prj_id
# Load service account credentials from the key file
#credentials = service_account.Credentials.from_service_account_file(service_account_file)
# Query for Germany (DE)
query_de = """
SELECT DISTINCT pr.DWH_rest_id,
pr.sf_account_id,
pr.prediction_month,
pr.churn_prob,
rs.restaurant_segment_latest,
rs.segmentation_trend_latest,
pr.owner_id,
pr.owner_name,
pr.country,
rs.onlinestatusendofmonth,
rs.Is_silver_L6M,
rs.Is_gold_L6M,
lifespan_months,
p.avg_activity_rate_l3m,
ROUND(p.averagefoodqualityrating_L3m,2) AS averagefoodqualityrating_L3m,
p.frac_of_days_online_L3M,
p.Lifetime_orders_per_month AS avg_Lifetime_orders_per_month,
p.avg_orders_l3m,
p.order_growth_l3m,
p.Lifetime_gmv_per_month AS avg_Lifetime_gmv_per_month,
p.avg_gmv_l3m,
p.gmv_growth_l3m
FROM predictions pr
JOIN dim_rest rs ON rs.restaurantid=pr.DWH_rest_id
JOIN fact_metrics p on p.restaurantid=rs.restaurantid
where onlinestatusendofmonth='Online (with Menu)'
AND country IN ('DE','DEL')
and avg_activity_rate_l3m<0.30
AND lifespan_months>6
ORDER BY avg_Lifetime_gmv_per_month DESC, churn_prob DESC
LIMIT 60;
"""
# Read the data from BigQuery into a DataFrame with the specified location
df_de = pandas_gbq.read_gbq(query_de, project_id=prj_id, dialect='standard')
print("Predictions Dataframe created successfully for DE")
#print(df_de.head())
df_final = df_de.loc[:, ['prediction_month','TMS_rest_id', 'sf_account_id','owner_id','owner_name']]
print(df_final.head())
return df_finalCreating Tasks in Salesforce
Next, we use a script to interact with the Salesforce API, creating tasks for each account. The script handles OAuth 2.0 authentication to ensure secure access. For each account, it generates a task with a due date set 10 days in the future, assigning it to the correct owner.
connect_to_salesforce.py
import requests
import json
import pandas as pd
from cryptography.fernet import Fernet
from dataExtraction import extract_from_big_query
# Loading the encryption key
key = b'YOUR ENCRYPTION KEY'
# Create the cipher suite
cipher_suite = Fernet(key)
# Read the encrypted data from the file
with open('encrypted_config_prod.json', 'rb') as f:
encrypted_data = f.read()
# Decrypt the data
config_data = cipher_suite.decrypt(encrypted_data)
# Convert bytes to string
config_string = config_data.decode('utf-8')
# Parse the JSON string
config = json.loads(config_string)
# Access Salesforce credentials
salesforce_config = config.get('salesforce', {})
consumer_key = salesforce_config.get('consumer_key')
consumer_secret = salesforce_config.get('consumer_secret')
token_uri = salesforce_config.get('token_uri')
instance_url = salesforce_config.get('instance_url')
token_type = salesforce_config.get('token_type')
# Function to obtain the access token using OAuth 2.0 authentication
def get_access_token():
try:
# Formulate the payload for token request
payload = {
'grant_type': 'client_credentials',
'client_id': consumer_key,
'client_secret': consumer_secret
}
# Send a POST request to obtain the access token
response = requests.post(token_uri, data=payload)
# Check if the token request was successful
if response.status_code == 200:
return response.json()['access_token']
else:
print("Failed to obtain access token. Status code:", response.status_code)
print("Response:", response.text)
return None
except Exception as e:
print("An error occurred while obtaining access token:", str(e))
return None
# Function to create a "Churn Prevention" Task for a particular Account and assign it to the owner
def create_task(account_id, due_date, owner_id):
try:
access_token = get_access_token()
if not access_token:
return
# Create headers for API requests
headers = {
'Authorization': f'{token_type} {access_token}',
'Content-Type': 'application/json'
}
# Task data to be created
task_data = {
"Subject": "Churn Prevention",
"WhatId": account_id,
# "Description": "08_05_24 Testing Shubham Call Rps to prevent churn V9",
"ActivityDate": due_date.strftime('%Y-%m-%d'),
"OwnerId": owner_id, # Assign the task to its respective owner
# Add other required fields here
}
# Send a POST request to create the task
create_task_endpoint = f"{instance_url}/services/data/v25.0/sobjects/Task"
response = requests.post(create_task_endpoint, headers=headers, json=task_data)
# Check if creating the task was successful
if response.status_code == 201:
print("Task created successfully for Account ID:", account_id)
else:
print("Failed to create task for Account ID:", account_id)
print("Status code:", response.status_code)
print("Response:", response.text)
except Exception as e:
print("An error occurred while creating task for Account ID:", account_id)
print(str(e))
df_final = extract_from_big_query()Orchestrating the Process
The orchestration script brings it all together, running monthly to iterate through the DataFrame and create Salesforce tasks. We ensure scalability and reliability by deploying this script as a Google Cloud Function.
main.py
from datetime import datetime, timedelta
from connect_to_salesforce import create_task, df_final
def monthly_script():
try:
# Print out a message to indicate the script is running
print("Churn Prevention Automated Task Creation Script is Started!!!")
# Iterate over each row in the DataFrame and create tasks respectively
for index, row in df_final.iterrows():
sf_account_id = row['sf_account_id']
owner_id = row['owner_id']
# Get Owner ID based on owner name
#owner_id = get_owner_id(owner_name)
if owner_id:
# Calculate +12 days from the current date for the Due date
due_date = datetime.now() + timedelta(days=10)
# Create a task for the current account and assign it to its respective owner
create_task(sf_account_id, due_date, owner_id)
except Exception as e:
print("An error occurred in the monthly_script:", str(e))
if __name__ == "__main__":
# You can trigger the script manually by calling the function
monthly_script()Deployment and Scheduling over Google Cloud Platform
Deploying the Scripts to Google Cloud Functions
- Package the Scripts: Upload all the scripts to Google Cloud Function by defining a new Function in Google Cloud Function Console.
Setting Up Google Cloud Scheduler
- Create a New Scheduler Job: In the Google Cloud Console, create a new scheduler job.
- Configure the Schedule: Set the frequency to
0 0 1 * *run at the beginning of every month. - Set the Target: Set the target to the deployed Cloud Function to trigger the specific Salesforce Automation Task creation scripts
The Impact: Before and After Automation
This chapter explores how we revolutionized our approach to Churn-Prevention through automation at our company. Before automation, our Account Management team manually assigned tasks listed in Google Sheets to respective Account owners. These tasks included contacting high-risk Churning partners by a specified due date to provide necessary care and support. However, this manual process often led to time-consuming delays that impacted our ability to promptly assist at-risk partners.
By analyzing Salesforce ‘Churn-Prevention’ task data from both before and after implementing automation, we gain valuable insights into the efficiency improvements and operational enhancements we’ve achieved.
Before Automation
- Task Initiation: 18 days on average from sending an email with a Google Sheet listing high-risk partners to task creation in Salesforce.
- Task Completion: 12 days on average from task creation to task completion.
- Total Time: 30 days on average from initial identification to task completion.
After Automation
- Task Initiation: Tasks are created in Salesforce within seconds.
- Task Completion: 10 days on average to complete the tasks.
- Total Time: Significantly reduced to 10 days only from initial identification to task completion.
Summary of Benefits
- Increased Efficiency: Reduced total Operational time from 30 days to 10 days.
- Enhanced Speed: Instantaneous task creation in Salesforce using the Automation script.
- Improved Response Time: Faster engagement with high-risk partners, leading to potentially reduced churn rates.
- Better Resource Allocation: With tasks being handled more quickly, resources can be allocated more efficiently, allowing the team to focus on more strategic activities rather than being bogged down by manual processes.
- Reduced Human Error: Automation minimizes the risk of human error that can occur with manual task management, ensuring that tasks are consistently created and managed accurately.
- Standardized Processes: Automated workflows standardize the process, ensuring that each partner receives the same level of attention and service, which can lead to more predictable and reliable outcomes.
Conclusion
Automating Salesforce task creation using Python and Google Cloud Platform is a game-changer. With the power of Python and Google Cloud resources, we ensure that critical tasks are created and assigned consistently every month. This automation not only saves time but also minimizes the risk of human errors, leading to better management and tracking of tasks in Salesforce.
While the initial setup might require some effort, the long-term benefits far outweigh the costs. By adopting this approach, you’re not just keeping up with the competition — you’re staying ahead.
Are you ready to ditch your tedious manual work and streamline the Salesforce task automation? Start today and transform how you manage tasks within Salesforce, one script at a time.
Just Eat Takeaway.com is hiring! Want to come to work with us? Apply today
