Import the Reporting Program

In the workspace for the client, check the "Operational" programs folder and look for the "Manual Reporting" Program. If it does not exist, import it into the workspace from the COE

Select the operational folder for the dealer and leave the default conflict rules for this program.

Rename the program with the dealer initials and remove the "- 1"

Update the Client Sheet

Navigate to the client sheet and add two tabs. One labeled "Marketo User Data" and another labeled "Marketo Email Data"

Pulling the reports

In the report program, there should be two reports. "Email Performance" and "People Report"

Click into the email performance report and click "Export" in the bottom-right corner

Open the exported data spread sheet and copy it into the "Marketo Email Data" tab

Next, click into the People Report.

click into the "People" tab

Click on the "View" at the top and select "Monthly Person Report"

At the top of the page, open the "list actions" menu and select "Export list"

Choose "Visible Columns" and then Export

Open the exported list and copy/paste the data into the "Marketo User Data" tab in the client sheet.

Right-click on the "Last Updated" column

Sort the sheet by lead score

Add a column labeled "Hot Lead" Be sure it's labeled EXACTLY as written here otherwise it will break. 

Add a "1" in the Hot Lead column for every row with a lead score over 100

Merging Sharpspring Data

Run an email report for the previous month with ALL emails included

In Sharpspring, go to the "Analytics" drop-down and select "Email Reports"

Use the drop-down menus to create an email report with all of the emails sent in the last month

Click "Build Report"

Export the report to CSV

Open the report and sort by "Deliveries"

Open the email data tab in the client sheet side by side with this export. Copy the data into the correct columns on the client sheet.

The columns will NOT line up. Copy each value individually and paste into the correct column in the client sheet.

Syncing Google Sheets

Go into Ninja Cat and sync up these as data sources for the client.

Click on your name in the upper right hand corner and select ‘SETUP’ from the drop down menu. You’ll be taken to the Data Sources set up page. Find ‘Google Sheets - SEO Account’ among the sources (it may be on the second page) and select it.

On the Network Settings page, select ‘EDIT MAPPING’. On the new mapping list page, you should see a bunch of different options.

Click on Marketo Email Stats. Do not edit anything on this initial page you are taken to! This controls the mapping for all accounts. Instead, you want to click on ‘CONNECTIONS’ in the tabbed navigation. You should see a list of our clients here. These are accounts that have already been synced.

Select the blue ‘ADD CONNECTIONS’ button and in the table it opens, you’ll need to add the following:

1. Unique Name. This should be the Dealer Name exactly as it appears on the title of their client sheet.

2. Google Spread Sheet. This is where you select the Client Sheet you want to sync to.

3. Google Sheet. This is where you will find the corresponding tab. Since we are doing work done currently, you will want to select ‘Marketo Email Data’.

Repeat for the Marketo User Data tab