Email File Management

Duplicate Leads in Salesforce? It’s not just messy — it’s dangerous! As a Salesforce Architect, one of the most underestimated pain points I see across orgs is poor duplicate management. It silently: ⚠️ Breaks automation 📉 Skews reporting ❌ Slows down sales 🛑 Violates GDPR rules Last week, I worked on optimizing a lead flow where 80,000+ leads were sitting unvalidated — many of them potential contacts, already in the system. Here’s how we tackled it: 📌 Step 1: Smart Duplicate Check → We built a flow that compares incoming Leads to Contacts & Leads using fuzzy logic (Email, Phone, Name, etc.) 📌 Step 2: Decision Branch → If a duplicate is found, we flag it or merge it automatically (using Apex + native Merge tools). → If not, we convert the Lead cleanly to a Contact, ensuring no clutter. 📌 Step 3: Automation with Guardrails → All this runs inside a scalable Salesforce Flow — enriched with Apex where needed — and leaves a full audit trail. 💡 Architecture isn’t just about building — it’s about protecting your data layer. If you're still relying on name-only matching or manual checks, you're setting your CRM up for failure. Let’s talk if you want a duplicate management framework that scales 👇 #Salesforce #CRMStrategy #DuplicateCheck #SalesforceFlow #Architect #RevOps #DataIntegrity

𝐒𝐐𝐋 𝐈𝐧𝐭𝐞𝐫𝐯𝐢𝐞𝐰 𝐒𝐞𝐫𝐢𝐞𝐬 – 𝐃𝐚𝐲 𝟏𝟐⁣ ⁣ ⁣ 𝐓𝐚𝐬𝐤:⁣ Write a SQL query to identify email addresses that appear more than once in the customers table.⁣ ⁣ This type of question is commonly asked in interviews at companies like PwC, KPMG, and Infosys, especially when the role involves data quality audits, reporting, or data migration tasks. The focus here is on identifying duplicates—an essential skill in data cleaning and preprocessing workflows.⁣ ⁣ ⁣ 𝐇𝐨𝐰 𝐭𝐨 𝐟𝐫𝐚𝐦𝐞 𝐢𝐭:⁣ Start by grouping the table by the email column. Then apply the COUNT(*) function to count how many times each email appears in the dataset. To find duplicates, use a HAVING clause to return only those email groups where the count is greater than one.⁣ ⁣ This logic helps detect data integrity issues such as multiple records with the same email due to failed validations or duplicate imports.⁣ ⁣ ⁣ 𝐂𝐨𝐧𝐜𝐞𝐩𝐭𝐬 𝐮𝐬𝐞𝐝:⁣ 1. 𝐆𝐑𝐎𝐔𝐏 𝐁𝐘 𝐂𝐥𝐚𝐮𝐬𝐞:⁣ Groups records by email so that aggregation functions can be used to count how many times each unique email appears.⁣ ⁣ 2. 𝐂𝐎𝐔𝐍𝐓(*) 𝐅𝐮𝐧𝐜𝐭𝐢𝐨𝐧:⁣ Counts the total number of records for each grouped email value. If an email appears more than once, its count will be greater than one.⁣ ⁣ 3. 𝐀𝐥𝐢𝐚𝐬𝐢𝐧𝐠 𝐀𝐠𝐠𝐫𝐞𝐠𝐚𝐭𝐞𝐬:⁣ The result of COUNT(*) is aliased as occurrences for better readability and downstream usage in reporting or debugging queries.⁣ ⁣ 4. 𝐇𝐀𝐕𝐈𝐍𝐆 𝐂𝐥𝐚𝐮𝐬𝐞 𝐭𝐨 𝐅𝐢𝐥𝐭𝐞𝐫 𝐀𝐠𝐠𝐫𝐞𝐠𝐚𝐭𝐞𝐬:⁣ Since WHERE cannot be used with aggregated values, the HAVING clause is applied to filter the grouped data. It returns only those emails with more than one record.⁣ ⁣ 5. 𝐃𝐚𝐭𝐚 𝐐𝐮𝐚𝐥𝐢𝐭𝐲 𝐑𝐞𝐥𝐞𝐯𝐚𝐧𝐜𝐞:⁣ Identifying duplicates is critical in ETL pipelines, CRM data syncs, and compliance checks. Interviewers expect you to write efficient queries that surface these issues clearly.⁣ ⁣ 6. 𝐀𝐝𝐯𝐚𝐧𝐜𝐞𝐝 𝐅𝐨𝐥𝐥𝐨𝐰-𝐔𝐩 𝐒𝐭𝐫𝐚𝐭𝐞𝐠𝐢𝐞𝐬:⁣ Once duplicates are identified, interviewers may ask how to remove or resolve them. You can suggest using ROW_NUMBER() to isolate the latest record, or DISTINCT to retain unique values based on business rules.⁣ ⁣ 7. 𝐏𝐫𝐚𝐜𝐭𝐢𝐜𝐚𝐥 𝐀𝐩𝐩𝐥𝐢𝐜𝐚𝐭𝐢𝐨𝐧:⁣ This pattern is useful in fraud detection, contact deduplication, lead cleanup, or preparing customer data for machine learning models.⁣ ⁣ ⁣ 𝐖𝐡𝐲 𝐭𝐡𝐢𝐬 𝐢𝐬 𝐚𝐬𝐤𝐞𝐝:⁣ This question tests your understanding of SQL’s grouping and filtering logic, and how to detect and report anomalies in data. Clean data is the foundation of every analytics project, and the ability to identify duplicates is a must-have skill for any analyst or engineer. Interviewers look for candidates who can think practically and solve messy data problems with precision.⁣ ⁣ ⁣ ⁣ #SQL #SQLInterview #PwCInterview #DataCleaning #HAVINGClause #DataAnalytics #SQLQuery #LearnSQL #DataQuality #BusinessIntelligence #InterviewPreparation #DataEngineering #AnalyticsJobs #SQLTips

 PySpark scenario-based interview questions & answer: 1) Deduplicate and normalize messy user data (Beginner): #Scenario: You receive a user signup CSV with messy names, mixed-case emails, and multiple signups per email. Keep the most recent signup per email and normalize fields. #Purpose: Data hygiene — prevents duplicate users and inconsistent keys that break joins and metrics. Question & data (sample): #Schema: user_id: int, full_name: string, email: string, signup_ts: string, country: string #Samplerows: (1, " john DOE ", "JOHN@EX.COM ", "2025-11-20 10:00", "US") (2, "John Doe", "john@ex.com", "2025-11-21 09:00", "US") (3, "alice", "alice@mail.com", "11/20/2025 12:00", "IN") #Approach: Read CSV with header. Trim & normalize (full_name → title case, email → lower-case). Parse multiple timestamp formats to timestamp. Filter obviously invalid emails (basic regex). Deduplicate by email, keeping row with latest signup_ts. #Explanation: Lowercasing emails and trimming prevents false-unique keys. Multiple to_timestamp attempts handle variable input formats. Window + row_number() deterministically selects the most recent record per email. Caveat: Basic regex filters obvious invalid addresses but not full RFC validation. Karthik K. #PySpark #DataCleaning #ETL #DataEngineering #ApacheSpark code:

SQL interview question: How to Identify and Delete Duplicates (with Code) Handling duplicate records in SQL is a common task, especially when dealing with raw or legacy datasets, and interviewers love to ask this. Here are 3 reliable methods to identify and delete duplicates using SQL: 1. 𝗨𝘀𝗶𝗻𝗴 𝗥𝗢𝗪_𝗡𝗨𝗠𝗕𝗘𝗥() (𝗕𝗲𝘀𝘁 𝗳𝗼𝗿 𝗰𝗼𝗺𝗽𝗹𝗲𝘅 𝗱𝘂𝗽𝗹𝗶𝗰𝗮𝘁𝗲 𝗰𝗼𝗻𝗱𝗶𝘁𝗶𝗼𝗻𝘀) WITH CTE AS (  SELECT *,       ROW_NUMBER() OVER (PARTITION BY name, email ORDER BY id) AS rn  FROM users ) DELETE FROM users WHERE id IN (  SELECT id FROM CTE WHERE rn > 1 ); ✅ Why this works: It keeps the first occurrence (based on id) and removes the rest. Super handy when deduplication depends on multiple columns. 2. 𝗨𝘀𝗶𝗻𝗴 𝗚𝗥𝗢𝗨𝗣 𝗕𝗬 𝘄𝗶𝘁𝗵 𝗠𝗜𝗡() 𝗼𝗿 𝗠𝗔𝗫() DELETE FROM users WHERE id NOT IN (  SELECT MIN(id)  FROM users  GROUP BY name, email ); ✅ Why this works: Simple datasets with clear duplicate keys. Just keeps the record with the smallest id. 3. 𝗨𝘀𝗶𝗻𝗴 𝗦𝗘𝗟𝗙 𝗝𝗢𝗜𝗡 DELETE u1 FROM users u1 JOIN users u2  ON u1.name = u2.name AND u1.email = u2.email WHERE u1.id > u2.id; ✅ Why this works: No CTE required, straightforward and readable. How would you answer that? Comment that down! ------------------------------------------------------------------ #SQL #interviewquestions

Every advanced SQL interview question — YoY growth, top-N per group, running totals, percent contribution — has the same answer. 𝗪𝗶𝗻𝗱𝗼𝘄 𝗳𝘂𝗻𝗰𝘁𝗶𝗼𝗻𝘀. Here are the 5 patterns that cover 90% of real analytics work — no theory, just the queries you'll actually use. 𝗣𝗮𝘁𝘁𝗲𝗿𝗻 𝟭: 𝗣𝗲𝗿𝗶𝗼𝗱-𝗼𝘃𝗲𝗿-𝗽𝗲𝗿𝗶𝗼𝗱 𝗰𝗼𝗺𝗽𝗮𝗿𝗶𝘀𝗼𝗻  • revenue - LAG(revenue) OVER (ORDER BY month)  • Month-over-month, week-over-week, YoY — one line. No self-joins. 𝗣𝗮𝘁𝘁𝗲𝗿𝗻 𝟮: 𝗧𝗼𝗽-𝗡 𝗽𝗲𝗿 𝗴𝗿𝗼𝘂𝗽  • ROW_NUMBER() OVER (PARTITION BY region ORDER BY sales DESC)  • Filter WHERE rn <= 3 in a CTE. Top 3 products per region, top 5 customers per segment — same pattern every time. 𝗣𝗮𝘁𝘁𝗲𝗿𝗻 𝟯: 𝗥𝘂𝗻𝗻𝗶𝗻𝗴 𝘁𝗼𝘁𝗮𝗹𝘀 & 𝗺𝗼𝘃𝗶𝗻𝗴 𝗮𝘃𝗲𝗿𝗮𝗴𝗲𝘀  • SUM(revenue) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING)  • AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)  • Cumulative revenue and 7-day rolling average. No temp tables, no loops. 𝗣𝗮𝘁𝘁𝗲𝗿𝗻 𝟰: 𝗣𝗲𝗿𝗰𝗲𝗻𝘁𝗮𝗴𝗲 𝗼𝗳 𝘁𝗼𝘁𝗮𝗹  • revenue * 100.0 / SUM(revenue) OVER ()  • Each row's share of overall revenue. OVER() with empty parentheses = entire result set as one partition. 𝗣𝗮𝘁𝘁𝗲𝗿𝗻 𝟱: 𝗗𝗲𝗱𝘂𝗽𝗹𝗶𝗰𝗮𝘁𝗶𝗼𝗻  • ROW_NUMBER() OVER (PARTITION BY email ORDER BY updated_at DESC)  • Keep rn = 1. Cleanest way to deduplicate without DELETE or DISTINCT ON. 𝗧𝗵𝗿𝗲𝗲 𝘁𝗵𝗶𝗻𝗴𝘀 𝘁𝗼 𝗿𝗲𝗺𝗲𝗺𝗯𝗲𝗿: → Window functions run after WHERE and GROUP BY — you can't filter on them directly, wrap in a CTE → LAST_VALUE with default frame only sees up to the current row — always set ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING → Every major platform supports these — BigQuery, Snowflake, Postgres, Databricks, Redshift If you're writing self-joins or correlated subqueries for any of the above, you're writing 5x the SQL you need to. Learn these 5 patterns. They'll cover most of what analytics actually asks for. #SQL #DataAnalytics #WindowFunctions #DataEngineering #Analytics

I EA'd for a Fortune 500 CEO for a week. Here's what I learned: Email isn't communication. It's a task list pretending to be communication. What a morning looked like: 9:47am - "Can you send me your availability?" Opens calendar. Checks timezones. Types out three slots. 8 minutes. 10:23am - "None of those work, what about next week?" Does it again. 6 minutes. 11:15am - "Remember that budget conversation?" Searches 400 emails. Tries five different keywords. 12 minutes. Four hours of my day doing tasks that emails contained. The actual reading? Five minutes total. Every tool treats AI like a feature: autocomplete, summaries, drafts. Nobody recognizes that email is just tasks with extra steps. By day three, I kept thinking in commands that don't exist: "Just give them my availability" "Handle this scheduling thread" "Find that conversation" "Write the polite no" Not "help me write faster." Just do it. The same way developers use command lines instead of clicking menus. We have AI that can pass the bar exam. And I spent four hours manually typing out calendar availability. What if you could type /give-availability and it just worked? What if /schedule-this-meeting handled the entire back-and-forth? I spent 20+ hours this week manually doing things a computer should handle. It felt absurd. Email isn't broken because we read too much or write too slow. Email is broken because it's a to-do list that makes you do everything manually. And nobody's fixing the actual problem. Working on this. DM me if you think email is secretly just tasks.

Spoke with a VP Sales last Tuesday whose reps ignored 70% of inbound leads because they "weren't worth the time." I see this across hundreds of teams. Here's the real issue with how most companies handle inbound: The problem isn’t effort or lead volume. It’s that most teams are operating on broken CRM data and AI just amplifies the damage. 1. The CRM Is The Bottleneck Their CRM was routing inbound to dead emails, duplicates, and people who left 6+ months ago. Reps would see a "warm lead, reach out, and it bounces. After 3-4 failed attempts, they stopped believing inbound was real. This is where Common Room's DataAgent, powered by Person360, changes the game, a unified identity layer that continuously resolves one real person across emails, roles, and duplicates. Clean data foundation = inbound that actually works. 2. Reps Blame Lead Quality When It's Data Quality "Marketing sends us garbage" is what most reps say. Reality: the leads might be fine, the data is broken. When contact info is wrong, even perfect-fit buyers look like time-wasters. Fix the data layer first, especially if AI is involved then evaluate lead quality. 3. Speed-To-Lead Dies In Routing Hell Inbound gets stuck bouncing between wrong owners, wrong territories, duplicate records. By the time it hits the right rep, 48 hours have passed. Speed only matters if you're reaching the right person with trustworthy data underneath. 4. No Follow-Up Structure Rep reaches out once, no response, moves on. No one owns nurture on cold inbound. These people raised their hand, that intent doesn't disappear. Best teams have a system for re-engaging over 30, 60 and 90 days. 70% of inbound was ignored. Reps stopped trusting the system. Pipeline was dying before it ever got worked. The issue isn’t your reps or lead quality. It’s a data foundation that quietly decays over time. Common Room’s DataAgent gives teams continuous visibility into outdated contacts, duplicate contacts, and duplicate accounts, so inbound workflows don’t break as data changes. Fix the foundation first. Learn more here → https://lnkd.in/g6CDDhyT Common Room gives you a unified identity layer, one person, verified contacts, no duplicates. Fix the foundation first. Everything else gets easier after that. #sales #inbound #revops #gtm #crm #commonroom

I don't think you understand. We're living through a time where, with 3-4 hours and $20/month, you can build internal tools that save you thousands. Six months ago, we were burning hours every week trying to manage our email infrastructure across 200+ clients. Our team would manually check which inboxes were disconnected, hunt down domains that weren't assigned to clients, and find infrastructure we were still paying for from churned clients. It was eating up 10-15 hours per week of manual work, and we were constantly missing things that cost us money. - Disconnected inboxes meant campaigns weren't sending. - Unassigned domains meant confused routing. - Clients who churned months ago still had infrastructure running. So I built a dashboard with Lovable that handles all of this automatically. Now it: 1. Flags inboxes with disconnects or disabled warmup so we catch issues before campaigns break 2. Identifies unassigned inboxes which helps with internal routing and prevents confusion 3. Shows us domains canceled at the provider level but still live in Smartlead so we can clean them up 4. Finds active domains in our infrastructure that aren't in Smartlead so we can reupload them properly 5. Surfaces churned clients who still have infrastructure running so we can shut it off and stop bleeding money This thing saves our team 10+ hours every week and probably $2,000+ per month in wasted infrastructure costs. All for the price of a couple Netflix subscriptions. The agencies that survive the next few years are the ones building custom solutions for their specific problems instead of trying to make generic tools work. Stop accepting manual processes that cost you time and money. Build something that solves your exact problem.

Here's how one missed invoice almost killed the entire business of one of our customers... A few months ago, an invoice got lost in an inbox. They had an inconsistent workflow setup, no real tracking, and the bill genuinely just disappeared. This wasn’t some nice-to-have tool. This vendor powered the core functionality of their entire product. When it went dark, their whole business went dark. Customers couldn’t use the product. The team had no access. Operations were fully dead in the water. They had to scramble to figure out what happened, pay months of overdue invoices, eat heavy penalties, and beg the vendor to restore service before customers churned. All because one bill slipped through the cracks. This is what broken AP really costs. - Bills vanish into inboxes - Approvals get lost - Due dates slide - Categories end up wrong - Problems quietly compound Yeah, paying your bills is annoying. But paying for preventable mistakes is worse. If your AP workflow still lives inside email, DMs and scattered systems, fix it before it fixes you. Learn more here: https://lnkd.in/eGjz_q83

𝐖𝐡𝐲 𝐄𝐦𝐚𝐢𝐥 𝐁𝐫𝐞𝐚𝐤𝐬 𝐄𝐱𝐞𝐜𝐮𝐭𝐢𝐨𝐧 Many managers try to use email as a workflow tool. A problem arises or a decision is required, a mail is sent, people are copied, and the expectation is that action will follow. Many times, it doesn’t. This leads to frustration and the feeling that people don’t care. Over time, the sender gives up (𝘸𝘩𝘺 𝘣𝘰𝘵𝘩𝘦𝘳, 𝘸𝘩𝘦𝘯 𝘵𝘩𝘰𝘴𝘦 𝘳𝘦𝘴𝘱𝘰𝘯𝘴𝘪𝘣𝘭𝘦 𝘴𝘦𝘦𝘮 𝘪𝘯𝘥𝘪𝘧𝘧𝘦𝘳𝘦𝘯𝘵). 𝐓𝐡𝐢𝐬 𝐢𝐬 𝐭𝐡𝐞 𝐬𝐞𝐧𝐝𝐞𝐫’𝐬 𝐯𝐢𝐞𝐰𝐩𝐨𝐢𝐧𝐭. 𝐅𝐫𝐨𝐦 𝐭𝐡𝐞 𝐫𝐞𝐜𝐞𝐢𝐯𝐞𝐫’𝐬 𝐬𝐢𝐝𝐞, 𝐢𝐭 𝐢𝐬 𝐚 𝐯𝐞𝐫𝐲 𝐝𝐢𝐟𝐟𝐞𝐫𝐞𝐧𝐭 𝐬𝐭𝐨𝐫𝐲. What they face is : - 𝐏𝐫𝐢𝐨𝐫𝐢𝐭𝐲 𝐦𝐢𝐬𝐚𝐥𝐢𝐠𝐧𝐦𝐞𝐧𝐭: What is high priority for the sender may not be high priority for the receiver...and so it gets missed. - 𝐏𝐚𝐫𝐤𝐢𝐧𝐠 𝐩𝐫𝐨𝐛𝐥𝐞𝐦: Anything important that requires thought..or emotional energy...is parked for later. But “later” is unstable. Before the receiver returns to it, another wave of emails arrives, with newer urgencies, and the parked mail slips further down. - 𝐌𝐮𝐥𝐭𝐢𝐩𝐥𝐞 𝐪𝐮𝐞𝐮𝐞𝐬: If clarity is missing, the problem compounds. More emails are exchanged, more people are copied. The same work now sits across multiple inboxes, waiting in multiple queues. With emails flooding inboxes, a strange paradox emerges: the sender believes everyone knows, while the recipient remains unaware, misaligned on urgency, or disconnected from elapsed time - leaving many tasks as orphans. 𝐓𝐡𝐞 𝐫𝐞𝐚𝐥 𝐢𝐬𝐬𝐮𝐞 𝐢𝐬 𝐭𝐡𝐢𝐬: when email is used as a workflow tool, it becomes an unrestricted source of work generation. It assumes the recipient’s attention is infinite and ignores the fact that many others are generating tasks for the same person. Unlimited work is injected into a system with limited attentional capacity. Work-in-process balloons, and everything slows down and some even gets lost...classic high-WIP effects ! Here's what needs to be done : 𝐒𝐭𝐨𝐩 𝐮𝐬𝐢𝐧𝐠 𝐞𝐦𝐚𝐢𝐥 𝐚𝐬 𝐚 𝐰𝐨𝐫𝐤𝐟𝐥𝐨𝐰 𝐭𝐨𝐨𝐥. Issues that require understanding and alignment are resolved faster in a 15-minute conversation than through weeks of emails. A conversation explicitly 1) 𝐫𝐞𝐬𝐞𝐫𝐯𝐞𝐬 𝐚𝐭𝐭𝐞𝐧𝐭𝐢𝐨𝐧, 2) 𝐫𝐞𝐬𝐨𝐥𝐯𝐞𝐬 𝐚𝐦𝐛𝐢𝐠𝐮𝐢𝐭𝐲 𝐢𝐧 𝐢𝐧𝐭𝐞𝐫𝐩𝐫𝐞𝐭𝐚𝐭𝐢𝐨𝐧 in real time, and 3) 𝐟𝐨𝐫𝐜𝐞𝐬 𝐜𝐨𝐧𝐯𝐞𝐫𝐠𝐞𝐧𝐜𝐞. Elapsed time collapses. 𝐓𝐡𝐞 𝐭𝐚𝐜𝐭𝐢𝐜𝐚𝐥 𝐞𝐧𝐚𝐛𝐥𝐞𝐫 : Every manager should deliberately set aside cadenced time for such conversations, with these slots visible to everyone. This visibility, and the reliance on conversations, prevents WIP from ballooning by filtering in only the right tasks. Conversations are the most effective use of limited attention. That is how work actually moves. Emails can document decisions. They should not be expected to move work between desks. 𝐃𝐨 𝐲𝐨𝐮 𝐫𝐮𝐧 𝐞𝐱𝐞𝐜𝐮𝐭𝐢𝐨𝐧 𝐭𝐡𝐫𝐨𝐮𝐠𝐡 𝐢𝐧𝐛𝐨𝐱𝐞𝐬...𝐨𝐫 𝐭𝐡𝐫𝐨𝐮𝐠𝐡 𝐜𝐨𝐧𝐯𝐞𝐫𝐬𝐚𝐭𝐢𝐨𝐧𝐬? #Execution #SystemsThinking #Flow