Last Updated on September 14, 2017

For screenshots paired with this training please see here:

Before you create the Data Studio Report:

  1. Make sure we have access to AdWords, Google Analytics, and Search Console
    1. Make note of which Google Analytics view you will want to use
    2. Make note of which Search Console account you will want to use (http, https, www, non-www)
  2. Setup the first Search Console google sheet (Group by Query and filter by country usa if you only want usa data)
    1. Instructions for this coming soon
    2. Copy the templates from the Monthly Reports folder on sebo.marketing@gmail.com’s Google Drive
    3. Grab the oldest full month of data – rename the sheet to “MMM YYYY” example: Jun 2017
    4. Copy that data to the “Previous Month” sheet
    5. Setup the backup and run immediately to grab the previous month’s data.
    6. Go to scripts and run the “Monthly Cumulative Totals” script (you will have to authorize it the very first time)
    7. Go to the Monthly Cumulative Totals sheet to make sure the script ran.  Change the date to the last day of the month of the report ran
    8. Go back to scripts and run “copy Previous Month” followed by “Monthly Cumulative Totals” script.  This should now add the most recent month to the “Monthly Cumulative Totals” sheet.
    9. Now schedule the scripts to run on the 3rd day of the month.  Copy previous Month script to run at 2am and “monthly Cumulative Totals” script to run at 3am.
    10. Go to monthly cumulative totals sheet and you will notice that Impressions, clicks, ctr, and avg. position are all blank for the first month of data but not the last.  You will have to manually grab that data from Search console.  Make sure you use the correct date range and country filter if you filtered the data by country when gathering those numbers.
  3. Setup the second Search Console google sheet (Group by Query and Device {in that order} and filter by country usa if you only want usa data)
    1. Follow the instructions from the first google sheet setup except that you don’t have to do the last step (manually gather impressions, clicks, etc) since it doesn’t apply to this report.

Create the Data Studio report

  1. Open up the GoEngineer Report (or whichever report you want to copy if another client report is different than the default and you want that report style)
  2. Click on the “Make a copy of this report” icon
  3. You will see a popup that will show the “Original Data Source” and the “New Data Source”.  The bulk of this training will be to change the “New Data Sources” to the new client’s datasets.
    1. Google Analytics
      1. Click the arrow
      2. Click Create New Data Source
      3. Choose Google Analytics
      4. Search for the client’s Google Analytics Account
      5. Choose the correct property and view and then press the connect button
      6. We won’t have to make any metric changes for Google Analytics so on the next screen just press “Add to Report”
    2. Search Console
      1. You will notice that there are 2 search console data sources.  One says “(URL)” and the other says “(Site)” at the front of the name.  As you follow the same steps for Google Analytics when connecting the two Search Console data sources be sure to choose site for site and url for url
      2. When you get to the screen that shows the various metrics add “(Site)” or “(URL)” before the name for future convenience sake then add to report.  Do this for both URL and Site data sources
    3. Search Console Google Sheets
      1. Connect the 2 search console google sheets that were created in the pre data studio steps
      2. You are going to connect to the “Monthly Cumulative Totals” sheet for both.
      3. On the metrics screen for both data sets you will click on the 3 dots after “Date” and press the “Duplicate” button
      4. Rename “Copy of Date” to “Month” and change the type to “Year Month (YYYYMM)
        1. When updating the metrics, sometimes you will notice that some of the fields are getting typed wrong.  For example a date gets labeled as a number or a number gets labeled as a date.  You will need those to be fixed for the reports to work correctly.This example, Desktop Clicks should match Desktop CTR – Number and Sum
    4. AdWords
      1. On the metrics screen you will make the following changes:
        1. Change the connection name from “AdWords” to “*Client Name* AdWords”
        2. Rename “Total conv. value” to “Est. Revenue”
        3. Duplicate “Month” and name the copy “Month (MM)” and change the date format to “Month (MM)”
        4. Create the following custom fields (currency type) with their respective formulas
          1. “PPC ROI” = Est. Revenue / Cost
          2. “Sebo PPC Mgmt Fee” = (Conversions – Conversions + 1) * 500
            1. Change 500 to whatever the client pays Sebo for PPC packages
          3. “PPC Profits” = Est. Revenue – Cost – Sebo PPC Mgmt Fee
  4. Once you have connected all of the correct data to the copy, press the “Create Report” button and then watch as the report is magically created.
    1. Rename the report to “*Client Name* Report”
    2. Replace the logo with the new client’s logo
      1. You may have to adjust the size of the logo as well as move the placement of Google Analytics label and the labels on the other sheets.
    3. Fix the broken reports on the Search Console page by changing the “time dimension” to “Month” for each broken metric
      1. If for some reason there are other errors then it is possible that some of the data got configured wrong in the duplication process.  You will have to update it.
        Desktop Clicks is missing here.  It should be a number and sum.  Not a date
    4. Fix the broken metrics on the AdWords page.
      1. The four lower metrics time dimension should be set to “Month (MM)”
      2. For the bottom two metrics you need to update the metric to match the label above the corresponding metric. (PPC Profits & PPC ROI)
      3. For the top metrics, update them to be “PPC Profits”, “PPC ROI”, and “Est. Revenue”
  5. At this point, the Data Studio report should be fully functional.  You may want to make adjustments to the reports based on the needs of each client.