Tag Archives: Excel

How to use the Google WMT crawl data – once you fix the date format!

The crawl data that you can download from Google’s Webmaster Central Tool would be much more useful IF you could arrange the data by date. You can’t pivot or sort data if the data is [first of all] in American date format, and 2ndly some is in date format, some in text format and some just throws an error.

So, how do you cleanse this data?

Step 1 – download your crawl data

Click on the “Download” option inside in Google’s webmaster tools interface

Step 2 – Open excel and import your data as “From Text”

Here is the advice from the Excel help file

Importing text files
You can use Excel to import data from a text file into a worksheet. (On the Data tab, in the Get External Data group, click From Text.) The Text Import Wizard examines the text file that you are importing and helps you ensure that the data is imported in the way that you want.

There are two ways to import data from a text file by using Excel: You can open the text file in Excel (which does not make a connection to the text file), or you can import the text file as an external data range (which does make a connection to the text file).

There are two commonly used text file formats:
Delimited text files (.txt), in which the TAB character (ASCII character code 009) usually separates each field of text.
Comma-separated values (CSV) text files (.csv), in which the comma character (,) usually separates each field of text.

You can also change the separator character that is used in both delimited and .csv text files. This may be necessary to make sure that the import or export operation works the way that you want it to.

You will be importing a .csv

Step 3 – Use the Text Import wizard

Use the Text Import Wizard. I have never had to make any changes on the first step.

On step 2 of 3, you should choose COMMA and not Tab.

Here is the important bit. On step 3 of 3 you can choose what is the format for the import. So, in the “Data Preview” pane, scroll to the right. Select the Date column and choose your preferred date format (see arrow).

Excel's Text Import Wizard - choosing data type

Excel's Text Import Wizard - choosing data type

Click Finish. Select the location you want your new table.

You would think that you would now be finished! No, Google also give us 2 date format

3/01/12
03/01/2012

Standarding the year format

I am sure that there must be an easier way than this, please share if you know. You can’t just format cells to date. So, insert some new columns to the right of the date column.

  1. Hightlight the Date column. Select “Text to Columns” on the “Data” tab
  2. In the wizard, choose to seperate by “other”, enter a slash “/”.
  3. Now use the good old fashioned “Find and replace”. Replace “12” with “2012”, then find all the accidentally created “202012” and replace with “2012”. And repeat if you still have 2011 data in there
  4. Then just Concatenate the 3 columns back together using this formula structure (assume in columns 1,2 and 3 and use the text insert of a slash inbetween quotation marks)
  5. =CONCATENATE(a1,”/”,a2,”/”,a3)

  6. Double click on the bottom right hand corner of the cell to copy all the way down
  7. Copy the resultant column, “Paste Special” next to it as “Values only”
  8. Delete the rest of the working columns

You are now ready to actually use your own data. Google please help us!

However, there are still bad data and formats in the data. So, I always prefer server side tools.

How to make a simple positive “Waterfall chart”

If you ever need to produce “blue sky” ideas or have to explain causality then a waterfall chart can be a great way to show that graphically. Especially in the less slides in a deck is good world.

I had to do one of these the other day. I was given a cryptic old workbook from finance, which would take longer to cut & paste/decipher than to actually teach myself how to do it. Plus, it was too complicated for what I needed anyway.

I had to make a simple, “if I had to reach a target” visual representation. So, I searched around on the inter-web and came across some mathematician level answers for complicated graphs or utter rubbish. Based on this and the bits I could understand I found my own way.

I am sure there are other ways from people smarter than me. But, I thought I would share my simple waterfall how to guide.

Step by step to make your simple positive “Waterfall chart”

  • Have your starting point, e.g. current run-rate in row 2, column 3 (see figure 1)
  • List out all your activities you intend to do, to move towards the target with incremental value. So activities in column 1 and values e.g. incremental orders in column 3
  • Put your target Row 8, column 3
  • In row7, column 3 work out the difference/gap if applicable to your story
  • Column 2 is now for the “height buffer”, which will be formatted out later on. This should just be the cumulative total to effectively suspend your real data (column 3). Will make sense in a minute
  • Plot your stacked bar graph. (See figure 2)

Waterfall data sample data

Figure 1. Sample data


Waterfall chart - almost there

Figure 2. Half way there

Now you need to remove the column 2 buffer data from VIEW

  • Highlight and delete the legend
  • Right click and format the target column to make a different colour
  • Right click on the ‘buffer’ series and “format data series” to “No Fill” and “No line colour”
  • Optional extras may include the colour of any difference (if any), data labels or lines to aid digesting

Your graph should now look a bit like this. (See figure 3)

Sample waterfall chart

Figure 3. Ta-dah - Waterfall chart ready for your formatting

From now on you can customise it any way you want. Add any kind of title etc and generally put it into your house style.

But I think you will agree, it is a very simple way to represent data in a single chart. And it will make you look like a pro. That is how I make a simple, positive waterfall style graph. Hope it works for you too.