CMOPS How To: Monthly Reporting

Last updated: March 17, 2026

This guide explains how to set up Client Marketing’s various reporting documents at the start of a new month, both to ensure retention of prior months’ data and that the new month’s data is correctly reported.

Ideally, these updates should be made on the first working day of a new month, although there is some leeway. The longer these tasks are left, though, the more inaccurate the prior months’ data becomes (due to changing client counts, etc.).

The documents to be updated are:

The ordering of steps in this guide is important to follow, as doing certain tasks out of turn can impact reporting elsewhere
​

1. Final Reports for Previous Month in the Ad Performance Dashboard

Before we archive the data and set up a new month in the Ad Performance Dashboard, we want to ensure that we have the full data from the previous month.

Manual Facebook Report:

  1. This report should already be up-to-date, as long as someone has taken care of the monthly budget check for the Client Sheet. We are just going to double-check before we set up for a new month.

  2. Click on the ‘Manual Facebook Ads Report…’ tab for the previous month.

  3. Click on cell A1 to view the formula there.

  4. Copy the URL from the formula and open it in another tab/window.

  5. Check the ‘Last edit was made…’ text beside the menu bar. As long as the last edit was made since the end of the previous month, we are good.

  6. If not, either wait until the budget check has been done, or if it is taking longer than expected, check in with whoever was responsible.

  7. Delete all the empty cells in the Manual Facebook Ads Report tab. If it’s not working, it means it’s pulling the empty cells from the imported sheet.

    • Hot tip: find the first blank/empty cell > hit shift + cmd + down arrow > all the empty cells will be highlighted > right-click delete cells

Manual Google Ads Report:

  1. You should receive the previous month’s Google Ads report via email on the 1st of the new month.

  2. Download it from the email.
    (If you haven't received the Google ads report, you can access it & download it from HERE)

  3. Open it in a new sheet and check that the columns match what is in the existing report.

    1. Delete Column H - Optimization Score

    2. Move Budge Name D to column C

    3. Move Currency Code to column D

    4. Insert a column for Currency and move to G

    5. Move AA Converted Currency Code to column M

    6. Delete the bottom rows with the totals

    7. Ensure all column headings match the ones in the Manual Google Ads Report tab and shift or delete any columns on your worksheet if necessary

  4. Delete any data in columns A-P of the ‘Manual Google Ads Report…’ tab if there is any

  5. Copy columns A-P from the downloaded Google Ads report and paste without formatting (Cmd+Shift+V) into the ‘Manual Google Ads Report…’ tab. (Copy & paste ONLY THE DATA, not the column titles)

  6. Ensure the formulas in columns Q, R and S AND columns AC to AH have been dragged down to include all rows with data in the report.

  7. Delete all the empty cells in the tab.

* The data will populate once we add data in the manual leads report tab. It will take time to load then.

Manual Leads Report:

  1. Go to the relevant report in Metabase

  2. Set the date range from the 1st of the previous month to the 1st of the current month.

  3. Click the blue play button.

  4. Click on the download icon at the bottom-right corner of the screen (looks like a cloud with a downwards arrow in it).

  5. Click on .csv and wait for the download.

  6. Open the downloaded file in a new sheet.

  7. Delete any existing data from columns A-J of the ‘Manual Lead Report…’ tab.

  8. Paste the data from the downloaded report into the ‘Manual Lead Report…’ tab.

  9. Edit cell K1 to reflect the date you are making the update. Double-click on the cell to select the date from a calendar.

  10. Delete all the empty cells in the tab.

2. Adding the New Month’s Reports to the Ad Performance Dashboard

Manual Facebook Report:

  1. Right-click on the ‘Manual Facebook Ads Report…’ tab for the previous month.

  2. In the menu that appears, click on ‘Duplicate’.

  3. Rename the duplicated tab from ‘Copy of…’ to ‘Manual Facebook Ads Report - MONTHYEAR’ with the date part in short form (e.g. Manual Facebook Ads Report - APR20).

  4. Go to cell A1 in your duplicated tab, which will contain a formula. In the formula, delete the existing URL and replace it with the URL of the manual ad report created for the new month. This should have been created when doing the monthly budget check and can be found in this folder.

  5. You will need to ‘allow access’ after pasting the new URL into cell A1.

  6. Scroll over to columns AE- AJ and ensure the formulas have been pulled down.

Manual Google Ads Report & Manual Leads Report:

  • Duplicate the tabs for these reports and rename them as with the Facebook report tab.

  • Delete the previous month’s data (columns A-P for the Google Ads report and columns A-J for the leads report) but do not delete anything else in these tabs. Note: don’t delete the actual columns, just the data in them.

  • To avoid any confusion from differing report formats, the data section of these tabs can be left blank

  • Manual Google Ads Report:

    • In the Manual Google Ads Report tab, update the formula in the ‘Conversions’ column (Q) to reference the current month (e.g. ‘APR20’ in the formula should be changed to ‘MAY20’). Update the formula in cell Q4 and drag it down the rest of the column. There are TWO dates in the formula that need to be updated.

  • Manual leads report:

    • Update the months in the formulas in columns L & M to look at the new month

Once you have created all three of these tabs for the new month, drag them to the left so that they are the first three tabs of all the manual reports.

3. Updating the ‘Overview’ Tab on the Ad Performance Dashboard

This needs to be updated before the summary tabs in order to preserve the previous month’s data.

  1. In the ‘Overview’ tab, select columns C, D, and E. Right-click and then select ‘Insert 3 right’.

  2. Select columns C, D, and E again. Copy them and then paste them without formatting (Cmd+Shift+V) in the newly created columns (F, G, and H).

  3. Merge cells F1, G1, and H1. Then replace the text ‘Current Month’ with the name of the previous month.

  4. Next, check that all the new columns (F, G, and H) are formatting correctly, i.e. CPC and CPL as $ values, CTR as a % and so on. If they are not, select the relevant cells and change the format.

  5. Update the ‘current month’ formulas to ensure they are using the new month’s data. The easiest way to do this is select the entire columns of C, D, and E.

  6. Then, press Cmd+Shift+H. In the ‘Find’ box, enter the abbreviation for the prior month. In the ‘Replace’ box enter the abbreviation for the new month. (E.g. APR20 in the ‘Find’ box, and MAR20 in the ‘Replace’ box.) Make sure ‘Also search within formulas’ is checked. Then, click ‘Replace all’.

  7. Once the new month’s data is added, the ‘Current Month’ section (columns C, D, and E) will automatically update.

4. Updating the ‘Summary’ Tabs on the Ad Performance Dashboard

These instructions are the same for the ‘SUMMARY (Facebook)’, ‘SUMMARY (Google Ads)’, and ‘SUMMARY (Leads)’ tabs.

Loom Video: https://www.loom.com/share/025c062b3cb34a0590229e7d9e09b840

  1. Select the existing columns for the previous month. For the Facebook tab, G-H. For the Google Ads tab, H-K. For the leads tab, H-I.

  2. Right-click, insert new columns to the right.

  3. Reselect the existing columns for the previous month, then paste them (with formatting) into the newly created columns.

  4. You will then need to edit some of the formulas to work properly in your new columns. You need the formulae to be exactly the same as in the existing column(s). The best way to do this is

    1. Select a cell with the working formula.

    2. Copy the formula from the formula bar at the top of the screen.

    3. Select the cell to paste it into, click into the formula bar and paste it there.

    4. Drag it down the rest of the new column.

    5. Here are the cells you should copy from and paste to:
      SUMMARY (Facebook): G8 > I8
      SUMMARY (Google Ads): H8 > L8, J8 > N8
      SUMMARY (Leads): H8 > J8
      ​

  5. For some reason, conditional formatting which shouldn’t be there gets added to some of the new columns. To do this, select the following cells and click ‘Format > Clear formatting’.
    💡 Ray’s Excel Tip: hit cmd + \ to clear formatting!
    ​
    Do this for:
    SUMMARY (Facebook): I8:I
    SUMMARY (Google Ads): L8:L, N8:N
    SUMMARY (Leads): J8:J
    ​

  6. Now, for each of the three tabs, select the cells with the average data (Rows 1-4) and paste them back into the same cells without formatting (Cmd+Shift+V). This is so that the data doesn’t change due to clients going live or churning.

  7. Next, change the name of the month for the left-most columns to the new month.
    ​
    This would be in the following cells:
    SUMMARY (Facebook): G5
    SUMMARY (Google Ads): H5
    SUMMARY (Leads): H6
    ​

  8. Now, we need to update these columns to look at data for the new month. The easiest way to do this is to edit the formula in the top cells and then drag it down.
    ​
    The cells to do this for are:
    SUMMARY (Facebook): G8
    SUMMARY (Google Ads): H8, J8
    SUMMARY (Leads): H8

  9. When you select these cells, type Cmd+Shift+H. In the ‘Find and replace’ dialogue box which appears:

    1. In ‘Find’, enter the month portion of the tab name of the previous month. E.g. ‘MAR20’

    2. In ‘Replace with’, enter the tab name for the new month. E.g. ‘APR20’

    3. In ‘Search’, select ‘Specific range’ and it will select the cell you have selected.

    4. Check the box for ‘Also search within formulas’.

    5. Click ‘Replace all’.

    6. Click ‘Done’.

    7. Drag the formula down the rest of the column

    8. Repeat these steps for each of the cells mentioned in step 8.

OLD STEPS - STOPPED REPORTING JULY 2023

5. Updating the Leading Indicators Sheet

**This sheet is all manual copy/paste info from data found on the list of sheets mentioned below**

The Leading Indicators contains high-level metrics on the performance of Client Marketing, enabling us to see any trends and anticipate how these metrics could relate to company-level metrics, esp. churn/downgrades.

This sheet can be found here. Most of the metrics in this sheet are taken

from other sheets updated in this process and simply pasted in.

  • Clients Managed Per FTE - This is a simple formula which requires the client count from the ‘Social Media // Client List’ (taken from the final version of the sheet before the end of the previous month) divided by the current headcount of the team.
    You can make adjustments to the headcount for people leaving or joining the team, as well as people in training or off for significant portion of the month. In these scenarios, people can count for a fraction rather than a whole person.
    E.g. Alex is off for two weeks in March. I would count him as 0.5 for that month.

  • % of Clients Meeting Budget SLA - This is taken from the ‘Client Ad Spend Reconciliation Sheet’ in the Summary tab. [skip for now; wait until Barbara does the monthly update around the middle of the month]

  • % of Clients Overspending - This is taken from the ‘Client Ad Spend Reconciliation Sheet’ in the Summary tab.[skip for now; wait until Barbara does the monthly update around the middle of the month]

  • % of Clients Meeting CTR Benchmark - This is taken from the previous month’s stats in the Ad Performance Dashboard (cell J4 in the ‘SUMMARY (Facebook)’ tab).

  • % of Clients Receiving Lead(s) - This is taken from the previous month’s stats in the Ad Performance Dashboard (cell K3 in the ‘SUMMARY (Leads)’ tab).

6. Updating the Data-Driven CX Dashboard

  1. In the Ad Performance Dashboard, a single step is needed. In the Summary (GA Ad Spent + Impression) AoF Q1-22 sheet, update B1 with the end-of-month date of three months ago. Eg: you are reporting at the beginning of March, and want to refresh the dashboard with the data for February. The date you would change B1 to would be 12-31-2022.

  2. In the CMOPS Dashboard tab, you only need to update one cell: E1. In that cell, enter the last day of the previous month.

  3. That's it! watch the magic happen 🎉