Managing Customer Databases

What happens when sales or accounting teams change the headers in the data? It messes up the lookups. Is it possible to deal with the changes without blowing up the model? Traditional Excel lookups don't work well when the names and locations of the data are modified. They can't find what they're looking for. So here are the 3 core parts of this technique: (1) Customer Transactions Tables (blue and white) Every customer's data is imported into the model using Power Query. All dynamic tables are named: "customer_name_tbl". (2) Dynamic Table Registry (green and white) You can keep a registry of all of your customer data tables using Power Query: = Excel.CurrentWorkbook() = Table.SelectRows(Source, each Text.EndsWith([Name], "tbl")) By placing all of your tabular customer data into a dynamic table, you can call on them quickly and easily without needing to reference the individual worksheets. To the right of the dynamic table, using an INDIRECT, point to the table name and return a dynamic array of the headers: =INDIRECT(table_listing_query[@Name]&"[#HEADERS]") I know that some of you will disown me for even thinking to use INDIRECT, but we're using it 5 times so this is unlikely to cause performance issues. (3) Dynamic Model (white) When you select the name of the customer in the data validation, the lookup goes to the Dynamic Table Registry and pulls the respective headers. These headers are then used to pull the correct data and maintains the integrity of the process. In other words, the data headers are the gold. And the dynamic registry of data headers is how we fix the problem that business partners unintentionally create. -------------------- Working with imperfect data doesn't mean we throw up our hands and give up. It also doesn't mean it should be someone's responsibility to spend hours or days cleaning up every time new data comes in. It means two things: (A) We need to build processes that don't require an overhaul. This calls for building more dynamic data management processes that can scale and require minimal (if any) rework. (B) We need to communicate with business partners whose actions impact ours, and explain what behaviors break the processes. When you combine better data management practices with recurring processes that business partners are bought into, you're better able to focus on what data analytics are telling you and less on the mechanics of the model.

We've all heard the old saying in advertising: "I know half of my advertising is working, I just don't know which half." For too long, that’s been the reality for many in automotive retail. We spend money on mass marketing to databases filled with dirty, outdated customer data. This leads to wasted ad spend, irrelevant messages, and a frustrating experience for our customers. We've learned that you don't need more leads. You need a cleaner process to get more out of the opportunities you already have. That's the power of a Customer Data Platform (CDP). Our journey with a CDP was about getting "unstuck" from old habits. The first critical step? Getting our data clean and establishing a single source of truth. We found that 52% of our customer data was dirty in some way, full of bad addresses, outdated phone numbers, and sold vehicles. By simply cleaning and enriching our data, our advertising started working more effectively almost instantly. Now, with our CDP, we're not just waiting for a lead form to show up. We’re engaging with customers in real time. We know when a shopper starts filling out a service scheduler or a trade-in form and abandons it. With this information, we can send a personalized, automated message to help them finish the process. The results from this single use case were immediate. This system is changing our business. We’ve seen our sold in timeframe rate jump to 30%, which is more than double the national average of 12.4%. By focusing on a better, cleaner process, March and April were two of the best months in our company's history...a feat that has never happened before. The goal is to control the experience, not just react to it. It's about moving from mass marketing to micro audiences, delivering the right message, at the right time, to the right person. What's the one "dirty data" problem that frustrates you the most? #wearerohrman

🔍 Day 2/10 Unlocking Data Modeling: Database Design Mistakes to Avoid As we dive deeper into data modeling, let's explore common pitfalls that can impact database efficiency and data integrity. Here are key mistakes to steer clear of and solutions to enhance your database design: 1️⃣ Using Business Fields as Primary Keys: It's tempting to use natural business fields as primary keys, but these can change and aren't within your control. Solution: Implement a surrogate key—a dedicated field solely for identifying records, ensuring stability and control over data relationships. 2️⃣ Storing Redundant Data: Avoid storing data that can be derived dynamically, such as age which changes over time. Solution: Calculate dynamic values like age or derived fields in SQL queries to maintain data accuracy and reduce redundancy. 3️⃣ Incorrect Naming of Attributes/Fields: Clear and consistent naming conventions are crucial for database clarity and maintenance. Use descriptive names that reflect the data's purpose and ensure uniformity across the database schema. 4️⃣ Poor or No Referential Integrity: Ensure data consistency by enforcing referential integrity constraints between related tables. This prevents orphaned records and maintains relational data integrity. 5️⃣ Storing Multiple Pieces of Information in One Field: Fields like addresses should be broken down into separate components (street, city, state, country) to facilitate easier querying and data management. 6️⃣ Mixing Optional Data Types in Columns: Avoid storing different types of data (e.g., mobile, home, office numbers) in separate columns. Instead, normalize your database by creating related tables (e.g., customer_phone) to handle different types of contact information efficiently. Effective data modeling isn't just about structure; it's about optimizing performance, ensuring data accuracy, and facilitating robust analytics. By addressing these common mistakes and adopting best practices, you pave the way for a more resilient and efficient database environment. 🚀 Let's continue to explore data modeling best practices together! Share your thoughts and experiences in the comments below. #datamodeling #databasedesign #datamanagement #dataintegrity #sql

Do you know what I have found to be one of the biggest hurdles in CRM data hygiene? 🕰️ Keeping information fresh and relevant! In the world of CRM, data ages like milk, not wine.🥛😬 Trying to run campaigns or outbound efforts using outdated data is like trying to navigate with an ancient map - you might end up in uncharted territory or, worse, completely lost. 🗺️ This challenge reflects our human struggle with time and change. Just as cultures evolve and adapt, so does the information about our customers and prospects. So, how do we keep pace with the relentless march of time? Here are some best practices: ✔️ Regular Data Audits: Schedule periodic reviews of your CRM data. It's like spring cleaning for your digital house! 🏠🧼 ✔️ Leverage Automation: Use HubSpot's tools to automatically update certain fields like workflows moving contacts through the lifecycle stages. Let the robots do the heavy lifting! 🤖💪 ✔️ Encourage Customer Input: Create opportunities for customers to update their own information. It's a win-win - they get better service, you get accurate data! ✔️ Train Your Team: Data hygiene is a team effort! Make data updating a part of every customer interaction Fresh data is the lifeblood of effective sales and marketing. ⏳By keeping your HubSpot CRM up-to-date, you're not just maintaining a database - you're preserving a living, breathing record of your customer relationships. 💖 #DataFreshness #HubSpotCRM #DataManagement #DataHygiene --- 👋🏼 Hi, I'm Omi, co-founder of Diaz & Cooper, a Platinum HubSpot Solutions Partner helping B2B companies create efficient revenue operations. I'm on a mission to bring the human back to HubSpot. Need some help with your data hygiene practices? Let's talk!

When you're an early startup, a few things change after you acquire your first set of paying customers. One phenomenon is confusion about multiple sources of truth. Once in a while I meet founders doing early stage sales where they bring up a problem: customer data is being edited in Airtable, the CRM, and their database. Chaos is ensuing with colliding values! What can be done? The answer: don't do this. Instead, declare a source of truth for each set of data. For example, your CRM should be the source of truth for business data (e.g. customer names). After all, that's where your future sales and finance people will be generating contracts and invoices from. Your database, on the other hand, should be the source of truth for product data. If you're piping a 'date of last login' field from your database to your CRM, no one should be manually editing that field in your CRM - it's not the source of truth for that value. Even if you don't have those teams in your company yet, it can be helpful to pretend they exist and organise your data processes accordingly.

A dirty database is a silent killer for your business... Ask yourself: - Are you targeting the right audience for your product/service? - Are you updating your contacts in your CRM? - Are you optimizing your customer experience? That's what I mean by your database... and that's just the tip of the iceberg. And I keep talking about it because BAD data means: - Your business loses an average of 27% in revenue. - Your sales teams lose 546 hours a year cleaning up dirty data instead of closing deals. - You spend millions fixing bad data when you could have prevented the issue in the first place. I wrote a blog post for the team recently and mentioned that our goal this year is to "lead with data, win with action." Here’s what that looks like: ✔️ Run regular audits → Identify outdated, incomplete, or duplicate records before they drain your budget. ✔️ Use intent data → Target buyers who are actually in-market, not just static lists of names. ✔️ Validate everything → Emails, phone numbers, job titles—if it’s wrong, you’re throwing money away. I always say that we don't win unless our customers win. Because great marketing starts with great data. Without it, even the best strategy won’t work.

I explained our philosophy of building an Installed base "map" to drive Aftermarket growth initiatives in my last post. But the curious-minded amongst you probably want to know the "how" of the process as well! So here goes: The central and foundational step that needs to get done is the unification of any and all data that has anything to do with customers, or assets that an OEM has engaged with over time. First, inventory all systems, processes and people who do any jobs that involve customers and assets. We see data trapped in ERP, CRM, PLM, Service, Tech support, spreadsheets, old databases (Lotus anyone? Access?!) that is relevant to building a good chronological history of an Installed base. Second, bring subject matter experts together to do ensure they validate and verify these sources and confirm "almost completeness". Our experience is that it takes an iterative process to even create this inventory of data sources before you can start the hard work of unification. Third, profile this data, cleanse, deduplicate it and classify equipment, parts, consumables, services etc per the taxonomy you prefer to use. This step can be time-consuming, but there are plenty of off the shelf tools available for experienced data teams, or better yet, outsource it to a vendor who can manage these flows consistently and continuously as your data gets updated and changes are made. Remember, this is not a one-and-done effort. Finally, when the data is prepared, have your users and SMEs validated it for errors and omissions, classification and categorization etc. As a wise person once said, data will remain dirty till it is used. The unification of data provides the foundation of the insight creation processes central to a refined, high ROI aftermarket growth engine. In my next post, I will outline some of the analytics and algorithms required to drive these initiatives. #dataunification #aftermarket #installedbase #dataquality #entytle