Skip to main content
Jon Travis

slack interaction

Slack is a great tool for collaboration, but it can be hard to keep up with all the conversations. Wouldn't it be great to have someone summarize the channel for you, giving you highlights and answering questions about the details?

In this guide, we will explore building a Slackbot that uses OpenAI's GPT-3 to summarize Slack channels. To quickly build and deploy the bot we'll use Patterns, which provides storage, webhook listeners, serverless execution, Slack & OpenAI integrations, and an execution scheduler.

The purpose of this guide is to give a broad overview of what is required to build the bot.

Source: For detailed source, see: Slack Summarizer on Github

Try it: To use the bot in your own Slack workspace, you can clone it here: Slack Summarizer in Patterns

Slack Summarizer graph

Interaction design

Here's how our bot should work:

  • Mention: A user @mentions the bot, asking it to summarize a #channel
  • Quick Reply: The bot immediately replies, creating a Slack thread to interact with the user
  • Fetch Messages: The bot makes requests to Slack to get the relevant channel history
  • Prompt Creation: The bot constructs a prompt using the channel messages and sends it to OpenAI's GPT-3 model
  • Reply to User: The response from OpenAI is sent back to the user in Slack
  • Summary Interaction: The user may continue to @mention the bot in the thread, asking for more details.

Prompt Design

Prompts are the API of GPT-3, and the community is still exploring the best ways to create them to get the best results. Our considerations for this guide are:

  • Length: the text-davinci-003 model has a limit of 4000 tokens per request. This limits the number of messages we can include in the prompt.
  • Structure: messages are ordered chronologically, and each message may have a list of replies.

Considering length, we will take the simplest approach: only query the last X hours of messages. This is inadequate for a real application, but it serves our purposes here. There are a variety of techniques to summarize large quantities of text, such as using embeddings to search for related information, chunking, etc. There isn't a currently a one-size-fits-all approach, but it's a very active area of research.

Considering structure and length together, we will make a minimal JSON representation of the messages; saving on our token budget, but preserving the structure. Note that our JSON does not have any time related information for a message. In our experience, the bot doesn't need it to be generally useful.

The pretty version of the below JSON is 155 tokens, while the compacted version is 59 tokens. It pays to be compact!

[
{
"u": "Marty McFly",
"t": "Hey Doc, I need your help with something",
"r": [
{
"u": "Doc Brown",
"t": "Sure Marty, what's up?"
},
{
"u": "Marty McFly",
"t": "I need to go back in time to 1955"
}
]
}
]

Channel Summarizing Prompt

We wrap the messages with processing instructions. When summarizing, the prompt looks like this:

Here are a list of messages in JSON format.

{the JSON messages}

Please summarize the messages and respond with bullets in markdown format.

Summary Interactive Prompt

When the user asks for more details about a summary, we supply the same messages but allow the user's query to be included directly in the prompt:

Here are a list of messages in JSON format.

{the JSON messages}

Please respond to this question in markdown format: {the user's question}

Working with the channel history

When a user @mentions the bot, we need to get the channel history. To do this, we simply ask Slack for the recent messages and threads within the channel. This can result in many API calls, each taking some time. To make this faster, we have two options:

  1. Store all Slack messages in a Patterns table and query against it. This would result in the fastest response time, but would require a lot of storage for things that might not ever be queried.
  2. Query Slack on demand but cache the results in a 'session', allowing the bot to reuse the messages for multiple interactions.

For this guide, we adopt the second approach. When a conversation is initiated with the bot, the messages are fetched from Slack and stored in a session. The session is then used for all subsequent interactions.

Session block slice

Replacing user IDs in Slack messages

Messages from the Slack API contain user IDs instead of user names. This allows users to change their names and still show up correctly in message history. The raw messages look like this:

{
'text': 'What do you think, <@U04F2AMBJ14>?',
'ts': '1673985851.921529',
...
}

We need to replace these IDs with the names of the user. Fortunately, we can simply ask Slack for a list of users in the workspace and use this mapping to replace the IDs in each message. This is a simple operation, but we don't want to do it every time the bot needs to make a summary; each API call will slow it down. The list of Slack users in a workspace doesn't change very often, so it's a perfect candidate for caching.

Caching user IDs every day

Patterns has two capabilities which help us quickly solve this problem:

  • Built-in storage, which gives us a place to put the cache
  • A scheduler, which can run our code periodically

We create a Python node called Import Slack Users which gets the list of users from Slack and puts it into a table called users. We schedule it to run once a day at 2am UTC by giving it the cron expression 0 2 * * *.

Now we have a users table which the rest of our bot can use to map user IDs to names.

The code to do this roughly looks like:

user_table = Table("users", mode="w")
user_table.init(unique_on="id")

users_list = slack.users_list()
user_table.upsert(users_list.get("members", []))

Interacting with OpenAI and Slack

The APIs for interacting with Slack and OpenAI are well-documented and easy to use. The final piece of the bot is to issue the query to OpenAI and send the response back to the user in Slack. This implemented in this section of the graph:

OpenAI slice

Evaluation and Conclusion

The bot actually performs quite well; it does a good job with the summary and responds reasonably to follow-up questions. Like all other GPT generative uses, it suffers from hallucination and repetition.

Is the bot useful? My Slack channels are not very active, so it's not difficult to read the original content or search when I'm curious about a topic. It would certainly be more useful in a channel with a lot of activity. It could be useful in other areas, such as summarizing the week's activity and sending a digest email.

If I find myself @messaging the bot instead of simply scrolling back, I'll know that the such a bot is a winning piece of tech.

Calder White

In the past, when I needed any type of product information like the best phone or cloud provider, I would often turn to Google. Many people do the same, but I've noticed that over time, Google has become more focused on turning a profit which litters my search results with paid advertisements that don’t actually answer my question. A friend suggested to instead append “Reddit” to the end of every Google search… this simple change helped me find more relevant and accurate results.

This inspired me to create a custom newsletter for any subreddit to send me Reddit's "thoughts" every day. Here's how I did it and here's a link to clone the public app and run your own newsletter!

Getting data from Reddit

To begin, I attempted to collect data from Reddit. I initially thought it would be easy, as Reddit is a modern technology company, and assumed I could access their data through an API with the proper credentials. However, I found that I needed to create a developer account and provide my username and password. In order to make it easy for others to replicate my application, I decided to use web scraping as a method to collect the data instead.

At first I looked at the "new" reddit site (reddit.com), only to find it is dynamically generated CSS and is thus hard to pin down from a web scraping perspective.

Confusing Classnames

However, in finding this I realized that dynamic site generation is a relatively new phenomenon and that in the earlier days of the internet people used their actual internet class names and ids to style and organize their web pages. So I turned to old.reddit.com, and sure enough, it was easily scrapable with telling class names such as ".title" and ".entry".

Good Classnames

I broke out Python’s BeautifulSoup and got to work writing a web scraping script in conjunction with the requests module. Another slight problem I encountered was that reddit did have some basic bot protection in their requests, but that was easily circumvented by copying valid requests headers & cookies and adding them to my GET request to make it look like it was coming from a real browser.

Determining Reddit's "Opinion" with OpenAI

To generate Reddit’s "opinion" on posts, I decided to take the comments as one contiguous block of text and summarize it with OpenAI’s Tl;dr feature. This way I left the tough decisions about which comments were more important up to OpenAI’s LLM instead of hard coded logic. One way this could be expanded is if I incorporated the votes of comments into how my summaries are generated, for example filtering out comments whose votes are too low.

The way Tl;dr works is that you simply insert any text up to a token limit and then append the string "Tl;dr" to it. Run that through OpenAI’s completion engine and it will output its best summary of the information provided. One limitation of this is that there is an output token limit of 60 tokens, which means that the summaries OpenAI provides will be quite small. Another tricky limitation was the input token limit which is set at 4000 tokens. Not only did many post’s comment threads exceed this limit, but calculating the exact ratio of character limits to token limits is a bit difficult. OpenAI does not actually provide you with resources to determine what is and is not a token to them. However, they do provide approximations on their website.

[source: https://help.openai.com/en/articles/4936856-what-are-tokens-and-how-to-count-them ]

Their approximation is that 1 token is roughly 4 characters. To stay in the safe zone, I approximated that 1 token is 3 characters, and multiplied the 4000 token limit by the 3 character ratio to give myself a character limit for OpenAI’s completion engine. Additionally, I implemented chunking to allow summarization of all of the comments. I simply chunked the input by the character limit, summmarized the chunks individually and then concatonated the summaries into one master summary. There is room for expansion in this strategy however. One way to achieve this may be to summarize smaller chunks of the comments and then rerun those concatonated summaries back into OpenAI for your final summary. This is essentially recursive summarizing, which I fear may cause issues due to residual errors present in OpenAI’s LLM, but if the model is good enough you might be able to get away with one iteration. This provides the added benefit of cohere text flow, instead of disjoint summaries strung together. As mentioned before, another way summarization could be improved is by filtering for the more popular opinions represented in higher vote posts instead of weighting all comments equally.

Extra Features from Patterns :)

Easy to read crons! Patterns allowed me to set my own crong and also told me in plain english what the schedule was. This saved me the hassle of going to crontab.guru every single time I want to set a crontab.

Crons in Patterns

Some nice built in features Patterns allowed me to have was the node form view option. This way I could easily see what my parameters were and quickly change them. For example, the subreddit or the email I was sending my newsletter to.

It was also nice to be able to use the OpenAI completion component as well as the "Send email with Gmail" component so I didn’t have to write all of the code to interface with these APIs. It allowed me to focus on what I was interested in, which was downloading and ingesting the data from reddit.

Patterns Graph

Wrapping up

There were some really interesting results from Reddit's summaries. Although my algorithm for combining chunks from reddit comments was not perfect, the results produced were very insightful.

In wallstreetbets Reddit advised against pump and dumps

Advising Against Pump and Dumps

Some banter about Jamie Dimon stating the obvious:

Jamie Obvious is Great at Stating the Obvious

Some good guidance on recession fears (with a bit of self promotion noise):

Recession Opinions

I was really impressed with the simplicity in summarizing the opinions with OpenAI + using the prebuilt components from patterns to glue these systems together! I think with some more tweaks you could certainly automate a newsletter and gain thousands of subscribers.

The Code

But the code! Here's a link to the public graph. Feel free to clone it and run your own newsletter!

Matthew Mitchener

When I was a Salesforce developer, I frequently worked with small internal teams within larger organizations. Since traditional AI solutions require large amounts of data and specialized infrastructure to function properly, utilizing AI capabilities was something that a small compliance department or customer service team wouldn’t even consider.

Now with services such as Cohere, you can fine-tune pre-trained Large Language Models (LLMs) and use Patterns to provide easy-to-configure data orchestration with no infrastructure required. No matter what budget or available resources, small teams no longer need to be left behind in the AI revolution and can take advantage of these powerful tools to improve their operations and decision-making.

In this article, I’ll explain how to quickly set up a Salesforce Case classifier using an LLM such as GPT-3. While this is a common use case for AI within Salesforce, this example can be applied to other AI solutions as well.

Goal

In this post, we will use Patterns to fetch data from Salesforce, fine-tune an LLM with Cohere and finally classify Salesforce and upload those classifications back to Salesforce. In the end, we will have built this Salesforce Case Classifier in Patterns. Feel free to clone this one and configure it yourself if you’d like to build it from scratch.

Prerequisites

Import training data into Patterns

  1. Open up Patterns and create a new app (call it anything you’d like)

  2. Inside your graph, click “Search Components” in the nav bar to open up our Marketplace and find a node called “Query Salesforce (SOQL)”

  3. Add this component to your graph and configure the following:

    1. Configure connection to your salesforce connection if one exists. If not, click “Connect to Salesforce”
    2. Set the query to:
    SELECT Id, Type, Subject, Status
    FROM Case
    WHERE Type != null
    AND Status = 'Closed'
  4. Rename the output table “salesforce_training”

Once configured with a Salesforce connection, your app should look like this.

Configuring a Salesforce connection with Patterns

Configure a webhook for incoming Cases to be classified

Patterns provides the ability to trigger graph nodes from a webhook. Unfortunately, Salesforce does not have webhooks support but we can get around this with a little bit of Apex.

Patterns setup

  1. First, let’s start by adding a “Webhook” component in Patterns by clicking the “Add” button in the top left, selecting “Webhook” and placing it on the graph
  2. Open the webhook component and copy its unique URL (we’ll need this later)

Salesforce setup

  1. Start by adding a “Remote Site Setting” by going to Setup > Security > Remote Site Settings and adding an entry for https://api.patterns.app
  2. Next, we need to add the following Apex class. You will need to replace https://api-staging.patterns.app/api/v1/webhooks/your-webhook with the URL we copied earlier (see step 2)
public class Patterns
{
@future(callout=true)
public static void sendToPatterns(Set<Id> caseIds) {
List<Case> cases = [SELECT Id, Subject FROM Case WHERE Id IN: caseIds];

HttpRequest request = new HttpRequest();
request.setEndpoint('https://api-staging.patterns.app/api/v1/webhooks/your-webhook');
request.setMethod('POST');
request.setHeader('Content-Type', 'application/json');
request.setBody(JSON.serialize(cases));

Http http = new Http();
http.send(request);
}
}
  1. Finally, create a new Apex trigger to execute our previous class on Case creation.
trigger CaseClassifier on Case (after insert) {
if(!Trigger.new.isEmpty()) {
Patterns.sendToPatterns(Trigger.newMap.keySet());
}
}

Patterns setup

Back in Patterns, we will now need to do some cleanup with the records we receive through our webhook.

  1. Add a new Python component and paste the following code
from patterns import (
Parameter,
State,
Table,
)

from pandas import DataFrame, concat
import json

webhook_results = Table("webhook_results", "r")
salesforce_testing = Table("salesforce_testing", "w")

stream_results = webhook_results.as_stream(order_by="timestamp")

records = []

for result in stream_results.consume_records():
data = result['record']
record = json.loads(data) if type(data) == str else data
print(data)
records.append(record)

df = DataFrame(records, columns=['Id', 'Subject'])
salesforce_testing.write(df)
  1. Configure your new python component by connecting the webhook_results to the webhook table.
  2. For the salesforce_testing table, we’ll need to create a new table component. Click the dropdown in the editor gutter and select the “create Table Store” option.

Your graph should now look like this:

Screen Shot 2022-12-30 at 8.29.49 PM.png

Cohere setup

Now that we have our training and testing data, let’s set up the classifier

  1. Go back to the Marketplace and search for a component called “Cohere Classify”
  2. Add the component to the graph and configure:
    1. Example Category Column = “Type”
    2. Example Text Column = “Subject”
    3. Input Text Column = “Subject”
    4. Connect cohere_input to the “salesforce_testing” table
    5. Connect cohere_examples to the “salesforce_training” table

In this example, we are classifying Cases based on the “Subject” field and predicting what “Type” it should be. However, free feel to configure whatever fields you like. The finished graph should look like this.

Configuring a Cohere component in Salesforce

Running the graph

Now that you’re all set, time to run our graph. Assuming your Salesforce org already has Cases we can train with, hover over the SOQL node and click the “Run” button (looks like a Play icon). This will run the graph and send our training data to Cohere.

We won’t see any output, so let’s see the result of our work by going back into our Salesforce org and create a new Case, such as the one below:

Creating a Salesforce Case

Shortly after creating a case, you should see the Patterns graph come to life.

Our case with the subject “Having issues with power generation” is given a prediction of the type “Electrical” and a confidence score of 0.909923

Results of our Salesforce Case classifier

Exporting to Salesforce

Our final step is to update Salesforce with the results of our classification engine.

  1. Create a new Python component and copy/paste the following code:
from patterns import (
Parameter,
State,
Table,
Connection
)

import requests
import json

salesforce = Parameter(
'salesforce',
type=Connection("salesforce")
)

access_token = salesforce.get("access_token")
instance_url = salesforce.get("instance_url")

cases = Table('cohere_output')
cases_stream = cases.as_stream()

headers = {"Authorization": f"Bearer {access_token}", "Content-Type": "application/json"}

for c in cases_stream.consume_records():
case_id=c['Id']
prediction=c['prediction']
data={ "Type": c['prediction'] }

result = requests.patch(
url=f'{instance_url}/services/data/v56.0/sobjects/Case/{case_id}',
data=json.dumps(data),
headers=headers
)

result.raise_for_status()
  1. Finally, configure the Salesforce connection and the table associated with our cohere output

By reading our output as a stream, we will only process one case at a single time. This ensures we update Salesforce opportunistically and not with the entire table every time our graph runs.

Conclusion

By utilizing Patterns and Cohere, we were able to build out a simple but powerful Salesforce Case classifier using an LLM. As we collect more data, we can continuously fine-tune our model by manually re-running the graph or scheduling it with a cron job. Hopefully, this helps you get started with not just Salesforce Case classification but also AI solutions such as text summarization, content moderation, and semantic searching. With a tool like Patterns, stitching this all together and deploying AI solutions has never been simpler.

Ken Van Haren

When I was at Square and the team was smaller we had a dreaded “analytics on-call” rotation. It was strictly rotated on a weekly basis, and if it was your turn up you knew you would get very little “real” work done that week and spend most of your time fielding ad-hoc questions from the various product and operations teams at the company (SQL monkeying, we called it). There was cutthroat competition for manager roles on the analytics team and I think this was entirely the result of managers being exempted from this rotation -- no status prize could rival the carrot of not doing on-call work.

So, the idea that something like analytics on-call could be entirely replaced by a next-token optimizer like GPT-3 has been an idea I’ve been excited to try for a long time now. I finally got a chance to sit down and try it out this week.

We’re about to kick off our fundraising process, so I to test the idea I attempted to build a free-form question answering analytics bot on top of the Crunchbase data set of investors, companies, and fundraising rounds. I call it CrunchBot.

Read on to get the details, but here is a quick example of it getting a simple thing exactly right:

crunchbot

And an example of it getting something more complex exactly right:

crunchbot

And an example of it getting something complex terrifically wrong (watch your join conditions davinci-003!):

crunchbot

And something of medium complexity mostly right (it’s so close here, needs an ilike instead of like, which it messed up despite a specific prompt telling it to watch out for this mistake):

crunchbot

Overall I was dumbfounded with the quality of the results. LLMs are a continual source of blown minds, but this is shockingly close to replacing an entire role at companies with only a few hours of effort.

How I built it

My plan was to build it in Patterns Studio. There were mostly four parts

  • Build the prompt from
    • user’s question
    • schemas and sample data of the available tables
    • clear directions
  • Run it through various GPT models and get 5+ completions of raw SQL
  • Execute the SQL against the relevant tables, pick the best result
  • Hook it up to a Slack bot

By the end I had a much more complex pipeline as I kept quickly finding prompt improvements. Specifically the SQL generation and execution part became a loop of:

  • Generate a candidate query
  • Quality check the SQL with GPT itself, asking it to spot common errors (NULLS LAST, for instance) and produce a correct version of the query
  • Run the SQL against the tables
  • If there was an error or no result, ask GPT to fix the query to produce a correct result and repeat the loop
  • Otherwise, return the result

This led to completion chains of over 20 calls to GPT for a single user question. There feels like some logarithmic improvement in each GPT call — you can continue to add more context and checks with every completion, exploring different versions and iterating on the result. This is the same process that a junior analyst would use to arrive at the answer, in this case it takes 15 seconds and costs $1 in credits vs $50 and 1 hour for the analyst. You have a LOT of leeway there to use even more crazy prompt pipelines before the ROI gets bad.

Anyways, here’s the step-by-step of how I built it. You can follow along in this template app on Patterns that has all the code (no data it in, Crunchbase is a proprietary data set — reach out kvh@patterns.app if you are interested in exploring this specific data set).

1. Get the Crunchbase data

The full Crunchbase dataset includes 2.4m organizations and 510k funding rounds. We ingest this via the full CSV dump every 24 hours into our postgres instance. For this analysis we restricted it to three tables: organizations, funding_rounds, and investments.

2. Build the initial prompt

The initial SQL generation prompt includes three basic elements: a summary of the tables and data available to query, the user’s question, and a prompt asking GPT to write a correct Postgres query. Here’s the exact template we used:

prompt = f"""{tables_summary}

As a senior analyst, given the above schemas and data, write a detailed and correct Postgres sql query to answer the analytical question:

"{question}"

Comment the query with your logic."""

Which results in this full prompt, for example:

Schema for table: organizations
uuid Text
name Text
roles Text
country_code Text
region Text
city Text
status Text
short_description Text
category_list Text
num_funding_rounds Float
total_funding_usd Float
founded_on Date
employee_count Text
email Text
primary_role Text

Data for table: organizations:
uuid name roles \
0 ac323097-bdd0-4507-9cbc-6186e61c47a5 Bootstrap Enterprises company
1 717ce629-38b6-494d-9ebf-f0eeb51506f8 Campanizer company
2 c8cbaa69-c9db-44e2-9ffa-eb4722a62fe3 Cambr company
3 5ab1ae3d-c3a1-4268-a532-b500d3dd6182 CallMeHelp company
4 143f840b-551c-4dbd-a92b-0804d654b5cf California Cannabis Market company

country_code region city status \
0 <NA> <NA> <NA> operating
1 USA Colorado Boulder operating
2 USA New York New York operating
3 GBR Stockport Stockport operating
4 USA California San Francisco closed

short_description \
0 Bootstrap Enterprises is an organic waste mana...
1 Campanizer organizes schedule and coordinates ...
2 Cambr enables companies to build and scale fin...
3 CallMeHelp provides early warning and care ove...
4 California Cannabis Market is an information t...

category_list num_funding_rounds \
0 Consulting,Organic,Waste Management NaN
1 Information Technology,Scheduling NaN
2 Banking,Financial Services NaN
3 Fitness,Health Care,Wellness NaN
4 B2B,Information Services,Information Technology NaN

total_funding_usd founded_on employee_count email \
0 NaN NaT unknown <NA>
1 NaN 2017-01-01 1-10 hello@campanizer.com
2 NaN NaT unknown sales@cambr.com
3 NaN 2017-01-01 1-10 <NA>
4 NaN 2018-01-01 1-10 <NA>

primary_role
0 company
1 company
2 company
3 company
4 company


Schema for table: investments
uuid Text
name Text
funding_round_uuid Text
funding_round_name Text
investor_uuid Text
investor_name Text
investor_type Text
is_lead_investor Boolean

Data for table: investments:
uuid \
0 524986f0-3049-54a4-fa72-f60897a5e61d
1 6556ab92-6465-25aa-1ffc-7f8b4b09a476
2 0216e06a-61f8-9cf1-19ba-20811229c53e
3 dadd7d86-520d-5e35-3033-fc1d8792ab91
4 581c4b38-9653-7117-9bd4-7ffe5c7eba69

name \
0 Accel investment in Series A - Meta
1 Greylock investment in Series B - Meta
2 Meritech Capital Partners investment in Series...
3 Trinity Ventures investment in Series B - Phot...
4 Founders Fund investment in Series A - Geni

funding_round_uuid funding_round_name \
0 d950d7a5-79ff-fb93-ca87-13386b0e2feb Series A - Meta
1 6fae3958-a001-27c0-fb7e-666266aedd78 Series B - Meta
2 6fae3958-a001-27c0-fb7e-666266aedd78 Series B - Meta
3 bcd5a63d-ed99-6963-0dd2-e36f6582f846 Series B - Photobucket
4 60e6afd9-1215-465a-dd17-0ed600d4e29b Series A - Geni

investor_uuid investor_name \
0 b08efc27-da40-505a-6f9d-c9e14247bf36 Accel
1 e2006571-6b7a-e477-002a-f7014f48a7e3 Greylock
2 8d5c7e48-82da-3025-dd46-346a31bab86f Meritech Capital Partners
3 7ca12f7a-2f8e-48b4-a8d1-1a33a0e275b9 Trinity Ventures
4 fb2f8884-ec07-895a-48d7-d9a9d4d7175c Founders Fund

investor_type is_lead_investor
0 organization True
1 organization True
2 organization True
3 organization <NA>
4 organization True


Schema for table: funding_rounds
uuid Text
region Text
city Text
investment_type Text
announced_on Date
raised_amount_usd Float
post_money_valuation_usd Float
investor_count Float
org_uuid Text
org_name Text
lead_investor_uuids Text

Data for table: funding_rounds:
uuid region city \
0 8a945939-18e0-cc9d-27b9-bf33817b2818 California Menlo Park
1 d950d7a5-79ff-fb93-ca87-13386b0e2feb California Menlo Park
2 6fae3958-a001-27c0-fb7e-666266aedd78 California Menlo Park
3 bcd5a63d-ed99-6963-0dd2-e36f6582f846 Colorado Denver
4 60e6afd9-1215-465a-dd17-0ed600d4e29b California West Hollywood

investment_type announced_on raised_amount_usd post_money_valuation_usd \
0 angel 2004-09-01 500000.0 NaN
1 series_a 2005-05-01 12700000.0 98000000.0
2 series_b 2006-04-01 27500000.0 502500000.0
3 series_b 2006-05-01 10500000.0 NaN
4 series_a 2007-01-17 NaN 10000000.0

investor_count org_uuid org_name \
0 4.0 df662812-7f97-0b43-9d3e-12f64f504fbb Meta
1 4.0 df662812-7f97-0b43-9d3e-12f64f504fbb Meta
2 5.0 df662812-7f97-0b43-9d3e-12f64f504fbb Meta
3 2.0 f53cb4de-236e-0b1b-dee8-7104a8b018f9 Photobucket
4 1.0 4111dc8b-c0df-2d24-ed33-30cd137b3098 Geni

lead_investor_uuids
0 3f47be49-2e32-8118-01a0-31685a4d0fd7
1 b08efc27-da40-505a-6f9d-c9e14247bf36
2 e2006571-6b7a-e477-002a-f7014f48a7e3,8d5c7e48-...
3 <NA>
4 fb2f8884-ec07-895a-48d7-d9a9d4d7175c


As a senior analyst, given the above schemas and data, write a detailed and correct Postgres sql query to answer the analytical question:

"Who were the largest biotech investors in 2022?"

Comment the query with your logic.

3. Double check the query

I found GPT made some common mistakes over and over again (the same ones any analyst would make), so I gave it a specific prompt to review each query and fix any bugs before doing anything else:

prompt = f"""{query.sql}

Double check the Postgres query above for common mistakes, including:
- Remembering to add `NULLS LAST` to an ORDER BY DESC clause
- Handling case sensitivity, e.g. using ILIKE instead of LIKE
- Ensuring the join columns are correct
- Casting values to the appropriate type

Rewrite the query here if there are any mistakes. If it looks good as it is, just reproduce the original query."""

4. Run the generated SQL against the database, fix any errors

Next we try to run the SQL against the database. If it produces a result, we store the result and query. If it produces no result or an error, we ask GPT to fix the SQL:

error_prompt = f"""{query.sql}

The query above produced the following error:

{query.error}

Rewrite the query with the error fixed:"""

no_result_prompt = f"""{query.sql}

The query above produced no result. Try rewriting the query so it will return results:"""

Here’s an example of this step working well, with the following query. Can you spot the error?

SELECT org_category,
SUM(CASE WHEN YEAR(announced_on) = 2021 THEN raised_amount_usd ELSE 0 END) as 2021_investments,
SUM(CASE WHEN YEAR(announced_on) = 2022 THEN raised_amount_usd ELSE 0 END) as 2022_investments,
SUM(CASE WHEN YEAR(announced_on) = 2022 THEN raised_amount_usd ELSE 0 END) - SUM(CASE WHEN YEAR(announced_on) = 2021 THEN raised_amount_usd ELSE 0 END) as diff_investments
FROM organizations o JOIN funding_rounds f ON o.uuid = f.org_uuid
GROUP BY org_category
ORDER BY diff_investments DESC NULLS LAST
LIMIT 1;

Syntax error:

ERROR:  syntax error at or near "2021_investments"
LINE 2: ..._on) = 2021 THEN raised_amount_usd ELSE 0 END) as 2021_inves...

Which was then fixed with this new query:

SELECT org_category,
SUM(CASE WHEN YEAR(announced_on) = 2021 THEN raised_amount_usd ELSE 0 END) as investments_2021,
SUM(CASE WHEN YEAR(announced_on) = 2022 THEN raised_amount_usd ELSE 0 END) as investments_2022,
SUM(CASE WHEN YEAR(announced_on) = 2022 THEN raised_amount_usd ELSE 0 END) - SUM(CASE WHEN YEAR(announced_on) = 2021 THEN raised_amount_usd ELSE 0 END) as diff_investments
FROM organizations o JOIN funding_rounds f ON o.uuid = f.org_uuid
GROUP BY org_category
ORDER BY diff_investments DESC NULLS LAST
LIMIT 1;

5. Repeat N times

Repeat the above completion loop as many times as we want to get a set of queries and results. I chose to stop this loop once I got a result that matched a previous result, or I hit my limit of max tries (5 in this case).

6. Build the Slack bot

I used the off-the-shelf Patterns template for Slack bots and hooked it up. I added a second slack node for acknowledging the original Slack mention quickly, since the full GPT pipeline can take 20 seconds plus.

Conclusion

It seems like there’s almost no limit to how good GPT could get at this. The remaining failure modes were ones of either missing context or not enough iterative debugging (the kind that is the beating heart of any analytics endeavor). For instance, GPT had to guess the exact names and spellings of categories or company names when answering questions. If it was allowed to run some “research” queries first, it could have been able to build enough context for a final prompt and query to get to the right answer. Playing around with GPT at this level you get the feeling that “recursive GPT” is very close to AGI. You could even ask GPT to reinforcement learn itself, adding new prompts based on fixes to previous questions. Of course, who knows what will happen to all this when GPT-4 drops.

(If you want to play around with a similar bot, you can clone the template of this app and switch to a different data set)

Chris Stanley

Tech support as an LLM Slack bot

One problem we’re working on at Patterns is how to scale our technical support. We have technical documentation, Slack channels, emails, and support tickets that all provide a way for us to interface with our customers. Like many folks, we've been playing around with the power and potential of new Large Language Models like ChatGPT, so we decided to see if we could help tackle our support problem with an LLM support bot.

We came in with somewhat low expectations -- we know these models are prone to common failure modes you'd expect from a next-token optimizer -- so we were shocked when we saw the end result. Read on to learn how we did it and our experience building with LLMs. By the end of the post, we'll have built a full app that you can clone and customize to have your own version of our bot if you'd like.

Game plan

Our plan was to build an automated slack bot that would respond to tech support questions with knowledge from our technical documentation, community slack channels, and previous support tickets.

Normally with LLMs you put all the necessary context in the prompt. We had far too much content to fit in the 2048 token limit for the normal prompt though. Luckily, many LLM providers like OpenAI provide a "fine-tuning" api where you can submit labeled example completions to fine-tune your own version of their LLM.

This fine-tuning workflow seemed like a good fit for our problem -- it could take our rich text corpus, fine-tune a model, and then provide completions via a Slack bot.

To serve this experience as a Patterns app end-to-end we need to:

  1. Generate training data from our text corpus
  2. Fine-tune our model (OpenAIs GPT-3 davinci-003 engine)
  3. Serve the fine-tuned model as a Slack bot

Let's build it!

Prerequisites

  • OpenAI account, API key
  • A text corpus to train your bot, e.g. technical documentation or something similar

Generating training data

Immediately we ran into a problem -- to fine-tune an OpenAI model requires a specific format of prompt-completion pairs:

{"prompt": "<prompt text>", "completion": "<ideal generated text>"}
{"prompt": "<prompt text>", "completion": "<ideal generated text>"}
{"prompt": "<prompt text>", "completion": "<ideal generated text>"}

Our free-form text corpus wouldn't work out of the box. We could manually generate the training data, but that's a lot of work. So we remembered the Golden Rule of LLMs: If it at first you don't succeed, send it through again with a new prompt.

So instead of manually labeling we come up with a series of prompts to automatically generate our labeled pairs from our corpus:

  1. Feed GPT a chunk of our corpus and ask it to generate three relevant questions for the chunk
  2. Feed those questions back in with the chunk and ask GPT to specifically answer the generated question using the chunk
  3. Take this output and pair with the original generated question for a labeled training example to feed to the fine-tuner

Training data

Import training data into Patterns

There are many ways to import our documentation into Patterns. To keep it simple for our example, we loaded ours into an Airtable base that we imported via the standard Airtable component in Patterns. An example of the technical documentation text contained:

Webhooks and Streaming

Patterns has native support for stream processing. A common automation is to have
a webhook ingesting real-time events from some external system, processing these
events with a series of python nodes, and then sending a response, either to a
communication channel like Slack or Discord, or to trigger an action in another
external system.

Generate questions to use as prompts from our docs

With our text data in Patterns, we wrote a prompt to tease out three questions that would act as our prompts in training data. From the marketplace we cloned the OpenAI Completions component, added the following prompt, and ran it to generate completions (an example is shown in green below).

You are reviewing the documentation for a data company called Patterns. Write 
three questions based on the documentation below.

Documentation:
Patterns has native support for stream processing. A common automation is to have
a webhook ingesting real-time events from some external system, processing these
events with a series of python nodes, and then sending a response, either to a
communication channel like Slack or Discord, or to trigger an action in another
external system.

Questions:
1. What types of real-time events are supported by Patterns webhooks?
2. How are python nodes used to process events?
3. How are responses sent to external systems?

This is what it looks like in the UI.

Foo

Generate answers from the questions in the prior step

With the three generated questions / prompts that are relevant to our task, we next generated answers for these using the documentation text. Again, we cloned the OpenAI Completions component and edited the prompt:

You are a smart and witty technical support agent for a data company called Patterns. 
Please generate answers to the following questions given the documentation.

Documentation:
Patterns has native support for stream processing. A common automation is to have
a webhook ingesting real-time events from some external system, processing these
events with a series of python nodes, and then sending a response, either to a
communication channel like Slack or Discord, or to trigger an action in another
external system.

Questions:
1. What types of real-time events are supported by Patterns webhooks?
2. How are python nodes used to process events?
3. How are responses sent to external systems?

Answers:
1. Patterns webhooks support a wide range of real-time events, including webhooks for Slack, Discord, and other external systems.
2. Python nodes are used to process the real-time events received via webhooks and transform them into the desired output format.
3. Responses can be sent to external systems through a communication channel like Slack or Discord, or by triggering an action in the external system.

Next we prepped the data and formatted it for fine-tuning. There are many tricks here and OpenAI has a CLI data preparation tool to ensure the structure of your data prior to upload.

Foo

Upload training data to OpenAI, and start the fine-tuning job

This resulted in prompt/completion pairs that we could structure and upload to OpenAI for fine-tuning. This involved first uploading our completions, which took a few seconds, and then kicking off the fine-tuning job on OpenAI.

Depending on the size of training data, and OpenAI's resource availability, fine-tuning can take anywhere from a few minutes to a few hours, for that reason we kept a state object in our fine-tuning script and check for updates until it's finished.

When the fine-tuning job completes successfully, we write out the model to another table named finetuned_models.

Configure fine-tuned model as a Slack Bot

With the fine-tuned model ready to go on OpenAIs servers, we could tackle the next step -- building a Slackbot to receive and respond to customer's questions.

To configure a Slack bot in Patterns that uses GPT-3 we set up the following flow:

  1. A webhook configured to receive messages from a Slack channel
  2. A Python node to detect when the bot is mentioned
  3. An OpenAI node, parameterized with the model_name from the fine-tuning process, that provides a completion to any prompt
  4. A Python node that serves completion back to the Slack channel

To do this, we replicated Engineering Advice GPT-3 Slack Bot into the same App and followed the configuration in that link.

Learnings and next steps

Overall, we were impressed with the bot's ability to generate answers to relevant questions that it's trained on. However, a lot of the time the bot just makes stuff up; this occurs for both prompts that do and don't have relevant samples in the training data. Making stuff up and being confidently wrong are well known side-effects of LLMs and there are many techniques to change this behavior.

Foo (totally made up URL, not even our domain)

In Part II of this post (coming soon), we will explore several avenues for improvement, including expanding the corpus via automated ingestion, using embeddings to encode documentation semantics to provide more robust links directly to documentation, and labeling some negative examples of when the bot should or should not respond.

If you have any questions about setting up a use case similar to this, please email me at chris@patterns.app or create a Slack channel from your dashboard and message us there.

You can check out all the code for this bot here. Clone and customize it with a free Patterns account. To interact with the bot directly, after signing up create a shared Slack channel with our team.

Chris Stanley

Hi! Thanks for checking out our first blog post. We're excited to share more with you in the future. For now, let us introduce ourselves and tell you a little bit about what we're working on. Keep an eye out for more content coming soon.

Who are we?

As a small team of engineers, statisticians, and designers, our goal is to make data and AI more accessible. With technology constantly advancing, many AI tools are only available to those who can afford them. By offering access to the latest and greatest models and a platform for sharing AI applications, we want to enable more people to work with AI.

Learn more about our team and vision on our about page.

What is Patterns?

Patterns is a unified data processing platform that is purpose built for connecting state-of-the-art AI models to your business systems. Patterns does this by providing an open platform for developing, deploying and sharing integrations and applications that run on a standard protocol.

You can read more details about our platform here.

We want to hear from you!

Are you building something interesting that you'd like to share with our community? Contact us here or send us an email at experts@patterns.app.