Slack Export

I have added the views in BQ to the tables I am working in. They are under the lrdata dataset in the Tort Intake Professionals project. The ones that I am currently populating are: lead, leadevent, leadeventattendee, leadhistory, leadstage, leadtag, leadtask. I am skipping lead_question for now.

Ryan (ryan@themedialab.agency)

2024-11-07 20:08:43

Ok. Let’s meet next week to understand the schema so we can get SQL modeling going to a billable log. Thanks Dustin.

👍 Dustin Surwill

Ryan (ryan@themedialab.agency)

2024-11-11 14:54:38

[Vertical Name] - [Customer Firms] (TIP) - [Lead Source]

Ryan (ryan@themedialab.agency)

2024-11-11 14:56:00

LA Foster Care - Miller / Van (TC) (TIP) - Contract

Ryan (ryan@themedialab.agency)

2024-11-11 15:20:16

Leads_1731359346.csv

Ryan (ryan@themedialab.agency)

2024-11-12 12:38:50

@Dustin Surwill, I am updating the Team Google sheet, can you send me the URL to your Monday Tasks Board for workload management.

Ryan (ryan@themedialab.agency)

2024-11-12 12:39:07

And add me to that board in Monday.com too. I won't touch it, just want to see it and have link in sheet.

Dustin Surwill (dsurwill@shield-legal.com)

2024-11-12 12:39:47

We use the follow Monday boards: • API Integrations: https://tortintakeprofessionals.monday.com/boards/4677317281/views/165769444 • Tasks: https://tortintakeprofessionals.monday.com/boards/5053948403/views/112014534

tortintakeprofessionals.monday.com

Welcome to monday.com | a new way of working

<a href="http://monday.com">monday.com</a> Work OS is an open platform where anyone can create the tools they need to run every aspect of their work.

Original URL: https://tortintakeprofessionals.monday.com/boards/4677317281/views/165769444

tortintakeprofessionals.monday.com

Welcome to monday.com | a new way of working

<a href="http://monday.com">monday.com</a> Work OS is an open platform where anyone can create the tools they need to run every aspect of their work.

Original URL: https://tortintakeprofessionals.monday.com/boards/5053948403/views/112014534

😎 Ryan

Dustin Surwill (dsurwill@shield-legal.com)

2024-11-12 12:41:47

You have been invited to those boards

Ryan (ryan@themedialab.agency)

2024-11-13 12:58:33

Thanks Dustin

Dustin Surwill (dsurwill@shield-legal.com)

2024-11-13 13:45:28

Can we create an LR status for "Secondary Interview - ReTriggered"?

Ryan (ryan@themedialab.agency)

2024-11-13 14:25:14

Sure, let me show you how to do this, conference room Storm Trooper, come on down.

Ryan (ryan@themedialab.agency)

2024-11-13 14:25:24

James and Nick are in waiting on you

Ryan (ryan@themedialab.agency)

2024-11-13 14:27:11

@Dustin Surwill

Dustin Surwill (dsurwill@shield-legal.com)

2024-11-13 14:31:37

Sorry, left for lunch and to collect a medical device

Ryan (ryan@themedialab.agency)

2024-11-13 14:58:18

https://www.loom.com/share/c611a928311841388b406095347e74a1

Ryan (ryan@themedialab.agency)

2024-11-13 14:58:33

Watch first 15 minutes, rest is just us moving on in on-board of James

Dustin Surwill (dsurwill@shield-legal.com)

2024-11-13 14:58:44

Okay

Ryan (ryan@themedialab.agency)

2024-11-13 16:25:42

So, it's ready for you to use in LawRuler, just name it exactly as you did above.

Dustin Surwill (dsurwill@shield-legal.com)

2024-11-13 16:25:56

Okay, thanks

Dustin Surwill (dsurwill@shield-legal.com)

2024-11-14 15:42:34

Can we create an LR status for "Signed & DECLINED ReTriggered"?

Ryan (ryan@themedialab.agency)

2024-11-14 15:58:47

Grab James Turner and Google Meet me real fast, so I can show him this process

Dustin Surwill (dsurwill@shield-legal.com)

2024-11-14 15:59:37

He is working remote today, and I am working a getting Miller integration complete since there is a deadline of tonight

Ryan (ryan@themedialab.agency)

2024-11-14 16:11:18

You can set it up in LawRuler

👍 Dustin Surwill

Dustin Surwill (dsurwill@shield-legal.com)

2024-11-15 10:02:58

Was the BI Pipeline Stand Up canceled? I did not receive any notifications that it was canceled or moved but no one is here.

Ryan (ryan@themedialab.agency)

2024-11-15 10:04:07

I did it in Slack in #C065YE3Q9UJ, I am still closing TIP's entire month of October, getting November caught up. Took 5 hours just to update 5400 leads revenue and expense yesterday.

👍 Dustin Surwill

Ryan (ryan@themedialab.agency)

2024-11-27 09:54:03

@Dustin Surwill Happy Thanksgiving. Thankful for you, the work you have done and the teamwork to get our integration project to this point. Have a great holiday!

Ryan (ryan@themedialab.agency)

2024-11-27 10:47:21

@Dustin Surwill, before end of day today, I need your estimate or forecasted total size in GB/TB of our LawRuler dataset? To answer the question, "How much data will we store and process warehousing LawRuler" from your lrdata, lrintakes, etc, including PDFs, DOCs, that we want to store?

I need it for some downstream budgeting for leadership team. Ty in advance.

Dustin Surwill (dsurwill@shield-legal.com)

2024-11-27 11:15:02

• Cloud SQL for lr data is storing 7.5 GB / 100 GB (All LR data related to leads) and costs between $4-$5/day (no matter the amount of storage) • Cloud SQL for integrations is storing 0.04 GB and costs $1.5/day (no matter the amount of storage) • Cloud Storage for integrations is storing 5.14GB and costs <$0.1/day • Cloud Functions for both tip and integrations cost <$1/month

🙏:skin_tone_4: Ryan

Ryan (ryan@themedialab.agency)

2024-11-27 11:41:18

So, of our 500,000 leads, totals to about 100 GB in total?

Dustin Surwill (dsurwill@shield-legal.com)

2024-11-27 11:41:45

no, all backfill of 600k leads + answers is <10GB

Dustin Surwill (dsurwill@shield-legal.com)

2024-11-27 11:41:57

100GB is the current max storage on that db

Ryan (ryan@themedialab.agency)

2024-11-27 11:42:29

If we store the retainer PDFs and CSVs, how much would it total approximately?

Dustin Surwill (dsurwill@shield-legal.com)

2024-11-27 11:44:07

We are in cloud storage for every lead that gets sent via integration. currently 3500 leads at 0.04GB = for all leads ~7GB for all docs

Ryan (ryan@themedialab.agency)

2024-11-27 12:05:14

Wow, got it.

🙏 Dustin Surwill

Ryan (ryan@themedialab.agency)

2024-11-27 12:05:26

Thank you

Ryan (ryan@themedialab.agency)

2024-12-04 12:01:49

be right on

👍 Dustin Surwill

Ryan (ryan@themedialab.agency)

2024-12-04 12:50:46

Target System Data for Ben Crump's SA for AFFF:

BCL SA - AFFF - All Cases through 12042024.xlsx

Dustin Surwill (dsurwill@shield-legal.com)

2025-01-13 20:00:52

*Thread Reply:* Can I get an updated version of this report? The integrations are converted and approved. The last thing we have is the backfill of the missing

Dustin Surwill (dsurwill@shield-legal.com)

2025-01-17 17:08:41

*Thread Reply:* The missing BCL AFF leads have now been sent

Ryan (ryan@themedialab.agency)

2024-12-04 12:51:41

Firefighting Foam - Crump - ELG - BLX / Firefighting Foam - Crump - ELG - Shield Legal where statuses are Signed eSign% and have not been sent (matched back to the sheet above) to BCL SA system.

Dustin Surwill (dsurwill@shield-legal.com)

2024-12-05 18:13:56

Here is the sheet that has that data: https://docs.google.com/spreadsheets/d/1nAxZwjVH8l5KpqxKf9jsOhogejy9vqYba5YqgaZJ4mk/edit?gid=374310013#gid=374310013

☝️:skin_tone_4: Ryan

Ryan (ryan@themedialab.agency)

2024-12-06 10:55:18

@Dustin Surwill, so this is 449 deals NOT in their system you can get a GCP Webhook setup and get these completed?

Dustin Surwill (dsurwill@shield-legal.com)

2024-12-06 13:33:32

Those integrations were not moved to GCP. It will be a few days to get it moved over. No MPA flow triggered in 28 days. It will take a few days to move those to GCP

Ryan (ryan@themedialab.agency)

2024-12-06 13:49:04

Ok. Add to board please.

👍 Dustin Surwill

Dustin Surwill (dsurwill@shield-legal.com)

2024-12-09 15:02:02

https://themedialaboratory.slack.com/archives/C05GHTD7M24/p1733426371291559

Ryan Vaspra (https://themedialaboratory.slack.com/team/UJF2W0EB1)

Good news, @Nicholas McFadden, what can we do with our LR Data to give QA / Integrations Team a way to monitor this? @Dustin Surwill, get with @Nick Ward and @James Turner ASAP, lets get some visibility into this. We need SQL written, I'll then train James on how to implement this and get it into Looker Enterprise. TIP LR Data • Case Type Name • Count of Signed e-Sign FINALs • Count of Signed e-Sign SENT • SENT contracts over FINALS total contracts ration = Integration Completion % • Count of Signed e-Sign RE-TRIGGERED ◦ Lead ID ◦ Lead Name ◦ Lead Documents count ◦ Lead e-Signed Date ◦ Lead Integration Sent Date ◦ Lead Documents Sent In PUSH Validation = Boolean ◦ Lead Integration PUSH total (how many times we had to send it)

Original URL: https://themedialaboratory.slack.com/archives/C05GHTD7M24/p1733426371291559

Ryan (ryan@themedialab.agency)

2024-12-10 10:10:51

We don't need this meeting now that we discussed BCL Depo Provera to SA integration, correct? Have some back to back meetings, so we can push if you need it. Lemme know:

image.png

Dustin Surwill (dsurwill@shield-legal.com)

2024-12-20 17:52:44

We converted Firefighting Foam - Crump - ELG - Shield Legal to GCP but are having a hard time testing since this integration goes to SmartAdvocate and they are returning an empty response (no case id)

Ryan (ryan@themedialab.agency)

2024-12-20 18:01:35

What’s the next step to resolve?

Dustin Surwill (dsurwill@shield-legal.com)

2024-12-20 18:03:19

We have already asked SA about the issue since it was impacting Meadow VGA first but they have not found a cause yet. We are currently working with ELG for a new integration but that system is different (a custom API) instead of SA. We can see about changing the old integration to use to new docs and endpoint?

Ryan (ryan@themedialab.agency)

2024-12-20 18:04:18

Sure. As long as we can get to SA BCL and ELG as required

Dustin Surwill (dsurwill@shield-legal.com)

2024-12-20 18:04:58

The old integration only went 1 place and we are not sure which it went to BCL or ELG

Ryan (ryan@themedialab.agency)

2024-12-20 18:05:47

The one we did was to BCL only.

👍 Dustin Surwill

Dustin Surwill (dsurwill@shield-legal.com)

2025-01-07 12:26:02

The Crump UDFs is for which case type? Depo-Provera - Crump - Napoli - Shield Legal or Depo-Provera - BCL - Napoli - BLX?

Ryan (ryan@themedialab.agency)

2025-01-07 12:34:50

They are clones, make the integration handle both and send both e-signs over.

Dustin Surwill (dsurwill@shield-legal.com)

2025-01-07 12:35:39

Does that mean Napoli also needs the e-signs from BLX?

Ryan (ryan@themedialab.agency)

2025-01-07 12:36:14

It means that Shield Legal version are paid leads, and the BLX version are free leads from the website when Cam gives the SEO team the greenlight to publish organic pages on BenCrump.com, make sense?

Dustin Surwill (dsurwill@shield-legal.com)

2025-01-07 12:36:46

And the integration is the same for both for all case types?

Ryan (ryan@themedialab.agency)

2025-01-07 12:42:02

Yes, same intake questions, just case type name should be different.

👍 Dustin Surwill

Ryan (ryan@themedialab.agency)

2025-01-07 12:42:05

Same docs, etc.

👍 Dustin Surwill

Ryan (ryan@themedialab.agency)

2025-01-13 20:15:00

@Dustin Surwill, you can find all of them in the typeofcase field and lead_source fields in this table in SL project: https://console.cloud.google.com/bigquery?ws=!1m5!1m4!4m3!1sshield-legal-bi!2sgcp_tip_bigquery_legacy_tip_staging!3sdbt_financial_log

accounts.google.com

Google Cloud Platform

Google Cloud Platform lets you build, deploy, and scale applications, websites, and services on the same infrastructure as Google.

Original URL: https://console.cloud.google.com/bigquery?ws=!1m5!1m4!4m3!1sshield-legal-bi!2sgcp_tip_bigquery_legacy_tip_staging!3sdbt_financial_log

Dustin Surwill (dsurwill@shield-legal.com)

2025-01-14 13:36:18

But how do we know if it is in their system? Previously you exported the cases from Crump's SA for AFFF, could you do that again? Your export was only until 12/4/2024

Ryan (ryan@themedialab.agency)

2025-01-14 15:19:56

Oh, those were all time from their system, no more would be pushed.

Ryan (ryan@themedialab.agency)

2025-01-14 15:20:03

Let me send you one file I do have

Ryan (ryan@themedialab.agency)

2025-01-14 15:32:43

Here is ALL BCL AFFFs @Dustin Surwill from SA no matter the source to check for dupes

Ryan (ryan@themedialab.agency)

2025-01-14 15:32:50

webDGCase (7).xlsx

Ryan (ryan@themedialab.agency)

2025-01-17 17:44:27

@Dustin Surwill , excellent, thanks. All fields validated correct in SA for BCL?

Dustin Surwill (dsurwill@shield-legal.com)

2025-01-17 17:45:01

Yes it was validated with a test before the push

Ryan (ryan@themedialab.agency)

2025-01-17 17:45:40

Ryan (ryan@themedialab.agency)

2025-01-17 17:45:50

I’ll let client know.

Dustin Surwill (dsurwill@shield-legal.com)

2025-08-06 12:31:07

Me, @Zekarias Haile & @Nicholas McFadden are stuck in a meeting and will be few minutes late

Dustin Surwill (dsurwill@shield-legal.com)

2025-08-13 14:01:31

Where is this meeting?

Dustin Surwill (dsurwill@shield-legal.com)

2025-08-21 14:11:00

SELECT ** FROM <a href="http://tort-intake-professionals.lr">tort-intake-professionals.lr</a>_data.case_type WHERE name LIKE '%Backpage%'

image.png

Ryan (ryan@themedialab.agency)

2025-08-21 14:11:33

SELECT ** FROM <a href="http://tort-intake-professionals.lr">tort-intake-professionals.lr</a>_<a href="http://case_types.lr">case_types.lr</a>_case_types WHERE name LIKE '%Backpage%' ORDER BY ID DESC;

Ryan (ryan@themedialab.agency)

2025-08-26 11:32:25

What was sent in mass to Siged e-Sign Retriggered yesterday?

Dustin Surwill (dsurwill@shield-legal.com)

2025-08-26 11:33:26

Backpage ACTS ACTS. So actually send the welcome letter. Worked with Ward for it

Ryan (ryan@themedialab.agency)

2025-08-26 11:33:49

Ok, those were existing deals then, moved to that new status in bulk, yeah?

Dustin Surwill (dsurwill@shield-legal.com)

2025-08-26 11:34:07

yes, from final & final - sent to retriggered

Ryan (ryan@themedialab.agency)

2025-08-26 11:36:00

Thanks, let me know when we have large batches, me and Edward and Zek.

👍 Dustin Surwill