What are Metrics, KPIs and Analytics?

Metrics

Metrics means a measurement or indicator, a common term used in business. The term is the only thing common about metrics. Every business will have important measurements which mean something different to them.

Ex. 1 – Company A may use a metric of widgets produced per hour to indicate how productive they are at producing widgets – the higher the number the better

Ex. 2 – Company B may see the same number as an indicator where quality starts to suffer

Read through the following article and identify 3 to 5 Metrics you find useful for your business – What are Business Metrics?

KPIs

Key Performance Indicators (KPIs) are targeted metrics for the key areas of focus in your business. They are still metrics however, these are the most important metrics you will find value from as performance indicators.

Using the above metrics exercise, identify one KPI for your business and describe what it is an indication of. NOTE: Generally speaking, KPIs are the positive measurements of good performance.

 

Analytics

Analytics on the other hand, is what you do with those metrics/measurements….the analysis of the data = analytics

Watch this brief overview video of what Analytics is and some example use cases.

Analytics tutorial: What is analytics? | lynda.com

DIY Data Analysis and Reporting Approaches

There are a several approaches the Do-It-Yourselfer can take when it comes to Business Analysis. Each approach offers certain advantages and disadvantages – all approaches depend on what data you can get your hands on.

Generally speaking these approaches come down to three things – 1. Getting the Data, 2. Analysing the data, 3. Displaying/Reporting the Data

Getting the Data – Common Data Sources Available:

  • Microsoft Excel Sheets
  • Microsoft Access Databases
  • Text Files (i.e. CSV (comma separated)
  • Database Connections (MS SQL, Oracle, MySQL, PostgreSQL, etc.)
  • Active Directory or other Corporate Employee Hierarchies
  • Web sources (either internal or externally created)
  • Self Service Reporting tools which allow exporting in one or more formats

Review your business and potential data types, often times reports received in one format can be exported or made to work in another format. It’s good to think outside the box and this gets easier as you do it more. Start to think how information in your data is related to each other = i.e how could you link a key field which is the same to one to the other (ex. Ext #, LoginID, Email)

Analysis and Reporting – Approaches:

  • Microsoft Excel
    • One or more data sheets in one or more workbooks
    • Sheets and workbooks may be linked and referenced to each other
    • Workbooks may contain static data or refreshable dynamic data (detail found in Excel–Data source connections)
    • If using refreshable data then the source may come from many places (detail found in Excel–Data source connections)
      • Ex. Databases, Web, Text Files, CRM systems, Social Media and the list goes on and on
    • Manipulating your data after establishing sources
      • Displaying your data after you’ve established sources – there are many ways to do this

For simplicity sake, using a report you already receive from someone else or created some other manual way – think of how it could be better or easier to produce – later modules will teach you some tips and tricks, but for now, simply start making notes on ways you need to simplify the data analysis and reporting functions you want to do.

Excel–Data source connections

*Note: All screen shots and walkthroughs are from MS Excel 2013

Microsoft Excel is a very powerful tool for data gathering, analysis and reporting. In this module we will focus on connecting to external data sources such as Databases, Web sources and Flat Files.

The benefit of using a data source connection is primarily for source data which changes. Once the connection is setup once, you simply have to use the “Refresh” option to refresh the data from the source automatically.

From the “Data” tab on the top Ribbon Menu the user has several choices for external data sources.

image

Next Module—>What to do with your data After You have it…

Excel–Data Source–From Web

<– Back to Parent Topic

Click “From Web” on the Data Tab of the Ribbon Menu

image_thumb

The following Window popup will appear….

  1. Paste a Web Address which has a Grid like result into the Address Bar
  2. Click Go
  3. Various Sections on the Webpage will appear with a Yellow –>, click the appropriate one which represents your desired data and it will turn Green
  4. Click Import

image_thumb2

Click OK after choosing which cell to import your data to

image_thumb6

Your Data Sheet will look something like this….

image_thumb5

 

Now try this yourself either by using your own Web Source or feel free to use the following URL <https://kentgreff.wordpress.com/sample-data/>

NOTE: This method of importing Webdata is not as powerful as the “”Power Query Method” discussed in later modules (and formatting tends to be a problem ) <INSERT Link REF>

 


<– Back to Parent Topic

Next Module—>What to do with your data After You have it…

Excel–Data Source -MS Access

<– Back to Parent Topic

Sample Template in example found here

  1. Click the Data Tab on the top Ribbon Menu
  2. Click From Access
  3. From the Popup menu locate your MS Access File
  4. Click Open

image

A List of Tables will Pop-up – Choose your Table and it will now appear in Excel

image

Now try this yourself either by using your own Access Database or feel free to use the following URL

http://access-templates.com/mobile/Fitness-Workout-Membership-Access-Database-Templates.accdb


<– Back to Parent Topic

Next Module—>What to do with your data After You have it…

Excel-Data Source-From Text

The “From Text” option is how to Import Text files which are capable of being split into columns to be used as a Data. The most common for mat is Comma Separated (CSV) but you will also come across Tab delimited or some other consistently delimited string.

In a Comma Separated file each column is separated by a comma and usually the text is in quotes.

Note: Quotes are important because the columnar data may contain a comma itself which is not meant to split the column

Example: (Note the quoted column where the data contains a comma)

Extension,TotalCalls,TotalDuration,TotalCharges,CountNMonths,Name
2062,316,2.861631944,30.6,4,”Greff, Kent”
2072,295,1.487037037,23.82,4,”Baggins, Bilbo”
2086,373,3.610636574,89.56,4,”Dog, Sully”
2100,220,1.698831019,20.54,4,”Cat, Zora”

Which in Excel, looks like this….

image

The “From Text” Wizard on the data tab will allow you to import from a text file. This wizard also runs if you open a text file in Excel from File | Open.

The following example will show you how to import either a Comma Delimited (CSV) or a TXT file with some other delimiter

image

 

image

These two screen shots show the difference if using a CSV or a Text File with a |  (Pipe) delimiter

imageimage

Click Finish and your Worksheet will have data such as this

image

Download the Sample Text Files found Here and practice the From Text steps

Microsoft Access– DIY Business Analysis

Microsoft Access is another powerful tool for your BA tool belt. It offers several great features for data analysis and small scale personal data mining; if you’re willing to put in the time to learn you can even build some pretty great, fully functioning applications.

It’s been my experience that Access is great tool to use personally for your own purposes but is less user friendly to those less familiar and unless you possess the skill to build a user friendly interface, it tends to be too complicated for the average business user.

As DIY Data Analyst you can take advantage of features like external data connections, relational data querying, wizard driven user friendly queries, store your own tables of data without IT departments to get you a db server, create simple forms to track data for later reporting and Wizard driven reporting.

If you take advantage of building a data model in Access you can also use Excel to connect to your new Access data and take advantage of all the features of Excel as well (Chart, Pivot Tables, Power Query)

External Data Sources:

Much like in our previous modules for Excel, Access has many of the same external data connection options such as, other Access DBs, Excel, Flat Files, SharePoint and ODBC compliant DBs (SQL Server, Oracle, MySQL, Etc.)

When connecting to External Data you have the option of Importing a Copy or Linking. If linking then some files cannot be open by another program (eg. Excel files must be closed if linking to them)

image

Read through the following two tutorials on External Connections in Access. Setup a connection to any source of your choice (Excel is a simple starting point).

http://sourcedaddy.com/ms-access/what-is-external-data.html

https://wagda.lib.washington.edu/gishelp/tutorial/AccessImport.pdf

Queries:

A query, by definition is a question. In Access, a query is the users way of asking a question about the data and getting a result. Example: What Sales data exists for customers in England –> Select * from SalesDataTable where Country =”England”

Like other relational databases, Access allows the user to perform queries against it’s data with SQL. For simplicity, Access offers friendly Wizard driven methods to create queries with no knowledge of SQL needed.

From the “Create” menu on the top Ribbon menu, Create a Simple Query using the Query Wizard (Note: you must have at least one Table)

image

Additional Reading on Queries:

Designing Simple Queries—>http://www.gcflearnfree.org/access2013/10

Designing a Multi-Table Query—>http://www.gcflearnfree.org/access2013/11

More Query Design Options—>http://www.gcflearnfree.org/access2013/12

Forms:

Forms are an input method for users to add content to their tables. For the DIY Data Analyst, a form might be used to quickly enter data not easily retrieved from a source into a Table for later analysis or reporting. Ex: Tracking Adhoc feedback on employees received. Forms can also be used to Ask the user for input and then execute – such as prompt for Date variables and then run a report.

To Create a Form is quite simple – the Form Wizard makes it very straightforward.

image

*Note: If you create a Form on a Linked Table the Data is Read-Only

Additional resources on Forms:

Form Creation—>http://www.gcflearnfree.org/access2013/16

Formatting Forms—>http://www.gcflearnfree.org/access2013/17

Subforms—>http://www.tutorialspoint.com/listtutorials/ms-access/forms/1

Access Reports:

Access reports are exactly what they sound like – reports. These are used to create canned reports for your data which can be regenerated at any time to represent refreshed data.

Reports can be very simple or very complex; reports can run on click or prompt users for parameters first (ex: Date values, Employee or Country); reports can group and summarize data or give a straightforward list.

Access offers a Report Wizard to get you started. The following screen shows a simple report created via the Wizard. Everything on this report can be customized and formatted to your own preference.

image

Additional Resources on Reports:

Creating Reports—>http://www.gcflearnfree.org/access2013/13

Advanced Report Options—>http://www.gcflearnfree.org/access2013/14

Adding Parameters—>http://www.gcflearnfree.org/access2013/20


If interested in additional learning on Microsoft Access, the internet is not short on tutorials and searching for a specific topic will often return a YouTube video or Web tutorial on the topic. I also highly recommend looking into an online education site such as Lynda.com if you are looking for more comprehensive approach to learning Access.