get zip open the zip and navigate as follows: Microsoft.flows flows open scary folder
search: "type":"Http" - locate the first instance, find where it says body after that and copy from the first curly bracket to the first instance of 3 curly brackets- we are looking to get the entire map/payload/data section
create a new json file in gcp-integrations-main called mpa_body.json, copy paste into there
open lawruler
go to setup case types, milestones, task list items search for the case. obtain case ID that you are working on next to it - write it down: 1766
go to automations ai and automation campaigns put in type of case locate the column that ends with webhook English Click pencil email content tab Copy the content shown go back to vscode, create a new txt file paste content
run mpa map cd to the gcp-integrations root $env:PYTHONPATH="." python .\tools\mpamap.py mpabody.json .\lawruler_body.txt
confirm change worked in mpa_body.json "First": "Contact/FirstName", "Last": "Contact/LastName",
create new folder called intakeXXX where XXX is the case ID you wrote down earlier move mpabody.json into your new folder change it's name to mapping.json
go to GitHub, gcp-integrations/intake_1811 download main.py, put it in your newly created folder open main.py in vscode delete line 15
FIND THE KEY go back to definition.json ctrl f search for: "name":"integration_key" and copy the long value that comes after that. ex: 5bca9b1188dc4e9ea426d696caf55c81 Navigate back to main.py inside of the folder you created change the key in line 14, to the key that you got previously.
python -c "import json;f=open('definition.json', 'r+');d=json.load(f);f.seek(0);json.dump(d,f,indent=4)"
if " integeration_key" is not pulling search "url" in the definition
we sent the test including documents successfully here's the ID"s in ur system to look. Id and matter ID below.
id:1002968 matterId:41930c7f22e24feda775b99c4647d9b7
https://youtu.be/GwSnAwsyhZY?list=PLHT5rv7PEE4N3ol8lBoxHBmzWvVW2UwmC
https://youtu.be/lturMFtmIsI?feature=shared
https://youtu.be/PuLUcmAzyO8?feature=shared
https://youtu.be/1fY1A8SRflI?feature=shared
https://www.cloudskillsboost.google/paths/420/course_templates/964/labs/486503
^Cloudskillsboost.google pas,, User- hze@out
Get Active or Archived updated in sheet Share Editor Permissions to @Zekarias Haile Have Zeke create new ones, merge dupes, check SQL for valid filtering
https://www.youtube.com/watch?v=-LNc1IvrC20&t=404s
I was requested to build dashboard using DataStudio template we created. create this one from BigQuery instead of Post
Here is a Custom Query SQL, you will ONLY use the metric columns called "c" such as "cdaily_revenuer" is what the client sees as "Total Budget Spent / Cost To Date".
SELECT ** FROM shield-legal-bi.sldashboard.financialdetails WHERE customer LIKE '%Nations/Van/Liu%' AND campaign LIKE '%Firefighting Foam%';
Elaborate this for me
https://youtu.be/5ZtA0QMawqc?feature=shared
If the Customer field in SL mediarawv2 or model.sl_dashboard.financials containts LIKE '%(TC)% OR '%/TC%' or TC/
call it an internal expense
SELECT ** FROM shield-legal-bi.sldashboard.financialdetails WHERE customer LIKE '%Flatirons%' AND campaign LIKE '%Firefighting Foam%';
https://www.zillow.com/homedetails/9620-W-Russell-Rd-APT-1050-Las-Vegas-NV-89148/66671814_zpid/
https://www.zillow.com/apartments/las-vegas-nv/vintage-at-the-lakes-apartments/5Xhx4m/
https://www.zillow.com/homedetails/7255-W-Sunset-Rd-APT-2062-Las-Vegas-NV-89113/66862437_zpid/
https://www.reddit.com/r/Thailand/comments/14us55x/cigna_global_health_any_objections_expat/
https://www.apartments.com/8695-tomnitz-ave-las-vegas-nv/nh45f1p/
https://www.apartments.com/9050-w-warm-springs-rd-las-vegas-nv/sqdkw74/
"Abuser 2 - Were they male or female?": "c-14965", "Abuser 4 - Were they male or female?": "c-14965",
8799 more than 3
Abuser 2 sex 9447
How many questions do
check the to see if the new custom ID if filled with existing lead ID
SELECT campaign, sum(cdailyleads)
FROM dashboard.matviewfiananicalsdetails
Where revdate BETWEEEN '2024-12-01' AND '2024-12-31'
AND saletype ='contract'
AND cdailyleads > 0
GROUP BY campaign
ORDER BY campaign ASC;
we were trying to send this: contenttype = mimetypes.guesstype(attachment[0])[0] file = { 'Id': intakelead["externalid"], 'filename': attachment[0], 'externalId': intakelead['leadid'], 'shortDescription': 'Retainer' if 'SignedContract' in attachment[0] else '', } response = requests.post('https://baileyglasserllp.my.salesforce-sites.com/customapi/services/apexrest/brioapi/SingleFileWebhook', params=file, headers={'sforgid': 'BRIOTEST', 'apikey': key, 'Content-Type': contenttype}) on the main.py but we are getting this Bailey Glasser: [["665129-Ronald2 Davit3-IntakeForm.pdf", "<Response [400]>", "{\"error\": \"id is missing\", \"success\": false}"]]. write email mentioning the above issue ChatGPT said:
ChatGPT Here's a formal email template you can use to report the issue you're encountering:
Subject: Issue with File Upload - Missing "id" Field in Request
Dear [Recipient's Name],
I hope this message finds you well.
We are encountering an issue while sending a file via the API to the endpoint <https://baileyglasserllp.my.salesforce-sites.com/customapi/services/apexrest/brioapi/SingleFileWebhook>. Specifically, we are receiving the following response:
<Response [400]>, "{\"error\": \"id is missing\", \"success\": false}
The error message suggests that the "id" field is missing in the request, but it is being included as 'Id' (capitalized) in the file dictionary:
file = {
'Id': intake_lead["external_id"],
'filename': attachment[0],
'externalId': intake_lead['lead_id'],
'shortDescription': 'Retainer' if 'SignedContract' in attachment[0] else '',
However, the API seems to be expecting "id" (lowercase) instead of "Id" (uppercase). After checking, we believe the issue stems from this field mismatch.
Additionally, we are using the following code to send the request with the Content-Type set dynamically using mimetypes:
content_type = mimetypes.guess_type(attachment[0])[0]
response = <a href="http://requests.post">requests.post</a>(
'<https://baileyglasserllp.my.salesforce-sites.com/customapi/services/apexrest/brioapi/SingleFileWebhook>',
params=file,
headers={'sforgid': 'BRIO_TEST', 'apikey': key, 'Content-Type': content_type}
To resolve this, we believe we need to update the field name to 'id' (lowercase) instead of 'Id' (uppercase).
Could you please confirm if this is the correct adjustment, or if there are any other requirements we should be aware of to resolve this error?
Looking forward to your guidance on this matter.
Thank you for your support.
Best regards,
[Your Name]
[Your Job Title]
[Your Company Name]
[Your Contact Information]
This email explains the issue clearly, asks for clarification, and provides enough context for the recipient to understand the problem and suggest a resolution. Let me know if you need any further edits or adjustments!
I wanted to bring up an issue we’re encountering with the file upload API. When we try to upload a file using the following request:
content_type = mimetypes.guess_type(attachment[0])[0]
file = {
'Id': intake_lead["external_id"],
'filename': attachment[0],
'externalId': intake_lead['lead_id'],
'shortDescription': 'Retainer' if 'SignedContract' in attachment[0] else '',
}
response = <a href="http://requests.post">requests.post</a>('(link unavailable)', params=file, headers={'sforgid': 'BRIO_TEST', 'apikey': key, 'Content-Type': content_type}
We’re getting a 400 response with this error message:
{
"error": "id is missing",
"success": false
From our end, we’re passing the Id field, which we’re setting to intake_lead["external_id"]. So, we’re confused as to why it’s saying the ID is missing. It would be helpful to clarify if the Id field needs to be formatted in a specific way or if any other fields are required that we're potentially missing.
Could you help us understand what might be going wrong here and guide us on how to resolve this issue?
JSON Samples The below JSON messages are detailed examples of initial posts as well as success and failure response messages.
Example Body The below is an example of a JSON message in the appropriate format to post:
{ "id": "a0LPY000001HgFB2A0", "documents": [ { "externalId": "testfile123", "name": "TestSignedRetainerAgreement.pdf", "description": "Test Signed Retainer Agreement uploaded from vendor", "shortDescription": "Retainer", "documentType": "application/pdf", "signedUrl": "https://resources.docs.salesforce.com/latest/latest/en-us/sfdc/pdf/basics.pdf" }, { "externalId": "testfile456", "name": "Test2.pdf", "description": "Test file 2 uploaded from vendor", "shortDescription": "Other", "documentType": "application/pdf", "signedUrl": "https://resources.docs.salesforce.com/latest/latest/en-us/sfdc/pdf/basics.pdf" } ] }
- Add a row for Cameron Wrench as the media buyer for the Google buy from the in-house team. @Zekarias Haile
- Ensure all field names are lowercase before saving. @Ryan
- Add 'IO' to the table name for account source and recreate the table if necessary. @Zekarias Haile
Leadcontract join on leadid and max on sent_time
s.`media_spend_log_id`,
s.`media_buyer_id`,
b.name AS media_buyer_name,
s.spend_date,
s.`platform_id`,
s.platform_name,
s.spend_amount,
b.overhead,
b.revenue_commission_amt,
b.commission_percent
FROM shield-legal-bi.sl_prod_application.io_media_buyer_spend AS s
JOIN shield-legal-bi.sl_prod_application.io_media_buyer AS b
ON s.`media_buyer_id` = b.id
ct.name AS case_type_name,
ls.name AS status,
il.external_id,
l.contactid,
l.updatedate,
l.createdate,
l.id AS lead_id,
l.documentscount AS lead_documents_count,
l.displayname AS name,
MAX(il.intake_processed_at) AS integration_sent_date,
MIN(CASE WHEN il.intake_document_response IS NOT NULL THEN 1 ELSE 0 END) AS documents_sent,
-- fc.serverstatus AS contract_server_status,
MAX(fc.senttime) AS contract_sent_time -- Taking the maximum senttime
FROM
`tort-intake-professionals.lr_data.lead` AS l -- Lead table in lr_data
LEFT JOIN
`tort-intake-professionals.Integrations.integration_log` AS il ON l.id = il.lead_id -- integration_log in Integrations
LEFT JOIN
`tort-intake-professionals.lr_data.lead_status` AS ls ON ls.id = l.statusid
LEFT JOIN
`tort-intake-professionals.lr_data.case_type` AS ct ON l.casetypeid = ct.id -- Case type in lr_data
LEFT JOIN
`tort-intake-professionals.lr_data.filtered_completed_contract` AS fc ON l.id = fc.leadid -- Join with filtered_completed_contract
GROUP BY
ct.name, l.id, l.documentscount, ls.name, l.displayname, il.external_id, l.contactid, l.updatedate, l.createdate,
fc.serverstatus
^Combinedleadsintegrations for Integration Dash
s.media_spend_log_id,
s.media_buyer_id,
s.spend_date,
s.platform_id,
s.platform_name,
s.spend_amount,
v.verticalID,
v.verticalName,
v.status,
v.groupID,
v.groupName,
v.totalOffers
FROM `shield-legal-bi.sl_prod_application.io_media_buyer_spend` AS s
LEFT JOIN `shield-legal-bi.leadspedia.verticals` AS v
ON s.ldp_vertical_id = v.verticalI
> ^ Join media_buyer_spend with Verticals on Vertical_ID
Lead ID 693550 695334 695676 695675 69007 T2
Cam price adjustment request for IL YTC and MI YTC
~Paraquat - Dicello/Meadow (TIP)~ • ~Tip has 1 more than shield: 3/24~ ~Michigan Pollution - Ben Crump/ELG (TIP)~ • ~Shield has 1 more than tip: 3/16~ ~Michigan Pollution - Ben Crump/ELG (INJURY) (TIP)~ • ~Tip has 2 more than shield: 3/16, 3/21~ ~NEC Baby Formula - Dicello/Flatirons (TIP)~ • ~Shield has one more than tip: 3/21~ ~Firefighting Foam - Nations/Levinson (TC) (TIP)~ • ~Tip has 1 more than Shield: 3/1~ ~Firefighting Foam - Nations/Levinson (Thyroid Only) (TC) (TIP)~ • ~Shield has 2 more than Tip: 3/1, 3/24~
Once I moved to the gcp folder by opening the folder using explorer .
run the the below command
al.**
FROM
`shield-legal-bi`.leadspedia.all_leads al
LEFT JOIN
`shield-legal-bi`.leadspedia.all_sold_leads asl
ON
al.leadID = asl.leadID
WHERE
asl.leadID IS NULL
SELECT **
FROM `shield-legal-bi.leadspedia.all_leads`
WHERE DATE(createdOnDate) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY) AND CURRENT_DATE()
AND s1 LIKE '%MB-JA%'
AND isTest = 'No'
AND sold = 'No'
ORDER BY createdOnDate DESC;
SELECT **
FROM `shield-legal-bi.leadspedia.all_leads`
WHERE DATE(createdOnDate) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY) AND CURRENT_DATE()
AND s1 LIKE '%MB-JA%'
AND isTest = 'No'
AND sold = 'No'
ORDER BY createdOnDate DESC;
https://youtu.be/g1qcu3fJCCg?feature=shared
```--Simple GET
--SELECT revdate, mediabuyer, campaign, customer, adplatform, dailyspend, dailyleads, signedcontracts, dailyrevenue, saletype, idxid select ** FROM public.mediarawv2 WHERE revdate BETWEEN '2025-04-09' AND '2025-04-09' AND signedcontracts > 0 AND campaign LIKE '%Chow%' -- AND campaign LIKE '%Michigan Youth Treatment Abuse%' -- AND customer LIKE '%Depo%' -- AND customer LIKE '%Nations/Levinson (Thyroid Only) (TC) (TIP))%' ORDER BY revdate ASC, media_buyer ASC;```
https://coursera.org/share/a951db4efa8583857e1169e2aa59365b
``CREATE TABLEshield-legal-bi.leadspedia.shieldsoldleads_financials` AS
SELECT 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.leadID, a.dateSold, a.price, a.verticalName, a.s1, a.s2, a.leadscore AS soldleadscore, b.leadspedialeadid, b.leademail, b.lawrulerleadid, b.lawrulercasetypeid, b.lawrulercasetypename, b.lawrulerleadcreatedate, b.contactfirstname, b.contactlastname, b.lawrulercasetypename, b.marketingsource, b.earliestesign, b.generaltorttype, b.primaryclientfirm, b.secondaryclientfirm, c.customerpattern, c.fivetransynced, c.fivetrandeleted, c.customerpattern, -- CASE -- WHEN b.leadspedialeadid IS NULL THEN 'referral' -- ELSE 'leadspedia' -- END AS lead_source
FROM
shield-legal-bi.leadspedia.all_sold_leads AS a
LEFT JOIN
tort-intake-professionals.Financial_Log_Dataset.Billable_Leads_With_Rates_and_lp AS b
ON
a.leadID = b.leadspedialeadid
LEFT JOIN
shield-legal-bi.sl_prod_application.io_customer_type AS c
ON
a.id = cast (c.id as String) ```
CREATE TABLE `shield-legal-bi.leadspedia.shield_sold_leads_financials` AS
SELECT
a.leadID,
a.first_name,
a.last_name,
a.email_address,
a.phone_home,
a.zip_code,
a.dateSold,
a.verticalName,
a.lead_score AS sold_lead_score,
b.leadspedia_lead_id,
b.lead_email,
b.contact_first_name,
b.contact_last_name,
b.lawruler_case_type_name,
b.marketing_source,
b.total_billable_minutes,
b.talk_time_revenue_in_USD
FROM
`shield-legal-bi.leadspedia.all_sold_leads` AS a
LEFT JOIN
`tort-intake-professionals.Financial_Log_Dataset.Billable_Leads_With_Rates_and_lp` AS b
ON
a.leadID = b.leadspedia_lead_id
SELECT leadespediaid, COUNT() AS recordcount
FROM your_project_id.your_dataset_id.your_table_name
GROUP BY leadespediaid
HAVING COUNT() > 1
ORDER BY recordcount DESC;
b.leadspedialeadid = CAST(l.id AS STRING)
CREATE TABLE `shield-legal-bi.leadspedia.shield_sold_leads_financials` AS
SELECT
a.leadID,
a.first_name,
a.last_name,
a.email_address,
a.phone_home,
a.zip_code,
a.dateSold,
a.verticalName,
a.s1,
a.s2,
a.lead_score AS sold_lead_score,
b.leadspedia_lead_id,
b.lead_email,
b.lawruler_lead_id,
b.lawruler_casetype_id,
b.lawruler_lead_create_date,
b.contact_first_name,
b.contact_last_name,
b.lawruler_case_type_name,
b.marketing_source,
b.general_tort_type,
b.primary_client_firm,
b.secondary_client_firm,
b.total_billable_minutes,
b.talk_time_revenue_in_USD
FROM
`shield-legal-bi.leadspedia.all_sold_leads` AS a
LEFT JOIN
`tort-intake-professionals.Financial_Log_Dataset.Billable_Leads_With_Rates_and_lp` AS b
ON
a.leadID = b.leadspedia_lead_id
HBL41TW8 653591, 659440 same address WGNAOXT2 657914, 658203 JTVO4RUF 657197, 657679 same add, phone
leadspedia_lead_id,
COUNT(DISTINCT lawruler_lead_id) AS unique_lawruler_ids,
STRING_AGG(DISTINCT CAST(lawruler_lead_id AS STRING), ', ') AS lawruler_ids_list
FROM `tort-intake-professionals.Financial_Log_Dataset.Billable_Leads_With_Rates_and_lp` AS b
LEFT JOIN
`tort-intake-professionals.lr_data.lead` As l
ON
b.leadspedia_lead_id = CAST(l.id AS STRING)
-- where l.casetypeid = 1705
GROUP BY leadspedia_lead_id
HAVING COUNT(DISTINCT lawruler_lead_id) > 1
ORDER BY unique_lawruler_ids DESC
get dupe Lp ids ^^^
The purpose of CAST() in SQL is to convert a value from one data type to another so that operations like joins, comparisons, or calculations can work properly when dealing with mixed data types.
leadspedia_lead_id,
COUNT(DISTINCT lawruler_lead_id) AS unique_lawruler_ids,
STRING_AGG(DISTINCT CAST(lawruler_lead_id AS STRING), ', ') AS lawruler_ids_list
FROM `tort-intake-professionals.Financial_Log_Dataset.Billable_Leads_With_Rates_and_lp` AS b
LEFT JOIN
`tort-intake-professionals.lr_data.lead` As l
ON
b.leadspedia_lead_id = CAST(l.id AS STRING)
-- where l.casetypeid = 1705
GROUP BY leadspedia_lead_id
HAVING COUNT(DISTINCT lawruler_lead_id) > 1
ORDER BY unique_lawruler_ids DESC
get dupe Lp ids
leadspedia_lead_id,
COUNT(DISTINCT lawruler_lead_id) AS unique_lawruler_ids,
STRING_AGG(DISTINCT CAST(lawruler_lead_id AS STRING), ', ') AS lawruler_ids_list
FROM `tort-intake-professionals.Financial_Log_Dataset.Billable_Leads_With_Rates_and_lp` AS b
LEFT JOIN
`tort-intake-professionals.lr_data.lead` As l
ON
b.leadspedia_lead_id = CAST(l.id AS STRING)
-- where l.casetypeid = 1705
GROUP BY leadspedia_lead_id
HAVING COUNT(DISTINCT lawruler_lead_id) > 1
ORDER BY unique_lawruler_ids DESC
get dupe Lp ids ^^^
``CREATE TABLEshield-legal-bi.leadspedia.shieldsoldleads_financials` AS
SELECT 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.leadID, a.dateSold, a.price, a.verticalName, a.s1, a.s2, a.leadscore AS soldleadscore, b.leadspedialeadid, b.leademail, b.lawrulerleadid, b.lawrulercasetypeid, b.lawrulerleadcreatedate, b.contactfirstname, b.contactlastname, b.lawrulercasetypename, b.marketingsource, b.generaltorttype, b.primaryclientfirm, b.secondaryclientfirm, c.customerpattern, c.fivetransynced, c.fivetrandeleted, c.customerpattern, CASE WHEN b.leadspedialeadid IS NULL THEN 'referral' ELSE 'leadspedia' END AS lead_source
FROM
shield-legal-bi.leadspedia.all_sold_leads AS a
LEFT JOIN
tort-intake-professionals.Financial_Log_Dataset.Billable_Leads_With_Rates_and_lp AS b
ON
a.leadID = b.leadspedialeadid
LEFT JOIN
shield-legal-bi.sl_prod_application.io_customer_type AS c
ON
a.id = cast (c.id as String) ```
``CREATE TABLEshield-legal-bi.leadspedia.shieldsoldleads_financials` AS
SELECT 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.leadID, a.dateSold, a.price, a.verticalName, a.s1, a.s2, a.leadscore AS soldleadscore, b.leadspedialeadid, b.leademail, b.lawrulerleadid, b.lawrulercasetypeid, b.lawrulercasetypename, b.lawrulerleadcreatedate, b.contactfirstname, b.contactlastname, b.lawrulercasetypename, b.marketingsource, b.earliestesign, b.generaltorttype, b.primaryclientfirm, b.secondaryclientfirm, c.customerpattern, c.fivetransynced, c.fivetrandeleted, c.customerpattern, -- CASE -- WHEN b.leadspedialeadid IS NULL THEN 'referral' -- ELSE 'leadspedia' -- END AS lead_source
FROM
shield-legal-bi.leadspedia.all_sold_leads AS a
LEFT JOIN
tort-intake-professionals.Financial_Log_Dataset.Billable_Leads_With_Rates_and_lp AS b
ON
a.leadID = b.leadspedialeadid
LEFT JOIN
shield-legal-bi.sl_prod_application.io_customer_type AS c
ON
a.id = cast (c.id as String) ```
```--Simple GET
--SELECT revdate, mediabuyer, campaign, customer, adplatform, dailyspend, dailyleads, signedcontracts, dailyrevenue, saletype, idxid select ** FROM public.mediarawv2 WHERE revdate BETWEEN '"2025-05-01"' AND '"2025-05-01"' AND signedcontracts > 0 AND campaign LIKE '%San%' -- AND mediabuyer LIKE '%John%' -- AND campaign LIKE '%Michigan Youth Treatment Abuse%' -- AND customer LIKE '%Dicello 13 /Flatirons (TIP)%' -- AND customer LIKE '%Texas%' ORDER BY revdate ASC, mediabuyer ASC;```
📌 What is a UDF (User-Defined Field)?
A User-Defined Field is a custom data field in a CRM (Customer Relationship Management) system that allows you to store information that’s not already part of the CRM’s default fields.
For example:
• Default CRM fields: First Name, Email, Phone
• UDFs you might add: Preferred Contact Time, Survey Response, Membership Level, Product Interest
📦 In Your Case:
> "For us to send data into a CRM, a UDF has to be created and we matched the questions with theirs..."
This likely means:
- 🛠️ UDFs had to be created in the CRM to hold answers from your form or data source.
- ✅ You matched the fields in your form (like survey questions or signup fields) with the corresponding UDFs in the CRM.
- 🔄 This mapping ensures that when you send data (like via an API or integration tool), each answer goes into the correct field in the CRM.
🔗 Example:
Imagine you're collecting customer feedback via a form with these questions:
• “How did you hear about us?”
• “Rate your experience”
The CRM doesn’t have fields for these by default, so:
• You create a UDF called
Referral_Source• And a UDF calledExperience_RatingThen, during data transfer: • You map: •"How did you hear about us?" → Referral_Source•"Rate your experience" → Experience_RatingNow when the data is sent, it lands in the right place.
✅ Summary: A UDF in CRM terms = a custom field you define to capture non-standard or business-specific data. Creating and matching UDFs ensures your external data fits into the CRM structure properly.
mediarawv2, iomediabuyer( Where MB are added and managed)ioadaccountsource, iomediabuyerretoolprod ( MB's spend) Looking for max date for MB, for vertical,(We need max submit in query), Allsoldleads and join with shieldlegaldatastore ( retainer) ( use the contract ID to get the price(Join on the ContractID), Leadspediaallcontracts( to get the price), orderpaidspent (lead level unless it's referral) and join with iomediabuyerretool_prod
The meeting schedule is about to end. Action Items for this meeting:
- Insert entries for leads that are missing in the spreadsheet for April and May, including checking for their last entries in previous months. @Zekarias Haile
- Check the cost per lead formula in the spreadsheet to ensure it matches the expected values. @Zekarias Haile
- Run the update statement to reflect changes on the dashboard for Mormon victim abuse. @Ryan
- Type the table names into the Shield Analytics Slack channel and tag Ryan, Brian, and Nick. @Zekarias Haile
- Brian needs to create sub tables for IO media buyer and IO customer type. @Brian Hirst
- Brian needs to update the IO media buyer spend history table. @Brian Hirst
- Create an IO media buyer spend history table based on how James did it for IO Law Rule. @Brian Hirst
- Map the i/o ad platforms to the drop-down boxes to avoid support maintenance issues. @Brian Hirst
- Brian to implement an automatic refresh for the retool prod table so that it updates every time media buyers submit data. @Brian Hirst
- Create a new table in the data set called 'shield legal, contract id, x ref' or 'xref' to strip out the string where there are multiple contract IDs. @Ryan
- Create a new table in the data set called 'shield legal, contract id, x ref or cross reference table' that strips out the string where there are many of them. @Zekarias Haile
- Order financials and create a mini to many orders to many rows in the financials by joining the order list to the leads PD and mapping to the financial details. @Ryan
- Map the contract IDs to the LDP and match it on the financial details using campaign, customer, and sale type. @Ryan
- Add a media buyer ID field in the production history table to connect with the staging table. @Brian Hirst
- Write the first Step One table by joining sold leads and retainers. @Zekarias Haile
```Allsoldleads joined with SL_datastore
SELECT 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.leadID, 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, -- CASE -- WHEN b.leadspedialeadid IS NULL THEN 'referral' -- ELSE 'leadspedia' -- END AS leadsource
FROM
shield-legal-bi.leadspedia.all_sold_leads AS a
LEFT JOIN
shield-legal-bi.tip_bigquery_shield_datastore_tip_external.shield_legal_datastore AS b
ON
a.leadID = b.leadspedialeadid```
5/16 FireFoam check if it's has two leads and check s1. since it says one CP on new Dash
a.leadID,
a.first_name,
a.last_name,
a.email_address,
a.phone_home,
a.zip_code,
a.campaignName,
a.buyerName,
a.buyerID,
a.contractID,
a.createdOn,
a.createdOnUTC,
a.leadID,
a.dateSold,
a.price,
a.verticalName,
a.s1,
a.s2,
a.lead_score AS sold_lead_score,
b.leadspedia_lead_id,
b.leadspedia_full_id,
b._fivetran_id,
b._fivetran_synced,
b._fivetran_deleted,
b.leadspedia_s1,
b.lead_email,
b.lawruler_lead_create_date,
b.contact_first_name,
b.contact_last_name,
b.marketing_source,
b.earliest_esign,
b.lawruler_casetype_id,
b.lawruler_lead_create_date,
b.lawruler_lead_id,
b.contract_ids,
c.price
-- CASE
-- WHEN b.leadspedia_lead_id IS NULL THEN 'referral'
-- ELSE 'leadspedia'
-- END AS lead_source
-- FROM
-- `shield-legal-bi.leadspedia.all_sold_leads` AS a
-- RIGHT OUTER JOIN
-- `shield-legal-bi.tip_bigquery_shield_datastore_tip_external.shield_legal_datastore` AS b
-- ON
-- a.leadID = b.leadspedia_lead_id
-- LEFT JOIN
-- `shield-legal-bi.leadspedia_all_contracts.leadspedia_all_contracts` AS c
-- ON
-- a.contractID = cast(c.contract_id AS string)
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.leadspedia_lead_id = a.leadID
LEFT JOIN
`shield-legal-bi.leadspedia_all_contracts.leadspedia_all_contracts` AS c
ON a.contractID = cast(c.contract_id AS STRING)
Greg smith Sean M Craig
Both data studio and looker enterprise
Greg smith Sean M Craig
Both data studio and looker enterprise
SPLIT(SPLIT(a.s1, '-')[SAFE_OFFSET(1)], '_')[SAFE_OFFSET(0)] AS media_source,
SPLIT(SPLIT(a.s1, '-')[SAFE_OFFSET(2)], '_')[SAFE_OFFSET(0)] AS mb_id_next_2,
a.leadID,
a.first_name,
a.last_name,
a.email_address,
a.phone_home,
a.zip_code,
a.campaignName,
a.buyerName,
a.buyerID,
a.contractID,
a.createdOn,
a.createdOnUTC,
a.dateSold,
a.price,
a.verticalName,
a.s1,
a.s2,
a.lead_score AS sold_lead_score,
b.leadspedia_lead_id,
b.leadspedia_full_id,
b._fivetran_id,
b._fivetran_synced,
b._fivetran_deleted,
b.leadspedia_s1,
-- b.lead_email,
b.lawruler_lead_create_date,
b.contact_first_name,
b.contact_last_name,
b.marketing_source,
b.earliest_esign,
b.lawruler_casetype_id,
b.lawruler_lead_create_date,
b.lawruler_lead_id,
b.contract_ids,
c.price AS contract_price
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.leadspedia_lead_id = a.leadID
LEFT JOIN
`shield-legal-bi.leadspedia_all_contracts.leadspedia_all_contracts` AS c
ON a.contractID = CAST(c.contract_id AS STRING)
LEFT JOIN `shield-legal-bi.sl_prod_application.io_media_buyer_retool_prod` AS m
on m.media_buyer = b.leadspedia_s1
LEFT JOIN `shield-legal-bi.sl_dashboard.orders_paid_spent` AS o
ON o.cpr_lp_contract_ids = a.contractID
WHERE
b._fivetran_deleted = false
Compare
SPLIT(SPLIT(a.s1, '-')[SAFE_OFFSET(1)], '_')[SAFE_OFFSET(0)] AS media_source,
SPLIT(SPLIT(a.s1, '-')[SAFE_OFFSET(2)], '_')[SAFE_OFFSET(0)] AS mb_id_next_2,
a.leadID,
a.first_name,
a.last_name,
a.email_address,
a.phone_home,
a.zip_code,
a.campaignName,
a.buyerName,
a.buyerID,
a.contractID,
a.createdOn,
a.createdOnUTC,
a.dateSold,
a.price,
a.verticalName,
a.s1,
a.s2,
a.lead_score AS sold_lead_score,
b.leadspedia_lead_id,
b.leadspedia_full_id,
b._fivetran_id,
b._fivetran_synced,
b._fivetran_deleted,
b.leadspedia_s1,
-- b.lead_email,
b.lawruler_lead_create_date,
b.contact_first_name,
b.contact_last_name,
b.marketing_source,
b.earliest_esign,
b.lawruler_casetype_id,
b.lawruler_lead_create_date,
b.lawruler_lead_id,
b.contract_ids,
c.price AS contract_price
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.leadspedia_lead_id = a.leadID
LEFT JOIN
`shield-legal-bi.leadspedia_all_contracts.leadspedia_all_contracts` AS c
ON a.contractID = CAST(c.contract_id AS STRING)
-- LEFT JOIN `shield-legal-bi.sl_prod_application.io_media_buyer_retool_prod` AS m
-- on m.vertical_id = a.verticalID
LEFT JOIN `shield-legal-bi.sl_dashboard.orders_paid_spent` AS o
ON o.cpr_lp_contract_ids = a.contractID
WHERE
b._fivetran_deleted = false
Cross check the price before update with the sheet.. to make sure the price is in range with the prior month. This when I done May and if row on sheet i is not, on rows on this query result, that where we insert..
Cross check the price before update with the sheet.. to make sure the price is in range with the prior month. This when I done May and if row on sheet i is not, on rows on this query result, that where we insert..
idx_id BLX 18 assigned to CR as SL leads 88132 88131 88130 88136 88135 88134 88139 88138 88137 88141 88140 88145 88144 88143 88133 88142
SPLIT(SPLIT(a.s1, '-')[SAFE_OFFSET(1)], '_')[SAFE_OFFSET(0)] AS media_source,
SPLIT(SPLIT(a.s1, '-')[SAFE_OFFSET(2)], '_')[SAFE_OFFSET(0)] AS mb_id_next_2,
a.leadID,
a.first_name,
a.last_name,
a.email_address,
a.phone_home,
a.zip_code,
a.campaignName,
a.buyerName,
a.buyerID,
a.contractID,
a.createdOn,
a.createdOnUTC,
a.dateSold,
a.price,
a.verticalName,
a.s1,
a.s2,
a.lead_score AS sold_lead_score,
b.leadspedia_lead_id,
b.leadspedia_full_id,
b._fivetran_id,
b._fivetran_synced,
b._fivetran_deleted,
b.leadspedia_s1,
-- b.lead_email,
b.lawruler_lead_create_date,
b.contact_first_name,
b.contact_last_name,
b.marketing_source,
b.earliest_esign,
b.lawruler_casetype_id,
b.lawruler_lead_create_date,
b.lawruler_lead_id,
b.contract_ids,
c.price AS contract_price,
m.vertical_id
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.leadspedia_lead_id = a.leadID
LEFT JOIN
`shield-legal-bi.leadspedia_all_contracts.leadspedia_all_contracts` AS c
ON a.contractID = CAST(c.contract_id AS STRING)
LEFT JOIN `shield-legal-bi.sl_prod_application.io_media_buyer_retool_prod` AS m
on cast(m.vertical_id as string) = a.verticalID
LEFT JOIN `shield-legal-bi.sl_dashboard.orders_paid_spent` AS o
ON o.cpr_lp_contract_ids = a.contractID
WHERE
b._fivetran_deleted = fals
``WITH deduped_media_buyer_spend AS (
SELECT DISTINCT
unique_identifier,
media_buyer,
agency,
vertical,
platform,
spend_date,
submit_date,
spend_amount,
vertical_id
FROMshield-legal-bi.slprodapplication.iomediabuyerretoolprod`
)
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```
^ Dedupes the spend to avoid duplication. We’ll create a CTE (common table expression) for your io_media_buyer_retool_prod table, selecting distinct unique_identifier values, and then join that deduplicated CTE to your main query.
TC cost adjustments(moving to non tc campaigns
``-- Create or replace view sl_staging.sl_new_fin_daily_alloc as
WITH deduped_media_buyer_spend AS (
SELECT
spend_date,
vertical_id,
SUM(spend_amount) AS total_spend
FROMshield-legal-bi.slprodapplication.iomediabuyerretoolprod`
GROUP BY spenddate, verticalid
),
contractleadcounts AS (
SELECT
dateSold,
contractID,
verticalID,
COUNT(**) AS total_leads
FROM shield-legal-bi.leadspedia.all_sold_leads
GROUP BY dateSold, contractID, verticalID
),
dailycontractspendalloc AS ( SELECT l.dateSold, l.contractID, l.verticalID, l.totalleads, m.totalspend, ROUND(m.totalspend / NULLIF(l.totalleads, 0), 2) AS allocatedcpl, ROUND(m.totalspend, 2) AS allocatedcontractspend FROM contractleadcounts l LEFT JOIN dedupedmediabuyerspend m ON l.verticalID = CAST(m.verticalid AS STRING) AND CAST(PARSETIMESTAMP('%F %H:%M:%S', l.dateSold) AS DATE) = m.spend_date
)
SELECT SPLIT(SPLIT(a.s1, '-')[SAFEOFFSET(1)], '')[SAFEOFFSET(0)] AS mediasource, SPLIT(SPLIT(a.s1, '-')[SAFEOFFSET(2)], '')[SAFEOFFSET(0)] AS mbidnext2,
-- allsoldleads 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, a.verticalID AS vertical_id,
-- shieldlegaldatastore b.leadspedialeadid, b.leadspediafullid, b.fivetranid, b.fivetransynced, b.fivetrandeleted, b.leadspedias1, b.lawrulerleadcreatedate, b.contactfirstname, b.contactlastname, b.marketingsource, b.earliestesign, b.lawrulercasetypeid, b.lawrulerleadid, b.contract_ids,
-- leadspediaallcontracts c.price AS contract_price,
-- spend allocation d.allocatedcontractspend, d.allocatedcpl, d.totalleads AS contractdailyleads, d.dateSold AS spendallocationdate
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
dailycontractspendalloc AS d
ON a.contractID = d.contractID
AND a.dateSold = d.dateSold
WHERE b.fivetrandeleted = FALSE AND DATE(a.dateSold) = DATE '2025-07-09'```
``-- Create or replace view sl_staging.sl_new_fin as
WITH deduped_media_buyer_spend AS (
SELECT DISTINCT
unique_identifier,
media_buyer,
agency,
vertical,
platform,
spend_date,
submit_date,
spend_amount,
vertical_id
FROMshield-legal-bi.slprodapplication.iomediabuyerretoolprod`
),
joineddata AS ( SELECT SPLIT(SPLIT(a.s1, '-')[SAFEOFFSET(1)], '')[SAFEOFFSET(0)] AS mediasource, SPLIT(SPLIT(a.s1, '-')[SAFEOFFSET(2)], '')[SAFEOFFSET(0)] AS mbidnext_2,
-- all_sold_leads fields
a.leadID,
a.first_name,
a.last_name,
a.email_address,
a.phone_home,
a.zip_code,
a.campaignName,
a.buyerName,
a.buyerID,
a.contractID,
a.createdOn,
-- DATETIME(a.createdOn, 'America/Los_Angeles') AS createdOn_pacific,
a.createdOnUTC,
a.dateSold,
TIMESTAMP(a.dateSold, 'America/Los_Angeles') AS dateSold_pacific,
a.price,
a.verticalName,
a.s1,
a.s2,
a.lead_score AS sold_lead_score,
a.verticalID AS vertical_id,
-- shield_legal_datastore fields
b.leadspedia_lead_id,
b.leadspedia_full_id,
b._fivetran_id,
b._fivetran_synced,
b._fivetran_deleted,
b.leadspedia_s1,
b.lawruler_lead_create_date,
-- DATETIME(b.lawruler_lead_create_date, 'America/Los_Angeles') AS lawruler_lead_create_date_pacific,
b.contact_first_name,
b.contact_last_name,
b.marketing_source,
b.earliest_esign,
DATETIME(b.earliest_esign, 'America/Los_Angeles') AS earliest_esign_pacific,
b.lawruler_casetype_id,
b.lawruler_lead_id,
b.contract_ids,
-- leadspedia_all_contracts
c.price AS contract_price,
-- media buyer spend
m.spend_date,
m.spend_amount,
m.vertical_id,
-- case type billing rate
r.io_casetype_billable_rate AS casetype_billable_rate
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
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
LEFT JOIN
tort-intake-professionals.tip_prod_application.io_lr_case_types_revenue_rates AS r
ON b.lawrulercasetypeid = CAST(r.lrcasetypesid AS STRING)
WHERE
b.fivetran_deleted = FALSE
),
finaldeduped AS ( SELECT **, ROWNUMBER() OVER (PARTITION BY lawrulerleadid ORDER BY createdOn DESC) AS rn FROM joined_data )
SELECT ** FROM final_deduped WHERE rn = 1;```
``-- Create or replace view sl_staging.sl_new_fin as
WITH deduped_media_buyer_spend AS (
SELECT DISTINCT
unique_identifier,
media_buyer,
agency,
vertical,
platform,
spend_date,
submit_date,
spend_amount,
vertical_id
FROMshield-legal-bi.slprodapplication.iomediabuyerretoolprod`
),
joineddata AS ( SELECT SPLIT(SPLIT(a.s1, '-')[SAFEOFFSET(1)], '')[SAFEOFFSET(0)] AS mediasource, SPLIT(SPLIT(a.s1, '-')[SAFEOFFSET(2)], '')[SAFEOFFSET(0)] AS mbidnext_2,
-- all_sold_leads fields
a.leadID,
a.first_name,
a.last_name,
a.email_address,
a.phone_home,
a.zip_code,
a.campaignName,
a.buyerName,
a.buyerID,
a.contractID,
a.createdOn,
-- DATETIME(a.createdOn, 'America/Los_Angeles') AS createdOn_pacific,
a.createdOnUTC,
a.dateSold,
TIMESTAMP(a.dateSold, 'America/Los_Angeles') AS dateSold_pacific,
a.price,
a.verticalName,
a.s1,
a.s2,
a.lead_score AS sold_lead_score,
a.verticalID AS vertical_id,
-- shield_legal_datastore fields
b.leadspedia_lead_id,
b.leadspedia_full_id,
b._fivetran_id,
b._fivetran_synced,
b._fivetran_deleted,
b.leadspedia_s1,
b.lawruler_lead_create_date,
-- DATETIME(b.lawruler_lead_create_date, 'America/Los_Angeles') AS lawruler_lead_create_date_pacific,
b.contact_first_name,
b.contact_last_name,
b.marketing_source,
b.earliest_esign,
DATETIME(b.earliest_esign, 'America/Los_Angeles') AS earliest_esign_pacific,
b.lawruler_casetype_id,
b.lawruler_lead_id,
b.contract_ids,
-- leadspedia_all_contracts
c.price AS contract_price,
-- media buyer spend
m.spend_date,
m.spend_amount,
m.vertical_id,
-- case type billing rate
r.io_casetype_billable_rate AS casetype_billable_rate
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
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
LEFT JOIN
tort-intake-professionals.tip_prod_application.io_lr_case_types_revenue_rates AS r
ON b.lawrulercasetypeid = CAST(r.lrcasetypesid AS STRING)
WHERE
b.fivetran_deleted = FALSE
),
finaldeduped AS ( SELECT **, ROWNUMBER() OVER (PARTITION BY lawrulerleadid ORDER BY createdOn DESC) AS rn FROM joined_data )
SELECT ** FROM final_deduped WHERE rn = 1;```
Old one shared with Ryan
``-- Create or replace view sl_staging.sl_new_fin as
WITH deduped_media_buyer_spend AS (
SELECT DISTINCT
unique_identifier,
media_buyer,
agency,
vertical,
platform,
spend_date,
submit_date,
spend_amount,
vertical_id
FROMshield-legal-bi.slprodapplication.iomediabuyerretoolprod`
)
SELECT SPLIT(SPLIT(a.s1, '-')[SAFEOFFSET(1)], '')[SAFEOFFSET(0)] AS mediasource, SPLIT(SPLIT(a.s1, '-')[SAFEOFFSET(2)], '')[SAFEOFFSET(0)] AS mbidnext2,
-- allsoldleads fields 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, a.verticalID AS vertical_id,
-- shieldlegaldatastore fields b.leadspedialeadid, b.leadspediafullid, b.fivetranid, b.fivetransynced, b.fivetrandeleted, b.leadspedias1, b.lawrulerleadcreatedate, b.contactfirstname, b.contactlastname, b.marketingsource, b.earliestesign, b.lawrulercasetypeid, b.lawrulerleadid, b.contract_ids,
-- leadspediaallcontracts c.price AS contract_price,
-- media buyer spend m.spenddate, m.spendamount, m.vertical_id,
-- case type billing rate r.iocasetypebillablerate AS casetypebillable_rate
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
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
LEFT JOIN
tort-intake-professionals.tip_prod_application.io_lr_case_types_revenue_rates AS r
ON b.lawrulercasetypeid = CAST(r.lrcasetypes_id AS STRING)
WHERE b.fivetrandeleted = FALSE;```
Cam sold 77 leads for 60000 (example that Backpage and PFAS Nations/Levinson price are not constantly same)
EOM update statement with case for 8/2025
UPDATE public.casetype_cc_costs
SET cpl = CASE
WHEN casetype LIKE 'Alameda Juv Detention Abuse%' THEN 88.03
WHEN casetype LIKE 'Bard Powerport Catheter%' THEN 55.05
WHEN casetype LIKE 'Chowchilla Womens Prison Abuse%' THEN 59
WHEN casetype LIKE 'Depo Provera%' THEN 16.87
WHEN casetype LIKE 'Dr Barry Brock Abuse%' THEN 54.34
WHEN casetype LIKE 'Dr Oumair Aejaz Abuse%' THEN 59
WHEN casetype LIKE 'Firefighting Foam%' THEN 63.17
WHEN casetype LIKE 'Hair Relaxer Uterine Cancer%' THEN 86.77
WHEN casetype LIKE 'Hair Salon Bladder Cancer%' THEN 29.93
WHEN casetype LIKE 'Illinois Juvenile Hall Abuse%' THEN 79.1
WHEN casetype LIKE 'Illinois Youth Treatment Abuse%' THEN 106.57
WHEN casetype LIKE 'Instant Soup Cup Child Burns%' THEN 29.58
WHEN casetype LIKE 'Kansas Juv Detention Center Abuse%' THEN 65.15
WHEN casetype LIKE 'Kansas YRTC Abuse%' THEN 41.21
WHEN casetype LIKE 'Kentucky Organ Donor Investigation%' THEN 93
WHEN casetype LIKE 'Los Padrinos Juv Hall Abuse%' THEN 96.3
WHEN casetype LIKE 'Michigan Catholic Clergy Abuse%' THEN 25.03
WHEN casetype LIKE 'Michigan Foster Care Abuse%' THEN 132.32
WHEN casetype LIKE 'Michigan Juvenile Hall Abuse%' THEN 161.93
WHEN casetype LIKE 'Michigan Youth Treatment Abuse%' THEN 94.49
WHEN casetype LIKE 'Mormon Victim Abuse%' THEN 98.24
WHEN casetype LIKE 'NEC Baby Formula%' THEN 44.43
WHEN casetype LIKE 'Nevada Juvenile Hall Abuse%' THEN 31.78
WHEN casetype LIKE 'New Jersey Juvenile Detention Abuse%' THEN 148.29
WHEN casetype LIKE 'Northwell Sleep Center%' THEN 67.81
WHEN casetype LIKE 'Paraquat%' THEN 111.63
WHEN casetype LIKE 'Pennsylvania Juvenile Hall Abuse%' THEN 56.57
WHEN casetype LIKE 'PFAS Water Contamination%' THEN 32.93
WHEN casetype LIKE 'Polinsky Childrens Center Abuse%' THEN 54.57
WHEN casetype LIKE 'San Bernardino Juv Detention Abuse%' THEN 140.51
WHEN casetype LIKE 'San Diego Juv Detention YRTC Abuse%' THEN 63.67
WHEN casetype LIKE 'Transvaginal Mesh%' THEN 14.99
WHEN casetype LIKE 'Video Gaming Sextortion%' THEN 55.19
WHEN casetype LIKE 'Dr Scott Lee Abuse%' THEN 88.6
WHEN casetype LIKE 'Gaming Addiction%' THEN 105.27
WHEN casetype LIKE 'LA Wildfires%' THEN 63.54
WHEN casetype LIKE 'Milestone Tax Solutions%' THEN 242
WHEN casetype LIKE 'Private Boarding School Abuse%' THEN 95.5
WHEN casetype LIKE 'Santa Clara Juv Detention Abuse%' THEN 54.57
WHEN casetype LIKE 'Type 2 Child Diabetes%' THEN 95.5
END
WHERE monthend = '2025-08-01'
AND (
casetype LIKE 'Alameda Juv Detention Abuse%' OR
casetype LIKE 'Bard Powerport Catheter%' OR
casetype LIKE 'Chowchilla Womens Prison Abuse%' OR
casetype LIKE 'Depo Provera%' OR
casetype LIKE 'Dr Barry Brock Abuse%' OR
casetype LIKE 'Dr Oumair Aejaz Abuse%' OR
casetype LIKE 'Firefighting Foam%' OR
casetype LIKE 'Hair Relaxer Uterine Cancer%' OR
casetype LIKE 'Hair Salon Bladder Cancer%' OR
casetype LIKE 'Illinois Juvenile Hall Abuse%' OR
casetype LIKE 'Illinois Youth Treatment Abuse%' OR
casetype LIKE 'Instant Soup Cup Child Burns%' OR
casetype LIKE 'Kansas Juv Detention Center Abuse%' OR
casetype LIKE 'Kansas YRTC Abuse%' OR
casetype LIKE 'Kentucky Organ Donor Investigation%' OR
casetype LIKE 'Los Padrinos Juv Hall Abuse%' OR
casetype LIKE 'Michigan Catholic Clergy Abuse%' OR
casetype LIKE 'Michigan Foster Care Abuse%' OR
casetype LIKE 'Michigan Juvenile Hall Abuse%' OR
casetype LIKE 'Michigan Youth Treatment Abuse%' OR
casetype LIKE 'Mormon Victim Abuse%' OR
casetype LIKE 'NEC Baby Formula%' OR
casetype LIKE 'Nevada Juvenile Hall Abuse%' OR
casetype LIKE 'New Jersey Juvenile Detention Abuse%' OR
casetype LIKE 'Northwell Sleep Center%' OR
casetype LIKE 'Paraquat%' OR
casetype LIKE 'Pennsylvania Juvenile Hall Abuse%' OR
casetype LIKE 'PFAS Water Contamination%' OR
casetype LIKE 'Polinsky Childrens Center Abuse%' OR
casetype LIKE 'San Bernardino Juv Detention Abuse%' OR
casetype LIKE 'San Diego Juv Detention YRTC Abuse%' OR
casetype LIKE 'Transvaginal Mesh%' OR
casetype LIKE 'Video Gaming Sextortion%' OR
casetype LIKE 'Dr Scott Lee Abuse%' OR
casetype LIKE 'Gaming Addiction%' OR
casetype LIKE 'LA Wildfires%' OR
casetype LIKE 'Milestone Tax Solutions%' OR
casetype LIKE 'Private Boarding School Abuse%' OR
casetype LIKE 'Santa Clara Juv Detention Abuse%' OR
case_type LIKE 'Type 2 Child Diabetes%'
);