Today, I am going to show some automated setup which I did for the adwords account to know the stats on daily/weekly basis.
I wrote an adwords script, which will output the google adwords data into google spreadsheet on daily (hourly/weekly/monthly also available) basis & give you reports in 3 different sheets.
You can also save your target metrics and check on daily basis where you stand as the data gets updated.
So, Lets start..
Adwords Daily Report Sheet
- It will give you day wise data. Also, you will have the ability to filter the data by device.
- This data will get updated daily for last 7 days (or any date range), as this script is scheduled to run everyday.
Adwords Monthly Report Sheet
- The monthly report sheet contains month on month stats for adwords.
- Also, the good part is, it will also show you the predicted sales expected at the end of current month!!!
- In this report too, you can filter the monthly & the monthly prediction data by device.
- This sheet will also get updated and new month will get added, if the script is scheduled.
- You can save your target metrics for the current month (also by Device) in a cell and check daily what the prediction says and where you stand.
Prediction logic:
- It will only use current or latest month’s stats to predict the stats at the end of the same month.
- Lets say, you output the data for the date range 1st Oct to 14th dec. It will use 1st Dec to 14th Dec data to predict the Dec month’s stats.
- So, as it collects more data for Dec, the prediction will change everyday and will match the exact data as the adwords data at the end of Dec month.
Adwords Day of Week & Ad Network Report Sheet
- As the name suggests, this sheet contains the day of the week report and also the ad network report.
- You can also keep the daily check on these reports for any trends in your category, as these reports too, will get updated automatically, if script is scheduled.
Automated Adwords Report Setup
Now, I’ll explain the step by step process for setting up these automated reports.
Step – 1:
- Copy and preview the script available here. If you are new to the scripts, check how to run adwords scripts.
Step – 2:
- Open the URL available in the log.
- The data you’ll get is of last 7 days by default.
- For custom date range data, you can input the new date in cell B1 & C1 (mm/dd/yyyy) and re-run the script.
Step – 3:
- For automation, you can input the start date as fixed date & end date as =TODAY()-1, i.e yesterday & schedule the script daily.
- So the end date being a variable, will change and script will output the data for the new date range, which will include yesterday’s data.
- Eg: Lets say, start date is 1st Nov (11/1/2016), end date is TODAY()-1 (12/23/2016) & the script is scheduled daily. So, when the script runs daily, yesterday’s data will get included. (11/1/2016 to 12/23/2016 —> 11/1/2016 to 12/24/2016)
Adwords Reports Use Cases
Daily Reports Sheet
- You can keep an eye on any drastic changes to any particular metric by the scheduling the scripts daily.
Start date: Start date of current month
End Date: Today() - If you want to run a campaign for a day, you can input your target metrics (also by Device) for a day and schedule the script hourly to know where you stand.
Monthly Reports Sheet
- You can input the targets for the current month (also by Device) in the monthly report sheet and schedule the script as
Start date: Start date of current month
End date : Today()
Run the script daily to check where you would be as per your targets.
Day of the week and Ad Network Sheet
- You can try ad scheduling by analyzing Day of week reports and compare the before and after results. You can also filter it out by device.
- Ad network reports might help you in focusing the budget on the right source. You can also set the targets for each ad network by device.
Other Use Cases
- Another use case would be putting start date as well as end date as variables.
For Eg: Start date : =Today()-7 & End date: =Today()-1.
Now, if the script is scheduled daily, you’ll get refreshed data for the last 7 days daily. - You can also schedule the script weekly to get last 7 days data or schedule the script for last 14 days (Date Range:Today()-15, Today()-1)
You can play around with the date range and script scheduling & create many use cases as per your needs. There are many possibilities.
Lastly, I wrote another script for automated mailer which will capture this data and mail me everyday (Screenshot below).
Mail screenshot:
Let us know some more use cases.
Ritwik is a product owner at DigiXport. He also loves to write technical & easy to understand blogs for Marketers & Entrepreneurs. A Firm Believer in Teaching -> Learning -> Growing. :)