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.