def update_pipeline_engagement_score():
with app.app_context():
# Updating SmartList Statistics
logger.info(
"TalentPipeline Engagement Score update process has been started "
"at %s" % datetime.utcnow().date().isoformat())
talent_pipelines = TalentPipeline.query.with_entities(
TalentPipeline.id).all()
talent_pipeline_ids = map(lambda talent_pipeline: talent_pipeline[0],
talent_pipelines)
talent_pipeline_cache = redis_dict(redis_store,
'pipelines_engagement_score')
for talent_pipeline_id in talent_pipeline_ids:
try:
pipeline_cache_key = 'pipelines_engagement_score_%s' % talent_pipeline_id
with Timeout(seconds=120):
talent_pipeline_cache[
pipeline_cache_key] = engagement_score_of_pipeline(
talent_pipeline_id)
logger.info(
"Engagement Score for TalentPipeline %s have been updated successfully"
% talent_pipeline_id)
except TimeoutException:
logger.exception(
"Timelimit exceeded for updating Engagement Score for "
"TalentPipeline %s" % talent_pipeline_id)
def update_smartlist_stats():
with app.app_context():
# Updating SmartList Statistics
logger.info(
"SmartList statistics update process has been started at %s" %
datetime.utcnow().date().isoformat())
smartlists = Smartlist.query.with_entities(Smartlist.id).all()
for smartlist_tuple in smartlists:
try:
get_stats_generic_function(Smartlist.query.get(
smartlist_tuple[0]),
'SmartList',
is_update=True,
offset=0)
logger.info(
"Statistics for Smartlist %s have been updated successfully"
% smartlist_tuple[0])
except Exception as e:
db.session.rollback()
logger.exception(
"Update statistics for SmartList %s is not successful because: "
"%s" % (smartlist_tuple[0], e.message))
smartlist_ids = map(lambda smartlist: smartlist[0], smartlists)
delete_dangling_stats(smartlist_ids, container='smartlist')
def update_talent_pipeline_stats():
with app.app_context():
# Updating TalentPipeline Statistics
logger.info(
"TalentPipeline statistics update process has been started at %s" %
datetime.utcnow().isoformat())
talent_pipelines = TalentPipeline.query.with_entities(
TalentPipeline.id).all()
for talent_pipeline_tuple in talent_pipelines:
try:
get_stats_generic_function(TalentPipeline.query.get(
talent_pipeline_tuple[0]),
'TalentPipeline',
is_update=True,
offset=0)
logger.info(
"Statistics for TalentPipeline %s have been updated successfully"
% talent_pipeline_tuple[0])
except Exception as e:
db.session.rollback()
logger.exception(
"Update statistics for TalentPipeline %s is not successful because: "
"%s" % (talent_pipeline_tuple[0], e.message))
talent_pipeline_ids = map(lambda talent_pipeline: talent_pipeline[0],
talent_pipelines)
delete_dangling_stats(talent_pipeline_ids, container='talent-pipeline')
def top_most_engaged_pipelines_of_candidate(candidate_id, limit):
"""
This endpoint will return top most engaged pipelines and their engagement score.
:param candidate_id: Id of candidate
:param limit: Number of results to be returned
:return: List of dicts containing pipeline's id, name and engagement score
"""
talent_pool_ids_of_candidate = TalentPoolCandidate.query.with_entities(
TalentPoolCandidate.talent_pool_id).filter(
TalentPoolCandidate.candidate_id == candidate_id).all()
talent_pool_ids_of_candidate = [
talent_pool_id_of_candidate.talent_pool_id
for talent_pool_id_of_candidate in talent_pool_ids_of_candidate
]
if not talent_pool_ids_of_candidate:
talent_pool_ids_of_candidate = ['NULL']
sql_query = """
SELECT talent_pipeline.id, talent_pipeline.name, avg(engagement_score_of_all_campaigns.engagement_score) as average_engagement_score_of_pipeline
FROM
(SELECT email_campaign_send.EmailCampaignId,
email_campaign_send_url_conversion.EmailCampaignSendId,
CASE WHEN sum(url_conversion.HitCount) = 0 THEN 0.0 WHEN sum(email_campaign_send_url_conversion.type * url_conversion.HitCount) > 0 THEN 100 ELSE 33.3 END AS engagement_score
FROM email_campaign_send
INNER JOIN email_campaign_send_url_conversion ON email_campaign_send.Id = email_campaign_send_url_conversion.EmailCampaignSendId
INNER JOIN url_conversion ON email_campaign_send_url_conversion.UrlConversionId = url_conversion.Id
WHERE email_campaign_send.candidateId = :candidate_id
GROUP BY email_campaign_send_url_conversion.EmailCampaignSendId) AS engagement_score_of_all_campaigns
NATURAL JOIN email_campaign_smart_list
INNER JOIN smart_list ON smart_list.Id = email_campaign_smart_list.SmartListId
INNER JOIN talent_pipeline ON talent_pipeline.id = smart_list.talentPipelineId
WHERE talent_pipeline.id IS NOT NULL AND talent_pipeline.talent_pool_id IN :talent_pool_ids GROUP BY talent_pipeline.id
ORDER BY average_engagement_score_of_pipeline DESC LIMIT :limit;
"""
try:
engagement_scores = db.session.connection().execute(
text(sql_query),
candidate_id=candidate_id,
limit=limit,
talent_pool_ids=tuple(talent_pool_ids_of_candidate))
return [{
'id':
engagement_score['id'],
'name':
engagement_score['name'],
'engagement_score':
float(str(
engagement_score['average_engagement_score_of_pipeline']))
} for engagement_score in engagement_scores]
except Exception as e:
logger.exception(
"Couldn't compute engagement score for all pipelines of a candidate(%s) "
"because (%s)" % (candidate_id, e.message))
return []
def top_most_engaged_candidates_of_pipeline(talent_pipeline_id, limit):
"""
This endpoint will return candidate_ids of top most engaged candidates of a talent_pipeline
:param talent_pipeline_id: Id of talent_pipeline
:param limit: Number of results returned by this method
:return: List of candidate Ids of top (limit) most engaged candidates
:rtype: list
"""
sql_query = """
SELECT candidates_engagement_score.CandidateId,
avg(candidates_engagement_score.campaign_engagement_score) AS engagement_score
FROM
(SELECT engagement_score_for_each_sent.CandidateId,
engagement_score_for_each_sent.EmailCampaignId,
avg(engagement_score_for_each_sent.engagement_score) AS campaign_engagement_score
FROM
(SELECT email_campaign_send.Id,
email_campaign_send.EmailCampaignId,
email_campaign_send.CandidateId,
CASE WHEN sum(url_conversion.HitCount) = 0 THEN 0.0 WHEN sum(email_campaign_send_url_conversion.type * url_conversion.HitCount) > 0 THEN 100 ELSE 33.3 END AS engagement_score
FROM smart_list
INNER JOIN email_campaign_smart_list ON smart_list.Id = email_campaign_smart_list.SmartListId
INNER JOIN email_campaign_send ON email_campaign_send.EmailCampaignId = email_campaign_smart_list.EmailCampaignId
INNER JOIN email_campaign_send_url_conversion ON email_campaign_send_url_conversion.EmailCampaignSendId = email_campaign_send.Id
INNER JOIN url_conversion ON email_campaign_send_url_conversion.UrlConversionId = url_conversion.Id
WHERE smart_list.talentPipelineId = :talent_pipeline_id
GROUP BY email_campaign_send.Id,
email_campaign_send.EmailCampaignId,
email_campaign_send.CandidateId) AS engagement_score_for_each_sent
GROUP BY engagement_score_for_each_sent.EmailCampaignId) AS candidates_engagement_score
GROUP BY candidates_engagement_score.CandidateId
ORDER BY engagement_score DESC LIMIT :limit;
"""
try:
engagement_scores = db.session.connection().execute(
text(sql_query),
talent_pipeline_id=talent_pipeline_id,
limit=limit)
return [{
'candidate_id':
engagement_score['CandidateId'],
'engagement_score':
float(str(engagement_score['engagement_score']))
} for engagement_score in engagement_scores]
except Exception as e:
logger.exception(
"Couldn't get top most engaged candidates of talent-pipeline(%s) "
"because (%s)" % (talent_pipeline_id, e.message))
return []
def engagement_score_of_pipeline(talent_pipeline_id):
"""
This endpoint will calculate engagement_score of a talent_pipeline
:param talent_pipeline_id: Id of talent_pipeline
:return: engagement_score of talent_pipeline
:rtype: list
"""
sql_query = """
SELECT avg(pipeline_engagement.campaign_engagement_score) AS pipeline_engagement_score
FROM
(SELECT avg(engagement_score_for_each_sent.engagement_score) AS campaign_engagement_score
FROM
(SELECT email_campaign_send.Id,
email_campaign_send.EmailCampaignId,
CASE WHEN sum(url_conversion.HitCount) = 0 THEN 0.0 WHEN sum(email_campaign_send_url_conversion.type * url_conversion.HitCount) > 0 THEN 100 ELSE 33.3 END AS engagement_score
FROM smart_list
INNER JOIN email_campaign_smart_list ON smart_list.Id = email_campaign_smart_list.SmartListId
INNER JOIN email_campaign_send ON email_campaign_send.EmailCampaignId = email_campaign_smart_list.EmailCampaignId
INNER JOIN email_campaign_send_url_conversion ON email_campaign_send_url_conversion.EmailCampaignSendId = email_campaign_send.Id
INNER JOIN url_conversion ON email_campaign_send_url_conversion.UrlConversionId = url_conversion.Id
WHERE smart_list.talentPipelineId = :talent_pipeline_id
GROUP BY email_campaign_send.Id, email_campaign_send.EmailCampaignId) AS engagement_score_for_each_sent
GROUP BY engagement_score_for_each_sent.EmailCampaignId) AS pipeline_engagement
"""
try:
engagement_score = db.session.connection().execute(
text(sql_query), talent_pipeline_id=talent_pipeline_id)
result = engagement_score.fetchone()
if not result or not result['pipeline_engagement_score']:
return None
else:
return float(str(result['pipeline_engagement_score']))
except Exception as e:
logger.exception(
"Couldn't compute engagement score for pipeline(%s) because (%s)" %
(talent_pipeline_id, e.message))
return None