Good afternoon Dustin, I wanted to let you know that my flight got canceled today and I won’t able to make it tomorrow. Im sorry and thank you for understanding.
Illinois Juvenile Hall Abuse - BG - Bay Point - Shield Legal
Hey, just want you to look it before I sent it
Do you have experience building database tables and setting up foreign keys?
not a lot but I have built a table. But foreign keys I don't remember doing that. What's up?
Hey Sean,
We can confirm that the populated fields are as expected. We also signed a sample retainer.
We sent a new one so please take a look and let us know if you have any questions.
ID:1014269 matter ID:7d378fa922d14169b2997bf86660ce27
does the retainer show in the intakedocumentsresponse column?
There is not a signed retainer in there. please include a new lead that has a signed retainer, ask @Ahsan or @Dwight Thomas if you need help signing. You can see if there is a signed retainer on that lead if you go to Documents > E-Sign > E-Sign Status
ohh my bad, I didnt sign after I change the contact name. I will go a head and sign now
Handling Firm (As provided by Bay Point)
try renaming vendor on L22 to intakeVendor and adding 'domain': 'external' to the matter_params. Add vendor, matterType, handlingForm to the mapping
<https://api.bridgify.com/venture/openapi/v1/matter?email=joe2test@gmail.com&externalId=622194&firstName=Joe2&lastName=Matt3&matterType=Sexual> Abuse&phone=2305020253&vendor=Shield Legal&handlingFirm=BaileyGlasser
This afternoon can you verify that the fields from the Bay Point Template document are included / updated in the PFAS campaign mapping 1739?
*Thread Reply:* on this one you wanted me to check intake 1739 is mapped as per the template right?
The doc is in Monday attached to the integration: https://tortintakeprofessionals.monday.com/boards/4677317281/pulses/6914673461?term=pfas&termColumns=XQAAAAK3AAAAAAAAAABBKoKDYebbzNbdUwNNLCQnk1dVKVc1dufsovK64KdbHz7qBPQeTPFsdRBEBU8Aaz4eRz-F5HuNi7FLuZoepCHy9d8Ooy6U1ZUg792y3r7o4GM2OnVVIp2j0F4EomssKmSyHgVmS6gyLm2FqUVQljSBLm6JqMXD9Fw4I7X2at2XkaiVPv93YEAA|https://tortintakeprofessionals.monday.com/boards/4677317281/pulses/6914673461?term=pfas&termC[…]S6gyLm2FqUVQljSBLm6JqMXD9Fw4I7X2at2XkaiVPv_93YEAA
*Thread Reply:* fixed the main for intake_1739
Please prioritize fixing the handlingFirm and sending a new test
Yeah, Im gonna fix that first. Just got back from lunch
fired test.. waiting 15 minutes to check the error log.
I see on the activity log that the email is delivered but on gcp logs I don't see it
Daniel also sent a test and it stalled your test until 2:30
perfect, it worked. should I send them the URL with the matter ID and ID?
<https://api.bridgify.com/venture/openapi/v1/matter?email=ted4test@gmail.com&externalId=622990&firstName=Ted4&lastName=Ryan5&matterType=Sexual> Abuse&phone=1953771526&vendor=Shield Legal&handlingFirm=Bailey Glasser
Here are the files they are asking about: • 622990-Ted4 Ryan5-Intake.csv - msg: Succeeded • 622990-Ted4 Ryan5-IntakeForm.pdf - msg: Succeeded • Ted4-622990-SignedContract.pdf - msg: Succeeded
Thanks, I was about to ask you. So this shows a doc is posted right?
Yes, as far as we know. Bridgify's system responded that the docs were uploaded
I will respond to Sean. I asked Anthony about those files
In 1863/main.py
- In L50 change
{'Files': attachment[1]}to{'Files': attachment} - Remove L46
You included the backticks (`). Please remove them. I was using them to highlight the code but since I pasted them in slack did not read them properly
so on intake_1739. I fixed main.py accordingly. But the mapping json is completely off with the doc
i dont even see most of questions that are on mapping appeared on the doc
Just remember the doc is only the fields that should be on every bay point campaign. That doc does not show the campaign specific fields
Make sure the stuff in the doc is in the mapping, do not worry about the stuff that is in the mapping that is not in the doc, yet
looks good, please send another test for 1863. will be the last for today.
<https://api.bridgify.com/venture/openapi/v1/matter?email=oliver7test@gmail.com&externalId=623092&firstName=Oliver7&lastName=Sam8&matterType=Sexual> Abuse&phone=9112422333&vendor=Shield Legal&handlingFirm=Bailey Glasser
id: 1017821
matterId: 80273c0733dc42e5a8bb4973a3656561
I recommend having multiple mappings in the same file, example:
{
{
"simmons": {
questions....
},
"baypoint": {
questions...
}
}
}
email: devops@shield-legal.com pass: nR83]8tq**>R1gn3%P8P]
Bay Point: <Response [200]> {"code":400,"message":"wrong matter relation: litigation","data":null}
https://github.com/shield-legal/gcp-integrations/pull/46#discussion_r1837356931 Fix before lunch tomorrow and send new test with new contact (to prevent matter duplication)
GM, I fixed the comments and created a new contact. Can I run a test now?
The logs are mixed, add comment explaining it
once pushed wait 5 minutes then send test
> • We can accept a one to one Diagnosis Field, in fact it feels too clunky to do a 12:1 mapping. This field as you know will be utilized across several Matters/Campaigns, so simplicity is key. The reason it exists currently as a 12:1 mapping, is Bridge does a lot of this Intake themselves and they ask if they were diagnosed with each type of cancer and select Yes/No on their side to capture.
> • Lets do this, itll require me revamping the Intake Layout a bit on my side, but lets test sending your singular diagnosis into the ‘injury’ field, I have moved it into the layout and try to get this across the finish line here. The main goal to get from 0 to 1 here is just getting the Client info and Documents posted in real time via API and then we can work through additional Intake fields or adjustments.
Based on the above quote from Sean at Bay Point. It looks like we need to change "Which of the following was the injured party diagnosed with?": "c-8721", to "injury": "c-8721"
*Thread Reply:* This for Illinois Juv -BG- Bay point right?
I will take a look. Ryan wants me to join a meeting
Simmons: <Response [201]> {"id":"a1D6T00000OOo8dUAD","success":"true"} Bay Point: <Response [200]> {"code":400,"message":"Matter already exists.","data":null}
Idk why it says matter already exists while I created new contact
Phone and Email also matter not just name
yeah, I changed phone, email and address
Simmons: <Response [201]> {"id":"a1D6T00000OOoDiUAL","success":"true"} Bay Point: <Response [200]> {"code":400,"message":"Matter already exists.","data":null}
Good afternoon,
We are testing the API integration using the following fields: name, phone number, email, and Lead ID to generate leads. However, during our testing, we encountered some error messages that we needed assistance with.
Could you please check on your end and let us know if we are missing any required fields or if there's a specific issue with the data we're submitting?
Error message below:
Simmons: <Response [201]> {"id":"a1D6T00000OOoDiUAL","success":"true"} Bay Point: <Response [200]> {"code":400,"message":"Matter already exists.","data":null}
Can you include the details of the 2 leads that we tried to test with?
It is still erroring on the main.py. Did you push the changes that changed intake['Email'] to intake['##']?
*Thread Reply:* Yes, I pushed with the updated one
Also the sound is on on your desktop, so everyone can hear if you get a notification. (Might want to change that)
Lead's 1 URL: https://api.bridgify.com/venture/openapi/v1/matter?email= kevin5david6test@gmail.com &externalId=626057&firstName=Kevin5&lastName=David6&matterType=Power%20Port&phone= 9852512426 &vendor=Shield%20Legal
Lead's 2 URL: https://api.bridgify.com/venture/openapi/v1/matter?email= don1rick2test@gmail.com
Found the issue Sean is talking about with the intake not passing: https://github.com/shield-legal/gcp-integrations/pull/46#discussion_r1840952787
I fixed and pushed it. Do you wanna me to test it?
GCP is on right? I see email being delivered but don't see it on the GCP log
It reverted back to Prod and errored. Retrigger the same lead
Thought you made a new lead when you asked to test it. Got a Matter already exists error since Don1 is already in their system. We need to make a new lead to try to not that error
ohh no I didnt. so do u wanna me create a new contact or new lead?
Simmons: <Response [201]> {"id":"a1D6T00000OOosHUAT","success":"true"} Bay Point: <Response [200]> {"code":0,"message":"Succeeded","data":{"id":1020285,"organizationId":247,"vendorOrganizationId":398,"matterId":"f71c3cf409b84bf49ed4263178189e5a","matterName":"Bill4 Max5","contactId":10760533,"prospe...
File "/workspace/main.py", line 39, in main matterid = matterresponse.json()['data']['matterId'] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^ TypeError: 'NoneType' object is not subscriptable
{"code":400,"message":"wrong matter relation: litigation","data":null}
okay. Do we have another contact in addition to Sean to cc?
everyone from the thread I replied to less than an hour ago
Can we mark PFAS Bay point done on Monday?
with an update that includes Sean's email
Sounds good. I don't get that notification thru Monday tho
It was assigned to me not you. I think that was the problem
Even though that is the case when someone write an update on Monday we got notified
lr user: zhaile@shield-legal.com lr pass: VrrxNi24%#
This is a better example: https://github.com/shield-legal/gcp-integrations/pull/58/files#
Good morning, Im not feeling well. And wanted to let you know that I will be working from home.
Is the mapping for BG the sames as Bay Point for 1863?
I do not see an update to the mapping file in your PR
I didn’t touch 1863 mapping. I only updated the main.py according to the new doc
So the fields are the same between BG and BP?
What do you want me to do on 1863 mapping? Im kinda confused
In this monday update there are 2 files for BG. 1 for the API and 1 for the mapping. Please add the BG mapping to 1863
Can I get an estimate on when it will be ready for testing?
I think we should be able to test it tomorrow.
Good afternoon, Darrel said API name is the one I need to use. He also shared me the sample ^ FYI
Then entities/0/children/0/body/textbody is the mapping
okay. Please ping me when u back from lunch. Thanks
Here is what needs to go in the mapping file as the BG mapping:
You will need to change the values to the c-###
from all these I will only take the questions that matches to our questions ?
Take all but leave the ones that do not match blank
Good morning, Im feeling unwell, so I will be working from home. Thanks!
Good morning, I'm in meeting with Ryan that I joined from home. Will be in office once done. FYI
are there fields from us that you did not add to the mapping?
baileyglasser: <Response [400]> {"error": "Upsert failed. First exception on row 0; first error: MALFORMED_ID, Case Type: id value of incorrect type: Juvenile Hall: [litify_pm__Case_Type__c]--(brioapi)", "success": false}
"caseType": "a03Hn00000vwHQeIAM",
curl -m 70 -X POST <https://us-west4-integrations-tip.cloudfunctions.net/test-intake_1863> -H "Authorization: bearer $(gcloud auth print-identity-token)" -H "Content-Type: application/json" -d '{ "bucket": "shield-legal-1863", "directory": "544498/intake" }'
{"data": {"name":"MAT_25010337519","externalId":"BRIO_TEST-2025-01-03 22:55:50zI9DD","id":"a0LPY000008TgC92AK","abuse":[{"externalId":"BRIO_TEST-2025-01-03 22:55:51YnHsX","id":"a3JPY000000Ab4j2AC"}],"client":{"externalId":"BRIO_TEST_623092","id":"001PY00000TQaLrYAL"}}, "success": true}
GM, Im in meeting with Ryan. Will go to office once done. FYI
Can you please reply to Bailey Glasser today?
I replied to Chris on Friday. But I see Virginia has some questions for us that she sent this morning
I do not see those emails. Please forward them to integrations@shield-legal.com and make that is included in future emails
I will. But I see integrations is being already cc’d
it keeps getting caught in the group filters
Do we still have to join the BG call in 15min?
We started another one and that will end by 12:30
I am not in office. ward asked if someone can join in the conference room
yeah I know you are not in office. Wondering if u can ask ward and join remotely?
When you have a chance please reply to Virginia with a generated payload file? We need to start with the secondary for this case type so it will take at least a week before we can send test interview data but it will be same test case.
Quick. We just need to keep it as a priority and keep constant communication
Take out the bay point fields and the baileyglasser key so it's like:
{"externalId":...
Idk if you see the email, but Chris is asking about test interview data? And also asking when can we send secondary?
Their interview data is our secondary case type. See my message from 12:07: > We need to start with the secondary integration for this case type so it will take at least a week before we can test interview data but it will send to the same case in their system.
Ohh okay. I was confused when they referred interview data. Im gonna reply to them saying we will start testing by the end of next week.
GM, I hope you're feeling better. Im in meeting with Ryan. Will be in office once done. FYI
New custom id for: Abuser 4 - Were they male or female? <<Custom25392>>
Anthony said they can change the custom ID. So should they also change the question?
Christopher D. Odle <codle@baileyglasser.com>
Virginia Schmitt <vschmitt@baileyglasser.com>
``` def updateleadsecondaryresponse(self, secondaryleadid: int, response: str): self.query(f'UPDATE leaddata SET secondaryresponse=%s, secondaryprocessedat=now() WHERE secondaryleadid=%s', [response, secondarylead_id])
def update_lead_secondary_lead(self, intake_lead_id: int, secondary_lead_id: int):
self.query(f'UPDATE lead_data SET secondary_lead_id=%s WHERE lead_id=%s', [secondary_lead_id, intake_lead_id])```
Chowchilla Womens Prison Abuse - Miller - Flanagan - Shield Legal
Good morning, I’m in meeting with Ryan. FYI
GM, working on daily financial routine. Will be in office once done.
GM, working on daily financial routine. Will be in office once done.
Oh okay. Probably if we can use Nick’s office for a minute
you can ask if you wish. Malissa will only be joining via video
Nick said ok. So we will join them from his office.
OrgID: shieldlegal API Key: zfkPzxC5oBtqkGDPOX8I500R curl --location 'https://baileyglasserllp.my.salesforce-sites.com/customapi/services/apexrest/brioapi/SingleFileWebhook?id=a0LPY000009LZ2P2AW&fileName=TestDoc.pdf&externalId=01312025_01&shortDescription=TestDoc' \ --header 'sforgid: BRIOTEST' \ --header 'apikey: JZxae8b92fnYSvrSHz' \ --header 'Content-Type: application/pdf' \ --header 'Cookie: BrowserId=ouTwP92wEe-AKf9V52OcQ; CookieConsentPolicy=0:1; LSKey-c$CookieConsentPolicy=0:1' \ --data-binary '@twfvWFMUV/TestDoc.pdf'
Christian Semmel 11:30 AM "" "Id" "Name" "[litifypmCaseTypec]" "a03Hn00000vwHQhIAM" "IL Sex Abuse" "[litifypmCaseTypec]" "a03Hn00000vwHQfIAM" "MD Sex Abuse" "[litifypmCaseTypec]" "a03Hn00000vwHQgIAM" "MI Sex Abuse" "[litifypmCaseTypec]" "a03PY00000FcRLBYA3" "NH Sex Abuse" "[litifypmCaseTypec]" "a03PY00000FcRLAYA3" "NJ Sex Abuse" "[litifypmCase_Typec]" "a03PY00000FcRL9YAN" "PA Sex Abuse" csemmel@gobrio.com
before you start working on this again either click the small circle with arrows next to the branch name or run git pull
I wanted to remind you that I’ll be off starting tomorrow. I hope the test went through smoothly on their end. However, in case there’s any issue and the campaign needs to be completed this week, could you please assist and address any problems that arise? I’d greatly appreciate your support in covering for me.
SELECT s.mediaspendlogid, s.mediabuyerid, b.name AS mediabuyername, s.spenddate, s.platformid, s.platformname, s.spendamount, b.overhead, b.revenuecommissionamt, b.commissionpercent FROM shield-legal-bi.slprodapplication.iomediabuyerspend AS s JOIN shield-legal-bi.slprodapplication.iomediabuyer AS b ON s.mediabuyer_id = b.id;
s.` media_spend_log_id`,
s.` media_buyer_id`,
b.name AS media_buyer_name,
s.spend_date,
s.`platform_id `,
s.platform_name,
s.spend_amount,
b.overhead,
b.revenue_commission_amt,
b.commission_percent
FROM shield-legal-bi.sl_prod_application.io_media_buyer_spend AS s
JOIN shield-legal-bi.sl_prod_application.io_media_buyer AS b
ON s.` media_buyer_id` = b.id
All the data is in the TIP project. Please create the BQ query for this. We do not have the Integration PUSH total at the moment. The Integration logs are in the Integrations dataset in the TIP BQ project. The rest of the data will be in the lr_data dataset.
ct.name AS case_type_name,
COUNT(CASE WHEN il.intake_status = 'Signed e-Sign FINAL' THEN 1 END) AS signed_esign_final_count,
COUNT(CASE WHEN il.intake_status = 'Signed e-Sign SENT' THEN 1 END) AS signed_esign_sent_count,
COALESCE(
COUNT(CASE WHEN il.intake_status = 'Signed e-Sign SENT' THEN 1 END) ** 1.0 /
NULLIF(COUNT(CASE WHEN il.intake_status = 'Signed e-Sign FINAL' THEN 1 END), 0),
0
) AS integration_completion_percentage,
COUNT(CASE WHEN il.intake_status = 'Signed e-Sign RE-TRIGGERED' THEN 1 END) AS signed_esign_retriggered_count,
l.id AS lead_id,
l.casename AS lead_name,
l.documentscount AS lead_documents_count,
MIN(CASE WHEN il.intake_status = 'Signed e-Sign SENT' THEN il.intake_processed_at END) AS lead_integration_sent_date
FROM
`tort-intake-professionals.lr_data.lead` AS l -- Lead table in lr_data
LEFT JOIN
`tort-intake-professionals.lr_data.case_type` AS ct ON l.casetypeid = ct.id -- Case type in lr_data
LEFT JOIN
`tort-intake-professionals.integrations.integration_log` AS il ON l.id = il.lead_id -- integration_log in integrations
GROUP BY
ct.name, l.id, l.casename, l.documentscount
Good morning, my car is having check engine light issues again so Im going to drop at the garage quick. Ty!
The mapping will be a clone with updating the larruler ids
I sent a test and went to lunch when I come back dont see an email delivered... thinking if this is the issue
looks like you copied the one for Bard PowerPort - Bay Point - Simmons - Shield Legal but did not update the casetype
I felt like I created the webhook correctly not sure im missing
The case type under the select recipients tab is still set to bay point simmons instead of crump simmons
Sent test lead, email delivered. would you mind checking for me at 3:30? I have an appointment at autonation Nissan (my car is not fixed yet) so I have to leave now
Will do, if successful can I send you the external id and you send an email to the firm tonight asking them to verify the data flow?
File "/workspace/main.py", line 66, in handle_crump
postgres.update_lead_intake_document_response(lead_id, existing['intake_document_response'] + '\nCrump: ' + crump_log['intake_document_response'])
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^~~~~~~~~~~~~
TypeError: unsupported operand type(s) for +: 'NoneType' and 'str'
datetime.now(UTC).strftime('%m-%d-%Y')
integrations@shield-legal.com Eric Brenner <EBrenner@seegerweiss.com> Jonathan Becker <jbecker@meadowlawfirm.com> Marco Rammairone <MRammairone@seegerweiss.com>
can we ask him about the Depo- Meadow-Seeger test lead we sent?
why we added Becker on the email then? wondering
Good afternoon, I spoke with Desirree and she wanted us to push the diagnosis description into the comment box if possible
Every field in our mapping should into comment box in SA (if they have it setup for that, I cannot recall if that is default)
so that probably be something they need to setup in their SA?
Only if the comment box is empty in your last test
I will double check but I dont think I have left empty
It would be in their SA. They may have to look
Since we do not have creds to crumps SA
ohh I thought you're referring the comment box on test lead
But I dont see a filed on our mapping that describe the diagnosis
Then we dont have it and cannot support her request without adding a question to the intake which requires cam's approval
not sure if this is what we are looking
To make sure I understood it correctly we are looking a question that describes the diagnosis right?
defendant is probably similar to injured party in the attached file from SA
email SA (rahul, "Rosmarin, Max" <Max@smartadvocate.com>, "igor@smartadvocate.com" <igor@smartadvocate.com>) & include crump • ask about Incident facts box populating when we are not sending to that field • ask about putting all fields sent in comments box • ask about defendant UDFs
Max's email:
- Can we send him a screenshot?
- You said that it was included in the comments box instead of not. We should get that turned on.
- Now the defendant should work.
That’s what we want right? Wanna make sure
Yes, plus the defendant fields did not populate
Good morning, I will be picking up my sister from airport at 9:30
pushed it. please take a look when u get a chance
@Dustin Surwill Im I good to send a test?
I made commits on the sggh branch. Please run git pull --ff-only to get my changes. I have to consult my notes and the email they sent to let you know the best course of action before the next test.
adjust the test questions to have different data for injured party and signer.
my notes say to add logic in the main.py to change the signer name to self when it equals the injured party name
I see the email delivered but I dont see it ran
it looks like the test came sucecessful. Im gonna emial them to check it
Anytime you send an email to asking to verify a test please include the external id from the database
we need to create a new leadid for each test. you can duplicate the lead for that purpose
SGGH cases: • San Diego 2005: 715118 • San Bernardino 2006: 716828 • IL YTC 1954: 706235 • IL Clergy 1953: 682469 • MI YTC 1952: 686808
I do not see your commit with the mapping for 1952
Ohh I only updated the branch. I can commit when I get home
<a href="mailto:kjaklevic@simmonsfirm.com">kjaklevic@simmonsfirm.com</a>
Mapping updates needed before sending
• 1929 - case type should be: Dr Barry Brock SA
• All "c-" needs to replaced ""
• Replace \u00a0 with a space
Did you talk to anyone about working remote on July 21st?
Did you talk to anyone about working remote on July 21st?
No, I haven't. I just added 10mins ago and about to let u and Nick know
If this is complete it also needs a status of complete: https://tortintakeprofessionals.monday.com/boards/5053948403/views/112014534/pulses/9315313311 Also the title was "New Integration" based on the description it should be "Create AWD - All Cases - Dashboard"
Ohh I thought I did that.. will fix it. Thanks
Hi Dustin, I was trying to generate the payload for MI JUV SGGH campaign ...was able to generate the intake for the 1st one but when I tried to generate the payload it's erroring out since I dont have intake_2032 on my folder
the folders do not have intake in the name anymore? make sure you are on the correct branch
okay, that's probably the issue then.. so as per your email, we are going to send all the payloads in one push right... so we need to send successful test to the secondary's?
I was thinking to send few payloads for intakes only
We already tested the intakes. The secondarys use the data from the intakes. @Dwight Thomas has the list of intake to secondary lead ids for 3 of the case types. The secondarys are currently pointed at the echo_function in gcp, so that we can generate the payloads without actually sending data. Generating the payload will be a 2 step process:
payload.py intake.json secondary/mapping.json
payload.py secondary.json firstpayload.json
ok. yes I know intake has been tested... then I will send them tomorrow morning
Send a zip with all the docs (intake + secondary) and the payload to me for each of the 3 tests
Good Morning, Dwight has only sent me 2 lead ids for MI JUV and MI Foster. Which one Is the 3rd one Clergy or YRTC ?
YRTC. There were no leads in Clergy when we looked earlier this week
I see. I'm gonna use random actual leads for YRTC...
MAke sure to use one that is in both intake & secondary
That is generated by the LR automation and saved in GCS by our integration code
I think this wasn't retriggered. Shall I retriggered it?
You are looking at the wrong table. look in the lr-data database for integrations.integrations_log
But the YRTC lead I have to retriggered it right? since Dwight didn't do that
Here are the docs(except the excel one) and payloads
No the secondary.json from gcs. Payload.json is combined with the mapping.json
Also we do not need the payload.json for intake only secondary
If I am misunderstanding, I will take a closer look on Monday
okay then we can do Monday... I will finish up Ryan's stuff now
Good afternoon, I will be working remote on July 7th as I will be traveling to Seattle. Told Nick and wanted to let you know.
Good morning, my apartment is scheduled for a yearly inspection at 9:30 and I need to be present let them in the inspectors, so I will be leaving around 9:15
Can you work on this?
Please review: https://github.com/shield-legal/gcp-integrations/pull/217
Please review: https://github.com/shield-legal/gcp-integrations/pull/218
Reasoning behind the changes https://peps.python.org/pep-0636/
copy Crump Camp Lejeune (268) mapping.json to
- MD Juvenile Hall Abuse - Crump - Slater - Shield Legal - https://themedialaboratory.slack.com/archives/C08R9MUDK71/p1751310835075129
- CA Juvenile Detention Abuse - Crump - Slater - Shield Legal - https://themedialaboratory.slack.com/archives/C08R9MUDK71/p1751311144434269
- Hair Relaxer Cancer - Crump/Dicello (IC) - Shield Legal - https://themedialaboratory.slack.com/archives/C08R9MUDK71/p1750965053718019
- Hair Relaxer Crump/Dicello/Shield - https://themedialaboratory.slack.com/archives/C08R9MUDK71/p1750960380643819 replace last 5 questions with questions specified in each post may need to check the case types in the files attached to the slack posts to verify the case types
Once you send the tests for BCL can you work on the mapping for 1752? There are some incorrect fields. The filtered log to just the broken fields is in broken_vga_sec_fields.json and all the fields possible for vga (intake & sec) are in vga_udfs.json
``-- create or replace view sl_staging.sl_new_fin
-- as
WITH deduped_media_buyer_spend AS (
SELECT
unique_identifier,
media_buyer,
agency,
vertical,
platform,
spend_date,
submit_date,
spend_amount,
vertical_id
FROMshield-legal-bi.slprodapplication.iomediabuyerretoolprod`
)
SELECT
SPLIT(SPLIT(a.s1, '-')[SAFEOFFSET(1)], '')[SAFEOFFSET(0)] AS mediasource,
SPLIT(SPLIT(a.s1, '-')[SAFEOFFSET(2)], '')[SAFEOFFSET(0)] AS mbidnext2,
a.leadID,
a.firstname,
a.lastname,
a.emailaddress,
a.phonehome,
a.zipcode,
a.campaignName,
a.buyerName,
a.buyerID,
a.contractID,
a.createdOn,
a.createdOnUTC,
a.dateSold,
a.price,
a.verticalName,
a.s1,
a.s2,
a.leadscore AS soldleadscore,
b.leadspedialeadid,
b.leadspediafullid,
b.fivetranid,
b.fivetransynced,
b.fivetrandeleted,
b.leadspedias1,
-- b.leademail,
b.lawrulerleadcreatedate,
b.contactfirstname,
b.contactlastname,
b.marketingsource,
b.earliestesign,
b.lawrulercasetypeid,
b.lawrulerleadid,
b.contractids,
c.price AS contractprice,
a.verticalID AS verticalid,
m.spenddate,
m.spendamount
FROM
shield-legal-bi.tip_bigquery_shield_datastore_tip_external.shield_legal_datastore AS b
LEFT JOIN
shield-legal-bi.leadspedia.all_sold_leads AS a
ON b.leadspedialeadid = a.leadID
LEFT JOIN
shield-legal-bi.leadspedia_all_contracts.leadspedia_all_contracts AS c
ON a.contractID = CAST(c.contract_id AS STRING)
LEFT JOIN
dedupedmediabuyerspend AS m --- de-dupe spends
ON CAST(m.verticalid AS STRING) = a.verticalID AND m.spenddate = CAST(CAST(a.createdOnUTC AS DATETIME) AS DATE)
LEFT JOIN shield-legal-bi.sl_dashboard.orders_paid_spent AS o
ON o.cprlpcontractids = a.contractID
WHERE
b.fivetrandeleted = false;```
BCL co-councils:
• 303 - DiCello Levitt LLP
• 304 - DiCello Levitt LLP
• 418 - Slater Slater Schulman LLP
• 215 - Slater Slater Schulman LLP
Have you done any DL mappings so far this weekend?
No, I haven’t. Im in California for some family events.
Good morning @Dustin Surwill I'm not feeling better today, will be working from home.