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.   

Why SMS is the Best Option in Emergencies

With the growth of the smart phone market, we are all dazzled by apps.  They are everywhere, they are shiny and fun, and there is an app for everything.  Apps communicate too, allowing for push messages, instant messages (IMs) and data sharing, and they do this over a data connection.  Data connection usage is skyrocketing. According to a study conducted by Cisco Systems: “Last year’s mobile data traffic was nearly 18 times the size of the entire global Internet in 2000.”  

In a disaster, bandwidth fills quickly for phone and data connections with emergency communications.  Not only are the first responders connecting with each other, but also everyone else is trying to reach family and friends.  Before long, mobile communications grind to a halt.

This is where SMS can help.  SMS has all of the benefits of being mobile, with added advantages that it does not require a data connection, takes up little space on the network and still works when bandwidth is small.  This makes SMS highly effective for these circumstances.  

SMS is Mobile

As a personal example, my cell phone is always in my pocket or in my hand wherever I go.  If I am in a meeting, I cannot always answer a voice call, but I can easily glance at a text message and respond.  It’s really the fastest way to deliver information to me wherever I am.  Even better, when traditional phone wires are down, my mobile phone may be the only way to reach me, even when I am home.  In an emergency, when every second counts, there’s no better choice.

SMS is Effective

Text message read rate is exceptionally high.  In fact, 90% of all incoming text messages are read within 3 minutes. It’s so easy to take moment to glance at the phone and receive information when a message comes in.  SMS is both convenient and unobtrusive.  

SMS gets Response

Studies show that text messaging is more effective than voice or email in getting a response.  In fact SMS typically receives a response rate 6 to 8 times better than email and 43% of responses occur within the first 15 minutes.  For the quickest response, when seconds count, text messaging is the true winner.

SMS does not strain Resources

Text messages travel the mobile networks in the tiny space on top of voice calls.  Have you ever noticed that when networks are crowded and calls can’t get through, text messages can?  I’ve been in a number of situations where I have had little to no phone service, but I could still send and receive simple text messages.

In emergencies, simple is often best for communicating information and instructions.  The simple approach of SMS leaves the bandwidth available for first responders and other critical life-saving communications and ensures that text subscribers still receive all of the details they need.

There are times when there is an advantage to going back to the basics.  SMS works with every type of phone in low and high bandwidth is fast and easy to use.  In an emergency, there would be no better way to mobilize the community. 

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.

Single Page Visits: Is “Bounce Rate” Good or Bad?

A person comes to your website looks at one page and exits.  Whether this is good or bad depends entirely on your goals, and your goals will vary from page to page.  For this reason, using bounce-rate as a page-level metric can be helpful and as a site-wide measure can be misleading.  

Before we explore further, what do I mean by bounce?  A bounce occurs when a session ends after viewing only one page. It can be calculated in summary for the entire site by dividing single page visits by total visits or on the page level by dividing single page visits for that page by the total entry visits for that page.  Some analytics tools calculate bounce rate automatically, saving the time and trouble.

When is a High Bounce Rate is Good?

A high bounce rate is desirable when the goal of a page is to deliver information to the visitor, often to answer a specific question.  Once the answer is read, the visitor completes the task and does not need to view other pages.  A common page of this type is the Contact Us page where the visitor finds the phone number and accomplishes the task.  In this case, the faster you can help the visitor complete the task the better so seeing a bounce rate rise is representative of good performance on the page.  You may also see that the page is frequently bookmarked providing a strong repeat visitor base.

When is a Low Bounce Rate is Good?

On the other hand, a low bounce rate is desirable when the goal of a page is to generate leads, produce sales or drive engagement with content.   In this case, the goal of the page is to raise interest and drive interaction with the website so if the visitor moves on to another page on the site, then the page performed well.  For pages like these, driving toward a lower bounce rate is desirable.  

Recommendation

It is very helpful to put site-wide bounce rate to use as an index to pick up on any sharp movements with the introduction of new campaigns and new releases.  It can be a beacon when used internally and compared to its own past performance.

If you are able, define the goals on a page-by-page level and track the bounce rate for those pages.  This does not need to be done for the entire site, but it can be great for the handful of key pages.  If you find it is high when it should be low or low when it should be high, then you can begin to take steps with content and layout to help the page perform better.

Web Data Decisions for Auditing and Compliance: 4 Areas for Consideration

A good web application provides good experience on the front end for customers and the back end for web managers.  Project funding tends to focus on the front end experience primarily, but it’s really the back end that can break a business when something goes wrong.  

• A customer calls and claims they were given last year’s terms and conditions.  
• A product manager says IT uploaded the wrong PDF.  
• A customer says they never logged in to authorize that payment.  
• A customer says that the data they saw was not theirs.

Careful planning now will make auditing requests like these much less painful in the future.  

First, take steps to enhance security for your portal database.  Close all of the possible unauthorized ports of entry.  Ensure that firewalls are strong.  Check the latest security recommendations and do all that’s possible to meet each one.  Are there features to require additional authentication under circumstances where the user is out of his or her normal area or has failed some login attempts?

Now, check the design of the portal database to be sure that there are adequate auditing tables available.  How are transactions tracked? Is there a session detail record to view all activity during a session?  Is the user’s IP addresses from sessions recorded so that likelihood of identity can be considered?   A well-designed web portal database is an important part of auditing because it can include details of every transaction and activity by user, including text entered where necessary.  This is very helpful when a user calls into the call center with concerns about his or her account.

Next, prepare your Web Analytics software for auditing capabilities.  Work with your web analytics professional to pass an unintelligible user ID key to the web analytics software for logged in users and create a custom report to record pages and downloads by this user key.  This simple step is a godsend when something goes wrong, like a PDF or web page has an error in it and you need to quickly identify who has seen this content so that a correction can be made to that group of users.  This is extremely valuable from a risk management perspective.

Now, take an inventory of the tools you have.  Do you have any session recording capabilities?  It is great to have access to a tool that will allow you to replay any session in question to see exactly what pages were viewed and what was accomplished.  Most companies do not have access to these tools for their web properties, but tools like these can be very helpful for auditing, especially in industries that are strictly regulated.

After some preparation, answers to tough auditing questions become more routine.

• A customer calls and claims they were given last year’s terms and conditions.  Retrieve that customer’s encrypted ID, open the web analytics reporting and query which PDFs were viewed.  
• Then, a product manager says IT uploaded the wrong PDF.  In the web analytics package, query that PDF and view which customers, if any, saw the incorrect PDF so that the product manager can draft a letter to those customers.  
• Now, a customer says they never logged in to authorize that payment.  Use your web portal database to see details of that transaction, verify the session details to see if there was a chance the account was compromised.  
• If a customer says they saw data from another customer and you have a session recording tool, check the tool to see what happened.  If not, look into the web portal database session activity to see if any wires might have been crossed.

With careful planning and a few simple steps, you will be prepared for these and any other questions that come up in the future.