To join the video meeting, click this link: https://meet.google.com/ttj-snhk-eee
Otherwise, to join by phone, dial
Morning, have a dental appointment at 9AM CT, so you can skip todays meeting and I'll post more of the "short videos" for each step. Then get to work on the diagraming in Miro of this work/data/tech flow. I'll setup time with you and James to review the architecture early next week.
Once I see the flow, I'll have you start doing the TikTok Leads pivot, TikTok Spend allocation, and Facebook spend to leads balancing.
Hey Ryan, we going to have two meetings tomorrow?
Skip the morning routine, join me on EOM meeting.
, Hey, I am 1/3rd the way complete doing some pretty headys SQL updates to the AWS financial_log table so we can START the rest of the EOM to complete. I'll push us back another hour and see where I land.
@Zekarias Haile, good morning. So it took all of yesterday to get 4500 leads financials updated, the EOM routine is still a bit weird because of the major changes. So Monday, lets plan to get you setup on DBT Shield Legal, PGAdmin Shield Legal and GCP Shield Legal. Onboard you, then I will have you start doing the financials with me next week.
Good morning Ryan, I understand that you had busy day yesterday. Sounds good. I will see you Monday!
GM Ryan, I will be 5 minutes late. Sorry about that.
Hey Ryan, did you already assigned me a task on Monday?
Can you also share me the fireflies recording for today's meeting? I wanted to review it.
@Zekarias Haile, https://app.fireflies.ai/view/Zeke-Onboarding::0uJis6p3iFTv1ze6
And send me link to the task in Monday you are referring to
I get a notification when someone assigned me a task on Monday and Im wondsering if you alreday assigned me or not?
Hey @Ryan Do we have a time range that helps determine whether a dashboard is considered active or inactive based on when it was last shared?
That would be on the orders, dashboard, but those old dashboards generally don’t have dates, but in some cases they might. I’m at the dentist, I’ll be online shortly.
I hope you're back now. I don't see orders on the dashboard. If you got few minutes I would love to show me that may be today or tomorrow.
Im froze… my pc is restarting by itself
@Zekarias Haile, new Looker Studio (freebie product) for Flatirons should have
Page 1 - Summary by case type (many data sources) • Order Stats • Order Completions • Order Cost Per Contract (Revenue / Signed Contracts) • .... • .... Page 2 - Flatirons Orders (ds: flatironsorderspaid_spent)
Page 3 - Flatirons Cases (ds: flatironstipfinanciallog) • Filter by leadsource "contains" Shield Legal in table (the retainers signed) • AND • Filter by currentstatus must ONLY retainers deals, and that is done by status field (not = to "Signed e-Sign%") OR typeofcase fields contains: PFS, VSS, MRT, MIL, SEC, MED. Page 4 - Flatirons Cases (ds: flatironstipfinanciallog) • Filter by lead_source "does not contains" Shield Legal in table (the post retainers such as PFS, VSS, MRT, MIL, SEC, MED (the source should containt DL Flatirons or ACTS DL Flatiron or Shield Legal
Per Page Layout:
• Better Header (Art, Logo of Firms, Shield Logo, border color) • Fliters • Scorecards • Linear Bar or Line Charts • Tables (table or pivot table)
@Ryan Can you please share me the November 2024 - TIP Financials Dashboard sheet. You only shared the October one.
Done, sorry. Back now, I'm sure I'll have work after this to validate financial metrics, so you got time to prep
@Ryan I'm done. Let me know when you are free to go over it with you.
*Thread Reply:* I setup time tomorrow. I am working on the SL financial metrics from Cam meeting. But how is your DataStudio Old Dashboard enhancement coming on Flatirons All Cases?
*Thread Reply:* Okay. I thought you said forget about this project after you tried to show me how to freeze the column on chatGPT and you even discarded the recorded video
*Thread Reply:* @Zekarias Haile, sorry, I meant only the ONE Columnar Freezing. Not the Flatirons All Cases Data Studio Enhancementy project though, that is this Dashboard:
Duplicate this one: https://lookerstudio.google.com/u/0/reporting/7de80e04-43be-4479-af16-acbb9b517dbf/page/ASVvC
Make new one more like this one: https://shieldlegal.cloud.looker.com/dashboards/75
*Thread Reply:* Ohh my bad. Okay, I will.
@Zekarias Haile, going to push EOM back to afternoon, let me know when you have the Sheet for October ready and PG Admin installed, here is login for Shield Legal only:
db_un: dbmasteruser
db_pw: k(h~$(5!Lig{nX7Qg8PDAJx,.z0#y&>c
db: dbmaster
port: 5432
server: <a href="http://ls-48c3a8e572feb82920362771ec6c47370fa262da.cezd6k0feuf4.us-west-2.rds.amazonaws.com">ls-48c3a8e572feb82920362771ec6c47370fa262da.cezd6k0feuf4.us-west-2.rds.amazonaws.com</a>
I'm sorry I didn't see the your message. Sure.
Ok, for now, here is your priority if this helps, I am on the call
- Setup PG Admin
- Prepare November 2024 sheet for Call Costs for EOM
- Nations/Lu Dashboard in DataStudio
- Flatirons DataStudio Dashboard clone from Looker Enterprise Board
@Ryan please send me today's recording.
Good morning @Ryan quick question for you about the nations dashboard.
The custom query is supposed to pull the data from the Big query right? if so, it says "there is no data to display" is that because the campaign is new and no data is there to pull from?
campaign is new, no data to display yet, but as long as your SQL matches the Leadspedia second segment (aka Customer Name), then it will find them when we go live.
Okay. Another question Will I use "Calculated fields " to get "Total Budget Spent / Cost To Date". column?
yes! Be sure those do a SUM function too on the fields in the custom query, you can see many many examples in older versions.
If you have 20–30 minutes available, could we schedule a short meeting? I need some help.
Okay. Give me 5 minutes to back to office Im in lunch.
To join the video meeting, click this link: https://meet.google.com/bzi-fssi-acx
Otherwise, to join by phone, dial
https://app.fireflies.ai/view/bzi-fssi-acx::qyaL2fjeJAKJ5xbn
Good afternoon, Ryan! Dustin asks me to work on integration project that I started and we were waiting for Docs which we got it today PgAdmin has been installed. FYI
Excellent! Thanks Zek, let me know when you are done that integration.
NP! Sure, I will.
I would assume we will start testing on Monday.
GM, Ryan! Did I missed a meeting this morning?
No, I just have you monitoring the morning financials meeting via that invite, you do not have to be on it until I give you notice. Hammer these dashboard skills for Looker DataStudio and Looker Enterprise, and SQL for BigQuery and POSTGres will come high and hard soon. I will add you to training for James today though.
Got it. Alright, I'm working on google cloud skills short course to get familiar with it. sounds good.
GM @Ryan I will be 10min late. Stuck at traffic
I got a notification for daily routine meeting scheduled from 9-10am Pst. Do we have that meeting now?
No, I was having time zone glitch challenges, I got it all fixed, and the meeting is at its normal time
Got it. just wanna make sure. I don't want to miss a meeting.
GM Ryan, FYI the integration I was working on is done for now. We will come back to it may be in a week or so. Now I'm reading on Looker and BigQuery. Do you have some time for me today? I need some help.
I'm having some issues when applying filtering and conditional formatting on the dashboard. I would love to go over with you when you have time today or tomorrow please.
Zek, be careful, you had accidentally updated the Production, shared with Client, Client Dashboard for Flatirons. Cameron called me and I fixed it removing the graph and score card. Be sure you are only creating a new version on this DEV version.
ohh my bad. I'm sorry about that. I will do.
GM @Ryan, Greg asked me to build a dashboard for LegaFi-NEC Baby Formula. And the custom query is not appearing. Tried to research on it and it says probably a permission issue which doesn't make sense
If you do select Asterix from sldashboard.financialdetail where where Customer like “%LegaFi%’
yeah, I have my query written ready like this
SELECT ** FROM shield-legal-bi.sldashboard.financialdetails WHERE customer LIKE '%LegaFi%' AND campaign LIKE '%NEC Baby Formula-LegaFi%';
but since the custom query is not appearing when I click it I couldn't paste or write a query
To be clear, when I click Custom Query>Shield Legal BI > the option to paste or write the custom query is not appearing
I did select but not appearing...
Okay. Thanks!
Do we know the difference between LegaFi Form and LegaFi
You want all three types, the one without form the one with form and the one with TikTok. That’s why you do %customername %
Basically your sequel should not include the form, the TikTok part just the customer name
Hey @Ryan, if you have a minute could you please take a look the NEC baby formula for LegaFi dashboard before I shared with Greg?
The SQL is wrong, and we have tons of rows, so you should see those in that sql below in BQ and in your dash once fixed.
s/b
SELECT **
FROM shield-legal-bi.sl_dashboard.financial_details
WHERE customer LIKE '%LegaFi%'
AND campaign LIKE '%NEC%';
and oing a LOWER function on customer and campaign and puting the string in lowercase is always a good pratice in "string matching in sql".
Good Morning @Ryan, I believe I fixed it now. Can you please take a look to see everything is good to go?
Zeke, make sure all formatting is done (center justified, zero decimals, and your Cost field is wrong, Client Cost = cdailyrevenue, please fix.
^ above comments are fixed. But when I was checking everything I saw one thing that I marked on the screenshot and that needs to be "customer" instead of campaign but It wont let me change it. Any Idea as to why?
I saw you were adjusting some stuff. Can I share the link with Greg now?
Next request, lets knock it out same-day, once you have a copyable template, this one, it goes MUCH faster.
@Zekarias Haile, I added page 59 to the Shield Financials dashbgoard in LookerE, can you resort them numerically please: https://shieldlegal.cloud.looker.com/boards/2
@Ryan I see you pushed the meeting to Tuesday, Jan 7. I forgot to tell you that I requested a day off on that day. So can we make it Jan 8? Thanks!
SL Morning Financials SQL statements, put these in your PGAdmin > SQL folder.
SL Financials Rebuild Lambda App: https://us-east-1.console.aws.amazon.com/lambda/home?region=us-east-1#/applications/dailyfinancials-rebuild-py311
Console sign-in URL https://960735831404.signin.aws.amazon.com/console
User name zhaile@shield-legal.com
Console password _zGFX1**N
TIP SL e-Signs Only Dahsboard: https://shieldlegal.cloud.looker.com/dashboards/43?Source=%25Shield+Legal%25
when you got a chance please share me today's recording.
Today, I’ll @ tag you when you can pull sold leads and build spreadsheet with TikTok Rows and create the TikTok records in the donottouvh table. After my son’s dentist appointment, we’ll team up and add sign ups and revenue together. I’
@Zekarias Haile, calc_perf and donottouch table are ready
@Zekarias Haile, back and ready. How are you coming?
I was waiting until you tag me to start pull sold leads
Get started, send me a Google Meet, I'll be on once you have the sheet ready and tiktok spend done so I can check it before we add sign ups
@Zekarias Haile, here is the sheet with leads loaded, get spend in, create TikTok rows and let me know when done.
When sheet and table is ready, send me a copy of your sheet back to me here please. @Zekarias Haile
To join the video meeting, click this link: https://meet.google.com/anf-bewe-zrd
Otherwise, to join by phone, dial
https://www.linkedin.com/pulse/excel-power-users-toolbox-mastering-shortcuts-ultimate-zubair/
@Zekarias Haile, errors I see:
• Missing Dublin, create row • Row 32 ID 220 is wrong contract, we don not have a Van from yesterday in Sign Ups, line 34, move sign up and rev • Missing Bay Point Illinois
yeah I saw that. Greg also wants me to create two dashboard for VAM Law - Firefighting Foam and DLA - IL Juv Hall
looker is giving some errors and looking on it
yeah, when I googled the error it says error is not documented... hopefully I will figured out
I wanted to change the data source from big query to postgres and it errors out. error code: a8c84d35
@Zekarias Haile, I'm at the airport in Dallas to Vegas, so I have about 2 hours until I leave. Glad to check when ready. Let me know ETA. Ty again.
I started inserting the data on the database. I will finish before you left Dallas.
Im validating now and reconciling the dash (61) vs 60 on the database. Looking for 1 missing
This is the one (ELG-SNL) I missed it and I don't see it on leadespedia
Thank you! I’ll handle weekend, you got Monday to Fridays. I’ll keep checking it and prepare the table brick you got it down faster.
GM @Ryan, to get yesterday’s date on the DailyGetNewMediaRows I have to run the calculate performance lambda right? Just wanna make sure
Ok. I luckily have my query rows open, so don’t do anything today.
Im glad. You have that open. Im sorry about that
It was mine, so I'll Copy & paste real quick for Cam
So to be clear here, you run the lambda in the morning?
Tomorrow you can run calc_perf and moving forward, I will check #dailynumbers , and e sure they submitted. Then once you have table ready I will validate accurate, and we can let Adam do adjustments.
@Ryan I see Caleb has spending on AFFF as per his post but I don't see AFFF on my sheet. What do you think is the issue?
Double check you have all the S ones checked and the pivot table filter box.?
Or, he just spent $2600 and got zero leads. So you need to take any of his Facebook fire foam Rose, clone them and put zero leads the spend, and divide the spend by the number of Rose for Caleb‘s fire foam you copied.(E.G.2 fire form rose for Caleb equals divided by two.
All S1s are checked except UO.
Okay I will do that.
Im on row 10 out of 25 when you count on type of case
When done, please get Craig Smith (our sales guy) in gmail the DataStudio dashboard link please.
While Im doing this can you clone Caleb's AFFF?
You got 1 more left to add for 66 total @Zekarias Haile?
Yes im coping the last one,, it is not on the table
Nice work, good job. I know it's taking 3 hours, told Cameron you will get faster. This was your first "full day solo".
Ty! yeah, I know I'm taking way too much time. I think today I waisted 30min to figure out the Caleb's AFFF spend. Will do faster soon
I just shared the ELG-AFFF dash Please validate it. Starting from yesterday looker is not letting me insert an image( to put ELG logo).
@Ryan I'm almost done. But I see the same thing for Caleb's AFFF spending
Finished. But for AFFF Caleb's spending, for which buyer name I'm going to rose on Facebook?
@Zekarias Haile, take CP's spend, and use rows 300, 301 and 302 to allocate across those buyers
I don't see row 300-302? it's on the table right?
ohh my bad I was looking the far left column..
I broke out those three TikTok’s for you, his 2600 bucks that may have made up not have leads from TikTok is what you put across those three. Makes sense.
yeah, so I will just put 849.92 each on the spend and "signed count" will be 0?
Thank you. Let me know when Cam’s dashboard is ready. Please triple check the data matches before you ship.
Sure. when Cam is expecting to get that?
See the post, try by tomorrow. Should not take more than 20 to 30
Remember to run this QUERY right after Calc_Perf lamda runs. It fixes TikTok spellings and AdPlatform values if a TikTok campaign. And removes errant BLX rows.
I will run the 1st delete and update statement right?
My pc started automatically updating in the middle of running the lambda
It shouldn’t hurt it, once the table’s complete just rerun it then you will be good
As long as calculate performance show you the green
@Ryan I don't see Caleb's spending on LA wildfires and AFFF.
@ if he had zero leads, that is called dead spend. That’s where you copy all of Caleb’s Facebook versions of those, zero out the leads zero out the cost per lead and split the media evenly across each vertical. Make sense?
Put that dead spend in the bottom section. Spend with. I leads and create a formula to slit by the count of rows you copied from Facebook and made TikTok
Make sure you pivot table filter has looked for all TikTok’s
okay, so I will put the dead spend at the bottom of CP's spend on TikTok pivot? And will split the spend on the each vertical on the database table right?
@Ryan if you are free can you help me finish up please? I'm half way done.
@Ryan Im done. Except the above two that doesn't have MB info
https://lookerstudio.google.com/reporting/a6a0f187-1619-478c-b1c9-6412a9e769fd/page/p_kuc84ifv3c, its the old dash, your did not update unless you refresh as Edwards completes the S1s in the Am and it can take up to an hyour for the new dash we use to refresh. Book mark this.
What about the Michigan Pollution(Injury)?
NVM, I found it on the dash.. will update that in sec
You’re welcome, by this time next week I think you’ll have it down Pat. If the calculate performance ever fails, just let me know. There are a couple conditions where that thing can fall apart.
I’m gonna move our day to engineering project meeting coming up, I’ve got a couple things. I’m still catching up on after the travel.
@Ryan the only dead spend we have is for CP. How do you want me to relocate to all MB's ?
Never mind, we’ll worry about it, I got my case types crossed.
I had WiFi issue.. but I started inserting data into the database
CP has dead spend on AFFF so im gonna do what we have done in the last two days? I want to ask because of Cam’s message yesterday
Yes, but only need to copy one row per "buyer", so for CP you can choose the AFFF that have Customer field containing "Form" for the deadspend. Make sense?
Send me your sheet from today, we are missing tons of TikTok spend.
@Zekarias Haile, you forgot all the TikTok spend, changed Facebooks to TikTok and put revenue down, so it was all wrong. I did my best to fix it, Monday we'll do the Sheet and Insert Rows and Facebook Reallocations again to ensure you get this part correct.
I'm sorry about that. Thanks for fixing that! Okay.
@Zekarias Haile,when ready for me to validate, ping me here and drop in the XLS sheet. Thank you.
Morning Zeke, please get numbers ready for validation. I’ll be unavailable between eight and nine, but I’ll be back online by 9:30.
I'm waiting on Edward to check Wood Pellet exposure injury Missing S1s. once that is fixed. It will be ready for validation. FYI
The last one, for this contract, is not sending leads, so no row will exist. You'll have to create them and get info from LP:
To join the video meeting, click this link: https://meet.google.com/uub-wjtj-kjg
Otherwise, to join by phone, dial
@Zekarias Haile, join me for train-up real fast on this mediarawv2 table update.
@Ryan quick question, every time I built a dash I have to share with Greg owen, Andy Rogers and of course Malissa right? just wanna confirm
@Zekarias Haile, from Monday forward, do not change contract 1417 in the morning financials table updates, use 1417 and it's new price of 7500, even though TIP says it is Dicello/BG/Flatirons, IT IS ACTUALLY Dicello/Flatirons 1417, it's the ONLY contract Active in leadspedia, I fixed all of our errors on this:
@Ryan when you get a chance please grant me access to Loghamana dash
Greg asks me to built couple dashes. FYI
@Zekarias Haile, don't start until I say so, you can hop on the call please to learn what's going on with me and Ed
Gm @Ryan I know you told me not put BG in 1417. Would that be the same for Michigan?
@Zekarias Haile, huddle with me. Let me know when ready.
Illinois Youth Treatment Abuse - SGGH/Gough (TIP) - Contract
Use old dash for now, new one must not be synced yet
And also should I wait till Edward finished the S1s and the contracts? Or I can run the calculate performance now?
I asked him 40mins ago and he told me that it's going to be 1-2 hours to finish S1s
@Ryan I see the price for mortgage race 0
@Zekarias Haile validated, please run adjustments, post the fails and run master pin after. Let me and cameron know when finished.
I have to tell Cam that it's ready for adjustment before I run the adjustment right?
I just did that, thanks for catching me. I almost made the same mistake.
Np! I will run adjustment when Cam completed the adjustment.
We were about to go out lunch as a team in about 30mins, would u mind if u run the adjustments for today if Cam is not done by that time?
This is what I'm showing when I click the latest log on log stream
Click the refresh button and try again, it worked just fine for me
refreshed couple times, but it's not working
can you please post it since it worked for you
You’re in the wrong cloud watch log, you don’t use master append. You use apply adjustments cloud watch log, try that.
Oh my bad. Thank you for you help @Ryan
Going lunch and heading home. FYI. See you tomorrow
INSERT INTO public.casetypecccosts (monthend, casetype, cpl) VALUES ('2025-02-01' , 'Hair Salon Bladder Cancer' , '8.0421052631579'), ('2025-02-01' , 'Illinois Youth Treatment Abuse' , '82.3784482758621'), ('2025-02-01' , 'Michigan Catholic Clergy Abuse' , '13.1724137931034'), ('2025-02-01' , 'Michigan Youth Treatment Abuse' , '43.4090909090909'), ('2025-02-01' , 'Private Boarding School Abuse' , '28.4827586206897'), ('2025-02-01' , 'Wood Pellet Lawsuits' , '203.687577092511')
@Ryan why are we adding a row on those dates( Sep 2022), I just wanted to understand it
Because this is an old order reconciliation, and those are two that just were missing that we’re never reconciled back in those days.
Are they having much later and need to be re-allocated, they could also be declines turned into approval
ohh I see. So I will change CP to ML on copied row of 7 and 11? Where do I get then price?
I mentioned that in the other channel, I said just look at that day or days around it
@Ryan I'm showing these LR Lead ID doesn't have S1
693550 695334 695676 695675 69007 For T2 694004 For T1
I'm not quite sure if these are the causes of the discrepancies
Edward is going to post in both tables and where I will need to update in Shield ?
Yes. Make it match in the reconciliation tab of the TIP financials dashboard.
I asked Edward and he thinks some S1s are being overwritten by the code and he has to check those
@Ryan you are doing S1s in the weekend right? Edward mentioned that he is not doing in the Weekend and I was wondering if that is the reason for this
Ok. Use the I do them, it could be old deals getting approved late popping in
@Ryan when S1s are allocated, I would add them as I do in the morning right? I wanna make sure I'm doing the reconciliation right
No, you have to add them to mediarawV2, that is the final table after you run master append. If you update, do not touch, you’re putting them in the complete wrong place.
Mera V2 has math public got financial log on the tip side, the dashboard does that for you. So when you get them reconciled based upon the errors that Esteban put into the post, then you ask Esteban to make sure that you did it right if you’re not using that same reconciliation dashboard to check while you update which is what I do
Ohh ok. Makes sense. I will do that. Ty
T2 on March 10 has 24 leads (11 JA,13ML) on Billable Leads Tab, 21 ON Publicmediarawv2 (JA 10, ML 11) - need to add 3 to on mediaraw_v2 (1 JA, 2ML). So when I add those 3 I'm not going to input their spend right? just signedcount and total revenue only?
But on T1 and Depo-DL shield has 3 and 1 more leads respectively. My question here is why do we have to add to mediarowv2 while Shield already has more leads than TIP
You just add the sign counts in the revenue to make sure they match across both database
They have to match, just like you do in the morning. You’re matching them to the sign ups for the day, if there’s a row for it to be added to you update the row. If there is no row for that sign up, you add the row and of course there’s no spend.
@Zekarias Haile, you can do things in this order:
- Morning Financials for 3-13-25
- Watch video on me reconciling / fixing February 2025 here: https://www.loom.com/share/88deae722db1496c9e1a4c605a70dbff
- Reconcile March 2025 to match TIP Dash
- Watch video I'll send soon on doing DECLINES.
Thank you Ryan for the video. What makes me confused was that Shield has more than TIP in that case what im going to do on shield side? But I will see your video
We have declines too, I’m doing those now for you and maybe that cleans up it some.
@Zekarias Haile, here is DECLINES video. February matches, after we finish numbers, you get March matching:
@Ryan JV posted his spending lately. Do u want me to add it manually?
Since we didnt run the master append I can add it to the donottouch right?
Calculate performance creates do not touch table each morning when run, and will overwrite it if you run after you finished, if you haven’t processed, apply adjustments, which builds an adjustedview table and then master append put it into media raw V2
Makes sense. Thanks for the clarification.
Can you add me to the wood pellet slack channel pls
Ask @Brian Hirst , he sets them up. Ask him add you on all
Looking at the reconciliation dash TIP is off by 81 since we haven't finished the numbers today (we have 82) so it would be off by 1 technically once we done with the numbers.
That’s the one you fix. Remind Cam is temp channel please.
Good afternoon @Ryan I fixed wood pellet dash discrepancies. But when I tried to do the Chowchilla- ACTS- Ghozland campaign, it's not on the Billable leads dash to be able to filter
So it looks like the signed e-sign FLAGGED status is not included in the dash as the only discrepancy I showing is this lead Id 692439
Please make sure it reconciles, assume TIP dash is always correct.
@Zekarias Haile, let me know when Calc_Perf is complete, I need to update Call Costs in SL side since they fixed the talk time minutes issues.
Sure. Waiting on Edward to complete S1s and will run it and let you know once complete.
Send me Google Meet link, lets have you do DECLINES.
To join the video meeting, click this link: https://meet.google.com/ceo-njet-brf
Otherwise, to join by phone, dial
user: dbmasteruser
pwd: Q7B2(R8d;m`n}l%kj2M7q^S&:}1z,F
db: tip_master
port: 5432
Host: ls-ee79dd780a352064468e4dc5bfef3cbc5675526f.cezd6k0feuf4.us-west-2.rds.amazonaws.com
<a href="http://ls-ee79dd780a352064468e4dc5bfef3cbc5675526f.cezd6k0feuf4.us-west-2.rds.amazonaws.com">ls-ee79dd780a352064468e4dc5bfef3cbc5675526f.cezd6k0feuf4.us-west-2.rds.amazonaws.com</a>
Finished up the declines and dash reconciliation. dash looks good now.
I was in a meeting that's why it took me a minute. FYI
post in #C022831MSUF a snapshot it's fixed please and let Esteban, Brittany, Edward know.
@Ryan for Cam's request on paraquat DL-ML I added the row but you told me only to add the count right but since there was no row at the beginning, should I add the rev also?
@Zekarias Haile , cameron already added the revenue to one of those Rose. You need to ask him the ID so you can look on the do not touch table and then reference it back to the media raw table. So don’t add the new row you need to add the count to the one he did the revenue adjustment on.
yeah he said ID 5 but I dont see that row on mediarawv2 table on 03/23
You’ve got a match it by the campaign and Customer name because they don’t keep that ID
Got to look at the do not touch table find row five that he adjusted and go add the count to that row in media raw
i understand that part but i dont even see the campaign under yesterday's number
Good Morning @Ryan calculate performance failed while i was running it. I'm going to run it again. FYI
*Thread Reply:* Try again and if it fails, I know a way to fix it if you give me the error from the red box
*Thread Reply:* { "errorMessage": "can't multiply sequence by non-int of type 'float'", "errorType": "TypeError", "requestId": "49f69a8e-6406-4d95-89c3-603937861c21", "stackTrace": [ " File \"/var/task/reconcile.py\", line 408, in lambdahandler\n df = handler()\n", " File \"/var/task/reconcile.py\", line 175, in handler\n m1[\"Spent\"] = m1[\"Count\"] ** m1[\"CPL\"]\n", " File \"/var/task/pandas/core/ops/common.py\", line 76, in newmethod\n return method(self, other)\n", " File \"/var/task/pandas/core/arraylike.py\", line 202, in mul\n return self.arithmethod(other, operator.mul)\n", " File \"/var/task/pandas/core/series.py\", line 5815, in arithmethod\n return base.IndexOpsMixin.arithmethod(self, other, op)\n", " File \"/var/task/pandas/core/base.py\", line 1381, in arithmethod\n result = ops.arithmeticop(lvalues, rvalues, op)\n", " File \"/var/task/pandas/core/ops/arrayops.py\", line 285, in arithmeticop\n resvalues = naarithmeticop(left, right, op) # type: ignore[arg-type]\n", " File \"/var/task/pandas/core/ops/arrayops.py\", line 229, in naarithmeticop\n result = _maskedarithop(left, right, op)\n", " File \"/var/task/pandas/core/ops/arrayops.py\", line 165, in maskedarith_op\n result[mask] = op(xrav[mask], yrav[mask])\n" ] }
*Thread Reply:* ^ here's the error as it failed again
*Thread Reply:* That’s the error I expected, that’s good.
quick questions, so for all Cam rows I will be just changing the ad platform to google right?
And I will not be adding paraquat-DL-ML deal that I added yesterday. Just wanted to confirm
In this board, please reorder the dashboard / pages to be in sequence:
@Zekarias Haile, please reorder again, I had to add page 61 to the Shield board.
@Ryan Do we have CA Juv with ACTS- DL/Flatirons? I only see CA Juv DL/Flatirons
@Ryan Calc fun lambda failed... tried again still failed
{ "errorMessage": "can't multiply sequence by non-int of type 'float'", "errorType": "TypeError", "requestId": "c6342ac9-8804-4024-8ef3-46c37a1969b8", "stackTrace": [ " File \"/var/task/reconcile.py\", line 408, in lambdahandler\n df = handler()\n", " File \"/var/task/reconcile.py\", line 175, in handler\n m1[\"Spent\"] = m1[\"Count\"] ** m1[\"CPL\"]\n", " File \"/var/task/pandas/core/ops/common.py\", line 76, in newmethod\n return method(self, other)\n", " File \"/var/task/pandas/core/arraylike.py\", line 202, in mul\n return self.arithmethod(other, operator.mul)\n", " File \"/var/task/pandas/core/series.py\", line 5815, in arithmethod\n return base.IndexOpsMixin.arithmethod(self, other, op)\n", " File \"/var/task/pandas/core/base.py\", line 1381, in arithmethod\n result = ops.arithmeticop(lvalues, rvalues, op)\n", " File \"/var/task/pandas/core/ops/arrayops.py\", line 285, in arithmeticop\n resvalues = naarithmeticop(left, right, op) # type: ignore[arg-type]\n", " File \"/var/task/pandas/core/ops/arrayops.py\", line 229, in naarithmeticop\n result = _maskedarithop(left, right, op)\n", " File \"/var/task/pandas/core/ops/arrayops.py\", line 165, in maskedarith_op\n result[mask] = op(xrav[mask], yrav[mask])\n" ] }
when you get a chance please send me the link
Now calc fun succeeded. Thanks for the help @Ryan appreciated!
Zeke, I’ll be unavailable Sunday morning, so please do the financials on Sunday. You don’t need to start at 7 AM however, it’s OK to sleep in.
I’m available in slack for questions, just on the go and won’t have the time
I will be in church until 11am. Is that okay if do it after 11?
Yes, just post to me and Cam that morning early and say you’ll start at 11am
@Ryan I see Michigan pollution and Wood pellet doesn't have S1s?
Edward can update tomorrow for yku, just tell him who you gave them too.
Thank you for that today, I was on a road trip for five hours
quick question @Ryan I don't see CP's Snapchat spend on the rows. Is that something that I need to add it manually? my 1st time seeing Snapchat spending
SnapChat will operate JUST like TikTok, own spend, own leads, etc. But you have to create a new tab pivot table in your XLS to be just for SnapChat, make sense
So, yes, manually until we have new MBs in the new Medi Buyer Portal
make sense, that is what I was thinking. Will create the pivot
make sense, that is what I was thinking. Will create the pivot
Is taking 8 minutes to cut and paste for one sheet to the never.
Good morning @Ryan , for the paraquat 12 am I good to update the customer column from "Dicello 12 /Flatirons (TIP)" to "Dicello 13 (TC/J) (TIP)" ?
@Zekarias Haile, what is status of the new single page that would have taken me 30 minutes to create for Media Buyers Unsold Leads? I needed that last week to test your SQL and dashboard build out.
I have the SQL ready and I can query the unsold leads but the cloned page was giving me an error saying that it couldn't get the data. So I'm trying to create one from the scratch
When you get a chance please take a look
https://lookerstudio.google.com/reporting/3d04ef2e-a722-4e64-b5a4-8843f435eb04
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;
@Zekarias Haile https://meet.google.com/qig-frds-ntf
@Ryan Since now JA confirmed. You want me to built for others too right? if so, pls share their dashes
*Thread Reply:* @Zekarias Haile, you are already shared, go to datastudio home page and search "media buyer" and they will all appear in the search results to add those tab #4s for each. Please triple check you have done them accurately before delivery. Thank you.
*Thread Reply:* ohh my bad. okay will do.
@Zekarias Haile, good morning. On Saturday the 27th, can you do 4/26/25 financials? I have kid sports very early for a golf tournament thing.
*Thread Reply:* @Ryan do u wanna me do the financials tomorrow or Sunday? the 27th is Sunday that's why I'm asking
*Thread Reply:* Oh, do both days please. The kids gold is two days.
*Thread Reply:* okay. But on Sunday I will be doing after 11am
Please run this on SL PG DBMaster, I am having PG Admin app issues I cannot resolve, and ping me when completed @Zekarias Haile
UPDATE public.monthlyopexpenses SET opexpense = '39856.84' WHERE monthend = '2024-01-01'; UPDATE public.monthlyopexpenses SET opexpense = '41801.10' WHERE monthend = '2024-02-01'; UPDATE public.monthlyopexpenses SET opexpense = '31586.77' WHERE monthend = '2024-03-01'; UPDATE public.monthlyopexpenses SET opexpense = '52344.40' WHERE monthend = '2024-04-01'; UPDATE public.monthlyopexpenses SET opexpense = '49298.23' WHERE monthend = '2024-05-01'; UPDATE public.monthlyopexpenses SET opexpense = '10091.47' WHERE monthend = '2024-06-01'; UPDATE public.monthlyopexpenses SET opexpense = '57631.90' WHERE monthend = '2024-07-01'; UPDATE public.monthlyopexpenses SET opexpense = '75472.58' WHERE monthend = '2024-08-01'; UPDATE public.monthlyopexpenses SET opexpense = '47800.87' WHERE monthend = '2024-09-01'; UPDATE public.monthlyopexpenses SET opexpense = '30512.94' WHERE monthend = '2024-10-01'; UPDATE public.monthlyopexpenses SET opexpense = '37736.30' WHERE monthend = '2024-11-01'; UPDATE public.monthlyopexpenses SET opexpense = '40874.97' WHERE monthend = '2024-12-01'; UPDATE public.monthlyopexpenses SET opexpense = '50326.55' WHERE monthend = '2025-01-01'; UPDATE public.monthlyopexpenses SET opexpense = '30529.86' WHERE monthend = '2025-02-01'; UPDATE public.monthlyopexpenses SET opexpense = '25832.87' WHERE monthend = '2025-03-01';
@Zekarias Haile, hop on please, have an ad hoc Call Center task for you that will be great way for you to really get the CCs EOM stuff down that is coming from Ownership and Finance team.
@Ryan Im out for lunch now. Can we do it when I get back in 30min?
That SQL is for TIP_MASTER DB, here is how to get it in PG Admin with new connection, if you get stuck, ask Edward for help @Zekarias Haile
TIP AWS Lightsail Postgres DB PG Admin or DB client
UN: dbmasteruser
PWD: Q`7B2(R8d`;m`n}l%kj2M7q^S&:}1z,F
Datbase Name: tip_master
Port: 5432
Endpoint: <a href="http://ls-ee79dd780a352064468e4dc5bfef3cbc5675526f.cezd6k0feuf4.us-west-2.rds.amazonaws.com">ls-ee79dd780a352064468e4dc5bfef3cbc5675526f.cezd6k0feuf4.us-west-2.rds.amazonaws.com</a
@Ryan It's $908k now. Thanks for your help again. Appreciated!
@Zekarias Haile , great. Post on channel we are closed for month!
@Zekarias Haile , my son’s birthday weekend is this weekend and I need you to do numbers this weekend. It’s ok to do them later for church on Sunday.
Good morning @Ryan, Rose told me that the 2024 call costs are still off, specially Dec 2024 is super high. So I'm gonna work on that
Sounds good. those are "daily operating expense" figures FYI
Or she keeps chaning them on us, she asked me to update Dec 2024 twice already.
ohh okay, I didnt know she was referring daily operating expenses. Thanks for letting me know that.
@Zekarias Haile, excuse me, that was untrue, I got wires crossed. Dec '2024 call costs if wrong need fixing as you know how to do.
Hey Ryan, tomorrow I have a Dr. appointment at 9:45 I will be heading once I finished the morning financials. If Cam finished the adjustment before I comeback. Could you please run the apply adjustment and post fails?
@Ryan Im back I will run the adjustment. Thanks!
sorry I was away from my desk... didn't see the huddle
@Zekarias Haile, remember I am on PTO in Florida from today through Monday. Thanks again for doing the numbers each day.
Call my cell phone 858–2 89–7818, I need you to remove some former employees from the dashboard immediately
@Ryan I think it would be great if Joe could remove their account from the google workspace so that we don't have to remove them on each dashboards
@Zekarias Haile , that is coming later. Work on the ones you have created n the last five months
Please get started. I did this once for 76 dashboards
ok. yeah I'm working on that, already did 53 dashboards
I know you didn't mention Brian Thiessen, but should I remove him as well?
I have removed all of them from Looker data studio but I don't see none of them on Looker Enterprise
Sorry about that fire drill, it came out of nowhere late on Friday.
Please reoder LookerE Shield Dashboard, I added a new page - 0 @Zekarias Haile
@Zekarias Haile, please do Friday numbers this Saturday, I'm camping with my boys. Thank you.
Hey @Ryan are able to run the Lambda and make sure TIP fin_log table has a row tomorrow morning?
Good morning @Ryan I wanted to let you know that TIP fin_log doesn’t have rows
@Zekarias Haile, use this dashboard, the new data to do todays numbers for now, I have no clue how to fix this fast:
@Zekarias Haile, you can double check the old dash versus new dash soon. I got it working, still prcoessing though. It has 90 for now.
Ok, yeah I see 103 now... missing few. Thanks.
Ok, yeah I see 103 now... missing few. Thanks.
This is the base table... waiting on Brian to finish the media buyer table
Ping Brian and Nick in a channel and ask when it will be ready, so I can follow in behind you. Thanks.
@Ryan Im sorry Im not in Vegas. Im out of town.(didn’t bring pc with me)
@Zekarias Haile, I could not do it, it's on you. Please fix
Please reorder the LookerE > Shield Financials dashboard, I added pages 46.25 and 46.5 for Cam and Marc @Zekarias Haile
Let me know when done, so I can tell Marc it's ready
Okay, will do when Im done with the meeting.
Good Morning Ryan, Can you please grant me access to this dash https://lookerstudio.google.com/u/0/reporting/29151c3f-1753-4c41-8a68-bfbccdf68557/page/ASVvC
Can you post revenue and media spend after numbers for validation and trust building in #C04PBGEGXK9 please?
I can do that but that won't prevent from adding the test lead to the revenue( if that was the intention). Actually today I forgot to run the query that remove the test lead. That is why it happened
Remove all LP test is TRUE leads from financials for sure.
Hey @Ryan did you say that you're on PTO this week? checking because I will be traveling to Seattle tonight for the weekend.
Do them Saturday and Sunday, I’ll get tomorrow on holiday. Have fun in Seattle.
You used to live in Seattle right? Any recommendations or must see places?
Fish market, snoqualmie falls, space needle, queens Ann hill to eat
@Zekarias Haile , you good on numbers? I cannot today or tomorrow
@Zekarias Haile @deleted-U07UDKM724B , numbers please
See inbox from me from Marc please. Let me know first.
*Thread Reply:* @Ryan I don't see $7755 spend on mediarawv2 in July. But the total media_spend for Firefoam in July is more than $27k
*Thread Reply:* In media raw v2, move the fire foam spin off of the contracts for TC, and move them to other ones where we put sign ups, but had no spin for the same day. That’s what he wants.
*Thread Reply:* Move the spend from customers that has TC to non TC customers on the same day right?
*Thread Reply:* And he wants TC spend 0?
*Thread Reply:* For July yes. It’s because they try to sign them to other non-TC contracts, and then they wind up getting sign ups, so he believes there should be no media spend against his revenue.
*Thread Reply:* Send dashboard screenshot shot, check TC summary by day page on Looker E for July.
*Thread Reply:* Seattle is beautiful. I really loved it. I will be flying back in the afternoon.
See inbox from me from Marc please. Let me know first.
@Ryan I don't see $7755 spend on mediarawv2 in July. But the total media_spend for Firefoam in July is more than $27k
Good Morning @Ryan I dont see a price on this contract and wondering if it has to do with the notes
This what he said
@Zekarias Haile , they have a credit history in Leeds Pia, you can look up the old price or go look it up in media raw V2 table, it was Pre Nee Criteria
The notes makes me think that this lead might moved to other contracts
Please send this to Cameron
yeah, just saw his message and was looking into it
@Ryan I couldn't find it.. I don't think I have access to it. Sorry
@Zekarias Haile , mind doing this now? Been helping Marc and Rose figure out some stuff.
@Ryan Im working on it. 2025's has been updated. updating 2024's now
Good morning @Ryan I wanted to let you know that I will be on PTO from July 28-31. Sorry about the short notice
@Zekarias Haile , I’ll cover mornings but get with Nicholas to confirm your cover for other stuff.
Dev, ad hoc data studio. And do numbers this weekend and next, I’ll be traveling both weekends. Thank you.
Where you going?
Can you please do tomorrow ? since I will be on the plane
I will talk to Nick about the other stuff
I will talk to Nick about the other stuff
I have to leave at 9am, what time do you fly out?
Flying out midnight I will be getting there 8am
@Ryan I dont see today's signup on the media raw v2
@Zekarias Haile , zero toes from yesterday in the table ?
I see 127 on 0 real time dash but the old one is 134.
That’s not the same thing, do we have rows on the mediarawv2 table for 134 from yesterday?
7 were moved today to non-billable status is why we have 127, we had 128 earlier. You should hunt down like Nick said.
There could’ve been part of the past quarry hung up, we do suck a lot of data out of that post grass with the same us
Mom ease validate media raw and do it touch have same ties, values etc for counts and rev
There were two rows, so it won’t rerun thinking it already ran. I’m going to re-run it again now you need to check the account, the revenue sum, the media has been some app. Send it in the mornings. OK this shouldn’t be difficult to validate before you submit the post and slack that it’s ready.
I have added JV spend on the mediarawv2 and wondering if that is causing the issue?
Please re-sort the Shield legal financials look for enterprise dashboard, I just added three new ones from Marc
@deleted-U07UDKM724B @Zekarias Haile do you got this morning?
Good morning Ryan, yes. I will work on it
@Ryan I will be back Sunday night. But I will be working Friday.
So you got Friday to Sunday numbers, I’m out this weekend.
If you’re able to run the gen_fin log in the morning before I start, that would be great.
Can Edward do it? I’ll be out Saturday and Sunday. Let me know if he can.
*Thread Reply:* Good morning @Ryan, I asked Edward and he said he can do it after 9am PT
@Zekarias Haile , I need a serious update, location of code, names of tables and status on SL Sold Leads and Billable Retainers base financial tables work.
You’ve had 2 months and I’ve only seen those two queries.
After numbers, please send me a detailed post that is comprehensive on it. Thank you.
This is urgent. 🚨
@Zekarias Haile , I need a serious update, location of code, names of tables and status on SL Sold Leads and Billable Retainers base financial tables work.
You’ve had 2 months and I’ve only seen those two queries.
After numbers, please send me a detailed post that is comprehensive on it. Thank you.
This is urgent. 🚨
I need code, I need tables in BQ, I need to know where the code lives, what’s been validated, what columns are missing, what columns you are missing, what’s not done.
Please take serious time to give me all this information before our afternoon status meeting g.
I need code, I need tables in BQ, I need to know where the code lives, what’s been validated, what columns are missing, what columns you are missing, what’s not done.
Please take serious time to give me all this information before our afternoon status meeting g.
@Ryan you are right It took me long time... we are missing spend and spent columns as far as i remember and Nick was trying to help me with the media buyers commission. I will ask the status on that and will let you know.
and I haven't created a table yet... since I didn't have all the columns
Im gonna use the old one but wondering where the big discrepancies came from since all the difference wont be declines
@Zekarias Haile , ask Edward to run TIP fin lambda , you know that.
@Ryan Know we have more in the old dash than dash o
@Zekarias Haile, use old one with 194, when you get declines from Edward, count them and let me, him, James, Nick and I know in a DM how many in Page 0 + Declines totals versus the 194. We can have James Turner investigate.
@Zekarias Haile, actually I just noticed MOST of it seems to be from Hair Relaxer Crump / Dicello with old dash showing 67, Page 0 showing new dash.
Sent a group DM, finish everything for morning financials except the counts and revenue part, head into office and buy some time to get answer.
@Ryan did the old dash not filtering "signed e-sign re-triggered" ?
@Ryan Carter wants access to the dicello/flatirons dash. I want to confirm with you before I give him access
Yes. Tell him it’s the old ones, new ones coming soon.
Good morning @Ryan when you get a chance can you please check if we have dash for Aaron Law Group( I checked and don't see one)
*Thread Reply:* @Ryan friendly reminder
*Thread Reply:* There isn’t a single one for him, just these. Make one please.
@Ryan I'm witnessing that JV FireFoam spend is not showing up when the calcu_perfomance finished (the row is not being created) wondering what is causing that
Did he have a row of 1 or more leads for FireFoam in DoNotTouch?
That's why, add one in mediarawv2 for John V, and use Leadspedia to see which ones are "active".
*Thread Reply:* I see only these two Firefighting contracts as active but they are BLX
*Thread Reply:* Those are all been deactivated, go back and look at the most recent media raw v2 for firefighting foam and put it on for that Customer
Will do but when there is a spend it supposed to create a row in DoNotTouch right?
If from yesterday after, in mediarawv2 only if you already did and finished numbers .
If from yesterday after, in mediarawv2 only if you already did and finished numbers .
Those are all been deactivated, go back and look at the most recent media raw v2 for firefighting foam and put it on for that Customer
To be clear here my question is whenever there is a spend for a vertical a row for that should be created in the DoNotTouch and moved over to mediarawv2 but I dont see firefightingFoam being created in DoNotTouch
Good morning @Ryan since the calc_perf failed now. Can I add the CP's backpage spend while adding the 0 spends to fix the lambda?
I added manually. Brian said it's in there but I don't see posted on slack
Ask him to post in Slack, he just made the channel
Yeah will ask him but the channel he made was for slack signed contract alerts right? I was saying that cp spend was not posted on daily numbers channel
@Ryan TIP cc cost matched at 1,309,000 and shield matched 1,300,000
@Zekarias Haile, NOT YET. Shield side shows $1,278,432. TIP shows $1.309
Good morning @Ryan I see you put Mormon - AWKO leads into Mormon -AWKO 2 and both Mormon -WS and Mormon OSOL -WS leads into Mormon - WS 2 in the weekends. I just saw them when I do the declines and wanted to give you heads up. I'm going to fix them.
Good morning @Ryan once I finished declines I will be posting August's dashboard matches right? then I will be started working when Brittany team finished the reconciliation right? or should I start working on July's? I wanted to confirm with you how you want me to do it
Once Edward is done all the declines and you finished, then I would start that way you’re not reconciling something that you’re gonna decline right after
Then you want to post that August is reconciled with a screenshot when done, then you wanna post when July is done, You can do one post like I did in that channel with two a images of all reconciled and Flatirons reconciled.
But I’ve always just done one month at a time one case type at a time.
ok, thanks. But should I wait Brittany team to do it first? or I can start by myself?
You can start yourself, tell her you’re starting on August and she can verify you when she starts. It’s posting that channel now and let everybody know what’s going on. Thank you.
Mind fixing this on donottouch, I cannot for the next 2 hours.
@Ryan Im gonna run the lambda's and post it. FYI
Good morning @Ryan I dont see daily sign ups on tip public financial table. Can you please run the TIP lambda?
He is not here yet… not sure if he is coming or not