Turn unanswered social messages into Zendesk tickets

Every two hours between 8am and 6pm on weekdays, catch the social messages my team has not answered yet, turn the real support problems into Zendesk tickets, and hand everything else to my community manager in one Slack message. Use a cron trigger, because Sprout Social does not send outgoing webhooks.

Start with Sprout Social List Customer Profiles to resolve the social profiles connected to my account, and keep each profile id alongside its network and display name so I can group by profile later. Then use Sprout Social Query Messages to pull inbound messages from the last 48 hours for those profiles, including their reply activity. Query Messages is cursor paginated, so pass paging.next_cursor back as page_cursor and keep going until the response no longer includes a cursor. Cursor paging is forward only, so do not try to walk back. The Sprout Social token is limited to 60 requests per minute across everything, so pace the calls and batch profiles into a single query where the filters allow it rather than querying one profile at a time.

Decide whether a message is unanswered from the reply activity attached to the message itself, not from a separate lookup. Keep only messages that have no outbound reply from us and that have been waiting longer than my response time target. Default that target to four hours unless I say otherwise, and measure the wait from when the message arrived to now.

Read each remaining message and sort it into one of three buckets. Ignore the ones that need no answer at all: obvious spam, giveaway and follow-for-follow bait, automated bot replies, and plain praise or a simple thank you with no question in it. Treat as a support problem anything where the sender is blocked, confused, out of pocket or unhappy with the product: broken features, failed payments or refunds, login and account access trouble, order and delivery problems, and complaints that expect a resolution. Everything else that still deserves a human reply, such as pre sales questions, partnership and press enquiries, feature requests and general community chat, goes to the Slack roundup instead.

For each support problem, create a Zendesk ticket with Zendesk Create Ticket. Put the sender's handle and the network in the subject along with a short summary of the issue, and put the full message text, the sender handle, the social profile it came in on, how long it has been waiting, and a link back to the conversation in the ticket body. Use the permalink on the Sprout message for that link when there is one, and otherwise include the Sprout message id and the profile name so someone can find it by hand.

Never open a second ticket for a message that already has one. Write the Sprout message id onto every ticket you create, both as the ticket's external id and as a tag shaped sprout-msg-<message id>, and before creating anything use Zendesk Search Tickets to look for that external id or tag. If a ticket already exists, skip the message entirely and do not mention it in Slack either. Do this check per message rather than once for the batch, because an unanswered message will keep showing up on every run until somebody replies to it.

Set the ticket priority from how long the message has been waiting and how upset the sender sounds, weighing both together. Use urgent for someone who is clearly angry or is reporting something like lost money, a failed payment or a total outage, and for anything that has been waiting more than a day. Use high for a visibly frustrated sender or a message that has been sitting well past the target, roughly half a day or more. Use normal for an ordinary problem raised calmly and recently. Use low for a minor question that is easy to answer. Lean one level higher when the message is public and visible to other customers rather than a private DM, since those cost us more the longer they sit.

Collect everything in the third bucket into a single Slack message with Slack Send a Message, posted to my community team channel. Group it by social profile with a heading per profile, and under each one list the sender handle, how long they have been waiting, a one line summary of what they want, and a link to the conversation, sorted with the longest waiting first. Open with a one line count of how many messages are waiting and how many tickets you raised this run. Send one message for the whole run, never one per item.

If there is nothing unanswered past the target, or everything found was spam, bot noise or praise, do not post to Slack and do not create any tickets. Stay quiet so the channel only lights up when there is real work to pick up.