Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-05-13 11:05:06

nickmcfadden@tortintakeprofessionals.com

Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-05-13 11:05:14

tip123

Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-05-13 18:58:23

Image from iOS
Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-05-13 19:34:49
Flatirons Campaign Benchmark Data (2).xlsx
deactivateduser (deactivateduser168066-themedialaboratory@slack-corp.com)
2024-05-14 11:41:11

Hey Nick, just checking if we're still on for that video call or if something came up

Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-05-14 11:44:30

something came up. I will ping you when done and then we can go over what is needed to wrap up

👍 deactivateduser
Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-05-14 11:45:26

sometimes it is better to text me if I am late or something. Usually will get a faster response from me when not in the office

deactivateduser (deactivateduser168066-themedialaboratory@slack-corp.com)
2024-05-14 11:45:42

Will do

Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-05-14 12:52:02

SELECT campaign,SUM(esignedcontracts) as SignedContracts, SUM(edailyrevenue) as Cost FROM shield-legal-bi.aws_legacy_views.matview_financial_details WHERE revdate between '2024-01-08' AND '2024-01-14' AND customer Like '%Flatirons%' GROUP BY campaign

Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-05-14 12:52:16

SELECT ** FROM shield-legal-bi.aws_legacy_views.matview_financial_details WHERE rev_date between '2024-03-11' AND '2024-03-17' AND customer Like '%Flatirons%' AND campaign Like '%Camp%'

Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-05-14 13:22:23

Hey don't share the finacials with anyone

Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-05-14 13:22:54

just talk about it with Brittanys team. They don't need to know anything about the investment amount just focus on the counts

deactivateduser (deactivateduser168066-themedialaboratory@slack-corp.com)
2024-05-14 13:23:02

Got it!

deactivateduser (deactivateduser168066-themedialaboratory@slack-corp.com)
2024-05-14 17:18:19

Here's the spreadsheet with the completed counts. I was able to work out the Camp Lejeune financial calculations and counts. Most everything I found seems to be a result of those flagged and in-process declines. One thing that stood out though is that the query against the financial_log returns a count of minus 1 for Camp Lejeune EO and a count of plus 1 for non-EO Camp Lejeune. I don't know if that one could have been processed incorrectly (week ending 4/28). Also, there was only one week I found where the investment amount listed in the spreadsheet was different from the results of the shield-bi queries (week ending 4/21 for CPAP).

gcf_reporting_20240513.xlsx
Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-05-14 17:19:25

Awesome great work! Can you get with Brittany’s team to figure out the differences? Brittany said she was free this afternoon

deactivateduser (deactivateduser168066-themedialaboratory@slack-corp.com)
2024-05-14 17:20:24

Will do! Should I not share the reporting spreadsheet with them though as that has the investments?

Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-05-14 17:20:44

Don’t share it with them

👍 deactivateduser
Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-05-14 17:20:59

Just go over the time periods and you can use bigquery to help investigate

Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-05-14 17:21:36

If it’s easier delete out the investment amount and calculated count columns and you can show them that

Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-05-14 17:21:50

The financial number shouldn’t matter for them

deactivateduser (deactivateduser168066-themedialaboratory@slack-corp.com)
2024-05-14 17:22:08

Sounds good

Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-05-16 15:54:23

Since Ryan is moving that meeting just keep working with David or on that stuff for him. Start looking at the ConnexOne tables in TIP BQ and start writing the SQL/Python to run it

deactivateduser (deactivateduser168066-themedialaboratory@slack-corp.com)
2024-05-16 15:57:07

Got it, I just wrapped up with David and he said he'll be sending us documentation outlining more specifically what he wants. I'll start looking at the Connex tables now.

Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-05-16 15:57:46

Ya see what you can do from what you know now.

Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-05-16 15:57:55

I’ll work on getting you a Looker login

👍 deactivateduser
Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-05-17 15:44:40

Hey I just messaged Myjanhee about 1 duplicate still in there

Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-05-17 15:45:45

Is she in the office today?

deactivateduser (deactivateduser168066-themedialaboratory@slack-corp.com)
2024-05-17 15:51:21

Let me go check

deactivateduser (deactivateduser168066-themedialaboratory@slack-corp.com)
2024-05-17 15:53:52

She's not at her desk

Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-05-17 15:55:14

Is her bag there? Can you ask if she is in today?

Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-05-17 15:55:41

I know they leave early so I don’t want to wait for the vss to go home

deactivateduser (deactivateduser168066-themedialaboratory@slack-corp.com)
2024-05-17 15:55:55

Her stuff is there and she came looking for you a couple of hours ago. I'll check over there again in a bit.

Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-05-17 15:59:21

Ok Cam needed her to run some pivots on an excel spreadsheet and then asked me to help her. I did it really quick and sent it to them.

👍 deactivateduser
Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-05-17 16:02:02

Try to get her or anyone else on the team to review it and make the edit so I can run and send to Chris

deactivateduser (deactivateduser168066-themedialaboratory@slack-corp.com)
2024-05-17 16:02:22

Will do

Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-05-17 16:13:10

She emailed me saying there are some issues with the pivots I sent her and then sent a new file but it doesnt look complete either. Can you get with her and update what ever she needs?

deactivateduser (deactivateduser168066-themedialaboratory@slack-corp.com)
2024-05-17 16:13:47

Sure thing, I'll head over there again now

Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-05-17 16:15:00

This is the one with my pivots

VSS MONTHLY COMPLETIONS (3).xlsx
Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-05-17 16:15:29

Link to her new version of data : https://docs.google.com/spreadsheets/d/1etsYga08hNzA3neBBpi6dVdwN2E66awqiLo6gEvwfEE/edit#gid=1492934639

Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-05-17 16:16:22

I just added all tabs into one All Data tab and then ran a pivot on that tab

Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-05-17 16:16:52

filtered for anything with "flat" as a case type

Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-05-17 16:17:42

to get just a flatiron only breakout and then the opposite for the others

Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-05-17 16:18:10

not sure what she needs but please make any edits and then send it to me when done

👍 deactivateduser
deactivateduser (deactivateduser168066-themedialaboratory@slack-corp.com)
2024-05-17 16:23:44

She had someone helping her when I got there and said that her daughter's rushing her to leave. She said she'd send you what it is she wants changed and I asked her to include me.

deactivateduser (deactivateduser168066-themedialaboratory@slack-corp.com)
2024-05-17 16:34:45

I also reminded her about the duplicate but she said that was a separate issue

Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-05-17 16:36:31

Ok thanks. Keep you’re eye out for that update from her. Once she sends it make the edits and send to me.

Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-05-17 16:37:20

Feel free to head out if you want. Just keep an eye on your email and slack

deactivateduser (deactivateduser168066-themedialaboratory@slack-corp.com)
2024-05-17 16:38:38

Will do

Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-05-17 16:39:02

Have a nice weekend

👍 deactivateduser
deactivateduser (deactivateduser168066-themedialaboratory@slack-corp.com)
2024-05-17 16:39:21

Thanks Nick, you too!

👍 Nicholas McFadden
deactivateduser (deactivateduser168066-themedialaboratory@slack-corp.com)
2024-05-17 20:46:42

Evening, Nick! All Myjanhee sent me was a forward of her email thread to you asking to provide her with pivots against the new version of her data. I don't know if that's the fix she needed but I went ahead and repeated the process you described using that data anyways. Hope you're weekend is off to a good start!

Shane's VSS MONTHLY COMPLETIONS.xlsx
Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-05-17 20:47:20

Ok thanks!

👍 deactivateduser
deactivateduser (deactivateduser168066-themedialaboratory@slack-corp.com)
2024-05-20 13:51:15
gcf_reporting_20240513 (1).xlsx
deactivateduser (deactivateduser168066-themedialaboratory@slack-corp.com)
2024-05-20 14:31:35

Hey, the reporting worked when using your account. It's good to go!

BG_Custom_Report_520-Custom Report-2024-05-20.xlsx
Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-05-20 16:27:41
gcf_reporting_20240520 Review v2.xlsx
deactivateduser (deactivateduser168066-themedialaboratory@slack-corp.com)
2024-05-20 17:33:10
gcf_reporting_20240520_review_v3.xlsx
Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-05-28 17:20:33
GCP Reporting (5.19.24)(SL) 3.xlsx
Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-05-28 17:22:34

cid ="tortintakeprofessionals.client.resource.owner" csecret= "3RsxCggviuYBdBZQ15DT1faJFOPRJLMG" uname= "nickmcfadden@tortintakeprofessionals.com" upass= "tip123"

Make a Post request for Authorization Tokens

def getlrauth(cid, csecret,uname,upass):

Authorization endpoint

auth_url = '<https://auth.lawruler.com/tortintakeprofessionals/identity/connect/token>'

## Pass credentials
body = {'grant_type': 'password',
            'username': u_name,
            'password': u_pass,
            'scope': 'openid profile read write offline_access',
            'client_id': c_id,
            'client_secret': c_secret
        }
headers = {'Content-Type': 'application/x-www-form-urlencoded'}
# Execute authorization token request
auth_resp = <a href="http://requests.post">requests.post</a>(auth_url,data=body,headers=headers)

# Store Token Info
auth_data = auth_resp.json()
a_token = auth_data['access_token']
token_type = auth_data['token_type']
r_token = auth_data['refresh_token']

return a_token'
Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-05-28 17:24:00

def getcasetypes(atoken): url = "https://apim.lawruler.com/tortintakeprofessionals/ApiList/GetCaseTypes"

headers = {
    'Content-Type': 'application/json',
    'Authorization': f'Bearer {a_token}'
}

response = requests.request("GET", url,headers=headers)

# Retrieve json response
json_resp = response.json()

data = json_resp['Data']

return data
deactivateduser (deactivateduser168066-themedialaboratory@slack-corp.com)
2024-05-29 13:10:44

Hey, Nick. I've noted the current status and lead id's for the records that look like they're causing the discrepancy between columns w and x. Let me know if there are any adjustments you'd like me to make.

gcp_reporting__5.19.24__sl__3.xlsx
deactivateduser (deactivateduser168066-themedialaboratory@slack-corp.com)
2024-05-30 10:45:58
gcp_reporting__5.19.24__sl__3.xlsx
Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-05-30 15:17:42

Hey I haven’t heard from the CSPs that handle the BG cases. Can you ask to get the spreadsheet they are using to track the completed secondary interviews. It maybe the one tied to the Google forms that I made for them but not sure.

deactivateduser (deactivateduser168066-themedialaboratory@slack-corp.com)
2024-05-30 15:20:33

Sure thing! I switched to work from home after lunch (my little sister told me the ceiling of our first floor is leaking water beneath the master bathroom on the second floor) but if you know the names of who I should ask, I'll reach out to them right away.

Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-05-30 15:28:16

Ok all good, just give me a heads up if something like that happens. I can’t remember their names so please ask Myjahnee

deactivateduser (deactivateduser168066-themedialaboratory@slack-corp.com)
2024-05-30 15:28:47

Will do!

Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-05-30 15:28:51

I know they sit like right in front of the right hand doorway to that area

Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-05-30 15:29:25

Just to give Myjahnee a reference

👍 deactivateduser
deactivateduser (deactivateduser168066-themedialaboratory@slack-corp.com)
2024-05-30 16:04:49

Myjahnee directed me to Henri Engle and Vernon Evans who supervise the CSP team. I've reached out to them on Slack and Gmail, I'll let you know as soon as I hear back from them.

👍 Nicholas McFadden
Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-05-30 16:05:16

Thanks

👍 deactivateduser
deactivateduser (deactivateduser168066-themedialaboratory@slack-corp.com)
2024-05-30 16:46:27

Hey, Vernon got back to me saying that he'll try to help us out. He feels that it isn't something that the CSP team would have however as they don't complete sexual abuse interviews. Did you talk to someone from VSS maybe instead?

Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-05-30 16:50:19

The ladies on that side called the lady who sits in that first row at the end. She said she would reach out with the document they use for tracking. Yes it’s VSS but when the status gets changed to “Secondary Interview Completed” by the CSPs I believe and I think they are notating when they are making that change.

Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-05-30 16:51:47

Hold on the lady is messaging me now

👍 deactivateduser
deactivateduser (deactivateduser168066-themedialaboratory@slack-corp.com)
2024-05-30 17:17:20

I'll start trying to put together a script that assigns the date the interview was completed from her spreadsheet to our custom report spreadsheet.

👍 Nicholas McFadden
Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-05-31 13:13:47

Lawruler API docs: https://support.lawruler.com/hc/en-us/articles/360042382314-The-Legal-CRM-API-Guide

👍 deactivateduser
deactivateduser (deactivateduser168066-themedialaboratory@slack-corp.com)
2024-05-31 13:28:54

Merged BG Spreadsheet:

BG_Report.xlsx
Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-06-03 12:43:33
gcp_reporting_sl_6.03.2024.xlsx
Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-06-03 12:43:44
Flatirons Campaign Benchmark Data (6).xlsx
deactivateduser (deactivateduser168066-themedialaboratory@slack-corp.com)
2024-06-03 14:36:09
gcp_reporting_sl_6.03.2024.xlsx
Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-06-04 11:20:14
Vici_API.ipynb
Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-06-04 11:21:45
API information.xlsx
Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-06-04 11:22:58

https://vicidial.org/docs/NON-AGENT_API.txt

Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-06-04 11:23:39

call_status_stats

deactivateduser (deactivateduser168066-themedialaboratory@slack-corp.com)
2024-06-10 16:08:02

Hey Nick! Is it alright with you if I work from home on my birthday (Thursday)?

👍 Nicholas McFadden
Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-06-10 16:15:05

Ya that's fine

deactivateduser (deactivateduser168066-themedialaboratory@slack-corp.com)
2024-06-10 16:16:13

Thanks! Are you team Android now or do you think your phone is fixable?

Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-06-10 16:32:09

Waiting to find out if they can save it

Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-06-10 16:32:25

Debating if I'm going to switch

👍 deactivateduser
deactivateduser (deactivateduser168066-themedialaboratory@slack-corp.com)
2024-06-11 13:24:42

Hey Nick, here's the completed financials. Nothing too irregular, just a couple of campaigns from the most recent weeks that were changed by +- 1 retained cases

gcp_reporting__20240610.xlsx
🙏 Nicholas McFadden
deactivateduser (deactivateduser168066-themedialaboratory@slack-corp.com)
2024-06-17 16:19:33
gcp_reporting__20240617 (1).xlsx
🙏 Nicholas McFadden
deactivateduser (deactivateduser168066-themedialaboratory@slack-corp.com)
2024-06-17 17:06:12

Hey, Nick! Just thought I'd send you a message explaining how I compile the BGReport in case we don't get a chance to download today. My script goes through your custom report and fetches only the rows with the status 'Secondary Interview - Completed' (I attached a screenshot showing rows with various statuses as an example of what it's filtering out). It then looks through the VSSCompletions spreadsheet and finds the date the interview was completed for each of the rows from this result set.

Unfortunately this leads to about 2 or 3 dozen rows that don't have a date found as the VSS team occasionally uses a lead id from a status other than 'Secondary Interview - Completed' (the lead id is the matching identifier used between the two spreadsheets). For these ones, I go through the VSS_Completions spreadsheet manually, look them up and assign their dates.

So the report that I send off only includes rows which have clients who have completed their secondary interviews. Let me know if you have any questions or would like me to send off the report (it's complete now).

Screenshot 2024-06-17 145006.png
BG_Report_Rearranged.xlsx
Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-06-18 11:04:03
Abuse Cases with Project ID and Shield Intake ID (1).xlsx
deactivateduser (deactivateduser168066-themedialaboratory@slack-corp.com)
2024-06-19 19:38:50
gcp_reporting__20240617_-_updated.xlsx
Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-06-24 12:20:06
gcp_reporting__20240624.xlsx
deactivateduser (deactivateduser168066-themedialaboratory@slack-corp.com)
2024-06-24 16:16:45
gcp_reporting__20240624.xlsx
deactivateduser (deactivateduser168066-themedialaboratory@slack-corp.com)
2024-06-24 17:07:39
gcp_reporting__20240624.xlsx
Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-06-25 12:20:12
Flatirons Campaign Benchmark Data (13).xlsx
Nicholas McFadden (nickmcfadden@shield-legal.com)
2024-06-25 12:58:11

in stormtrooper