Chat with us, powered by LiveChat

People are searching for the vehicles you have in your inventory. For example, someone is searching for (and wants to buy) a 2011 Meindl 246i (’cause it’s just about the coolest car never made).

If a dealer who had that vehicle in stock were to create an ad specifically for the 2011 Meindl 246i and target keywords like “2011 meindl 246i” and send visitors to the exact vehicle mentioned in the ad, really good things would happen.

Because the dealer’s ad would be shown only to people searching for a 2011 Meindl 246i and the ad would be extremely relevant to those people, the ad would experience a high Click-Through Rate relative to other ads in the same position targeting the same keyword. And because CTR is the single most important factor in Quality Score and, by extension, Ad Rank, the ad would experience a relatively low Cost Per Click and, subsequently, a relatively higher position.

quality-score

This boils down to more clicks for less money. If each of the advertisers above have the same budget, say $1000, the dealer paying $4.26/click will receive 235 clicks. However, the dealer paying $1.13/click will receive 885 clicks; nearly 4 times the number of clicks for the same cost! And if both advertisers experience the same conversion rate, the advertiser paying $1.13/click will also obtain nearly 4 times the number of sales from paid search ads, as well!

This hypothetical scenario underscores the importance of targeting your ads and keywords to the specific vehicles in your inventory.

I’m just gonna come right out and say it: Don’t even bother trying to do this manually. I mean, if you only have three or four vehicles in you inventory at any given time, then sure, you could (and probably should) manage these ads manually. But I’m guessing you’ve got somewhere between 100-500 or more new and used vehicles on your lot. And you’re likely selling and acquiring quite a few vehicles each day. It would be more costly to pay someone to manually manage your ads and keywords than to pay for a software as a service that will automate the whole process.

An automated dynamic inventory-based ad management system works as follows:

  • You supply the system with your inventory feed. For example, for VIN NINJA we acquire your inventory through HomeNet. (HomeNet can extract your inventory from your DMS, your website, or a variety of other sources.)
  • You create ad and keyword templates. These templates include placeholders that will be replaced with vehicle information from your feed. For example, the template for an ad headline might look like:
    {condition} {year} {make} {model}
  • The system would then generate ads and keywords for each of the vehicles in your inventory, replacing the placeholders with the respective information from each vehicle.
    Used 2011 Meindl 246i
  • When you acquire a new vehicle and it appears in your feed, the system creates a new ad.
  • When you sell a vehicle, the corresponding ad is removed.

Ready to get started? Check out VIN NINJA.

VIN-Ninja-graphic

Related Posts

26 Responses
  1. Dave Meindl

    Import the data into your “data” sheet with importrange. Effectively, there’s no difference between static data and imported data.

  2. David Stirlng

    Thank you very much for writing this. Using your example made it really easy to create a “Sort by: [Column name]” dropdown menu.

  3. Rodolpho Brock

    I MADE THIS SCRIPT

    function columnToLetter(column)
    {
    var temp, letter = ”;
    while (column > 0)
    {
    temp = (column – 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column – temp – 1) / 26;
    }
    return letter;
    }

    function columnOf(columnName, sheetName, headerRow){
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName(sheetName);
    headerRow = (headerRow?headerRow:1);

    var headerAddress = “A” + headerRow + “:” + columnToLetter(sheet.getLastColumn()) + headerRow;
    var range = sheet.getRange(headerAddress);
    var values = range.getValues();

    for( var column = 0; column <= values[0].length; column++ ) {
    if( values[0][column] == columnName ) return columnToLetter(column+1);
    }

    throw ("ERROR: NOT FOUND!");
    }

    SO I USE LIKE THESE:
    =QUERY(data!A:C;CONCATENATE("SELECT ";columnOf("Year";"data")))

  4. Wanderley Oliveira

    Thank you so much for sharing this awesome way to get things working out in a easy way. Your exemplos made it clear!

    Hugs from Brasil

  5. Elaine

    Thank you for posting this! I spent a ridiculous amount of time searching for a way to deal with my variable column locations. It seemed like such a small problem when I created the spreadsheet, yet it was incredibly difficult to find a working solution. This worked a treat AND provided to ability to match a cell reference instead of a string. I ended up creating a column of drop down menus to choose my desired headers:

    ‘DataSheet’ contains the data with variable columns
    ‘QuerySheet’ is where you choose the headers you’d like
    “DataHeaders” is a named range for your data’s headers, ie ‘DataSheet’!1:1
    ‘QuerySheet’!A2 and down are each Data Validated with a dropdown list for the named range “DataHeaders”

    =QUERY(‘DataSheet’!$A:$Z,”SELECT”
    &SUBSTITUTE(ADDRESS(1,MATCH(‘QuerySheet’!$A$2,DataHeaders,0),4),1,””)
    &”, “&SUBSTITUTE(ADDRESS(1,MATCH(‘QuerySheet’!$A$3,DataHeaders,0),4),1,””)
    &”, “&SUBSTITUTE(ADDRESS(1,MATCH(‘QuerySheet’!$A$4,DataHeaders,0),4),1,””)
    &”, “&SUBSTITUTE(ADDRESS(1,MATCH(‘QuerySheet’!$A$5,DataHeaders,0),4),1,””))

    My data set has over 60 columns, and I use this to grab over 30 of them with no problems. The formula is absurdly long, but it works. I’m sure it could be condensed to be more efficient, but I’ve spent far too much time already on such a small issue! It would be great if Google could make selecting headers in a Query more intuitive.

    Thanks again!

  6. Arun Kumar Nair

    This Is close to what I was searching. Thank you. Can you help me with my exact problem. It is as below:
    I have a sheet with sales data of salesman selling two products x and y arranged over a period of time. Like x jan’17 y jan’17 x feb’17 y feb’17 and so on till x dec’18 y dec’18. I want to find all columns which has sales of x in 17. Is it possible?

  7. Narendra

    hi expert, I have done code in MS XLS but this does not work for Google Sheets can you please help me create a macro for Google sheet, which will find certain text in a in given column and copy entire row and paste in given sheet name after Last entry.

    For an example I have a sheet which content verius fruites data on date 13-1-2018, if I run macro then it should find mango and apple in the given column refrence and copy entire row and add it to “mango” sheet after 12-1-2018 entry and same thing with apple too

  8. You can create macros in Google Sheets. However, in this case, it sounds like a macro might be unnecessary (and inefficient).

    For example, you could use QUERY. Suppose that your “fruit” is in column C.
    =QUERY(product_sales!A:Z,"SELECT * WHERE C = 'mango'")
    If you want to show only records from yesterday, you’d do something like the following. (Suppose that the dates are in column A.)
    =QUERY(product_sales!A:Z,"SELECT * WHERE C = 'mango' AND A = DATE '"&TEXT(DATEVALUE(TODAY()-1),"yyyy-mm-dd")&"'")

    Alternatively, you could use the FILTER function to pull in only the desired rows.

    However, easiest of all, you could skip all the extra sheets and formulas and simply create filtered views of the original sheet.

  9. Narendra

    Thanks sir for your reply. Actually I don’t want to filter data , what I want to do is, find given text here is “mango” in given column specified and copy entire row than it should add in next empty row in named “mangos inventory” sheet.

    I have searched a lot on net and taking some bits and pieces from verius code I came up with this code ,

    function givenmodi() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var s = ss.getSheetByName(‘Sheet1’);
    var r = s.getDataRange();
    var v = r.getValues();
    var arr = new Array();
    for(var i=0;i<v.length;i++) {
    if(v[i][0]==='Table') {
    arr.push(v[i]);
    }
    }
    var t = ss.getSheetByName('Table');
    t.clearContents();
    t.getRange(1,1,arr.length,arr[0].length).setValues(arr);
    };

    Now this code finding and copying properly but problem is it's not adding in next empty row in " mangos inventory'" sheet. In fact it's easiering sheet and than pasting..

    Hope you understood my problem.

    Thanks & regards

  10. Niranjan

    Hi, Thanks for the Query.
    How to arrange the same in ascending or descending order of a particular column value

  11. DP

    Is it possible to add Arithmetic, Grouping, Etc?

    I tried things like sum(“&SUBSTITUTE(ADDRESS(1,MATCH(“Year”,data!A1:C1,0),4),1,””)&” ), but kept getting errors.

  12. Yep. SUM, GROUP BY, etc. will all work so long as the their conditions are met. For example, SUM requires that all rows in the column be numeric. Anyway, here’s an example that works:

    =QUERY(data!A:C,"SELECT SUM("&SUBSTITUTE(ADDRESS(1,MATCH("Year",data!A1:C1,0),4),1,"")&")")

  13. Eléonore Breton

    Dave, thanks a million for your post, extremely useful. Would you by chance have a trick to select multiple columns with the same header? For example, I have 3 columns ‘Components’ but using SUBSTITUTE(ADDRESS(1;MATCH(“Components”;’Import’!A1:EE1;0);4);”1″;””) will always give me the first one…
    Best,
    E

  14. Eléonore, I’d recommend not having multiple columns with exactly the same header name. You can think of your spreadsheets like a database, especially when using QUERY. And think of each individual sheet like a table in your database. The first row is the header row in your table, and each column has a unique name. Having a unique name is how SQL and your database work together to identify what column you want. For example, if you have multiple columns named “Components” in a single table, writing a query like “SELECT `Components` FROM my_database” would be ambiguous. It’s unclear which “Components” column is desired.

    The quick and dirty solution is to add a sequential numeric value to each “Components” header name (e.g. Components_1, Components_2, Components_3).

  15. Eléonore Breton

    Dave, yes I totally agree this isn’t a database format for my sheet but it’s the format I export from the tool I use (Jira). Yes I’ll think I’ll have to resort to your quick & dirty solution.
    Thanks a lot again!
    Eléonore

Leave a Reply