Every month, the Design team produces hundreds of graphics and videos for several clients, which sums up to 10k files per year. In my role as Design Lead, I wanted to provide insights about production with the rest of the management board and increase transparency over the work of my team.
Shareable Project Files
When I joined the team in 2019, everyone was still saving files on their laptops (!). So, as a first step, I needed their output in a shared folder. Google Drive (“Backup and synch”, back in the days) was the obvious choice, mainly because it allowed to selectively download the files one was working on, leaving the rest (about 20 Terabytes of data) on the cloud.
This proved to be a very timely solution, as shortly after the pandemic forced us to work from home.
Folder Structure
Every designer has their own way of organising files. The design team and I discussed the file structure internally, looking for a solution that could have fit everyone.
The team learned to store all the original editable files and assets in a Project folder and render they output files in a separate Output folder, which is shared with the other teams.
File Naming
With the Output folder up and running, I had a source from where I could pull data. Though, I was still missing some important information, such as Client name, date of production and typology of file.
Again, I discussed with the team a file-naming protocol which could include these information.
Once we decided for a protocol, from time to time, we had to go thought the onboarding again together, double-check typos and set up reminders to avoid messing up the data. It wasn’t always easy, but once the team could see the results, they found their motivation to follow the protocol.
Database
At last, I could instruct a programmer to write a script that targeted the Output folder and pasted the filenames into a Google spreadsheet database.
Every time the script read the underscore symbol _ in a filename, it writes what follows in the next column of the database. The script also fetches the URL of each file and the duration of each video from their metadata.
Development
I could generate insights from the database via a series of interconnected formulas.
Dashboard
Now I could finally built a dashboard that could glimpse over the team’s production. The dashboard features a drop-down menu that filters the results per client.
(The data visualised in the charts below are fictional)
Results
The learnings generated by the database supported all departments across the organisation taking informed decisions.
Finance – Proof or update the clients’ invoices with the actual content production.
Accounting – Insights over the clients’ activity. For instance, the dashboard will show if a client decreased their involvement compared to the previous months. It might be time to revamp our collaboration.
Business Intelligence – The performance of the published content can now be linked to the actual visuals. With this infrastructure in place, we can learn what performs better both on a quantitative and qualitative standpoint.
HR – Identify trends per product: learning, for instance, that there is an increasing demand for animated content could lead to hire more motion designers.
Repeat! Repeat!
The database is fully automated, the team only needs to name their files following the protocol.
It took quite some time to onboard everyone properly, listen to their concerns and optimise the tool in a way that inspired them to give their support.