Monday 17 August 2015

Should your Data be in a Database?

5 Benefits of a well-designed database

Why Data is Difficult

It’s tempting to think that data isn’t difficult to manage.  It doesn’t have emotional problems, complain of having a bad day, or be prone to phone in sick.  Isn’t data factual and therefore easy to pin down?  If only ….

Data, as far as business is concerned, is often far too fluid for most managers’ liking.  Consider data about people. People inconveniently get married, change their jobs, and get older.  But surely business data is easier? If only companies didn’t close down, move premises or change the nature of their business, things might be easier.  And then arguments break out about what data should be held, and how to categorise things.

Yet whatever type of data you need to manage, the way you hold and manipulate that data is either a source of competitive advantage or a giant headache.

Many companies or departments still hold significant amounts of data in spreadsheets or paper-based systems.  Whilst paper–based systems are rarer than they used to be, they still exist.  Paper can’t be deleted, it is highly visible (if bulky) and it gives the manager a sense of solidity and permanence.  And of course spreadsheets are very quick and easy.  Yet both spreadsheets and paper have serious limitations.

5 Benefits of a Well-Designed Database

A relational database is a robust way to hold data.  It has five big benefits that neither paper nor spreadsheets can compete with.
  1. A database creates a centralised data store.  When data is centralized it is shareable, and accessible from anywhere, particularly if the database is held in the cloud.  Holding data in one place means you get a full picture, instead of a number of partial views.  And it reduces possibility of duplicate data. Centralised data can be properly secured, backed up, and password protected. 
  2. Organised data. Most well-designed databases are professionally-designed databases.  That means that a certain amount of rigour in requirements analysis and data design has been applied. Stakeholders have been consulted, and differences of opinion aired resulting in a design that everyone agrees with. A well-designed database makes it easy and quick to find what you need, and to add more information in the correct place.  Spreadsheets may be easy to create, but quickly get muddled and unwieldy.
  3. Databases love analysis.  Databases are ideal for looking at data in multiple ways. So the same data set can be presented in differently either for different audiences, or to help understand the data better.  For the most part, spreadsheets present data in one way only.  Whilst it is possible to change the order of data using a spreadsheet it is both limited and error-prone.  Databases, on the other hand, are designed for many different types of analysis.
  4. Databases Improve Data Quality.  Databases are designed to improve data quality in a way that no other tool can.  By tightly defining the type of a data that can be added to a given field, or by adding business logic, you ensure that the right type of information is entered.  Look up tables ensure consistency in data entry so no matter how many people are using the database, your data is still in good shape for analysis and reporting purposes. 
  5. Data is Future Proofed.  Whilst there is much talk of big data, some systems start small and then grow.  You may only see the need for a recording a handful of attributes but over time that may change.  Databases are designed to let you add more attributes, and more records, all without penalty.  Spreadsheets, on the other hand, get more difficult to manage as attributes and records are added.  Holding information in a database also enables you to join data sets together, to get a fuller picture of your business.  
A databases is more complex to set up than a spreadsheet, but offer a great deal more as data is safeguarded and improved over time.  Unless you are dealing with very small amounts of data, the effort in setting up the database is a small price to pay compared to the benefits.  There are significant advantages to a business in going through the process of deciding what data to hold, consulting stakeholders, and defining business logic behind the data.  The exercise always throws new light on data, and new insights into business processes.

In today’s internet-enabled world, data is no longer a business after-thought.  Whatever the nature of your business, data about customers, competitors, operations and much else now lies at the heart of the organization. 
Anatec Software is a team of specialists in Microsoft SQL Server, the world’s most trusted relational database.  We focus on thorough requirements analysis, database design and development, as well as performance turning.  

Wednesday 29 July 2015

It’s a date!

Microsoft SQL Server Date and Time

One of the advantages of a database is that you can restrict the type of data that is entered.  Unlike a spreadsheet that lets you enter any kind of data, such as text, a smiley face or a date, SQL Server will force you to enter the correct type of data in a specific field.

This may sound like a disadvantage but it’s not.  Flexibility seems like a good thing, but you pay a heavy price. The old adage of "Garbage in; garbage out" is so true. A database makes sure the right information gets entered into the right field at the point of entry.  When you want to analyse the information later, it actually makes sense. Comparisons, selections and reports are correct, instead of finding that half your data is incorrect.

For example, SQL Server has six different data types for date and time:

Data type
Date range
Time range
Storage space
01-Jan-1753 to 31-Dec-9999
00:00:00 through 23:59:59.997
8 bytes
01-Jan-1900 to 06-Jun-2079
00:00:00 through 23:59:59
4 bytes
01-Jan-001 to 31-Dec-9999
00:00:00 through 23:59:59.9999999
6 bytes for precisions less than 3
7 bytes for precisions 3 and 4
8 bytes for all other precisions
7 is the default if precision not stated
01-Jan-001 to 31-Dec-9999
01-Jan-001 to 31-Dec-9999
00:00:00 through 23:59:59.9999999
Time zone offset range -14:00 to +14:00
10 bytes
00:00:00.0000000 through 23:59:59.9999999
5 bytes

That gives you options, depending on the type of data you want to hold.  But you have to be careful.  Datetime, for example, may be suitable for many purposes, but if you want to record historical facts.  The reason is simple - you can’t enter a year earlier than 1753.

So why would a date data type start at 1753? Is it just an arbitrary date so long ago that it surely must cover everything? I haven’t asked the SQL Server team, and maybe I should, but my guess is that it was when the Gregorian calendar was introduced to Britain and parts of the USA.  Interestingly, at the time of the switchover from the Julian calendar to the Gregorian calendar people often used two dates, so as to be clear.  Quite sensibly, SQL Server doesn't allow that, but it does allow you to choose the right date/time data type for your needs.

Choosing the correct data type for your data and storage needs will immediately improve the quality of your data. Whether its a date, a smiley face or text ...

Wednesday 8 July 2015

Where to start with Data Driven Marketing

The successful US retailer John Wanamaker (1838 – 1922) is credited with the all too true witticism:

“Half the money I spend on advertising is wasted, the trouble is I don’t know which half”.

His quip goes to the heart of marketing – how to match buyers with a need to sellers who have what they want, but without enormous waste? Our internet age is prolific in providing data from buyers: web logs, Google Analytics, Facebook stats, Twitter feeds, plus all manner of data from our internal systems. Buyers are actually telling us what they want, we just have to interpret it correctly.

Which is what data driven marketing is all about.  Using the available data to reduce waste in the marketing system.  To match buyers who like, want and need a product or service, with those who sell it.

The benefits of data driven marketing are compelling.  Data tells us what customers want, and what they don’t want, so we can match what we offer to their needs and wants more efficiently.  Marketing costs are lowered, which is good for both buyer and seller.

But how do you get started with data driven marketing? 

The first activity is to understand your customers.  Carry out an audit of what data you have about your customers, and which systems that data is stored in.  Email, accounts, and CRM systems are likely to hold data, and so might operational systems, interactions on social media. The audit should also include the gaps: the customer information you would like, but don’t currently have, and what you would do with that information.

The audit creates a plan to bring together the relevant data, ideally in a database, so it can be analysed by a range of criteria. 

Holding customer information in a database, rather than Post-It Notes, mind maps or anywhere else is important. Whilst Post-It Notes may be colourful, they don’t give the power and flexibility of a database.  A spreadsheet is a more likely candidate, but it doesn’t provide the flexibility or future-proofing of a database.  As data is added, either records or attributes, spreadsheets become more difficult to use.  A database is designed to hold any volume of data, from small to large, and a database enables you to categorise data by a variety of attributes. As you work with your data, using a robust database like Microsoft SQL Server becomes increasingly important. Crucially, a database allows you to join information from different systems, known as a data warehouse or data mart, without any rekeying.

Sales and customer data is a solid first step in creating a data driven marketing strategy that has immediate benefits.  You get a factual profile of who buys your product or service, and perhaps who doesn’t.  It can form the basis for tests into similar or dissimilar sectors, and the basis for tailored marketing communications.  

Anatec Software Ltd specialises in database design, development and consultancy including data warehouses and analytics for marketing.  We work with Microsoft technologies including SQL Server database and Business Intelligence tools for both on-premises installations, and Microsoft Azure. 

Friday 27 March 2015

Data driven marketing - then and now

Marketing has always been data driven, at least some marketing.  In the old days there was something called mail order.  We now call it e-commerce and its not so different. I started my career in the family mail order business and there was a fair bit of data around even then.  My Dad could often be found making complex calculations on the back of an envelope. Things called cheques used to arrive in the post so envelopes were not in short supply. He would figure out what people had bought and what we should sell.  Then we got something called a computer. At least we called it a computer ...

Now I could regale you with stories about how the computer looked like a space ship (not a joke, it really did) but that’s not the point.  The point is that data driven marketing isn’t new.  What’s new is the amount and quality of data that’s available, and the tools we have to analyze it.  All of which is a lot more than an email away from those happy day of envelopes and biros. 

Data sources

Even the smallest businesses hold data in a multitude of systems: accounts, CRM (customer relationship management) system, stock control, estimating, custom databases, spreadsheets, the list goes on.  Many businesses now have data in the cloud (a concept that well confuses my Dad), as well as in-house PCs or servers.  Then there’s data held in third party systems such as Google Analytics, Facebook, Twitter and more.  Our most precious marketing data is held all over the place – quite literally.

Data quality

If you suffer from poor quality data in some of your systems, you are no doubt expecting me to talk about improving the quality of data before its analyzed.  That’s certainly important and relevant.  But on a more positive note, the amount of high quality data that’s available to us is quite staggering.  We can see who has visited which web pages, we know what people are saying on Twitter, we can target advertising on Facebook to a degree that was a much more expensive in the biro days.  Although less business is conducted face to face now, we have the opportunity to know more about our customers and how they buy.  If we care to look, that is.

Marketing Analytics

So what to do with our data, data, everywhere?  Sense-making would be close to impossible if it wasn’t for two great marketing saviors – SQL Server and Excel.  We can join up disparate data sources in data warehouses (they sound huge, but you can have little warehouses too), we can analyse data from multiple perspectives (data in cubes), and we can manipulate it in Excel.  This is where the PC really starts to show it can face up to any envelope!

Marketers no longer have to rely on static reports, they can work with the data.  There’s all the difference in the world between passively reading data, and being able to manipulate and play with data.  You can create a graph, perform calculations, create pivot tables, look at a sub-set, and add in extra information.

Bringing it together

Bringing it all together isn’t nearly as daunting as it sounds, and surprisingly you already have the software on your PC.  Creating marketing dashboards using Excel is now both feasible and powerful, providing you have the data to feed your dashboard. 

It seems a world away from a small mail order business, but the principles are just the same.  Only the shape and power of the computers has changed.  Plus some neat technologies that are well suited to solving marketing problems. Oh, and there are fewer biros around.

Talk to us!

Have you got something to say about data driven marketing?  Or biros?  Do you want to talk about how Microsoft technologies could help you on your marketing data journey?  We are specialists in databases for marketing, data warehouses and joining the marketing dots.  Leave a comment or get in touch.

Wednesday 14 January 2015

I'll Build a Spreadsheet for That!

Whenever a job needs doing, or you want to measure or monitor something, the trusty spreadsheet appears.  Someone says brightly “I’ll build a spreadsheet for that!”  Without too much thought heads are nodded, people disappear with the warm glow of a job well done.

Sometimes the spreadsheet is built, but not always.  If the spreadsheet is built, sometimes it does the job, but often it doesn’t.  Perhaps more importantly, though, it may not meet everyone’s needs.  Then, instead of going back to the drawing board, multiple spreadsheets start appearing.  Excel is quick, easy, and doesn’t require any expert knowledge, so everyone mucks in to solve their own particular problem.  And therein lies its undoing.

Having said all of the above, Excel is a great tool, which we all use a lot for all sorts of useful things.  I’m sure I couldn’t live without it.  But it’s not suitable for everything.  Sometimes it takes a database like Microsoft SQL Server to make sure the job is done properly.  Here’s why:

  1. No one can build a database without discussing and consulting other people about what’s needed.  Even the simplest databases take thought, planning and a design.  Where to host it, who needs to use it, or even whether the database is worth building.  That communication is vital and enlightening.
  2. Databases are designed to protect data.  You could say it’s their raison d’etre.  Microsoft SQL Server is designed from the ground up to make sure data is never lost, that it is correct, and that if several people try and update it at the same time, the conflict is resolved elegantly.  Much as I love Excel, I can’t say the same about a spreadsheet.
  3. Microsoft SQL Server is designed to let many people use and update information.   People who are expert in the subject area, and those who are less expert.  You can check that the data entered is within an allowed range, that data is only entered once when used within another record, and that duplicate records are not entered.  None of these things seem important at the beginning, but they are all very important once a system has been used for even the shortest time.

There are many more reasons why database systems tend to do the job they were built for, and provide business value over a long period of time.  As each new version of Microsoft SQL Server is released, more functionality is added to protect and enhance business data.  You don’t need to hold huge quantities of data, or massively complex information, for a database to be the best solution.  But if you want everyone to input data in the same way, to be able to work from up to date information, and trust that the data is correct, Microsoft SQL Server could well prove to be part of the solution. 

Now you can use SQL Database on Azure, giving everyone the ease and convenience of the cloud. 
So next time someone says “I’ll build a spreadsheet for that!” pause a moment and reflect on whether it will provide the share-ability, and durability that your information needs.  And if you think a Microsoft SQL Server database might be better for your needs, but would like some help in setting it up, we’d be happy to help.