@Tony @deleted-U05EFH3S2TA @Mike @Luke Fontaine new process for the Agent Payroll Status Report x 2 a day to help Alan.
- Setup process at 12PM: a. Run Status Change SQL for "Signed E-Sign Final, Case Review - Phone Transfer and Secondary Interview - Final" b. Run TIP Fin Log Generate Lambda c. Run Status Fix SQL
- Build Dashboard based upon this spec: https://docs.google.com/spreadsheets/d/16hvTjszfNYLKqepTvcsl_h0GejKitpjKCW6VTiZmCZk/edit?usp=sharing
- Give to Alan and show how to export into CSV
*Thread Reply:* Can @Mike get Lambda to run at 12PM on it's own in AWS Lambda.
@Luke Fontaine
SQL for TIP Dash EOM close to get Total Expense, Director Commissions Pool and Total Shield Legal only sign ups updated in database from our TIP Financials Google worksheet tab.
```-- Get the rows SELECT ** FROM directorcommissionv2 WHERE revyear = '2023' AND revmonth BETWEEN '8' AND '12' ORDER BY rev_month ASC;
-- Update Row For Quarterly Commissions
UPDATE public.directorcommissionv2
SET
numesignedcontracts='2797',
directorcommissionmonthly='37125'
-- quarterlyactive=1
WHERE revyear = '2023'
AND revmonth = '12'
SELECT **
FROM public.financialconstantsv2
WHERE revdate >= '2023-10-01' AND revdate <= '2024-02-01'
ORDER BY revdate DESC```
@Cameron Rentch @Tony @deleted-U04GU9EUV9A @deleted-U05EFH3S2TA
@Cameron Rentch, on January 5th, you requested a "TIP Leads Dashboard" that you envisoned on a television in the call center area. That TV dashboard is ready to be setup and used. This dashboard will update at 4:00AM, 8:15AM, 12:15PM and 3:15PM Pacific Times.
The full-screen, present mode that has to be simply setup once on a TV screen in a browser are the last steps once the hardware is ready. @Cameron Rentch, who do we lean on in Las Vegas to deliver this?
And if you want different visualizations, feedback appreciated.
FYI @Mike @Luke Fontaine
*Thread Reply:* @deleted-U04GZ79CPNG, if you can get a Chrome Browser logged into any TIP Google Account, this URL needs to be added, and then I can send a quick loom on how to configure to "present in full screen mode". Can you help me?
https://lookerstudio.google.com/reporting/e6291642-d160-4f97-a866-83b9a91d90a2
*Thread Reply:* @deleted-U04GZ79CPNG https://www.loom.com/share/337f9516c92445db9e3c8e1004018856
*Thread Reply:* @deleted-U04GZ79CPNG , get the final nod from @Tony, but the dashboard is updated. It has all new leads "created in LawRuler" for today excluding Form 95s.
*Thread Reply:* It's ready for primetime on the TV. Please be sure you set dashboard to "Present" mode in the 3 dots menu in the pop-up header once on live screen. 🙂
@Luke Fontaine @Brian Hirst, just invited you to the dashboard above for TV. Read above. I have custom queries pulling the data from our now, soon to be updated 4x per day TIP Dashboard. If @Tony and team want changes, additions, etc, please work with me to setup. I've got some slick ideas once we get some feedback from others.
*Thread Reply:* @Luke Fontaine @Brian Hirst
The sql I tested in PG Admin, using ChatGPT to quickly get the date solution.
```--GROUP BY TO CREATE REPORT DATASET TODAY - LIST visual SELECT typeofcase, COUNT(lrleadid) FROM public.rawstatusreport WHERE created >= CURRENTDATE AND (source LIKE '%Shield%' OR source LIKE '%BLX%') GROUP BY typeof_case
--GROUP BY TO CREATE REPORT DATASET 3 TRAILING DAYS - PIE CHART SELECT typeofcase, COUNT(lrleadid) FROM public.rawstatusreport WHERE created BETWEEN CURRENTDATE - INTERVAL '3 days' AND CURRENTDATE GROUP BY typeofcase```
@Tony @deleted-U04GU9EUV9A @deleted-U05EFH3S2TA
Here is the Agent Report that updates at 8AM, 12:30 PM and 3:30 PM each day @deleted-U05EFH3S2TA and @Debbie. Please review and make sure this lines up with your manual numbers. We can add this to the TV dashboard and make it scroll from page to page automatically once you guys approve it.
https://lookerstudio.google.com/reporting/c3fa4d6d-2195-44b6-a60f-145930efb23a/page/p_kuc84ifv3c
Important to note that it is only updating those 3 times per day based upon the "first status" not the "current status" until @Quint Underwood finishes some updates to our Lambda python script to update statuses as they change from day to day. So for now if we see 3 e-Sign WIP, those won't show FINAL if changed during the day until the update is ready. It's top priority for the developer.
FYI @Debbie
adding @Altonese Neely and @Brian Thiessen for viewing purposes of the DB as well FYI @Ryan
@Altonese Neely has joined the channel
@Brian Thiessen has joined the channel
@deleted-U05EFH3S2TA, @Luke Fontaine and I will grab you a bit later today to review your report with 277 and our dashboard having 276 for 1-30-2024, we are just 1 off from your Status Report you emailed.
Hey @here I’ve deployed the testing TIP lambda(s) w/ the status update feature (request #1), some details below:
It’s a separate database, Lambda function, & deployment pipeline so it will run entirely in parallel with the existing TIP infrastructure. The database was cloned a few hours ago so there may be some discrepancies due to writes in the interim. The TIP file upload triggers (raw call report & raw status report uploads) are also set up to trigger this function, so it should be possible to compare the old database/lambda to new database/lambda to verify correct functionality.
TIP-Py311-S3toRawTablePy311-vcF9lypjeRNS: https://us-east-1.console.aws.amazon.com/lambda/home?region=us-east-1#/functions/TIP-Py311-S3toRawTablePy311-vcF9lypjeRNS?tab=code TIP-Py311-GenerateFinancialLogPy311-v0F91O8x6PLG: https://us-east-1.console.aws.amazon.com/lambda/home?region=us-east-1#/functions/TIP-Py311-GenerateFinancialLogPy311-v0F91O8x6PLG?tab=configure
The credentials for the new testing database (TIP-LS-CORE-DEVELOPMENT) can be found here in AWS Secrets Manager: https://us-east-1.console.aws.amazon.com/secretsmanager/secret?name=databases%2Flightsail%2FTIP-LS-CORE-DEVELOPMENT®ion=us-east-1
I’ve also updated the LAMBDAMASTERTABLE Google sheet w/ this lambda. The new ones are highlighted in yellow https://docs.google.com/spreadsheets/d/1gQI2vERq2k-T_3xRiGL19drtWfm2S55VCzjYBZ65AZk/edit#gid=0
I’m currently attending an industry conference in Spain so I’m on Central European Time (+9 ahead of Pacific Time), somewhat conveniently since my availability will be late evening = noonish PT. I will try to check in w/ you tomorrow night and look through the logs to see if there’s any incorrect behavior or if I missed something during deployment.
@Quint Underwood, ah, did not know you were traveling. I know it's evening time in Spain, but we have a current issue with Generate Fin Log PY31, any chance you can look? I am exploring the Type of Case names in the rawstatusreport table to see if I can remedy that...
@Ryan I'm on, investigating issues right now
@Quint Underwood, start with Calculate Performance issue first.
I just ran Calc_Perf after deleting Jacks row in TypeForm since the Lambda Error Log shows an error after it did not find Moreno Valley Pastor Child Abuse,579.43 in the INDEX for JA.
That did not work, 3 runs and 3 fails on Calculate Performance @Quint Underwood, i'll let you debug:
2024_02_26T10:25:19.559_08:00
[ERROR] TypeError: can't multiply sequence by non-int of type 'float'
Traceback (most recent call last):
File "/var/task/reconcile.py", line 392, in lambda_handler
df = handler()
File "/var/task/reconcile.py", line 159, in handler
m1["Spent"] = m1["Count"] ** m1["CPL"]
File "/var/task/pandas/core/ops/common.py", line 76, in new_method
return method(self, other)
File "/var/task/pandas/core/arraylike.py", line 202, in __mul__
return self._arith_method(other, operator.mul)
File "/var/task/pandas/core/series.py", line 5815, in _arith_method
return base.IndexOpsMixin._arith_method(self, other, op)
File "/var/task/pandas/core/base.py", line 1381, in _arith_method
result = ops.arithmetic_op(lvalues, rvalues, op)
File "/var/task/pandas/core/ops/array_ops.py", line 285, in arithmetic_op
res_values = _na_arithmetic_op(left, right, op) # type: ignore[arg-type]
File "/var/task/pandas/core/ops/array_ops.py", line 229, in _na_arithmetic_op
result = _masked_arith_op(left, right, op)
File "/var/task/pandas/core/ops/array_ops.py", line 165, in _masked_arith_op
result[mask] = op(xrav[mask], yrav[mask])
@Ryan I'll run debug calc-perf locally and figure out what's up there
@Quint Underwood, thank you. Standing by with @Luke Fontaine
@Ryan I fixed the bug in CalcPerf & lambda is updated w/ fix. I'll let you run/not run so I don't overwrite do_not_touch
The issue was an "inf" (infinity) in some part of the financial calculations... Specifically the CPL inf below for ML Birth Injury spend. I'm guessing it was due to the mediabuyer spend input errors you mentioned
mediaBuyer ML
verticalName Birth Injury
buyerName <NA>
contractName <NA>
contractID <NA>
Count 0.0
LeadRevenue NaN
RPL NaN
CPL inf
Spend 1759.64
Name: 44, dtype: objec
@Quint Underwood @Luke Fontaine, Calc_Perf ran successfully,
@Quint Underwood is working on TIP Gen Fin Log fixes, I'll work on the lookup formula to use the sheet, hopefully you beat me to getting the production table updated with all signed e-signs (we show 80 in our rawstatusreport table FYI from 2/25/24) but I'll prepare a "workaround google sheet" JIC it takes you longer, for @Luke Fontaine
@Ryan @Luke Fontaine TIP should be fixed
TIP issue--indeed was caused by deployment last night. I neglected to change the Serverless Function Names in the AWS SAM template when I pushed the testing branch. So as a result it overwrote the existing lambda functions with the new ones, which apparently didn't work (I haven't dug into the logs yet)
@Quint Underwood, the old Lambda App is the "TESTING" ones, can you send the links to run the PROD one?
@Quint Underwood, I cannot find the PROD lambdas. 🙂
@Ryan Yeah I spoke too soon, something's still mixed up. I'll tear down the TESTING ones and redeploy those later
@Quint Underwood, ok. I'm runnign this one now, not sure but think it's the correct one to run: https://us-east-1.console.aws.amazon.com/lambda/home?region=us-east-1#/functions/TIP-dev-GenerateFinancialLogPy311-CKwZpRfitFs8?tab=testing
@Quint Underwood, you can run Prod GenFinLog_Py311 when you get it reconfigured, we only have 19 e-signs in the table from 2-25-24, expecting at least 79 to 80.
*Thread Reply:* I'll rerun once it's updated. It's possible call report/status report need to be reuploaded as well to get into DB, I'll check
*Thread Reply:* No problem, upload the one from 4AM and 8AM on call and status then. The next one at 12PM-ish will be over soon too.
@Quint Underwood let me know when the genfinancials lambdas up and working, just need it for the last part of doing financials
@Luke Fontaine Here's the Generate Financial Log lambda (TIP): https://us-east-1.console.aws.amazon.com/lambda/home?region=us-east-1#/functions/TIP-Py311-GenerateFinancialLogPy311-9YN38SeLubUn?tab=configure
@Quint Underwood, running it now. Noting no log group associated.
*Thread Reply:* The no log groups is due to it technically being a new lambda that hasn't been run before, and thus doesn't have any logs
*Thread Reply:* Got it. The rawstatusreport table had the 89 marked as "TRUE" for ingested by fin log on 2-25-24, so we made those false @Quint Underwood, and running that prod lamda now. Stand by.
FYI @Luke Fontaine
If we need to upload the status reports again, put into the same PROD s3 bucket @Quint Underwood?
*Thread Reply:* Same with call reports
*Thread Reply:* Trying to upload the 4AM ones now and running that Lambda @Quint Underwood
@Quint Underwood, so we have 37 e-signs for 2-25-37 and we confirmed that is accurate. Please make sure all the PROD configuration is back and working before the testing misconfiguration. @Luke Fontaine, you can get dailies done.
@Ryan Confirming I've reconfigured everything (trigger on S3 upload, timers for FinancialLog) for production as before
Ping me if something looks amiss and I can look at it tomorrow evening (CET)/tomorrow morning (PT)
@Ryan & @Luke Fontaine I've redeployed the TIP TESTING stack just now. I'll check in on it this evening and adjust as necessary. I verified that the existing GenerateFinancialLogPy311 is still working as expected (no errors like last time)
(Due to some AWS quirks (we can only directly trigger one lambda function from S3 bucket), I'm using a slightly different trigger method for the TESTING GenerateFinancialLog lambda. Therefore there may be some slight modifications I need to make)
Relevant info:
• All lambda details (for both TIP-Py311 and TIP-TESTING) are up-to-date in the Shield AWS Lambda Master Table
• New TIP testing database (fresh copy as of today, March 04 2024): TIP-LS-CORE-TESTING
• Credentials for new testing database can be found in
*Thread Reply:* @Quint Underwood, @Luke Fontaine and I are working towards looking at this, is the CORE DEV table running daily and updating fin_log table with status changes since this post 9 days ago?
*Thread Reply:* Hey @Ryan I haven't looked in a few days, let me pull up logs/database and confirm
*Thread Reply:* @Ryan Some of the TIP callreports need to be reprocessed for the testing DB (same as for the production DB last week), also spotted some errors in logs. Standby on those items
*Thread Reply:* Hey @Ryan & @Luke Fontaine I've fixed the errors from last week and redeployed
*Thread Reply:* Should be good to go for comparing database entries, etc.
*Thread Reply:* @Quint Underwood, excellent! Thank you.
Also @Ryan & @Luke Fontaine, I think there may be an issue with S3toRawTable when processing rawcallreports. I noticed errors in logs due to error in processing Excel file format (similar to an error @Ryan and I saw a few years ago). The most recent entries to raw_call_report table are Feb. 12 2024, so issue predates any TIP TESTING deployment & therefore unrelated. (See pgadmin screenshot below)
*Thread Reply:* @Quint Underwood, yes, we need that fixed, that is why our revenue for talk time is not working correctly. Please advise on a fix and I'll review the test enviro ASAP.
*Thread Reply:* @Quint Underwood, this is pretty urgent as we do weekly invoicing on Mondays, can you get this fixed for at least last week Monday to Sunday ASAP?
*Thread Reply:* Actually I see each day here Quint. Is the PDF in the email causing issues? Are you looking into the right log?
*Thread Reply:* Hey @Ryan I have a few min right now to try to fix, otherwise I'll continue in a few hours after company event.
Regarding the underlying issue--it looks like the Excel files are successfully uploaded to the tip-rawcallreports bucket, but the S3toRawTable lambda isn't parsing the file correctly and thus the call report rows aren't added to database
*Thread Reply:* @Ryan I have a fix for the callreport issue (based on previous fix for statusreport issue). Working on backfilling the raw_call_report table right now, starting with Feb 13 so it's complete
*Thread Reply:* **Actually I need to run rn but pushing fix right now
Provided this is an issue, I can work on this later today around ~noon PT
@Ryan Finished backfilling rawcallreports from Sunday Feb 25 through Sunday March 3 (inclusive). Can do the others in a few hours when back
Actually, they did upload @Quint Underwood, but the TIP GEN Fin Log is not processing everything...
And when I ran "TIP-Py311-GenerateFinancialLogPy311-9YN38SeLubUn" just now, I got a "Failed to Fetch" error and then re-ran it.
Hey @Ryan Didn't check slack last night, but yep I can investigate the above today
Hey @Ryan Still working on the above, should have some time this afternoon/evening to figure out what's going on
@Sabrina Da Silva has left the channel
@Altonese Neely has left the channel
@Brian Thiessen has joined the channel
@deleted-U06VDQGHZLL and @deleted-U055HQT39PC, I have added you both to Looker Enterprise, which currently only has the existing production database behind it showing the Billable Leads. Lets hop on a quick call so I can walk you through how to use it.
@deleted-U0410U6Q8J3 Can you let me and @James Turner know times next week for 30 minutes that we can get with you and resolve the DBT Snapshot issues?
Ty.
@deleted-U055HQT39PC, and FYI @James Turner @Mark Maniora @Malissa @Nick Ward @Nicholas McFadden, go ahead and make the name change in our meeting today in LawRuler, Benchmark Report, and Leadspedia.
@deleted-U055HQT39PC, once done, post to me a response like this:
OLD NAME: example - exp - exp - shield legal
change to
NEW NAME: example 2 - exp - exp - shield legal
I will then go update the TIP database after, Cam, Marc have me in a leadership financials meeting.
*Thread Reply:* @deleted-U055HQT39PC , and I would need to know the date that this starts if there’s an order before it that were changing it from, if we’re just renaming an entire order that it won’t matter
*Thread Reply:* ok should we try to have this meeting on Monday then? @Ryan @deleted-U055HQT39PC @Nick Ward
*Thread Reply:* No, have it. We are only changing one by request
*Thread Reply:* does anyone know which one it is?
*Thread Reply:* @deleted-U055HQT39PC , which one?
@Mark Maniora has joined the channel
@Nick Ward has joined the channel
@here 'CA Juv Hall Abuse - ACTS - DL Flatirons - Shield Legal' has been updated in TIP Dashboards.
@deleted-U072JS2NHRQ @deleted-U06C7A8PVLJ, the AWS Lambda GenerateFinancialLog has been updated to import all current "billable statuses" for ASA by @James Turner today. So that means this week, we'll see new leads enter dashboard / database at 3:00 AM PT when it runs on schedule. And next Monday I will not need to run the manual-hack to get those imported.
You may work with Alan, Brianna and Brittany to ensure you do a weekly reconcile of these to keep them on-par with your monthly billing.
FYI @Malissa @Nick Ward , thanks @James Turner!!
Sure thing! Please let me know if you notice any issues with it.
@James Turner, it worked. This is the Looker Studio > Financials > Leads Yesterday filter for those new statuses.
@Brian Thiessen, for me and @James Turner, we need real-life tip operating expense data to "seed" our iotipoperating_expense table, see below:
@Brian Thiessen, hey, from our TIP PnL analysis we did here a while back, can you send me the expenses in a google sheet that ONLY have the values for the columns we defined by month in each sheet-column?
Row • Month (yyyy-mm) Columns
- AGGREGATED PAYROLL (e.g. Bonuses not listed in our pricing exercise here)
- OCCUPANCY EXPENSES
- TRAVEL EXPENSES
- TECHNOLOGY EXPENSES
- OFFICE EXPENSE
- PROFESSIONAL SERVICES
*Thread Reply:* @Brian Thiessen, we need a sheet just like this tab, from Jan 2024 to October 2024 please, we'll mark the rows we need based on the first tab.
Below is the case type level Payroll and Other Personnel Expenses:
• inboundpayrollcostper $216 • outboundscreeningpayrollcostper $182 • outboundintakepayrollcostper $110 • processingpayrollcostper $71 • asapayrollcost_per $203
*Thread Reply:* FYI, @James Turner, so the pink rows in the tab are the ones that we put as rows. Noting Agent Commissions are a standalone field in our case types io table
@Ryan, for which months, and just totals for each column, as opposed to totals that make up each column? For instance, $100K for aggregated payroll or $25K for wages, $25K for payroll taxes, etc?
According to our TIP PnL analysis sheet, you only put into those row's values anything you did not put at the line item level. I recall #2 to #6 being kept out of the case type-level pricing, and #1 you just need to give us the bonuses, so need you to ensure you recall how you formulated but the sheet was our guide:
@deleted-U06C7A8PVLJ, any progress on doing the iorevrates to financial_log pricing and agent commission updates?
@Zekarias Haile @deleted-U06C7A8PVLJ TIP & SL EOM Tech Resources:
Loom:
@Nicholas McFadden @James Turner, FYI, not urgent but lets get it paused and archived if not using anymore:
I'm not familiar enough with either of those to say how/if they are used, but we can certainly dig in when we get the time!
@Nicholas McFadden, if you can setup time to show James these since on TIP side. 🙂
*Thread Reply:* @Nicholas McFadden, please setup a 30 minute meeting to go over using Fivetran as the GCP function to control the data pipes for AI project with @James Scott and @Josh Josue ASAP.
Want to ensure support for data pipelines is alleviated to Fivetran so Zek / Edward over time can monitor.
*Thread Reply:* @James Scott @Josh Josue, did you get Fivetran training?
*Thread Reply:* Hi Ryan, I have not yet had the Fivetran training. I'll follow up with Nick about it
*Thread Reply:* is that the one for feb 4
*Thread Reply:* I’m not sure, confirm with Nick. Want our AI pipes GCF in there by Josh
@James Turner let’s find time this week to go over it
@James Turner @deleted-U06C7A8PVLJ, better table for seeding your pctmain table with billingid or billingreferenceids:
Here is that table that has ALL orders and what is active, and ONE column from Copper for the Leadspedai Contract IDs: https://console.cloud.google.com/bigquery?orgonly=true&project=shield-legal-bi&supportedpurview=project&ws=!1m5!1m4!4m3!1sshield-legal-bi!2ssldashboard!3sorderspaidspent|https://console.cloud.google.com/bigquery?orgonly=true&project=shield-legal-bi&supportedp[…]4m3!1sshield-legal-bi!2ssldashboard!3sorderspaidspent
@James Turner and @deleted-U06C7A8PVLJ, we can pipe that data from BigQuery SL to BigQuery TIP in FiveTran easy too if necessary.
Looks like I don't have permission to view
@James Turner, you now have full access to the SL project.
Awesome! Thank you! It looks like this has cprlpcontractids which I imagine will be a direct import to the billingid column you mentioned wanting,
Is the lpverticalid a lawruler id we can map to?
@Brian Hirst @James Turner :
SELECT **
FROM `tort-intake-professionals.tip_prod_application.io_tip_lr_status_rates`
WHERE service_type IS NOT NULL
ORDER BY service_type, service_sequence ASC
@deleted-U06C7A8PVLJ, in the post above, strikeout these 2 items, did them this AM.
• Update DB with Staffing Fees (not Flatirons) • Update Looker Studio & Enterprise Formulas for Flatirons Overhead cost for Fi CSP, VSS & MRT
@deleted-U06C7A8PVLJ, update TIP fin log, https://docs.google.com/spreadsheets/d/141X3FR7-gX-IplS14qSk2FDRr_YIDV6ZYdHhQeKFW-k/edit?usp=sharing
@James Turner @Tony @Nick Ward @Nicholas McFadden https://www.five9.com/products/capabilities/call-center-apis-and-sdks
@James Turner Could you please add 2024-12-01, 2025-01-01, 2025-02-01, and 2025-03-01 rows to the iotipmonthlyexpenses table under the tipprod_application dataset. My app is having issues adding the rows from the interface.
*Thread Reply:* Hey there, do you just need the dates added as new rows or does that data need to be seeded as well?
*Thread Reply:* Hmmm. @Ryan do you think it’s best if the data is seeded or blank and I’ll show Brian and whomever else is using the app to fill it out?
*Thread Reply:* @Brian Hirst, train Brian to add it, include James to ensure we do it correct. 🙂
*Thread Reply:* Cool. @James Turner just the rows added then please!
@James Turner, please update the board before our meeting today. Dates, Status, any important comments. Need to get through all tasks at a "high" level today so I can really work on project timing for deliveries with partners. Ty, see you guys soon.
https://tortintakeprofessionals.monday.com/boards/6304263960/views/156553659
@Joe Santana, @deleted-U06C7A8PVLJ needs a laptop for the push to rollout the new BI financials and operations. He'll need to be able to float around the office and discuss with users what dashboard and data they'
@Nick Ward @deleted-U06VDQGHZLL, did you ever get your people setup to be Basic Looker Enterprise (LookerE) Viewer users veresus LookerE Builder Users?
We can get training setup with @James Turner, @deleted-U06C7A8PVLJ and start getting the users created, roles assigned and security / permissions tested.
FYI @Nicholas McFadden
@James Turner, urgent but quick fix. TIP DBT needs the 2 sql model files updated, pushed to staging, and then pushed to GitHub Main production inside GitHub from Staging. @Nicholas McFadden has to be the approver of the push to Production in GitHub, you select him in your git hub request.
https://www.loom.com/share/92793958d16d4019a2a8e470609e5472
Then you ensure it syncs in Fivetran to SL GCP BigQuery project and it should update @Nicholas McFadden
@James Turner, urgent but quick fix. TIP DBT needs the 2 sql model files updated, pushed to staging, and then pushed to GitHub Main production inside GitHub from Staging. @Nicholas McFadden has to be the approver of the push to Production in GitHub, you select him in your git hub request.
https://www.loom.com/share/92793958d16d4019a2a8e470609e5472
Then you ensure it syncs in Fivetran to SL GCP BigQuery project and it should update @Nicholas McFadden
First Error:
17:07:21 **9** of **12** ERROR creating sql table model tip_staging.dbt_lr_case_types_revenue_rates [**ERROR** in 1.53s] - models/stage/lawruler/dbtlrcasetypesrevenue_rates.sql
Second Error:
17:07:22 **10** of **12** ERROR creating sql table model tip_external.shield_legal_datastore .... [**ERROR** in 1.37s]- models/external/shieldlegaldatastore.sql
To resolve these errors I made changes made to the code blocks to use a SELECT ** instead of hard coding each column. This will prevent future issues with potential Schema changes.
Changes in DBT have been made, and saved, as well as pushed to Github. I do not have access to Fivetran to ensure it syncs there. @Nicholas McFadden If you would, please pick up from there and let me know if you run into additional problems.
@James Turner, Marc called me with his self-audit of the new Page 0, and the old Dashboard / Slack for 7/9, 7/8 and probably further back.
The new AFFF-PFAS Mil. Base Exposures that have TC plus other 3rd parties (e.g. LEV - ELG TCSA - 531 - 1829) are in Slack, in new Dashboard we are missing several. We see https://tortintakeprofessionals.lawruler.com/update-lead.aspx?lead=793720, in old TIP dash as signed e-sign, we do not see her in Page 0 https://shieldlegal.cloud.looker.com/dashboards/118?Leadspedia+S1=&Leadspedia+Lead+ID=&Marketing+Source=Shield+Legal&Earliest+Esign=2025%2F07%2F09&General+Tort+Type=AFFF+PFAS+Military+Base+Exposure%2CAFFF-PFAS+Military+Base+Exposure&Lawruler+Case+Type+Name=|here:
I'm guessing PCTID is not fully setup for this? Can you investigate ASAP and figure this out?
We have 181 for 7/9 in our current dashboard, we have 162 in Page 0.
@Carter Matzinger has joined the channel
@James Turner Read.ai's TIP BI Sprint notes from last week:
FYI @deleted-U06C7A8PVLJ @CC Kitanovski @Nick Ward
Action Items • James Turner will send the link to the financials board to Ryan Vaspra for review. (Active) • James Turner will add a billable filter to the dashboard to improve lead tracking. (Active) • James Turner will check with Melissa and Rose regarding the invoicing process and how to handle real-time data discrepancies. (Active) • Ryan Vaspra will review the financial dashboards and provide feedback on their usability. (Active) Key Questions & Answers Q: What specific improvements can be made to the financial dashboards to enhance usability? A: Feedback on usability will be provided after reviewing the dashboards. Q: How will the team handle discrepancies in invoicing when real-time data changes occur? A: The invoicing process will be clarified with Melissa and Rose.
@James Turner, please update the iolawrulerrevenue_rates table to make all Shield Legal RET deals with 0 per minute talk time revenue / price. FYI @Malissa, we are prepping the new Invoices by Month and going through some validation.
Profit x Day in AWS from public financial log table, plus directorcommissionv2 plus financial_constant
SELECT fl.signed_date,
count(fl.current_status) AS sum_esigned_contracts,
sum(fl.talk_time_expense) AS sum_talk_time_expense,
sum(fl.talk_time_minutes) AS sum_talk_time_minutes,
sum(fl.contract_revenue) AS sum_contract_revenue,
sum(fl.talk_time_revenue) AS sum_talk_time_revenue,
sum(fl.net_revenue) AS sum_net_revenue,
sum(fl.gross_revenue) AS sum_gross_revenue,
sum(fl.agent_commission) AS sum_agent_commision,
sum(fl.sales_commission) AS sum_sales_commision,
CASE
WHEN date_part('year'::text, fl.signed_date) = dcv2.rev_year AND date_part('month'::text, fl.signed_date) = dcv2.rev_month AND date_part('day'::text, fl.signed_date) = dcv2.rev_day THEN dcv2.director_commission_monthly
WHEN dcv2.quarterly_active = 1::double precision AND date_part('month'::text, fl.signed_date) = dcv2.rev_month AND date_part('day'::text, fl.signed_date) = dcv2.rev_day THEN dcv2.director_commission_monthly + dcv2.director_commission_quarterly
ELSE '0'::double precision
END AS director_commission,
fc.payroll_expense /
CASE
WHEN date_part('month'::text, fc.rev_date) = ANY (ARRAY[1::double precision, 3::double precision, 5::double precision, 7::double precision, 8::double precision, 10::double precision, 12::double precision]) THEN 31
WHEN date_part('month'::text, fc.rev_date) = ANY (ARRAY[4::double precision, 6::double precision, 9::double precision, 11::double precision]) THEN 30
ELSE 28
END::double precision AS payroll_expense,
COALESCE(fc.operation_expense_director_base_daily_pay, 0::double precision) + COALESCE(fc.operation_expense_rent_per_day, 0::double precision) + COALESCE(fc.operation_expense_lawruler_licenses_daily, 0::double precision) + COALESCE(fc.operation_expense_lawruler_daily_cost, 0::double precision) AS operation_expense,
COALESCE(sum(fl.talk_time_expense), 0::double precision) + COALESCE(sum(fl.agent_commission), 0::double precision) + COALESCE(sum(fl.sales_commission), 0::double precision) + fc.payroll_expense /
CASE
WHEN date_part('month'::text, fc.rev_date) = ANY (ARRAY[1::double precision, 3::double precision, 5::double precision, 7::double precision, 8::double precision, 10::double precision, 12::double precision]) THEN 31
WHEN date_part('month'::text, fc.rev_date) = ANY (ARRAY[4::double precision, 6::double precision, 9::double precision, 11::double precision]) THEN 30
ELSE 28
END::double precision +
CASE
WHEN date_part('year'::text, fl.signed_date) = dcv2.rev_year AND date_part('month'::text, fl.signed_date) = dcv2.rev_month AND date_part('day'::text, fl.signed_date) = dcv2.rev_day THEN dcv2.director_commission_monthly
WHEN dcv2.quarterly_active = 1::double precision AND date_part('month'::text, fl.signed_date) = dcv2.rev_month AND date_part('day'::text, fl.signed_date) = dcv2.rev_day THEN dcv2.director_commission_monthly + dcv2.director_commission_quarterly
ELSE '0'::double precision
END + COALESCE(fc.operation_expense_director_base_daily_pay, 0::double precision) + COALESCE(fc.operation_expense_rent_per_day, 0::double precision) + COALESCE(fc.operation_expense_lawruler_licenses_daily, 0::double precision) + COALESCE(fc.operation_expense_lawruler_daily_cost, 0::double precision) AS total_expense,
COALESCE(sum(fl.net_revenue), 0::double precision) - (COALESCE(sum(fl.talk_time_expense), 0::double precision) + COALESCE(sum(fl.agent_commission), 0::double precision) + COALESCE(sum(fl.sales_commission), 0::double precision) + fc.payroll_expense /
CASE
WHEN date_part('month'::text, fc.rev_date) = ANY (ARRAY[1::double precision, 3::double precision, 5::double precision, 7::double precision, 8::double precision, 10::double precision, 12::double precision]) THEN 31
WHEN date_part('month'::text, fc.rev_date) = ANY (ARRAY[4::double precision, 6::double precision, 9::double precision, 11::double precision]) THEN 30
ELSE 28
END::double precision +
CASE
WHEN date_part('year'::text, fl.signed_date) = dcv2.rev_year AND date_part('month'::text, fl.signed_date) = dcv2.rev_month AND date_part('day'::text, fl.signed_date) = dcv2.rev_day THEN dcv2.director_commission_monthly
WHEN dcv2.quarterly_active = 1::double precision AND date_part('month'::text, fl.signed_date) = dcv2.rev_month AND date_part('day'::text, fl.signed_date) = dcv2.rev_day THEN dcv2.director_commission_monthly + dcv2.director_commission_quarterly
ELSE '0'::double precision
END + COALESCE(fc.operation_expense_director_base_daily_pay, 0::double precision) + COALESCE(fc.operation_expense_rent_per_day, 0::double precision) + COALESCE(fc.operation_expense_lawruler_licenses_daily, 0::double precision) + COALESCE(fc.operation_expense_lawruler_daily_cost, 0::double precision)) AS net_profit,
COALESCE(sum(fl.net_revenue), 0::double precision) / (COALESCE(sum(fl.net_revenue), 0::double precision) - (COALESCE(sum(fl.talk_time_expense), 0::double precision) + COALESCE(sum(fl.agent_commission), 0::double precision) + COALESCE(sum(fl.sales_commission), 0::double precision) + fc.payroll_expense /
CASE
WHEN date_part('month'::text, fc.rev_date) = ANY (ARRAY[1::double precision, 3::double precision, 5::double precision, 7::double precision, 8::double precision, 10::double precision, 12::double precision]) THEN 31
WHEN date_part('month'::text, fc.rev_date) = ANY (ARRAY[4::double precision, 6::double precision, 9::double precision, 11::double precision]) THEN 30
ELSE 28
END::double precision +
CASE
WHEN date_part('year'::text, fl.signed_date) = dcv2.rev_year AND date_part('month'::text, fl.signed_date) = dcv2.rev_month AND date_part('day'::text, fl.signed_date) = dcv2.rev_day THEN dcv2.director_commission_monthly
WHEN dcv2.quarterly_active = 1::double precision AND date_part('month'::text, fl.signed_date) = dcv2.rev_month AND date_part('day'::text, fl.signed_date) = dcv2.rev_day THEN dcv2.director_commission_monthly + dcv2.director_commission_quarterly
ELSE '0'::double precision
END + COALESCE(fc.operation_expense_director_base_daily_pay, 0::double precision) + COALESCE(fc.operation_expense_rent_per_day, 0::double precision) + COALESCE(fc.operation_expense_lawruler_licenses_daily, 0::double precision) + COALESCE(fc.operation_expense_lawruler_daily_cost, 0::double precision))) ** 100::double precision AS net_profit_margin
FROM financial_constants_v2 fc
JOIN financial_log fl ON date_part('year'::text, fl.signed_date) = date_part('year'::text, fc.rev_date) AND date_part('month'::text, fl.signed_date) = date_part('month'::text, fc.rev_date)
JOIN director_commission_v2 dcv2 ON date_part('month'::text, fl.signed_date) = dcv2.rev_month AND date_part('year'::text, fl.signed_date) = dcv2.rev_year AND fl.current_status::text <> 'Signed & DECLINED'::text AND fl.current_status::text <> 'Contacted Attempted'::text AND fl.current_status::text <> 'Signed - Declined - Processed'::text AND fl.current_status::text <> 'Case Review - Firm Rejected'::text AND fl.current_status::text <> 'Case Review - Agent Disqualified'::text AND fl.current_status::text <> 'Case Review - Disqualified'::text AND fl.current_status::text <> 'Case Review - Firm Declined'::text
GROUP BY fl.signed_date, fc.payroll_expense, fc.operation_expense_director_base_daily_pay, fc.operation_expense_rent_per_day, fc.operation_expense_lawruler_licenses_daily, fc.operation_expense_lawruler_daily_cost, fc.director_commission_tier_1, fc.director_commission_tier_2, fc.director_commission_tier_3, fc.director_commission_quarterly_tier_1, fc.director_commission_quarterly_tier_2, fc.director_commission_quarterly_tier_3, fc.rev_date, dcv2.rev_year, dcv2.rev_day, dcv2.rev_month, dcv2.director_commission_monthly, dcv2.director_commission_quarterly, dcv2.quarterly_active;
@Dustin Surwill @James Turner @Brian Hirst FYI @Malissa, I do not see any of the Backpage case types in BigQuery in tort-intake-professionals.<a href="http://tip_prod_application.io">tip_prod_application.io</a>_lr_case_types_revenue_ratestable, so it won't show up in ReTool. Can we please investigate and fix ASAP?
*Thread Reply:* this has been fixed @Dustin Surwill @James Turner, using lrdata.casetype not the Five9 one we sunsetted.
@Dustin Surwill has joined the channel
@Ryan Tracing the BQ queries, the table you are asking about is copied 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 . That table used to be synced via FiveTran but is not now and the table was last modified Jul 31st.
I have no idea what was setup in fivetran for that table. I recommend updating your queries to use <a href="http://tort-intake-professionals.lr">tort-intake-professionals.lr</a>_data.case_types instead of <a href="http://lr_case_types.lr">lr_case_types.lr</a>_case_types
@James Turner, this was a BigQuery Schedule Query that updated that table
The scheduled query pulls from <a href="http://lr_case_types.lr">lr_case_types.lr</a>_case_types
It was not FiveTran @Dustin Surwill, @James Turner, can you investigate this?
Currently backed up on a few other things, but I will add it to the list-
@Malissa, we found a data transform that was failing since July 31st, so we need these updated in Case Type Pricing Retool ASAP Please! And note ONLY the Others for TIP have $2.00 rate talk time per minute. We setup the new scheduled queries to default to 0.00 since most of these are for shield.
@James Turner, I fixed the 2 scheduled queries in BigQuery > Scheduled Queries, noting in the name we are now using lrdata.casetype view not the now defunct lrcasetype.lrcasetype Five9 pipeline.
@Malissa, when can you have these updated? 🙂
Retainers for Shield for TIP
- Backpage CityXGuide Sex Trafficking - Levinson - Levinson - Shield Legal
- Social Media Teen Harm - Marsh - HRSC - Shield Legal
- AFFF PFAS Military Base Exposure - VAM - ELG - Meadow - Shield Legal
- Backpage CityXGuide Sex Trafficking - PLF - PLF - Shield Legal
- San Bernardino County JDC Abuse - PLF - PLF - Shield Legal
- Dublin Prison Abuse 2 - PLF - PLF - Shield Legal
- AFFF PFAS Military Base Exposure - Nations - Levinson - TC - Shield Legal
- Chowchilla Womens Prison Abuse - Aaron - SGGH - Meadow - Shield Legal
- IL JDC Abuse - Baypoint - SGGH - Shield Legal
- Chowchilla Womens Prison Abuse 2 - SGGH - Meadow - Shield Legal
- San Bernardino County JDC Abuse - ACTS - ACTS - Shield Legal
- Backpage and CityXGuide Sex Trafficking Victims - ACTS - ACTS - Shield Legal
- NEC Baby Formula 2 - AWKO - AWKO - Shield Legal
- Sacramento JDC Abuse - DL - Flatirons - Shield Legal
- Riverside JDC Abuse - DL - Flatirons - Shield Legal
- Video Gaming Sextortion - Aaron - SGGH - Meadow - Shield Legal
- PFAS Water Contamination - Aaron - ELG - Shield Legal
- Chowchilla Womens Prison Abuse 2 - ACTS - ACTS - Shield Legal
- Hernia Mesh - Dampier - Levin Papantonio - Shield Legal
- Gaming Addiction - Kuzyk - Meadow Law - Shield Legal Others for TIP
- Backpage CityXGuide Sex Trafficking SEC - Levinson - Levinson
- San Bernardino County JDC Abuse SEC - PLF - PLF
- Dublin Prison Abuse 2 SEC - PLF - PLF
- Backpage CityXGuide Sex Trafficking SEC - PLF - PLF
- Firefighting Foam Military Records - DL - Flatirons - DiCello Levitt
- Chowchilla Womens Prison Abuse SEC - Aaron - SGGH - Meadow - Aaron
- IL JDC Abuse SEC - Baypoint - SGGH - Baypoint
- San Diego County JDC Abuse SEC - SGGH - AWD - SGGH
- San Bernardino County JDC Abuse SEC - SGGH - AWD - SGGH
- Chowchilla Womens Prison Abuse 2 SEC - SGGH - Meadow - Meadow
- Sacramento JDC Abuse SEC - DL - Flatirons - DiCello Levitt
- Riverside JDC Abuse SEC - DL - Flatirons - DiCello Levitt
- NEC Baby Formula 2 PFS - AWKO - AWKO
- Chowchilla Womens Prison Abuse 2 DOCS - ACTS - ACTS
- Chowchilla Womens Prison Abuse SEC - SGGH - Milberg - Sanders - Milberg
- MI YRTC Abuse SEC - SGGH - Meadow - Yih
- MI Juv Hall Abuse SEC - SGGH - Meadow - Yih
- MI Clergy Abuse SEC - SGGH - Meadow - Yih
- MI Foster Care Abuse SEC - SGGH - Meadow - Yih
- Round Up PFS Appointment - Wagstaff
- Instant Soup Cup Child Burns Medical Questions - DL - Flatirons - DiCello Levitt
- MacLaren Hall Abuse Resign - DL - LOSG(TC) - Dicello Levitt
- CA Juv Hall Abuse Resign - DL - LOSG - Dicello Levitt
- CIW Abuse Resign - DL - LOSG - Dicello Levitt
- Chowchilla Abuse Resign - DL - Van - Dicello Levitt
- Chowchilla Abuse Resign - DL - LOSG - Dicello Levitt
- CA Juv Hall Abuse Resign - DL - Van - Dicello Levitt
- Social Media Teen Harm - DL - DL - DiCello Levitt
- Paraquat SEC - DL - ML - DiCello Levitt
@Ryan i'm in there right now ryan. Let me see how fast i can get a few done and i can give you an estimate
@Ryan some of those SECs are new to this month so i won't have pricing yet. Do you want me to just use what i have?
All Shield Retainers are $236 billable rate and $0 talk time rate FYI @Malissa 🙂
@Ryan @James Turner Retainers done. Moving on to Others for TIP.
@Ryan @James Turner I just finished. The confetti cannon was lagging for a while. If any didn't save let me know.
Also, i didn't know what rate to use for the Resigns. I used 250 as that's the rate we're using for the recapture project.
@Brian Hirst, @Malissa needs immediate access to TIP Retool's LR Status Rates. It's still not working, when can you have it working? We need it by EOW next week no later please. If you are tied up on workload, I will have @James Turner finish it. Please advise and confirm.
I had it working last week Investigating now
@Brian Hirst, send video and lets look at the only 3 Statuses that have rates, VGA SEC Tier 1, Tier 2 and Tier 3 and ensure the metadata is up to date.
And @Brian Hirst, if Status Name is how we look up the active record to edit, can we move it somewhere more user friendly, rather than lower right, may be the first with a tool tip to explain to look up status?
@James Turner, did you ever finish that Portfolio Dashboard template for TC? Can you send to me please?
Because it is an "external view" dashboard, you may require special permissions to view it. Let me know if you get access denied or other issues.
@James Turner, I am updating this TC dashboard with a massive data clean up project since not all Case Types that should have TC in them do. I am keeping a sheet, I am updating Case Type names in LawRuler, AWS TIP Fin Log table and validating them. I added 2 more strings into each Explore Pane / View's own filters and removed the Global Filter looking for TC, keeping out TCA and adding "Steve - Slater" and "SAZL", older codes that mean "TC". We should have around 17K not 12K, so I'll report back to this message when this long process is complete.
FYI @deleted-U055HQT39PC @Nick Ward @Tony @Carter Matzinger @Malissa. This is for a new Dashboard for TC Call Center Lead Flows, and Marc's SL dash shows 17K in cases from TIP, when this dashboard only showed 11K to start. Old bad data getting fixed.
*Thread Reply:* @James Turner @deleted-U055HQT39PC @Tony @Carter Matzinger @Malissa, @deleted-U06C7A8PVLJ
These LR case types below were updated to correctly include TC. The rest matched.
*Thread Reply:* @Ryan @deleted-U055HQT39PC @Tony @Carter Matzinger @Malissa @deleted-U06C7A8PVLJ @Nick Ward Where did you place the backup of it before making changes?
Changing the fundamentals of how this page works, may have some unforeseen consequences and I need to see if these changes may break other things.
Changing the case names in lawruler can potentially break an unknown amount of other systems- my understanding is we would not be doing that anymore once a campaign went live?
When building roles and permissions for who can see individual rows on looker, we rely on using PCTID for associated law firms.. If the case type name is changed instead of properly associating a partner firm to a campaign, thing's won't display properly for people without full admin access.
Currently locked up on a few other projects, but if you let me know where the backup is, I'll look into it and make sure the changes aren't going to crash other systems.
*Thread Reply:* Check PCTID for those changes James. I just added TC to them after 2!d firm.
*Thread Reply:* @James Turner, for these we need to ensure lr_data is updating with their new names:
*Thread Reply:* All of them in LR got a TC before the final " - Shield Legal".
FYI @deleted-U06CVB2MHRN @deleted-U04GZ79CPNG @deleted-U06C7A8PVLJ @Zekarias Haile, this could cause reconcile issues due to some errors I found in SL Customer names over the years as well. Please double check your December 2023 to now reconciliation.
We will need to go back to 2021, 2022 and rest of 2023 too. I saw several TC deals needing clean up. I’ll setup a meeting on how to do that next week since we will need to check for declined and re-signed approvals again.
From LR? @James Turner could you pull that? All leads that have a system E sign date with a non-billable status?
@deleted-U04GZ79CPNG , so should we have him review these before 2024 and confirm if any are to be re-signed or should we just look at your list and ensure we have them declined in the system for sake of reconciliation?
Bouncing between a few things so I apologize if this has been said but I can definitely pull all leads that have a e-sign date but are non-billable. @Ryan @deleted-U04GZ79CPNG
Please lemme know the following factors: • Do you need a specific date range for leadcreatedate? • Do you need a specific date range for earlieste-signdate? • Do you need everything, or just specific campaigns, if so please list the lawruler casetypeids
Bouncing between a few things so I apologize if this has been said but I can definitely pull all leads that have a e-sign date but are non-billable. @Ryan @deleted-U04GZ79CPNG
Please lemme know the following factors: • Do you need a specific date range for leadcreatedate? • Do you need a specific date range for earlieste-signdate? • Do you need everything, or just specific campaigns, if so please list the lawruler casetypeids
Let’s get all of them and can you make a table dashboard for them? Something simple for TIP Operations so we can have this checked by the team?
That’s fine Brittany. @James Turner , your dashboard can be Jan 2024 bill date and forward as default filter.
@Zekarias Haile , next week we can do rest of 2023 reconciliation considering LR data accurate from November 2023 on back.