cplysy

This article is rated as:

 

 

As a data analyst in the world of evaluation, much of my work at Three Hive involves combining similar datasets from multiple sources into one master file to analyze them in aggregate. For example, a recent project of ours involves combining patient experience data collected at two sites, one in the North and one in the South, to analyze together. Although both sites use the same survey questions, each site asks the questions in a different order and store their data slightly differently, which means merging these datasets takes a few extra steps. While I don’t believe there is a wrong way to merge datasets in situations like this, there are certain steps that I like to take to set myself up for a straight-forward analysis process. In this article, I’ll walk you through the steps I take when working through a task like this using a fake dataset as an example.

1) Before you start…

• Pick an analysis program. The analysis program you use will impact some of the decisions you need to make about your data, so it is good to decide what you will be using as early in the process as possible! The program you use for data prep doesn’t need to be the same program that you conduct your analysis in, but it should be one that you are familiar with. For most of my analyses, Excel is usually powerful enough to do the trick, but you can choose whichever program you are most comfortable with as long as it allows you to explore the data and make changes where needed.

• Start a ‘data diary’. This step might not be necessary if your data set is small and simple, but I find that it’s helpful to record the steps I take in cleaning so they can be easily replicated in the future or adjusted if I realize I’ve made a mistake (which happens more often than I’d like to admit!). Lately, I have been using a tab on the project’s OneNote in our online work environment, but you can use any word-processing software that you prefer for this. In the past I have used physical notebooks as a data diary but found that this made it much harder to collaborate and share my process with the rest of my team. Whenever possible, I suggest using online documents that you can share across your notes with the project team!

  • Within your data diary, start preparing a data dictionary – this is where you can take notes about the variables in each data set as you explore your data. To learn more, check out our article on Data Dictionaries!

2) Explore your data (and take notes)

• Explore your individual datasets. Work through each data set individually to understand what data was collected, and how it is displayed in your data processing software. I find it useful to create a list of the main variables in each dataset and write a short blurb about each in my data diary. This gives me a good overview of the data I will be working with and allows for easy comparisons in the next step of the process.

• Compare similarities and differences. After exploring and making notes on each dataset individually, I compare my notes to find similarities and differences between them. In the project that involved combining data from the North and South sites, I noticed that both sites had the same main variables but that they were in a different order, and that the North site collected one extra piece of information than the South site did. For this project, my data diary and dictionary looked something like this after exploring and comparing the data:

3) Prepare your original data. When I combine data in Excel, I like to copy and paste each individual dataset into a separate tab of an Excel workbook, like this:

Next, I assign identifiers to the individual data sets. This will allow me to link the responses from the master dataset to the original datasets if I need to make changes in the future. To do this, I create two new variables for each original dataset by inserting columns in Excel by right-clicking on the column header and selecting ‘Insert’ from the pop-up menu.

After inserting two new columns to the left, I will give them the titles of “ID” and “Source”

• ID: This variable will assign a unique numeric value for each observation (row) of data within each original dataset. To do this quickly in Excel, you can type 1 in cell A2 and 2 in cell A3, then select both A2 and A3 and double click on the small green triangle at the bottom left of your selection to autofill the rest of the rows, like this:

Source: This variable will be used to differentiate each observation (row) of data in the master dataset depending on which site it came from. To do this, I will label all the data in the North site dataset as “North” and all the data in the South dataset as “South”. When we eventually combine all the data into one mater dataset, this will allow me to easily determine which site each piece of data came from.

Repeat this process for each individual dataset you have, being sure to assign a different ‘Source’ name for each dataset. Each dataset should now look something like this:

4) Prepare your master dataset. Once I have gone through and compared the variables in each data set to determine which can be combined and which cannot, I create a new worksheet using the ‘+’ sign to the right of my workbook tabs to lay out the variables that will be in my master dataset.

At this point, there are a few decisions to make!

• In our example, the North and South sites organized their variables in different orders. I like the way the North site ordered their variables, so I will use their ordering, but you can choose what works best for you.

• The North site also collected information on the reason for each respondent’s visit to their doctor, which the South site did not collect. You may choose to include or exclude variables that aren’t present in all datasets depending on what types of questions you are hoping to answer. In this case, it could be useful to see if wait times for patients varied depending on the reason for their visit, so I will include the ‘Reason for Visit’ variable even though the South site did not collect that information.

Once you have decided which variables you will include in your master dataset, and in which order, you can start typing out the variable names as headings in row 1 of your master dataset worksheet. Your workbook should now look something like this:

Convert your master dataset outline into a table for easy sorting for our next step by selecting the headers and one row down, clicking ‘Insert’, ‘Table’, checking the ‘My table has headers’ box (if it isn’t already checked), and ‘Ok’.

5) Combine your data. All that’s left now is to combine the data from our original North and South datasets into our master dataset! To start, copy all the ID and Source values from each original dataset and paste it into the ID and Source columns of the master dataset. The table you made in the last step will automatically update to add in all the rows of data you have just pasted.

For the rest of the data, the way you choose to add it to your new master data table will depend largely on the size and complexity of the dataset.

• For small datasets like in our example, it can be as easy as copying data from one variable at a time and pasting it into the corresponding column in our master dataset, double-checking that you are pasting the data into the correct column and rows each time.

• Since the South site didn’t include a ‘Reason for Visit’ variable, I added in a value of ‘Not asked’ which will be more helpful to me during analysis than leaving those fields blank.

*Tip: If you’re like me and tend to get lost in your head when working through repetitive tasks like this only to question what you did later on, I have found it helpful to voice record myself narrating each step as I do it. Once I have finished the copy-and-pasting of data, I replay my voice recording and type out what I did into my data diary. This way, I never have to question if I *actually* pasted all the data from all the sources I needed to!

• For larger or more complex datasets, you may need to use a function like XLOOKUP or the PowerQuery tool to avoid spending too many hours copying and pasting data across sheets. Keep an eye out on Eval Academy for our upcoming article on PowerQuery for beginners to learn more! In the meantime, here is a useful article on the XLOOKUP function for multiple criteria by MyExcelOnline: https://www.myexcelonline.com/blog/xlookup-with-multiple-criteria/

6. You’re ready to clean! Once all the relevant data has been copied and pasted from all source datasets into your master dataset, you are ready for data cleaning. Check out our Data Cleaning Toolbox article for guidance on cleaning your new master dataset!

It can be intimidating to try combining datasets that aren’t perfectly aligned, but by taking some extra time at the beginning to understand the data you have and the result you are aiming for, you can create a clear road map for yourself and your team! Not only can this help to make the process much smoother, but it may also inspire new ways of analyzing and describing the data.

---

Let us know how you find this process in the comments below!

This article is rated as:

 

 

Evaluations of any size tend to need to adhere to budgets, whether for financial or human resourcing constraints. There are certain pitfalls that can quickly derail your budget. Where possible, it’s important to clarify these during project scoping (check out our article here). This article will guide you through some of the most common budget pitfalls to help you plan and support you to stay on budget throughout your evaluation.

Frequent meetings.

Meetings are part of any project and are important for building relationships, understanding context, getting buy-in, and gathering or sharing information. However, meetings, especially over a long period of time can quickly eat away at your budget. So how can you tell if you need to budget more money or time for meetings? Typically, the early stages of an evaluation for a simple project will need 2-6 meetings to set up the evaluation and develop data collection tools, then often monthly or quarterly touch base meetings. So, what clues suggest that meetings might take up more of your budget? Some things to watch out for include: 

• Many people or organizations involved

• Participatory evaluation methods

• Complex or rapidly changing projects

  • Or those with lots of detail or context

• Projects that continue over a long period of time

• Meetings that focus on the minutia, where lots of discussion is required, often go off track. This is not necessarily a bad thing, but if you notice a team likes to delve into deep discussions in meetings, they might take more time or you might need more meetings than you originally thought.

---

Travel.

While the shift to virtual meetings and a greater reliance on virtual tools can reduce your travel budget, there are certain things that are easier in person. Travel can quickly eat up your budget if you haven’t accounted for it fully. Mileage or car rentals, insurance, per diems, hotels, and even just the time to travel to a meeting, chat afterwards, and travel back can add up quickly. Typically for a virtual meeting, we budget time for 15+ min of prep or wrap up on either end of a meeting. Adding in travel and time to find parking can easily add an hour or more to that estimate.

---

Participant honorariums.

Valuing your participants’ time and knowledge is important, but it can be tricky to get it right. You may want to consider compensating participants for providing guidance, their time in meetings, answering surveys, participating in interviews or focus groups, or supporting data analyses. Make sure you check out what the going rate is for providing incentives or thank you’s to participants and who is responsible for footing that bill.

To learn more, check out our article: Incentives for participation in evaluation

---

Administrative data analysis.

Administrative data can be a real doozy. Evaluations may rely on situations where we are using data collected by others or working within data collection systems (like electronic medical records, for example) where we don’t have much influence on how the data are collected or what the data export looks like. It’s tricky to estimate how long it will take to piece together disjointed data. Entering, cleaning, and understanding these data can quickly eat up a lot of time without you noticing.

To learn more, check out our article: New Checklist: Information request checklist

---

Surprises and re-work.

Ah, yes, the unpredictable joys of surprises, or re-work. In addition to data analysis taking up extra time, surprises like incomplete data sets or extra data that no one told you about can quickly throw your budget off track. Assumptions made during the data collection and analysis processes that aren’t clarified with the project team can lead you to re-do some of your analyses or sensemaking. And sometimes, key pieces of information can get lost or forgotten in translation, requiring you to re-do work that you’ve already done. Another surprise that can impact your budget is when timelines are condensed; in these cases, you may need to use more evaluators to get the work done quickly. More evaluators mean more project management time, including getting everyone up to speed and keeping them on the same page.

---

Endless report edits.

You know the ones. Often this happens when many stakeholder groups are involved, or the report sits for a long time before people provide feedback. Sometimes your report can get stuck in editing wars as people debate the use of the Oxford comma or wordsmith paragraphs ad infinitum.

---

Scope Creep.

When I think about scope creep, I imagine some nefarious project manager demanding more work that’s outside of our contract or scope of agreement; however, scope creep often happens in a much more subtle and less evil way. Many times, scope creep just sort of happens, without either party necessarily realizing they are drifting outside the bounds of their agreement. Maybe you only budgeted for one report, but then the team realizes there’s an opportunity to present to leadership who will respond best to something tailored specifically to their interests. Or perhaps the project team realizes that the staff survey they’ve been running for years and forgot to mention when you were planning the evaluation might hold insights that could support your evaluation.

To learn more, check out our article: Scope Creep: When to indulge it and when to avoid it

---

At the end of the day, estimating an evaluation budget and then monitoring if you are on track are important parts of managing an evaluation. We’ve found that budget management can be more difficult for the smaller, “simple” projects as a lot of the items that can quickly derail your budget are project management issues, which are the same for small projects as they are for large ones. Where possible, it’s nice to add in a ‘contingencies’ line to a budget to give the evaluation some breathing room. By keeping an eye out for common issues that can derail your budget, you will be better prepared to identify and manage these issues.

If you’re interested in learning more about budgeting for an evaluation, our Program Evaluation for Program Managers course contains a handy budget template, along with other helpful evaluation planning and implementation knowledge.

This article is rated as:

 

 

At Eval Academy, we’ve discussed the importance of ethical practice when collecting qualitative data including obtaining consent and maintaining confidentiality and anonymity. Many of our previous conversations have focused on the need to ensure the safety and well-being of participants.

In this article, we highlight the importance of also ensuring interviewer safety to make the interview experience effective for collecting data and a positive experience for everyone involved! Here are our 5 tips for safeguarding the interviewer:

1.     Conduct a pre-interview risk assessment.

Before starting your interviews, it’s important to assess potential psychological risks associated with the discussion topic or participant characteristics. Take some time to evaluate factors such as sensitive topics, the possibility for emotional distress, or power dynamics that might arise during the interview process. This assessment will help you to identify potential challenges that might occur and provide you with the opportunity to develop strategies to address them. If you work as part of a team, engage them in a discussion about potential psychological concerns you have, including any personal impacts or triggering situations you might find yourself in.

2.     Establish clear expectations and guidelines.

The purpose, scope, and expectations of the interview process should be clearly communicated to participants before they consent to take part. This includes sharing information on the topics that will be covered. As well as knowing the purpose of the interviews, participants should be fully aware of their rights, confidentiality measures, and the voluntary nature of the interviews. Sharing documentation beforehand in a way that is appropriate to your participants will manage their expectations for the session and allow you to establish guidelines for respectful communication, confidentiality, and appropriate behaviour throughout the interview process.

3.     Conduct the interview in a safe and secure location.

Prior to the interview, take time to assess the physical risk and safety of the interview location. Choose a location that ensures the safety and privacy of both you, the interviewer, and the participant. If completing the interviews virtually, ensure you have access to a quiet and private location where you can easily access any documentation and support systems you might need. For virtual interviews, encourage the participant to take the call in a location where they can have some privacy. If conducting in-person interviews, select a neutral and comfortable space that allows for confidential conversations.

If you are not able to select the location of the interview, assess the context of the location to examine how much you know about where you will be conducting the interview and other safeguards that can be taken. This includes ensuring there is an available staff member to check in on the interview periodically, ensuring you’re aware of exit points and doorways, assessing the space for objects that could be used as weapons, and knowing how to navigate the neighbourhood safely. During the interview itself, be prepared to take note of what the participant is wearing and how they’re positioning themselves, as well as anyone else who is at the location.

4.     Have an emergency plan in place.

Based on your pre-interview risk assessment, you should prepare for potential emergencies or distressing situations.

An emergency plan for the interviewer: Identify your own emergency contact, such as the project leader. It can be useful to make a list of potential scenarios that might arise from the interviews and develop an action plan of how you would address them. Be prepared to respond calmly and compassionately in distressing situations.

An emergency plan for participants: Develop an emergency plan that includes contact information for relevant authorities or support services for participants. Familiarize yourself with appropriate resources such as helplines or counselling services and be prepared to provide this information to participants if they require assistance. Printing this information on handouts can ensure it is easily accessible to participants.

5.     Establish a debrief and support system.

Don’t be afraid to ask for support! Recognize that conducting interviews, particularly on sensitive topics, can affect the emotional well-being of both interviewers and participants. Establish a debriefing process with a colleague or a mental health professional to create space to discuss any challenges, emotional impact, or concerns.

---

As interviewers, it’s our responsibility to prioritize the safety, well-being, and ethical inclusion of participants. By conducting a pre-interview assessment, setting clear expectations, assessing the physical risk and safety of the interview location, having an emergency plan in place and knowing how to use it, and establishing support systems, we can ensure the interview process is respectful, ethical, and valuable for everyone involved.

For more information and resources on conducting interviews, check out:

• How to conduct interviews

• How to transcribe interviews like a pro

• How to use Calendly to schedule interviews like a pro

• Tips for conducting interviews

• Standard interview templates bundle

This article is rated as:

 

 

Eval Academy just released a new infographic, “10 tips for designing quality reports!”

Many of us have come across reports that feel cramped, unengaging, and let’s face it, a little bit boring! Dedicating some time to designing your report before writing can significantly affect its overall quality. By taking some time to focus on design, you can create a report that is not only refined and professional, but also visually appealing.

Back in November 2022, we shared five elements to include in your reporting style guide. In this article, I dive a little deeper as I  reflect on my recent experience of designing an engaging and visually appealing report for a Three Hive Consulting client. You can download the infographic here – I recommend printing it and keeping it in sight to help you keep design in mind when writing your next report!

Before we jump into it, I recommend checking with your client or organization to see if they have set branding, including fonts and colours, that they would like you to use in reporting. If so, integrate that into each of the following steps.

1)    Pick a colour scheme and stick to it.

• This includes colouring for fonts, quotes, visuals, graphics, and cover and end pages.

• Consider the content of the report and the tone you want to portray. How do your chosen colours support that message?

• Use a colour wheel to find complimentary colours.

• Stick to a limited colour palette. I’ve found that 3 different colours tend to work best.

• Consider the accessibility of your colour palette. Is it colour-blind friendly? Or do the colours have cultural significance you should be aware of? There are a number of tools available online that provide further information on colour-blind friendly palettes and how to choose colours:

  • Colouring for Colourblindness

  • The Best Charts for Colourblind Viewers

  • Colour Brewer

• Microsoft also has tools available to help you pick colours from any application you have running and copy them in a configurable format to your clipboard! You can learn more about Windows PowerToys Colour Picker here.

2) Use consistent fonts.

• This includes consistent font styles, sizes, and colours from your colour scheme.

• Choose easy-to-read font styles such as Serif (e.g., Times New Roman, Georgia, Cambria) and Sans Serif (e.g., Arial, Helvetica, Calibri).

• For body text, avoid using too many styles such as bold, italicized, or underlined which can make a report look cluttered. See below for an example:

3)    Use clear sections that tell a story.

• This includes using page breaks, headings, and sub-headings to make it easier for readers to find information.

  • Headings should be clear and descriptive to help readers quickly understand what each section is about and how it relates to the overall story.

  • Use different fonts to distinguish between headings, sub-headings, and content.

    • Pick one font for headings. This should be large and easily distinguished when skimming. Using a block of colour can help people quickly identify a high-level heading.

    • Pick one font for the body text.

      • Vary the font size, bolding, and italics in a consistent manner to delineate different heading levels.

• Clear sections help to organize information in a logical way to improve readability.

• Consider using a table of contents with hyperlinks to make finding sections that bit more efficient!

4)    Use bullet points and numbered lists.

• This can include using icons within lists to make them more eye-catching.

• Bullet points and lists help to highlight important points, improve how information is structured, and emphasize information.

• They can also help to make complex information easier to read and understand.

• Bullet points and lists can save space in a report, whilst also adding more white space!

5)    Include visuals.

• Charts, graphs, and other visual aids can help make data easier to understand and can make the report more interesting. For example, take a look at our article 3 Easy Ways to Quantify Your Qualitative Data.

• Choose the right type of visual aid depending on the type of information you’re presenting as mentioned in our articles 7 Tips for Better Data Visualizations and Dial Down Your Data.

• Make sure visuals are high-quality, simple, and formatted consistently – for more information, take a look at our article Chart Templates: The Time Saver You Should Be Using.

• But don’t rely solely on visual aids, they’re there to support the information presented in the report!

6)    Embrace the white space.

• White space, also known as negative space, refers to the blank or empty areas in a report.

• The trick is not to cram too much information onto one page! Use white space to break up long paragraphs or chunks of text and create sections to make it easier for readers to scan and digest the information presented.

7)    Check your margins and spacing.

• Checking margins and spacing for consistency ensures visual appeal and readability. It also helps to avoid clutter and ensures the report stays aligned when printing and sharing.

8)    Utilize the grouping tool.

• The grouping tool in Microsoft applications allows you to group related objects together. This can be useful for organizing content in a logical and visually appealing way.

• Grouping allows for easier editing, more control over design, and can improve efficiency!

9)    Create templates and start early.

• Whether you’re creating your report in Microsoft Word or PowerPoint, before you even start writing I recommend creating a template that combines all of these elements! This will save you time when you’re writing, ensure consistency, and allow you to have greater control over the design.

10)    Proofread, again and again!

• Last but certainly not least, proofread your report multiple times! I like to proofread first for content, spelling, and grammar, and then again for formatting. Take a break between these different sessions to help you look at the document with fresh eyes.

• Ask a critical friend or colleague to proofread. No matter how thorough you are, there’s always that one typo that slips through the cracks!

---

For more information on report writing, check out the following Eval Academy links:

• Six hacks for renovating your evaluation report Part1: Take them on a journey 

• Part 2: Consistency is Cool

• Part 3: Practice Proximity

• Part 4: Make it pop!

• Part 5: Photo Love

• Part 6: Dial Down Your Data

What are your tips for designing quality reports? Share them in the comments below.