Setting up an ODBC Data Connection in Microsoft Windows

Someday you may find yourself needing to set up an ODBC data connection in Windows.  Depending on your reporting tool and the database you need to access, on ODBC connection may be required to connect your database to the reporting tool.  This can feel overwhelming, but it is actually straightforward and only a few steps.

In Microsoft Windows, you need to open the Control Panel.  Depending on your version of Windows, there are different ways to do that.   In Windows 8, you will find it under Settings in the right-side menu.

Once your Control Panel is Open, double-click on Administrator Tools.  

In the Administrator Tools window, you will see “ODBC Data Sources”.  If you are using later versions of Windows, you will have to choose either 32 bit or 64 bit.  Double-click to choose the one you need.

In the ODBC Data Source Administrator window, click the Add button.

Next, you will see a list of available drivers.  In this case, this PC has only one driver available, for SQL Server.  You will want to select the driver that matches the type of data you need to access.  Select the driver, and click Finish.

Next you will need to tell Windows where to find the data.  The name of the data source should match the name of the database in most cases.  Select the server from the dropdown list.  Click Next.

If you have a single-sign-on using Windows authentication to access the data, select use Windows.  If not, select  authentication specifically for that database and enter the User ID and Password below.  Some databases may not require authentication at all.  Click Next

Clicking next will test the connection and will let you know if any errors occur.  The next step is to use your new ODBC connection in your software.  If you have any issues going forward, come back to the ODBC Data Source Administrator in Control Panel, select the source and choose Configure to modify the settings.

Wishing you happy database reporting!!

Why Don't They Add Up? Understanding How Web Metrics Work Across Time Periods

It’s a common question, really.  Inevitably someone will walk up to you and say, “There is something wrong with your reporting.  When I add up your daily visitors they do not equal the weekly visitors you are reporting.  Then, I add up the monthly visitors and they do not equal the yearly visitors.  Why are you underreporting?”

Of course, you are not underreporting at all, and Visitors is not the only metric impacted by this phenomenon.  This is a great time to grab a white board and explain.  

Web analytics tools gather data on web activity 24/7 in a constant stream.  This stream is then portioned into time buckets, for example: daily, weekly, monthly, quarterly and yearly.

To do this, the data is cut at the time cutoffs for each bucket and then totaled within the bucket to calculate metrics like: hourly visitors, daily visitors, weekly visitors, monthly visitors, quarterly visitors and yearly visitors. It is the cutting that could place a single visitor into two time buckets at the same time, which would double-count if the two time periods were added together, and when the data is uncut, the single visitor is counted only once.

The best way to explain is to illustrate with an example.  It is Saturday, June 30th at 11:50PM and I visit karenlynnvincent.com.  While I am there, I read the About Karen Vincent page and 3 of her blog entries, for a total of 4 pages.  In the middle of the last entry I am reading, I notice that it is now Sunday, July 1 at 12:03AM.  I shut down my computer and go to sleep.

Yearly Reports:  I am 1 Visitor in 1 Visit with 4 Page Views.  Yearly reports would reflect these metrics accurately.

Quarterly Reports:  The quarterly report would split my session at 12:00AM, July 1. This means that the Q2 Quarterly Report would show 1 Visitor, 1 Visit and 4 Page Views.  The Q3 Report would show 1 Visitor, 1 Visit, 1 Page View.  These reports are accurate for activity within each quarter, but would over-inflate what happened if the reports were added together.

Monthly Reports:  The monthly report would also split my session at 12:00AM, July 1.  The June report would show 1 Visitor, 1 Visit and 4 Page Views.  The July report would show 1 Visitor, 1 Visit, 1 Page View.  While the monthly reports accurately show web activity for each month, but when added together they would over-count activity.

Weekly Reports:  The weekly reports can be set to bucket weeks Sunday through Saturday or Monday through Sunday.  If bucketed Sunday through Saturday, one week would carry 1 Visitor, 1 Visit and 4 Page Views and the next week would have 1 Visitor, 1 Visit, and 1 Page View.  If bucketed Monday through Sunday, the report would keep the session together and would accurately reflect 1 Visitor in 1 Visit with 4 Page Views.

Daily Reports: Daily reports split at 12:00AM.  This means that Saturday would hold 1 Visitor, 1 Visit and 4 Page Views and Sunday would hold 1 Visitor, 1 Visit, and 1 Page View.  

Each report is accurate for its time period, but would provide inaccurate data if data for those time periods were to be added together manually in a spreadsheet.  To safeguard the accuracy of the reports, be sure that reporting you do for any time period is based on reports from your Web Analytics software for that time period, and that no manual adding is done.  Weekly reports should be based on Weekly blocks, Monthly on Monthly Blocks, etc to accurately reflect activity

Tell those who point out the difference to you that they are very clever to have found one of the lost secrets of data aggregation: that when it comes to aggregating, the whole is usually NOT the sum of its parts.  Encourage them to stop adding and instead pull fresh and accurate data for each time period they need.  This will provide a an accurate picture of activity on the site.

Accurate Reporting for Crime Analysis: Five Key Considerations

There is no way any vendor can predict every request for information that might come across every crime analyst’s desk. So many times the information we need is not available in standard reports in CAD or RMS.  When this happens, we can use the tools we have available on hand to create our own reports.

To do this, we need to have access to CAD and RMS databases and some knowledge of how that data is put together.  Below are five key considerations for creating accurate reports.

1. Choosing the Right Data Source

You have a general idea what your report should look like, maybe even a picture in your head.  Now you must figure out where you will find your information.  Where does the information you seek exist?  Is it in your CAD system?  Do you need a report based on RMS information?  Is it in a Microsoft Access database?  Identifying your data source is the first step toward having the information you need.  

For instance, I may know that my report needs to list every burglary that happened this quarter.  It needs to show: report number, report date, penal code classification, reporting district, location, and property taken.

Now that I have identified what I need exactly, I need to know where I can get this information.  My Information Systems or IT department has set up an Access database for me that stores RMS information.  It is updated every morning at 5:00am.  This will be my data source.

Crystal Reports is able to connect to databases (ODBC and SQL), Queries and Dictionaries.  Oftentimes, Microsoft Access and Excel will also connect.  If your Information Systems, Information Technology or Technology Section will not let you connect directly to your CAD or RMS, they may be willing to set up a data dump on another server or create a Dictionary for you.  Your IS/IT/TS should be willing to create an option that will work for you.

2. Choosing the Right Reporting Tool

I have already mentioned Crystal Reports, which is a very versatile choice for most reports.  Some police departments, dispatch centers or regional agencies may use Humingbird or Cognos.  Usually, the type of tool is dependent on what licenses the organization has at hand and what can be easily installed in time.  

It is often possible to bypass dedicated reporting tools for more commonly installed Microsft Access or Excel.  Choice of tool is very important when thinking about the audience and how you will distribute your report.  For example, there is widespread use and basic knowledge of Excel, whereas people in general are less comfortable with Access.  If you need to create are report that is interactive for the user, Excel is typically a good choice.  Crystal Reports can be exported in a variety of formats, including PDF and XLS, but it can also be viewed in a free Crystal Reports Viewer that let’s the user drill down and interact with the report in the Crystal Reports format.

3. Understanding the Table Relationships

Even if you have a connection to your CAD or RMS system, how can you tell where to find your data?  You must find a way to understand how the tables are set up.  Your IS/IT/TS department should be able to provide you with descriptive information about where to find the data you need.

You must learn how the tables are related to one another.  For instance, each case may have multiple subjects.  There would be a case table listing the cases and a subject table listing subjects with a case number identifier.  The tables would be related by case number so that you would be allowed to view the subjects associated with each case.

In my case, I know that the report number, report date, penal code, reporting district and location are in the Main table, but the property taken is in the Property table.  The Main table and the Property table are linked together in a one-to-many relationship by their common Report Number field.  This means that each single report may have many items listed in the Property table.

When you bring the tables into Crystal Reports or Microsoft Access, you will need to create a Join for them based on report number.  There are several Join Types to choose from: Equal or Inner Join, Left Outer Join, Right Outer Join and Full Outer Join.  You can read about Join Types in this article here.

4. Choosing the Right Summaries

Sometimes your report is a simple list report showing all events, and other times you need to include totals.  When you total or summarize your data, you can run the risk of getting unexpected results that could unintentionally mislead you. 

Count is important if you want to know the number of events, but count can mean multiple things depending on the linking you have in your report and what field you are counting.  For example, if you choose a field that always has a value, like incident number or report number, you will count 1 per each record, but if you choose a field where missing values are possible for some records, you may miscount inadvertently.  Also, whenever you have a link in place, especially with a one-to-many relationship, if you use Count of incident number, you will get a full count of records across both tables and not a count of incidents.  If you want a count of incidents or reports, it’s best to switch to a Distinct Count in Crystal Reports.  

Count and distinct count are managed differently in the reporting tools to it is important to test thoroughly to understand what you are counting in the tool you are using and to make sure that the results make sense to you.

If you take my Crystal Reports for Law Enforcement class, you will walk through many examples of how these types differ from each other and change depending on the groups you have created in your report and where you physically place the summary.

5. Checking the Results

Examining the report carefully when you finish is critical to the accuracy.  The records you see and the summaries displayed should make sense to you based on your knowledge of both the data and the way the department operates.  If anything looks “off” in any way, it only takes a few minutes to check some records in RMS or CAD to be sure what you are seeing is worth believing.  

Sending out a report that hasn’t been checked is dangerous because people will use these numbers to make operational decisions and plan budgets for the future.  So getting the wrong information from reports could seriously hurt the organization.  Also, sending out reports without checking accuracy can damage the reputation of the Crime Analysis Unit.  To succeed, you need Administration, Investigations and Patrol to trust in you and the information you provide.  

A small amount of time is all that it takes to ensure that reports are accurate and ready for action.   

Joining Data from Multiple Tables: Link Types Explained

Relational databases store data efficiently across many tables related to each other by common fields or columns. Reporting on relational databases requires some knowledge about those relationships and columns in order to link tables together in a way that will give you accurate results. Here are a few examples of 1:many relationships:

•    1 Sales Professional is authorized to sell many products 
•    1 Company has many contacts in different departments
•    1 police or fire call has many units responding

There are several tools available for reporting on relational databases.  Regardless of the reporting tool, you need to first understand the way the tables are set up and what common field connects or joins the information in those tables together.  Once you know that, you can decide what style of link or join will meet your needs.

For the purpose of explaining, I will describe two tables the “Main | Left” table and the “Many | Right” table and have designated examples of these in scenarios below.

Inner Join

Inner Joins are often the default setting in the reporting tool.  This returns records from both tables only if they have a match in each table.  Inner joins are designed for occasions where you only care to see data where there are matches present in both tables.  Because it is default, it is the most commonly used, but it can cause miscounts if records are unexpectedly left out of the results when no match is found.  

Outer Joins are designed for occasions when you need to see data, even if it does not match anything in the other table.  We are given the option of the Left Outer Join, the Right Outer Join and the Full Outer Join.

Left Outer Join

The Left Outer Join displays all records from the left main table and only the matching records from the right many table. This is the safest method for joining two tables where full record counts are required, even if data is missing, and for this reason, it is also very commonly used. 

Right Outer Join

The Right Outer Join displays all records from the right table and only the matching records from the left table.  This style of linking is not as common.  In most relational databases, there isn’t much opportunity for a Many table to have a record without an associated Main table record.  For this reason, right outer join can be used to identify potential problems in the database.

Full Outer Join

The Full Outer Join will show all records from both tables regardless of a match. This style of linking is also not as common.  I have seen this used primarily in HR/Finance for error checking and reconciliation.  For example, it’s sometimes necessary to identify instances where there is a payroll record without an employee 401k contribution or a employee 401k contribution without an accompanying payroll record.  This form of join lets you quickly identify problem records and make corrections.

In Summary

When in a hurry, the safest option is always the left outer join where the left table is the main table and the right table is the many table.   This will ensure that you have all of your main records present plus the associated matching records from the many table.  When you need to find errors, try a full outer join to see where matches are missing.  

There’s no harm in linking different ways and looking at results until you find the right join strategy for your tables.  The most important ingredient of any report is data familiarity, and experimenting with link types can help you learn more about the way your data is setup.  

Data and Measurement Considerations for Product Marketing

Creating great supporting documentation requires many decisions, like pdf or html, public or gated, and internal or external.  These decisions affect what is measured and how.  Some require preplanning and some are simply built in.

PDF v HTML

You are tasked with creating online tutorials.  Do you want users to view the tutorial on the website in HTML or do you want them to download the tutorials as a PDF?  Often site structure, templates, engineering or development resources and time constraints will impact and decide for you which option you choose.  The choice made will impact how you measure the success of the content.

For HTML web page tutorials measurement is the same as it would be for any web page.  You can gather metrics on Unique Visitors, Visits or Sessions, Page Views, Repeat Visitors, time on page, shares, etc.  HTML tutorials are often bookmarked, bringing visitors pack to the site repeatedly and creating opportunities for onsite advertising later.

The PDF format limits available KPIs.  You will have the standard HTML metrics, listed above, for the page that the PDF is linked from and the number of times the PDF was downloaded.  PDFs are great for when you want to give the visitor something they can hold on to, save locally or print in a nice branded format.  

Public v Gated

You are tasked with creating a technical asset.  Do you make it Public and generally available on the website with other web content or will it be Gated and available only after the visitor fills out a lead capture form?  The general rule of thumb is that highly rare, sensitive or valuable content (analyst papers, comprehensive research, etc) should be gated so that the visitor is providing the company with value in exchange for the value they are receiving.  Gating or not gating content will impact options for measuring success.

Public html or PDF content is available just like any other page on the website and is measured with standard metrics, like Unique Visitors, Visits or Sessions, Page Views, Repeat Visitors, time on page, shares, PDF downloads, etc.  

Gated content is provided only after a form-fill is completed.  Measurement acts more like a funnel.  In order to measure this properly, you will need to understand the full process, so ask these questions:

•    Are there any online campaigns for this?
•    What is the URL of the form page?
•    Where is the URL of the file?
•    What happens when the user submits the form?  

o    Is the file opened in the browser immediately?

o    Is the user emailed a link directly to the file?

o    Is the user emailed a link to a web page to download the file?

For gated content, you want to collect leads and promote it with online campaigns. In order to measure what is working best, you will need to add a campaign parameter to the link. The parameter content you will need for your links depends on what analytics software you have installed on the site. 

You can track the page metrics for the form page without any additional tracking, but tracking completions will require either a confirmation page or a snippet of code on the submit button.  

Whenever possible, I request that an HTML confirmation page be created.  Since this page is only displayed when a form is submitted, it is usually a fairly accurate measure of form completions.  

When a popup is displayed instead of a page, then you will need to place a snippet of code on the form submit button in order to know how many forms were submitted.  The type of button code you will need will depend on what analytics software is installed on the website.  You can ask your web analytics team and your IT developers or engineers for assistance.

As a best practice, I request both a confirmation page and code on the button.  

If you know the URL of the file, you can track the downloads of the file in the web analytics software.  With this plus implementing the paragraphs above, you will know referring campaign, form arrivals, form completions and file downloads.  This is a solid basic funnel.

What happens when the user submits the form will determine how detailed your measurements can be.  
•    If the file is opened immediately and you have completed work in the paragraphs above, you have done all that you can do and you will have the solid basic funnel described above.  
•    If the user is emailed a link directly to the file and you have completed the work in the paragraphs above, you have done all that you can do and you will have the solid basic funnel described above.  
•    If the user is emailed a link to a page to download the file, you have a few more options.  You will be able to track views of this intermediary page and downloads of the file.  If you have control over the link in the email, you can tag that link with a URL parameter that lets you see how many clickthroughs came from that email.  This option adds a bit more detail to the basic funnel and helps you understand where the process may break down.

Gating or not gating drastically changes what is measured and the amount of effort put in to measurement.  When deciding to gate, please allow yourself a little time to work out the details of the tracking codes you will need.  A world of helpful information will be missing if you don’t.

Internal v External

Some content is made for telling the world about your product and some is messaging only for internal sales and marketing staff.  External content is easy enough to track by using standard metrics discussed above.  Internal content can also be tracked, but it may take a little more work and will depend on what tracking mechanisms are available where the internal content is stored.  If it’s linked from HTML on an Internet website or wiki, it’s possible to have web analytics installed there to provide familiar metrics.  Also, if a content management tool is used, useful stats may be available there as well.

Summary

Product marketing managers have many decisions to make when delivering content, and the types of performance metrics needed can help guide those decisions.  Having a good relationship with web analytics professionals in your company can help ensure that you will know how your assets are performing in any circumstance.

Product Managers: Building Good Measurement into your Web Applications

With so much focus on appearance and functionality, web portal data decisions can be easily overlooked at a time when performance metrics are more crucial than ever.  As more functionality is added, good data collection is needed for product management.

Product managers need to understand how the site is being used to inform the product roadmaps and order website changes as needed.  There are two different building blocks for measuring the web performance and both are needed to achieve a complete understanding of the online customer: web analytics and web portal database.

Web Analytics

Web analytics tells you about the online behavior of the customer by painting a picture of the most popular content and how a customer moves through the website to accomplish tasks.  There is a wealth of information in the basic analytics reports found in web analytics packages like Google Analytics, WebTrends,  Site Catalyst, and CoreMetrics.  Even more information on fall out from web form fills, clicking to off-site links, and other detailed task and funnel behavior can be tracked with additional planning and page-specific javascript tags or URL parameters.

Web analytics is fundamental to all web properties, but is also commonly overlooked during the development process.  In order to determine the impact of the redesigned property, getting the proper code in place early in the project is essential.  The basic requirement is a snippet of code often referred to as a base tag.  Depending on the analytics package and the company policies, this is either a direct copy/paste or include to the page header or page footer.  This code is the engine that communicates page activity to the analytics package.  For advanced reporting and specialty metrics, a web analytics professional can provide code to be included within the content of the page to provide additional performance metrics.

Getting the basics is easy.  Often it only requires mentioning that you want the tracking installed, and in some cases, it is standard and automatically included with no effort at all.  For this reason, tracking is often taken for granted, and taking web analytics for granted is a huge mistake.  I have seen many cases at companies large and small where sites have gone live with no tracking because it wasn't mentioned in the business requirements. Without metrics, the product manager was not able to demonstrate the success of the product.

Add tracking requirements to the standard requirements template to be used in every web project and open discussions with web analytics professionals in your company at the start of the project.  This will ensure you can avoid KPI pitfalls when work is complete.

Portal Database

Make the most of the web portal database behind the application, used to deliver information to the page.  These databases run the application functionality of website, processing transactions, recording changes to user profiles, error messages delivered, logins, etc.  Because of this, they hold a wealth of information on how the site is being used.  While web analytics delivers web content and behavior details, the web portal database delivers information on adoption and usage at a transaction level.

The types of metrics you can expect to gain from the web portal database include: percent of users logged in in the last 90 days, login frequency, login duration, average transactions per person, total transactions, revenue, etc.   

The most common issue is overlooking the requirements for the performance indicators (KPIs) during the design and development of the database.  To be sure that the portal database will deliver the metrics you need, take an hour at the start of a project to brainstorm all possible performance indicators that my be needed in the future and discuss with the IT Architect and DBA on our team.  It is much easier to include these in the database design at the start than it is to make a change along the way.

Summary

The key to success is timing a three-step process: (1) consider the data you will need at the beginning of the redesign, (2) collect product management requirements early and (3) coordinate with DBA and Web Analytics professionals throughout the course of the project.  Early and continual involvement will ensure you are collecting good data from the start and can report your success measure KPIs along the way.