How to Automate End of Day Reports for Your Agency Team

What do you need to automate end of day reports?

The system needs four kinds of tool, and the exact brands matter less than the framework. The same process works with whatever you already use.

The video above walks through the full build. This guide gives you the structure and the settings.

• An automation tool. Make.com and Zapier are the two main no code options. Zapier is a little more beginner friendly. Make.com is a little cheaper and does more but takes longer to understand. Both offer free trials.
• A communication tool. We use Slack, but you can apply the same framework to any messaging tool.
• A database. We use Google Sheets to keep the build simple. For a proper setup we would use a real database tool such as Airtable or SmartSuite, which scale better.
• A form tool. Google Forms is free. We use Typeform in our own agency. Whichever you pick, check it has API access, which most do.

Automation 1: How do you remind the team to fill in their form?

End of day reports only work if people complete them regularly. The first automation sends a Slack message at a set time each day with a button linking straight to the right form.

The example uses two departments, customer success managers and appointment setters, each with its own form and its own Slack channel. For more departments, add another message step for each.

1. Create a new scenario in Make.com and add the Slack module to create a message.
2. Connect your Slack account and choose the public channel for the first department.
3. Leave the text field empty and use blocks instead. Blocks let you send buttons and richer formatting. Build the layout in Slack's Block Kit Builder and paste the code into Make.com.
4. Replace the link in the code with the URL of that department's form, so the button opens the correct one.
5. Under advanced settings, set an emoji icon and a username such as end of day bot, so the message looks clean instead of showing as an integration.
6. Test the module, check the message in Slack and click the button to confirm the link works.
7. Clone the module for each other department. Change only the channel and the form link, then test again.
8. Set the schedule. Choose days of the week if your team does not work every day, for example Monday to Friday, and a time such as 4pm.

Automation 2: How do you store submitted forms in a database?

The second automation takes each submission and adds it to your database. The example sheet holds the date, department, name, hours worked and feedback, with a colour coded section for each department.

Google Forms can be connected directly to Google Sheets. We used Make.com anyway so the same framework works with any form tool and any database.

1. Create a new scenario and add the Google Forms module to watch responses. Select your form and set a limit of five responses per run, in case several people finish at once.
2. Design the form so number questions only accept numbers. If someone types a word instead of a digit, it breaks the data in your database. The example form asks for the employee name, hours worked, client meetings, tasks completed, issues resolved and optional feedback.
3. Submit a test response and run the module so Make.com receives a real submission.
4. Add a Google Sheets module to add a row, and select your sheet.
5. Map the fields. For the date, use the format date function with the current date. Type the department as plain text. Then map each form answer, expanding the text answers so you pick the actual value.
6. Run the scenario and check that the new row appears at the bottom of the sheet.
7. Set the schedule. We run it daily at 17:55, five minutes before the summary step.
8. Clone the scenario for the second department. Swap the form, remove the columns that do not apply, and map that team's own questions, such as follow ups and calls made.

Automation 3: How do you summarise the data every day?

The third automation reads the database, posts the numbers to each team's channel and writes an overall summary for admins. It runs every day at 6pm.

In the Google Sheet, we added simple formulas that calculate one metric per department for the current day: the average number of appointments set, and the average number of meetings held by customer success. A real build would analyse much more.

1. Create a new scenario with a Google Sheets module that gets a range of values, pointing at the cells that hold the daily averages.
2. Run it to confirm it returns the two values.
3. Add a Slack module for each department's channel that posts that team's average.
4. Add an OpenAI module using the prompt completion method. Pass in both numbers and ask for a short, friendly summary of the day. A cheaper model is enough for something this simple.
5. Add a final Slack module that posts the AI generated summary to a channel for admins or the CEO.
6. Test every module, then set the schedule for 6pm.

How do you take this system further?

The build above is deliberately basic. Four upgrades make it stronger.

• Use a proper database such as Airtable or SmartSuite instead of Google Sheets.
• Ask more questions, but keep the form short enough that people still complete it every day.
• Use an assistant setup rather than a one line prompt when AI is involved. The responses are better and more consistent, especially once you summarise more than two columns.
• Add a follow up step. If someone has not completed the form by a set time, such as 5:30pm, send them a second message reminding them of the deadline.