When the ad agency had very few customers, managers would manually prepare reports for them. The solution seemed to be profitable: no need to pay for additional software, development, and anything else, from expenses. Just salary.
With the growth of the business, this practice became unprofitable. It took longer and longer to write reports. Errors appeared due to manual data processing. We will now tell you how the agency solved the reports problem.
ad agency, works with 500 advertisers.
reduce the time spent on report preparation for customers
reduce the number of errors in reports
free up time for employees in order to assign them new tasks (avoid hiring new staff)
develop a program that will help customize reports according to the wishes of the agency’s customers
automate reports preparation through Google API
1 minute is spent on 500 report preparation
0 errors in reports due to automation
There are now resources available to engage new clients, and it is now easier for the agency to scale the business
Agency prepares 500 reports per week
The ad agency operates from Florida and serves customers in Europe, America, and other regions.
The agency’s specialization is video marketing. The team is responsible for the full cycle: briefing, script writing, casting, music, editing, and other tasks related to content release. Another area is production. The agency publishes content on a variety of platforms. For example, social networks, YouTube, entertainment services, and mobile platforms.
One source of income is royalties for high converting ads. As proof of the quality of their work, the team prepares reports for customers. On average, the team releases 500 reports per week.
Problem: two employees spent all their time on reports and didn’t have time for other tasks
The agency used Google Ads reports for reports on video content conversion. Using Google Ads reports, customers could track dynamics and make sure that the agency was fulfilling its obligations. The agency team could correct ad campaigns in time if they didn’t achieve the desired results.
The reports were prepared by two managers. They downloaded all versions of Google Ads reports for each customer, processed the data, and generated a report taking into account customer wishes.
One big plus for manual report preparation is money. There’s no need to pay for an additional program, development, or anything else, just staff salaries. But at some point these savings disappeared.
When there were no more than a hundred customers, managers could handle this load. But with each new company it took more time to generate reports and new problems appeared.
❌ The managers didn’t have time to analyze the campaigns. If earlier managers had time to do web analytics tasks, now they only have time for routine work: download, copy, paste, recalculate, save, send.
Generating a single report was taking up to 15 minutes. And when there were 500 of them, managers had time only for report preparation and nothing else during the work day.
❌ Errors appeared. A manager could copy the wrong cell, or incorrectly enter a formula. When a report was ready, the numbers didn’t reconcile. For example, an advertiser was targeting a female audience in Florida but was receiving data for a male audience from California.
❌ It’s impossible to connect new customers quickly. Two managers could not cope with the current load, so it became risky trying to attract new customers: there was no one to make reports.
The agency had two options to solve the problem: automate report generation, or increase spending (for example, hiring more employees or outsourcing work).
Scheme of work before automation: 500 reports took up to 125 hours
Manager starts preparation
- Opens Google Ads
- Sets the parameters of the campaign
Configures reportSelect parameters for several report options:
- watched video to end?
- Downloads all variants of report
- Copy data to Excel — on different sheets
- Prescribe a formula for data comparison
- Based on the obtained results, prepare tables for the customer
- Build diagrams
- Prepare a report for sending to the customer: logo, dates, and other attributes
- Done. The report can be sent.
Solution: automate reports via API
The customer turned to us with a request: to speed up the process of report preparation, and to have the report take into account the customer’s wishes. This is what we did…
Suggestions for the report. First, we talked with the customer and collected the requirements for the report: what data to collect, how to group it, what to compare it with, etc.
Adaptation of the finished product to the customer’s tasks. Due to the customized setup, regular Google reports wasn’t suitable, so we could not simply pull up the data that Google was already collecting.
To customize reports, we used ready-made libraries, which we modified based on our software solutions. As a result, we have a technology that receives data from a standard report and reassembles it according to a given algorithm.
Negotiations with Google. You can pull data for a report through the API — this is a standard solution for such tasks. But there is a problem: until Google approves access to the API, you won’t be able to use it. This has to do with server load: any changes will increase the load, and Google is not sure that they really want these changes.
We took over negotiations with Google. Our team called and corresponded with the Google support team, justified the work through the API, and in the end we managed to convince Google to open access for us.
Technical setup and support. As soon as we got access to the API, we connected Google and our software, and started preparing reports.
Project technical block
Powered by PHP 7.2
- Silex 2.0 microframework
- Library for the Google API
- Template engine
- Styles — Bootstrap 3.3.7
Result: Reports are generated automatically
Now, as a result of automation, the company spends fewer resources on reports, and the work with reports has become easier and faster. Let us explain what has changed.
✅ Saving time. Reports are now generated and sent to customers automatically, without employee participation. The agency doesn’t need to hire new staff as managers now have time to complete other tasks and keep records.
✅ No mistakes. Data is downloaded via the API directly, without manual processing or connection to third-party services. As such, there is no risk of mistakes: the information in the reports matches with the information at Google.
Scheme of work after automation: it takes one minute to prepare all reports.
ManagerDoesn’t work with reports
- The report is generated automatically
- The software sends a report to the customer
- Done. Customer received a report