https://app.fireflies.ai/view/James-Turner-On-Board-to-TIP::Q0sFIGBK8p3Sunnw
Action items
James Turner • Document the old and new system in Miro (01:27:57) • Set up time with Ryan to update AWS Lambda Generate Financial Log (01:28:48) • Set up time with Brian Hurst and Ryan Vaspra for LR Statuses Data engineering and application development (01:29:33) • Meet with the team to learn their skills and capabilities (01:44:57) • Schedule meetings with Ryan for Law Ruler status and TIP generate Financial log tasks (01:42:05) Ryan Vaspra • Onboard James to all platforms as they go through tasks (01:42:05) • Help James write the financial log (01:44:13) Unassigned • Ask Cameron about access to TIP financials for Nick Ward (01:16:46) • Investigate what users use in the TIP Financials dashboard (01:31:50)
Hey there' Working on building out a structure for pipeline documentation and had a few Qs- For the financial dashboards and apps what are all the sources used, and what is the ingestion point currently to get that info into GCP/Bitquery?
Pretty close. DBT is the modeling layer, and would come after BIgquery.
Cool, I also threw on a column for finance apps as a separate entity from finance dashboards- we can workshop and build more detail when we do the finance task 👍
Once I get access to the full Miro from Tony, I can get you a higher quality version
Hey Ryan, off the wall but I saw we had a position open for another data engineer under James Scott and a data security specialist, and I wanted to let you know about a friend of mine who might be a good fit.
She's a software/data engineer with 6 years of experience working at one of Mark Zuckerberg's startups. Truthfully she taught me a majority of what I know
@James Turner, have her send her resume to me and James Scott.
Excited to be getting all this mapped out at the 1000ft view. If you can, lemme know what I'm missing for the current financial data pipelines. Big Question marks are unknown transport methods.
Let’s start out meeting with this on Monday. I’ll want to take a look with you. Look forward to it.
Console sign-in URL
https://960735831404.signin.aws.amazon.com/console User name
jturner@shield-legal.com Console password
hUpY6Fw&
That was cool, learned a lot! Do we have a set deadline for when we need those statuses added in by?
Quint and I are going to have another meeting on Friday to review, I was hoping to test and experiment til then, and on friday have him code review before I push
@James Turner, send me the Monday.com URL for your tasks board that you guys are using...
Will do- I am having some trouble with the link to the meeting, if possible can you please send me an invite
Here is the one for integrations: https://tortintakeprofessionals.monday.com/boards/5053948403/views/112014534
Here is the one for the new team: (Still setting it up) leadership side - https://tortintakeprofessionals.monday.com/boards/7845568494/views/168707558 general tasks -https://tortintakeprofessionals.monday.com/boards/7845561739/views/168707402 (Still setting it up)
sent request to access leadership board @James Turner
Is Leadership Board yours for your TIP Analytics team?
Yes- the Strategy and operations board is for the incoming team- I am using the leadership board to keep track of responsibilities, making sure everyone is taken care of
The DevOps, tasks board is for the Integration team, I set that up originally as well
Name the Leadership Board 'TIP Analytics Leadership Board" and name General Tasks "Integrations Dev Ops" board, just so we can get them square on team and purpose.
Wasn't able to get ahold of Quint for Friday- gonna push code updates now. Please let me know if you see any issues with the new statuses added
Do you know if there was any Hipaa signed yesterday? Manually checked the reports and didn't see any on there, not sure if I need to edit that code
*Thread Reply:* I do not, the status report in LR shows that or just look up that status in LawRuler > Inbox > Filters > Status and you'll see an Inbox of those and their "Status Dates / Idle Dates".
@James Turner, lets make iotipexpense seed meeting 45 minutes tomorrow, and then lr_status one 45 mintues, make them one meeting.
Hey there, apologies- I was in a series of 1:1s
No problem, I messaged Brian T. for our seed meeting, appreciate the reschedule and consolidation. I got to hit the road, but tomorrow we can get tables seeded for both IO data needs.
and only need invites to ryan@shield-legal.com too! 😉
When you get the chance, let's chat about moving some of these tables out of BigQuery and onto designated datasets within PostgreSQL on GCP. It should save us some money and make everything a lot easier to work with moving forward. + it would all compatible with Re-tool and DBT.
With Dustin getting all of the lawruler data in PostgreSQL, it makes sense for us to have the central dataset for all of our financial data in PostgreSQL as well. That way when we want to make smaller data sets for looker or other dashboards, it's all coming from one place, with potentially simpler SQL queries. Plus we would have data backup and built in test environments
I don’t agree, BigQuery is a data warehouse system
Postgres is more handy for a true transactional system
This is a warehouse with light application. And I really wanted his GcP LR data in GCP BigQuery
Ah I wasn't aware of that- The driving thought of this was him having everything in PostgreSQL and wanting it under one roof.
That’s what Google cloud, big query, five Tran, DBT is all about.
I'll dig with him a bit about why it's PostgreSQL atm
Dustin was moving fast to get LR inbox data, he put it in Google cloud via another pipe I believe? And I’m OK with that, but I’d like it eventually migrated into Google cloud functions directly into big query to be honest
That tracks- I appreciate you breaking that down
Thanks for getting it, when we need to blend marketing data, consumer social data, etc, etc, Google will reign supreme here.
Good morning! Is there any way we can push our meeting tomorrow to 10:30? Were implementing daily standups for the new teams to keep everything on track and 10am is gonna work best for everyone. If 10am is a hard start for tomorrow for us, I can figure something else out with the team.
See inbox, send me meeting invite, lets do this fast...
Oh, the online conference was a real video game, I thought you meant the new Monday User Interface, I was like WHAT???
Yeah it was cool, you walked around and clicked on stages to get the full Livestream of the press conference
Update from yesterday:
Status is commissionable, but not billable. Lambda code reflects a non-billable status.
Hmm. We cannot count the commission if not billable. I think we’ll need to engineer another column on the status that allows us to put billable rate and case type pricing override, makes sense.
Hm, okay good to know. Would it make sense to make this a billable item? What would have to go into making something billable?
Confirm which Case Types are using this from Anthony, then we'll discuss this in our DE meeting
@James Turner Get Edward and Aidan in an office, send me Google Meet link.
@James Turner, I'm trying to verify but I think someone DELETED our PG database, give me a moment to verify
Hey there, apologies I was in a meeting away from my laptop. Currently working on a high prio issue with Connex, if it okay if we do the DBT thing another day?
I would love to hop on some of those classes if we can make that possible!
Edward is booked out on other projects from 12:30 - 2pm when he gets off. We can push out the DBT stuff, and sync it with some other Qs I have for you if that works better
Something cool to share with the team regarding current operations:
https://www.tiktok.com/t/ZTYHLT1wx/
Hey there, do you still have the notes for our meeting last thursday? I am looking to build out and seed the status_rates table properly now that I have the new team in a state of up and running
https://app.fireflies.ai/view/TIP-LawRuler-App-Data-Engineering::IpkNcp6eQd1QFX7S\
Brian and i are on, s1: from: @TIP Signed Contracts Alert from:@TIP Signed Contracts Alert before:2024-12-14 after:2024-12-12
Hopping on in a moment- Ran late helping edward with existing issue
Btw I meant to ask earlier- How are you feeling? You mentioned being sick yesterday
Better c it’s was like a two day cold. Thanks for asking.
Update on the SMTM (Show Me The Money) query project from late last week:
Blocked by a bug with Dustins Lr_data not pulling lead names properly. Already met with him and he's on that.
Otherwise making progress
Excellent, did we find those other columns to add too?
ALT ID and S1 ID don't exist natively anywhere in the lr_data database, but I came up with a workaround to get that info and I'm building tests now.
Oh, what’s the workaround? And does Dustin not have all custom field question and answers in his dataset? He should.
He should, but it looks like the ones for Alt_ID and S1 are missing. Other questions are getting in just fine. I'm gonna work with him to find out why when he gets back.
He might as well get all of those form fields. If he forgot, Brian Hirst can give you all of the IDs
Update on LRData Issues and Progress So I have some good news and a few challenges to share regarding the LRData database.
In the process of creating it, I found a MAJOR issue in the lr_data set, but I believe we can significantly reduce the timeline setback for resolving them from an estimated two months that Dustin quoted to just over a week..
Issue 1: Missing Contact Data
The Problem:
A bug in the LR_Data extraction process has resulted in no Personally Identifiable Information (PII) being pulled into the lr_data.contact table.
The Solution: I collaborated with Dustin to develop a fix, which we believe is ready to implement. Once deployed, the system will require the weekend to fully process and pull the data. Once completed, name and contact data will be successfully restored in the database.
Issue 2: Missing ALTID and S1ID
The Problem:
The LR_Data extraction was originally designed to capture changes in the activity log. Since ALT_ID and S1_ID are default questions that never change, they were inadvertently excluded from the database, along with a significant amount of other critical information.
While this is a setback at the moment, identifying the root cause is huge in preventing similar occurrences in the future.
The Solution: Extracting the default data for all question answers was initially estimated to take 40+ days, in addition to the time required for Dustin to build the necessary code. However, I am working closely with Dustin to optimize this process. We believe we can reduce the extraction timeline to just a few days.
Summary: Overall, Despite hitting the roadblocks, these fixes we have in mind will not only address the current issues but also improve the overall stability and reliability of the LR_Data in the future, and if everything works as Dustin believes, we should have this table ready by mid-next week.
@James Turner , thank you for the detailed update. I’ll see you tomorrow at the office. Pumped.
Hey! I saw the 4 years of Shield today! Congrats~
Thanks! I technically started when we formed it in April 2019 but was just 1099.
Hey there- do you have a open half hour any time today? I wanted to talk to you about something in person while we had the chance.
Was this might of been meant for the other James? By the way, when you get the chance please send me a list of all the tables you want the query updated on.
Got us a room just in case your office isn't available. The big glass room by the entrance. (only one available)a
Seeded that status table and brought it into looker board of LR status's. I believe it's working, but I am having permission issues sending it to ward and the like because they don't have access on Bigquery
Share it to Google Hsset from BigQuery, share sheet
@James Turner, able to jion that call from Edward?
Hey there- I apologize, I wasn't feeling well so when I realized we weren't doing the previous meeting I headed out to quickcare.
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 and set it as that data type, 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!
The PK_ID is a completely new primary key for the application table. Only, it’ll keep record of all new entries, just make it an interval but not a PK restraint. Helps later in DBT.
And yes, later is fine once on 2.
Thanks for the clarification!
Got it on the externalstatusname_desc~
For the pk_id, it looks like Bigquery doesn't natively support INTERVAL as a data type-
Fortunately if we are looking for a way to track when changes are made to a row and how long it's been since the last update, we can use a "Timestamp" data type and change the column name to "last updated"
Let me know if there’s anything else to adjust!
Hey there, do you have 20 minutes this morning?
Worked late last night and figured out how we can solve all of our case name issues in a way that will work for everyone. Already got approval from the Data teams, David representing 5nine and Tony!
Today, maybe later. If not, we can squeeze it in the morning. I can’t wait to hear about it.
Hey there,
For the new table for the new case type method, I need to insert retainercode, clientfirm, partnerfirms, service, billing source, and billingid on oldname or lawrulerid
What would be the best table for me to grab all of that info so I can make sure this is all good to go for you before we change all the case names?
That is the production PostGresDB table but you can make a copy of it in DBT, practice the query in BigQuery, quick and easy. The change SQL to postgres and run it on production table. Want to cover that in Monday Sprint Priority meeting?
I should be able to manage it, I just need the seed data so I can set up my table on Bigquery but I don't believe I have access to the that production PostgresDB table
And make sure that translation table is ready before you do it. That will be KEY.
For sure- gonna fill in all this info, give it the PCID# and get everything else finished for that table before we make any changes in LR
The goal is to do it all in one big push so we don't have to pay lawruler to change things multiple times.
It is gonna fall on the various teams/individuals to make sure they get all their name based automations moved over themselves as strategy and ops doesn't have the bandwidth given the timeframe but were framing this as a "pro" so we can get an audit of what reports we actually use vs what is bogging down LR for no reason
They should be automating off LR_ID which shouldn't change anyway
Let me know so I can check the ones I use too JIC, the lambda will need the Cast Tyoe parsing updates too
So it may parse case type, parent case type and client will still work possibly.
It’s designed to look for the four hyphens to create parent case type, case type, retainer code on the first, second-the delivery firm the third-is the partner firm and the fourth-is the billing source
Gotcha, so that would need to be changed to look for the PCTID# and refer to the table.
Once I get everything ready for that reference table, you'll be the first to know!
If possible, can you set up a view of that prod postgresql table in bigquery under the tort-intake-professionals? I don't have access to that PostgreSQL table directly, so a view would allow me to pull data from it, but not change anything on it
Awesome! I wasn't sure if that was up to date. Thank you much! I'm excited to be fixing one of the biggest issues that's been plaguing us for so long
Apologies for the off hours message- I just want to confirm a few details.. From the financial_log table, mapped to how you want the new case names here
Does: lpcontractid = billing id ? and casetyperetainer_code ?
Use case type retainer code and don’t touch the Lp fields.
On the table update leave, to be specific:
lpcontractid lpcontractname lpleads1
@James Turner, can you share the Shield + TIP lead workflow diagram to me in PNG real fast, need it for Tony and Investor
Apologies, I was on the floor in office giving a presentation on something so I had my phone/slack away. Would you still like me to send that over? I can also present on it and answer Q's if needed
In the finance logs, does it show every status something has been, or only current status?
Working on something I think you're gonna like
rawstatusreport table does, financiallog only shows what status it first appears in the LR Status Report that in put into the raw table once the Lambda runs at 2:30 AM. But as you know Edward and I update finlog table with DECLINE and Billable DECLINE statuses manually and it sucks.
How is LR Lead data and the TIP io app tables coming, those are URGENT to be completed.
All of my focus has been on the flatiron stuff, case name ID changes, associated dashboards, and making sure things are moving with five9 since those were communicated as priority previously on the TIP side
Properly splitting and cleaning data so we can move forward without having to re-do later has been a task that has taken longer than expected, but it's already showing it's benefit TIP side, with a lot of future benefits!
Overall we have been making really good progress on that, and once we have that foundation, getting everything moving on the IO tables are going to be much smoother.
If we can, please write up a exact requirements for those tables like we did with the status tables that went super well. That way I can make sure we get it right the first time
Dashboard for PCIF looks great. But needs to be in looker enterprise.
Both are good to use now, so studio was smart first move. Nice work.
We should seto DBT model and get that built by Edward so he can learn Lokkwr
The PCTID looker dashboard is just select data off a database data set still in construction.. It's set up in studio so agents on the floor can use it to look up info on cases while they take calls. a goal for that is to put links to case qualifications and other required information in there so it's a one stop shop for agents. As we move into billing and other boards we can use enterprise for sure.
The reason I asked about the status stuff is because revamping the naming conventions sets us up for a lead tracker that will hopefully blow you away!
I'm building the foundation for it as a proof of concept with the finance data as a source of lead status truth.
Here check this out, click one of the latest cases (It's still very much under construction)
https://lookerstudio.google.com/reporting/7d5bd7b5-fc56-4517-8478-3e06b80d5fd1
I'm excited to see what we achieve with these next steps on the fin data and the HTFU project! Thanks for including me and my team.
By the way, I wanted to ask what the Paylocity for TIP staff item was? I have been leading that team like we talked about awhile back, and it's been going amazing but haven't seen anything regarding the title/offer change or paylocity handover for the new role taking an official lead status
@James Turner Cameron accepted the invite to the workflow, but I’m traveling and won’t make it. Let’s just get his feedback update this flow, and then we can review together with Joe and go over the object model we did this week. Thanks again for all your great help and ideas.
Hey there' I am looking to expand that dashboard I showed you to include a lot more data by direct request of Cam. I forsee looker studio struggling with the load and want to attempt to build it in looker enterprise. Do you know if I have access to enterprise on my account?
We can setup a Looker session for you and Edward.
If you can, please send me access to enterprise. I want to get some hands on experience playing with it
TIP and SL are in the same instance, I need to show you the model files, how to import a view, how to update the view with custom metrics, refinements so leave the "Admin" section alone until you train with me as to not break something in the LookML code.
Hey James, forgive my outburst. I just got way over stimulated while trying to brain dump. My apologies.
You're absolutely golden! I wanted to go off and rant with you because I agree, but I'm in office near the same people that pull me all over so I held my tongue.
TIP rules state we need to be in office but Honestly, I envy the James Scott's and Brian's that can work remote because people like us can lock in and so much more REAL work done!
Understand, I would request and office maybe with Nick Ward?
I do share the office with Ward currently and it does help a lot, but you know how it goes when you're in office
Yes. I guess then, maybe you should negotiate a three hour quiet lock per day because I think it’s quite normal for the mornings to be less hectic, is that true?
You, and your team. Slack off, phones muted. Watches muted. I counted recently how much interruption I get by everything. It’s bad…. lol
Please send me the firefly from that meeting
Hey there' What is the raw "source of truth" table for Leadspedia? I am looking for a table where they have Leadspedia Lead ID, and Leadspedia Contract ID in the same table and it has not been edited or transformed at all. Ideally a direct rip from Leadspedia
That’s exactly what Nick McFadden is working on, we have all leads, but we don’t have the contract ID yet. Ping him in the shield analytics channel and you can tag me.
James, Cam question now. Let me know.
I have a dashboard set up where you can pull all of the questions and answers for each lead, and splice it by tort type, client firms, case type, lawruler ID, and many more- and include as many or few questions that you want for each one.
Send to Cameron and we can have a 15 minute show and tell.
Sure thing. Sent- we good for right now?
Yes, I’ll tell him and see when he wants a tutorial
What about the flow chart updates they gave you, Miro updated?
During one of our syncs, I brought up that I needed to focus on that flowchart project and was instructed by you to hold on that project until you talked to Cam about the scope of the CRM. Then you said you would let me know what he said, and you instructed me to focus on the finance log / io tables for now because that took priority.
I would be happy to start it up again tomorrow, and hop on a call with you both so we can sort out priorities and deadlines for each item?
, He was looking for your dashboard, I realized, he’s really difficult at explaining his request
But we should have that dashboard updated before we send it to the vendors. Could we get it updated before EOW?
Let's all sync and do a demo to display where were at with it, and set scale on exactly what updates we are looking for so I can give a solid timeline.
At the moment this dashboard is built exclusively for internal use, but it's currently being updated about every hour with all leads, questions, and answers and has already made a big impact in allowing us to quickly generate reports for DL & Flatirons,
When you can, me know what time/day you and cam want the demo so I can get something prepped
Cameron has an asked for it yet, I just gave him a link and I’ll ask him later if he needs one
[ Not time sensitive ]
I used a private AI server and wrote a massive 6000+ character prompt to get a realistic analysis of timeline / cost / maintained cost..
I had it review available historical records of cost and timelines on existing projects made by other companies, fed it our project requirements, and had it write up realistic analysis. Let me know if you're interested in taking a look!
For the financial logs, do you want every sold lead that is pulled from Leadspedia, or just what has populated in Lawruler? There is 855,091 unique leads that we got from leadspedia, and only 654,067 leads in Lawruler.
My suggestion would be to keep the higher number and just have leads not have case data.
Cool- Of those 654k in lawruler, only about 504k actually have the description field populated correctly so the contracts come in. It looks like it's mostly the really old leads with the issue, so it should be fine, but I wanted to make sure you know!
Thank you, the other leads and Leeds PD are going to be unsold leads due to spam filters, duplicates, and other things like that
@James Turner can you send me the Miro board of the new flow with LeadComplete
You can come to me and Andy's office for our TIP meeting
Oh cool! I'll swing on by in a few minutes! I updated the flow chart on Monday so you have it there, and you have access to the Miro board as well!
Should I hop out or am I okay to keep grinding this HTFU flow out here??
Includes current and proposed processes with LeadComplete
Can you grab a conference room for the tech meeting?
Hey there' I have some news on a big success to help with your S1 issues! Give me a call when you can~
Apologies for the late message, but any way you can make a change to something on the io rev rates table with retool? I want to test what I build it but don't wanna mess anything up
Cool, it worked! Set it up with a valid from timestamp, as well as a change timestamp so when we refer to it later, we just look for the row with the latest valid_from value. Gonna dig in on how to automate all that later but this is good progress
Is there any way I can get the GPT Plus? Would be helpful for fact checking on these next steps of finlog\
4197460006979356
05/28 831
Cameron Rentch
5170 Badura Avenue
Las Vegas NV 89118
Far from done and I haven't incorporated status yet because that still needs to be done on re-tool but check this out:
When you still null values on this, it's likely because there are still a LOT of null values on the iocasetypesrevrates table
Saw you were in a meeting so I hung around for 15 minutes before leaving just in case-
Made some great progress, I would be happy to show you if you're available!
I’m on call, my apologies. I thought it was at 1:30. I’m on the go. I got time now.
I can make it quick, just call whenever you get a minute 👍
Found an issue with the data I presented earlier, Gonna dig on it and make sure I can get that fully resolved before sending anything out.
Fact checking, do we want to pull billing information from the moment the lead is created, or the moment the status changes from one thing to another? Moment of creation would be easier, since that remains static but I want to make sure
We want to pull the pricing when it converts into a billable status as dictated by the Io case types, rev rates, table, data values
Not gonna bore you with the details but I found a problem where the data would be accurate now, but would fail over time and it would be super expensive to run. Spent hours last night and all morning re-writing and re-optimizing the entire flow so it will work, stay working, and not be expensive despite being in BQ
SELECT ** FROM tort-intake-professionals.Financial_Log_Dataset.pctid_x_data_with_billable_status_and_rev_rates_inital
ORDER BY lead_id DESC
Feel free to check it out- I can get into the nitty gritty of technical but I wanna keep it young presidents format for the meeting
@James Scott, when will this table be autorefreshed and explained to me, Zek, James, Nick and Brian how to get a matching set of "Signed e-Signs from Shield" for "yesterday" in this new data?
I have questions about the schema / fields specific to statuses, etc. Not even sure my SQL is correct on the filter clauses.
SELECT **
FROM tort-intake-professionals.Financial_Log_Dataset.pctid_x_data_with_billable_status_and_rev_rates
WHERE status LIKE '%Signed e-Sign%'
AND marketing_source LIKE '%Shield Legal%'
AND DATE(status_change_time) = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
ORDER BY lead_id DESC;
Hey there, was this meant for James Scott, or me (James Turner)?
If for me, It's already on a schedule to update about every hour, and a unique list of leads where the status is currently OR ever was "Signed e-sign" is relatively simple.
I'm working on the call time request now, but can help with that as well, just need to know priority
Hey there, do you have a minute to chat on the call stuff? Rose is stressing because the numbers are weird and she doesn't know where any of them are coming from. I can give my numbers on talk time for leads that went signed-esign from Five9 and I feel confident on those, but the rest of these numbers aren't making any sense.
After speaking with Edward and Aiden, and doing some measurements of my own I believe we may have been missing a good amount on the talk time count even taking into consideration we only charge for leads that went final and use a billable status within RET. Setting up the meeting now where I'll break it down further to explain what I'm seeing
I've spoken with her to pause until after the meeting today that I just put on the calendar. I did want to ask, Febuary was at 90k talk time minutes Friday night, and now it's at over 150k. I spoke to Edward, Aiden and Zeke and nobody claims to have edited anything- Did you update on your end?
February from our conversation last week was supposed to be at 150k from what you told me, so you had Edward and Aiden update spend to get it to match Five9 is what I understand
I added talk time minutes to DataStudio Profit x Month but it's on the Invoices by Month: https://lookerstudio.google.com/reporting/a6a0f187-1619-478c-b1c9-6412a9e769fd/page/p_vojobl6ytc
I'm still seeing some weirdness, but I'm happy to break it down futher in that meeting. Overall, it's good for TIP and shield as long as we can play it right with clients
Hey you still around? Would love to show you something in person
I’m on the plane, I land at six. I can do tomorrow.
I’ll ping you in the morning after a couple meetings
No worries, It's just something I threw together to help monitor the accuracy of the new system
Even made a fancy new icon to show the move over to a new financial system
That slick, we need to get that into Looker enterprise and build a diagnostic section on both sides of the companies
Not sure if you're still around, but if you are, check this out- BillableLeadsWithRatesandleadspediadata
SELECT **
FROM `tort-intake-professionals.Financial_Log_Dataset.Billable_Leads_With_Rates_and_lp`
ORDER BY DATE(earliest_esign) DESC
LIMIT 1000
Even shows the time that it took from the createdate of the lead out of leadspedia, to the moment it gets e-signed for the first time
Same CC info as last time or a different one?
James, we been validated on new e-signs for SL compared to PG e-signs for SL last several days?
Yes! NewFin is looking good! Only discrepancies we have seen is when secondary campaigns make it into the "Yesterday E-Signs" as shown in past updates
This place is cool! Our friends from Fivetran say hello
Very cool, I’m glad you guys are having an informative event
Just went to a Data engineer meetup where we talked about current issues we are facing and talk about it as a group.
I brought up attempting to predict staffing needs for dynamically shifting workloads (never confidently knowing if a campaign is going to pop off or not) and came up with some pretty good ideas.
We build staffing for incoming calls off of a standard like we talked about, but if we find that we get hard spikes that we would need the 100 or whatever agents, that doesn't make sense to staff around full time... We supplement that with a 'charge by usage' set of specially trained AI agents. That way we don't miss a call, we still have a fully human staff as needed, and since a bunch of incoming calls end up getting transferred anyway, the time with an AI agent is limited.
I'm going to a training with Salesforce later where we build specialized AI agents so I can get my feet under me just how possible this is..
These systems are supposed to outperform all humans even at intake. With great power comes great responsibility of you get my Star Wars reference. So keep it tight on purpose or the human agents may panic.
But this is something we should absolutely have.
I definitely get your reference! Right now it looks like the best AI agents are all tied to Salesforce, but research is why I'm here! I'll keep digging!
Ok. Ask Google Cloud representatives for product on GCP that performs well, Salesforce competes with Google to some degree
Need clarification on profit / loss monthly expenses table I'm working on - Do you have a minute for a quick call?
Hey there, Sorry about not giving you a ring- You got me right as I went to lunch. Enjoy your time off!
Hey there, I believe I understand. Currently I'm building a table that adds up all lead revenue vs expenses so I needed to make sure that all of those columns would be added up as revenue
Hey, let’s look at just your schema on your added marketing_source and then we can talk about what from there is turned into a by day by service type financial roll up. If it takes longer than 15 in our meeting today, let’s setup one last billable leads to financials requirements meeting.
That way we don’t forget a dimension in a roll up table.
Take a look at the schema on what table? marketing_source is a column on a few of my tables. Right now i'm doing the sum of expenses from monthly expenses io, doing the total billable lead count per month, and adding up all revenue per ret lead per month
Take a look at the bottom, that's most recent
Sorry about that, I was away from keyboard in another meeting. What other item should we add?
Sorry about that, I was away from keyboard in another meeting. What other item should we add?
threw those crystal ball numbers together and they are bigger than i expected. Shooting it your way now
Summarized it quite a bit and sent it over to you, Cam, Tony, and Ward so you can each get a view on it.
Good morning, in your new billableleads table, can you clean up this records leadid field? You'll see the case type name is accidentally in there with a carriage return:
SELECT ** FROM `tort-intake-professionals.Financial_Log_Dataset.Billable_Leads_With_Rates_and_lp`
WHERE marketing_source = 'Shield Legal'
AND lawruler_lead_id = 323751 --Contract ID in LP is 1737 - Crump / Simmons Bard, not Lanier....
ORDER BY earliest_esign DESC
LIMIT 1000 -- Adjust as needed
This is true in a few records in that table BTW, might be older ones?
149377 646640
@James Turner, plus some of the leadspedialeadid values say "this is test"
--SEE Leads that are test leads
SELECT leadspedia_lead_id, lawruler_lead_id
FROM `shield-legal-bi.tip_bigquery_shield_datastore_tip_external.shield_legal_datastore`
WHERE leadspedia_lead_id LIKE '%est%'
ORDER BY earliest_esign DESC LIMIT 5000
So, data stores will need a clause to check for "test" in first name, last name, and other fields to ensure we don't send garbage data to Dicello, etc, in near future.
For the carriage return + Leadspedia ID issue, • This issue happens when someone is manually entering that data into the case description field on Lawruler. • The LeadspediaLeadId field is just the lawruler Case Description field taken from LR_Data. • The correction needs to be done agent side on Lawruler, and the corrections would be brought into the table during the next scheduled query. For the 'Test Leads' • I can build a filter that cuts out anything with %test% in the first or last name + leadspedia data. • it is important to note that integrations has been instructed by some firms to NOT use the word test, in their test leads, so it may not catch all of them.
For the carriage return + Leadspedia ID issue, • This issue happens when someone is manually entering that data into the case description field on Lawruler. • The LeadspediaLeadId field is just the lawruler Case Description field taken from LR_Data. • The correction needs to be done agent side on Lawruler, and the corrections would be brought into the table during the next scheduled query. For the 'Test Leads' • I can build a filter that cuts out anything with %test% in the first or last name + leadspedia data. • it is important to note that integrations has been instructed by some firms to NOT use the word test, in their test leads, so it may not catch all of them.
If you can put these filters on the existing table?
Great timing! Just finished putting the Test filters on that table!
Perfect. I have everything done and ready for show for Shield too.
Heck yeah- It feels good to see this all coming together!
Minor tweak incoming- Filtering out tests also filters rows with null values.
Minor tweak incoming- Filtering out tests also filters rows with null values.
Thank you, I’ll be on the call probably five after but just have idle chitchat, I’ll share my screen and go through a quick show and tell and we’ll save about 45 minutes
You're good- Would it work better if we rescheduled? I'm double booked anyway
This won’t take 15 minutes for me to show you guys, let’s keep it since there’s enough people on it to me the 15 minutes. Hopefully that reduction in time let you manager double book.
I'm building out the code to get that full financial view we discussed and have a few questions.
To ensure my questions make sense, I have provided a screenshot of the current status of the full financial view as of now, and an explanation of how a few of the columns work:
• sumofexpenses is a sum total of everything on monthly expenses table, by month. • revenueperlead is a sum total of the iocasetypebillable rate, and talktimerevenueinUSD, per lead. divided by totalbillableleadcount • profitperlead is costperlead - revenueper_lead With all of that in mind, here are my questions:
- What should we do about agent commission?
a. Currently there is nothing in our system that correctly tracks agent commission.
b. We can build out a system, but it's complex because of spiffs, and is handled entirely by Anthony's team and Tony in Excel.
c. Getting a true look at agent commission has been attempted to achieve before, but kept getting pushed back for other projects. - How should we handle / divide monthlyexpenses? a. I imagine that Shield and TIP share monthly expenses, but do not know the split, so this is the entirety of the iomonthly_expenses table.
- What other columns / fields should I be adding? Please provide a detailed description of how that column should work.
- What other modifications need to be done for this to reflect accurately? a. Please provide modifications written with details so I can ensure they are included and not forgotten.
@James Turner , some I will answer verbally. The ones that need detail I’ll write.
• sumofexpenses is a sum total of everything on monthly expenses table, by month. ◦ RKV: Correct, and those plus agentcommission = Total TIP Expense • revenueperlead is a sum total of the iocasetypebillable rate, and talktimerevenueinUSD, per lead. divided by totalbillableleadcount ◦ RKV: Correct • profitperlead is costperlead - revenueperlead ◦ RKV: Correct, and this formula is same for Gross Revenue - Total Expense = Net Profit With all of that in mind, here are my questions:
- What should we do about agent commission? a. Currently there is nothing in our system that correctly tracks agent commission. RKV: There should be, we have ioagentcommissionrate & ioagentoveridecommissionrate in the tort-intake-professionals.FinancialLogDataset.BillableLeadsWithRatesandlp table b. We can build out a system, but it's complex because of spiffs, and is handled entirely by Anthony's team and Tony in Excel. c. Getting a true look at agent commission has been attempted to achieve before, but kept getting pushed back for other projects.
- How should we handle / divide monthlyexpenses? a. I imagine that Shield and TIP share monthly expenses, but do not know the split, so this is the entirety of the iomonthly_expenses table. i. RKV: All TIP CRQ, RET and POST service types billable leads for one month would "share" their portion of the monthly expenses.
- What other columns / fields should I be adding? Please provide a detailed description of how that column should work. a. RKV: Just the gross_revenue, total expense, net profit columns plus agent (LawRuler Current Assignee) or any other field stat shows "who" worked on it.
- What other modifications need to be done for this to reflect accurately? a. Please provide modifications written with details so I can ensure they are included and not forgotten.
Hey there- Good morning! Busy day on my end with a few back to back things but I wanted to thank you for the clarification and provide some insight for where we may hit roadblocks.
For commission -
• Despite having those fields in the database, commission changes constantly and on the fly from Tony.
• The only place that commission is being accurately tracked is by the agents themselves and every 2 weeks when somebody from Sobos team runs commission with all the spiffs and bonus's in google sheets.
• We can bring that data in every pay period, but it's important to know that the data on the database as of now is not currently a reliable way to see commission.
• Ideally I would like to allocate time to work with Sobos team to build a system to accurately track commission.
For "How should we handle / divide monthlyexpenses?"
• Currently we just have Ret billable leads because all of our systems have been designed and measured against shield dashboards that use e-signs to identify a billable lead.
• We can design a system that pulls in all billable leads, but we may run into issues with duplicates because of system around a whole new lead ID for the same individual, just for the secondary interview process. This is likely able to be resolved, but we need to take the time to ensure that works out.
For this section: What other columns / fields should I be adding? Please provide a detailed description of how that column should work.
You said: RKV: Just the grossrevenue, total expense, net profit columns plus agent (LawRuler Current Assignee) or any other field stat shows "who" worked on it.
• In the photo provided yesterday, I sent over a layout of what I am building and it shows a system of all totals summed by month. There is no way to add individual agent when we are summing all of the totals.
• Are you refering to adding individual agents to the billableleadswithratesand_lp table??
I am off to other meetings for Dicello/Flatirons stuff but please let me know what you think and if we can allocate the time to build the commission system with Sobos team to make sure our numbers are accurate.
I researched the data lineage service on GCP and I think it would be really helpful to get everything ready for analysts and other engineers like we talked about,
it does have a cost based on our bigquery usage which you can more about here: https://cloud.google.com/dataplex/pricing
Is it alright if we enable it for a month to try it? If it turns out to not be helpful or too pricy, we can turn it off at any point.
It will give us a full view of how data is transformed before and after going to DBT and other sources. DBT uses the same thing
In theory it should be able to show the DBT transformations after the data comes back into Bigquery too, but I haven't had a chance to test that so I can't guarantee.
In theory it should be able to show the DBT transformations after the data comes back into Bigquery too, but I haven't had a chance to test that so I can't guarantee.
It will give us a full view of how data is transformed before and after going to DBT and other sources. DBT uses the same thing
Haven't tested it myself, but from what they advertise, Yeah.
When you say history, are you looking for a record of all the changes made, or a separate "history" table for each row like the iorevrates_history? It keeps that record of transformations, but I don't believe it makes a full historical copy of each item it touches.
When you say history, are you looking for a record of all the changes made, or a separate "history" table for each row like the iorevrates_history? It keeps that record of transformations, but I don't believe it makes a full historical copy of each item it touches.
Ideally we just use it until we get full documentation set up for all the GCP pipelines, and then lean on DBT after
Unrelated - For the commission item we discussed:
Short term solution to commission issue is set up!
• I was able to get the last 15 months of agent commission totals from HR and Payroll, and implemented them as column " aggagentcommission" in iotipmonthly_expenses. • I had Brian add an "Agent Commission" field on Lawruler Operating Expenses Retool, so when Rose does that every month, she can add the total commission rate. • Agent Commission aggregate for the last 15 months has been seeded!
Excellent! The. We should clear it out on io lr case type rev rates yeah ? And have Brian remove it from the portal UI
Cleared it from iolrcasetyperev_rates, as well as the history table. Got Brian on the task of removing them from the retool as well.
(ioagentoverideactive, ioagentcommissionrate, ioagentoveridecommissionrate)
If you have any scheduled queries that feed those, please let me know so they don't break.
Check the one scheduled query that does the insert and updates but it’s setting it to zero I think.
Things to do following 4/29/2025 Sprint Meeting:
Brian w/ Retool:
• Brian (With help from James as needed.) - Remove Commission Schedule tab from 'Status Rates' tab on Lawruler_Case_Type_IDs and Lawruler_Case_Type_Names from <a href="http://tort-intake-professionals.lr">tort-intake-professionals.lr</a>_<a href="http://case_types.lr">case_types.lr</a>_case_types .
◦ James - Add Leadspedia_Contract_IDs to the table as a new column in the schema with the data type of "ARRAY"
▪︎ ARRAY Format. Ex: [ 177, 188, 940 ] .
◦ James - Seed existing Data from tort-intake-professionals.pctid_data.pctid_data_from_excel .
◦ James (With help from Brian as needed) - Create a Retool tab for PCTID 2.0 on 'TIP Operations Application' .
◦ James (With help from Edward & Mal as available) - Determine how to seed Leadspedia_Contract_IDsarrays.
◦ James - Train Mal to use the PCTID 2.0 Retool and update it whenever a new LawRuler Case type is created.
◦ Mal - After training, Mal will own updating that Retool.
Leadspedia Data for Billable Leads pipeline
• James - Update Zeke, McFadden, Sobo, and Louis on leadspedia_lead_id processes.
• James - Create a new system that identifies Lawruler_Lead_ID's that do not have a leadspedia_lead_id and update's relevant tables to have the leadspediacontractid as the minimum value withinLeadspedia_Contract_IDs of the same Lawruler_Case_Type_IDs in PCTID 2.0.
• Important Note - Following this update, Lawruler Leads with no known Leadspedia source, will have Leadspedia_contract_ID data.
• Important Note - Following this update, Lawruler Leads with no known Leadspedia source, will NOT have values for fields such as S1, and Leadspedia_Lead_ID That is fine and approved by Ryan.
Things to do following 4/29/2025 Sprint Meeting:
Brian w/ Retool:
• Brian (With help from James as needed.) - Remove Commission Schedule tab from 'Status Rates' tab on Lawruler_Case_Type_IDs and Lawruler_Case_Type_Names from <a href="http://tort-intake-professionals.lr">tort-intake-professionals.lr</a>_<a href="http://case_types.lr">case_types.lr</a>_case_types .
◦ James - Add Leadspedia_Contract_IDs to the table as a new column in the schema with the data type of "ARRAY"
▪︎ ARRAY Format. Ex: [ 177, 188, 940 ] .
◦ James - Seed existing Data from tort-intake-professionals.pctid_data.pctid_data_from_excel .
◦ James (With help from Brian as needed) - Create a Retool tab for PCTID 2.0 on 'TIP Operations Application' .
◦ James (With help from Edward & Mal as available) - Determine how to seed Leadspedia_Contract_IDsarrays.
◦ James - Train Mal to use the PCTID 2.0 Retool and update it whenever a new LawRuler Case type is created.
◦ Mal - After training, Mal will own updating that Retool.
Leadspedia Data for Billable Leads pipeline
• James - Update Zeke, McFadden, Sobo, and Louis on leadspedia_lead_id processes.
• James - Create a new system that identifies Lawruler_Lead_ID's that do not have a leadspedia_lead_id and update's relevant tables to have the leadspediacontractid as the minimum value withinLeadspedia_Contract_IDs of the same Lawruler_Case_Type_IDs in PCTID 2.0.
• Important Note - Following this update, Lawruler Leads with no known Leadspedia source, will have Leadspedia_contract_ID data.
• Important Note - Following this update, Lawruler Leads with no known Leadspedia source, will NOT have values for fields such as S1, and Leadspedia_Lead_ID That is fine and approved by Ryan.
@Nicholas McFadden see details. We will assign S1s if not traced from TIP that we insert into our sold leads financials table model
Check out the logistics chat. Tagged everyone there with their relevant tasks and who needs to know
PCTID v2 is nearly ready to launch on Retool, but we still need to seed the values with Leadspedia Contract IDs. I do not have access to Leadspedia so If you can, please extract a report with all of the leadspedia contract ID's per Lawruler case type ID. Once we get those values, I can seed it through and we will be ready to move onto phase two, which is making sure every lead in lawruler has a proper leadspedia contract ID
PCTID v2 is nearly ready to launch on Retool, but we still need to seed the values with Leadspedia Contract IDs. I do not have access to Leadspedia so If you can, please extract a report with all of the leadspedia contract ID's per Lawruler case type ID. Once we get those values, I can seed it through and we will be ready to move onto phase two, which is making sure every lead in lawruler has a proper leadspedia contract ID
Our SL BigQuery PVS table has them perfect from Malissa and Mark
PVS table? Do you by any chance know the directory for it? I'm not as familiar with the SL dataset
I built it and it’s accurate, and Edward uses a Flatirons WHERE clause from DBT
Looks like we have a bunch of tables under qs_sl_dashboard and sl_dashboard but none of the columns match 1:1 what is on lawruler so we can't seed using it reliably.
I spoke to mark and he said that the leadspedia Contract ID is tied by the exact LawRuler casetype/name and is entered in the 'Leadspedia delivery method parameters' by going to Settings -> Integrations -> Lead Integrations within leadspedia.
Is there anywhere where it is tied to the Lawruler Case Type ID instead? The names of campaigns have changed so many times, Mal will have to assign contract IDS for 897 Lawruler campaigns by hand if we can't find an actual match anywhere in the database.
You would use the PVS table to extract Leeds, Pia contract ideas and match them to your PCT ID Leeds Pia contract IDs
I put a field called "campaign tags' so If you or Cam ever want to add a specific 'tag' to track a case all the way through, you can do so without ever having to add it to the case type name
and you can add as many tags as you want
Hey there, I know it's late friday evening for you right now but I wanted to shoot an update your way that were ahead of schedule on the sprint items as of today
Ward sent me to work remote today so I didn't get sucked into the open house chaos and something about working out of a coffee shop hit just right! I got as much done today as would normally take me 3-4 days lol
I sent the full updates in the logistics chat, but I got the pctidv2 dashboard good to go, with contracts seeded, and implemented the code changes to make sure all leads will have a contractid like we talked about
In your pipelines that you have built out from BillableLeadsWithRatesand_lp, do you do any time zone management? What time zone do you expect that data to come in as?
I believe I found the timezone bug but before I start working on fixes, I want to make sure I'm not pushing a problem downstream.
Ask Nick and Zeke based upon their leadspedia data, not sure if in use yet. Add me to DM with them. I know LP UI is always EST
That bit about LP UI being EST is good to know. We should double check to see if that's being converted to UTC as it's brought in.
Check GCP leadspedia data that has several tables. Easy to locate.
Just updated this dashboard to work with the new systems -
Threw a "all information" page on there so you can see all leads and ID if there are any additional fields we need before moving forward. https://lookerstudio.google.com/u/0/reporting/951a17ae-df4e-4965-8567-b3123015a5d3/page/p_21sh3qs8rd
@James Turner , call my cell real quick, got update on Paylocity.
When we are splitting Agent Commissions out of other payroll expenses and updating that via retool- Do you want us to make those changes retroactively as far back as we have data available, or start at X month moving forward??
Updating you on the 6 campaigns added into Lawruler overnight (pictured) in case nobody else has.
From what I have been told, these are Non-Billable "Favor" campaigns, and they should not be considered when we run billing.
To accommodate for that, I have added in a new campaign tag called "Non-Billable" that we can track (using the tag feature I showed off yesterday.) and I'm planning out the tweaks to the pipeline.
Hey check this out:
Other Revenue dashboard- Play with it, and lemme know if you see any tweaks you want
Hey check this out:
Other Revenue dashboard- Play with it, and lemme know if you see any tweaks you want
🚩Quick Flag:
Looker Studio/Enterprise doesn’t handle array fields well for filters or dropdowns (e.g. partner firms, LP contracts, campaign tags). Even though BQ encourages arrays, Looker doesn’t play nice with them — known Google inconsistency. Not breaking anything right now, but something to keep in mind moving forward. I’m working on a workaround (possibly via Retool). Will keep you in the loop if this turns into a larger blocker.
All good for now. ✅
What's your GitHub handle, adding you to Codeowners Teams on SL-BI and all branches so you and Nick can approve them in GitHub.
I see the two queries that failed but there's no comments saying what they do, or where they pull from/go to?
I want to confirm, for dbtlrcasetypesrevenuerates are these supposed to be pulling from tort-intake-professionals.tipprodapplication.iolrcasetypesrevenuerates??
We changed the columns on there weeks ago when we found out commission wasn't set up right. Do you want all the columns from that table?
Pushed the pull request through, and requested @Nicholas McFadden to check on Fivetran since I do not have access to that.
What is the destination of those tables so I can make sure they updated properly?
Cool, and how frequent is the refresh rate?
Check Jobs for staging and prod in DBT, 15 I think. Maybe 5.
Hey there, any way we can push the Sprint meeting to Tuesday? No major updates on my end due to
Hey good morning! I wanted to give you a heads up before our meeting later so you're ready and up to date as to why we called it.
I've been hitting roadblock after roadblock trying to build a commission system because of the call center so here is a summary of what you need to know going into this:
• Anthony Sobo got requested to build a separate commission system by Tony. • Christian Rodgers built that system using google sheets that does not interact with the database at all. • Because of constantly changing Commission systems, that system requires it be constantly maintained by it's creator Christian Rodgers. • I attempted to call a meeting with call center leadership on how I want to build a new commission system and was shut down by Sobo saying they already built it. • I attempted to meet him halfway and explain that if he can deliver the metrics we need for financials, that would be enough for us, but unfortunately did not get through to him. • Christian Rodgers should be on our team, since Edward left, but Tony and Sobo are holding him tight and it's restricting the work we can get done. I NEED:
• Documented Clarification from leadership across the company on who is to own the commission project. • Documented Clarification on who is to maintain it when intake makes future changes that effect systems. • WRITTEN Requirements from Tony on what he needs to have a functioning commission system for the call center. • Written Clarification, and confirmation on a move-over date for Christian Rodgers, as well as who he reports to. • Confirmation of IF we want to include include Paylocity data or not. ◦ PRO: Constantly updated list of all agents, if NO, supervisors would be in charge of keeping their agent lists up to date. ◦ CONS: Would require a lot of permission issues around sensitive data, and take more time to develop.
I think we put the commissions in payroll field as part of our monthly operating expenses and get that number from Roger’s and Tony for now. We take care of revenue, billing and will just allocate the commissions as an average per billable tip lead.
Then when we have Invoices by month , week and TIP PnL dashboards, they’ll learn that they are disconnected from actual versus averages. They’ll break our data warehouse commission solution anyways with their constant tinkering. Then you can leave this whole BLOCK in the rear view mirror for now.
Getting the profit and loss, and the invoice dashboards done is more important than getting exact commissions if they’ve got some lame system to do it.
Paylocity should only be used to get the people’ and time information, no financials
And that can be done after we get the new financials by lead and by day tables created and merged with the old historical data.
Sounds good to me! I'll open the meeting with the doc I sent but if you wanna follow up with that, it will make the commission side easy, and we can move into trying to figure out what's going on with Christian team wise.
Mentor / mentee moment: Thanks for hopping in on that. It can be a little intimidating being the only non-leadership person in a meeting like that, especially when the topic is financial as well as personnel / personal
Digging into those numbers talked about in the admin chat now to make sure because something felt off. Had to step away for a minute but I'll be right back and join the meeting.
Great article, I think you might like it: Pocket Guide to Modern Databases
I'm not in office yet, but I can start working from home to try and get this resolved now if that's alright?
I'm not in office yet, but I can start working from home to try and get this resolved now if that's alright?
I got on WIFI (poorly) and see only two for 6-2-2025 in BillableLeads...
SELECT **
FROM `tort-intake-professionals.Financial_Log_Dataset.Billable_Leads_With_Rates_and_lp`
WHERE earliest_esign > '2025-06-02'
ORDER BY earliest_esign DESC
We have probably 10 to 15 e-signs today already, if Dustin and you can speed up the pulls today, I can use this table to possibly build a page per MB for e-Signs.
Yeah I'm seeing that now and looking into it. It's important to remember this system goes by EARLIEST esign so data is gonna be this skewed if we are finalizing leads from the last few days this morning
Seeing if I can get you a pull from the activity log that shows all esigned status changes so we can build a temporary table out of that.
Cool, I got all esign lead status changes of out the activity log, but it's set to update every hour. Which is as fast as I can get from LR_data right now unless Dustin bumps up the speed (could cause Lawruler issues)
Setting up a Looker that will update with those so Cam can see them as they come in until we figure out the notification system
Here is a working query I wrote:
SELECT
earliestesign,
SPLIT(lawrulercasetypename, '-')[OFFSET(0)] AS firstsegmentcasetypename,
lawrulerleadid,
leadspedialeadid,
contractids,
lawrulerleaddisplayname,
leadspedias1
FROM tort-intake-professionals.Financial_Log_Dataset.Billable_Leads_With_Rates_and_lp
WHERE DATE(earliestesign) = CURRENTDATE()
AND leadsourcetype LIKE '%Shield%'
ORDER BY earliestesign DESC
It should ONLY show these columns, I tried to get the SQL to make primary firm and partner firm into abberviations, but it wasnt' working:
If we can ask Dustin to speed it up to every 30 minutes and monitor LawRuler? It's that urgent to Cam. Maybe have Nick Ward inform LR so we can work with them?
Just sent that over to you. It has each lead as it comes in, the campaign the specific tort type, what status it came from, and went to, and filters for date and e-sign so by default it
I can also tweak it so it ONLY shows intake campaigns if you want
Just use the query above in your dashboard, make it look like this:
No filters, only "today", with these columns.
Then send out to Cameron, Yan, Maximus, Jack A., John Ventura, Brian, me, Tony Jones and anyone else that needs to see them. I've put these into EACH Media Buyer's personal dashboards too. Then lets get LR ramped up for every 30 minutes.
@James Turner, you have to use my SQL, you cannot show the MB's firm names of any kind please.
Your dashboard shows full Case Type name, cannot do that, see mjy SQL and how I trim it.
Cam and Yan will use yours for the Google deal and to see ALL MB's deals.
@James Turner, share yours to Cam, Yan and Tony and me please.
My laptop is dying, no plugs on Southwest. Thanks for the emergency help.
Gotcha, I'll tweak your query to get it working, and replace the dataset on the dashboard with that confirmed billable data instead.
Confirming- you ONLY want the leads with shield legal as the marking source on this dashboard?
I thought cam would use it for all, not just Shield?
Hopefully we can use these for long term but knowing Cam, he’ll want them faster from email automations in Slack.
Cool- By the way, "generalcasetype" is usually what you're looking for when you trim that case name.
I have it set up so it does that within PCTID
It was blank on many of the ones from your billable leads table
Just updated it, but it should be self updating so that's good for me to know. Thank you
Spread out columns, MB's need to see their full S1s.
That might be how you are viewing it. It's showing full size on mine
Do you know what conference room we decided on?
When setting up the dashboard for Tony, we discovered what's going on with the wild billable minutes:
The call center is spam calling leads, AND calling from the wrong campaigns, + using the wrong dispositions.
It's so bad that one lead got called 17 TIMES IN ONE MINUTE, and 90% of the calls were manual dials from the wrong campaign. (What would you do if you got called 18 times in a minute?)
The call center doing this without repercussion eliminates any kind of AI training, because it completely messing up the call data, and on top of that, we are incorrectly billing the wrong firm. This is big bad.
We NEED to get rid of Manual Dialing, but Tony won't listen to Nick and I. What should we do?
When setting up the dashboard for Tony, we discovered what's going on with the wild billable minutes:
The call center is spam calling leads, AND calling from the wrong campaigns, and using the wrong dispositions.
It's so bad that one lead got called 17 TIMES IN ONE MINUTE, and 90% of the calls were manual dials from the wrong campaign. (What would you do if you got called 18 times in a minute?)
The call center doing this without repercussion eliminates any kind of AI training, or use case we could do because it completely messing up the call data, and on top of that, we are incorrectly billing the wrong firm. This is big bad.
We NEED to get rid of Manual Dialing, but Tony won't listen to Nick and I. What should we do?
When setting up the dashboard for Tony, we discovered what's going on with the wild billable minutes:
The call center is spam calling leads, AND calling from the wrong campaigns, + using the wrong dispositions.
It's so bad that one lead got called 17 TIMES IN ONE MINUTE, and 90% of the calls were manual dials from the wrong campaign. (What would you do if you got called 18 times in a minute?)
The call center doing this without repercussion eliminates any kind of AI training, or use case we could do because it completely messing up the call data, and on top of that, we are incorrectly billing the wrong firm. This is big bad.
We NEED to get rid of Manual Dialing, but Tony won't listen to Nick and I. What should we do?
When setting up the dashboard for Tony, we discovered what's going on with the wild billable minutes:
The call center is spam calling leads, AND calling from the wrong campaigns, + using the wrong dispositions.
It's so bad that one lead got called 17 TIMES IN ONE MINUTE, and 90% of the calls were manual dials from the wrong campaign. (What would you do if you got called 18 times in a minute?)
The call center doing this without repercussion eliminates any kind of AI training, because it completely messing up the call data, and on top of that, we are incorrectly billing the wrong firm. This is big bad.
We NEED to get rid of Manual Dialing, but Tony won't listen to Nick and I. What should we do?
Gonna talk to Cam about it, but to keep you in the loop:
Since the 1st of the month we have had: • $28,370 of revenue potentially being billed to the wrong clients on our $2 a minute average. • 21,323 Manual Dials with the incorrect Campaign selected (55% of all manual Dials being done incorrectly.) • 14,185 Incorrectly placed talk time minutes due to the call center not doing what they need to do and selecting the incorrect campaigns because agents are trying to be "Competitive"
Gonna talk to Cam about it, but to keep you in the loop:
Since the 1st of the month we have had: • $28,370 of revenue potentially being billed to the wrong clients on our $2 a minute average. • 21,323 Manual Dials with the incorrect Campaign selected (55% of all manual Dials being done incorrectly.) • 14,185 Incorrectly placed talk time minutes due to the call center not doing what they need to do and selecting the incorrect campaigns because agents are trying to be "Competitive"
Ensure Edward does not apply any of that to our EOM talk time in PG
Made some tweaks so as long as he pulls the billable talk time from my billableleadswith_lp data, it will be correct because we are pulling the right campaign from Lawruler, not Five9
Had a whole meeting with Tony, Joe, Cam, and Nick, where I presented what I found. I believe it went well. We are going to continue Manual Dialing and our broken quota/commission system which I believe is a mistake, but regardless, we are moving in the right direction
All you can do is communicate the concern, good job. They’ve been making our data messy for a long time, nothing new.
Good morning- I noticed we have a bunch of SEC leads being counted on 1.3- is that on purpose? My understanding was we count original retainers but no secondary campaigns?
Yes, they are billable and you added the status to the lambda for them to count.
Good morning! I set up the BillableLeadsWithRatesandlpwithsecondaries table so that way we have billableleadswithratesandlp for just intake/retainer leads, and "with_secondaries" includes all of the secondary leads
When you get the chance, please give Rose a ring to explain exactly where she should be pulling the data from to put it onto the 'Other Expenses' Tool. I gave her a rundown of how to use it, but she would like explicit instructions from you for where and what data to pull, and methods to avoid counting as duplicates.
When you get the chance, please give Rose a ring to explain exactly where she should be pulling the data from to put it onto the 'Other Expenses' Tool. I gave her a rundown of how to use it, but she would like explicit instructions from you for where and what data to pull, and methods to avoid counting as duplicates.
Prep for meeting today, you can smile, listen and Malissa and you can zero out iocasetyperevenue_rates table $2.00 per talk time rate.
Hey Ryan, quick heads up: Edward hit a snag with the PG admin change, so I stepped in to help him get it sorted after wrapping up my own piece.
Since I’m not super familiar with the older system, I had him walk me through his understanding first so we could double-check our approach. I’m confident it’s solid, but if anything looks off on your end, definitely let me know, happy to course-correct.
With Dustin out this week and remote next, and McFadden unexpectedly out sick, I already found myself unofficially stepping in as an acting lead- even more so than usual. I flagged to Ward that I’ll be pulled in a few directions, as I support the junior teams to keep things moving, but I’ll do my best to keep everything steady in addition to my other tasks.
Gonna get started with that role list now, and then start moving things over to looker enterprise. I'll keep you updated in case we run into anything.
@James Turner , thank you and I really appreciate the proactive approach. Excellent.
Did you or anyone change any permissions on Bigquery or elsewhere in any of our data pipelines? Out of nowhere most of our scheduled queries started crashing out of nowhere all at once
Did you or anyone change any permissions on Bigquery or elsewhere in any of our data pipelines? Out of nowhere most of our scheduled queries started crashing out of nowhere all at once
@James Turner, please ensure this table does not change, it broke 5 dashboards with field changes:
Sorry about that, I did not know that .view was being used for anything other than what I had built at that point.
Note all of it was still in UTC time, and Looker Enterprise does not support array values like Looker Studio does so it was presenting data incorrectly.
I was working on a fix to try and get those to appear correctly
Sorry about that, I did not know that .view was being used for anything other than what I had built at that point.
Note all of it was still in UTC time, and Looker Enterprise does not support array values like Looker Studio does so it was presenting data incorrectly.
I was working on a fix to try and get those to appear correctly
Yeah, but Datastudio sucks for security, uniform management and goes out all the time since it's a free SaaS.
I talked to tony in person. He said he would send me the prices almost an hour ago and still nothing.
*Thread Reply:* tell him we are doing $216 on all of them
Is data updating on lp_billable? How many you have yesterday
*Thread Reply:* Looking into this, it looks like Lawruler made some tweaks that broke the API by adding new fields and they did not notify us. Dustin is reporting that the issue has been resolved.
*Thread Reply:* The issue is currently being resolved** Should be presenting correctly in about 4 hours.
And here is the error message, seems like your table change changed the data types, can we address the Looker View LKM file fixes so it does not break the dashboard @James Turner?
Hey there, I am currently away from Laptop. since lookml stores it in git, we should be able to roll it back to before I started changing it yesterday.
For an immediate fix there is a working dashboard in Looker data studio that I shared with you with all of the info you're looking for
Let’s wait until you can just update the code to fix data types , let me know when you can.
Please fix this dashboard request from @Cameron Rentch James. That page is the new dashboard I mentioned that I built on Wednesday. The data table and type cha fed need the Total Cintracts Sigbed pane fixed for yesterday filters.
> If you click the pencil icon on that third top right pain in that page when you’re editing the dashboard, you’ll see that the sequel isn’t fully working. Which means we should check the other two pains, the one on the top left of the table in the bottom to ensure that all of it actually works and that’s the number sync up in total.
Apologies for the delay on this. I'm currently in southern California on vacation. I was attempting to figure out how to roll back the lookml via mobile but did not see the ability to do so.
It looks like I don't have permissions to roll back to previously on lookML from what I am seeing.
This has everything they need until we can figure out how to properly handle array values in Looker Enterprise, and it's secure to only who we choose to share it with
I made you an editor so you can edit permissions as needed
What version? What day? Send me link to GirHub code.
@James Turner, I am giving you GitHub Admin access in both TIPLooker and TIPBI
Sent invite for https://github.com/SL-BI/tip_datawarehouse_looker
and
Sent invite for https://github.com/SL-BI/tip_project
Both are Admin, you are Owner in GCP for TIP, adding you to Shield. Let me know when the changes are made.
And Admin for SL https://github.com/SL-BI/sl_project
When I was looking at lookml earlier, I saw a commit from 12 days ago that has the semi-working lookml code that you used for your dashboards. If we can find a way to pull that back the dashboards will appear to work again but not accurately represent data while stored in Looker Enterprise, because Looker Enterprise does not store the array values correctly.
The change I made to the LookMl, in the latest update on Friday changes the array values to comma separated string values so you can see the entire string of partner firms and other fields, as opposed to only the first value.
My recommendation would be to update the dashboard to accommodate the now mostly correct data set.
If you would like to return to the data that does not portray correctly because of LookMl not supporting arrays, but the row count should be right, we need to find where the lookml commits are stored. I could not find them on our GitHub earlier and could not find a way to find them on Looker Enterprise either.
If we do find a way to find it, the commit was last updated 12 days ago. Once found we can make a whole new dataset and use that lookML code instead of replacing the updated code.
I am currently driving back to Las Vegas right now, but I'm happy to pick up on Monday to either update the dashboards you made to accommodate the corrected data set, try and find a way to return to the incorrect data set we were previously presenting, or come to a new solution together
@James Turner, I want this page to work like it did, and we can figure out Array fields later on a different table, we are not using those fields.
Marc and Cameron use it every day now, it cannot break.
@James Turner, can you get it working? Between Zek forgetting daily financials and this breaking, I've worked every day of my vacation so far
Not at the moment, I'm driving right now, but I can get it working tomorrow.
I did not know that the data set was being used for Marc and Cam when I made corrections to it.
I am not opposed to working while on vacation, but when you told me to make sure it does not change on Friday, I stopped changing it as I was instructed.
I never asked for the table to change, it worked for several weeks then just broke, I did not know you would update it. But yes, let Cameron know you'll get his page working. Please do not put them on DataStudio. I have worked for years to get them onto Looker Enterprise, and trying to ween them off Slack Alert channels.
@James Turner , tomorrow or Tuesday when you have time, reach out to bytecode and ask them if they know a way to handle the right field and look for enterprise.
Sounds like a plan! I'll get started on it first thing tomorrow morning!
I worked on this late last night, after getting home, and wrapped it up this morning. Please confirm if this is as it should be:
Note that the Earliest Esign Metric you are using is it UTC, not PST. Please let me know if that is alright, or if that needs to change. I don't want to make any more tweaks without approval, that could break things.
Send to cameron please, he’ll let you know if that looks right
Can we fix the table to sink on Pacific time midnight please. Thanks James.
Good morning! Do we know what exact time it reset? I started poking around at this last night and I'm hoping to get it fully resolved this AM.
I believe I have it figured out and a fix implemented, but if I have an idea of the time it resets I can be sure.
Sending out an Email you're CCed on- you can ignore it- just want you in the loop for when you get back.
Sorry, that was the email you could ignore- I meant to send it on Friday but opted to save for Monday instead. I appreciate you digging in though
I made sure to build the standards around what you asked for; DBT, Looker Enterprise, Ect
When you get back- Not important now.
Murilo from sphere team, is requesting read access for some tables in the Bigquery org that I believe only you as the Org owner has access to give.
He is also looking for information / Read access on the Fivetran and DBT automations.
When would be a good time for you when you get back to give access, and set up meetings to answer any questions that may arise.
I attempted to provide read only access at the org level so he can see how that works, but do not have access to do so.
@James Turner, send me his email and I'll get him access.
Good morning! Welcome back~
His email is mcamargos@sphereinc.com
@James Turner, let Murilo know I have given him access to BQ, DBT and FiveTran for both SL and TIP BI warehouses. He needs to talk to Dustin about LawRuler integrations and data warehouse he puts into PostGres that is then mirrored over to BigQuery.
Here are the only two users we have on the old PostGres systems,, he MUST be careful, these logins run everything:
TIP Call Center
AWS Lightsaid Postgres DB PG Admin or DB client
user: dbmasteruser
pwd: Q7B2(R8d;m`n}l%kj2M7q^S&:}1z,F
dbname: tip_master
port: 5432
URI: ls-ee79dd780a352064468e4dc5bfef3cbc5675526f.cezd6k0feuf4.us-west-2.rds.amazonaws.com
SL Marketing System
AWS Lightsail`
user: dbmasteruser
pwd: k(h~$(5!Lig{nX7Qg8PDAJx,.z0#y&>c
dbname: dbmaster
port: 5432
URI: ls-48c3a8e572feb82920362771ec6c47370fa262da.cezd6k0feuf4.us-west-2.rds.amazonaws.com
Did you open '0 - Real Time Retainer Alerts' or make any changes to it this morning?
Nevermind- It was a cache issue. All is well
I got quite a bit on there, but was not able to get all of the permission sets because I don't have visability into everything in themedialab.agency bigquery- From what I learned after getting this to joe, there are a lot more projects than just the Shield Legal BI and Tort Intake Professionals ones that I see
Made two versions of that dashboard I showed you- one for you and others that should be privy to the financial info, and one for the call center people. The one for you is called TIP All Lead Information - BI
Default is set to "today", and only showing billable leads, both intake and secondary interview, of any tort type but it's all completely adjustable however you want
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 the day that Ward went on Vacation.
They are populating on there, but mal hasn't been filling them out as they come in. I'll talk to her about it today to remind her
Please get them into PCTID ASAP, she may need help or have her have Greg Owens do it.
Oh my way into work now- I'll fill them in asap
It looks like the call center started using 'Master' Campaigns instead of properly selecting the right campaign which we need for metrics and billing. Did anyone talk to you about this??
Spoke with Sobo, and Carter Matzinger, and it looks like this is the direction we are going for the new dialer type, which I like- gets us away from Manual Dialing.
Carter will likely be setting something up with you to discuss how we may need to modify financials.
They are using the master campaigns for all outbound cases- and to get the right campaigns for the minutes, we will use the LR data that it's associated with
Sorry for missing the meeting, got locked in on a request and missed the slack notification. I am currently doing an audit on the billable leads pipeline to make sure we aren't missing anything, and getting Integrations some data to help make sure they don't miss leads like what happened with the DIcello email issue, by request of Carter.
I am happy to hop on now to go over anything needed, or if you would like to just send me the meeting notes that works for me as well!
Take care of that, I sent the meeting for tomorrow to cover next steps and getting all field system to the new BI system
On Secondary Leads- Post Retainer Interview is NOT done in Five9, so we have no way of tracking their talk time minutes.
I would like to confirm, we are ONLY tracking and billing for talk time for the inital retainer, correct?
Also, with our new dialing method, we should be seeing a lot more billable call time. I spoke with carter about it and he said he would speak with Cam/you but I wanted to know if we forsee any issues because that will change how I build this out
Are you available for a quick call? Found something on Secondary leads I need to clear up
What we talked about makes sense for about 9000 leads but I have another 17 thousand leads between intake, secondary, blx, acts ect, where the current status is billable = true, no e-sign date, and iostatusbasedpricingactive = FALSE
What I recommend is we take the date it went into that billable status, and charge that against the iocasetypebillable_rate for the campaign it belongs to
For example we have 488 leads that belong to Lexamica CRQ - BCL - BCL - BLX with no earliest e-sign, and that campaign has no over-ride, so we charge the 236 from it's iocasetypebillable_rate
Correct, as long as in status that is considered billable from the status_rates table.
Cool- so on the new system where we look at all leads, not just intake retainers, they will now have a value known as 'billable_date' because that covers leads with the overwrite rate, as well as any other outliers.
earliestesign will still exist for leads that have a esign date, and overwrite billabledate when possible, but we will use billabledate as the defacto measure against iorevrates, and statusrates to get the right values for revenue data
How do we want to handle the Milestone Tax Solutions - Shield Legal campaigns? I have heard mixed messages on what it actually is, but we have 'Signed e-Sign FINAL's on it
How do we want to handle the Milestone Tax Solutions - Shield Legal campaigns? I have heard mixed messages on what it actually is, but we have 'Signed e-Sign FINAL's on it
Add to PCTID, ask Tony price and ensure they are setup as normal.
We are making damn good progress on the profit / loss summary! Still in data validation + testing, and waiting for rose to update values before I would declare anything ready but we are moving in the right direction!
We are making damn good progress on the profit / loss summary! Still in data validation + testing, and waiting for rose to update values before I would declare anything ready but we are moving in the right direction!
Now that we are getting more than 10 data sets in TIP_datawarehouse, it will make sense to change it to A,B,C, rather than 1,2,3 because it will go 1,10,11,2,3,4, ect-
Am I all good to change those over?
By changing these from 1,2,3 to A,B,C it will stay in a desired order
Covered everything we spoke about, but let me know if there is anything else you would like to see on here:
*Thread Reply:* This is looking great. We will discuss in detail this in our TIP meeting this week.
And it goes hand-in-hand with this guy here: https://shieldlegal.cloud.looker.com/dashboards/147?Billable+Date+Date=today&Primary+Client+Firm=
(Note the Intake numbers may vary slightly from Dashboard 0 because we split out BLX here + slight timing differences)
Now that we have a more 'Complete' set of Billable Leads- I set this up to fact check myself as I built the P&L
Let me know if we want to swap out the 0 - Real Time Retainer Alerts for this, or add this as something for Cam / Marc
@James Turner, this all leads looks good too. Just need to update them a bit on format and layout for TIP management and SL leadership. I'll show you what to make them look like in our meeting, plus filters. Once we have the below pages ready, then we can switch over TIP Invoicing to this system. Until SL Financials is using the new TIP BI data for counts, revenue, call costs across their dashboards, we will have to still run the old system Lambdas with Edward or give this task to you, as TIP Data Engineer to run, sunset when ready and you can probably create a script to run it to make it easier until we sunset. We'll discuss in our TIP weekly.
Please send me the Meeting Recording / transcript from earlier when you get the chance
Hey there- Good morning! What does 'Signed Contracts' here represent? is it just any billable lead at all?
For actual billable E-signs, Sobo and I each separately got 157 billable e-signs for the 23rd.
Summary count of financial_log per day of billable leads
Gotcha' and does that current number reflect any kind of reconciliation for leads that are no longer billable?
Is that still using earliest E-sign or earliest billable status in the case of a lead without an E-sign?
I ask because you're running higher than Sobo and I both doing independent data runs.
We have 157 Esigns on the 23rd, you have 167.
We have 226 Total Billable Leads after removing no longer billable, and test leads, and you have 239 - I just want to find the gaps and make sure I'm understanding these discrepencies
I ask because you're running higher than Sobo and I both doing independent data runs.
We have 157 Esigns on the 23rd, you have 167.
We have 226 Total Billable Leads after removing no longer billable, and test leads, and you have 239 - I just want to find the gaps and make sure I'm understanding these discrepencies
Mine includes all Billable status's. Not just E-signs
@James Turner, able to join call or should I push?
Good morning- Let's push back if possible, traveling at the moment.
Got this set up with new revenue coming in each day, and 0 values for now- once it's updated on the expences table by rose- it will update properly.
Apologies for the late message, but did we ever wipe all talk time rev for the bulk of May, or was it just those specific campaigns we spoke about? Looking to resolve an odd bug and I'm fact checking myself.
Good morning! Have you found any way to replicate the pivot style tables from Looker Studio into Looker Enterprise?
A quick dig into it on ChatGPT says it's not possible to do those expanding dashboards, and our best bet is page-wide filters and a 'flat' table- but I want to make sure I'm not missing something.
This is what I am referring to by the way:
They have Pivot features in Data Grid but I would build a SQL model table updated every five minutes and out it into TIP_dashjoard dataset in BQ
I'll mess around with that and let you know what I put together, then we can tweak as needed for Cam- Thank you!
Do you have the contact info for our looker contact? I wanted to ask a few questions that I'm not finding answers for in their documentation.
For these secondary campaigns, what should we put as the marketing firm? My understanding is Yih is a tag that Cam / SGGH is using
Looks like he is on Vacation- for the Intake campaigns they site Shield Legal in the name, but normally when I see campaigns that result in Secondaries they don't go through Shield Legal?
I will attempt to find out when he is coming back, and leave marking as blank for now until we can verify what it should be.
It took some hardcoding to make it work properly, but I'm pretty proud of how this came out. Going to be building the 'Invoices by Week' next and if all goes according to plan, it will be good to go for review by end of day!
It took some hardcoding to make it work properly, but I'm pretty proud of how this came out. Going to be building the 'Invoices by Week' next and if all goes according to plan, it will be good to go for review by end of day!
Great work, Nice! Do you have a "Total Fees" column in there for Rose?
I have it set to a row count of 500 right now for easy building / review but when we are ready to launch, we can set that as needed.
Correct- It's two different dashboards so Cam and marc don't have to click around.
That being said, I set it up so an experienced user can drill down and see the exact minute it became billable if they would care to do so.
*Thread Reply:* AI huddle notes are ready. Edit, share, assign action items, or just squirrel them away for later. 🐿️
Good morning! I've been digging into the permissions on Looker enterprise and it looks like the best way to do it is to limit permissions on the folder/dashboard level and also limit it on the model level by editing model sets to groups citing the permission they should require beyond just Shield or TIP. We should also remove the ability for the standard viewer to look into explores and only see what it on the dashboard for the data sets where there may be sensitive data in the data set, but not available on the dashboard.
I can get started on all of these changes, but I wanted to verify with you we were in a good place to start before beginning.
Good morning! I've been digging into the permissions on Looker enterprise and it looks like the best way to do it is to limit permissions on the folder/dashboard level and also limit it on the model level by editing model sets to groups citing the permission they should require beyond just Shield or TIP. We should also remove the ability for the standard viewer to look into explores and only see what it on the dashboard for the data sets where there may be sensitive data in the data set, but not available on the dashboard.
I can get started on all of these changes, but I wanted to verify with you we were in a good place to start before beginning.
Should Alan Gil and Tony have access to the TIP Financials shared folder? I saw they has been manually added to that shared folder, but he was never put in the 'Financial Viewers' Group.
I am considering making a 'TIP Financial Viewers', 'Shield Financial Viewers' and 'Master Financial Viewers' groups, so we can cleanly split it by company, and by people who do work in both.
Check DataStudio. I think Tony has access, but Alan may only have the Operations side.
For the user permissions, not the viewer permissions, we gotta figure out how to block the financials from the Explore too.
And yes, folder level - user level is how we do it now.
In the role permissions, we can remove peoples abilites to view the explore- and we can also split the explores into different groups. For example: instead of having everything under tipdatawarehouse, that should be split into tipfinancial, and tip_operations so that way a supervisor can be given access to the explore level, without having access to financials.
That piece is going to be quite a bit more work because it involves re-building what we already have, so it may just be better to remove explore access for everyone that's not a developer for now, and make those good choices moving forward
Anthony won’t be able to build dashboards then, right?
If someone is making dashboards, they would be considered a developer, which is why we would want to split up the models to only give access to certain explores depending on what developer group you are in.
Changes:
- Changed sub-total table style as requested.
- Adjusted Filter Options
- Refined Signed and Declined
@James Turner, I moved tiles. I added "Flatirons" to the middle pane, make sure 3rd pane has "Flatirons" as the filter "flatirons" did not work. And make middle pain all Signed & DECLINED" contains only. Then table 1 is non-billable leads, table 2 is Signed & Declined, and Table 3 is the details of those Signed & Declined". 🙂
Table 1 still has Flatirons deals in it for example on the filter issue. 🙂
Looking into why the Flatirons filter is not working now-
Been testing and reading docs - Best option I'm seeing is to just add a filter, for now. Looks like Enterprise doesn't support string search in their custom tile based filters.
Made the middle section all signed and declines summary, and made section 3 'Signed and Declined Lead Details'
Made the middle section all signed and declines summary, and made section 3 'Signed and Declined Lead Details'
Come to room 2 conference, let me show you one thing to fix on it.
@James Turner, watch this and let me know once we tally up in the panes correctly.
@James Turner, watch this and let me know once we tally up in the panes correctly.
Apologies- stepped out to grab some lunch at 1. Be back soon!
Going through it now- Got it all fixed.
In regards to the 'Signed and Declined Intake Lead Details' table: • For the The Legacy 'Table Report' Does not support a total unless we add a measure. • Since it is a details table, no real measures are needed since it shows the exact details of each lead. For the 'Signed and Declined Intake Lead Details' table, I can set it as a standard table so it can show row counts for confirmation.
LookML RBAC Q - What is the 'Marketplace Automation' role for? Theres only 1 user and they are also named 'Marketplace Automation'
And who is Brian Viewer?
Before you leave, do you have a few minutes for a quick 1:1 in person? I have a more mentor style question for you I wanted to get your thoughts on
Before you leave, do you have a few minutes for a quick 1:1 in person? I have a more mentor style question for you I wanted to get your thoughts on
I had to run already. We can do that Friday early. Send me invite.
Do we want people to be able to use Gemini on Looker Enterprise? I would advise we remove the 'Gemini Default Users' as right now every person on the system has access.
No, their Gemini sucks, it just builds lame SQL and is not truly conversational. And you have to pick an Explore specifically. It's lame attempt by Google.
By the way, here is what I have come up with so far on Looker RBAC - For Phase 1 which will fix access for all viewers I just need to build out the actual Permission sets next, get everything approved and then we can start implementing changes.
Phase 2 is fixing permission for Devs by cleaning up and changing the actual .view and model files to have separate versions for data sets with and without financial information.
@James Turner , need SL Financial and SL Operations Roles where Marc, Cameron, Andy, me, Rose, Malissa will go.
That would be under the 'Shield BI Dev' Role for you since you would have edit access, 'Viewer Master' role for Marc, Cameron, Andy, Rose, and Mal, and all of you will also get access to the Master Financial Viewer group which would be the group you add to the tables.
Since the group would include each of you at different roles, they would be able to see, and explore, you would be able to Develop
That would be under the 'Shield BI Dev' Role for you since you would have edit access, 'Master Financial Viewer' role for Marc, Cameron, Andy, Rose, and Mal, and all of you will also get access to the Master Financial Viewer group which would be the group you add to the tables.
That would be under the 'Shield BI Dev' Role for you since you would have edit access, 'Viewer Master' role for Marc, Cameron, Andy, Rose, and Mal, and all of you will also get access to the Master Financial Viewer group which would be the group you add to the tables.
Hey Ryan,
We currently have two Looker Enterprise folders named similarly, which I’d like to clarify for RBAC:
• TIP – Operations Team – does not include financial information. • TIP Operations – does include financial information. I propose renaming the second folder to “Operations with Financials” since it contains both Shield and TIP operational data with financials. This will allow me to update permissions so only authorized users can access financial data.
Do I have your approval to proceed?
Hey Ryan,
We currently have two Looker Enterprise folders named similarly, which I’d like to clarify for RBAC:
• TIP – Operations Team – does not include financial information. • TIP Operations – does include financial information. I propose renaming the second folder to “Operations with Financials” since it contains both Shield and TIP operational data with financials. This will allow me to update permissions so only authorized users can access financial data.
Do I have your approval to proceed?
For the TC campaigns, I see campaigns with TC %% where %% represents some kind of unknown meaning. Are all of these TC campaigns as well, or some offshoot of TC??
For the TC campaigns, I see campaigns with TC %% where %% represents some kind of unknown meaning. Are all of these TC campaigns as well, or some offshoot of TC??
If it says TC, it’s TC. God knows if Sob forgot the TC is some.
@James Turner, quick easy reqeust from Marc for TIP dashboard data.
*Thread Reply:* @James Turner, make this the 1st priority to not keep Marc waiting. Ty!
*Thread Reply:* Confirming you are referring to the splitting of las Vegas and Phoenix data on dashboard 0?
Currently looking into that now- but all current system architecture does not pull closing agent into any of these pipelines so we will need to change all of that to make it work.
*Thread Reply:* I'll make it happen, but I want to share that with you so you understand this may take a little while.
*Thread Reply:* Why, is assignee not on our billableleadswithlp tables?
*Thread Reply:* Not currently on Lawrulerwithleadsenriched which is the base we use for everything, and includes iscurrently_billable which is the boolian that flags if a lead is billable.
I Worked with Dustin on understanding how that information is generated and I'm getting it in now.
Apologies, been in back to back fires all day and missed this message. I can hop in now
Working on the TC Dashboard now. I’m wrapping up the prerequisite items to ensure everything runs smoothly.
• Made strong progress setting up Rules and Permissions for external users. There’s still cleanup to do on existing access systems, but the blueprint is in place. • Currently beta testing row-level permissions so external law firms only see their own data. It’s coming along well, and I’m focused on making sure it’s fully stable before we use it as the gateway for client dashboard data.
@James Turner, will show you in our meeting tomorrow. It's pretty easy, need Total Filtered Leads for Phoenix versus Vegas on our Page 0 existing dashboard.
Validation from TIP AWS for July 2025 SL retainers only
Why, is assignee not on our billableleadswithlp tables?
Some people just have their name in lawruler, so they are location not specified
Until we get an actual org chart with a database of employees (Needed for Leadcomplete)
@James Turner , DM me, Cameron and Tony and ask Tony to update all named people with PHX or LV now. I’ll chime in, this is urgent for owners.
@James Turner , DM me, Cameron and Tony and ask Tony to update all named people with PHX or LV now. I’ll chime in, this is urgent for owners.
@James Turner, please join ASAP. Your acting as Data Engineer on a killer AI project Chris and CC will lead up.
Can you look at the filter and make sure that no non-retainer case types are in there, cameron sees two
It's because someone put it in a Signed e-Sign status. We have that as a retainer/intake status. Working on fix now
Hey, I added 2 more columns by merely creating filtered custom measures and did not touch the original Unique Contracts column in the top left pane, but it shows 55 and the rest show 58. Can you help me figure this out? I checked filter settings, pane Explore filters and everything seems correct.
First one is Shield Financials folder, second one is TIP Operations Team folder.
It self cured, nice. Nevermind on the above ☝️:skintone4:
Sorry about that! I was doing the Operations Onboarding with CC so I can get her familiar with how the company runs prior to the data walkthrough
I was also verifying Carter, sobo, ect- DO have access to the tip operations team folder so they can see the TIP Operations board
Thank you. Please help them log in if they cannot figure it out
@James Turner, I fixed iolrrev_rates Scheduled Queries, moved Shield Reconciliation to TIP Operations Team in LookerE, and have Malissa updating the missing 40 to 50's pricing information.
Is there a reason that Shield Recon wouldn't be in Shield Ops? the TIP Operations Team folder and board is for non-financial information for the call center and operations people here
Anything that you have in the TIP Operations Team folder, call center leaders will be able to see. That's why we set up separate folders for TIP Financials, Shield Financials, and TIP Operations
Anything that you have in the TIP Operations Team folder, call center leaders will be able to see. That's why we set up separate folders for TIP Financials, Shield Financials, and TIP Operations
Ty for fixing that- currently swamped with a lot of different things rn and trying to get this call center data present ready and accurate before presentation at 4
Becuase Brittany, Aidan and Esteban use it. It's a tab on the old DataStudio > v1.3
Good morning- I'm reaching out to ask if we can potentially reschedule the meetings for tomorrow? Currently sick with a bad case of the stomach Flu.
Since we aren't allowed to work remote, I put in a PTO request for a sick day, and was asked to try and reschedule my meetings.
Let me know- thank you!
@James Turner, you in? https://meet.google.com/rkx-wuee-ckc?authuser=0
To touch on what we talked about before in the meeting. This lead went live- we called them 45 times in under 2 minutes. (We got ahold of them on the first call, but the call dropped, likely because of us spamming them)
It also shows agents trying to steal leads by putting it in false status's so other agents don't get it. This Amani person had a 2 second call and put "Does not Qualify" so they could try and hide the lead from other people
If we build a metric for Marc that shows what we talked about, we are rewarding these guys for harassment and stealing from their co-workers instead of being good at their job. Tony needs to fix his call center before we can go forward.
@James Turner, I added Agents Converting since you are sick. I had time open up. https://shieldlegal.cloud.looker.com/dashboards/118?Leadspedia+S1=&Leadspedia+Lead+ID=&Marketing+Source=Shield+Legal&Earliest+Esign=this+day&General+Tort+Type=&Lawruler+Case+Type+Name=&Intake+Agent=|https://shieldlegal.cloud.looker.com/dashboards/118?Leadspedia+S1=&Leadspedia+Lead+ID=&Mark[…]eral+Tort+Type=&Lawruler+Case+Type+Name=&Intake+Agent=
Thank you- I'll inspect to see how you set it up-
When you get the chance, please read my last few messages about how the agents are manipulating it
Check out how the call center reacts when we bring it to their attention.. I don't know how we are supposed to "politics" this. Advice?
*Thread Reply:* Carter is digging into this with aligned concerns on Commission structure and Manual Dialing. I did ask Malissa to ensure all Category, Category Points and Denominations are filled out in Retool.
Marc and Cam both asked for updates to Page 0 and I have a question on RBC in Looker
@James Turner, I cleaned up Page 0 on Shield Financials for Agent Closers for Marc.
Abd does any of our Looker Enterprise data sources have "all leads" so we could easily add a Scorecard showing how many "Active Agents" from looking at unique count of all agents active versus those that had sign ups?
When you say you cleaned it up, what did you do? I'm not seeing any changes on my end?
We have two data sources like you describe
all_lawruler_leads_with_enrichment Contains a row for every lead in lawruler with a bunch of "Basic" info about the lead.
10 - Five9 and Lawruler Data Joined Contains a row for every call that an agent makes in Five9, has a bunch of call info + information about the lead
These include is_currently_billable to show if they are billable, when they became billable by billable_date
Please, Please, Please, do not edit or change anything on these data sets- we have a LOT of systems that use them.
@James Turner, I only use the LookerE datasets as is to create additional things when I know the data is there.
What is the Looker Explore name for "alllawrulerleadswithenrichment" ?
Coming back from lunch now- lemme grab that for you
"8 - All Law Ruler Leads With Enrichment"