Financials have been starting late, I pushed to 8:00 AM tommorrow, no reschedules, we will get'er done.
Morning guys! EOM upon us.
- We have 38 deals for Sunday. Zek will tackle those.
- I'll do declines and check reconciliation early
- Edward, please get the request for EOM Slack post out for February 2025, no Happy New Year required! :lol:
- I may be 10 minutes late to our EOM meeting, get started without me. When I get on, we'll send Rose F. the sheets we need completed to finish database updates for dashboard and finances.
- Zek, will teach you how to totally prep the Shield Cost Per Worked Leads / Intakes so you can get that as ready as possible.
@deleted-U06C7A8PVLJ , just remove the at tag to Brian Thiessen, he’s no longer here. Thank you.
TIP • ~Request Total Expense & Profit & Loss statement for month~ <-- Need to show Edward how they should calculate and enter into portal • ~Get total SL e-signs, calculate Director Commissions count and commission pool from TIP Dash into Commissionv2 table and Slack channel: <-- 2008 total as of 3/3/25 8:10AM PST~ • ~Update _iolrcasetypesrevenuerates_ table with Effective Billable Rates and Commissions @deleted-U06C7A8PVLJ~ • ~Update DB with Staffing Fees (not Flatirons) @Ryan~ • ~Update Looker Studio & Enterprise Formulas for Flatirons Overhead cost for Flatirons CSP, VSS & MRT & BG CSP, BCL CSP and Dicello CSP. What about ACTS CSP, please confirm if we bill all per completed case file and when that officially started, JIC. @Ryan~ • ~Update VSS Completions prices for Month for Billing from Malissa into Fi Overhead @Ryan~ • Update VSS Completion in FinLog to match Backups in sheet @deleted-U06C7A8PVLJ _SL • ~Cost Per Intakes for TIP and Call Center Costs validated with actual CPI (doing side by side) @Zekarias Haile~ • ~Worked Leads for Campaigns active but not receiving leads from SL (e.g. Lejeune) @Zekarias Haile~ • ~Update SL Past & Current Month CPIs @Zekarias Haile~ • ~Validate Call Center Costs Match from SL & TIP Dashboards @Zekarias Haile~ • ~Request Monthly Operating Expense from this sheet from Rose F. @Ryan~
@deleted-U06C7A8PVLJ
```--GET ALL EXISTING ROWS SELECT ** --dateinserted, client, typeofcase,parentcasetype, grossrevenue FROM public.financiallog WHERE leadsource = 'TIP Staffing' AND dateinserted >= '2025-01-31' ORDER BY client,signeddate ASC;
-- Bailey Glasser
-- INSERT INSERT INTO public.financiallog ( lrleadid, dateinserted, signeddate, typeofcase, parentcasetype, casetyperetainercode, client, cocounsel, lpcontractid, lpcontractname, lpcontractsettingsprice, lpcontractscheduledowprice, lpleadid, lpleads1, leadsource, firstname, lastname, currentstatus, currentassignee, casedescription, contractrevenue, talktimerevenue, grossrevenue, talktimeexpense, netrevenue, agentcommission, salescommission, talktime_minutes) VALUES ( 39, '2024-09-01', '2024-09-01', 'TIP CSP Fees - Bailey Glasser', 'TIP CSP Fees', 'For August 2024', 'Bailey Glasser', '', NULL, '', 0, 0, NULL, '', 'TIP Staffing', '', '', 'Billed', 'Brian Thiessen', '', 106598.72, 0, 106598.72, 0, 106598.72, 0, 0, 0 );
-- BEN CRUMP LAW
-- INSERT INSERT INTO public.financiallog ( lrleadid, dateinserted, signeddate, typeofcase, parentcasetype, casetyperetainercode, client, cocounsel, lpcontractid, lpcontractname, lpcontractsettingsprice, lpcontractscheduledowprice, lpleadid, lpleads1, leadsource, firstname, lastname, currentstatus, currentassignee, casedescription, contractrevenue, talktimerevenue, grossrevenue, talktimeexpense, netrevenue, agentcommission, salescommission, talktime_minutes) VALUES ( 40, '2024-09-01', '2024-09-01', 'TIP CSP Fees - Ben Crump Law', 'TIP CSP Fees', 'For August 2024', 'Ben Crump Law', '', NULL, '', 0, 0, NULL, '', 'TIP Staffing', '', '', 'Billed', 'Brian Thiessen', '', 72121.96, 0, 72121.96, 0, 72121.96, 0, 0, 0 );
-- DiCello Levitt
-- INSERT INSERT INTO public.financiallog ( lrleadid, dateinserted, signeddate, typeofcase, parentcasetype, casetyperetainercode, client, cocounsel, lpcontractid, lpcontractname, lpcontractsettingsprice, lpcontractscheduledowprice, lpleadid, lpleads1, leadsource, firstname, lastname, currentstatus, currentassignee, casedescription, contractrevenue, talktimerevenue, grossrevenue, talktimeexpense, netrevenue, agentcommission, salescommission, talktime_minutes) VALUES ( 41, '2024-09-01', '2024-09-01', 'TIP CSP Fees - DiCello Levitt', 'TIP CSP Fees', 'For August 2024', 'DiCello Levitt', '', NULL, '', 0, 0, NULL, '', 'TIP Staffing', '', '', 'Billed', 'Brian Thiessen', '', 14218.83, 0, 14218.83, 0, 14218.83, 0, 0, 0 );```
So for EOM TIP 02 this would be the correct type frames right:
@deleted-U06C7A8PVLJ , excellent work! @Zekarias Haile , you can start the sheet. We’ll meet Friday to wrap up.
*Thread Reply:* sounds good. I will start working now.
Thank you ! also instead of hard coding the dates I was thinking of adding this to it in the future:
@deleted-U06C7A8PVLJ , can you do 3-5-25 S1s real fast so Zeke can get started. He only needs yesterday’s completed.
Ok. Then we need to see what the lamb is doing, and if it is fixing transfers, then we may leave it in place, but just have to have the shield reconciliation done daily checks by Zeke
Please let me when you guys are taking a look at the lambda
Be easier to find out what the code is doing, and then decide if we keep it as is so we clearly understand what it’s doing, and then just do the manual reconciliation each day. Because it does help that it might be auto transferring for LawRuler to match the database better.
Posting this for reference purposes since I recall whenever I do a change in TIP's database, SL database may need to be changed as well. I verified this in LR so what Martin asked for is correct.
@Zekarias Haile , this means you’re doing revenue and Customer field changes in mediarawv2
Malissa requested to switch these wood pellets from T1 to T2 due to their diagnoses. Luis C changed it in LR and I just changed in TIP's database so you may need to change it from your end next @Zekarias Haile
@Ryan I think the discrepancy we have on 3/24 on FireFoam-Thyroid only- Nations/Levinson is that the one I added manually for Cam. So I think Edward need to add it to TIP database?
if that's fixed. everything will be good to go
If it’s not there but he said he added it so let’s double check.
hmm, the only difference im showing on both the dash and DB is that lead
@deleted-U06C7A8PVLJ I don't see that lead on TIP dash. Would you mind checking please
@deleted-U06C7A8PVLJ I see 702206 (Richard O’donnell) is under JA, while that should be under Cam
The platforms, Google, the media buyers CR and the campaign variable is afff
Edward, why don’t you go see Joe’s team today and inquire directly. And if he acts like he doesn’t know what he’s talking about. Say, Ryan requested it when he was in town with us last week. And let me know what happens.
@Zekarias Haile Martin R requested for me to remove these two leads from TIPs dashboard since they are currently not in final statuses and from what I am seeing in LR aka activity log, agents are dispo-ing it a specific way which may be causing it to become "Signed e-Sign Client Opt Out". Sending a screenshot of what this looks like in activity log (just in case you want to see what I am referring to) I am also including a screenshot of those two leads and it's case type. Sharing it with you just in case you need to remove it from SL dashboard as well
Question. For e-Signs Yesterday, should we be billing for secondaries for military records out of curiosity? I shall attach a screenshot of which case type:
No, changes those statuses to their post signing one in LR
@deleted-U06C7A8PVLJ perfect , @Zekarias Haile , your up!
*Thread Reply:* Shall I start working on building the sheet?
@deleted-U06C7A8PVLJ , you can work on the VSS completions backups reconciliation in old dashboard.
No, not until you finish the VSS completions. Because you’re gonna be adding more billable Rose and making sure they have the right contract revenue. Remember post retainer doesn’t have a agent commissions
These should be on the TIP's dashboard since they are in a billable status so I am adding them: 248985 248991 547900 545729 @Zekarias Haile Just in case you need to add it from your end for SL
*Thread Reply:* @deleted-U06C7A8PVLJ I will. Thanks!
Ask Alan on that closed question. It should be unbillable but if they are billable, we need to know.
Show my full status, are they included already in fin_log???
I think so for now. If we have to change them later in the "old table" for the "new table" join, we'll do that we we no longer process new ones in the old system, make sense?
@deleted-U06C7A8PVLJ, and since Brian H. does not have the IO Retool portal working to update a status to my attempt to use (maybe I did it wrong), I used this to update them NEW BI system:
SELECT **
FROM tort-intake-professionals.<a href="http://tip_prod_application.io">tip_prod_application.io</a>_tip_lr_status_rates
WHERE status_name LIKE '%Closed%'
UPDATE tort-intake-professionals.<a href="http://tip_prod_application.io">tip_prod_application.io</a>_tip_lr_status_rates
SET billable = TRUE
WHERE status_name LIKE '%Closed%';
Good Morning @Ryan and @deleted-U06C7A8PVLJ I wont be able to finish my morning financials in 10 minutes. Can we please push our meeting to 8:45am?
Good Morning @Ryan and @deleted-U06C7A8PVLJ I wont be able to finish my morning financials in 10 minutes. Can we please push our meeting to 8:45am?
No, the talk time minutes make the talk time revenue, which makes the total revenue. So we need that finished before invoices are ready. If Zeke did the sheet, then it’ll have to be updated with the new talk time revenue
*Thread Reply:* I'm almost done. The only missing is this column that I'm not sure where to get the numbers
*Thread Reply:* @Zekarias Haile , because the talk time wasn’t ready it’s not accurate
*Thread Reply:* So you will be able to update just the talk time when Edward tells you it’s ready, but then you have to do all the math to get the total fees, correct by adding them together.
*Thread Reply:* I will send you the results from the tip database for those work leads column
*Thread Reply:* @deleted-U06C7A8PVLJ, let him know when Invoices by Month is ready so he can update talk time.
How about we meet tomorrow to make sure we’re 100% ready. I’ll push it.
@Ryan Is it possible that I can have an upgraded account for Loom. I noticed you use it a lot for your recordings and I think it will be helpful for my end to create SOPs for some of the stuff I am doing for you along with some of the codes I am building so that I don't have to remember "every little detail" and can start taking "video notes" which then I can share with you or others in the team to refer to it? I think free one is only 25 videos max lol
@deleted-U06C7A8PVLJ, ask James and Nick, that would be up to them.
Update: I am creating the SQL query to pull the talk time for five9 and I can see we have a bigquery dataset called five9source which then we have a table called callperleaddata.
The last time it was updated was in march 17th 2025.
I will have to get with integration team to see how they can update the bigquery table so that we have the latest info since I am unable to pull reports in five9 manually and this table isn't being updated daily and James T fintable is showing NULL values for BCL call time @Ryan @Zekarias Haile
For BLX - BCL does these look like all the billable statuses @Ryan I see these in the dashboard but want to confirm just in case a status (one or more) is here that shouldn't be
I got my SQL queries ready and I simply just need to get together with integration team to have them update that bigquery table and from there I shall have that ready for us - update
@Zekarias Haile, send me and Edward the URL to the March 2025 sheet please
SELECT ** FROM public.casetypecccosts WHERE monthend BETWEEN '2025-03-01' AND '2025-03-01' --AND casetype ILIKE '%Polin%' ORDER BY casetype, monthend ASC;
('2025-03-01' , 'Type 2 Child Diabetes' , '175.50142857143'), ('2025-03-01' , 'Private Boarding School Abuse' , '46.69974941725'), ('2025-03-01' , 'Mortgage Race Lawsuit' , '202.756'), ('2025-03-01' , 'Stock Fraud' , '171.22333333333'),
@Ryan was it dashboard 34 that you want us to compare the total fee with it?
Yes, Call Costs total for April match the TIP costs
*Thread Reply:* @Ryan I want to be clear, I don't see any number that much with the March total fee on #34. Once we confirmed that I can run the update statement tomorrow morning.
*Thread Reply:* March 2025 must match, excuse me, I've been ansering questions all day for dozens of people
@Zekarias Haile , nothing is correct Zeke if you compare page 34 to your worksheet, none of the total fees are accurate. This is a mess. I need you to get this correct and get that number right
@Zekarias Haile, even the TIP Worked Leads are not in the SL mediarawv2
@Zekarias Haile
These range MUST HAVE THESE NEGATIVE LEADS in the table for the most recent Customer, so use this range: https://docs.google.com/spreadsheets/d/1M9ys5XKOTOVD27JFmVhiJDu1QYRWLeQqa54nz2zt3P0/edit?gid=0#gid=0&range=Q:S
And this SQL to get these values into a row, please do ASAP and have done correct.
--GET LATEST ROWS SELECT ** FROM public.mediarawv2 WHERE mediabuyer LIKE '%TIP Worked Leads%' ORDER BY revdate DESC;
--FIND OLD ONES TO CREATE A NEW ROW WITH RECENT CUSTOMER SELECT ** FROM public.mediarawv2 WHERE revdate BETWEEN '2024-07-01' AND '2024-07-31' AND campaign LIKE '%Ethy%' ORDER BY revdate DESC
@Zekarias Haile, I am doing the TIP worked leads, this seciton never made it into mediarawv2 yesterday and having that many leads missing is probably why you are $200K too high on CCs in SL side.
@deleted-U06C7A8PVLJ, the final SL CC match now at $775,786 from our Invoices by Month at $774,793.75. You can close the month. I had to add TIP Worked Leads and INSERT 2-3 more verticals to get it right. FYI @Zekarias Haile
Post to all the same people on your first post including Cam
Thank you @Ryan. My apologies for made you redo it. But I think now I understood it. And @deleted-U06C7A8PVLJ and I are going to make an SOP to help us understand both sides and the whole process. Thanks @deleted-U06C7A8PVLJ also for your help!
Next month, I want you guys to do it on your own. Think you can pull it off?
Great. So next month, I will let you guys do each step so I can check at the end of each step.
@deleted-U06C7A8PVLJ, for your SOP, this is the very last step: https://www.loom.com/share/64cf2e46ac404616b75628c592d96269
@deleted-U06C7A8PVLJ, for your SOP, this is the very last step: https://www.loom.com/share/64cf2e46ac404616b75628c592d96269
@deleted-U06C7A8PVLJ , can we cha fe the case type for all of yesterdays Paraquat 12s?
@Ryan are we changing only yesterday's?
*Thread Reply:* I wasn't sure what Cam meant by All
These are from yesterday. I can go ahead and change them to 13 if needed
@deleted-U06C7A8PVLJ , double check them in LR to see if Tony made switch already but yes please do update them and make sure LR matches
Is cameron mentioning to give this one to himself or to Yan? I am not familiar who Yan is / his S1s if it is him
*Thread Reply:* Yeah, I think Yan is the one submitting Cam's spending
Should all the lpcontractnames for all paraquat 13 leads be the same? I would think yes. If so I can update them all to be Paraquat - Dicello 13 (TC/J) (TIP) - Contract
Yes, but Zeke needs to update in SL db too
@Zekarias Haile
*Thread Reply:* Ty @deleted-U06C7A8PVLJ !
@Zekarias Haile I had to remove this one from the TIP dashboard since agent accidently moved it into a final status on accident and reported that it isn't supposed to be a final status at all.
*Thread Reply:* Ryan answered your questions so I will let you what his answer to your questions was... when I come back from lunch @deleted-U06C7A8PVLJ
We need to stop using datastudio anyways, so aligned
Agreed ! The challenge I think what may be hard is that Doug wants a visual that shows when we hit cap or about to hit cap for one of their case types. I believe that info / data source lives in Cameron's head as in it is determined based on what is negotiated with him and the firm and at any point that cap can change (from my experience it does when we are about to hit cap for certain case types). I am assuming maybe Mal or Greg may have a spreadsheet that keeps track of those things
@Ryan e-signs yesterday dashes' aren't working... any thought as to why this happening
@deleted-U06C7A8PVLJ, did we have rows in fin_log for 4-27-25?
@here, I manually uploaded the 4-27-25 status report into AWS S3, it worked and running genfinlog with 4600 rows to process.
@Zekarias Haile @deleted-U06C7A8PVLJ, that fixed it
we have 41, please do normal routine @deleted-U06C7A8PVLJ, then @Zekarias Haile, you are good once he's done
*Thread Reply:* @deleted-U06C7A8PVLJ please let me know when you're done.
*Thread Reply:* Congrats on your new position! Shield is glad to have you @deleted-U06C7A8PVLJ
*Thread Reply:* @deleted-U06C7A8PVLJ , call me for this. I have it already built.
*Thread Reply:* On our old data. We have a Flatirons datastore built in SL DBT fyi @Nicholas McFadden
*Thread Reply:* You can join them and create it on the new data for the sign ups from TIP GCP.
*Thread Reply:* Thank you @Ryan I found it. I will review the data and will let you know if I have questions about it.
@here good morning guys, I’m gonna leave the end of month routines to you guys. If you can make the post to the team, and get the first part rolling. This shouldn’t take you about 30 minutes to an hour today.
Great. Gently nudge Rose and Malissa Monday morning on status. @Zekarias Haile , wait until all retainer fees are updated.
@Ryan Is it okay if I create new rows in TIP database for missing leads for signed & declines? In law ruler for CA Juv Hall Abuse - ACTS - DL Flatirons - Shield Legal it shows 14 signed and declines while in our database we only have 7. I included a screenshot of me looking for the 'other 7" which has no results
@Zekarias Haile , please add them back. We have done last year reconciliation in past.
*Thread Reply:* @Ryan those are signed and declined. They are not in TIP dash so it's going to create data discrepancy with Shield
Good morning team. @Zekarias Haile This one is moving to signed & declined. An intake agent decided to put this as a final on their own. I reported this to the proper individuals however just wanted to share with you for SL end
Nick M. Should be included in this. Is this the meeting invite sent?
Great. To answer the question now, that data is coming from a large complicated joint of copper data, leads Pia data and the old aggregated financials.
It didn't show up, Jeez LR. I am pulling it from Report Center now and dropping here, do this in future IF it does not show up?
Get the call costs sheet done, the staff revenue and case revenue all complete including talk time from Five9. Once that’s all done I can check sheet, check prices and get Flatirons overhead into SL Looker. So use today to get those done and let me know.
@Zekarias Haile I am removing this lead from the dashboard 713278 since Alan Gill deleted this lead due to duplicate. (My guess)
I would tell Melissa to correct her sheet before you do anything else,
@Ryan @Zekarias Haile I completed the VSS Monthly Completions Scratch for month of April. I am going back to finishing the talk time and once that is updated I will post that here as well
I uploaded a hard copy of those reports in our google cloud bucket. Bucket is called eom-talk-time-reports-blx-bcl-shield-legal and I organized it based on year --> month --> bcl and shield legal folders which has the reports That way we can refer to them in the future if needed
Here is the SQL query that calculates BLX - BCL talk time and under neath that query is the S.L one for intakes
Guys, how much more do we need done for EOM? What all is left? We need it done by EOD today.
Okay this is where we last had it. I updated the talk time and VSS completion sheet. I slacked Nick W who should be giving me the talk time for secondaries in ring central so once he has that the secondaries talk time will be finished too. I am reviewing what is left
@Zekarias Haile , can you get sheet done before you update the cost per leads for call costs?
*Thread Reply:* Yeah, sure. I was waiting for talk time to be updated
*Thread Reply:* Please let me know when sheet is complete!
*Thread Reply:* @Ryan I'm done. Sheets has been shared.
@Zekarias Haile, your sheet looks correct. Please run UPDATE SQL for 2025-04-01 and 2025-05-01 for all verticals. Please make sure we have entries for Mormon Abuse and San Diego Juv Detention YRTC Abuse. Check Jack, Caleb and Maximus's Daily Financials tab on each of their dashboards to ensure we have no nulls and forgot any new INSERTS. If brand new, give it a CPL of 25.00 as initial placeholder.
@Zekarias Haile, once done let me know so I can validate Call Costs match in SL versus TIP dash.
Im running 2025-04-01 update @Ryan quick question as you can see on the sheet, PA Juv doesn't have corresponding match neither on SL sold leads column nor TIP worked lead column. How should I handle it?
Just make sure it has a table w try for both months.
I’ll do S1s allocations and you just post them, use the 1 code to see them.
@Zekarias Haile, so the SL Call Costs are at $825K, not close enough. So this sheet here: https://docs.google.com/spreadsheets/d/1hz2yhgfxKgkxzhsQdSZmPB6v6Av7GQBmecFAWf9X6CY/edit?gid=0#gid=0
Left side is SL dashboard exported, right side is your Google Sheet for TIP cost per worked leads, line these up on the same row for matching, and figure out why SL is $83K off, we need to ensure we have an April and May entry for any Case Types / Campaigns missing and get their March or February CPL value carried forward to increase SL call costs to be closer to $908K.
Make sense? Very important you compare and match the SL costs to TIP costs once your SQL UPDATES & INSERTS are done.
*Thread Reply:* Okay, I will take a look once I complete the number. TY!
*Thread Reply:* I'm running the update statement. FYI
*Thread Reply:* @Ryan I lined up and saw where the difference and ran the update statement accordingly. But It seems that is even not the increasing the SL cost
*Thread Reply:* @Zekarias Haile , let me know when this is correct at $908K
*Thread Reply:* Will start working when I get back from lunch and will let you know.
*Thread Reply:* @Ryan I checked all of it and there's no missing or wrong entries both on April and May but I dont that being reflected on the dash and the dash is still of by around $59k.
*Thread Reply:* @Zekarias Haile , high light the ones in pink on the sheet where they don’t match in SL total call costs
*Thread Reply:* The ones missing the $59K, something is up
*Thread Reply:* And this has never happened before
*Thread Reply:* I have marked them those didnt match
@Zekarias Haile, so the SL Call Costs are at $825K, not close enough. So this sheet here: https://docs.google.com/spreadsheets/d/1hz2yhgfxKgkxzhsQdSZmPB6v6Av7GQBmecFAWf9X6CY/edit?gid=0#gid=0
Left side is SL dashboard exported, right side is your Google Sheet for TIP cost per worked leads, line these up on the same row for matching, and figure out why SL is $83K off, we need to ensure we have an April and May entry for any Case Types / Campaigns missing and get their March or February CPL value carried forward to increase SL call costs to be closer to $908K.
Make sense? Very important you compare and match the SL costs to TIP costs once your SQL UPDATES & INSERTS are done.
It looks like Gaming Addiction is the one that is not in the old dash. I checked the gaming addiction channel and I don't see signup in there
Use old one, there were two test integration leads in there today.
updated on SL side and Told Anthony to update it on LR and remind agents.
@deleted-U06C7A8PVLJ, again, this April 2025 spike to 10,840 minutes is true Five9 signed e-sign contract total talk time for Bard Powerport orders only correct?
@deleted-U06C7A8PVLJ, get with Nick Ward and make sure we are only adding in Five9 Talk Time for leads signed e-sign per campaign into our numbers each month. If we really did 10,804 minutes for 101 contracts, that is fine, just need to double make sure.
Great. It just could be that 101 contracts took that much talk time minutes.
Ok, so you are skipping TIP BI priorities meeting?
Edward, please rerun and see if we get more into PostGres fin_log table.
Cam says we closed 151 but assuming he counted from Slack Alerts…not sure.
Ok. Let me know. I’ll be offline from an hour, let me know.
@deleted-U06C7A8PVLJ , let me mess with it. Might need to reload status report. Give me 45
This lead ID is where the error occurs: 735315, I deleted it from the rawstatusreport table and re-running again. If it fails after this, I'll reload 5-28-2025 status report and see what happens.
@here, I have 149, but the new status report failed again on 735315, so I removed the LP Lead ID to see if that causes the fail.
@Zekarias Haile, you can update the DONOTTOUCH table soon.
@deleted-U06C7A8PVLJ, that worked! Removing case description, so please add this S1 to it in the finlog database. RXPZ0O6V and do other S1s and posts for the 150, PL-FacebookMB-CPCMP-PFAS06.1
@deleted-U06C7A8PVLJ, you and Zek can get started. I am traveling to office now. Just let @Zekarias Haile know when you can start.
@Edward Weber , come to kitchen, I’ll tell you this
@deleted-U06C7A8PVLJ as per Cam we are missing around 33 leads. I think u need to rerun the lambda again please
@deleted-U06C7A8PVLJ, validate with our data and TIP team how many e-signs we have NOW for 6-3-2025 before you run, your LR Data shows 153 too:
*Thread Reply:* @Ryan I think u are looking 6/2 numbers... for 6/3 the dash you created shows 166
*Thread Reply:* How many do you have in Adjustments DNT table @Zekarias Haile?
*Thread Reply:* Ty, please confirm final new count. Should be 166 per LR Data.
*Thread Reply:* Edward, you have to read the logs, and find out which one it’s failing on over and over, and remove the case description value, but look up the S1 to add later. If it fails doing that, then you just remove that row from the status table, and add it manually. I don’t know what that error is, or why it’s failing, but it is failing on one lead when I solved it last time.
*Thread Reply:* I need you to read the logs closer and see right where it fails and what lead ID it’s failing on, and go pull it up in the Ross status table, I’m headed to the airport. We gotta get that thing to 166, but it may process to 186 because that’s looking at yesterday’s status report data. Even though you’ll decline those other 20 out tomorrow.
*Thread Reply:* I’m in the car to the airport, do you understand what you need to do so that I don’t have to worry about this not getting done while I’m traveling?
*Thread Reply:* You are doing this in rawstatusreport table.
@deleted-U06C7A8PVLJ, validate with our data and TIP team how many e-signs we have NOW for 6-3-2025 before you run, your LR Data shows 153 too:
@deleted-U06C7A8PVLJ, we do need a re-run as we are missing 13 deals. Old PG DB has 153, new system shows 166 as of now. It was at 183 but declines have occured today
@deleted-U06C7A8PVLJ , if and of the ones you deleted, those could be e-signs, so you need to manually add them to fin_log, get it?
@Ryan @deleted-U06C7A8PVLJ I dont see rows in fin_log for 6/4
Edward, have James look at the logs and the data sets that you’re having to take out and put back in just in case they’re a sign, this error is new. We can’t figure out why.
It’s pulling contract IDs those can’t be known. It also looks at both prices, maybe those are no? Send Mark Urra the case type names and let him check.
Mark is troubleshooting and this is what he found so far
Ah hah! That is the issue. Great find. So what now? We signed one on a non-existent contract.
@Zekarias Haile, you need ensure it is put in the numbers as the 2nd one from @deleted-U06C7A8PVLJ, makes sense guys? The contract it signed to has been moved. @deleted-U06C7A8PVLJ, make sure the 2nd one is how the template is named in LawRuler and in TIPFinLog, get it?
Sure, will do. Please let me know when you done Edward.
*Thread Reply:* I asked others and this is what they said about LR
@deleted-U06C7A8PVLJ, did we learn how to prevent error now? Missing LP Contract or NULL value.
*Thread Reply:* This is the same file as above except it goes more into detail such as if it is an intake or secondary along with law ruler IDs and when they were signed up as well
@deleted-U06C7A8PVLJ @Zekarias Haile, it appears that when I populated Agent and Office Location that were NULL, it worked this morning on lead ID 749860. We have 121 in old PG, 115 in new GCP.
New dash is back at 117, so @Zekarias Haile, get it to 121 to match old one.
@Zekarias Haile, since I am camping, can yuo do tomorrow after church, I'll have TIP side done before you start. Thank you.
*Thread Reply:* Thanks @deleted-U06C7A8PVLJ
@deleted-U06C7A8PVLJ ; also make sure Rose completes the staffing sheets AND let me know for old system.
BUT, Rose must fill out new system Opearting Expneses and bee Ofher Revenue.
~P~lease ask James Turner to ensure she does that. I’ll have EOM review with you two and Rose, Malissa to make sure.
@Ryan shared a sheet with you. When u get a chance please take a look. Ty!
@Zekarias Haile Olivia from reporting team asked for this lead to be moved over to "Mormon Victim Abuse OSOL - AWKO - AWKO - Shield Legal" since that is what law ruler is showing. So I changed it from TIP's end and you may need to update it from SL end
*Thread Reply:* @deleted-U06C7A8PVLJ From which campaign do you moved from?
*Thread Reply:* Nvm, I think I saw from LR it was in Mormon -AWKO-AWKO campaign. Thanks
@Ryan you going to take care of the BLX ones right?
What does she mean? Flatirons VSS or something else that is NOT in PG?
I’ll be on the 10:30 to check EOM status with you.
I don't see this contract on LP. Do we have San Bernardino DL - DL ?
*Thread Reply:* @Ryan . This what happened to this lead. "it's a referral just from Dicello and Tony instructed to put it under just DL" and Mal asked Cam and he said " Not billable. We will only bill from TIP manual sign up. 200.00"
so my question is since this lead is counted as part of today's deal... I have to remove it and Edward also needs to do the same thing?
*Thread Reply:* Remove it if not billable, it's now a decline, correct @Zekarias Haile
*Thread Reply:* No it's not decline. It was referred by DL that's why it's not billable
*Thread Reply:* Cam said will only bill from TIP manual sign up 200.00. Not sure what he meant by that
*Thread Reply:* Too will charge to close, no revenue on shield side. Make sense?
yeah Im going to ask Sobo.. since they need to change it in LR
@Zekarias Haile , check mediarawv2, that an error? Use reconciliation dashboard to confirm. That looks like we missed a contract count possibly?
@Ryan . This what happened to this lead. "it's a referral just from Dicello and Tony instructed to put it under just DL" and Mal asked Cam and he said " Not billable. We will only bill from TIP manual sign up. 200.00"
so my question is since this lead is counted as part of today's deal... I have to remove it and Edward also needs to do the same thing?
Morning guys, need EOM done by 9am today, have EOM meeting. Zek, please let me know when Call Costs from TIP match in SL so I can verify too.
Sorry to ask but just want to confirm I am not updating it wrongly, grossrevenue and netrevenue is calculated based on the CNX1 Horizontal-Outcome Call Time Calculator spreadsheet? Since the two fields grossrevenue and netrevenue I get those from that spreadsheet.
We’re talking about public.financial_log table only, just like when you update the revenue rates and the agent commissions at the end of the month. The talk time minutes from Connex one and 59 should be added manually ongoing after this, but the talk time revenue should be set to zero for all Shield legal billable retainers.
Yes, I understand we're focusing only on the public.financial_log table for Shield Legal billable retainers. My main goal here is to clarify how exactly we're calculating the gross_revenue and net_revenue. Currently, I'm using the CNX1 Horizontal-Outcome Call Time Calculator Google spreadsheet, which calculates these revenues automatically once I input the necessary values.
Since I didn't originally build this spreadsheet, I'm trying to better grasp the underlying formula. Specifically, I'm referring to column R, labeled Case Review Call Revenue Per Row in FinLog, which currently uses a formula like N65 ** 2. Here, N65 represents the "Case Review Minutes," and I assume multiplying by 2 means we charge $2 per minute of talk time.
If I modify the formula by removing ** 2, leaving it as just N65, this change reflects minutes alone, not revenue. This adjustment would consequently update the Rev per Lead columns (gross_revenue and net_revenue) and the Minutes per e-Sign column (which aligns with talk_time_minutes).
Could you confirm if my understanding is correct? I am also attaching a screenshot of the SQL query that I normally use to update those values (an old one)
This spreadsheet is what I've been using to calculate gross_revenue and net_revenue. Typically, I just input the values, and the spreadsheet handles the calculations automatically.
Since I didn't originally create these formulas, I want to make sure I'm correctly updating the spreadsheet for the revised calculations you've requested. Does it sound like I am correct with how things should be updated?
Got it, the conttract revenue is the total and net revenue
Thank you @deleted-U06C7A8PVLJ! I have updated the sheet. Will run the update statement once I'm done with the numbers.
@Ryan TIP CC is $788k and SL is $790k... working on the difference to see if I could bring SL down. FYI
@Ryan Rose asked me to let her know that when the update is done. May CC cost matched at $788K and $789k. So I'm going to let her know that May is good to go.
@Zekarias Haile , thank you. I’ll be gone on PTO starting Monday for two weeks good luck, and if you run into trouble on these lamb does lean into Nick and James, they know and understand us better than I do.
For now we shall place them there @Ryan and here is the list @Zekarias Haile. We can remove these later on if needed if there is a special process for blx however Flatirons stated they want to see them in the dashboard by Monday morning
@Cameron Rentch didn’t sell those, they are organic and don’t belong in SL but ask Cameron if he wa ya the counts added with zero revenue for these.
@Zekarias Haile I shall work with you tomorrow on this. Cameron stated those 28 DeMayo Media buyer doesn't matter (so we can add them as CR) and to add them as shield legal
That makes sense. We should get their feedback on what summary metrics that would like to see visualized
These clients statuses were changed to Signed & Declined per a meeting I had with Abe. @Zekarias Haile
EOM upon us!!! 🤣 Please post out the Slack communication @deleted-U06C7A8PVLJ, and lets see if we can get this done and wrapped by July 10th to achieve our "close prior month" goals set by Cam and Marc.
@Ryan SL matched at 1,539,000 and TIP Matched at 1,547,000. Trying to make it closer.
@Ryan are we good to close June numbers? @deleted-U06C7A8PVLJ fyi
Ask Mark Maniora , he sets them up. If not him, Malissa
@Ryan, Edward and me was running the lambda and keeps failing. Finally we got this error. Just bumped the time out from 8 to 15 minutes. FYI . And it's currently running and hopefully this will fix it
"errorMessage": "2025-07-23T17:50:12.463Z 99ae1748-233a-43f2-a301-d3ea868a65c7 Task timed out after 512.07 seconds"
@here, that happens when our email captured LawRuler status report has more than 5500 rows in it to process. But yes, 15 minutes should fix it!
@Zekarias Haile, Marc & Cam are asking for financials to be ready, how much time till you got the 120 in there?
@Ryan Can you take a look at the error please?
"errorMessage": "2025-07-23T18:13:25.407Z f06e9d3c-ff72-4262-9928-8135c66daa78 Task timed out after 900.05 seconds"
3c-ff72-4262-9928-8135c66daa78 Added finlog entry: FinancialLog(lrleadid=110183, signeddate=datetime.date(2022, 11, 14)) [INFO] 2025-07-23T18:13:18.576Z f06e9d3c-ff72-4262-9928-8135c66daa78 #5118/6025: [INFO] 20250723T18:13:18.577Z f06e9d3c-ff72-4262-9928-8135c66daa78 LR Lead ID: 109900 [INFO] 2025-07-23T18:13:18.577Z f06e9d3c-ff72-4262-9928-8135c66daa78 Idle Time: 2025-07-22 22:26:48 [INFO] 2025-07-23T18:13:18.577Z f06e9d3c-ff72-4262-9928-8135c66daa78 Case Desc.: UADDG4CQ [INFO] 2025-07-23T18:13:19.379Z f06e9d3c-ff72-4262-9928-8135c66daa78 LP Lead ID: UADDG4CQ [INFO] 2025-07-23T18:13:19.687Z f06e9d3c-ff72-4262-9928-8135c66daa78 Added finlog entry: FinancialLog(lrleadid=109900, signeddate=datetime.date(2022, 11, 14)) [INFO] 2025-07-23T18:13:19.749Z f06e9d3c-ff72-4262-9928-8135c66daa78 #5119/6025: [INFO] 20250723T18:13:19.749Z f06e9d3c-ff72-4262-9928-8135c66daa78 LR Lead ID: 110155 [INFO] 2025-07-23T18:13:19.750Z f06e9d3c-ff72-4262-9928-8135c66daa78 Idle Time: 2025-07-22 22:26:48 [INFO] 2025-07-23T18:13:19.750Z f06e9d3c-ff72-4262-9928-8135c66daa78 Case Desc.: YPZEYDA6 [INFO] 2025-07-23T18:13:20.520Z f06e9d3c-ff72-4262-9928-8135c66daa78 LP Lead ID: YPZEYDA6 [INFO] 2025-07-23T18:13:20.829Z f06e9d3c-ff72-4262-9928-8135c66daa78 Added finlog entry: FinancialLog(lrleadid=110155, signeddate=datetime.date(2022, 11, 14)) [INFO] 2025-07-23T18:13:20.892Z f06e9d3c-ff72-4262-9928-8135c66daa78 #5120/6025: [INFO] 20250723T18:13:20.892Z f06e9d3c-ff72-4262-9928-8135c66daa78 LR Lead ID: 110228 [INFO] 2025-07-23T18:13:20.892Z f06e9d3c-ff72-4262-9928-8135c66daa78 Idle Time: 2025-07-22 22:26:48 [INFO] 2025-07-23T18:13:20.892Z f06e9d3c-ff72-4262-9928-8135c66daa78 Case Desc.: IUXPXHPN [INFO] 2025-07-23T18:13:21.685Z f06e9d3c-ff72-4262-9928-8135c66daa78 LP Lead ID: IUXPXHPN [INFO] 2025-07-23T18:13:21.994Z f06e9d3c-ff72-4262-9928-8135c66daa78 Added finlog entry: FinancialLog(lrleadid=110228, signeddate=datetime.date(2022, 11, 14)) [INFO] 2025-07-23T18:13:22.056Z f06e9d3c-ff72-4262-9928-8135c66daa78 #5121/6025: [INFO] 20250723T18:13:22.056Z f06e9d3c-ff72-4262-9928-8135c66daa78 LR Lead ID: 110112 [INFO] 2025-07-23T18:13:22.056Z f06e9d3c-ff72-4262-9928-8135c66daa78 Idle Time: 2025-07-22 22:26:48 [INFO] 2025-07-23T18:13:22.056Z f06e9d3c-ff72-4262-9928-8135c66daa78 Case Desc.: 2VMOLJC6 [INFO] 2025-07-23T18:13:22.834Z f06e9d3c-ff72-4262-9928-8135c66daa78 LP Lead ID: 2VMOLJC6 [INFO] 2025-07-23T18:13:23.142Z f06e9d3c-ff72-4262-9928-8135c66daa78 Added finlog entry: FinancialLog(lrleadid=110112, signeddate=datetime.date(2022, 11, 26)) [INFO] 2025-07-23T18:13:23.204Z f06e9d3c-ff72-4262-9928-8135c66daa78 #5122/6025: [INFO] 20250723T18:13:23.204Z f06e9d3c-ff72-4262-9928-8135c66daa78 LR Lead ID: 110103 [INFO] 2025-07-23T18:13:23.204Z f06e9d3c-ff72-4262-9928-8135c66daa78 Idle Time: 2025-07-22 22:26:48 [INFO] 2025-07-23T18:13:23.204Z f06e9d3c-ff72-4262-9928-8135c66daa78 Case Desc.: 6V46IWIL [INFO] 2025-07-23T18:13:23.979Z f06e9d3c-ff72-4262-9928-8135c66daa78 LP Lead ID: 6V46IWIL [INFO] 2025-07-23T18:13:24.292Z f06e9d3c-ff72-4262-9928-8135c66daa78 Added finlog entry: FinancialLog(lrleadid=110103, signeddate=datetime.date(2022, 11, 14)) [INFO] 2025-07-23T18:13:24.354Z f06e9d3c-ff72-4262-9928-8135c66daa78 #5123/6025: [INFO] 20250723T18:13:24.355Z f06e9d3c-ff72-4262-9928-8135c66daa78 LR Lead ID: 110096 [INFO] 2025-07-23T18:13:24.355Z f06e9d3c-ff72-4262-9928-8135c66daa78 Idle Time: 2025-07-22 22:26:48 [INFO] 2025-07-23T18:13:24.355Z f06e9d3c-ff72-4262-9928-8135c66daa78 Case Desc.: 6FZKRFZ6 [INFO] 2025-07-23T18:13:25.102Z f06e9d3c-ff72-4262-9928-8135c66daa78 LP Lead ID: 6FZKRFZ6 2025-07-23T18:13:25.407Z f06e9d3c-ff72-4262-9928-8135c66daa78 Task timed out after 900.05 seconds
END RequestId: f06e9d3c-ff72-4262-9928-8135c66daa78
REPORT RequestId: f06e9d3c-ff72-4262-9928-8135c66daa78 Duration: 900046.09 ms Billed Duration: 900000 ms Memory Size: 512 MB Max Memory Used: 231 MB Init Duration: 2176.25 ms
XRAY TraceId: 1-688122bd-7dc2c21c79bd68200652777e SegmentId: 4c4a87202f4be79a Sampled: true
@Ryan do I have to wait for the lambda run successfully before I post it's ready for adjustments? Since we have the sign ins
It will catch up and finish green once it drops below 6025 rows to process.
@deleted-U06C7A8PVLJ make GenFinLog time out 30 minutes, it timed out at 15 today for 6600 ish rows.
@Zekarias Haile, almost done, juggling dev meeting and re-running this.
@deleted-U06C7A8PVLJ, I have put in S1s, please post them to slack when you can. I made the ...dowprice_ column a value of 1 if I added S1.
@Ryan Edward and I were discussing about changing the signed date on TIP publicfinanciallog for those 67 Hair relaxer Crump/DL leads re-triggered on 08/04 to match the signed date with LR for data integrity purpose and I want to let you know first before I do that
@deleted-U06C7A8PVLJ , setup his user and triple make triple sure he has zero access to the Explore Menus for financials. Then he becomes a User and he can make dashboards.
The folder access doesn’t control Explore Users. And please clean up that menu with your tests. He explore statements. You need to involve James to make sure he can help. Let me know how to do it.
I need to push a little bit further our end of the month meeting, my laptop just died and I’m remote without my adapter
*Thread Reply:* Thank you @deleted-U06C7A8PVLJ
*Thread Reply:* I will finish up my part.
Mal stated these two case types she doesn't have the costs for as a friendly heads up. Otherwise I captured all the case types for the month of july. I am now going to work on updating the talk time stuff
@Zekarias Haile , I meet with Cameron at 9:30 am to close month, please have EOM on SL side ready. Let me know when CC costs match.
I see we are about $9K off, work on reducing that @Zekarias Haile and we are good from here.
@Ryan running the update the statement right now
@Ryan update statement has been completed and working on reconciliation
Seems like our update got us further away from $1.38M, Cam and Rose are waiting for us to close, please let us know as you reconcile it back up. @Zekarias Haile, FYI @deleted-U06C7A8PVLJ
TIP Invoices by Month shows $1,339,638.
@Ryan It matches now. the difference is less than 2%
This is the current e-sign date. It should be updated with 2/19/2025 @Zekarias Haile for this law ruler lead 451334 PL-TiktokMB-MLCMPCPAPFORM41Ques_creatives
@Zekarias Haile Law Ruler ID: 501615 - Current e-sign date: 2025-04-30. It should be 6/4/2024 since that is when they signed the retainer. The first date (2025) was when there was a status change which isn't correct. Let's update it to 6/4/2024. PL-FacebookMB-MLCPAPFORM1Question
@deleted-U06C7A8PVLJ the dashboard wont show signed and declined status... so I cant added to SL database
*Thread Reply:* Some of those dates don’t match up in shield at work, because the tip sign date isn’t always the same if they had to do a re-sign,
*Thread Reply:* Or they made a mistake and did a resign
@Zekarias Haile Law Ruler ID: 631908 - Current signed date is 04/30/2025 however we would need to update the date to 11-14-2024 so that benchmark and flatirons dashboard would match. 11-14-2024 is when it was signed; 04/30/2025 is a status change but not an actual retainer signature at all
@Zekarias Haile This lead: 709104 is going to be signed & decline soon (luis is going to change it) but I got tony approval for them to change it from warehouse to signed & decline due to client not signing the retainer but only the conflict waiver which triggered a false signed e-sign date. (Posting this as a future reference since we would need to ensure this matching SL database and flatirons dashboard)
Per Cameron, please update DBs on TIP and SL AWS to reconcile. Here are those Lead IDs:
- 712508
- 71962
- 711822
- 809956
- 715471
@Ryan Edward and I have updated the DB's and Edward texted Luis to change it on LR.
@Ryan Edward and I have updated the DB's and Edward texted Luis to change it on LR.
FYI for August 2025.
No, just know that your morning routine of doing declines needs to go back to December 1, 2023 to ensure Flatirons are always accounted for. And that doing declines in the AM is key as we need "Yesterday" to be true ASAP each day.
@Zekarias Haile, Brittany's team stopped doing the old SL to old TIP AWS reconciliation, that will be enforced as a MUST do each day like we did for years but if fell off earlier in the summer. They stopped doing it. That has casued this issue of poor reconciliation. So starting in August 2025, we'll need every case type reconciled, all the way back to December 2023.
It is now Edward, it was set to Jan 20th, 2025 for some odd reason.
Tomorrow, probably have a lot to clean up.
I'm reloading LR Status Reports from 25 and 26 today to get e-signs into TIP AWS financial_log FYI
@Zekarias Haile, I'm rerunning 8-26-2025 JUST in case some where declined at 2:30 AM and made active e-signed this morning. Keep plucking the leadIDs from Page 0 for differences though. Just doing a triple check on my side.
@Zekarias Haile @deleted-U06C7A8PVLJ, what caused the 15 deals from the 2025-08-25 to not be in financial_log when Real Time BI (Page 0) showed 144 versus our 129?
@Ryan Some of the leads were re-triggered yesterday (8-26) as a result the signed date on fin_log was showing 8-26. But when I checked the LR activity log the earliest sign in is 8-25 so I moved them to 8-25 on the database for data integrity purposes