Scraping indie game subreddits for developers and games

[andygello555]

Proposition

This would be a process that is very similar to the current Twitter scraping process.
We will need to check the following subreddits daily:

• r/GameDevelopment
• r/SoloDevelopment
• r/gamedesign
• r/IndieDev
• r/truegamedev
• r/gamedev
• r/IndieGaming.
• r/gamedevscreens

What will need to change

• New tables
  • RedditDeveloper table to track individual developers found on Reddit
  • RedditDeveloperSnapshot table to save the snapshots of RedditDevelopers to
• Need to alter the Games table to include references to RedditDevelopers (probably in a new array column)
• Need to create a whole new scraping process for Reddit
  • Either add more Phases to the Scout procedure
  • Or add a completely new set of Phase.Runs for the Reddit scrape
• Would also need to add a bunch more cached fields to the ScoutState object

How will scraping work?

• Discovery
  • Fetch the top X posts divided equally between the subreddits mentioned above + max Y comments for each post
  • For each Post we will see if we can extract any game URLs from the:
    • Post title
    • Post body
    • Any of the comments from the OP
  • Aggregate post metrics + comment engagement + author's karma
  • Run any Game URLs through the storefront scrapers
• Update (this will probably need to run after the Twitter updates have ended because they need to be synced up in entirely different ways).
  • Any Developers marked as Reddit devs we will fetch the posts that they have made to the subreddits mentioned above in the last 7 days
  • Aggregate post metrics + comment engagement + author's karma

TODO implementation:

• These two updated columns now take usernames of both Reddit and Twitter users. Twitter usernames will be prefixed with "T" and Reddit usernames will be prefixed with "R". We need to double check the usages of these fields, as well as their DB column counterparts, to ensure that we are setting and using these fields properly after these changes
  • Check all usages of Game.Developers as well as its column: games.developers
  • Check all usages of Game.VerifiedDeveloperUsernames as well as its column: games.verified_developer_usernames
• We need to add new versions of the UserTweetTimes and DeveloperSnapshots cached fields for Reddit developers
• Update Discovery phase
  • Create TestDiscoveryPhase test
• Update Update phase
  • Update TestUpdatePhase test
• Update Snapshot phase
  • Update TestSnapshotPhase test
• Update Disable phase
  • Update TestDisablePhase test
• Update Enable phase
  • Update TestEnablePhase test
• Update Delete phase. Wasn't that hard to update as we don't need to distinguish between the different Developer types here
  • Update TestDeletePhase test
• Update Measure phase
  • Change wording in Measure emails regarding Twitter developers
    • Need to use RedditPostIDs sometimes instead of TweetIDs
  • New Reddit "From" label needed in linked Monday board + need to change Monday config to find where a game was found by looking at its developers
  • Add a test for the Measure phase
    • Need a new mapping config for tests + new Monday board
• Add the new Reddit metric fields in DeveloperSnapshot as weighted fields
  • RedditPublicMetrics
  • PostPublicMetrics
• SubredditFetch procedure should check rate limits for non-paginateable bindings
• Add new constants to README

TODO after implementation:

• Set developers.type column to "T" (for TwitterDeveloperType): update developers set type = 'T';
• Prefix each developer username in games.developers with "T" (for TwitterDeveloperType) for each Game in games: update games set (developers, verified_developer_usernames) = (array(select 'T' || unnest from unnest(developers)), array(select 'T' || unnest from unnest(verified_developer_usernames)));
• Same thing as the above for the games.verified_developer_usernames column
• Set developers.current_reddit_* to 0? Might not need to because we might check for nil anyway for RedditPublicMetrics Gorm always returns the empty embedded struct even if all columns in the struct are null in the DB
• Set developer_snapshots.reddit_user_* to 0? Might not need to because we might check for nil anyway for RedditPublicMetrics Gorm always returns the empty embedded struct even if all columns in the struct are null in the DB
• Set developer_snapshots.total_post_* to zero values? Might not need to because we might check for nil anyway for PostPublicMetrics Gorm always returns the empty embedded struct even if all columns in the struct are null in the DB
• Might need to set developer_snaphots.reddit_post_ids to its default
• Make sure the new config.json constants and Monday values are changed to their appropriate values
• Might need to increase constants.scout_timeout in config.json
• Need to run update computed fields procedure because of the changes to the weights to the weighted fields

To reset DB to state without Reddit developers:

Delete developers

delete from developers where id in (select r.id from (select id, ('R' || username) username from developers where type = 'R') r, games where r.username = ANY(games.developers) and cardinality(developers) = 1);

Remove Reddit usernames from games

update games set developers = array_remove(developers, r2.unnest) from (select * from (select unnest(developers) from games where 'R' = ANY(select left(unnest, 1) from unnest(developers))) r where left(r.unnest, 1) = 'R') r2 where r2.unnest = any(developers);

Delete any games with no verified/non-verified developers

delete from games where cardinality(developers) = 0 and cardinality(verified_developer_usernames) = 0;

Monday.com Item ID: #3806777653