dbmasteruser
Q7B2(R8d;m`n}l%kj2M7q^S&:}1z,F
tip_master
5432
ls-ee79dd780a352064468e4dc5bfef3cbc5675526f.cezd6k0feuf4.us-west-2.rds.amazonaws.com
Morning, here is the "Daily Routine" I'll have you watch live on Monday recorded for today that we'll have you do. There is one more step to it to update signed and declines in the TIP Fin Log table but this part is a stand alone step:
Second video for training on TIP Financial Log Referral Allocations, TIP Slack Alerts
SQL for TIP Daily Morning Routine in PG Admin:
```-- FIND MISSING S1s in TIP DB SELECT lrleadid,casetype,firstname,lastname,currentassignee --SELECT lrleadid,typeofcase,firstname,lastname,currentassignee,casedescription SELECT ** FROM public.financiallog WHERE signeddate = CURRENTDATE - INTERVAL '1 day' AND currentstatus LIKE 'Signed e-Sign%' AND leadsource LIKE 'Shield Legal%' --AND (LOWER(firstname) LIKE '%test%' OR LOWER(lastname) LIKE '%test%') ORDER BY typeofcase,lastname ASC;
--MAKE S1s by Parent Case Type and LP Lead IDs SELECT signeddate, 'DL - FI - 439 - 1359' as AltID, firstname, lastname,'S1:'as S1Label, lpleads1,lpleadid,typeofcase,lpcontractscheduledowprice FROM public.financiallog WHERE signeddate = CURRENTDATE - INTERVAL '1 day' AND currentstatus LIKE 'Signed e-Sign%' AND leadsource LIKE 'Shield Legal%' AND (lpleadid = 'Referral' OR lpleadid = 'TV') ORDER BY typeofcase,lastname,lpcontractscheduledow_price ASC;```
@deleted-U072JS2NHRQ, hop on this Huddle real fast
@deleted-U072JS2NHRQ, try this link: https://meet.google.com/kty-xoee-egq
Try now, or see below for login @deleted-U072JS2NHRQ
I tried with Root user and IAM user and it says: this for both
@deleted-U072JS2NHRQ, try again. It worked for me first try, but I did not change that temp password.
URL: https://960735831404.signin.aws.amazon.com/console Account ID: 960735831404 User: aidan@tortintakeprofessionals.com Temp. Pass: [vLrN^5)
```For our meeting, the 3 sql statements.
--CASE REVIEWS SECTION SELECT signeddate, lpleads1,currentstatus,** FROM public.financiallog WHERE signeddate BETWEEN '2024-07-01' AND '2024-07-07' AND currentstatus LIKE 'Case Review%' ORDER BY signeddate ASC
UPDATE public.financiallog
SET talktimerevenue = (talktimerevenue+'28.37209302'),
grossrevenue = (grossrevenue+'28.37209302'),
netrevenue = (netrevenue+'28.37209302'),
talktimeminutes = (talktimeminutes+'14.18604651')
WHERE signeddate BETWEEN '2024-07-01' AND '2024-07-07'
AND currentstatus LIKE 'Case Review%'
--ESIGNS SECTIONS
SELECT signeddate, lpleads1,currentstatus,**
FROM public.financiallog
WHERE signeddate BETWEEN '2024-07-01' AND '2024-07-07'
AND currentstatus LIKE 'Signed e-Sign%'
ORDER BY signed_date ASC
UPDATE public.financiallog
SET talktimerevenue = (talktimerevenue+'83.47985507'),
grossrevenue = (grossrevenue+'83.47985507'),
netrevenue = (netrevenue+'83.47985507'),
talktimeminutes = (talktimeminutes+'41.73992754')
WHERE signeddate BETWEEN '2024-07-01' AND '2024-07-07'
AND currentstatus LIKE 'Signed e-Sign%'
--SECONDARY INTERVIEW SECTION
SELECT signeddate, lpleads1,currentstatus,**
FROM public.financiallog
WHERE signeddate BETWEEN '2024-07-01' AND '2024-07-07'
AND currentstatus LIKE 'Secondary Interview%'
ORDER BY signed_date ASC
UPDATE public.financiallog SET talktimerevenue = (talktimerevenue+'0'), grossrevenue = (grossrevenue+'0'), netrevenue = (netrevenue+'0'), talktimeminutes = (talktimeminutes+'0') WHERE signeddate BETWEEN '2024-07-01' AND '2024-07-07' AND current_status LIKE 'Secondary Interview%'```
```-- ASA Services
-- Monthly Routine -- Update Raw Status Report -- RUN Generate Fin Log Lambda UPDATE rawstatusreport SET currentstatus = 'Signed e-Sign Secondary Interview - ASA' WHERE currentstatus = 'Secondary Interview - ASA';
-- ONE TIME CODE FOR CLEAN UP --Update Fin Log table UPDATE public.financiallog SET currentstatus = 'Secondary Interview - ASA' WHERE current_status Like '%Signed e-Sign Secondary Interview - ASA%'
--GET LOG ROWS SELECT ** FROM public.financiallog WHERE currentstatus LIKE 'Secondary Interview - ASA%' ORDER BY date_inserted DESC;
--UPDATE LOG ROWS --MUST RUN TWICE!!! UPDATE public.financiallog SET contractrevenue = '100', agentcommission = '30', grossrevenue = contractrevenue + talktimerevenue, netrevenue = contractrevenue + talktimerevenue, talktimeexpense = '0' WHERE currentstatus LIKE 'Secondary Interview - ASA%';```
I'm in it too
SL / TIP Daily Routine
Wednesday, July 17 · 10:00 – 11:00am
Time zone: America/Chicago
Google Meet joining info
Video call link: https://meet.google.com/cru-ivif-oph
Or dial: (US)
Yes they will match , but you have to make sure sure in the dashboard you’re only selecting Shield Legal source, and that they’re all the E signs
Is this just Maximus testing out updating his updated media buyer code? or is this something I should look into
Don’t worry about that, that’s Maximus testing his campaigns
Gotcha, and last question: How do I find this again: it has a lp lead id, but no s1
<#C03GKEAN2SD|camp-lejeune> channel, look at last XLS for that day
Hey Ryan, I'm doing the ASA portion of the weekly financial revenue update, but, I just want to confirm I run this last part twice, right? I know it says to, but just want to confirm since it's first time I'm doing by myself 😅
@deleted-U072JS2NHRQ, yes, twice, first one updates the contract revenue and commission, then the second runs ensures the gross and net revenue calculate
@deleted-U072JS2NHRQ, send me the LP Lead ID please
Just as a heads up, I see that you or someone else using financial reporting updated these leads to test lead dispos, but they're still showing up in the lambda reporting and the sql query that I run.
What happens if the Lambda fails @Ryan? Should I just run it again? idk if it's a big deal or not
the Lambda said it succeeded, but every row was an error so I don't know if it worked or not, it just seems suspicious so I wanted to let you know either way
It’s poor wording by the developer, that’s just showing you that those are duplicates and not to be inserted again
Morning Aidan. FYI, I spoke to Cameron, Tony and awaiting Brittany's response but I did get one more TIP daily routine task that takes about 15 minutes similar to the S1s. Updating the financial log table statuses to their respective decline status if declined after post e-signing and it does not pass final QA.
I created a report to deliver at 4AM and have some more SQL tricks to show you to create a quick look up of these statuses to update their value in the table.
I'll be out in London all next week so maybe we can meet today and I can show you how to do these?
Actually, I will do a Loom video of me doing it right now for you.
Great, we are recording now. Note that the report will show up via email, so you won't have to export manually from LawRuler
@deleted-U072JS2NHRQ, now this video shows me doing an "ok" job at the front-end to confirm if they are "Signed & DECLINED" or if "Billable to Firm Declined" or "...Client Opt Out". You should filter the https://docs.google.com/spreadsheets/d/1of10KUMcKX-pYj7U9MRIw_D7paNvOVwMFbtORtJM9bI/edit?gid=1893613719#gid=1893613719&range=A:B|sheet and update the SQL formulas to do them in small batches by each of those 3 statuses:
https://www.loom.com/share/a7ea348491224eea8ce40b277b983ae0
```SELECT signeddate,currentstatus,parentcasetype,typeofcase,lpleads1, firstname, lastname, lpleadid, lrleadid,** FROM public.financial_log WHERE ( -- CUT AND PASTE THE NEW ONES FROM THE TIP SQL TOOLS SHEET BELOW HERE
-- DO NOT DELETE THE BRACKETS
lrleadid = '101307' OR lrleadid = '101534' OR lrleadid = '106011' OR lrleadid = '106429' OR lrleadid = '106743' OR lrleadid = '109049' OR lrleadid = '109069' OR lrleadid = '109266' ) ORDER BY signed_date ASC```
Then I need the declined lead IDs so I can remove from SL financials.
@deleted-U072JS2NHRQ, the two lead IDs from yesterday from Chowchilla - ACTS/DL - Flatirons - Shield Legal, are all for Maximus, please be careful to not allocate to people that are not showing up in the rows with S1s for a given case type
Not sure how Jack got a lead, but I updated them already to be ML
Not sure how Jack got one, but I will update it to be JA
you can always check the dash and see who is getting them on prior days from yesterday too to guide who is "spending money on those".
I left it off, and I did declines last night on the plane, so there would be none today.
And yes, you post in #C022831MSUF, "No Declines" to me
Good morning. Would you mind waiting to 8:45AM to do the S1s?
Want to see if that helps prevent us losing them from the automatic run at 8:30 am?
I already ran it, and did the allocations. Wait till 845 before you post the slack alerts, just to see if the automatic run at 8:30 ruins what I’ve done
Excellent. @deleted-U072JS2NHRQ, I think we can run it at 6:00 AM to 6:30AM each day, and then do the S1 and Contract ID allocations and check at 8:45 to make sure we didn't lose them.
We can make up on Monday and figure it out. Enjoy your weekend.
Morning @deleted-U072JS2NHRQ , you get Declines report this morning? I see it.
That’s fine, I got confused and today had no S1s.
@deleted-U072JS2NHRQ, think I solved the problem with our S1s disappearing. Our AWS Lambda was running too soon before our data showed up, so that is why we would get "new rows" in your 6:30 AM PDT run and hopefully no more "missing rows" and no more "S1 overwrites".
@deleted-U072JS2NHRQ, sounds good. Probably just deletes this AM with only five deals.
@deleted-U072JS2NHRQ, that is supposed to be a Ben Crump BLX case, the lead is an organic lead from BLX.
@deleted-U072JS2NHRQ, hold on a moment. Not all sign ups came over. Let me check a few things in AWS.
So once before LawRuler corrupted their own reports. The 3:45AM PDT report only had 39 rows in it so that is obviously not correct. So I deleted the Scheduled Report and recreated it plus manually uploaded the correct one. Rerunning TIP Generate Fin Log lambda now, fingers crossed.
We should have 85 or so sign ups from yesterday and our the fin_log table only showed "Dates Inserted" of 8-19-2024 when we should have 8-20-2024 in there as well from this mornings run.
@deleted-U072JS2NHRQ, now you can do the S1s and Contract IDs, we have 90 like we are supposed too...no wonder we were getting deals late in the day compared to the earlier part of the day from the day before .
This may fix the need to do the Lambda run each morning. We only started doing that recently and now we know why we had to do it. Let me know each morning when you run it. Know the count in the table before you run and after and let me know if that changes.
Great. Finally we figured it out, man. I was going crazy seeing the "Yesterday" sign ups differ later in the day (meaning our 3:00PM) report was not corrupted so it ran correctly.
@deleted-U072JS2NHRQ, update lead id 575433 to "PL-FacebookMB-MLlumen34" in slack, I updated finlog, you used an Asbestos S1 on a CA Juve Hall. Make sure your allocate only amongst the "Parent Case Types".
Look in the #C07GYEQ8H7T channel, scroll up and see who's initials have been getting deals the few days before.
@deleted-U072JS2NHRQ, excellent. I know I fixed the issue. LawRuler Status Reports got corrupted. Let Brittany, Alan and Tony know. If you open them up and they look like they do in the video, got to recreate it.
none of the people are showing up, it's like this: and there should be WAY more than 3 for 8.27.24
As long as they spent on them. Look at yesterday for that case type. Follow that insight to confirm.
I did the S1s and Contract IDs, just post to channels please @deleted-U072JS2NHRQ, I have today off so I started early
Great, send Declines soon after that, I am ready on the Shield side for Cam already.
Yes, test. You can always look for an actual retainer signed too in the LR documents section
In the morning, when you get any TEST leads, don't delete them from the fin_log table, just mark them "Signed & DECLINED" so they are not put back in later by accident.
We got a 1.3MB LR Status Report, usually about 700KB, so it might be timing out trying to run through 6500 rows. Stand by.
@deleted-U072JS2NHRQ, still working on it, stay tuned
@deleted-U072JS2NHRQ, it finally finished but running ONE more time for good measure
Not sure how so many got sent, will keep an eye on it but it has 6700 rows versus the typical 3000 rows. The Lambda times out after 00:08:30
But it picks up where it left off, so I just kept re-running it and checking dashboard
Ready, I did the S1 allocation, please do Contract IDs and posts of referrals, ty @deleted-U072JS2NHRQ
Maybe they did a mass bulk status change on several rows.
That would cause "status report" to have nearly triple the rows
No, the media buys have not run VGA since July so it's been all catch up on thousands of leads.
ok deal, I did this part too prior to running the 'get log rows' portion of case reviews, e-sign, and secondary interview stuff
I am updating this SQL for September, and will get clarification of "billable statuses" from Alan, it appears to have changed again without notice to me.
I'll get September loaded in about one more minute or so
Then you can do 9/1 to 9/15 Secondary Interview Talk Time
@deleted-U072JS2NHRQ, I have updated the SQL to account for all ASA billable statuses, we are good for September 2024 through the 15th. You good to update Talk Time Revenue now?
```-- ASA Services
-- Routine
-- #1 GET rows from status report to COUNT total SELECT ** FROM rawstatusreport WHERE (currentstatus = 'Secondary Interview - ASA' OR currentstatus = 'Secondary Interview - Final' OR currentstatus = 'Secondary Interview - Sent' OR currentstatus = 'Secondary Interview - Completed' OR currentstatus = 'HIPAA Signed' OR currentstatus = 'VGA 2nd Interview Completed - Tier 1' OR currentstatus = 'VGA 2nd Interview Completed - Tier 2' OR currentstatus = 'VGA 2nd Interview Completed - Tier 3') AND date_inserted >= '2024-09-01 00:00:00';
-- #2 Update Raw Status Report with Signed e-Sign string UPDATE rawstatusreport SET currentstatus = 'Signed e-Sign ' || currentstatus WHERE (currentstatus = 'Secondary Interview - ASA' OR currentstatus = 'Secondary Interview - Final' OR currentstatus = 'Secondary Interview - Sent' OR currentstatus = 'Secondary Interview - Completed' OR currentstatus = 'HIPAA Signed' OR currentstatus = 'VGA 2nd Interview Completed - Tier 1' OR currentstatus = 'VGA 2nd Interview Completed - Tier 2' OR currentstatus = 'VGA 2nd Interview Completed - Tier 3') AND date_inserted >= '2024-09-01 00:00:00';
-- #3 RUN Generate Fin Log Lambda to process #2
-- #4 Update Fin Log table to revert back to actual status for ASA UPDATE public.financiallog SET currentstatus = REPLACE(currentstatus, 'Signed e-Sign ', '') WHERE (currentstatus LIKE '%Secondary Interview - ASA' OR currentstatus LIKE '%Secondary Interview - Final' OR currentstatus LIKE '%Secondary Interview - Sent' OR currentstatus LIKE 'Secondary Interview - Completed' OR currentstatus LIKE '%HIPAA Signed' OR currentstatus LIKE '%VGA 2nd Interview Completed - Tier 1' OR currentstatus LIKE '%VGA 2nd Interview Completed - Tier 2' OR currentstatus LIKE '%VGA 2nd Interview Completed - Tier 3') AND dateinserted >= '2024-09-01 00:00:00'
-- #5 & #8 GET rows with updated status to validate revenue update below and CHECK rows after #7 SELECT ** FROM public.financiallog WHERE (currentstatus LIKE '%Secondary Interview - ASA' OR currentstatus LIKE '%Secondary Interview - Final' OR currentstatus LIKE '%Secondary Interview - Sent' OR currentstatus LIKE 'Secondary Interview - Completed' OR currentstatus LIKE '%HIPAA Signed' OR currentstatus LIKE '%VGA 2nd Interview Completed - Tier 1' OR currentstatus LIKE '%VGA 2nd Interview Completed - Tier 2' OR currentstatus LIKE '%VGA 2nd Interview Completed - Tier 3') AND dateinserted >= '2024-09-01 00:00:00' ORDER BY signed_date DESC;
--#7 UPDATE LOG ROWS --MUST RUN TWICE!!! UPDATE public.financiallog SET contractrevenue = '100', agentcommission = '0', grossrevenue = contractrevenue + talktimerevenue, netrevenue = contractrevenue + talktimerevenue, talktimeexpense = '0' WHERE (currentstatus LIKE '%Secondary Interview - ASA' OR currentstatus LIKE '%Secondary Interview - Final' OR currentstatus LIKE '%Secondary Interview - Sent' OR currentstatus LIKE 'Secondary Interview - Completed' OR currentstatus LIKE '%HIPAA Signed' OR currentstatus LIKE '%VGA 2nd Interview Completed - Tier 1' OR currentstatus LIKE '%VGA 2nd Interview Completed - Tier 2' OR currentstatus LIKE '%VGA 2nd Interview Completed - Tier 3') AND dateinserted >= '2024-09-01 00:00:00';```
Of course, and again just do the Talk Time Revenue part, I did that entire set of SQL expressions for September.
I did all of that sequel code, for now I just need you to make sure that we get the talk time revenue spread across those so you would use the bottom half of that sequel, to select the existing rose to know what to update their revenue too. Does that make sense?
We can hop on call tomorrow and review the SQL for talk time revenue JIC
Over and over. I am back filling the missing ASA leads
We should be almost done. It will complete green when done. We had 10K rows to process, in 8 minutes it can scan about 3000 rows and process about 300 to 400 leads into log.
[INFO] 2024-09-17T14:50:15.303Z 0c292936-ea3d-4e05-9673-99fa9f7479aa #4341/4908:
It's starting to fill in the sign ups, so we are close. I started this last night but had to go to kid sports practices.
I found TONS of unbilled ASA stuff. :facepalm::skintone_4:
Same issue, we started with 6018 status changes yesterday. I meet with the engineer, I'll ask him to double the timeout processing.
And watch this to see how you can sorta monitor it from my limited knowledge of AWS Lambdas:
So you can see in this video, it fails at 8:30:00 usually if we have more than 3500 rows to process. This one started at 6400-ish and seems to process about 400 to 300 rows so each subsequent run is working. After it completes successfully @deleted-U072JS2NHRQ, run it one more time for good measure. It's not an intelligent script, it just processes sequentially and I keep seeing weird deals pop up later here and there, so just get 2 "Success" :largegreencircle:
@deleted-U072JS2NHRQ, it worked. Running one more time for confidence.
@deleted-U072JS2NHRQ , updated and improved code to set contract revenue by the case type, use this one next time you run it and moving forward:
```-- ASA Services Revenue Update
-- Revision Date: 9/16/2024 11:54 AM CDT -- Revision Notes: -- Added all billable statuses per Alan Gill on 9/16/2024
-- #1 GET rows from status report to COUNT total SELECT ** FROM rawstatusreport WHERE (currentstatus = 'Secondary Interview - ASA' OR currentstatus = 'Secondary Interview - Final' OR currentstatus = 'Secondary Interview - Sent' OR currentstatus = 'Secondary Interview - Completed' OR currentstatus = 'HIPAA Signed' OR currentstatus = 'VGA 2nd Interview Completed - Tier 1' OR currentstatus = 'VGA 2nd Interview Completed - Tier 2' OR currentstatus = 'VGA 2nd Interview Completed - Tier 3') AND dateinserted >= '2024-09-01 00:00:00' AND ingestedbyfinlog = false
-- #2 Update Raw Status Report with Signed e-Sign string UPDATE rawstatusreport SET currentstatus = 'Signed e-Sign ' || currentstatus WHERE (currentstatus = 'Secondary Interview - ASA' OR currentstatus = 'Secondary Interview - Final' OR currentstatus = 'Secondary Interview - Sent' OR currentstatus = 'Secondary Interview - Completed' OR currentstatus = 'HIPAA Signed' OR currentstatus = 'VGA 2nd Interview Completed - Tier 1' OR currentstatus = 'VGA 2nd Interview Completed - Tier 2' OR currentstatus = 'VGA 2nd Interview Completed - Tier 3') AND dateinserted >= '2024-09-01 00:00:00' AND ingestedbyfinlog = false
-- #3 RUN Generate Fin Log Lambda to process #2
-- #4 Update Fin Log table to revert back to actual status for ASA UPDATE public.financiallog SET currentstatus = REPLACE(currentstatus, 'Signed e-Sign ', '') WHERE (currentstatus LIKE '%Secondary Interview - ASA' OR currentstatus LIKE '%Secondary Interview - Final' OR currentstatus LIKE '%Secondary Interview - Sent' OR currentstatus LIKE '%Secondary Interview - Completed' OR currentstatus LIKE '%HIPAA Signed' OR currentstatus LIKE '%VGA 2nd Interview Completed - Tier 1' OR currentstatus LIKE '%VGA 2nd Interview Completed - Tier 2' OR currentstatus LIKE '%VGA 2nd Interview Completed - Tier 3') AND signeddate >= '2024-09-01 00:00:00';
-- #5 & #8 GET rows with updated status to validate revenue update below and CHECK rows after #7 SELECT ** FROM public.financiallog WHERE (currentstatus LIKE '%Secondary Interview - ASA' OR currentstatus LIKE '%Secondary Interview - Final' OR currentstatus LIKE '%Secondary Interview - Sent' OR currentstatus LIKE 'Secondary Interview - Completed' OR currentstatus LIKE '%HIPAA Signed' OR currentstatus LIKE '%VGA 2nd Interview Completed - Tier 1' OR currentstatus LIKE '%VGA 2nd Interview Completed - Tier 2' OR currentstatus LIKE '%VGA 2nd Interview Completed - Tier 3') AND signeddate >= '2024-09-01 00:00:00' ORDER BY signed_date DESC;
--#7 UPDATE LOG ROWS --MUST RUN TWICE!!! UPDATE public.financiallog SET contractrevenue = CASE WHEN typeofcase = 'Gaming Addiction - Meadow Law - SEC3 - Meadow Law' THEN 50 WHEN typeofcase = 'Gaming Addiction - Meadow Law - SEC2 - Meadow Law' THEN 100 WHEN typeofcase = 'Camp Lejeune Intake - Dicello - Form 95 2024 - Dicello Levitt' THEN 100 WHEN typeofcase = 'Camp Lejeune Intake - DL (B) - Form 95 2024 - Dicello Levitt' THEN 100 WHEN typeofcase = 'Archived New Form 95' THEN 100 WHEN typeofcase = 'New New Form 95' THEN 100 WHEN typeofcase = 'Archived Dicello Form 95' THEN 100 WHEN typeofcase = 'Form 95 EPIQ 2' THEN 100 WHEN typeofcase = 'Form 95 EPIQ 3' THEN 100 WHEN typeofcase = 'Form 95 EPIQ' THEN 100 WHEN typeofcase = 'CPAP Census' THEN 150 WHEN typeofcase = 'Gaming Addiction - Meadow Law - SEC1 - Meadow Law' THEN 175 WHEN typeofcase = 'Paraquat e-Sign' THEN 225 WHEN typeofcase = 'Paraquat - Dicello Filing Project' THEN 225 WHEN typeofcase = 'Hair Relaxer PFS - Dicello Levitt' THEN 325 WHEN typeofcase = 'Chowchilla - Van - Long Form Interview' THEN 410 WHEN typeofcase = 'Lakin Sex Abuse PA 2nd Interview' THEN 450 WHEN typeofcase = 'Lakin/AKG Sex Abuse PA 2nd Interview' THEN 450 WHEN typeofcase = 'BG - Sex Abuse - Illinois' THEN 450 WHEN typeofcase = 'BG - Sex Abuse - Maryland' THEN 450 WHEN typeofcase = 'BG - Sex Abuse - Michigan' THEN 450 WHEN typeofcase = 'BG - Sex Abuse - New Hampshire' THEN 450 WHEN typeofcase = 'BG - Sex Abuse - New Jersey' THEN 450 WHEN typeofcase = 'BG - Sex Abuse - Pennsylvania' THEN 450 WHEN typeofcase = 'DL - Flatirons - Sex Abuse - IL' THEN 450 WHEN typeofcase = 'DL - Flatirons - Sex Abuse - MD' THEN 450 WHEN typeofcase = 'DL - Flatirons - Sex Abuse - NH' THEN 450 WHEN typeofcase = 'DL - Flatirons - Sex Abuse - NJ' THEN 450 WHEN typeofcase = 'DL - Flatirons - Sex Abuse - PA' THEN 450 WHEN typeofcase = 'DL/Flatirons - Sex Abuse - New Hampshire' THEN 450 WHEN typeofcase = 'Arizona Juvenile Detention Center - ML - Secondary Interview' THEN 450 WHEN typeofcase = 'NJ Sex Abuse SEC - DL - TC' THEN 450 ELSE contractrevenue END, agentcommission = 0, -- No agentcommission specified in your dataset grossrevenue = contractrevenue + talktimerevenue, netrevenue = contractrevenue + talktimerevenue, talktimeexpense = 0 WHERE typeofcase IN ( 'Gaming Addiction - Meadow Law - SEC3 - Meadow Law', 'Gaming Addiction - Meadow Law - SEC2 - Meadow Law', 'Camp Lejeune Intake - Dicello - Form 95 2024 - Dicello Levitt', 'Camp Lejeune Intake - DL (B) - Form 95 2024 - Dicello Levitt', 'Archived New Form 95', 'New New Form 95', 'Archived Dicello Form 95', 'Form 95 EPIQ 2', 'Form 95 EPIQ 3', 'Form 95 EPIQ', 'CPAP Census', 'Gaming Addiction - Meadow Law - SEC1 - Meadow Law', 'Paraquat e-Sign', 'Paraquat - Dicello Filing Project', 'Hair Relaxer PFS - Dicello Levitt', 'Chowchilla - Van - Long Form Interview', 'Lakin Sex Abuse PA 2nd Interview', 'Lakin/AKG Sex Abuse PA 2nd Interview', 'BG - Sex Abuse - Illinois', 'BG - Sex Abuse - Maryland', 'BG - Sex Abuse - Michigan', 'BG - Sex Abuse - New Hampshire', 'BG - Sex Abuse - New Jersey', 'BG - Sex Abuse - Pennsylvania', 'DL - Flatirons - Sex Abuse - IL', 'DL - Flatirons - Sex Abuse - MD', 'DL - Flatirons - Sex Abuse - NH', 'DL - Flatirons - Sex Abuse - NJ', 'DL - Flatirons - Sex Abuse - PA', 'DL/Flatirons - Sex Abuse - New Hampshire', 'Arizona Juvenile Detention Center - ML - Secondary Interview', 'NJ Sex Abuse SEC - DL - TC' ) AND signeddate >= '2024-09-01 00:00:00';
```
Just put signed and decline, do not delete it. The getting put back in there with the old status. Just changed to signed and declined.
@deleted-U072JS2NHRQ, updated Post Retainer SQL to import rows with new-statuses (not in the python code) and price them correctly each Monday after we get the Connex One call time revenue updated. Only #7 query was updated with new information. And we'll hopefully not need this query for much longer.
```-- ASA Services Revenue Update
-- Revision Date: 9/16/2024 11:54 AM CDT -- Revision Notes: -- Added all billable statuses per Alan Gill on 9/16/2024
-- #1 GET rows from status report to COUNT total SELECT ** FROM rawstatusreport WHERE (currentstatus = 'Secondary Interview - ASA' OR currentstatus = 'Secondary Interview - Final' OR currentstatus = 'Secondary Interview - Sent' OR currentstatus = 'Secondary Interview - Completed' OR currentstatus = 'HIPAA Signed' OR currentstatus = 'VGA 2nd Interview Completed - Tier 1' OR currentstatus = 'VGA 2nd Interview Completed - Tier 2' OR currentstatus = 'VGA 2nd Interview Completed - Tier 3') AND dateinserted >= '2024-09-01 00:00:00' AND ingestedbyfinlog = false
-- #2 Update Raw Status Report with Signed e-Sign string UPDATE rawstatusreport SET currentstatus = 'Signed e-Sign ' || currentstatus WHERE (currentstatus = 'Secondary Interview - ASA' OR currentstatus = 'Secondary Interview - Final' OR currentstatus = 'Secondary Interview - Sent' OR currentstatus = 'Secondary Interview - Completed' OR currentstatus = 'HIPAA Signed' OR currentstatus = 'VGA 2nd Interview Completed - Tier 1' OR currentstatus = 'VGA 2nd Interview Completed - Tier 2' OR currentstatus = 'VGA 2nd Interview Completed - Tier 3') AND dateinserted >= '2024-09-01 00:00:00' AND ingestedbyfinlog = false
-- #3 RUN Generate Fin Log Lambda to process #2
-- #4 Update Fin Log table to revert back to actual status for ASA UPDATE public.financiallog SET currentstatus = REPLACE(currentstatus, 'Signed e-Sign ', '') WHERE (currentstatus LIKE '%Secondary Interview - ASA' OR currentstatus LIKE '%Secondary Interview - Final' OR currentstatus LIKE '%Secondary Interview - Sent' OR currentstatus LIKE '%Secondary Interview - Completed' OR currentstatus LIKE '%HIPAA Signed' OR currentstatus LIKE '%VGA 2nd Interview Completed - Tier 1' OR currentstatus LIKE '%VGA 2nd Interview Completed - Tier 2' OR currentstatus LIKE '%VGA 2nd Interview Completed - Tier 3') AND signeddate >= '2024-09-01 00:00:00';
-- #5 & #8 GET rows with updated status to validate revenue update below and CHECK rows after #7 SELECT ** FROM public.financiallog WHERE (currentstatus LIKE '%Secondary Interview - ASA' OR currentstatus LIKE '%Secondary Interview - Final' OR currentstatus LIKE '%Secondary Interview - Sent' OR currentstatus LIKE 'Secondary Interview - Completed' OR currentstatus LIKE '%HIPAA Signed' OR currentstatus LIKE '%VGA 2nd Interview Completed - Tier 1' OR currentstatus LIKE '%VGA 2nd Interview Completed - Tier 2' OR currentstatus LIKE '%VGA 2nd Interview Completed - Tier 3') AND signeddate >= '2024-09-01 00:00:00' ORDER BY signed_date DESC;
--#7 UPDATE LOG ROWS
--MUST RUN TWICE!!!
UPDATE public.financiallog
SET contractrevenue = CASE
WHEN typeofcase = 'Gaming Addiction - Meadow Law - SEC1 - Meadow Law' THEN 175
WHEN typeofcase = 'Gaming Addiction - Meadow Law - SEC3 - Meadow Law' THEN 50
WHEN typeofcase = 'Gaming Addiction - Meadow Law - SEC2 - Meadow Law' THEN 100
WHEN typeofcase = 'Gaming Addiction - Meadow Law - SEC - Meadow Law' AND currentstatus = 'VGA 2nd Interview Completed - Tier 1' THEN 175
WHEN typeofcase = 'Gaming Addiction - Meadow Law - SEC - Meadow Law' AND currentstatus = 'VGA 2nd Interview Completed - Tier 2' THEN 100
WHEN typeofcase = 'Gaming Addiction - Meadow Law - SEC - Meadow Law' AND currentstatus = 'VGA 2nd Interview Completed - Tier 3' THEN 50
WHEN typeofcase = 'Camp Lejeune Intake - Dicello - Form 95 2024 - Dicello Levitt' THEN 100
WHEN typeofcase = 'Camp Lejeune Intake - DL (B) - Form 95 2024 - Dicello Levitt' THEN 100
WHEN typeofcase = 'Archived New Form 95' THEN 100
WHEN typeofcase = 'New New Form 95' THEN 100
WHEN typeofcase = 'Archived Dicello Form 95' THEN 100
WHEN typeofcase = 'Form 95 EPIQ 2' THEN 100
WHEN typeofcase = 'Form 95 EPIQ 3' THEN 100
WHEN typeofcase = 'Form 95 EPIQ' THEN 100
WHEN typeofcase = 'CPAP Census' THEN 150
WHEN typeofcase = 'Paraquat e-Sign' THEN 225
WHEN typeofcase = 'Paraquat - Dicello Filing Project' THEN 225
WHEN typeofcase = 'Hair Relaxer PFS - Dicello Levitt' THEN 325
WHEN typeofcase = 'Chowchilla - Van - Long Form Interview' THEN 410
WHEN typeofcase = 'Lakin Sex Abuse PA 2nd Interview' THEN 450
WHEN typeofcase = 'Lakin/AKG Sex Abuse PA 2nd Interview' THEN 450
WHEN typeofcase = 'BG - Sex Abuse - Illinois' THEN 450
WHEN typeofcase = 'BG - Sex Abuse - Maryland' THEN 450
WHEN typeofcase = 'BG - Sex Abuse - Michigan' THEN 450
WHEN typeofcase = 'BG - Sex Abuse - New Hampshire' THEN 450
WHEN typeofcase = 'BG - Sex Abuse - New Jersey' THEN 450
WHEN typeofcase = 'BG - Sex Abuse - Pennsylvania' THEN 450
WHEN typeofcase = 'DL - Flatirons - Sex Abuse - IL' THEN 450
WHEN typeofcase = 'DL - Flatirons - Sex Abuse - MD' THEN 450
WHEN typeofcase = 'DL - Flatirons - Sex Abuse - NH' THEN 450
WHEN typeofcase = 'DL - Flatirons - Sex Abuse - NJ' THEN 450
WHEN typeofcase = 'DL - Flatirons - Sex Abuse - PA' THEN 450
WHEN typeofcase = 'DL/Flatirons - Sex Abuse - New Hampshire' THEN 450
WHEN typeofcase = 'Arizona Juvenile Detention Center - ML - Secondary Interview' THEN 450
WHEN typeofcase = 'NJ Sex Abuse SEC - DL - TC' THEN 450
--NEW LINES AS OF 10/3/24
WHEN typeofcase = 'ACTS Adult VSS Documents' THEN 425
WHEN typeofcase = 'ACTS Fact Sheet' THEN 425
WHEN typeofcase = 'ACTS Juv VSS Documents' THEN 425
WHEN typeofcase = 'Hair Relaxer Complaint PFS - DL Flatirons' THEN 425
WHEN typeofcase = 'VGA Secondary Interview - ML - ML' AND currentstatus = 'VGA 2nd Interview Completed - Tier 1' THEN 175
WHEN typeofcase = 'VGA Secondary Interview - ML - ML' AND currentstatus = 'VGA 2nd Interview Completed - Tier 2' THEN 100
WHEN typeofcase = 'VGA Secondary Interview - ML - ML' AND currentstatus = 'VGA 2nd Interview Completed - Tier 3' THEN 50
WHEN typeofcase = 'Illinois - BG/DL Conflict Waiver' THEN 100
ELSE contractrevenue
END,
agentcommission = 0, -- No agentcommission specified in your dataset
grossrevenue = contractrevenue + talktimerevenue,
netrevenue = contractrevenue + talktimerevenue,
talktimeexpense = 0
WHERE typeofcase IN (
'Gaming Addiction - Meadow Law - SEC - Meadow Law',
'Gaming Addiction - Meadow Law - SEC1 - Meadow Law',
'Gaming Addiction - Meadow Law - SEC3 - Meadow Law',
'Gaming Addiction - Meadow Law - SEC2 - Meadow Law',
'Camp Lejeune Intake - Dicello - Form 95 2024 - Dicello Levitt',
'Camp Lejeune Intake - DL (B) - Form 95 2024 - Dicello Levitt',
'Archived New Form 95',
'New New Form 95',
'Archived Dicello Form 95',
'Form 95 EPIQ 2',
'Form 95 EPIQ 3',
'Form 95 EPIQ',
'CPAP Census',
'Paraquat e-Sign',
'Paraquat - Dicello Filing Project',
'Hair Relaxer PFS - Dicello Levitt',
'Chowchilla - Van - Long Form Interview',
'Lakin Sex Abuse PA 2nd Interview',
'Lakin/AKG Sex Abuse PA 2nd Interview',
'BG - Sex Abuse - Illinois',
'BG - Sex Abuse - Maryland',
'BG - Sex Abuse - Michigan',
'BG - Sex Abuse - New Hampshire',
'BG - Sex Abuse - New Jersey',
'BG - Sex Abuse - Pennsylvania',
'DL - Flatirons - Sex Abuse - IL',
'DL - Flatirons - Sex Abuse - MD',
'DL - Flatirons - Sex Abuse - NH',
'DL - Flatirons - Sex Abuse - NJ',
'DL - Flatirons - Sex Abuse - PA',
'DL/Flatirons - Sex Abuse - New Hampshire',
'Arizona Juvenile Detention Center - ML - Secondary Interview',
'NJ Sex Abuse SEC - DL - TC'
--NEW LINES AS OF 10/3/24
'ACTS Adult VSS Documents',
'ACTS Fact Sheet',
'ACTS Juv VSS Documents',
'Hair Relaxer Complaint PFS - DL Flatirons',
'VGA Secondary Interview - ML - ML',
'Illinois - BG/DL Conflict Waiver'
)
AND signeddate >= '2024-09-01 00:00:00';```
*Thread Reply:* @deleted-U072JS2NHRQ, you can discontinue running this script in on Monday mornings. I am going to work with Alan and Malissa to do these at EOM.
@deleted-U072JS2NHRQ, change this one to Signed & DECLINED: 598142
@deleted-U072JS2NHRQ, make 599838 Signed & DECLINED too.
@deleted-U072JS2NHRQ, next Monday, just do the Dialer expenses after morning routines on S1s, slack alerts, the status changes for ASA I will handle, it's gotten time to clean this up with the ASA team.
Hey Aidan, since technically I am on the Shield side of the companies, go to Shelly and Tony together, and just let Alan know you are going to request a comp review. That just professional advice.
From your skills perspective, SQL, LookML (Looker SQL-esque code to setup data connectors) I think having you and Edward be the Analysts for the TIP Dashboards for Tony, Alan, Brianna, Brittany, etc, would be great since you both have intimate knowledge.
From a engineering perspective, let me run this by the Integrations team leaders on our Monday tech meeting. We have Five9 dialer data pipes, models and visuals to build soon!
Hey @Ryan when I run the query for yesterday's e-signs it only pulls 1 lead. I wanted to let you know just in case:
We should have about 50+, use this Slack search to see them: S1: before:2024-10-11 after:2024-10-09
@deleted-U072JS2NHRQ, yep, the LR Status Report did not show up from LawRuler via email. Uploadiong now, I'll let you know when you can re-run the Lambda and we should see plenty pop into the db
How many of those are they? Can you look in Law Ruler and make sure those are signed retainer deals, I don’t see a channel for them and I’ll have to check once I’m at the airport gate in about 30 minutes if there’s even a contract for it? Can you direct message MarK Maniora and inquire about that?
OK, just leave them as is. I don’t know if shields being billed for these, or we should rename this case type to have the billing source be DiCello
@deleted-U072JS2NHRQ, and update the AND statement to this:
AND (current_status LIKE '%Secondary Interview - ASA'
OR current_status LIKE '%Secondary Interview - Final'
OR current_status LIKE '%Secondary Interview - Sent'
OR current_status LIKE 'Secondary Interview - Completed'
OR current_status LIKE '%HIPAA Signed'
OR current_status LIKE '%VGA 2nd Interview Completed - Tier 1'
OR current_status LIKE '%VGA 2nd Interview Completed - Tier 2'
OR current_status LIKE '%VGA 2nd Interview Completed - Tier 3')
This will ensure we spread the ASA calls out better from CNX1
Don't do any of that, I already did the Lead Import and Pricing, you are just updating the Talk Time Revenue
Those prices are getting updated, changed, corrected, reconciled, etc, so I am handling it since I am having them push these into the new TIP App to store pricing.
Is the Talk Time Revenue Part only the top half of this and then putting it in the google sheet? Just wanna make sure
@deleted-U072JS2NHRQ, just to be safe,
use this version, I updated it to update the TT rev for the new "billable statuses" on ASA. Just update your dates.
```--CASE REVIEWS SECTION SELECT signeddate, lpleads1,currentstatus,** FROM public.financiallog WHERE signeddate BETWEEN '2024-07-01' AND '2024-07-07' AND currentstatus LIKE 'Case Review%' ORDER BY signeddate ASC
UPDATE public.financiallog SET talktimerevenue = (talktimerevenue+'28.37209302'), grossrevenue = (grossrevenue+'28.37209302'), netrevenue = (netrevenue+'28.37209302'), talktimeminutes = (talktimeminutes+'14.18604651') WHERE signeddate BETWEEN '2024-07-01' AND '2024-07-07' AND current_status LIKE 'Case Review%'
--ESIGNS SECTIONS SELECT signeddate, lpleads1,currentstatus,** FROM public.financiallog WHERE signeddate BETWEEN '2024-07-01' AND '2024-07-07' AND currentstatus LIKE 'Signed e-Sign%' ORDER BY signeddate ASC
UPDATE public.financiallog SET talktimerevenue = (talktimerevenue+'83.47985507'), grossrevenue = (grossrevenue+'83.47985507'), netrevenue = (netrevenue+'83.47985507'), talktimeminutes = (talktimeminutes+'41.73992754') WHERE signeddate BETWEEN '2024-07-01' AND '2024-07-07' AND current_status LIKE 'Signed e-Sign%'
--SECONDARY INTERVIEW SECTION SELECT signeddate, lpleads1,currentstatus,** FROM public.financiallog WHERE signeddate BETWEEN '2024-07-01' AND '2024-07-07' AND (currentstatus = 'Secondary Interview - ASA' OR currentstatus = 'Secondary Interview - Final' OR currentstatus = 'Secondary Interview - Sent' OR currentstatus = 'Secondary Interview - Completed' OR currentstatus = 'HIPAA Signed' OR currentstatus = 'VGA 2nd Interview Completed - Tier 1' OR currentstatus = 'VGA 2nd Interview Completed - Tier 2' OR currentstatus = 'VGA 2nd Interview Completed - Tier 3') ORDER BY signed_date ASC;
UPDATE public.financiallog SET talktimerevenue = (talktimerevenue+'0'), grossrevenue = (grossrevenue+'0'), netrevenue = (netrevenue+'0'), talktimeminutes = (talktimeminutes+'0') WHERE signeddate BETWEEN '2024-07-01' AND '2024-07-07' AND (currentstatus = 'Secondary Interview - ASA' OR currentstatus = 'Secondary Interview - Final' OR currentstatus = 'Secondary Interview - Sent' OR currentstatus = 'Secondary Interview - Completed' OR currentstatus = 'HIPAA Signed' OR currentstatus = 'VGA 2nd Interview Completed - Tier 1' OR currentstatus = 'VGA 2nd Interview Completed - Tier 2' OR currentstatus = 'VGA 2nd Interview Completed - Tier 3');```
Lead ID 606513 is Bard? https://tortintakeprofessionals.lawruler.com/update-lead.aspx?lead=606513
@deleted-U072JS2NHRQ, this one was a referral, mark it PL-FacebookMB-CP_
We don't have a pure DL - DL contract, so it's the 2nd one I guess. You may ask Tony to confirm if it's under the wrong Case Type in LawRuler though and let me know.
[Parent Case Type Name] [Service Code or Leadspedia Contract ID] - [Client Firm] - [Partner Firm(s)] - [Billing Source]
It appears to NOT be a "billable lead from Shield Legal", so it SHOULD NOT have a case type with "Shield Legal" in the [BILLING SOURCE] segment of the Case Type name. It should be - Dicello Levitt.
I'll skip it for now and mark the Billing Source as Dicello Levitt until we confirm what to do with it.
*Thread Reply:* When I come up for air, I'll address this. We do and I can use it as a testing ground for something.
*Thread Reply:* Aidan, I want to setup you on DBT for TIP Data Warehouse and have you help me start creating Looker Enterprise dashboards. Confirm some times you are free this week for an hour and we can work on setting this up.
But wait to do it until I let you know I've imported those rows, give me a bit. Have some meetings.
Yea, I'm going to add some Post Retainers into it, so redo when I let you know
@deleted-U072JS2NHRQ, ok, I just imported about 150 new ASAs so you can "redo" the talk time minutes and revenue to all of them for last week.
@deleted-U072JS2NHRQ , need to update the WHERE clause with the status med from the SELECT clause.
Got to update all those statuses we count from the where clauses
@deleted-U072JS2NHRQ, like this one:
```--SECONDARY INTERVIEW SECTION SELECT signeddate, lpleads1,currentstatus,** FROM public.financiallog WHERE signeddate BETWEEN '2024-07-01' AND '2024-07-07' AND (currentstatus = 'Secondary Interview - ASA' OR currentstatus = 'Secondary Interview - Final' OR currentstatus = 'Secondary Interview - Sent' OR currentstatus = 'Secondary Interview - Completed' OR currentstatus = 'HIPAA Signed' OR currentstatus = 'VGA 2nd Interview Completed - Tier 1' OR currentstatus = 'VGA 2nd Interview Completed - Tier 2' OR currentstatus = 'VGA 2nd Interview Completed - Tier 3') ORDER BY signed_date ASC;
UPDATE public.financiallog SET talktimerevenue = (talktimerevenue+'0'), grossrevenue = (grossrevenue+'0'), netrevenue = (netrevenue+'0'), talktimeminutes = (talktimeminutes+'0') WHERE signeddate BETWEEN '2024-07-01' AND '2024-07-07' AND (currentstatus = 'Secondary Interview - ASA' OR currentstatus = 'Secondary Interview - Final' OR currentstatus = 'Secondary Interview - Sent' OR currentstatus = 'Secondary Interview - Completed' OR currentstatus = 'HIPAA Signed' OR currentstatus = 'VGA 2nd Interview Completed - Tier 1' OR currentstatus = 'VGA 2nd Interview Completed - Tier 2' OR currentstatus = 'VGA 2nd Interview Completed - Tier 3');```
Oh, perfect, so we are aligned on the SQL, thank you.
@deleted-U072JS2NHRQ, there are some AWS Lightsail (database module) connection issues, I am running GenerateFinLog lambda now. Make sure you re-run it in about 10 minutes. I ensured the Status Report was imported into the database.
I'm at the dentist office, going to reboot the Lighsail server, I'll advise when this is resolved.
@deleted-U072JS2NHRQ, I'm running genfinlog lambda, I think our syncing of data from Fivetran for our BI needs is causing DB connection issues during the morning runs. So I made them 5 minute data syncs instead of syncing each 1 minute.
You can delete them or leave them as "Signed & DECLINED" and they are not counted in financials.
@Ryan do you happen to know if my password changed? I just entered the one that you gave me before, but like then it pops up again right away
It's been connection issues, give me a minute to check it out.
I am running Gen Fin Log too, let me know if PG Admin access works
It's been a lightsail issue lately, not sure how to resolve besides "Reboot Server" option in AWS. Let me do that.
@deleted-U072JS2NHRQ, reboot did the trick. Lambda humming along now.
@deleted-U072JS2NHRQ, I decided to put you into Looker Enterprise like I have a few others at TIP and Shield to start. But you will be a "User" not just a "Viewer" but we'll need to make sure I get the permissions correct so you see the data properly in the Looker Explore. Let me push our session to early next week. What time do you leave each day?
Look at Enterprise is totally different than the free data studio
I’ll import the Rose, don’t do the talk time revenue until I let you know
@deleted-U072JS2NHRQ, Nick McFadden said he has you in Looker Enterprise already, are you?
Got it. Ok. Let me schedule Looker enterprise setup for you and confirm access. I’ll setup training next week when I’m in office.
This is the new Looker though:https://shieldlegal.cloud.looker.com/login
When you say you're importing them, you mean this part right? There's more code, but
Nothing is different today. I ran Lambda and will import ASA, you do the S1, Declines, Contract IDs and Slack Alerts plus add the CRQ (Case Reviews, RET (e-signs) and POST (ASA) Connex One talk time revenue.
Importing ASA leads now into Fin Log table, will confirm when done.
Ask Brian Hirst and Nick McFadden, but I don’t believe so. He should make sure we can get these fields from API
Will this tab be accurate ? And Nick McFadden really n da to use Flatirons Benchmark report as requirement La to design it yeah? That report needs to be a post retainer standard.
If it does have Shield Legal in the case type name as the lead source at the very end, we need to fix that in Law Ruler because these are organic leads for Ben Crump, therefore they’re not technically shield retail
Just change the database lead source to BLX, that’s how I’ve been doing it until we get that name changed in Law Ruler
In the old database that we are using now as production system, we have a copy of the nightly status report for all status changes, and then we added a column that tells you if it was ingested by the financial log. So we will have many rows for one lead ID where it shows you the old status, and the current statuses.
That same thing is being built by Dustin for the new system. It sure how he is storing the “changes history” but even if he doesn’t, I will build a table in the new system that will keep a record of everything in Law Ruler that change status, and the timestamp of when that happened.
If you needed the raw status report table data for a specific instance right now, let me know
Oh, welcome to the new digs. How nice is that place!?!
I'll do the S1s and Contract and Slack Posts, do DELETES when you can.
@deleted-U072JS2NHRQ, please update the S1 value for the below to be PL-TikTok_MB-ML, CP not doing Chowchilla.
@deleted-U072JS2NHRQ, nevermind, it was a rogue, random lead submitted from CP's site, leave as is. I'll update the financials.
Got it, I’ll get this to James’s team on TIP side then.
@deleted-U072JS2NHRQ, I did S1s, please post to channels.
@deleted-U072JS2NHRQ, tomorrow just do the DECLINES, I'll do S1s, ContractIDs and TIP Slack Alerts.
Yeah, I will get you onto Looker Enterprise as an Explore User, then you can use it for SQL as needed for reports soon.
About 5 more minutes, standby. And when done, double check S1 and contract IDs, hopefully it didnt revert to blanks/ NULLs again
*Thread Reply:* That is because I ran a large sweep to close October and backfilled some I believe
Don’t worry about the 19 for November, that’s yesterday. We haven’t finished numbers yet.
Do the declines and talk time revenue, that is 100% TIP
The S1s, ContractIDs and Slack Posts are for Shield but it’s still TIP data in TIP system.
Cameron and I have been working on getting tip stuff to tip, and back-and-forth
Train up Edward ASAP, I’ll retrain and then get this automated anyways.
That part was fixed, so you are good to do Talk Time Revenue for Edward. I pressed Joe S. to get PG Admin on his and Zek's machines today. I'll advise. Let Britt know.
The GenFinLog lambda was updated with all "current billable statuses" last week.
I tried and it said needed Admin approval, that’s on TIP side maybe? Send me posts for that channel
Train him but you can keep doing it, just let him be the backup. He’ll need to understand what we are doing with dialer data soon.
Hey @Ryan , I’m trying to help Greg Owen out with a reconciliation of the LegaFi - NEC case type. He’s saying that it’s showing 53 contracts signed, but I’m only seeing 40 in LawRuler and the Operations dashboard. This is a screenshot of what Greg sent me, it seems like a different dashboard or a different view. Can you check into this for us? Thank you!
Use reconciliation dashboard and send me scrapshot
@deleted-U072JS2NHRQ , make the date February 9th, numbers for 2/10 are not ready.
@deleted-U072JS2NHRQ, I only will have 10 in Feb, this one is a TEST lead left at a billable status: 622856
@deleted-U072JS2NHRQ, there are 2 line items in SL side, you did not select both, so we are good.
All I did was copy and paste the formulas from above in the history that were correct and pasted it over the error ones, go ahead and update it on your own please
Looker Enterprise, put a request in with James Turner if you need one. We hope to be off of DataStudio by years end.
@deleted-U072JS2NHRQ , we cannot have three BI tools. Someone asked about Domo. But we are on the GCP and PostGres stack so let’s stick with viewing.
Also, we want ours with secure credential login, this is highly sensitive information we share.
We are looking for a user roll for you, it allows you to build visual, sequel, and create dashboards. They’re just checking on data privacy for the financials and other things that are only for management to see. We got you.