PPC Reporting Automation

Inspiration

Both me and Boris work in online marketing. I'm an analytics consultant, and Boris is a marketing specialist handling multiple clients for which he runs online campaigns. As we both are part of a bigger marketing community, we often hear people complaining about the reporting and how time-consuming it might be, especially if it needs to be done on a monthly basis. We wanted to use Coda AI to (ideally) fully automate this process, or at least give marketers a nice starting point they can easily iterate on.

What it does

If you prefer video format, most of this information can be found in the attached video Our document is primarily aimed at PPC specialists who are running campaigns in Google Ads, Meta, or similar marketing platforms. In its current form, it works best with Google Ads campaigns, but could be easily transformed to work on top of any dataset (we chose Google Analytics 4, as I had previous expertise building GA4 Coda pack, that's why we focused on Google Ads first).

Our solution takes the underlying dataset, which is in SOURCE_TABLE, and then we provide this table as an input for the Coda AI to generate 5 different outputs:

Data Insights Automatically generates insights by comparing selected metrics from the last month with the month previous to that one.

Goal and Target tracking The doc contains another source table with KPIs per client, so AI can compare actual results with the KPIs and targets we decided on with the client.

Monthly Change It compares the last month with the previous month and displays the percentual change in selected metrics.

Frequently Asked Questions You can ask questions about your dataset and get them answered by the Coda AI. We also pre-prepared FAQ in the form of a dropdown list (with the help of Coda AI), but this is easily expandable if needed.

Email We also combine all of the above and let Coda AI compose an email for your client as a part of monthly reporting.

How we built it

To make this work we needed to build a simple pack, that uses Google Analytics Reporting API to pull data to the SOURCE_TABLE. Again, this could be replaced by any other pack that pulls data from different platforms (Meta, LinkedIn, etc.). That was the part I worked on, then Boris took the role of the AI prompt engineer and was perfecting the prompts, so we could get the desired results for the features we described in the section above.

Then we added a few quality-of-life improvements, such as the client info box that automatically pulls information about the client in various parts of the doc, added a refresh button to refresh the dataset, and a graph to visualize Ad Spend / Revenue per month.

Challenges we ran into

Limitations of the source tables First, we tried to import Google Ads data manually (just to check how Coda AI prompts work). However, this dataset was too large, so we needed to trim it quite a bit. That's why we decided to go with the Google Analytics 4 reporting API instead, where we were fully in control of what metrics we want to include (so the input table won't be too large).

Copying of prompts At first, we wanted to make one template page, which could be duplicated, and insert the data into each one separately. However, we ran into a problem in that the AI prompts were not properly duplicated from the original page. We didn't have much time left, so we decided to manually prepare templates for additional clients, so the doc can be used for multiple clients right away.

AI Hallucinations Around 20% of the time the Coda AI hallucinates, and its output is just a random set of numbers from the underlying dataset. We tried to modify the prompt so this won't happen that often, but it can be easily fixed by re-running the prompt box.

Accomplishments that we're proud of

We learned about this Coda AI challenge just a week ago, so we are really proud we were able to finish it to its current form. We really enjoyed the extra hours we put into this project, and we want to keep working on this further (more on that in the last chapter).

What we learned

Besides working with Coda AI, we learned a lot about project management (since we needed to plan this out and finish it in one week). We both had previous experience with Coda, but definitely learned a few tricks, especially when working with formulas.

What's next for PPC Reporting Automation

Adjusting the prompts - keep iterating on the prompts so they produce better outputs. Data sources - implementing support for multiple data sources - Social Media APIs (Meta, Linkedin, etc.) and Google Ads API Our end goal would use this as a single source of truth when it comes to evaluating online marketing campaigns.

Built With

• coda
• coda-ai
• google-analytics-4-reporting-api
• oauth