James Turner (jturner@shield-legal.com)
2025-06-25 13:05:57

Apologies for the confusion on that- When we (Shield / TIP) originally started working with Lawruler, Leadspedia and Five9, the data team made a fuss about wanting to own those pipelines ourselves so we have it for things like this, but we were pushed to have lawruler own, and maintain it.

Since then, we have had enough difficulties with Lawruler that they have become cagey about giving out any information on what they maintain so it's more difficult to use other sources.

Here is the Leadspedia API docs from what I found, and I'm setting up a meeting now between us so I can help break down what we requested lawruler build awhile back

https://developer.leadspedia.com/docs/core-api/12dab89109ded-core-api

developer.leadspedia.com
Core API | Core API
## Welcome LeadsPedia Core API reference Powered by Stoplight.
Original URL: https://developer.leadspedia.com/docs/core-api/12dab89109ded-core-api
deleted-U08T4FKSC04
2025-06-25 13:41:30

Hey James, no worries. Thanks for the clarification.

I think if we're able to determine which fields lawruler pulls from leadspedia, we won't need their help in understanding how they do it.

You already pull lots of data from leadpedia into BigQuery, it shouldn't be too complicated pulling a smaller set of this data into whatever replaces lwaruler.

Same goes for five9

👍 James Turner
deleted-U08T4FKSC04
2025-06-25 13:59:06

There was a discussion about PII data not being store in lawruler. Correct me if I'm wrong, but when I join these two tables: lr_data.case_question and lr_data.lead_question, I can see the answers given by leads to very private questions. Is that correct or that discussion was about something else?

James Turner (jturner@shield-legal.com)
2025-06-25 14:09:44

Great question!

PII is 100% being stored in Lawruler, and also being stored in Bigquery and other database sources.

Specifically what we do not store in Lawruler is Medical documentation.

We may keep records in the sense of "I spoke to a doctor on this day for this reason" but the actual medical documents we get from clients and their doctors, do not go through Lawruler.

We have a 'Medical Records' team that handles obtaining those documents, and sends those directly to the Law Firms medical records teams. I can speak to the Medical Records team leaders and find out how they usually get those over but I believe it is through email, or access to the lawfirms Onedrive

deleted-U08T4FKSC04
2025-06-25 14:10:36

Ooh, ok, yes, that makes sense now. Thanks

James Turner (jturner@shield-legal.com)
2025-06-25 14:15:07

Just spoke to the Medical Records team~ What they do is use the questions you see in Lawruler, to reach out to doctors / military documentation leaders / ect- and request those documents.

In most circumstances, we tell them. "We need these documents, send them to X address where X is the law firm requesting them"

In some cases, they send those documents to us via physical mail, and we fax / physical mail those over to the law firms.

James Turner (jturner@shield-legal.com)
2025-06-25 14:17:44

and in more rare circumstances they will sometimes email us the documents, and we forward the email to the law firms

deleted-U08T4FKSC04
2025-06-25 14:20:55

Ok, so in most cases neither LawRuler or ShieldLegal have access to this info

James Turner (jturner@shield-legal.com)
2025-06-25 14:22:10

To medical documentation, yes correct!

deleted-U08T4FKSC04
2025-06-25 14:32:25

On a different topic, is there another gcp project at ShieldLegal? Maybe something called gcp-integrations or integrations-tip. I can see there is a github repo with that name that sends data to law firms. Would be nice to understand what data is sent and how it's generated

James Turner (jturner@shield-legal.com)
2025-06-25 14:41:02

I don't believe there is a GCP project- with that being said, but let me poke around. I believe that data is pulled from Lr-data and sent via cloud functions and python scripts made by Dustin Surwill.

James Turner (jturner@shield-legal.com)
2025-06-25 14:42:22

I see that Dustin has a five9-call-report under the TIP Cloud Functions and I believe that is what pulls all the data from five9

James Turner (jturner@shield-legal.com)
2025-06-25 14:43:15

and it looks like the leadspedia into Bigquery api pull is handled by functions on the shield legal bi side

image.png
deleted-U08T4FKSC04
2025-06-25 14:46:22

I mean the functions from this repo: https://github.com/shield-legal/gcp-integrations/blob/main/common/cloudpostgres.py

They seem to be deployed at a GCP project integrations-tip

James Turner (jturner@shield-legal.com)
2025-06-25 14:46:44

I'll reach out to dustin and get a more solid answer

deleted-U08T4FKSC04
2025-06-25 14:48:59

Thanks 🙏

James Turner (jturner@shield-legal.com)
2025-06-25 15:57:40

Just spoke with Dustin, it looks like there is a GCP project for integrations- I was not aware because I did not have access to it lol

😂 deleted-U08T4FKSC04
deleted-U08T4FKSC04
2025-06-25 16:46:00

*Thread Reply:* Can we get read access to that project?

Also to some BQ datasets on TIP project. When I try to see their tables, it’s empty. I suspect it’s a permission problem.

James Turner (jturner@shield-legal.com)
2025-06-25 16:07:10

Oh by the way, what is your time zone? I want to be courteous as I can in setting up meetings between you, Felipe and I.

deleted-U08T4FKSC04
2025-06-25 16:46:23

*Thread Reply:* I’m on GMT-3 (Brazil)

James Turner (jturner@shield-legal.com)
2025-06-25 16:55:13

It would not surprise me if the tables were empty because Ryan may have cleared them on accident, or set them up and never used them- but if you let me know the tables I can check on my end.

I have requested Dustin Give read access as he admins the integrations one, and I'll invite him to the meeting tomorrow

deleted-U08T4FKSC04
2025-06-25 16:56:45

Thanks for checking. Tomorrow I’ll send you a list of the datasets I don’t have access to.

Have you set up the meeting? I need to see if I’ll have any conflicts

James Turner (jturner@shield-legal.com)
2025-06-25 16:57:19

Not yet- I was going to set it up for 11am PST, 3pm BRT- does that work for you?

deleted-U08T4FKSC04
2025-06-25 16:59:18

Do you have availability to set up 30 min earlier?

deleted-U08T4FKSC04
2025-06-25 16:59:34

I’ll have another appointment at 11:30

James Turner (jturner@shield-legal.com)
2025-06-25 17:00:36

Dustin is busy then but I can do 12 noon PST , 4pm BRT?

deleted-U08T4FKSC04
2025-06-25 17:01:19

Let’s keep it at 11am, I’ll work around

James Turner (jturner@shield-legal.com)
2025-06-25 17:01:49

I appreciate- I'll set up a agenda so we can move quickly

deleted-U08T4FKSC04
2025-06-25 17:07:16

Amazing! Thanks a lot

deleted-U08T4FKSC04
2025-06-27 14:26:21

Hey James, yeterday's call was great, thanks for showing how things work in so many detail.

I still have a few questions that maybe you can help address:

BQ dataset permissions:

  • these are some of the datasets I think we might not have permission to query the tables:
    • shield-legal-bi project: awslegacydashboard, awslegacypublic, copper, fivetranseawardinspirestaging, fivetrantest, gcptipbigquerylegacytipstaging, googleads, leadspediaallcontracts, shieldadsofflineevents, TipCommission.
    • tort-intake-professionals project: awslegagcypublic, cnx1 (multiple), five9measures, fivetran** (multiple), lrcallreport, lrcasetypes, lrlayouts, lrstatuses, shieldbibigquery.

Integration from LAWRULER -> LAW FIRMS

  • I've seen that there is a process getting unread emails on a schedule to generate the intakes for leads/case_types and then send them to the law firms.
  • Which process sends the emails to intakes@tortintakeprofessionals.com? Is it leadspedia?

TikTok integration

  • I noticed there is a function on GCP that sends information to tiktok business API, something to do with CRM. Can I have more context on this?
    • What triggers this function and why it's needed

As for the following, I know that DBT and Fivetran is controlled by Ryan, but can you ping him about it when he's back?

DBT models

  • can we have access to the models to check if there are any other important lawruler fields being used?

Fivetran ETL

  • can we have access to understand from where it's pulling/pushing data?
James Turner (jturner@shield-legal.com)
2025-06-27 14:36:10

Hey there~ Happy to do it! I'm booked out solid for about the next hour and a half so I will get you the full detailed answers / fixes later, but I have read your message, and started putting a new gears in motion to get you answers for when I become available.

A lot of these are all Ryan owned( shield-legal-bi project, all AWS, Fivetran, and DBT data sets ) but I'll ping him and luckily he comes back Monday, so I'm confident we can make quick work on getting you access to those early next week.

For the integration from Lawruler to Law firms, that is going to be with our integrations teams, think Dustin and Nick Mcfadden. I'll request they take a dive in on those. If you have exact questions reguarding it past what you have here, please let me know!

For Tiktok, that is all Nick Mcfadden. Same thing goes, I'll reach out and if you have more questions please throw em my way and I'll pass forward.

deleted-U08T4FKSC04
2025-06-27 14:37:44

Thanks a lot! And no worries this can be 100% async.

James Turner (jturner@shield-legal.com)
2025-06-27 14:42:59

By the way- I spoke to Felipe today, and it came to my attention that your team was never given the "Original current process" flowcharts which provide a better look at what we are currently actually doing, rather than where we would like to be.

These are the original builds of our current processes (As of about 3 months ago) The new flowcharts you saw had requested changes from Ryan + Cam to show where we would like to be, but aren't necessarily where we are now. I passed them off to Felipe but think these should be useful!

James Turner (jturner@shield-legal.com)
2025-06-27 14:43:05
Shield Legal and TIP - Current Processes.zip
deleted-U08T4FKSC04
2025-06-27 15:09:24

*Thread Reply:* Amazing, could find lots of things we covered yuesterday in here. Thanks

James Turner (jturner@shield-legal.com)
2025-06-27 14:43:41

By the way, is his name pronounced "Fil-eep-ay" or "Fill-Eep"? I am worried I've been saying it wrong

deleted-U08T4FKSC04
2025-06-27 14:49:42

I think “Fil-eep-ay” is closer to the right pronunciation.

Not sure how I’d write the last syllable pronunciation. It would be the sound of “ay” but without the “y”. Like a truncated “ay” if that makes any sense 😂

James Turner (jturner@shield-legal.com)
2025-06-27 14:50:13

Lol I think I understand- I appreciate it!

deleted-U08T4FKSC04
2025-06-27 15:09:46

*Thread Reply:* I just hope I'm not pronunciating his name incorrectly lol

James Turner (jturner@shield-legal.com)
2025-06-27 14:50:27

Lol I think I understand- I appreciate it!

James Turner (jturner@shield-legal.com)
2025-06-30 11:50:16

Hey good morning man! I forwarded your message to ryan, gave him your email. He said he gave you access and requested I forward this over.

Ryan Vaspra (https://themedialaboratory.slack.com/team/UJF2W0EB1)
<p>@James Turner, let Murilo know I have given him access to BQ, DBT and FiveTran for both SL and TIP BI warehouses. He needs to talk to Dustin about LawRuler integrations and data warehouse he puts into PostGres that is then mirrored over to BigQuery.</p> <p>Here are the only two users we have on the old PostGres systems,, he MUST be careful, these logins run everything:</p> <p><strong>TIP Call Center</strong> <strong>AWS Lightsaid Postgres DB PG Admin or DB client</strong> user: dbmasteruser<br /> pwd: Q<code>7B2(R8d</code>;m`n}l%kj2M7q^S&amp;:}1z,F<br /> dbname: tip_master<br /> port: 5432<br /> URI: <a href="http://ls-ee79dd780a352064468e4dc5bfef3cbc5675526f.cezd6k0feuf4.us-west-2.rds.amazonaws.com">ls-ee79dd780a352064468e4dc5bfef3cbc5675526f.cezd6k0feuf4.us-west-2.rds.amazonaws.com</a></p> <p><strong>SL Marketing System</strong> <strong>AWS Lightsail`</strong> user<strong>:</strong> dbmasteruser<br /> pwd: k(h~$(5!Lig{nX7Qg8PDAJx,.z0#y&amp;&gt;c<br /> dbname: dbmaster<br /> port: 5432<br /> URI: <a href="http://ls-48c3a8e572feb82920362771ec6c47370fa262da.cezd6k0feuf4.us-west-2.rds.amazonaws.com">ls-48c3a8e572feb82920362771ec6c47370fa262da.cezd6k0feuf4.us-west-2.rds.amazonaws.com</a></p>
Slack Conversation
James Turner (jturner@shield-legal.com)
2025-06-30 11:53:48

Hey good morning man! I forwarded your message to ryan, gave him your email. He said he gave you access and requested I forward this over.

Ryan Vaspra (https://themedialaboratory.slack.com/team/UJF2W0EB1)
<p>@James Turner, let Murilo know I have given him access to BQ, DBT and FiveTran for both SL and TIP BI warehouses. He needs to talk to Dustin about LawRuler integrations and data warehouse he puts into PostGres that is then mirrored over to BigQuery.</p> <p>Here are the only two users we have on the old PostGres systems,, he MUST be careful, these logins run everything:</p> <p><strong>TIP Call Center</strong> <strong>AWS Lightsaid Postgres DB PG Admin or DB client</strong> user: dbmasteruser<br /> pwd: Q<code>7B2(R8d</code>;m`n}l%kj2M7q^S&amp;:}1z,F<br /> dbname: tip_master<br /> port: 5432<br /> URI: <a href="http://ls-ee79dd780a352064468e4dc5bfef3cbc5675526f.cezd6k0feuf4.us-west-2.rds.amazonaws.com">ls-ee79dd780a352064468e4dc5bfef3cbc5675526f.cezd6k0feuf4.us-west-2.rds.amazonaws.com</a></p> <p><strong>SL Marketing System</strong> <strong>AWS Lightsail`</strong> user<strong>:</strong> dbmasteruser<br /> pwd: k(h~$(5!Lig{nX7Qg8PDAJx,.z0#y&amp;&gt;c<br /> dbname: dbmaster<br /> port: 5432<br /> URI: <a href="http://ls-48c3a8e572feb82920362771ec6c47370fa262da.cezd6k0feuf4.us-west-2.rds.amazonaws.com">ls-48c3a8e572feb82920362771ec6c47370fa262da.cezd6k0feuf4.us-west-2.rds.amazonaws.com</a></p>
Slack Conversation
deleted-U08T4FKSC04
2025-06-30 11:55:48

Hey James, thanks! Not sure if I’ll need to login into Postgres with these users. Do you know what he means by “old Postgres systems”?

James Turner (jturner@shield-legal.com)
2025-06-30 12:00:06

I believe what he means by 'Old PostGres' is this is the old system for company lead tracking and financials.

Ryan has a special way of calling just about everything in the systems.

I haven't ever touched any of his PostgreSQL or AWS systems myself, so I can't speak to why he set any of it up the way he did, or didn't make you a read only account instead of sending over the only admin log in. My recommendation would be to try to pin down EXACTLY what you're looking for, and ask Ryan in a group meeting with one of your leaders present to keep it all on track.

James Turner (jturner@shield-legal.com)
2025-06-30 12:01:31

I believe what he means by 'Old PostGres' is this is the old system for company lead tracking and financials.

Ryan has a special way of calling just about everything in the systems.

I haven't ever touched any of his PostgreSQL or AWS systems myself, so I can't speak to why he set any of it up the way he did, or didn't make you a read only account instead of sending over the only admin log in. My recommendation would be to try to pin down EXACTLY what you're looking for, and ask Ryan in a group meeting with one of your leaders present to keep it all on track.

deleted-U08T4FKSC04
2025-06-30 12:03:41

Thanks. Makes sense.

In a discovery phase like this it’s a bit hard to pin point exactly what I need but I’ll try to be more specific.

When you say old system does that mean it’s not in use anymore?

deleted-U08T4FKSC04
2025-06-30 12:04:06

Or that you’re building stuff to replace it to some extent?

James Turner (jturner@shield-legal.com)
2025-06-30 12:06:02

I am building stuff to replace it, and my system to replace it is mostly all done, just in testing, tweak, and rollout phase

James Turner (jturner@shield-legal.com)
2025-06-30 12:07:16

From what I understand, the PCTIDv2, and tort-intake-professionals.FinancialLogDataset buckets on GCP that I made, were all to replace what is in there now

James Turner (jturner@shield-legal.com)
2025-06-30 12:07:48

But it is still in use on dashboards and as a way of testing metrics for my system (NewFin)

deleted-U08T4FKSC04
2025-06-30 12:09:03

Ok, interesting, thanks!

I can see pctidv2 and financial log dataset is heavily used on views/scheduled queries and possibly dashboards

👍 James Turner
James Turner (jturner@shield-legal.com)
2025-06-30 12:09:38

One of my main goals over the last 6 months has been to get a better grip on keeping track of lead information here

James Turner (jturner@shield-legal.com)
2025-06-30 12:11:54

For example, we use Five9 to call leads, and Lawruler to record lead info. I build the system for billable call time as you know- but prior to five9 we had some questionable systems in place to estimate how much call time was spent on each lead and we discovered it was very far from accurate.

James Turner (jturner@shield-legal.com)
2025-06-30 12:12:28

Using LR_Data and Five9 data, I was able to see exactly how much intake time was spent per lead and get more accurate numbers.

James Turner (jturner@shield-legal.com)
2025-06-30 12:13:17

The 'old system' has a lot of half baked things like that call time, that we are trying to get rid of, but nobody ever properly audited because Ryan is the only owner

deleted-U08T4FKSC04
2025-06-30 12:16:40

Ok, that makes sense. So the pctidv2 and financial log dataset are part of the new system, right?

Would you say auditing these databases is important for the task of replacing law ruler? From what you’re telling me it might be better to focus on the new system as it’s more accurate

James Turner (jturner@shield-legal.com)
2025-06-30 12:20:28

So the pctidv2 and financial log dataset are part of the new system, right? Yes Would you say auditing these databases is important for the task of replacing law ruler? From what you're telling me it might be better to focus on the new system as it's more accurate I would say that focusing on the new systems is more important, but a basic audit of the old systems may be good to make sure we aren't missing anything

deleted-U08T4FKSC04
2025-06-30 12:22:06

Ok, thanks a lot!