click here to see our v1 advertising reporting template

*V1 TEMPLATE dash/DATA STUDIO - Amazon Advertising Report (sample_data)

Merchant Type: Seller or Vendor (Seller Central or Vendor Central merchants)

Paid or Free?: Free

ADVERTISING REPORT TEMPLATE
Google Data Studio Setup Instructions

​With dash/DATA STUDIO you have the freedom to create your own custom reports and dashboards using a reporting platform of your choosing (Google Data Studio, Tableau, Zoho, Power BI, etc.).  Familiarizing yourself with the data and building reports can take time, energy, and resources, so we’ve created some basic Google Data Studio templates to get you off to a fast start and using your data right away. 

The below templates were built for Google Data Studio (GDS) as the platform is ubiquitous, it’s the simplest solution we’ve found, and most importantly… it’s free.  Of note, you will need a Google account (i.e. Gmail) to utilize the templates. GDS does have limitations on the amount of data you can process/visualize, so if you are managing large brands and data sets, we recommend exploring a more robust platform like Tableau or Zoho Analytics for your reporting purposes.

With that said, by following these instructions you’ll have AMAZON ADVERTISING REPORTS built using your dash data in no time!

STEP 1: Gather your Database Credentials and a SellerID value from the dash platform

​You will need two pieces of information from the dash platform to create your data sources for each merchant in GDS:

  • Your database credentials, which GDS uses to establish a connection to your database data
  • The SellerID value of the merchant you want to report on, which GDS references to pull the corresponding data of a given merchant.

1) DATABASE CREDENTIALS: Your database credentials are available in the dash platform under Setting > Database Admin. The credentials are used by GDS to establish a connection to your database data.

​You’ll be prompted to enter your “Master Password” which you received with your initial onboarding email from dash titled “dash/DATA STUDIO - Welcome to dash! Password and Credentials enclosed”. 

​Once you’ve entered the Master password and "VERIFY ACCESS", you’ll be shown your ‘Host Name’, ‘Username’, ‘Database’ (Schema), and ‘Password’ (Hidden). The Port value is not required.

Copy these credentials (via a screen grab or notepad), as they’ll be used in GDS to create data sources for your reports in Step 3.

2) SELLER ID: For GDS to recognize and connect to a specific Merchant account you will need to reference a SellerID.  The SellerID can be found under Settings > Import Data.

*NOTE: If you’d like to show the data of multiple merchants, or marketplaces of a merchant, in a single report in GDS, you will need to gather all relevant SellerID values of the merchants/marketplaces.

We generally recommend including just one merchant in each Google Data Studio report and corresponding data source. Adding multiple merchants to the report and data sources can lead to slow loading times as Google Data Studio attempts to load large amounts of data from your database. 

STEP 2: Login to Google Data Studio

You will need a Google account for this step, so if you don’t have one already, you can create one here: Google Account Creation.

​While logged into an existing Google account or your newly created Google account, navigate to Google Data Studio. We recommend you double-check the account to be sure it is the correct one. Once you copy the report and data sources, it will be tied to this account.

STEP 3: Create your Data Sources

Our pre-built Google Data Studio Advertising Reporting templates leverage four tables (data sources) within your database: the ‘campaignmetric’, ‘keywordtargetingmetric’, ‘productadmetric’ and 'sellermonthmetric' tables.  The templates are currently connected to demo data so you will simply be copying the current data sources and updating them to use your own credentials. 

1) DATA SOURCE #1: campaignmetric

​​First, click this link: campaign metric data source

​You may need to copy and paste the link manually if it doesn’t open up to the screen below.  Also, if it’s your first time logging into Google Data Studio, you may need to perform a quick setup of your account.

​Then click the 'copy data source' icon in the upper right hand side of the screen and click "COPY DATA SOURCE" in the pop-up. 

​If you see an initial error message at this step (see below), no worries…it’s because you’ve yet to authenticate your data source which we’ll do next. Just click “OK” to clear the message. Also, depending on your browser/settings, you may have been directed to a new tab/window.  The original tab is no longer needed so feel free to close it.  

​From the new (copied) data source tab you’ll want to (1) rename the data source, (2) enter your database credentials, (3) update the custom query with your Seller ID value, and (4) authenticate the connection. (More details below)

  • *NOTE: If you do not see the custom query text after copying the data source per #3 above, refresh the page and the query should appear. It's important that the query is present for the data source to be copied properly.
    • Alternatively, you can copy the below query into your data source:
      • select * from campaignmetric_3mo
        where SellerID in (42,1)
  • For (1) the name you choose for the data source is up to you, but for simplicity/consistency we recommend:                             “MySQL - themerchantname - campaignmetric - SellerID X” with “X” being the Seller ID you captured in Step 1.2.
  • For (2), to authenticate and complete the connection, enter the database credentials you captured in Step 1.1 for ‘Host Name’, ‘Database’ (Schema), ‘Username’, and ‘DB User Password’. Once you’ve entered the credentials, click “AUTHENTICATE” to establish a connection to your database.
  • Then (3), update the custom query with your SellerID value captured in Step 1.2, leaving the rest of the query as it is.
    • NOTE - if you’d like to include multiple merchants in the report, enter the SellerID values of each in the parentheses, separating each SellerID value by a comma. If you are only reporting on a single merchant, simply enter the single SellerID value in the parentheses and delete any commas in the parentheses.
    • i.e., “where SellerID in (71,73)”
  • Finally (4), authenticate your database


​Once you’ve authenticated your database, click “RECONNECT” in the top right-hand corner (see below). You may see some Semantic field name changes and/or Additional fields in the popup screen that appears. Click “APPLY” to finalize your data source.

2) DATA SOURCE #2: keywordtargetingmetric
Creating the keywordtargetingmetric data source will follow the same process used to create the campaignmetric data source.

Click this link: keyword targeting metric data source

Then click the ‘copy data source’ icon in the upper right-hand side of the screen and click "COPY DATA SOURCE" in the pop-up.

Repeat the steps you used to create the first data source to complete the data source creation.

For reference, the template query for this data source is:
select * from keywordtargetingmetric_3mo
where SellerID in (42,1)

3) DATA SOURCE #3: productadmetric
Creating the productadmetric data source will follow the same process used to create the campaignmetric data source.

Click this link: product ad metric data source

Then click the ‘copy data source’ icon in the upper right-hand side of the screen and click "COPY DATA SOURCE" in the pop-up.

Repeat the steps you used to create the first data source to complete the data source creation.

For reference, the template query for this data source is:
select * from productadmetric_3mo
where SellerID in (42,1)

4) DATA SOURCE #4: sellermonthmetric
Creating the sellermonthmetric data source will follow the same process used to create the campaignmetric data source.

Click this link: seller month metric data source

Then click the ‘copy data source’ icon in the upper right-hand side of the screen and click "COPY DATA SOURCE" in the pop-up.
​​
Repeat the steps you used to create the first data source to complete the data source creation.

For reference, the template query for this data source is:
select * from sellermonthmetric
where SellerID in (42,1)

STEP 4: Copy and Connect the Google Data Studio Report Template

Now that your data sources are created and connected to your database, we’ll need to copy the report template and connect it to your newly created data sources.

First, click this link: Google Data Studio Report Template

Then, click the “MAKE A COPY OF THIS REPORT” button in the upper right hand corner of the screen.  NOTE: Do not proceed further until you've updated your data sources as instructed below.

After copying the report, you’ll see the below pop-up window where you will select the “New Data Sources” you just created.  

For each respective data source, select the your corresponding newly created data source from the drop-downs for ‘campaignmetric’, ‘keywordtargetingmetric’,  ‘productadmetric’, and 'sellermonthmetric.

Lastly, click “COPY REPORT”

​A new window will launch with your copy of the report, and you’ll see data automatically begin to populate as the report refreshes.  Google Data Studio will open the report in edit mode by default, and you can update the report title to your liking.  Once you’ve done that, click “VIEW” and you’re done!

A Few Additional Items to Note:

The report will automatically save to your Google Data Studio account under ‘Reports’. The corresponding data sources can be found under ‘Data Sources’.

We have built filters (A) into the report which are accessible in ‘View’ mode.  Applying these filters will update the data in the page of the report you are currently viewing.

We’ve also built ‘page buttons’ (B) directly into the report.  Clicking these in ‘View’ mode will redirect you to the corresponding page.  The various pages are also accessible through Google Data Studio’s Left Nav menu.

With the idea that you may be using the template for internal/external reporting, we’ve built text boxes (C) into the template.  These can be populated as needed in ‘Edit’ Mode.

And just a reminder, Amazon Advertising Data is only available for the trailing 60-days upon initial connection to your db.  We store data ongoing from your first day of connection so you never lose access, but we can only pull data going back to 60-days prior to the first day the merchant account was connected/activated.  Depending on the timing of these events, you may see a partial month of data in the charts/tables for the first month of the time range available.