Digital Marketing Report Automation & Data Monitoring
When you Google: “Digital Marketing Report Automation” – this definition appears
“What is report automation? Report automation is the process through which digital marketing reports are created and automatically updated using a software. The gathered data can then be delivered to specific email addresses on a regular basis with automatic email dispatches.”
I agree with most of this definition, except the part where it says it needs to be delivered by mail…why should it? It can be kept in the cloud, available only for those intended any time they need it.
Here in Adaxa we take pride in our tailor made, custom client reporting, where they can see their campaigns and other metric performance daily, almost in real time – and most important – satisfying different management roles by giving them the data they need. CEO, VP of Marketing, Sales Department or Marketing Manager are not interested in the same charts, tables and data – that’s for sure.
Digital Marketing Reports – throwback
Here’s how we lived through Marketing Reports evolution
Back in the days, as a corporate marketing manager, we had a scheduled Report due date, that practice is common and clients should be aware where their money was spent in the previous period. It’s not a matter of good practice, no matter who you are, an Agency, Freelancer or something else – it’s your obligation to keep your client up to date.
Dealing with a bunch of PPC accounts, me and my team would basically reserve a day that we would call Reporting day – nobody liked those days, to be honest.
It was a dull and repetitive task of exporting the data in Microsoft Excel and trying to explain the numbers to different people with different knowledge and interest in Internet Marketing Performance.
Trying to keep a professional look and keep it brief and to the point was a struggle since most of our clients got bombarded by the same looking and kind of boring report. Not to mention the times when the client asked for a report for a specific, short time, campaign we were running – those kept us from doing the thing we do best, run and optimize campaigns – and that was frustrating.We’ve had to do something to …..
At the time we were stuck with MS Excel, since there weren’t many Reporting Tools available on the market at the time and also it would be a struggle to force a bunch of corporate people to switch. Personally, I was highly motivated to solve this problem, so I took a week off from my Campaigns Management and tried to figure out how to, at least automatize that Copy/Pasting.
Learning some Excel VBA stuff helped me create a simple template that had 2 sheets, Raw data and Report. This little script had a great impact, you could generate a report just by exporting data from the platform like Google Ad’s (Adwords back in the days), pasting it into a template, push one button and the report with appropriate Header, Footer, Company Logo and all the necessary charts was created and exported to PDF.
Not even close to deserve calling it Report Automation, but it was the first step. Somebody needed to take all those PDF’s and send each and every one to the Client and it’s team members. That was also causing a confusion, who needs to get what.
Reports Automation Tools
Things changed mid 2016, when Google rolled out Google Data Studio it’s own free data visualization and business intelligence reporting tool. I’ll talk more about it in my next article but generally, Data Studio is a beautiful, straight forward tool, anybody can use.
Also nowadays, there’s a bunch of great tools you can connect to your Advertising and Analytics platforms – and your job is done (or is it?)
Simply by Googling the term, you can find a bunch of great articles online that could help you resolve the reporting issue.
Personally I would read Digital Agency Network and Wordstream’s articles and for sure I’d get inspired to choose the appropriate tool.
So why do we avoid using Data Studio or any other third-party tool? – We don’t, in fact we use it some times in short-term client contracts
In other chases, given the time me and my teammates invested in learning new skills (Google APP’s scripting, web scraping, etc.) and the fact that we love and live data – we are motivated to serve our clients the best thing we can.
And here’s how we do it.
Digital Marketing Reporting System – Adaxa Agency
Let’s assume that the initial client brief is done, team members are familiar with the product/service, measurement strategy is agreed, we have access to all platforms involved, tags are implemented, goals created and Marketing Campaigns can roll out.
Before you start building a Digital Marketing Reporting System, you need to know who will use it and what they want to know.
Usually, you would have 2 or 3 levels,
- Executive/Owner’s report – straightforward data mostly about overall sales
- Marketing Team report – this can go across all metrics broken down by most important dimensions like Location, Devices, Advertising Platforms. We usually include the non Advertising Data, like SEO performance, site speeds end whatever we agree on.
- Campaign specific report – usually used by Agency members to notice problems on any level as fast as possible.
Google Sheets is the bare bone of our reporting system, we can’t live without it. To be honest, I forgot how to use other sheet tools since I stopped playing with VBA. It’s flexible, fast, reliable and always online. If you throw in some G App’s scripting knowledge into it, and we’ve been doing it for years, it can do things you can’t imagine. But I’ll talk more about it in some other article, now let’s get back to Report Creation.
Start building your own Marketing Reporting System
Steps to follow:
1.Data Collection
2.Data Aggregation
3.Reports Design & Automation
4.Report Delivery
Data Collection
Data collection is the process of gathering and measuring information on targeted variables in an established system, which then enables one to answer relevant questions and evaluate outcomes.
First step of the process is of course, getting to know where and how you can access the needed data. So let’s dive in.
1. Google Analytics Data
Most important and also most common source is, of course Google Analytics, there are quite a few ways to get data imported from GA to Sheets, the fastest and most straightforward is the:
Google Analytics Spreadsheet Add-on brings you the power of the Google Analytics API combined with the power of data manipulation in Google Spreadsheets. With this tool, you can:
– Query and report data from multiple views.
– Compute and display custom calculations.
– Create visualizations and embed those visualizations on third-party websites.
– Schedule your reports to run and update automatically.
– Control who can see your data and visualizations by using Google Sheets’ sharing and privacy features.
If you want even more control and options, you can use the Google Analytics API and Google Apps Script to access your Google Analytics data from Google Sheets. This is powerful because it allows you to utilize all the great features of Google Sheets with your analytics data, such as easy sharing, collaboration, charting and visualization tools.
2. Advertising Platforms Data
There are a bunch of Marketing platforms businesses around the world use to promote their services or products and therefore every single one of them, at least as far as we know, have some kind of API you can use to import into Google Sheets for better understanding and reporting. For example, here’s an extensive guide Import Microsoft (Bing) Ads Data into Google Sheets. We’ll stick to Google Ad’s in this example,
Similar to Google Analytics, Google Ads also have a plugin in G Suite Marketplace that’s completely free and easy to use.
Of course there’s Google Apps script, where you also create a more flexible and reliable solution. Brainlabs team and their CEO Daniel Gilbert have a bunch of scripts you can use to get data into Google Sheets. Optmyzr and their boss, ex Googler, Frederick Vallaeys have a script for importing Google Ads data into Google sheets
Some of these techniques require some programming skills, but don’t be afraid, there’s a much faster way to do this later on.
3. Other Data Import
Now this is the part where we can put in use the full strength of Google Sheets, using Google Apps Script language or built-in GSheet functions, we collect data from Social Media and web in general that we can use. Here are some examples,
Basics data compare, like the number of followers on Twitter between accounts using an “=IMPORTXML”
More advanced stuff, like programming your own function in Google Sheets, like this one we found, created by magnificent Dave Johnson, that scrapes Wikipedia for the name entered and returns the Date of Birth, date of death/or still alive + calculates the Age of the person.
Crawl Youtube to keep record of all your Channel Views, video by video, using this function “=SPLIT(IMPORTXML(B2,“//div[@class=’watch-view-count’]”),” “,true)”
By playing more with Google Apps, you can create your own Scrapers, like this Youtube script we use
Also you can crawl your own SitemapXML to see new products in store, and much much more….
But where do you draw the line, you might ask?
We still use custom scrapers to get some client relevant data from the web and we draw the line when it comes to importing Data from Advertising Platforms, since in most chases, all the platforms have something specific to it and the physiognomy of them keeps changing all the time.
If you or your agency like to keep focus on the important things like – running successful campaigns and bringing a positive ROI and also don’t want to get in too deep in programming – there’s a solution!
SUPERMETRICS for GOOGLE SHEETS
I like to call myself an Supermetrics advocate, since I’ve been using it for years and the only thing I can say about them is that they created something special and are still doing a great job. This sweet tool is importing from almost all sources, like;
Bing Webmaster Tools, Custom JSON/CSV, Database, Facebook Ads, Facebook Insights, Facebook Public Data, Google Ads, Google AdSense, Google Analytics, Google BigQuery, Google My Business, Google Search Console, Instagram Insights, LinkedIn Company Pages, MailChimp, Microsoft Advertising, Moz, Optimizely, Pinterest Public Data, Quora Ads, Reddit Public Data, SEMrush Analytics, Shopify, Stripe, Tumblr Public Data, Twitter Public Data, Twitter Ads, Vimeo Public Data, VKontakte Public Data, Yandex.Direct, Yandex.Metrica, YouTube.
And that’s why we rely on it to bring us the data we need.
*-Please note that we are not affiliated with them and this is not a sponsored article – it’s just pure love.
Data Aggregation
So we have the raw data, now what?
Create final reports sheets inside that sheet – Keep reporting Levels in mind, don’t mix them up and serve people data they do not need.
It’s important to take in mind that you need to have a different sheet inside a spreadsheet for each platform Data (Keep Analytics, Advertising and Scraped Data in separate RAW sheets).
Label them correctly, so you can Merge the data
Start merging data like cost, clicks, impressions, conversions from different platforms using Structured Query Language inside Google sheets
Group, filter and manipulate data so you can use them inside the report.
Reports Design & Automation
Now things are ready for the fun part, start assembling reports, creating tables and charts. Google Sheets has more than enough chart types that can be customized however you want them. Line, Column, Combo, Bar, Pie, Scatter, Histogram and many more.
Last step in this chapter is to create a date/time stamp and set up your data sources to auto refresh.
Now you have a custom made Digital Marketing and overall Business Insights System you can share.
Report Delivery
There’s many ways you can choose to get this reports delivered,
We prefer the safest solution, by embedding every report into our site by creating a client specific landing page on our site that requires username and password. So the data can stay safe.
You can export it as an Iframe and limit the visibility only to people that have the url link.
Reports can be embedded in Google Sites or any other web page that you and your Client consider safe. You can restrict the viewers by giving view permissions to specific users that are logged into specific Gmail accounts.
Few more ideas you can do with with this kind of reporting
- Weather Alerts – If you have a product or service where sales depend on weather conditions, you can import Locations you advertise and scrape current weather conditions there.
- Scrape Sitemap for changes – it’s common for big eCommerce systems to add and remove items on in the webshop – and advertising is still running
- Tweet on change – system can send a tweet, mentioning you, when some changes happen.
- Send Mail from report – Connect Gmail using Gapps to send the report to the client when the sheet refreshes.
- Create a Calendar Log – when CTR changes or any other unusual behavior occurs, log that in your Google Calendar automatically
- Create PPTX Presentations – you can create a Google Presentation File, copy/paste tables and charts into it and they will auto update immediately after Google Sheets refresh.
And this is how we do it, no templates, no hassle – just a clean and simple Digital Marketing Reporting System.
Feel free to comment and don’t hesitate to ask us Anything!
Adaxa loves data 🙂
Data Monitoring Dashboards – for Agencies and Campaign Managers
Our Digital Campaign Managers use, client and platform specific reports to streamline their daily tasks, making stuff much easier for them by looking at campaign/client specific data.
These are the most advanced and also tailor made.
These types of dashboards, created from the same RAW data, use Data Validation, so when we need to investigate a specific campaign, we select the campaign name from the drop-down menu and all the data, including tables and charts adjust accordingly.
Managers use this extensive dashboard to quickly:
- Compare campaigns performance in the past 7, 14, 28, 30 days,
- Compare campaign vs whole account across all metrics like Impressions,Clicks,Conversions,CPL,CTR,Conversion Rate,Average CPC
- Filter keywords and search terms based on performance
- Compare organic vs campaign traffic quality
- Analyze product performance
- Spot user Location anomaly’s
And much more….
If you are interested in something like this, feel free to Contact Us or just ping us via Facebook, LinkedIn or Twitter.
Enjoy reporting!