Skip to main content

Group data across multiple rows with the Group Attributes data service

Group data across multiple rows with the Group Attributes data service in Productsup.

Introduction

The Group Attributes data service lets you group information from different rows in your feed. You can do the following actions to the values for products grouped by some common attribute:

The service works based on the SQL function GROUP BY. See SQL Tutorial for more information.

Prerequisites

To set up the Group Attributes data service, you need:

  1. A product identifier. See Set a product identifier for more information.

  2. At least two (2) columns in your feed if you want to use the Concat grouping method.

Add the Group Attributes data service

  1. Go to Data Services from your site's main menu and select ADD SERVICE.

  2. Search for Group Attributes, select Add, rename the service as desired, and enter a prefix that should prepend the result column name.

    You can leave the default column prefix ___.

    Group Attributes data service
  3. Choose the stage containing the columns you want to group in Service Data Level and select Add.

    Note

    The Group Attributes data service is available for the import and intermediate stages.

  4. Select the column containing the values you wish to group from the drop-down menu Column containing the values.

    Note

    If you choose Import in Service Data Level in Step 3, the drop-down menu Column containing the values displays the columns of your import stage. If you choose Intermediate, the drop-down menu shows your intermediate-stage columns.

  5. Optionally, in the drop-down menu Import column that you want to group by (optional), select the column by which the platform should determine the common attribute.

    Tip

    To choose several columns, hold the CTRL (or CMD) key while selecting.

    Warning

    If you choose the Concat method further in the setup process, you must select a column in this drop-down menu not to cause data processing errors.

  6. Enter the desired name of the result column into the Result column field.

    Note

    The system creates a new column to output the grouped data into it without overwriting the existing columns. The platform prepends the Result column's name with the prefix you set in Step 2.

  7. Select the grouping method from the Method drop-down menu.

    Note

    For the Concat method only:

    1. In Delimeter, select the delimiter that separates the grouped values in the result column.

    2. Activate Concat unique if you don't want to display duplicate values.

  8. Select Save.

    Group Attributes settings
  9. Add another Group Attributes data service to your site and repeat steps starting with Step 4 to group more values.

  10. For the platform to process a new data service, select Run in the top-right corner of your site's view.

Examples of the output for different group methods

During setting up the data service, you can select from five (5) different data-grouping methods from the Method drop-down menu:

  • Concat

  • Count

  • Max

  • Min

  • Sum

Here is a sample feed we can use to show how each grouping method works:

id

item_group_id

stock

size

color

discount_in_percent

1

123

4

S

red

10

2

123

2

S

blue

15

3

123

1

L

blue

25

4

456

5

38

black

7

5

456

4

38

blue

15

6

456

7

38

green

5

7

456

2

40

green

10

Concat method

Use the Concat method to combine all values from different rows in one column.

Example:

To concat sizes for products with the same item_group_id, use the following setup:

  • Column containing the values: size

  • Import column that you want to group by (optional): item_group_id

  • Result column: concat_sizes

  • Delimiter: ,COMMA

  • Concat unique: off

Using this setup, the system groups all sizes of the products with the same item_group_id in a newly created ___concat_sizes column:

id

item_group_id

stock

size

color

discount_in_percent

___concat_sizes

1

123

4

S

red

10

S,S,L

2

123

2

S

blue

15

S,S,L

3

123

1

L

blue

25

S,S,L

4

456

5

38

black

7

38,38,38,40

5

456

4

38

blue

15

38,38,38,40

6

456

7

38

green

5

38,38,38,40

7

456

2

40

green

10

38,38,38,40

Note

The system combines the data without any spaces between characters.

Count method

Use the Count method to count the number of product variations.

Example:

To count the number of product variations with the same item_group_id, use the following setup:

  • Column containing the values: item_group_id

  • Import column that you want to group by (optional): item_group_id

  • Result column: count_variations

Using this setup, the system counts all product variations with the same item_group_id and records the result in a newly created ___count_variations column:

id

item_group_id

stock

size

color

discount_in_percent

___count_variations

1

123

4

S

red

10

3

2

123

2

S

blue

15

3

3

123

1

L

blue

25

3

4

456

5

38

black

7

4

5

456

4

38

blue

15

4

6

456

7

38

green

5

4

7

456

2

40

green

10

4

Max and Min methods

Use the Max or Min methods to find and display the highest or lowest value.

Example:

To return the highest discount among all products with the same item_group_id, use the following setup:

  • Column containing the values: discount_in_percent

  • Import column that you want to group by (optional): item_group_id

  • Result column: max_discount

Using this setup, the system compares discounts of all products with the same item_group_id and records the highest value in all rows of a newly created ___max_discount column for the products with the same item_group_id:

id

item_group_id

stock

size

color

discount_in_percent

___max_discount

1

123

4

S

red

10

25

2

123

2

S

blue

15

25

3

123

1

L

blue

25

25

4

456

5

38

black

7

15

5

456

4

38

blue

15

15

6

456

7

38

green

5

15

7

456

2

40

green

10

15

Sum method

Use the Sum method to return the numerical sum of products matching the selected conditions.

Example:

To sum the stock for products with the same item_group_id and color, use the following setup:

  • Column containing the values: stock

  • Import column that you want to group by (optional): item_group_id and color

  • Result column: stock_sum

Using this setup, the system sums all products with the same item_group_id and color and records the numbers into a newly created ___stock_sum column:

id

item_group_id

stock

size

color

discount_in_percent

___stock_sum

1

123

4

S

red

10

4

2

123

2

S

blue

15

3

3

123

1

L

blue

25

3

4

456

5

38

black

7

5

5

456

4

38

blue

15

4

6

456

7

38

green

5

9

7

456

2

40

green

10

9

Edit the Group Attributes data service

  1. Go to Data Services from your site's main menu.

  2. Search for your data service.

  3. Select the cogwheel icon () next to the desired data service to edit settings.

  4. Select Save.

Delete the Group Attributes data service

  1. Go to Data Services from your site's main menu.

  2. Select the cogwheel icon () next to the desired data service.

  3. In the Danger Area panel, select Remove this service.

  4. Select Yes.