deleted-U05QUSWUJA2
2024-04-10 12:14:37

@Ryan I looked into the data coming across from ConnexOne into BQ. I noticed that things aren't filling out all of the tables too. However, I did find "created" date, "leadid", and "casetype" as a json string in the "custom" field of the "contactdata" table in the "connexonetip_cxm" dataset. Should be able to grab that info from there but what other info might you need for your project?

deleted-U05QUSWUJA2
2024-04-10 12:17:45

If in order to match things up with Law Ruler, ConnexOne has the data you are looking for coming across as nested json strings isn't ideal but not a problem either. This is a perfect example of using DBT to parse this out with SQL for one of the transformation layers!

deleted-U05QUSWUJA2
2024-04-10 12:19:01
image.png
Ryan (ryan@themedialab.agency)
2024-04-10 12:25:30

@deleted-U05QUSWUJA2 @Nicholas McFadden, excellent on that connection string find.

But I was looking for these Outcomes in all the Outcomes tables and could not find all the data values, so what's up with the tables not being completely filled out? That is what held me and Luke up from trying to recreate the Horizontal Call Status Report in SQL for doing ConnexOne revenue capture for AWS TIP Dash for weekly and monthly invoicing.

Ryan (ryan@themedialab.agency)
2024-04-10 12:26:21

SELECT ** FROM `tort-intake-professionals.connex_one_tip_cxm.outcomes` WHERE name LIKE '%Transferred to PD%' -- e-sign DONE OR name LIKE '%Sent E-sign%' -- e-sign DONE OR name LIKE '%House Flag (Final)%' -- e-sign OR name LIKE '%BCL Email Transfer%' -- case review OR name LIKE '%BCL Phone Transfer%' -- case review OR name LIKE '%Secondary Interview Final%'; -- post retainer

Ryan (ryan@themedialab.agency)
2024-04-10 12:26:36
image.png
deleted-U05QUSWUJA2
2024-04-10 12:26:37

I agree it should be not sure why it’s not. I’m scheduling a call with Adam

deleted-U05QUSWUJA2
2024-04-10 12:28:10

Just saying it might be nested in some other table for now if you need to get at it. I’ll keep searching and let you know the results of my convo with Adam

Ryan (ryan@themedialab.agency)
2024-04-10 12:29:07

Perfect.

Ryan (ryan@themedialab.agency)
2024-04-10 12:29:49

Other than that, we'll use our time with the DB team to learn where core reporting data lives:

Talk Time by lead Talk time by campaign ... by queue by status, etc.

deleted-U05QUSWUJA2
2024-04-10 12:41:59

Talked with Adam, unfortunately he doesn't have access to the DB so we will need to wait to talk with them tomorrow. However, he confirmed that the custom field I was seeing was just for the LR-Connex lead match as he was the one that at least set that up.

Ryan (ryan@themedialab.agency)
2024-04-10 12:47:49

Is that his permanent solution, and does @Brian Hirst know how to set that up in the future?

deleted-U05QUSWUJA2
2024-04-10 13:01:38

As far as it making the matches I guess more or less but he doesn’t have access to the DB so he isn’t sure how it’s getting stored. It would be more getting there DB team to parse his responses