How to Create Power Query – Brand, Availability, Price

Step 1: Prepare Resources

  1. Create your own copy of our Google Sheets Power Query Template:
  2. Download Excel template:  
  3. Download PPC Robot template:

Step 2: Setup PPC Robot Report

1. In PPC Robot create a new Sklik Custom Report.

2. At the bottom of the screen click “Upload parameters from file” and use the template you downloaded.

3. Go to the next screen and name the report, keep the output type to Google Spreadsheets, set the update frequency according to your needs (i.e. how often you will update the campaigns in Sklik), and select the accounts you will be creating the Power Query for.

4. Create and run the report(s).

5. Go to the report in Google Spreadsheets and click “Share” and then “Get shareable link”, then click “Done”.

6. This next step works best with the Chrome browser.  In the Google Sheet click File > Download as > Comma-separated values.

7. (In Chrome) Go to Window > Downloads and right click on the grey URL of the sheet you just downloaded and click “Copy”.

8. Save this Sklik Output for PQ URL (in notepad or in a word processor) for later.

Step 3: Input Data in Google Sheets Template

1. If you haven’t yet, make a copy of our Google Sheets Power Query Template (https://docs.google.com/spreadsheets/d/1nlWf9M2rsJy4xY7-UhDt_HJm3_FCimE8UYfUTDQX-WM/edit?usp=sharing) by clicking on File > Make a copy, and adding it to your Google Drive.

2. In your Power Query Template, you can use the default Availability values or remove the ones you don’t need.  Please note – these are in Sklik format and all available options are there, so you do not need to add anything, you can simply remove the ones you don’t need or use it as is.

3. Then click on the Price Ranges tab and enter the price ranges you would like to use for the groups in Sklik.  Follow the same format in the template, and we recommend using 2-5 groups only. If you have a lot of information in your zbozi feed, we recommend using fewer price groups.

4. In the Product and Ad Groups Output Format tabs you can change the campaign name and budget if necessary.

5. Click “Share” and then “Get shareable link”, then click “Done”.

6. This next step works best with the Chrome browser  Do #6-8 for each tab in this document While you are on the Availability tab click File > Download as > Comma-separated values.

7. (In Chrome) Go to Window > Downloads and right click on the grey URL of the sheet you just downloaded and click “Copy”.

8. Save this Power Query Template – Availability (and all others) URL (in notepad or in a word processor) for later.

Step 4: Input Data in Excel Template

Please note – you need to be able to run Power Query in Excel, which is only available in the Windows version

1. Open the Brand Availability Price Template in Excel and click on the Data tab then click on “Queries & Connections”.  ***If you see “SECURITY WARNING External Data Connections have been disabled” then just click “Enable Content”.

2. In the Queries & Connections column double click on “***Zbozi Feed” to open Power Query Editor.

3. If you see this warning just click “OK”.

4. On the left side of the editor you will see all the Queries, the ones with “***” need your attention.

5. We will go from top to bottom, starting with “***Zbozi Feed”.  On the right, under APPLIED STEPS, click on “Source”, then in the middle of the screen you will see the formula bar with the text, “INSERT FEED HERE”.  This is will you will paste your zbozi feed URL (replacing the text).

6. Next, we will click on “***Availability” in the Queries browser on the left.  Again, we will click on “Source” and replace the text, “INSERT AVAILABILITY GOOGLE SHEET URL HERE” with the URL we saved early from the Availability tab in the Power Query Template.

7. Next, we will click on “***Price Ranges” in the Queries browser on the left.  Again, we will click on “Source” and replace the text, “INSERT PRICE RANGES GOOGLE SHEET URL HERE” with the URL we saved early from the Price Ranges tab in the Power Query Template.

8. Next, we will click on “***Product Groups Output Format” in the Queries browser on the left.  Again, we will click on “Source” and replace the text, “INSERT PRODUCT GROUPS OUTPUT FORMAT GOOGLE SHEET URL HERE” with the URL we saved early from the Product Groups Output Format tab in the Power Query Template.

9. Next, we will click on “***Ad Groups Output Format” in the Queries browser on the left.  Again, we will click on “Source” and replace the text, “INSERT AD GROUPS OUTPUT FORMAT GOOGLE SHEET URL HERE” with the URL we saved early from the Ad Groups Output Format tab in the Power Query Template.

10. Next, we will click on “***Current Groups Sklik” in the Queries browser on the left.  Again, we will click on “Source” and replace the text, “INSERT CURRENT GROUPS SKLIK GOOGLE SHEET URL HERE” with the URL we saved early from the Sklik Output for PQ URL.

11. Finally, we will click on “***Product Group Data” in the Queries browser on the left.  This time we will click on “***INSERT SHOP INFO HERE” in the APPLIED STEPS browser on the right.  And we will replace the text, “INSERT SHOP INFO HERE” with the PLA shop information for the specific client from Sklik, in this format:  zbozi:#####:Customer

12. Now we will refresh all the data by clicking on Refresh Preview > Refresh All.  If there is a large feed this can take some minutes.

13. We are now ready to leave the Power Query Editor, so we will click on the “X” in the upper right hand corner and click on “Keep”.

14. Now the Power Query is set, and from now on all we need to do is refresh the data in Excel.  We do this in the Data tab by clicking on Refresh All > Refresh All.

Step 5: Import to Sklik

1. Now we are ready to import the 4 sheets in the Excel file to Sklik.  First, we need to prepare each individual sheet as a separate .csv file with UTF-8 encoding.  To do this we use a text editor (like Sublime Text). Select all the rows and columns (including the header row 1) in the first sheet, Ad Groups Output Desktop, and copy & paste the data into the text editor.  Then save it as a .csv with UTF-8 encoding. Do this for all 4 sheets.

2. In Sklik, go to the correct account and click on Tools > Campaign import.

3. Then click “Read file” and select the first .csv file with Ad Groups Output Desktop (this must upload before the Product Groups).  Once it has uploaded, select it, and select UTF-8 in the file encoding dropdown.

4. Then click continue, and in the next screen the file will be checked.  If everything is ok, and you do not receive any errors, then be sure you select the 3 option, Update mode, so that you do not erase all of your campaign data in the account.

5. Import the first .csv then repeat to upload the last 3 files.  And you are done!

Leave a Reply

Your email address will not be published. Required fields are marked *


Líbí se ti PPC Robot? Nebuď hamoun a poděl se s přáteli! Facebook Tweet Youtube

Že ses ještě nerozhodl?

Nevadí, můžeš na měsíc vyzkoušet trial verzi zdarma.