Count clicks during time range for impressions during time range?

TROUBLESHOOTING Ingestion
#1

I have clicks and impressions. Click has a timestamp, impression has a timestamp.
I want to count:

Clicks whose timestamp is {start_date} < click_timestamp < {end_date} and who’s related impression is also {start_date} < impression_timestamp < {end_date}

{start_date} and {end_date} are dynamic and given at query time.

DataModel1 - Single data source for clicks and impressions:
If my data model has rows for clicks and rows for impressions, example:
imp, id, timestamp
click, id, timestamp
click, id, timestamp

then I would be able to first filter everything by {start_date} < __time < {end_date}

DataModel 2 - impressions datasource that contains clicks aggregations:
However, if my data model is only the impression rows with their relevant aggregations:

id, timestamp, num_clicks

Then I lost the timestamp of the click, so it’s possible that the click happened after {end_date} and I will still count it, though I didn’t want to.

However, I think DataModel2 is superior because it’s much simpler and from what I read it’s more standard, is there a way to solve this request with DataModel2?

#2

It seems like in order for data model 2 to work it would need to have the {start_date} {end_date} parameters ahead of time, at the time of ingestion which doesn’t seem viable. I like #1 because it models both impressions and clicks as events which should give you more flexibility of analysis and allow for real-time analytics as the impressions and clicks occur.

1 Like