Suggestions for creating an in-house marketing campaign database in a Microsoft environment?

I'd like to create an in-house solution to store marketing segment, list, campaign, and communication data. Right now nothing is centralized/standardized. Data is located on a variety of SQL servers, Access databases, and Excel spreadsheets. It's been a real pain when it comes to reporting/tracking.

I'm in a Microsoft SQL Server environment and have access to:

• Microsoft Access
• Microsoft SQL Server Management Studio
• Microsoft Business Intelligence Development Studio

Security and compli开发者_开发问答ance is pretty restrictive in my environment. Purchasing a third party software package doesn't appear to be an option. I may have the possibility of having a SQL server sandbox environment created for my use.

I'm curious what suggestions you would recommend and why. I need to think about all aspects including existing data retrieval/parsing (some on a continuing basis), data import into the new marketing datamart, and reporting. Some kind of GUI may be required as there isn't currently one for tracking/categorizing much of the data. One other individual may need access to help with regular imports to help spread workload.

Thanks.

Your requirements are incomplete:

existing data retrieval/parsing (some on a continuing basis),

data import into the new marketing datamart, and reporting.

Some kind of GUI may be required as there isn't currently one for tracking/categorizing much of the data.

One other individual may need access to help with regular imports to help spread workload.

Here's your question: "I'm curious what suggestions you would recommend and why"

Here's the answer.

1. Who will use this? Exactly who? Call every single one of them and talk to them about what they do.

2. What is the business value? "solution to store marketing segment, list, campaign, and communication data" is a bad idea. No one wants a "solution" -- they want to get their jobs done. Few people have "problems" that need "solutions". They are already perfectly able to do their job. The best you can do is make them more efficient. Do they care about their personal efficiency? I doubt it.

3. Who has the problem? What problem do they have?

   Think of a "Bacon and Eggs Breakfast". The chicken lays an egg and walks away. The pig, however, is totally committed to the bacon.

   Find the pigs and chickens. Your data is not going to identify your actors and your business problem. Find the people who have the problem. Find out how big and costly the problem is. Be absolutely sure, you've found the real problem that is costing someone real money. Cozy up to the person who's losing the most money and make sure they want their problem solved. They are the pig -- they can be totally committed.

4. Eventually, you'll want to build one central SQL/Server database and get rid of Excel Spreadsheets and MS-Access databases. You might want to have an MS-Access front-end which provides nice applications that use SQL/Server.

If you are actually talking about a data warehouse, then you must next read books by Ralph Kimball. [It's not clear, BTW, that a data warehouse is even relevant. With no problem to solve and no user who has the problem, a data warehouse is just as bad an idea as a web services framework or a new Bentley for me (Black and Silver, thank you.)

If you want to build a data warehouse, your "existing data retrieval/parsing" and "data import into the new marketing datamart" will called ETL.

Your "and reporting" will be moved from an "oh, by the way" to the central most important feature of whatever it is you're doing.

Your "Some kind of GUI" will go away. Data warehousing is not much of a GUI thing. Reporting is as close as you get. Maybe you'll have to create some Master Data Management tools, but even then, those are more rules than interactions.

"One other individual may need access" Really? The end users are what? Chopped liver? They need query access, too, or they won't see any of your data.

You indicate that a purchase of third party software is not an option, however, you should consider that the purchase may in fact be cheaper than the in house development effort, and may bring you to a working system much faster, with less in house skills required.