James Scott (jamesscott@shield-legal.com)
2025-02-24 12:33:58

@Ryan

James Scott (jamesscott@shield-legal.com)
2025-02-24 12:34:07

is this the table u want? SELECT ** FROM tort-intake-professionals.Financial_Log_Dataset.pctid_with_lr_casetype_and_leadspedia_contract_data LIMIT 1000

James Scott (jamesscott@shield-legal.com)
2025-02-24 12:34:10

here is there mroe fields

Ryan (ryan@themedialab.agency)
2025-02-24 12:37:46

@James Scott, send us schema here or link to see that table...

James Scott (jamesscott@shield-legal.com)
2025-02-24 12:44:31

@Ryan what more needs to be added to this.

Ryan (ryan@themedialab.agency)
2025-02-24 12:46:04

This is most of it, but we need to JOIN to the iolrcasetyperevenuerates and iolrstatusrevenuerates tables in BigQuery so we can add the AND clause to check the billable active flag where we join on the LR CaseType IDs from this PCTID table.

Ryan (ryan@themedialab.agency)
2025-02-24 12:46:10

I can explain more in our meeting today.

James Scott (jamesscott@shield-legal.com)
2025-02-24 12:46:30

gotcha thats what i needed

Ryan (ryan@themedialab.agency)
2025-02-24 12:47:42

Check with James Turner that the data values in the iolrstatusrevenuerates is ready, the casetypes is in production with Malissa Phillips keeping it accurate for prices, effective dates, etc...

Ryan (ryan@themedialab.agency)
2025-02-24 12:48:36

So when Leadspedia Sold Leads is JOINED to this TIP side where the pctid.status is "billable" from the rev_rates tables, then we have our Shield Legal financials GOLD!

James Scott (jamesscott@shield-legal.com)
2025-02-24 12:50:34

yes him and i been talking

Ryan (ryan@themedialab.agency)
2025-02-24 12:50:41

Great

Ryan (ryan@themedialab.agency)
2025-02-24 12:50:47

So close! 🙂