Tell me if this is sounding familiar? You have a few regular data-wrangling or reporting tasks that you complete each week/month using Excel. You re-download the latest data, process it in the same way as last time, copy and paste your updated charts into PowerPoint or Word, "pdf it" and then shoot it off in an email. Or maybe just sending out a cleaned and summarised Excel file is enough.
Sometimes this process runs smoothly. Other times, people change the data at the last minute and then you have to re-run the whole process, re-copy over the charts, all at the eleventh hour, under heaps of pressure.
For automating such recurring, manual work tasks (especially reporting), there are heaps of great options out there, to assist you in the areas of data pipelines, data-wrangling, producing outputs (charts and tables) and delivery. You could:
- get better at Excel. Start using Tables, more advanced formulas, PivotTables, PivotCharts.
- explore PowerQuery -the massively under-utilised component of Excel -for data-wrangling.
- use Googlesheets and learn a little bit of SQL (to produce a few simple data-wrangling queries) to rearrange / summarise your data right up there in the app (where it can be shared).
- get acquainted with Power BI or Tableau and start producing customised charts (and dashboards) that actually delight the people you’re presenting them to.
- learn a drag-and-drop data-wrangling tool like Tableau Prep or Alteryx.
- look into all those cool, new WebApps that will integrate all your different data sources together (eg. Zapier, IFTTT, StitchData, Integromat, Coda, AirTable), so that whenever changes happen the appropriate flow-on effects occur, as well.
With the maturing connectivity between business Apps; in most cases, these "no-code" options will get you up and running fast and with pretty reliable security. The fees will almost certainly outweigh the hours you would have spent over the coming months (even if exploring all the options and the initial setup chewed through a fair chunk).
But it is very possible that, eventually, your needs will become more specialised than what is available “off the shelf”. Best case scenario is that there is some plug-in or another paid tool available or you have to bring in a developer to write some custom code to get you through.
Okay, to get to my point. I think learning to code R is a good alternative.
The R language offers incredible versatility. There is an army of open source developers out there, all vying to produce useful packages that expand upon the already extensive functionality of R.
There is also substantial empowerment with learning to code and getting your computer to do the complex things that you once had to do, manually. All of a sudden, you become a "creator" instead of a "consumer". Furthermore, as you develop your R skills, you accumulate a back-catalog of techniques that you can re-deploy nearly instantly in similar-enough contexts. If you're working with a team of R users, this effect gets amplified.
Maybe I'm going a bit too far, but learning to code R makes you feel like the "master of your own destiny" as opposed to just another user, paying for a tool like everybody else (regardless of how good that tool is and how engaging the community is).
But not everybody wants to learn to code. These paid, specialised tools are popular for a reason and are actually quite good. So, it's certainly possible to utilise them the first time around. Set up that new data-downloading automation in Zapier or Integromat; or design and prototype your dashboard in Tableau, have Stitch update your database with your latest Xero data. But, after this integral process of figuring out the specifics of what you actually need (as an end result), consider leveraging and enhancing the data literacy of your team by transitioning the process over to R.
With such processes coded up, the on-going cost virtually disappears, sharing it and reproducing it throughout your organisation can be as simple as sharing the script (and having a really solid chat, so they know the intricacies of how it works). The manual labour element of the sharing is almost completely removed, because you do not need a person looking over the other's shoulder, showing them where to click and drag and drop things to get the process set up.
And when things inevitably change and your process stops working, reviewing a script for bugs is generally far easier than having to right click everywhere and opening all the windows and menus in "drag and drop" applications.
So, with that, I would like to announce my new course in R.