How to Prep Data for Client Reports

How to Prepare Data for Client Reports

Creating reports is a two-phase process:

1. Prepare the data (done once per day-ish)

2. Create reports (can be done multiple times using the prepared data)

Think of this like prepping a kitchen. You prep once, then you can cook up multiple reports. This guide covers Phase 1: Data Preparation.

Each day (or when needed), you will export and refresh three data sources:

1. HubSpot Social Posts Data

2. HubSpot Tasks Data

3. WordPress AdRotate Data

All exported data is uploaded into the master Google Sheet and replaces the old data tabs.

Master Google Sheet:
https://docs.google.com/spreadsheets/d/1picHzy_LXatys1EZafkQDMkeuyPENlowy_lv0gdMiG4/edit?gid=351458795#gid=351458795

1. Export & Replace HubSpot Socials Data

Export from HubSpot

• Go to: https://app-ap1.hubspot.com/social/43811754/manage/published

• Click “Export posts” (see screenshot).

• Select your email from the dropdown and click Export to confirm (see screenshot). Wait for the email and download the file once delivered.

Import into Master Sheet

• Open the Master Google Sheet. Click File → Import (see screenshot).

• Select Upload → Browse (see screenshot). Locate your Socials export file, click Open.

• In the Import File popup, change the import location to “Insert new sheet(s)” (see screenshot), then click Import data. Wait for the upload to complete.

• Three new tabs will appear. Only “Export All” is required. Delete “Last 7 Days” and “Previous Month” sheets.

Clean & Replace Old Data

Now it's time to replace the old Socials data sheet with this new one you've imported. The proper way to do that is by renaming each tab.

• Double-click the existing tab named SOCIALS and copy the title. Then, type a full stop at the end, so the tab title becomes SOCIALS. with a full stop.

• Next, double-click the new “Export All” tab and paste the title to rename it to SOCIALS without a full stop.

• The COMBO tab will now be reading from the new SOCIALS tab. Once you've confirmed this is working (the COMBO tab isn't showing an error), you can delete the old SOCIALS. tab safely.

And you're done! The Master Google Sheet is now working with up to date Socials data.

2. Export & Replace HubSpot Tasks Data

Export from HubSpot

• Go to: https://app-ap1.hubspot.com/reports-list/43811754/120972159/

• Click Actions → Export unsummarised data (see screenshot). Click Export in the popup.

• Download the file from your email. Click the ZIP file to expand it into a folder. Inside the expanded folder, you are looking for the file that is NOT labelled “hubspot summary”.

Import into Master Sheet

• Open the Master Google Sheet. Click File → Import. Select Upload → Browse.

• Navigate inside the unzipped folder and select the correct Tasks export file (aka NOT the one labelled “hubspot summary”).

• In the Import popup, choose “Insert new sheet(s)” then click Import data. Wait for the upload to complete.

Replace Old Data

Now it's time to replace the old Tasks data sheet with this new one you've imported. The proper way to do that is by renaming each tab.

• Double-click the existing TASKS tab and copy the name. Then, add a full stop so it becomes TASKS. with a full stop.

• Next, double-click the title of the newly imported tab. Paste text to change the tab title to TASKS without a full stop.

• Confirm the COMBO tab is reading this new sheet correctly (isn't showing an error), then you can delete the old TASKS. tab safely.

3. Export & Replace WordPress AdRotate Data

Export from WordPress

• Go to: https://glamadelaide.com.au/wp-admin/admin.php?page=wpda

• Expand the first dropdown, then expand Views, find vw_adrotate_by_ad, and click Explore (see screenshot).

• Click the End Date column once, then click it again to sort by most recently expired ads. Then, click the Select All checkbox (see screenshot).

• Click Bulk Actions, ensure Export to CSV is selected, then click Apply (see screenshot). The export file will download.

Import into Master Sheet

• Open the Master Google Sheet. Click File → Import. Select Upload → Browse.

• Locate your AdRotate CSV file and click Open.

• In the Import popup, choose “Insert new sheet(s)” then click Import data.

Replace Old Data

Now it's time to replace the old Tasks data sheet with this new one you've imported. The proper way to do that is by renaming each tab.

• Double-click the existing ADVERTS tab and copy the name. Then, add a full stop so it becomes ADVERTS. with a full stop.

• Next, double-click the title of the newly imported tab. Paste text to change the tab title to ADVERTS without a full stop.

• Confirm the COMBO tab is reading this new sheet correctly (isn't showing an error), then you can delete the old ADVERTS. tab safely. 

Data Prep Complete

You have now updated the SOCIALS, TASKS, and ADVERTS data. The COMBO tab is now pulling from fresh data and reports will use up-to-date information.

You’re ready to cook up some reports!