Cameron Rentch (cameron@internetthings.com)
2024-01-11 15:31:14

@Luke Fontaine did we get all of these updates processed? I still see errors in the dash

Luke Fontaine (luke@shield-legal.com)
2024-01-11 15:35:00

@Cameron Rentch yes I updated all of the above. What errors are you seeing?

Cameron Rentch (cameron@internetthings.com)
2024-01-11 16:53:07

Crump Keller NEC, we have NO money left to spend. It all been spent, weve been paid Crump Bell Lejeune, we have NO money left to spend, it has been spent, weve been paid

I zeroed these out in prior above sheets, not refelcted properly in the dash

Cameron Rentch (cameron@internetthings.com)
2024-01-11 16:53:12

@Luke Fontaine ^

Cameron Rentch (cameron@internetthings.com)
2024-01-11 16:53:31

I am going to go through the entire sheet again because there are others

☝️:skin_tone_4: Ryan
Luke Fontaine (luke@shield-legal.com)
2024-01-11 17:10:38

@Cameron Rentch looking at the database on both those campaigns the "orderamount" and "orderamt_paid" is the same, meaning no money left to spend should be reflected on the dashboard. I'll have to check the dashboard to see if there's a connection issue or a filter that's causing this

Cameron Rentch (cameron@internetthings.com)
2024-01-11 18:48:21

@Luke Fontaine that is exactly what I am talking about. It is showing "rev left to spend" that should be "ZERO"

Ryan (ryan@themedialab.agency)
2024-01-11 18:54:35

@Luke Fontaine, that means the Contract Names don't match or the Start Date and End Date are wrong. Camp Lejeune - Crump / Bell is 2022 / 2023, not 2023/2024. @Luke Fontaine, go to SL Master Financials (Page 15) and you'll see Crump / Bell CLJ is from November 30th to to Dec 18, 2023.

Ryan (ryan@themedialab.agency)
2024-01-11 18:55:05

@Luke Fontaine

image.png
Ryan (ryan@themedialab.agency)
2024-01-11 19:02:00

*Thread Reply:* So Luke, look Revenue column and when you see on some past date that we spent "597,500" or close to it, that would be the start date to match Cameron's 597,500 revenue spent to be zero. Make sense @Luke Fontaine?

@Cameron Rentch

Ryan (ryan@themedialab.agency)
2024-01-11 18:56:13

Page 16 I mean

Cameron Rentch (cameron@internetthings.com)
2024-01-11 18:57:26

that doesnt make sense, I think for some reason there is a disconnect. Most of the changes I made were related to money which should have removed 90% of anything in the "rev left to spend" category

Ryan (ryan@themedialab.agency)
2024-01-11 18:57:27

@Luke Fontaine, Crump Keller NEC is 10/4/2022 to 12/18/2023,

Ryan (ryan@themedialab.agency)
2024-01-11 18:59:22

Cameron, if the "Revenue Spent is blank" and we "have fulfilled the order" only 3 things can be wrong.

• Contract Name in dash is not matching Leadspedia Contract name VERBATIM • Dates are wrong on Order Start and End Date If Revenue Left To Spend is wrong, that means we mjust update the "Order Amount Paid" must be updated.

Ryan (ryan@themedialab.agency)
2024-01-11 19:00:53

@Cameron Rentch, so for Crump / Bell, give us the order dates for what should equal the "597,500" Order Amount Paid. FYI @Luke Fontaine, it has to be a later date

Ryan (ryan@themedialab.agency)
2024-01-11 19:05:16

@Cameron Rentch, stepping out for dinner, I'll review and see if I can "deciper the order dates" from the spend of $597,500 for Crump/Bell CLJ and NEC Crump / Keller.

Ryan (ryan@themedialab.agency)
2024-01-11 21:08:07

@Cameron Rentch So from 2/17/23 to 5/26/23 is $601,500 in revenue for Camp Lejeune Bell, I would have to manually move e-signs around to get an exact $597,500 exactly for that order. Want me to do that?

image.png
Ryan (ryan@themedialab.agency)
2024-01-11 21:15:27

@Cameron Rentch, and for NEC Crump / Keller, 2/10/23 to 5/10/23 is $147,000 in revenue spent, so I can update the order dates and move signs up to get $144,000 on those dates. Want me to do that too?

Ryan (ryan@themedialab.agency)
2024-01-11 21:15:30
image.png
Cameron Rentch (cameron@internetthings.com)
2024-01-12 10:07:19

@Ryan no, add total revenue recd to match and zero it all out.

Ryan (ryan@themedialab.agency)
2024-01-12 10:13:46

@Cameron Rentch, updated, any others you need order dates derived?

image.png
Cameron Rentch (cameron@internetthings.com)
2024-01-12 11:05:43

going through entire database/dash will provide updates this afternoon

Ryan (ryan@themedialab.agency)
2024-01-12 11:05:53

Ok.

Ryan (ryan@themedialab.agency)
2024-01-15 09:07:39

*Thread Reply:* @Luke Fontaine, these updates are completed.

Cameron Rentch (cameron@internetthings.com)
2024-01-13 11:03:12

@Luke Fontaine @Ryan changes above^ Also, paid vs spent is missing Asbestos Bailey Glasser (just BG only). I dont see it anywhere? please add

❓ Ryan
Ryan (ryan@themedialab.agency)
2024-01-15 09:26:48

*Thread Reply:* @Cameron Rentch, is it not order 68, "Asbestos - Bailey Glasser (TIP) - Contract"?

Ryan (ryan@themedialab.agency)
2024-01-15 12:57:07

*Thread Reply:* @Cameron Rentch, is it not order 68, "Asbestos - Bailey Glasser (TIP) - Contract"?

❓ Ryan
Cameron Rentch (cameron@internetthings.com)
2024-01-13 11:16:06

order to add: payments rec'd 1-11-24 Doug Rochen = 500K BG AFFF (existing campaign new payment) Bailey Glasser Asbestos = 520,234 (existing campaign payment) Aiken Farrel Kroloff/Napoli AFFF/PFAS= 250K each, start date Dec 10 Meirowitz & Wasserberg/BG AFFF = 300K, start date Jan 10, 2800/per Bowersox/BG AFFF = 100K, existing campaign, new payment, 2800/per Ghozland/BG AFFF = 300K, existing campaign, new payment, 2800/per

@Mark Maniora if there is an existing contract in leadspedia add budget, otherwise create new leadspedia contract

👍:skin_tone_2: Mark Maniora
✅ Luke Fontaine
Ryan (ryan@themedialab.agency)
2024-01-15 09:25:04

*Thread Reply:* @Luke Fontaine, you do these and confirm with @Mark Maniora the ones he submits to load and add payments.

FYI @Cameron Rentch

👍:skin_tone_2: Mark Maniora
Mark Maniora (markmaniora@shield-legal.com)
2024-01-15 09:46:49

*Thread Reply:* @Luke Fontaine I am finished adding any orders that were needed in the dash. Let me know if you need anything

👏:skin_tone_4: Ryan
Luke Fontaine (luke@shield-legal.com)
2024-01-15 12:02:28

*Thread Reply:* @Mark Maniora is Aiken ready? I see the request but there's no LP contract name, and I can't find it in LP

Mark Maniora (markmaniora@shield-legal.com)
2024-01-15 12:03:19

*Thread Reply:* Search “AFK” in LeadsPedia

Mark Maniora (markmaniora@shield-legal.com)
2024-01-15 12:03:33

*Thread Reply:* It was too long so we had to shorten it

🙏:skin_tone_4: Ryan
Luke Fontaine (luke@shield-legal.com)
2024-01-15 12:26:33

*Thread Reply:* @Ryan completed

Cameron Rentch (cameron@internetthings.com)
2024-01-13 11:16:14

@Luke Fontaine @Ryan ^

Ryan (ryan@themedialab.agency)
2024-01-15 09:22:11

*Thread Reply:* @Cameron Rentch, order #s 160 & 161 are split orders (FB & TT), so I made an estimate that you wanted the "entire order" zeroed out...so for TT with 7950 revenue spent, I deducted that amount from 375,000 and put 367,050 on 160. See dark green rows on your sheet to confirm if I did this correct.

FYI @Luke Fontaine

Ryan (ryan@themedialab.agency)
2024-01-15 12:57:45

*Thread Reply:* @Cameron Rentch, order #s 160 & 161 are split orders (FB & TT), so I made an estimate that you wanted the "entire order" zeroed out...so for TT with 7950 revenue spent, I deducted that amount from 375,000 and put 367,050 on 160. See dark green rows on your sheet to confirm if I did this correct.

FYI @Luke Fontaine

✅ Ryan
Cameron Rentch (cameron@internetthings.com)
2024-01-15 13:12:01

*Thread Reply:* @Ryan incorrect. We added budget and should have balance of rev left to spend on Ghozland BG AFFF. That is why I added UPDATE. I increased the order amount in column L

Ryan (ryan@themedialab.agency)
2024-01-15 13:12:26

*Thread Reply:* Got it

Ryan (ryan@themedialab.agency)
2024-01-15 13:12:31

*Thread Reply:* Will revert

Ryan (ryan@themedialab.agency)
2024-01-15 13:16:01

*Thread Reply:* Updated

Cameron Rentch (cameron@internetthings.com)
2024-01-13 11:32:11

more updates

Cameron Rentch (cameron@internetthings.com)
2024-01-14 11:30:26

@Ryan can you get the above adjustments are completed today please. I will look over the dash after updated to make sure it is all accurate

Ryan (ryan@themedialab.agency)
2024-01-14 11:50:17

@Cameron Rentch , Luke and I are out and on the go but we’ll chip away and get it all done by tomorrow.

Cameron Rentch (cameron@internetthings.com)
2024-01-14 19:38:18

Please get done by noon tmrw @Ryan this is not a lot of work.

Ryan (ryan@themedialab.agency)
2024-01-15 13:08:13

@Cameron Rentch, the above are done except the two questions I have above with the ❓

Ryan (ryan@themedialab.agency)
2024-01-15 13:18:01

@Cameron Rentch, this is done if you want to double check the dash.

FYI @Luke Fontaine @Mark Maniora

👍:skin_tone_2: Mark Maniora
Cameron Rentch (cameron@internetthings.com)
2024-01-15 13:38:19

standby

Cameron Rentch (cameron@internetthings.com)
2024-01-15 13:45:15

@Ryan still a bunch of errors

  1. anything in column "revenue left to spend" that is a NEGATIVE, need to be added to the column "Revenue Spent Not Paid". Everytime I give you guys updates this is happening.
  2. Examples attached https://docs.google.com/spreadsheets/d/1gLz2klWj_OpsUUhZTDwnIS0qETzw4URq3ROJaM_Qxmo/edit?usp=sharing
  3. Please go through every single line item, active, paused, completed and ensure this is done
Shield Legal - Financial - Master DB v4.0_M - Shield Orders_Table
Cameron Rentch (cameron@internetthings.com)
2024-01-15 13:46:36

line 183 is NOT A BAILEY GLASSER campaign it is Crump/TPBM

✅ Ryan
Cameron Rentch (cameron@internetthings.com)
2024-01-15 13:47:21

this thing is a mess and we continue to have riddled with mistakes

☝️:skin_tone_4: Ryan
Cameron Rentch (cameron@internetthings.com)
2024-01-15 13:48:52

163 STILL NOT CALCULATING RIGHT

✅ Ryan
Cameron Rentch (cameron@internetthings.com)
2024-01-15 13:49:13

Yuri, Mormon. We have spent budget and signed 7 contracts

✅ Ryan
Cameron Rentch (cameron@internetthings.com)
2024-01-15 13:50:27

181 walker we have been paid and FILLED ORDER. Update as requested. Showing we still have to spend $, not accurate

✅ Ryan
Cameron Rentch (cameron@internetthings.com)
2024-01-15 13:53:07

160 and 161 are WRONG. Someone double of triple entered their amounts

✅ Ryan
Cameron Rentch (cameron@internetthings.com)
2024-01-15 13:53:41

159 budget is done, we have NOTHING left to spend

✅ Ryan
Cameron Rentch (cameron@internetthings.com)
2024-01-15 13:54:55

134 budget is complete NOTHING left to spend

✅ Ryan
Cameron Rentch (cameron@internetthings.com)
2024-01-15 13:55:38

131 budget complete NOTHING left to spend

✅ Ryan
Cameron Rentch (cameron@internetthings.com)
2024-01-15 13:57:08

91 remaining budget is 500K (last payment 500k just recevied)

✅ Ryan
Cameron Rentch (cameron@internetthings.com)
2024-01-15 13:57:57

68 budget is complete NOTHING left to spend

Ryan (ryan@themedialab.agency)
2024-01-15 14:01:10

@Luke Fontaine, I will review these first

Ryan (ryan@themedialab.agency)
2024-01-15 14:04:33

@Cameron Rentch, starting one at a time, first with 183...

line 183 is NOT A BAILEY GLASSER campaign it is Crump/TPBM advertiser changed to Crump. FYI @Mark Maniora and @Luke Fontaine, watch form submissions and INSERT statements please.

Ryan (ryan@themedialab.agency)
2024-01-15 14:10:34

163 is fixed @Cameron Rentch, @Luke Fontaine, the Contract Name in the Orders table did not match the MediaRawv2 / Leadspedia Contract name. (Updated using 'Yuri/Susen Law Group/Slater (TIP)' in the Customer name.

Ryan (ryan@themedialab.agency)
2024-01-15 14:18:37

181 Walker MVA fixed, dates were wrong @Cameron Rentch, if we got paid more than $63499, let me know.

Ryan (ryan@themedialab.agency)
2024-01-15 14:44:43

@Cameron Rentch 160 & 161 fixed, we had been given a payment in the #C061BREN405, then you gave us another payment, we were not aware they were duplicates.

Ryan (ryan@themedialab.agency)
2024-01-15 14:45:22

@Cameron Rentch 159 budget to be zero, we spent $29,000 so order target amount and order payment amount updated to $29,000 to zero out

Ryan (ryan@themedialab.agency)
2024-01-15 14:48:54

@Cameron Rentch 134 is done, since we spent 68000, updated order budget and order budget paid to 68000 to zero out

Ryan (ryan@themedialab.agency)
2024-01-15 14:59:31

@Cameron Rentch, 131 updated. @Luke Fontaine @Mark Maniora, LP contract name did not match in orders table.

👍:skin_tone_2: Mark Maniora
Ryan (ryan@themedialab.agency)
2024-01-15 15:10:26

@Cameron Rentch, 91 updated to show $500k left to spend and that is the final payment.

Ryan (ryan@themedialab.agency)
2024-01-15 15:24:37

@Cameron Rentch, 68 is updated. These udpates are done.

The errors above were of three types:

• Order Dates not matching, so revenue spent, leads and contracts signed incomplete • Contract Name in Leadspedia not matching what entered into Shield Orders Table • Order Target Amount and the Order Amount Paid were a part of some type of "Change Order" where the original order was paused, then reallocated / moved / etc. Proposed solution If you want to meet and map out how orders come in we can solve it.

One idea might be best to have @Debbie submit the orders into the google sheet, than if existing order in Leadspedia already, have @Mark Maniora confirm in the #C061BREN405 channel. If not, then confirm new order and then @Luke Fontaine and I will give @Mark Maniora the Leadspedia Contract Name to use, he gives us the Leadspedia Contract ID or IDs, and then we can make sure the dashboard is more stable and accurate. Making the people that send the invoices out for sales the "subject matter expert" and we can go from there...

Cameron Rentch (cameron@internetthings.com)
2024-01-16 09:35:34

@Ryan attached is an updated sheet that shows remaining budgets for law firm campaigns. Let's make sure these "match" accordingly!

https://docs.google.com/spreadsheets/d/11OHNCuaVekJwAmvVwqVruposIPpbw72n/edit#gid=814876070

Open Campaigns 1.15.24.xlsx
Ryan (ryan@themedialab.agency)
2024-01-17 11:16:20

*Thread Reply:* @Cameron Rentch @Debbie, @Luke Fontaine @Mark Maniora, this is updated according to the sheet.

@Cameron Rentch, please note the two orders in yellow rows, please double check those, they are "split orders" and have 2 rows each in the Order Dashboard, want to make sure their subotals match your sheet the way you intended.

Remember to use the "Split Orders" drop down box to select 2 rows for the the split orders.

image.png
👍:skin_tone_2: Mark Maniora
Ryan (ryan@themedialab.agency)
2024-01-16 09:49:20

@Cameron Rentch, to confirm before we dive into this and update order dashboards, you want us to use Column C "Budget" and subtract Column D "Budget Remain" to get "Budget Amount Paid", and update the Dashboard column "Order Amount Paid" with those values?

Cameron Rentch (cameron@internetthings.com)
2024-01-16 11:22:02

no @Ryan I want to to locate the campaign in system and match the "rev left to spend" to column D (remaining budget). I dont care how you arrive there.

Ryan (ryan@themedialab.agency)
2024-01-16 11:56:46

@Cameron Rentch, then I either have to update the "Order Amount Paid" or I'll have to change the daily revenue per contract on those orders since the "revenue is a daily metric we calculate each day when we run morning financials".

I'll do a few and see what this entails, this isn't as easy as it seems even though the "math is" easy.

Ryan (ryan@themedialab.agency)
2024-01-16 12:22:02

@Cameron Rentch, ok, using Chow ACTS (order id 45), to get to Revenue Left To Spend of $112,333

  1. Updated Order Amount to $350,000
  2. Updated Order Amount Paid to $217,333
  3. Daily Revenue summed for order is $105,000 through 1/14/24
  4. Subtract revenue from $105,000 from $217,333 order amount paid = $112,333 <-YOUR DESIRED VALUE FOR REVENUE LEFT TO SPEND. Does this work or do you want me to make Order Amount Paid $350,000 and increase the revenue per contract / price per contract to get you the $112,333 left to spend?
Cameron Rentch (cameron@internetthings.com)
2024-01-16 12:29:37

@Ryan nothing changes with existing rev per contract. Just remaining funds(rev left to spend)

Ryan (ryan@themedialab.agency)
2024-01-16 12:31:08

@Cameron Rentch Ok, then the way I did it is with Order Amt Paid changing based on the Revenue Spent will get your values accurate. LIke I did on Order 45 Chow ACTS-only.

Cameron Rentch (cameron@internetthings.com)
2024-01-17 14:14:12

@Ryan is the pad v spent fully updated now?

Ryan (ryan@themedialab.agency)
2024-01-17 14:33:26

Yes!

Ryan (ryan@themedialab.agency)
2024-01-17 14:33:45

Except 2 questions in yellow on your Andy sheet: https://docs.google.com/spreadsheets/d/11OHNCuaVekJwAmvVwqVruposIPpbw72n/edit#gid=814876070

Cameron Rentch (cameron@internetthings.com)
2024-01-17 14:35:31

@Ryan what are the questions? Note the AFK/Napoli is 250K PFAS 250K AFFF Both have been paid As long as the dates are accurate for campaign start then the numbers will be accurate

What is Sbaiti question>?

Ryan (ryan@themedialab.agency)
2024-01-17 15:23:24

I got it answered already for Sbaiti from Andy. I'll update AFK/Nap

Ryan (ryan@themedialab.agency)
2024-01-17 15:25:27

Updated AFK, this is done @Cameron Rentch

Cameron Rentch (cameron@internetthings.com)
2024-02-24 14:02:38

@Ryan @Luke Fontaine important updates on paid vs spent. Note on this one we are missing huge order for Flatirons Dicello for paraquat

Ryan (ryan@themedialab.agency)
2024-02-24 14:27:13

@Luke Fontaine , after morning financials, after flatirons reconcile, we’ll update. @Cameron Rentch , copper CRM is going to replace this manual dashboard soon.

Cameron Rentch (cameron@internetthings.com)
2024-02-24 17:58:34

@Ryan got it. Marc is going to want in the dash, as am I.

Ryan (ryan@themedialab.agency)
2024-02-24 17:58:48

You bet.

Cameron Rentch (cameron@internetthings.com)
2024-02-25 13:28:55

@Ryan @Luke Fontaine @Mark Maniora note these was the entire "DELETE" tab with ALL orders. Fully updated. FYI only took me about an hour to update so lets get this all updated tmrw asap. Please make sure you notice status changes and some duplicate entires and transfers https://docs.google.com/spreadsheets/d/1xDzwi-wU1vxuvCHAbBl7OD0RYaUyOx-MOyV7zM1mOdg/edit?usp=sharing

Shield Legal - Financial - Master DB v4.0_M - Paid Versus Spent_Table
✅ Luke Fontaine
Ryan (ryan@themedialab.agency)
2024-02-25 13:49:45

@Luke Fontaine, after morning fins, flatirons please do these 8 sheets plus the updates to the DELETEs in the ldp_contracts tables. Plus once complete UPDATE all records that equal "DELETE" and make them read "Archive" instead. This will mirror Copper. Work with @Mark Maniora for any missing LP contracts.

@Cameron Rentch, after this is complete I will be using this orders dashboard table to help Andy get the Advertisers (Companies), the Orders (Opportunities) into Copper and setup to be in the "Orders Pipeline" so we can have Copper 100% configured and then have @Debbie and team check them. From there, we'll see if we can import Copper into our AWS database and connect the Orders to our MediaRawv2 (revenue, spend, leads, contracts, etc.).

Want this to be the last "development on the old system" as its taking time from building the new Google Cloud system.

🆗 Luke Fontaine
Ryan (ryan@themedialab.agency)
2024-02-25 13:50:38

@Luke Fontaine, as you complete them, add the ✅ and "un-pin" them. Ty.