Last Updated on March 3, 2025

We have broken this down into a few easy steps:

  1. Add Client to MC’s Report Automation Sheet
  2. Setup Search Console Data Sheet (pt. 1)
  3. Search Console Data Sheet (pt. 2)
  4. Connect Google Ads to MC Report Automation

Add Client to MC’s Report Automation Sheet

For both the email report and the Looker Studio report we are pulling data from Google Analytics, Search Console, and Google Ads.  You should make sure we have access to these accounts before you do anything else.

We should have access to our client’s tools using the agency@sebomarketing.com email address.  The scripts and reports should also be created using the agency@ email.  In some cases you may have to use a different email to access Google tools and run reports such as white label clients.

  1. Once you know we have the appropriate access then open up the MC Email Automation Sheet (with Agency@ Chrome User) and click on the correct MC’s sheet.
  2. Ad a new row and fill out the fields you can (put MCs info in client info if you don’t know the client info) – Looker Studio Link and SC Data Sheet Link will be filled out later.  For the 2 date fields, just copy the formula from the cell above.  The faded fields will be addressed later.

If a client is only doing SEO then we don’t want to include the Google Ads Customer ID on their line item in the email automation sheet.  If we aren’t reporting on any SEO or Search Console stuff for a client then don’t include the Search Console Data Sheet URL in the list so that we only report on Google Ads.

Search Console Data Sheet (pt. 1)

Please use agency@sebomarketing.com to complete these steps.  If sebo.marketing@gmail.com or some other email has the access to some components then complete those steps with that account but do everything else with agency@sebomarketing.com

In this step you will:

  1. Create the client’s SCDS and move it to the client’s folder in the Shared Drive
  2. Setup the connection between MC Email Automation and SCDS
  3. Setup the Google Analytics data capture
  1. Create the Search Console Data Sheet (SCDS) from template by going here: https://docs.google.com/spreadsheets/u/0/?tgif=d&ftv=1
    You will choose the “*Client Name* SCDS” Sebo Template.
  2. Rename the file and move it to the client’s folder in the shared drive “Client Manager” folder architecture.
  3. GA4 Instructions:
    • Go to the “Configuration GA4 reports” sheet and populate the Property ID and the Property Name in columns B, C, D, & E.
      • Property ID can be found by going to the property -> property settings in Google Analytics.
    • Then go to the “Extensions” dropdown -> Reporting for Google Analytics 4 -> Run Reports. If it comes up with some errors then make sure you are running it with the account that has access to Google Analytics.
      • If the CR reports give an error it means that either this account doesn’t have CallRail or the CallRail Google Analytics setup wasn’t done yet.  If they have CallRail please do that training and then come back to finish this.  If they don’t have CallRail then type in “TRUE” in the “Skip” cells of the configuration so that those reports will be skipped going forward.
    • Then you need to schedule the Google Analytics script to run each month by going to Extensions -> Reporting for Google Analytics 4 -> Schedule Reports and have it run every month on the second.
  4. Copy the Client’s SCDS URL and paste it in the Client Email Report Automation sheet in the appropriate place (SC Data Sheet Link).
  5. Go to the “Email Stuff” sheet on the SCDS and update J1 with the URL for the specific MC’s Email Automation Sheet as well as the client’s name as listed in the Email Automation sheet.  If these two items are entered correctly then it will either populate with queries from the other sheet or it will ask you to validate the connection.

Search Console Data Sheet (pt. 2)

Now that you have the easy stuff out of the way it is time to gather the Search Console data for the last few months and populate the Monthly Cumulative Totals and Position Log sheets.  Don’t worry.  The process isn’t complicated.  There are just a lot of steps involved. We are going to gather the previous 3 months of data unless the MC specifically asks for data beyond the last 3 months.

  1. First, we will grab the data before last month.  So if today is April 13th then we will capture January and February’s data in this step.
    1. Under Add-ons menu launch Search Analytics for Sheets sidebar. (Remember this still has to be done with the account that has access to Search Console)
    2. On the requests tab choose the verified site to grab data from.  Ideally you will use the sc-domain but if not, then choose the primary URL variant.
    3. Choose the date range for 3 months ago (if today is April 13th then you will choose January 1st to January 31st). For this one you will not have anything selected under “Group By”.
    4. Before you click the “Request Data” button select the sheet to the far left, then click the button. It will create a sheet with a timestamp.  Rename the sheet to the first three letters of the month you ran (In this case – Jan”).
    5. Move this sheet to the far left and now go back to the sidebar and under “Group By” choose “Query” and make sure the date range is still the same.  Push the button again. This time name the sheet “Mmm YYYY” (Jan 2021).
    6. Repeat steps 3-5 with the next month (Feb 2021) using the same format.
    7. If the MC wants data beyond the last 3 months then repeat again with the additional months of data.
    8. Don’t capture last month’s data yet.  We will do that later.
  2. From the top menu select “Sebo Functions” -> “Show Sidebar”.  You will see two input fields along with a “Compile Data” button.  Start entering in the data one month at a time. The first field is the sheet containing the Queries so “Mmm YYYY” (Jan 2021). The second field is for the month totals so “Mmm” (Jan). And press the “Compile Data” button.  This button will then grab the data from the totals sheet and move it to another sheet and then delete that sheet. Repeat this for all of the months of data you captured – by default just 3 months ago and 2 months ago (January and February in my example).
  3. Reopen the Search Analytics for Sheets sidebar and this time go to the “Recurrent Requests” tab (or “Backups”).  Choose the correct “Verified Site” and choose “Query” for “Group By” and check the box for “Run a backup cycle right away”.  Finally, press the “Enable Backup” button and wait for it to run.
  4. Now reopen the Sebo Functions Sidebar and press the button in step 6 – “Grab Last Month Data and Schedule”. It will take a second or two to run.
  5. Then press the “Populate Position Log Queries” and let it do its thing.  When it finishes finish off by pressing the “Filter Down Queries” button.
  6. Go to the Monthly Cumulative Totals sheet and delete the first row of data. This row is most likely not needed or repeated.
  7. Within the position log sheet you will need to change the date of the last column to the month of the first data you gathered (Jan in my example). It is important that you don’t delete this last column as formulas are pulling from this column.
  8. Then delete or add any columns as needed.
  9. Check the “Email Stuff” sheet and make sure all of the data is now populated.

Now that the Email Automation sheet and the Search Console Data Sheet are all setup you can create the Data Studio report.  Refer to the next page for how to create the Data Studio report for a client.

Create a Data Studio Report