*Thread Reply:* @Ryan I’ll take care of it
We have one already notifying us @deleted-U04KC9L4HRB
@Luke Fontaine @Ryan you may have already created - if so please send to me, if not please create dashbaord for Sbaiti & Company for camp lejeune + camp lejeune EO. Also, I need this dash "cloned" for Flatirons. If you could make a separate dash with Flatirons/Sbaiti on it and send to me please?
We need two separate to clarify - one for Sbaiti alone and one for Flatirons/Sbaiti
@Cameron Rentch here is the all cases dash board for Sbahiti & Company Shield Legal - External - Sbaiti/Sbaiti I can create a dashboard today for Flatirons/Sbaiti, however it looks like the only Flatirons contracts we currently have built in Leadspedia is DiCello/Flatirons, so the dashboard will just be empty until we have a contract built out for Flatirons/Sbaiti and I can reset the dashboard then
@Luke Fontaine the Sbaiti and Sbaitit Flatirons are one in the same. I just need two separate dashbaords for it, where I will give access to two different groups. Make sense?
I dont need anything changed just a clone dashbaord where you can add flatiron logo
@Cameron Rentch got you, the CLJ Sbaiti/Sbaiti campaign also includes Flatirons, even though they're not listed on the contract name. I'll get that dashboard copied with Flatirons logo and sent over shortly
@Cameron Rentch here's that requested dashboard, also added it to #shieldclientdashboards • Shield Legal - External - Camp Lejeune - Sbaiti/Sbaiti/Flatirons
*Thread Reply:* @Ryan this should go into the already made bg/ghozland dash
*Thread Reply:* @Ryan this will go into the already created acts dash
*Thread Reply:* @Ryan this will go into the ACTs dashboard
*Thread Reply:* @Ryan will go into ACTS dash
*Thread Reply:* @Ryan will go into ACTS dashboard
*Thread Reply:* @Ryan this will go into the ACTS dash
*Thread Reply:* @Ryan this will be added to the ACTS dash
@Brian Hirst @Mark Maniora can you guys please set up and configure the posting for this leadspedia contract for CPAP? It is for Tik tok. Note that this uses different fields @Brian Hirst drop down version of the form not the check-boxes
@Mark Maniora take some time and spot check this csv against Birth leads in leadspedia? These are all from Tik tok. We are using a "preferred" form. You can swing by and talk with maximus he can explain if you need more details. We are just trying to see if the "preferred" leads only are posting into leadspedia
@Luke Fontaine can you please send me the dashboard link for AFK-Napoli? (Aiken Farrell Kroloff) should have PFAS and AFFF as case types thank you
@Cameron Rentch Shield Legal - External - All Cases - Napoli Shkolnik/AFK
@Ryan @Luke Fontaine please check calcs on this dash
14 EO at 10500 = 147000 11 NON EO at 7000 = 77000
That should show 224,000 spend, BUT dash reporting 227,500
please fix
https://lookerstudio.google.com/u/0/reporting/a75a615e-3162-4b70-8b0b-af69136bbf70/page/ASVvC
@Cameron Rentch @Ryan Fixed. There was one lead that was counted as an EO instead of Non EO causing the discrepancy
@Ryan I would like to see some proposed examples of new customer dashboards we are working on. Making sure I am clear, I do not like the standard "Google" dashboards we use. They are very "meh". I would like to see some dashbaords more in line with these types of products, and/or just purchase a subscription so we can pipe in our data into more usable graphic interfaces.
I would like significant traction made on this in the next two week and have a series of demos done with 3rd party products. We need to speed this up.
list of top ten
https://marketingarsenal.io/custom-dashboard-software/
@Cameron Rentch, as a part of the Biz Intelligence project, we are building new external dashboards as you reqeusted. We have TONS of work to do before we get there, so having us work on old data and old dashboards will cause real delay, as we are working on your "Shield Legal" and "Tort Intake Professionals" database design and data pipelines across the board with @Nicholas McFadden @Luke Fontaine @Brian Hirst and me.
While supporting the technical debt / manual daily udpates and computations of the old system.
So, we'll use these as inspiration in "dashboard user interface design" when much further downstream we build out dashboards. Thanks for sharing.
@Cameron Rentch can you give us a little more insight into the features/functionality you are wanting from these other options and what are the main issues with the "Google" dashboards? That way we can make sure to look for a product that fits your criteria and to ensure it includes the features you desire.
We have a stack that can produce custom dashboard interfaces that @James Scott shared @Cameron Rentch. We have some testing to do but we will know more soon.
Now, getting all the data mapped out from the data sources, and getting it transformed for dashboards is our hyper focus for the next six months.
@Cameron Rentch this is our proposed stack below. Looker Enterprise is the solution we have instead of a Domo (used it before), Grow is same as Funnel.io, etc., and the others on the list are all "out of the box" and would not give us the flexibility we need for building an internal portal, external client portal and external SaaS platform with just "one of those".
But in effort to invest in the data and data science and keep "application / portal" costs down, @James Scott and I will be testing the Looker Enterprise API to pull data and use a "hand built custom dashboard interface" using simpler, free components. https://michael-scherding.medium.com/building-a-data-visualization-app-with-looker-streamlit-and-plotly-9e9d2f324779 (sample image attached)
FYI @Nicholas McFadden @Brian Hirst @Luke Fontaine @Mike
- DBDiagram.io - Data Design / Schemas
- Fivetran - Data Extraction Management
- GCP Cloud Function - Data Load Management
- BigQuery - Data Storage and Warehousing Management
- DBT - Data Transformation Management
- Retool & Looker Enterprise - Internal Dashboards
- Retool & Looker Enterprise API & Streamlit & Plotly OR Retool & Looker Enterprise Embed Module - External Dashboards
*Thread Reply:* @Cameron Rentch, we also have Looker Enterprise prototyped on our old, current Postgres Database on AWS. We are focused on data design for new system this week and next, then we'll get the Looker prototype ready to share internally including a new client dashboard design to show you.
@Cameron Rentch, and today, we have our Fivetran training for me, @Nicholas McFadden, @Brian Hirst and @Luke Fontaine with our GCP certified partner ByteCode. So we'll be unavailable from 10AM to 11:30 AM PST.
@Brian Hirst can you please work on getting me a report from lawruler for the following: All s1's that contain Tik Tok All signed contracts last 30 days for: Paraquat (Dicello/Flatirons) CPAP (Dicello/Flatirons) Birth Injury (ACTS CA) AFFF (3 or 4 campaigns BG Ghozland, BCL - ELG, BG Loghmana, Napoli AFK) Camp Lejeune (EO and NON EO Sbaiti)
@Cameron Rentch I'll do last 30 days reports first and then start on the TikTok S1's. There are 26 different reports to pull s1's from.
thanks @Brian Hirst also need Bailey Glasser Levinsons, Asbestos
@Cameron Rentch any special fields required for the last 30 days report? or is the regular Lead ID, First Name, Last Name, Type of Case, Status, Phone Number enough?
@Brian Hirst Lead ID, First Name, Last Name, Type of Case, Status, s1 is all that is needed thank you!
@Cameron Rentch Ok do you want a signed contract report for ALL last 30 days for these: Paraquat (Dicello/Flatirons) CPAP (Dicello/Flatirons) Birth Injury (ACTS CA) AFFF (3 or 4 campaigns BG Ghozland, BCL - ELG, BG Loghmana, Napoli AFK) Camp Lejeune (EO and NON EO Sbaiti) OR Just the contracts signed to the casetypes with a TikTok S1
only the contracts signed with a Tik Tok s1
@Cameron Rentch LR reporting is back up so I am starting these now.
@Brian Hirst can you please do me two favors:
- remove "last name" from report
- rename the reports to "omit" the law firm name (I need to provide this to Tik tok engineers and team, dont want to show them last name or law firm name
@Cameron Rentch do you want the individuals ones too?
@Brian Hirst yes please individual ones too
@Brian Hirst I am seeing some disparity in the amount of signed contracts(what is showing in the channels is more than what you are showing on your reporting for AFFF, Camp Lejeune, Asbestos
I'll put raw in here and sort them by s1 type so you can see what I pulled
I went back a little further in December and deleted the ones that were outside the 30 days you requested due to the discrepancy of LR filtering the reports on intake date and not signed date.
@Cameron Rentch I highlighted the TikTok s1's in Orange
If you give me instance where you found one in the channel that was not in the report I can narrow it down. I am looking now
also @Brian Hirst the EO Sbaiti report is only showing FB S1 tags?^
@Cameron Rentch I just checked, and there is not a discrepancy between the report for Bailey Glasser Levinson and the channel. The ALT ID for that one is "BG - LTC - 498 - 1276." There are other BG Asbestos signings under different casetypes that have a TikTok S1.. If you want every sign up with a TikTok S1 I will need to expand to to more casetypes and work backwards from Leadspedia to see which casetypes have a TikTok contract attached. That will take some time.
@Cameron Rentch Sbaiti EO only had 1 TikTok S1
The only other Sbaiti EO with a tiktok S1 was added by Luke so it was not in the LR system.
@Brian Hirst I am seeing two signed? if Luke added please find out where it is? It should be in lawruler
The record is in law ruler, but the S1 tag is not. That is why Luke added it manually
can you pls update the record in lawruler (manually add the S1 please) @Brian Hirst ^
@Luke Fontaine good morning can you please get me dashboard links for Aiken Farrell Kroloff dashboard for the AFK/Napoli Scholnik PFAS and AFFF campaigns?
@Cameron Rentch here's the link for the Shield Legal - External - All Cases - Napoli Shkolnik/AFK
@Luke Fontaine I found the AFK/Napoli dash: https://lookerstudio.google.com/u/0/reporting/dd82c053-72bd-4424-ab93-e26fdbc475cc/page/ASVvC Can you please add these users to the dash? (I think they are google accounts)
@Cameron Rentch they both look like google accounts, I'll go ahead and add them now
@Luke Fontaine, confirming new items for MB Dashboards for us to tackle.
- Ad Ad Platfrom to their first tab as Filter & Expandable Dimension (done for JA)
- Ad Ad Platform Filter to their second tab (done for JA)
- Ad New Tab for Billable Leads from TIP where S1 contains MB-XX
- Ad Custom Column for Total Rev 1M + 5K, / 2M + 10K / 3M +15K I'll work on this but let you know if I need help
@Brian Hirst @Mark Maniora please set up this tik tok contract Asap for AFK. Turn it to active as soon as it is ready please
@Luke Fontaine can you please create a dashbaord for ACTS Birth injury, post here when done. Thank you
@Cameron Rentch yeah no problem, I'll get that to you today
@Cameron Rentch here's the ACTS Birth Injury Dash Shield Legal - External - ACTS Law Firm - Birth Injury
*Thread Reply:* @Ryan this will go into the DL/flatirons preexisting dash
@Mark Maniora @Brian Hirst please create and configure a Tik tok contract
Lead Distribution Contract (1392) Firefighting Foam - Dicello/Flatirons (TikTok) (TIP) - Contract
@Luke Fontaine, make sure this order's 2nd LP Contract is added in our Split Orders note field using the same format as I updated that field a few weeks back in the Shield Orders dashboard.
@Brian Hirst Babin Human taffciking getting unknown post responeses?
and not showing the lawruler posting? Just vici?
the pones with not match, show Vici successful BUT showing as "not match" in leadspedia
@Cameron Rentch I switched the primary delivery method to TIP. Looking if all the leads went through or not
ok please make sure they get manually pushed through
@Luke Fontaine you probably already did this and sent but can you please resend me the IBEX dashbaords for Ozempic and Hair Relaxer? Thanks I will update with email addresses !
@Cameron Rentch yeah no problem! • Shield Legal - External - Nigh Goldenberg - IBEX • Shield Legal - External - LevGold - IBEX The co-council is different for Ozempic and Hair Relaxers so they're currently on two different dashboards, but I can create one for IBEX all if that's preferred
@Brian Hirst Dicello New Jersey Juv Abuse failing please fix asap
@Brian Hirst , check Case type name. Anthony updated it.
@Cameron Rentch Name updated. Anthony updated the name last night. I sent him another message reminding him again that changing these names while in action is not best practices.
@Ryan Please remind me as well if you ask Anthony to change something.
@Brian Hirst, I did and you, me, him and Luke should meet and clean up the 5-7 versions of Juvenile Hall, Maryland Juvenile Hall, etc....you LP, him LR and Luke and I the TIP Database so that we are using the exact Vertical Names from Leadspedia. I'll be doing the same from LP to Copper for Andy.
@Luke Fontaine please add these to ibex dashboards
@Cameron Rentch I went ahead and added gjpete18@gmail.com to both IBEX dashboards, however I'm not able to share to the other three since they're not gmail accounts
*Thread Reply:* @Ryan this will funnel I to the Flatirons dash
@Luke Fontaine please add jrlegalmarketing@gmail.com to the IBEX dashboards
@Luke Fontaine can you please create a customer dash for Babin Law for Human Trafficking campaign? Thank you
@Cameron Rentch, he's traveling, I'll look and make it...will Babin have more case types coming soon?
tbd I thknk human traficking is his main tort right now
@Cameron Rentch, https://lookerstudio.google.com/reporting/fbcca096-9b60-42e3-a6ae-95b9cb254d83 for Babin
@Mark Maniora @Brian Hirst Please update/create a Tik tok AFFF contract for this matching contract, clone filters from DL-Flatirons Tik Tok contract Firefighting Foam - Bailey Glasser/Meirowitz & Wasserberg (TIP) Turn on active asap
*Thread Reply:* @Brian Hirst Leadspedia is ready
*Thread Reply:* Lead Distribution Contract (1397) Firefighting Foam - Bailey Glasser/Meirowitz & Wasserberg (TikTok) (TIP) - Contract
*Thread Reply:* @Luke Fontaine, when back in office, please check on this being setup if necessary
*Thread Reply:* @Luke Fontaine, when back in office, please check on this being setup if necessary too please
@Luke Fontaine do we have a dashbaord for Legal Case advisors for NJ Juv Hall? Note this is a LEAD sale so use the different columns
@Cameron Rentch yes here's this pre-existing one, just updated to include NJ Juv Hall Shield Legal - External - Legal Case Advisors
@Luke Fontaine can you remove the dup entry for New Jersey?
@Luke Fontaine @Ryan please remove everything except NJ Juv abuse from LCA dash
*Thread Reply:* @Luke Fontaine, please do when you have a free few minutes. Ty.
*Thread Reply:* @Cameron Rentch @Ryan just updated, dash now will only show NJ Juv Abuse
*Thread Reply:* thanks @Luke Fontaine can you also please ensure this email in invited? ocenterpr@gmail.com
*Thread Reply:* @Cameron Rentch invite has gone out
@Luke Fontaine can you please add this email to the LCA dashboard? legalcaseadvisers@gmail.com
@Brian Hirst note that the LL ELG and the LL TC/MC ELG are both showing up the same in lawruler channels:
Email - PFAS - Levinson (TC/MC) - ELG - Vaughda Wooten 417592 Channel - LEV - ELG - 531 - 1407 Vaughda Wooten - S1:PL-FacebookMB-CPCMP-PFAS_01Ah
Email - Firefighting Foam - Levinson (TC/MC) - ELG - Christopher Riley 417544 Channel - LL - ELG - 531 - 1398 Christopher Riley - S1:PL-FacebookMB-CPCMP-FFFACFFAC86CT3
NOTE all other AFFF's are shoing the same (see channel)
LL - ELG - 531 - 1400 Eugene Keller - S1:PL-FacebookMB-JVCMP-Solange7-vid911 LL - ELG - 531 - 1398 Brad Boerner - S1:PL-FacebookMB-JVCMP-Solange7-vid911
@Cameron Rentch Levinson ELG and Levinson ELG TC have the same prefix of "LL - ELG - 531" but the last 4 digit number discerns the contract being TC vs non TC. Do you want an easier way to recognize TC vs non TC? If so @Ryan and I need agree and decide the best way to amend the alt ID to include "TC." I have been using the first 2 law firms to recognize the contract. I am not against putting "TC" and the end of the second law firm's name to quickly recognize "TC" leads vs non. Example: The lead would read "LL - ELGTC - 531 - 1398" instead of "LL - ELG - 531 - 1398."
yes please @Brian Hirst if we could see the "TC/MC" in the channel please that would be great
*Thread Reply:* This one actually went into the pre-exisitng Levinson dash
*Thread Reply:* @Ryan this was also added to the preexisting oakwood dash
*Thread Reply:* @Ryan this was added to the preexisting Oakwood dash
@Ryan @Cameron Rentch Note: Richard from Connex One and I have working meeting tomorrow to establish best way forward to test this dialer efficiently and without interruption to business. At @Tony’s request we will be testing with Intuit first.
@Brian Hirst excellent, let me know how it goes. I’ll be interested to learn the posting configurations.
*Thread Reply:* Me too 😂 . So we will be able to post LP>Connex One>Lawruler. Connex One is very Modular but more complex. I just need to do a labor/benefit analysis with you once I fully understand the process to see if it is something we want to utilize. Richard was very helpful and willing assist in building whatever we would like inside Connex One.
*Thread Reply:* @Mark Maniora when you get a chance, can you submit the request for this campaign?
*Thread Reply:* yes, I will let you know when it's done
*Thread Reply:* @Luke Fontaine I have entered this order into the dash
@Luke Fontaine can you please build me a separate dashbaord for Levinson Legal (TC/MC) PFAS and AFFF (they can be in same dash). However, instead of "daily revenue" per and "total revenue", I need our actual daily spend + total spend. If you send to me I will review before we send links out
*Thread Reply:* @Cameron Rentch yeah bo problem, I can get that over by midday tomorrow if that works
*Thread Reply:* @Luke Fontaine that should be fine thanks
*Thread Reply:* @Cameron Rentch is this what you were looking for? Shield Legal - External - FireFoam/PFAS - Levinson
*Thread Reply:* @Luke Fontaine I need a dash that ONLY has the TC/MC campaigns. Thank you. We can do a separate one for the NON TC/MC campaigns. Both at our cost please.
*Thread Reply:* @Luke Fontaine good morning let me know when this is ready and if you need clarification on anything
*Thread Reply:* @Cameron Rentch added additional filter, dash will now only show TC/MC campaigns
@Luke Fontaine please add PA JUV Hall to Legal Case Advisor dash, campaign start date 2/26/24
@Brian Hirst @Mike what happeneed here? A posting failure I have never seen
@Cameron Rentch I've never seen that one before either. it sounds like LR queued the post for some reason instead of posting it in realtime. they might be working on something and had to temporarily queue post processing
I just tried to manually sell again and it did the same thing?
I'd give it a little while and try again in case LR is working on something on their end
the first time this happened was yesterday, today I tried manually selling and happened again
I’ve never seen “API post queued” LP posted it correctly but it looks like LR wasn’t ready to receive it.
@Brian Hirst can you please investigate and put a note into LR for this?
@Brian Hirst lmk if there's anything I can do on my end to help
@Brian Hirst @Ryan for tomorrow on the connect stuff, we need to prioritize this ahead of other projects we can discuss but a lot of reasons
if you guys can jump on a very quick call I will explain the issues and importance, too much to type out
@Luke Fontaine can you please create a separate dash now for the Levinson/ELG AFFF and PFAS (one separate from the Levinson TC/MC AFFF and PFAS)
*Thread Reply:* @Cameron Rentch do you want this one to specifically exclude TC/MC or have it as all AFFF PFAS Levinson/ELG
*Thread Reply:* Here's all PFAS/AFFF cases for Levinson/ELG Shield Legal - External - FireFoam/PFAS - Levinson/ELG
@Luke Fontaine please add this email address to the dash for Levinson TC/MC smb202021401@gmail.com
@Cameron Rentch Making delivery methods for Connex One now. James from Connex is doing something on his end to make it more usable for agents before I switch it on, I will do highest volume campaigns first and we can switch them on when I get the OK.
Hey @Ryan today is first day of non-daylight savings, do you want me to adjust lambda triggers?
*Thread Reply:* They are updated, lmk if anything looks off on the triggers
@Cameron Rentch at 11 we are switching asbestos and firefoam from VICI to ConnexOne. James and I will be doing it live while we're on a call to make sure everything goes smoothly
ok @Brian Hirst thanks and keep me posted
@Cameron Rentch Firefoam and Asbestos are live and delivering to Connex and TIP simultaneously. I watched "MLQIUJ0V" and "P6ONLHS4" come in real time and get pushed to the systems successfully. I am getting a priority list from TIP on next verticals to switch over. We are wanting to migrate whole verticals at a time.
We are still working on some agent usability features before we switch 100%
@Brian Hirst @Luke Fontaine, please make sure these are all in TypeForm and in Copper, plus when channels are ready, I added them to the SL database too for you. I did these now because Anthony S. on the TIP side needs to see these so he builds the LawRuler.TypeofCase name, he names them the same.
@Cameron Rentch switching back to Vici temporarily until Connex figures something out. I was told all agents are back in Vici
@Brian Hirst @Mike duplicate data issues - we need to resolve this ASAP. Big problem, more than caleb>
@Cameron Rentch It is only Caleb. Only the highlighted leads are duplicates. He pulled all the intuit and Paraquat leads
fyi, we've already created a new zap for caleb's paraquat form.. just waiting for data to see if it fixed it. If it does fix it we'll go through and make new zaps for any other misbehaving forms
Look on sheet there are leads from Jack (JA) @Brian Hirst @Mike
@Cameron Rentch I should have worded that differently. So these are duplicates but they are different than the instances we are trying to fix. They are owned by different media buyers and came in at different times. Every other lead I've investigated on that sheet, they are owned by the same Media buyer and came into LP at the same exact time
@Cameron Rentch just wanted to follow up here.. Dwight and I started researching from the beginning of things while Brian started at the end. we met in the middle, and our research concluded this is 100% not on our end. It appears to be an issue on the MBs side. We're working with Caleb to get a new form and zap live so we can test
@Cameron Rentch @Ryan @Brian Hirst I spoke with @Caleb Peters this morning, and he's reporting that the new form/zap we setup yesterday is working as expected so far. we'll continue to monitor things to be sure. fyi @Dwight Thomas
*Thread Reply:* @Ryan this will flow into the preexisting Oakwood dash
*Thread Reply:* @Ryan this will filter into the dl and flatirons dash
*Thread Reply:* @Ryan same with this one, will filter into a preexisting dash
*Thread Reply:* @Ryan I believe there's already a torticity dash this will funnel into but double checking
@Mike @Cameron Rentch I've been checking LP daily for Intuit and paraquat leads that shared names, phone numbers, and created on date. None have come in since the form change on March 13th.
@Mark Maniora @Brian Hirst add caleb to onstar drive tracking channel
@Brian Hirst we are restarting Crump/ELG AFFF (two contracts in leadspedia). Do these need to be reconfigured for the new dialer or still on vici?
@Cameron Rentch both are still going to VICI.
As far as the new dialer is concerned, I have not received any updates on the the blocks from Lawruler, or on how TIP would like to proceed.
ok great just making sure @Brian Hirst
@Brian Hirst @Ryan I need a quick analytics pivot table created
The last 500 AFFF cases most recent should be all campaigns
Of these the count and percentage of each specific diagnosis
Ie prostate, kidney, thyroid etc
Need asap pls thank you
@Ryan @Cameron Rentch I'm off to a doctor's apt. I can start at 4.
@Cameron Rentch Here is Disease, count of disease, and percentage of 500 for that disease, for the last 500 sign ups. I put the lead ID's, first name, last name, and CaseType in there. Let me know if you want me to take it out.
I couldn't do a pivot table because the diagnosis data was messy, ie: diagnosis written as "Stage 2 Prostate Cancer," "Prostate Cancer," "Cancerous lump on Prostate,"
@Brian Hirst, can I get all emails and phones for all Signed e-Sign Camp Lejeune in LawRuler (all case types) please. This is for @Cameron Rentch and the TikTok team. I've already gotten all for BP Camp Lejeune to create a super look-a-like list.
Ty!
@Brian Hirst , never mind. I went ahead and got it and delivered it
*Thread Reply:* Thanks I’m doing a SQL training and got sucked into it
@Luke Fontaine @Ryan can you please make a dashbaord for me ASAP(need today) - separate one for Levinson SJ/BG AFFF? (need this on its own dash). Thank you
@Cameron Rentch no problem, I'll get that out to you shortly
@Cameron Rentch Shield Legal - External - FireFoam - Bailey Glasser/Levinson/SJ
@Quint Underwood, any update on the TIP Gen Fin Log morning runs?
And see below from Caleb, the @IntradaySoldLeads_Microservice he says is having some struggles. Can you check logs <#G01DR080C23|> here and in AWS and see if we see issues?
*Thread Reply:* @Quint Underwood, per @Brian Hirst, the ISL slash command is setup correclty but not working. Please advise.
*Thread Reply:* @Ryan I think there are some optimizations that I can make to make the commands run faster and avoid timing out. I'll try to look into this more & figure out a plan of action on Sunday
*Thread Reply:* It’s causing Luke extra time and trust
@IntradaySoldLeads_Microservice has joined the channel
@Mark Hines @Mike @Brian Hirst @Ryan we need to get the trusted form certificate captured in all FB native forms. Please read, connect, develope and plan and let me know how we intend to, and how quickly we can test and integrate. Start with "birth injury" please.
https://activeprospect.com/blog/how-to-claim-facebook-lead-ads-certificates-with-trustedform/
@Cameron Rentch, Mike and I will add this update to his Facebook Ads app while doing the e-Sign CRM events too. We kickoff in 10 minutes, @Mike and I do....
@Brian Hirst, for these case types, Maryland JH, Penn JH, Adventist, New Jersey JH all failed, can you check the below.
TY. FYI @Quint Underwood
@Brian Hirst we need to create new postings to Broughton for birth injury
1 = we have existing campaign that passes fields from this existing landing page (should be using these fields on current posting. We need to add the "trusted form certificate data from the landing pages and pass into posting to BP. https://shieldcasejustice.com/birth-injury/. Do not activate this contract yet, keep paused but re-test.
You can see the trusted form details in leadspedia here (see this lead ID below)
2= we are running TT and FB FORMs with drop down options for each. We will need to create a SEPARATE contract in leadspedia for the FORMS. It is the only way I can apply the filters so the proper leads are passed to BP. @Mike @Ryan are working on implementing the trsuted form on these forms but we do NOT need to wait, and can add the trusted form stuff later. Please let me know when contract is built (leave paused for now, but I will then add filters)
Need to escalate this asap, test both postings.
*Thread Reply:* @Brian Hirst, once you make these new contracts, make sure you let me and @Mark Maniora know so we can update Copper with the 2 new contract IDs so they link to a Copper deal.
*Thread Reply:* Will do. I emailed Sean asking where he wants that info posted. Once he emails, I will test, he'll confirm, then I'll make a new contract.
I would like to get the above done and tested by tomorrow please
@Cameron Rentch I saw the email from last week, I'll email Sean to see what field he wants that piece of data in and make sure everything looks good.
@Cameron Rentch is the new Birth Injury Contract going to be the same as the current just with different filters?
*Thread Reply:* It is the same, but it has different dropouts and values as a side from checkboxes
*Thread Reply:* I will have to create the filters
@Brian Hirst let’s name it same but add (Form)
@Cameron Rentch, please check your gmail inbox for invite from Copper. Please click, setup your login by choosing login with Google with your Shield Legal account. Once logged in, let me know. I'm asking because their database we have piped into the new Data Warehouse is not showing me all of your Companies / Advertisers I uploaded and hoping you "activating your login" might solve this. Please do ASAP.
FYI @Andy Rogers, if this does not fix my issue, will need to ping Vikki for some technical support.
@Ryan I dont have an invite anywhere?
@Cameron Rentch, invite resent just now, check Spam and Search for "Copper"
@Cameron Rentch, nice. I see you got into the system. Thank you.
@Ryan @Brian Hirst not seeing any automated leads posting into lejeune or cpap accounts
please check on systems and services asap^
no leads at 5am were sent to channels^ @Ryan @Brian Hirst
@Brian Hirst will check in LR to see if these were manual status changes that causes them to SKIP our automation to post in channel. We have zero failures in Zapier.
Signed contract bot is working. Slash commands are also working for the media buyers. Github looks good but checking all branches
@Ryan Github branches look good. The sold leads posting stopped working yesterday after the 8am posting. Leadspedia has been timing out a lot lately trying to pull reports. I don't know if it has something to do with that. Quint will have to look at the logs.
@Cameron Rentch working on a fix, shouldn't be long. In the meantime Media Buyers can use the slash command to see sold leads
@Quint Underwood, just sent you text. Can you ping @Brian Hirst and figure this out?
<#C03GKEAN2SD|camp-lejeune> #cpap-recall and channel wide
*Thread Reply:* @Cameron Rentch, I texted Quint and tagged him here.
*Thread Reply:* @Cameron Rentch @Brian Hirst, @Quint Underwood responded and will investigate soon after some morning meeting lockdown.
Camp Lejeune and CPAP are fine. Signings are posting in. It's the ISL microservice that is failing in all channels @Quint Underwood
@Ryan Just pushed a fix for the failed ISL runs from today, there were incorrect channels mappings for the following channels:
onstar-drive-tracking,332
uhc-optum-change-databreach,331
state-farm-denial,320
(They had the Leadspedia vertical names instead of the slack channel names)
@Brian Hirst , that is something I think you are doing by accident
@Brian Hirst @Mark Maniora please create another leadspedia contract for Crump/Levin Perconti (Similar to what we did for Broughton) call it "FORM", please and let me know as soon as it is ready
*Thread Reply:* @Brian Hirst Leadspedia is ready.
Lead Distribution Contract (1468) Birth Injury (Form) - Crump/Levin Perconti - Lead
*Thread Reply:* @Mark Maniora, please make sure Copper is updated!
*Thread Reply:* @Mark Maniora I believe these will be going to TIP like the other Birth Injury contract for BCL LP @Cameron Rentch Please confirm
do NOT activate it but please let me know when ready^
@Cameron Rentch Is the new Birth Injury - Crump/Levin Perconti going to TIP for contract signings?
have one say (FORM) in the contract name please
Lead Distribution Contract (1468) Birth Injury (Form) - Crump/Levin Perconti (TIP) - Contract
@Mike @Mark Hines please review this asap.
I think we need to implement some thing on our landing pages and website immediately for anyone coming in especially through California
https://termageddon.com/california-invasion-of-privacy-act-cipa-requirements-for-website-tracking/
Can you both see if there are any types of automatic except buttons we can push through WordPress or from our servers to users?
@Cameron Rentch Do you mean by putting a button for accepting cookies etc? Or something different t? Thanks
@Mark Hines @Mike if there isnt something within our current stack, we could always look to use a system like this one:
https://usercentrics.com/consent-management-platform-powered-by-usercentrics/
actually this one may be better? https://termageddon.com/tools/cookie-consent-solution/
@Cameron Rentch ok. We use a cookie plugin for BLX. I will compare it to these and see what the best solution is and get back to you and Mike to make a final decision before implementing. Thanks
can you loan the plugoin to a lander we arent using at the moment (maybe one of the new video games ones) so I can see how it is displayed/appears?
@Cameron Rentch Will do. I can do it by morning.
@Mark Hines top priority on this in the am
@Cameron Rentch I setup and posted the test site in Wordpress. Let me know if you would rather me post it here. Thanks
@Mike, what is your latest on these Zap errors: [ALERT] Possible error on your TikTok Native Forms Postback Zap!
@Ryan can we disable those alerts for that zap? there are several places in the code where a 401 is returned based on certain criteria. it does not necessarily indicate a problem with the zap.
@Mike , got it. as long as we know when they really error.
@Brian Hirst @Mark Maniora I need a FORM contract built for SaddleRock Roundup in leadspedia.
Brian note that the fields that are being used for this post are here...(see zapier from caleb's account)
please let me know when ready but leave OFF for now. Thanks
*Thread Reply:* @Mark Maniora LMK when this contract is built
*Thread Reply:* @Brian Hirst contract is ready in LP:
Lead Distribution Contract (1469) Round Up (Form) - SaddleRock Law Group (TIP) - Contract
*Thread Reply:* @Cameron Rentch Do you want me to add a budget and cost per to the new SaddleRock Round Up contract? I just put $0 for now.
*Thread Reply:* @Cameron Rentch this contract is delivering to TIP
@Cameron Rentch Same Casetype in Lawruler or new form only casetype?
@Brian Hirst please see email from Broughton. the trusted form is not passing through in posting from leads that are on the landing pages. @Mike what is status of getting the FB form integration for trusted form. Need this asap think it has been a few weeks now
*Thread Reply:* @Mike is working on wrapping up FB CRM Event testing and juggling the FB Trusted Forms service too @Cameron Rentch.
@Cameron Rentch The TrustedForm field is not populated in Leadspedia on the leads that sold to BP, so there isn't something to pass.
@Brian Hirst it IS there if it comes from a landing page. @Mike is working on trusted form integration from forms
@Cameron Rentch that lead didn't sell. Looks like the majority of leads are coming from forms. Seam said it looked good on the test. I'll send another and make sure it is passing
@Mike What field are you posting the trusted form into, it should be "trustedformcert_id," yeah?
@Cameron Rentch Investigating on a vertical level the ability to pass that value needed to be turned on
@Mike If it is mapped to "trusted_form" lmk and I will change it to that
@Brian Hirst trusted form is not setup yet on the fb forms
@Cameron Rentch I figured it out. Leads that were supposed to sell on the form contracts were selling on the landing page contract. The filter on the contract is not operating as intended. This lead "AVN2O8IT" Was sold on the "Birth Injury - Broughton Partners - Lead" Contract. The filter on that contract says to not accept leads that contain "FORM" in the S1 field but that is not happening, if you look at the S1 field for that lead it is "PL-FacebookMB-MLBirthInjuryFORM1." That is why the "trusted form" field has not been populated. Let me know if that makes sense or not, it took me a while to work through that.
@Brian Hirst please get with LeadsPedia and have them give us feedback on the filters.
We need anything with “Form” contained in the S1 to only sell to the “form” contract.
@Cameron Rentch Working with LP now. Also the TrustedForm Cert URL is passing to BP still when it is populated
@Cameron Rentch Filter setup was fine, the vertical itself needed some editing. Filter is now working as intended. Also, Sean is asking for the lead's that have come overs TrustedForm Cert URLs. I don't know what to tell him.
@Cameron Rentch @Ryan I need the account ids, pixel ids, and access tokens from the MBs for the FB CRM events stuff
@Mike, I'll setup a meeting for that this week with MBs.
@Mike @Ryan this is all thru Nitido, but it's fine to have a mtg with media buyers. I would suggest you create a google sheet and have them simply complete the information
@Cameron Rentch, will do. Then we will send it to the Nitido.
@Ryan I would suggest actually send to media buyers first, but copy Nitido on the document
@Mike URGENT. Please get with @Mark Hines immedaitely. Many sites are failing to reach "thank you page" after we installed a cookie plugin for complicance.
*Thread Reply:* @Ryan I'm going to have to reschedule our 11am call please. unfortunately mark isn't able to fix this ☝️:skintone2: on his end, so I've got to do it
@Mike I don't think it is the cookie plugin at this point but not sure. I submitted a ticket. Because everything is fine on some sites that have the cookie plugin. And I removed it from one site, and still not working. More explanation in the ticket. Thank You!
@Brian Hirst @Mark Maniora I am going to need additional contracts created in leasdpedia for short form Firefighting foam campaigns. Currently leads are not selling properly in leadspedia due to filter issues. To start, please create Nations Dickerson, Nations Wenner, in them please create "FORM". I will post the fields to map for this. Standby. When ready, notify me but do not activate
*Thread Reply:* New Contracts:
Lead Distribution Contract (1478) Firefighting Foam (Form) - Nations/Dickerson (TIP) - Contract
*Thread Reply:* Lead Distribution Contract (1479) Firefighting Foam (Form) - Nations/Wenner (TIP) - Contract
*Thread Reply:* @Brian Hirst @Cameron Rentch FYI ^
@Brian Hirst @Mark Maniora if you guys can please escalte the abiove - I have leads sitting that need to get pushed out let me know when ready^ thank you
creating those new contracts now. @Brian Hirst I will send the names to you soon
@Cameron Rentch both Firefoam Form contracts are ready
thanks @Brian Hirst and @Mark Maniora
@Cameron Rentch @Mark Hines all of the broken sites have been updated. @Mark Hines please let me know if I overlooked any, or if you notice anything strange
@Brian Hirst @Mark Maniora can you please create and set up another "FORM" leadspedia contract for cochran? Let me know when it is ready, please leave inactive. Thank you. Moving forward on all new Firefoam, let's follow the process of 3 leadspedia contracts. thank you
@Brian Hirst leadspedia is ready:
Lead Distribution Contract (1500) Firefighting Foam (Form) - Cochran Firm (TIP) - Contract
@Brian Hirst @Mark Maniora can you please create form contracts for each of these Levinson Legal ELG firefoam Levinson Legal ELG TC/MC firefoam Keep pauwed but let me know when ready^
@Brian Hirst
Lead Distribution Contract (1507) Firefighting Foam (Form) - Levinson Legal/ELG (TIP) - Contract
*Thread Reply:* Lead Distribution Contract (1508) Firefighting Foam (Form) - Levinson Legal (TC/MC)/ELG (TIP) - Contract
@Ryan @Luke Fontaine can you resend me the dashboard for Dicello Lejeune GWPB 2?
@Luke Fontaine @Cameron Rentch https://lookerstudio.google.com/reporting/a2b667f8-1757-4f8a-87c3-82587e020d9e/page/p_ib80f43vxc
@Brian Hirst @Mark Maniora good morning need a FORM contract bult for Crump ELF forefoam. When ready please let me know but leave off. Ty
Lead Distribution Contract (1509) Firefighting Foam (Form) - Crump/ELG (TIP) - Contract
FYI @Brian Hirst
*Thread Reply:* @Ryan yep, contract was added to copper
@Luke Fontaine good morning can you please create Baypoint ELG PFAS dash. Please add also add craig smith (craig@shield-legal.com) to the access
*Thread Reply:* No problem, I'll get that created and sent out
@Mark Maniora @Brian Hirst please create "form" contract for Firefighting Foam - Dicello/Flatirons (TIP) - Contract in leadspedia. When completed leave paused for time being.
@Cameron Rentch @Ryan update on dbt financials; I've finished creating the one to many connections throughout the joining tables, will be connecting and setting up visuals through lookerenterprise. keep an eye out for it to come
@Cameron Rentch, this will have you able to reconcile, have Mark M. / Greg O. / Brian T. / Malissa P. clean up Copper to get the dashboard accurate for you and Marc for Paid Not Spent and By Account Manager (new Marc requested page). Thanks @Luke Fontaine, plesae send Looker Enterprise pages once ready with "rolled up dailies" version.
@Ryan please send only me the new paid vs spent sheets before marc reviews.
*Thread Reply:* That’s the plan. FYI @Luke Fontaine
*Thread Reply:* @Luke Fontaine, where is the new Paid vs Spent tab? @Cameron Rentch needs it ASAP, our investor partner inquired again this morning on status.
*Thread Reply:* @Ryan my contract actually states that my scope of work only includes lookerstudio campaign client dashboards. Much like the affiliates EOM, I again was doing this, and the very generous work far outside of my contracted scope with the dbt modeling set up without requesting additional compensation as a favor, but I hear you loud and clear and will be pulling back to our contract.
*Thread Reply:* @Luke Fontaine , what is the status of the dashboard
*Thread Reply:* @Luke Fontaine , what is status of this?
*Thread Reply:* @Luke Fontaine, the data for the Shield Legal Client Dashboard in Looker Enterprise / Retool is using this exact same data as the Paid Vs. Spent dashboard. The reason I gave you this project becuase the data is the same except how we name the columns and what columns we show the "external Shield law firm / investor clients".
So is the Paid Vs. Spent "roll up" in Looker Enterprise ready? If not, confirm so and if you are declining to finish this project, also let me know. I can give this work out to an internal resource and have you focus on the Flatirons draft for the SL client dashboard embed.
@Luke Fontaine can you please add email address here to Napoli AFK Dashboard please? (they said it is a google address) b@afk-law.com
*Thread Reply:* @Cameron Rentch just tried that email, it doesn't look compatible. Did they send over the correct email address, and/or are sure it's a google based address?
@Ryan I already told MARC. I need to review it first I will do it when I get into the office this morning please post the link here.
@Cameron Rentch, got it. Marc texted me, I told him waiting on @Luke Fontaine to send us the rolled up dash version for our validation.
Yes @Ryan Marc wants a tab that shows all campaigns that are under Andy so he can see profitability of campaigns related to only his sales.
He let me know, that will be easy with the new dashboard @Luke Fontaine is finishing, since Andy's deals, law firms in copper will be tied to the daily financials.
@Luke Fontaine , need a spreadsheet that lists all the information below in columns. One row per dashboard.
- Dashboard name.
- Dashboard URL.
- Dashboard recipients that it is shared to
- Dashboard builder/owner (you or me or Brian)
Once you have that, share to me, @Cameron Rentch and @Andy Rogers so we can confirm which ones can be archived and which ones are active.
We need this by Monday, the 15th. Thank you.
@Ryan @Luke Fontaine LCA dash is showing another client (Lakin). We need to get this fixed immediately before customer sees another customer in their dash
@Ryan sent requested spreadsheet with current client dashboards, type below as well
*Thread Reply:* Thanks Luke, will take a look soon and let you know if it has what we need. Ty.
*Thread Reply:* @Luke Fontaine, when you have time today, please make me "Owner" of all of these please.
*Thread Reply:* @Luke Fontaine, see new Column A in your Google Sheet. Please make me owner of all active ones and then please move the Archive ones to your Trash but do not delete them permanently. The Trash folder in LookerStudio is just an archive folder essentially and will not delete them.
*Thread Reply:* @Luke Fontaine , have you made me owner yet? And have we archived the ones cam and Andy noted?
*Thread Reply:* @Ryan no, I’ll work on these items this next week.
*Thread Reply:* @Luke Fontaine, did we get these moved to be owned by me yet? Want to make that best practice each time you create one.
*Thread Reply:* @Luke Fontaine, please advise on this.
@Luke Fontaine, today after financials, please reconcile LawRuler / TIP Dashboard / Shield Dashboard for January 2024. Seems most of the irreconciled are AFFF. Please use Dennis to pull reports for you to make sure you have them accurate to LawRuler. We'll want to get 2024 YTD completed and start with one month at a time:
*Thread Reply:* @Luke Fontaine, please post the reconciled January 2024 snapshot here please
*Thread Reply:* @Luke Fontaine , great. Please post the February as is right now. Then please work on that month next for tomorrow.
@Mike, please setup a Google User Group called flatirons-dash@shield-legal.com and put @Brian Hirst, @Nicholas McFadden and myself on it please. We will be using it as a Looker Enterprise licensed user for our external dashboard portal login, Looker embed oauth, and clients viewing pleasure of in-development dashboard and user portal application.
Please let us know when ready. Ty!
FYI @Cameron Rentch
@Ryan done! I sent a test email that you guys should be receiving. please lmk if you didn't get it
@Luke Fontaine please confirm you can get me the TIP Invoices x Month sheet ready for Cost Per Worked Leads and send over to me and @Nicholas McFadden by EOD.
*Thread Reply:* @Luke Fontaine, please confirm, working on the other items now.
https://docs.google.com/spreadsheets/d/1OyNqAoCk76OLYpEaizcl5sWDfeZcv5MRMqQ4AD_gJ0Y/edit?usp=sharing
*Thread Reply:* @Ryan I never received an updated contract that included affiliates EOM
*Thread Reply:* It's a part of the shield financials in the contract from my review of your contract
*Thread Reply:* If you cannot do this in replace of your email marketing and call automation tasks you have not done for 8-9 months, let me know.
*Thread Reply:* I'm not sure what copy you're looking at, but in the signed version I have it is not included. Those are not equivalent items. Additionally, those were removed per your request; I even offered to help train the replacement at no additional cost, even though training is not part of my contract either.
*Thread Reply:* it's an, I'm going to stick to my contract.
@Luke Fontaine, starting today I need you to perform the morning financials everyday of the week including weekends.
*Thread Reply:* It's in your contract @Luke Fontaine
FYI @deleted-U05DY7UGM2L
@Luke Fontaine, please confirm these are all showing Signed & Declined in TIP Dash and reflected in Shield Legal for me ASAP today,.
@Luke Fontaine, please advise on above. Tony is waiting for some information on this confirmation.
@Ryan @Nicholas McFadden Flatirons has a couple of requests:
In the data(dashbaord) and in the data that Nick provides to Flatirons weekly with Dave's spreadsheet can we please:
- breakout /split the Camp Lejeune EO vs NON EO cases
- show new entries for the campaigns that have updated criteria as of July 5 (this is when the updated criteria was applied and price increases were applied a. AFFF (now only 6 diagnoses accepted b. IL abuse (now only claimants 41 or under are accepted Please let me know if you have any questions.
*Thread Reply:* @Nicholas McFadden, I will look into this manual dashboard update shortly.
*Thread Reply:* @Ryan let’s hop on a call to discuss
*Thread Reply:* @Cameron Rentch
- Dashboard has Customer column added that shows EO vs Non-EO and they can use the Law Firms filter to see it broken out.
- Calling Nick now on the #2 bullet
*Thread Reply:* @Nicholas McFadden https://meet.google.com/fsi-eyec-qta?authuser=0
@Ryan @Ryan the "/" command to pull leads is not working, please investigate and fix in <#C07H4K9BWTH|video-gaming-sextortion> channel
*Thread Reply:* @Quint Underwood , can you investigate?
*Thread Reply:* @Brian Hirst , this requires the @IntradaySoldLeads_Microservice setup.
*Thread Reply:* It’s a part of the service as a whole. The slack slash commands in the CSV.
*Thread Reply:* @Quint Underwood , fyi. Let @Brian Hirst investigate first.
*Thread Reply:* @Cameron Rentch, we found the issue. The Vertical is missing the MB vertical field, which is required to run the /isl command. Asked Mark H. to add it and we can test that it's working.
@Quint Underwood, the media buyer field has been added, will test it now.
*Thread Reply:* Worked for me now, appears to be fixed
*Thread Reply:* It does, marc went back and checked the verticals to make sure he didn’t forget that code on anything else recently
@Ryan @Brian Hirst @Mark Maniora note from Jackson re CA Juv Hall miller neilsen
I'm not sure if you're the right person to bring this up with but CA Juv Hall Miller Nielsen doesn't have a Form Fields tab, so S1's generate in the initial posting of the lead but without the Form Fields tab, all S1's need to be added manually into the reports. Just wanted to bring that up. If it's not a big deal for your purposes, I totally understand. I have no problem adding it manually, but others who utilize the reporting functions and are less familiar with the system might have trouble finding it.
I'd be glad to help you with that. Here's a Python approach to allocate leads fairly and randomly among your four media buyers based on their daily spending:
- Import necessary libraries: import random import datetime
Define media buyer data: mediabuyers = { "Buyer1": {"dailyspending": 1000, "campaigncategories": ["Category1", "Category2"]}, "Buyer2": {"dailyspending": 500, "campaigncategories": ["Category2", "Category3"]}, "Buyer3": {"dailyspending": 1500, "campaigncategories": ["Category1", "Category3"]}, "Buyer4": {"dailyspending": 800, "campaign_categories": ["Category2", "Category4"]} }
Calculate total daily spending: totaldailyspending = sum(buyer["dailyspending"] for buyer in mediabuyers.values())
Calculate allocation weights based on spending: allocationweights = {buyer: buyer["dailyspending"] / totaldailyspending for buyer in media_buyers}
Create a function to allocate leads: def allocatelead(referraldata):
Extract referral date
referraldate = referraldata["referral_date"]
Calculate day of the week (0 for Monday, 1 for Tuesday, etc.)
dayofweek = referral_date.weekday()
Choose a random number between 0 and 1
random_number = random.random()
Iterate through media buyers and allocate lead based on weights
cumulativeweight = 0 for buyer, weight in allocationweights.items(): cumulativeweight += weight if randomnumber <= cumulative_weight: return buyer
Use the function to allocate leads: referraldata = { "referraldate": datetime.datetime(2024, 9, 3) # Example referral date }
assignedbuyer = allocatelead(referraldata) print(f"Lead allocated to: {assignedbuyer}")
This approach ensures fair and random allocation of leads based on the media buyers' daily spending. You can replace the example referral date with the actual referral data you have.
@Brian Hirst, can we deactivate Vici delivery methods since we'll be using FiveNine soon as the full time dialer. Just check with Tony on Vici before you do anything.
FYI @Cameron Rentch and @Mark Maniora
Yep. Mark and I are chatting about it right now
@Nicholas McFadden, FYI for the Monday call with SOK. FYI @Cameron Rentch, we'll advise after if we have any issues with offering the industry standard API pingback when they retain a lead.
Backstory: Shield media buying team is generating leads. Cameron wants all DSS microservices (TIP Slack Alerts, Slack /isl, regular financial reporting) to function normally even though these Asbestos leads will be email-routed to the SOK Call Center lead intake system.
CR Requirements: • Slack Alerts w/o MBs knowing a difference • TIP Fin Log table must be updated for COUNTS, etc. Best Solution: • Pass Leadspedia Lead with LP Lead ID / Alt ID and S1 to LawRuler as normal • LawRuler Automation to deliver lead to SOK CC just like they require via email and code in Subject Line and fields as needed (Mark Maniora) + LR Lead ID with custom 3P Call Center Contacted default status. • LawRuler Automation to setup like "TIP Slack Alerts" for "Signed e-Sign - 3P Call Center FINAL" to envoke AWS TIP Slack Alert lambda • Request SOK's Bill and Team to use the LawRuler and LawRuler Lead_ID to ping back and change the status from "3P Call Center Contacted" to "Signed e-Sign - 3P Call Center FINAL". ◦ I will work with Tony and team to get a LawRuler case type called "Asbestos - SOK - Shield leads - Sokolove" created, and Bill receiving the leads tested and validated ◦ @Nicholas McFadden please plan to work with our Integration team & SOK team get them using the LawRuler API with the LR Lead ID to update to new status tested and validated.
@Brian Hirst can you set up new channels for Dr Riceberg Prescription Medication Overpayment LA County Foster Care Abuse
@Cameron Rentch, here you go. Let us know.
FYI @Mark Maniora @Nicholas McFadden
@Brian Hirst @Mark Maniora please set up channel for new nursing home abuse vertical. Note the old channel is not working because it is attached to a old vertical.
@Brian Hirst @Mark Maniora The new one has Litigation on the end
"This is a new Nursing Home Abuse Vertical. There is one in there already, but this one is a new one for a new/specific with Litigation on the end and just to be clear, the vertical ID is 368. Thanks"
Lead Type: Nursing Home Abuse Litigation
@Nicholas McFadden, FYI. If the test tomorrow from Stephanie at SOK does not update our record in LawRuler here, then will need you to connect with their developer and figure out what's going on, see your gmail inbox. https://tortintakeprofessionals.lawruler.com/update-lead.aspx?lead=593215
*Thread Reply:* @Nicholas McFadden
So the lead in LawRuler did not update the SOK sent custom values from how they are now in LR here and how she said their payload updated them: https://tortintakeprofessionals.lawruler.com/update-lead.aspx?lead=593215
Their webhook DID update our Status correctly, but the custom fields are not showing the udpated value of 3 or Open so I wonder if their custom21941 intake id have to match. See my email.
IF she says they have to match, they retest with matching values, if she said id does not have to match, them we need to figure out why their update to the custom fields is not happening.
PAYLOAD
LeadId: "593215" Custom21941: "04371541" Custom21942: "3" Status: "Signed e-Sign 3P Call Center Used" Custom21943: "Open"
@Brian Hirst @Mark Maniora, please send a new test lead fro LP to LR to SOKCC for their integration team. Please let me know the Lead ID and once it is send to them.
*Thread Reply:* If we can do this ASAP guys, that would be appreciated.
@Ryan can you check out this customer dash Levinson ELG TC/MC
It is the one attached to
Email is smb202021401@gmail.com
Can you update and resend me working link
*Thread Reply:* @Cameron Rentch, just to make sure, do you want daily_revenue (client cost) or daily spend on these since a TC?
Don't trust Luke's old work, so making sure
@Ryan actual spend on this specific dash
@Mark Maniora @Brian Thiessen can we make sure that the "TC" and "MC" is visible in channel for <#C03GSRB8RCN|ca-juvenile-hall-abuse>?
This one, as example, is "MC" but we cant tell without researching the lead itself
ACTS - LEV - 495 - 1601
@Cameron Rentch yes, I will update that
sorry meant to tag @Brian Hirst my fault thanks @Mark Maniora
@Ryan can you fix this dash - customer says not working > The Dashboard link is located here: FYF/ACTS - CA Juv Hall > To access your dashboard, I need email addresses that are either associated with a Google account, or a "gmail" address. We utilize Looker as part of Google Data Studio and this is a requirement for access. Please reply to this email with the email addresses and we will add them with permissions asap. >
@Cameron Rentch, it's working for me. Must have been a DataStudio glitch.
@Ryan can you please create a NEW dashboard for this campaign
ACTS - LEV MC - 495
send invite to smb202021401@gmail.com
Note this should have all metrics like every regular customer. Thank you!
@Ryan @Cameron Rentch Note: with the new Leadspedia UI/UX some things have moved. For me the biggest one I used that has moved was the filters. They are now on the far right towards the top. Took me a while to figure that one out.
ah thanks @Brian Hirst helpful! I am still getting used to it
@Ryan @Brian Hirst alot of contracts are not posting into multiple channels...we have a bunch signed that are not posting into channels. NEC, Paraquat, AFFF
I am already investigating the logs...it's working in some cases, let me continue looking.
Firefoam = 16 Powerport = 4 CA juv hall = 1 LA County Foster = 1 Chowchilla = 1 Depo provera = 13 IL Abuse = 1 Md Abuse = 2 NH abuse = 1 PA abuse = 2 Paraquat = 2 NEC = 4
I just did manual count with tony of all e-signs, these are counts currently - it looks like channels stopped working at some point tpday
@Ryan I’ll delete some channels from PG admin that aren’t being used any more. This has been an issue in the past but I was under the impression those limits were expanded.
@Brian Hirst it seems like something from a timing perspective happened we we set up the Depo Provera channel, is it a "-" in the case type or seomthing?
*Thread Reply:* They operate individual of each other for contract postings. ISL can be affected by this but those are working. I suspect limits have been reached. Deleting unused verticals may help this. We’ll see though.
@Cameron Rentch, it's working again I think. I'm in the logs, I see deals coming over. I have resent <#C07RT802PQW|depo-provera> ones
@Ryan what was the issue or what was changed? The only thing I changed for the posting was deleting the method from PG admin and re-adding
Just the Depo Provera row. Called it a method bc I think of it as its own delivery method for signings.
@Cameron Rentch @Brian Hirst, not sure but it appears there was an AWS Lightsail database access outage earlier. Quint was struggling to connect earlier and of the ones I resent just now, they worked fine. Of the ones that didn't post those were LawRuler canceled e-sign mistakes by agents. So I reconciled all below.
~Firefoam = 16~ ~Powerport = 4~ ~CA juv hall = 1 (2 of 2 posted~ LA County Foster = 1 ~Chowchilla = 1~ ~Depo provera = 13~ ~IL Abuse = 1~ ~Md Abuse = 2~ ~NH abuse = 1~ PA abuse = 2 ~Paraquat = 2 (2)~ ~NEC = 4 (3)~
@Cameron Rentch does Conyers Biolab get a channel? I see most of the leads aren't going through Leadspedia
@Brian Hirst can you pls set up channel for Private boarding school abuse? Thank you
@Brian Hirst can you set up channel for Ethylene Oxide
@Brian Hirst can you set up a channel for Morgtage Race Lawsuit ASAP? Thank you
@Malissa, these should not be missing their Slack Alerts, so I added "Signed e-Sign IN PROCESS" to the LawRuler Automation like Tony does for his e-sign tracker. Can you keep an eye out for duplicates and let me know if we still have missing ones.
@Cameron Rentch, quick update. Flatirons as use-case, here is my current new client dashboard. Still plugging away on it and awaiting @Nicholas McFadden benchmark report component, which is nearly ready.
@Ryan reminder on this dash, I want the ability to drill down into each campaign to get specific details (not any contact or PII data, but case date specific to each case type). If you create a google sheet with each tort/case type I can tell you which fields we want to pull in data from.
@Cameron Rentch , once you select one from the table; the rest will update. Once @Nicholas McFadden gets the lr_inbox data, I’ll come get what you want to filter into by each case type.
@Ryan I need a "custom" customer dashboard for Crump, by EOD that I can share with Jarret. Campaigns I need listed are:
Crump/Slater Illinois Abuse Crump/Slater MD Juv Hall Crump/ACTS Chowchilla (not yet live) Crump/Napoli Depo provera Crump Lanier Mortgage race lawsuit Crump/BG New Jersey Juv Abuse
Everything else standard columns, put the date "start" of the dash on Feb 2024). Once you get please post to me thank you
*Thread Reply:* Sent invite to market@, Jarret@, a gmail Jon uses and you, check inbox.
@Mark Maniora @Brian Hirst I need a NEC Legafi (FORM) contract created. Let me know when ready and I will add filters then activate thanks
@Cameron Rentch The NEC Legafi FORM is ready
@Mark Maniora @Ryan @Brian Hirst NEC Legafi contracts are missing the "S1" field (data not passing through. Need this fixed asap pelase thank you
@Ryan can you please pop me the link to the new crump dash you created? Also, can you add Crump/Slater CA Juv Hall Detention to the dash? We just got sign ups today
@Cameron Rentch, done above. Once we finish yesterday financials, the 10 will appear.
https://lookerstudio.google.com/u/0/reporting/952bf88a-0673-4481-8bdb-916187014f80/page/ASVvC
@Ryan please grant me permissions to share and/or share the crump dash above with bcldashboard2023@gmail.com
also @Ryan please add Firefighting foam to the new crump dashboard, again only from Feb of this year. Thank you
@Nicholas McFadden, can you please fix this Fivetran function and get syncing, need it ready for next week's status app build out. FYI @Brian Hirst @Dustin Surwill @Zekarias Haile
@Ryan on the crump dashboard, can you please add Crump Slater CA Juv Hall Crump ACTS Chowchilla Then tomorrow please add Crump/Meadows gaming addiction
*Thread Reply:* @Cameron Rentch, these are all added in dash
@Cameron Rentch, if these are "new orders" (e.g. #2, etc.), please be sure you have TIP Intake and LP Contracts cloned and Malissa puts the "new order" in Copper. Same way we fixed Maryland Crump/Slater #2.
@Ryan I dont even understand what youre asking me lol, can you nudge her pls thanks
these have been active for past week
I'll leave them as is, if you see your Orders Dashboard showing one order from the past for CA Juv Hall CrumpSlater combined with this new one that started again a week ago, let me know. If this is a change-order (meaning your old CA Juv Hall Crump/Slater LP Contract) just got more money and you went active again a week ago, then it's setup correctly.
@Ryan @Nicholas McFadden Note we are missing LA County Foster Care sign ups in Flatirons dashboards, rev and benchmark tabs. Please ensure this gets added in ASAP. We started about 8 days ago. Thanks
I’ll have it added in for this weeks update
@Malissa @Greg Owen, please work with @Cameron Rentch and @Andy Rogers to update
Looker DataStudio (Legacy System) Active Client Dashboards https://docs.google.com/spreadsheets/d/1h2KichEoFitU5KeHn_JQdRswiDTvlmvlA1mRyVdrwhQ/edit?usp=sharing
Can we have this completed by end of week @Malissa and @Greg Owen? If not, let me know a date you can have it completed.
@Ryan I have updated the list to show ones that can be archived. @Malissa can you double check the list to make sure I didn't miss any?
@Malissa @Greg Owen, thanks. @Zekarias Haile, we'll meet on Friday so I can share all "active" dashboards to you.
@Ryan please add Crump/ACTS Chowchilla to the new Crump dashboard asap. Started on Nov 6
@here, pushing our meetings to tomorrow, have some revenue financial adjustments to prioritize
Alright. I'll be finishing up getting a root canal at that time. Might be a little late.
@Brian Hirst @Ryan fyi bunch of new channels needed for new campaigns:
Type 2 child diabetes Button Batteries NV Juv hall abuse Illinois foster care abuse
@Brian Hirst @Ryan we are restarting Dublin womens prison abuse. I think we archived this channel can you reset asap? We have leads in leaspedia.
I'll double check to make sure signings are still set up to post
@Brian Hirst @Ryan please check #vertical_fields for new channels to set up
@Mark Maniora note we have NYC private school leads not selling to Marsh. You used the wrong vertical, can you please fix and manually push leads from the week?
@Cameron Rentch
The NY Sex Abuse - Marsh/HRSC contract is set up for the vertical "NY Sex Abuse."
and this was the request:
New Campaign: Campaign: NY Sex Abuse Firm: Marsh Law Firm/Hach Rose Schirripa & Cheverie
Are you saying it should be set up for the vertical "NYC Private School Abuse?"
FYI @Malissa I am switching this contract to the vertical NYC Private School Abuse. Original request was for NY Sex Abuse though
updates completed and changed to NYC Private School Abuse
@Mark Maniora reminder to go back for all leads from prior week and manually sell to Marsh firm. Thank you
@Ryan on Crump dash, please make sure that Michigan Pollution DTE campaign for Crump/ELG is listed. Also we do not want organic pages because we dont want other to see or know what is going on...campaign started 12/19
@Zekarias Haile, please update Crump All Cases dash to include Michigan Pollution please.
@Ryan please make sure Crump dash has Bard Powerport (Crump/Lanier). Started 12/12.
@Ryan @Zekarias Haile can you please get make sure the Marc Barmazel is added into every "tort" channel to see sign ups
@Cameron Rentch, @Brian Hirst, this had something to do with the ldp_vertical table's vetical name field, I had to repaste them in cutting and pasting directly from the box to ensure no special invisible characters, and then resent emails and it worked. In future.
I fixed these two channels and found the same issue in Wood Pellets
*Thread Reply:* Whenever I add into this table I cut/paste plain text directly from slack and leadspedia. What were the invisible/special characters?
@Brian Hirst, in future please add Marc and Edward to each channel addition.
@Ryan please update Crump dash to include all 2025 cases and we are mising a ton of cases from 2024, not sure who goofed on this dash please fix ASAP
Example - gaming shows "30" signed on their dash, but we have 338 signed to date
At Cameron, you asked for specific campaigns on this dashboard when we created it, here they are below and you even gave me a date to use. Do you want ALL Crump cases on here instead now?
SELECT rev_date,media_buyer,campaign,customer,ad_platform,daily_spend,daily_leads,signed_contracts,daily_revenue,sale_type,idx_id
FROM public.media_raw_v2
WHERE
(
campaign LIKE '%Illinois Juv%' AND customer LIKE '%Crump/Slater%'
OR campaign LIKE '%Maryland Juv%' AND customer LIKE '%Crump/Slater%'
OR campaign LIKE '%Depo Provera%' AND customer LIKE '%Crump/Napoli%'
OR campaign LIKE '%Chowchilla%' AND customer LIKE '%ACTS/Crump%'
OR campaign LIKE '%Chowchilla%' AND customer LIKE '%Crump/Slater%'
OR campaign LIKE '%Gaming Addiction%' AND customer LIKE '%Crump/Meadow%'
OR campaign LIKE '%Mortgage Race%' AND customer LIKE '%Crump/Lanier%'
OR campaign LIKE '%New Jersey Juv%' AND customer LIKE '%Crump/BG%'
OR campaign LIKE '%CA Juv%' AND customer LIKE '%Crump/Slater%'
OR campaign LIKE '%Fire%' AND customer LIKE '%Crump%'
OR campaign LIKE '%Oxbryta%' AND customer LIKE '%Crump%Napoli%'
OR campaign LIKE '%Michigan%' AND customer LIKE '%Crump/ELG%'
OR campaign LIKE '%Bard Powerport Catheter%' AND customer LIKE '%Crump/Lanier%'
)
AND rev_date >= '2024-12-12'
ORDER BY rev_date DESC
Example - gaming shows "30" signed on their dash, but we have 338 signed to date
@Ryan @Brian Hirst @Mike major issues with webhooks - no landing pages have been sending leads to leadsepdia since EOD friday. Just FYI Mike is working on it
@Mike, URGENT, you need to update the CTM to LP 2 app with the new Leadspedia webhook for Ben Crump and Dishowitz call only leads to be sent form CTM. Can you fix and test and confirm?
FYI @Malissa, for Ben Dishowitz.
*Thread Reply:* @Mike, tested BCL and Dish into LP? 🙂
*Thread Reply:* I don't have a way to test it but it was literally just swapping the url out. the "new" url has actually been live and working for a year+ now
@Nicholas McFadden, what is latest on Flatirons new reporting and Leadspedia pipelines for raw data? Please let me and @Zekarias Haile know details. Ty!
*Thread Reply:* Working on them today
*Thread Reply:* Ok. Once all raw is piped, setup meeting with me and Zek.
*Thread Reply:* Hopefully have the different tables in there for you
*Thread Reply:* All leads table in SL BQ working on the rest
@Ryan can you pelase create a dashboard for AWD, they should have co counsel firms: Wagstaff = Paraquat ACTS = Chowchilla Dr Brock = Stiner Gould
Campaign start dates = May 13, 2024
*Thread Reply:* @Zekarias Haile , please get this drafted by and if day, send to me here with link when ready.
*Thread Reply:* @Zekarias Haile, did we get this out?
*Thread Reply:* Not yet. I will finish up later.
*Thread Reply:* @Zekarias Haile , status on above?
*Thread Reply:* Im still working on that. I’ll share the link by EOD.
*Thread Reply:* @Ryan @Cameron Rentch please see dashes below.
Paraquat - AWD - Wagstaff Chow- AWD - ACTS Dr Brock - AWD - SGGH
*Thread Reply:* @Zekarias Haile please send dashboard links for AWD to:
CM@gfoamllc.com md@gfoamllc.com
(these should be associated with google accounts)
*Thread Reply:* @Cameron Rentch it says it is not linked to google
*Thread Reply:* @Zekarias Haile I confirmed that these email addresses are Google related. They were all able to access.
Please also add:
*Thread Reply:* @Cameron Rentch for some reason cm@gfoamllc.com is the only email that I'm be able to add it. Others are still giving me an error.
@Ryan I need Crump dash here: https://lookerstudio.google.com/u/0/reporting/952bf88a-0673-4481-8bdb-916187014f80/page/ASVvC
Updated with CA Juv Hall, ONLY should show sign ups from 10/20/24 and after. Currently showing old budget, need updated ASAP please causing issues with jon wright understanding accurate budgets and funds used
@Zekarias Haile , please read above. Then send the SQL for this add to the crump dash this case and buyer with those dates. Please be sure to keep the rest of the sql “as is” without date parameters. Think SQL CASE function
Out for lunch. Will do it when I get back.
@James Scott @James Turner @Joe Santana hop on tech managers meeting please
@Nicholas McFadden & @Zekarias Haile
//Input form by Media Buyers
Table io_media_buyer_spend
media_spend_log_id integer [primary key]
media_buyer_id integer //FK
spend_date date
platform_id integer // FK
platform_name
ad_account_source_id //FK
ad_account_source varchar // For imcluding Nitido, Facebook Ads Direct, TikTok Ads Direct, <new entry> baby table
ldp_vertical_id integer //FK
spend_amount float
form_submit_date timestamp
form_updated_date timestamp
}
Table io_ad_platforms {
platform_id integer [primary key]
platform_name varchar [note: 'Facebook,TikTok,FacebookIFLA,SnapChat,Google,Parler,Outbrain,Bing,Reddit,Yahoo,Taboola,LinkedIn,DV360,TV']
created_date timestamp
modified_date
ioadplatforms seed data
- MNTN
- MediaMath
- Outbrain
- Other Media
- Parler
- Radio
- TikTok
- TV
- YouTube
ioadaccountsource id name (values from the adaccountsource field above) createdate updated_date
Hey can you share the video when you receive the recording?
I sent the firefly, Google auto sends when done processing.
@Brian Hirst, this video shows a BUG that is causing real problems for @Malissa and team. Watch it and you'll see the bug happen as they change JUST the Category and Points here.
Can you fix ASAP?
https://www.loom.com/share/ddc6a0b085594226aafd28827bc37ec3?from_recorder=1&focus_title=1
@Ryan I'm having an issue replicating it. Want to hop on a quick one?
@Malissa the editable "new billable rate" now reflects the non editable "current billable rate." So you can make changes to the casetype prices without editing that field and the billable rate will not 0 out. @Ryan ^
Thanks @Brian Hirst! I'll let you know if there are any issues. 🙂
@Mark Hines @Mark Maniora @Brian Hirst seeing alot of sign ups in <#C088N7M83J4|wood-pellet-lawsuits> that are missing s1's. Media buyers show that all of their S1's are submitted.
@Cameron Rentch I downloaded an S1 report for wood pellet sign ups The first 10 I checked that were missing S1's in Lawruler were also missing S1's in Leadspedia.
@Cameron Rentch I'm seeing a lot under this ref URL "https://shieldjusticewatch.com/wood-pellet-lawsuits/" There should be an S1 on there
@Mark Hines the media buyer puts different S1's on the end of the URL in the ads.
@Mark Hines I'm seeing a couple that have the s1 on the referring URL but not in the s1 section AZ60S8RY This lead for example.
@Mark Hines nvm that is a form vs lander thing. We covered that already. My mistake
@Brian Hirst I am seeing a lot from that page that DO have the s1 in leadspedia. Everything on all of the pages looks good. Im not sure why some are coming through and some are not, Are you saying it might be on the Media Buyers end? https://shieldjusticewatch.com/wood-pellet-lawsuits/ MBV6SGA1
@Mark Hines All the leads that do have s1's in Leadspedia are in lawruler. The ones that do not have S1's in Lawruler do not have S1's in Leadspedia.
So the ones that don't have s1 look like they don't have it on the URL which makes me think that the Media buyer didn't add it on that URL?
@Mark Hines it is an issue of them not getting into Leadspedia because it is not there. Lead "YZSUI837" for example. The ref URL is only "https://woodpelletclaims.com/" it should have an S1 on the end of there. I believe is it something set up incorrectly on the media buyers end.
@Cameron Rentch So after looking into everything looks good on our end and we believe it is something on the Media Buyers end for example as Brian stated:
It is an issue of them not getting into Leadspedia because it is not there. Lead "YZSUI837" for example. The ref URL is only "https://woodpelletclaims.com/" it should have an S1 on the end of there. I believe is it something set up incorrectly on the media buyers end
And you can see that the ones that don't have s1 look like they don't have it on the URL which makes me think that the Media buyer didn't add it on that URL. These are form the same site
@Brian Hirst we need to look into the zapier connections with Integrations team. Could be something off there. @Brian Hirst Can one of you please get with Nick Ward, Dustin, Mcfadden and Dwight. please and thank you
The two site we see this happening. https://shieldjusticewatch.com/wood-pellet-lawsuits/ ML https://woodpelletclaims.com/ CP
@Brian Hirst @Mark Maniora, for the big switch to Five9 dialer, in that meeting, invite @Nicholas McFadden, @Tony and @deleted-U055HQT39PC so we know the "key values" for the webhook parameters from LP to F9 & LR are clear for active campaigns.
We need to know:
- Total Active "orders" (PO) & "contracts" (..the line items on the PO) can is available in our BigQuery tables @Brian Hirst, not just Leadspedia.
- F9 webhook parameters from @Nick Ward and team involved in Five9
- PCTID integration
- Five9 Pipes to TIP BigQuery
- Estimate timeline and priority of implementation
*Thread Reply:* @Brian Hirst, @Mark Maniora, on the invite include @James Turner for that we get all requirements for above
*Thread Reply:* @Brian Hirst @Mark Maniora, setup a meeting today please. My calendar is up to date, I am out at appointment so please schedule 30 minutes after.
*Thread Reply:* @Ryan @James Turner Should we push the meeting? I just saw James' email.
*Thread Reply:* Yes please, I am available after 3pm today.
*Thread Reply:* @Ryan does after 3pm pst work for you?
@Nick Ward has joined the channel
@Ryan The integration of leads in real-time is Leadspedia->Law Ruler->Five9 is done via Zapier by Law Ruler. They had a method that they used from another client and set us up, however, this was before they deployed webhooks, so maybe there is another change to assess on their end.
Right, but you want these people to send to Law Ruler and 59 in parallel?
@Brian Hirst bunch of new tort channels needed. Get with @Mark Maniora if needed. Ty
@Nicholas McFadden @Zekarias Haile, for our SL BI call tomorrow, here is the schema we need built in BigQuery ASAP to get Shield Legal sold leads. Then with case description from LawRuler, we'll add more columns to this in DBT to update the "Price" with the value from lpallcontracts when we find a match in PCTID's "LP Contract IDs" field @James Turner has added. Then we can create dbtslfinancial_details that has the expenses and other financial details for the new BI system.
Table ldpsoldleadsraw (https://docs.leadspedia.com/getting-started/LeadsPedia-Basics/LeadsPedia-Workflow.html)
Table ldp_sold_leads_raw [headercolor: #24BAB1] {
ldp_lead_id integer [primary key]
ldp_sold_leads_inserted timestamp
first_name varchar
last_name varchar
phone_home varchar
phone_cell varchar
phone_work varchar
phone_ext varchar
address varchar
address2 varchar
city varchar
state varchar
zip integer
county varchar
country varchar
date_of_birth date
email varchar
ldp_advertiser_name varchar
ldp_advertiser_id integer
ldp_alternative_id varchar
ldp_contract_name varchar
ldp_contract_id integer
affiliate varchar
campaign varchar
offer varchar
vertical_name varchar
vertical_id integer
jornaya_lead_id integer
trusted_form_cert_id integer
s1 varchar
s2 varchar
s3 varchar
s4 varchar
s5 varchar
lead_id_token integer
price float
cherry_picked varchar
returned varchar
test varchar
ip_address varchar
date_created timestamp
date_sold timestamp
ref_url varchar
media_buyer varchar
vertical_fields_answers text [note: 'All Verticals Fields Questions and Answers Put Into ASCII Array']
}
Table dbtshieldsoldleads
{
bi_sold_leads_insert_id integer [primary key]
ldp_lead_id integer [note: 'Can be NULL if lead from LawRuler does not have LDP Lead ID. e.g. call only or referral leads']
ldp_advertiser_id integer //FK
vertical_id integer //FK
ldp_contract_id integer //FK
ldp_alternative_id varchar
order_id integer //FK
platform_id integer //FK
media_buyer_id varchar //FK
customer_type_id integer //FK
lr_lead_id integer //FK
lr_casetype_id integer //FK
lr_current_status_id integer //FK
first_name varchar
last_name varchar
phone_home varchar
phone_cell varchar
phone_work varchar
phone_ext varchar
address varchar
address2 varchar
city varchar
state varchar
zip integer
county varchar
country varchar
date_of_birth date
email varchar
affiliate varchar
campaign varchar
offer varchar
jornaya_lead_id integer
trusted_form_cert_id integer
s1 varchar
s2 varchar
s3 varchar
s4 varchar
s5 varchar
lead_id_token integer
price float
cherry_picked varchar
returned varchar
test varchar
ip_address varchar
date_created date
date_sold date
ref_url varchar
vertical_fields_answers varchar
lr_date_contacted date
lr_date_signed date
lr_date_declined date
lr_date_transferred date
}
@Mark Maniora, please add (TC) to all of these contracts and let me and Zek know when done. Thanks. FYI @Zekarias Haile
@here , I am working on a heavy reconciliation post declines from TIP. Will advise when done.
@Ryan @Zekarias Haile need new customer dash for ELG/Crump Michigan Pullution -injury -property damage
Wood Pellet -injury -property damage
*Thread Reply:* @Ryan @Cameron Rentch @Malissa Wood pellet injury and property has been shared with you for both ELG and Crump. please check it out and let me know if something needs to be fixed.
*Thread Reply:* @Zekarias Haile we'll need Michigan too. just a reminder. 🙂
*Thread Reply:* @Malissa, @Ryan said only wood pellet on the text below.
*Thread Reply:* Confirm with Cameron is separate dashboard. I just passed along his request.
*Thread Reply:* @Cameron Rentch @Ryan @Malissa Michigan injury and property are done.
important we get this once Ryan has reconciled the above campaigns
@Zekarias Haile, right now, do this dashboard and @Cameron Rentch and @Malissa and me when ready. Wood Pellets ONLY.
@Nicholas McFadden I need you and team to do some research and find the information below for me:
Eaton/Altadena fires in LA -identify main geo areas that residents have relocated after losing their homes. Presumably there will be "pockets" of folks in specific ares (ie riverside county, san diego, orange county etc, out of state NV, AZ, etc) -identify assumptive totals of people that have relocated to each area
We need this as quickly as possible, it will require some research but there seem to be a lot of publicly available information online. thanks!
*Thread Reply:* @Cameron Rentch here is my research regarding the fires. Let me know if/when you want to discuss.
*Thread Reply:* @Cameron Rentch ^
Ok I will get on it. See what we can pull
I know you need this quick but if this helps with the litigation long term could be interesting to have a poll on something like the LA subreddit (use a burner/annonymous account) to see where people are going. Then compare the poll info with other research/data points. We could scrape social media and see if there are common locations mentioned. We could add a question to our social media/web forms with this as a question (not required but if they can provide it then why not grab it). Just a couple ideas I had if this would help long term to track.
@Mark Hines can you check out this lead "M1FQZ6PI" in LP? The S1 didn't go through
@Brian Hirst There is no way for me to test it but everything on the site looks correct. It's setup just like every other form.
Ok. In LP the S1 is only on the Ref URL it isn't in either of the S1 fields. Tracking Info or Lead Info
@Brian Hirst It's not on other leads from that same page. Maybe they don't have it set up right on MB side?
I see that as well @Cameron Rentch ^^ When you look at the Lead in LP if the S1 is going to pass it needs to be in the Sub Affiliate 1 field in the tracking info tab.
@Cameron Rentch @Nicholas McFadden if either of you know a realtor in SoCal, they might be able to pull data about the housing market. Like places that have seen a massive uptick since the fire.
@Brian Hirst @Mark Hines has anyone checked with the integrations team, perhaps they are making error with zapier or soemthing?
@Cameron Rentch I'll check with Dwight but he only thing is, I don't think this has to do with Zapier because these are coming straight from the site. Everything on the site looks good, same as always and I duplicated the form and hard coded test and it came through. Maybe something on the Media Buyers end?
@Nicholas McFadden @James Scott @Dustin Surwill @Zekarias Haile @Brian Hirst @James Turner @deleted-U06C7A8PVLJ, please take 10 minutes and update the below columns on the Monday Project Board. Please have it completed before we meet for the tech team managers meeting.
https://tortintakeprofessionals.monday.com/boards/6304263960/views/156553659
- Status
- % Complete (0% to 100%) < new field
- Update date to "realistic complettion dates" please. FYI @Joe Santana
@Brian Hirst, @Mark Maniora, @Cameron Rentch asked if you guys can prioritize cleaning up the LawRuler "Duplicate Lead" server response from the lead-post into Leadspedia. We need the Leadspedia Unsold Leads Queue to tell us "Duplicate Lead" to save Media Buyers and Mark Maniora manual sale time. Plus our LP raw data pipeline will need the correct post response to ensure it does not cause downstream "automation of manuals being sold".
FYI @Nicholas McFadden
see example, this is rampant in leadspedia needs to get cleaned up opn every campaign. We should NEVER have a not match UNLESS it is filter related. Ev ery Duplicate should have "Duplicate" as response
@Cameron Rentch The response is "not match" because it got rejected by lawruler, it got rejected by lawruler because it already exists in their system. It will only say "duplicate" in Leadspedia if it is a duplicate in Leadspedia's system. People submit multiple leads using their last name as their first name, or their middle initial in front of their last name, a lawruler's system catches it even though leadspedia's did not, and it gets booted for being a duplicate.
In the first 2 images it shows the above lead in Lawruler when it was submitted yesterday, and then the "no match" (which was actually a duplicate) result. Leadspedia did not catch it because they flipped their first and last names, but it seems lawruler has a filter for that. This person seems to submit a form for everything they see and not respond (5th image)
If you go to "leads posts log" in Leadspedia it will display the response token from Lawruler in Leadspedia For example lead "YU4HFL6J" says "no match" in the "all leads" dash but it says "lead already exists" in the leads post log
We've run into this before, and Leadspedia said we could not fix it on their platform. I asked if they could add the "message token" field into the "all leads" dash and they also said not possible.
I know that was a bit long winded. Let me know if anything was unclear. I am looking into a workaround. @Mark Maniora @Ryan
@Brian Hirst you should be able to properly match “response code” from LawRuler to replace “not match” with Duplicate. This is common proactive but needs to be set up on every campaign.
@Cameron Rentch I agree, I should be able to. It should be common practice. I set up a callback with leadspedia to figure something out. If they cannot I am going to request they add a "message token" column in the leads dashboard where it says "duplicate" right now.
@Cameron Rentch @Mark Maniora @Ryan I forwarded Leadspedia's response. I had a call with Andy for a workaround. He said he'd try to get something else worked out
*Thread Reply:* @Brian Hirst, where did this wind up with Leadspedia?
*Thread Reply:* No change from the forwarded email
*Thread Reply:* @Brian Hirst, what ever came of this? Any expected movement on solution or is this just going to be something we do in the BI data warehouse?
*Thread Reply:* Leadspedia is unable to change the response in the "all leads" dashboard. The "Leads Post Log" contains the message token i.e. response, which will say "lead already exists."
*Thread Reply:* @Brian Hirst, ask Nick McFadden to give you the URL to BigQuery for the table that would tell you this in the Leadspedia Raw Data. This can become a Shield Legal Operations dashboard for Mark M. in LookerE.
*Thread Reply:* Ok. I'm just spitballing, but if we pulled the "all leads" table along with "leads post log" and matched "no match" with "lead already exists" we would get the "no match" leads that exist in Lawruler already.
@here can someone please find FLatirons/Dicello customer dash and send to me ASAP? Need fast
Is the one you are referring to orange? I believe @deleted-U06C7A8PVLJ was working on it but has left for the day. I can reach out and attempt to get ahold of him
@James Scott @Nicholas McFadden @Zekarias Haile @Brian Hirst @Dustin Surwill please update the board before our meeting today. Dates, Status, any important comments. Need to get through all tasks at a "high" level today so I can really work on project timing for deliveries with partners. Ty, see you guys soon.
https://tortintakeprofessionals.monday.com/boards/6304263960/views/156553659
@Brian Hirst new vertical "Los Padrinos Juv Hall"
@Ryan @Brian Hirst I would like to request that we add the "lead created date" from leadspedia and add it into ther s1 tag when posting into slack.
Example would be something like this it would be incredibly helpful so we can really track e-signs based on daily traffic and older leads
ELG - MDW - 565 - 1690 Eddie Benezue - 030725 - S1:PL-FacebookMB-CPCMP-FFFACFFAC110.1F
@Cameron Rentch, we are not going to change the old system, but @Nicholas McFadden and I have discussed using the almost ready BI datawarehouse data that has been connected from Leadspedia to TIP Billing to LawRuler so we definitely can do that when we build TIP Slack Alerts in GCP. The old system is fragile. We can definitely get a "dashboard" that shows this very soon, i'll let @James Turner know about it in our weekly dev meetings.
Leads are all set up with the createdate in the PCTID-X-Data for when the new system is ready to go!
Just a question but does it have to come as part of the ID itself? Could it be a different field or metadata on the record?
Just concatenate it in the post @Nicholas McFadden
Ok it’s just for the post into slack?
Otherwise I would be cautious of putting everything into the ID as it isn’t the best data practice and could cause unforeseen issues like with Five9 and the Case Type names. Just food for thought.
Yes, and if you have a messaging log table, we can use it for other things and just concatenate what we need or don’t need
@Brian Hirst @Ryan we laiunched instant noodle cup burns last night, cant finfd a channel please set up asap
vertical is "instant soup cup burns"
@James Scott @Zekarias Haile @Brian Hirst, FYI @Nicholas McFadden
@James Turner lets setup a meeting for Monday or Tuesday to go over the table, schema and query we need to validate against the old public.financial_log. (e.g. 3/12/25 has 74, this query only returns 42...help!).
SELECT **
FROM tort-intake-professionals.Financial_Log_Dataset.pctid_x_data_with_billable_status_and_rev_rates
--WHERE lead_id = 696367
--WHERE to_status LIKE '%Signed e-Sign%'
WHERE marketing_source LIKE '%Shield Legal%'
AND DATE(status_change_time) = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
ORDER BY lead_id DESC;
This could be a couple things, but we can check it out for sure!
If you can, send me a copy of the results you are getting with that same query on the old fin log
@James Turner, run this query and you'll get 42 results. But dash shows 74 for yesterday in old public.financial_log, so if you can figure that out, James Scott is sending invite to discuss tomorrow morning.
Is the public.financial_log table RIGHT NOW - 24 hours, or is it looking for the entirety of yesterday from midnight, to midnight? Also what time zone does it work off of?
The entirety of yesterday from midnight to 11 5959
If you want to find: • A row for each unique lead_id • ...Where the current status is one of those available on yours. • .. and it was changed to one of those status's on yesterdays date, • And it has Shield delegated as the marketing firm This is the query you would be looking for:
WITH ranked_data AS (
SELECT
**,
ROW_NUMBER() OVER (PARTITION BY lead_id ORDER BY status_change_time DESC) AS rn
FROM `tort-intake-professionals.Financial_Log_Dataset.pctid_x_data_with_billable_status_and_rev_rates`
WHERE status IN (
"Signed e-Sign FINAL",
"Signed e-Sign",
"Signed e-Sign FLAGGED",
"Signed e-Sign QA WIP",
"Signed e-Sign RE-TRIGGERED"
)
AND
to_status IN (
"Signed e-Sign FINAL",
"Signed e-Sign",
"Signed e-Sign FLAGGED",
"Signed e-Sign QA WIP",
"Signed e-Sign RE-TRIGGERED"
)
AND DATE(status_change_time) = DATE("2025-03-12")
AND marketing_source = "Shield Legal"
)
SELECT **
FROM ranked_data
WHERE rn = 1;
I'm actually getting one more than what is on the tip financial 1.3 dashboard
It does appear the old financials may have Dicello leads filtered out unintentionally which would explain the discrepancy.
@Brian Hirst @Ryan new campaign started McDonogh school abuse. Can you please set up channel if it hasn’t been set up already?
@Ryan gm, I need the Piad vs spent changes that @Malissa sent last night updated please, as well as the wood pellet and MI pollution rev changes posted and resynced. Please let me know as soon as this is done, as I am waiting to meet and review with Marc
@Cameron Rentch , those we all done last night. If you need rev changes, send me those.
@Cameron Rentch Checking to see if it took now Cam
Michigan pollution is a little off but that's because Martin sent Edward a list of ones that were in the wrong bucket. It's not off by much though (I have 627,300, the PvS shows 626,200).
@Ryan The spend on St Helens and Hudson are still different from what Cam has. St Helens should be 7500 spent and Hudson is 10500 spent
instant soup works! 🥳
Wood Pellets is still incorrect but give me about 10 minutes and i'll have it fixed.
@Ryan we need to adjust total rev in dash for St Helen and Hudson to above, and have it reflective in PVS
@Cameron Rentch @Malissa the spend for Hudson was missing one day of $915, that is being added now.
@Cameron Rentch, great. Tell me the rev. per contract you want for both and I'll update.
@Ryan there are no contracts, justy make the total rev equal to waht is above
st helen total rev 7500 hudson total rev 10500
@Cameron Rentch, got it. Saving the DB now with those rev updates. Give it 15 to all sync up.
@Malissa and @Mark Maniora, will t2 and property use filters and route leads? Or will JUST T1 continue this for Wood Pellets. @Zekarias Haile FYI
@Ryan Everything is going through T1 and then being manually changed to T2 or Property.
@Cameron Rentch, JA sent his Helens spend, updating one more row and spreading out the $7500 rev, will be updated in LookerE in 20 or so
@Cameron Rentch @Ryan Just checked - St Helens is now showing 7500
@Cameron Rentch i think we got stuff fixed! The big thing to call out is that there are 3 wood pellets. Tier 2 is super negative. The others show a positive balance. When added together, it's still very negative and shows they owe us $$.
@Malissa we need to adjust the budgets so that they are all negative on wood pellets, we cant have one positive (even if it balances out)
ok i can do that. it won't really match the budget reconciliation i did though. i can make them all negative for the PvS
@Cameron Rentch This should be what it looks like once the PvS report syncs:
@Cameron Rentch you should see the updated balances in PVS now. :)
@Jack A. @Maximus @Caleb Peters, what channels are not sending ISL leads? FYI @Brian Hirst
@shield-legal-data has left the channel
@Ryan looks like slash commands are working and I didn’t see any missing ISL’s.
@Brian Hirst , urgent. Can you check configuration for issues. Slack slash command /isl isn’t working per Jack
@James Scott @Nicholas McFadden, I need the Leadspedia Sold Leads SQL from our Leadspedia Raw Data badly for the MBs to get access to their "sold leads" as they happen throughout the day, can one of you send me that SQL ASAP? Or tell me the table we created that they live in in BigQuery?
@Mark Maniora @Ryan please fix the #paraquat DL-DL contract to reflect DL TC-J pls
@Ryan @Zekarias Haile adjustments need to DL/Flatirons - April 12 we had 1 AFFF signed, please change rate from 12,000 to 10500 total rev. Let me know when updated
*Thread Reply:* @Cameron Rentch @Ryan Done.
@Ryan @Brian Hirst @Mark Maniora please see that we have a channel for "Storm damage" - it is under vertical "General storm damage", we have a mailer campaign and a standard campaign both using this vertical. One lead manually sold moments ago from JA to the standard campaign. @Mark Maniora we also need a "placeholder" campaign in leadspedia for Storm law for the mailer, leave "inactive" so that Ryan and team, have a campaign to post the contracts into slack channel etc
@Cameron Rentch so there will be a “general storm damage” channel?
yes we are likely not using any of the "state" specific channels, only the "general storm damage"
created the second placeholder/flyer contract
Lead Contract (1768) Texas Storm Damage - Storm Law/Levinson (FLYER) (TIP) - Contract
@Ryan we are on google call, we failed to set up conversion tracking need you to join this asap
@Ryan @Nicholas McFadden @Brian Hirst
• Start from the tables: mediarawv2, iomediabuyer (where Media Buyers are added and managed), and ioadaccountsource. • Include a join with iomediabuyerretool_prod to get the spend associated with each Media Buyer.
• Get the latest (max) submit date for each Media Buyer and filter by a specific vertical.
a. Include Lead and Retainer Info:
• Join with All_sold_leads and shield_legal_datastore to bring in retainer details.
• Use ContractID to join All_sold_leads with both shield_legal_datastore and Leadspedia_all_contracts to retrieve the contract price.
a. Bring in Lead Spend Data:
• Join with order_paid_spent to get spend at the lead level, unless the lead is a referral.
• Also join this data with io_media_buyer_retool_prod to correlate lead-level spend with Media Buyer data.
@Zekarias Haile, please resort the LookerE > Shield Legal Financials board dashboard pages, I had to add page 65 to 67 this AM for Marc. Ty!
@Mike, FYI @Mark Hines, you have access to all new Google Ads accounts for Google team to check Conversions are active in Google Ads.
@Ryan @Mark Maniora @Brian Hirst @Zekarias Haile we signed up a LA wildfire case from mailers yesterday
LA Wildfires - DL - Flatirons (Physical Damage Only) - Elaine McDonald 733196
Did not post to channel, we should have a leadspedia contract as placeholder.
Also, @Ryan you can put a total expense over this month at 10k, 333.33/daily spend for "Mailers" as a media type, you can use "CR" as media buyer.
@Mark Maniora , post new LP contract name here please. @Zekarias Haile , this contract needs $10k divided by the 31 days added with the new LP Customer name added to mediarawv2. Please add from May 1st to May 19 once contract made.
@Malissa make sure it’s in Copper and linked to LP
*Thread Reply:* @Ryan 733196 this lead that Cam posted above is not signed under LA Wildfires - Dicello/Flatirons (Flyer) (TIP) - Contract. The LR case type doesn't have "Flyer' in the contract
*Thread Reply:* @Ryan it's in copper and linked. all good there
looking into this. I already have this contract set up "LA Wildfires - Dicello/Flatirons (Flyer) (TIP) - Contract"
@Mark Maniora , post new LP contract name here please. @Zekarias Haile , this contract needs $10k divided by the 31 days added with the new LP Customer name added to mediarawv2. Please add from May 1st to May 19 once contract made.
@Malissa make sure it’s in Copper and linked to LP
@Mark Maniora @Cameron Rentch, how do @Zekarias Haile @deleted-U06C7A8PVLJ and I know from LawRuler which is a mailer lead?
there is a separate mailer campaign in lawruler
@deleted-U06C7A8PVLJ @Zekarias Haile, please update this in LawRuler and PostGres DB log table. Someone remind agent Mailer goes to LR mailer campaign. LA Wildfires - DL - Flatirons (Physical Damage Only) - Elaine McDonald 733196
@Brian Hirst @Ryan can you check to see if <#C061EMXTEAE|salon-professional-bladder-cancer> is not working? I am seeing some sign ups sent via email, but notjhing posting in channel
Hair Salon Bladder Cancer - DL - Flatirons - Rousaan Jamahl McLeod 741136
@Cameron Rentch one posted right after this message. I’ll see why this one didn’t post
@Ryan can you please confirm these are shopwing in systems and dashboards? @Brian Hirst noticed these were not posting to channel and I dont recall seeing them in financials
DL - FL - 439 - 1711 - Lisa Grant - S1:PL-FacebookMB-MLSBLADDERFORMarcads1A DL - FL - 439 - 1711 - Lisa Latorre - S1:PL-FacebookMB-MLSBLADDERFORM1 DL - FL - 439 - 1711 - Stephanie Brown - S1:PL-FacebookMB-ML2AI DL - FL - 439 - 1711 - Rousson Mcleod - S1:PL-FacebookMB-MLSBLADDERFORMarcads1A DL - FL - 439 - 1711 Marisa maresca Maresca - S1:PL-FacebookMB-MLSBLADDERFORMarcads2_a (note this was shpwing in dash as hair relaxer so make sure it is moved to Salon instead.
@Cameron Rentch , they did not make billable by midnight, not in numbers. Have TIP team confirm if still signed. If so, I can load and add later. Not at house.
ok they were not from yesterday check <#C061EMXTEAE|salon-professional-bladder-cancer> from last week. I dont ever recall seeing them in adjustment dash
@Brian Hirst , see from Cam, can you post manually ASAP?
```Ryan. Possible slack channel posting issues. 9 Chowchilla signed today, 6 final, only 2 in channel.
```Here are the 9 chowchilla s from today. 6 are already final
*Thread Reply:* Just saw this. Sent a meeting for tomorrow to investigate why the posting are working but not for all.
*Thread Reply:* Ok. I. Checked everything, just monitor in AM. I’m traveling to Vegas
@James Turner @Nicholas McFadden @Brian Hirst
TIP Slack Alerts are working intermittently. Urgent help needed as I am on a plane just boarded.
@Brian Hirst , please double check the ldp_verticals table, triple check the values are valid, it seems to fail on certain channels. Please post manually until fix.
@Nicholas McFadden , please review AWS lambda function and logs to let us know the issue it’s throwing. Possible runtime issue?
James, is the shield datastore up and running? I could build a page in the MB dashboards - and group by each S1 MB values.
This is TOP, first priority to resolve.
@James Turner @Nicholas McFadden @Brian Hirst
TIP Slack Alerts are working intermittently. Urgent help needed as I am on a plane just boarded.
@Brian Hirst , please double check the ldp_verticals table, triple check the values are valid, it seems to fail on certain channels. Please post manually until fix.
@Nicholas McFadden , please review AWS lambda function and logs to let us know the issue it’s throwing. Possible runtime issue?
James, is the shield datastore up and running? I could build a page in the MB dashboards - and group by each S1 MB values.
This is TOP, first priority to resolve.
@Ryan I checked tables yesterday and they looks good. The channels are posting, but like you mentioned, it is intermittent.
@Brian Hirst, maybe ensure no periods, no special characters are in the table, and possibly re-enter the values to see if PGAdmin shows them bolded when exaclty the same, the last time this happened, that's what I did to fix it.
@Ryan I checked yesterday. I'll check again, but if that were the case, the postings would not be working at all, not intermittently. They also have not been changed since they were working.
@Brian Hirst , can you archive old channels we don’t use. Once it was a channel count issue.
If there is potential for any mismatches, you can find the values as they appear in Lawruler from the PCTID V2 table.
I am aware of naming mismatches last week due to the call center changing things.
When I get in, I can check to make sure that billableleadswithratesand_lp is updating properly, and that the DBT function has not failed, but at this point I have not received any email notifications which should alert me of failures on the GCP end
When I get in, I can check to make sure that billableleadswithratesand_lp is updating properly, and that the DBT function has not failed, but at this point I have not received any email notifications which should alert me of failures on the GCP end
The table in Shield this AM only had things from June 1st. When is first sync?
Billableleadswithratesand_lp updates every hour.
Since I haven't received any email notifications of that query failing, it's possible the issue comes prior to that in the form of source data. I'll message Dustin and check to see if the LR_Data extraction experienced any issues.
Does your table BLillabe_leads have 6/2 in it now?
@Ryan In addition to archiving channels, I will delete from Github and PG Admin.
I'm not in office yet, but I can check right now.
@Nick Ward @James Turner Ryan came by and wants to make the DB run faster (every 15 minutes) during business hours if possible. I have determined that it is possible. Any issues that on your ends?
I just finished changing it to 30 from an hour but I don't forsee any issues. We will need to bump up the five9 extraction though if we want accurate call info to go with it.
*Thread Reply:* Thats not a good idea for the recordings but the call logs normally only take 7-8 minutes to update
*Thread Reply:* I don't forsee us needing recordings that quick, but if we can get the call logs updated every 15min as well, without breaking anything that would be awesome
*Thread Reply:* I will make them match when I change it
*Thread Reply:* FYI @Nicholas McFadden @Brian Hirst. LR Data during work hours each 15 minutes and after hours back to hourly.
*Thread Reply:* shield-legal-bi.leadspedia.all_sold_leads,
shield-legal-bi.leadspedia.all_leads will both need to be changed to 15 minute updates on the shield side.
@Ryan are those updated via DBT, scheduled query, or something else??
*Thread Reply:* leadspedia data is currently updated hourly and cannot be made faster without a couple of days of work
*Thread Reply:* Okay so important note @Ryan We will have the leads in, but they WILL NOT have leadspedia information right away. That will still take an hour.
*Thread Reply:* They’re fine on the Leeds Pia data to be an hourly, they need to know sign up, so they know when to spend and win to not spend
@Ryan can you pls find and send me the Flatirons Customer looker dash? I accidentally closed it out
@Chris Krecicki, new project.
@Chris Krecicki has joined the channel
got it, will start enginnering a solution, you'll be able to access it in shieldlegal.dev when its ready
@Chris Krecicki look forward to what youve built. Thanks everyone! @Ryan @Joe Santana
Great seeing this, thank you Ryan and Chris!
@Chris Krecicki , are you using Canonical for any of our products you are developing?
never heard of it -- i am using postgres for a datbase/vector db -- using openai for api calls and i am considering using another layer to improve similarity search from postgres with a repo called superlinked eventually. But for now I'll just use sentence transformers -- I really don't like using 3rd party tools if I don't have to when building because we become tied to them @Ryan
never heard of it -- i am using postgres for a datbase/vector db -- using openai for api calls and i am considering using another layer to improve similarity search from postgres with a repo called superlinked eventually. But for now I'll just use sentence transformers -- I really don't like using 3rd party tools if I don't have to when building because we become tied to them @Ryan
Ok. Great. What is the cost per month on these tools shaping up to be?
I don't have access to the OpenAI and the Assembly.ai accounts, you'd have to ask James Scott -- and now that we're not using OpenAI for cleaning data and I am using a local llama3.1:8b ollama model we won't spend much on OpenAI right now.
James said all of it is under $500 and a chunk of that was testing data cleaning with openai which wont be feasible financially
Will do, he already confirmed Canonical is t his side either. Thanks for confirming.
I asked Dustin to take a look. Said he will check after lunch.
Also we use https://platform.openai.com/docs/models/gpt-4.1-nano for most all of the work I do. The cheapest model with the largest context window .. if you were curious.
Assembly.ai the transcription service costs about $0.12 per hour -- which is insanly good and their transcription model is on point
@Ryan you're welcome -- im finishing cleaning this data set today and tomorrow and then Cam's project has my full attention.
@Ryan can you please locate and send the the customer dash for Levinson ELG TC/MC AFFF/PFAS? I can not seem to locate it
*Thread Reply:* Just saw Cam's message, But I couldn't find it may be a I don't have access to it. Asked Greg and Mal to see if they can find it
*Thread Reply:* @Ryan Both Greg and Mal don't have access
*Thread Reply:* @Ryan I know you are working on other pressing stuff pls send when you can
*Thread Reply:* @Cameron Rentch https://lookerstudio.google.com/reporting/ca9519d6-b2c7-46b9-82c3-b3fe29a79633/page/ASVvC
@Nicholas McFadden @Brian Hirst @Zekarias Haile, this is for how Brian's retool form will work to figure out agency fee. Meaning SQL logic for Agency Fee at 7% to 1M, etc, would be only where agency field contains "Nitido".
@Brian Hirst not seeing a channel for backpage cityxguide abuse, can you set up and activate?
campaign is live and we signed a contract today
Backpage and CityXGuide Sex Trafficking Victims - ACTS - Jaren Christine George 818128
@Zekarias Haile @James Scott @Nicholas McFadden here is what is left without the forwarded message:
SL BI Financials and Operations
• Leadspedia sold leads + LawRuler Retainers for contract buyers + referral leads + media spend + mb commission + sales commission + agency fee + daily operation expense + call costs base table • Financial Details Schema Transform • Profit by Month • Media Buyers Commissions • Yan Performance Dashboards • Monthly Expenses • Order Financials and dependant DBT models for Leads and Contracts SUMing • UPSERTS into old Financial Details of new Financial Details to keep historical data
@CC Kitanovski has joined the channel
@Nicholas McFadden, can you send the SQL and/or table name links from BigQuery for this new table. I want to review it so I can assess what above still needs to be done in detail. Thank you.
@Mark Maniora @Malissa, FYI @Nicholas McFadden @James Scott @Zekarias Haile
Does Milestone Tax Debt Resolution - Milestone (TIP) - Lead contract have leads going to TIP where Milestone pays the call costs directly instead of Shield?
@Nicholas McFadden @Zekarias Haile @James Turner @CC Kitanovski, here is me taking our raw BQ tables, plus our SL financial metrics to take a swing at the soldleadsfinancialdetails "base financial table" schema. Note I highlighted the key financial metrics we should use to create Gross Profit, Total Expense, Net Profit once we roll this up into a rollupfinancial_details table from this base financial table.
Let me know if you have questions: https://docs.google.com/spreadsheets/d/1NVrVcfVdrwGbmhQa9_irxfUq_BAs8xfTWP5nbKP7ouk/edit?usp=sharing
*Thread Reply:* Very helpful while I'm still learning my way around the data! Thanks for sharing!
@deleted-U06C7A8PVLJ @Zekarias Haile @James Scott @CC Kitanovski @Nicholas McFadden
• Order Summary (SL order data on AWS) • Order Details (SL base financials new) • Order Call Center Summary (James Turner) • Order Case Intake Q&A Details • Order Post Retainer Q&A Details
@deleted-U06C7A8PVLJ @Zekarias Haile, FYI @James Turner,
Here is an Order Summary built on Flatirons Datastore in DBT using Shield Legal Copper new GCP data plus connecting to BigQuery PostGres synced AWS PostGres SQL data for the Order Summary Dashboard. https://shieldlegal.cloud.looker.com/dashboards/75
@Nicholas McFadden @James Scott @Zekarias Haile
Where are we on the Shield Legal Sold Leads and Billable Retained base table (aka sldashboard.shieldlegalsoldleadfinancialdetails)? Have we solved the JOIN issue with dupes / repeats for referral leads? Have we incorporated media spend and validated it against the historical data? Have we created the key financial columns in our schema sheet?
FYI @Joe Santana
I’ve been working on cleaning the mappings. We still have case types without contracts or pricing. James and I discussed splitting out the referrals into a separate bucket. We will create a daily ledger/log that will house each lead and spend transaction
I’ve been working on cleaning the mappings. We have still case types without contracts or pricing. James and I discussed splitting out the referrals into a separate bucket. We will create a daily ledger/log that will house each lead and spend transaction
@Ryan we met yesterday on the getting the flow and code together and rules of how we can achieve this, i believe nick is coding something today
Right now had to pivot as I am working on the tasks Cameron assigned us to reconcile.
I’ve been working on cleaning the mappings. We still have case types without contracts or pricing. James and I discussed splitting out the referrals into a separate bucket. We will create a daily ledger/log that will house each lead and spend transaction
@Nicholas McFadden, please return to coding. These financials are mission critical, I spoke to Cameron about his Flatirons questions.
@Nicholas McFadden, where are we per those questions?
- Where are we on the Shield Legal Sold Leads and Billable Retained base table (aka sldashboard.shieldlegalsoldleadfinancialdetails)?
- Have we solved the JOIN issue with dupes / repeats for referral leads?
- Have we incorporated media spend and validated it against the historical data?
- Have we created the key financial columns in our schema sheet?
Ok will do, let me put Edward in the right direction and then I will get back
- Still have a mapping issue for all the leads on the base but made big improvement
- Referrals are going to be split out and handled separately so we can apply the round robin logic since their isn't LP info to go off of
- I have been including the media spend and will be incorporated in the ledger
- Haven't gotten to this yet. I am including everything so I can ensure I am not getting duplicates and some additional info to help determine that I won't need in the final
Who would have info on contract pricing that isn't in LP and pre-dates Copper?
I have a bunch of case types that don't have any contract
Also, how are we handling secondaries? I can not include that for now but would imagine we need to roll that in too
@Nicholas McFadden , we have the old table, mediarawV2 that would have the prices for those contracts. That tables campaign column, Customer column and sale type columns, concatenated equal leads contract name.
You can find the Price by looking for the price on that date.
The Shield Legal base financials as of today won’t include post secondary files, that is tips additional business.