Excel at Reporting: How to Import Data into Google Spreadsheets Automagically
“Data! Data! Data! I can’t make bricks without clay.”
― Arthur Conan Doyle, The Adventure of the Copper Beeches
Just as Sherlock Holmes needed information to solve crime cases, so do marketers rely on data to understand what is working and what can be improved about their efforts. As a marketer managing programmatic campaigns, you must heavily depend on reporting data to improve your company’s programmatic buying strategy.
For this purpose, you most likely want to have all the campaign data in one place in an easy-to-read form. The UI built-in dashboard seems to be the right place to track key metrics, but what if it’s missing specific analytics or customization features? Or have you ever wondered how to enable other team members to view campaign data in real time without having to login? Say ‘hello’ to the Reporting API!
Why use Reporting API
The Getintent Reporting API is an effective tool for smart dashboarding and data analytics. It automatically imports campaign stats from the Getintent UI into a Google Sheet, turning raw data into actionable insights. You can use Reporting API to create pivot tables, graphs, dashboards, and other real-time reports showing campaign and creative statistics, spend numbers, or impressions.
Once you take time to construct an appropriate Google Sheet, you get those neat dashboards for quick ad hoc analysis that help you trace up-to-the-minute parameters and see trends unrecognizable otherwise. See an example of the link on the screenshot below:
Here are three steps to parse the Getintent UI data into Google spreadsheets.
1. Access API and choose variables
To enable reporting API, get your API key. Find the access token in your profile settings:
and paste it in the link below instead of <token>:
https://reporting.getintent.com/api/v2/metadata?token=<token>The link provides access to the metadata that contains all the report forms and variables available for you through API:
Here is the most important information to get from a metadata report:
- report_name — the name of the report available for your API key;
- key_fields — usually used as filters and rows to group values;
- value_fields — usually used as values in columns.
2. Choose the report and variables
Any report from the UI can be acquired via a Reporting API link like this one:
https://reporting.getintent.com/api/v2/reports?token=<token>&dataset_name=<dataset>&start=<start_date>&end=<end_date>Here is a list of attributes necessary or optional for your API request:
You can build the link without all those manipulations in steps 1 and 2 by using the key Link to API request that is available in your UI Reporting after a report is built:
Here are a few examples of Reporting API requests viewable as comma-separated-values tables that can be pasted into Google spreadsheets:
- The report on clicks, impressions and average CPM with advertiser, month and campaign breakdowns:
https://reporting.getintent.com/api/v2/reports?token=<token>&dataset_name=hourly_budget&keys=advertiser_id,month,campaign_id&timezone=UTC&start=2018-05-01&end=2018-05-18&values=imps,clicks,cpm&filter[]=eq:campaign_currency:USD&filter[]=eq:&csv_separator=,- The report on impressions and spend budget by countries:
https://reporting.getintent.com/api/v2/reports?token=<token>&dataset_name=browser_traffic&keys=country&timezone=UTC&start=2018-05-01&end=2018-05-18&values=imps,budget&filter[]=eq:campaign_currency:USD&filter[]=eq:&csv_separator=,- The report on impressions and spend budget by SSPs:
https://reporting.getintent.com/api/v2/reports?token=<token>&dataset_name=browser_traffic&keys=ssp&timezone=UTC&start=2018-05-01&end=2018-05-18&values=imps,budget&filter[]=eq:campaign_currency:USD&filter[]=eq:&csv_separator=,3. Paste the data into Google spreadsheets and construct the report
Once you construct a link, paste it into a Google spreadsheet using Excel =IMPORTDATA() function:
By combining the API report link with =CONCATENATE() and =TEXT() function, you can change API report query parameters just by altering the numbers in the cell.
For example, you can change the date in your API link by concatenating your link with certain dates or write the =TODAY() function in the concatenated cell so the report is always up-to-date.
You can also insert a new timezone, API key or any other parameter without rewriting the API link:
After you get the report, you can build custom tables or graphs that will be updated automatically in real time:
Turn insights into action
Now that your team has a clearer picture of how your programmatic campaigns are performing, make sure to turn those insights into actions. You may get carried away with building fancy reports and dashboards, but don’t forget analytics provides no value if that data is not acted upon.
