Ryan (ryan@themedialab.agency)
2025-01-02 10:05:53

@James Turner and @Nicholas McFadden, you can just read and watch, we'll post videos of TIP & SL EOM, these manual financial analyst tasks must be automated in both TIP BI and SL BI systems, this goes hand in hand with our io_app table concepts.

✅ deleted-U06C7A8PVLJ
Ryan (ryan@themedialab.agency)
2025-01-02 10:06:11

End Of Month Checklist:

TIP • Request Total Expense & Profit & Loss statement for month #C0231EJ3P7B channel and update TIP Op Expenses with per day numbe • ~Get total SL e-signs, calculate Director Commissions count and commission pool from TIP Dash into Commissionv2 table and Slack channel~ • ~Update _iolrcasetypesrevenuerates_ table with Effective Billable Rates and Commissions @deleted-U06C7A8PVLJ~ • ~Update DB with Staffing Fees (not Flatirons)~ • ~Update Looker Studio & Enterprise Formulas for Flatirons Overhead cost for Fi CSP, VSS & MRT~ • ~Update VSS Completions prices for Month for Billing~ • ~Receive TIP Total Expense, Update table and Post in TIPFinance Slack channel "Month is Closed." - TIP at $793K, SL at $796K~ _SL • ~Cost Per Intakes for TIP and Call Center Costs validated with actual CPI (doing side by side) @Zekarias Haile~ • ~Worked Leads for Campaigns active but not receiving leads from SL (e.g. Lejeune)~ • ~Update SL Past & Current Month CPIs,~ • ~Add TIP Worked Leads overages into SL mediarawv2~ • ~Validate Call Center Costs Match from SL & TIP Dashboards~ BLX • Invoices & Commissions for Client Services & Media Buying

Nick Ward (nicholas@tortintakeprofessionals.com)
2025-01-02 10:12:09

@Nick Ward has joined the conversation

deleted-U06C7A8PVLJ
2025-01-02 11:46:45

@Ryan

Zekarias Haile (zhaile@shield-legal.com)
2025-01-02 11:46:45

@here

James Turner (jturner@shield-legal.com)
2025-04-21 14:06:16

Just so I can make sure I caught everything, sharing what I understand next steps are for myself. If you have next steps let me know

For James:

Add all new campaigns into pctid ref sheet (easy)

make sure the numbers match from billableleadswithratesandlp and billable_leads on https://lookerstudio.google.com/u/0/reporting/a6a0f187-1619-478c-b1c9-6412a9e769fd/page/p_jjec6oa0tc (easy)

If not: catch why and fix (med)

See if leadspedia contract matches for leads that we changed from one contract to another, if it does awesome, if not build a new system to catch updates. When it updates, make sure that the new campaign info updates, and all the revue info updates. Removes old info (Advanced workflow)

Determine what data is needed from five9 data into billableleadswithratesandlp (Figure out that entire flow)(Med-Hard) Sum all of those data points per lead and put all of that in a summed five9 table (med) add all of the summed five9 data columns into billableleadswithratesandlp (med)

Determine next steps with DBT

Extra info: --------------

SELECT ** FROM tort-intake-professionals.Financial_Log_Dataset.Billable_Leads_With_Rates_and_lp WHERE marketingsource = 'Shield Legal' --AND leadid = 713755 --Contract ID should be in LP is 1737 AND earliestesign BETWEEN '2025-04-01' AND '2025-04-20' ORDER BY earliest_esign DESC

👍 Nick Ward
:thank_you: Nick Ward
James Turner (jturner@shield-legal.com)
2025-04-21 14:08:04

Just so I can make sure I caught everything, sharing what I understand next steps are for myself. If you have next steps let me know

For James:

Add all new campaigns into pctid ref sheet (easy)

make sure the numbers match from billableleadswithratesandlp and billable_leads on https://lookerstudio.google.com/u/0/reporting/a6a0f187-1619-478c-b1c9-6412a9e769fd/page/p_jjec6oa0tc (easy)

If not: catch why and fix (med)

See if leadspedia contract matches for leads that we changed from one contract to another, if it does awesome, if not build a new system to catch updates. When it updates, make sure that the new campaign info updates, and all the revue info updates (Advanced workflow)

Determine what data is needed from five9 data into billableleadswithratesandlp (Figure out that entire flow)(Med-Hard) Sum all of those data points per lead and put all of that in a summed five9 table (med) add all of the summed five9 data columns into billableleadswithratesandlp (med)

Determine next steps with DBT

Extra info: --------------

SELECT ** FROM tort-intake-professionals.Financial_Log_Dataset.Billable_Leads_With_Rates_and_lp WHERE marketingsource = 'Shield Legal' --AND leadid = 713755 --Contract ID should be in LP is 1737 AND earliestesign BETWEEN '2025-04-01' AND '2025-04-20' ORDER BY earliest_esign DESC

James Turner (jturner@shield-legal.com)
2025-04-21 14:08:41

Just so I can make sure I caught everything, sharing what I understand next steps are for myself. If you have next steps let me know

For James:

Add all new campaigns into pctid ref sheet (easy)

make sure the numbers match from billableleadswithratesandlp and billable_leads on https://lookerstudio.google.com/u/0/reporting/a6a0f187-1619-478c-b1c9-6412a9e769fd/page/p_jjec6oa0tc (easy)

If not: catch why and fix (med)

See if leadspedia contract matches for leads that we changed from one contract to another, if it does awesome, if not build a new system to catch updates. When it updates, make sure that the new campaign info updates, and all the revue info updates. Removes old info (Advanced workflow)

Determine what data is needed from five9 data into billableleadswithratesandlp (Figure out that entire flow)(Med-Hard) Sum all of those data points per lead and put all of that in a summed five9 table (med) add all of the summed five9 data columns into billableleadswithratesandlp (med)

Determine next steps with DBT

Extra info: --------------

SELECT ** FROM tort-intake-professionals.Financial_Log_Dataset.Billable_Leads_With_Rates_and_lp WHERE marketingsource = 'Shield Legal' --AND leadid = 713755 --Contract ID should be in LP is 1737 AND earliestesign BETWEEN '2025-04-01' AND '2025-04-20' ORDER BY earliest_esign DESC

Ryan (ryan@themedialab.agency)
2025-04-21 14:12:19

@James Turner, yes!

Ryan (ryan@themedialab.agency)
2025-04-21 14:12:21

Nice notes

James Turner (jturner@shield-legal.com)
2025-04-21 14:25:01

That lead got Declined which is why it is not showing up. Please provide another example of a lead that changed contracts.

image.png
Zekarias Haile (zhaile@shield-legal.com)
2025-04-21 15:45:13

*Thread Reply:* @James Turner try this one 708264

James Turner (jturner@shield-legal.com)
2025-04-21 15:50:16

*Thread Reply:* Cool what should it be? Like what was the contract initially and what was it changed to?

Zekarias Haile (zhaile@shield-legal.com)
2025-04-21 15:52:49

*Thread Reply:* Bard powerport-BCL-Simmons to Bard powerport- Crump-Lanier

James Turner (jturner@shield-legal.com)
2025-04-21 14:37:09

Numbers look good after updating the case types

image.png
Ryan (ryan@themedialab.agency)
2025-04-21 14:46:30

What’s the difference of 18 in the 965 to 947?

Can we find those 18 missing? @James Turner @deleted-U06C7A8PVLJ

James Turner (jturner@shield-legal.com)
2025-04-21 15:39:55

9 got changed to Signed and Declined 1 is a test lead that got re-triggered. 1 got changed to Unsigned 1 was a discrepancy caused by time zones that will be automatically resolved.

image.png
image.png
Ryan (ryan@themedialab.agency)
2025-04-21 15:55:51

*Thread Reply:* @deleted-U06C7A8PVLJ how many from 4/1/25 to 4/20/25 after those changes.

@James Turner , we need to have both match exactly even if those 12 are true.

Meaning, @deleted-U06C7A8PVLJ can process PG dB updates to reduce 965. And make sure @Zekarias Haile updates SL with any changes you make.

Then we can measure your sql validation knowing the total 18 difference @James Turner , following me?

👍 James Turner
James Turner (jturner@shield-legal.com)
2025-04-22 11:28:29

*Thread Reply:* Understood~ Please let me know when those are updated on the PG dB side @Zekarias Haile @deleted-U06C7A8PVLJ

👍 Zekarias Haile
deleted-U06C7A8PVLJ
2025-04-22 11:50:37

*Thread Reply:* What count are you getting with the SQL query?

deleted-U06C7A8PVLJ
2025-04-22 11:50:51

*Thread Reply:* I am getting 956 from april 1st to april 20th

deleted-U06C7A8PVLJ
2025-04-22 11:51:08

*Thread Reply:* This is based on the dashboard @James Turner

James Turner (jturner@shield-legal.com)
2025-04-22 12:02:38

*Thread Reply:* We love to see it!

image.png
image.png
deleted-U06C7A8PVLJ
2025-04-22 12:08:18

*Thread Reply:* To confirm SQL query and dashboard for V1.2 and V1.3 both match with 956

🙏 James Turner
deleted-U06C7A8PVLJ
2025-04-22 12:09:02

*Thread Reply:* If we do current date (as of today) that's when it may not match since statuses are being changed throughout the day but if we back log it to yesterday's date after I and zek apply the S&D in PGAdmin then we are in the clear

Ryan (ryan@themedialab.agency)
2025-04-22 13:18:17

*Thread Reply:* @deleted-U06C7A8PVLJ and @James Turner , excellent!!! Post this SQL to me here. I’ll setup time to record a DBT datastore configuration to create a PROD version for customer Shield Legal.

Or do you want me to invite you to a work session where I will focus on getting it done, and you learn by watching.

James Turner (jturner@shield-legal.com)
2025-04-22 13:18:56

*Thread Reply:* I'll get you that updated SQL shortly! Cleaning up all the column names so it's easier to use now! 😁

Ryan (ryan@themedialab.agency)
2025-04-22 13:24:43

*Thread Reply:* Ty, I'll review it one more time from schema perspective but we'll need the LeadspediaContractIDs issue resolved and Five9 talk-time minutes SUMMED at LRLeadIDs first, whether billable or non-billable in your base TIP tables, then Shield's billable sign ups and costs will be ready for SL BI financials dev. @Brian Hirst FYI

👍 James Turner
James Turner (jturner@shield-legal.com)
2025-04-22 14:00:04

*Thread Reply:* Five9 talk-time minutes summed per billable lead. Talk time revenue in USD has been added as well.

image.png
James Turner (jturner@shield-legal.com)
2025-04-22 14:03:06

*Thread Reply:* Currently I am only totaling talk time for leads that are billable.

Would you also like a table with all talk time totals, regardless of if a lead is billable or not?

Ryan (ryan@themedialab.agency)
2025-04-22 15:12:40

*Thread Reply:* @James Turner, my crystal ball says yes with a simple question from "Cameron or Tony", how much would TIP make if we billed for all "dial and talk time" on an order versus just what we signed and billed.

🔮 deleted-U06C7A8PVLJ
👍 James Turner
Zekarias Haile (zhaile@shield-legal.com)
2025-04-21 15:54:18

Bard powerport-BCL-Simmons to Bard powerport- Crump-Lanier

James Turner (jturner@shield-legal.com)
2025-04-22 11:37:51

Built a data-automated solution with @Nicholas McFadden,@deleted-U06C7A8PVLJ, and @deleted-U055HQT39PC regarding the Leadspedia contract matching issue. Awaiting examples to test the solution. This solution will also help ensure referrals are handled correctly.

👍 Nicholas McFadden, Zekarias Haile
:meow_attention: deleted-U06C7A8PVLJ, Ryan
👍:skin_tone_4: Ryan
👍:skin_tone_2: deleted-U06C7A8PVLJ
James Turner (jturner@shield-legal.com)
2025-04-22 11:37:58

Built a data-automated solution with @Nicholas McFadden,@deleted-U06C7A8PVLJ, and @deleted-U055HQT39PC regarding the Leadspedia contract matching issue. Awaiting examples to test the solution. This solution will also help ensure referrals are handled correctly.

James Turner (jturner@shield-legal.com)
2025-04-22 13:19:14

I'll get you that updated SQL shortly! Cleaning up all the column names so it's easier to use now! 😁

Brian Hirst (brian@themedialab.agency)
2025-04-22 13:25:01

@Brian Hirst has joined the conversation

James Turner (jturner@shield-legal.com)
2025-04-22 14:13:12

Schema has been updated on BillableLeadsWithRatesand_lp to • Have more easily understood column names • Include billable talk time totals • Include billable talk time revenue • Have un-necessary columns stripped for ease of use and speed.

Billable_Leads_With_Rates_and_lp SCHEMA.csv
James Turner (jturner@shield-legal.com)
2025-04-22 14:14:03

Schema has been updated on BillableLeadsWithRatesand_lp to • Have more easily understood column names • Include billable talk time totals • Include billable talk time revenue • Have un-necessary columns stripped for ease of use and speed.

James Turner (jturner@shield-legal.com)
2025-04-22 14:44:22

@Ryan Here is the updated query for you to get Shield Legal - Revenue Per Lead with an adjustable date window. It is saved https://console.cloud.google.com/bigquery?ws=!1m7!1m6!12m5!1m3!1stort-intake-professionals!2sus-west2!3sd8631ce6-9f9f-4cdf-aee2-f88a10c9ff47!2e1|HERE for easy access.

accounts.google.com
Google Cloud Platform
Google Cloud Platform lets you build, deploy, and scale applications, websites, and services on the same infrastructure as Google.
Original URL: https://console.cloud.google.com/bigquery?ws=!1m7!1m6!12m5!1m3!1stort-intake-professionals!2sus-west2!3sd8631ce6-9f9f-4cdf-aee2-f88a10c9ff47!2e1
James Turner (jturner@shield-legal.com)
2025-04-22 14:44:42

@Ryan Here is the updated query for you to get Shield Legal - Revenue Per Lead with an adjustable date window. It is saved https://console.cloud.google.com/bigquery?ws=!1m7!1m6!12m5!1m3!1stort-intake-professionals!2sus-west2!3sd8631ce6-9f9f-4cdf-aee2-f88a10c9ff47!2e1|HERE for easy access.

accounts.google.com
Google Cloud Platform
Google Cloud Platform lets you build, deploy, and scale applications, websites, and services on the same infrastructure as Google.
Original URL: https://console.cloud.google.com/bigquery?ws=!1m7!1m6!12m5!1m3!1stort-intake-professionals!2sus-west2!3sd8631ce6-9f9f-4cdf-aee2-f88a10c9ff47!2e1
deleted-U06C7A8PVLJ
2025-04-22 14:46:22

Thanks @James Turner Matches the dashboard :laptop_parrot:

image.png
image.png
👍 James Turner
:laptop_parrot: James Turner, deleted-U06C7A8PVLJ
Ryan (ryan@themedialab.agency)
2025-04-22 15:00:51

Excellent, checking in 10

Ryan (ryan@themedialab.agency)
2025-04-22 15:17:56

@James Turner

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

@James Turner, so we need the case type name from LawRuler plus any PCTID IDs on table BillableLeadsWithRatesandlp, so when a referral lead has no contractids, then we can know what case it is using in the datastore. And how often is this updated this table?

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

Otherwise, it looks fantastic!

James Turner (jturner@shield-legal.com)
2025-04-22 15:29:55

• BillableLeadsWithRatesand_lp is updated Hourly. ◦ Maximum delay before new information may be available is 2 hours due to synchronization between systems, but usually it will be within the hour.

I'll bring over the PCTID + case type information by end of day and let you know when it's done 👍

🙏:skin_tone_4: Ryan
:meow_party: deleted-U06C7A8PVLJ
James Turner (jturner@shield-legal.com)
2025-04-22 15:30:29

• BillableLeadsWithRatesand_lp is updated Hourly. ◦ Maximum delay before new information may be available is 2 hours due to synchronization between systems, but usually it will be within the hour.

I'll bring over the PCTID + case type information by end of day and let you know when it's done 👍

Ryan (ryan@themedialab.agency)
2025-04-22 15:31:23

@James Turner, THANK YOU

James Turner (jturner@shield-legal.com)
2025-04-22 16:32:33

@Ryan PCTID info has been brought in!

We may make some tweaks to clean up the broad, general, and sub tort categories to make it easier to use, I'll let you know 👍

James Turner (jturner@shield-legal.com)
2025-04-22 16:33:34

@Ryan PCTID info has been brought in!

We may make some tweaks to clean up the broad, general, and sub tort categories to make it easier to use, I'll let you know 👍

James Turner (jturner@shield-legal.com)
2025-04-22 16:33:34

@Ryan PCTID info has been brought in!

We may make some tweaks to clean up the broad, general, and sub tort categories to make it easier to use, I'll let you know 👍

James Turner (jturner@shield-legal.com)
2025-04-22 16:33:40
image.png
Ryan (ryan@themedialab.agency)
2025-04-22 16:39:19

Thank you!!!