#!/usr/bin/env bash
# Lost reason post-mortem
# Why deals died last quarter, ranked, with the revenue attached to each reason. Price objections and slow follow-up need very different fixes, and this tells you which one you actually have.
#
# Category : Reporting
# Safety   : read only
# Uses     : reports run, lost-reasons list
# Returns  : Lost reasons ranked by count and by the value that walked.
#
# Requires the SendSquared CLI and jq:
#   curl -fsSL https://cli.sendsquared.com/install.sh | bash
#   sendsquared auth login
#
# Free to use and adapt. https://sendsquared.com/cli/scripts/#lost-reason-postmortem

# Why we lost, ranked by deals then value.
set -euo pipefail
FROM=${1:-$(date -u -v-90d +%F 2>/dev/null || date -u -d "-90 days" +%F)}
TO=${2:-$(date -u +%F)}

ROWS=$(sendsquared reports run lead_lost_reason --from "$FROM" --to "$TO")
[ "$(jq '.data | length' <<<"$ROWS")" -eq 0 ] && { echo "no closed-lost leads in $FROM to $TO"; exit 0; }

echo "--- why deals were lost, $FROM to $TO"
jq -r '
  .data[]
  | [ ((.lost_reason // "no reason recorded") | gsub("[\\t\\n]";"") ),
      ((.total_leads // 0) | tonumber),
      (((.value // 0) | tonumber) / 100 | floor) ]
  | @tsv
' <<<"$ROWS" \
| awk -F'\t' '{n[$1]+=$2; v[$1]+=$3} END {for (k in n) printf "%s\t%d\t%d\n", k, n[k], v[k]}' \
| sort -t$'\t' -k2 -rn \
| awk -F'\t' 'BEGIN{printf "  %-38s %7s %12s\n","LOST REASON","DEALS","VALUE"}
    {printf "  %-38s %7s %12s\n",$1,$2,"$"$3}'

echo
echo "--- which sources lose the most"
jq -r '.data[] | [ (.source_label // "unattributed"), ((.total_leads // 0) | tonumber) ] | @tsv' <<<"$ROWS" \
| awk -F'\t' '{n[$1]+=$2} END {for (k in n) printf "  %-38s %s\n", k, n[k]}' | sort -k2 -rn | head -8