Ryan (ryan@themedialab.agency)
2025-05-29 13:57:19

@Nicholas McFadden Can you send me a status update on Integrations for Crump, Data Warehouse for Crump, Shield Base Financials tables and models, plus Flatirons team?

@Brian Hirst, is retool for Media Buyer Spend ready?

Ryan (ryan@themedialab.agency)
2025-05-29 13:57:55

Canceled meeting for Slack post with todays busyness

Brian Hirst (brian@themedialab.agency)
2025-05-29 14:36:30

Working on it Spend data should be populated later today

Nicholas McFadden (nickmcfadden@shield-legal.com)
2025-05-29 14:59:46

@Zekarias Haile and I have a working session each day now to finalize the Financials. We are waiting on an update from @Brian Hirst regarding the retool to GCP for MB spend. It has been slow going with BCL due to some reconciliation and Smart Advocate being a pain but we have caught up and completed all existing integrations. I would like to have a convo with you around the DW. We are going to start by pulling in their SA data and start building some basic dashboards/reports for them. The Pentas Mullens data will be hard to acquire but we can discuss more when you have some time.

Nicholas McFadden (nickmcfadden@shield-legal.com)
2025-05-29 15:01:47

Aidan hasn’t been approved yet for Flatirons but Edward has been working hard on some reconciliations. Everything is good with Flatirons right now

✅ Ryan
Ryan (ryan@themedialab.agency)
2025-05-29 16:08:00

I’m in town next week, let’s meet. Andy Rogers can get us Pintas Mullins reports.

👍 Nicholas McFadden
Nicholas McFadden (nickmcfadden@shield-legal.com)
2025-05-29 16:53:51

Ok great lets talk more then

🙏:skin_tone_4: Ryan
Zekarias Haile (zhaile@shield-legal.com)
2025-06-11 13:40:43

@Brian Hirst just wanted to follow and see when you expect the MB spend table to be ready

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

@Brian Hirst, need this ASAP

Ryan (ryan@themedialab.agency)
2025-06-11 13:41:27

AND NEED TIP Status Rates working too.

Ryan (ryan@themedialab.agency)
2025-06-11 13:41:34

AND NEED TIP Status Rates working too.

Brian Hirst (brian@themedialab.agency)
2025-06-11 13:41:45

@Ryan what doesn't work on status rates?

Ryan (ryan@themedialab.agency)
2025-06-11 13:42:05

See portal, no data loads, fields out of order

Ryan (ryan@themedialab.agency)
2025-06-11 13:42:48

But SL financials need media spend so we can get daily financials modeled. FYI @Nicholas McFadden

Brian Hirst (brian@themedialab.agency)
2025-06-11 13:45:02

@Ryan @Zekarias Haile Idk what happened to status rates table but looks like everything is gone. I still have the codes so that can be up and running again in a few hours. MB spend app works, how much data do you want entered in?

Brian Hirst (brian@themedialab.agency)
2025-06-11 13:45:35

June to now?

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

Yes, @James Turner , can you help herebok seed data

Brian Hirst (brian@themedialab.agency)
2025-06-12 12:01:54

@Ryan @Zekarias Haile Seeding data but the issue I was running into was not all verticals are in bigquery. Fix has been a block but working on that now

🙏:skin_tone_3: Zekarias Haile
🙏:skin_tone_4: Ryan
Nicholas McFadden (nickmcfadden@shield-legal.com)
2025-06-12 12:39:26

found the issue

Nicholas McFadden (nickmcfadden@shield-legal.com)
2025-06-12 12:39:38

redeploying now

👍:skin_tone_3: Zekarias Haile
👍:skin_tone_4: Ryan
Brian Hirst (brian@themedialab.agency)
2025-06-12 12:42:09

Sweet

Brian Hirst (brian@themedialab.agency)
2025-06-12 12:42:50

Thanks

Zekarias Haile (zhaile@shield-legal.com)
2025-06-30 14:13:34

``` WITH dedupedmediabuyerspend AS ( SELECT DISTINCT uniqueidentifier, mediabuyer, agency, vertical, platform, spenddate, submitdate, spendamount, vertical_id FROM shield-legal-bi.sl_prod_application.io_media_buyer_retool_prod )

SELECT SPLIT(SPLIT(a.s1, '-')[SAFEOFFSET(1)], '')[SAFEOFFSET(0)] AS mediasource, SPLIT(SPLIT(a.s1, '-')[SAFEOFFSET(2)], '')[SAFEOFFSET(0)] AS mbidnext2, a.leadID, a.firstname, a.lastname, a.emailaddress, a.phonehome, a.zipcode, a.campaignName, a.buyerName, a.buyerID, a.contractID, a.createdOn, a.createdOnUTC, a.dateSold, a.price, a.verticalName, a.s1, a.s2, a.leadscore AS soldleadscore, b.leadspedialeadid, b.leadspediafullid, b.fivetranid, b.fivetransynced, b.fivetrandeleted, b.leadspedias1, -- b.leademail, b.lawrulerleadcreatedate, b.contactfirstname, b.contactlastname, b.marketingsource, b.earliestesign, b.lawrulercasetypeid, b.lawrulerleadcreatedate, b.lawrulerleadid, b.contractids, c.price AS contractprice, m.verticalid FROM shield-legal-bi.tip_bigquery_shield_datastore_tip_external.shield_legal_datastore AS b LEFT JOIN shield-legal-bi.leadspedia.all_sold_leads AS a ON b.leadspedialeadid = a.leadID LEFT JOIN shield-legal-bi.leadspedia_all_contracts.leadspedia_all_contracts AS c ON a.contractID = CAST(c.contractid AS STRING)

LEFT JOIN
dedupedmediabuyerspend AS m --- de-dupe spends ON CAST(m.verticalid AS STRING) = a.verticalID LEFT JOIN shield-legal-bi.sl_dashboard.orders_paid_spent AS o ON o.cprlpcontractids = a.contractID WHERE b.fivetran_deleted = false```

☝️:skin_tone_4: Ryan
Ryan (ryan@themedialab.agency)
2025-07-11 10:56:55

*Thread Reply:* @Zekarias Haile, FYI @Nicholas McFadden,

This looks good. Please talk to James Turner @Zekarias Haile and get the field for call center costs, it's called Io Casetype Billable Rate and add that column, then what about the rows from LawRuler that are referrals / inbound calls, where we do not have a lead in leadspedia?

👍:skin_tone_3: Zekarias Haile
Ryan (ryan@themedialab.agency)
2025-07-11 10:59:53

*Thread Reply:* The count of e-signs from LawRuler in this table should be matching this page when filtered to "Is Previous" "Day": https://shieldlegal.cloud.looker.com/dashboards/118?Leadspedia+S1=&Leadspedia+Lead+ID=&Marketing+Source=Shield+Legal&Earliest+Esign=last+day&General+Tort+Type=&Lawruler+Case+Type+Name=|https://shieldlegal.cloud.looker.com/dashboards/118?Leadspedia+S1=&Leadspedia+Lead+ID=&Mark[…]n=last+day&General+Tort+Type=&Lawruler+Case+Type+Name=

Old DB has 181 from yesterday, new BI DB has 181, and your SQL should have 181. @Zekarias Haile, lets get that updated so that your summary SQL shows 181, it has 184.

Zekarias Haile (zhaile@shield-legal.com)
2025-07-11 11:10:57

*Thread Reply:* Okay, will work on that

Ryan (ryan@themedialab.agency)
2025-07-11 11:17:11

*Thread Reply:* Looking promising, keep it going. 💪:skintone4:

🙌:skin_tone_3: Zekarias Haile
Zekarias Haile (zhaile@shield-legal.com)
2025-07-15 12:16:51

*Thread Reply:* @Ryan the reason why we are not getting the correct count for the day is because some leads in shield_legal_datastore are showing signed as the next day. Here's one example

image.png
image.png
Zekarias Haile (zhaile@shield-legal.com)
2025-07-15 12:21:42

*Thread Reply:* So since the SL datastore is our source table that feed our financial table and wondering what the fix would be for this to get the right count

Ryan (ryan@themedialab.agency)
2025-07-15 12:33:21

*Thread Reply:* Ask James Turnerto ensure our dates are UTC Pacific Time possibly? Have him validate his side, side be side. I spoke to him and knows he will help you.

Zekarias Haile (zhaile@shield-legal.com)
2025-07-15 12:36:25

*Thread Reply:* Okay, will do. Thanks!

Zekarias Haile (zhaile@shield-legal.com)
2025-06-30 14:15:08

``` WITH dedupedmediabuyerspend AS ( SELECT DISTINCT uniqueidentifier, mediabuyer, agency, vertical, platform, spenddate, submitdate, spendamount, vertical_id FROM shield-legal-bi.sl_prod_application.io_media_buyer_retool_prod )

SELECT SPLIT(SPLIT(a.s1, '-')[SAFEOFFSET(1)], '')[SAFEOFFSET(0)] AS mediasource, SPLIT(SPLIT(a.s1, '-')[SAFEOFFSET(2)], '')[SAFEOFFSET(0)] AS mbidnext2, a.leadID, a.firstname, a.lastname, a.emailaddress, a.phonehome, a.zipcode, a.campaignName, a.buyerName, a.buyerID, a.contractID, a.createdOn, a.createdOnUTC, a.dateSold, a.price, a.verticalName, a.s1, a.s2, a.leadscore AS soldleadscore, b.leadspedialeadid, b.leadspediafullid, b.fivetranid, b.fivetransynced, b.fivetrandeleted, b.leadspedias1, -- b.leademail, b.lawrulerleadcreatedate, b.contactfirstname, b.contactlastname, b.marketingsource, b.earliestesign, b.lawrulercasetypeid, b.lawrulerleadcreatedate, b.lawrulerleadid, b.contractids, c.price AS contractprice, m.verticalid FROM shield-legal-bi.tip_bigquery_shield_datastore_tip_external.shield_legal_datastore AS b LEFT JOIN shield-legal-bi.leadspedia.all_sold_leads AS a ON b.leadspedialeadid = a.leadID LEFT JOIN shield-legal-bi.leadspedia_all_contracts.leadspedia_all_contracts AS c ON a.contractID = CAST(c.contractid AS STRING)

LEFT JOIN
dedupedmediabuyerspend AS m --- de-dupe spends ON CAST(m.verticalid AS STRING) = a.verticalID LEFT JOIN shield-legal-bi.sl_dashboard.orders_paid_spent AS o ON o.cprlpcontractids = a.contractID WHERE b.fivetran_deleted = false```

Zekarias Haile (zhaile@shield-legal.com)
2025-06-30 14:16:09

@Ryan please take a look when you get a chance ^

Ryan (ryan@themedialab.agency)
2025-06-30 14:34:38

Is the data store table updating and reflecting latest data @Zekarias Haile ?

Zekarias Haile (zhaile@shield-legal.com)
2025-06-30 14:35:48

Yes

Ryan (ryan@themedialab.agency)
2025-06-30 14:39:49

Great. @Zekarias Haile , how does it look in revenue and media spend for yesterday, assuming you out some real spend in to validate a prior day? Got that to post here?

Zekarias Haile (zhaile@shield-legal.com)
2025-06-30 14:42:42

I have query that shows yesterday sign up and revenue. I will post that when I get back from lunch.

🙌:skin_tone_4: Ryan
Zekarias Haile (zhaile@shield-legal.com)
2025-06-30 16:18:53

SELECT lawruler_lead_id, marketing_source,campaignName, earliest_esign, contract_price,media_source,mb_id_next_2, leadID,first_name, last_name, spend_amount, spend_date FROM ( select**, row_number()over (partition by leadspedia_lead_id order by earliest_esign DESC) AS row_num FROM`shield-legal-bi.sl_staging.sl_new_fin` WHERE DATE(earliest_esign) = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) ) where row_num = 1 order by campaignName ASC

Brian Hirst (brian@themedialab.agency)
2025-08-21 08:35:39

For today’s meeting, let’s use July’s financials for MB spend if we are getting into that part. August is behind right now as I’ve been messing with different ways to figure out how to break the table and I would need to spend more time than I have to triple check its accuracy. I’ll spend the morning doing a basic audit/review of July’s MB spend. August’s can be up to date by tomorrow afternoon.

Ryan (ryan@themedialab.agency)
2025-08-21 08:47:44

Thank you.

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

And we need TIP status rates UI fixed to work @Brian Hirst , did James mention that?

Brian Hirst (brian@themedialab.agency)
2025-08-21 08:53:25

He did I’ll fix today.

🙏:skin_tone_4: Ryan