Skip to content
English
Sign in
Go to glamadelaide.com.au
  • There are no suggestions because the search field is empty.

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

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

  2. Click “Export posts” (see screenshot).

  3. 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

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

  2. Locate your Socials export file and click Open.

  3. 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.

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

Clean & Replace Old Data

  1. Double-click the existing tab named SOCIALS and copy the title. Add a full stop so it becomes SOCIALS.

  2. Double-click the new “Export All” tab and paste the title to rename it to SOCIALS

  3. The COMBO tab will now be reading from the new SOCIALS tab. Once you've confirmed this is working, you can delete the old SOCIALS. tab safely.

2. Export & Replace HubSpot Tasks Data

Export from HubSpot

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

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

  3. Download the file from your email. Click the ZIP file to expand it into a folder. You are looking for the file that is NOT labelled “hubspot summary”.

Import into Master Sheet

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

  2. Navigate inside the unzipped folder and select the correct Tasks export file.

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

  4. IMPORTANT STEP: Scroll to the bottom of the imported sheet and DELETE the two blank rows at the end. (HubSpot didn't used to include these rows but now they do. If these blank rows are not removed, reports will break.)

Replace Old Data

  1. Double-click the existing TASKS tab and copy the name. Add a full stop so it becomes TASKS.

  2. Rename the newly imported tab to TASKS

  3. Confirm the COMBO tab is reading correctly, then delete the old TASKS. tab once confirmed.

3. Export & Replace WordPress AdRotate Data

Export from WordPress

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

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

  3. Click the End Date column once, then click it again to sort by most recently expired ads.

  4. Click the Select All checkbox.

  5. Click Bulk Actions, ensure Export to CSV is selected, then click Apply. The export file will download.

Import into Master Sheet

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

  2. Locate your AdRotate CSV file and click Open.

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

Replace Old Data

  1. Double-click the existing ADVERTS tab and copy the name. Add a full stop so it becomes ADVERTS.

  2. Rename the newly imported tab to ADVERTS.

  3. Confirm the COMBO tab is updating correctly, then delete the old ADVERTS. tab once confirmed.

Data Prep Complete

You have now refreshed SOCIALS, TASKS, and ADVERTS. The COMBO tab is now pulling from fresh data and reports can be created.

You’re ready to cook up some reports!