Posted on

What is ETL?

It’s a geeky term that stands for Extract, Transform and Load and used in order to manage databases. Apart from us mentioning ETL on this page, we’re quite keen to avoid this jargon as we like the term data transformation better.

ETL is really all about using software data tools to get data (a dataset) from one or more databases, manipulating it using rules or actions e.g. reformat, merge, split, de-dupe the data and then uploading the transformed results to another database. You could of course update the original database if you needed to.

ETL’s non-geeky equivalent terms include data migration, data conversion, and data mapping, which to some readers could be seen as still geeky!

ETL comes in handy as it eliminates the need to do repetitive data transformation task manually. Instead of spending hours manually transforming the data the alternative options are either to write some bespoke software coding or use a data tool which are available as a ready-to-use software package.
These data tools make the job of transforming data easier and besides saving time, these data tools will support different database formats (Oracle, SAP, mySQL) and different data types (CSV, XML, flat, fixed).

These data tools are expensive but powerful applications which you install on your local machine. Both the source and destination dataset structures will be shown to the user and the graphical display will show lines mapping the data across to the different structure, hence the term data mapping.

Data mappings can be integral to your processes as some data tools will also allow a data mapping to be scheduled so that the rules can run on datasets based on a schedule which will automatically extract live data, transform it using the data mapping rules and making sure that it gets to its destination (the transformed dataset).

All-in-all a very simple concept and terminology for what is a really complex task as data comes in all shapes and sizes.

And just in case you’re wondering… Yes, DataWand would be classed as an ETL tool though its different from traditional ETL tools as the DataWand Wizard tool is available online and it manipulates the data based on the actions you’ve chosen and then displays the transformed data results immediately which you can then email to yourself and upload it to a database if you wanted to.

Posted on

How to clean up names

Towards the end of last week we were asked by a potential customer if ….

“our software can automatically specify genders etc? What does it do with ambiguous ones?
One of our clients lists is just a field containing first name and surname, some is capitalised and some is not.”

Yes, we can!

DataWand has a good go at deducing genders (Male / Female) and titles (Mr. / Ms.). If its an ambiguous one (we call it a unisex name), we leave the data as is. (and if we don’t have a reference gender for it we leave it untouched too).
We also have a ‘Title Case’ action which will sort out any name field case issues.

For example if you wanted to extract the gender and titles, here’s what the data input could be and its transformed data results:

kelly mitchell –> Kelly Mitchell,
Ms nancy drew –> Ms. Nancy Drew, Female
Jay fitzgerald –> Jay Fitzgerald,
Russell Rodgers –> Mr. Russell Rogers, Male

(The commas above just represents the separate of data)

DataWand does these data transformations in real-time, its automated and done in moments. We don’t have no gremlins or little imps in the background hand crunching the data. Of course there are some alternatives to using us to extract gender and title information, these include:

– Manually correcting the data and working out what the genders are
– Asking a software coder very nicely to help out to automate the work needed
– Learning how to write Excel macros and do it yourself
– Using Microsoft Word’s Change Case menu option to correct the case

Of course we’re bias and we would choose DataWand everytime to extract gender and title from name information, but the choice is yours!

Posted on

What’s in a name?

Here at DataWand we’ve been concocting up rules and brewing algorithms to be able to make very educated guesses as to what someone’s name, title and gender is based on names and email addresses given.

Why? Well, its to help marketeers make their life easier so that they end up with better formed names for their marketing campaigns, be that via email or via post. In the case of email, our aim is to improve the probability of getting pass spam filters and reduce reader irritation! Imagine you receive an email with Dear J, instead of Dear Joanna, wouldn’t that irritate you? And if you’re wondering why post, its to improve the chances of it being delivered to the right final address, especially as if there’s a postal redirection.

In our perfect world, it would have been really nice if all personal email addresses were formatted as:


But, no, that would be too easy and lets not forget that names aren’t unique and some people choose to use only part of their name in their email address. So, back to the real world and normally what happens is we get a range of email addresses which is missing structure and we end up reformatting everything manually.

But now there’s an alternative, DataWand! We’ve built in a couple of nifty actions that we’re currently testing with our beta users.

The first one is ‘Extract details from email address’. It looks at email addresses and automatically extracts forenames and surnames even when its not so nicely formatted and title cases the text for you: –> Jo –> Albert Einstein –> Marie Curie –> J Goodall –> Edwin Hubble

Now, what’s clever (if we don’t say ourselves) is our shiny new ‘Ensure title’ action which will add Mr. or Ms. as a prefix to existing data if no title is present, so you end up with:

Ms. Jo
Mr. Albert Einstein
Ms. Marie Curie
J Goodall
Mr. Edwin Hubble

It may not be 100% perfect, but its getting you closer there while saving you hours of manual data entry work.

Is there anything else that you can think of which will help even more?

Posted on

Where art thou data conversation?

We’ve spent this afternoon searching Twitter to try and join in conversations ‘all about data’ .

But we’re having a difficult time finding that right conversation to join in. Everyone’s talking about big data (the latest buzz word these days) and business intelligence (BI) but we don’t specialise in either of those. Nor are we hard core data cleansers (comparing name, address and contact details against a master all-knowing database), at least not yet.

We are sure that there’s a market niche for DataWand out there because if your data isn’t quite in the right structure or needs to be extracted, merged, split, reformatted, enriched or de-duplicated, that’s when we can wave our magic wands and help out. I think its time to dust off our crystal ball and take a deeper look to find those companies and individuals that are tired of spending hours manually cleaning up data and want to become wizards.

In the meantime, our Twitter search continues… and we hope to conjure out of thin air, the appearance of #DataWand on Twitter.

Posted on

Why we should all love data

We love data and can’t imagine a world without it.

Data is at the heart of everything because by using it we can turn it into information. Lets remind ourselves what the difference between the two are:

Data: Facts and statistics collected together for reference or analysis

Information: Data that (1) has been verified to be accurate and timely, (2) is specific and organised for a purpose, (3) is presented within a context that gives it meaning and relevance, and (4) that can lead to an increase in understanding and decrease in uncertainty.

Wow. Data can be turned into rich information and we can’t think of anyone who isn’t interested in having more of that.

Rich information can tell us more about the world at large. This could be statistics, market research and surveys, finding out about the shopping habits of people, running marketing campaigns to a specific target audience. It could be the difference between a salesperson hitting or missing their target for their quarter. It could allow us to calculate with accuracy weather patterns, as demonstrated by Sandy, the hurricane which is causing havoc on the US east coast as I type this. The possibilities of using information are endless.

But in order for information to be rich, its source data or what some people like to call ‘master data’ has to accurate, relevant and to a high quality. What we’ve found out is that generally we’re not very good at keeping our data quality up and find ourselves spending countless hours carrying out housekeeping tasks to get it back up to standard and its normally done when we need it at the last minute.

That’s where a toolkit like DataWand comes in. Our ambition is to improve data quality and allow people to quickly get what they want and need. We’re just starting out and figuring out how we can help so if you would like to be part of our journey please do get in touch and together we can have rich information.

Posted on

DataWand has magically appeared!

With a swish of our wand and some magical words, we released DataWand out into the public domain.

Wave your DataWand and instead of turning frogs into princes, DataWand will transform your data from one look to another and you can see the transformation taking place right in front of you. This happens all online, in real-time, and for a charge will email your final results to you.

Become a Wizard with DataWand

Are you a marketeer? Save yourself hours of manually changing the first letter of a name to upper case which you may be investing time in as you want to improve your email marketing conversion rate by using DataWand to do that for you in seconds.

Are you tired of having UK mobile numbers missing out their ‘0’ prefix, wave your wand and hey presto! the 0 magically appears.

It’s multi-purpose and suits the needs of different people in the organisation, even the more techy roles. If you are a database administrator and the marketing person hands you a list of contact details, we have the perfect wand to wave over the data to magically turn it into INSERT INTO SQL statements.

We know that DataWand isn’t quite perfect yet (hence its a beta release) and we’re just at the start of our journey but we want you to help us make sure that DataWand works for you.

We’re betting our Wizard’s Tower on this and would love to hear how DataWand helped you with your data. If you need a helping hand, please do get in contact with us via our contact form. Our Wizards have their wands at the ready.

Happy transforming!