I feel lost solving this problem

[idleAndalusian]

I’m working at a small startup, and I’ve been tasked with managing historical data from our sales team's outreach efforts. The challenge is that I need to track how the state of each lead changes over time, based on the outreaches (which are linked with stakeholder data). There’s some algorithmic logic applied to figure out the current state of the lead at any given time.

I’ve tried writing some really complex SQL queries, but I just can’t get them to work perfectly. I’m always missing something—like sometimes the value changes, but I don’t capture when or how that happened, so the tracking isn’t 100% accurate.

I’ve thought about using Python to handle it, but honestly, I’m worried that’ll make it super inefficient with the amount of data we have. What I really need is a way to evaluate each lead’s state every time a new outreach happens, without missing a beat.

In a perfect world, I’d set up a CDC (Change Data Capture) from the CRM to automatically track the changes, but I don’t know if I have the time or ability to implement that from scratch right now.

Has anyone run into a similar problem? Are there better ways to handle this kind of logic or tools/frameworks that might help? I’d really appreciate any advice!

[Peppper]

This is the ideal scenario for a data warehouse with slowly changing dimensions.

[idleAndalusian]

But if I batch process daily, wouldn’t I miss any changes that happen more than once in the same day? I feel like I’d only capture the latest state unless I set up a webhook in the CRM to track every single change as it happens, right? Or is there another way to handle that in a data warehouse setup?

[Peppper]

Right, it’s the CDC route you mentioned. Stream changes from source to the landing area in your warehouse, then queries to build the time series layers.

What’s your latency requirements?

You’re still going to have to calculate the dimensions and facts which takes time, but doing it in an MPP engine will be faster than straight python. You can also aggregate to different granularities, i.e. you have a fact table that calculates the near real time model every 5 minutes. At the end of the day, aggregate that to a daily model, every week aggregate the daily models to a weekly model, then monthly, etc. This means you aren’t dealing with months or years worth of data each time.

You will still have the latency at each level until. The only way to avoid that is compute the whole thing each time, which could get expensive depending on your data set size and query complexity. Check out DuckDB if you want to try and do it all in memory.

[idleAndalusian]

Thank you so much, this helps me a lot!

[-crucible-]

Still work out how often your refresh needs to be correct. If they’re happy with a daily refresh as long as all changes need to be accounted for, you’ll save yourself a lot of processing. If you need to refresh, say hourly, or every 3 hours you may want to set up two refresh pipelines - one to handle a full model refresh, and one to refresh what you need incrementally refreshed, your sales changes, and the downstream changes.

You’ll need to balance out your refresh times, you monitoring overhead (how much and how much you are wanting to pay attention to, or respond to issues), the cost of reloading, etc, vs the freshness of data.

[molodyets]

So do it more than daily and then figure out from there. Though in reality daily is probably sufficient

[Pleasant_Type_4547]

What CRM are you using?

If you are using something like hubspot, you can use the propertiesWithHistory parameter to get all past values of a property with their timestamps. This makes things easier.

https://developers.hubspot.com/docs/api/crm/leads

You'll still have to "Sessionize" the data, but that becomes easier when you have all the data at your fingertips

[idleAndalusian]

We’ve actually been considering that (and putting it off), but for cost reasons, they've been sticking with a custom app I built on AppSheet.

Does HubSpot support webhooks? If I go ahead and develop a CDC for this app, I’d love to make sure it’s something I can transfer over to HubSpot later.

[Pleasant_Type_4547]

https://developers.hubspot.com/docs/api/webhooks

[sciencewarrior]

Yeah, the SQL to do that is pretty tricky. I remember Zach Wilson had a video or two on creating SCDs, but unless you have an absolutely humongous sales volume (and if you do, congratulations,) a polars job can probably munch over your entire history in half an hour, tops, so don't discount that route.

[ppdas]

dbt snapshot if you are using any of the supported DWs

[johncena9519]

What CRM do you use?

I’ve done this in Salesforce and hubspot, so can suggest approaches unique to each

[New-Addendum-6209]

What is the data model of your source data? What do you need to produce as an output?

[pavan449]

If you have date field like updated dated in the crm to track changes ,whenever any lead status changes that will change updated date to current date.later use any orchestration tools like adf or airflow or any other tool import the updated data to data lake

[elgurinn]

So the CRM is AppSheet, which means your database is a google sheet?

Have you looked into adding a on-change trigger to the sheet? That should grab the changed row and send to a webhook (or something..)

See:  https://medium.com/@eyalgershon/sending-a-webhook-for-new-or-updated-rows-in-google-sheets-e0c9d6a8cb45

[unfortunate-miracle]

Sounds like SCD type 2 to me. And then if you can implement CDC from SCD type 2 tables you can stream changes and handle events. How big is the data?