Ryan (ryan@themedialab.agency)
2025-06-04 14:57:50

The instant soup cup burns number two, plus the other bard and instant soup cup prices would have to be changed according to the portal for May and June when you do your invoice update for May. Makes sense?

Ryan (ryan@themedialab.agency)
2025-06-04 14:58:20

Cameron keeps pushing down the prices when leads are easy to get.

Ryan (ryan@themedialab.agency)
2025-06-04 15:02:00

He just did Meta Lawsuit too, so you can see that we need to start prices cheap.

So Edward, one more to update June fin log once both ISCCB 2 and Meta prices are set by Malissa and crew.

👀 deleted-U06C7A8PVLJ
Malissa (malissa@shield-legal.com)
2025-06-04 15:11:15

@deleted-U06C7A8PVLJ can you update fin_log for June with these prices asap?

$0 per call $166 call center $30 commission

deleted-U06C7A8PVLJ
2025-06-04 15:13:17

@Malissa Reviewing

Ryan (ryan@themedialab.agency)
2025-06-04 15:14:49

@Malissa , which case type ?

👀 deleted-U06C7A8PVLJ
Ryan (ryan@themedialab.agency)
2025-06-04 15:20:20

@Malissa ☝️:skintone4:

Malissa (malissa@shield-legal.com)
2025-06-04 15:21:04

Instant Soup Cup Child Burns 2 - DL - Flatirons - Shield Legal Instant Soup Cup Child Burns - DL - Flatirons - Shield Legal Instant Soup Cup Child Burns - Gomez - Gomez

👀 deleted-U06C7A8PVLJ
Malissa (malissa@shield-legal.com)
2025-06-04 15:21:20

sorry had to step away for a second

Ryan (ryan@themedialab.agency)
2025-06-04 15:22:07

@deleted-U06C7A8PVLJ make these true for May and June MTD 2025 please.

👀 deleted-U06C7A8PVLJ
Ryan (ryan@themedialab.agency)
2025-06-04 15:22:29

Might as well avoid CR asking later for May to change with June.

deleted-U06C7A8PVLJ
2025-06-04 15:27:33

@Ryan Sure! Just to confirm I’m updating the correct fields for those case types: • $0 per call: I'll set the talk time field value to 0. • $166 Call center field: This corresponds to the io_casetype_billable_rate, correct? (I've included a screenshot for confirmation.) • $30 commission field: I assume this corresponds to the io_agent_cmmission_rate, correct? (I've included a screenshot for confirmation.) If that's correct, I'll proceed with the updates.

image.png
deleted-U06C7A8PVLJ
2025-06-04 15:28:02

I know these changes will take place in Pgadmin but just want to confirm I am looking at the right fields

Ryan (ryan@themedialab.agency)
2025-06-04 15:43:27

@deleted-U06C7A8PVLJ , like every Monty you MUST also run it twice to update gross revenue and total revenue, etc. like the SQL shows.

Correct on the rest.

deleted-U06C7A8PVLJ
2025-06-04 15:43:54

Ah thanks for the reminder for the twice part. Okay I know what we are updating then. TY. Will post once completed @Ryan

☝️:skin_tone_4: Ryan
deleted-U06C7A8PVLJ
2025-06-04 15:48:01

Okay I ran it twice to update the agent commission and contract revenue (call center commissions) so we should be good for that @Ryan @Malissa Going to work on updating the talk time next

deleted-U06C7A8PVLJ
2025-06-04 15:48:40

*Thread Reply:* I am also posting a before and after. (I took the raw data of the fields before the update and after the data) just in case we need it in the future.

Instant Cup Noodles - PGadmin - Updated Contract_Revenue and Agent_Commission (May 1st 2025 to June 4th 2025).csv
Instant Cup Noodles - PGadmin Backup (May 1 2025 to 06-04-2025).csv
deleted-U06C7A8PVLJ
2025-06-04 15:56:11

Last confirmation, I see a few different fields for "call or talk". Am I updating all of these fields with a value of $0:

  1. talktimerevenue
  2. gross_revenue
  3. net_revenue
  4. talktimeminutes I am attaching a screenshot of what I see to confirm. @Ryan
image.png
deleted-U06C7A8PVLJ
2025-06-04 15:58:32

*Thread Reply:* Since the SQL query I have to update talk time looks like this so I will simply filter it based on those case types + those dates and update to 0 for all of above I believe

image.png
Ryan (ryan@themedialab.agency)
2025-06-04 16:23:00

*Thread Reply:* Yes , and date ranges on May to June to date.

deleted-U06C7A8PVLJ
2025-06-04 16:41:06

*Thread Reply:* Thank you @Ryan Going to update those three fields now

Ryan (ryan@themedialab.agency)
2025-06-04 16:48:22

*Thread Reply:* Great. Thank you.

✅ deleted-U06C7A8PVLJ
Ryan (ryan@themedialab.agency)
2025-06-04 16:22:36

Not minutes, yes to rest.

Ryan (ryan@themedialab.agency)
2025-06-04 16:24:21

Update done and correct @deleted-U06C7A8PVLJ for May and June?

Ryan (ryan@themedialab.agency)
2025-06-04 16:25:15

@deleted-U06C7A8PVLJ And for May EOM, if Malissa am said prices are good to go yet?

If so, let’s get the sql ready to do the same JUST for May.

deleted-U06C7A8PVLJ
2025-06-04 16:42:57

Going to work on updating the talk time now @Ryan As for May EOM for prices, @Malissa is still working on getting that per my last conversation with her

deleted-U06C7A8PVLJ
2025-06-04 16:43:17

Once ready I can run the SQL for just May

Ryan (ryan@themedialab.agency)
2025-06-04 16:48:04

Thank you

deleted-U06C7A8PVLJ
2025-06-04 16:55:23

Okay I updated the talk time for these three fields:

  1. talktimerevenue
  2. gross_revenue
  3. net_revenue These were placed with the value of 0 for May 1st of 2025 to current date for those three case types only. I am going to also attach the before and after changes in a .csv just for our future reference if needed.
Instant Cup Noodles - PGadmin - Talk Time After Update (05-01-2025 - 06-04-2025).csv
Instant Cup Noodles - PGadmin - Talk Time Before Update (05-01-2025 - 06-04-2025).csv
deleted-U06C7A8PVLJ
2025-06-04 16:55:42

@Ryan Updates are now complete for talk time, call center and agent commissions

Ryan (ryan@themedialab.agency)
2025-06-04 17:01:02

Great, will confirm sold leads and update CC table for Cam in SL!

🙌:skin_tone_2: deleted-U06C7A8PVLJ
slackbot
2025-06-10 10:52:08

Ryan Vaspra moved some of the messages from this conversation to one with #mpdm-ryanvaspra--malissa--zhaile--rose--edwardw-1.

Ryan (ryan@themedialab.agency)
2025-06-11 08:10:10

@Zekarias Haile and @deleted-U06C7A8PVLJ, Call Costs in SL match at 995K and TIP at 992K, the 3K difference is for leads we sold with a CPL in Shield but not converted and billed in TIP. Updating TIP Total Operating Expenses now.

✅ Zekarias Haile
Ryan (ryan@themedialab.agency)
2025-06-11 08:10:24

@deleted-U06C7A8PVLJ, you can let Rose know we are closed when you get in today. Thanks everyone!

✅ deleted-U06C7A8PVLJ
deleted-U06C7A8PVLJ
2025-06-11 09:50:32

@Ryan Sounds good. Great job team !!

Ryan (ryan@themedialab.agency)
2025-06-11 09:56:31

@Zekarias Haile , for that $3K we could make the ones missing in TIP, SL cpl value zero and that would bring it down to $992, could you match

Zekarias Haile (zhaile@shield-legal.com)
2025-06-11 10:17:42

@RyanWhich $3k is that? ... I only see $1200 difference

image.png
image.png
Ryan (ryan@themedialab.agency)
2025-06-11 10:23:57

I was using 995k - 992k to guesstimate the $3k, see if you can match TIP at ~992K

Zekarias Haile (zhaile@shield-legal.com)
2025-06-11 10:31:45

okay.

Ryan (ryan@themedialab.agency)
2025-06-11 12:03:15

Heads up, spoke to Cam and Marc with Rose, we are going to get rid of talk time minutes $2.00 per rate for May 2025 and June MTD. That means we will:

  1. Update BigQuery to remove $2.00 talk time from the new BI TIP Portal App @James Turner and @Malissa, he can help do it with SQL quickly (he's not in this chat FYI, I'll let him know you'll see him)
  2. Update PG financiallog and zero out talktimerevenue, recalculate grossrevenue and net_revenue, but keep track of talk time minutes each month. (MUST update routine SQL) @deleted-U06C7A8PVLJ
  3. Once we have new, talk-time-rev'less numbers, I'l let Rose know, and @Zekarias Haile, you can update your sheet where the $992K will come down by $300 to $400K and update SL cccostscpi table to reflect and match. This will allow ownership to see better numbers clearer in the old system, ensure SL is not paying twice for TIP expense losses, and Rose was trained on using the Other Revenue and Operating Expenses in the TIP Retool Portal app.

Now you 3 are prepared for the meeting.

👀 Zekarias Haile, deleted-U06C7A8PVLJ
deleted-U06C7A8PVLJ
2025-06-11 13:25:58

*Thread Reply:* Update: Per our meeting for my task, just touch Shield Legal / Intake case types and not any secondaries; zero out talk time revenue and recalculate grossrevenue and netrevenue

Ryan (ryan@themedialab.agency)
2025-06-11 12:04:02

Heads up, spoke to Cam and Marc with Rose, we are going to get rid of talk time minutes $2.00 per rate for May 2025 and June MTD. That means we will:

  1. Update BigQuery to remove $2.00 talk time from the new BI TIP Portal App @James Turner and @Malissa, he can help do it with SQL quickly (he's not in this chat FYI, I'll let him know you'll see him)
  2. Update PG financiallog and zero out talktimerevenue, recalculate grossrevenue and net_revenue, but keep track of talk time minutes each month. (MUST update routine SQL) @deleted-U06C7A8PVLJ
  3. Once we have new, talk-time-rev'less numbers, I'l let Rose know, and @Zekarias Haile, you can update your sheet where the $992K will come down by $300 to $400K and update SL cccostscpi table to reflect and match. This will allow ownership to see better numbers clearer in the old system, ensure SL is not paying twice for TIP expense losses, and Rose was trained on using the Other Revenue and Operating Expenses in the TIP Retool Portal app.

Now you 3 are prepared for the meeting.

Ryan (ryan@themedialab.agency)
2025-06-11 13:02:33

@Malissa, we'll send you recording since your OOTO

Ryan (ryan@themedialab.agency)
2025-08-13 13:17:41

@Zekarias Haile @deleted-U06C7A8PVLJ, be prepared to update TIP fin_log and SL CC cpl costs for some, Cameron wants TIP to drop prices once Zek is done July 2025.

@Malissa, feel like every damn SL deal should be just lowest price from start moving forward. So that maybe the question to Cam isn't what is "cheaper and retrofit" but "what can TIP charge more for to account for the actual cost to get retainers"? You know?

✅ deleted-U06C7A8PVLJ, Zekarias Haile
Malissa (malissa@shield-legal.com)
2025-08-13 13:26:47

@Ryan I'm fine with adjusting what we need to. Not sure the best method since it sometimes feels like a moving target

🎯 deleted-U06C7A8PVLJ, Ryan
Zekarias Haile (zhaile@shield-legal.com)
2025-08-13 15:39:27

@Ryan do we know what adjustment we are going make yet?

Ryan (ryan@themedialab.agency)
2025-08-13 17:17:41

No, not yet. Waiting on Cam

👍:skin_tone_3: Zekarias Haile
Malissa (malissa@shield-legal.com)
2025-08-14 18:48:38

hi @deleted-U06C7A8PVLJ and @Zekarias Haile

I've attached a file with active campaigns that i need your help repricing. The new rate is in column b.

please update these in revenue_rates table with SQL to reprice the invoices.

@Ryan i've updated most everything else. there are so many of these campaign types that I just can't do them all on my own. Tony said we can revisit if it doesn't work.

I did find that the call time charge of 2 dollars is still floating around in some. i removed what i found.

Rates.xlsx
👀 deleted-U06C7A8PVLJ, Zekarias Haile
deleted-U06C7A8PVLJ
2025-08-14 18:57:11

Sounds like a plan. To confirm, I will update call center costs for those campaigns based on column B for the month of July only?

Malissa (malissa@shield-legal.com)
2025-08-14 18:58:27

Whatever Ryan needs you to do. If you go to the other tab, there’s every campaign. Just in case I messed anything up lol

deleted-U06C7A8PVLJ
2025-08-14 19:00:08

Oh okay let me check with Ryan @Ryan Does what I mention above sound correct? Thank you @Malissa

Ryan (ryan@themedialab.agency)
2025-08-14 22:31:47

Edward, you take her new price and update from the old price, so that we can re-calculate the old system. So it should be updated in the new systems. I owe revenue rates table, and it should also be done just like you do end of month.

deleted-U06C7A8PVLJ
2025-08-14 23:27:45

Okay I will update it for the month of july then

Ryan (ryan@themedialab.agency)
2025-08-15 01:10:23

Sounds good Edward, thank you.

deleted-U06C7A8PVLJ
2025-08-15 12:40:53

Hi @Malissa — I was looking more closely at the rates.xlsx and tried to update it, but I noticed the file lists contract names rather than campaign / case type names. I’m having trouble figuring out which contract belongs to which case type. @Ryan — I know I’m just updating the July numbers, so would it be safe to update the call center costs for any of the following categories? • Firefighting Foam • Chowchilla • IL JDC • MI Juv Hall • MI YTC As long as the case types fall under these categories, I should just use the rates within that excel sheet? Or it has to be very specific AFFF, chowchilla, etc ?

deleted-U06C7A8PVLJ
2025-08-15 12:41:03

@Zekarias Haile FYI

Malissa (malissa@shield-legal.com)
2025-08-15 12:44:34

@deleted-U06C7A8PVLJ @Ryan so what i have to do is i have to find all the campaigns with that category and then i have to update them individually.

Malissa (malissa@shield-legal.com)
2025-08-15 12:45:08

i think i did all of the MI Juv Hall and MI YTC but i might have put the wrong date. I was mentally gone when i was working on it late yesterday.

Ryan (ryan@themedialab.agency)
2025-08-15 12:45:22

@Malissa, use this Dashboard, click Source to pop out contract and that should make it faster:

https://lookerstudio.google.com/reporting/a6a0f187-1619-478c-b1c9-6412a9e769fd/page/p_vojobl6ytc?pli=1

Ryan (ryan@themedialab.agency)
2025-08-15 12:45:32

Click + on Parent Case Type too

Ryan (ryan@themedialab.agency)
2025-08-15 12:45:49

Filter to Source = Shield legal, Month July 2025, etc.

Ryan (ryan@themedialab.agency)
2025-08-15 12:45:50

🙂

Malissa (malissa@shield-legal.com)
2025-08-15 12:46:05

one of the thoughts i've had with updating pricing in the retool app is that it's no longer sustainable.

Ryan (ryan@themedialab.agency)
2025-08-15 12:46:07

@deleted-U06C7A8PVLJ Same would work for you.

Malissa (malissa@shield-legal.com)
2025-08-15 12:46:32

Like... i spent 3 hours working on it yesterday and didn't get done.

Ryan (ryan@themedialab.agency)
2025-08-15 12:46:45

You may need a Spreadsheet style UI table to do things in bulk over time, but if you can add them weekly, any large repricings, we are glad to handle Malissa

Ryan (ryan@themedialab.agency)
2025-08-15 12:47:07

@Malissa, use this Dashboard, click Source to pop out contract and that should make it faster:

https://lookerstudio.google.com/reporting/a6a0f187-1619-478c-b1c9-6412a9e769fd/page/p_vojobl6ytc?pli=1

Click + on Parent Case Type too

Filter to Source = Shield legal, Month July 2025, etc.

Malissa (malissa@shield-legal.com)
2025-08-15 12:47:11

cool. a spreadsheet is one of the ideas i had that i was going to share with you and brian. i got a few others

Malissa (malissa@shield-legal.com)
2025-08-15 12:47:37

when it's a big all at once thing like tony and i were working on, it's just... i need a bulk upload lol

Ryan (ryan@themedialab.agency)
2025-08-15 12:47:52

This will make it super fast to tell @deleted-U06C7A8PVLJ which prices to update in the iocasetyperevrates table and then recalculate financiallog for AWS TIP DB.

deleted-U06C7A8PVLJ
2025-08-15 12:50:45

@Malissa Let me know if this would make it easier for you and I. I ran a SQL to see all the sign ups for just july. Then I looked at your categories (afff, chowchilla, etc) and if it has that within the name I kept it in this .xlsx From there I think this should simplify it

July Case Types.xlsx
😭 Malissa
❤️ Malissa
deleted-U06C7A8PVLJ
2025-08-15 12:51:14

Not sure if this is exactly the same as the contract names you provided earlier ago but this is all the sign ups for july 2025 for those categories

Malissa (malissa@shield-legal.com)
2025-08-15 12:51:32

it's a start @deleted-U06C7A8PVLJ! We will make it do!

Malissa (malissa@shield-legal.com)
2025-08-15 12:51:49

it's going to be a bit before i can get to it though. doing a quick audit

deleted-U06C7A8PVLJ
2025-08-15 12:52:52

I understand sorry about the overall situation. The sql code I run looks for campaign names and not contract names and I am not too familiar with which contract name matches with campaign, etc

👍 Malissa
Malissa (malissa@shield-legal.com)
2025-08-15 13:01:17

Just finished the audit. working on this one now

Malissa (malissa@shield-legal.com)
2025-08-15 13:09:27

@deleted-U06C7A8PVLJ i added a few Michigan campaigns. I think we got everything

Malissa (malissa@shield-legal.com)
2025-08-15 13:09:36
July Case Types.xlsx
deleted-U06C7A8PVLJ
2025-08-15 13:14:19

Thank you @Malissa this looks great ! I will do the update here shortly cameron is asking me to do something right away so I will update everyone once those changes have been made

deleted-U06C7A8PVLJ
2025-08-15 14:38:27

Updates were made. I am posting sql query as reference for myself in the future. @Zekarias Haile FYI

Call Center Costs and Agent Commission July 2025 - Update.sql
Zekarias Haile (zhaile@shield-legal.com)
2025-08-15 14:46:22

Thank you @deleted-U06C7A8PVLJ . I will start updating the cpl accordingly.

🙌:skin_tone_2: deleted-U06C7A8PVLJ
Ryan (ryan@themedialab.agency)
2025-08-27 14:59:15

@Malissa, can you please make sure all Case Types active in Page 0 for August are fully seutp with hard costs, rates, talk time, fully, category, category points, etc, are all setup in TIP BI App portal?

Here is the sheet that has 2 tables, ones missing Billable Rates and the second, Hard Costs missing hard costs. We need this updated for August to continue to validate TIP financials.

Can you aim to have that done by EOW? 🙂

👍 Malissa
Malissa (malissa@shield-legal.com)
2025-08-28 16:15:02

*Thread Reply:* i think i got everything @Ryan. Just waiting for the sheet to refresh

✅ Ryan
Ryan (ryan@themedialab.agency)
2025-08-28 16:16:32

*Thread Reply:* @James Turner, ☝️:skintone4:

Malissa (malissa@shield-legal.com)
2025-08-27 15:24:50

236 base rate right? @Ryan

Malissa (malissa@shield-legal.com)
2025-08-27 15:26:20

and 0 talk time

Malissa (malissa@shield-legal.com)
2025-08-27 15:34:08

^for intakes

Ryan (ryan@themedialab.agency)
2025-08-27 15:43:47

Yes

👍 Malissa
Ryan (ryan@themedialab.agency)
2025-08-27 15:43:56

Unless Tony says otherwise

Malissa (malissa@shield-legal.com)
2025-08-27 15:44:33

he hasn't said anything about that so far. i can bother him again

Ryan (ryan@themedialab.agency)
2025-08-27 15:44:48

He called me today on Milestone 2

👍 Malissa