How to create a time series with the total of a column that changes every day


I am trying to create a time series in which I show how a values changes over time (the ID columns moves from unverified to verified basically each day). I would need to create a dimension that updates everyday with the total number of verified IDs and unverified IDs and their date…any ideas how to do that? I want to see how the number changes over time but in the current dataset, there is no date field with when the change from unverified to verified occurs. I also need a way to lock in the total value for each column each day, to be part of the time series.


This sounded easy until


I think this would be hard to build 100% in Looker without any kind of ETL. You’re talking about creating a snapshot of the data, but it isn’t something that could be done retroactively since there isn’t a date column to associate it with— So every day, you’d need to snapshot and tag the data. I wonder if you could do this with some kind of incremental PDT:

Where every day, it inserts the results of a super simple query ( SELECT CURRENT_DATE(),ID,verification_status). (This is kind of ETL, I suppose)

You could then build filtered counts to calculate the number of verified/unverified IDs by date.

Interested to hear what you wind up with. It’s possible my incremental PDT idea is overcomplicating things, but it’d be a cool solution!