If you send reports to your clients, you’ve probably gotten the question: Do you have a report that shows the most important metrics for campaign progress?
Likewise, if you use Google Analytics, then you’ve probably faced the challenge of figuring out exactly what to show and how. Analytics offers a lot of ways to slice the data and show it graphically, like this:
The problem is that you want to show something succinct that can also be understood by everyone who sees it. Likewise, you want a way to create this report quickly and easily.
Luckily, Google offers some helpful tools to make both possible using Google Docs integration with Google Analytics API and Google Sites. We (Max and Phoebe) have joined forces to not only show you how to build the Google Doc to capture and organize your campaign data, but also how to create an awesome online report using Google Sites to save time and wow your clients. We’ve also included several templates that you can use yourself.
Let’s kick things off with defining the reporting metrics and building the Google Doc – This is Max’s part.
Step 1 – Set Up the Google Doc API Integration
This will enable you to pull the data directly from Google Analytics API using whatever data slices you’d like. To learn how to do this, see Shawn’s awesome post It’s Magic! Automating Your Reports With Google’s API. To demonstrate how to build the Google Doc and Google Site, we’ve created a very straightforward template Google Doc for reference that’s publicly accessible here. Please note that this Google Doc is not connected to Google’s API, you’ll need to do that yourself.
Once you set up a Google Doc with the API integration, you’ll know whether it’s connected because “Google Analytics” will appear in the top navigation like this:
Step 2 – Determine Report Metrics
Just like Google Analytics, the API enables seemingly endless metrics and slices of data. Since we’re talking about higher ups here, we’ve decided the following metrics are relevant for demonstrating campaign success overall and also at a high level:
Goal 1 – Conversions
- 1.1 Total – By Type
- 1.2 Total – By Source
- 1.3 Mobile vs Mon-Mobile
- 1.4 Growth of Social Leads
Goal 2 – Prospect Traffic
- 2.1 Non-Branded Search Traffic
- 2.2 All Traffic Sources
Goal 3 – Social Media
- Visitors (or Fans/Followers)
Goal 4 – Mobile
- Site Traffic
Goal 5 – Healthy Site
- Crawled & Indexed Pages
Step 3 – Set Up Data Queries
Once the Google Analytics API is operational, you can then make specific data queries. We’ve already set up the template Google Doc with specific data queries for the report we’re building here. Since our template doc is not actually connected to Google’s API, some of the fields need to be updated with Google Account and Advanced Segment data (these are indicated in yellow and green).
Google Drive / API Reference offers tremendous amounts of information on the setup.
Here’s a run down of the different query fields we’re using:
- query/value – These are listed numerically. There’s a query and value for each data request.
- type – We’re using “core” only for this template.
- IDs (in yellow) – Enter the Profile ID for the Google Account here. Use the format ga:profileid#. You’ll find the Profile ID under Profile Settings in the Admin tab on your Google Analytics account:
- start date / end data – This is the actual start and end dates for the data pull. (Pro Tip: Rather than change the end date manually at the end of the month, go ahead and put the year-end date. That way the monthly totals will update automatically every month until you’ve reached the end of the year.)
- last-n-days – This is not required if start date and end date are entered. See Google API reference for more information.
- metrics – We’re using mostly two metrics for this template: ga:visits and ga:goal1Completions (where the number changes based on 1, 2, 3 goal, etc.). I also like using ga:visitors (rather than ga:visits) to show unique traffic.
- dimensions – Because we’re reporting on monthly traffic ongoing (meaning, updating every month), we’re using ga:nthMonth.
- sort – Here we’re not using any sorting calls.
- filters – We’re also not using filters.
- segment (in green) – Enter a standard code or use Advanced Segments for customized views. Use the format gaid::segment#. With our template, we’ve used mostly Advanced Segments. If there is no code in the segment field, then one is not necessary. Finding the Advanced Segments number is a little tricky but easy once you know where to look:
1. Turn on the Advanced Segment
2. While in Analytics, look for the numeric code after “Duser” in the URL:
- start-index – Again, not using this feature here.
- max results – We’ve set this at 10,000 as a high ceiling to capture everything we’re reporting on. So far this hasn’t created too many issues; we’ve had a few instances when we get notices from Google that we’ve reached a maximum on data requests. This is most likely because we’re running API calls for multiple profiles in the same account.
- sheet name – As each query runs, a new worksheet will be created automatically (or updated if it already exists) labeled the corresponding “sheet name”. When creating the sheet names, try to use clear and memorable names – it will make your life easier when sorting through the data when creating your charts. For ease of explanation, we’ll call these worksheets “data sheets”.
- Note: These data sheets get updated every time the queries are run; that means anything added manually will get erased the next time the query updates.
Here’s a screenshot of the tabs showing the gaconfig and corresponding data sheets for “Mobile Traffic” and “CPC Traffic”:
Step 4 – Organize the Data
Now we can organize the data so it can be evaluated. We’ll use the data sheets (from above) to build out the presentational worksheets (“preso sheets”) for each of our Report Metrics (from Step 2).
Remember, the data sheets get overwritten each time the queries are updated, so we’ll want to create preso sheets for sorting the data and, ultimately, creating awesome charts.
To illustrate the process, we’ll walk through building the preso sheet for our first metric – Conversions By Type.
Let’s start with the preso sheet. You’ll see it’s labeled “1.1 Conversions – Type”.
Pro Tip: Use a number in the name of each preso sheet so you can reference it easily when pulling the charts into the Google Site. Likewise, we add numbers to the preso sheet names in order to distinguish them from the data sheets, which we recommend “hiding” after you’ve linked them to the appropriate preso sheet.
Here’s a screenshot of the worksheet:
You’ll see that we’ve broken down conversions by the four types of conversion for this campaign: Contact Us – Reg, Contact Us – Quick, Verify Insurance and Self Assessment. If you have only one conversion, then go with that.
The data for these columns is pulling from the “Conversions” data sheet which was set up in the gaconfig to separate the data for each of these into columns. Simply link your preso sheet to the data sheet so that the numbers get updated automatically every time the query runs. What’s great about query results is that the first month’s data always populates on row 13; this makes it very easy for confirming if the right cells are connected. We’ve set up the preso sheet for you already so you can enable this for the Analytics API or use this template in your own Google Doc.
Notice that we’ve listed the monthly dates in Column A to correspond with the start date / end date we set in the gaconfig sheet.
Pro Tip: Add the months to extend through the end of the year (or whatever reporting period you’re using in the start date / end date) so that the monthly results update ongoing, without having to be updated manually.
Step 5 – Create the Charts
Now that the data is organized, it’s time to make an awesome chart. Following are the basic steps. Phoebe goes into more detail below about the best way to design the charts.
1. Highlight the data, including any label columns or rows
2. Click Insert
3. Select Chart
4. Under Start tab, confirm correct data is selected and displaying
5. Under Charts tab, select chart type (bar, column, etc.) as well as side-by-side or stacked results
6. Under Customize tab, edit Chart Title, Legend, etc.
Since we set the end date to be the same as the end of the reporting period, the chart will show empty data for the future months.
Pro Tip: Use the full date range of the reporting period (including future months) in the chart. When the data updates on the Google Doc it will automatically update the chart on the Google Site. Otherwise, the chart will have to be updated manually in the Google Doc and then inserted again into the Google Site.
BONUS – Simplify Non-Branded Search & (Not Provided)
We’ve included our handy formulas for estimating what portion of (not provided) search traffic can be attributable to non-branded search traffic. You can find this in the “2.1 Non-Branded Search” preso sheet. You’ll see that the formula pulls in “Total Org Search”, “(not provided)” and “Nonb Search (no ‘not prov’)” from the data sheets (which pull from the Google API – see the red arrows).
Everything else calculates automatically. Here’s a screenshot of how the preso sheet is structured:
Here’s what the final chart looks like:
Now, it’s time to create the impressive and efficient online report using Google Sites – This is Phoebe’s part.
Step 6 – Create The Google Site Report Using Our Template!
Congratulations. Today is your lucky day. Literally L.U.C.K.Y. We are going to share with you the beyond-awesome Google Site Campaign Report template. It has everything you need, including everything we discussed above! (Talk about standing on the shoulders of giants.) We just want to make your life easier, because when you are happy, we are also.
Not familiar with Google Site? No worries. We will walk you through this!
1. Sign in to your Gmail account
2. Go to this link to get the template: https://sites.google.com/site/executivesummaryreport/
Here is what the report template looks like:
- 3. Click “USE TEMPLATE”
- 4. Create the report site name and url
a) Our template “Campaign Report” is automatically selected
b) Name your site
c) Create your own URL address for your site
d) Click “CREATE”
Step 7 – Customize Your Site: Create Your Own Banner
1. Create customized banner
2. On your Google site in the top right corner, click “More” then “Edit site layout”
3. Select and double click the banner
4. Click “Choose File” to upload your own banner.
Step 8- Pull In The Data To Create Your Own charts.
1. Make sure you are on the page with the chart you want to edit.
2. Go to the upper right corner, and click “Edit page(s)”
3. Select “Edit Chart”
4. Click the pointed link (on the below img) to get to the back end of the Google spreadsheet.
5. Now, you can enter your own set of data to create the chart for your campaign!
Step 9 – Delete/ Add A Page
1. It is super easy to delete a page – Go to the upper right corner, and click “More” and then “Delete page”.
2. To add a new page, click the “New Page” icon on the top right corner.
3. Then set the page name and location
a) Name your new page
b) Select a location of this page for the Navigation
c) Click “CREATE”
4. Feel free to use our title/section graphics template for your new pages. You can totally create your own set of graphics or use solely text instead.
Download our graphics templates:
Step 10 – Insert A New Chart
1. Go to the top right corner, click “Edit Page(s)”
2. Go to the top left corner, select: “Insert” > “Chart”
3. Select the Google spreadsheet which contains the graph/chart you want
4. Select an existing chart or build a new one
- a) Select the existing chart on the spreadsheet or “Build a Chart”
- b) To match the way we do our chart, put 800px for the width and 500px for the height.
- c) If you are “Building a Chart,” put the range of the data you want to be included on your chart under “Select sheet and range.”
- d) Go to “Charts” to choose your Chart format
Which one should I pick? Well, it depends on what you want to show:
- Comparison: Bar Chart, Line Chart
- Distribution: Scatter
- Composition: Pie Chart (Show simple share of Total)
e) Go to “Customize” to customize your chart! To follow our style, choose “Bold,” 16 font size and dark blue for Title.
d) Now, you have the chart on your page (Note: To make the chart visible, you will have to click “Save” on your upper right corner.)! If you want to edit the chart again, you can move your mouse on the top of your graph, then an editing panel will show up, so you can play with different options.
Now We’re Cooking With Gas!
If you haven’t yet checked out our Google Site report template, be sure to take a look.
Now you have (hopefully) everything you need to use our Google Site Report templates to make your own awesome reports. Don’t forget – not only do these online reports make it easy to show clients what’s happening, they also update whenever you refresh the data (or set a “trigger” to run scheduled updates). That’s a huge time saver in itself!
I hope you are as excited as we are. If you have any questions, please leave us some comments. We will try our best to assist!