Hey there~ I came up with something I think you and your team might find useful
Its a basic little python set of tools that I call Cartographer
2 main tools, one is Mr.Context which takes 2 different excel columns and uses a basic machine learning model to understand context on both, find the best match and spit that out as an excel file
Exactly yeah- if you want we can set up a 1:1 tomorrow and I'll show you how it works
Hey good morning man- This is the first run so you might run into hiccups here and there but this should work for ya
I'm gonna send it from my other acc because it won't let me send an application here
Oh my way to the new office now, may be a couple mins late
Hey man, gotta sync with you when you get the chance
I was supposed to be out of this meeting like 40 minutes ago so hopefully soon lol
Sure thing' I'll try and do it asap, I'll head over to your desk
My bad, I didn't realize you were in this too
After this, let's try and sync with Nick so we can get a better idea of why ryan is running you through all this and what he's talking about. My understanding is he would be having another team do the finance stuff, not you
Please for the love of dog, don't ever name your commits "dasdh"
Are you following? 100% okay if you aren't
Plan to keep our meeting with Ryan for now, but know that I am in a meeting with him now and it's set to go long
Cool stuff- Meeting with people from Google
So not quite, the larger the query, the more PC power necessary and that's what costs, but that can add up very quickly. What are you thinking to do?
Best path for now would be to build the queries in postgresql and talk to Dustin and I before sending anything through. I recommend mocking up schema blueprints of what you want things to do/look like
Good question- pg Admin is kind of like vscode for postgres SQL databases
They are very very similiar, I think pretty much the same outside of a new minor verbage differences
Hey are you busy today? I got some cool info and wanted to discuss the reason for that project I posted about
Edward's LR Endpoints / API Scripts Hey James. I am sending you a link where I am posting for scripts I built that are adhoc in terms of using law ruler API's and ones that are scheduled weekly. I am still adding more to it (as I search my codes) but essentially this should cover all the codes I built so far that uses the LR API for different purposes
Heyo- Notice will be going out to the broader company soon but pause all reports from lawruler and any code that touches the lawruler API
Yaknow how we were SUPER careful about how we named everything yesterday on the schema?
Heyo, can you please check if the pk_id columns are integers on ryans Postgresql tables?
I'm planning on sending this to ryan but I want to be sure first..
Hey Ryan, I’ve made good progress on the status rates table. I’m also using this as a teaching opportunity for Edward, who is shadowing me, and he seems to be picking it up well! I did have a couple of questions regarding the specs for the table:
- For the
pk_id, I noticed that it’s currently specified as anINTERVAL. Since thepk_idin the PostgreSQL table is an integer, did you want to source it from that table, or should we consider another approach? - For the
external_status_name_desc, should we populate this with a default value for now so it can be properly populated using Brian’s tool? Thanks for your guidance!
If you wanna come to my office for the sync, youre all good to cmon over
I'm getting all the final bits and bobs put together for the status table Ryan requested last week ahead of our meeting with him today, but wanted to give you the opportunity to look it over~
I started early this morning and didn't drive in yet because I was locked in on projects lol
Now rather than create 2 seperate automation scripts, I created one that should do both.
I know you are new to SQL but if you wanna look over the tables and let me know if that query makes sense I would appreciate lol
I know we discussed leaving out things not on that excel sheet, and the pros and cons, but as ryan wrote it on the requirements (excel line 19)- that would insert them anyway?
You're all good, the way he wrote it was confusing
he said to make a piece of code that inserts data from the ID column of the the lr_statusus table to the new status table every 5 minutes
insert being something that puts new columns in, so we don't actually want to insert every time
because you will get a bunch of duplicates
My understanding of what he was asking for was a way to see if it's already in there, and insert new ones
Hey there' You are all good. Thank you for letting me know ahead of time. Get some rest!
I spoke to Nick and let him know you told me yesterday that you aren't feeling well. Are you still okay for our sync today?
Nick is concerned about the flatirons stuff
update FINLOG // table name Parentcasetype = "oldname" typeofcase = "Michigan Pollution INJURY - Ben Crump Law - ELG - Shield Legal" retainercode = _
where leadid IN (1,2,3,4)
What is the parent case type? get list of lead IDs to change to new case type What should new retainer code be?
Yessirree- I'm on my way over now. May have to call it a lil early bc I have a cam presentation right after
I appreciate you filling me in on that! I've been doing the double slashes forever and making sure my path names have no spaces. This is much easier!
Hey there~ is it okay if we cancel this 1:1 and re-sync later in the week? Working on a piece of code here and allllmooostt there (maybe)
My guess is the reason they told Ryan to make a seperate project for each data set, is so you by default only include the information you want, rather than import everything and have to manually hide and edit each item
My understanding:
project - all of the connections you want for a project
View - a read only view of a table that you are connected to measure - a way to get new results, from existing data points. For example, a measure might be a count of how many leads you have per case.
explore: all of your tables from the view + all of the measures you created.
visualization: the actual graph/bar chart/table visualization, that you want to put into a dashboard.
DBT is taking the role of creating measures from lookml
Hey there, I apologize - you're good to keep going on with the flatirons stuff. Let me know if you run into any trouble with it
From what I'm getting out of this.. it's kind of like jupyter but for SQL
But instead of different code blocks, it's different files, in different sections
I don't really see why this is needed vs just properly organizing GitHub and using code but ehhh
Telling someone "Make a table" doesn't mean anything when you don't tell them what you want the table to be.
Yeah- I am attempting to slowly lead Ryan to doing that, and I think the finance log stuff is a good example of him starting to understand that we need that information to do our jobs
For the finance log stuff (https://docs.google.com/spreadsheets/d/1p9QZpMZX4ww4AXFAnu1nJRb8XCozzWEGtjZK1UWPmQA/edit?usp=sharing) I am in the process of attempting to get LP contract ID on each campaign right now, but running into some issues because of how the contract ID is pulled from Lawruler initally
Hey there, in the postgresql tables, is there any leadspedia tables that show the contract ids?
I looked through every single table in both the tip and shield bigquery and found contracts, but nothing I can join them on.
I think that might actually be what I need..
I didn't realize we had data on postgresql that still wasn't viewed over
Do you see a way to share it with me, or to create a view to Bigquery?
Hhm- I'm not either, but it's important that I get access to that postgresql data if we wanna make this finance table make any sense.
I apologize for taking you away from what you were doing but can you check to see if it is mirrored on Bigquery. I looked through every table but I /hope/ I just missed it
Well shit- This might be it.. I wonder if I overlooked it because the number of rows don't make sense
we have 645,000 leads in LR, and this only has 110k rows]
That table that you originally showed me in Postgres
Hey man, welcome back! How was your day off?
Got my script to work and working on creating a nice little dashboard to view
I was thinking "Wow this is really orange"
How did you get that orange is his favorite color?
Let's move the sync until tomorrow. Ryan's going over and it's throwing my whole schedule out
Hey there, is there any way we can incorporate the subcat stuff first? Already seeing it where it would be helpful on a few other projects
I appreciate! Just to clarify what I mean is the intake/bcl/secondary bools and the secondary_category
I'm starting the process of the "All Case Types in LR" page into BQ and there are about 44 errors that are preventing things from being brought in as a proper boolian.
For example:
in column L, which is a true false for if it's a Sec case, instead of saying "Yes" it says SEC.
Am I okay to change those kind of things and clean it up?
I'll keep you updated on my progress on this side- I wanted to make sure it was okay with you before I edit your thing haha
How are you doing? Is it alright if we push to 1:30? hitting a flow
Looking at this here, and it looks like the Docs cases are showing up as intake, do you know why that is?
I may have read it wrong, because Im searching for dupes but the one populating as intake true is • CA Juvenile Hall Abuse DOCS - ACTS - ACTS - ACTS
Looks like it might actually just be that one
That makes sense, I think it was actually just that one case type that was incorrect
I can change on my end, sorry for confusion lol
Good catch! That is what I was trying to figure out earlier when I mentioned we didn't have status discrepancy on my table
I see you trimmed " " but are you including blanks or "spaces" in those searches\
If you add OR " " to the IS NOT NULL line
I think the trim just removes the empty spaces, but removing them wouldn't make it a null value
Talked to ryan and actually learned some helpful stuff
Doing well! Working on a lot of ryans stuff. I don't have much to update on, because I'm still sorting out the weird in most of it
I hope there isn't too much weird stuff about it haha.
As for myself I am working on building this for Luis and Flatirons specifically:
There are a few parts that my script must do as well in order to inform Flatirons about discrepancies / data that needs to be adjusted from their end. I went ahead and wrote out what was discussed to me in a meeting I had with all the parties involved and got them to confirm with me thru email that this is what is needed and if so I shall build it just like this. Since last time I build the code the way that it was mentioned to me I did it like that, however one person asked me if I was factoring in something else when that wasn't told to me at all. So basically just having it documented that way if it wasn't something agreed on then they can't say it was me lol
Sure thing! Swing on by, Nick is here as well so we can talk to him
Hey James. I am looking at those tables you mentioned in bigquery and got together with aidan about it and we probably still need more clarity on it. Aidan had a task where he would calculate talk time for intakes and secondary, etc and he is transferring that task to me since Ryan wants me to do it but since it is my first time learning it I am not sure what I am looking for in the terms of what fields in the bigquery table wise and I showed Aidan the same thing and he isn't sure. Also based off what Ryan is saying he wants us three to get together to solve this and I feel like we may need David as well since he is the five9 guy. Aidan just needs to know "How to pull the reports" which then he will show me so that I can do it from my end too. However Aidan leaves before 2:00 pm and looks like Ryan needs us to have this solved by today for Rose for financials
Sorry I left for lunch right before you sent that~
Time time per section like screener, closer, ect is going to be a really difficult thing. It's on my radar but right now we have no way in the data to separate what job a person does.
We might be able to go by status change but it's not reliable
I brought it up with Ryan briefly, and he told me not to worry about that part and just do the other aspects of financials for now. I didn't know he was passing it off to you guys
I talked to David briefly but we can try again
Hey there' I would like to get Nick in on our 1:1 for today if we can so we can talk over the request from Ryan and figure out what he needs + what he was getting from connex before
Hes not in the office right now, but I'll let you know when he gets back in- he just stepped out.. but I'll take a look at that for now
Hey there, I just sent 2 google sheets to you, can you please take a look and see what leads these aren't lining up on and potentially why?
WITH rankeddata AS (
SELECT
**,
ROWNUMBER() OVER (PARTITION BY leadid ORDER BY statuschangetime DESC) AS rn
FROM tort-intake-professionals.Financial_Log_Dataset.pctid_x_data_with_billable_status_and_rev_rates
WHERE (status LIKE '%Signed e-Sign%' OR tostatus LIKE '%Signed e-Sign%')
AND status NOT LIKE '%Secondary Interview%'
)
SELECT **
FROM rankeddata
WHERE rn = 1
AND DATE(statuschangetime) = DATE('2025-03-17')
AND marketingsource = 'Shield Legal'
-- AND intakebool = TRUE
AND testbool = FALSE
-- to do: Do not include tests
Where are you seeing Signed and Declined? I'm not seeing those
Interesting, yeah it looks like it is on intake under review on mine and does not show at all under is. I wonder if I change my query to have to include the to_status AND current status rather than OR
Also just updated the Ref table so in a bit I can re-generate
Lol a bit, I am watching Solo Leveling now and I am a big Cowboy Bebop fan
Haha I did enjoy FMAB quite a bit! It's been YEARS since I saw it
I haven't seen yu yu hakusho but i heard good things!
If you have a cheap projector or any projector really, I recommend using it to watch those older animes because the way it emulates those art styles are mint
Ima head out for lunch but I'll be back later
Hey there, any other findings about discrepencies?
No meeting today, trying to figure out the ryan stuff + some stuff for tony
but if you have any updates, feel free to slack me!
Today is tough because I got an impromtu task from tony on top of the ryan one, but tomorrow works!
Hey there~ Thank you so much!! I really appreciate it
Oh my gosh I just saw you sent that at midnight haha
Looker presents it as a unique letter for some reason
Cool! When we do our meeting I was wondering if you could take a look at this for me and tell me if it makes sense? https://docs.google.com/spreadsheets/d/1b9GRCOvmbiisRjG6_CmhjJ6KhdJE6aQg1TDrqYwH-HY/edit?usp=sharing
I need to go through on Lawruler and do the manual confirmation for each lead like on the manual confirmation tab.
Hey there, I want to attempt to validate my finlog stuff. On the Shield Reconciliation tab, is that JUST shield as the marketing or everyone?
I imagine just shield but I want to make sure
I got a lil fancy with it and made a lil logo/icon to show it's the new system
Hey there, happy Monday! I completely understand the hesitancy with this, you're golden! I'll draft up an email to send over today. I just had IT put in a third monitor for me so this is good timing
Can you send me a screenshot of the request from Ryan to Joe?
Please remind me your exact title again?
was it Junior or Associate data engineer? I don't have all the paylocity stuff
Can you send me the screenshot again but a little higher so It fully shows the date that message was sent
No worries at all, Just wanted it saved for my records
Hopefully that should get us a prompt follow up
Hey there, what happens when you go here: https://github.com/shield-legal/NewFin
Hey there, do you know if the current/old financial system uses the rev rates IO table?
Let me know what you get back from Ryan- I appreciate!
For Call time, is it total call time in Five9, Call time per campaign, or call time per billable lead or something else?
I believe for military records they lump multiple campaigns together and they can only do a military record one if the original campaign is already e-signed so we would just remove that entirely
So far I am pulling all of the call time for any lead that went billable in the month of march
five9.date_and_hour,
five9.bill_time__rounded_,
five9.lead_id,
leadrate.io_call_time_minute_rate
FROM five9_source.five9_bulk_call_data_tabularv2 AS five9
RIGHT JOIN Financial_Log_Dataset.Billable_Leads_With_Rates AS leadrate ON
SAFE_CAST(leadrate.leadid AS int64) = SAFE_CAST(five9.lead_id AS int64)
WHERE DATE(earliest_esign) BETWEEN '2025-03-01' AND '2025-03-31'
ORDER BY lead_id DESC, date_and_hour DES
Waiting for it to run now, but I am testing this:
five9.date_and_hour,
five9.bill_time__rounded_,
five9.lead_id,
leadrate.io_call_time_minute_rate,
leaddata.client_firm,
leaddata.old_name
FROM five9_source.five9_bulk_call_data_tabularv2 AS five9
RIGHT JOIN Financial_Log_Dataset.Billable_Leads_With_Rates AS leadrate
ON SAFE_CAST(leadrate.leadid AS int64) = SAFE_CAST(five9.lead_id AS int64)
RIGHT JOIN Financial_Log_Dataset.Billable_Leads_Unique AS leaddata
ON SAFE_CAST(leadrate.leadid AS int64) = leaddata.leadid
WHERE DATE(leaddata.earliest_esign) BETWEEN '2025-03-01' AND '2025-03-31'
ORDER BY five9.lead_id DESC, five9.date_and_hour DES
That way we can split by Client Firm or by campaign for total talk time
Sorry about that. We can talk privately in the 1:1 but Ahsan really had no place there, especially when he doesn't understand any of what it actually goes to
Please go ahead and throw that in the project chat so other ppl in the meeting + Nick Ward can see it, also please give any kind of ETA on how long that will take.
Sure thing! I'll swing by later and we can review~
Hey do you want me to try and get you some tech merch while I'm here?
I'll see what I can grab you. Stickers at least!
I got you! (Also available in white, I got one for you, one for ward)
Hey there, Ward is out of the office right now, but I have something for you to work on when you get the time..
Can you please research into docker and google cloud functions? I am writing up the code that combines monthly expenses and monthly profit and since it's much more intensive I'm doing it in python
I want to make sure we are good to go to have it run on the cloud once it's ready
Check out the expensesandprofit.py file in the Newfin github and become familiar with it. If you still aren't added lemme know
so the issue I'm running into right now, is on my billableleadswithrates dataframe, my 'earliestesign' column is a timestamp, while the monthyearenddatepk is a date datatype and they aren't playing nice together
I need to build some kind of method to turn them both into the same datatype
We also need to go through every column on here and try and figure out what we should actually be adding up for monthly profit.
*Thread Reply:* Monthly profit should be based on what Ryan has on his dashboard for TIP Financials dashboard v1.3 for "Profit X Month".
*Thread Reply:* Alright cool, so if you want to do some coding stuff, were gonna need to start converting our data to those points
*Thread Reply:* 1. Billable Leads (count)
- Talk Time Mins (count)
- Talk Time Rev (Measure of Talk time Minutes per leadid x iocalltimeminuterate within BillableLeadsWithRates)
- Contract Revenue ( Figure out what this is. It might be a sum of casetypebillablerate but honestly idk)
- gross Rev
- agent comission (I am pretty sure this is in no way accurate right now because David handled comission directly without ryans involvement)
- director commission ( that isn't in monthly expenses so idk where he's getting that)
- total expense
- profit
*Thread Reply:* Take a good read through that NewFin documentation I sent you, and make sure you understand how the code is working so far on expensesandprofit
*Thread Reply:* I just updated the code to incclude the date standard btw
*Thread Reply:* Go ahead and make a branch off main, and build some code that determines a count of billable leads per month off of the billableleadswithrates where the esign date matches the date of monthlyexpenses_processed
*Thread Reply:* If you can make it in a seperate python file so we can import it as a function to keep it nice and clean that would be awesome but isn't 100% necessary
*Thread Reply:* I reviewed the table scheme for the two that you are referring to (billableleadswithrates) and iotipmonthlyexpenses along with the NewFin documentation as well. Along with, I took some valuable notes on how to calculate the director's commission which I shall share that with you along with the spreadsheet that is used for it:
It looks like pandas has a pd.to_datetime() function that we might be able to uise to simplify this whole thing
If you can start digging into how we can have a cloud function read from github, and how we can trigger it, that would be super helpful
*Thread Reply:* Potentially, or we can schedule it since it's going to be in mainly python. What would be the trigger for it?
*Thread Reply:* Cool that might work well! Thank you
Sorry getting lost in threads- you're right to approach it that way though since were looking at a few different issues
It can be done in sql but it's gonna be slow and expensive
pythons going to be much better at doing math quickly and efficently.
Hey there~ I updated the code on expensesandprofit.py last night, to be a lot easier to read\
I thought they were swapping the first 2 rows??
Lol I'm happy for you! That's a good spot
Hey good morning- I dug into the Building "Monthly Expenses Processed" last night and ultimately couldn't find a solution I loved before heading out so I'm going to dig into it a bit this morning along with some other pieces for the Newfin code
I wrote like 3 or 4 functions last night trying to find something to work but they all felt off or had some unintended consiquence.
Came up with something new while half asleep last night and ran it through AI to check it this morning..
```# ==================================== Building "Monthly Expenses Processed" ========================================
monthlyexpensesprocessed = (iotipmonthlyexpenses.assign(sumofexpenses=lambda df: df.sum(axis=1,numericonly=True) )[['monthyearenddatepk', 'sumofexpenses']] .rename(columns={'monthyearenddatepk': 'monthandyear'})) print(monthlyexpensesprocessed)
====================================================================================================================```
AI response: The two snippets do the “same” end‐result—one row per month, one total per row—but under the hood they’re quite different:
- No explicit loop vs. a pointless loop
• Your original:
• python
•
• for row in iotipmonthlyexpenses:
• monthlyexpensesprocessed['monthandyear'] = … # repeats the _entire column assignment every iteration
• monthlyexpensesprocessed['sumofexpenses'] = … # repeats the entire sum computation every iteration
• You’re re‑assigning those full columns once for each row in your DataFrame—so if you have 100 months you do 100 redundant copies of the same work.
◦
•
.assign()version: ◦ Pandas does the sum once, in a single vectorized operation, no Python‐level loop at all. dropna()vs.sum(..., numeric_only=True)• Your original usedio_tip_monthly_expenses.dropna().sum(axis=1, …). ◦ By default,dropna()with no arguments drops any row that has any NaN in any column. You’ll lose rows you might’ve wanted to keep. • Withdf.sum(axis=1, numeric_only=True)◦ pandas will skip over (not drop) non‑numeric columns and NaNs within each row, so you still get a total even if one of your expense columns was null that month.- Cleaner, self‐documenting code
- python
-
- iotipmonthly_expenses
- .assign(sumofexpenses=lambda df: df.sum(axis=1, numeric_only=True))
- [['monthyearenddatepk','sumofexpenses']]
- .rename(columns={'monthyearenddatepk':'monthandyear'})
- – you can read it top to bottom as “take your table, add a column that is
- its row‑wise sum, then select & rename.” No temp DataFrames, no reassign inside a loop, no accidental data loss from
dropna(). - Performance
- Every pandas vectorized operation is written in C under the hood; Python loops are orders of magnitude slower. As your table grows, the difference will scale up.
Bottom line:
Your logic ends up “looking” similar, but using
.assign(... sum(...))is: • More efficient (no repeated work) • Safer (doesn’t nuke rows with any NaN) • More idiomatic (1 line vs. 5+ lines of setup + loop)
I tried groupby last night. I don't remember why but for some reason the AI had some issue with it?
Sorry about that! Im planting seeds to try and get you and I desks that are close by with a tv nearby so we can work together
thanks for building that out and figuring out secrets manager
Cool, I emailed ryan, and I'm making some tweaks to call stats, lemme wrap up these few lines rq and I'll check it out
Looks good visually- pulling it down and testing now
Merged into Master, tested on master and it looks good
The dataframe we want to reference is billableleadswith_rates. If we just want certain columns we can do that by indexing...
Just throwing notes out there as I review
So earlier in the code, I pull that table, and clean it (turn it to dataframe, standardize datatypes ect)
So it looks like your logic is sound, but what we wanna do is create a new dataframe based off the current dataset we have. Looking for a way to display what I mean now
making a tweak and I'll commit it so we can compare the differences
I really like the way you built the logic for this!
thanks for throwing the merge in there too
Here I just commited, check out how I tweaked it. I kept the majority of your logic, but re-used a lot of the code we already built in
Your same branch, that way you can view em side by side
Honestly, it was a pretty simple tweak to get it working with the rest of the code
In retrospect I should change the variable name monthandyear to esignmonthand_year to be more clear
lol sorry about that, I stepped out for lunch
I'm using that .groupby(), .sum() method you came up with to add the by row revenue per lead
I'm gonna push a commit soon to a new branch if you wanna check my work? I'm a little tired this morning so I need it lol
I don't believe so but let me take a gander
Let me see if it causes any issues with my merge, if so no worries I'll manually throw it in
I will definitely be keeping that for the future though haha
Hey there, when you say dashboard, which dashboard do you mean? tip v1.3?
Good catch, I'm looking into it now. In the first section we do change it to a datetime, but where I initially thought we were just 'cropping' that value, it looks like we are turning it into a string
Heyo~ I think I found a way to make those changes to the table names like you mentioned
We can't change the names at the sources because Bigquery limitations BUT..
I was gonna say we can change the names in the scheduled queries and re-generate the middleware tables
We just have to be really careful and methodical about how we do this because if we change it at the source first, it can break scheduled queries.
Have we tested? I'm seeing conflicting info on the internet
Now we just gotta audit what tables we actually use, and work backwards lol
I made some tweaks to it I need to update you on, it looks like we are set for this meeting with Ryan in a few and I'm sure we will pick up more tasks there lol
Yeah it looks like it's Mcfadden, Brian and I but I do have a task for you- we will go over it later
Taking lunch in a few mins but I'll sync with you when I get back
Let's push back our 1:1 if possible. I ended up getting stuck on the way to lunch so I got out at like 12:45
Cool, it may be beneficial so I'll try and join but depending on how friday goes I may have to opt out.
For the project for you, remember how we talked about how we can use the bones of compare_csv.py and use it with the google cloud api to pull data from LR to get a clear, automated report of the discrepancies between the two lists?
I want to work with you on a piece that uses that to build a daily report, so you can see discrepencies daily, adjust what needs to be adjusted likely on the oldfin side, and report the results daily to ryan,mcfadden, ect
Exactly yeah- and that way you can go in and edit the leads on his end (and maybe ours) as needed to make sure they match up
Cool- This is a flatirons/dicello thing that mal is pulling us in on
Btw, I just wanted to say- You have really been kicking butt lately in not just keeping up with me and shifting projects, but actively working alongside me on the chaos
You're picking it all up quick and should feel proud'
Yes! That is a reason it would not show up
It means that it was probably set up incorrectly on REF and that column needs to be checked lol
Just added lr casetype id 2016 if you wanna fill out the info on it
That's awesome! I'm excited to see it. I'm poking around right now to determine why those 10 leads made it into LR data but not into our system
It looks like they got pulled into Billableleadsunique which is the first step in the pipeline
It has to be something in the scheduled query for billableleadswithratesand_lp
Figured it out. They were all getting flagged as test leads because their details contained the word test
I had the test filtering for most columns on because I was worried integrations would put it in weird places.
lol not quite, it's a lot simpler than that
Just a big ol scheduled query joining tables.
Does this link take you to the scheduled query?
I'll cut them down to just include what's in that screenshot I sent
I think we should be trying to move away from the scheduled reports in Lawruler, and build out those scheduled reports in GCP as of what Ward said but we can take a look!
Sorry, I'm still booting up this morning haha
uh no? here the scheduled query name I was referring to:
BillableLeadsWithRatesandleadspediadata SCHEDULED
If the data in the pipeline isn't doing what you think it should be, check that query, as it goes through each data source that is necessary for the end table
So you don't need to run it- the result of the query is run every hour and spit out as tort-intake-professionals.FinancialLogDataset.BillableLeadsWithRatesand_lp
so the query I gave you that pulls the shield legal ones specifically with the date filter
runs on the tort-intake-professionals.FinancialLogDataset.BillableLeadsWithRatesand_lp table
Gotcha. I found what I was referring to. I was asking if I still run this to get the results to compare it with dashboard:
Ahh gotcha, Yes that is still what you run lol
I just needed to edit the source table for that so it included the proper tests
Nah- Honestly I want to change the name of tort-intake-professionals.FinancialLogDataset.BillableLeadsWithRatesand_lp because it was never meant to become the defacto where everything is pulled from
but changing the name now would effect ryans pipelines too much
Hey there, won';t be able to make it to that meeting but please share any notes you get if it seems relevant. I got pulled into other things
Thank you so much!
In reference to the google sheetr doc for dougs project, I got pulled in on it, but it's primary a brittany and Mcfadden task so they know more than I do
I'm, working on figuring out why 717873 isn't showing up and it's weird..
717287 showed up after a little bit which makes me think it got caught oddly in scheduled queries
but 873 is still not, despite showing up in other things
Hey there, I moved it to 1:30 but otherwise we're good!
I love that ryan started using :fastparrot:
I think you're right. it's difficult but staying calm through the chaos makes it so much more doable
Hey there~ Let's scratch the 1:1 today but if you can, please send me an update with any progress on getting that postgresql and bigquery data into sheets
Sorry I think I misunderstood before, as long as you're okay with doing the check each day, that's totaly fine
The reason I nudged tword code is so you can become familiar with more API connections, and linking data from multiple API connections, but ultimately as long as it gets done, it's all good
By the way, I'm finally getting the PCTID into retool and wrapping my head around it
It doesn't fully work yet, I'm sorting out the bugs on it while creating it but I am moving things over to a tag / array system so when you go to enter a law firm, it will display entrees that are already there and you can just select them.
I did sign in through my shield email. I wouldn't show it to anyone else yet, but since you had such a big part of pctid_v1
i want you to see how v2 is developing lol
Cool it's under personalized Casetype id
Hey there, do you have any idea what table Ryan is talking about?
Yeah I'm looking for leadspedia contract ID's and the Lawruler campaign names so I can tie em together
Im sadly not seeing anything with the lawruler data though
Closest I'm seeing is that orders_list so far, but the names on campaigns changed so most don't work anymore
I think what we are looking for is "name"
but they never updated it to include the right lawruler names after they change them
I think you're right. Probably going to have to pull all of the leadspedia ID's from my billable leads tables, pull all the contracts from the leads, and write the leads to the contracts that way
we might miss a few contracts if they never got a billable lead but ehhh
I gave you access. Go to the Primary Casetype ID tab
Once we get the Leadspedia contract ID's in we can move everything over to this table instead of the excel one
Try editing it- It uses entrees that were already entered to give suggestions
My Billableleadswithratesandlp already has the contractids
I'm sure there's still errors floating around, see what you can find quickly (I also used some machine learning stuff to normalize the data a little further)
If people want to add tags like tv or flyer or timbukto or whatever
they can add all the tags they want as an array system
Interesting. So it looks like not every campaign actually has billable leads with a contract associated
a lot to, but not all of them which is odd.
Hell yeah it worked! A new campaign got created in Lawruler and it automagicly pulled it in!
Do what you gotta do, I'm just excited lol
I am OOO for the day, but it works out because I want you to watch something and let me know if it makes sense..
Go to Primary Casetype ID tab, and scroll down to the bottom
It's going to be a small team of people that use this, but I needed to make a tutorial for mal anyway so i figured throw it on here
More importantly though, do you feel like you could use this tool after watching it? At least to update case types as they come in
I processed data out of the excel one, cleaned it up quite a bit, and then imported it column by column\
Nah you just type it in, and select what you just added from the dropdown
I wasn't sure if that needed to be added or not, so I appreciate you asking
I appreciate the insight- I think you are right!
Thanks man, actually could not have done it without you contributing so much to the v1 on excel..
and we aren't done yet. We gotta start moving our pipelines over to read from pctidv2 instead of pctidfrom_excel
Hey there, what are the 4 leads that are not showing up?
Testing it but I /think/ I got the timezone stuff figured out
Heyo, I'll swing by to you in a few minutes
I found what may have been causing some of the bugs and I think I fixed it.
When you get back, let's run the numbers again for today and see what we find
So I was looking through the data and I realized that despite declaring the data as pacific time, it was presenting it as UTC because that is the industry standard
So we were feeding 5pm pacific time, and to keep ryans end from having to mess with timezones, we were doing the time zone call thing, which was skewing it, but still outputting UTC data.
So I modified it to save as Pacific time data, but it will still spit out as UTC unless called otherwise
Because the query "Shield Legal - Revenue Per Lead Query - Date Selectable" calls for a time zone conversion in where clause and not the select cause it pulls the right window of time
I can tweak it to include a new column called "earliestesignpacific_time"
So in your queries to count up the day- you'll still be solid using that scheduled query and column field
but know that the time is now correct, but presented in UTC
No changes needed for that post first thing in the morning as long as you use that scheduled query ( Shield Legal - Revenue Per Lead Query - Date Selectable ) to get the leads for any given time window.
That query has the time zone conversion to PST within the where statement so it makes sure that the time window it's searching for is accurate.
So in that statement, if I'm saying give me everything on May 4th PST in that where statement..
Even if it happened at 5am May 5th UTC.. It will still show up, because thats the correct window.
Make sense?
Like that call states the 4th in Pacific time
Hey there~
Not sure about the sheet, but the status table on the database has a boolian value for if a status is terminal
tort-intake-professionals.tipprodapplication.iotiplrstatusrates
Has every status in Lawruler, and if it's a 'final status' terminal should be true
Ohh- you're looking for billable status's, not the "final status" that a lead ends up in
What do you mean also updates those billable status's?
My query if you need it but essentially just knowing which ones are billable are good
Like other tables that determine if a status is billable?
Heyo~ With you moving to the shield side and going fully into flatirons, what do you think about canceling our 1:1 and making it a as needed thing? I'm still always happy to help, I just know we are both busy now haha
Lol I really did like those, those are really cool!
For sure! I used to draw with alchol markers and get the big clear packing tape and make my own stickers
I liked the select ** from feelings WHERE reason IS null
actually- was that WHERE reason = NULL or IS NULL?
I liked that one lol
If you do sell em, I think it would be SELECT ** from feelings WHERE reason IS NULL
but that's just be being really dorky about it lol
I wanted to reach out to you to see if you can reach out to Rose for this:
Hey good morning! I would be happy to reach out to Rose, but I need a comprehensive explanation of how he would like it done on the 'other revenue' page. Is Rose ONLY adding up revenue from staffing? What does he mean by " new system operating expenses and bee other revenue" ?
You're good, I let her know, and ryan sent a message about what to put on there in the chat
Smaller companies (less employees) generally have more tax forgiveness due to 'small buisness'
so move all the profit from tip to shield to bring home more money.
I'm doing the same thing but a little tweaked. I just created snapshots of the rev rates and rev rates history tables so in case I nuke it, I have backups
Honestly not really- That's what I kept trying to ask Ryan about but he kept interrupting me.
On my new system, I know that I need to go into my iolrcasetypesrevenuerates table, and update all rows to existing case types to have an iocalltimeflatfee of 0 for may going forward. The way these tables work, is iolrcasetypesrevenuerates is the "current" pricing of everything, while the tort-intake-professionals.tipprodapplication.iolrcasetypesrevenuerateshistory is the price of each item, with multiple rows that cover different time windows depending on when the lead became billable
Correction- all of the campaigns where the iocasetypecategory is ret
and in your case, I believe it's all campaigns where the iocasetypecategory is RET, and the marketing source for the campaign is also shield legal
another correction, I meant iotipservice_type not case type category
We are keeping all of the storing of talk time as is, just changing the cost from $2 flat rate to 0
Hey there' I talked to Ward and he said you needed a little extra help? I don't have access to the PG financial log but after stepping away for a few minutes, I think I understand what He's asking you to do
Note that nobody ever actually told me what campaigns specifically, just that super vague name and 'filter' so I went through every campaign with those phrases in the name and did what was NOT a secondary, and what already had pricing to indicate it was active
Ryan ended up giving the pricing. I messaged tony 3 times, and finally talked to him in person where he said he would send it
I waited an hour, got no response, told ryan and he came up with the 216
If not, we have the record of what to change/add, that's why I threw it in there
You all good going back and updating all those to 196?
Wanted to let you know Ryan gave you some big shout outs this morning on our meeting
He seemed super happy with the work you did while he was gone
Hey there, do you know anything about this new 'Outbound' Call type? It looks like Tony's team may have made a new 'call_type' in Five9 and started using it the day that Ward went on Vacation.
> Can we please make a looker enterprise account for Anthony Sobo and Christian Rodgers
Oh shoot- for some reason it never sent the slack notification
Hey there, for some reason Christian is missing a dev mode? He can edit dashboards though?
*Thread Reply:* Hey there, I am here with Ward and Carter
So far it's mostly just people who haven't read the doc, talking about things that are explained later in the doc
And Ryan wanting to now add 2 more pieces of software to be replaced (Copper and PandaDocs)
Data Set Update: • contactdob and contactcellphone from the Lawruler Contact Card has been added to the alllawrulerleadswithenrichment, and 'leads with five9' datasets
I'm confused where people are getting a billable date of yesterday? These got signed back in 2022..
(( Wait- Is it still okay to make "Hell yeah Brother" jokes since he passed away?? ))
'iscurrentlybillable' and 'billable_date'is based on a few factors.
What it looks at, is the current status of the lead, and if that current status is a TRUE for 'billable' on the iostatusrates table.
To determine the 'billable date' of the lead, it first finds the earliest instance of that lead becoming e-signed and uses that timestamp for the billable date. IF the lead is somehow billable, but never got an e-signed status (Think secondaries, or outsourced leads) it goes by the date that the earliest 'billable' status was set. IF the lead is currently in a billable status, but we have no record of when it got into that status, then the billable date becomes the lead creation timestamp in Lawruler (Very rare, but it does happen on leads that were copied from a billable lead and the previous lead was deleted)
Little complex sounding at first, but really it's pretty simple, and just has a couple of fail safes built in
Sure thing- What is all the info we have on the lead?
Potential campaigns could be:
1,842 - CPAP Settlement Package - DL - Flatirons 643 - CPAP - Flatirons CSP Medical Records - Dicello Levitt 598 - CPAP - DL - Flatirons - Shield Legal
That makes sense, I was wondering why we were looking at such an old campaign
You're all good- I can hang out a little late tonight til like 5:15ish if needed
I gotta head on out but here is that query I used with comments so it's easy to re-use
WITH status_log AS (
SELECT **
FROM `tort-intake-professionals.lr_data.lead_history_status`
WHERE DATE(`date`) BETWEEN DATE '2025-04-28' AND DATE '2025-05-04' -- Edit date range here
),
case_type_info AS (
SELECT
lawruler_lead_id,
lawruler_casetype_id
FROM `tort-intake-professionals.tip_ops_dashboards.All_LawRuler_Leads_With_Enrichment`
WHERE lawruler_casetype_id = "1879") -- Edit Campaign ID here
SELECT
s.**,
c.**
FROM status_log AS s
JOIN case_type_info AS c
ON s.leadid = c.lawruler_lead_id;
Happy to help! Lemme know if you need help finding anything else
Good morning! Sorting through the shared folders in Looker enterprise and I see we have three different folders for Flatirons. Is there any way we can consolidate all of that into a single root folder? We can still add Subfolders as needed
This covers how the structure for each primary firm should be set up, that way we can do permissions at the folder level
And here is how we set up the permissions... I'm making roles and group blueprints right now.
Let me know if you can think of anything else before I start implementing
So permissions for things like being able to explore or save a dataset is given at the role level, but you can group individual users into a 'group' for easy adding
Hey James. I wanted to pick your brain. Is it possible the "app" aka retool that mal uses can eventually have these features or do you think it is a limitation of retool in general ?
It might be a little tricky to implement, but I don't see any reason it couldn't. Depends what shes trying to bulk upload, and select multiple of
By the way, check this out-
```# Within a lookml .view file:
EXTERNAL PERMISSIONS
Raw array handle (hidden)
dimension: allfirmsarrayraw { hidden: yes sql: ${TABLE}.allfirms_array ;; }
Matches the logged-in user's client_code, OR returns 'ALL' to bypass filtering
dimension: clientcodematch { type: string group_label: "Access / Ownership" description: "Row-level access: equals the user's client code if present, or 'ALL' for override users."
sql:
{% assign cc = _user_attributes["client_code"] %}
{% if cc == "ALL" %}
'ALL'
{% else %}
(SELECT ANY_VALUE(f)
FROM UNNEST(${all_firms_array_raw}) AS f
WHERE f = {{ cc | sql_quote }})
{% endif %} ;;
}``` If I set this up right, we can make it so we can set up row level permissions on models, where if their law firm exists within all_firms, it gives them permission to see that row..
So when we set up user profiles.. we set their client_code For internal people like Marc, we set as "ALL", for the company external people, we just set the client code to whatever the company name is in PCTID
I haven't tested it yet, so I can't guarantee it works yet lol,
Are you available for me to head over there for a few minutes and test something with you??
cool, I'll head over in a few- I appreciate
Hey there, i am attempting to build a map like you have for the abuse states, but I have over 5k leads.. I remember you had a really cool summary measure thing of how many leads per zipcode, how did you set that up?
I was gonna go by zip code but anything like that yeah
Hmm dang- Yeah that's not really scalable to realtime without heavy backend work..
Even if I go for state, I'm still going to have 1 row per lead which will only give us the first 5000 leads max
How did you get the interactable map? was that googlemaps visualization?
I set it up in a way that asks for and supports selecting a broad tort type or campaign first and I think that works pretty well
Thank you- It's taken HEAVY inspo from your mormon page
Hey there- is the information that feeds into ryans 1.3 in Looker enterprise? I want to build something out to compare new and old systems
I know the actual dashboard is- for info I'm trying to find a way to reconsile between the old financial systems and newfin. I saw July closed yesterday with around 3545, but my newfin system said 3575 so I wanted to find the differences
Yeah- I want to find the data that shows the leads deemed billable in the old system\
Hey there' I need access to Ryans postgres so I can start doing data verification- Do you know how to set up that connection in Pycharm, and what table the finalized leads are?
Happy to help where I can, just to make sure I understand- you're looking for all leads that were "Signed and Declined" and went into a different status?
WITH statuslog AS (
SELECT **
FROM <a href="http://tort-intake-professionals.lr">tort-intake-professionals.lr</a>_data.lead_history_status
WHERE DATE(date) BETWEEN DATE '2024-06-10' AND DATE '2024-06-16' -- Edit date range here
),
casetypeinfo AS (
SELECT
lawrulerleadid,
lawrulercasetypeid
FROM tort-intake-professionals.tip_ops_dashboards.All_LawRuler_Leads_With_Enrichment
WHERE lawrulercasetype_id = "605"
), -- Edit Campaign ID here
enrichedleaddata AS (
SELECT
iscurrentlybillable,
lawrulerleadid
FROM tort-intake-professionals.tip_ops_dashboards.All_LawRuler_Leads_With_Enrichment
)
SELECT
s.,
c.,
en.iscurrentlybillable
FROM status_log AS s
JOIN casetypeinfo AS c ON s.leadid = c.lawrulerleadid JOIN enrichedleaddata AS en ON c.lawrulerleadid = en.lawrulerleadid --WHERE fromstatus LIKE "%DEC%" --AND WHERE iscurrentlybillable = TRUE
