Forum Academy Marketplace Showcase Pricing Features

Line Graph of user sign ups

Need help Database
#1

I’m having trouble with something I hope has an easy an obvious solution. I’m trying to show Active Subscribers over time. My data is structured such that each Subscriber has a status: Active, Cancelled, Hold, and there are dates for each state: Sign-up Date, Cancel Date, Hold Start, Hold End

I can get the correct number of Active Subscribers on a given date by merging several searches with different conditions, but I’m at a loss for how to show the total number of active subscribers over time, given that a subscriber with a Cancelled status was, at a past date, Active. Showing how many signed up, cancelled, went on hold, or came off hold over time is easy, but how to aggregate the Subscribers over time?

#2

I don’t think you are going to be able to do it without capturing more information to report on. I think if I understand correctly, what you will need to do his also have a “change history” table. This table will hold when a User change status, eg signed up. Then you could have a query that went “How many change history events are changed to ‘signed up’ within a time interval for unique users”. And also report on all the other changes, also do a group by and sweep up all the status changes counts in one query.

About Me

I’m a Bubble Freelancer. I like Bubble coaching and making plugins for tricky API’s.

My Bubble plugins Pleased to say all 5* reviews.
1 to 1 coaching with nocodeguides.io DM me for a free voucher code if you want a quick chat.
My Youtube Channel Mostly about my plugins.

My Freelancing Website

@lindsay_knowcode

#3

Yes, that’s what I was afraid of… I can accomplish this with custom states to a degree, but it’s slow since I need a new custom state for every date within a date range. Definitely not ideal. I will keep playing with it. Thanks!

1 Like
#4

I was able to solve this using the Toolbox plugin (Expression) and some javascript. Here’s what my Javascript looks like:

var array1 = [Search for Contacts:grouped by signup-date's count],
array2 = [Search for Contacts:grouped by cancel-date's count],
l = Math.max(array1.length, array2.length);
var step1 = Array(l);
for(var i=0; i<l; ++i) step1[i] = (array1[i] || 0) - (array2[i] || 0) ;

var step2 = step1.reduce(function(r, a) {
  if (r.length > 0)
    a += r[r.length - 1];
  r.push(a);
  return r;
}, []);

var step3 = Array();
for(var x=0; x<l; ++x) step3[x] = (step2[x] || 0) + Search for Contacts merged with Search for 
Contacts merged with Search for Contacts:count;

step3;

I’m not too familiar with Javascript (I copied this from StackOverflow and tweaked a bit), but here’s the gist of how it works.

First, define an array1 as a list of COUNTS OF contacts who signed up between two dates. It’s important that this be a list of NUMBERS.

Then, define array2 as a list of COUNTS OF contacts who cancelled between two dates.

step1 takes each item in array1 and subtracts from it the number in array2 and saves it into a new array called step1. You could perform any math operation here that makes sense for you, and you could easily do it between more than 2 arrays by copying the above syntax. For example, you could do array1+array2/array3-array4 if that made sense for your use case. I haven’t experimented on different-sized lists. I’m not sure it would work if the lists were different sizes.

Then, step2 performs a cumulative sum of every item in step1. So if step1 was [4,5,3,1], step2 would be [4,9,12,13]

Finally, step3 just takes each number in step2 and adds to it the ‘beginning balance’. In my case, it’s a count of active users prior to the start date. The entire function then returns the values in the new list ‘step3’

I then use a chart element to make the categories the list of dates, and the values are the output from the expression described above and voila, you have a graph showing the cumulative change over time.

Hopefully this is helpful to anyone else trying to solve for this (or similar) problems.

1 Like