dash/REPORTING Starter Template - DPST, Ads, MWS

Merchant Type: Seller (Seller Central Only)

Paid or Free?: Free

click here to see the live template in Google Data Studio

Google Data Studio Setup Instructions

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

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 ‘keywordtargetingmetric’, 'sellermonthmetric', 'business reports', and 'orders metric' 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: business_reports_dpst_item

​​First, click this link: business_reports_dpst_item 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 business_reports_dpst_item
where SellerID in (71)

 

  • For (1) the name you choose for the data source is up to you, but for simplicity/consistency we recommend: “tablename - merchantname - 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: business_reports_dpst_total
​​
Creating the remaining data sources will follow the same process used to create the first data source above.

Click this link: business_reports_dpst_total 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 business_reports_dpst_total

where SellerID in (71)



3) DATA SOURCE #3: keywordtargetingmetric_3mo

Click this link: keywordtargetingmetric 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  

   SellerID,

   SellerName,

   MarketPlaceName,

   DateTime,

   SearchTerm,

   SUM(Cost) AS Spend,

   SUM(Sales) AS 'Ad_Sales',

   SUM(Orders) AS 'Ad_Orders',

   SUM(Impressions) AS Impressions,

   SUM(Clicks) AS Clicks

FROM

   keywordtargetingmetric_3mo

WHERE

   SellerID IN (71)

GROUP BY SellerID, DateTime , SearchTerm



4) DATA SOURCE #4: sellermonthmetric

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 (71)

5) DATA SOURCE #5: mws orders metric by Customer Type


Click this link: mws_orders_metric 13mo by Customer Type 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  

   om.SellerID,

   om.SellerName,

   om.MarketPlaceName,

   DATE_FORMAT(om.PurchaseDate,'%Y-%m-01') as Date,

   IsRepeatCustomer,

   count(distinct BuyerEmail) as CustomerCount,

   (SUM(om.ItemPrice) - SUM(om.ItemPromotionDiscount)) AS ShippedRevenue,

   SUM(om.Quantity) AS ShippedUnits,

   COUNT(DISTINCT om.AmazonOrderId) AS ShippedOrders

FROM

   mws_orders_metric om

WHERE

   om.SellerID IN (71)

       AND om.PurchaseDate >= DATE_FORMAT(CURDATE(), '%Y-%m-1') - INTERVAL 13 MONTH

       AND om.OrderStatus LIKE 'Shipped%'

GROUP BY SellerID, DATE_FORMAT(om.PurchaseDate,'%Y-%m-01'), IsRepeatCustomer

 

6) DATA SOURCE #6: mws orders metric by Month, SKU, Customer Type


Click this link: mws_orders_metric 3mo by Month, SKU, Customer Type 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  

   om.SellerID,

   om.SellerName,

   om.MarketPlaceName,

   DATE_FORMAT(om.PurchaseDate,'%Y-%m-01') as Date,

   IsRepeatCustomer,

   om.ASIN,

   om.SKU,

   om.ItemName,

   (SUM(om.ItemPrice) - SUM(om.ItemPromotionDiscount)) AS ShippedRevenue,

   SUM(om.Quantity) AS ShippedUnits,

   COUNT(DISTINCT om.AmazonOrderId) AS ShippedOrders

FROM

   mws_orders_metric om

WHERE

   om.SellerID IN (71)

       AND om.PurchaseDate >= DATE_FORMAT(CURDATE(), '%Y-%m-1') - INTERVAL 3 MONTH

       AND om.OrderStatus LIKE 'Shipped%'

GROUP BY SellerID, Date, om.SKU, om.IsRepeatCustomer

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

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!