Best way to create a time series to track Twitter metrics over time?

Need help Database
1

Hello Bubblers!

I’m building a web app that uses Twitter’s API. Through the API, I can fetch public metrics for any Twitter user. Most importantly, I can see the user’s current number of followers, number of follows, and number of tweets.

I wish to track the development of these metrics over time for a selected number of users that I have added to my database with a field for their corresponding Twitter ID). To do this, I need to store these metrics in my database, which brings me to my two questions:

  1. What database structure would you choose to store this data? (see my attempt below)
  2. I’m on the Personal plan, so I can only run recurring workflows on a monthly basis. Ideally, I would like to fetch this data more often than that. Apart from upgrading to the Professional plan, is my only option to make manual updates every X days? How would you approach this?

My database structure
Data type: Time Series
Fields:

  • Timestamp (type: date)
  • Followers (type: number)
  • Following (type: number)
  • Tweets (type: number)
  • Twitter ID (type: text)

I would then add a list of Time Series to each of my users so that I can Do a search for Time Series that matches the desired user.

Thanks a ton in advance! :pray:

2

Looks good except don’t store a list on the User because the list is going to get massive. Instead just do a User field on your Time Series, and to get a certain user’s metrics you Do a search for Time Series with a constraint User = [your user]

Don’t use recurring events, just make a regular API workflow and at the end of the workflow have it schedule itself for Current date/time + days (1):rounded down to date so it runs it again at 12AM the next day. Then you make a button somewhere to start the 1st one manually. I do recommend some kind of conditional to stop the infinite loop if needed

1 Like
3

Thanks for your reply Tyler! Your second point on how to set up a recursive API Workflow is really clever :smiley:

Regarding your comment about not storing it as a list on the User, is the reason that it will slow down other operations for that user (i.e. check if a user is logged in, find the username of a user)? Any other reason?

4

Yes, imagine if you wanted to get last months analytics, storing a list would mean you do User’s Time Serieses:filtered (with some date constraints) now your client has to download the whole list then filter out what you want. You can imagine with thousands of days of records this would get slower and slower.

VS. using search and constraints the server can find the exact items that fit the date constraints and deliver that to the client instantly

1 Like
5

That makes sense – thanks for explaining!