My app gets minute by minute advertising data via API. Let’s say “Impressions” and displays it to the user so that they can see, say… impressions for their campaign over time. 30 days is common, but could be more… 6 months, 12 months etc.
I receive the data as a delta, so every minute the API just tells me how the impressions have changed -2, +1, +5, -1 etc.
I am considering two ways to display this data.. First, by essentially building an aggregation for this data hourly…
But second… by just recording it raw and letting the user drill down on the front end.
Will the second option make my app very slow?
If bubble needs to retrieve all of these 3x per minute pieces of data - (129k rows of data for just 30 days) and group and sort them and do math on them….. seems like it would be slow.
If I aggregate hourly, then that drops to 1500 rows of data bubble needs to crunch for 30 days of data.
Bubble is not good for storing analytics for long periods. Use something that’s made for this purpose and exposes and endpoint which you can call on demand based on filters
I hadn’t considered that. My experience with this is limited - any suggestions? Your comment makes me think of things like typesense - but I’m not sure if that’s right…
Are you on a dedicated server? If that were the case, then you could probably do this with the aggregation when you want to. Otherwise, it will most likely get very expensive. Especially once you start growing users. It might be ok to get started and create an MVP, which might be ok if you don’t have users lined up to use this yet. Sometimes you shouldn’t worry about things until it actually becomes a problem. It might never take off enough to be a problem.
Do you have a bunch or users lined up to use your app?
That means you are looking at maybe between $200-400 or more per month depending on how much workload that will be consuming. Subscription Planner | Bubble
Something to consider. Bubble can handle it most likely, but could you afford it? There are always ways to optimize WUs but this is just a really rough estimate for you to keep in mind. Does that help at all?
Yeah. I’m not sure if there are other cheaper alternatives to saving something in the database every minute. Bubble used to be the best option but since they moved to WUs it is much more expensive. Sometimes just pulling in the data using the API connector is a great way to save money. You can look into Xano and see their prices to see if they would be cheaper or not.
Group by and aggregates operators e.g count scale fine on large data sets and don’t require returning all the rows to the client so is fast and WU efficient.
You cannot, and should not, however, return 129,000 records to the front-end as it’ll be expensive, slow, and use a lot of data.
This is not 100% true. You can, if you want, return 129,000 records. But should you, or rather, do you need to? The real question would be do you need to. In your case, with Data Jedi, you do not need to. You can run this conversion in a backend workflow and return only the records that changed.
@ian11 I do not know what the data structure is of the API call, but based on what you wrote that I read, this statement is not necessarily applicable to your use case since your data count comes back as delta and not an aggregated count, requiring you to add or subtract those delta values to arrive at the ‘new’ values. I could be wrong, but that is how I read your use case.
No real need if you are using Data Jedi or some plugin that allows you to easily modify values on front end or backend. You can instead just take the incoming api call data, filter out all delta values of 0 and then just modify the values whose delta is greater than or less than 0.
How is the user going to actually interact with this. Is this some dashboard that is open all day long that they continuously view to have minute by minute updates or is this a dashboard they will go to view once a day or week to just check up on impressions?
Is the user needing ALL values, or can it be restricted to fetch from api only the values relevant to the current user?
Is the delta value going to be returned from the API such that if you do not request the data for 24 hours, will the delta be accurate based on changes since your last API request. Same concept, if you request every 10 minutes, is the delta value accurate. For me, the question is, if you request every 24 hours, does your delta value accurately assess how much has this value changed since the last request 24 hours previously. OR do you NEED to have it every minute in order for delta values to be accurate?
Is the 129K rows of data for 30 day period? Or would you have the same 129K rows of data over a 3 minute period? What I am wondering is what does this data from the API represent in real terms. If you have a ‘impressions’ with an unique ID to represent the impressions for a one minute interval on an advert with unique ID so we can say, call the API anytime, every 1 minute or every 24 hours, you will likely see the same unique ID of an advert listed more than once, with multiple unique IDs of the ‘impressions’ delta. So I fetch via API would I be able to filter by the ID of the advert to see all ‘updates’ of impression delta since my last api fetch request regardless of duration between fetches.
If you would see the same advert unique ID multiple times, each time having attached a unique id for impressions over the time span, that therefore has multiple delta values (+1, +4, -6), this would be easy to put into the arbitrary text operator in the screen shot below as a list of deltas because the + and - in front of each will result in the math returned properly.
Does you api provider allow for constraints, especially date constraints.
In order to really solve the query, we need to understand clearly, what is the data structure, how is the data returned, what constraints can be used when fetching the data, how is the data going to actually be used by the user.
When knowing all those very important details in coming up with an optimized approach (there are so many ways you can do one thing), you can land on the best solution based on your data and use case.
What a great response. I appreciate all of the advice. I think a lot of it was given under a misconception.
Right now, the data is streamed to by bubble database via API… this happens on the backend. So as each raw delta comes through, I am going to just write it to the bubble database as is.
So, I’m not fetching the data through the API in real time, it’s coming directly from the bubble database.
129k records represents approximately 3 records per minute over 30 days.
Users will check in on the data periodically, almost never need to see it minute by minute - but certainly hour by hour. They won’t check on it hour by hour but will want to go and see a chart of impressions for a Friday between 12 and 8 pm.
I’m going to dive very deeply into your response to make sure I understand it fully, and I am going to check out Data Jedi as well.
My concern was basically this…. Is it performative enough for me to, on the front end, basically do a search like this “impressions > grouped by hour > aggregate impressions count” - this would take the 129k records and make them into about 1500 groups.
Thank you for your help, You’ve offered a lot of advice and I am going to spend time going through it.
Yes, you can do it natively without plugins. Each record will need an ‘hour’ date field which is the date timestamp rounded down to the hour (this is because Bubble doesn’t have a group by hour, though that’s off the top of my head as I’m not at the editor right now). Then, you can group by exact date. There are for sure other ways of doing it too (as with every Bubble problem) but for a core functionality doing it natively will likely be wise
If your api data that returns Delta values (ie: -4,+5,0), you would first need to process that into an actual number and not leave it as a text. Once that is done, yes, you could use the group by operator on the full database of 129K records. This will result in a WU cost equivalent to returning 1500 things which is 22.5 WUs per search (0.015 WU per item return from database multiplied by 1500). Do that for a single user one time a day, and it is not going to cost you an arm and a leg.
The processing of the data, so as to convert delta value into a number value, and the saving of the data into the database is also going to cost WUs, and a developer should consider those costs as well.
So for each record that needs to be created, there is a create new thing action involved, and adding the values to that thing, you will be charged around 1.62 WUs just to create it. That over the period of 30 days for 129K records is going to be 208,980 WUs. Running a backend workflow to fetch the API call data every one minute, will cost WUs as well.
If you are doing this, you also have the costs of WUs for the recursive backend workflow you need to run to save each API response object as it’s own custom data type entry in the database, so if you loop over in one month 129K backend workflows, that is 0.7 WUs per scheduled backend workflow, so another 90,300 WUs.
Additionally, when working with Groupings, you lose all other data associated with the ‘thing’ unless you structure your grouping to have it part of the grouping, but that grouping is not the ‘thing’ any more, it is the grouping data, so you lose all other relevant data points from the thing unless you include them into your grouping. Usually grouping is used just to group by a number for aggregate counts and charts. But if you are going to be wanting to allow a user to look at a spreadsheet and perform filters and sorts on other values from the ‘thing’ grouping is not the correct choice of method for reducing the WU costs associated with returning large sets of data.
You could always display the grouping, have some related piece of information be part of the grouping and then run a search on the real ‘things’ using the grouping data as a constraint, but then, what is the point of using grouping for reducing WU costs in regards to fetching data?
Writing directly to the database as is, is not the right approach because the data from API is returned as a delta and not a number. Unless you first convert it to number via the dynamic expression operators to convert a text to number, but since some have a + sign, you also need to use regex to strip the + or - sign, but then you lose whether or not the delta indicated a negative, so you need to account for all of that as well to ensure your data you save to the database, could in fact be used properly for the group by, which would require it to have numbers in order to do the aggregate count properly. That is just another consideration into the complexity required to do the group by approach.
I am not sure how your data is returned from the API, I still have all the same questions from my original post about that data. Specifically, can you fetch via API by constraining dates and get just one single Delta value for one ‘advert’ during that time span? Would running the API call, instead of every single one minute, just when the user requests the information, get you an ability to accurately return to them the number of impressions during that time span?
Basically, I could use my plugin for this situation in multiple ways, and the correct approach to use depends a lot on the structure of the data you receive from the API.
One approach could be, when API call is run as an action in backend workflow I can take those values, manipulate them with a single server side action using the plugin, then, store them directly into the database as a list field on a custom data type. That custom data type would have a field for date/time.
Then in the UI, I can allow a user to filter and sort by dates, and return FULL OBJECTS will ALL DATA POINTS (different from groupings) for those constrained periods of time using a search for of the custom data type that holds the list field of objects and date/time field. Then I can display those api objects from the list fields into any UI elements I want. For example, if I want to display a chart, I will group the api objects data as required for charts, as well as have a complete spreadsheet to show all other details of the data.
What that would do is cost me the 0.5 WU per second the backend workflow runs to process the data so that the Delta returned from the API is a number, plus the creation of just one single new custom data type (1.62 WUs). Then when you search, you will be charged 0.015 WU for each custom data type record returned, but still have the COMPLETE SET OF DATA of objects.
So for example, if you save into custom data type every one hour, you will have for one day, 24 custom data type entries, and if you constrain by one full week as a user might, that would be 168 entries, so around 2.52 WUs per search like that.
So to summarize, using the Grouped by Method will restrict your UI and feature sets regarding what a user can see and filter by, so that is not good, but may not be applicable as all you might need to show is the number, but you also need to deal with the complexity of your api data being a delta and not a number. With an approach using the Plugin and the Hybrid Data Structure, you do not need to deal with any restrictions on UI as ALL the data will be there based on the filter dates the user selects.
The Group by method will cost you if you want to return for an entire 1 month period, consolidated from 129K records, to 1500 groupings 22.5 WUs, but with Hybrid Data Structure approach you would fetch 720 custom data type records for 10.8 WUs and still have ALL the data points. This cost is per view per user. It can add up quick.
The group by method will cost you to save the data around 299.280 over the course of the month having to run the recursive backend workflow to save each api response as an individual custom data type entry. But using the Hybrid Data Structure approach that would be 1,166 WUs (saving to custom data type the date field and list of objects field), 504 WUs for scheduling of backend workflows (24 per day, 30 days a week at a cost of 0.7 WUs per run), and then the 0.5 WUs per second processing the server side action from the plugin (I have not tested your use case but will venture to guess it will be processed in under 5 seconds if not 2, so lets overestimate and say 10 seconds per time) so maybe 720 - 3,600 per month.
Totals:
Grouped by per month with just one fetch per month = 299,302.5
Hybrid Data Structure = 2,400 - 5,280
Hybrid Data Structure approach costs you anywhere from 0.008 - 0.0176 (that is not even 2%) what the groped by method would cost over a one month period…and yes, no limitations for UI as ALL DATA POINTS are available.
I do not believe @georgecollier consider the implications of saving the data and running the backend workflows? Perhaps he had and has another method of saving it than I imagined?
The data is not fetched from the API. It’s streamed to a bubble webhook.
Here is the exact setup:
My app subscribes to a marketing data stream
The marketing platform then dumps marketing data into an SQS Queue in AWS
A lambda function listens for new messages in the SQS Queue and then sends the message to bubble via a webhook
In bubble, when the data is received via webhook, I create a new “Thing” <<<< This last step is the one I am debating.
Wondering if I should keep every raw record and aggregate front end, or just aggregate hourly on the back end as it comes in. Here is an example payload:
Based on what you’ve said so far, it sounds like your app will be useful for getting data via an API call from outside of bubble - which may be a great solution if I were to say, redirect my lambda function to drop the data into an AWS database or something like that, rather then bubble back end, and then use your plugin to retrieve that data with an API call.
For me, it is just semantics. Whether it is ‘fetched’ or ‘streamed’ it is coming from an API, so nothing else about my comments is different. The only difference is whether you are in charge of how and when you request and get the information from the data provider (ie: fetched) or whether the data provider is in charge of when you get the information sent to you (ie: streamed).
For me, I think of it like this. If you call a restaurant for a food order that you will eat at home, the food is cooked by the restaurant not you (like getting data from a 3rd party source - like a data provider). You have two choices of how the restaurant will provide that food so you can eat it (ie: save to your database), you can drive to the restaurant and pick it up (ie: fetch via api) or you can have them deliver it to you (ie; stream via webhook)
Thanks for sharing that example payload, makes everything clear now what you should do.
Looks like ‘impressions’ is not a Delta (ie; +3 or -4) and instead is just a raw number (ie: 3)
That is great, that makes things easier.
Looks like from the example, you only get one ‘thing’ per webhook, and not a list. So you will not receive a webhook with multiple advertiser_ids and marketplace_ids per webhook, and instead it will just be one ‘object’ like your example. That is easier to work with.
I think a misunderstanding, or at least I think about it a bit differently. A webhook, is for me, the data provider calling you to alert you to new information. An API call is you calling the data provider to ask if there is any new information. Since you are working with a webhook, for me, that is exactly the same concept as running a API call, the only difference in my view is how and when does that data get sent to Bubble. If via an API call to an outside data provider or via a webhook from an outside data provider, its the same issue, you are getting data sent to your bubble app from an outside provider, so everything else is the same.
So, you can do the group by and have the grouping data (lose easy access to the keyword_text or currency values - only show aggregate counts) which will allow you to return from bubble DB for less WUs (@georgecollier is correct that it lowers WU costs for fetching data compared to normal ‘do a search for’)…the WU costs of saving the data into your Bubble database are no different since the webhook is streaming the data whenever it wants and is sending just one object at at time, so you’ll be faced with the same WU costs of saving to the database with either approach, and do not need to do any recursive backend to save each returned object, because your webhook only sends one object at a time.
If I were to be building this for my own business, I would change how I get the data and instead of letting the webhooks run, I’d do API requests to reduce my WUs, but I personally, just like saving WUs everywhere I can so I have more resources to allocate to other feature sets.
Does that help at all?
