Find the problems your market keeps raising

Every Monday at 08:00, the newest posts in the subreddits you name are read, the AI groups the frustrations and unmet needs that keep coming back into themes, and the ones with real evidence behind them are emailed to you. It looks for problems nobody has solved yet, not for mentions of you, so themes are reported whether or not your product is named.

Each theme comes with how many posts sit behind it, what those people want, and the title and link of every post behind it. Every post the AI cites is checked against the posts actually read, and any it did not get from that list is discarded, so nothing is invented. A theme with too few posts is left out rather than padded, and a week where nothing recurs is reported as exactly that. Each reported theme is also appended to your Google Sheet as a history that is never overwritten.

Before the first run you need a sheet with a tab headed 'Date', 'Theme', 'Posts behind it', 'What they want', 'Subreddits read' and 'Example links'.

Run every Monday at 08:00.Run every Monday at 08:00.

Set subreddits to blank.Set subreddits to blank.

Set marketDescription to blank.Set marketDescription to blank.

Set digestRecipient to blank.Set digestRecipient to blank.

Set themeLogUrl to blank.Set themeLogUrl to blank.

Set themeLogTab to blank.Set themeLogTab to blank.

Set maxSubreddits to 5.Set maxSubreddits to 5.

Set postsPerSubreddit to 50.Set postsPerSubreddit to 50.

Set minPostsPerTheme to 3.Set minPostsPerTheme to 3.

Set maxThemesReported to 10.Set maxThemesReported to 10.

Set subredditCap to maxSubreddits multiplied by 1.Set subredditCap to maxSubreddits multiplied by 1.

Set postCap to postsPerSubreddit multiplied by 1.Set postCap to postsPerSubreddit multiplied by 1.

Set themeMinimum to minPostsPerTheme multiplied by 1.Set themeMinimum to minPostsPerTheme multiplied by 1.

Set themeCap to maxThemesReported multiplied by 1.Set themeCap to maxThemesReported multiplied by 1.

If postCap is less than 1 or postCap is greater than 100If postCap is less than 1 or postCap is greater than 100

Yes

Stop and report a problem: 'postsPerSubreddit' has to be a whole number between 1 and 100. Reddit will not return more than 100 posts in one read.Stop and report a problem: 'postsPerSubreddit' has to be a whole number between 1 and 100. Reddit will not return more than 100 posts in one read.

No

No steps

If subredditCap is less than 1If subredditCap is less than 1

Yes

Stop and report a problem: 'maxSubreddits' has to be at least 1 - that is how many of the subreddits you listed get read each run.Stop and report a problem: 'maxSubreddits' has to be at least 1 - that is how many of the subreddits you listed get read each run.

No

No steps

If themeMinimum is less than 2If themeMinimum is less than 2

Yes

Stop and report a problem: 'minPostsPerTheme' has to be at least 2. One post is somebody's bad day; a theme needs more than that behind it.Stop and report a problem: 'minPostsPerTheme' has to be at least 2. One post is somebody's bad day; a theme needs more than that behind it.

No

No steps

Get today's date and save it as runDate.Get today's date and save it as runDate.

Start an empty list and save it as postRefs.Start an empty list and save it as postRefs.

Start an empty list and save it as postLabels.Start an empty list and save it as postLabels.

Start an empty list and save it as postLinks.Start an empty list and save it as postLinks.

Start an empty list and save it as catalogueLines.Start an empty list and save it as catalogueLines.

Start an empty list and save it as subredditsRead.Start an empty list and save it as subredditsRead.

Set postsRead to 0.Set postsRead to 0.

Set subredditsTried to 0.Set subredditsTried to 0.

Set postsSkipped to 0.Set postsSkipped to 0.

For each subredditEntry in subreddits split by ,:For each subredditEntry in subreddits split by ,:

If subredditsTried is greater than or equal to subredditCapIf subredditsTried is greater than or equal to subredditCap

Yes

Stop repeating.Stop repeating.

No

No steps

Trim subredditEntry and save it as subredditName.Trim subredditEntry and save it as subredditName.

If subredditName isn't emptyIf subredditName isn't empty

Yes

Add 1 to subredditsTried.Add 1 to subredditsTried.

Set subredditPrefix to r/subredditName - .Set subredditPrefix to r/subredditName - .

Set subredditTag to [r/subredditName] .Set subredditTag to [r/subredditName] .

Set listing to nothing.Set listing to nothing.

Set listingRead to false.Set listingRead to false.

If a step below fails, save the error as readErrorIf a step below fails, save the error as readError

Try

RedditList posts in r/subredditName. Save it as listing.List posts in r/subredditName. Save it as listing.

Change listingRead to true.Change listingRead to true.

On error

Show in the output: Could not read r/subredditName: readError as text.Show in the output: Could not read r/subredditName: readError as text.

If listingRead is yesIf listingRead is yes

Yes

Add subredditName to the end of subredditsRead and save it as subredditsRead.Add subredditName to the end of subredditsRead and save it as subredditsRead.

For each post in listing.posts:For each post in listing.posts:

If a step below fails, save the error as postError If a step below fails, save the error as postError

Try

Trim post.title and save it as postTitle.Trim post.title and save it as postTitle.

Trim post.permalink and save it as postPath.Trim post.permalink and save it as postPath.

If postTitle isn't empty and postPath starts with /r/If postTitle isn't empty and postPath starts with /r/

Yes

Add 1 to postsRead.Add 1 to postsRead.

Set postRef to P@postsRead.Set postRef to P@postsRead.

Add postRef to the end of postRefs and save it as postRefs.Add postRef to the end of postRefs and save it as postRefs.

Add subredditPrefix plus postTitle to the end of postLabels and save it as postLabels.Add subredditPrefix plus postTitle to the end of postLabels and save it as postLabels.

Add https://www.reddit.com@postPath to the end of postLinks and save it as postLinks.Add https://www.reddit.com@postPath to the end of postLinks and save it as postLinks.

Add postRef plus subredditTag plus postTitle to the end of catalogueLines and save it as catalogueLines.Add postRef plus subredditTag plus postTitle to the end of catalogueLines and save it as catalogueLines.

No

Add 1 to postsSkipped.Add 1 to postsSkipped.

On error

Add 1 to postsSkipped.Add 1 to postsSkipped.

Show in the output: Skipped a post with no readable title or link in subredditName: postError as text.Show in the output: Skipped a post with no readable title or link in subredditName: postError as text.

No

No steps

No

No steps

If postRefs is emptyIf postRefs is empty

Yes

Finish the run: No posts could be read from the subreddits you listed, so there is nothing to group and nothing has been emailed. Check the names in the 'subreddits' step and that your Reddit connection is working.Finish the run: No posts could be read from the subreddits you listed, so there is nothing to group and nothing has been emailed. Check the names in the 'subreddits' step and that your Reddit connection is working.

No

No steps

Join catalogueLines with a new line and save it as catalogue.Join catalogueLines with a new line and save it as catalogue.

Set promptOpening to:Set promptOpening to:You are helping a team work out which problems their market keeps running into. Below are recent posts from the communities where their customers spend time. Each post is listed with a label. The team describes their market like this:

Set promptFormat to:Set promptFormat to:Group the posts that describe a frustration, a complaint or a need nobody has met into themes. Reply with one line per theme and nothing else, in exactly this format: THEME: <short name> | REFS: <labels, comma separated> | NEED: <one sentence on what these people want and cannot get today> Rules: - Every label in REFS must be copied exactly from the list below. Never invent a label, and never cite a post that is not in the list. - Put a post in a theme only when its own title describes that problem. Do not stretch a post to make a theme bigger. - Only report a theme that at least

Set promptClosing to:Set promptClosing to:different posts support. - Ignore posts that are not complaints, frustrations or unmet needs: announcements, jokes, self-promotion and questions that were already answered. - If nothing recurs across enough posts, reply with the single word NONE. Posts:

Set themePrompt to promptOpening plus marketDescription plus promptFormat plus themeMinimum plus promptClosing plus catalogue.Set themePrompt to promptOpening plus marketDescription plus promptFormat plus themeMinimum plus promptClosing plus catalogue.

Set modelReply to nothing.Set modelReply to nothing.

Set modelAnswered to false.Set modelAnswered to false.

If a step below fails, save the error as modelErrorIf a step below fails, save the error as modelError

Try

OpenAIAsk OpenAI: themePrompt. Save it as modelReply.Ask OpenAI: themePrompt. Save it as modelReply.

Change modelAnswered to true.Change modelAnswered to true.

On error

Show in the output: Could not group the posts into themes: modelError as text.Show in the output: Could not group the posts into themes: modelError as text.

If modelAnswered is noIf modelAnswered is no

Yes

Stop and report a problem: The posts were read but could not be grouped into themes this run, so nothing has been written to your sheet or emailed. Nothing is lost - the next run reads the subreddits again.Stop and report a problem: The posts were read but could not be grouped into themes this run, so nothing has been written to your sheet or emailed. Nothing is lost - the next run reads the subreddits again.

No

No steps

Set themeAnswer to blank.Set themeAnswer to blank.

If a step below fails, save the error as answerErrorIf a step below fails, save the error as answerError

Try

Trim modelReply.response_text and save it as themeAnswer.Trim modelReply.response_text and save it as themeAnswer.

On error

Show in the output: The model returned no text to read themes out of..Show in the output: The model returned no text to read themes out of..

If themeAnswer is emptyIf themeAnswer is empty

Yes

Stop and report a problem: The posts were read but the grouping step came back empty, so nothing has been written to your sheet or emailed.Stop and report a problem: The posts were read but the grouping step came back empty, so nothing has been written to your sheet or emailed.

No

No steps

Start an empty list and save it as themeSections.Start an empty list and save it as themeSections.

Set themesReported to 0.Set themesReported to 0.

Set themesDropped to 0.Set themesDropped to 0.

Set unmatchedLabels to 0.Set unmatchedLabels to 0.

For each rawThemeLine in themeAnswer split by a new line:For each rawThemeLine in themeAnswer split by a new line:

If themesReported is greater than or equal to themeCapIf themesReported is greater than or equal to themeCap

Yes

Stop repeating.Stop repeating.

No

No steps

Trim rawThemeLine and save it as themeLine.Trim rawThemeLine and save it as themeLine.

Split themeLine by | and save it as themeParts.Split themeLine by | and save it as themeParts.

If themeLine contains THEME: and themeLine contains REFS: and themeLine…If themeLine contains THEME: and themeLine contains REFS: and themeLine…

Yes

Trim themeParts[0] with THEME: replaced by and save it as themeName.Trim themeParts[0] with THEME: replaced by and save it as themeName.

Replace REFS: with in themeParts[1] and save it as themeRefField.Replace REFS: with in themeParts[1] and save it as themeRefField.

Trim themeParts[2] with NEED: replaced by and save it as themeNeed.Trim themeParts[2] with NEED: replaced by and save it as themeNeed.

Start an empty list and save it as evidenceLines.Start an empty list and save it as evidenceLines.

Start an empty list and save it as evidenceUrls.Start an empty list and save it as evidenceUrls.

Start an empty list and save it as seenRefs.Start an empty list and save it as seenRefs.

For each refToken in themeRefField split by ,:For each refToken in themeRefField split by ,:

Trim refToken and save it as citedRef. Trim refToken and save it as citedRef.

If citedRef isn't empty and seenRefs doesn't contain citedRef If citedRef isn't empty and seenRefs doesn't contain citedRef

Yes

Add citedRef to the end of seenRefs and save it as seenRefs.Add citedRef to the end of seenRefs and save it as seenRefs.

Find the position of citedRef in postRefs and save it as postPosition.Find the position of citedRef in postRefs and save it as postPosition.

If postPosition is greater than or equal to 0If postPosition is greater than or equal to 0

Yes

Add postLabels[@postPosition]Add postLabels[@postPosition] postLinks[@postPosition] to the end of evidenceLines and save it as evidenceLines.

Add postLinks[@postPosition] to the end of evidenceUrls and save it as evidenceUrls.Add postLinks[@postPosition] to the end of evidenceUrls and save it as evidenceUrls.

No

Add 1 to unmatchedLabels.Add 1 to unmatchedLabels.

No

No steps

Count the items in evidenceLines and save it as evidenceCount.Count the items in evidenceLines and save it as evidenceCount.

If evidenceCount is greater than or equal to themeMinimum and themeName isn't…If evidenceCount is greater than or equal to themeMinimum and themeName isn't…

Yes

Add 1 to themesReported.Add 1 to themesReported.

Set themeHeadline to themesReported. themeName.Set themeHeadline to themesReported. themeName.

Set themeCountLine to Posts behind it: evidenceCount of the postsRead read this run.Set themeCountLine to Posts behind it: evidenceCount of the postsRead read this run.

Set themeNeedLine to What they want: themeNeed.Set themeNeedLine to What they want: themeNeed.

Join evidenceLines with a new line and save it as themeEvidence.Join evidenceLines with a new line and save it as themeEvidence.

Set themeSection to themeHeadlineSet themeSection to themeHeadline themeCountLine themeNeedLine themeEvidence.

Add themeSection to the end of themeSections and save it as themeSections.Add themeSection to the end of themeSections and save it as themeSections.

If a step below fails, save the error as logErrorIf a step below fails, save the error as logError

Try

Google SheetsAdd a row to themeLogTab in themeLogUrl.Add a row to themeLogTab in themeLogUrl.

On error

Show in the output: Could not log the theme to your sheet: logError as text.Show in the output: Could not log the theme to your sheet: logError as text.

No

Add 1 to themesDropped.Add 1 to themesDropped.

Show in the output: Left out '@themeName': only evidenceCount of the posts it named were posts actually read this run.Show in the output: Left out '@themeName': only evidenceCount of the posts it named were posts actually read this run.

No

No steps

Set scopeLine to Read up to postCap of the newest posts in each of the length of subredditsRead subreddit(s): subredditsRead joined by , .Set scopeLine to Read up to postCap of the newest posts in each of the length of subredditsRead subreddit(s): subredditsRead joined by , .

Set capsLine to Caps for one run: at most subredditCap subreddits, up to postCap posts from each, and at most themeCap themes reported.Set capsLine to Caps for one run: at most subredditCap subreddits, up to postCap posts from each, and at most themeCap themes reported.

Set evidenceNote to Every post named below was read this run and the link goes straight to the thread. A theme needs at least themeMinimum of them behind it to be reported.Set evidenceNote to Every post named below was read this run and the link goes straight to the thread. A theme needs at least themeMinimum of them behind it to be reported.

Set countsLine to Themes reported: themesReported. Themes left out for too little evidence: themesDropped. Posts skipped for a missing title or link: postsSkipped.Set countsLine to Themes reported: themesReported. Themes left out for too little evidence: themesDropped. Posts skipped for a missing title or link: postsSkipped.

Set integrityLine to blank.Set integrityLine to blank.

If unmatchedLabels is greater than 0If unmatchedLabels is greater than 0

Yes

Change integrityLine to Change integrityLine to unmatchedLabels cited post label(s) did not match anything that was read, so they were discarded rather than reported.

No

No steps

Set themeBody to blank.Set themeBody to blank.

If themesReported is greater than 0If themesReported is greater than 0

Yes

Join themeSections with a blank line and save it as themeBody.Join themeSections with a blank line and save it as themeBody.

No

Change themeBody to Nothing came up often enough this week. No theme reached themeMinimum posts, so there is nothing to report - which is the honest answer rather than a thin one.Change themeBody to Nothing came up often enough this week. No theme reached themeMinimum posts, so there is nothing to report - which is the honest answer rather than a thin one.

Set digestOpening to What your market kept complaining aboutSet digestOpening to What your market kept complaining about scopeLine capsLine evidenceNote .

Set digestClosing to Set digestClosing to countsLine@integrityLine.

Set digestBody to digestOpening plus themeBody plus digestClosing.Set digestBody to digestOpening plus themeBody plus digestClosing.

Set digestSubject to Recurring complaints in your market - themesReported theme(s) - runDate.Set digestSubject to Recurring complaints in your market - themesReported theme(s) - runDate.

GmailSend an email to digestRecipient.Send an email to digestRecipient. Subject: digestSubjectdigestBody