How to Reduce Reporting Time by Half in Under One Month

If you are reading this, you probably have a reporting process that takes too long, is inefficient, and doesn’t give you the insights you need.

  • You might be in charge of a team that is building these reports, and no matter what you try, it always takes longer than you expect to get reporting done.
  • You might be the one in charge of producing reports, and you’re tired of spending so much of your time manually updating spreadsheets and downloading csv files from numerous systems.
  • Or maybe you are the CEO and you need faster insight, but you can never seem to get it.

If any of these are the case, stay with me.

You Are not Alone

Even though technology for processing and reporting on data is getting better and better, there are a huge number of companies still trying to get reporting done with Excel and other manual methods. This leads to long hours, errors, and poor insight. No one wants to deal with a ballooning number of Excel files with arcane file names that no one can keep straight.

That’s a recipe for disaster.

So if you’ve outgrown Excel, and you need a better way, I’m going to lay out step by step how to automate away all of that headache.

I’ve been in the data space for over 10 years building data pipelines, and here at Data Crunch we do this kind of work for clients of all sizes. The process works. Here is one of our clients, that we trained how to properly use Tableau, that was able to reduce their reporting time by half (and that’s usually a modest gain):

Here are the seven steps you’ll want to go through to automate away your old, broken data process and put an automated data pipeline in place. The ROI on these projects is huge.

The seven steps are

  • Map out all of the steps currently being taken to create your reporting
  • Identify which manual steps can be automated
  • Identify appropriate technology and/or code to put in the place of your manual processes
  • Write up an automation plan and automation documentation
  • Build the automation and ensure it’s running without errors
  • Set up alerts and checkpoints to monitor the process going forward
  • Do an ROI analysis of your new data pipeline

 

Following these steps you can turn a manual process like this:

Manual Process Total Time to Completion: 7 Hours

Manual Flow Chart

To an automated like this:

Total Time to Completion: 30 minutes

automated flow chart

(1) Map out out all of the steps currently being taken to create your reporting

  • Build out a flow chart of every step that is taken to get from raw data to finished report
    • Identify who is taking each step, and what exactly they are doing. You’ll need to be very detailed here so that the entire process can be translated into a third party tool or code.
  • Types of steps will include:
    • Data collection steps
      • Some examples include
        • Download from a CRM or other source system
        • Manual inputs into Excel
        • Surveys
    • Data cleaning steps
      • Some examples include
        • Separating out first, middle, and last names
        • Standardizing address fields
        • Filling in or accounting for missing data
        • Ensuring no errors are in the data
    • Data processing steps
      • Some examples include
        • Rolling up the data
        • Applying a key to transform values
        • Pivot tables
        • Joins/Vlookups
    • Data movement steps
      • Some examples include
        • Uploading data into another system
        • Moving data between different spreadsheets
    • Data analysis steps
      • Some examples include
        • Classification
        • Linear regression
        • Forecasting
        • Calculated fields
    • Data visualization steps
      • Some examples include
        • Bar charts
        • Scatterplots
        • Pareto charts
        • Line charts
        • Tables
    • Data presentation steps
      • Some examples include
        • Powerpoint presentations
        • Written reports
        • Dashboards (possibly interactive)

When you are done, your flow could look something like this:

 

Manual Flow Chart no notes

(2) Identify which manual steps can be automated

  • Can any of these steps be removed or combined? Are any not necessary?
  • Score how much time each step takes
  • Estimate how large the risk is that a mistake will happen in a manual step, and what the impact of a mistake would be
  • How many of these manual steps can be automated?
    • Some steps will be very easy to automate, others will be harder, and some might be impossible
      • For example, if you need to pull data down from a legacy system to mix with other data, and there is no available API, there may not be a way of automating this download piece. For these cases, you probably can’t remove the manual step altogether, put you can put technology into place to make it less painful and time consuming
  • Determine what the ROI would be if all of the manual steps could be automated (essentially, they now take zero hours to complete)

When you are done, your analysis may look something like this:

ROI calculations

(3) Identify appropriate technology and/or code to put in the place of your manual processes

  • Identify what technology can be used to automate the manual steps
    • There are multiple ways of getting automation done, and various mixes of tools you could use. In the end, your choice of tools will be determined by the following criteria:
      • How much technical skill does it take to implement with this technology? Do we have those skills in house, or need to hire, or bring on consultants?
      • What is the cost of the technology upfront vs the cost of the technology in the long term?
        • Generally, the easier the technology is to use, the higher the yearly and upfront fees
        • The harder the technology is to use (writing code), the higher the cost for the labor, but the lower the cost in the long term
  • In addition to the technology you use to actually collect, process, model, and analyze the data, you will also have to select technology to run that process. This could include
    • Cloud platforms (AWS, Google Cloud, Microsoft Azure)
      • All of the major cloud providers have tools and technology to help schedule, run, and debug pipeline processes
    • In house servers
    • Platform specific scheduling (Some of the tools you may consider above have their own scheduling services. These usually take advantage of cloud services like AWS on the backend)
      • Here are some examples of technology solutions you might think about for each of the steps, but it is by no means inclusive of all choices
        • Data collection steps
          • Code
          • Fivetran
          • Matillion
        • Data cleaning steps
          • Code
          • Tableau Prep
          • Trifacta
          • Knime
          • Alteryx
        • Data processing steps
          • Code
          • Tablea Prep
          • PowerBI
          • Trifacta
          • Knime
          • Alteryx
        • Data movement steps
          • Code
          • Fivetran
          • Matillion
          • Tablea Prep
          • Trifacta
          • Knime
          • Alteryx
        • Data analysis steps
          • Code
          • Knime
          • Alteryx
        • Data visualization steps
          • Code
          • Tableau
          • PowerBI
        • Data presentation steps
          • Code
          • Tableau
          • PowerBI
          • Amazon Quicksight
          • Powerpoint

 

(4) Write up an automation plan and automation documentation

    • Create a document that maps out what is to be done with the following pieces of the automation project. This will not only be an execution plan, but can be referenced after the project is over so anyone can know exactly what data is flowing, how it’s being processed, who the owners are, and where the touchpoints are.
      • Data governance
        • Who owns the data elements being processed? Who is in charge of ensuring accuracy and quality assurance of data going in?
        • What are the agreed upon, standard names and definitions for the data being used, so everyone is speaking the same language?
        •  
    • Data flow
      • What data elements are entering the pipeline, how are they being manipulated, and what is the expected end result?
        • Make sure all stakeholders interested in the end result of the data agree on this
    • Technology touchpoints
      • Where will the technology touch the data, and what will it do?
    • Human touchpoints
      • Where will humans be in charge of the process, and what will they do?
      • Make sure and name specific owners of each point of the process
  • This document is extremely important for the following reasons
    • Full transparency into how the data is being handled
    • Accessible document everyone can agree on
    • If people move on from their roles or the company, there is always a document that can be referenced to assign new owners and ensure continuity of the process
  • Someone should be assigned as the owner of the document and process as a whole, to ensure it’s always up to date and the process is running according to plan

 

(5) Build the automation and ensure it is running without errors

  • Execute against the document and get the pipeline built and scheduled
  • Audit the results and make sure they come out as expected

When you are done, your flow could work something like this:

automated flow chart no notes

(6) Set up alerts and checkpoints to monitor the process going forward

  • Put alerts and checks into place to ensure the pipeline and scheduling doesn’t break moving forward
    • Make sure the alerts are informative, so if it does break, it can be repaired quickly

 

(7) ROI analysis of a data pipeline

  • Total up the cost of building the pipeline, and the cost of continually running the pipeline, in comparison to the cost of running it manually. Calculate your time to breakeven and savings moving forward.

When you are done, your conclusions may look something like this:

ROI calculation summary

Building Automation is Hard, But it Does not Have to Be

To get all of the above done, you’ll need a host of skills, capabilities, and experience, most likely including

  • Thorough understanding of current technology, capabilities, price points, and limitations
  • Understanding of databases and schemas
  • Understanding of how to deal with small vs big, vs enormous data
  • Skills to build out cloud infrastructure
  • Skills to work with third-party tools
  • Business acumen to understand real priorities and how reporting can move the needle
  • Reporting and BI skills to apply design best practices and get reporting that works
  • Presentation skills
  • Communication skills
  • Project management skills
  • Knowledge of the common pitfalls that you run into when building data pipelines
  • Scrum and agile best practices
  • ETL
  • Data engineering
  • Data modeling

 

To name just a few.

But that’s where we have you covered. Working with data to drive business results is all we do, day in and day out. We have a repeatable process that gets the job done quickly, every time, and with positive ROI. 

We can build out your automated data processes like we’ve done for other clients. Here’s GeniSys again on what it’s like to work with us:

If you want a free assessment of your current data processes and would like to see what it would take to automate them, fill out our quick questionnaire and set up a time to chat with us.